[sr-dev] git:master: lib/srdb1: Updated constraints and indices for some of the presence tables
Peter Dunkley
peter.dunkley at crocodile-rcs.com
Mon Mar 12 23:01:47 CET 2012
Module: sip-router
Branch: master
Commit: 419e26078bb9e8f67e4e613c21db3feee418c8a5
URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=419e26078bb9e8f67e4e613c21db3feee418c8a5
Author: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Committer: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
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)
);
More information about the sr-dev
mailing list