[icinga-checkins] icinga.org: icinga-core/master: remove mysql binary selects, use case-sensitive collation instead (William Preston)
git at icinga.org
git at icinga.org
Tue Apr 20 16:17:23 CEST 2010
Module: icinga-core
Branch: master
Commit: bb1ff18f489fc2495d84cc52db2028fa7efcbaed
URL: https://git.icinga.org/?p=icinga-core.git;a=commit;h=bb1ff18f489fc2495d84cc52db2028fa7efcbaed
Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date: Tue Apr 20 16:03:37 2010 +0200
remove mysql binary selects, use case-sensitive collation instead (William Preston)
The binary selects fail to use indexes, causing poor performance during restart/ reload.
added upgrade path for mysql
fixes #267
fixes #361
fixes #365
---
Changelog | 1 +
module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql | 20 ++++++++++
module/idoutils/db/mysql/mysql.sql | 10 +++--
module/idoutils/db/oracle/oracle.sql | 2 +
module/idoutils/db/pgsql/pgsql.sql | 22 +++++------
module/idoutils/src/dbhandlers.c | 44 +++++-----------------
6 files changed, 49 insertions(+), 50 deletions(-)
diff --git a/Changelog b/Changelog
index 4cc8344..7f09f17 100644
--- a/Changelog
+++ b/Changelog
@@ -5,6 +5,7 @@ Icinga 1.0.x Change Log
1.0.2 - 30/06/2010
ENHANCEMENTS
+* idoutils: remove mysql binary selects, use case-sensitive collation instead (William Preston)
* idoutils: Spread the list of linked lists thinner (extend objects hash slots) (Opsera Ltd)
* idoutils: change select * into select [*_]id if unused results
diff --git a/module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql b/module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql
new file mode 100644
index 0000000..c368976
--- /dev/null
+++ b/module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql
@@ -0,0 +1,20 @@
+-- --------------------------------------------------------
+-- mysql-upgrade-1.0.2.sql
+-- DB definition for MySQL
+--
+-- Copyright (c) 2010 Icinga Development Team (http://www.icinga.org)
+--
+-- Changes:
+-- no more binary casts, instead updating collation to case-sensitive
+--
+-- -- --------------------------------------------------------
+
+-- -----------------------------------------
+-- change collation to case-sensitive
+-- -----------------------------------------
+
+ALTER TABLE `icinga_hosts` MODIFY COLUMN `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '';
+ALTER TABLE `icinga_objects` MODIFY COLUMN `name1` varchar(128) character set latin1 collate latin1_general_cs NOT NULL default '';
+ALTER TABLE `icinga_objects` MODIFY COLUMN `name2` varchar(128) character set latin1 collate latin1_general_cs default NULL;
+ALTER TABLE `icinga_services` MODIFY COLUMN `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '';
+
diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index 67a6dde..f40befd 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -2,6 +2,8 @@
-- mysql.sql
-- DB definition for MySQL
--
+--
+-- Copyright (c) 2009-2010 Icinga Development Team (http://www.icinga.org)
--
-- -- --------------------------------------------------------
@@ -623,7 +625,7 @@ CREATE TABLE IF NOT EXISTS `icinga_hosts` (
`config_type` smallint(6) NOT NULL default '0',
`host_object_id` int(11) NOT NULL default '0',
`alias` varchar(64) character set latin1 NOT NULL default '',
- `display_name` varchar(64) character set latin1 NOT NULL default '',
+ `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '',
`address` varchar(128) character set latin1 NOT NULL default '',
`check_command_object_id` int(11) NOT NULL default '0',
`check_command_args` varchar(255) character set latin1 NOT NULL default '',
@@ -851,8 +853,8 @@ CREATE TABLE IF NOT EXISTS `icinga_objects` (
`object_id` int(11) NOT NULL auto_increment,
`instance_id` smallint(6) NOT NULL default '0',
`objecttype_id` smallint(6) NOT NULL default '0',
- `name1` varchar(128) character set latin1 NOT NULL default '',
- `name2` varchar(128) character set latin1 default NULL,
+ `name1` varchar(128) character set latin1 collate latin1_general_cs NOT NULL default '',
+ `name2` varchar(128) character set latin1 collate latin1_general_cs default NULL,
`is_active` smallint(6) NOT NULL default '0',
PRIMARY KEY (`object_id`),
KEY `objecttype_id` (`objecttype_id`,`name1`,`name2`)
@@ -1108,7 +1110,7 @@ CREATE TABLE IF NOT EXISTS `icinga_services` (
`config_type` smallint(6) NOT NULL default '0',
`host_object_id` int(11) NOT NULL default '0',
`service_object_id` int(11) NOT NULL default '0',
- `display_name` varchar(64) character set latin1 NOT NULL default '',
+ `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '',
`check_command_object_id` int(11) NOT NULL default '0',
`check_command_args` varchar(255) character set latin1 NOT NULL default '',
`eventhandler_command_object_id` int(11) NOT NULL default '0',
diff --git a/module/idoutils/db/oracle/oracle.sql b/module/idoutils/db/oracle/oracle.sql
index 2ef60cf..288af52 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -1,6 +1,8 @@
-- --------------------------------------------------------
-- oracle.sql
-- DB definition for Oracle
+--
+-- Copyright (c) 2009-2010 Icinga Development Team (http://www.icinga.org)
--
-- requires ocilib, oracle (instantclient) libs+sdk to work
-- specify oracle (instantclient) libs+sdk in ocilib configure
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index 7f04af4..3650d9b 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -1,15 +1,13 @@
-/*
-# pgsql.sql
-# - modfied mysql.sql to work with postgres
-# - added from_unixtime and unix_timestamp
-# as sql function
-#
-# initial version: 2009-05-13 Markus Manzke
-# current version: 2010-02-03 Michael Friedrich <michael.friedrich at univie.ac.at>
-#
-#--------------------------------------------------------------------------------
-#
-*/
+-- --------------------------------------------------------
+-- pgsql.sql
+-- DB definition for Postgresql
+--
+-- Copyright (c) 2009-2010 Icinga Development Team (http://www.icinga.org)
+--
+-- initial version: 2009-05-13 Markus Manzke
+-- current version: 2010-02-03 Michael Friedrich <michael.friedrich at univie.ac.at>
+--
+-- --------------------------------------------------------
--
-- Functions
diff --git a/module/idoutils/src/dbhandlers.c b/module/idoutils/src/dbhandlers.c
index b0bd46e..d230901 100644
--- a/module/idoutils/src/dbhandlers.c
+++ b/module/idoutils/src/dbhandlers.c
@@ -79,16 +79,6 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
} else {
es[0] = ndo2db_db_escape_string(idi, name1);
switch (idi->dbinfo.server_type) {
- case NDO2DB_DBSERVER_MYSQL:
- /* mysql does case INsensitive compare, use BINARY */
- if (asprintf(&buf1, "BINARY name1='%s'", es[0]) == -1)
- buf1 = NULL;
- break;
- case NDO2DB_DBSERVER_PGSQL:
- /* Postgres does case sensitive compare */
- if (asprintf(&buf1, "name1='%s'", es[0]) == -1)
- buf1 = NULL;
- break;
case NDO2DB_DBSERVER_DB2:
break;
case NDO2DB_DBSERVER_FIREBIRD:
@@ -100,19 +90,17 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
case NDO2DB_DBSERVER_MSQL:
break;
case NDO2DB_DBSERVER_ORACLE:
-
-#ifdef USE_ORACLE /* Oracle ocilib specific */
- /* Oracle does case sensitive compare */
- if (asprintf(&buf1, "name1='%s'", es[0]) == -1)
- buf1 = NULL;
-#endif /* Oracle ocilib specific */
-
break;
case NDO2DB_DBSERVER_SQLITE:
break;
case NDO2DB_DBSERVER_SQLITE3:
break;
default:
+ /* William Preston: mysql does case sensitive compare
+ * IF the collation is changed to latin1_general_cs */
+ /* Postgres does case sensitive compare */
+ if (asprintf(&buf1, "name1='%s'", es[0]) == -1)
+ buf1 = NULL;
break;
}
}
@@ -124,16 +112,6 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
} else {
es[1] = ndo2db_db_escape_string(idi, name2);
switch (idi->dbinfo.server_type) {
- case NDO2DB_DBSERVER_MYSQL:
- /* mysql does case INsensitive compare, use BINARY */
- if (asprintf(&buf2, "BINARY name2='%s'", es[1]) == -1)
- buf2 = NULL;
- break;
- case NDO2DB_DBSERVER_PGSQL:
- /* Postgres does case sensitive compare */
- if (asprintf(&buf2, "name2='%s'", es[1]) == -1)
- buf2 = NULL;
- break;
case NDO2DB_DBSERVER_DB2:
break;
case NDO2DB_DBSERVER_FIREBIRD:
@@ -145,19 +123,17 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
case NDO2DB_DBSERVER_MSQL:
break;
case NDO2DB_DBSERVER_ORACLE:
-
-//#ifdef USE_ORACLE /* Oracle ocilib specific */
- /* Oracle does case sensitive compare */
-// if (asprintf(&buf2, "name2='%s'", es[1]) == -1)
-// buf2 = NULL;
-//#endif /* Oracle ocilib specific */
-
break;
case NDO2DB_DBSERVER_SQLITE:
break;
case NDO2DB_DBSERVER_SQLITE3:
break;
default:
+ /* William Preston: mysql does case sensitive compare
+ * IF the collation is changed to latin1_general_cs */
+ /* Postgres does case sensitive compare */
+ if (asprintf(&buf2, "name2='%s'", es[1]) == -1)
+ buf2 = NULL;
break;
}
}
More information about the icinga-checkins
mailing list