|
|
|
|
|
| Last published: 02 February 2004 | Doc for 0.41.2 |
|
Project Documentation
Search
|
User ManualTable of Contents Importing / Exporting a Project Selecting Tables to Process and Table Mapping Selecting Columns to Process, Column Mapping and Record Filtering Executing Operations (Plugins) Configuring opendbcopy (email notification etc.) Executing opendbcopy from the command line (batch mode) Overview (back to top)opendbcopy must be understood as a framework to configure and read database meta data. The complete configuration can be saved as xml file for simple re-use. Executing an operation such as migrating data from one database into another is done using plugins. Opendbcopy comes along with a set of standard plugins, such as copying records of tables from one database into another, creating SQL Insert scripts, creating flat files with data or creating statistics.
Opendbcopy provides a wizard to configure a database project.
Executing a plugin can be done either from the Graphical User Interface or directly via command line providing an xml file with the required configuration.
Plugin behaviour can be modified using the plugin.xml file in the plugins directory, see 'Plugins' for details.
Opendbcopy can be used to work with one database or two databases. Choose the database mode required on the first screen of the wizard, see Connection Details for further information.
If problems occur please have a look at the Frequently Asked Questions (FAQ), User Mailing List or contact me by email ( smith@puzzle.ch ) providing application and execution log from the log directory (reset after a new restart of opendbcopy).
Software Requirements (back to top)opendbcopy requires a Java Runtime Environment. If not yet installed, please download a Java Runtime Environment or Developer Kit 1.3 or higher from http://java.sun.com .
Opendbcopy has been tested using J2SDK 1.3 and 1.4.
The full installation of opendbcopy including the complete off line documentation requires about 7MB of disk space.
Installing opendbcopy (back to top)opendbcopy is distributed using a set-up wizard (IzPack Installer ? platform independent). Once successfully downloaded the opendbcopy-xx-install.jar can be started by entering the following command in a shell (Command Prompt for Windows users)
Go to the downloaded file's directory.
Enter the following command: java -jar opendbcopy-xx.install.jar (replace xx with current version)
If Java can be found a dialogue pops up asking to select a preferred language for the set-up wizard. Choose between English, German or French. Please note that opendbcopy is distributed in English only.
On the following screens read the README text and License Agreement. Then choose where a location to install opendbcopy.
After this select the packages to install. To unpack the source code and API documentation too, select these packages as well. These packages may be of interest to developers.
After installation the set-up wizard can create desktop shortcuts if opendbcopy is running on a Windows platform.
If you have modified SQLDrivers.xml and install a newer version of opendbcopy please make a safety copy of SQLDrivers.xml and overwrite the new standard SQLDrivers.xml file to re-use your settings after installation.
Before using opendbcopy (back to top)Make sure the required database drivers are set in the CLASSPATH environment variable or were copied into the lib directory of opendbcopy. When copying the database drivers into the lib directory of opendbcopy, one does not need to add those to the CLASSPATH any more , because those drivers are dynamically loaded at start-up of opendbcopy.
Starting opendbcopy (back to top)When running opendbcopy on a Windows platform one can either start open dbcopy using the shortcut 'opendbcopy launcher' on the desktop or click on 'opendbcopy launcher' within the program group opendbcopy under Start Menu, Programs.
Windows users can also start opendbcopy by calling the start.bat from a command line or Windows Explorer.
Linux, Unix or Mac users please call the start.sh script to launch opendbcopy.
Opendbcopy is launched via a shell and then opens a graphical user interface. Do not close the shell window if you do not want to close opendbcopy too. The shell window is automatically closed when closing opendbcopy via menu 'Project, Exit'.
Importing / Exporting a Project (back to top)At any time during configuration of a database migration project one can save its configuration by clicking on 'Project, Export Project ...' and specify a file name and location.
At any time one can also import an existing project. Therefore click on 'Project, Import Project ...'.
Please note that the file dialogue appearing only shows XML files.
If you feel comfortable with the XML project configuration you can also manually manipulate a project file or reopen it in opendbcopy and modify it using the graphical user interface. See the developer's section for details about the project XML file.
Connection Details (back to top)
When launching opendbcopy, the first step to take is to set-up a database connection, or two connections, one to the source and one to the destination database if working in dual database mode. This is where most problems may occur, for example when trying to connect to a database which is located behind a firewall and does not allow to connect directly, missing JDBC drivers, etc. (see FAQ for details)
The following screen allows to configure databas e connections.
Choosing a database mode (back to top)By default dual database mode is selected. This requires successful configuration and test to both database connections, source and destination, before one can continue with the wizard using the next button or by clicking on the Model(s) tab.
To work in single database mode select the single database check box.
Please note that the current release allows to change from dual database mode into single, but not the other way round without forcing an error. This 'bug' will be fixed if required.
Specifying a database connection (back to top)The source and destination connection details section allow to specify details to source and destination database.
The driver name fields allow to select a driver from the pre-configured driver settings file (see configuring drivers for details).
If one selects a driver from the driver name select box, the Driver Class and URL text fields are automatically filled. Please note that the URL must be further configured, e.g. replace < server > by a server URL, < dbname > by a database name etc.
In the user field enter the user which has the required rights to execute the plugins you want to use. To read database meta data no special user rights are required.
The password field is hidden, but when saving a project as xml file the password is saved as plain text!
Once done, click on 'Apply and Test'. When receiving errors please have a look at them and correct appropriately.
When working in dual database mode, configure the destination database connection details too.
The wizard let's you continue once the database connection details have been applied and tested successfully.
Please note that it is up to you to ensure that you always select the original database in the source section and the target database in the destination section. Certain plugins only allow to execute operations (plugins) on the destination database.
Database Model Details (back to top)The database model details screen lets one specify the catalogue and/or schema to work with. Please note that not every database works with catalogues and schemas. Some work with catalogues only, others with schemas only.
Opendbcopy has already read the available catalogue and/or schema names given the connection details on the previous screen. If possible, a catalogue or schema have already been pre-selected given the database url (reading the catalogue) or user name (schema name).
When working with Microsoft Access databases please be aware to select the catalogue manually from the list of catalogues provided.
When working with PostgreSQL please be aware that the current implementation of opendbcopy does not read tables using qualified table names, e.g. catalogue_name.table_name. It reads and accesses tables only by table name.
Working in single database mode, one can only specify source model details. To capture (read) database meta data click on 'Capture Source Model'. Please be patient as this process may take a while!
Further model details (back to top)If you only want to capture certain tables you may use SQL wild cards (% for anything, _ for one character) to reduce the number of tables to capture. E.g. To capture tables beginning with TX, enter TX%.
Reading Primary, Foreign Keys and Indexes (back to top)When copying data on-line it is important that data is inserted in th e right order. For example having a parent and child table which are linked by a foreign key constraint, it is a must that data of the parent table is inserted before the records are inserted into the child table. Opendbcopy is able to read and manage foreign key constraints when selecting the 'Read Foreign Keys' check box.
Reading Primary Keys and Indexes is only required when using a plugin that creates or modifies table structures which is currently not available by the standard plugins provided.
Please note that when one does not require reading Primary Keys, Foreign Keys or Indexes to uncheck the appropriate check boxes to save time capturing database models.
Selecting Tables to Process and Table Mapping (back to top)Single Database Mode (back to top)In single database mode, one can select which tables / views to process. Please note that certain plugins (e.g. Statistic plugins) do not bother which tables are selected to process, others, such as creating dump files or SQL scripts only process the tables / views which are selected.
In single database mode the screen looks like
Using the button 'Deselect All' or 'Select All' once all tables are deselected lets one uncheck or check all tables / views.
Please note that only tables / views which are selected can be further configured on the following Process Columns tab.
Dual Database Mode (back to top)If working in dual database mode the screen looks like
In the leftmost column all tables and / or views are listed which were read when capturing the source database model. The second column shows available tables / views from the captured destination model. By default opendbcopy tries to automatically map a destination table / view to a given source table / view. Thereby it does not matter if the source table / view is written using small or capital letters.
Fields which do not contain any entry in the second column, the mapping column, could not be automatically mapped. To map a certain source table / view to a destination table / view, click on the appropriate field and select a destination table / view from the list provided.
Tables / Views, which were automatically mapped, can also be changed by clicking on the appropriate field and by choosing another destination table / view.
The last column specifies if the mapped source / destination table shall be processed. One can 'Deselect All' tables / views or 'Select All' once all rows have been deselected.
Once completed proceed to the next tab.
Selecting Columns to Process, Column Mapping and Record Filtering (back to top)Single Database Mode (back to top)In single database mode the screen looks like
On the left hand side a tree element allows to navigate over tables / views which have been set to process on the previous tab.
When clicking on a table / view, a table structure showing all source columns of the selected table / view appear on the right hand side of the screen.
If not all columns shall be processed uncheck appropriate column(s).
Dual Database Mode (back to top)In dual database mode the screen looks like
Click on a table / view in the tree navigation on the left hand side of the screen to see available source columns of selected table / view. Columns of the destination table / view, which could be mapped automatically (case insensitive), are listed in the second column next to the appropriate source column and are automatically checked to be processed.
Again one can change already mapped destination columns by clicking on the appropriate field.
If no destination column could be associated, please select one from the appropriate list when clicking on a field in the destination column, if required.
Setting Table Filters (back to top)By default all records of a source table / view are being processed once one executes an operation (plugin). To filter records specify an SQL compliant WHERE statement in the appropriate table filter text field.
E.g. To filter records which have an internal id (field IPERSON_NR) greater than 200000, enter
IPERSON_NR > 200000
and click on Apply and Test
If the test is successful, the number of records which will be processed is shown next to the table filter buttons. If an error occurs there must be an error in the filter specification.
Again a filter can be set as 'Process' or not. If applying and testing were successful, the check box is automatically selected.
The following SQL filter commands can be used:
Logical Operators: > , < , =, AND, OR
Ordering Operators: ORDER BY (to reorder the records in the destination table / view)
e.g.
IPERSON_NR > 200000 AND IPERSON_NR < 300000 ORDER BY IPERSON_NR
Setting global String filters (back to top)Currently opendbcopy provides the following string filters:
Trim Strings (back to top)Remove spaces at the beginning and at the end of a string (any string compatible database column type such as CHAR, VARCHAR, TEXT, etc.)
Remove multiple intermediate white spaces (back to top)e.g. Having a string field in a database column, e.g. a street number followed by street name and there is more than one white space between the two 'texts' in the same database field, all white spaces except one are removed.
Set NULL if String is empty (if column is NULLABLE) (back to top)If a table provides string compatible fields which are nullable (containing nothing than SQL Null) and the source field once trimmed has length = 0 (an empty string), this filter inserts SQL Null into the destination field instead of an empty string.
Executing Operations (Plugins) (back to top)The last tab is for the selection of a plugin to execute.
The screen looks like
At the top the plugins available for the current database mode selected (single or dual) are listed.
Select the plugin to execute and click on 'Execute'.
Operation progress is shown at the bottom. An operation can be cancelled at any time by clicking on 'Cancel'.
A commit to the database is done once all records of a given table / view are copied.
Progress information is displayed in the 'Execution Log' section and also into the execution log file in the log directory of opendbcopy. Once restarting opendbcopy all log files are reset unless you change the configuration of opendbcopy, see Configuring opendbcopy for details.
Please note that not all databases support roll-back functionality. E.g. using the copy plugin with a MySQL database, not supporting roll-back operations, records already written to the destination table(s) / view(s) cannot be undone. The delete plugin for example allows to delete all destination tables / views which are selected for processing.
For a list of available plugins and configuration please see the plugins section.
Configuring Drivers (back to top)Using an external XML file to configure driver pre-selections lets one change default database driver classes, names and connection URLs easily.
If a database driver is not listed within 'Driver Name' selection one can easily append a further entry into the SQLDrivers.xml file in the conf directory of opendbcopy.
An entry consists of the following elements:
When appending a new driver please return a short feedback to the developer(s) of opendbcopy.
If you have modified SQLDrivers.xml and install a newer version of opendbcopy please make a safety copy of SQLDrivers.xml and overwrite the new standard SQLDrivers.xml after installation.
Configuring opendbcopy (email notification etc.) (back to top)The following options can be configured for opendbcopy
Email notification of plugin execution status (back to top)To turn on email notification for plugin execution status (simple information or errors) open log4j.properties file located in the conf directory of opendbcopy Change log4j.logger.opendbcopy.plugin =INFO, STDOUT, EXECFILE into log4j.logger.opendbcopy.plugin =INFO, STDOUT, EXECFILE, MAIL Then change the following line with appropriate information log4j.appender.MAIL.layout.ConversionPattern =MAIL_SERVER & SENDER_EMAIL & RECIPIENT_EMAIL & %d & %5p & %c & (%F Line:%L) & %m%n Replace MAIL_SERVER with your mail server SENDER_EMAIL with the email address that shall appear as sender RECIPIENT_EMAIL with the email address of the email recipient Save the changes and restart opendbcopy. Now all information generated by plugins is automatically sent to the email recipient as separate email showing the complete message and date / time of creation. XML Encoding of project XML files (back to top)To change XML encoding of project files change the XML encoding in opendbcopy.properties in the directory conf, save the changes and restart opendbcopy.
Log Level (back to top)Especially when developing new plugins further log information can be helpful. Each package of opendbcopy is configured with a log level. Change the appropriate package log level. Available log levels are DEBUG, INFO, WARN, ERROR, FATAL.
Look and Feel (back to top)By default the look and feel is the operating system's look and feel. When removing look_and_feel =system in opendb copy.properties (conf directory) or changing its value into anything else, the Java look and feel is selected. Future releases may allow to select between different layouts within the graphical user interface, if required.
Locations to log, plugins and SQL drivers configuration (back to top)opendbcopy.properties (conf directory) allows to specify where the log, plugins and SQLDrivers configurations are located. Change appropriate if required. One may use relative or absolute path names.
Executing opendbcopy from the command line (batch mode) (back to top)Once a database migration project has been set up using the graphical user interface and saved as XML file, opendbcopy can be launched via command line without showing any graphical user interface and directly execute a selected operation (plugin).
Currently only execution of one plugin at the time is supported. Future releases may support execution of several plugins, if required.
Starting opendbcopy from the command line accepts the following syntax:
Minimum argument is:
file=<project_path_filename>
Non mandatory arguments:
operation=<operation> runlevel=<runlevel>
LEGEND file: your opendbcopy project file to open (see different examples below) operation: 'execute' to immediately execute the project's plugin runlevel: '1' for SHELL only, '5' for GUI (Graphical User Interface)
Example call 1: (no arguments - > create a new project)
> java opendbcopy.controller.MainController
Example call 2: (import project from xml file - > continue your work)
> java opendbcopy.controller.MainController file=c:/opendbcopy/myproject.xml
Example call 3: (import project xml from file and execute contained plugin in GUI mode)
> java opendbcopy.controller.MainController file=/tmp/projects/myproject.xml operation=execute
Example call 4: (import project xml from file and execute contained plugin in SHELL mode)
> java opendbcopy.controller.MainController file=myproject.xml operation=execute runlevel=1 |
|
|
| © 2004, Puzzle ITC GmbH |
Squirrel-SQL
