User Tools

Site Tools


install:upgrade:4.2.x-to-4.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
Last revision Both sides next revision
install:upgrade:4.2.x-to-4.3.0 [2015/06/10 17:22]
miconda [rtpengine]
install:upgrade:4.2.x-to-4.3.0 [2015/10/09 13:28]
klaus3000
Line 58: Line 58:
 <code sql> <code sql>
 -- table: location -- table: location
-ALTER TABLE location ADD COLUMN server_id INT(11) DEFAULT 0 NOT NULL; +ALTER TABLE location ADD COLUMN `server_idINT(11) DEFAULT 0 NOT NULL; 
-ALTER TABLE location ADD COLUMN connection_id INT(11) DEFAULT 0 NOT NULL; +ALTER TABLE location ADD COLUMN `connection_idINT(11) DEFAULT 0 NOT NULL; 
-ALTER TABLE location ADD COLUMN keepalive INT(11) DEFAULT 0 NOT NULL; +ALTER TABLE location ADD COLUMN `keepaliveINT(11) DEFAULT 0 NOT NULL; 
-ALTER TABLE location ADD COLUMN partition INT(11) DEFAULT 0 NOT NULL;+ALTER TABLE location ADD COLUMN `partitionINT(11) DEFAULT 0 NOT NULL;
 DELETE FROM version WHERE table_name='location'; DELETE FROM version WHERE table_name='location';
 INSERT INTO version (`table_name`, `table_version`) values ('location','8'); INSERT INTO version (`table_name`, `table_version`) values ('location','8');
  
 -- table: presentity -- table: presentity
-ALTER TABLE presentity ADD COLUMN priority INT(11) DEFAULT 0 NOT NULL;+ALTER TABLE presentity ADD COLUMN `priorityINT(11) DEFAULT 0 NOT NULL;
 DELETE FROM version WHERE table_name='presentity'; DELETE FROM version WHERE table_name='presentity';
 INSERT INTO version (`table_name`, `table_version`) values ('presentity','4'); INSERT INTO version (`table_name`, `table_version`) values ('presentity','4');
  
 -- table: sip_trace -- table: sip_trace
-ALTER TABLE sip_trace ADD COLUMN totag VARCHAR(64) DEFAULT '' NOT NULL;+ALTER TABLE sip_trace ADD COLUMN `totagVARCHAR(64) DEFAULT '' NOT NULL;
 UPDATE version SET table_version='4' WHERE table_name='sip_trace'; UPDATE version SET table_version='4' WHERE table_name='sip_trace';
  
Line 89: Line 89:
  
 </code> </code>
 +
 +==== Upgrade Old Postgres Database Structure ====
 +
 +Run following SQL statements in psql client to upgrade database structure from v4.2 to v4.3:
 +
 +NOTE This drops and recreates the acc_cdrs table. Thus make sure to backup them first!!! You might also need to restore the privileges for the acc_cdrs table.
 +
 +<code sql>
 +-- table: location
 +ALTER TABLE location ADD COLUMN server_id INTEGER DEFAULT 0 NOT NULL;
 +ALTER TABLE location ADD COLUMN connection_id INTEGER DEFAULT 0 NOT NULL;
 +ALTER TABLE location ADD COLUMN keepalive INTEGER DEFAULT 0 NOT NULL;
 +ALTER TABLE location ADD COLUMN partition INTEGER DEFAULT 0 NOT NULL;
 +DELETE FROM version WHERE TABLE_NAME='location';
 +INSERT INTO version (table_name, table_version) VALUES ('location','8');
 + 
 +-- table: presentity
 +ALTER TABLE presentity ADD COLUMN priority INTEGER DEFAULT 0 NOT NULL;
 +DELETE FROM version WHERE TABLE_NAME='presentity';
 +INSERT INTO version (table_name, table_version) VALUES ('presentity','4');
 + 
 +-- table: sip_trace
 +ALTER TABLE sip_trace ADD COLUMN totag VARCHAR(64) DEFAULT '' NOT NULL;
 +UPDATE version SET table_version='4' WHERE table_name='sip_trace';
 + 
 +-- table: acc_cdrs
 +DROP TABLE acc_cdrs;
 +CREATE TABLE acc_cdrs (
 +    id SERIAL PRIMARY KEY NOT NULL,
 +    start_time TIMESTAMP WITHOUT TIME ZONE DEFAULT '2000-01-01 00:00:00' NOT NULL,
 +    end_time TIMESTAMP WITHOUT TIME ZONE DEFAULT '2000-01-01 00:00:00' NOT NULL,
 +    duration REAL DEFAULT 0 NOT NULL
 +);
 +UPDATE version SET table_version='2' WHERE table_name='acc_cdrs';
 + 
 +-- table: silo
 +ALTER TABLE silo ALTER COLUMN body SET DEFAULT '';
 +ALTER TABLE silo ALTER COLUMN body SET NOT NULL;
 +ALTER TABLE silo ALTER COLUMN extra_hdrs SET DEFAULT '';
 +ALTER TABLE silo ALTER COLUMN extra_hdrs SET NOT NULL;
 +UPDATE version SET  table_version='8' WHERE table_name='silo';
 +</code>
 +
install/upgrade/4.2.x-to-4.3.0.txt · Last modified: 2015/10/09 14:03 by klaus3000