[sr-dev] git:master: modules_k/presence Fixed indexes in SQL schemas

Module: sip-router Branch: master Commit: 652f08a52fbc42ab413015ff4a3135d71538f71b URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=652f08a… Author: Anca Vamanu &lt;anca.vamanu(a)1and1.ro&gt; Committer: Anca Vamanu &lt;anca.vamanu(a)1and1.ro&gt; Date: Fri Mar 9 16:17:56 2012 +0200 modules_k/presence Fixed indexes in SQL schemas Added index on expires for both presentity and active_watchers tables Fixed unique index on active_watchers(no longer matched the operations done in the code) --- lib/srdb1/schema/pr_active_watchers.xml | 8 ++++++-- lib/srdb1/schema/pr_presentity.xml | 10 ++++++++-- utils/kamctl/db_sqlite/presence-create.sql | 6 +++++- utils/kamctl/mysql/presence-create.sql | 6 +++++- utils/kamctl/oracle/presence-create.sql | 6 +++++- utils/kamctl/postgres/presence-create.sql | 6 +++++- 6 files changed, 34 insertions(+), 8 deletions(-) diff --git a/lib/srdb1/schema/pr_active_watchers.xml b/lib/srdb1/schema/pr_active_watchers.xml index 35d697a..73d5e2f 100644 --- a/lib/srdb1/schema/pr_active_watchers.xml +++ b/lib/srdb1/schema/pr_active_watchers.xml @@ -128,7 +128,7 @@ <description>Record route</description> </column> - <column> + <column id="expires"> <name>expires</name> <type>int</type> <size>&expires_len;</size> @@ -174,11 +174,15 @@ <index> <name>active_watchers_idx</name> - <colref linkend="presentity_uri"/> <colref linkend="callid"/> <colref linkend="to_tag"/> <colref linkend="from_tag"/> <unique/> </index> + <index> + <name>active_watchers_expires</name> + <colref linkend="expires"/> + </index> + </table> diff --git a/lib/srdb1/schema/pr_presentity.xml b/lib/srdb1/schema/pr_presentity.xml index 188b9ce..f37b753 100644 --- a/lib/srdb1/schema/pr_presentity.xml +++ b/lib/srdb1/schema/pr_presentity.xml @@ -62,6 +62,7 @@ <type>int</type> <size>&expires_len;</size> <description>Expires</description> + <index/> </column> <column> @@ -75,8 +76,8 @@ <name>body</name> <type>binary</type> </column> - - <column id="sender"> + + <column id="sender"> <name>sender</name> <type>string</type> <size>&uri_len;</size> @@ -92,4 +93,9 @@ <unique/> </index> + <index> + <name>presentity_expires</name> + <colref linkend="expires"/> + </index> + </table> diff --git a/utils/kamctl/db_sqlite/presence-create.sql b/utils/kamctl/db_sqlite/presence-create.sql index c99f3a9..ac0e007 100644 --- a/utils/kamctl/db_sqlite/presence-create.sql +++ b/utils/kamctl/db_sqlite/presence-create.sql @@ -12,6 +12,8 @@ CREATE TABLE presentity ( CONSTRAINT presentity_presentity_idx UNIQUE (username, domain, event, etag) ); +CREATE INDEX presentity_presentity_expires ON presentity (expires); + INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( id INTEGER PRIMARY KEY NOT NULL, @@ -35,9 +37,11 @@ CREATE TABLE active_watchers ( version INTEGER DEFAULT 0 NOT NULL, socket_info VARCHAR(64) NOT NULL, local_contact VARCHAR(128) NOT NULL, - CONSTRAINT active_watchers_active_watchers_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT active_watchers_active_watchers_idx UNIQUE (callid, to_tag, from_tag) ); +CREATE INDEX active_watchers_active_watchers_expires ON active_watchers (expires); + INSERT INTO version (table_name, table_version) values ('watchers','3'); CREATE TABLE watchers ( id INTEGER PRIMARY KEY NOT NULL, diff --git a/utils/kamctl/mysql/presence-create.sql b/utils/kamctl/mysql/presence-create.sql index 577ad81..51644df 100644 --- a/utils/kamctl/mysql/presence-create.sql +++ b/utils/kamctl/mysql/presence-create.sql @@ -12,6 +12,8 @@ CREATE TABLE presentity ( CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag) ) ENGINE=MyISAM; +CREATE INDEX presentity_expires ON presentity (expires); + INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, @@ -35,9 +37,11 @@ CREATE TABLE active_watchers ( version INT(11) DEFAULT 0 NOT NULL, socket_info VARCHAR(64) NOT NULL, local_contact VARCHAR(128) NOT NULL, - CONSTRAINT active_watchers_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag) ) ENGINE=MyISAM; +CREATE INDEX active_watchers_expires ON active_watchers (expires); + INSERT INTO version (table_name, table_version) values ('watchers','3'); CREATE TABLE watchers ( 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 cd3dbf5..9ef4797 100644 --- a/utils/kamctl/oracle/presence-create.sql +++ b/utils/kamctl/oracle/presence-create.sql @@ -20,6 +20,8 @@ END presentity_tr; / BEGIN map2users('presentity'); END; / +CREATE INDEX presentity_presentity_expires ON presentity (expires); + INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( id NUMBER(10) PRIMARY KEY, @@ -43,7 +45,7 @@ CREATE TABLE active_watchers ( version NUMBER(10) DEFAULT 0 NOT NULL, socket_info VARCHAR2(64), local_contact VARCHAR2(128), - CONSTRAINT ORA_active_watchers_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT ORA_active_watchers_idx UNIQUE (callid, to_tag, from_tag) ); CREATE OR REPLACE TRIGGER active_watchers_tr @@ -54,6 +56,8 @@ END active_watchers_tr; / BEGIN map2users('active_watchers'); END; / +CREATE INDEX ORA_active_watchers_expires ON active_watchers (expires); + INSERT INTO version (table_name, table_version) values ('watchers','3'); CREATE TABLE watchers ( id NUMBER(10) PRIMARY KEY, diff --git a/utils/kamctl/postgres/presence-create.sql b/utils/kamctl/postgres/presence-create.sql index 104e5bf..6b61de6 100644 --- a/utils/kamctl/postgres/presence-create.sql +++ b/utils/kamctl/postgres/presence-create.sql @@ -12,6 +12,8 @@ CREATE TABLE presentity ( CONSTRAINT presentity_presentity_idx UNIQUE (username, domain, event, etag) ); +CREATE INDEX presentity_presentity_expires ON presentity (expires); + INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( id SERIAL PRIMARY KEY NOT NULL, @@ -35,9 +37,11 @@ CREATE TABLE active_watchers ( version INTEGER DEFAULT 0 NOT NULL, socket_info VARCHAR(64) NOT NULL, local_contact VARCHAR(128) NOT NULL, - CONSTRAINT active_watchers_active_watchers_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT active_watchers_active_watchers_idx UNIQUE (callid, to_tag, from_tag) ); +CREATE INDEX active_watchers_active_watchers_expires ON active_watchers (expires); + INSERT INTO version (table_name, table_version) values ('watchers','3'); CREATE TABLE watchers ( id SERIAL PRIMARY KEY NOT NULL,