User Tools

Site Tools


install:upgrade:5.2.x-to-5.3.0

Differences

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

Link to this comparison view

Next revision
Previous revision
install:upgrade:5.2.x-to-5.3.0 [2019/09/05 08:06]
henningw created
install:upgrade:5.2.x-to-5.3.0 [2019/10/17 21:46] (current)
amessina_messinet.com Add PostgreSQL upgrade transaction/statements
Line 59: Line 59:
  
 <code sql> <code sql>
-...+-- acc table 
 +ALTER TABLE acc CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL DEFAULT ''; -- # was varchar(64) NOT NULL DEFAULT '' 
 +ALTER TABLE acc CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL DEFAULT ''; -- # was varchar(64) NOT NULL DEFAULT '' 
 + 
 +-- active_watchers table 
 +ALTER TABLE active_watchers CHANGE COLUMN contact contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE active_watchers CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE active_watchers CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE active_watchers CHANGE COLUMN presentity_uri presentity_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE active_watchers CHANGE COLUMN local_contact local_contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE aliases CHANGE COLUMN received received varchar(255) DEFAULT NULL; -- # was varchar(128) DEFAULT NULL 
 + 
 +ALTER TABLE carrierfailureroute CHANGE COLUMN host_name host_name varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE carrierroute CHANGE COLUMN rewrite_host rewrite_host varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 + 
 +ALTER TABLE dialog CHANGE COLUMN req_uri req_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN caller_contact caller_contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN callee_contact callee_contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN from_uri from_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE dialog CHANGE COLUMN to_uri to_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE missed_calls CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL DEFAULT ''; -- # was varchar(64) NOT NULL DEFAULT '' 
 +ALTER TABLE missed_calls CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL DEFAULT ''; -- # was varchar(64) NOT NULL DEFAULT '' 
 + 
 +ALTER TABLE pdt CHANGE COLUMN domain domain varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE pdt CHANGE COLUMN sdomain sdomain varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE presentity CHANGE COLUMN sender sender varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE pua CHANGE COLUMN remote_contact remote_contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE pua CHANGE COLUMN watcher_uri watcher_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE pua CHANGE COLUMN contact contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE pua CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE pua CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE pua CHANGE COLUMN pres_uri pres_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE purplemap CHANGE COLUMN sip_user sip_user varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE purplemap CHANGE COLUMN ext_user ext_user varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE rls_presentity CHANGE COLUMN resource_uri resource_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE rls_watchers CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE rls_watchers CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE rls_watchers CHANGE COLUMN presentity_uri presentity_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE rls_watchers CHANGE COLUMN local_contact local_contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +ALTER TABLE rls_watchers CHANGE COLUMN contact contact varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 + 
 +ALTER TABLE sca_subscriptions CHANGE COLUMN to_tag to_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 +ALTER TABLE sca_subscriptions CHANGE COLUMN from_tag from_tag varchar(128) NOT NULL; -- # was varchar(64) NOT NULL 
 + 
 +ALTER TABLE silo CHANGE COLUMN dst_addr dst_addr varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE silo CHANGE COLUMN src_addr src_addr varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 + 
 +ALTER TABLE sip_trace CHANGE COLUMN fromtag fromtag varchar(128) NOT NULL DEFAULT ''; -- # was varchar(64) NOT NULL DEFAULT '' 
 +ALTER TABLE sip_trace CHANGE COLUMN status status varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE sip_trace CHANGE COLUMN traced_user traced_user varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE sip_trace CHANGE COLUMN totag totag varchar(128) NOT NULL DEFAULT ''; -- # was varchar(64) NOT NULL DEFAULT '' 
 + 
 +ALTER TABLE speed_dial CHANGE COLUMN new_uri new_uri varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 + 
 +ALTER TABLE topos_d CHANGE COLUMN a_contact a_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_d CHANGE COLUMN b_contact b_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_d CHANGE COLUMN b_uri b_uri varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_d CHANGE COLUMN a_uri a_uri varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_d CHANGE COLUMN bs_contact bs_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_d CHANGE COLUMN as_contact as_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_d CHANGE COLUMN r_uri r_uri varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 + 
 +ALTER TABLE topos_t CHANGE COLUMN x_uri x_uri varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_t CHANGE COLUMN a_contact a_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_t CHANGE COLUMN b_contact b_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_t CHANGE COLUMN bs_contact bs_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_t CHANGE COLUMN b_srcaddr b_srcaddr varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_t CHANGE COLUMN as_contact as_contact varchar(512) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE topos_t CHANGE COLUMN a_srcaddr a_srcaddr varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 + 
 +-- uacreg table 
 +ALTER TABLE uacreg CHANGE COLUMN auth_proxy auth_proxy varchar(255) NOT NULL DEFAULT ''; -- # was varchar(128) NOT NULL DEFAULT '' 
 +ALTER TABLE uacreg ADD COLUMN socket varchar(128) NOT NULL DEFAULT ''; 
 +UPDATE version SET table_version=4 WHERE TABLE_NAME='uacreg'; 
 + 
 + 
 +-- usr_preferences table 
 +ALTER TABLE usr_preferences CHANGE COLUMN username username varchar(255) NOT NULL DEFAULT '0'; -- # was varchar(128) NOT NULL DEFAULT '0' 
 + 
 + 
 +-- watchers table 
 +ALTER TABLE watchers CHANGE COLUMN presentity_uri presentity_uri varchar(255) NOT NULL; -- # was varchar(128) NOT NULL 
 +</code> 
 + 
 + 
 +==== PostgreSQL Database Structure ==== 
 + 
 +Run the following SQL statements in PostgreSQL client to upgrade database structure from v5.2 to v5.3: 
 + 
 +<code sql> 
 +BEGIN; 
 + 
 +-- acc table 
 +ALTER TABLE IF EXISTS acc 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET DEFAULT '', ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET DEFAULT '', ALTER COLUMN from_tag SET NOT NULL; 
 +  
 +-- active_watchers table 
 +ALTER TABLE IF EXISTS active_watchers 
 +ALTER COLUMN contact TYPE varchar(255), ALTER COLUMN contact SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET NOT NULL, 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN presentity_uri TYPE varchar(255), ALTER COLUMN presentity_uri SET NOT NULL, 
 +ALTER COLUMN local_contact TYPE varchar(255), ALTER COLUMN local_contact SET NOT NULL; 
 + 
 +-- aliases table 
 +ALTER TABLE IF EXISTS aliases 
 +ALTER COLUMN received TYPE varchar(255), ALTER COLUMN received SET DEFAULT NULL; 
 + 
 +-- carrierfailureroute table 
 +ALTER TABLE IF EXISTS carrierfailureroute 
 +ALTER COLUMN host_name TYPE varchar(255), ALTER COLUMN host_name SET DEFAULT '', ALTER COLUMN host_name SET NOT NULL; 
 + 
 +-- carrierroute table 
 +ALTER TABLE IF EXISTS carrierroute 
 +ALTER COLUMN rewrite_host TYPE varchar(255), ALTER COLUMN rewrite_host SET DEFAULT '', ALTER COLUMN rewrite_host SET NOT NULL; 
 + 
 +-- dialog table 
 +ALTER TABLE IF EXISTS dialog 
 +ALTER COLUMN req_uri TYPE varchar(255), ALTER COLUMN req_uri SET NOT NULL, 
 +ALTER COLUMN caller_contact TYPE varchar(255), ALTER COLUMN caller_contact SET NOT NULL, 
 +ALTER COLUMN callee_contact TYPE varchar(255), ALTER COLUMN callee_contact SET NOT NULL, 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET NOT NULL, 
 +ALTER COLUMN from_uri TYPE varchar(255), ALTER COLUMN from_uri SET NOT NULL, 
 +ALTER COLUMN to_uri TYPE varchar(255), ALTER COLUMN to_uri SET NOT NULL; 
 + 
 +-- missed_calls table 
 +ALTER TABLE IF EXISTS missed_calls 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET DEFAULT '', ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET DEFAULT '', ALTER COLUMN from_tag SET NOT NULL; 
 + 
 +-- pdt table 
 +ALTER TABLE IF EXISTS pdt 
 +ALTER COLUMN domain TYPE varchar(255), ALTER COLUMN domain SET DEFAULT '', ALTER COLUMN domain SET NOT NULL, 
 +ALTER COLUMN sdomain TYPE varchar(255), ALTER COLUMN sdomain SET NOT NULL; 
 + 
 +-- presentity table 
 +ALTER TABLE IF EXISTS presentity 
 +ALTER COLUMN sender TYPE varchar(255), ALTER COLUMN sender SET NOT NULL; 
 + 
 +-- pua table 
 +ALTER TABLE IF EXISTS pua 
 +ALTER COLUMN remote_contact TYPE varchar(255), ALTER COLUMN remote_contact SET NOT NULL, 
 +ALTER COLUMN watcher_uri TYPE varchar(255), ALTER COLUMN watcher_uri SET NOT NULL, 
 +ALTER COLUMN contact TYPE varchar(255), ALTER COLUMN contact SET NOT NULL, 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET NOT NULL, 
 +ALTER COLUMN pres_uri TYPE varchar(255), ALTER COLUMN pres_uri SET NOT NULL; 
 + 
 +-- purplemap table 
 +ALTER TABLE IF EXISTS purplemap 
 +ALTER COLUMN sip_user TYPE varchar(255), ALTER COLUMN sip_user SET NOT NULL, 
 +ALTER COLUMN ext_user TYPE varchar(255), ALTER COLUMN ext_user SET NOT NULL; 
 + 
 +-- rls_presentity table 
 +ALTER TABLE IF EXISTS rls_presentity 
 +ALTER COLUMN resource_uri TYPE varchar(255), ALTER COLUMN resource_uri SET NOT NULL; 
 + 
 +-- rls_watchers table 
 +ALTER TABLE IF EXISTS rls_watchers 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET NOT NULL, 
 +ALTER COLUMN presentity_uri TYPE varchar(255), ALTER COLUMN presentity_uri SET NOT NULL, 
 +ALTER COLUMN local_contact TYPE varchar(255), ALTER COLUMN local_contact SET NOT NULL, 
 +ALTER COLUMN contact TYPE varchar(255), ALTER COLUMN contact SET NOT NULL; 
 + 
 +-- sca_subscriptions table 
 +ALTER TABLE IF EXISTS sca_subscriptions 
 +ALTER COLUMN to_tag TYPE varchar(128), ALTER COLUMN to_tag SET NOT NULL, 
 +ALTER COLUMN from_tag TYPE varchar(128), ALTER COLUMN from_tag SET NOT NULL; 
 + 
 +-- silo table 
 +ALTER TABLE IF EXISTS silo 
 +ALTER COLUMN dst_addr TYPE varchar(255), ALTER COLUMN dst_addr SET DEFAULT '', ALTER COLUMN dst_addr SET NOT NULL, 
 +ALTER COLUMN src_addr TYPE varchar(255), ALTER COLUMN src_addr SET DEFAULT '', ALTER COLUMN src_addr SET NOT NULL; 
 + 
 +-- sip_trace table 
 +ALTER TABLE IF EXISTS sip_trace 
 +ALTER COLUMN fromtag TYPE varchar(128), ALTER COLUMN fromtag SET DEFAULT '', ALTER COLUMN fromtag SET NOT NULL, 
 +ALTER COLUMN status TYPE varchar(255), ALTER COLUMN status SET DEFAULT '', ALTER COLUMN status SET NOT NULL, 
 +ALTER COLUMN traced_user TYPE varchar(255), ALTER COLUMN traced_user SET DEFAULT '', ALTER COLUMN traced_user SET NOT NULL, 
 +ALTER COLUMN totag TYPE varchar(128), ALTER COLUMN totag SET DEFAULT '', ALTER COLUMN totag SET NOT NULL; 
 + 
 +-- speed_dial table 
 +ALTER TABLE IF EXISTS speed_dial 
 +ALTER COLUMN new_uri TYPE varchar(255), ALTER COLUMN new_uri SET DEFAULT '', ALTER COLUMN new_uri SET NOT NULL; 
 + 
 +-- topos_d table 
 +ALTER TABLE IF EXISTS topos_d 
 +ALTER COLUMN a_contact TYPE varchar(512), ALTER COLUMN a_contact SET DEFAULT '', ALTER COLUMN a_contact SET NOT NULL, 
 +ALTER COLUMN b_contact TYPE varchar(512), ALTER COLUMN b_contact SET DEFAULT '', ALTER COLUMN b_contact SET NOT NULL, 
 +ALTER COLUMN b_uri TYPE varchar(255), ALTER COLUMN b_uri SET DEFAULT '', ALTER COLUMN b_uri SET NOT NULL, 
 +ALTER COLUMN a_uri TYPE varchar(255), ALTER COLUMN a_uri SET DEFAULT '', ALTER COLUMN a_uri SET NOT NULL, 
 +ALTER COLUMN bs_contact TYPE varchar(512), ALTER COLUMN bs_contact SET DEFAULT '', ALTER COLUMN bs_contact SET NOT NULL, 
 +ALTER COLUMN as_contact TYPE varchar(512), ALTER COLUMN as_contact SET DEFAULT '', ALTER COLUMN as_contact SET NOT NULL, 
 +ALTER COLUMN r_uri TYPE varchar(255), ALTER COLUMN r_uri SET DEFAULT '', ALTER COLUMN r_uri SET NOT NULL; 
 + 
 +-- topos_t table 
 +ALTER TABLE IF EXISTS topos_t 
 +ALTER COLUMN x_uri TYPE varchar(255), ALTER COLUMN x_uri SET DEFAULT '', ALTER COLUMN x_uri SET NOT NULL, 
 +ALTER COLUMN a_contact TYPE varchar(512), ALTER COLUMN a_contact SET DEFAULT '', ALTER COLUMN a_contact SET NOT NULL, 
 +ALTER COLUMN b_contact TYPE varchar(512), ALTER COLUMN b_contact SET DEFAULT '', ALTER COLUMN b_contact SET NOT NULL, 
 +ALTER COLUMN bs_contact TYPE varchar(512), ALTER COLUMN bs_contact SET DEFAULT '', ALTER COLUMN bs_contact SET NOT NULL, 
 +ALTER COLUMN b_srcaddr TYPE varchar(255), ALTER COLUMN b_srcaddr SET DEFAULT '', ALTER COLUMN b_srcaddr SET NOT NULL, 
 +ALTER COLUMN as_contact TYPE varchar(512), ALTER COLUMN as_contact SET DEFAULT '', ALTER COLUMN as_contact SET NOT NULL, 
 +ALTER COLUMN a_srcaddr TYPE varchar(255), ALTER COLUMN a_srcaddr SET DEFAULT '', ALTER COLUMN a_srcaddr SET NOT NULL; 
 +  
 +-- uacreg table 
 +ALTER TABLE IF EXISTS uacreg 
 +ALTER COLUMN auth_proxy TYPE varchar(255), ALTER COLUMN auth_proxy SET DEFAULT '', ALTER COLUMN auth_proxy SET NOT NULL, 
 +ADD COLUMN IF NOT EXISTS socket varchar(128) NOT NULL DEFAULT ''; 
 +UPDATE version SET table_version=4 WHERE table_name='uacreg'; 
 + 
 +-- usr_preferences table 
 +ALTER TABLE IF EXISTS usr_preferences 
 +ALTER COLUMN username TYPE varchar(255), ALTER COLUMN username SET DEFAULT '0', ALTER COLUMN username SET NOT NULL; 
 + 
 +-- watchers table 
 +ALTER TABLE IF EXISTS watchers 
 +ALTER COLUMN presentity_uri TYPE varchar(255), ALTER COLUMN presentity_uri SET NOT NULL; 
 + 
 +COMMIT;
 </code> </code>
install/upgrade/5.2.x-to-5.3.0.1567663579.txt.gz · Last modified: 2019/09/05 08:06 by henningw