utils/kamctl/mysql/presence-create.sql
9a207da8
 INSERT INTO version (table_name, table_version) values ('presentity','3');
25288e28
 CREATE TABLE presentity (
7545c62a
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
25288e28
     username VARCHAR(64) NOT NULL,
     domain VARCHAR(64) NOT NULL,
     event VARCHAR(64) NOT NULL,
     etag VARCHAR(64) NOT NULL,
7545c62a
     expires INT(11) NOT NULL,
     received_time INT(11) NOT NULL,
     body BLOB NOT NULL,
2c8d9874
     sender VARCHAR(128) NOT NULL,
7545c62a
     CONSTRAINT presentity_idx UNIQUE (username, domain, event, etag)
ffe8aa17
 );
25288e28
 
652f08a5
 CREATE INDEX presentity_expires ON presentity (expires);
419e2607
 CREATE INDEX account_idx ON presentity (username, domain, event);
652f08a5
 
6583dd78
 INSERT INTO version (table_name, table_version) values ('active_watchers','11');
25288e28
 CREATE TABLE active_watchers (
7545c62a
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
187e6aef
     presentity_uri VARCHAR(128) NOT NULL,
     watcher_username VARCHAR(64) NOT NULL,
     watcher_domain VARCHAR(64) NOT NULL,
25288e28
     to_user VARCHAR(64) NOT NULL,
     to_domain VARCHAR(64) NOT NULL,
3ddcafb4
     event VARCHAR(64) DEFAULT 'presence' NOT NULL,
25288e28
     event_id VARCHAR(64),
     to_tag VARCHAR(64) NOT NULL,
     from_tag VARCHAR(64) NOT NULL,
fbbfac4e
     callid VARCHAR(255) NOT NULL,
7545c62a
     local_cseq INT(11) NOT NULL,
     remote_cseq INT(11) NOT NULL,
f2ffcfc6
     contact VARCHAR(128) NOT NULL,
25288e28
     record_route TEXT,
7545c62a
     expires INT(11) NOT NULL,
     status INT(11) DEFAULT 2 NOT NULL,
a5b48b9e
     reason VARCHAR(64) NOT NULL,
7545c62a
     version INT(11) DEFAULT 0 NOT NULL,
25288e28
     socket_info VARCHAR(64) NOT NULL,
     local_contact VARCHAR(128) NOT NULL,
039c72b1
     from_user VARCHAR(64) NOT NULL,
     from_domain VARCHAR(64) NOT NULL,
ffb5799a
     updated INT(11) NOT NULL,
6583dd78
     updated_winfo INT(11) NOT NULL,
652f08a5
     CONSTRAINT active_watchers_idx UNIQUE (callid, to_tag, from_tag)
ffe8aa17
 );
25288e28
 
652f08a5
 CREATE INDEX active_watchers_expires ON active_watchers (expires);
2b62b73f
 CREATE INDEX active_watchers_pres ON active_watchers (presentity_uri, event);
ffb5799a
 CREATE INDEX updated_idx ON active_watchers (updated);
6583dd78
 CREATE INDEX updated_winfo_idx ON active_watchers (updated_winfo, presentity_uri);
652f08a5
 
187e6aef
 INSERT INTO version (table_name, table_version) values ('watchers','3');
25288e28
 CREATE TABLE watchers (
7545c62a
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
187e6aef
     presentity_uri VARCHAR(128) NOT NULL,
     watcher_username VARCHAR(64) NOT NULL,
     watcher_domain VARCHAR(64) NOT NULL,
3ddcafb4
     event VARCHAR(64) DEFAULT 'presence' NOT NULL,
7545c62a
     status INT(11) NOT NULL,
25288e28
     reason VARCHAR(64),
7545c62a
     inserted_time INT(11) NOT NULL,
     CONSTRAINT watcher_idx UNIQUE (presentity_uri, watcher_username, watcher_domain, event)
ffe8aa17
 );
41f3930a
 
7545c62a
 INSERT INTO version (table_name, table_version) values ('xcap','4');
17ee186d
 CREATE TABLE xcap (
45a9d608
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
41f3930a
     username VARCHAR(64) NOT NULL,
     domain VARCHAR(64) NOT NULL,
7545c62a
     doc MEDIUMBLOB NOT NULL,
     doc_type INT(11) NOT NULL,
387cb10c
     etag VARCHAR(64) NOT NULL,
7545c62a
     source INT(11) NOT NULL,
45a9d608
     doc_uri VARCHAR(255) NOT NULL,
7545c62a
     port INT(11) NOT NULL,
2a41fe4f
     CONSTRAINT doc_uri_idx UNIQUE (doc_uri)
ffe8aa17
 );
41f3930a
 
45a9d608
 CREATE INDEX account_doc_type_idx ON xcap (username, domain, doc_type);
 CREATE INDEX account_doc_type_uri_idx ON xcap (username, domain, doc_type, doc_uri);
 CREATE INDEX account_doc_uri_idx ON xcap (username, domain, doc_uri);
 
1aaf6bc3
 INSERT INTO version (table_name, table_version) values ('pua','7');
41f3930a
 CREATE TABLE pua (
7545c62a
     id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
41f3930a
     pres_uri VARCHAR(128) NOT NULL,
3d9ea85f
     pres_id VARCHAR(255) NOT NULL,
7545c62a
     event INT(11) NOT NULL,
     expires INT(11) NOT NULL,
     desired_expires INT(11) NOT NULL,
     flag INT(11) NOT NULL,
41f3930a
     etag VARCHAR(64) NOT NULL,
     tuple_id VARCHAR(64),
     watcher_uri VARCHAR(128) NOT NULL,
1aaf6bc3
     call_id VARCHAR(255) NOT NULL,
41f3930a
     to_tag VARCHAR(64) NOT NULL,
     from_tag VARCHAR(64) NOT NULL,
7545c62a
     cseq INT(11) NOT NULL,
41f3930a
     record_route TEXT,
     contact VARCHAR(128) NOT NULL,
2c8d9874
     remote_contact VARCHAR(128) NOT NULL,
7545c62a
     version INT(11) NOT NULL,
419e2607
     extra_headers TEXT NOT NULL,
f252606e
     CONSTRAINT pua_idx UNIQUE (etag, tuple_id, call_id, from_tag)
ffe8aa17
 );
41f3930a
 
f252606e
 CREATE INDEX expires_idx ON pua (expires);
2b62b73f
 CREATE INDEX dialog1_idx ON pua (pres_id, pres_uri);
 CREATE INDEX dialog2_idx ON pua (call_id, from_tag);
 CREATE INDEX record_idx ON pua (pres_id);
f252606e