You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Rusty Wright <ru...@berkeley.edu> on 2006/08/23 02:34:56 UTC
autoincrement
How do I specify an autoincrement column such that the generated sql
works with both mysql and postgresql? In my database xml file I have
> <?xml version="1.0"?>
> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
>
> <database name="waitlist" defaultIdMethod="native">
> <table name="waitlist">
> <column name="waitlist_key"
> type="bigint"
> size="20"
> required="true"
> primaryKey="true"
> autoIncrement="true"
> />
When I run this under ant with PostgreSQL it connects to the database
and gets the following error. I think it's not creating the sequence
first. When I use MySql it does everything without any errors.
> ddlToDatabase] WARNING: SQL Command CREATE TABLE waitlist
> [ddlToDatabase] (
> [ddlToDatabase] waitlist_key BIGINT NOT NULL UNIQUE DEFAULT nextval('waitlist_waitlist_key_seq'),
> [ddlToDatabase] facility VARCHAR(20) NOT NULL,
> [ddlToDatabase] uid VARCHAR(16) NOT NULL,
> [ddlToDatabase] cn VARCHAR(40) NOT NULL,
> [ddlToDatabase] type VARCHAR(20) NOT NULL,
> [ddlToDatabase] platform VARCHAR(8) NOT NULL,
> [ddlToDatabase] signin_time TIMESTAMP NOT NULL,
> [ddlToDatabase] PRIMARY KEY (waitlist_key)
> [ddlToDatabase] ) failed with: ERROR: relation "waitlist_waitlist_key_seq" does not exist
Thanks
Re: autoincrement
Posted by Rusty Wright <ru...@berkeley.edu>.
Ok, thanks. I entered the issue about sequences for jira. Let me know
if you need any more information on that.
Thomas Dudziak wrote:
> On 8/22/06, Rusty Wright <ru...@berkeley.edu> wrote:
>
>> I'm also getting some warning that I don't understand (I'm a postgresql
>> newbie):
>>
>> > CREATE SEQUENCE
>> > psql:waitlist_generated.sql:40: NOTICE: CREATE TABLE / PRIMARY KEY
>> will create implicit index "waitlist_pkey" for table "waitlist"
>> > CREATE TABLE
>
> That's normal. PostgreSQL simply informs you that it will create an
> index for the primary key (since you didn't specify one yourself).
> Just ignore it.
>
>> (I don't even know how to examine the tables that I've just created.
>> Duh.)
>
> There's a commandline tool (called psql or pgsql or something like
> this). On Windows, you also get a graphical client with your
> PostgreSQL installation.
> Since you're using Java, you can also use the great Squirrel SQL tool
> (http://squirrel-sql.sourceforge.net/).
>
> Tom
Re: autoincrement
Posted by Thomas Dudziak <to...@gmail.com>.
On 8/22/06, Rusty Wright <ru...@berkeley.edu> wrote:
> I'm also getting some warning that I don't understand (I'm a postgresql
> newbie):
>
> > CREATE SEQUENCE
> > psql:waitlist_generated.sql:40: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "waitlist_pkey" for table "waitlist"
> > CREATE TABLE
That's normal. PostgreSQL simply informs you that it will create an
index for the primary key (since you didn't specify one yourself).
Just ignore it.
> (I don't even know how to examine the tables that I've just created. Duh.)
There's a commandline tool (called psql or pgsql or something like
this). On Windows, you also get a graphical client with your
PostgreSQL installation.
Since you're using Java, you can also use the great Squirrel SQL tool
(http://squirrel-sql.sourceforge.net/).
Tom
Re: autoincrement
Posted by Rusty Wright <ru...@berkeley.edu>.
If I feed the waitlist_generated.sql file to the postgresql command line
program psql then it successfully creates the tables.
I'm also getting some warning that I don't understand (I'm a postgresql
newbie):
> CREATE SEQUENCE
> psql:waitlist_generated.sql:40: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "waitlist_pkey" for table "waitlist"
> CREATE TABLE
(I don't even know how to examine the tables that I've just created. Duh.)
Rusty Wright wrote:
> Sorry, I wasn't clear about how I'm using DdlUtils. I'm using ant. In
> my build.xml file the ant target for DdlUtils contains the following.
>
>> <ddlToDatabase>
>> <fileset dir="sql">
>> <include name="waitlist.xml"/>
>> </fileset>
>>
>> <database driverClassName="${db1_driverClassName}"
>> url="${db1_url}"
>> username="${db1_username}"
>> password="${db1_password}"
>> />
>>
>> <writeSchemaToDatabase />
>>
>> <writeSchemaSqlToFile
>> alterDatabase="false"
>> doDrops="false"
>> failOnError="true"
>> outputFile="sql/waitlist_generated.sql"
>> />
>> </ddlToDatabase>
>
> When I run it for mysql my build.xml has
>
>> <property
>> file="db.properties-mysql"
>> />
>
> And when I run it for postgresql I change build.xml so that it has
>
>> <property
>> file="db.properties-postgres"
>> />
>
> And in the db.properties-<whatever> files are lines like
>
>> db1_driverClassName=com.mysql.jdbc.Driver
>> db1_url=jdbc:mysql://localhost/waitlist
>
> and
>
>> db1_driverClassName=org.postgresql.Driver
>> db1_url=jdbc:postgresql://localhost/waitlist
>
> So it's generating the database specific sql for each one. But for
> PostgreSQL it's using nextval() with a sequence that hasn't been defined
> or specified.
>
> I'm guessing that I'm doing something wrong or leaving something out
> since it's not creating the sequence.
>
> Ok, wait; I commented out the writeSchemaToDatabase and looked at the
> sql file it generated and the CREATE SEQUENCE is in the sql file. So
> why is it bombing when it tries to create the tables directly?
>
> Thomas Dudziak wrote:
>> On 8/22/06, Rusty Wright <ru...@berkeley.edu> wrote:
>>
>>> How do I specify an autoincrement column such that the generated sql
>>> works with both mysql and postgresql? In my database xml file I have
>>
>> I'm not quite sure what you mean ? The idea of DdlUtils is to specify
>> the database model in a database-independent XML format and then let
>> DdlUtils create the database for you or get database-specific SQL for
>> that XML database schema.
>> Especially for database creation, every database has different
>> statements and functionality, so it is quite likely that the SQL
>> generated for even the simplest database model will only work for the
>> database that you created the SQL for, and for no other database.
>>
>> Tom
Re: autoincrement
Posted by Thomas Dudziak <to...@gmail.com>.
> So it's generating the database specific sql for each one. But for
> PostgreSQL it's using nextval() with a sequence that hasn't been defined
> or specified.
>
> I'm guessing that I'm doing something wrong or leaving something out
> since it's not creating the sequence.
That's odd. Could you create an issue in DdlUtils' JIRA and attach an
XML database schema that leads to this error ?
Tom
Re: autoincrement
Posted by Rusty Wright <ru...@berkeley.edu>.
Sorry, I wasn't clear about how I'm using DdlUtils. I'm using ant. In
my build.xml file the ant target for DdlUtils contains the following.
> <ddlToDatabase>
> <fileset dir="sql">
> <include name="waitlist.xml"/>
> </fileset>
>
> <database driverClassName="${db1_driverClassName}"
> url="${db1_url}"
> username="${db1_username}"
> password="${db1_password}"
> />
>
> <writeSchemaToDatabase />
>
> <writeSchemaSqlToFile
> alterDatabase="false"
> doDrops="false"
> failOnError="true"
> outputFile="sql/waitlist_generated.sql"
> />
> </ddlToDatabase>
When I run it for mysql my build.xml has
> <property
> file="db.properties-mysql"
> />
And when I run it for postgresql I change build.xml so that it has
> <property
> file="db.properties-postgres"
> />
And in the db.properties-<whatever> files are lines like
> db1_driverClassName=com.mysql.jdbc.Driver
> db1_url=jdbc:mysql://localhost/waitlist
and
> db1_driverClassName=org.postgresql.Driver
> db1_url=jdbc:postgresql://localhost/waitlist
So it's generating the database specific sql for each one. But for
PostgreSQL it's using nextval() with a sequence that hasn't been defined
or specified.
I'm guessing that I'm doing something wrong or leaving something out
since it's not creating the sequence.
Ok, wait; I commented out the writeSchemaToDatabase and looked at the
sql file it generated and the CREATE SEQUENCE is in the sql file. So
why is it bombing when it tries to create the tables directly?
Thomas Dudziak wrote:
> On 8/22/06, Rusty Wright <ru...@berkeley.edu> wrote:
>
>> How do I specify an autoincrement column such that the generated sql
>> works with both mysql and postgresql? In my database xml file I have
>
> I'm not quite sure what you mean ? The idea of DdlUtils is to specify
> the database model in a database-independent XML format and then let
> DdlUtils create the database for you or get database-specific SQL for
> that XML database schema.
> Especially for database creation, every database has different
> statements and functionality, so it is quite likely that the SQL
> generated for even the simplest database model will only work for the
> database that you created the SQL for, and for no other database.
>
> Tom
Re: autoincrement
Posted by Thomas Dudziak <to...@gmail.com>.
On 8/22/06, Rusty Wright <ru...@berkeley.edu> wrote:
> How do I specify an autoincrement column such that the generated sql
> works with both mysql and postgresql? In my database xml file I have
I'm not quite sure what you mean ? The idea of DdlUtils is to specify
the database model in a database-independent XML format and then let
DdlUtils create the database for you or get database-specific SQL for
that XML database schema.
Especially for database creation, every database has different
statements and functionality, so it is quite likely that the SQL
generated for even the simplest database model will only work for the
database that you created the SQL for, and for no other database.
Tom