[Perfparse-devel] Re: Database creation questions

[Ben C. <Be...@cl...>]

Chris,

First off, really glad to hear you are going to have a go at this, I am 
sure PerfParse will be greatly improved by this.

Let me answer in line:

> I've been looking at the database creation scripts. There are some parts 
> of the SQL commands that are not available in Oracle. The differences 
> are significant enough that I am going to create separate create and 
> delete scripts for Oracle and we will likely need to create separate 
> scripts for each database supported because the creation syntax and 
> options can be fairly specific even when the query syntax is the same.

Ok.  The creation script is named using the DBMS name so that others may 
be supplied :)

> The create statements all have an "IF NOT EXISTS" statement. I assume 
> that this causes causes the create statements to not create a table if 
> one already exists but also not generate an error if the table isn't 
> created for that reason.

Not exactly.  If the table exists, no creation takes place and the 
existing table is left in place.

> This isn't an option in Oracle. If the table 
> already exists, Oracle is going to generate an error. The only way 
> around this is drop the table first but if the table doesn't exist, then 
> that will generate an error. Do we need to allow someone to run the 
> create script on top of an already created database? I would prefer to 
> drop each table and then create it. This seems to be the style that is 
> prevalent among Oracle DBAs that I have spoken to.

The advantage of the 'IS NOT EXISTS' is that the script can be run again 
and only create new tables.  But this is flawed.  This syntax does not 
exist for the 'ALTER TABLE' so these parts will fail anyway.

In short, the script is only used on the first install.  It should never 
be used after that.  (Maybe I will add that comment to the head of the 
script as well :)

> In Oracle, the standard way to do a variable length character string is 
> using a VARCHAR2 rather then the VARCHAR. I believe this will still 
> interface in the same way when we do our queries. I have also kept the 
> lengths on them so that the data should be compatible across databases.

Ok.  Yves talked to me about an elegant abstraction layer for multiple 
DBMSs.  I am sure we can turn this idea into a template :)

> Oracle also doesn't have support for the Auto Increment data type so I 
> created a sequences and triggers to do the same thing. Also the foreign 
> key constraint "ON DELETE RESTRICT" is redundant and the syntax not 
> supported in Oracle.

Is the ON DELETE RESTRICT default?  It is default in MySQL, I just like 
adding it for clarity.

> I may have made some other changes between the MySQL scripts and the 
> Oracle scripts but I can't remember right now. I am including a tar.gz 
> file with the two files in them. The files will be placed in the contrib 
> directory right next to the MySQL scripts. Please look them over and 
> send me any questions. I have tested the scripts against an Oracle 
> database so they are syntactically correct and I believe they are 
> programatically correct as well.

I'll take a look and answer in a while.

Regards,

Ben