GitList
Browse code

kamctl: regenerate database creation scripts after scheme extensions

Henning Westerholt authored on 27/08/2019 20:00:12
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ b6763b8527
... ... 
@@ -7,7 +7,7 @@ CREATE TABLE `presentity` (
7 7 
     `expires` INT(11) NOT NULL,
8 8 
     `received_time` INT(11) NOT NULL,
9 9 
     `body` BLOB NOT NULL,
10 
-    `sender` VARCHAR(128) NOT NULL,
10 
+    `sender` VARCHAR(255) NOT NULL,
11 11 
     `priority` INT(11) DEFAULT 0 NOT NULL,
12 12 
     `ruid` VARCHAR(64),
13 13 
     CONSTRAINT presentity_idx UNIQUE (`username`, `domain`, `event`, `etag`),
... ... 
@@ -21,7 +21,7 @@ INSERT INTO version (table_name, table_version) values ('presentity','5');
21 21
22 22 
 CREATE TABLE `active_watchers` (
23 23 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
24 
-    `presentity_uri` VARCHAR(128) NOT NULL,
24 
+    `presentity_uri` VARCHAR(255) NOT NULL,
25 25 
     `watcher_username` VARCHAR(64) NOT NULL,
26 26 
     `watcher_domain` VARCHAR(64) NOT NULL,
27 27 
     `to_user` VARCHAR(64) NOT NULL,
... ... 
@@ -33,14 +33,14 @@ CREATE TABLE `active_watchers` (
33 33 
     `callid` VARCHAR(255) NOT NULL,
34 34 
     `local_cseq` INT(11) NOT NULL,
35 35 
     `remote_cseq` INT(11) NOT NULL,
36 
-    `contact` VARCHAR(128) NOT NULL,
36 
+    `contact` VARCHAR(255) NOT NULL,
37 37 
     `record_route` TEXT,
38 38 
     `expires` INT(11) NOT NULL,
39 39 
     `status` INT(11) DEFAULT 2 NOT NULL,
40 40 
     `reason` VARCHAR(64),
41 41 
     `version` INT(11) DEFAULT 0 NOT NULL,
42 42 
     `socket_info` VARCHAR(64) NOT NULL,
43 
-    `local_contact` VARCHAR(128) NOT NULL,
43 
+    `local_contact` VARCHAR(255) NOT NULL,
44 44 
     `from_user` VARCHAR(64) NOT NULL,
45 45 
     `from_domain` VARCHAR(64) NOT NULL,
46 46 
     `updated` INT(11) NOT NULL,
... ... 
@@ -59,7 +59,7 @@ INSERT INTO version (table_name, table_version) values ('active_watchers','12');
59 59
60 60 
 CREATE TABLE `watchers` (
61 61 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
62 
-    `presentity_uri` VARCHAR(128) NOT NULL,
62 
+    `presentity_uri` VARCHAR(255) NOT NULL,
63 63 
     `watcher_username` VARCHAR(64) NOT NULL,
64 64 
     `watcher_domain` VARCHAR(64) NOT NULL,
65 65 
     `event` VARCHAR(64) DEFAULT 'presence' NOT NULL,
... ... 
@@ -92,7 +92,7 @@ INSERT INTO version (table_name, table_version) values ('xcap','4');
92 92
93 93 
 CREATE TABLE `pua` (
94 94 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
95 
-    `pres_uri` VARCHAR(128) NOT NULL,
95 
+    `pres_uri` VARCHAR(255) NOT NULL,
96 96 
     `pres_id` VARCHAR(255) NOT NULL,
97 97 
     `event` INT(11) NOT NULL,
98 98 
     `expires` INT(11) NOT NULL,
... ... 
@@ -100,14 +100,14 @@ CREATE TABLE `pua` (
100 100 
     `flag` INT(11) NOT NULL,
101 101 
     `etag` VARCHAR(128) NOT NULL,
102 102 
     `tuple_id` VARCHAR(64),
103 
-    `watcher_uri` VARCHAR(128) NOT NULL,
103 
+    `watcher_uri` VARCHAR(255) NOT NULL,
104 104 
     `call_id` VARCHAR(255) NOT NULL,
105 105 
     `to_tag` VARCHAR(128) NOT NULL,
106 106 
     `from_tag` VARCHAR(128) NOT NULL,
107 107 
     `cseq` INT(11) NOT NULL,
108 108 
     `record_route` TEXT,
109 
-    `contact` VARCHAR(128) NOT NULL,
110 
-    `remote_contact` VARCHAR(128) NOT NULL,
109 
+    `contact` VARCHAR(255) NOT NULL,
110 
+    `remote_contact` VARCHAR(255) NOT NULL,
111 111 
     `version` INT(11) NOT NULL,
112 112 
     `extra_headers` TEXT NOT NULL,
113 113 
     CONSTRAINT pua_idx UNIQUE (`etag`, `tuple_id`, `call_id`, `from_tag`)
Browse code

kamctl: regenerated db schema files for from_tag and to_tag changes.

Øyvind Kolbu authored on 03/04/2019 12:36:38 • Henning Westerholt committed on 08/04/2019 19:21:33
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 98c352de18
... ... 
@@ -28,8 +28,8 @@ CREATE TABLE `active_watchers` (
28 28 
     `to_domain` VARCHAR(64) NOT NULL,
29 29 
     `event` VARCHAR(64) DEFAULT 'presence' NOT NULL,
30 30 
     `event_id` VARCHAR(64),
31 
-    `to_tag` VARCHAR(64) NOT NULL,
32 
-    `from_tag` VARCHAR(64) NOT NULL,
31 
+    `to_tag` VARCHAR(128) NOT NULL,
32 
+    `from_tag` VARCHAR(128) NOT NULL,
33 33 
     `callid` VARCHAR(255) NOT NULL,
34 34 
     `local_cseq` INT(11) NOT NULL,
35 35 
     `remote_cseq` INT(11) NOT NULL,
... ... 
@@ -102,8 +102,8 @@ CREATE TABLE `pua` (
102 102 
     `tuple_id` VARCHAR(64),
103 103 
     `watcher_uri` VARCHAR(128) NOT NULL,
104 104 
     `call_id` VARCHAR(255) NOT NULL,
105 
-    `to_tag` VARCHAR(64) NOT NULL,
106 
-    `from_tag` VARCHAR(64) NOT NULL,
105 
+    `to_tag` VARCHAR(128) NOT NULL,
106 
+    `from_tag` VARCHAR(128) NOT NULL,
107 107 
     `cseq` INT(11) NOT NULL,
108 108 
     `record_route` TEXT,
109 109 
     `contact` VARCHAR(128) NOT NULL,
Browse code

kamctl: new update to the sql creation scripts

Daniel-Constantin Mierla authored on 15/11/2018 08:37:04
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ aaabbc1adb
... ... 
@@ -77,7 +77,7 @@ CREATE TABLE `xcap` (
77 77 
     `domain` VARCHAR(64) NOT NULL,
78 78 
     `doc` MEDIUMBLOB NOT NULL,
79 79 
     `doc_type` INT(11) NOT NULL,
80 
-    `etag` VARCHAR(64) NOT NULL,
80 
+    `etag` VARCHAR(128) NOT NULL,
81 81 
     `source` INT(11) NOT NULL,
82 82 
     `doc_uri` VARCHAR(255) NOT NULL,
83 83 
     `port` INT(11) NOT NULL,
... ... 
@@ -98,7 +98,7 @@ CREATE TABLE `pua` (
98 98 
     `expires` INT(11) NOT NULL,
99 99 
     `desired_expires` INT(11) NOT NULL,
100 100 
     `flag` INT(11) NOT NULL,
101 
-    `etag` VARCHAR(64) NOT NULL,
101 
+    `etag` VARCHAR(128) NOT NULL,
102 102 
     `tuple_id` VARCHAR(64),
103 103 
     `watcher_uri` VARCHAR(128) NOT NULL,
104 104 
     `call_id` VARCHAR(255) NOT NULL,
Browse code

kamctl: regenerated the sql creation scripts

Daniel-Constantin Mierla authored on 15/11/2018 08:33:29
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 89fcea29b4
... ... 
@@ -3,7 +3,7 @@ CREATE TABLE `presentity` (
3 3 
     `username` VARCHAR(64) NOT NULL,
4 4 
     `domain` VARCHAR(64) NOT NULL,
5 5 
     `event` VARCHAR(64) NOT NULL,
6 
-    `etag` VARCHAR(64) NOT NULL,
6 
+    `etag` VARCHAR(128) NOT NULL,
7 7 
     `expires` INT(11) NOT NULL,
8 8 
     `received_time` INT(11) NOT NULL,
9 9 
     `body` BLOB NOT NULL,
Browse code

schema: allow null value for active_watchers user-agent column

some UAs not sending Userg-Agent header on SUBSCRIBE

lazedo authored on 07/02/2018 14:42:25
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 7653124c2d
... ... 
@@ -46,7 +46,7 @@ CREATE TABLE `active_watchers` (
46 46 
     `updated` INT(11) NOT NULL,
47 47 
     `updated_winfo` INT(11) NOT NULL,
48 48 
     `flags` INT(11) DEFAULT 0 NOT NULL,
49 
-    `user_agent` VARCHAR(255) DEFAULT '' NOT NULL,
49 
+    `user_agent` VARCHAR(255) DEFAULT '',
50 50 
     CONSTRAINT active_watchers_idx UNIQUE (`callid`, `to_tag`, `from_tag`)
51 51 
 );
52 52
Browse code

kamctl: regenerated db scripts to include presentity ruid column

Charles Chance authored on 24/01/2018 14:20:53
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ a4034c1d08
... ... 
@@ -9,13 +9,15 @@ CREATE TABLE `presentity` (
9 9 
     `body` BLOB NOT NULL,
10 10 
     `sender` VARCHAR(128) NOT NULL,
11 11 
     `priority` INT(11) DEFAULT 0 NOT NULL,
12 
-    CONSTRAINT presentity_idx UNIQUE (`username`, `domain`, `event`, `etag`)
12 
+    `ruid` VARCHAR(64),
13 
+    CONSTRAINT presentity_idx UNIQUE (`username`, `domain`, `event`, `etag`),
14 
+    CONSTRAINT ruid_idx UNIQUE (`ruid`)
13 15 
 );
14 16
15 17 
 CREATE INDEX presentity_expires ON presentity (`expires`);
16 18 
 CREATE INDEX account_idx ON presentity (`username`, `domain`, `event`);
17 19
18 
-INSERT INTO version (table_name, table_version) values ('presentity','4');
20 
+INSERT INTO version (table_name, table_version) values ('presentity','5');
19 21
20 22 
 CREATE TABLE `active_watchers` (
21 23 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
Browse code

kamctl: regenerated scripts to create database structure for presence

Daniel-Constantin Mierla authored on 09/12/2017 08:21:46
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 900f2a1559
... ... 
@@ -35,7 +35,7 @@ CREATE TABLE `active_watchers` (
35 35 
     `record_route` TEXT,
36 36 
     `expires` INT(11) NOT NULL,
37 37 
     `status` INT(11) DEFAULT 2 NOT NULL,
38 
-    `reason` VARCHAR(64) NOT NULL,
38 
+    `reason` VARCHAR(64),
39 39 
     `version` INT(11) DEFAULT 0 NOT NULL,
40 40 
     `socket_info` VARCHAR(64) NOT NULL,
41 41 
     `local_contact` VARCHAR(128) NOT NULL,
Browse code

presence: add flags and user_agent to schema

lazedo authored on 25/01/2016 13:19:20 • Luis Azedo committed on 31/01/2016 00:09:04
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ b1daeaca38
... ... 
@@ -43,6 +43,8 @@ CREATE TABLE `active_watchers` (
43 43 
     `from_domain` VARCHAR(64) NOT NULL,
44 44 
     `updated` INT(11) NOT NULL,
45 45 
     `updated_winfo` INT(11) NOT NULL,
46 
+    `flags` INT(11) DEFAULT 0 NOT NULL,
47 
+    `user_agent` VARCHAR(255) DEFAULT '' NOT NULL,
46 48 
     CONSTRAINT active_watchers_idx UNIQUE (`callid`, `to_tag`, `from_tag`)
47 49 
 );
48 50
... ... 
@@ -51,7 +53,7 @@ CREATE INDEX active_watchers_pres ON active_watchers (`presentity_uri`, `event`)
51 53 
 CREATE INDEX updated_idx ON active_watchers (`updated`);
52 54 
 CREATE INDEX updated_winfo_idx ON active_watchers (`updated_winfo`, `presentity_uri`);
53 55
54 
-INSERT INTO version (table_name, table_version) values ('active_watchers','11');
56 
+INSERT INTO version (table_name, table_version) values ('active_watchers','12');
55 57
56 58 
 CREATE TABLE `watchers` (
57 59 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
Browse code

kamctl: db sql files updated after xsl changes

- insert statement for version is after creation of the table

Daniel-Constantin Mierla authored on 04/12/2015 20:59:11
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 784f73b880
... ... 
@@ -1,4 +1,3 @@
1 
-INSERT INTO version (table_name, table_version) values ('presentity','4');
2 1 
 CREATE TABLE `presentity` (
3 2 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
4 3 
     `username` VARCHAR(64) NOT NULL,
... ... 
@@ -16,7 +15,8 @@ CREATE TABLE `presentity` (
16 15 
 CREATE INDEX presentity_expires ON presentity (`expires`);
17 16 
 CREATE INDEX account_idx ON presentity (`username`, `domain`, `event`);
18 17
19 
-INSERT INTO version (table_name, table_version) values ('active_watchers','11');
18 
+INSERT INTO version (table_name, table_version) values ('presentity','4');
19 
+
20 20 
 CREATE TABLE `active_watchers` (
21 21 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
22 22 
     `presentity_uri` VARCHAR(128) NOT NULL,
... ... 
@@ -51,7 +51,8 @@ CREATE INDEX active_watchers_pres ON active_watchers (`presentity_uri`, `event`)
51 51 
 CREATE INDEX updated_idx ON active_watchers (`updated`);
52 52 
 CREATE INDEX updated_winfo_idx ON active_watchers (`updated_winfo`, `presentity_uri`);
53 53
54 
-INSERT INTO version (table_name, table_version) values ('watchers','3');
54 
+INSERT INTO version (table_name, table_version) values ('active_watchers','11');
55 
+
55 56 
 CREATE TABLE `watchers` (
56 57 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
57 58 
     `presentity_uri` VARCHAR(128) NOT NULL,
... ... 
@@ -64,7 +65,8 @@ CREATE TABLE `watchers` (
64 65 
     CONSTRAINT watcher_idx UNIQUE (`presentity_uri`, `watcher_username`, `watcher_domain`, `event`)
65 66 
 );
66 67
67 
-INSERT INTO version (table_name, table_version) values ('xcap','4');
68 
+INSERT INTO version (table_name, table_version) values ('watchers','3');
69 
+
68 70 
 CREATE TABLE `xcap` (
69 71 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
70 72 
     `username` VARCHAR(64) NOT NULL,
... ... 
@@ -82,7 +84,8 @@ CREATE INDEX account_doc_type_idx ON xcap (`username`, `domain`, `doc_type`);
82 84 
 CREATE INDEX account_doc_type_uri_idx ON xcap (`username`, `domain`, `doc_type`, `doc_uri`);
83 85 
 CREATE INDEX account_doc_uri_idx ON xcap (`username`, `domain`, `doc_uri`);
84 86
85 
-INSERT INTO version (table_name, table_version) values ('pua','7');
87 
+INSERT INTO version (table_name, table_version) values ('xcap','4');
88 
+
86 89 
 CREATE TABLE `pua` (
87 90 
     `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
88 91 
     `pres_uri` VARCHAR(128) NOT NULL,
... ... 
@@ -111,3 +114,5 @@ CREATE INDEX dialog1_idx ON pua (`pres_id`, `pres_uri`);
111 114 
 CREATE INDEX dialog2_idx ON pua (`call_id`, `from_tag`);
112 115 
 CREATE INDEX record_idx ON pua (`pres_id`);
113 116
117 
+INSERT INTO version (table_name, table_version) values ('pua','7');
118 
+
Browse code

kamctl: regenerated mysql creation scripts

Daniel-Constantin Mierla authored on 13/05/2015 20:24:28
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 98f2b39450
... ... 
@@ -1,113 +1,113 @@
1 1 
 INSERT INTO version (table_name, table_version) values ('presentity','4');
2 
-CREATE TABLE presentity (
3 
-    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
4 
-    username VARCHAR(64) NOT NULL,
5 
-    domain VARCHAR(64) NOT NULL,
6 
-    event VARCHAR(64) NOT NULL,
7 
-    etag VARCHAR(64) NOT NULL,
8 
-    expires INT(11) NOT NULL,
9 
-    received_time INT(11) NOT NULL,
10 
-    body BLOB NOT NULL,
11 
-    sender VARCHAR(128) NOT NULL,
12 
-    priority INT(11) DEFAULT 0 NOT NULL,
13 
-    CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
2 
+CREATE TABLE `presentity` (
3 
+    `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
4 
+    `username` VARCHAR(64) NOT NULL,
5 
+    `domain` VARCHAR(64) NOT NULL,
6 
+    `event` VARCHAR(64) NOT NULL,
7 
+    `etag` VARCHAR(64) NOT NULL,
8 
+    `expires` INT(11) NOT NULL,
9 
+    `received_time` INT(11) NOT NULL,
10 
+    `body` BLOB NOT NULL,
11 
+    `sender` VARCHAR(128) NOT NULL,
12 
+    `priority` INT(11) DEFAULT 0 NOT NULL,
13 
+    CONSTRAINT presentity_idx UNIQUE (`username`, `domain`, `event`, `etag`)
14 14 
 );
15 15
16 
-CREATE INDEX presentity_expires ON presentity (expires);
17 
-CREATE INDEX account_idx ON presentity (username, domain, event);
16 
+CREATE INDEX presentity_expires ON presentity (`expires`);
17 
+CREATE INDEX account_idx ON presentity (`username`, `domain`, `event`);
18 18
19 19 
 INSERT INTO version (table_name, table_version) values ('active_watchers','11');
20 
-CREATE TABLE active_watchers (
21 
-    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
22 
-    presentity_uri VARCHAR(128) NOT NULL,
23 
-    watcher_username VARCHAR(64) NOT NULL,
24 
-    watcher_domain VARCHAR(64) NOT NULL,
25 
-    to_user VARCHAR(64) NOT NULL,
26 
-    to_domain VARCHAR(64) NOT NULL,
27 
-    event VARCHAR(64) DEFAULT 'presence' NOT NULL,
28 
-    event_id VARCHAR(64),
29 
-    to_tag VARCHAR(64) NOT NULL,
30 
-    from_tag VARCHAR(64) NOT NULL,
31 
-    callid VARCHAR(255) NOT NULL,
32 
-    local_cseq INT(11) NOT NULL,
33 
-    remote_cseq INT(11) NOT NULL,
34 
-    contact VARCHAR(128) NOT NULL,
35 
-    record_route TEXT,
36 
-    expires INT(11) NOT NULL,
37 
-    status INT(11) DEFAULT 2 NOT NULL,
38 
-    reason VARCHAR(64) NOT NULL,
39 
-    version INT(11) DEFAULT 0 NOT NULL,
40 
-    socket_info VARCHAR(64) NOT NULL,
41 
-    local_contact VARCHAR(128) NOT NULL,
42 
-    from_user VARCHAR(64) NOT NULL,
43 
-    from_domain VARCHAR(64) NOT NULL,
44 
-    updated INT(11) NOT NULL,
45 
-    updated_winfo INT(11) NOT NULL,
46 
-    CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
20 
+CREATE TABLE `active_watchers` (
21 
+    `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
22 
+    `presentity_uri` VARCHAR(128) NOT NULL,
23 
+    `watcher_username` VARCHAR(64) NOT NULL,
24 
+    `watcher_domain` VARCHAR(64) NOT NULL,
25 
+    `to_user` VARCHAR(64) NOT NULL,
26 
+    `to_domain` VARCHAR(64) NOT NULL,
27 
+    `event` VARCHAR(64) DEFAULT 'presence' NOT NULL,
28 
+    `event_id` VARCHAR(64),
29 
+    `to_tag` VARCHAR(64) NOT NULL,
30 
+    `from_tag` VARCHAR(64) NOT NULL,
31 
+    `callid` VARCHAR(255) NOT NULL,
32 
+    `local_cseq` INT(11) NOT NULL,
33 
+    `remote_cseq` INT(11) NOT NULL,
34 
+    `contact` VARCHAR(128) NOT NULL,
35 
+    `record_route` TEXT,
36 
+    `expires` INT(11) NOT NULL,
37 
+    `status` INT(11) DEFAULT 2 NOT NULL,
38 
+    `reason` VARCHAR(64) NOT NULL,
39 
+    `version` INT(11) DEFAULT 0 NOT NULL,
40 
+    `socket_info` VARCHAR(64) NOT NULL,
41 
+    `local_contact` VARCHAR(128) NOT NULL,
42 
+    `from_user` VARCHAR(64) NOT NULL,
43 
+    `from_domain` VARCHAR(64) NOT NULL,
44 
+    `updated` INT(11) NOT NULL,
45 
+    `updated_winfo` INT(11) NOT NULL,
46 
+    CONSTRAINT active_watchers_idx UNIQUE (`callid`, `to_tag`, `from_tag`)
47 47 
 );
48 48
49 
-CREATE INDEX active_watchers_expires ON active_watchers (expires);
50 
-CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri, event);
51 
-CREATE INDEX updated_idx ON active_watchers (updated);
52 
-CREATE INDEX updated_winfo_idx ON active_watchers (updated_winfo, presentity_uri);
49 
+CREATE INDEX active_watchers_expires ON active_watchers (`expires`);
50 
+CREATE INDEX active_watchers_pres ON active_watchers (`presentity_uri`, `event`);
51 
+CREATE INDEX updated_idx ON active_watchers (`updated`);
52 
+CREATE INDEX updated_winfo_idx ON active_watchers (`updated_winfo`, `presentity_uri`);
53 53
54 54 
 INSERT INTO version (table_name, table_version) values ('watchers','3');
55 
-CREATE TABLE watchers (
56 
-    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
57 
-    presentity_uri VARCHAR(128) NOT NULL,
58 
-    watcher_username VARCHAR(64) NOT NULL,
59 
-    watcher_domain VARCHAR(64) NOT NULL,
60 
-    event VARCHAR(64) DEFAULT 'presence' NOT NULL,
61 
-    status INT(11) NOT NULL,
62 
-    reason VARCHAR(64),
63 
-    inserted_time INT(11) NOT NULL,
64 
-    CONSTRAINT watcher_idx UNIQUE (presentity_uri, watcher_username, watcher_domain, event)
55 
+CREATE TABLE `watchers` (
56 
+    `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
57 
+    `presentity_uri` VARCHAR(128) NOT NULL,
58 
+    `watcher_username` VARCHAR(64) NOT NULL,
59 
+    `watcher_domain` VARCHAR(64) NOT NULL,
60 
+    `event` VARCHAR(64) DEFAULT 'presence' NOT NULL,
61 
+    `status` INT(11) NOT NULL,
62 
+    `reason` VARCHAR(64),
63 
+    `inserted_time` INT(11) NOT NULL,
64 
+    CONSTRAINT watcher_idx UNIQUE (`presentity_uri`, `watcher_username`, `watcher_domain`, `event`)
65 65 
 );
66 66
67 67 
 INSERT INTO version (table_name, table_version) values ('xcap','4');
68 
-CREATE TABLE xcap (
69 
-    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
70 
-    username VARCHAR(64) NOT NULL,
71 
-    domain VARCHAR(64) NOT NULL,
72 
-    doc MEDIUMBLOB NOT NULL,
73 
-    doc_type INT(11) NOT NULL,
74 
-    etag VARCHAR(64) NOT NULL,
75 
-    source INT(11) NOT NULL,
76 
-    doc_uri VARCHAR(255) NOT NULL,
77 
-    port INT(11) NOT NULL,
78 
-    CONSTRAINT doc_uri_idx UNIQUE (doc_uri)
68 
+CREATE TABLE `xcap` (
69 
+    `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
70 
+    `username` VARCHAR(64) NOT NULL,
71 
+    `domain` VARCHAR(64) NOT NULL,
72 
+    `doc` MEDIUMBLOB NOT NULL,
73 
+    `doc_type` INT(11) NOT NULL,
74 
+    `etag` VARCHAR(64) NOT NULL,
75 
+    `source` INT(11) NOT NULL,
76 
+    `doc_uri` VARCHAR(255) NOT NULL,
77 
+    `port` INT(11) NOT NULL,
78 
+    CONSTRAINT doc_uri_idx UNIQUE (`doc_uri`)
79 79 
 );
80 80
81 
-CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
82 
-CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri);
83 
-CREATE INDEX account_doc_uri_idx ON xcap (username, domain, doc_uri);
81 
+CREATE INDEX account_doc_type_idx ON xcap (`username`, `domain`, `doc_type`);
82 
+CREATE INDEX account_doc_type_uri_idx ON xcap (`username`, `domain`, `doc_type`, `doc_uri`);
83 
+CREATE INDEX account_doc_uri_idx ON xcap (`username`, `domain`, `doc_uri`);
84 84
85 85 
 INSERT INTO version (table_name, table_version) values ('pua','7');
86 
-CREATE TABLE pua (
87 
-    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
88 
-    pres_uri VARCHAR(128) NOT NULL,
89 
-    pres_id VARCHAR(255) NOT NULL,
90 
-    event INT(11) NOT NULL,
91 
-    expires INT(11) NOT NULL,
92 
-    desired_expires INT(11) NOT NULL,
93 
-    flag INT(11) NOT NULL,
94 
-    etag VARCHAR(64) NOT NULL,
95 
-    tuple_id VARCHAR(64),
96 
-    watcher_uri VARCHAR(128) NOT NULL,
97 
-    call_id VARCHAR(255) NOT NULL,
98 
-    to_tag VARCHAR(64) NOT NULL,
99 
-    from_tag VARCHAR(64) NOT NULL,
100 
-    cseq INT(11) NOT NULL,
101 
-    record_route TEXT,
102 
-    contact VARCHAR(128) NOT NULL,
103 
-    remote_contact VARCHAR(128) NOT NULL,
104 
-    version INT(11) NOT NULL,
105 
-    extra_headers TEXT NOT NULL,
106 
-    CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
86 
+CREATE TABLE `pua` (
87 
+    `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
88 
+    `pres_uri` VARCHAR(128) NOT NULL,
89 
+    `pres_id` VARCHAR(255) NOT NULL,
90 
+    `event` INT(11) NOT NULL,
91 
+    `expires` INT(11) NOT NULL,
92 
+    `desired_expires` INT(11) NOT NULL,
93 
+    `flag` INT(11) NOT NULL,
94 
+    `etag` VARCHAR(64) NOT NULL,
95 
+    `tuple_id` VARCHAR(64),
96 
+    `watcher_uri` VARCHAR(128) NOT NULL,
97 
+    `call_id` VARCHAR(255) NOT NULL,
98 
+    `to_tag` VARCHAR(64) NOT NULL,
99 
+    `from_tag` VARCHAR(64) NOT NULL,
100 
+    `cseq` INT(11) NOT NULL,
101 
+    `record_route` TEXT,
102 
+    `contact` VARCHAR(128) NOT NULL,
103 
+    `remote_contact` VARCHAR(128) NOT NULL,
104 
+    `version` INT(11) NOT NULL,
105 
+    `extra_headers` TEXT NOT NULL,
106 
+    CONSTRAINT pua_idx UNIQUE (`etag`, `tuple_id`, `call_id`, `from_tag`)
107 107 
 );
108 108
109 
-CREATE INDEX expires_idx ON pua (expires);
110 
-CREATE INDEX dialog1_idx ON pua (pres_id, pres_uri);
111 
-CREATE INDEX dialog2_idx ON pua (call_id, from_tag);
112 
-CREATE INDEX record_idx ON pua (pres_id);
109 
+CREATE INDEX expires_idx ON pua (`expires`);
110 
+CREATE INDEX dialog1_idx ON pua (`pres_id`, `pres_uri`);
111 
+CREATE INDEX dialog2_idx ON pua (`call_id`, `from_tag`);
112 
+CREATE INDEX record_idx ON pua (`pres_id`);
113 113
Browse code

kamctl: regenerated db schema - updates to sip_trace and presentity

Daniel-Constantin Mierla authored on 26/03/2015 18:57:12
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 97389e4e84
... ... 
@@ -1,4 +1,4 @@
1 
-INSERT INTO version (table_name, table_version) values ('presentity','3');
1 
+INSERT INTO version (table_name, table_version) values ('presentity','4');
2 2 
 CREATE TABLE presentity (
3 3 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
4 4 
     username VARCHAR(64) NOT NULL,
... ... 
@@ -9,6 +9,7 @@ CREATE TABLE presentity (
9 9 
     received_time INT(11) NOT NULL,
10 10 
     body BLOB NOT NULL,
11 11 
     sender VARCHAR(128) NOT NULL,
12 
+    priority INT(11) DEFAULT 0 NOT NULL,
12 13 
     CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
13 14 
 );
14 15
Browse code

DB scheme: remove default MySQL engine definition

* remove default MySQL engine definition from table definitions
* if nothing is specified then MySQL server will choose the default from its cfg
* if you like to specify it, change the MYSQL_TABLE_TYPE define in
lib/srdb1/schema/entities.xml and run "make dbschema" in the top level directory
* the stylesheet doesn't check the engine type, Kamailio supports MyISAM and InnoDB

Henning Westerholt authored on 29/05/2013 10:38:10
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ ffe8aa173c
... ... 
@@ -10,7 +10,7 @@ CREATE TABLE presentity (
10 10 
     body BLOB NOT NULL,
11 11 
     sender VARCHAR(128) NOT NULL,
12 12 
     CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
13 
-) ENGINE=MyISAM;
13 
+);
14 14
15 15 
 CREATE INDEX presentity_expires ON presentity (expires);
16 16 
 CREATE INDEX account_idx ON presentity (username, domain, event);
... ... 
@@ -43,7 +43,7 @@ CREATE TABLE active_watchers (
43 43 
     updated INT(11) NOT NULL,
44 44 
     updated_winfo INT(11) NOT NULL,
45 45 
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
46 
-) ENGINE=MyISAM;
46 
+);
47 47
48 48 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
49 49 
 CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri, event);
... ... 
@@ -61,7 +61,7 @@ CREATE TABLE watchers (
61 61 
     reason VARCHAR(64),
62 62 
     inserted_time INT(11) NOT NULL,
63 63 
     CONSTRAINT watcher_idx UNIQUE (presentity_uri, watcher_username, watcher_domain, event)
64 
-) ENGINE=MyISAM;
64 
+);
65 65
66 66 
 INSERT INTO version (table_name, table_version) values ('xcap','4');
67 67 
 CREATE TABLE xcap (
... ... 
@@ -75,7 +75,7 @@ CREATE TABLE xcap (
75 75 
     doc_uri VARCHAR(255) NOT NULL,
76 76 
     port INT(11) NOT NULL,
77 77 
     CONSTRAINT doc_uri_idx UNIQUE (doc_uri)
78 
-) ENGINE=MyISAM;
78 
+);
79 79
80 80 
 CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
81 81 
 CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri);
... ... 
@@ -103,7 +103,7 @@ CREATE TABLE pua (
103 103 
     version INT(11) NOT NULL,
104 104 
     extra_headers TEXT NOT NULL,
105 105 
     CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
106 
-) ENGINE=MyISAM;
106 
+);
107 107
108 108 
 CREATE INDEX expires_idx ON pua (expires);
109 109 
 CREATE INDEX dialog1_idx ON pua (pres_id, pres_uri);
Browse code

Revert "DB scheme: as discussed on sr-users list, change default mysql engine to InnoDB"

This reverts commit fa49fe070adadfff893b338f2a2d69776c043653.

Henning Westerholt authored on 29/05/2013 10:30:58
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 5aa71845f6
... ... 
@@ -10,7 +10,7 @@ CREATE TABLE presentity (
10 10 
     body BLOB NOT NULL,
11 11 
     sender VARCHAR(128) NOT NULL,
12 12 
     CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
13 
-) ENGINE=InnoDB;
13 
+) ENGINE=MyISAM;
14 14
15 15 
 CREATE INDEX presentity_expires ON presentity (expires);
16 16 
 CREATE INDEX account_idx ON presentity (username, domain, event);
... ... 
@@ -43,7 +43,7 @@ CREATE TABLE active_watchers (
43 43 
     updated INT(11) NOT NULL,
44 44 
     updated_winfo INT(11) NOT NULL,
45 45 
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
46 
-) ENGINE=InnoDB;
46 
+) ENGINE=MyISAM;
47 47
48 48 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
49 49 
 CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri, event);
... ... 
@@ -61,7 +61,7 @@ CREATE TABLE watchers (
61 61 
     reason VARCHAR(64),
62 62 
     inserted_time INT(11) NOT NULL,
63 63 
     CONSTRAINT watcher_idx UNIQUE (presentity_uri, watcher_username, watcher_domain, event)
64 
-) ENGINE=InnoDB;
64 
+) ENGINE=MyISAM;
65 65
66 66 
 INSERT INTO version (table_name, table_version) values ('xcap','4');
67 67 
 CREATE TABLE xcap (
... ... 
@@ -75,7 +75,7 @@ CREATE TABLE xcap (
75 75 
     doc_uri VARCHAR(255) NOT NULL,
76 76 
     port INT(11) NOT NULL,
77 77 
     CONSTRAINT doc_uri_idx UNIQUE (doc_uri)
78 
-) ENGINE=InnoDB;
78 
+) ENGINE=MyISAM;
79 79
80 80 
 CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
81 81 
 CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri);
... ... 
@@ -103,7 +103,7 @@ CREATE TABLE pua (
103 103 
     version INT(11) NOT NULL,
104 104 
     extra_headers TEXT NOT NULL,
105 105 
     CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
106 
-) ENGINE=InnoDB;
106 
+) ENGINE=MyISAM;
107 107
108 108 
 CREATE INDEX expires_idx ON pua (expires);
109 109 
 CREATE INDEX dialog1_idx ON pua (pres_id, pres_uri);
Browse code

DB scheme: as discussed on sr-users list, change default mysql engine to InnoDB

Henning Westerholt authored on 29/05/2013 09:49:59
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ fa49fe070a
... ... 
@@ -10,7 +10,7 @@ CREATE TABLE presentity (
10 10 
     body BLOB NOT NULL,
11 11 
     sender VARCHAR(128) NOT NULL,
12 12 
     CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
13 
-) ENGINE=MyISAM;
13 
+) ENGINE=InnoDB;
14 14
15 15 
 CREATE INDEX presentity_expires ON presentity (expires);
16 16 
 CREATE INDEX account_idx ON presentity (username, domain, event);
... ... 
@@ -43,7 +43,7 @@ CREATE TABLE active_watchers (
43 43 
     updated INT(11) NOT NULL,
44 44 
     updated_winfo INT(11) NOT NULL,
45 45 
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
46 
-) ENGINE=MyISAM;
46 
+) ENGINE=InnoDB;
47 47
48 48 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
49 49 
 CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri, event);
... ... 
@@ -61,7 +61,7 @@ CREATE TABLE watchers (
61 61 
     reason VARCHAR(64),
62 62 
     inserted_time INT(11) NOT NULL,
63 63 
     CONSTRAINT watcher_idx UNIQUE (presentity_uri, watcher_username, watcher_domain, event)
64 
-) ENGINE=MyISAM;
64 
+) ENGINE=InnoDB;
65 65
66 66 
 INSERT INTO version (table_name, table_version) values ('xcap','4');
67 67 
 CREATE TABLE xcap (
... ... 
@@ -75,7 +75,7 @@ CREATE TABLE xcap (
75 75 
     doc_uri VARCHAR(255) NOT NULL,
76 76 
     port INT(11) NOT NULL,
77 77 
     CONSTRAINT doc_uri_idx UNIQUE (doc_uri)
78 
-) ENGINE=MyISAM;
78 
+) ENGINE=InnoDB;
79 79
80 80 
 CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
81 81 
 CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri);
... ... 
@@ -103,7 +103,7 @@ CREATE TABLE pua (
103 103 
     version INT(11) NOT NULL,
104 104 
     extra_headers TEXT NOT NULL,
105 105 
     CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
106 
-) ENGINE=MyISAM;
106 
+) ENGINE=InnoDB;
107 107
108 108 
 CREATE INDEX expires_idx ON pua (expires);
109 109 
 CREATE INDEX dialog1_idx ON pua (pres_id, pres_uri);
Browse code

lib/srdb1/schema: Updated indices for presence tables based on results of latest performance testing

- By Hugh Waite @ Crocodile RCS Ltd and Peter Dunkley @ Crocodile RCS Ltd

Peter Dunkley authored on 11/09/2012 12:59:55
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 2b62b73f8d
... ... 
@@ -46,7 +46,7 @@ CREATE TABLE active_watchers (
46 46 
 ) ENGINE=MyISAM;
47 47
48 48 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
49 
-CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri);
49 
+CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri, event);
50 50 
 CREATE INDEX updated_idx ON active_watchers (updated);
51 51 
 CREATE INDEX updated_winfo_idx ON active_watchers (updated_winfo, presentity_uri);
52 52
... ... 
@@ -106,10 +106,7 @@ CREATE TABLE pua (
106 106 
 ) ENGINE=MyISAM;
107 107
108 108 
 CREATE INDEX expires_idx ON pua (expires);
109 
-CREATE INDEX dialog1_idx ON pua (call_id, from_tag, to_tag);
110 
-CREATE INDEX dialog2_idx ON pua (pres_id, pres_uri);
111 
-CREATE INDEX tmp_dlg1_idx ON pua (call_id, from_tag);
112 
-CREATE INDEX tmp_dlg2_idx ON pua (pres_id, pres_uri, call_id, from_tag);
113 
-CREATE INDEX tmp_record1_idx ON pua (pres_id);
114 
-CREATE INDEX tmp_record2_idx ON pua (pres_id, etag);
109 
+CREATE INDEX dialog1_idx ON pua (pres_id, pres_uri);
110 
+CREATE INDEX dialog2_idx ON pua (call_id, from_tag);
111 
+CREATE INDEX record_idx ON pua (pres_id);
115 112
Browse code

kamctl: regenerated the db creation scripts

- there were changes in presence tables not propagated to scripts
- it includes the updates for location tables

Daniel-Constantin Mierla authored on 11/09/2012 07:02:51
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ f252606e41
... ... 
@@ -102,13 +102,14 @@ CREATE TABLE pua (
102 102 
     remote_contact VARCHAR(128) NOT NULL,
103 103 
     version INT(11) NOT NULL,
104 104 
     extra_headers TEXT NOT NULL,
105 
-    CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag),
105 
+    CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
106 106 
 ) ENGINE=MyISAM;
107 107
108 
+CREATE INDEX expires_idx ON pua (expires);
108 109 
 CREATE INDEX dialog1_idx ON pua (call_id, from_tag, to_tag);
109 110 
 CREATE INDEX dialog2_idx ON pua (pres_id, pres_uri);
110 111 
 CREATE INDEX tmp_dlg1_idx ON pua (call_id, from_tag);
111 112 
 CREATE INDEX tmp_dlg2_idx ON pua (pres_id, pres_uri, call_id, from_tag);
112 113 
 CREATE INDEX tmp_record1_idx ON pua (pres_id);
113 114 
 CREATE INDEX tmp_record2_idx ON pua (pres_id, etag);
114 
-CREATE INDEX expires_idx ON pua (expires);
115 
+
Browse code

db schema: removed unique requirement from pua expires_idx

- TODO: change also other than mysql table structures

Juha Heinanen authored on 07/09/2012 16:16:37
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ bedb7fdc6d
... ... 
@@ -103,7 +103,6 @@ CREATE TABLE pua (
103 103 
     version INT(11) NOT NULL,
104 104 
     extra_headers TEXT NOT NULL,
105 105 
     CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag),
106 
-    CONSTRAINT expires_idx UNIQUE (expires)
107 106 
 ) ENGINE=MyISAM;
108 107
109 108 
 CREATE INDEX dialog1_idx ON pua (call_id, from_tag, to_tag);
... ... 
@@ -112,4 +111,4 @@ CREATE INDEX tmp_dlg1_idx ON pua (call_id, from_tag);
112 111 
 CREATE INDEX tmp_dlg2_idx ON pua (pres_id, pres_uri, call_id, from_tag);
113 112 
 CREATE INDEX tmp_record1_idx ON pua (pres_id);
114 113 
 CREATE INDEX tmp_record2_idx ON pua (pres_id, etag);
115 
-
114 
+CREATE INDEX expires_idx ON pua (expires);
Browse code

lib/srdb1/schema, modules_k/presence, utils/kamctl: Added another field to active_watchers DB

- Added the updated_winfo field (part of the presence notifier
fix) now to get in before the freeze.
- Also updated active_watchers table version.

Peter Dunkley authored on 23/04/2012 15:32:36
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 6583dd7869
... ... 
@@ -15,7 +15,7 @@ CREATE TABLE presentity (
15 15 
 CREATE INDEX presentity_expires ON presentity (expires);
16 16 
 CREATE INDEX account_idx ON presentity (username, domain, event);
17 17
18 
-INSERT INTO version (table_name, table_version) values ('active_watchers','10');
18 
+INSERT INTO version (table_name, table_version) values ('active_watchers','11');
19 19 
 CREATE TABLE active_watchers (
20 20 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
21 21 
     presentity_uri VARCHAR(128) NOT NULL,
... ... 
@@ -41,12 +41,14 @@ CREATE TABLE active_watchers (
41 41 
     from_user VARCHAR(64) NOT NULL,
42 42 
     from_domain VARCHAR(64) NOT NULL,
43 43 
     updated INT(11) NOT NULL,
44 
+    updated_winfo INT(11) NOT NULL,
44 45 
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
45 46 
 ) ENGINE=MyISAM;
46 47
47 48 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
48 49 
 CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri);
49 50 
 CREATE INDEX updated_idx ON active_watchers (updated);
51 
+CREATE INDEX updated_winfo_idx ON active_watchers (updated_winfo, presentity_uri);
50 52
51 53 
 INSERT INTO version (table_name, table_version) values ('watchers','3');
52 54 
 CREATE TABLE watchers (
Browse code

lib/srdb1/schema, utils/kamctl: Updated db schemas for upcoming presence notifier development

- Added the new column to active_watchers so that the schemas don't
change after the code freeze on Monday.

Peter Dunkley authored on 20/04/2012 15:27:03
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ ffb5799a51
... ... 
@@ -40,11 +40,13 @@ CREATE TABLE active_watchers (
40 40 
     local_contact VARCHAR(128) NOT NULL,
41 41 
     from_user VARCHAR(64) NOT NULL,
42 42 
     from_domain VARCHAR(64) NOT NULL,
43 
+    updated INT(11) NOT NULL,
43 44 
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
44 45 
 ) ENGINE=MyISAM;
45 46
46 47 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
47 48 
 CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri);
49 
+CREATE INDEX updated_idx ON active_watchers (updated);
48 50
49 51 
 INSERT INTO version (table_name, table_version) values ('watchers','3');
50 52 
 CREATE TABLE watchers (
Browse code

lib/srdb1/schema, utils/kamctl: Updated DB schemas for presence and RLS

- New fields for from_user and from_domain as these may be different
to the watcher_user and watcher_domain now.

Peter Dunkley authored on 19/04/2012 17:11:33
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 039c72b1f0
... ... 
@@ -15,7 +15,7 @@ CREATE TABLE presentity (
15 15 
 CREATE INDEX presentity_expires ON presentity (expires);
16 16 
 CREATE INDEX account_idx ON presentity (username, domain, event);
17 17
18 
-INSERT INTO version (table_name, table_version) values ('active_watchers','9');
18 
+INSERT INTO version (table_name, table_version) values ('active_watchers','10');
19 19 
 CREATE TABLE active_watchers (
20 20 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
21 21 
     presentity_uri VARCHAR(128) NOT NULL,
... ... 
@@ -38,6 +38,8 @@ CREATE TABLE active_watchers (
38 38 
     version INT(11) DEFAULT 0 NOT NULL,
39 39 
     socket_info VARCHAR(64) NOT NULL,
40 40 
     local_contact VARCHAR(128) NOT NULL,
41 
+    from_user VARCHAR(64) NOT NULL,
42 
+    from_domain VARCHAR(64) NOT NULL,
41 43 
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
42 44 
 ) ENGINE=MyISAM;
43 45
Browse code

lib/srdb1/schema: Updated XCAP indices to fix MySQL primary key issue

- The previous commit was incorrect

Peter Dunkley authored on 20/03/2012 16:54:18
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 2a41fe4fd1
... ... 
@@ -68,7 +68,7 @@ CREATE TABLE xcap (
68 68 
     source INT(11) NOT NULL,
69 69 
     doc_uri VARCHAR(255) NOT NULL,
70 70 
     port INT(11) NOT NULL,
71 
-    CONSTRAINT doc_uri_idx UNIQUE ()
71 
+    CONSTRAINT doc_uri_idx UNIQUE (doc_uri)
72 72 
 ) ENGINE=MyISAM;
73 73
74 74 
 CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
Browse code

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

Peter Dunkley authored on 20/03/2012 16:47:03
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 45a9d6089a
... ... 
@@ -59,20 +59,22 @@ CREATE TABLE watchers (
59 59
60 60 
 INSERT INTO version (table_name, table_version) values ('xcap','4');
61 61 
 CREATE TABLE xcap (
62 
-    id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
62 
+    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
63 63 
     username VARCHAR(64) NOT NULL,
64 64 
     domain VARCHAR(64) NOT NULL,
65 65 
     doc MEDIUMBLOB NOT NULL,
66 66 
     doc_type INT(11) NOT NULL,
67 67 
     etag VARCHAR(64) NOT NULL,
68 68 
     source INT(11) NOT NULL,
69 
-    doc_uri VARCHAR(255) PRIMARY KEY NOT NULL,
69 
+    doc_uri VARCHAR(255) NOT NULL,
70 70 
     port INT(11) NOT NULL,
71 
-    CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type),
72 
-    CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri),
73 
-    CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri)
71 
+    CONSTRAINT doc_uri_idx UNIQUE ()
74 72 
 ) ENGINE=MyISAM;
75 73
74 
+CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
75 
+CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri);
76 
+CREATE INDEX account_doc_uri_idx ON xcap (username, domain, doc_uri);
77 
+
76 78 
 INSERT INTO version (table_name, table_version) values ('pua','7');
77 79 
 CREATE TABLE pua (
78 80 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
Browse code

utils/kamctl: rebuilt .sql files to reflect changes to pua, rls, and xcap tables

Peter Dunkley authored on 19/03/2012 16:12:02
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 6853a8c061
... ... 
@@ -59,20 +59,20 @@ CREATE TABLE watchers (
59 59
60 60 
 INSERT INTO version (table_name, table_version) values ('xcap','4');
61 61 
 CREATE TABLE xcap (
62 
-    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
62 
+    id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
63 63 
     username VARCHAR(64) NOT NULL,
64 64 
     domain VARCHAR(64) NOT NULL,
65 65 
     doc MEDIUMBLOB NOT NULL,
66 66 
     doc_type INT(11) NOT NULL,
67 67 
     etag VARCHAR(64) NOT NULL,
68 68 
     source INT(11) NOT NULL,
69 
-    doc_uri VARCHAR(255) NOT NULL,
69 
+    doc_uri VARCHAR(255) PRIMARY KEY NOT NULL,
70 70 
     port INT(11) NOT NULL,
71 
-    CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type, doc_uri)
71 
+    CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type),
72 
+    CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri),
73 
+    CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri)
72 74 
 ) ENGINE=MyISAM;
73 75
74 
-CREATE INDEX source_idx ON xcap (source);
75 
-
76 76 
 INSERT INTO version (table_name, table_version) values ('pua','7');
77 77 
 CREATE TABLE pua (
78 78 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
... ... 
@@ -94,10 +94,14 @@ CREATE TABLE pua (
94 94 
     remote_contact VARCHAR(128) NOT NULL,
95 95 
     version INT(11) NOT NULL,
96 96 
     extra_headers TEXT NOT NULL,
97 
-    CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
97 
+    CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag),
98 
+    CONSTRAINT expires_idx UNIQUE (expires)
98 99 
 ) ENGINE=MyISAM;
99 100
100 
-CREATE INDEX presid_idx ON pua (pres_id);
101 
-CREATE INDEX dialog_idx ON pua (call_id, from_tag, to_tag);
102 
-CREATE INDEX tmp_dlg_idx ON pua (pres_id, pres_uri, call_id, from_tag);
101 
+CREATE INDEX dialog1_idx ON pua (call_id, from_tag, to_tag);
102 
+CREATE INDEX dialog2_idx ON pua (pres_id, pres_uri);
103 
+CREATE INDEX tmp_dlg1_idx ON pua (call_id, from_tag);
104 
+CREATE INDEX tmp_dlg2_idx ON pua (pres_id, pres_uri, call_id, from_tag);
105 
+CREATE INDEX tmp_record1_idx ON pua (pres_id);
106 
+CREATE INDEX tmp_record2_idx ON pua (pres_id, etag);
103 107
Browse code

modules_k/presence Added an index to active_watchers table

For DB_ONLY mode an index on presentity_uri is needed.
(Used for queries when sending Notifies as a consequence of a received
Publish)

Anca Vamanu authored on 15/03/2012 16:47:56
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 25ce428ab1
... ... 
@@ -42,6 +42,7 @@ CREATE TABLE active_watchers (
42 42 
 ) ENGINE=MyISAM;
43 43
44 44 
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
45 
+CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri);
45 46
46 47 
 INSERT INTO version (table_name, table_version) values ('watchers','3');
47 48 
 CREATE TABLE watchers (
Browse code

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

Peter Dunkley authored on 12/03/2012 21:38:19
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 419e26078b
... ... 
@@ -13,6 +13,7 @@ CREATE TABLE presentity (
13 13 
 ) ENGINE=MyISAM;
14 14
15 15 
 CREATE INDEX presentity_expires ON presentity (expires);
16 
+CREATE INDEX account_idx ON presentity (username, domain, event);
16 17
17 18 
 INSERT INTO version (table_name, table_version) values ('active_watchers','9');
18 19 
 CREATE TABLE active_watchers (
... ... 
@@ -91,6 +92,11 @@ CREATE TABLE pua (
91 92 
     contact VARCHAR(128) NOT NULL,
92 93 
     remote_contact VARCHAR(128) NOT NULL,
93 94 
     version INT(11) NOT NULL,
94 
-    extra_headers TEXT NOT NULL
95 
+    extra_headers TEXT NOT NULL,
96 
+    CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
95 97 
 ) ENGINE=MyISAM;
96 98
99 
+CREATE INDEX presid_idx ON pua (pres_id);
100 
+CREATE INDEX dialog_idx ON pua (call_id, from_tag, to_tag);
101 
+CREATE INDEX tmp_dlg_idx ON pua (pres_id, pres_uri, call_id, from_tag);
102 
+
Browse code

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)

Anca Vamanu authored on 09/03/2012 14:17:56
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 652f08a52f
... ... 
@@ -12,6 +12,8 @@ CREATE TABLE presentity (
12 12 
     CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
13 13 
 ) ENGINE=MyISAM;
14 14
15 
+CREATE INDEX presentity_expires ON presentity (expires);
16 
+
15 17 
 INSERT INTO version (table_name, table_version) values ('active_watchers','9');
16 18 
 CREATE TABLE active_watchers (
17 19 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
... ... 
@@ -35,9 +37,11 @@ CREATE TABLE active_watchers (
35 37 
     version INT(11) DEFAULT 0 NOT NULL,
36 38 
     socket_info VARCHAR(64) NOT NULL,
37 39 
     local_contact VARCHAR(128) NOT NULL,
38 
-    CONSTRAINT active_watchers_idx UNIQUE (presentity_uri, callid, to_tag, from_tag)
40 
+    CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
39 41 
 ) ENGINE=MyISAM;
40 42
43 
+CREATE INDEX active_watchers_expires ON active_watchers (expires);
44 
+
41 45 
 INSERT INTO version (table_name, table_version) values ('watchers','3');
42 46 
 CREATE TABLE watchers (
43 47 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
Browse code

kamctl: regenerated sql scripts from latest db schema

Daniel-Constantin Mierla authored on 08/09/2011 14:05:42
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 3dde3b652f
... ... 
@@ -60,7 +60,7 @@ CREATE TABLE xcap (
60 60 
     doc_type INT(11) NOT NULL,
61 61 
     etag VARCHAR(64) NOT NULL,
62 62 
     source INT(11) NOT NULL,
63 
-    doc_uri VARCHAR(128) NOT NULL,
63 
+    doc_uri VARCHAR(255) NOT NULL,
64 64 
     port INT(11) NOT NULL,
65 65 
     CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type, doc_uri)
66 66 
 ) ENGINE=MyISAM;
Browse code

srdb1: updated the definition of siptrace table to include time_us column

- regenerated scripts to create the database schema based on latest xml
schema definition

Daniel-Constantin Mierla authored on 13/08/2011 21:37:10
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 1aaf6bc3a2
... ... 
@@ -67,7 +67,7 @@ CREATE TABLE xcap (
67 67
68 68 
 CREATE INDEX source_idx ON xcap (source);
69 69
70 
-INSERT INTO version (table_name, table_version) values ('pua','6');
70 
+INSERT INTO version (table_name, table_version) values ('pua','7');
71 71 
 CREATE TABLE pua (
72 72 
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
73 73 
     pres_uri VARCHAR(128) NOT NULL,
... ... 
@@ -79,7 +79,7 @@ CREATE TABLE pua (
79 79 
     etag VARCHAR(64) NOT NULL,
80 80 
     tuple_id VARCHAR(64),
81 81 
     watcher_uri VARCHAR(128) NOT NULL,
82 
-    call_id VARCHAR(128) NOT NULL,
82 
+    call_id VARCHAR(255) NOT NULL,
83 83 
     to_tag VARCHAR(64) NOT NULL,
84 84 
     from_tag VARCHAR(64) NOT NULL,
85 85 
     cseq INT(11) NOT NULL,
Browse code

srdb1, modules_k/presence_xml, modules_k/xcap_server: Updated xcap table version from 3 to 4

- Also fixed a merge issue where I copied the PostgreSQL version of
presence-create.sql over the MySQL one.

pd authored on 27/07/2011 13:44:39
Showing 1 changed files
utils/kamctl/mysql/presence-create.sql
History View file @ 7545c62ab6
... ... 
@@ -1,20 +1,20 @@
1 1 
 INSERT INTO version (table_name, table_version) values ('presentity','3');
2 2 
 CREATE TABLE presentity (
3 
-    id SERIAL PRIMARY KEY NOT NULL,
3 
+    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
4 4 
     username VARCHAR(64) NOT NULL,
5 5 
     domain VARCHAR(64) NOT NULL,
6 6 
     event VARCHAR(64) NOT NULL,
7 7 
     etag VARCHAR(64) NOT NULL,
8 
-    expires INTEGER NOT NULL,
9 
-    received_time INTEGER NOT NULL,
10 
-    body BYTEA NOT NULL,
8 
+    expires INT(11) NOT NULL,
9 
+    received_time INT(11) NOT NULL,
10 
+    body BLOB NOT NULL,
11 11 
     sender VARCHAR(128) NOT NULL,
12 
-    CONSTRAINT presentity_presentity_idx UNIQUE (username, domain, event, etag)
13 
-);
12 
+    CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
13 
+) ENGINE=MyISAM;
14 14
15 15 
 INSERT INTO version (table_name, table_version) values ('active_watchers','9');
16 16 
 CREATE TABLE active_watchers (
17 
-    id SERIAL PRIMARY KEY NOT NULL,
17 
+    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
18 18 
     presentity_uri VARCHAR(128) NOT NULL,
19 19 
     watcher_username VARCHAR(64) NOT NULL,
20 20 
     watcher_domain VARCHAR(64) NOT NULL,
... ... 
@@ -25,68 +25,68 @@ CREATE TABLE active_watchers (
25