[sr-dev] git:master: lib/srdb1/schema: Updated XCAP indices to fix MySQL primary key issue
Peter Dunkley
peter.dunkley at crocodile-rcs.com
Tue Mar 20 17:48:19 CET 2012
Module: sip-router
Branch: master
Commit: 45a9d6089a3d03d64f7648a2682ef18f5149171f
URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=45a9d6089a3d03d64f7648a2682ef18f5149171f
Author: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Committer: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
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,
More information about the sr-dev
mailing list