Kamailio SIP Server Wiki

User Tools

Site Tools

Trace:
install:upgrade:3.2.x-to-3.3.0

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
Next revision Both sides next revision
install:upgrade:3.2.x-to-3.3.0 [2011/12/21 17:13]
86.59.95.122 		install:upgrade:3.2.x-to-3.3.0 [2012/05/08 16:52]
miconda [SQL Commands]
Line 6: Line 6:
  
 These sections presents notes, listed by modules, about the structure of database tables that existed in v3.2.x and changed during development of v3.3.0. These sections presents notes, listed by modules, about the structure of database tables that existed in v3.2.x and changed during development of v3.3.0.
 +
 +==== modules_k/dialog ====
 +
 +  * table **dialog**
 +    * table version is 7
 +    * new columns:
 +      * iflags INT(10) UNSIGNED DEFAULT 0 NOT NULL
 +      * xdata VARCHAR(512)
 +    * column definition changes:
 +      * caller_cseq VARCHAR(20) NOT NULL
 +      * callee_cseq VARCHAR(20) NOT NULL
 +
 +==== modules_k/domain ====
 +  * new table **domain_attrs**
 +  * table **domain**
 +    * new column **did**
  
 ==== modules/lcr ==== ==== modules/lcr ====
Line 11: Line 27:
   * table **lcr_gw**   * table **lcr_gw**
     * ip_addr column has now the size varchar(47)     * ip_addr column has now the size varchar(47)
 +  * table **lcr_rule**
 +    * new column request_uri varchar(64)
 +    * table version is 2
 +==== modules_k/msilo ====
 +
 +  * table **silo**
 +    * table version is 6
 +    * new columns:
 +      * extra_hdrs TEXT DEFAULT ' ' NOT NULL
 +
 +==== modules/mtree ====
 +
 +  * table **mtrees**
 +    * table version is 2
 +    * definition changes:
 +      * drop: CONSTRAINT tname_tprefix_idx UNIQUE (tname, tprefix)
 +      * add: CONSTRAINT tname_tprefix_tvalue_idx UNIQUE (tname, tprefix, tvalue)
 +
 +==== modules_k/permissions ====
 +
 +  * table **address**
 +    * table version is 5
 +    * column definition changes:
 +      * grp INT(11) UNSIGNED DEFAULT 1 NOT NULL
 +      * ip_addr VARCHAR(48) NOT NULL
 +      * mask INT DEFAULT 32 NOT NULL
 +
 +==== modules_k/usrloc ====
 +  * table **aliases**
 +    * table version is 5
 +    * new columns:
 +      * ruid VARCHAR(64) DEFAULT ' ' NOT NULL
 +      * instance VARCHAR(255) DEFAULT NULL
 +  * table **location**
 +    * table version is 5
 +    * new columns:
 +      * ruid VARCHAR(64) DEFAULT ' ' NOT NULL
 +      * instance VARCHAR(255) DEFAULT NULL
 +      * reg_id INT(11) DEFAULT 0 NOT NULL
 +
 ==== SQL Commands ==== ==== SQL Commands ====
 +
 +=== MySQL ===
  
 You can use next SQL commands (made for MySQL) to update the structure of existing tables in v3.2.x for v3.3.0: You can use next SQL commands (made for MySQL) to update the structure of existing tables in v3.2.x for v3.3.0:
Line 17: Line 75:
 <code sql> <code sql>
  
-ALTER TABLE lcr_gw MODIFY ip_addr VARCHAR(47) NOT NULL;+-- PERMISSIONS module 
 +ALTER TABLE address CHANGE COLUMN ip_addr ip_addr varchar(48) NOT NULL; # was varchar(15) NOT NULL 
 +ALTER TABLE address CHANGE COLUMN grp grp int(11) unsigned NOT NULL DEFAULT '1'; # was smallint(5) unsigned NOT NULL DEFAULT '1' 
 +ALTER TABLE address CHANGE COLUMN mask mask int(11) NOT NULL DEFAULT '32'; # was tinyint(4) NOT NULL DEFAULT '32'
  
-</code>+-- DIALOG module 
 +DELETE FROM dialog; 
 +ALTER TABLE dialog CHANGE COLUMN caller_cseq caller_cseq varchar(20) NOT NULL; # was varchar(7) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN callee_cseq callee_cseq varchar(20) NOT NULL; # was varchar(7) NOT NULL 
 +ALTER TABLE dialog DROP COLUMN toroute; # was int(10) unsigned NOT NULL DEFAULT '0' 
 +ALTER TABLE dialog ADD COLUMN iflags int(10) unsigned NOT NULL DEFAULT '0'; 
 +ALTER TABLE dialog ADD COLUMN xdata varchar(512) DEFAULT NULL;
  
-This is the translation of the above script for PostgreSQL (should work with 9.1.1, but is untested!)+DELETE FROM dialog_vars;
  
-<code sql>+-- LCR module 
 +ALTER TABLE lcr_gw CHANGE COLUMN ip_addr ip_addr varchar(47) DEFAULT NULL; # was varchar(15) DEFAULT NULL 
 +ALTER TABLE lcr_rule ADD COLUMN request_uri varchar(64) DEFAULT NULL;
  
-ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(47);+-- USRLOC module 
 +DELETE FROM location; 
 +ALTER TABLE location ADD COLUMN ruid varchar(64) NOT NULL DEFAULT ''; 
 +ALTER TABLE location ADD COLUMN reg_id int(11) NOT NULL DEFAULT '0'; 
 +ALTER TABLE location ADD COLUMN instance varchar(255) DEFAULT NULL; 
 + 
 +ALTER TABLE aliases ADD COLUMN ruid varchar(64) NOT NULL DEFAULT ''; 
 +ALTER TABLE aliases ADD COLUMN reg_id int(11) NOT NULL DEFAULT '0'; 
 +ALTER TABLE aliases ADD COLUMN instance varchar(255) DEFAULT NULL; 
 + 
 +-- MSILO module 
 +ALTER TABLE silo ADD COLUMN status int(11) NOT NULL DEFAULT '0'; 
 +ALTER TABLE silo ADD COLUMN extra_hdrs text NOT NULL DEFAULT ''; 
 +ALTER TABLE silo ADD COLUMN callid varchar(128) NOT NULL DEFAULT ''; 
 + 
 +-- DOMAIN module 
 +ALTER TABLE domain CHANGE COLUMN domain domain varchar(64) NOT NULL; # was varchar(64) NOT NULL DEFAULT '' 
 +ALTER TABLE domain ADD COLUMN did varchar(64) DEFAULT NULL; 
 + 
 +CREATE TABLE domain_attrs ( 
 +  id int(10) unsigned NOT NULL AUTO_INCREMENT, 
 +  did varchar(64) NOT NULL, 
 +  name varchar(32) NOT NULL, 
 +  type int(10) unsigned NOT NULL, 
 +  value varchar(255) NOT NULL, 
 +  last_modified datetime NOT NULL DEFAULT '1900-01-01 00:00:01', 
 +  PRIMARY KEY (id), 
 +  UNIQUE KEY domain_attrs_idx (did,name,value) 
 +) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
 + 
 +-- PRESENCE modules 
 +DELETE FROM active_watchers; 
 +ALTER TABLE active_watchers ADD COLUMN from_domain varchar(64) NOT NULL; 
 +ALTER TABLE active_watchers ADD COLUMN updated_winfo int(11) NOT NULL; 
 +ALTER TABLE active_watchers ADD COLUMN updated int(11) NOT NULL; 
 +ALTER TABLE active_watchers ADD COLUMN from_user varchar(64) NOT NULL; 
 +ALTER TABLE active_watchers DROP INDEX active_watchers_idx; # was UNIQUE (presentity_uri,callid,to_tag,from_tag) 
 +ALTER TABLE active_watchers ADD UNIQUE active_watchers_idx (callid,to_tag,from_tag); 
 +ALTER TABLE active_watchers ADD INDEX active_watchers_expires (expires); 
 +ALTER TABLE active_watchers ADD INDEX active_watchers_pres (presentity_uri); 
 +ALTER TABLE active_watchers ADD INDEX updated_idx (updated); 
 +ALTER TABLE active_watchers ADD INDEX updated_winfo_idx (updated_winfo,presentity_uri); 
 + 
 +ALTER TABLE presentity ADD INDEX account_idx (username,domain,event); 
 +ALTER TABLE presentity ADD INDEX presentity_expires (expires); 
 + 
 +ALTER TABLE pua ADD INDEX tmp_record2_idx (pres_id,etag); 
 +ALTER TABLE pua ADD INDEX tmp_dlg2_idx (pres_id,pres_uri,call_id,from_tag); 
 +ALTER TABLE pua ADD INDEX tmp_record1_idx (pres_id); 
 +ALTER TABLE pua ADD INDEX dialog2_idx (pres_id,pres_uri); 
 +ALTER TABLE pua ADD INDEX dialog1_idx (call_id,from_tag,to_tag); 
 +ALTER TABLE pua ADD INDEX tmp_dlg1_idx (call_id,from_tag); 
 +ALTER TABLE pua ADD UNIQUE pua_idx (etag,tuple_id,call_id,from_tag); 
 +ALTER TABLE pua ADD UNIQUE expires_idx (expires); 
 + 
 +ALTER TABLE rls_presentity ADD INDEX rlsubs_idx (rlsubs_did); 
 +ALTER TABLE rls_presentity ADD INDEX expires_idx (expires); 
 + 
 +DELETE FROM rls_watchers; 
 +ALTER TABLE rls_watchers ADD COLUMN from_domain varchar(64) NOT NULL; 
 +ALTER TABLE rls_watchers ADD COLUMN updated int(11) NOT NULL; 
 +ALTER TABLE rls_watchers ADD COLUMN from_user varchar(64) NOT NULL; 
 +ALTER TABLE rls_watchers DROP INDEX rls_watcher_idx; # was UNIQUE (presentity_uri,callid,to_tag,from_tag) 
 +ALTER TABLE rls_watchers ADD UNIQUE rls_watcher_idx (callid,to_tag,from_tag); 
 +ALTER TABLE rls_watchers ADD INDEX rls_watchers_expires (expires); 
 +ALTER TABLE rls_watchers ADD INDEX updated_idx (updated); 
 +ALTER TABLE rls_watchers ADD INDEX rls_watchers_update (watcher_username,watcher_domain,event); 
 +ALTER TABLE rls_watchers ADD INDEX rls_watchers_delete (callid,to_tag); 
 + 
 +ALTER TABLE xcap DROP INDEX source_idx; # was INDEX (source) 
 +ALTER TABLE xcap DROP INDEX account_doc_type_idx; # was UNIQUE (username,domain,doc_type,doc_uri) 
 +ALTER TABLE xcap ADD INDEX account_doc_type_idx (username,domain,doc_type); 
 +ALTER TABLE xcap ADD INDEX account_doc_type_uri_idx (username,domain,doc_type,doc_uri); 
 +ALTER TABLE xcap ADD UNIQUE doc_uri_idx (doc_uri); 
 +ALTER TABLE xcap ADD INDEX account_doc_uri_idx (username,domain,doc_uri); 
 + 
 + 
 +-- VERSION table 
 +DELETE FROM version WHERE table_name='dialog'; 
 +INSERT INTO version (table_name, table_version) values ('dialog','7'); 
 +DELETE FROM version WHERE table_name='domain'; 
 +INSERT INTO version (table_name, table_version) values ('domain','2'); 
 +DELETE FROM version WHERE table_name='domain_attrs'; 
 +INSERT INTO version (table_name, table_version) values ('domain_attrs','1'); 
 +DELETE FROM version WHERE table_name='silo'; 
 +INSERT INTO version (table_name, table_version) values ('silo','7'); 
 +DELETE FROM version WHERE table_name='mtrees'; 
 +INSERT INTO version (table_name, table_version) values ('mtrees','2'); 
 +DELETE FROM version WHERE table_name='address'; 
 +INSERT INTO version (table_name, table_version) values ('address','5'); 
 +DELETE FROM version WHERE table_name='active_watchers'; 
 +INSERT INTO version (table_name, table_version) values ('active_watchers','11'); 
 +DELETE FROM version WHERE table_name='aliases'; 
 +INSERT INTO version (table_name, table_version) values ('aliases','5'); 
 +DELETE FROM version WHERE table_name='rls_watchers'; 
 +INSERT INTO version (table_name, table_version) values ('rls_watchers','3'); 
 +DELETE FROM version WHERE table_name='location'; 
 +INSERT INTO version (table_name, table_version) values ('location','5');
  
 </code> </code>
  
 +=== PostgreSQL ===
 +
 +This is the translation of the above MySQL script for PostgreSQL (should work with 9.1.1, but is untested!)
 +
 +<code sql>
 +-- TO BE ADDED
 +-- example alter command: ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(47);
 +
 +</code>
 ===== Modules ===== ===== Modules =====
 +
 +==== modules_k/presence ====
 +
 +  * removed db_mode and fallback2db parameters
 +  * added two new parameters subs_db_mode and publ_cache to replace the ones that were removed
  
 ==== modules/your-module-here ==== ==== modules/your-module-here ====
  
   * Add changes or additions reflected in kamailio config syntax here.   * Add changes or additions reflected in kamailio config syntax here.
install/upgrade/3.2.x-to-3.3.0.txt · Last modified: 2014/09/23 11:21 by klaus3000

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki