[sr-dev] git:master: lib/srdb1: Updated constraints and indices for some of the presence tables

Module: sip-router Branch: master Commit: 419e26078bb9e8f67e4e613c21db3feee418c8a5 URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=419e260… Author: Peter Dunkley &lt;peter.dunkley(a)crocodile-rcs.com&gt; Committer: Peter Dunkley &lt;peter.dunkley(a)crocodile-rcs.com&gt; Date: Mon Mar 12 21:38:19 2012 +0000 lib/srdb1: Updated constraints and indices for some of the presence tables - Updated to match recent presence/pua/rls code changes - Added new index on username, domain, and event to the presentity table - Added unique constraint on etag, tuple_id, call_id, and from_tag to the pua table - Added indices on (pres_id), (call_id, from_tag, to_tag), and (pres_id, pres_uri, call_id, from_tag) to the pua table - Removed presentity uri from the rls_watcher table index --- lib/srdb1/schema/pr_presentity.xml | 7 +++++ lib/srdb1/schema/pr_pua.xml | 40 +++++++++++++++++++++++---- lib/srdb1/schema/rls_watchers.xml | 1 - utils/kamctl/db_sqlite/presence-create.sql | 8 +++++- utils/kamctl/db_sqlite/rls-create.sql | 2 +- utils/kamctl/mysql/presence-create.sql | 8 +++++- utils/kamctl/mysql/rls-create.sql | 2 +- utils/kamctl/oracle/presence-create.sql | 8 +++++- utils/kamctl/oracle/rls-create.sql | 2 +- utils/kamctl/postgres/presence-create.sql | 8 +++++- utils/kamctl/postgres/rls-create.sql | 2 +- 11 files changed, 73 insertions(+), 15 deletions(-) diff --git a/lib/srdb1/schema/pr_presentity.xml b/lib/srdb1/schema/pr_presentity.xml index f37b753..eb793ca 100644 --- a/lib/srdb1/schema/pr_presentity.xml +++ b/lib/srdb1/schema/pr_presentity.xml @@ -98,4 +98,11 @@ <colref linkend="expires"/> </index> + <index> + <name>account_idx</name> + <colref linkend="username"/> + <colref linkend="domain"/> + <colref linkend="event"/> + </index> + </table> diff --git a/lib/srdb1/schema/pr_pua.xml b/lib/srdb1/schema/pr_pua.xml index 79e5986..6a3079c 100644 --- a/lib/srdb1/schema/pr_pua.xml +++ b/lib/srdb1/schema/pr_pua.xml @@ -26,7 +26,7 @@ <description>Unique ID</description> </column> - <column> + <column id="pres_uri"> <name>pres_uri</name> <type>string</type> <size>&uri_len;</size> @@ -34,7 +34,7 @@ <natural/> </column> - <column> + <column id="pres_id"> <name>pres_id</name> <type>string</type> <size>255</size> @@ -70,14 +70,14 @@ <description>Flags</description> </column> - <column> + <column id="etag"> <name>etag</name> <type>string</type> <size>&domain_len;</size> <description>Etag</description> </column> - <column> + <column id="tuple_id"> <name>tuple_id</name> <type>string</type> <size>&domain_len;</size> @@ -92,7 +92,7 @@ <description>Watcher URI</description> </column> - <column> + <column id="call_id"> <name>call_id</name> <type>string</type> <size>&callid_len;</size> @@ -106,7 +106,7 @@ <description>To tag</description> </column> - <column> + <column id="from_tag"> <name>from_tag</name> <type>string</type> <size>&domain_len;</size> @@ -151,4 +151,32 @@ <description>Extra Headers</description> </column> + <index> + <name>pua_idx</name> + <colref linkend="etag"/> + <colref linkend="tuple_id"/> + <colref linkend="call_id"/> + <colref linkend="from_tag"/> + <unique/> + </index> + + <index> + <name>presid_idx</name> + <colref linkend="pres_id"/> + </index> + + <index> + <name>dialog_idx</name> + <colref linkend="call_id"/> + <colref linkend="from_tag"/> + <colref linkend="to_tag"/> + </index> + + <index> + <name>tmp_dlg_idx</name> + <colref linkend="pres_id"/> + <colref linkend="pres_uri"/> + <colref linkend="call_id"/> + <colref linkend="from_tag"/> + </index> </table> diff --git a/lib/srdb1/schema/rls_watchers.xml b/lib/srdb1/schema/rls_watchers.xml index 8362e77..3f67dc0 100644 --- a/lib/srdb1/schema/rls_watchers.xml +++ b/lib/srdb1/schema/rls_watchers.xml @@ -175,7 +175,6 @@ <index> <name>rls_watcher_idx</name> - <colref linkend="presentity_uri"/> <colref linkend="callid"/> <colref linkend="to_tag"/> <colref linkend="from_tag"/> diff --git a/utils/kamctl/db_sqlite/presence-create.sql b/utils/kamctl/db_sqlite/presence-create.sql index ac0e007..8a4a07c 100644 --- a/utils/kamctl/db_sqlite/presence-create.sql +++ b/utils/kamctl/db_sqlite/presence-create.sql @@ -13,6 +13,7 @@ CREATE TABLE presentity ( ); CREATE INDEX presentity_presentity_expires ON presentity (expires); +CREATE INDEX presentity_account_idx ON presentity (username, domain, event); INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( @@ -91,6 +92,11 @@ CREATE TABLE pua ( contact VARCHAR(128) NOT NULL, remote_contact VARCHAR(128) NOT NULL, version INTEGER NOT NULL, - extra_headers TEXT NOT NULL + extra_headers TEXT NOT NULL, + CONSTRAINT pua_pua_idx UNIQUE (etag, tuple_id, call_id, from_tag) ); +CREATE INDEX pua_presid_idx ON pua (pres_id); +CREATE INDEX pua_dialog_idx ON pua (call_id, from_tag, to_tag); +CREATE INDEX pua_tmp_dlg_idx ON pua (pres_id, pres_uri, call_id, from_tag); + diff --git a/utils/kamctl/db_sqlite/rls-create.sql b/utils/kamctl/db_sqlite/rls-create.sql index 4a05695..d98256e 100644 --- a/utils/kamctl/db_sqlite/rls-create.sql +++ b/utils/kamctl/db_sqlite/rls-create.sql @@ -37,6 +37,6 @@ CREATE TABLE rls_watchers ( version INTEGER DEFAULT 0 NOT NULL, socket_info VARCHAR(64) NOT NULL, local_contact VARCHAR(128) NOT NULL, - CONSTRAINT rls_watchers_rls_watcher_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT rls_watchers_rls_watcher_idx UNIQUE (callid, to_tag, from_tag) ); diff --git a/utils/kamctl/mysql/presence-create.sql b/utils/kamctl/mysql/presence-create.sql index 51644df..1ba1e05 100644 --- a/utils/kamctl/mysql/presence-create.sql +++ b/utils/kamctl/mysql/presence-create.sql @@ -13,6 +13,7 @@ CREATE TABLE presentity ( ) ENGINE=MyISAM; CREATE INDEX presentity_expires ON presentity (expires); +CREATE INDEX account_idx ON presentity (username, domain, event); INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( @@ -91,6 +92,11 @@ CREATE TABLE pua ( contact VARCHAR(128) NOT NULL, remote_contact VARCHAR(128) NOT NULL, version INT(11) NOT NULL, - extra_headers TEXT NOT NULL + extra_headers TEXT NOT NULL, + CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag) ) ENGINE=MyISAM; +CREATE INDEX presid_idx ON pua (pres_id); +CREATE INDEX dialog_idx ON pua (call_id, from_tag, to_tag); +CREATE INDEX tmp_dlg_idx ON pua (pres_id, pres_uri, call_id, from_tag); + diff --git a/utils/kamctl/mysql/rls-create.sql b/utils/kamctl/mysql/rls-create.sql index 02a6f75..28e3c60 100644 --- a/utils/kamctl/mysql/rls-create.sql +++ b/utils/kamctl/mysql/rls-create.sql @@ -37,6 +37,6 @@ CREATE TABLE rls_watchers ( version INT(11) DEFAULT 0 NOT NULL, socket_info VARCHAR(64) NOT NULL, local_contact VARCHAR(128) NOT NULL, - CONSTRAINT rls_watcher_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT rls_watcher_idx UNIQUE (callid, to_tag, from_tag) ) ENGINE=MyISAM; diff --git a/utils/kamctl/oracle/presence-create.sql b/utils/kamctl/oracle/presence-create.sql index 9ef4797..2e9c932 100644 --- a/utils/kamctl/oracle/presence-create.sql +++ b/utils/kamctl/oracle/presence-create.sql @@ -21,6 +21,7 @@ END presentity_tr; BEGIN map2users('presentity'); END; / CREATE INDEX presentity_presentity_expires ON presentity (expires); +CREATE INDEX presentity_account_idx ON presentity (username, domain, event); INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( @@ -123,7 +124,8 @@ CREATE TABLE pua ( contact VARCHAR2(128), remote_contact VARCHAR2(128), version NUMBER(10), - extra_headers CLOB + extra_headers CLOB, + CONSTRAINT pua_pua_idx UNIQUE (etag, tuple_id, call_id, from_tag) ); CREATE OR REPLACE TRIGGER pua_tr @@ -134,3 +136,7 @@ END pua_tr; / BEGIN map2users('pua'); END; / +CREATE INDEX pua_presid_idx ON pua (pres_id); +CREATE INDEX pua_dialog_idx ON pua (call_id, from_tag, to_tag); +CREATE INDEX pua_tmp_dlg_idx ON pua (pres_id, pres_uri, call_id, from_tag); + diff --git a/utils/kamctl/oracle/rls-create.sql b/utils/kamctl/oracle/rls-create.sql index 21b6f62..b4aac84 100644 --- a/utils/kamctl/oracle/rls-create.sql +++ b/utils/kamctl/oracle/rls-create.sql @@ -45,7 +45,7 @@ CREATE TABLE rls_watchers ( version NUMBER(10) DEFAULT 0 NOT NULL, socket_info VARCHAR2(64), local_contact VARCHAR2(128), - CONSTRAINT rls_watchers_rls_watcher_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT rls_watchers_rls_watcher_idx UNIQUE (callid, to_tag, from_tag) ); CREATE OR REPLACE TRIGGER rls_watchers_tr diff --git a/utils/kamctl/postgres/presence-create.sql b/utils/kamctl/postgres/presence-create.sql index 6b61de6..8be1dcc 100644 --- a/utils/kamctl/postgres/presence-create.sql +++ b/utils/kamctl/postgres/presence-create.sql @@ -13,6 +13,7 @@ CREATE TABLE presentity ( ); CREATE INDEX presentity_presentity_expires ON presentity (expires); +CREATE INDEX presentity_account_idx ON presentity (username, domain, event); INSERT INTO version (table_name, table_version) values ('active_watchers','9'); CREATE TABLE active_watchers ( @@ -91,6 +92,11 @@ CREATE TABLE pua ( contact VARCHAR(128) NOT NULL, remote_contact VARCHAR(128) NOT NULL, version INTEGER NOT NULL, - extra_headers TEXT NOT NULL + extra_headers TEXT NOT NULL, + CONSTRAINT pua_pua_idx UNIQUE (etag, tuple_id, call_id, from_tag) ); +CREATE INDEX pua_presid_idx ON pua (pres_id); +CREATE INDEX pua_dialog_idx ON pua (call_id, from_tag, to_tag); +CREATE INDEX pua_tmp_dlg_idx ON pua (pres_id, pres_uri, call_id, from_tag); + diff --git a/utils/kamctl/postgres/rls-create.sql b/utils/kamctl/postgres/rls-create.sql index 76735a2..99e97ca 100644 --- a/utils/kamctl/postgres/rls-create.sql +++ b/utils/kamctl/postgres/rls-create.sql @@ -37,6 +37,6 @@ CREATE TABLE rls_watchers ( version INTEGER DEFAULT 0 NOT NULL, socket_info VARCHAR(64) NOT NULL, local_contact VARCHAR(128) NOT NULL, - CONSTRAINT rls_watchers_rls_watcher_idx UNIQUE (presentity_uri, callid, to_tag, from_tag) + CONSTRAINT rls_watchers_rls_watcher_idx UNIQUE (callid, to_tag, from_tag) );