Documentation DBCopy Eclipse Plugin
by Oliver Gries
Version: $Revision: 1.4 $ [$Date: 2003/08/10 14:32:16 $]
There are three simple steps to get the plugin to work.
- 1. Install it
- 2. Configure it
- 3. Use it
The installation of this plugin is quite simple. Just unzip the content into the $ECLIPSE_HOME/plugins directory preserving the directory structure provided.
After unzipped the directory structure should look like:
Restart Eclipse to get the plugin activated.
There are two steps needed to configure the plugin.
- [JDBC Driver] - Setup the JDBC driver and additional libraries
- [DB Locations] - Configure the connections
JDBC Driver Setup
Before you can use a driver you need to attach the library (jar or zip) of this driver to the classpath. This can be done dynamicly through the JDBC Driver Preferences page.
Specify a name and the location of the library.
With the Load Order buttons the attachment order to the class loader can be affected. This is importent because dependend libraries of JDBC driver need to be loaded before the driver itself. (e.g. The antlr.jar for QED needs to have a lower order position than the qed.jar driver itself)
Here you can configure your database connections. Each setup stands for one database location you can use to copy to/from.
|Name||An unique name for this connection|
|Driver Class||The JDBC driver class e.g. org.gjt.mm.mysql.Driver|
|URL||The full JDBC URL to connect to the database. This is vendor specific so have a look at their documentation. For MySQL it might look like jdbc:mysql://localhost:3306/test whereby test is the database to connect to.|
|Database Name||Infact this is the alias which gets attached in front of any table in the SQL statement. If you don't need this you can leave this empty (from version 0.1.1 on, version 0.1.0 needs an entry here) and it's up to the JDBC driver to intepret it.|
|User ID||The userid for this connection|
|Password||The password for this connection|
|Max Columns||The max number of columns the driver supports. If you are unsure, use 255.|
|Standard Location||This connection gets first selected once you open the SQL Editor provided by this plugin.|
The only thing you can configure at the moment is the file extension DBCopy will use to create new SQL command files.
That's all. Of course there might be problems by using certain drivers, but this problems should be not different than normal developer praxis by using the JDBC driver in any application.
Wizards, Editors and Views
DB Copy provides following tools:
- A copy wizard
- A SQL editor and result view
- Ant tasks [from version 0.2.0 on]
DB Copy Wizard
Start this wizard thoughout the New action of eclipse (either [File - New - Other] or by selecting a project [New - Other]).
On the first page select the source and target db location (which you setup during the configuration process). Use Retrieve Tables to list the tables of the source db location. Choose the table(s) you want to copy. If you want the table to get automatically in your target db location created just switch the checkbox on. Press next if you want to proceed.
Note that if you want to copy the Foreign Key Constraints as well the copy order of your tables matters.
If you just want a one to one copy you will not need to provided additional mapping parameters. However with this option you can easily migrate data to other tables.
Saying you have an application which use a certain table structure. The new version of this application uses a different persistence mechanism to access non transient data. (EJB-CMP -> JDO, RO Mapping Tools, ...). Not every time it will be possible to preserve the old table structure. On this page is possible to map columns from the source table to columns of the target table. You can exclude columns to copy as well.
How to map?
You can only map if the Create Tables feature is switched off.
- Retrieve Target Tables
- Retrieve Columns for Source and Target Tables
- Map Columns to each other either by selecting the appropriate lines in the lists and hit the button between those lists or by simply using drag and drop. You can drag either from source to target or vice versa.
- Provide a SQL WHERE clause as needed.
- Hit the Test Condition button to see how many items will be copied.
Standard Mapping Rules
The copy process is based on following rules:
|Standard Mapping||[Create Tables] is enabled. No column mapping can be specified.||Table allready exists||Copy process will not stop but try to proceed. If the existing table structure / name fits the source table, the copy process should be successfull|
|Standard Mapping||[Create Tables] is disabled. No column mapping has been specified.||Table doesn't exist. Table exists but doesn't fit the source table structure.||Copy process will not stop but try to proceed. It presumes that there are columns in the existing table which fit to the source table. The mapping is done by the equality of the table and column name. However if there are to many errors (Standard is 50, can be configured in the db.properties file of the plugin, in an later version within the preferences of th plugin itself), the copy data process will stop and proceed with the next table.|
|Standard Mapping||[Create Tables] is enabled. [Copy FK Constraints] is enabled. No column mapping can be specified.||The creation of FK constraints fails||The table will be created without FK constraints. The copy process will not stop. Note that tests with different databases have shown that this feature doesn't seem to be very stable. This is mainly due to different implementation of the IMPORTED KEYS specification of each JDBC driver. But give it a try you might be successfull anyway ;-)|
|Standard Mapping||[Drop Tables] is enabled.||Drop Tables fails||The copy process will not stop. Note that you can just drop all tables without copying any data by selecting only this feature and deselect all others (Don't know whether this is good at all ...).|
|Standard Mapping||[Create Tables] and [Drop Tables] is enabled.[FK Constraints]on your choice. [Copy Data] is disabled||see above||With this settings you can just recreate your table structure on a different database.|
|Additional Mapping||[Create Tables] is disabled. The source table is linked to a target table of your choice by hitting the [Map] button for tables. No additional column mappings has been specified.||If the column names differ, the copy will fail.||Stopping after the max number of errors has been occured.|
|Additional Mapping||[Create Tables] is disabled. The source table is linked to a target table of your choice by hitting the [Map] button for tables. Additional column mappings has been specified.||If the target columns have a differnt type than the source columns, the copy will fail. If not all columns of the target table are mapped but are e.g. not nullable, the copy will fail. If not all columns of the source tables are mapped/ignored and there is no column with the same name on the target table, the copy will fail.||Stopping after the max number of errors has been occured.|
Start the copy process on the 3rd page. A progress bar shows the current step.
After the copy process is finished you see the messages created during the copy action. Double click on one item to see the full message (especially good for exceptions).
SQL editor and result view
The SQL editor provided by this plugin can execute multiple SQL statements. Select the DB Location you want to use and execute it. You can simple switch your DB Location and your SQL commands gets executed on a different database. The Result View gets automatically opened.
You can change the standard file extension of the SQL Command File beeing created by the SQL Command File wizard in the preferences dialog as well. However you have to take into consideration that by changing the standard extension (which is dbcopy at the moment) you have to take care to register this extension to the apropriate editor on your own.
Have a look at the FAQ section.