Re: [Objectbridge-developers] Database structures

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi Kief,

Kief Morris wrote:
> 
> Hello, I've just started working with OJB, and I must say I quite
> like it. I had partially developed a similar package myself last year,
> but decided that since DB code isn't a focus of my work (and it's
> a considerable time sink) I needed to find something else to use.
> OJB fits the bill very neatly, thanks to those of you who've been
> developing it.
> 
> I'm having a few problems however. I have a project using mySQL,
> and I haven't got the database bridge in place yet.
> 
> One problem is I'm not sure how to structure the tables which ojb
> uses. I've used the information in repository.xml to create the tables,
> but I'm not sure (a) how big the CHAR columns should be, or

The CHAR columns hold serialized OID objects. An OID is a wrapper object
to databases primary key. As primary keys can be compound of an
arbitrary number of columns, the size of these columns depends on your
DB schema. I guess 512 may be a sound value. 

> (b) whether I need to put anything in for the CollectionDescriptors -
> foreign keys? 

CollectionDescriptors are medadata for 1-n relationships in your
databases schema. thus you need foreign keys in your dependend table,
containing primary key values from the master table.
The CollectionDescriptor contains a list of indexes of the primary keys
of the master table
(<descriptor_ids>index1,index2,...]</descriptor_ids>)

> I'll put the table definitions I'm using at the end of this
> message - I decided to make most of the CHAR fields 255 characters
> to start with, and to ignore the collections since I can't see anything
> relating to table columns in the xml.
> 
> The error I'm getting at the moment may or may not be related to
> the table structures. Here's the exception:
> 
>     [junit] Running com.kief.babbletest.ForumTest
>     [junit] OJB Descriptor Repository: file:/C:/src/new/babble/test/conf/repository.xml
>     [junit] connection.prepareStatement(connection.nativeSQL(SELECT CLASSNAME, FIELDNAME, CURRENT FROM OJB_SEQ WHERE (CLASSNAME = ?) AND (FIELDNAME = ?)), 1004, 1007)
>     [junit] java.lang.AbstractMethodError
>     [junit]     at ojb.broker.accesslayer.StatementsForClass.prepareStatement(StatementsForClass.java:123)
>     [junit]     at ojb.broker.accesslayer.StatementsForClass.getSelectByPKkStmt(StatementsForClass.java:246)
>     [junit]     at ojb.broker.accesslayer.StatementManager.getSelectByPKStatement(StatementManager.java:196)
>     [junit]     at ojb.broker.accesslayer.JdbcAccess.materializeObject(JdbcAccess.java:253)
>     [junit]     at ojb.broker.PersistenceBrokerImpl.getDBObject(PersistenceBrokerImpl.java:776)
> 
> The third line is output from a println I put in immediately before the error, which happens
> on the line:
> 
>         result = connection.prepareStatement(nsql, TYPE, CONCUR);

this method signature is not present in JDBC 1.0 drivers thus you get
the abstract method error!
Use a JDBC 2.0 driver instead or change the source code to use the jdbc
1.0 signature:
connection.prepareStatement(nsql);

> 
> So apparently the prepareStatement() method of the Connection object returned by
> my JDBC driver is considered to be abstract? I'm not sure why this would be,
> prepared statements do work on this driver in other code of mine. Any clues?
> I'm using mm.mysql.jdbc-1.2c, my relevant repository.xml looks like:
> 
>    <JdbcConnectionDescriptor id="default">
>       <dbms.name>BabbleTest</dbms.name>
>       <driver.name>org.gjt.mm.mysql.Driver</driver.name>
>       <url.protocol>jdbc</url.protocol>
>       <url.subprotocol>mysql</url.subprotocol>
>       <url.dbalias>//localhost/wild5</url.dbalias>
>    </JdbcConnectionDescriptor>
> 
> Has anybody else reported using ojb successfully with mySQL?

Some people tried but I did not get any responses if they had problems
or not. I'm planning to have a regular support for mySql as well as for
DB2 and Oracle.

> 
> Thanks for any help.
> 
> Here are the table structures I'm using:
> 
>         -- For Class ojb.odmg.NamedRootsEntry
>         CREATE TABLE IF NOT EXISTS OJB_NRM (
>                         NAME VARCHAR(255) NOT NULL,   
>                         OID VARCHAR(64),
>                         PRIMARY KEY (NAME)
>         );

the length for name is ok (you won't have names longer than 255 bytes,
will you?)
But the size for the OID shoould be bigger as it will contain fully
serialized OID Object, say 512!

> 
>         -- For Class ojb.odmg.collections.DListImpl
>         CREATE TABLE IF NOT EXISTS OJB_DLIST (
>          ID INT NOT NULL,
>          SIZE INT,
>                 --  CollectionDescriptor
>                 PRIMARY KEY (ID)
>         );

No need to maintain anything regarding CollectionDescriptors in this
table! All items in a DList collection are maintained in the
OJB_DLIST_ENTRIES table.

> 
>         -- For Class ojb.odmg.collections.DListEntry
>         CREATE TABLE IF NOT EXISTS OJB_DLIST_ENTRIES (
>                         ID INT NOT NULL,
>                         DLIST_ID INT,
>                         POSITION INT,
>                         OID VARCHAR(64),
>                         PRIMARY KEY (ID)
>         );
> 

Make OID bigger (same reason as above)!

>         -- For Class ojb.broker.SequenceEntry
>         CREATE TABLE IF NOT EXISTS OJB_SEQ (
>                 CLASSNAME VARCHAR(255) NOT NULL,
>                 FIELDNAME VARCHAR(64),
>                 CURRENT INT,
>                 PRIMARY KEY (CLASSNAME)
>         );
> 
>         -- For Class ojb.odmg.collections.DBagImpl
>         CREATE TABLE IF NOT EXISTS OJB_DLIST (
>                 ID INT NOT NULL,
>                 SIZE INT,
>                 -- CollectionDescriptor
>                 PRIMARY KEY(ID)
>    );

Don't worry about the Collection Descriptors here :-)
> 
>         -- For Class ojb.odmg.collections.DSetImpl
>         CREATE TABLE IF NOT EXISTS OJB_DSET (
>                 ID INT NOT NULL,
>                 SIZE INT,
>                 -- CollectionDescriptor
>                 PRIMARY KEY(ID)
>         );
> 
>         -- For Class ojb.odmg.collections.DSetEntry
>         CREATE TABLE IF NOT EXISTS OJB_DSET_ENTRIES (
>                 ID INT NOT NULL,
>                 DLIST_ID INT,
>                 POSITION INT,
>                 OID VARCHAR(255),
>                 PRIMARY KEY(ID)
>    );

OID should be bigger
> 
>         -- For Class ojb.odmg.collections.DMapImpl
>         CREATE TABLE IF NOT EXISTS OJB_DMAP (
>                 ID INT NOT NULL,
>                 SIZE INT,
>                 -- CollectionDescriptor
>                 PRIMARY KEY(ID)
>         );
> 
>         -- For Class ojb.odmg.collections.DMapEntry
>         CREATE TABLE IF NOT EXISTS OJB_DMAP_ENTRIES (
>                 ID INT NOT NULL,
>                 DMAP_ID INT,
>                 KEY_OID VARCHAR(64),
>                 VALUE_OID VARCHAR(64),
>                 PRIMARY KEY(ID)
>         );
> 

KEY_OID, VALUE_OID should be bigger (see above).

regards,

Thomas

> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> http://lists.sourceforge.net/lists/listinfo/objectbridge-developers