You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by josef richberg <sq...@yahoo.com> on 2001/12/12 21:13:00 UTC
adding new types to Torque..mysql
I'm looking to adjust the way the Mysql schema is
created using torque. I am loooking at adding some
keywords and modifiying the resultant .sql file. I
looked at adding a keyword to
tdk/share/conf/torque/templates/sql/base/mysql/db.props.
The line I added was:
MEDIUMINT=MEDIUMINT
When I try to use it in type="MEDIUMINT", I get the
following error(in velocity log):
"Method get threw exception for reference $dbprops in
/sql/base/mysql/columns.vm at [2,20]"
That line is "#set ( $type = $dbprops.get($col.Type)
)"
Shouldn't this pick up the column type from the
db.props file?
--josef
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Postgres issues (Was:Re: adding new types to Torque..mysql)
Posted by Colm McCartan <co...@owl.co.uk>.
John McNally wrote:
> Sorry, I missed the response to my question, but you should specify the
> fk as a composite, not as two distinct fk's.
>
>
>>> <foreign-key foreignTable="DOCUMENT_VERSION">
>>> <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
>>> </foreign-key>
>>> <foreign-key foreignTable="DOCUMENT_VERSION">
>>> <reference local="VERSION_ID" foreign="VERSION_ID"/>
>>> </foreign-key>
>>>
>
> Should be:
>
>
>>> <foreign-key foreignTable="DOCUMENT_VERSION">
>>> <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
>>> <reference local="VERSION_ID" foreign="VERSION_ID"/>
>>> </foreign-key>
>>>
>
> john mcnally
>
> Daniel Rall wrote:
>
>>Hi Colm. Was there ever a resolution to this?
OK. Thanks for the clarification John - I shall try this at my next
rebuild. BTW, what are your thoughts on the bad SQL generated for the
user table alias as discussed in:
http://www.mail-archive.com/turbine-user@jakarta.apache.org/msg05399.html
Here, foreign key references are made to the table alias name, instead
of to the underlying turbine_user table...
Also, I am tracing some problems with native id generation. The
DBPostgres adapter class appears to have invalid code in its
getIdSqlForAutoIncrement method. It seems to try to issue SQL that looks
like this:
select currval(seq_name);
instead of
select last_value from seq_name;
Has anyone come across this?
All in all, I'm tempted to go over to idBroker to bypass this problem.
Any thoughts welcome.
While I'm on, thanks for your comments Scott (Eade) on how to approach
patching Torque - at the moment this may demand more time than I can
afford but cheers anyway. I would welcome any details about the torque
test framework you mention. Also, you mentioned a new 'hack' for
Object.vm - I'd certainly like to see it included in the howto.
Cheers,
colm
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by John McNally <jm...@collab.net>.
Sorry, I missed the response to my question, but you should specify the
fk as a composite, not as two distinct fk's.
> > <foreign-key foreignTable="DOCUMENT_VERSION">
> > <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
> > </foreign-key>
> > <foreign-key foreignTable="DOCUMENT_VERSION">
> > <reference local="VERSION_ID" foreign="VERSION_ID"/>
> > </foreign-key>
Should be:
> > <foreign-key foreignTable="DOCUMENT_VERSION">
> > <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
> > <reference local="VERSION_ID" foreign="VERSION_ID"/>
> > </foreign-key>
john mcnally
Daniel Rall wrote:
>
> Hi Colm. Was there ever a resolution to this?
>
> Daniel
>
> Colm McCartan <co...@owl.co.uk> writes:
>
> > John McNally wrote:
> >
> >> Colm McCartan wrote:
> >> (apart from the fact that torque generates
> >>
> >>>incorrect sql for foreign key references to composite primary keys -
> >>>another story altogether...)
> >> can you give an example?
> >
> >
> > Sure. Heres a table from our schema with a composite primary key:
> >
> > -----------------------------------------------------------------------
> >
> > <table name = "DOCUMENT_VERSION">
> > <column name="DOCUMENT_ID" required="true" autoIncrement="false" primaryKey="true" type="BIGINT"/>
> > <column name="VERSION_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> > <column name="COMMENTS" required="true" size="255" type="VARCHAR"/>
> > <foreign-key foreignTable="DOCUMENT">
> > <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
> > </foreign-key>
> > </table>
> > -----------------------------------------------------------------------
> >
> > referenced by the following table:
> >
> >
> > -----------------------------------------------------------------------
> > <table name="USER_DOCUMENT_REL">
> > <column name="DOCUMENT_ID" required="true" autoIncrement="false" primaryKey="true" type="BIGINT"/>
> > <column name="VERSION_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> > <column name="TURBINE_USER_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> > <column name="REL_TYPE_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> > <foreign-key foreignTable="DOCUMENT_VERSION">
> > <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
> > </foreign-key>
> > <foreign-key foreignTable="DOCUMENT_VERSION">
> > <reference local="VERSION_ID" foreign="VERSION_ID"/>
> > </foreign-key>
> > </table>
> > -----------------------------------------------------------------------
> >
> >
> > Torque generates the following SQL DDL for the second table:
> >
> >
> > -----------------------------------------------------------------------
> > CREATE TABLE USER_DOCUMENT_REL
> > (
> > DOCUMENT_ID int8 NOT NULL,
> > VERSION_ID integer NOT NULL,
> > TURBINE_USER_ID integer NOT NULL,
> > REL_TYPE_ID integer NOT NULL,
> > PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID)
> > );
> > ALTER TABLE USER_DOCUMENT_REL
> > ADD CONSTRAINT DOCUMENT_ID FOREIGN KEY (DOCUMENT_ID)
> > REFERENCES DOCUMENT_VERSION (DOCUMENT_ID);
> >
> > ALTER TABLE USER_DOCUMENT_REL
> > ADD CONSTRAINT VERSION_ID FOREIGN KEY (VERSION_ID)
> > REFERENCES DOCUMENT_VERSION (VERSION_ID);
> > -----------------------------------------------------------------------
> >
> > This generates errors at build time with the db complaining about
> > invalid foreign key references (sadly, I don't have the detail of the
> > exceptions any more). After I hand-edited to the following, it was
> > fine:
> >
> > -----------------------------------------------------------------------
> > CREATE TABLE USER_DOCUMENT_REL
> > (
> > DOCUMENT_ID int8 NOT NULL,
> > VERSION_ID integer NOT NULL,
> > TURBINE_USER_ID integer NOT NULL,
> > REL_TYPE_ID integer NOT NULL,
> > PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID),
> > FOREIGN KEY(DOCUMENT_ID, VERSION_ID) REFERENCES DOCUMENT_VERSION
> > );
> > -----------------------------------------------------------------------
> >
> > The version is: postgreSQL 7.1.2 and tdk2.1
> >
> > We can easily resolve this by going to an 'artificial' autogenerated
> > primary key (or manually generating the db).
> >
> > Let me know if you need any more info or if I have misunderstood how
> > to do this.
> >
> > colm
> >
> >
> >
> >
> >
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by Daniel Rall <dl...@finemaltcoding.com>.
Hi Colm. Was there ever a resolution to this?
Daniel
Colm McCartan <co...@owl.co.uk> writes:
> John McNally wrote:
>
>> Colm McCartan wrote:
>> (apart from the fact that torque generates
>>
>>>incorrect sql for foreign key references to composite primary keys -
>>>another story altogether...)
>> can you give an example?
>
>
> Sure. Heres a table from our schema with a composite primary key:
>
> -----------------------------------------------------------------------
>
> <table name = "DOCUMENT_VERSION">
> <column name="DOCUMENT_ID" required="true" autoIncrement="false" primaryKey="true" type="BIGINT"/>
> <column name="VERSION_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> <column name="COMMENTS" required="true" size="255" type="VARCHAR"/>
> <foreign-key foreignTable="DOCUMENT">
> <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
> </foreign-key>
> </table>
> -----------------------------------------------------------------------
>
> referenced by the following table:
>
>
> -----------------------------------------------------------------------
> <table name="USER_DOCUMENT_REL">
> <column name="DOCUMENT_ID" required="true" autoIncrement="false" primaryKey="true" type="BIGINT"/>
> <column name="VERSION_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> <column name="TURBINE_USER_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> <column name="REL_TYPE_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
> <foreign-key foreignTable="DOCUMENT_VERSION">
> <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
> </foreign-key>
> <foreign-key foreignTable="DOCUMENT_VERSION">
> <reference local="VERSION_ID" foreign="VERSION_ID"/>
> </foreign-key>
> </table>
> -----------------------------------------------------------------------
>
>
> Torque generates the following SQL DDL for the second table:
>
>
> -----------------------------------------------------------------------
> CREATE TABLE USER_DOCUMENT_REL
> (
> DOCUMENT_ID int8 NOT NULL,
> VERSION_ID integer NOT NULL,
> TURBINE_USER_ID integer NOT NULL,
> REL_TYPE_ID integer NOT NULL,
> PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID)
> );
> ALTER TABLE USER_DOCUMENT_REL
> ADD CONSTRAINT DOCUMENT_ID FOREIGN KEY (DOCUMENT_ID)
> REFERENCES DOCUMENT_VERSION (DOCUMENT_ID);
>
> ALTER TABLE USER_DOCUMENT_REL
> ADD CONSTRAINT VERSION_ID FOREIGN KEY (VERSION_ID)
> REFERENCES DOCUMENT_VERSION (VERSION_ID);
> -----------------------------------------------------------------------
>
> This generates errors at build time with the db complaining about
> invalid foreign key references (sadly, I don't have the detail of the
> exceptions any more). After I hand-edited to the following, it was
> fine:
>
> -----------------------------------------------------------------------
> CREATE TABLE USER_DOCUMENT_REL
> (
> DOCUMENT_ID int8 NOT NULL,
> VERSION_ID integer NOT NULL,
> TURBINE_USER_ID integer NOT NULL,
> REL_TYPE_ID integer NOT NULL,
> PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID),
> FOREIGN KEY(DOCUMENT_ID, VERSION_ID) REFERENCES DOCUMENT_VERSION
> );
> -----------------------------------------------------------------------
>
> The version is: postgreSQL 7.1.2 and tdk2.1
>
> We can easily resolve this by going to an 'artificial' autogenerated
> primary key (or manually generating the db).
>
> Let me know if you need any more info or if I have misunderstood how
> to do this.
>
> colm
>
>
>
>
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by Colm McCartan <co...@owl.co.uk>.
John McNally wrote:
> Colm McCartan wrote:
> (apart from the fact that torque generates
>
>>incorrect sql for foreign key references to composite primary keys -
>>another story altogether...)
> can you give an example?
Sure. Heres a table from our schema with a composite primary key:
-----------------------------------------------------------------------
<table name = "DOCUMENT_VERSION">
<column name="DOCUMENT_ID" required="true" autoIncrement="false" primaryKey="true" type="BIGINT"/>
<column name="VERSION_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
<column name="COMMENTS" required="true" size="255" type="VARCHAR"/>
<foreign-key foreignTable="DOCUMENT">
<reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
</foreign-key>
</table>
-----------------------------------------------------------------------
referenced by the following table:
-----------------------------------------------------------------------
<table name="USER_DOCUMENT_REL">
<column name="DOCUMENT_ID" required="true" autoIncrement="false" primaryKey="true" type="BIGINT"/>
<column name="VERSION_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
<column name="TURBINE_USER_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
<column name="REL_TYPE_ID" required="true" autoIncrement="false" primaryKey="true" type="INTEGER"/>
<foreign-key foreignTable="DOCUMENT_VERSION">
<reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
</foreign-key>
<foreign-key foreignTable="DOCUMENT_VERSION">
<reference local="VERSION_ID" foreign="VERSION_ID"/>
</foreign-key>
</table>
-----------------------------------------------------------------------
Torque generates the following SQL DDL for the second table:
-----------------------------------------------------------------------
CREATE TABLE USER_DOCUMENT_REL
(
DOCUMENT_ID int8 NOT NULL,
VERSION_ID integer NOT NULL,
TURBINE_USER_ID integer NOT NULL,
REL_TYPE_ID integer NOT NULL,
PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID)
);
ALTER TABLE USER_DOCUMENT_REL
ADD CONSTRAINT DOCUMENT_ID FOREIGN KEY (DOCUMENT_ID)
REFERENCES DOCUMENT_VERSION (DOCUMENT_ID);
ALTER TABLE USER_DOCUMENT_REL
ADD CONSTRAINT VERSION_ID FOREIGN KEY (VERSION_ID)
REFERENCES DOCUMENT_VERSION (VERSION_ID);
-----------------------------------------------------------------------
This generates errors at build time with the db complaining about
invalid foreign key references (sadly, I don't have the detail of the
exceptions any more). After I hand-edited to the following, it was fine:
-----------------------------------------------------------------------
CREATE TABLE USER_DOCUMENT_REL
(
DOCUMENT_ID int8 NOT NULL,
VERSION_ID integer NOT NULL,
TURBINE_USER_ID integer NOT NULL,
REL_TYPE_ID integer NOT NULL,
PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID),
FOREIGN KEY(DOCUMENT_ID, VERSION_ID) REFERENCES DOCUMENT_VERSION
);
-----------------------------------------------------------------------
The version is: postgreSQL 7.1.2 and tdk2.1
We can easily resolve this by going to an 'artificial' autogenerated
primary key (or manually generating the db).
Let me know if you need any more info or if I have misunderstood how to
do this.
colm
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by josef richberg <sq...@yahoo.com>.
Most RDBMS follow this rule:
Given Index (A,B,C), the following conditions will
allow the index to be considered..
Columns provided (Search Arguments):
A
A,B
A,B,C
The following combinations will not be allowed:
B
C
B,C
So as long as I provide the entire key or portions I
can use the index. I would like the option to specify
how my tables are created.
--josef
--- Daniel Rall <dl...@finemaltcoding.com> wrote:
> josef richberg <sq...@yahoo.com> writes:
>
> > If I have a 3 column primary key (A,B,C), defined
> as
> > such:
> > <column name ="A" primaryKey="true" .....>
> > <column name ="B" primaryKey="true" .....>
> > <column name ="C" primaryKey="true" .....>
> >
> > I get three indexes:
> > PRIMARY KEY(A,B,C)
> > INDEX (B,C)
> > INDEX (C)
> >
> > I don't need all three.
>
> If you ever use less that all three PK columns in
> this join table, you
> will likely need all three. For most RDBMSes, when
> you use only one
> or two of the PK columns, and they are not used in
> the same order
> defined in the PK declaration in the schema, you
> will not get the
> benefit of the indexing on those columns, and a full
> table scan will
> be performed (grossly inefficient). This is
> mentioned in the
> documentation (see
> o.a.t.engine.database.model.Table#doHeavyIndex()).
>
> I thought that there used to be a way to turn this
> behavior off, but
> don't see one on looking. If for some reason you
> don't think you need
> this behavior, I'd commit a patch to the
> jakarta-turbine-torque
> repository which allows it to be turned off.
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by Daniel Rall <dl...@finemaltcoding.com>.
josef richberg <sq...@yahoo.com> writes:
> If I have a 3 column primary key (A,B,C), defined as
> such:
> <column name ="A" primaryKey="true" .....>
> <column name ="B" primaryKey="true" .....>
> <column name ="C" primaryKey="true" .....>
>
> I get three indexes:
> PRIMARY KEY(A,B,C)
> INDEX (B,C)
> INDEX (C)
>
> I don't need all three.
If you ever use less that all three PK columns in this join table, you
will likely need all three. For most RDBMSes, when you use only one
or two of the PK columns, and they are not used in the same order
defined in the PK declaration in the schema, you will not get the
benefit of the indexing on those columns, and a full table scan will
be performed (grossly inefficient). This is mentioned in the
documentation (see o.a.t.engine.database.model.Table#doHeavyIndex()).
I thought that there used to be a way to turn this behavior off, but
don't see one on looking. If for some reason you don't think you need
this behavior, I'd commit a patch to the jakarta-turbine-torque
repository which allows it to be turned off.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by josef richberg <sq...@yahoo.com>.
I gave an example in a previous reference. Here it is
again.
If I have a 3 column primary key (A,B,C), defined as
such:
<column name ="A" primaryKey="true" .....>
<column name ="B" primaryKey="true" .....>
<column name ="C" primaryKey="true" .....>
I get three indexes:
PRIMARY KEY(A,B,C)
INDEX (B,C)
INDEX (C)
I don't need all three.
--josef
--- John McNally <jm...@collab.net> wrote:
> Colm McCartan wrote:
> >
> > Frank
>
>
> (apart from the fact that torque generates
> > incorrect sql for foreign key references to
> composite primary keys -
> > another story altogether...)
> >
> > colm
> >
>
> can you give an example?
>
> john mcnally
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by John McNally <jm...@collab.net>.
Colm McCartan wrote:
>
> Frank
(apart from the fact that torque generates
> incorrect sql for foreign key references to composite primary keys -
> another story altogether...)
>
> colm
>
can you give an example?
john mcnally
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by Colm McCartan <co...@owl.co.uk>.
Frank
I'm sure you're correct that it might not be a great idea to *alter*
these but in my case (and maybe Josef's) I had a blank entry for BIGINT
in my postgres db.props and I was able to successfully generate a schema
after adding the int8 entry (apart from the fact that torque generates
incorrect sql for foreign key references to composite primary keys -
another story altogether...)
colm
Frank W. Nolden wrote:
> MEDIUMINT is not defined in db.props file by default. You cannot just add new keywords in this file. They have to be understood by the generation angine as well.
> You can change the mapping from the already available keywords, e.g. change the BIGINT to INT (don't why you would like to do this, but it is an example). I would not advice you to change the mappings if you are using the latest db.props file, since they are adapted best to the various RDBMSs.
>
> josef richberg wrote:
>
> > I'm looking to adjust the way the Mysql schema is
> > created using torque. I am loooking at adding some
> > keywords and modifiying the resultant .sql file. I
> > looked at adding a keyword to
> > tdk/share/conf/torque/templates/sql/base/mysql/db.props.
> > The line I added was:
> > MEDIUMINT=MEDIUMINT
> > When I try to use it in type="MEDIUMINT", I get the
> > following error(in velocity log):
> >
> > "Method get threw exception for reference $dbprops in
> > /sql/base/mysql/columns.vm at [2,20]"
> >
> > That line is "#set ( $type = $dbprops.get($col.Type)
> > )"
> >
> > Shouldn't this pick up the column type from the
> > db.props file?
> >
> > --josef
>
>
>
> I tripped over the same thing - you want to change db.props in
> webappname/WEB-INF/build/bin/torque/templates/sql/base rather than in
> the tdk-wide share (or else, make sure you do a top-level ant build
> after editing the tdk-wide copy)
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by Daniel Rall <dl...@finemaltcoding.com>.
John McNally <jm...@collab.net> writes:
> It seems rather rare that you will have an application with such a
> compound pk, and you will never want to ask for all rows that have C=x.
> But since it was marked as a todo and it was easy, I added an attribute
> skipHeavyIndexing that you can use on the table or database tags to shut
> it off on a table by table basis, or for the entire schema.
Well there ya go! Teach me to read the entire thread...
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by josef richberg <sq...@yahoo.com>.
To answer your question about C=x, if I want to be
able to do that I can add and index to do that. Now
that you have added this tag, how do I incorporate it
into torque in the tdk?
--josef
--- John McNally <jm...@collab.net> wrote:
> It seems rather rare that you will have an
> application with such a
> compound pk, and you will never want to ask for all
> rows that have C=x.
> But since it was marked as a todo and it was easy, I
> added an attribute
> skipHeavyIndexing that you can use on the table or
> database tags to shut
> it off on a table by table basis, or for the entire
> schema.
>
> john mcnally
>
> josef richberg wrote:
> >
> > How can that lead to improved performance? I am
> > always providing all three keys so I will always
> use
> > the Primary Key index. How do the other two
> indexes
> > help? They take up space and need to be
> maintained,
> > so I see that as a hinderance.
> >
> > --josef
> > --- John McNally <jm...@collab.net> wrote:
> > > josef richberg wrote:
> > > >
> > > ...
> > > >
> > > > If I designate 3 fields a "primary" (A,B,C),
> > > torque
> > > > will generate the following indexes:
> > > >
> > > > primary(A,B,C)
> > > > index(B,C)
> > > > index(C)
> > > >
> > > > That is incorrect. I would eventually like to
> > > change
> > > > it, but I'm starting small.
> > > >
> > > > --josef
> > >
> > > Why do you consider this incorrect? It will
> lead to
> > > improved
> > > performance, at least for mysql.
> > >
> > > john mcnally
> > >
> > > --
> > > To unsubscribe, e-mail:
> > >
> <ma...@jakarta.apache.org>
> > > For additional commands, e-mail:
> > > <ma...@jakarta.apache.org>
> > >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Check out Yahoo! Shopping and Yahoo! Auctions for
> all of
> > your unique holiday gifts! Buy at
> http://shopping.yahoo.com
> > or bid at http://auctions.yahoo.com
> >
> > --
> > To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by John McNally <jm...@collab.net>.
It seems rather rare that you will have an application with such a
compound pk, and you will never want to ask for all rows that have C=x.
But since it was marked as a todo and it was easy, I added an attribute
skipHeavyIndexing that you can use on the table or database tags to shut
it off on a table by table basis, or for the entire schema.
john mcnally
josef richberg wrote:
>
> How can that lead to improved performance? I am
> always providing all three keys so I will always use
> the Primary Key index. How do the other two indexes
> help? They take up space and need to be maintained,
> so I see that as a hinderance.
>
> --josef
> --- John McNally <jm...@collab.net> wrote:
> > josef richberg wrote:
> > >
> > ...
> > >
> > > If I designate 3 fields a "primary" (A,B,C),
> > torque
> > > will generate the following indexes:
> > >
> > > primary(A,B,C)
> > > index(B,C)
> > > index(C)
> > >
> > > That is incorrect. I would eventually like to
> > change
> > > it, but I'm starting small.
> > >
> > > --josef
> >
> > Why do you consider this incorrect? It will lead to
> > improved
> > performance, at least for mysql.
> >
> > john mcnally
> >
> > --
> > To unsubscribe, e-mail:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by josef richberg <sq...@yahoo.com>.
How can that lead to improved performance? I am
always providing all three keys so I will always use
the Primary Key index. How do the other two indexes
help? They take up space and need to be maintained,
so I see that as a hinderance.
--josef
--- John McNally <jm...@collab.net> wrote:
> josef richberg wrote:
> >
> ...
> >
> > If I designate 3 fields a "primary" (A,B,C),
> torque
> > will generate the following indexes:
> >
> > primary(A,B,C)
> > index(B,C)
> > index(C)
> >
> > That is incorrect. I would eventually like to
> change
> > it, but I'm starting small.
> >
> > --josef
>
> Why do you consider this incorrect? It will lead to
> improved
> performance, at least for mysql.
>
> john mcnally
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by John McNally <jm...@collab.net>.
josef richberg wrote:
>
...
>
> If I designate 3 fields a "primary" (A,B,C), torque
> will generate the following indexes:
>
> primary(A,B,C)
> index(B,C)
> index(C)
>
> That is incorrect. I would eventually like to change
> it, but I'm starting small.
>
> --josef
Why do you consider this incorrect? It will lead to improved
performance, at least for mysql.
john mcnally
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by josef richberg <sq...@yahoo.com>.
You are correct it is not, but it is a valid MySQL
datatype. The addition of the datatype is a small
part of the issue. Torque also generates additional
indexes where it shouldn't and that can lead to poor
db performance.
If I designate 3 fields a "primary" (A,B,C), torque
will generate the following indexes:
primary(A,B,C)
index(B,C)
index(C)
That is incorrect. I would eventually like to change
it, but I'm starting small.
--josef
--- "Frank W. Nolden" <fr...@maxware.nl> wrote:
> MEDIUMINT is not defined in db.props file by
> default. You cannot just add new keywords in this
> file. They have to be understood by the generation
> angine as well.
> You can change the mapping from the already
> available keywords, e.g. change the BIGINT to INT
> (don't why you would like to do this, but it is an
> example). I would not advice you to change the
> mappings if you are using the latest db.props file,
> since they are adapted best to the various RDBMSs.
>
> ----- Original Message -----
> From: Colm McCartan
> To: Turbine Users List
> Sent: Thursday, December 13, 2001 11:02
> Subject: Re: adding new types to Torque..mysql
>
>
>
>
> josef richberg wrote:
>
> > I'm looking to adjust the way the Mysql schema
> is
> > created using torque. I am loooking at adding
> some
> > keywords and modifiying the resultant .sql file.
> I
> > looked at adding a keyword to
> >
>
tdk/share/conf/torque/templates/sql/base/mysql/db.props.
> > The line I added was:
> > MEDIUMINT=MEDIUMINT
> > When I try to use it in type="MEDIUMINT", I get
> the
> > following error(in velocity log):
> >
> > "Method get threw exception for reference
> $dbprops in
> > /sql/base/mysql/columns.vm at [2,20]"
> >
> > That line is "#set ( $type =
> $dbprops.get($col.Type)
> > )"
> >
> > Shouldn't this pick up the column type from the
> > db.props file?
> >
> > --josef
>
>
>
> I tripped over the same thing - you want to change
> db.props in
>
>
webappname/WEB-INF/build/bin/torque/templates/sql/base
> rather than in
> the tdk-wide share (or else, make sure you do a
> top-level ant build
> after editing the tdk-wide copy)
>
> Good luck,
> colm
>
>
>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by "Frank W. Nolden" <fr...@maxware.nl>.
MEDIUMINT is not defined in db.props file by default. You cannot just add new keywords in this file. They have to be understood by the generation angine as well.
You can change the mapping from the already available keywords, e.g. change the BIGINT to INT (don't why you would like to do this, but it is an example). I would not advice you to change the mappings if you are using the latest db.props file, since they are adapted best to the various RDBMSs.
----- Original Message -----
From: Colm McCartan
To: Turbine Users List
Sent: Thursday, December 13, 2001 11:02
Subject: Re: adding new types to Torque..mysql
josef richberg wrote:
> I'm looking to adjust the way the Mysql schema is
> created using torque. I am loooking at adding some
> keywords and modifiying the resultant .sql file. I
> looked at adding a keyword to
> tdk/share/conf/torque/templates/sql/base/mysql/db.props.
> The line I added was:
> MEDIUMINT=MEDIUMINT
> When I try to use it in type="MEDIUMINT", I get the
> following error(in velocity log):
>
> "Method get threw exception for reference $dbprops in
> /sql/base/mysql/columns.vm at [2,20]"
>
> That line is "#set ( $type = $dbprops.get($col.Type)
> )"
>
> Shouldn't this pick up the column type from the
> db.props file?
>
> --josef
I tripped over the same thing - you want to change db.props in
webappname/WEB-INF/build/bin/torque/templates/sql/base rather than in
the tdk-wide share (or else, make sure you do a top-level ant build
after editing the tdk-wide copy)
Good luck,
colm
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by josef richberg <sq...@yahoo.com>.
Colm,
I tried what you suggested. I placed my change:
"MEDIUMINT = MEDIUMINT" in the
\tdk\webapps\joeapp\WEB-INF\build\bin\torque\templates\sql\base\mysql\db.props
when I then say type="MEDIUMINT" and to "ant init"
from the \tdk\webapps\joeapp\WEB-INF\build directory,
I get the same error.
--josef
--- Colm McCartan <co...@owl.co.uk> wrote:
>
>
> josef richberg wrote:
>
> > I'm looking to adjust the way the Mysql schema is
> > created using torque. I am loooking at adding
> some
> > keywords and modifiying the resultant .sql file.
> I
> > looked at adding a keyword to
> >
>
tdk/share/conf/torque/templates/sql/base/mysql/db.props.
> > The line I added was:
> > MEDIUMINT=MEDIUMINT
> > When I try to use it in type="MEDIUMINT", I get
> the
> > following error(in velocity log):
> >
> > "Method get threw exception for reference $dbprops
> in
> > /sql/base/mysql/columns.vm at [2,20]"
> >
> > That line is "#set ( $type =
> $dbprops.get($col.Type)
> > )"
> >
> > Shouldn't this pick up the column type from the
> > db.props file?
> >
> > --josef
>
>
>
> I tripped over the same thing - you want to change
> db.props in
>
webappname/WEB-INF/build/bin/torque/templates/sql/base
> rather than in
> the tdk-wide share (or else, make sure you do a
> top-level ant build
> after editing the tdk-wide copy)
>
> Good luck,
> colm
>
>
>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: adding new types to Torque..mysql
Posted by Colm McCartan <co...@owl.co.uk>.
josef richberg wrote:
> I'm looking to adjust the way the Mysql schema is
> created using torque. I am loooking at adding some
> keywords and modifiying the resultant .sql file. I
> looked at adding a keyword to
> tdk/share/conf/torque/templates/sql/base/mysql/db.props.
> The line I added was:
> MEDIUMINT=MEDIUMINT
> When I try to use it in type="MEDIUMINT", I get the
> following error(in velocity log):
>
> "Method get threw exception for reference $dbprops in
> /sql/base/mysql/columns.vm at [2,20]"
>
> That line is "#set ( $type = $dbprops.get($col.Type)
> )"
>
> Shouldn't this pick up the column type from the
> db.props file?
>
> --josef
I tripped over the same thing - you want to change db.props in
webappname/WEB-INF/build/bin/torque/templates/sql/base rather than in
the tdk-wide share (or else, make sure you do a top-level ant build
after editing the tdk-wide copy)
Good luck,
colm
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>