diff options
Diffstat (limited to 'upgrades/dbpatches/1.4/postgresql')
67 files changed, 1004 insertions, 0 deletions
diff --git a/upgrades/dbpatches/1.4/postgresql/patch/acknowledges.sql b/upgrades/dbpatches/1.4/postgresql/patch/acknowledges.sql new file mode 100644 index 00000000..7846291f --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/acknowledges.sql @@ -0,0 +1,15 @@ +CREATE TABLE acknowledges_tmp ( + acknowledgeid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + eventid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + message varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (acknowledgeid) +); +CREATE INDEX acknowledges_1 on acknowledges_tmp (userid); +CREATE INDEX acknowledges_2 on acknowledges_tmp (eventid); +CREATE INDEX acknowledges_3 on acknowledges_tmp (clock); + +insert into acknowledges_tmp select * from acknowledges; +drop table acknowledges; +alter table acknowledges_tmp rename to acknowledges; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/actions.sql b/upgrades/dbpatches/1.4/postgresql/patch/actions.sql new file mode 100644 index 00000000..9f45bd08 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/actions.sql @@ -0,0 +1,30 @@ +CREATE TABLE actions_tmp ( + actionid bigintd DEFAULT '0' NOT NULL, + name varchar(255) DEFAULT '' NOT NULL, + eventsource integer DEFAULT '0' NOT NULL, + evaltype integer DEFAULT '0' NOT NULL, + status integer DEFAULT '0' NOT NULL, + PRIMARY KEY (actionid) +); + +CREATE TABLE operations ( + operationid bigint DEFAULT '0' NOT NULL, + actionid bigint DEFAULT '0' NOT NULL, + operationtype integer DEFAULT '0' NOT NULL, + object integer DEFAULT '0' NOT NULL, + objectid bigint DEFAULT '0' NOT NULL, + shortdata varchar(255) DEFAULT '' NOT NULL, + longdata text DEFAULT '' NOT NULL, + scripts_tmp text DEFAULT '' NOT NULL, + PRIMARY KEY (operationid) +); +CREATE INDEX operations_1 on operations (actionid); + +insert into actions_tmp select actionid,actionid,source,0,status from actions; + +insert into operations select actionid,actionid,actiontype,recipient,userid,subject,message,scripts from actions; +update operations set longdata=scripts_tmp where operationtype=1; +alter table operations drop scripts_tmp; + +drop table actions; +alter table actions_tmp rename actions; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/alerts.sql b/upgrades/dbpatches/1.4/postgresql/patch/alerts.sql new file mode 100644 index 00000000..413e0ed0 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/alerts.sql @@ -0,0 +1,26 @@ +CREATE TABLE alerts_tmp ( + alertid bigint DEFAULT '0' NOT NULL, + actionid bigint DEFAULT '0' NOT NULL, + triggerid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + mediatypeid bigint DEFAULT '0' NOT NULL, + sendto varchar(100) DEFAULT '' NOT NULL, + subject varchar(255) DEFAULT '' NOT NULL, + message text DEFAULT '' NOT NULL, + status integer DEFAULT '0' NOT NULL, + retries integer DEFAULT '0' NOT NULL, + error varchar(128) DEFAULT '' NOT NULL, + nextcheck integer DEFAULT '0' NOT NULL, + PRIMARY KEY (alertid) +); +CREATE INDEX alerts_1 on alerts_tmp (actionid); +CREATE INDEX alerts_2 on alerts_tmp (clock); +CREATE INDEX alerts_3 on alerts_tmp (triggerid); +CREATE INDEX alerts_4 on alerts_tmp (status,retries); +CREATE INDEX alerts_5 on alerts_tmp (mediatypeid); +CREATE INDEX alerts_6 on alerts_tmp (userid); + +insert into alerts_tmp select alertid,actionid,triggerid,userid,clock,mediatypeid,sendto,subject,message,status,retries,error,nextcheck from alerts; +drop table alerts; +alter table alerts_tmp rename to alerts; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/applications.sql b/upgrades/dbpatches/1.4/postgresql/patch/applications.sql new file mode 100644 index 00000000..13d05bd3 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/applications.sql @@ -0,0 +1,14 @@ +CREATE TABLE applications_tmp ( + applicationid bigint DEFAULT '0' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + name varchar(255) DEFAULT '' NOT NULL, + templateid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (applicationid) +); + +CREATE INDEX applications_1 on applications_tmp (templateid); +CREATE UNIQUE INDEX applications_2 on applications_tmp (hostid,name); + +insert into applications_tmp select * from applications; +drop table applications; +alter table applications_tmp rename to applications; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/auditlog.sql b/upgrades/dbpatches/1.4/postgresql/patch/auditlog.sql new file mode 100644 index 00000000..a262a3a8 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/auditlog.sql @@ -0,0 +1,15 @@ +CREATE TABLE auditlog_tmp ( + auditid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + action integer DEFAULT '0' NOT NULL, + resourcetype integer DEFAULT '0' NOT NULL, + details varchar(128) DEFAULT '0' NOT NULL, + PRIMARY KEY (auditid) +); +CREATE INDEX auditlog_1 on auditlog_tmp (userid,clock); +CREATE INDEX auditlog_2 on auditlog_tmp (clock); + +insert into auditlog_tmp select * from auditlog; +drop table auditlog; +alter table auditlog_tmp rename to auditlog; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/autoreg.sql b/upgrades/dbpatches/1.4/postgresql/patch/autoreg.sql new file mode 100644 index 00000000..5ef888dd --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/autoreg.sql @@ -0,0 +1,11 @@ +CREATE TABLE autoreg_tmp ( + id bigint DEFAULT '0' NOT NULL, + priority integer DEFAULT '0' NOT NULL, + pattern varchar(255) DEFAULT '' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (id) +); + +insert into autoreg_tmp select * from autoreg; +drop table autoreg; +alter table autoreg_tmp rename to autoreg; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/conditions.sql b/upgrades/dbpatches/1.4/postgresql/patch/conditions.sql new file mode 100644 index 00000000..e0f8f834 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/conditions.sql @@ -0,0 +1,13 @@ +CREATE TABLE conditions_tmp ( + conditionid bigint DEFAULT '0' NOT NULL, + actionid bigint DEFAULT '0' NOT NULL, + conditiontype integer DEFAULT '0' NOT NULL, + operator integer DEFAULT '0' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (conditionid) +); +CREATE INDEX conditions_1 on conditions_tmp (actionid); + +insert into conditions_tmp select * from conditions; +drop table conditions; +alter table conditions_tmp rename to conditions; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/config.sql b/upgrades/dbpatches/1.4/postgresql/patch/config.sql new file mode 100644 index 00000000..b7f9e2a6 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/config.sql @@ -0,0 +1,13 @@ +CREATE TABLE config_tmp ( + configid bigint DEFAULT '0' NOT NULL, + alert_history integer DEFAULT '0' NOT NULL, + event_history integer DEFAULT '0' NOT NULL, + refresh_unsupported integer DEFAULT '0' NOT NULL, + work_period varchar(100) DEFAULT '1-5,00:00-24:00' NOT NULL, + alert_usrgrpid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (configid) +); + +insert into config_tmp select 1,alert_history,alarm_history,refresh_unsupported,work_period,0 from config; +drop table config; +alter table config_tmp rename to config; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/dchecks.sql b/upgrades/dbpatches/1.4/postgresql/patch/dchecks.sql new file mode 100644 index 00000000..5cf5d873 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/dchecks.sql @@ -0,0 +1,7 @@ +CREATE TABLE dchecks ( + dcheckid bigint DEFAULT '0' NOT NULL, + druleid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + ports varchar(255) DEFAULT '0' NOT NULL, + PRIMARY KEY (dcheckid) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/dhosts.sql b/upgrades/dbpatches/1.4/postgresql/patch/dhosts.sql new file mode 100644 index 00000000..f4a1c855 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/dhosts.sql @@ -0,0 +1,10 @@ +CREATE TABLE dhosts ( + dhostid bigint DEFAULT '0' NOT NULL, + druleid bigint DEFAULT '0' NOT NULL, + ip varchar(15) DEFAULT '' NOT NULL, + status integer DEFAULT '0' NOT NULL, + lastup integer DEFAULT '0' NOT NULL, + lastdown integer DEFAULT '0' NOT NULL, + eventsent integer DEFAULT '0' NOT NULL, + PRIMARY KEY (dhostid) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/drules.sql b/upgrades/dbpatches/1.4/postgresql/patch/drules.sql new file mode 100644 index 00000000..f05f14b9 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/drules.sql @@ -0,0 +1,14 @@ +CREATE TABLE drules ( + druleid bigint DEFAULT '0' NOT NULL, + name varchar(255) DEFAULT '' NOT NULL, + ipfirst varchar(15) DEFAULT '' NOT NULL, + iplast varchar(15) DEFAULT '' NOT NULL, + delay integer DEFAULT '0' NOT NULL, + nextcheck integer DEFAULT '0' NOT NULL, + status integer DEFAULT '0' NOT NULL, + upevent integer DEFAULT '0' NOT NULL, + downevent integer DEFAULT '0' NOT NULL, + svcupevent integer DEFAULT '0' NOT NULL, + svcdownevent integer DEFAULT '0' NOT NULL, + PRIMARY KEY (druleid) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/dservices.sql b/upgrades/dbpatches/1.4/postgresql/patch/dservices.sql new file mode 100644 index 00000000..2636c81d --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/dservices.sql @@ -0,0 +1,11 @@ +CREATE TABLE dservices ( + dserviceid bigint DEFAULT '0' NOT NULL, + dhostid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + port integer DEFAULT '0' NOT NULL, + status integer DEFAULT '0' NOT NULL, + lastup integer DEFAULT '0' NOT NULL, + lastdown integer DEFAULT '0' NOT NULL, + eventsent integer DEFAULT '0' NOT NULL, + PRIMARY KEY (dserviceid) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/events.sql b/upgrades/dbpatches/1.4/postgresql/patch/events.sql new file mode 100644 index 00000000..95ef6320 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/events.sql @@ -0,0 +1,14 @@ +CREATE TABLE events ( + eventid bigint DEFAULT '0' NOT NULL, + source integer DEFAULT '0' NOT NULL, + sourceid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value integer DEFAULT '0' NOT NULL, + acknowledged integer DEFAULT '0' NOT NULL, + PRIMARY KEY (eventid) +); +CREATE INDEX events_1 on events (object,objectid,clock); +CREATE INDEX events_2 on events (clock); + +insert into events select alarmid,0,triggerid,clock,value,acknowledged from alarms; +drop table alarms; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/functions.sql b/upgrades/dbpatches/1.4/postgresql/patch/functions.sql new file mode 100644 index 00000000..ca5bd668 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/functions.sql @@ -0,0 +1,15 @@ +CREATE TABLE functions_tmp ( + functionid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + triggerid bigint DEFAULT '0' NOT NULL, + lastvalue varchar(255) , + function varchar(12) DEFAULT '' NOT NULL, + parameter varchar(255) DEFAULT '0' NOT NULL, + PRIMARY KEY (functionid) +); +CREATE INDEX functions_1 on functions_tmp (triggerid); +CREATE INDEX functions_2 on functions_tmp (itemid,function,parameter); + +insert into functions_tmp select * from functions; +drop table functions; +alter table functions_tmp rename to functions; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/graphs.sql b/upgrades/dbpatches/1.4/postgresql/patch/graphs.sql new file mode 100644 index 00000000..38a6c1a4 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/graphs.sql @@ -0,0 +1,19 @@ +CREATE TABLE graphs_tmp ( + graphid bigint DEFAULT '0' NOT NULL, + name varchar(128) DEFAULT '' NOT NULL, + width integer DEFAULT '0' NOT NULL, + height integer DEFAULT '0' NOT NULL, + yaxistype integer DEFAULT '0' NOT NULL, + yaxismin numeric(16,4) DEFAULT '0' NOT NULL, + yaxismax numeric(16,4) DEFAULT '0' NOT NULL, + templateid bigint DEFAULT '0' NOT NULL, + show_work_period integer DEFAULT '1' NOT NULL, + show_triggers integer DEFAULT '1' NOT NULL, + graphtype integer DEFAULT '0' NOT NULL, + PRIMARY KEY (graphid) +); +CREATE INDEX graphs_graphs_1 on graphs_tmp (name); + +insert into graphs_tmp select *,0 from graphs; +drop table graphs; +alter table graphs_tmp rename to graphs; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/graphs_items.sql b/upgrades/dbpatches/1.4/postgresql/patch/graphs_items.sql new file mode 100644 index 00000000..5236f0a4 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/graphs_items.sql @@ -0,0 +1,17 @@ +CREATE TABLE graphs_items_tmp ( + gitemid bigint DEFAULT '0' NOT NULL, + graphid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + drawtype integer DEFAULT '0' NOT NULL, + sortorder integer DEFAULT '0' NOT NULL, + color varchar(32) DEFAULT 'Dark Green' NOT NULL, + yaxisside integer DEFAULT '1' NOT NULL, + calc_fnc integer DEFAULT '2' NOT NULL, + type integer DEFAULT '0' NOT NULL, + periods_cnt integer DEFAULT '5' NOT NULL, + PRIMARY KEY (gitemid) +); + +insert into graphs_items_tmp select * from graphs_items; +drop table graphs_items; +alter table graphs_items_tmp rename to graphs_items; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/groups.sql b/upgrades/dbpatches/1.4/postgresql/patch/groups.sql new file mode 100644 index 00000000..8a94be71 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/groups.sql @@ -0,0 +1,10 @@ +CREATE TABLE groups_tmp ( + groupid bigint DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '' NOT NULL, + PRIMARY KEY (groupid) +); +CREATE INDEX groups_1 on groups_tmp (name); + +insert into groups_tmp select * from groups; +drop table groups; +alter table groups_tmp rename to groups; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/help_items.sql b/upgrades/dbpatches/1.4/postgresql/patch/help_items.sql new file mode 100644 index 00000000..4cfdf525 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/help_items.sql @@ -0,0 +1,10 @@ +CREATE TABLE help_items_tmp ( + itemtype integer DEFAULT '0' NOT NULL, + key_ varchar(255) DEFAULT '' NOT NULL, + description varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (itemtype,key_) +); + +insert into help_items_tmp select * from help_items; +drop table help_items; +alter table help_items_tmp rename to help_items; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history.sql b/upgrades/dbpatches/1.4/postgresql/patch/history.sql new file mode 100644 index 00000000..aa7a9664 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history.sql @@ -0,0 +1,10 @@ +CREATE TABLE history_tmp ( + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value numeric(16,4) DEFAULT '0.0000' NOT NULL +); +CREATE INDEX history_1 on history_tmp (itemid,clock); + +insert into history_tmp select * from history; +drop table history; +alter table history_tmp rename to history; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_log.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_log.sql new file mode 100644 index 00000000..b00151ee --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_log.sql @@ -0,0 +1,15 @@ +CREATE TABLE history_log_tmp ( + id bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + timestamp integer DEFAULT '0' NOT NULL, + source varchar(64) DEFAULT '' NOT NULL, + severity integer DEFAULT '0' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX history_log_1 on history_log_tmp (itemid,clock); + +insert into history_log_tmp select * from history_log; +drop table history_log; +alter table history_log_tmp rename to history_log; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_str.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_str.sql new file mode 100644 index 00000000..3f083b74 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_str.sql @@ -0,0 +1,10 @@ +CREATE TABLE history_str_tmp ( + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL +); +CREATE INDEX history_str_1 on history_str_tmp (itemid,clock); + +insert into history_str_tmp select * from history_str; +drop table history_str; +alter table history_str_tmp rename to history_str; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_str_sync.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_str_sync.sql new file mode 100644 index 00000000..091603fc --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_str_sync.sql @@ -0,0 +1,9 @@ +CREATE TABLE history_str_sync ( + id serial , + nodeid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX history_str_sync_1 on history_str_sync (nodeid,id); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_sync.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_sync.sql new file mode 100644 index 00000000..237cbcba --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_sync.sql @@ -0,0 +1,9 @@ +CREATE TABLE history_sync ( + id serial , + nodeid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value numeric(16,4) DEFAULT '0.0000' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX history_sync_1 on history_sync (nodeid,id); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_text.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_text.sql new file mode 100644 index 00000000..501e5090 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_text.sql @@ -0,0 +1,12 @@ +CREATE TABLE history_text_tmp ( + id bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value text DEFAULT '' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX history_text_1 on history_text_tmp (itemid,clock); + +insert into history_text_tmp select * from history_text; +drop table history_text; +alter table history_text_tmp rename to history_text; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_uint.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_uint.sql new file mode 100644 index 00000000..942d4d37 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_uint.sql @@ -0,0 +1,10 @@ +CREATE TABLE history_uint_tmp ( + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value bigint DEFAULT '0' NOT NULL +); +CREATE INDEX history_uint_1 on history_uint_tmp (itemid,clock); + +insert into history_uint_tmp select * from history_uint; +drop table history_uint; +alter table history_uint_tmp rename to history_uint; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/history_uint_sync.sql b/upgrades/dbpatches/1.4/postgresql/patch/history_uint_sync.sql new file mode 100644 index 00000000..369d386a --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/history_uint_sync.sql @@ -0,0 +1,9 @@ +CREATE TABLE history_uint_sync ( + id serial , + nodeid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX history_uint_sync_1 on history_uint_sync (nodeid,id); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql b/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql new file mode 100644 index 00000000..8b633a94 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql @@ -0,0 +1,20 @@ +CREATE TABLE hosts_tmp ( + hostid bigint DEFAULT '0' NOT NULL, + host varchar(64) DEFAULT '' NOT NULL, + dns varchar(64) DEFAULT '' NOT NULL, + useip integer DEFAULT '1' NOT NULL, + ip varchar(15) DEFAULT '127.0.0.1' NOT NULL, + port integer DEFAULT '0' NOT NULL, + status integer DEFAULT '0' NOT NULL, + disable_until integer DEFAULT '0' NOT NULL, + error varchar(128) DEFAULT '' NOT NULL, + available integer DEFAULT '0' NOT NULL, + errors_from integer DEFAULT '0' NOT NULL, + PRIMARY KEY (hostid) +); +CREATE INDEX hosts_1 on hosts_tmp (host); +CREATE INDEX hosts_2 on hosts_tmp (status); + +insert into hosts_tmp select hostid,host,host,useip,ip,port,status,disable_until,error,available,errors_from from hosts; +drop table hosts; +alter table hosts_tmp rename to hosts; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/hosts_groups.sql b/upgrades/dbpatches/1.4/postgresql/patch/hosts_groups.sql new file mode 100644 index 00000000..d8bfe0bf --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/hosts_groups.sql @@ -0,0 +1,24 @@ +CREATE TABLE hosts_groups_tmp ( + hostgroupid bigint DEFAULT '0' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + groupid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (hostgroupid) +); +CREATE INDEX hosts_groups_groups_1 on hosts_groups_tmp (hostid,groupid); + +insert into hosts_groups_tmp select NULL,hostid,groupid from hosts_groups; +drop table hosts_groups; +alter table hosts_groups_tmp rename hosts_groups; + + +CREATE TABLE hosts_groups_tmp ( + hostgroupid bigint DEFAULT '0' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + groupid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (hostgroupid) +); +CREATE INDEX hosts_groups_groups_1 on hosts_groups_tmp (hostid,groupid); + +insert into hosts_groups_tmp select * from hosts_groups; +drop table hosts_groups; +alter table hosts_groups_tmp rename to hosts_groups; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/hosts_profiles.sql b/upgrades/dbpatches/1.4/postgresql/patch/hosts_profiles.sql new file mode 100644 index 00000000..e6be1ab9 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/hosts_profiles.sql @@ -0,0 +1,19 @@ +CREATE TABLE hosts_profiles_tmp ( + hostid bigint DEFAULT '0' NOT NULL, + devicetype varchar(64) DEFAULT '' NOT NULL, + name varchar(64) DEFAULT '' NOT NULL, + os varchar(64) DEFAULT '' NOT NULL, + serialno varchar(64) DEFAULT '' NOT NULL, + tag varchar(64) DEFAULT '' NOT NULL, + macaddress varchar(64) DEFAULT '' NOT NULL, + hardware text DEFAULT '' NOT NULL, + software text DEFAULT '' NOT NULL, + contact text DEFAULT '' NOT NULL, + location text DEFAULT '' NOT NULL, + notes text DEFAULT '' NOT NULL, + PRIMARY KEY (hostid) +); + +insert into hosts_profiles_tmp select * from hosts_profiles; +drop table hosts_profiles; +alter table hosts_profiles_tmp rename to hosts_profiles; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql b/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql new file mode 100644 index 00000000..d42bcf5a --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql @@ -0,0 +1,23 @@ +CREATE TABLE hosts_templates_tmp ( + hosttemplateid bigint DEFAULT '0' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + templateid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (hosttemplateid) +); +CREATE UNIQUE INDEX hosts_templates_1 on hosts_templates_tmp (hostid,templateid); + +insert into hosts_templates_tmp select NULL,hostid,templateid from hosts_templates; +drop table hosts_templates; +alter table hosts_templates_tmp rename to hosts_templates; + +CREATE TABLE hosts_templates_tmp ( + hosttemplateid bigint DEFAULT '0' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + templateid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (hosttemplateid) +); +CREATE UNIQUE INDEX hosts_templates_1 on hosts_templates_tmp (hostid,templateid); + +insert into hosts_templates_tmp select * from hosts_templates; +drop table hosts_templates; +alter table hosts_templates_tmp rename to hosts_templates; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/housekeeper.sql b/upgrades/dbpatches/1.4/postgresql/patch/housekeeper.sql new file mode 100644 index 00000000..a05cb024 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/housekeeper.sql @@ -0,0 +1,11 @@ +CREATE TABLE housekeeper_tmp ( + housekeeperid bigint DEFAULT '0' NOT NULL, + tablename varchar(64) DEFAULT '' NOT NULL, + field varchar(64) DEFAULT '' NOT NULL, + value integer DEFAULT '0' NOT NULL, + PRIMARY KEY (housekeeperid) +); + +insert into housekeeper_tmp select * from housekeeper; +drop table housekeeper; +alter table housekeeper_tmp rename to housekeeper; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/httpstep.sql b/upgrades/dbpatches/1.4/postgresql/patch/httpstep.sql new file mode 100644 index 00000000..7f99fbfc --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/httpstep.sql @@ -0,0 +1,12 @@ +CREATE TABLE httpstep ( + httpstepid bigint DEFAULT '0' NOT NULL, + httptestid bigint DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '' NOT NULL, + no integer DEFAULT '0' NOT NULL, + url varchar(128) DEFAULT '' NOT NULL, + timeout integer DEFAULT '30' NOT NULL, + posts text DEFAULT '' NOT NULL, + required varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (httpstepid) +); +CREATE INDEX httpstep_httpstep_1 on httpstep (httptestid); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/httpstepitem.sql b/upgrades/dbpatches/1.4/postgresql/patch/httpstepitem.sql new file mode 100644 index 00000000..c8232b97 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/httpstepitem.sql @@ -0,0 +1,8 @@ +CREATE TABLE httpstepitem ( + httpstepitemid bigint DEFAULT '0' NOT NULL, + httpstepid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + PRIMARY KEY (httpstepitemid) +); +CREATE UNIQUE INDEX httpstepitem_httpstepitem_1 on httpstepitem (httpstepid,itemid); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/httptest.sql b/upgrades/dbpatches/1.4/postgresql/patch/httptest.sql new file mode 100644 index 00000000..ac1ed843 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/httptest.sql @@ -0,0 +1,18 @@ +CREATE TABLE httptest ( + httptestid bigint DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '' NOT NULL, + applicationid bigint DEFAULT '0' NOT NULL, + lastcheck integer DEFAULT '0' NOT NULL, + nextcheck integer DEFAULT '0' NOT NULL, + curstate integer DEFAULT '0' NOT NULL, + curstep integer DEFAULT '0' NOT NULL, + lastfailedstep bigint DEFAULT '0' NOT NULL, + delay integer DEFAULT '60' NOT NULL, + status integer DEFAULT '0' NOT NULL, + macros text DEFAULT '' NOT NULL, + agent varchar(255) DEFAULT '' NOT NULL, + time numeric(16,4) DEFAULT '0' NOT NULL, + error varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (httptestid) +); +CREATE INDEX httptest_httptest_1 on httptest (httptestid); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/httptestitem.sql b/upgrades/dbpatches/1.4/postgresql/patch/httptestitem.sql new file mode 100644 index 00000000..17ffd7c3 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/httptestitem.sql @@ -0,0 +1,8 @@ +CREATE TABLE httptestitem ( + httptestitemid bigint DEFAULT '0' NOT NULL, + httptestid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + PRIMARY KEY (httptestitemid) +); +CREATE UNIQUE INDEX httptestitem_httptestitem_1 on httptestitem (httptestid,itemid); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/ids.sql b/upgrades/dbpatches/1.4/postgresql/patch/ids.sql new file mode 100644 index 00000000..8e49ef0e --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/ids.sql @@ -0,0 +1,7 @@ +CREATE TABLE ids ( + nodeid integer DEFAULT '0' NOT NULL, + table_name varchar(64) DEFAULT '' NOT NULL, + field_name varchar(64) DEFAULT '' NOT NULL, + nextid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (nodeid,table_name,field_name) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/images.sql b/upgrades/dbpatches/1.4/postgresql/patch/images.sql new file mode 100644 index 00000000..250068ae --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/images.sql @@ -0,0 +1,12 @@ +CREATE TABLE images_tmp ( + imageid bigint DEFAULT '0' NOT NULL, + imagetype integer DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '0' NOT NULL, + image bytea DEFAULT '' NOT NULL, + PRIMARY KEY (imageid) +); +CREATE INDEX images_1 on images_tmp (imagetype,name); + +insert into images_tmp select * from images; +drop table images; +alter table images_tmp rename to images; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/items.sql b/upgrades/dbpatches/1.4/postgresql/patch/items.sql new file mode 100644 index 00000000..f34aac7d --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/items.sql @@ -0,0 +1,43 @@ +CREATE TABLE items_tmp ( + itemid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + snmp_community varchar(64) DEFAULT '' NOT NULL, + snmp_oid varchar(255) DEFAULT '' NOT NULL, + snmp_port integer DEFAULT '161' NOT NULL, + hostid bigint DEFAULT '0' NOT NULL, + description varchar(255) DEFAULT '' NOT NULL, + key_ varchar(255) DEFAULT '' NOT NULL, + delay integer DEFAULT '0' NOT NULL, + history integer DEFAULT '90' NOT NULL, + trends integer DEFAULT '365' NOT NULL, + nextcheck integer DEFAULT '0' NOT NULL, + lastvalue varchar(255) NULL, + lastclock integer NULL, + prevvalue varchar(255) NULL, + status integer DEFAULT '0' NOT NULL, + value_type integer DEFAULT '0' NOT NULL, + trapper_hosts varchar(255) DEFAULT '' NOT NULL, + units varchar(10) DEFAULT '' NOT NULL, + multiplier integer DEFAULT '0' NOT NULL, + delta integer DEFAULT '0' NOT NULL, + prevorgvalue varchar(255) NULL, + snmpv3_securityname varchar(64) DEFAULT '' NOT NULL, + snmpv3_securitylevel integer DEFAULT '0' NOT NULL, + snmpv3_authpassphrase varchar(64) DEFAULT '' NOT NULL, + snmpv3_privpassphrase varchar(64) DEFAULT '' NOT NULL, + formula varchar(255) DEFAULT '0' NOT NULL, + error varchar(128) DEFAULT '' NOT NULL, + lastlogsize integer DEFAULT '0' NOT NULL, + logtimefmt varchar(64) DEFAULT '' NOT NULL, + templateid bigint DEFAULT '0' NOT NULL, + valuemapid bigint DEFAULT '0' NOT NULL, + delay_flex varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (itemid) +); +CREATE UNIQUE INDEX items_1 on items_tmp (hostid,key_); +CREATE INDEX items_2 on items_tmp (nextcheck); +CREATE INDEX items_3 on items_tmp (status); + +insert into items_tmp select *,'' from items; +drop table items; +alter table items_tmp rename to items; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/items_applications.sql b/upgrades/dbpatches/1.4/postgresql/patch/items_applications.sql new file mode 100644 index 00000000..9c345851 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/items_applications.sql @@ -0,0 +1,23 @@ +CREATE TABLE items_applications_tmp ( + itemappid bigint DEFAULT '0' NOT NULL, + applicationid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (itemappid) +); +CREATE INDEX items_applications_1 on items_applications_tmp (applicationid,itemid); + +insert into items_applications_tmp select NULL,applicationid,applicationid from items_applications; +drop table items_applications; +alter table items_applications_tmp rename to items_applications; + +CREATE TABLE items_applications_tmp ( + itemappid bigint DEFAULT '0' NOT NULL, + applicationid bigint DEFAULT '0' NOT NULL, + itemid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (itemappid) +); +CREATE INDEX items_applications_1 on items_applications_tmp (applicationid,itemid); + +insert into items_applications_tmp select * from items_applications; +drop table items_applications; +alter table items_applications_tmp rename to items_applications; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/mappings.sql b/upgrades/dbpatches/1.4/postgresql/patch/mappings.sql new file mode 100644 index 00000000..8c3d013d --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/mappings.sql @@ -0,0 +1,12 @@ +CREATE TABLE mappings_tmp ( + mappingid bigint DEFAULT '0' NOT NULL, + valuemapid bigint DEFAULT '0' NOT NULL, + value varchar(64) DEFAULT '' NOT NULL, + newvalue varchar(64) DEFAULT '' NOT NULL, + PRIMARY KEY (mappingid) +); +CREATE INDEX mappings_1 on mappings_tmp (valuemapid); + +insert into mappings_tmp select * from mappings; +drop table mappings; +alter table mappings_tmp rename to mappings; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/media.sql b/upgrades/dbpatches/1.4/postgresql/patch/media.sql new file mode 100644 index 00000000..b931ebe1 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/media.sql @@ -0,0 +1,16 @@ +CREATE TABLE media_tmp ( + mediaid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + mediatypeid bigint DEFAULT '0' NOT NULL, + sendto varchar(100) DEFAULT '' NOT NULL, + active integer DEFAULT '0' NOT NULL, + severity integer DEFAULT '63' NOT NULL, + period varchar(100) DEFAULT '1-7,00:00-23:59' NOT NULL, + PRIMARY KEY (mediaid) +); +CREATE INDEX media_1 on media_tmp (userid); +CREATE INDEX media_2 on media_tmp (mediatypeid); + +insert into media_tmp select * from media; +drop table media; +alter table media_tmp rename to media; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/media_type.sql b/upgrades/dbpatches/1.4/postgresql/patch/media_type.sql new file mode 100644 index 00000000..898d2f3a --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/media_type.sql @@ -0,0 +1,17 @@ +CREATE TABLE media_type_tmp ( + mediatypeid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + description varchar(100) DEFAULT '' NOT NULL, + smtp_server varchar(255) DEFAULT '' NOT NULL, + smtp_helo varchar(255) DEFAULT '' NOT NULL, + smtp_email varchar(255) DEFAULT '' NOT NULL, + exec_path varchar(255) DEFAULT '' NOT NULL, + gsm_modem varchar(255) DEFAULT '' NOT NULL, + username varchar(255) DEFAULT '' NOT NULL, + passwd varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (mediatypeid) +); + +insert into media_type_tmp select mediatypeid,type,description,smtp_server,smtp_helo,smtp_email,exec_path,gsm_modem,'','' from media_type; +drop table media_type; +alter table media_type_tmp rename to media_type; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/node_cksum.sql b/upgrades/dbpatches/1.4/postgresql/patch/node_cksum.sql new file mode 100644 index 00000000..f1edfb85 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/node_cksum.sql @@ -0,0 +1,11 @@ +CREATE TABLE node_cksum ( + cksumid bigint DEFAULT '0' NOT NULL, + nodeid bigint DEFAULT '0' NOT NULL, + tablename varchar(64) DEFAULT '' NOT NULL, + fieldname varchar(64) DEFAULT '' NOT NULL, + recordid bigint DEFAULT '0' NOT NULL, + cksumtype integer DEFAULT '0' NOT NULL, + cksum char(32) DEFAULT '' NOT NULL, + PRIMARY KEY (cksumid) +); +CREATE INDEX node_cksum_cksum_1 on node_cksum (nodeid,tablename,fieldname,recordid,cksumtype); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/node_configlog.sql b/upgrades/dbpatches/1.4/postgresql/patch/node_configlog.sql new file mode 100644 index 00000000..4e3fe54c --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/node_configlog.sql @@ -0,0 +1,12 @@ +CREATE TABLE node_configlog ( + conflogid bigint DEFAULT '0' NOT NULL, + nodeid bigint DEFAULT '0' NOT NULL, + tablename varchar(64) DEFAULT '' NOT NULL, + recordid bigint DEFAULT '0' NOT NULL, + operation integer DEFAULT '0' NOT NULL, + sync_master integer DEFAULT '0' NOT NULL, + sync_slave integer DEFAULT '0' NOT NULL, + PRIMARY KEY (nodeid,conflogid) +); +CREATE INDEX node_configlog_configlog_1 on node_configlog (conflogid); +CREATE INDEX node_configlog_configlog_2 on node_configlog (nodeid,tablename); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/nodes.sql b/upgrades/dbpatches/1.4/postgresql/patch/nodes.sql new file mode 100644 index 00000000..9054c83b --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/nodes.sql @@ -0,0 +1,16 @@ +CREATE TABLE nodes ( + nodeid integer DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '0' NOT NULL, + timezone integer DEFAULT '0' NOT NULL, + ip varchar(15) DEFAULT '' NOT NULL, + port integer DEFAULT '10051' NOT NULL, + slave_history integer DEFAULT '30' NOT NULL, + slave_trends integer DEFAULT '365' NOT NULL, + event_lastid bigint DEFAULT '0' NOT NULL, + history_lastid bigint DEFAULT '0' NOT NULL, + history_str_lastid bigint DEFAULT '0' NOT NULL, + history_uint_lastid bigint DEFAULT '0' NOT NULL, + nodetype integer DEFAULT '0' NOT NULL, + masterid integer DEFAULT '0' NOT NULL, + PRIMARY KEY (nodeid) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/operations.sql b/upgrades/dbpatches/1.4/postgresql/patch/operations.sql new file mode 100644 index 00000000..85631027 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/operations.sql @@ -0,0 +1 @@ +-- See actions.sql diff --git a/upgrades/dbpatches/1.4/postgresql/patch/profiles.sql b/upgrades/dbpatches/1.4/postgresql/patch/profiles.sql new file mode 100644 index 00000000..f56a5c0f --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/profiles.sql @@ -0,0 +1,27 @@ +CREATE TABLE profiles_tmp ( + profileid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + idx varchar(64) DEFAULT '' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + valuetype integer DEFAULT 0 NOT NULL, + PRIMARY KEY (profileid) +); +CREATE UNIQUE INDEX profiles_1 on profiles_tmp (userid,idx); + +insert into profiles_tmp select NULL,userid,idx,value,valuetype from profiles; +drop table profiles; +alter table profiles_tmp rename to profiles; + +CREATE TABLE profiles_tmp ( + profileid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + idx varchar(64) DEFAULT '' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + valuetype integer DEFAULT 0 NOT NULL, + PRIMARY KEY (profileid) +); +CREATE UNIQUE INDEX profiles_1 on profiles_tmp (userid,idx); + +insert into profiles_tmp select * from profiles; +drop table profiles; +alter table profiles_tmp rename to profiles; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/rights.sql b/upgrades/dbpatches/1.4/postgresql/patch/rights.sql new file mode 100644 index 00000000..19d72064 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/rights.sql @@ -0,0 +1,13 @@ +CREATE TABLE rights_tmp ( + rightid bigint DEFAULT '0' NOT NULL, + groupid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + permission integer DEFAULT '0' NOT NULL, + id bigint, + PRIMARY KEY (rightid) +); +CREATE INDEX rights_1 on rights_tmp (groupid); + +--insert into rights_tmp select rightid,groupid,type::integer,permission,id from rights; +drop table rights; +alter table rights_tmp rename to rights; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/screens.sql b/upgrades/dbpatches/1.4/postgresql/patch/screens.sql new file mode 100644 index 00000000..f3c42074 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/screens.sql @@ -0,0 +1,11 @@ +CREATE TABLE screens_tmp ( + screenid bigint DEFAULT '0' NOT NULL, + name varchar(255) DEFAULT 'Screen' NOT NULL, + hsize integer DEFAULT '1' NOT NULL, + vsize integer DEFAULT '1' NOT NULL, + PRIMARY KEY (screenid) +); + +insert into screens_tmp select * from screens; +drop table screens; +alter table screens_tmp rename to screens; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/screens_items.sql b/upgrades/dbpatches/1.4/postgresql/patch/screens_items.sql new file mode 100644 index 00000000..d00811a6 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/screens_items.sql @@ -0,0 +1,45 @@ +CREATE TABLE screens_items_tmp ( + screenitemid bigint DEFAULT '0' NOT NULL, + screenid bigint DEFAULT '0' NOT NULL, + resourcetype integer DEFAULT '0' NOT NULL, + resourceid bigint DEFAULT '0' NOT NULL, + width integer DEFAULT '320' NOT NULL, + height integer DEFAULT '200' NOT NULL, + x integer DEFAULT '0' NOT NULL, + y integer DEFAULT '0' NOT NULL, + colspan integer DEFAULT '0' NOT NULL, + rowspan integer DEFAULT '0' NOT NULL, + elements integer DEFAULT '25' NOT NULL, + valign integer DEFAULT '0' NOT NULL, + halign integer DEFAULT '0' NOT NULL, + style integer DEFAULT '0' NOT NULL, + url varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (screenitemid) +); + +insert into screens_items_tmp select NULL,screenid,resourcetype,resourceid,width,height,x,y,colspan,rowspan,elements,valign,halign,style,url from screens_items; +drop table screens_items; +alter table screens_items_tmp rename to screens_items; + +CREATE TABLE screens_items_tmp ( + screenitemid bigint DEFAULT '0' NOT NULL, + screenid bigint DEFAULT '0' NOT NULL, + resourcetype integer DEFAULT '0' NOT NULL, + resourceid bigint DEFAULT '0' NOT NULL, + width integer DEFAULT '320' NOT NULL, + height integer DEFAULT '200' NOT NULL, + x integer DEFAULT '0' NOT NULL, + y integer DEFAULT '0' NOT NULL, + colspan integer DEFAULT '0' NOT NULL, + rowspan integer DEFAULT '0' NOT NULL, + elements integer DEFAULT '25' NOT NULL, + valign integer DEFAULT '0' NOT NULL, + halign integer DEFAULT '0' NOT NULL, + style integer DEFAULT '0' NOT NULL, + url varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (screenitemid) +); + +insert into screens_items_tmp select * from screens_items; +drop table screens_items; +alter table screens_items_tmp rename to screens_items; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/service_alarms.sql b/upgrades/dbpatches/1.4/postgresql/patch/service_alarms.sql new file mode 100644 index 00000000..f595afab --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/service_alarms.sql @@ -0,0 +1,13 @@ +CREATE TABLE service_alarms_tmp ( + servicealarmid bigint DEFAULT '0' NOT NULL, + serviceid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value integer DEFAULT '0' NOT NULL, + PRIMARY KEY (servicealarmid) +); +CREATE INDEX service_alarms_1 on service_alarms_tmp (serviceid,clock); +CREATE INDEX service_alarms_2 on service_alarms_tmp (clock); + +insert into service_alarms_tmp select * from service_alarms; +drop table service_alarms; +alter table service_alarms_tmp rename to service_alarms; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/services.sql b/upgrades/dbpatches/1.4/postgresql/patch/services.sql new file mode 100644 index 00000000..efafc4e5 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/services.sql @@ -0,0 +1,15 @@ +CREATE TABLE services_tmp ( + serviceid bigint DEFAULT '0' NOT NULL, + name varchar(128) DEFAULT '' NOT NULL, + status integer DEFAULT '0' NOT NULL, + algorithm integer DEFAULT '0' NOT NULL, + triggerid bigint , + showsla integer DEFAULT '0' NOT NULL, + goodsla numeric(5,2) DEFAULT '99.9' NOT NULL, + sortorder integer DEFAULT '0' NOT NULL, + PRIMARY KEY (serviceid) +); + +insert into services_tmp select * from services; +drop table services; +alter table services_tmp rename to services; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/services_links.sql b/upgrades/dbpatches/1.4/postgresql/patch/services_links.sql new file mode 100644 index 00000000..2e5e9253 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/services_links.sql @@ -0,0 +1,13 @@ +CREATE TABLE services_links_tmp ( + linkid bigint DEFAULT '0' NOT NULL, + serviceupid bigint DEFAULT '0' NOT NULL, + servicedownid bigint DEFAULT '0' NOT NULL, + soft integer DEFAULT '0' NOT NULL, + PRIMARY KEY (linkid) +); +CREATE INDEX services_links_links_1 on services_links_tmp (servicedownid); +CREATE UNIQUE INDEX services_links_links_2 on services_links_tmp (serviceupid,servicedownid); + +insert into services_links_tmp select * from services_links; +drop table services_links; +alter table services_links_tmp rename to services_links; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/services_times.sql b/upgrades/dbpatches/1.4/postgresql/patch/services_times.sql new file mode 100644 index 00000000..e9e2e305 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/services_times.sql @@ -0,0 +1,10 @@ +CREATE TABLE services_times ( + timeid bigint DEFAULT '0' NOT NULL, + serviceid bigint DEFAULT '0' NOT NULL, + type integer DEFAULT '0' NOT NULL, + ts_from integer DEFAULT '0' NOT NULL, + ts_to integer DEFAULT '0' NOT NULL, + note varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (timeid) +); +CREATE INDEX services_times_times_1 on services_times (serviceid,type,ts_from,ts_to); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/sessions.sql b/upgrades/dbpatches/1.4/postgresql/patch/sessions.sql new file mode 100644 index 00000000..d9d3d45d --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/sessions.sql @@ -0,0 +1,10 @@ +CREATE TABLE sessions_tmp ( + sessionid varchar(32) DEFAULT '' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + lastaccess integer DEFAULT '0' NOT NULL, + PRIMARY KEY (sessionid) +); + +insert into sessions_tmp select * from sessions; +drop table sessions; +alter table sessions_tmp rename to sessions; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/slides.sql b/upgrades/dbpatches/1.4/postgresql/patch/slides.sql new file mode 100644 index 00000000..4d5fdd3a --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/slides.sql @@ -0,0 +1,9 @@ +CREATE TABLE slides ( + slideid bigint DEFAULT '0' NOT NULL, + slideshowid bigint DEFAULT '0' NOT NULL, + step integer DEFAULT '0' NOT NULL, + delay integer DEFAULT '0' NOT NULL, + PRIMARY KEY (slideid) +); +CREATE INDEX slides_slides_1 on slides (slideshowid); + diff --git a/upgrades/dbpatches/1.4/postgresql/patch/slideshows.sql b/upgrades/dbpatches/1.4/postgresql/patch/slideshows.sql new file mode 100644 index 00000000..ad806da0 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/slideshows.sql @@ -0,0 +1,6 @@ +CREATE TABLE slideshows ( + slideshowid bigint DEFAULT '0' NOT NULL, + name varchar(255) DEFAULT '' NOT NULL, + delay integer DEFAULT '0' NOT NULL, + PRIMARY KEY (slideshowid) +); diff --git a/upgrades/dbpatches/1.4/postgresql/patch/sysmaps.sql b/upgrades/dbpatches/1.4/postgresql/patch/sysmaps.sql new file mode 100644 index 00000000..975530db --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/sysmaps.sql @@ -0,0 +1,15 @@ +CREATE TABLE sysmaps_tmp ( + sysmapid bigint DEFAULT '0' NOT NULL, + name varchar(128) DEFAULT '' NOT NULL, + width integer DEFAULT '0' NOT NULL, + height integer DEFAULT '0' NOT NULL, + backgroundid bigint DEFAULT '0' NOT NULL, + label_type integer DEFAULT '0' NOT NULL, + label_location integer DEFAULT '0' NOT NULL, + PRIMARY KEY (sysmapid) +); +CREATE INDEX sysmaps_1 on sysmaps_tmp (name); + +insert into sysmaps_tmp select s.sysmapid,s.name,s.width,s.height,i.imageid,s.label_type,s.label_location from sysmaps s,images i where s.background=i.name; +drop table sysmaps; +alter table sysmaps_tmp rename to sysmaps; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/sysmaps_elements.sql b/upgrades/dbpatches/1.4/postgresql/patch/sysmaps_elements.sql new file mode 100644 index 00000000..97c2e847 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/sysmaps_elements.sql @@ -0,0 +1,19 @@ +CREATE TABLE sysmaps_elements_tmp ( + selementid bigint DEFAULT '0' NOT NULL, + sysmapid bigint DEFAULT '0' NOT NULL, + elementid bigint DEFAULT '0' NOT NULL, + elementtype integer DEFAULT '0' NOT NULL, + iconid_off bigint DEFAULT '0' NOT NULL, + iconid_on bigint DEFAULT '0' NOT NULL, + iconid_unknown bigint DEFAULT '0' NOT NULL, + label varchar(128) DEFAULT '' NOT NULL, + label_location integer NULL, + x integer DEFAULT '0' NOT NULL, + y integer DEFAULT '0' NOT NULL, + url varchar(255) DEFAULT '' NOT NULL, + PRIMARY KEY (selementid) +); + +insert into sysmaps_elements_tmp select s.selementid,s.sysmapid,s.elementid,s.elementtype,i1.imageid,i2.imageid,i1.imageid,s.label,s.label_location,s.x,s.y,s.url from sysmaps_elements s,images i1,images i2 where s.icon=i1.name and s.icon_on=i2.name; +drop table sysmaps_elements; +alter table sysmaps_elements_tmp rename to sysmaps_elements; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/sysmaps_links.sql b/upgrades/dbpatches/1.4/postgresql/patch/sysmaps_links.sql new file mode 100644 index 00000000..9ae49130 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/sysmaps_links.sql @@ -0,0 +1,16 @@ +CREATE TABLE sysmaps_links_tmp ( + linkid bigint DEFAULT '0' NOT NULL, + sysmapid bigint DEFAULT '0' NOT NULL, + selementid1 bigint DEFAULT '0' NOT NULL, + selementid2 bigint DEFAULT '0' NOT NULL, + triggerid bigint , + drawtype_off integer DEFAULT '0' NOT NULL, + color_off varchar(32) DEFAULT 'Black' NOT NULL, + drawtype_on integer DEFAULT '0' NOT NULL, + color_on varchar(32) DEFAULT 'Red' NOT NULL, + PRIMARY KEY (linkid) +); + +insert into sysmaps_links_tmp select * from sysmaps_links; +drop table sysmaps_links; +alter table sysmaps_links_tmp rename to sysmaps_links; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/trends.sql b/upgrades/dbpatches/1.4/postgresql/patch/trends.sql new file mode 100644 index 00000000..7e10db00 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/trends.sql @@ -0,0 +1,13 @@ +CREATE TABLE trends_tmp ( + itemid bigint DEFAULT '0' NOT NULL, + clock integer DEFAULT '0' NOT NULL, + num integer DEFAULT '0' NOT NULL, + value_min numeric(16,4) DEFAULT '0.0000' NOT NULL, + value_avg numeric(16,4) DEFAULT '0.0000' NOT NULL, + value_max numeric(16,4) DEFAULT '0.0000' NOT NULL, + PRIMARY KEY (itemid,clock) +); + +insert into trends_tmp select * from trends; +drop table trends; +alter table trends_tmp rename to trends; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/trigger_depends.sql b/upgrades/dbpatches/1.4/postgresql/patch/trigger_depends.sql new file mode 100644 index 00000000..0a1974db --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/trigger_depends.sql @@ -0,0 +1,25 @@ +CREATE TABLE trigger_depends_tmp ( + triggerdepid bigint DEFAULT '0' NOT NULL, + triggerid_down bigint DEFAULT '0' NOT NULL, + triggerid_up bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (triggerdepid) +); +CREATE INDEX trigger_depends_1 on trigger_depends_tmp (triggerid_down,triggerid_up); +CREATE INDEX trigger_depends_2 on trigger_depends_tmp (triggerid_up); + +insert into trigger_depends_tmp select NULL,triggerid_down,triggerid_up from trigger_depends; +drop table trigger_depends; +alter table trigger_depends_tmp rename to trigger_depends; + +CREATE TABLE trigger_depends_tmp ( + triggerdepid bigint DEFAULT '0' NOT NULL, + triggerid_down bigint DEFAULT '0' NOT NULL, + triggerid_up bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (triggerdepid) +); +CREATE INDEX trigger_depends_1 on trigger_depends_tmp (triggerid_down,triggerid_up); +CREATE INDEX trigger_depends_2 on trigger_depends_tmp (triggerid_up); + +insert into trigger_depends_tmp select * from trigger_depends; +drop table trigger_depends; +alter table trigger_depends_tmp rename to trigger_depends; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/triggers.sql b/upgrades/dbpatches/1.4/postgresql/patch/triggers.sql new file mode 100644 index 00000000..57c348d0 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/triggers.sql @@ -0,0 +1,21 @@ +CREATE TABLE triggers_tmp ( + triggerid bigint DEFAULT '0' NOT NULL, + expression varchar(255) DEFAULT '' NOT NULL, + description varchar(255) DEFAULT '' NOT NULL, + url varchar(255) DEFAULT '' NOT NULL, + status integer DEFAULT '0' NOT NULL, + value integer DEFAULT '0' NOT NULL, + priority integer DEFAULT '0' NOT NULL, + lastchange integer DEFAULT '0' NOT NULL, + dep_level integer DEFAULT '0' NOT NULL, + comments text , + error varchar(128) DEFAULT '' NOT NULL, + templateid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (triggerid) +); +CREATE INDEX triggers_1 on triggers_tmp (status); +CREATE INDEX triggers_2 on triggers_tmp (value); + +insert into triggers_tmp select * from triggers; +drop table triggers; +alter table triggers_tmp rename to triggers; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/users.sql b/upgrades/dbpatches/1.4/postgresql/patch/users.sql new file mode 100644 index 00000000..67c0bbe1 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/users.sql @@ -0,0 +1,19 @@ +CREATE TABLE users_tmp ( + userid bigint DEFAULT '0' NOT NULL, + alias varchar(100) DEFAULT '' NOT NULL, + name varchar(100) DEFAULT '' NOT NULL, + surname varchar(100) DEFAULT '' NOT NULL, + passwd char(32) DEFAULT '' NOT NULL, + url varchar(255) DEFAULT '' NOT NULL, + autologout integer DEFAULT '900' NOT NULL, + lang varchar(5) DEFAULT 'en_gb' NOT NULL, + refresh integer DEFAULT '30' NOT NULL, + type integer DEFAULT '0' NOT NULL, + PRIMARY KEY (userid) +); +CREATE INDEX users_1 on users_tmp (alias); + +insert into users_tmp select userid,alias,name,surname,passwd,url,autologout,lang,refresh,1 from users; +update users_tmp set type=3 where name='Admin'; +drop table users; +alter table users_tmp rename to users; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/users_groups.sql b/upgrades/dbpatches/1.4/postgresql/patch/users_groups.sql new file mode 100644 index 00000000..abe3866b --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/users_groups.sql @@ -0,0 +1,23 @@ +CREATE TABLE users_groups_tmp ( + id bigint DEFAULT '0' NOT NULL, + usrgrpid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX users_groups_1 on users_groups_tmp (usrgrpid,userid); + +insert into users_groups_tmp select NULL,usrgrpid,userid from users_groups; +drop table users_groups; +alter table users_groups_tmp rename to users_groups; + +CREATE TABLE users_groups_tmp ( + id bigint DEFAULT '0' NOT NULL, + usrgrpid bigint DEFAULT '0' NOT NULL, + userid bigint DEFAULT '0' NOT NULL, + PRIMARY KEY (id) +); +CREATE INDEX users_groups_1 on users_groups_tmp (usrgrpid,userid); + +insert into users_groups_tmp select * from users_groups; +drop table users_groups; +alter table users_groups_tmp rename to users_groups; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/usrgrp.sql b/upgrades/dbpatches/1.4/postgresql/patch/usrgrp.sql new file mode 100644 index 00000000..5ad81ff7 --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/usrgrp.sql @@ -0,0 +1,10 @@ +CREATE TABLE usrgrp_tmp ( + usrgrpid bigint DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '' NOT NULL, + PRIMARY KEY (usrgrpid) +); +CREATE INDEX usrgrp_1 on usrgrp_tmp (name); + +insert into usrgrp_tmp select * from usrgrp; +drop table usrgrp; +alter table usrgrp_tmp rename to usrgrp; diff --git a/upgrades/dbpatches/1.4/postgresql/patch/valuemaps.sql b/upgrades/dbpatches/1.4/postgresql/patch/valuemaps.sql new file mode 100644 index 00000000..addad09c --- /dev/null +++ b/upgrades/dbpatches/1.4/postgresql/patch/valuemaps.sql @@ -0,0 +1,10 @@ +CREATE TABLE valuemaps_tmp ( + valuemapid bigint DEFAULT '0' NOT NULL, + name varchar(64) DEFAULT '' NOT NULL, + PRIMARY KEY (valuemapid) +); +CREATE INDEX valuemaps_1 on valuemaps_tmp (name); + +insert into valuemaps_tmp select * from valuemaps; +drop table valuemaps; +alter table valuemaps_tmp rename to valuemaps; |