[Kamailio-Devel] db schema generation problems

[Henning Westerholt]

On Thursday 31 July 2008, Dan Pascu wrote:
> I found 2 issues when generating db schemas. They were not present in 1.3,
> and they seem to be related to oracle. I do not know if this is the
> trigger, or some other changes were made to the schema generation, but
> the issues are below:
>
> 1. I have this definition for the last_modified column in the grp table:

>         <type db="mysql">TIMESTAMP</type>

> Basically I added the <type db="mysql">TIMESTAMP</type> to use TIMESTAMP
> instead of DATETIME with mysql. However, the default value for the oracle
> database is used for mysql as well, even though I didn't ask for a
> default value with mysql (or the other databases):
>
>     last_modified TIMESTAMP DEFAULT 'to_date('0000-00-00
> 00:00:00','yyyy-mm-dd hh24:mi:ss')' NOT NULL,

Hello Dan,

i've tried to reproduce the problem you reported. On my system it seems to work:

Index: db/schema/grp.xml
===================================================================
--- db/schema/grp.xml   (revision 4532)
+++ db/schema/grp.xml   (working copy)
@@ -56,6 +56,7 @@
     <column>
         <name>last_modified</name>
         <type>datetime</type>
+        <type db="mysql">TIMESTAMP</type>
         <default>&DEFAULT_DATETIME;</default>
         <default db="oracle">to_date('&DEFAULT_DATETIME;','yyyy-mm-dd hh24:mi:ss')</default>
         <description>Date and time when this record was last modified.</description>
Index: scripts/mysql/group-create.sql
===================================================================
--- scripts/mysql/group-create.sql      (revision 4532)
+++ scripts/mysql/group-create.sql      (working copy)
@@ -4,7 +4,7 @@
     username VARCHAR(64) DEFAULT '' NOT NULL,
     domain VARCHAR(64) DEFAULT '' NOT NULL,
     grp VARCHAR(64) DEFAULT '' NOT NULL,
-    last_modified DATETIME DEFAULT '1900-01-01 00:00:01' NOT NULL,
+    last_modified TIMESTAMP DEFAULT '1900-01-01 00:00:01' NOT NULL,
     CONSTRAINT account_group_idx UNIQUE (username, domain, grp)
 ) ENGINE=MyISAM;

So i'm not sure why this don't work on your system. You can send me the xml
file in question if you like.

> 2. I added some foreign key constraints on some tables using something
> like below:
>
> i.e. it tries to use the definition for the mysql database. All other
> databases (dbtext, db_berkeley) do not have this issue

There was indeed a bug in the oracle XSL script, fixed now.

Thanks for the report,

Henning