[OpenSER-Devel] Defining MySQL foreign key constraint

[Henning Westerholt]

On Thursday 12 July 2007, you wrote:
> 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">
> [..]
> </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.

Hello Mircea,

this error is actually a feature, to prevent you from specifying only a type 
for one database, and forget about the others.. ;-)

But for your purposes it should work if you add two "type" defines for 
postgresql and dbtext, or uncomment the databases you don't need in the 
Makefile.

E.g.:

<type db="mysql">......</type>
<type db="postgres"></type>
<type db="dbtext">invalid</type>
<null/>

This way you will get an empty column in postgresql, and a actual but not used 
column for dbtext. 

> 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).

This is fine for me. Lets wait a little bit if somebody object against the new 
syntax, then i'll do this change next week.

Cheers,

Henning