Thursday, July 26, 2012

Como creer disco de instalacion y installar por otra computadoras

  • connectar al Internet
  • iniciar la sessió com itadmin
  • Arreglar algunos cosas
    • Eror con Origen Kiwix
      • Menu/Preferencias/Origenes del software
      • Abrir "Other Software"
      • anule la seleccion de ... ppa...kiwixteam
      • cerrar y recargar
  • Actualizar
    • sigue menu/preferencias/Gestor de actualizaciones
    • has click por "instalar actualizaciones"
    • (hay un error con kiwix, pero no importa)
  • Remastersys
    • sigue menu/preferencias/Remastersys Backup
    • selecionar "Modify", despues "OK"
      • click "e" despues "OK" para no incluir algunos archivos
        • verificar que dice esso, si no, escribe:
        • /share/wiki/wikipedia_es_all_09_2010_beta1.zim /home/itadmin/* /home/maestra/*
        • esta dice que no incluya wikipedia ni index por 2 usarios (si incluya, es mas grande que disco DVD
      • click "c", despues "OK" a modificar nombre de archivo
        • creer una nombre, como 
        • lm9lxdenowiki.iso
        •  ... o algo que termina con .iso y no tiene spaces
    • selecionar "Backup", despues "OK", despues OK en nuevo ventana
      • esperar mucho tiempo!
      • (y no hacer nada más en el equipo)
  • quemar DVD o USB
    • DVD
      • conectar disco DVD con disco vacio
      • selecionar /home/remastersys/remastersys/lm9lxdenowiki.iso
      • empuje boton derecho del raton
      • selecionar "abrir con Xfburn"
    • USB
      • Formatear USB con ext4
      • instalar Unetbootin
      • Abrir UNetbootin: menu/Herramientas del sistema/UNetbootin
      • Selecionar "DiscoImagen" con ISO y archivo /home/remastersys/remastersys/[nombredeISO].iso
      • Selecionar Mostrar Todas las Unidades y selecionar su USB (talves /dev/sdb1)
      • Empuje Aceptar ... y esperar
      • si formatear USB con ntfs, tiene mensaje bootmgr is missing... es problema con ntfs
        • formatear USB con ext4 o ext3
    • con USB, si tiene sufficiente grande, se puede creer al iso de remastersys con todos archivos incluido al wikipedia

Termino con instruciones otra dia cuando esta proceso terminas!
pero es algo asi::::

Create Disk and Test
  • by default remastersys creates an iso file in the /home/remastersys/remastersys directory named custombackup.iso
  • I copied this over to another machine to burn it using
  • [scp /home/remastersys/remastersys/custombackup.iso maestra@[ipofmachine]:/home/maestra/Downloads]

Installing From DVD & followup steps

After burning the DVD, you can live boot another computer.  The menu option for directly installing from disk was not working.  Once the system is booted in live mode, you need to log off and log on as another user with admin privelages (auto boot set to boot into listo user with no privelages). 
  • switch users to Maestra user
  • open home folder
  • double click on the Install shortcut
  • language and time zone choices are not important as the system installs the options in the remastered image instead of what is chosen here.
  • Reboot after installation, removing install disk when prompted
  • Log off and back on as Maestra (need privelaged user)
  • Install any updates
  • Remove the following broken programs
    • gltron
    • gplanarity
  • Add the following programs (last minute addition - games that work well on older computers)
    • slingshot
    • pyracerz (Py Racerz)
    • tomatoes (I have no tomatoes)
  • Copy Wikipedia image from other computer
    • I saved this on a particular computer in directory /home/username/share
    • sudo scp [user]@[ipaddres]:/home/[user]/share/wikipedia_es_all_09_2010_beta1.zim /share/wiki/  --this copies from other computer to correct directory
    • sudo chown /share/wiki/wi.... itadmin

Friday, June 29, 2012

Nino Mint - XFCE Desktop for Spanish Education

Nino Mint Project     Learn - Play - Do - Build - Grow

Linux for Install on Older Computers for Educational use in Central America and Carribian

(work in progress)  
Based on the success of my last build (see this link) plus enhancements like locking down some settings (aka partial Kiosk mode) and including a reset script.

I decided for my current build to utilize Linux Mint Debian (LMDE) 32 bit with XFCE windows manager.  This will hopefully allow me in months and years ahead to update the build without starting from scratch.  XFCE will also allow some nice features like Kiosk Mode.  I am planning to implement some of Kiosk mode features combined with a RESET script to restore desktop areas which I do not lock.  Another idea is to setup a pre-configured server with PXE boot support and Clonezilla loaded with an original image, so they can completely reformat the computers when needed.

Without further ado, below are the steps I took to create a full featured, ready to use system for distribution on refurbished computers.

Tale of Contents
OS Install
Programs Installed - Basic
Users and Groups
Directories for Shared Files
Additional Program Setup Steps
System Tweaks - only once
System Tweaks - each user
Hide and Uninstall Programs
Kiosk Mode and Reset Script

OS Install
Installed LMDE XFCE 32 bit with 1 gig swap, otherwise remaining disk partitioned with ext4, no LVM.  Install all updates.

Programs Installed
The easiest way to install these is in terminal using command "sudo apt-get install" followed by a list of the programs listed below separated by a space (first letter of program is not normally capitalized and capitalization matters).

  • Guake - nice f12 terminal application - I will use this if no one else (favorite)
  • Xournal - note taking program for mixing drawn and typed notes
  • Programs included with Linux Mint: (will hide or delete those in Red for listo user)
    • This list is too long to type here, so I will only include those we desire to hide or delete
    • Terminal de Root
  • Darktable - professional quality photo program
  • Dia - Diagram editor
  • Inkscape - vector graphics program
    • ink-generator - to make pdfs and more
  • LibreCAD - CAD program
  • Picasa - Photo program
  • Pinta - simple, but flexible image editor
  • Gimp (Programa de maipulacion de imagenes de GNU)- powerfull and complicated image editor
    • abr2gbr - photoshop brush converter for importing custom brushes
    • gimp-data-extras - extra brushes, palates, etc for Gimp (installed by default)
    • gimp-gap - adds video editing to Gimp
    • gimp-help-es - help files for Gimp (installed by default)
    • gimp-plugin-registry - various add on tools
    • gimp-resynthesizer - makes large textures from small samples (add on)
  • Scribus - desktop publishing program
    • Scribus-doc - documentation (documentation English only)
    • scribus-ng-doc - additional documentation
    • scribus-template - more templates
  • Programs included with Linux Mint: (will hide or delete those in Red for listo user)
    • gThumb - photo thumbnail viewer
    • LibreOffice Draw - drawing program
    • Simple Scan - scanner interface
    • Visor de documentos - pdf viewer
    • Visor de imagenes - image viewer
  • Chromium-browser - fully opensource browser
    • chromium-browser-l10n - language packs
    • chromium-inspector - page inspector (lmde edition only)
    • chromium-codecs-ffmpeg and chromium-codecs-ffmpeg-extra (LM13 only)
  • Filezilla - gui FTP client
  • Programs included with Linux Mint: (will hide or delete those in Red for listo user)
    • client Thunderbird - email client
    • Client Pidgin - instant message client
    • GNOME PPP - dial up  modem configuration tool
    • Darktable - professional quality photo programNavegador web Firefox - web browser
    • Transmission - download manager
    • XChat IRC - instant message client
  • Basic256 - for learning basic programming language
  • BibleTime - Bible Study Program
  • Childsplay - education program for small children
    • childsplay-alphabet-sounds-es
  • El Pequeno Mago (littlewizard) - Learn basics of programing
  • GChemPaint - Chemistry Molecule Builder
  • GeoGebra - Geometry learning application
  • GNU Solfege (Solfege) - music education program
    • solfege-doc
  • GPeriodic - simple periodic table
  • JFractionLab - learning fractions
  • Kalzium - Periodic table of elements
    • KHelpCenter4 - Help center used for this and other KDE designed applications
  • Kbruch -- fraction learning application    (not yet in Spanish language)
  • Kiwix - (see below how to install)
  • Klavaro - Typing tutor
  • KmPlot - Plotting program
  • KStars - Star map program
    • kstars-data-extra-tycho2 -- extra starmap info
  • KTurtle - Programming learning program   (not yet in Spanish language)
  • Marble - Globe and map program
    • marble-plugins -- enables the shared libraries
    • libmarblewidget13 - shared libraries for Marble
  • MathWar - a math memorization game
  • Suite educativa GCompris (GCompris) - educational program for small kids
    • gcompris-sound-es  -- Spanish language for gcompris
    • gnucap - electrical circuit plugin for gcompris
  • TuxMath - Math game
  • TuxPaint - painting program for very young kids (80Mb)
  • TuxType - Typing game
  • Glchess (ajedrez) - faster chess game (3 Mb) 
    • stockfish - computer AI player
  • Atomix - puzzle chemistry game
  • Biloba - strategy board game
  • Bovo - 5 in a row strategy game
  • Ceferino - platform (2Mb disk space)
  • Chromium-bsu - arcade style space shooter
  • ColorCode - like mastermind (1 Mb)
  • Enigma - marble puzzle
  • ExtremeTuxRacer - downhill ski game with penguin
  • Flobopuyo - color matching tetris like game
  • FreeCiv-GTK (freeciv-client-gtk) - turn based strategy game
  • Frogatto - classic platform game
  • Frozen-bubble - bubble matching game
  • gBrainy - puzzle game (automatically installs in Spanish language)
  • GRhino - Go clone (2 Mb)
  • Wesnoth (la batalla por) - turn based strategy game
  • lBreakout2 - like classic breakout
  • lMarbles - game like atomix
  • Mahjongg - classic tile game
  • Minas (gnomine) - clone of Minesweep
  • Neverball - tilting floor marble game
  • Neverputt - mini golf game
  • OpenArena - FPS like Doom
  • Pingus - like lemmings
  • PyRacerz - top down toy car racing game
  • Quadrapassel - tetris clone
  • Ri-li - train game for younger kids
  • Slingshot - physics based space battle
  • Sudoku (gnome-sudoku) - math puzzle game
  • Solitario AisleRoit (aisleroit)- Solitare game with many games in one
  • Suite ecucativa GCompris (gcompris) - also in Education
  • Supertux - Mario clone
  • Tuxfootball - Soccer game
  • Warmux - multiplayer war game
  • AcetoneISO - disk emulator to mount CD/DVD images
  • Audacity - multi-track audio editor
  • Bombono DVD (bombono-dvd) - DVD authoring program
  • GNU Solfege (solfege) (also above in education)
  • Imagination - Slide show maker
  • Openshot - create and edit videos
    • openshot-doc - documentation files
  • Programs included with Linux Mint: (will hide or delete those in Red for listo user)
    • Brasero - cd burning
    • Gnome MPlayer - media player
    • Grabadora de sonidos - simple sound recorder
    • Mezclador - volume conrol
    • Reproductgor de peliculas
    • VLC - media player (was included)
  • Freemind - mind mapping program
    • freemind-doc  - help files
    • freemind-browser  - for viewing in web browser
    • freemind-plugins-svg
    • freemind-plugins-script
    • freemind-plugins-help
  • LibreOffice - all programs
    • libreoffice-help-es  -- Spanish help files for LibreOffice
    • libreoffice-l10n-es -- Spanish language
    • openclipart-libreoffice -- clip art files
    • libreoffice-pdfimport -- pdf import filter
    • libreoffice-evolution  - email plugin
    • libreoffice-filter-binfilter  - legacy file filters
    • docvert-libreoffice  - publish to pdf and other formats
    • Programs included with Linux Mint: (will hide or delete those in Red for listo user)
      • Diccionario - dictionary which requires online access
      • LibreOffice Base, Calc, Draw, Impress, Math, and Writer
    • Aptoncd - program to create backups, transfer packages to non-connected machines, etc  I use this for burning a DVD of my full build for installing directly on another computer.
    • Virtualbox-nonfree - virtualization program
    • OpenSSH-server - used for connecting to computer via SSH (for remote maintenance)
      • openssh-blacklist  - know bad openssh keys
      • openssh-blacklist-extra
    • numlockx - turns numlock on automatically at startup
    • gnome-system-tools (LM13 only was missing users and groups plus other apps)

    Users and Groups
    • Setup the following users via system/users and groups/add user
      • maestra: Installed with this user, will be utilized by instructors
      • listo: User for general use by students
      • itadmin: Reserved for IT admins only
      • adelante: Rarely used by students or staff with laptops
        (change password for each separate machine)
    • Advanced User Settings
      • Under system/users and groups, then advanced settings/user privileges 
      • All users should have all priveleges checked 
      • EXCEPT listo user should NOT have "administer the system", "monitor system registries", or "configure printers" checked
    • Groups - Select Manage groups (from the users Settings app) and make the following changes
      • adm - select all users EXCEPT listo
      • admin - create and select select all users EXCEPT listo
      • sambashare - select all users EXCEPT listo
      • ssh - select itadmin only
      • staff - select maestra and itadmin only
      • sudo group - select all users EXCEPT listo
      • users group - select all users
      • vboxuser - select all users
        • must be done after installation of virtualbox
    Directories for Shared Files
    Make the following directories for shared file location
    • /share  -- used to hold shared information via the following subdirectories
      • sudo mkdir /share
    • /share/basic
      • sudo mkdir /share/basic
    • /share/biblia
      • sudo mkdir /share/biblia
    • /share/foto
      • sudo mkdir /share/foto
    • /share/musica
      • sudo mkdir /share/musica
    • /share/wiki
      • sudo mkdir /share/wiki
    • /share/admin
      • sudo mkdir /share/admin
    Now modify rights on these directories
    • /share rights - I want itadmin as owner with associated group as users (-R command makes it recursive to all subdirectories)
      • sudo chown -R itadmin.users /share
    • change rights so all users can read, write and execute files in these directories
      • sudo chmod -R a+rwx /share
    • Add sticky bit on these directories which disallows different users from renaming or deleting another users's files or directories.  That way we can add content as itadmin or maestra that all users can access, but cannot delete or modify (e.g. bible and wiki files).
      • sudo chmod -R +t /share
    • Verify the directory structure... should be as follows
      • sudo ls -l /share
    • change rights on /admin directory .....
    drwxr-xr-x  2  itadmin itadmin..... admin
    drwxrwxrwt 2 itadmin users ....... basic
    drwxrwxrwt 2 itadmin users ....... biblia
    drwxrwxrwt 2 itadmin users ....... foto
    drwxrwxrwt 2 itadmin users ....... musica
    drwxrwxrwt 2 itadmin users ....... wiki

    Additional Program Setup Steps 
    Programs that need additional setup to work perfectly

    • Basic256 - help files not fully included by default, plus add examples to share directory
      • go to and download the latest program file
      • unpack download file into Descargas/basic directoy
      • Copy the 'help' folder to /usr/share/basic256/ and check correct ownership and permissions... after first removing the broken help files
        • sudo rm -R /usr/share/basic256/help
        • cd ~/Descargas/basic
        • sudo cp -R ./help /usr/share/basic256/  
          (this may not work in later version of the program)
        • ls -l /usr/share/basic
      • Copy the Examples directory into /share/basic/examples
        • sudo cp -R ./Examples /share/basic/
      • Copy the program icons
        • sudo cp ./resources/images/basic256.png /usr/share/basic256/
        • sudo cp ./resources/images/basic256.ico /usr/share/basic256/
      • Change menu to use the icon
        • gksudo gedit /usr/share/applications/basic256.desktop
          • change Icon= line to read
          • Icon=/usr/share/basic256/basic256.ico
        • save file
    • Kiwix - the latest Kiwix does not readily install, here are the steps I needed to take
      • log in as itadmin 
      • Download Latest GNU/Linux version if Kiwix program from here:
      • From the same site, also download the Spanish language zim file (10Gb for March 2012 build)
        • test file with md5:
        • md5sum /home/itadmin/Descargas/wikipedia_es_all_03_2012.zim
        • checks out equal to sum listed on web site
      • move to share directory (in terminal)
        • mv /home/itadmin/Descargas/kiwix[.....] /share/wiki
      • move zim file to share directory
        • mv /home/itadmin/Descargas/wiki[...] /share/wiki
      • unpack program
        • tar xvjf /share/wiki/kiwix[...].tar.bz2 -C /share/wiki
      • remove original tar file
        • rm /share/wiki/kiwix[...].tar.bz2
      • tested kiwix program by navigating to /share/wiki/kiwix/kiwix and double clicking on it (it is executable)... success
      • Add a menu item for kiwix for all users
        • in terminal go to /usr/share/applications directory
          • cd /usr/share/applications
        • copy klavaro.desktop as kiwix.desktop
          • sudo cp klavaro.desktop kiwix.desktop
        • edit file
          • sudo nano kiwix.desktop
          • change the following lines
            • Name=Wikipedia_Kiwix
            • Comment=Offline Encycolpedia Browser
            • Comment(es)=Encyclopedia Wikipedia Local
            • Exec=/share/wiki/kiwix/kiwix
            • Icon=/share/wiki/kiwix/xulrunner/chrome/icons/default/default16.png
          • ctrl-o to save, then ctrl-x to exit
        • Try running program from menu.... Fails with permission issue (also icon did not show up
          • look at directory permissions... 
          • cd /share/wiki
          • ls -l
            • shows kiwix owned by itadmin with owner permissions, but no permissions for group or other users....
            • of course this is because of sticky bit.... tricky
        • Correct permission issues by applying different permissions
          • sudo chmod a+rx -R /share/wiki/kiwix
          • Check permissions with ls -l on this directory
          • Shows read and execute permissions for all users
        • Try to launch from menu again -- success
        • Log out and try to launch from menu as listo user -- success
      • Build the index for listo user
    User Specific Changes
    Do each of the following steps while logged on as Listo user, and maybe other users as desired.

    Program Changes (user specific)
    • Set language to Spanish for the following programs
      • TuxTyping
      • Scribus -- via File/Preferences/General/Language
      • LibreCAD -- via startup dialog
    • GlChess
      • select stockfish as the computer user
    • Marble educational program - install extra maps
      • Open application and install a couple of additional maps
      • Natural Earth III, Historical 1570, Historical 1786, all other Historical maps, climate zones, one of each planet
    • Libreoffice
      • After installed add Spanish Language for Spell check, and other extensions
        Extensions:  Espanol, PDF Import, Export as Images, 
        • Tools/Extensions...
        • click on link to download more extensions
        • in web site click on Extensions sections, then type in Spanish and search
        • Download Spanish Dictionary from LibreOffice 
        • Go back to Extension tool and add Dictionary from Descargas folder
      • Add the other Writer extensions in the same manner
    • Chromium preferences
      • select settings under the lined "customize and control" icon in upper right of browser app
      • Basics
        • Click on "Make Chromium My default browser"
      • Advanced settings
        • Passwords and forms
          • deselect "enable autofill"
          • deselect "offer to save passwords"
      • Privacy - Content settings
        • Cookies
          • select "keep local data only until I quit my browser"
        • Location
          • select "do not allow any site to track physical location"
        • Notifications
          • select "Do not allow any site to show desktop notifications"
    • Firefox preferences
      • Edit/Preferences/Privacy
        • Tracking
          • select "tell websites I do not want to be tracked"
        • History
          • Select "Use custom setting for History" then 
          • deselect "remember search and form history"
          • Keep cookies until "I close Firefox"
          • select "clear history when Firefox closes"
      • Security
        • deselect "remember passwords for sites
    System Tweaks (user specific)
    • Add "Places" to panel (start bar)
      • right click on bottom panel and open panel preferences
      • on items tab, click add a new item, then select "Places" and close
      • in item list move places up to right after first separator then close
    • Audio Controls - add those needed
      • click on speaker icon on lower right
      • Select audio card "Playback: internal audio.."
      • Click on "select controls" button and add master control
      • now un-mute audio... and can adjust level as needed
    • "share" directory Link
      • Open file manager and navigate to the root directory
      • drag the /share directory onto the bottom of the left side of the file manager to create a permanent link  (could have used symlink, but this works well)
    • Application AutoStart - remove unwanted program auto starts 
      • settings/session and startup/Application Autostart (each user except adelante - may be often used on laptops for college students)
      • deselect the following
        • Blueman Applet
        • Guake (keep on for ITadmin)
        • mintUPdate (keep on for ITadmin)
        • mintUPload
        • mintWelcome (keep on for ITadmin)
    • Number of desktops = 1 (multiple desktops is confusing to windows users, maybe this will change over time, but too often someone tells me; "all my stuff suddenly disappeared")
      • Settings/Workspaces
      • Set # down to 1 (2 for itadmin)

    System Tweaks  - Which only need to be done once
    • Auto log-in for principle user
      • system/login window/security
        • check "Enable automatic login"
        • select listo user
        • check "Enable Timed Login"
        • select listo user
        • select 15 second pause
    • No font changes for login window
      • system/login window/accessibility
        • deselect "allow users to change...."
    • Turn numbers lock on automatically (seems to be no way to select it in a menu, so install a little program to run at start) - only done once
      • in terminal
        • sudo apt-get install numlockx (or use software manager)
    • Remove Unnecessary Grub Boot Menu Options - after several kernel options propogate in the boot menu, it may get busy.  The easiest way to clean this up is to remove the older, associated packages linux-header-[kernel version & architecture] and linux-image-[kernel version & arc.].  This removes the older kernels and grub, stops listing them as boot options.
      • open synaptic package manager
      • search for older linux-image and linux-header packages (3 for each kernel version)
      • mark for complete removal

    Move or Hide Menu Items for Programs
    Hide the following programs as shown below:

    Hide these by copying the .desktop file into local directory, then changing this file so it doesn't show the program in the menu.  This does not remove the program, just hides it.  Alternately, the program can be disallowed.

    Add Kiosk Mode and Reset Script Functionality
    Add functionality so it is hard to modify desktop beyond point of usable... and ability to reset for listo user.  Possibly make desktop reset automatic on start-up (would be very useful in classroom setting).  Also hide some menu items for some users.

    Regarding a startup script which would reset the panel and other desktop settings, there seem to be a couple good suggestions (see additional resources below).  Namely Cron jobs and a special start-up script.  Since Cron jobs are ubiquitous across just about all Linux distros, this seems like a good method.  Also, one can create a script which can be potentially run at another time and from a directory that itadmin user has rights to making it easy for modifying or replacing the script or adding other maintenance scripts with different functionality.

    Scripts desired

    - desktopsave -- will be used to save desktop configuration for user listo
    • Will need to be run as itadmin user, otherwise will not be able to write to config backup directory
    - desktopreset -- will be used to reset desktop configuration for user listo
    • Should be allowed to run as any user
    Script Setup Steps
    • Create Directory (from terminal as itadmin user)
      • sudo mkdir /share/admin
      • cd /share
      • ls -l  (to see what it looks like)
      • sudo chown itadmin:itadmin /share/admin
      • ls -l (gives output like this which is good)
        • drwxr-xr-x 2 itadmin  itadmin  4096 date time admin
    • Create save to directory (as itadmin user)
      • cd /share/admin
      • mkdir homesave
    • Create desktopsave script file
      • cd /share/admin
      • touch desktopsave
      • ls -l
      • chmod u+x desktopsave
      • ls -l
        • this gives... -rwxr--r-- l itadmin itadmin date time desktopsave
        • file is writable and executable by itadmin user only but readable by all.  This way other users cannot execute this program and save a bad configuration.
    • desktopsave script... edit in your favorite browser and add the following lines
      • #!/bin/bash
      • # desktop save script, used to save listo user configuration
        • this first line is standard for scritps, second line is a comment
      • cd /share/admin/homesave
      • # delete the previous backup
      • rm -r /share/admin/homesave/listobak
      • # make a copy of the current backup
      • cp -r /share/admin/homesave/listo /share/admin/homesave/listobak
      • # delete the current backup
      • rm -r /share/admin/homesave/listo
      • # copy listo home directory (.config and .local only for now)
      • mkdir /share/admin/homesave/listo
      • cp -r /home/listo/.config /share/admin/homesave/
      • cp -r /home/listo/.local /share/admin/homesave/
      • # make readable to all users
      • chmod -R a+r /share/admin/homesave/listo
        • this is not really necessary based on permissions on the homesave directory, but I include it in case these permissions get changed
    • Create desktopfix script file
      • cd /share/admin
      • touch desktopfix
      • chmod a+x desktopfix
      • ls -l
        • this shows file is readable and executable by anyone, but only itadmin can write to it, so any user has the rights to run this script to reset the listo user desktop
    • desktopfix script... edit in your favorite browser and add the following lines
      • #! /bin/bash
      • # desktop reset script
      • # just copy configuration and local directories (for now) other files can be manually copied if needed
      • rsync -r --delete /share/admin/homesave/listo/.config /home/listo/
      • rsync -r --delete /share/admin/homesave/listo/.local /home/listo/
      • # the following restarts the bottom panel if it was closed
      • #xfce4-panel         commented out - doesn't work well in script
      • # the following only applies to older xfce 4.2, starts taskbar if stoppped
      • #xftaskbar4           commented out - for older xfce
      • # the following only for xfce 4.4 lus, reloads windows manager (if borders missing on programs)
      • xfdesktop
    • desktopfixlistomenu script... edit in your favorite browser and add the following lines
      • similar to the above script, except rsync lines are commented out and the xfce4-panel is not
    Create Menu items for desktopfix and desktopsave scripts  
    The following menu items are desired:
    For listo user - REPARAR DESKTOP

    For itadmin user - REPARAR DESKTOP
                               - GUARDAR DESKTOP

    • Copy .desktop programs to appropriate users to use as templates and change ownership of files
      • sudo cp /usr/share/applications/gparted.desktop /home/listo/.local/share/applications/reparar.desktop
      • sudo nano /home/listo/.local/share/aplications/reparar.desktop
        • change the following lines
          • Name=FIX DESKTOP
          • Name[es]=REPARAR DESKTOP
          • Name[fr]=FIXER BUREAU
          • GenericName=FIX DESKTOP
          • GenericName[es]=REPARAR DESKTOP
          • GenericName[fr]=FIXER BUREAU
          • X-GNOME-FulName=FIX DESKTOP
          • Comment=FIX LISTO'S DESKTOP
          • Comment[es]=REPARAR DESKTOP DE LISTO
          • Comment[fr]=FIXER BUREAU LISTO
          • Exec=/share/admin/desktopfixlistomenu
          • Icon=gparted
          • Terminal=true
          • Type=Application
          • ....
        • save file
      • sudo chown listo.listo /home/listo/.local/share/applications/reparar.desktop
      • sudo cp /home/listo/.local/share/applications/reparar.desktop /home/itadmin/.local/share/applications/reparar.desktop
      • sudo cp /home/listo/.local/share/applications/reparar.desktop /home/itadmin/.local/share/applications/guardar.desktop
      • sudo nano /home/itadmin/.local/share/applications/reparar.desktop
        • change the following lines
          • Exec=sudo /share/admin/desktopfix
        • save file
      • sudo nano /home/itadmin/.local/shaore/applications/guardar.desktop
        • change the following lines
          • Name=SAVE DESKTOP
          • Name[es]=GUARDAR DESKTOP
          • Name[fr]=SAUVEGARDER BUREAU
          • ...
          • Exec=sudo /share/admin/desktopsave
        • save file
      • sudo chown itadmin.itadmin /home/itadmin/.local/share/applications/guardar.desktop
      • sudo chown itadmin.itadmin /home/itadmin/.local/share/applications/reparar.desktop
      • ls -l /home/itadmin/.local/share/applications
        • shows itadmin as owner on all .desktop files here

    Problem Resolution - common

    • Disappearing [start] panel, desktop or windows borders
      Because Xfce is modular, each of these can be inadvertently stopped, removed or crashed.  Type the following command into terminal (Alt-F2), depending on the missing item.  You may need to choose "Save session for furue logins when shutting down to make changes permanent.
      • Panel (start bar) missing --> xfce4-panel
      • Taskbar missing --> xftaskbar4  (xfce4.2 only)
      • Windows Manager missing (no borders on programs) -->xfdesktop (xfce 4.4 only)
    • Video not working smoothly - may need to change video drivers

    TO DO: For future Releases

    • Local update repository... for updating all computer without using Internet bandwidth.  Probably should be added to the Zentyal server, maybe using virtual server
      • found this tip:  install apt-proxy or approx on a server... maybe directly on Zentyal server, then can either configure each computer to point to Zentyal or maybe even configure the Zentyal server to send all update requests to it!  
      • This caches all updates on the apt-proxy or approx server and keeps individual computers from downloading updates from the Internet.
    • Deployment/reinstall system
      • Clonezilla server.. side by side with Zentyal or as a VM under Zentyal (most secure)?
      • Clonezilla on DVD or USB boot?
      • remastersys?  limited to 4GB, I believe
    • Configuration management program on server options include:
      • Puppet - or another auto configuration management program. Need to research and test first
    • Monitoring server program, e.g. Nagios?
    • Other Games Possibilities from
      • Xonotic (was Nexuiz) -best in breed free fps
      • Voxelands - minecraft clone
      • The Dark Mod - like theif
      • Tales of Maj'Eyal (ToME) - a roguelike RPG
      • 0 A.D. - like Age of Empires II
      • Supertuxcart
      • Hedgewars - like worms
    Additional Resources

    Programs to try

    • iTalc
    • DVDstyler, tremulus
    • WorldofGoo

    Stuff Tried, but not Kept
    Games and Programs which were tried, but did not work with either this configuration or the age of computers I am using.
    • Play on Linux: ... There are too many good games being ported to Linux, this is no longer critical to include.
    • Educational: Stellarium (did not open), Celestia (slow), Pysycache (slow kids program)
    • FPShooters: Hedgewars, Nexuiz, Sauerbraten, Alien arena, AssaultCube
    • Board Games: BrutalChess, DreamChess, LondonLaw, Mah-jong (complicated), Pychess, Quarry (complicated), 
    • Strategy Games: rott-Rise of the Triad (fails to start), Warzone 2100, pioneers (complicated), stroq (hard to understand), Widelands (slow),  
    • Archade Games: Blockout II (3d tetris, doesn't load), Zaz (doesn't load), 
    • Platform Games: SMC (campy mario nock off), 
    • Other: Flightgear (very large), Supertuxkart (slow), Titanion (fails to load), Frets on Fire (crashed), Oolite (hangs), Wormux (nice game, but hangs when quitting), rrotage (couldn't figure out how to play)
    • Those which would be worth trying on better hardware:
      • Widelands
      • Liquidwar
    • Dosbox... not installing this program which allows playing of old DOS games since this is no longer relevant, especially for better quality hardware
    • TuxGuitar - unable to figur out midi backend... tried installing Timidity, qmidiarp, qjackctl as options, then selecting in TuxGuitar... hmmm, I don't really understand this midi stuff, giving up for now.
    • Chemestry... Chemtool, EasyChem, GDIS Molecule modeler, ghemical, PyMol, and Viewmol (GChemPaint was chosen instead)
    • Games that were good or decent, but just had too many already
      • Berusky - lazy bug puzzle game
      • Konquest - complex strategy game
      • lTris - tetris clone, but I liked quadrapazzel better
      • Xmoto - 2d motorcross game - still needs just a bit of development work, was missing some levels
    Obsolete Instructions from Previous version - now fixed or not aplicable

    • Childsplay - To get Childsplay to use Spanish alphabet sounds, need to specify at launch by changing the menu launcher as follows.
      • program needs to launch with the following command (caps important)
      • LANGUAGE=es childsplay
      • tested from command line... honestly the Spanish tract is not that great, hmm maybe I should keep it in English... learning English numbers not so bad ?
    • BibleTime
      • Bibletime is crashing when installing bibles and references... 
      • This is a bug in the Sword module... easiest solution may be waiting for bug fix, then installing it
      • currently on libsword8 1.6.2 + dfsg-1 and bibletime 2.8.1-2

    Tuesday, June 5, 2012

    Fedora 16 lxde Laptop

    Installing Fedora 16 for the first time on one of my Laptops... after years of using Ubuntu, Linux Mint and other Debian based distros.

    New Program Installs.

    • Google Chrome:  one of the first things I do with a new install is pull out Google Chrome.  
    • Java Runtime Environment
    • Try to install packagekit to aid in installing new software
      • Open Yum Extender
      • search for packagekit
      • select the following packages for install: PackageKit, PackageKit-glib, PackageKit-yum, PackageKit-command-not-found, PackageKit-docs, PackageKit-browser-plugin
      • select gnome-packagekit and apply changes.  This installs one dependency 
      • NOW, I can see Add/Remove Programs in System Tools
    • crypto-utils
    • dolfin???? gigolo used to connect to network storage, but this only opens file system up in web browser.
    • virt-manager 0.9.1
      • This version has configuration of bridges built into it for KVM virtualized machines
      • Not sure if this would work for ubuntu servers or just Fedora/Red-hat types
    • Programs I had installed:
      • filezilla
      • google chrome
      • pitivi - video editor
      • add/remove software --see above -- packagekit
      • Gigolo - for remote file system access
      • virtual machine filesystem browser
      • virt-manager 0.9.1
      • EasyLife - for added media support/configuration

    Tuesday, May 29, 2012

    LibreOffice Base Embedded hsqldb Conversion to MySQL backend and Base Front end

    LibreOffice Database with mySQL

    Install MySQL server and admin stuff.  Using Software Manager

    • mysql-server --this is a metapackage for all the required server programs for latest build
    • mysql-admin --this is a GUI admion tool for mysql
    • automysqlbackup  -- OK, this one makes sense based on my recent lost data episode... however found out later that I got the same functionality from mysql-server
    • mysql-query-browser --sounds good, I can do a little raw sql queries
    • libreoffice-mysql-connector  --hmm, I thought this was already in place, but this lists my version of libreoffice 3.5.3, I but will wait till after testing before deciding to install....... OK In the end needed to install this to connect directly to MySQL without ODBC or JDBC wrapper.

    Initial Steps - Connecting to MySQL on my computer
    • Opened mySQL Administrator and tried to enter connection
      Of course this did NOT work right off the bat... would have been too easy for unprofessional database program.
      • hostname = localhost
      • port = default of 3306
      • username = myuser
      • password = mine
    • Added my user to the group mysql
    • ran the following commands
      • sudo /etc/init.d/mysql restart
        this command returns: mysql staart/running, process 9312
      • sudo mysql -u root -p
        this command returns: Access denied for user 'root@'localhost'
    • Retried to open in mySQL Administrator
      returns Access denied for user
    • made a password for root - just to test, will take away root access if not needed
      sudo passwd root  -- this did not help, so disabled -- sudo passwd -l root
    • Still get the same message when trying to connect to SQL from administrator console or terminal
      read this
    • mySQL  HAS IT'S OWN ROOT ACCOUNT.... now I get it
    • Open mySQL Administrator and enter the following:
      • hostname = localhost
      • port = 3306
      • username = root
      • password = [blank]
    MySQL Administrator - Next Steps
    • create password for root account
    • create new user - same as my normal user
    • Create a new Schemata
      • Right click in Schemata area
      • Select create schema
      • Name it GVaccounts
    • Go to my user and add all Schema Privileges to GVaccounts
    Connect from OpenOffice
    Now I want to connect to this new schema from LibreOffice Base and save as a new Database
    • Open LibreOffice Base
    • Connect to an existing database
    • Select MySQL and press next
    • Asks for ODBC or JDBC Connectivity.... OK time to install the libreoffice-mysql-connector package from software manager to see if I can get native support
    • Awesome, restarted base and now get a third option to Connect directly!  Choose that one :)
    • Database name: GVaccounts
    • Server/Port = localhost   3306
    • Saved ODF file as GVaccounts in appropriate directory
    • got an error that could not connect (using password = no).... need to figure out how to enter password
    • Do this in Edit/Database/Properties
    Transfer Tables  - Attempt failed due to current bug  (see Below)
    Now that I can connect to MySQL database, copy the tables over
    • Open the new MySQL pointing database side by side with old copy of my database and attempt to drag and drop tables
      • I get the message "unknown database root@localhost when logged to server by root
      • I get the message "create command denied by User..." when logged to server by my new user
        • seems like it is not pointing to specific database... also the system database mysql shows in the Tables
        • I may need to change Server parameter from localhost to something that specifies the exact database...... looking into it
        • OR... the Database name I give of "GVaccouts" is missing something
      • TEST appending data
      • as mentioned, I see GVaccounts and mysql databases
      • So I create a table in the GVaccounts database
    • in MySQL Administrator Tools/manage connections create new connections
      • Tried to create a new connection to a specific database using another port, but did not work
    • It may be that current native MySQL connections don't have Schema unique connectors... see related answer at bottom of this post:
    • OpenOffice Bug reports including copying tables into Base connected to a MySQL database not working properly... This exactly describes my problem.
    • There are some report of dragging to copy working with JDBC based connection (not native), but the above report suggest this is also broken.  Here is the report where one users stated success.
    • Not sure I want to also setup a jdbc connectoion to MySQL while I am not sure it will work either, so I think I will try the next approach on my radar...
    Transfer Tables - Another Approach (I did not use)
    This method may be faster than the method I used, but does require that you pay close attention to Field formats, writing down the format for each field, then selecting the proper format as part of the Import procedure.  You will also may need to set your key fields after you import the data.  The steps are the same as the Data Import - Copy and Paste into Existing Table section below

    • ... except after you right click in the Table area of the new MySQL connected base file, 
    • you type the name of the new table you are creating, 
    • then you select to import definition and data
    • then you need to setup your field data types
    • and setup your keys (maybe after importing data)?
    • I quickly tested this method and it seems to work fine, but I personally imported my table definitions from the sql dump instead.

    Transfer Tables - New Approach Export then Import SQL dump files
    Because dragging and dropping tables was not working, try a more round about method of Exporting from the old database.
    • Export database
      • Open my old database
      • Tools/SQL
      • Enter command
        • SCRIPT '/home/UserName/pathiwant/database.sql'
      • click on Execute
      • Status reports "Command successfully executed."  --seemed way to fast for the amount of data I have.
    • View generated SQL statement
      • Right click file and select Open with Text Editor (gedit)
      • Amazing, Table create statements exist for each table, plus Insert statements for all data!  Looks great so far.
    • Import Tables one at a time
      • keep  database.sql file open in text editor
      • Open MySQL Query Browser
      • copy a create table statement over and execute
        • getting a syntax error, studying syntax
      • Modifications based on SQL syntax allowed:
        • CACHED (from CREATE CACHED TABLE) apparently not allowed
        • Remove quotes from around table and field names... not correct
        • longvarchar is not an acceptable data type
        • primary key must be specified as separate item, separated by comas e.g. ", PRIMARY KEY(field), "
      • Syntax guides - for ful reference
      • Example of query that did not work at first, then how I modified it so it did work:
        • Did not work:
          •  CREATE CACHED TABLE "CategoryDef"("Category" VARCHAR(50) NOT NULL PRIMARY KEY,"CategoryDetails" LONGVARCHAR)
        • Worked:  
          • CREATE TABLE CategoryDef(Category VARCHAR(50) NOT NULL, PRIMARY KEY(Category), CategoryDetails VARCHAR(255))
    • FIX:  Enable ANSI quotes in MySQL as described here:  plus modes described here:
    • I will change to ANSI quotes mode via MySQL Administrator as follows.
      • Open MySQL Administrator, connecting to the correct data source
      • Navigate to Startup Paramaters, then the Advanced tab
      • check the box "Use ANSI sql" about 2/3 way down page
      • click the save changes button at the bottom right of the window
        • I get an editing right error
        • bottom of form tells me my config file is at /etc/mysql/my.cnf
        • this means I need root privelages to make this change
        • look at menu item to figure out how to launch from command line
      • Close, then restart MySQL Administrator from command line as follows:
        gksudo /usr/bin/mysql-admin
        • make the change described above.  I suggest when you get to the check box you first click revert changes, then check the ANSI sql box, then save changes.  (when I was scrolling down, I accidentally changed another value on the form when I scrolled over it... and the value scrolled to another value).  The revert button changes anything back to how it was until you hit save.
      • go to Service control in MySQL Administrator, Stop the server, then restart.
    • Re-open MySQL Query Browser  -- CREATE TABLE
      • paste another of the CREATE TABLE statements which was output from Base into it.
      • remove the CACHED from CREATE CACHED TABLE
      • fix any mis-mached data types (this time there were none)
      • Query browser now accepts how the primary key is defined, so no need to change it
      • Success!
    • Actual SQL statement modifications I made for CREATE TABLE statements
      • remove CACHED from between CREATE TABLE
      • changed LONGVARCHAR to VARCHAR(255)
    • Table type = InnoDB:  For more rigorous data security, I want to modify the Data Storage Engine from MyISAM to InnoDB.  As I read it, InnoDB is more hardened to improper shut downs, etc and less likely to be corrupted from unexpected issues.  
      • Open MySQL Administrator
      • Select Catalogs, then the schemata desired
      • Select a table, then choose the button "Edit Table"
      • On Table Options Tab, in the Sotrage Engine area, choose InnoDB from the drop down box.
      • Click Apply Changes, then execute.
      • Repeat for each table.
    • ALTER TABLE statement in the sql dump are not needed (so ignored) as they were related to the "GENERATE BY DEFAULT..." statement we replaces with AUTO_INCREMENT.  MySQL will auto-increment based on the largest value+1.
    • CREATE VIEW statements... not sure yet if I need these views with MySQL backend.  I will insert them for now... by copying one CREATE VIEW statement at a time into Query Browser.
      • casewhen does not exist in MySQL, converted statement to CASE WHEN... ELSE...END statements
      • You can see the new views in MySQL administrator via Catalogs/YourSchemata/Views tab
      • And I can see the Views in Base in the Table browser section
    Loading Data: INSERT INTO statements  - first attempts
    • Create tables works fine... it is inserting data into them that may prove to be a challenge if we use this method as in my test, each insert into statement must be modified, to remove the quotes around the table name.  Also each set of data must be coma separated without "insert into" statement between the sets. This could work if you only had a small amount of data, but for my principle tables with transaction data, would be untenable.  SEE FIX ABOVE to enable double quotes around table and column names in MySQL.
      • Did not work:
        • INSERT INTO "CategoryDef" VALUES('Capital','Capitalized expenditures')
        • INSERT INTO "CategoryDef" VALUES('Deposit','Deposit that is not income')
        • INSERT INTO "CategoryDef" VALUES('Expense','Cash Expense items')
        • INSERT INTO "CategoryDef" VALUES('Income','Cash Income Items')
        • INSERT INTO "CategoryDef" VALUES('Tax','For tax reporting only, typically entered as expense in another form')
      • Worked:
        • INSERT INTO CategoryDef VALUES('Capital','Capitalized expenditures'), ('Deposit','Deposit that is not income'), ('Expense','Cash Expense items'), ('Income','Cash Income Items'), ('Tax','For tax reporting only, typically entered as expense in another form'), ('Utility','Specialized expense for utilities only')
      • After enabling ANSI quotes (see above), The quotes around column names was not a problem, only multiple lines
        • Copy a single INSERT INTO statement for the newly created table from the .sql dump file
        • Success!  works without error
      • Multiple statements - to insert many rows at once
        • multiple statements produce error when paste into query window
        • Open new script Tab via file/new scritp tab
          • past multiple statements here
          • when execute, states done, but no records appear in table
          • NEED ; AFTER EACH STATEMENT even the last one
          • Success after putting semi-colon after each statement
          • OK, so importing 10,000 records like this if you need to add semi-colons after statements will not work well.  Maybe another product does not require the semi-colons or maybe there is a setting somewhere.....
        • ONE SOLUTION: So, I can copy sections of the INSERT INTO statements (say one table at a time) into LibreOffice Writer, then search for ")" and replace with ");"  This only works because I know I have no end parenthesis within my data.  Otherwise I would need to be more clever.
    DATA IMPORT -Actual step by step
    • Copy and Paste (append) into Existing Table Base (old) to Base (new)
      • After trying to copy whole tables including definitions without success, I gave up on the Copy from One Base (embedded-old) to the other Base (MySQL connected-new).  Turns out I gave up too soon.  Discovered you can Copy and Paste if you have the Table Definition and only Append the Data.
      • I did have a data definition conflict on one table which I couldn't figure out how to resolve, so ended up copying data over for that table via the next method.
      • This is much like the following procedure, but much quicker.
      • Steps
        • Verify your table definitions exist properly in the new Database/MySQL
        • In the old Base program Right click a table and select copy
        • In the new Base program Right click the corresponding table and select paste
          • Select Append data
          • Ensure the table name is correct as SchemaName.TableName
          • click next then make sure all the source table fields are selected and the orders fo the fields are the same
          • Click Create and verify your data was imported.
          • Success!
    • Copy and Paste into Existing Table -- Success -- Second Easiest ApproachThanks to:
      • Register your old database as a data source in LibreOffice
        • Tools/Options/LibreOffice Base/Databases/New
        • Give the data source a name and point to the file :)
      • If your table has lots of data, to avoid data loss, or computer crashes, you may need to create queries and separate  your data into smaller chunks via simple select queries.  You would then copy data to the spreadsheet from the query instead of the table, so as to work with a manageable sub-set of data.
      • Import data into spreadsheet
        • Open a Blank spreadsheet (or new tab)
        • Press F4 or select View/Datasources
        • In the new data section above the spreadsheet, Navigate to the Table of the old database you want to import
        • When the table data shows to the right, click in the upper left box of the data to select all data (or select all or a subset any other way)
        • Copy data down to the spreadsheet by copy/paste or by dragging
      • Copy data from spreadsheet into Database
        • Highlight the data in the spreadsheet, including headers
        • Navigate to you new MySQL connected database
        • Right click on the Table name you want to append this data into and choose paste
        • Choose "Append data" and "Use first line as column names", verify the table name is correct as YourSchemaName.TableName  and press Next
        • Make sure the box is selected for the Source table field of each field you want to import (each one in this case)
        • Make sure the field order between the Source and Destination tables are the same, use the arrows to modify if needed (shouldn't be necessary if you created tables exactly the same)
        • Select the Create button, then open the table to verify your data is there (may need to wait a sec for large amounts of data)
        • Success!
    • MySQL Query Browser: Modified Data Via Find and Replace --Works
      • Open the .sql dump file in a text editor
      • Locate a desired set of INSERT INTO statements (perhaps a single table, perhaps all your tables).
      • Copy and paste into a blank document in a text editor of your choice.  I am using LibreOffice Writer 3.5
      • Find and replace to place semi-colons at the end of each line.
        • Find statement:
          • If you are certain that your data has no end parenthesis within it ")", then you can simply use that.
          • Some text editors allows you to use "&" for end of line, mine did, but skipped lines.  Finding on this was enabled via selecting "regular expressions" find
          • if your last field was always text you could use ') or NULL)
        • Replace everthing you find plus a semi-colon.  If you use the & find, see help link above for re-inserting end of line code
        • My example:
          • position at the top of the document
          • Edit/find and replace
            • ensure "regular expressions" is not selected in "more options" section
            • Search for = )
            • Replace with = );
            • select replace all
      • Open MySQL Query Browser and connect to your database
      • Select File/New Script tab
      • Copy and paste your modified INSERT INTO statements into the New Script tab
      • Click on Execute button
      • Open your database and verify the records have been inserted.
      • Success!
    • MySQL Command Line: Load Data Local Infile  --Stumped
      • Open the .sql dump file in a text editor
      • Locate a desired set of INSERT INTO statements (perhaps a single table, perhaps all your tables).
      • Copy and paste into a blank document in a text editor of your choice.  I am using LibreOffice Writer 3.5
      • File/Save as text format (.txt)
      • Open mysql command line (open terminal in Linux)
        • open terminal (Linux)
        • mysql -u DBUser -h [localhost or servername or ip] -p
        • "-h localhost" is optional on local machine
      • Connect to your database
        • connect YourDB
      • Load File...... OK not working because it is looking for a data file only, not sql statements.
      • This would work well if you had data in a spreadsheet then saved it as a delimited txt output file.  
      • I could modify my SQL dump to remove the extra stuff with find and replace, but what's the point... could load into MySQL Query browser just as easily... see next section
        • for fun, I did this... but didn't follow through to complete success (unsure how to quote a single quote successfully)  The statement I got to, then decided it was unnecessary effort and no more fun was this:
        • LOAD DATA LOCAL INFILE '/home/chenier/Documents/GreenView/GVaccount/DataTemp/HouseDef2.txt' INTO TABLE HouseDef FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY ' LINES TERMINATED BY '\r\n';
        • error on the FIELDS ENCLOSED BY
    • Export to Spreadsheet, Create delimited file, then import into SQL --Not Tried, but I know this would work
      • This is much like the section above, but with exporting data into txt file via spreadsheet save as instead of directly from sql dump.
    • MySQL Command Line run SQL Statement --Stumped
    Queries, Forms and Reports Importing

    • Open the old database and the new database side by side
    • Drag and drop (or copy/paste) each object you want to keep from one to the other

    Modifications Needed to Queries, Forms and Reports for MySQL
    This is not a comprehensive list, just the items I ran across

    • Queries
    • Forms
      • Change Table Names whenever referenced
        • Form Properties/Data tab => Content and Filter properties
        • Change table name to include Schema name in front
          • e.g. Filter to GVaccounts.Filter
          • Remove Double quotes from around Table name whenever exist

    • Reports

    Moving Macros over & Modifications

    • In the new Database, Create modules to correspond to those in the old database
      • Tools/Macros/Organize Macros/LibreOffice Basic
      • In your database, navigate to the correct location and select new
    • Copy and paste macros from within the modules in the old database to within the same modules on the new database
    • Rename the macro calls if you need to
    • Changes within the macros
      • none yet...

    Backup Utilities...

    Sunday, May 27, 2012

    Unsuccessful: Convirt LibreOffice Database to HSQLDB 2.x back end from embedded

    After my Open office .odb database crashed and I lost about a day of work, I decided to convert my database from embedded to the more robust front end - back end configuration.
    I found help sources like the above and a few others, but it seemed not all the information was put together neatly for Linux Mint / Ubuntu /Debian users, so I thought I would document my own conversion attempt.

    !!  FINAL WORD FROM ME  !!  Moving onto another approach for now.  Got stuck on Java Heap Space errors.     In the End:  I changed to MySQL backend with LibreOffice Base front end.  I've been running solid for over a year with this new configuration, and I love it.... Runs well and MySQL is forgiving of me turning my computer on and off daily without taking special steps.  Speed Now is much FASTER!  Here are my notes:

    I will either use database as embedded until OpenOffice/LibreOffice implements full support of HSQLDB 2.x or I will Install MySQL server and use it as the back end.... look for upcoming posts :)

    I use Linux Mint 11, 64bit Gnome with LibreOffice Build 2

    First step is to setup the database program to allow multi-mode
    Being on Linux Mint 11, I need to follow the 'Manual "multi-mode" database setup:' section of the above reference.  Here are my steps precisely
    • Make a couple copies of my database and place them in other directories and back up devices for safe keeping away from harm!
    • Open my database and check Tools/Options/Java
      • Use a Java runtime environ IS already checked
      • Sun Microsystems Inc. 1.6.0_26 is selected
      • I suppose this is recent enough, though I have a later version downloading in case I need to install it.
    • Could not find hsqldb.jar on my system anywhere, searched many forums and help documents with no good reference on what to do other than install HSQL 2.x.  In searching for help, I found it very wanting, so I stumbled along.... and happened to get it right the first time:   I went to and downloaded 2.2.8 file as a .zip file.
      • Downloaded
      • unzip this file
      • transfer the hsqldb directory from inside this directory into my Home folder /home/UserName/hsql
      • renamed it with . in front so it is hidden directory... /home/UserName/.hsqldb
      • the hsqldb.jar file is located in the /lib subdirectory and the help document states it was designed to work with java 1.6, so the java runtime I have should work fine.
    • Back to Base....
      • Open Base
      • Open tools/options/java and push the Class Path button
      • Add Archive... and select /home/UserName/.hsqldb/lib/hsqldb.jar
    • Creating a blank two part database  (in this example I will use 'MyAccounts' as the database name
      • create an empty new directory for your database like: /home/UserName/Accounting/MyAccounts
      • Select Connect to an existing database
      • Select JDBC and hit next
      • Datasource URL: jdbc: hsqldb:file:/home/UserName/Accounting/MyAccounts/MyAccounts;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
        • there are no spaces in the above
        • the first MyAccounts is the residing directory name
        • the second MyAccounts is for the created file name
        • the other parameters I don't really understand yet, though one can guess at some obvious interpretation
      • JDBC driver class = org.hsqldb.jdbcDriver
        • no idea why it is that, but it works
      • UserName = SA
        • unclear if that was needed or just preference of the original blog poster
      • Keep both defaults of register the database and save it
      • this created a new database, but asked me to save it, so I put in the same name, MyAccounts in the MyAccounts directory and it seemed to work.
      • So what I have now is a totally blank database named MyAccounts in the /home/UserName/Accounting/MyAccounts directory.  Besides the .odb file there are a few other files which we will replace in the next step.
    Next convirt my existing database into the new file:  
    To one of your copies of your original database:
    • Rename the dabase to [] .... that is change .odb to .zip
    • Open the zip with a zip tool and extract the contents of the 'database' folder into your MyAccounts folder.
    • in that folder, delete the existing MyAccounts.script,, files if they exist
    • Do NOT delete the MyAccounts.odb file   :)
    • Rename the extracted files (script, properties, data) to MyAccounts.script, and
    • Now when I open my database, MyAccounts.db, I see the tables from my old database, but none of the queries, forms or reports
    Now Copy Queries, Forms and Reports
    The above items need to be copied from the old database.
    • For Forms and Reports, it is as easy as opening your old database at the same time as your new one and dragging each Form and Report from the old database into the new Database front end.
    • For Queries, I ran into a problem that I got an error whenever I tried to drag a query over:
      • Error given:
        • The connection to the data source "NAME of database" could not be established.
        • file input/output error
        • /path/of/database/
      • This error is apparently caused by the later, incompatible version of hsqldb.jar being used.  I was able to correctly open older databases when I removed the reference to the new hsqldb.jar file in the LibreOffice Tools/Options/java/Class Path  menu area.  So I could then open the older database file and access tables and queries, but not the new separated one (of course).
      • The solution was to keep the reference to the new hsqldb.jar, then in the old database, Right click each Query, select Copy, then acknowledge the error message, then in the new database, right click in the Query area and select paste.  Simple work around once discovered.
    Persistent Java Heap Space Error
    OK, so now when I open a large table or query I get an out of memory Java Heap space error.  So far unable to work around even with parameters passed to java -Xms1024m -Xmx2048m.  I tried many things here, but in the end was still stuck.  I then got thinking, maybe it is better to wait until hsqldb 2.x is officially supported in Base, otherwise I may struggle with this with every updated (assuming I could work through it).  I could end up with problems bigger than I currently have.

    FINAL WORD FROM ME:  Moving onto another approach for now.  Got stuck on Java Heap Space errors.

    Copying Macros  ... I did this, but no bother sharing detailed steps, here is other help copied from someone else's online post..... Macros require export/import within the Base IDE (Integrated Development Environment) "organize" functions (more on this as needed). 
    NOTE: table column Field Properties workaround: Right-click your table(s) to Edit or Copy them. The Edit function is now limited to Add/Delete table columns/rows. The Copy function is now used to adjust existing table column/row properties. Simply Copy the table to a newly named table; adjust column properties as desired using the popup copy-table Wizard; after confirming the results simply delete the original table; then repeat the table Copy to reuse/reset the table to its original name; reset any global table relationships. Of course, those proficient with SQL can simply bypass the GUI workaround and perform these adjustments directly with ALTER TABLE ALTER COLUMN and/or ALTER TABLE RENAME TO commands. In any case, it's a good practice to issue the CHECKPOINT DEFRAG command using 'Tools>SQL...' after adjusting tables in this manner. And don't forget to use View > Refresh Tables to see your changes when working directly with SQL to alter table structures.

    Autonumber malfunction:

    Other helpful references:

    Memory issues: