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
  • http://dev.mysql.com/doc/refman/5.5/en/postinstallation.html
    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: http://user.services.openoffice.org/en/forum/viewtopic.php?f=40&t=32741
  • 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. http://user.services.openoffice.org/en/forum/viewtopic.php?t=6924
  • 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: http://dev.mysql.com/doc/refman/5.6/en/identifiers.html  plus modes described here:http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html
  • 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: http://user.services.openoffice.org/en/forum/viewtopic.php?f=5&t=38908
    • 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
  • http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html
    syntax: http://dev.mysql.com/doc/refman/5.6/en/load-data.html
    • 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 http://forge.mysql.com/wiki/MySQL_User_Guide_Part_VI_Tutorials
    • 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...