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
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 14:03] (current)
klaus3000
Line 46: Line 46:
 There are several changes to the database structure, in short: There are several changes to the database structure, in short:
  
-  * location table has three new columns: server_id, connection_id, keepalive+  * location (aliases) table has three new columns: server_id, connection_id, keepalive
   * presentity table has a new column: priority   * presentity table has a new column: priority
   * sip_trace table has a new column: totag   * sip_trace table has a new column: totag
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: aliases
 +ALTER TABLE aliases ADD COLUMN `server_id` INT(11) DEFAULT 0 NOT NULL;
 +ALTER TABLE aliases ADD COLUMN `connection_id` INT(11) DEFAULT 0 NOT NULL;
 +ALTER TABLE aliases ADD COLUMN `keepalive` INT(11) DEFAULT 0 NOT NULL;
 +ALTER TABLE aliases ADD COLUMN `partition` INT(11) DEFAULT 0 NOT NULL;
 +DELETE FROM version WHERE table_name='aliases';
 +INSERT INTO version (`table_name`, `table_version`) values ('aliases','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 97:
  
 </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: aliases
 +ALTER TABLE aliases ADD COLUMN server_id INTEGER DEFAULT 0 NOT NULL;
 +ALTER TABLE aliases ADD COLUMN connection_id INTEGER DEFAULT 0 NOT NULL;
 +ALTER TABLE aliases ADD COLUMN keepalive INTEGER DEFAULT 0 NOT NULL;
 +ALTER TABLE aliases ADD COLUMN partition INTEGER DEFAULT 0 NOT NULL;
 +DELETE FROM version WHERE TABLE_NAME='aliases';
 +INSERT INTO version (table_name, table_version) VALUES ('aliases','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.1433949777.txt.gz · Last modified: 2015/06/10 17:22 by miconda