Browse code

kamctl: regenerate database creation scripts after scheme extensions

Henning Westerholt authored on 27/08/2019 20:00:12
Showing 1 changed files
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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
... ...
@@ -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