[sr-dev] git:master: modules_k/presence Fixed indexes in SQL schemas
Anca Vamanu
anca.vamanu at 1and1.ro
Fri Mar 9 15:27:22 CET 2012
Module: sip-router
Branch: master
Commit: 652f08a52fbc42ab413015ff4a3135d71538f71b
URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=652f08a52fbc42ab413015ff4a3135d71538f71b
Author: Anca Vamanu <anca.vamanu at 1and1.ro>
Committer: Anca Vamanu <anca.vamanu at 1and1.ro>
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,
More information about the sr-dev
mailing list