- fixed upgrade script to correctly process host-template linkage (Alexei)

   [svn merge -r4535:4538 svn://svn.zabbix.com/branches/1.4.2]


git-svn-id: svn://svn.zabbix.com/trunk@4539 97f52cf1-0a1b-0410-bd0e-c28be96e8082

4 files changed, 46 insertions, 50 deletions

diff --git a/upgrades/dbpatches/1.4/mysql/patch/hosts.sql b/upgrades/dbpatches/1.4/mysql/patch/hosts.sql
index 82bc0b33..cbe5c675 100644
--- a/upgrades/dbpatches/1.4/mysql/patch/hosts.sql
+++ b/upgrades/dbpatches/1.4/mysql/patch/hosts.sql
@@ -1,20 +1 @@
-CREATE TABLE hosts_tmp (
-	hostid		bigint unsigned		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)
-) ENGINE=InnoDB;
-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 hosts;
+-- See hosts_templates.sql
diff --git a/upgrades/dbpatches/1.4/mysql/patch/hosts_templates.sql b/upgrades/dbpatches/1.4/mysql/patch/hosts_templates.sql
index 98dc2df0..585f7176 100644
--- a/upgrades/dbpatches/1.4/mysql/patch/hosts_templates.sql
+++ b/upgrades/dbpatches/1.4/mysql/patch/hosts_templates.sql
@@ -6,7 +6,7 @@ CREATE TABLE hosts_templates_tmp (
 ) ENGINE=InnoDB;
 CREATE UNIQUE INDEX hosts_templates_1 on hosts_templates_tmp (hostid,templateid);
 
-insert into hosts_templates_tmp select NULL,hostid,templateid from  hosts_templates;
+insert into hosts_templates_tmp select NULL,hostid,templateid from hosts where templateid<>0;
 drop table  hosts_templates;
 alter table hosts_templates_tmp rename  hosts_templates;
 
@@ -21,3 +21,26 @@ 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  hosts_templates;
+
+-- hosts.sql
+
+CREATE TABLE hosts_tmp (
+	hostid		bigint unsigned		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)
+) ENGINE=InnoDB;
+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 hosts;
diff --git a/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql b/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql
index c21106e9..cbe5c675 100644
--- a/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql
+++ b/upgrades/dbpatches/1.4/postgresql/patch/hosts.sql
@@ -1,20 +1 @@
-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)
-) with OIDS;
-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;
+-- See hosts_templates.sql
diff --git a/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql b/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql
index 02ef63d6..a98cc764 100644
--- a/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql
+++ b/upgrades/dbpatches/1.4/postgresql/patch/hosts_templates.sql
@@ -6,18 +6,29 @@ CREATE TABLE hosts_templates_tmp (
 ) with OIDS;
 CREATE UNIQUE INDEX hosts_templates_tmp_1 on hosts_templates_tmp (hostid,templateid);
 
-insert into hosts_templates_tmp select NULL,hostid,templateid from  hosts_templates;
+insert into hosts_templates_tmp select hostid,hostid,templateid from  hosts where templateid<>0;
 drop table  hosts_templates;
 alter table hosts_templates_tmp rename  to hosts_templates;
 
-CREATE TABLE hosts_templates_tmp (
-	hosttemplateid	bigint DEFAULT '0'	NOT NULL,
+-- hosts
+
+CREATE TABLE hosts_tmp (
 	hostid		bigint DEFAULT '0'	NOT NULL,
-	templateid	bigint DEFAULT '0'	NOT NULL,
-	PRIMARY KEY (hosttemplateid)
+	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)
 ) with OIDS;
-CREATE UNIQUE INDEX hosts_templates_1 on hosts_templates_tmp (hostid,templateid);
+CREATE INDEX hosts_1 on hosts_tmp (host);
+CREATE INDEX hosts_2 on hosts_tmp (status);
 
-insert into hosts_templates_tmp select * from  hosts_templates;
-drop table  hosts_templates;
-alter table hosts_templates_tmp rename to hosts_templates;
+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;