[Kamailio-Devel] another db schema generation problem

[Dan Pascu]

It appears that the way indexes are generated has changed. Previously they 
were intergrated in the table creation, now they are created after the 
table is created. This breaks foreign key constrains, which no longer 
find the index to use.

In 1.3:

CREATE TABLE grp (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    subscriber_id INT(10) UNSIGNED DEFAULT NULL,
    username VARCHAR(64) NOT NULL DEFAULT '',
    domain VARCHAR(64) NOT NULL DEFAULT '',
    grp VARCHAR(64) NOT NULL DEFAULT '',
    last_modified TIMESTAMP NOT NULL,
     CONSTRAINT grp_subscriber_id_exists FOREIGN KEY (subscriber_id) 
REFERENCES subscriber (id) ON DELETE CASCADE,
    UNIQUE KEY account_group_idx (username, domain, grp),
    KEY grp_subscriber_id_exists (subscriber_id)
) ENGINE=InnoDB;


In 1.4:

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 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;

CREATE INDEX grp_subscriber_id_exists ON grp (subscriber_id);


-- 
Dan