Migrating DB from Embedded to Split-File

[felix]

I am attempting to migrate a significantly sized DB from an embedded mode to a split-file mode and am following the migration steps outlined by dacm in 'Migrating an "Embedded Database" from the all-inclusive database file (.odb) to a robust "split-file" database folder'

Steps A-H presented no problems and I can see my tables and they all appear OK. However I have a problem with the "Field Properties workaround". When I copy a table to a new (differently named) table the new table is created. I then delete the original table, but when I then want to rename the new table to the original table name, the rename option is not presented.

Also I noted that in the process of copying the table, I couldn't edit the column properties of the table. I didn't want to do this but I assumed that I should be able to do so if I so desired at thatpoint.

What am I doing wrong.

[DACM]

Steps A-H presented no problems and I can see my tables and they all appear OK. However...

...I couldn't edit the column properties of the table...

What am I doing wrong.

Looks like a successful port/migration overall.

Reference: [Tutorial] Splitting an "embedded database"

However, the 'rename' option for Tables is no longer available with 'split' (non-embedded) databases. It's one of several Field Properties that appear frozen in the Base GUI when connecting to an external database. And it only gets worse when connecting to any other backend database engine (PostgreSQL, MySQL, Oracle, etc.).

Here's the Field Properties workaround:

The Base Graphical User Interface (GUI) works well when managing Field Properties within the context of an "Embedded database" configuration. However, this configuration is prone to data loss due to zip (.odb) file corruption upon OOo crash. The immediate solution configures Base for external database access through the built-in HyperSQL database engine (tutorial). So fortunately, Base supports alternate database configurations through compatible drivers (JDBC, ODBC, etc.). Unfortunately, many of these drivers do not integrate with the 'Field Properties' aspect of the Base GUI very well. Or at least not as well as you might expect.

In reality, the Field Properties only appear frozen. You can effectively access most Field Properties by other means through the GUI. For instance, you can Cut, Copy, Paste, Delete, Insert Rows [database columns] which then grants access to the column's Field Properties. You can also apply a Primary Key(s). Or better yet, you can Right-click > Copy > then Paste the entire table in order to access most exisiting Field Properties (perhaps repeating this twice to re-use the original table name; then issue CHECKPOINT DEFRAG to recover the minimum database size using Tools > SQL...). This last option is outlined step-by-step with graphics at in this tutorial link.

But we still need a workaround for: AutoValue and Default Value. These properties (and others) require SQL as outlined below. The last remaining field property, Format Example, is apparently hard-frozen because there's no known workaround.

AutoValue

If you try to set the AutoValue field, it will always flip to "No" -- but this can be set to 'Yes' using SQL as outlined below (keeping in mind I'm not the resident SQL expert so backup your database folder first):

With an existing table:

use Tools > SQL... to add the AutoValue function to your Primary Key column as shown below...
replacing the orange-colored Table and Column names with your own:

ALTER TABLE "TableName" ALTER COLUMN "ColumnName" INTEGER IDENTITY;

This will update the table but you'll need to click: View > Refresh Tables to see the change in the Base GUI.

Note: You'll get an error if the specified column doesn't exist OR is not already a Primary Key column.

To add a new column as the Primary Key with AutoValue:
ALTER TABLE "TableName" ADD COLUMN "ColumnName" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
or
To add the Primary Key constraint to an existing column with unique values:
ALTER TABLE "TableName" ADD PRIMARY KEY ("ColumnName")
Note: INTEGER IDENTITY provides up to a couple billion (2^31) records per table. This generous limit equates to generating 100,000 records, per table, per day, for 60 years. But these values are generated in sequence without re-use so if you commonly generate millions of records per week then consider BIGINT IDENTITY which allows nine-quintillion (2^63) unique values -- which equates to generating 1 billion rows per second for 300 years.

Note: GENERATED {BY DEFAULT | ALWAYS} AS IDENTITY syntax is supported by HSQLDB 2.x. The two options {BY DEFAULT | ALWAYS} are very similar when applied to a Primary Key or other Unique Constraint column. Prior to HSQLDB 2.2 the IDENTITY sequence generator could only be applied to the Primary Key column.

When creating a new table: (with the Base wizard)

Select an ID field and AutoValue = Yes,
then type IDENTITY in the resulting 'Auto-increment statement' box.
Then de-select Create a primary key on the Next page of the Table Wizard.

Default Value

A Column's Default Value can also be setup using SQL as outlined here. Or, if you have the know-how, you can use a text editor to make the changes directly in your .script file.

[felix]

All sounds simple but unfortunately I still can't get it to work. Step J says:

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

When I try to increase a text field from 20 chars to 25 chrs the process won't accept the change and as soon as I exit the text box the value reverts to its original value.

What am I doing wrong? It good that my data is there but I need to be able to change the table definitions from time to time.

[felix]

Since my last post I have been able to edit properties of cols in the split file environment by copying a column to a newly named column, modifying the properties of the column, then deleting the original column and repeating the process by copying the newly named column to a column with the original column name. Only problem is when I go to save these changes they are rejected with a message to the effect that that column already exists.

Am I to conclude that it is only possible to edit column properties if the modifications are made and then saved as a new column name. If this is the case this is not a viable solution as all macros accessing that table would then have to be modified to reflect the new column name.

[Frankoz1959]

Hi,

I have followed the steps in the tutorial after setting the path,changing scripts and downloading the mydb file. I'm running LibreOffice 3.6.5.2 and have found the change to a split file necessary due to the now apparent instability of the embedded option. Why thats not made clear at the outset, I don't know - but I guess that's a moot point here.

After following the above and extracting and renaming the database files, I opened the mydb file by double clicking but nothing shows under the Table icon. I have attempted to troubleshoot the path, which is: c:\program files (x86)\LibreOffice 3.6\Program\Classes\hsqldb.jar but don't really know what error to look for and also the database URL: hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false. In summary I'm stumped.

Can you help me with what I'm doing wrong, or suggest another way for me to import the tables and forms?

Any help will be much appreciated.

Thanks
FrankOz

[MTP]

felix - After you make the copied column and delete the original column, save the table and close it. Then reopen the table and copy the column back to the name you want.

[Greengiant224]

FrankOz1959 posted:
the database URL: hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

The highlighted red path shown above has omitted your drive letter, is this a typo?

It should read:
C:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

The extracted files (*.script; *.data; *.properties) [where * = name of your database] should be pasted into the C:/users/public/databases/mydb/ folder alongside the mydb.odb file. If that's where you have installed it. You should now be able to view the enclosed tables.

Kind regards

Greengiant 224

[Frankoz1959]

Hi Greengiant 224,

Thanks for the reply. No, the omission of the drive letter was made by the program. I wasn't sure if you meant place the string after "file:" or to replace "file" with "C". In any event I have tried both and while they accepted and the tests for connection worked, I'm still unable to see the tables which are in nominated folder.

One thing which I should highlight is that while I renamed the extracted database files as required, the program seems to ignore them and create "mydb" files separately; in other words it ignores my "properties' and "script' files when I attempt to open the mydb database and creates new versions (see attached). Could this be the issue?

Thanks for your assistance,

Rgds
FrankOz

[Villeroy]

This is the URL of an actual database of mine. The backend is located on drive S: of a Windows box. The same .odb file can be used by all Windows boxes in the same network with the same network drive S:
Drive S: has a directory S:/hsqldbdata/db2/ with files Kassenbuch.script and Kassenbuch.properties

Code: Select all   Expand viewCollapse view

hsqldb:file:////S:/hsqldbdata/db2/Kassenbuch;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

This database connection provides write access to one client at a time and read-only access to subsequent clients.
From my Linux box I access the very same database with a modified URL ...

Code: Select all   Expand viewCollapse view

hsqldb:file:////mnt/praxis/SERVER/Daten/hsqldbdata/db2/Kassenbuch;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

... where /mnt/praxis/SERVER/Daten/ is a mount point to mount the S: drive.

###############################################

This is the URL of another database of mine. The backend runs as a server on a machine with IP 192.168.15.1. The same .odb file can be used by all boxes in the same network:

Code: Select all   Expand viewCollapse view

hsqldb:hsql://192.168.15.1/chargen;default_schema=true

This database connection provides simultanious write access to all authorized clients.

[MTP]

I notice in the picture that one of the script files just displays "mydb" and the other displays "mydb.script" - although both are called SCRIPT files by Windows. It makes me think Windows is hiding the file extension and the actual names are "mydb.script" and "mydb.script.script". Try deleting the .script off your filename and see if that makes the database recognize the file as belonging to it. (And the same thing for the properties file.)

[Villeroy]

Hiding file name extensions is the most serious bug (aka "feature") in Microsoft Windows since 1995. You've got to turn this option off in order to work with files on a Windows machine.

[Frankoz1959]

Thanks MTP, I thought that might do the trick too, alas while the file modified dates suggests that Libre Office read them, it is still is not showing the table data.

I'm becoming a little concerned. I have about 300 entries in an increasingly dodgy embedded database that I need to keep working on. I thought I'd lost it all yesterday but a backup finally worked.

I think I followed the instructions to the letter, except I didn't reboot after setting the path (I don't use the quickstarter).

Is there possibly a step that I've missed? Is this the correct path now?
(hsqldb:file:////c:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false)

Thanks for any help.

rgds
FrankOz

[DACM]

Am I to conclude that it is only possible to edit column properties if the modifications are made and then saved as a new column name. If this is the case this is not a viable solution as all macros accessing that table would then have to be modified to reflect the new column name.

No, you should be able to reuse the original column name using the GUI workaround. It may be necessary to issue the CHECKPOINT DEFRAG and/or Refresh Tables before creating a new column with the original name. Otherwise, the original column was not successfully dropped, which may be a hangup with referential integrity (delete relationships on the target column) or possibly constraints (delete any constraints on the target column).

Did you try copying the entire table as opposed to the individual column?

Note to Step J:

...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.

Otherwise, the Base SQL-console (using SQL; as opposed to the GUI) should be used to accomplish DDL tasks as necessary using the HSQLDB 1.8 documentation as applicable.

-------------------------------------------------------------------------------------------------------------------

Is there possibly a step that I've missed?

FrankOz

You've apparently done everything exactly right...except follow Villeroy's advice concerning the display of file extensions in Windows. The extensions of your database files were never actually changed due to this Windows bug (feature).

Oh, and the original URL will suffice (without drive letter or additional forward-slashes) as long as the database folder is located on the same drive letter as the database-server (hsqldb.jar). So there's no immediate reason to change the URL from the original file download (mydb.file.odb) in your case.

hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

The original URL was derived through a collaborative effort (including folks posting in this thread). The drive letter was eliminated, and the forward-slashes were selected over back-slashes, in order to maintain cross-platform compatibility with Linux, Windows, and Mac. If this is not the case, then please report the issues with the original URL in light of this cross-platform intent.

[MTP]

Frank - Did you delete the .data extension as well?

[Villeroy]

I found an easier way to convert an existing database:
1) Extract the hsqldb.jar and point the office to it (Options>Java>Class Path)
2) Extract the database directory and add your DB_NAME prefix to the extracted files.
3) Modify the following properties programatically:
-- DataSource.URL = jdbc:hsqldb:file:/PATH/DB_NAME;default_schema=true;shutdown=true
-- DataSource.Settings.JavaDriverClass = org.hsqldb.jdbcDrive
-- DataSource.User = SA

I used the MRI extension in Set-Mode as a GUI to modify these properties.
A simple Basic macro might look like this:

Code: Select all   Expand viewCollapse view

Sub Switch2External
REM adjust the value of cURL:
Const cURL = "jdbc:hsqldb:file:/PATH/DB_NAME;default_schema=true;shutdown=true"

oDS = ThisDatabaseDocument.DataSource
oDS.URL = cURL
oDS.User = "SA"
oDS.Settings.JavaDriverClass = "org.hsqldb.jdbcDriver"
ThisDatabaseDocument.store()
End Sub


Edit: Fixed a copy&paste error: jdbcDriver

[Frankoz1959]

Hi DACM/MTP/Villeroy,

Thanks again. I have readjusted the database URL, but now find that if i try to change the filenames in the mydb directory, that they lose their file extensions - why that has changed from a few days ago, I just don't know.

Given that I seem to be doing what is required but having no success, is there another perhaps more manual way of getting the tables into the Split Database - for example, in exporting Base to Access (which I'm reluctantly starting to consider) it seems that extracting the table to a spreadsheet file (CSV?) provides a basis for uploading? Can this be done between the embedded and split databases? Will it transfer all data (including pictures)?

Alternatively, Villeroy, I downloaded the MRI extension but need to a bit of help in finding where to create the macro. When I open the extension, the screen is already populated - not sure how to go about the macro process. Can you point me to some instructions - couldn't find much online.

Thanks All

[DACM]

...but now find that if i try to change the filenames in the mydb directory, <that ?> they lose their file extensions

That's odd. Did you enable 'Show Known File Type Extensions' ?

[Villeroy]

A programmer may use MRI as a GUI without writing a macro. You can copy my snippet into the database container (under tools>macros>Organize>basic>"yourDB.odb">[Edit...]) and run it (key F5) with a valid URL pointing to your database files. It seems to work this way but I tested only one embedded DB of mine.
You can also copy the snippet somewhere in the global container "My Macros" and replace the term ThisDatabaseDocument with ThisComponent.