Hello Henning,
1. Yes, this worked, however I saw a problem (maybe I'm doing something
wrong):
I have the following XML description of my table:
<table id="my_table" xmlns:db="http://docbook.org/ns/docbook";>
<name>my_table</name>
<version>2</version>
<type db="mysql">&MYSQL_TABLE_TYPE;</type>
....
<index>
<name>diversion_subscriber_id_exists</name>
<colref linkend="subscriber_id"/>
</index>
<column>
<null/>
<type db="mysql">CONSTRAINT `my_table_subscriber_id_exists`
FOREIGN KEY
(`subscriber_id`) REFERENCES `subscriber` (`id`) ON DELETE CASCADE
</type>
</column>
</table>
The sql script for MySQL is generated correctly, however I receive this
error for postgres and dbtext:
ERROR: Table: my_table, column: - unsupported column type: CONSTRAINT
`my_table_subscriber_id_exists` FOREIGN KEY (`subscriber_id`) REFERENCES
`subscriber` (`id`) ON DELETE CASCADE.
My impression was that the column should have been ignored by postgres
and dbtext, because of the db="mysql" attribute.
2. This second issue is not related to the first one:
The MySQL tables are created with Type=MyISAM|InnoDB, instead of
ENGINE=MyISAM|InnoDB, the result being a warning from MySQL:
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use
'ENGINE=storage_engine' instead
MySQL documentation says:
The ENGINE and TYPE options specify the storage engine for the table.
ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is
the preferred option name as of those versions, and TYPE has become
deprecated. TYPE is supported throughout the 4.x series, but likely will
be removed in the future.
so maybe we could also change TYPE to ENGINE, if everybody is fine with
it (there aren't people who use older versions of MySQL).
Thanks.
Henning Westerholt wrote:
On Wednesday 11 July 2007, Mircea Amarascu wrote:
Hello,
I've written an OpenSER module that uses tables with the following
structure:
CREATE TABLE `my_table` (
[..]
CONSTRAINT `my_table_subscriber_id_exists` FOREIGN KEY
(`subscriber_id`) REFERENCES `subscriber` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
they link to the subscriber table using a foreign key constraint.
I'd like to ask how can I express this structure in DB schema's XML
format, because I haven't found any example of something similar. Can
you give me an answer, or point me to the appropriate documentation or
references ?
Hello Mircea,
foreign keys constraints are unfortunaly not supported at the moment. Most
people using MyISAM because of performance reasons, and there are also not
available in dbtext. In oder to support this you could extend the XSL scripts
to generate this statements.
You could use the following statement in your xml definition to create the
table with CONSTRAINTs:
column>
<type db="mysql">CONSTRAINT `my_table_subscriber_id_exists` FOREIGN KEY
(`subscriber_id`) REFERENCES `subscriber` (`id`) ON DELETE CASCADE</type>
</column>
This uses the 'standard' column definition and will simply copy the given SQL
code into the generated table.
Does this work for you?
Henning
_______________________________________________
Devel mailing list
Devel@openser.org
http://openser.org/cgi-bin/mailman/listinfo/devel
