[Kamailio-Devel] db schema generation problems

[Dan Pascu]

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:

    <column>
        <name>last_modified</name>
        <type>datetime</type>
        <type db="mysql">TIMESTAMP</type>
        <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>
    </column>

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

CREATE TABLE grp (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    subscriber_id INT(10) UNSIGNED DEFAULT NULL,
    username VARCHAR(64) DEFAULT '' NOT NULL,
    domain VARCHAR(64) DEFAULT '' NOT NULL,
    grp VARCHAR(64) DEFAULT '' NOT NULL,
    last_modified TIMESTAMP DEFAULT 'to_date('0000-00-00 
00:00:00','yyyy-mm-dd hh24:mi:ss')' NOT NULL,
     CONSTRAINT grp_subscriber_id_exists FOREIGN KEY (subscriber_id) 
REFERENCES subscriber (id) ON DELETE CASCADE,
    CONSTRAINT account_group_idx UNIQUE (username, domain, grp)
) ENGINE=InnoDB;


2. I added some foreign key constraints on some tables using something 
like below:

    <column>
        <type db="mysql">CONSTRAINT grp_subscriber_id_exists
            FOREIGN KEY (subscriber_id) REFERENCES subscriber (id) ON 
DELETE CASCADE
        </type>
        <type db="postgres">FOREIGN KEY (subscriber_id) REFERENCES 
subscriber(id) ON DELETE CASCADE</type>
        <type db="oracle">invalid</type>
        <type db="dbtext">invalid</type>
        <type db="db_berkeley">invalid</type>
        <null/>
    </column>

Now even though I explicitly declared that this as invalid for oracle, 
when the db schema for oracle is generated it gives this error:

ERROR: Table: grp, column:  - unsupported column type: CONSTRAINT 
grp_subscriber_id_exists FOREIGN KEY (subscriber_id) REFERENCES 
subscriber (id) ON DELETE CASCADE.

i.e. it tries to use the definition for the mysql database. All other 
databases (dbtext, db_berkeley) do not have this issue

-- 
Dan