From 5323ada3a5d26df1b6633e56567cb1a9cdb7dfb2 Mon Sep 17 00:00:00 2001 From: alex Date: Fri, 7 Mar 2008 21:47:16 +0000 Subject: - [DEV-136] database upgrade scripts created for MySQL (Alexei) git-svn-id: svn://svn.zabbix.com/trunk@5463 97f52cf1-0a1b-0410-bd0e-c28be96e8082 --- upgrades/dbpatches/1.6/mysql/patch/alerts.sql | 6 ++++-- upgrades/dbpatches/1.6/mysql/patch/config.sql | 4 ++++ upgrades/dbpatches/1.6/mysql/patch/dhosts.sql | 1 + upgrades/dbpatches/1.6/mysql/patch/drules.sql | 1 + upgrades/dbpatches/1.6/mysql/patch/events.sql | 6 ++++++ upgrades/dbpatches/1.6/mysql/patch/graphs.sql | 2 ++ upgrades/dbpatches/1.6/mysql/patch/hosts.sql | 3 +++ upgrades/dbpatches/1.6/mysql/patch/httptest.sql | 1 + upgrades/dbpatches/1.6/mysql/patch/node_cksum.sql | 11 +++++++++++ upgrades/dbpatches/1.6/mysql/patch/node_configlog.sql | 1 + upgrades/dbpatches/1.6/mysql/patch/nodes.sql | 6 ++++++ upgrades/dbpatches/1.6/mysql/patch/profiles.sql | 3 +++ upgrades/dbpatches/1.6/mysql/patch/proxies.sql | 13 +++++++++++++ upgrades/dbpatches/1.6/mysql/patch/proxy_dhistory.sql | 13 +++++++++++++ upgrades/dbpatches/1.6/mysql/patch/screens_items.sql | 1 + upgrades/dbpatches/1.6/mysql/patch/scripts.sql | 7 +++++++ .../dbpatches/1.6/mysql/patch/sysmaps_link_triggers.sql | 10 ++++++++++ upgrades/dbpatches/1.6/mysql/patch/sysmaps_links.sql | 9 +++++++++ upgrades/dbpatches/1.6/mysql/patch/trends_uint.sql | 9 +++++++++ upgrades/dbpatches/1.6/mysql/patch/triggers.sql | 4 ++++ upgrades/dbpatches/1.6/mysql/patch/users.sql | 2 ++ upgrades/dbpatches/1.6/mysql/patch/usrgrp.sql | 2 ++ 22 files changed, 113 insertions(+), 2 deletions(-) create mode 100644 upgrades/dbpatches/1.6/mysql/patch/config.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/dhosts.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/drules.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/events.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/graphs.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/hosts.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/httptest.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/node_cksum.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/node_configlog.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/nodes.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/profiles.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/proxies.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/proxy_dhistory.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/screens_items.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/scripts.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/sysmaps_link_triggers.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/sysmaps_links.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/trends_uint.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/triggers.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/users.sql create mode 100644 upgrades/dbpatches/1.6/mysql/patch/usrgrp.sql (limited to 'upgrades/dbpatches') 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; -- cgit