LucidDbPowerArchitect

From Eigenpedia

Jump to: navigation, search

Power*Architect (from the SQL Power Group) is an open-source cross-platform data modeling tool. This page explains how to set it up for use with LucidDB.

Contents

Install

First, download and install a copy. The version against which these instructions were developed was Power*Architect 0.9.10 running on Linux.

The first time you start it up, it will tell you that it is going to create a file named pl.ini. Write down the location of this new file, since later on you're going to need to edit it.

Connect

Assuming you already have a LucidDB server up and running, let's connect to it. From the Connections menu, choose Database Connection Manager, and then JDBC Drivers. Click the + button to create a new driver, and then fill in the dialog as follows, using the Add JAR... button to locate your LucidDB JDBC client driver .jar:

Click OK, and then back in the connection manager dialog, click New... to create a connection using the new driver:

Click OK to save it. Then, shut down Power*Architect, because we need to give it some information about LucidDB before continuing. After shutting down, open the pl.ini file in a text editor and look for a section like this one:

[Database Types_10]
JDBC URL=jdbc:luciddb:rmi://localhost
JDBC JAR_0=/home/jvs/open/luciddb/plugin/LucidDbClient.jar
JDBC JAR Count=1
Name=LucidDB
JDBC Driver Class=com.lucidera.jdbc.LucidDbRmiDriver

Edit the section, adding lines as follows:

[Database Types_10]
JDBC URL=jdbc:luciddb:rmi://localhost
JDBC JAR_0=/home/jvs/open/luciddb/plugin/LucidDbClient.jar
JDBC JAR Count=1
Name=LucidDB
JDBC Driver Class=com.lucidera.jdbc.LucidDbRmiDriver
ca.sqlpower.architect.profile.ColumnProfileResult$StringLengthSQLFunction=LENGTH(:)
ca.sqlpower.architect.profile.ColumnProfileResult$AverageSQLFunction=AVG(:)
ca.sqlpower.architect.profile.ColumnProfileResult$CaseWhenNullSQLFunction=CASE WHEN : IS NULL THEN : END
DDL Generator=ca.sqlpower.architect.ddl.GenericDDLGenerator

Now restart Power*Architect, right-click on the Project icon in the tree view, choose Add Source Connection and then your new LucidDB connection name. You should now be able to browse the tree. Here's what it looks like if you expand the LOCALDB catalog and then the SYS_ROOT schema:

Reverse Engineer

Now let's do some reverse engineering. Drag DBA_SCHEMAS and DBA_TABLES from the tree into the right-hand diagram.

Since these are views, there is no primary key information to be obtained via reverse engineering, but you can augment it by right-clicking the columns and editing their properties. You can also document the foreign keys implicit in the data. Here's an example:

Forward Engineer

At this point, you can also do things like forward-engineering to generate a CREATE TABLE script corresponding to the diagram above. Use the Tools menu, Forward Engineer... command, and when prompted, use IBM DB2 for the dialect (it's the closest to LucidDB and SQL:2003). Here's an example of the output:

CREATE TABLE DBA_SCHEMAS (
                CATALOG_NAME VARCHAR(128) NOT NULL,
                SCHEMA_NAME VARCHAR(128) NOT NULL,
                CREATOR VARCHAR(128) NOT NULL,
                CREATION_TIMESTAMP TIMESTAMP NULL,
                LAST_MODIFIED_TIMESTAMP TIMESTAMP NULL,
                REMARKS VARCHAR(65535) NULL,
                MOF_ID VARCHAR(128) NULL,
                LINEAGE_ID VARCHAR(128) NULL,
                CONSTRAINT DBA_SCHEMAS_pk PRIMARY KEY (CATALOG_NAME, SCHEMA_NAME)
);
CREATE TABLE DBA_TABLES (
                CATALOG_NAME VARCHAR(128) NOT NULL,
                SCHEMA_NAME VARCHAR(128) NOT NULL,
                TABLE_NAME VARCHAR(128) NOT NULL,
                TABLE_TYPE VARCHAR(128) NULL,
                CREATOR VARCHAR(128) NOT NULL,
                CREATION_TIMESTAMP TIMESTAMP NULL,
                LAST_MODIFIED_TIMESTAMP TIMESTAMP NULL,
                REMARKS VARCHAR(128) NULL,
                MOF_ID VARCHAR(128) NULL,
                LINEAGE_ID VARCHAR(128) NULL,
                CONSTRAINT DBA_TABLES_pk PRIMARY KEY (CATALOG_NAME, SCHEMA_NAME, TABLE_NAME)
);
ALTER TABLE DBA_TABLES ADD CONSTRAINT DBA_SCHEMAS_DBA_TABLES_fk
FOREIGN KEY (CATALOG_NAME, SCHEMA_NAME)
REFERENCES DBA_SCHEMAS (CATALOG_NAME, SCHEMA_NAME)
NOT DEFERRABLE;

Note that you would not actually be able to execute the last command due to the lack of support for foreign keys in LucidDB, as well as the NULL declarations in the column specifications. Power*Architect seems to think that is the opposite of NOT NULL, but it's illegal to specify it in SQL:2003; filed bug 1569. This bug has been fixed; you can get the fix by downloading a recent nightly build.

Profile Data

Power*Architect has support for data profiling. It relies on the pl.ini information entered above, so be sure you did not skip that step before continuing, otherwise you'll end up with a NullPointerException.

Select a table in the tree, and then use the Profile menu to sample it. The queries issued are heavy-duty (and Power*Architect is not aware of LucidDB's TABLESAMPLE support), so if you have a lot of data, this may take a long time. Here's an example of the results:

Compare Data Models

Now let's try Power*Architect's feature for comparing data models. It relies on the pl.ini information entered above, so be sure you did not skip that step before continuing, otherwise you'll end up with a NullPointerException. In sqllineClient, execute the following script:

create schema s1;
create table s1.t1(i int);
create table s1.t2(a int, b varchar(2));
create schema s2;
create table s2.t2(a int, b varchar(2), c double);
create table s2.t3(k timestamp);

From the Tools menu, choose Compare DM, and then fill in the form as follows:

Click the Start button, and you should get output as follows:

DROP TABLE LOCALDB.S1.T1;
 ALTER TABLE LOCALDB.S1.T2 ADD COLUMN C DOUBLE NULL;
CREATE TABLE LOCALDB.S1.T3 (
                K TIMESTAMP NULL
);

You can also choose to get an English description instead:

Table T1 should be removed
Table T2 needs no changes
	Column A needs no changes
	Column B needs no changes
	Column C should be added
Table T3 should be added

Run SQL

You can use the SQL Runner to execute ad hoc SQL statements. (When it asks you for a password, you can type anything, unless you have actually set up security on LucidDB. Don't leave it blank, otherwise it will keep asking you for every execution.)

Caveats

Power*Architect only knows about indexes, but not UNIQUE constraints (other than PRIMARY KEY). LucidDB only supports SQL:2003 standard UNIQUE constraints (not the rather common but non-standard CREATE UNIQUE INDEX).

Some operations are known not to work due to LucidDB limitations:

  • Copy Table (tries to use a prepared update statement, which would be very slow anyway due to single-row inserts)
  • ETL integration (Kettle doesn't know about LucidDB yet)
  • Anything to do with ALTER TABLE or foreign keys, neither of which LucidDB supports yet
Personal tools