- [DEV-136] database upgrade scripts created for MySQL (Alexei)

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

22 files changed, 113 insertions, 2 deletions

diff --git a/upgrades/dbpatches/1.6/mysql/patch/alerts.sql b/upgrades/dbpatches/1.6/mysql/patch/alerts.sql
index 5b7b547f..9e248778 100644
--- a/upgrades/dbpatches/1.6/mysql/patch/alerts.sql
+++ b/upgrades/dbpatches/1.6/mysql/patch/alerts.sql
@@ -1,2 +1,4 @@
-update alerts set status=3
-where retries>=2
+alter table alerts drop triggerid;
+alter table alerts add eventid bigint(20) unsigned NOT NULL default '0';
+
+update alerts set status=3 where retries>=2;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/config.sql b/upgrades/dbpatches/1.6/mysql/patch/config.sql
new file mode 100644
index 00000000..9c58fde2
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/config.sql
@@ -0,0 +1,4 @@
+alter table config add  event_ack_enable int(11) NOT NULL default '1';
+alter table config add  event_expire int(11) NOT NULL default '7';
+alter table config add  event_show_max int(11) NOT NULL default '100';
+alter table config add  default_theme varchar(128) NOT NULL default 'default.css';
diff --git a/upgrades/dbpatches/1.6/mysql/patch/dhosts.sql b/upgrades/dbpatches/1.6/mysql/patch/dhosts.sql
new file mode 100644
index 00000000..6dd530f6
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/dhosts.sql
@@ -0,0 +1 @@
+alter table dhosts modify ip varchar(39) NOT NULL default '';
diff --git a/upgrades/dbpatches/1.6/mysql/patch/drules.sql b/upgrades/dbpatches/1.6/mysql/patch/drules.sql
new file mode 100644
index 00000000..677b45d5
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/drules.sql
@@ -0,0 +1 @@
+alter table drules add proxyid bigint(20) unsigned NOT NULL default '0';
diff --git a/upgrades/dbpatches/1.6/mysql/patch/events.sql b/upgrades/dbpatches/1.6/mysql/patch/events.sql
new file mode 100644
index 00000000..3bf8ccb4
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/events.sql
@@ -0,0 +1,6 @@
+alter table events drop index events_1;
+alter table events drop index events_2;
+
+CREATE INDEX events_1 on events (object,objectid,eventid);
+CREATE INDEX events_2 on events (clock);
+CREATE INDEX events_3 on events (object,objectid,clock);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/graphs.sql b/upgrades/dbpatches/1.6/mysql/patch/graphs.sql
new file mode 100644
index 00000000..8f364244
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/graphs.sql
@@ -0,0 +1,2 @@
+alter table graphs add show_legend int(11) NOT NULL default '0';
+alter table graphs add show_3d int(11) NOT NULL default '0';
diff --git a/upgrades/dbpatches/1.6/mysql/patch/hosts.sql b/upgrades/dbpatches/1.6/mysql/patch/hosts.sql
new file mode 100644
index 00000000..9bcd180a
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/hosts.sql
@@ -0,0 +1,3 @@
+alter table hosts add proxyid bigint unsigned DEFAULT '0' NOT NULL;
+alter table hosts modify ip varchar(39) DEFAULT '127.0.0.1' NOT NULL;
+CREATE INDEX hosts_3 on hosts (proxyid);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/httptest.sql b/upgrades/dbpatches/1.6/mysql/patch/httptest.sql
new file mode 100644
index 00000000..8a90989b
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/httptest.sql
@@ -0,0 +1 @@
+CREATE INDEX httptest_httptest_1 on httptest (applicationid);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/node_cksum.sql b/upgrades/dbpatches/1.6/mysql/patch/node_cksum.sql
new file mode 100644
index 00000000..c32edb88
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/node_cksum.sql
@@ -0,0 +1,11 @@
+drop table node_cksum;
+
+CREATE TABLE node_cksum (
+        nodeid          integer         DEFAULT '0'     NOT NULL,
+        tablename               varchar(64)             DEFAULT ''      NOT NULL,
+        recordid                bigint unsigned         DEFAULT '0'     NOT NULL,
+        cksumtype               integer         DEFAULT '0'     NOT NULL,
+        cksum           text            DEFAULT ''      NOT NULL,
+        sync            char(128)               DEFAULT ''      NOT NULL
+) type=InnoDB;
+CREATE INDEX node_cksum_cksum_1 on node_cksum (nodeid,tablename,recordid,cksumtype);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/node_configlog.sql b/upgrades/dbpatches/1.6/mysql/patch/node_configlog.sql
new file mode 100644
index 00000000..8aa3064a
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/node_configlog.sql
@@ -0,0 +1 @@
+drop table node_configlog.sql;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/nodes.sql b/upgrades/dbpatches/1.6/mysql/patch/nodes.sql
new file mode 100644
index 00000000..cc560943
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/nodes.sql
@@ -0,0 +1,6 @@
+alter table nodes modify ip varchar(39) DEFAULT '' NOT NULL;
+
+alter table nodes drop event_lastid;
+alter table nodes drop history_lastid;
+alter table nodes drop history_str_lastid;
+alter table nodes drop history_uint_lastid;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/profiles.sql b/upgrades/dbpatches/1.6/mysql/patch/profiles.sql
new file mode 100644
index 00000000..fc8d46ff
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/profiles.sql
@@ -0,0 +1,3 @@
+delete from profiles;
+
+alter table profiles drop index profiles_1;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/proxies.sql b/upgrades/dbpatches/1.6/mysql/patch/proxies.sql
new file mode 100644
index 00000000..5acb40b9
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/proxies.sql
@@ -0,0 +1,13 @@
+CREATE TABLE proxies (
+        proxyid         bigint unsigned         DEFAULT '0'     NOT NULL,
+        name            varchar(64)             DEFAULT ''      NOT NULL,
+        lastaccess              integer         DEFAULT '0'     NOT NULL,
+        history_lastid          bigint unsigned         DEFAULT '0'     NOT NULL,
+        history_uint_lastid             bigint unsigned         DEFAULT '0'     NOT NULL,
+        history_str_lastid              bigint unsigned         DEFAULT '0'     NOT NULL,
+        history_text_lastid             bigint unsigned         DEFAULT '0'     NOT NULL,
+        history_log_lastid              bigint unsigned         DEFAULT '0'     NOT NULL,
+        dhistory_lastid         bigint unsigned         DEFAULT '0'     NOT NULL,
+        PRIMARY KEY (proxyid)
+) type=InnoDB;
+CREATE INDEX proxies_1 on proxies (name);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/proxy_dhistory.sql b/upgrades/dbpatches/1.6/mysql/patch/proxy_dhistory.sql
new file mode 100644
index 00000000..b55248ad
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/proxy_dhistory.sql
@@ -0,0 +1,13 @@
+CREATE TABLE proxy_dhistory (
+        id              bigint unsigned                 NOT NULL        auto_increment unique,
+        clock           integer         DEFAULT '0'     NOT NULL,
+        druleid         bigint unsigned         DEFAULT '0'     NOT NULL,
+        type            integer         DEFAULT '0'     NOT NULL,
+        ip              varchar(39)             DEFAULT ''      NOT NULL,
+        port            integer         DEFAULT '0'     NOT NULL,
+        key_            varchar(255)            DEFAULT '0'     NOT NULL,
+        value           varchar(255)            DEFAULT '0'     NOT NULL,
+        status          integer         DEFAULT '0'     NOT NULL,
+        PRIMARY KEY (id)
+) type=InnoDB;
+CREATE INDEX proxy_dhistory_1 on proxy_dhistory (clock);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/screens_items.sql b/upgrades/dbpatches/1.6/mysql/patch/screens_items.sql
new file mode 100644
index 00000000..2b84a29a
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/screens_items.sql
@@ -0,0 +1 @@
+alter table screens_items add dynamic integer DEFAULT '0' NOT NULL;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/scripts.sql b/upgrades/dbpatches/1.6/mysql/patch/scripts.sql
new file mode 100644
index 00000000..7a6823b4
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/scripts.sql
@@ -0,0 +1,7 @@
+CREATE TABLE scripts (
+        scriptid                bigint unsigned         DEFAULT '0'     NOT NULL,
+        name            varchar(255)            DEFAULT ''      NOT NULL,
+        command         varchar(255)            DEFAULT ''      NOT NULL,
+        host_access             integer         DEFAULT '0'     NOT NULL,
+        PRIMARY KEY (scriptid)
+) type=InnoDB;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/sysmaps_link_triggers.sql b/upgrades/dbpatches/1.6/mysql/patch/sysmaps_link_triggers.sql
new file mode 100644
index 00000000..83bc33a7
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/sysmaps_link_triggers.sql
@@ -0,0 +1,10 @@
+-- TODO Populate data from sysmaps_links
+
+CREATE TABLE sysmaps_link_triggers (
+        linkid          bigint unsigned         DEFAULT '0'     NOT NULL,
+        triggerid               bigint unsigned                 ,
+        drawtype                integer         DEFAULT '0'     NOT NULL,
+        color           varchar(6)              DEFAULT '000000'        NOT NULL,
+        PRIMARY KEY (linkid,triggerid)
+) type=InnoDB;
+CREATE INDEX sysmaps_link_triggers_1 on sysmaps_link_triggers (linkid);
diff --git a/upgrades/dbpatches/1.6/mysql/patch/sysmaps_links.sql b/upgrades/dbpatches/1.6/mysql/patch/sysmaps_links.sql
new file mode 100644
index 00000000..010b0b59
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/sysmaps_links.sql
@@ -0,0 +1,9 @@
+alter table sysmaps_links drop triggerid;
+alter table sysmaps_links drop drawtype_off;
+alter table sysmaps_links drop color_off;
+alter table sysmaps_links drop drawtype_on;
+alter table sysmaps_links drop color_on;
+
+alter table sysmaps_links add drawtype integer DEFAULT '0' NOT NULL;
+alter table sysmaps_links add color varchar(6) DEFAULT '000000' NOT NULL;
+
diff --git a/upgrades/dbpatches/1.6/mysql/patch/trends_uint.sql b/upgrades/dbpatches/1.6/mysql/patch/trends_uint.sql
new file mode 100644
index 00000000..cd44e0f7
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/trends_uint.sql
@@ -0,0 +1,9 @@
+CREATE TABLE trends_uint (
+        itemid          bigint unsigned         DEFAULT '0'     NOT NULL,
+        clock           integer         DEFAULT '0'     NOT NULL,
+        num             integer         DEFAULT '0'     NOT NULL,
+        value_min               bigint unsigned         DEFAULT '0'     NOT NULL,
+        value_avg               bigint unsigned         DEFAULT '0'     NOT NULL,
+        value_max               bigint unsigned         DEFAULT '0'     NOT NULL,
+        PRIMARY KEY (itemid,clock)
+) type=InnoDB;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/triggers.sql b/upgrades/dbpatches/1.6/mysql/patch/triggers.sql
new file mode 100644
index 00000000..4bd5a75f
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/triggers.sql
@@ -0,0 +1,4 @@
+update triggers set comments='' where comments is null;
+alter table triggers modify comments blob DEFAULT '' NOT NULL;
+
+alter table triggers add type integer DEFAULT '0' NOT NULL;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/users.sql b/upgrades/dbpatches/1.6/mysql/patch/users.sql
new file mode 100644
index 00000000..5234b1c4
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/users.sql
@@ -0,0 +1,2 @@
+alter table users add autologin integer DEFAULT '0' NOT NULL;
+alter table users add theme varchar(128) DEFAULT 'default.css' NOT NULL;
diff --git a/upgrades/dbpatches/1.6/mysql/patch/usrgrp.sql b/upgrades/dbpatches/1.6/mysql/patch/usrgrp.sql
new file mode 100644
index 00000000..3fe42f85
--- /dev/null
+++ b/upgrades/dbpatches/1.6/mysql/patch/usrgrp.sql
@@ -0,0 +1,2 @@
+alter table usrgrp add gui_access integer DEFAULT '0' NOT NULL;
+alter table usrgrp add users_status integer DEFAULT '0' NOT NULL;