[sr-dev] git:master: lib/srdb1/schema: Updated XCAP indices to fix MySQL primary key issue

Module: sip-router Branch: master Commit: 45a9d6089a3d03d64f7648a2682ef18f5149171f URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=45a9d60… Author: Peter Dunkley &lt;peter.dunkley(a)crocodile-rcs.com&gt; Committer: Peter Dunkley &lt;peter.dunkley(a)crocodile-rcs.com&gt; Date: Tue Mar 20 16:47:03 2012 +0000 lib/srdb1/schema: Updated XCAP indices to fix MySQL primary key issue - Made id primary key again - Made doc_uri a unique index (constraint) - Removed unique constraint from other indices as no longer needed --- lib/srdb1/schema/pr_xcap.xml | 10 ++++++---- utils/kamctl/db_sqlite/presence-create.sql | 12 +++++++----- utils/kamctl/mysql/presence-create.sql | 12 +++++++----- utils/kamctl/oracle/presence-create.sql | 12 +++++++----- utils/kamctl/postgres/presence-create.sql | 12 +++++++----- 5 files changed, 34 insertions(+), 24 deletions(-) diff --git a/lib/srdb1/schema/pr_xcap.xml b/lib/srdb1/schema/pr_xcap.xml index ec6c126..e0e42df 100644 --- a/lib/srdb1/schema/pr_xcap.xml +++ b/lib/srdb1/schema/pr_xcap.xml @@ -23,6 +23,7 @@ <autoincrement/> <type db="dbtext">int,auto</type> <description>Unique ID</description> + <primary/> </column> <column id="username"> @@ -73,7 +74,6 @@ <type>string</type> <size>&xcap_uri_len;</size> <description>Document uri</description> - <primary/> </column> <column id="port"> @@ -84,11 +84,15 @@ </column> <index> + <name>doc_uri_idx</name> + <unique/> + </index> + + <index> <name>account_doc_type_idx</name> <colref linkend="username"/> <colref linkend="domain"/> <colref linkend="doc_type"/> - <unique/> </index> <index> @@ -97,7 +101,6 @@ <colref linkend="domain"/> <colref linkend="doc_type"/> <colref linkend="doc_uri"/> - <unique/> </index> <index> @@ -105,6 +108,5 @@ <colref linkend="username"/> <colref linkend="domain"/> <colref linkend="doc_uri"/> - <unique/> </index> </table> diff --git a/utils/kamctl/db_sqlite/presence-create.sql b/utils/kamctl/db_sqlite/presence-create.sql index 8d23108..96ba84e 100644 --- a/utils/kamctl/db_sqlite/presence-create.sql +++ b/utils/kamctl/db_sqlite/presence-create.sql @@ -59,20 +59,22 @@ CREATE TABLE watchers ( INSERT INTO version (table_name, table_version) values ('xcap','4'); CREATE TABLE xcap ( - id INTEGER NOT NULL, + id INTEGER PRIMARY KEY NOT NULL, username VARCHAR(64) NOT NULL, domain VARCHAR(64) NOT NULL, doc BYTEA NOT NULL, doc_type INTEGER NOT NULL, etag VARCHAR(64) NOT NULL, source INTEGER NOT NULL, - doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, + doc_uri VARCHAR(255) NOT NULL, port INTEGER NOT NULL, - CONSTRAINT xcap_account_doc_type_idx UNIQUE (username, domain, doc_type), - CONSTRAINT xcap_account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), - CONSTRAINT xcap_account_doc_uri_idx UNIQUE (username, domain, doc_uri) + CONSTRAINT xcap_doc_uri_idx UNIQUE () ); +CREATE INDEX xcap_account_doc_type_idx ON xcap (username, domain, doc_type); +CREATE INDEX xcap_account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri); +CREATE INDEX xcap_account_doc_uri_idx ON xcap (username, domain, doc_uri); + INSERT INTO version (table_name, table_version) values ('pua','7'); CREATE TABLE pua ( id INTEGER PRIMARY KEY NOT NULL, diff --git a/utils/kamctl/mysql/presence-create.sql b/utils/kamctl/mysql/presence-create.sql index 6395738..b052448 100644 --- a/utils/kamctl/mysql/presence-create.sql +++ b/utils/kamctl/mysql/presence-create.sql @@ -59,20 +59,22 @@ CREATE TABLE watchers ( INSERT INTO version (table_name, table_version) values ('xcap','4'); CREATE TABLE xcap ( - id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, + id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, username VARCHAR(64) NOT NULL, domain VARCHAR(64) NOT NULL, doc MEDIUMBLOB NOT NULL, doc_type INT(11) NOT NULL, etag VARCHAR(64) NOT NULL, source INT(11) NOT NULL, - doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, + doc_uri VARCHAR(255) NOT NULL, port INT(11) NOT NULL, - CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), - CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), - CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) + CONSTRAINT doc_uri_idx UNIQUE () ) ENGINE=MyISAM; +CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type); +CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri); +CREATE INDEX account_doc_uri_idx ON xcap (username, domain, doc_uri); + INSERT INTO version (table_name, table_version) values ('pua','7'); CREATE TABLE pua ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, diff --git a/utils/kamctl/oracle/presence-create.sql b/utils/kamctl/oracle/presence-create.sql index 92403e5..52eaa48 100644 --- a/utils/kamctl/oracle/presence-create.sql +++ b/utils/kamctl/oracle/presence-create.sql @@ -83,18 +83,16 @@ BEGIN map2users('watchers'); END; / INSERT INTO version (table_name, table_version) values ('xcap','4'); CREATE TABLE xcap ( - id NUMBER(10), + id NUMBER(10) PRIMARY KEY, username VARCHAR2(64), domain VARCHAR2(64), doc BLOB, doc_type NUMBER(10), etag VARCHAR2(64), source NUMBER(10), - doc_uri VARCHAR2(255) PRIMARY KEY, + doc_uri VARCHAR2(255), port NUMBER(10), - CONSTRAINT xcap_account_doc_type_idx UNIQUE (username, domain, doc_type), - CONSTRAINT xcap_account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), - CONSTRAINT xcap_account_doc_uri_idx UNIQUE (username, domain, doc_uri) + CONSTRAINT xcap_doc_uri_idx UNIQUE () ); CREATE OR REPLACE TRIGGER xcap_tr @@ -105,6 +103,10 @@ END xcap_tr; / BEGIN map2users('xcap'); END; / +CREATE INDEX xcap_account_doc_type_idx ON xcap (username, domain, doc_type); +CREATE INDEX xcap_account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri); +CREATE INDEX xcap_account_doc_uri_idx ON xcap (username, domain, doc_uri); + INSERT INTO version (table_name, table_version) values ('pua','7'); CREATE TABLE pua ( id NUMBER(10) PRIMARY KEY, diff --git a/utils/kamctl/postgres/presence-create.sql b/utils/kamctl/postgres/presence-create.sql index 57b4451..c960bce 100644 --- a/utils/kamctl/postgres/presence-create.sql +++ b/utils/kamctl/postgres/presence-create.sql @@ -59,20 +59,22 @@ CREATE TABLE watchers ( INSERT INTO version (table_name, table_version) values ('xcap','4'); CREATE TABLE xcap ( - id SERIAL NOT NULL, + id SERIAL PRIMARY KEY NOT NULL, username VARCHAR(64) NOT NULL, domain VARCHAR(64) NOT NULL, doc BYTEA NOT NULL, doc_type INTEGER NOT NULL, etag VARCHAR(64) NOT NULL, source INTEGER NOT NULL, - doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, + doc_uri VARCHAR(255) NOT NULL, port INTEGER NOT NULL, - CONSTRAINT xcap_account_doc_type_idx UNIQUE (username, domain, doc_type), - CONSTRAINT xcap_account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), - CONSTRAINT xcap_account_doc_uri_idx UNIQUE (username, domain, doc_uri) + CONSTRAINT xcap_doc_uri_idx UNIQUE () ); +CREATE INDEX xcap_account_doc_type_idx ON xcap (username, domain, doc_type); +CREATE INDEX xcap_account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri); +CREATE INDEX xcap_account_doc_uri_idx ON xcap (username, domain, doc_uri); + INSERT INTO version (table_name, table_version) values ('pua','7'); CREATE TABLE pua ( id SERIAL PRIMARY KEY NOT NULL,