OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to: Migrate Base Projects to Multi-User
Goto page 1, 2, 3, 4, 5  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Jan 10, 2010 5:16 pm    Post subject: How to: Migrate Base Projects to Multi-User Reply with quote

[update: Nov 2010]
This post is accurate and contains much more in-depth information on the topic, but it does not reflect the latest break-through including automated installers that do all the setup work for you. You'll be migrating to a flexible, 'split-file' database (folder) which can be accessed seamlessly by a single-user -OR- in client-server mode for multi-user access. Here's the new links:
The Migration of "embedded database" fie to a "split-file" database
and the related tutorial which covers all aspects of the configuration with Base:
[Tutorial] Avoiding data loss
Once you're up and running on a single computer with your split-file database running in server mode, and you're ready to add clients then read through the details of this post.

___________________________________________________________________________

The following is an overview of the steps and considerations involved with migrating a single-user Base project to client-server. This setup offers several advantages including multi-user access with user-accounts for secure database sharing over a network or internet connection. Additional benefits include a robust data-environment which overcomes the data corruption issues inherent to the default "single-file" database design which leaves data vulnerable to Base instabilities and crashes -- which can result in data loss [ 1, 2, 3, 4, 5, 6, 7... ]. This separation also allows the developer to supply front-end updates on-the-fly (as a single .odb file) without affecting data or disturbing the running RDBMS server component. As before, the resulting (.odb) file can also be used as a registered data source in OpenOffice for seamless access from Writer, Calc or Impress. And finally, this configuration allows the developer to upgrade to HSQLDB 2.0 or other RDBMS as desired.

As you may know HSQLDB is the embedded RDBMS in Base (OpenOffice.org Base). You'll need to run the RDBMS component in server mode to enable client-server features. This process effectively separates the database back-end (server and database files) from the ooBase front-end (GUI components: forms, reports, queries and macros). This tutorial specifically covers the steps necessary to run Base with HSQLDB-server on the same machine. Additional notes & links cover LAN client setup, and to some extent WEB client access. Everything you'll need is included with the standard OpenOffice/Java installation when utilizing the included HSQLDB engine (or consider H2 migration). The entire client-server database system can be distributed freely and run without installation from a USB flash drive when using OpenOffice Portable** with Java Portable. And if you're looking for a portable menu that can start/stop the HSQLDB server automatically, I use PStart to automate step 1f below.
** edit: You'll need a workaround to employ the Portable version of OpenOffice at this time, due to a flaw in the portable launcher that deletes client-server related settings.

But I'm no expert, so I would appreciate any correction or critique especially by the many experts who take pride in the information flow of this forum.

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

Running HSQLDB in server mode:
If at all possible, you'll want to begin by running the HSQLDB server and the Base client on the same computer. In any case, the HSQLDB back-end components, including the database server application and the data folder, will reside on a single computer. For this role, it's best to select a computer with sufficient RAM memory to cache your database tables in the RAM allocated to Java -- minus any LOB content -- plus at least 500 MB for OS and other overhead. Bottom Line: A fairly modern desktop or laptop with 1 GB of RAM is probably the minimum for good RDBMS performance nowadays. You'll want to adjust your Java RAM allocation, if you run large queries (>50 MB in size), and perhaps add physical RAM if performance suffers with very large (200+ MB) databases. Your database files will be contained in a folder (of your choice) on this computer. You'll need to "share" this database folder on the network. Other client computers will run a copy of your front-end (.odb file) along with OpenOffice and Java.

Side Note: Consider the durability and security of your data when selecting computer hardware. Any RDBMS can lose un-recorded data upon power failure regardless of software ACID ('D' = durability) compliance. ACID durability requires a finite amount of time in all cases, and that time is often increased by design to balance performance and scaling issues. Either way, this is just a matter of seconds, so consider running your RDBMS server (and clients) on a laptop with battery, or on a desktop with UPS backup power. This will ensure that all "commited" and "un-commited" data has time to become "durable" even as unprotected computers may be "rebooting." If you utilize additional networking hardware, consider plugging that infrastructure into a UPS as well. Of course, you can network without additional hardware by using an "ad hoc" WiFi network, but these can be less secure and harder to manage than a simple “infrastructure” WiFi network. So as a minimum, consider a portable, USB-powered, wireless router or the free Connectify software (requires Windows 7 on the RDBMS-server computer). Beyond computer/network considerations, an additional form of data durability is achieved through regular backups. Disk mirroring or striping-with-parity (RAID) protects against disk failure, while automated backups using synchronization software can provide date-time-stamped copies at regular intervals, even to the "cloud." More advanced protections such as RDBMS clustering and automated recovery are best left to IT professionals, but this 24/7 availability can be had for a few bucks per month if you don't mind internet latencies, shared servers, and adopting a common RDBMS & related management tools (LAMP tips). Data "security" involves additional measures. Encryption goes a long way in securing the transport and storage of data, as does RDBMS user-accounts, passwords, rights and roles. All of these are supported by HSQLDB (2.0 for natively encrypted databases). In addition, consider "full disk encryption" to secure against unencrypted file-fragments and page-files generated by the operating system or SSD wear-leveling mechanisms. Likewise, with "Cloud" synchronization consider a fully encrypted service.

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

If you're not familiar with running Base-HSQLDB client-server, here's a step-by-step:

STEP 1: Start the HSQLDB Server…

NOTE: Only one computer will run the HSQLDB server, so this particular step is unique to the computer chosen to host the database back-end server. Steps 2 & 3 must be accomplished on all computers requiring access to the database using the OOo Base front-end (.odb file). This tutorial specifically outlines the process of setting-up the Base front-end on the same computer that runs the HSQLDB server. See the notes that immediately follow STEP 3 below for more information on setting up additional client computers.

step 1a: HSQLDB is written in Java for maximum portability. It requires the Java Runtime Environment (JRE) which is normally installed on your machine, because Java is the backbone of some OpenOffice 3 features (Wizards and Base functions), among other apps. Either way, you can test your Java installation and /or download the latest JRE from Sun.

step 1b: HSQLDB Server is started with a command line string. A simple batch file may also be used to start the HSQLDB Server (see step 1e below).

step 1c: Note your path to the “Java.exe” installation:
The default Windows 32-bit path is: \Program Files\Java\jre6\bin\Java.exe

step 1d: And note your path to “hsqldb.jar” which is found in the OpenOffice “classes” folder:
<your path>\openoffice.org 3\basis\program\classes\hsqldb.jar
UPGRADE NOTE: Feel free to upgrade this .jar file to the latest HSQLDB 1.8.x version. However, if you want to upgrade to HSQLDB 2.0 it's best to add a subfolder (such as: \openoffice.org 3\basis\program\classes\HSQLDB2\hsqldb.jar) because the default version 1.8.x is necessary to support any single-file databases you may have. The latest HSQLDB 2.0 (OOo 3.4 in 2011...) is incompatible with HSQLDB 1.8 (OOo 3.3 and earlier). Update: For early adopters, the HSQLDB 2.0 "general release" is now available. The web site includes upgrade instructions from previous versions. This is a one-way upgrade path. It's as simple as issuing a SHUTDOWN COMPACT command using Tools > SQL in Base, then shutting down Base and stopping the HSQLDB server application, replacing the HSQLDB.jar file with the latest release, then re-starting the server application and Base with your old database files, then issue the SHUTDOWN COMPACT command once again to complete the upgrade.[/color]

step 1e: Now use these paths in a script/batch file to automate server startup (recommended) -OR- issue the command manually from the command line (tedious). Study the following batch file code and substitute your own paths to java.exe and hsqldb.jar. Relative paths are allowed and encouraged for portability. Also, replace “file:mydb” below with your desired database filename. If you don’t have an HSQL database file-set at this point, the file-set will be created using the filename you specify, located in the same folder as this batch file (or wherever the startup command is executed).

In Windows, copy this code to a text file (notepad) and save with a .bat extension.

Start_HSQLDB_Server.bat
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice.org 3\basis\program\classes\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:mydb
exit

Note: Database filenames may contain capital letters (-database.0 file:MyDB), but only small letters are allowed in an alias name (-dbname.0 aliasdb). Feel free to specify a filename for your database such as MyDB (otherwise your database filename will be "test"). But for simplicity, don't specify an alias name unless your HSQLDB server will serve multiple databases (advanced), because an alias can complicate matters as you reach step 3c below (hsqldb:hsql://localhost/aliasdb;default_schema=true).

step 1f: In Windows, optionally hide the DOS window as follows:
BatchLauncher.vbs.txt (right-click 'Save As' and rename to BatchLauncher.vbs) (courtesy of: Torgeir Bakken) (or try RunHide.vbs)
Code:
wscript BatchLauncher.vbs "Start_HSQLDB_Server.bat"

step 1g: And for a proper shutdown of your HSQL database files and server, may I (and glj) suggest:

Stop_HSQLDB_Server.bat
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice.org 3\basis\program\classes\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.util.ShutdownServer
exit

Note: This batch file cannot be used to shutdown a database/server with a password set. See these two (2) posts for a slightly modified batch file and information on shutting-down a password-protected database/server.

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

STEP 2: Setup OpenOffice for Java server access…

step 2a: Start Base and go to ‘Tools > Options > OpenOffice.org > Java’

step 2b: Java Options: make sure ‘Use a Java runtime environment’ is selected. Then select a JRE here, or ‘Add…’ a JRE as necessary using your Java.exe path.
Note: when running OpenOffice and HSQLDB Server on the same computer, if multiple JRE's are available for choosing, be careful here to select the same JRE installation you use to run the HSQLDB Server (above). Click on each one and note the path below it.

step 2c: Next click ‘Class Path’ then ‘Add Folder’. Navigate to \openoffice\basis\program\classes\

step 2d: Also click ‘Add Archive…’ Select hsqldb.jar

step 2e: Close (including OpenOffice Quick Starter) and restart Base to save these Java settings.

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

STEP 3: Connect Base to the Existing Database using the Wizard…

step 3a: Start Base, and select ‘Connect to an Existing Database’

step 3b: Select ‘JDBC’ and then select ‘Next >>’

step 3c: Fill-in ‘Datasource URL’ after [ jdbc: ] with:
hsqldb:hsql://localhost/;default_schema=true

step 3d: Fill-in ‘JDBC properties / driver class’ with:
org.hsqldb.jdbcDriver

The following screen shot is part of a series provided by sliderule, found at the bottom of this thread-page.
I would encourage you to scroll-down and work through this step visually.
Return here when complete...


graphic provided courtesy of sliderule

Note: the ' hsql: ' protocol and ' //localhost/ ' connection specified above provide access to the first database (without an alias name) served by the HSQLDB server on the same computer. To specify a particular database by filename or alias (rare), or to specify a secure protocol (recommended), or to access the database on another computer by designating the IP address or domain (required for LAN or Web access), see the documentation. For example, ' hsqldb:hsqls://192.168.1.210/;default_schema=true ' provides a secure connection (hsqls versus hsql) to another computer on the LAN (static-IP: 192.168.1.210), using the default secure port (554), providing access to the first database (zero length alias name string) served by the HSQLDB server.

Further Note: It is possible to provide internet access to a computer serving HSQLDB in ' webserver ' mode. Setting up a HSQLDB webserver involves your router, where you'll need to adjust your 'port-forwarding/gaming' and 'static-DHCP' settings to give your 'HSQLDB webserver' computer a port and static-IP address. And, you'll need a domain (such as a free DynDNS domain) so your router's external (dynamic) IP address can be located without renting a static IP address from your ISP. Client computers (those used to access the database) are not web-servers so no special setup is required for internet database access, except the proper 'Datasource URL' in OpenOffice (
hsqldb:https://my_server_domain.homeip.net/;default_schema=true). HSQLDB is inherently secure since it runs within a Java virtual machine. But if you prefer a battle-tested solution, see "Choose your own Back-End Database for ooBase" (below) for an alternative RDBMS. So as you can see, you can use your OpenOffice front-end to access an internet-connected HSQLDB (or other) database. That's because network/internet access is simply a function of the driver (JDBC, ODBC-bridge, etc.) which can be configured for many scenarios. And unless you're a .Net/Dreamweaver/Eclipse expert, it's going to be hard to beat the polish of forms and reports created with ooBase (or MS Access).

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

STEP 4: Test and Troubleshoot…

step 4a: With Base running, look at the bottom status windows. The status windows should reflect your driver string settings from step 3c above:
JDBC and hsqldb:hsql://localhost/;default_schema=true.
If your status window shows 'Embedded database and HSQL database engine' then you're NOT using the external HSQLDB Server with that particular .odb file.
After confirming your status (as client-server), a good test is to click on ‘Tables’ in the Base GUI. If you see the Table wizards and any tables you've created, without a popup error, then you're setup is working !!!
If you get an error it could be any number of things such as (I've encountered each of these):
(1) the HSQLDB server is not running or setup properly; check your machine’s running tasks and make sure you see Java.exe,
(2a) the 'Datasource URL' or the 'JDBC driver class' are not setup correctly; test the driver and connection separately using Base: Edit > Database > Properties… (2b) The 'JDBC driver class' syntax must be perfect, without so much as an empty space after the 'org.hsqldb.jdbcDriver' string; place your cursor at the end of the string (or highlight the entire string) and ensure there is no trailing blank space after the entry.
(3a) And if you're running Base with the HSQLDB server on the same computer, it could be that you have multiple copies of Java on your machine with Base trying to use one copy, and your HSQLDB server running in another copy. (3b) Check your HSQLDB Server startup string carefully, noting the path to each file. These should match the Java and hsqldb.jar paths setup in OpenOffice. Remember, alias database names complicate matters and should be left blank (especially for troubleshooting) both in the Base driver connection properties, and in the HSQLDB startup string.
(4)
OpenOfficePortable offers portable-launchers which contain code that "automatically handle paths to Java". These launchers currently destroy your Java Class path settings from Step 2 above. And, these launchers may override your intentions to use a specific copy of Java. It appears that only two folder locations are allowed for JavaPortable by OpenOfficePortable code: PortableApps\CommonFiles\Java -or- PortableApps\OpenOfficePortable\App\Java.
(5) Username or Password issues (sqltool.rc file setup or tutorial)
(6) Network sharing or access issues require advanced troubleshooting beyond the scope of this tutorial. Whenever possible, test your client-server components on one computer before splitting the client-server functions across a network.

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

With HSQLDB running and accessible in server mode…

Setting Up Users:
With a "back-end" server configuration HSQLDB supports: "Database security with passwords, user rights and roles with GRANT and REVOKE". You can use ooBase as the "front-end" to setup those multi-user features in the HSQLDB server but only through SQL commands...
See "Create additional Accounts" in the HSQLDB documentation.

NOTE: You may also be interested in a hack that enables Adding User Accounts and Passwords to a Base Database while running HSQLDB embedded.

Migrating an Embedded database:
[edit: the drag&drop of tables is disputed by sliderule in a separate post below]
[edit: 'sliderule' was onto something by cautioning the drag & drop of 'tables' in particular. Thank you 'glj' for uncovering the glitch in tables that have been drag&dropped from embedded to client-server instances of Base. The glitch causes the auto-increment setting (field property) used for primary key integer fields to be disabled. This breaks the intended function under Base, so new records cannot be added to the table(s) without manually incrementing the primary key field. Therefore, 'tables' should not be drag&dropped during migration.]


The following hybrid-drag & drop migration method is offered.

(1) Shutdown Base and the HSQLDB server.
(2) Now extract the four files that makeup the database file-set from the embedded-mode .odb file using a zip file utility such as 7-zip. The Base file is simply a zip archive file with an 'odb' file extension. The necessary file-set is located in the "database" folder found within the .odb (zip) file.
(3) Rename the four database files. The files are currently named: script, backup, properties and data. These filenames will become file-extensions upon renaming them using your database filename; you must use the same database filename you designate in the command line (batch file) used to start the HSQLDB server. For example, when using the database name "mydb" the extracted file-set names become: mydb.script, mydb.backup, mydb.properties and mydb.data. While the database is in use, another file named mydb.log (in this case) is created which holds all changes generated during the current database session.
Note: The .script, .data, and .log files are critical to HSQLDB database backup and recovery because they make-up your database and should be backed-up regularly. The .data file and associated .backup file will not exist if you use "memory tables" which is the default for HSQLDB. However, since OpenOffice creates "cached tables" by default when running HSQLDB-embedded, these extra files (.data and .backup) will exist, and will migrate to your client-server environment when using the zip-extraction technique in step (2) above. And unless you change a setting in the extracted .properties file, "cached" tables will continue to be created in your client-server setup. And finally, the .log file will not exist after a "proper" shutdown or "checkpoint" of the database (HSQLDB back-end components) which is accomplished by issuing (using: Tools > SQL... ) the SQL command: CHECKPOINT DEFRAG <or> SHUTDOWN COMPACT.
(4) Move (and replace as necessary) these files into your client-server database folder.
(5) Now start the HSQLDB server.
(6) Now start two separate instances of Base with your source .odb file (embedded database) in one instance, and your target .odb file (server connection database) open in the other. Your recently extracted tables should be available in the client-server (JDBC) instance of Base. If not, make sure you moved the files to the correct location after extraction and renaming.
(7) Now simply drag & drop everything except the 'tables' to the client-server (JDBC) instance of Base. This includes queries, forms and reports, while macros can be drag&dropped (or exported/imported as a "library") from their own 'Organize macros' interface within Base.
(8 ) Finally, setup your table relationships to match your embedded version -- and everything works!

Well, almost everything... The table's "Field Properties" will be locked for editing with the GUI. But note that Table creation, copying, and deletion is still available using the GUI / Wizards. This offers a workaround. For instance, simply drag-&-drop an existing table to the Table icon to create a renamed copy. Follow the Wizard ('Next' button) which allows you to make any changes to the table structure as desired. After careful verification of this new table, simply delete the original table. Then to rename the newly created table using the GUI, re-accomplish the drag-&-drop copy process while specifying the original table name, and simply press 'Create' to bypass the step-by-step Wizard. Finally, verify the new table and delete the intermediate table to complete the process. So the remaining GUI functions and associated workarounds may prove sufficient for most casual designers, but keep in mind that once you've added data to a table it's difficult to make changes that require data conversion OR that affect foreign key relationships. Of course, you can use SQL commands such as ALTER TABLE ALTER COLUMN to change column properties (dataTypes, etc.) as well. Or, experts might edit the .script database file manually.

Now when you save your client-server connected (.odb) file, you're actually saving everything except the tables (data) – the tables are saved automatically by the HSQLDB server on the server host-computer to the database in the folder of your choice. The (.odb) file constitutes your database "front-end" which you distribute to each client computer along with OpenOffice. The data folder on the HSQLDB server, along with Java and the server (hsqldb.jar file) applet, constitutes your database "back-end."

Divorcing MS Access:
Using ooBase as a front-end with HSQLDB in Server-mode is sufficient for many MS Access ports giving them new life as freely distributed, multi-platform solutions. For migration, (in MS Windows) Base can be used to connect to a 'Microsoft Access' or 'Microsoft Access 2007' database for limited read/write access. It's best to import those tables into HSQLDB (or other back-end RDBMS) in order to gain full control over table structures, etc. Once again, simply drag&drop the tables between multiple instances of Base.

But I should warn you, porting other elements from MS Access to ooBase is non-trivial. As of 2010, Base cannot import Access forms, reports, queries or macros. And I wouldn't hold my breath. The reality is Base is not Access, and Access is not Base. Access is the more mature product with more features, better ease-of-use, multiple database access, great documentation, vast support communities, and educational-courses offered at most community colleges. Base is maturing rapidly, but has limited documentation, single database access (per .odb file), and virtually no local community support. But Base enjoys wider platform support, unrivaled portability, free distribution, source code transparency for verifiable security, and some additional future-proofing as open source software. And the limitation of a "single data source per .odb file" is almost semantics when Base products are used by other OpenOffice components (including documents, spreadsheets, and slides) which allow fine-grain access to an unlimited number of data sources (see "Knowing Your Limits" below). But MS Access still wins with the ability to query multiple databases from a single query. Both MS Access and Base are flaky when running their embedded database engines, but they become quite trustworthy as front-ends in a client-server environment. This is due in large part to the quality and features of the RDBMS back-end, where the entire client-server application gains robustness, concurrency, security, and large-database stability. This is all quite seamless to the end-user.

Knowing your Limits:
An individual instance of ooBase (each .odb file) is limited to a single RDBMS connection. But other OpenOffice components (Calc, Writer, Impress) do not have this limitation. This offers a workaround. With these components, each form 'control' (field, list-box, table-grid, etc) can access a different data source (.odb file). Each data source (.odb file) in-turn provides seamless access to its respective database whether embedded or client-server. Use ' View > Toolbars > Form Controls ' OR ' Insert > Fields > Other > Database ' to insert form elements on your document, spreadsheet, or slide. On the other hand, Base forms and switchboard extension provide a rather clean user-interface compared with a Writer document or Calc spreadsheet. So there may be tradeoffs. But I also want to point out that an Impress slideshow has amazing potential as perhaps the cleanest, and most elegant front-end available through OpenOffice. But alas, Impress has a major bug that garbles form controls at runtime rendering this approach currently useless (as of version 3.1.1).

Also note that HSQLDB (RDBMS back-end) has a relatively limited feature-set and exhibits poor scalability [EDIT: these issues are addressed with version HSQLDB 2.x]. For instance, the non-concurrent table-access model can quickly impact performance as you add high-transaction users, depending on your application. If it's any comfort, the Jet RDBMS in MS Access suffers from similar feature limitations, and exhibits serious scaling issues -- Microsoft suggests a very arbitrary 10 concurrent-user limit. Overall, HSQLDB is a generous candidate for MS Access ports and should scale more gracefully than Jet/Access, at least based upon a multi-user benchmark (see BenchB: Transactions Client-Server). This benchmark simulates hundreds of transactions per second, and suggests that HSQLDB scales to 10 high-data users neck-and-neck with MySQL/PostgreSQL. Theoretically, this could mean support for dozens of low-transaction users. But don't get the wrong idea, HSQLDB might support 10+ data-intensive connections (your mileage will vary), but it would grind to a halt in "high-concurrency" production environments where MySQL/PostgreSQL thrive.

See: Some RDBMS Pro's and Con's related to Base
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Tue Jun 26, 2012 12:27 pm; edited 246 times in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Jan 10, 2010 9:48 pm    Post subject: Reply with quote

dacm:

Great start, in explaining a technique to 'migrate' from HSQL Embedded database . . . the 'default' built-in database engine . . . to the more 'robust' HSQL Java server ( and allowing multi-user capabilites ).

You said above:

dacm wrote:
Once HSQLDB Server is running, I've found it takes less than 5 minutes to convert an embedded database to a server-based database. Simply drag and drop everything (tables, queries, forms, reports, macros) into another, clean instance of ooBase running HSQLDB in server mode -- and everything works!


In my experience, it is not quite that easy. I have been using both, the Embedded Database, and, HSQL Java Server ( the Beta . . . HSQL 2.0 ). A couple of things, since you have to tell OpenOffice about the use of the two Java file . . . in the Tools -> Options part . . .the location of the HSQL Lib directory, and, the specific hsqldb.jar file . . . you canNOT open BOTH your prior Embedded .odb file, and, the new HSQL Server file concurrently . . . at least not to read the tables . . . therefore . . . you canNOT merely drag and drop the TABLES from the old to the new. Yes, you can drag and drop Queries, Forms and Reports, but, to the best of my knowledge / experience, not the table data.

Of course, one could unzip the .odb Embedded file, and, strip out the SCRIPT file, OR, issue the SCRIPT 'C:\MyScriptFile.SQL' command to create the INSERT statements, OR, create .CSV files with the command(s) . . .

Code:
SELECT * INTO FILE "MyTable_CSV" FROM "MyTable"

for each table, and, populate the NEW tables using HSQL tools.

I hope this makes sense, and, gracias for you efforts.

Sliderule
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Jan 11, 2010 4:43 pm    Post subject: Reply with quote

sliderule,

Thank you for the feedback and let me tell you that you've taught me so much over the past few months as I've read the forum!

I understand your point concerning two competing instances of Base: one accessing an embedded HSQLDB (.odb) database, and another instance of Base accessing a client-server HSQLDB -- simultaneously. And I understand your reasoning which is centered around the Class Path/Archive(Jar) setup in OpenOffice.

Well, my drag&drop suggestion evidently wasn't a "simple" matter based upon your feedback. I'll have to re-trace my steps and try to figure out if I'm doing something unique in order to get this working. I actually perform a variation of this step a few times a week as I test my ooBasic macros in the client-server mode for compatibility. However, I start with an existing server-mode HSQLDB and simply delete everything, save the empty database, and then begin the drag&drop process from my embedded HSQLDB instance of Base. The Tables & Data transfer using the "create" button just like all other components...except the Macros which allow drag&drop in their own "Organize Macros" interface. And for what it's worth, my embedded HSQLDB instance of ooBase reflects the Java 1.6.0_17 and Class Path/Archive 'hsqldb.jar" paths. I would imagine that the embedded-mode instance is simply ignoring the Class Path settings, but I have to do more testing to say for sure.

I've further defined my development environment below...but I don't think that matters here. It must have something to do with my steps (such as when I start the Java server relative to starting Base?) but I'll have to retrace and see if I can break it before I can begin to explain the proper steps in the How-To guide and/or add the SQL equivalents as you've suggested..

OpenOffice 3.1.1 Portable (from PortableApps.com)
Java 1.6.0_17 (using JavaPortable.ini from PortableApps.com)
HSQLDB 1.8.1.1
Windows 7 Home Premium 64-bit
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jan 11, 2010 6:23 pm    Post subject: Reply with quote

dacm:

Again, thanks so very much for your efforts.

Just as an FYI ( and for anyone else reading this ) . . . as of OpenOffice 3.1 and 3.1.1 . . . when a user drags and drops a Query from one database to another, the NAME of the Query is NOT 'carried' over, meaning, that the user will have to re-enter ( retype ) the OLD name to match.

However, I understand that an 'enhancement' with OpenOffice 3.2 ( to be released, to the best of my knowledge 2010-01 . . . AKA January 2010 ) will resolve the above situation.

Also, HSQL 2.0, which as of today, is still in beta ( albeit final stages of beta until it is 'officially' released ) does include date / time arithmetic . . . using a DATEADD function, and, INTERVAL arithmetic. I understand, OpenOffice hopes to include this update with OpenOffice 3.3 ( NOT the next release of OpenOffice in January 2010 ). Just thought I would mention this.

Again,dacm, please, keep up the great work.

Sldierule
Back to top
View user's profile Send private message
Shammat
General User
General User


Joined: 22 Jul 2003
Posts: 25

PostPosted: Thu Jan 14, 2010 3:18 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

dacm wrote:

PostgreSQL
- often slowest of those RDBMS's profiled here, but speed is generally indirectly-proportional to full and true ACID compliance, to reliability, and to robustness
That's a myth that has been carried over from over 15 years ago.
It might have been true for versions from the last century, but current versions (8.3 or 8.4) are equally fast if not faster than MySQL, Oracle or DB2

Compared to an in-memory database (h2, HSQLDB) any full-blown server will be slower.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Jan 14, 2010 10:45 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

Shammat wrote:

That's a myth that has been carried over from over 15 years ago.
It might have been true for versions from the last century, but current versions (8.3 or 8.4) are equally fast if not faster than MySQL, Oracle or DB2

Compared to an in-memory database (h2, HSQLDB) any full-blown server will be slower.

Yes, perhaps I should delete [edit: decided to delete it] that statement or qualitfy the term "slowest" because the margins are relatively small. For comparison, I was specifically referencing a fairly recent benchmark of PostgreSQL 8.3.7 which is URL'd in the post (http://www.h2database.com/html/performance.html). I was most interested in the "Client-Server" table, and specifically "BenchB" which is multi-user. There, PostgreSQL appears slightly slower without using indexes. BenchB simulates only 10-users so the tide would turn dramatically against H2 / HSQLDB as the user-count grows. I was also considering recent internet discussions which challenged Thomas Mueller's (Open Source) benchmarking with a proprietary benchmark where PostgreSQL beat MySQL and H2 (client-server) speed through indexing. There, I presume all were using indexing for apples-to-apples comparison.

I wasn't trying to shed a bad light on PostgreSQL, but for balance (two Con's among Pro/Con's) I was simply considering everything I've read in recent years. I have no first-hand experience with MySQL or PostgreSQL. I actually skipped Derby, NOT for speed issues (alone), but because it didn't bring anything unique to the OpenOffice discussion like HSQLDB's integration, H2's multi-mode, MySQL's popularity, or PostgreSQL's top-of-the-pack attributes.

Thank you for that correction and clarification. Do you have a "Con" for PostgreSQL relative to MySQL?


Last edited by dacm on Sat Jan 16, 2010 10:22 pm; edited 1 time in total
Back to top
View user's profile Send private message
Shammat
General User
General User


Joined: 22 Jul 2003
Posts: 25

PostPosted: Fri Jan 15, 2010 3:28 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

[quote="dacm"]
Shammat wrote:
Do you have a "Con" for PostgreSQL relative to MySQL?
More than just one.

First there is the license which affects anyone building applications based on it. MySQL is only free if you license your software under the GPL as well. Even linking against the libraries requires either GPL license for your project or buying a commercial licenses.
Not a problem for someone "only" using it though.

I think the biggest difference is the development strategy of the two teams. Whereas MySQL first tries to make things fast and only later tries to make it reliable and robust, Postgres' most important rule is to make things robust first. Once it's working stable and all potential problems that could lead to a loss of data are removed, then it's made fast. This attitude is probably what spread the myth about Postgres being slow (and the 7.x line admittedly was slow with regards to read performance)

Postgres deals with a high load of concurrent reads and writes a lot better than MySQL. MySQL is optimized for small, simple read-only queries (which are typically found in web applications). But the MySQL optimizer quickly chokes on more complicated statements and produces very bad execution plans.
PostgreSQL is a lot better when it comes to optimizing non-trivial statements.

There was an interesting benchmark comparing the two system. The main goal was to benchmark different hardware architectures, but the database figures are equally interesting:
http://tweakers.net/reviews/649/1/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-1.html

What annoys me most about MySQL are a lot of little things (no check constraints for example) and limits. You want a full text search? Sure, but you can't use it with a transactional table or define foreign keys on them. Because fulltext search is only available with MyISAM not innoDB. Same goes for spatial data. Yes it's available. No it can't be used in a transactional table.

PostgreSQL is a lot closer to the ANSI Standard than MySQL (quoting object names is totally "broken" in MySQL). The don't even support the ANSI operator for string concatenation (instead || is an OR operator!)

The behaviour of one MySQL installation might be completely different to another depending on things like strict_mode or ansi_mode or even the underlying file system when it comes to case sensitivity of object names.

PostgreSQL support for stored procedures and triggers are far superiour to MySQL's

And last but not least the overall (SQL) features are a lot more mature in PostgreSQL. I'm thinking about recursive queries, windowing functions, XML support, data type extensions, just to name a few.

Don't get me wrong: MySQL is not a bad database. It's good if you don't care for your data (because InnoDB is not a very safe storage engine) and when you have mostly simple read-only queries.

You might also be interested in this comparison:
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009

Yes, it is written by someone from the Postgres team, but I think it's a pretty fair comparison.

For me as a developer the license is the biggest drawback. With MySQL I always need to think about if my usage already requires a commercial license or not. With PostgreSQL I simply don't care
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Jan 16, 2010 1:21 am    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

Shammat wrote:
For me as a developer the license is the biggest drawback. With MySQL I always need to think about if my usage already requires a commercial license or not. With PostgreSQL I simply don't care

Okay Shammat, those are all good points. I especially think this licensing bullet is relative to ooBase developers and I've updated the Pro/Con lists accordingly.

Otherwise, thank you for the inputs for the benefit of all readers. I love your keen expertise and enthusiasm for PostgreSQL. All of my research points in that same direction overall, but the Pro/Con list is an ascending progression towards larger [projects], more scalable, more robust, and feature rich RDBMS's -- while attempting to profile those RDBMS's that bring something unique to the table.

Legacy reputations aside, my research indicates that MySQL and PostgreSQL are are both maturing towards the same goals of speed, standards, scalability (including multi-proc), reliability, features and ease. Every version brings them closer to these goals and more difficult to distinguish (at least on on paper).


Last edited by dacm on Sat Jan 16, 2010 12:38 pm; edited 1 time in total
Back to top
View user's profile Send private message
Shammat
General User
General User


Joined: 22 Jul 2003
Posts: 25

PostPosted: Sat Jan 16, 2010 3:06 am    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

dacm wrote:
Every version brings them closer to these goals and more difficult to distinguish (at least on on paper).
Very true.
But when working with both, Postgres just "feels" better Wink

It's the numerous little things that you only discover while working with a product that sum up to a big difference...
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Thu Feb 25, 2010 2:34 pm    Post subject: How to: Migrate Base Projects to Multi-User Reply with quote

When I ran the batch file (adjusted for paths as they are on my computer) as given in step 1e, the paths were set in the Environment OK, but the Java command failed with the following output:

Exception in thread "main" java.lang.NoClassDefFoundError: org/hsqldb/Server
Caused by: java.lang.ClassNotFoundException: org.hsqldb.Server
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher%AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
Could not find the main class: org.hsqldb.Server. Program will exit.

Does anyone know how to correct this problem?

Also, after doing step 2, I got to step 3a only to find that Connection Type is grayed out. Perhaps that is related to the failure of the above batch process?
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Feb 25, 2010 3:05 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

glj wrote:
Caused by: java.lang.ClassNotFoundException: org.hsqldb.Server
Could not find the main class: org.hsqldb.Server. Program will exit.

Well it appears that Java is starting normally, but there is something wrong with the path to hsqldb.jar

I just duplicated your error above by changing my own path to hsqldb.jar in the batch file by one extra character.

All I can suggest is a careful examination of the jar path.

glj wrote:
Also, after doing step 2, I got to step 3a ... Perhaps that is related to the failure of the above batch process?

Correct.
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Thu Feb 25, 2010 10:34 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

Quote:
Well it appears that Java is starting normally, but there is something wrong with the path to hsqldb.jar
You were right. I copied and pasted the code as found in your first message. That code has a space between the = and the path for hsqldb.jar. Removing that space allowed the hsql server to start. But there are still problems.

Quote:
If you don’t have an HSQL database file-folder at this point, an empty one will be created with the filename you specify, located in the same folder as this batch file (or wherever the startup command is executed).
I see that four files are created - library.script, library.properties, library.log, and library.lck - as the server starts, but no empty folder is created. I'm not sure why that failed.

I completed Step 2 without any problems. I restarted Base, but wasn't sure whether I was supposed to open the database I am trying to convert from embedded to client/server or whether I was supposed to create a new database. So I tried both ways. Unfortunately, Step 3a still has Connection Type grayed out so I can't get any farther through the procedure. What should I do to continue with Step 3?
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Feb 25, 2010 11:08 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

glj wrote:
That code has a space between the = and the path for hsqldb.jar. Removing that space allowed the hsql server to start.

Great!
And I fixed the tutorial above. Good catch!

Quote:
I see that four files are created - library.script, library.properties, library.log, and library.lck - as the server starts, but no empty folder is created. I'm not sure why that failed.


No problems there. You've selected the database name 'library.' I was struggling for the wording to describe the set of database files and location at the same time without going into detail in the above quote. I should re-word that. The set of databse files are created in whatever folder location is used to issue the hsqldb startup command line. No folder is created -- I really meant file-set rather than file-folder.

Quote:
I completed Step 2 without any problems. I restarted Base, but wasn't sure whether I was supposed to open the database I am trying to convert from embedded to client/server or whether I was supposed to create a new database. So I tried both ways. Unfortunately, Step 3a still has Connection Type grayed out so I can't get any farther through the procedure. What should I do to continue with Step 3?

So when you create a new database, do you see JDBC in the bottom status window? Make sure you're not creating another embedded database (.odb file).

Check the troubleshooting in step 4, because I think it will require OpenOfficePortable to do the drag&drop migration from embedded mode (.odb) to JDBC server mode (.odb). There's no reason to use Base 3.2 because Forms do not function properly due to at least two bugs. Role back to 3.1.1 until Base 3.2.1 arrives.


Last edited by dacm on Sun Feb 28, 2010 8:14 pm; edited 3 times in total
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Fri Feb 26, 2010 8:09 am    Post subject: Reply with quote

Apparently, I'm getting in over my head here. I have never tried to convert an embedded database to client/server before so I thought I would be able to do that using this procedure.

Quote:
Role back to 3.1.1 and use OpenOfficePortable for the migration.
I'm using 3.0.1 Is that a problem? Obviously, I haven't even got close to needing the Portable version to do the drag and drop. I'm still trying to get Step 3a to work.

Quote:
So when you create a new database, do you see JDBC in the bottom status window?
With the HSQL server running and having finished Step 2, I restarted Base. In the Database Wizard step 1 I assumed I was supposed to select the third option "Connect to an existing database" and leave it set at JDBC. The next window "Set up a connection to a JDBC database" (step 2 of the Wizard) has two empty boxes that I had to fill before I could proceed, "Datasource URL jdbc:" and "JDBC driver class". Were these supposed to be empty (remember, I did steps 2a through 2e)? In the Datasource box am I supposed to type a DOS path to the files that the batch file created? I have no idea what I'm supposed to type in the "JDBC driver class" box.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Feb 26, 2010 9:44 am    Post subject: Reply with quote

Forgive me for adding here, but, I am just trying to help.

glj, based on your comment:
glj wrote:
With the HSQL server running and having finished Step 2, I restarted Base. In the Database Wizard step 1 I assumed I was supposed to select the third option "Connect to an existing database" and leave it set at JDBC. The next window "Set up a connection to a JDBC database" (step 2 of the Wizard) has two empty boxes that I had to fill before I could proceed, "Datasource URL jdbc:" and "JDBC driver class". Were these supposed to be empty (remember, I did steps 2a through 2e)? In the Datasource box am I supposed to type a DOS path to the files that the batch file created? I have no idea what I'm supposed to type in the "JDBC driver class" box.

I hope the following graphics will help.

VERY IMPORTANT NOTE :

The instructions below apply to OpenOffice 3.2, and prior versions as well. OpenOffice future versions ( that is, when HSQL 2.0 is included ) will require slightly different instructions. Smile

Open your OpenOffice application, choose to create a new Database



From the Database Wizard:



On the next screen, fill in the following items:

Code:
hsqldb:hsql://localhost/;default_schema=true

org.hsqldb.jdbcDriver




Press the Test class button



Press the OK button

Press the Next>> button



Press the Test connection button



Press the OK button

Press the Next>> button



Press the Finish>> button

I hope this helps, please be sure to let me / us know. Smile

Sliderule

Edit: Thanks to glj for pointing out some incorrect items I 'originally' posted above. One graphic and some words have been modified, to reflect the items glj found. Again, thanks for your efforts and patience.


Last edited by Sliderule on Sat Feb 27, 2010 7:18 pm; edited 3 times in total
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2, 3, 4, 5  Next
Page 1 of 5

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group