[dev.icinga.com #8613] IDO queries related to concrete objects should not depend on collations

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/8613

Created by tgelf on 2015-03-05 15:02:06 +00:00

Assignee: jmeyer
Status: Resolved (closed on 2015-04-13 13:56:55 +00:00)
Target Version: 2.0.0-rc1
Last Update: 2015-04-13 13:56:55 +00:00 (in Redmine)

---

We use COLLATE as we need to issue case insensitive search queries. For MySQL they look as follows:

...name1 COLLATE latin1_general_ci = 'hostname' AND name2 COLLATE ...

While this perfectly makes sense when searching, it absolutely doesn't when we alreay know the exact object name. In theory we could base queries on IDs for IDO backends. We don't, as we want all our backends to behave as similar as possible, offering the very same abstraction level.

It's however perfectly valid to use those COLLATE-based columns for search only. This will boost our detail area and all queries issueing a complicated joins.

Best,
Thomas

Changesets

2015-03-12 15:32:05 +00:00 by elippmann 7212434

Remove COLLATE from host_name and service_description in the Contact query

refs #8614
refs #8613

2015-03-12 15:33:28 +00:00 by elippmann 112f8eb

Remove COLLATE from host_name and service_description in the Contactgroup query

refs #8614
refs #8613

2015-04-08 12:40:34 +00:00 by jmeyer b449127

Do not adjust the collation when querying host names

refs #8613

2015-04-08 12:41:13 +00:00 by jmeyer 98c76a6

Do not adjust the collation when querying service descriptions

refs #8613

2015-04-08 12:42:57 +00:00 by jmeyer c476ee6

Add an additional column for hostgroup names with unchanged collation

refs #8613

2015-04-08 12:49:18 +00:00 by jmeyer 9557585

Add an additional column for servicegroup names with unchanged collation

refs #8613

2015-04-08 12:51:04 +00:00 by jmeyer c56c565

Do not permit querying columns with changed collation and a valid alternative

refs #8613

2015-04-08 12:57:08 +00:00 by jmeyer e94c99e

Use `host' instead of `host_name' when searching for hosts

refs #8613

2015-04-08 12:57:30 +00:00 by jmeyer 55e9407

Use `service' instead of `service_description' when searching for services

refs #8613

2015-04-08 14:25:29 +00:00 by jmeyer b93d18a

Require `host_name' instead of `host' to show or list particular objects

`host' implies that it is possible to provide case-less identifiers which is
*not* possible.

refs #8613

2015-04-08 14:49:52 +00:00 by jmeyer 09ab601

Require `service_description' instead of `service' to show a particular service

`service' implies that it is possible to provide case-less identifiers which is
*not* possible.

refs #8613

2015-04-09 08:27:41 +00:00 by jmeyer b9aee47

Adjust command links so that they'll use `host_name' instead of `host'

refs #8613

2015-04-09 08:28:34 +00:00 by jmeyer cee189a

Adjust command links so that they'll use `service_description'

...instead of `service'

refs #8613

2015-04-09 09:12:09 +00:00 by jmeyer 7d98466

Use `host_name' to decide whether to show a service's host or not

`host' might match multiple hosts even if no * is being part of the filter.

refs #8613

2015-04-09 09:41:56 +00:00 by jmeyer f7550f5

Permit to filter hosts and services by hostgroup_name and servicegroup_name

refs #8613

2015-04-09 09:43:21 +00:00 by jmeyer e5eca97

Use `hostgroup_name' instead of `hostgroup' to filter hosts and services

refs #8613

2015-04-09 09:43:51 +00:00 by jmeyer 1e6f292

Use `servicegroup_name' instead of `servicegroup' to filter services

refs #8613

2015-04-09 10:49:06 +00:00 by jmeyer cb95f53

CommentQuery: Fetch and display object names with their initial collation

i.e. `host_name' instead of `comment_host' alias `host'
and `service_description' instead of `comment_service' alias `service'.

refs #8613

2015-04-09 10:53:28 +00:00 by jmeyer 480f551

DataView\Comment: Do not permit querying `host' and `service'

refs #8613

2015-04-09 10:55:57 +00:00 by jmeyer 002b820

CommentQuery: Provide `service_host' additionally to `service_host_name'

refs #8613

2015-04-09 10:56:31 +00:00 by jmeyer 8ed1c8e

DataView\Comment: Add `service_host_name' as allowed query column

refs #8613

2015-04-09 11:06:38 +00:00 by jmeyer 21c7601

CommentQuery: Add `comment_author_name' as additional query column

refs #8613

2015-04-09 11:07:50 +00:00 by jmeyer 5372dfb

Fetch a comment's author using `comment_author_name'

refs #8613

2015-04-09 12:17:27 +00:00 by jmeyer 84a2727

Eventhistory: Fetch object names using their columns with initial collation

refs #8613

2015-04-09 12:19:31 +00:00 by jmeyer 2c38ccd

EventHistoryQuery: Drop column `service_host_name'

refs #8613

2015-04-09 12:39:47 +00:00 by jmeyer ae10e01

EventHistoryQuery: Remove COLLATE from the column `host_name'

refs #8613

2015-04-09 12:40:12 +00:00 by jmeyer b7e1c2d

EventHistoryQuery: Remove COLLATE from the column `service_description'

refs #8613

2015-04-09 12:41:10 +00:00 by jmeyer ec67c28

DataView\EventHistory: Do not permit `host' and `service' to be queried

refs #8613

2015-04-09 12:44:10 +00:00 by jmeyer 9e79419

EventHistoryQuery: Provide `hostgroup_name' as alternative to `hostgroup'

This can't be queried though until #7274 has been resolved.

refs #8613

2015-04-09 13:13:59 +00:00 by jmeyer 51bf5cf

*historyQuery: Drop redundant query columns

refs #8613

2015-04-09 13:29:01 +00:00 by jmeyer ea874f8

*historyQuery: Remove COLLATE from `host_name' and `service_description'

refs #8613

2015-04-10 07:39:39 +00:00 by jmeyer 3c47ba1

monitoring/show/contact: Require `contact_name' instead of `contact'

refs #8613

2015-04-10 07:45:23 +00:00 by jmeyer f00adc2

ContactQuery: Provide ci query columns for aliases and email addresses

refs #8613

2015-04-10 07:46:41 +00:00 by jmeyer 52dcecf

ContactQuery: Change query column `contact_name' to be case sensitive

...and provide `contact' as case-insensitive alternative.

refs #8613

2015-04-10 07:48:22 +00:00 by jmeyer 0ec1a18

ContactQuery: Fix query column `service' not referencing a service's name

refs #8613

2015-04-10 07:49:47 +00:00 by jmeyer 6d03b2f

DataView\Contact: Add missing filter columns

refs #8613

2015-04-10 07:50:26 +00:00 by jmeyer 3d7e418

ContactQuery: Provide case insensitive filter column `service_host'

refs #8613

2015-04-10 10:12:50 +00:00 by jmeyer 1dc16a1

ContactgroupQuery: Remove COLLATE from query column `contactgroup_name'

refs #8613

2015-04-10 10:13:23 +00:00 by jmeyer 6536878

ContactgroupQuery: Remove COLLATE from query column `contact_name'

refs #8613

2015-04-10 10:14:11 +00:00 by jmeyer 71c0131

ContactgroupQuery: Make query column `contactgroup_alias' case-insensitive

refs #8613

2015-04-10 10:14:47 +00:00 by jmeyer 5ec4919

Do not filter for `contactgroup' in case the concrete group name is known

refs #8613

2015-04-10 10:20:28 +00:00 by jmeyer 03c8de5

ContactgroupQuery: Provide case-insensitive filter column `service_host'

refs #8613

2015-04-10 10:27:24 +00:00 by jmeyer 638eda7

ContactgroupQuery: Make query column `contact_alias' case-insensitive

refs #8613

2015-04-10 10:27:44 +00:00 by jmeyer 0628efb

ContactgroupQuery: Make query column `contact_email' case-insensitive

refs #8613

2015-04-10 10:56:18 +00:00 by jmeyer b8efe01

CustomvarQuery: Remove COLLATE from query columns

...and provide case-insensitive filter columns alternatively.

refs #8613

2015-04-10 12:19:38 +00:00 by jmeyer fda261b

Use case-sensitive query columns when filtering downtimes by object

refs #8613

2015-04-10 12:23:05 +00:00 by jmeyer 1c32d8e

DowntimeQuery: Add _name suffix to the query column `downtime_author'

...to indicate that it's a case-sensitive column.

refs #8613

2015-04-10 12:24:53 +00:00 by jmeyer 0f07274

DowntimeQuery: Make query column `author' case-insensitive

refs #8613

2015-04-10 12:25:26 +00:00 by jmeyer 5ccf6a7

DowntimeQuery: Remove redundant query columns

refs #8613

2015-04-10 12:25:53 +00:00 by jmeyer 92634a1

DowntimeQuery: Provide case-insensitive filter column `service_host'

refs #8613

2015-04-10 13:04:59 +00:00 by jmeyer 1f4b3aa

Fix host and service multiselection

refs #8613

2015-04-10 13:19:55 +00:00 by jmeyer 9f075e0

HostgroupQuery: Remove COLLATE from query column `hostgroup_name'

refs #8613

2015-04-10 13:20:19 +00:00 by jmeyer 69e520a

HostgroupQuery: Remove COLLATE from query column `host_name'

refs #8613

2015-04-10 13:21:18 +00:00 by jmeyer f61370f

DataView\Hostgroup: Add missing query columns

refs #8613

2015-04-10 13:31:09 +00:00 by jmeyer 26f0b46

ServicegroupQuery: Remove COLLATE from query column `host_name'

refs #8613

2015-04-10 13:31:51 +00:00 by jmeyer a17c2ed

ServicegroupQuery: Remove COLLATE from query column `service_host_name'

refs #8613

2015-04-10 13:32:47 +00:00 by jmeyer 055cd61

ServicegroupQuery: Remove COLLATE from query column `service_desription'

refs #8613

2015-04-10 13:34:28 +00:00 by jmeyer fdd6a4d

ServicegroupQuery: Remove COLLATE from query column `servicegroup_name'

...and add a case-insensitive alternative called `servicegroup'.

refs #8613

2015-04-10 13:34:55 +00:00 by jmeyer 4bc3afa

ServicegroupQuery: Make query column `servicegroup_alias' case-insensitive

refs #8613

2015-04-10 13:35:24 +00:00 by jmeyer 629becf

DataView\Servicegroup: Add missing query and filter columns

refs #8613

2015-04-10 13:54:18 +00:00 by jmeyer 904e91b

EventgridQuery: Remove COLLATE from query column `host_name'

refs #8613

2015-04-10 13:54:55 +00:00 by jmeyer 8b2c019

EventgridQuery: Remove COLLATE from query column `service_description'

refs #8613

2015-04-10 13:58:55 +00:00 by jmeyer 42e93ff

DataView\Eventgrid: Add missing query and filter columns

refs #8613

2015-04-10 14:01:09 +00:00 by jmeyer ea16d45

EventgridQuery: Make filter column `hostgroup' case-insensitive

...and add a case-sensitive query column.

refs #8613

2015-04-10 14:01:22 +00:00 by jmeyer e7c1e55

EventgridQuery: Make filter column `servicegroup' case-insensitive

...and add a case-sensitive query column.

refs #8613

2015-04-10 14:28:33 +00:00 by jmeyer c59d5ac

Hostgroups: Use query column `hostgroup_name' instead of `hostgroup'

refs #8613

2015-04-10 14:28:57 +00:00 by jmeyer c10617d

Servicegroups: Use query column `servicegroup_name' instead of `servicegroup'

refs #8613

2015-04-13 13:24:15 +00:00 by jmeyer 96954ea

NotificationQuery: Use query column `host_name' instead of `host'

refs #8613

2015-04-13 13:25:10 +00:00 by jmeyer 15399d7

NotificationQuery: Use query column `service_description' instead of `service'

refs #8613

2015-04-13 13:26:06 +00:00 by jmeyer 644a386

NotificationQuery: Use query column `notification_contact_name'

...instead of `notification_contact'.

refs #8613

2015-04-13 13:32:52 +00:00 by jmeyer 3653ec8

NotificationQuery: Provide case-insensitive filter column `host'

refs #8613

2015-04-13 13:33:19 +00:00 by jmeyer 29f05bf

NotificationQuery: Provide case-insensitive filter column `service'

refs #8613

2015-04-13 13:33:33 +00:00 by jmeyer 9cc5469

NotificationQuery: Provide case-insensitive filter column `contact'

refs #8613

2015-04-13 13:55:53 +00:00 by jmeyer 46296dc

Merge branch 'feature/non-collated-ido-queries-8613'

resolves #8613

---

Relations:

• relates #8612
• relates #8613
• blocks #9007
• blocks #8949

[icinga-migration]

Updated by tgelf on 2015-03-05 15:02:16 +00:00

• Relates set to 8612

[icinga-migration]

Updated by elippmann on 2015-03-05 15:40:48 +00:00

• Tracker changed from Bug to Feature
• Subject changed from Queries related to concrete objects should depend on collations to IDO queries related to concrete objects should not depend on collations
• Category set to Monitoring
• Target Version set to 2.0.0-rc1

[icinga-migration]

Updated by tgelf on 2015-03-05 15:42:44 +00:00

• Status changed from New to Feedback
• Assigned to set to elippmann

If I correctly remember what we discussed a while ago one way of solving this could be to remove collations from host_name, service_description, service_host_name and similar and keep host, service and eventually host_service as their respective collate'd aliases.

What do you think about this?

Cheers,
Thomas

[icinga-migration]

Updated by jmeyer on 2015-04-08 08:11:47 +00:00

• Status changed from Feedback to Assigned
• Assigned to changed from elippmann to jmeyer

[icinga-migration]

Updated by jmeyer on 2015-04-08 08:30:30 +00:00

• Blocks set to 9007

[icinga-migration]

Updated by icinga-kanban on 2015-04-08 14:57:15 +00:00

Build !#577 triggered by commit 09ab601 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by icinga-kanban on 2015-04-09 08:39:44 +00:00

Build !#578 triggered by the commits cee189a, b9aee47 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by jmeyer on 2015-04-09 08:47:26 +00:00

• Relates set to 9029

[icinga-migration]

Updated by icinga-kanban on 2015-04-09 09:14:49 +00:00

Build !#579 triggered by commit 7d98466 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by icinga-kanban on 2015-04-09 09:47:26 +00:00

Build !#580 triggered by the commits 1e6f292, e5eca97, f7550f5 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by icinga-kanban on 2015-04-09 11:14:03 +00:00

Build !#581 triggered by the commits bc6a086, 5372dfb, 21c7601, 8ed1c8e, 002b820, 480f551, cb95f53 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by icinga-kanban on 2015-04-09 12:54:43 +00:00

Build !#582 triggered by the commits 9e79419, ec67c28, b7e1c2d, ae10e01, 044a15d, 2c38ccd, 84a2727 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by icinga-kanban on 2015-04-09 13:33:14 +00:00

Build !#583 triggered by the commits 3d09615, ea874f8, 51bf5cf passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by elippmann on 2015-04-10 08:25:53 +00:00

• Blocks set to 8949

[icinga-migration]

Updated by icinga-kanban on 2015-04-10 17:03:53 +00:00

Build !#585 triggered by the commits c10617d, c59d5ac, e7c1e55, ea16d45, 42e93ff, 8b2c019, 904e91b, 629becf, 4bc3afa, fdd6a4d, 055cd61, a17c2ed, 26f0b46, f61370f, 69e520a, 9f075e0, 1f4b3aa, 92634a1, 5ccf6a7, 0f07274, 1c32d8e, fda261b, b8efe01, 0628efb, 638eda7, 0fe59aa, 03c8de5, 31a05a3, a51b93e, 5ec4919, 71c0131, 6536878, 1dc16a1, 8477859, 3d7e418, 6d03b2f, 0ec1a18, 52dcecf, f00adc2, 3c47ba1 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by icinga-kanban on 2015-04-13 13:37:59 +00:00

Build !#588 triggered by the commits 9cc5469, 29f05bf, 3653ec8, 644a386, 15399d7, 96954ea, 24e06e5 passed successfully.

Branch: origin/feature/non-collated-ido-queries-8613
Author: Johannes Meyer

[icinga-migration]

Updated by jmeyer on 2015-04-13 13:56:55 +00:00

• Status changed from Assigned to Resolved
• Done % changed from 0 to 100

Applied in changeset 46296dc.