db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Keeney, Thomas" <Keen...@SEC.GOV>
Subject RE: probleems with generating DDL for mySql
Date Wed, 26 Mar 2003 16:33:09 GMT
Jan,

The sql2xml generation is not that accurate.  I've had the most success with
writing my schema files by hand, then running Torque OM generation from
these schemas.  Take a look at the schema DTD
http://jakarta.apache.org/turbine/dtd/database.dtd for pointers.  You can
find the mapping between the schema types and MySQL types within the Torque
distribution: torque/templates/sql/base/mysql/db.props.  Also, make sure you
have the database set to mysql in the build.properties. 

I have fixed your schema below so it will generate the DDL:

<table name="email_configuration" javaName="EmailConfiguration"
idMethod="idbroker">
    <column name="round_no" javaName="RoundNo" primaryKey="true"
required="true" type="SMALLINT" />
    <column name="message" javaName="Message" required="false"
type="LONGVARCHAR" />
    <column name="days_after_previous" javaName="DaysAfterPrevious"
required="true" type="SMALLINT" />
    <column name="to_INS" javaName="ToIns" required="false" type="CHAR"
size="3" />
    <column name="to_MAN" javaName="ToMan" required="false" type="CHAR"
size="3" />
    <column name="to_CO" javaName="ToCo" required="false" type="CHAR"
size="3" />
    <column name="cc_INS" javaName="CcIns" required="false" type="CHAR"
size="3" />
    <column name="cc_MAN" javaName="CcMan" required="false" type="CHAR"
size="3" />
    <column name="cc_CO" javaName="CcCo" required="false" type="CHAR"
size="3" />
</table>


The above schema will generate this DDL.

CREATE TABLE email_configuration
(
        round_no SMALLINT NOT NULL,
        message MEDIUMTEXT,
        days_after_previous SMALLINT NOT NULL,
        to_INS CHAR (3),
        to_MAN CHAR (3),
        to_CO CHAR (3),
        cc_INS CHAR (3),
        cc_MAN CHAR (3),
        cc_CO CHAR (3),
    PRIMARY KEY(round_no)
);


Your business rules will have to enforce the 3 character limit on your CHAR
fields since the Torque OM objects just use Strings for these field values.
I didn't see a direct mapping between a schema type and the MySQL "TEXT"
type, so I used the schema type "LONGVARCHAR" to get a MySQL "MEDIUMTEXT".
If you need the message column set to type "TEXT", then modify the generated
DDL and use the Torque OM objects generated from the above schema.  Note
that the MySQL TEXT, MEDIUMTEXT, and CLOB types are all treated as Strings
in the Torque objects.

Regards,

Tom

-----Original Message-----
From: Jan Marten Visser [mailto:jan.marten.visser@virgil.nl]
Sent: Wednesday, March 26, 2003 6:03 AM
To: torque
Subject: probleems with generating DDL for mySql


Hi,

I seem to have a problem with generating columns with datatype 'TEXT'
for a mysql database:

I start with an sql file containing:

create table email_configuration
(
    round_no                  SMALLINT               not null,
    message                   TEXT              not null,
    days_after_previous       SMALLINT               not null,
    to_INS                    CHAR(3)                null    ,
    to_MAN                    CHAR(3)                null    ,
    to_CO                     CHAR(3)                null    ,
    cc_INS                    CHAR(3)                null    ,
    cc_MAN                    CHAR(3)                null    ,
    cc_CO                     CHAR(3)                null    ,
    primary key (round_no)
);


with ant (sql2xml) I create the xml containing:

<table name="email_configuration" javaName="EmailConfiguration"
idMethod="idbroker">
    <column name="round_no" javaName="RoundNo" primaryKey="true"
required="true" type="INTEGER" />
    <column name="message" javaName="Message" required="false"
type="VARCHAR" />
    <column name="days_after_previous" javaName="DaysAfterPrevious"
required="true" type="INTEGER" />
    <column name="to_INS" javaName="ToIns" required="false"
type="VARCHAR" size="3" />
    <column name="to_MAN" javaName="ToMan" required="false"
type="VARCHAR" size="3" />
    <column name="to_CO" javaName="ToCo" required="false" type="VARCHAR"
size="3" />
    <column name="cc_INS" javaName="CcIns" required="false"
type="VARCHAR" size="3" />
    <column name="cc_MAN" javaName="CcMan" required="false"
type="VARCHAR" size="3" />
    <column name="cc_CO" javaName="CcCo" required="false" type="VARCHAR"
size="3" />
</table>

This is already beginning to look bad, because the column type "TEXT" in
the original .sql file has changed to 'VARCHAR' without a size. Also you
can see that the type 'SMALLINT' (which mysql knows) has changed to
'INTEGER' which mysql does not know.

If I create sql again I get:

CREATE TABLE email_configuration
(
        round_no INTEGER NOT NULL,
        message VARCHAR,
        days_after_previous INTEGER NOT NULL,
        to_INS VARCHAR (3),
        to_MAN VARCHAR (3),
        to_CO VARCHAR (3),
        cc_INS VARCHAR (3),
        cc_MAN VARCHAR (3),
        cc_CO VARCHAR (3),
    PRIMARY KEY(round_no)
);

So I have the dateformat 'VARCHAR' (with a size) which is not recognized
by mySql and the type "INTEGER" which is not recognized by mysql (should
be 'INT' or 'SMALLINT').

These are the relevant buildproperties:

torque.database.createUrl = jdbc:mysql://10.21.221.3:3306/mysql
torque.database.buildUrl = jdbc:mysql://10.21.221.3:3306/cnt
torque.database.url = jdbc:mysql://10.21.221.3:3306/cnt
torque.database.driver = com.mysql.jdbc.Driver

Anyone recognizes this problem and/or knows any solutions?

Cheers,

JM


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Mime
View raw message