....I..... [ZBX-7108] fixed duplicate entry error on graph_theme when upgrading proxie database

4 files changed, 77 insertions, 16 deletions

diff --git a/upgrades/dbpatches/2.0/ibm_db2/patch/graph_theme.sql b/upgrades/dbpatches/2.0/ibm_db2/patch/graph_theme.sql
index bcce3946fba..fff1827020a 100644
--- a/upgrades/dbpatches/2.0/ibm_db2/patch/graph_theme.sql
+++ b/upgrades/dbpatches/2.0/ibm_db2/patch/graph_theme.sql
@@ -15,9 +15,25 @@ UPDATE graph_theme SET theme = 'darkblue' WHERE theme = 'css_bb.css'
 UPDATE graph_theme SET theme = 'originalblue' WHERE theme = 'css_ob.css'
 /
 -- Insert new graph theme
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview) VALUES ((SELECT MAX(graphthemeid) + 1 FROM graph_theme), 'Dark orange', 'darkorange', '333333', '0A0A0A', '888888', '222222', '4F4F4F', 'EFEFEF', 'DFDFDF', 'FF5500', 'FF5500', 'FF1111', '1F1F1F', 1, 1)
-/
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview) VALUES ((SELECT MAX(graphthemeid) + 1 FROM graph_theme), 'Classic', 'classic', 'F0F0F0', 'FFFFFF', '333333', 'CCCCCC', 'AAAAAA', '000000', '222222', 'AA4444', '11CC11', 'CC1111', 'E0E0E0', 1, 1)
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Dark orange' AS description, 'darkorange' AS theme,
+	'333333' AS backgroundcolor, '0A0A0A' AS graphcolor, '888888' AS graphbordercolor, '222222' AS gridcolor,
+	'4F4F4F' AS maingridcolor, 'EFEFEF' AS gridbordercolor, 'DFDFDF' AS textcolor, 'FF5500' AS highlightcolor,
+	'FF5500' AS leftpercentilecolor, 'FF1111' AS rightpercentilecolor, '1F1F1F' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+FROM dual WHERE EXISTS (SELECT NULL FROM graph_theme)
+/
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Classic' AS description, 'classic' AS theme,
+	'F0F0F0' AS backgroundcolor, 'FFFFFF' AS graphcolor, '333333' AS graphbordercolor, 'CCCCCC' AS gridcolor,
+	'AAAAAA' AS maingridcolor, '000000' AS gridbordercolor, '222222' AS textcolor, 'AA4444' AS highlightcolor,
+	'11CC11' AS leftpercentilecolor, 'CC1111' AS rightpercentilecolor, 'E0E0E0' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+FROM dual WHERE EXISTS (SELECT NULL FROM graph_theme)
 /
 DELETE FROM ids WHERE table_name = 'graph_theme'
 /
diff --git a/upgrades/dbpatches/2.0/mysql/patch/graph_theme.sql b/upgrades/dbpatches/2.0/mysql/patch/graph_theme.sql
index 5bb30288043..0ec59e744dd 100644
--- a/upgrades/dbpatches/2.0/mysql/patch/graph_theme.sql
+++ b/upgrades/dbpatches/2.0/mysql/patch/graph_theme.sql
@@ -5,9 +5,24 @@ UPDATE graph_theme SET theme = 'darkblue' WHERE theme = 'css_bb.css';
 UPDATE graph_theme SET theme = 'originalblue' WHERE theme = 'css_ob.css';
 
 -- Insert new graph theme
-SET @graphthemeid = (SELECT MAX(graphthemeid) FROM graph_theme);
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
-VALUES
-(@graphthemeid + 1, 'Dark orange', 'darkorange', '333333', '0A0A0A', '888888', '222222', '4F4F4F', 'EFEFEF', 'DFDFDF', 'FF5500', 'FF5500', 'FF1111', '1F1F1F', 1, 1),
-(@graphthemeid + 2, 'Classic', 'classic', 'F0F0F0', 'FFFFFF', '333333', 'CCCCCC', 'AAAAAA', '000000', '222222', 'AA4444', '11CC11', 'CC1111', 'E0E0E0', 1, 1);
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Dark orange' AS description, 'darkorange' AS theme,
+	'333333' AS backgroundcolor, '0A0A0A' AS graphcolor, '888888' AS graphbordercolor, '222222' AS gridcolor,
+	'4F4F4F' AS maingridcolor, 'EFEFEF' AS gridbordercolor, 'DFDFDF' AS textcolor, 'FF5500' AS highlightcolor,
+	'FF5500' AS leftpercentilecolor, 'FF1111' AS rightpercentilecolor, '1F1F1F' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+FROM dual WHERE EXISTS (SELECT NULL FROM graph_theme);
+
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Classic' AS description, 'classic' AS theme,
+	'F0F0F0' AS backgroundcolor, 'FFFFFF' AS graphcolor, '333333' AS graphbordercolor, 'CCCCCC' AS gridcolor,
+	'AAAAAA' AS maingridcolor, '000000' AS gridbordercolor, '222222' AS textcolor, 'AA4444' AS highlightcolor,
+	'11CC11' AS leftpercentilecolor, 'CC1111' AS rightpercentilecolor, 'E0E0E0' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+FROM dual WHERE EXISTS (SELECT NULL FROM graph_theme);
+
 DELETE FROM ids WHERE table_name = 'graph_theme';
diff --git a/upgrades/dbpatches/2.0/oracle/patch/graph_theme.sql b/upgrades/dbpatches/2.0/oracle/patch/graph_theme.sql
index d203475a412..0d749e0830e 100644
--- a/upgrades/dbpatches/2.0/oracle/patch/graph_theme.sql
+++ b/upgrades/dbpatches/2.0/oracle/patch/graph_theme.sql
@@ -6,9 +6,24 @@ UPDATE graph_theme SET theme = 'darkblue' WHERE theme = 'css_bb.css';
 UPDATE graph_theme SET theme = 'originalblue' WHERE theme = 'css_ob.css';
 
 -- Insert new graph theme
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
-VALUES ((SELECT MAX(graphthemeid) + 1 FROM graph_theme), 'Dark orange', 'darkorange', '333333', '0A0A0A', '888888', '222222', '4F4F4F', 'EFEFEF', 'DFDFDF', 'FF5500', 'FF5500', 'FF1111', '1F1F1F', 1, 1);
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
-VALUES ((SELECT MAX(graphthemeid) + 1 FROM graph_theme), 'Classic', 'classic', 'F0F0F0', 'FFFFFF', '333333', 'CCCCCC', 'AAAAAA', '000000', '222222', 'AA4444', '11CC11', 'CC1111', 'E0E0E0', 1, 1);
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Dark orange' AS description, 'darkorange' AS theme,
+	'333333' AS backgroundcolor, '0A0A0A' AS graphcolor, '888888' AS graphbordercolor, '222222' AS gridcolor,
+	'4F4F4F' AS maingridcolor, 'EFEFEF' AS gridbordercolor, 'DFDFDF' AS textcolor, 'FF5500' AS highlightcolor,
+	'FF5500' AS leftpercentilecolor, 'FF1111' AS rightpercentilecolor, '1F1F1F' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+FROM dual WHERE EXISTS (SELECT NULL FROM graph_theme);
+
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Classic' AS description, 'classic' AS theme,
+	'F0F0F0' AS backgroundcolor, 'FFFFFF' AS graphcolor, '333333' AS graphbordercolor, 'CCCCCC' AS gridcolor,
+	'AAAAAA' AS maingridcolor, '000000' AS gridbordercolor, '222222' AS textcolor, 'AA4444' AS highlightcolor,
+	'11CC11' AS leftpercentilecolor, 'CC1111' AS rightpercentilecolor, 'E0E0E0' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+FROM dual WHERE EXISTS (SELECT NULL FROM graph_theme);
 
 DELETE FROM ids WHERE table_name = 'graph_theme';
diff --git a/upgrades/dbpatches/2.0/postgresql/patch/graph_theme.sql b/upgrades/dbpatches/2.0/postgresql/patch/graph_theme.sql
index 6611ed4c718..a6e5a5f6a33 100644
--- a/upgrades/dbpatches/2.0/postgresql/patch/graph_theme.sql
+++ b/upgrades/dbpatches/2.0/postgresql/patch/graph_theme.sql
@@ -6,9 +6,24 @@ UPDATE graph_theme SET theme = 'darkblue' WHERE theme = 'css_bb.css';
 UPDATE graph_theme SET theme = 'originalblue' WHERE theme = 'css_ob.css';
 
 -- Insert new graph theme
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
-VALUES ((SELECT MAX(graphthemeid) + 1 FROM graph_theme), 'Dark orange', 'darkorange', '333333', '0A0A0A', '888888', '222222', '4F4F4F', 'EFEFEF', 'DFDFDF', 'FF5500', 'FF5500', 'FF1111', '1F1F1F', 1, 1);
-INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
-VALUES ((SELECT MAX(graphthemeid) + 1 FROM graph_theme), 'Classic', 'classic', 'F0F0F0', 'FFFFFF', '333333', 'CCCCCC', 'AAAAAA', '000000', '222222', 'AA4444', '11CC11', 'CC1111', 'E0E0E0', 1, 1);
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Dark orange' AS description, 'darkorange' AS theme,
+	'333333' AS backgroundcolor, '0A0A0A' AS graphcolor, '888888' AS graphbordercolor, '222222' AS gridcolor,
+	'4F4F4F' AS maingridcolor, 'EFEFEF' AS gridbordercolor, 'DFDFDF' AS textcolor, 'FF5500' AS highlightcolor,
+	'FF5500' AS leftpercentilecolor, 'FF1111' AS rightpercentilecolor, '1F1F1F' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+WHERE EXISTS (SELECT NULL FROM graph_theme);
+
+INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor,
+	maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor,
+	nonworktimecolor, gridview, legendview)
+SELECT (SELECT MAX(graphthemeid) FROM graph_theme) + 1 AS graphthemeid, 'Classic' AS description, 'classic' AS theme,
+	'F0F0F0' AS backgroundcolor, 'FFFFFF' AS graphcolor, '333333' AS graphbordercolor, 'CCCCCC' AS gridcolor,
+	'AAAAAA' AS maingridcolor, '000000' AS gridbordercolor, '222222' AS textcolor, 'AA4444' AS highlightcolor,
+	'11CC11' AS leftpercentilecolor, 'CC1111' AS rightpercentilecolor, 'E0E0E0' AS nonworktimecolor, 1 AS gridview,
+	1 AS legendview
+WHERE EXISTS (SELECT NULL FROM graph_theme);
 
 DELETE FROM ids WHERE table_name = 'graph_theme';