2

I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search.

I have a table storing key/value pairs. There are also 2 limiting columns in the table, host and environment, both nullable. The way that the limiting tables work is that if there is an entry in the host column then the key/value applies to that host, similarly if there is an entry in the environment column then that entry takes effect only for that environment. If a key/value is sought with a host or environment then the limited values take precedent over the values where host=NULL or environment=NULL. Furthermore, host takes precedence over environment.

So as an example, here are some table entries:

id      env     host     key    value
1       NULL    NULL     fruit  apple
2       prod    NULL     fruit  banana
3       prod    www      fruit  mango
4       test    NULL     fruit  peach
5       NULL    test     fruit  orange

Given that example data, what I'm after is a SQL statement (or statements) that will return these data based on these criteria:

env     host   key    data returned
NULL    NULL   fruit  1, fruit, apple
prod    NULL   fruit  2, fruit, banana
NULL    www    fruit  3, fruit, mango
junk    junk   fruit  1, fruit, apple  // NULL/NULL values are used as a fallback
junk    www    fruit  1, fruit, apple  // no match, fallback
prod    test   fruit  5, fruit, orange // host=test takes precedence

Does that make sense?

I can break the search up into multiple selects, try each one, check to see if I get a hit, etc, but I'm hoping to achieve this as efficiently as possible.

Ideally I would like the type of search that would be easily extensible if more limiters are added later (e.g. group, namespace, etc).

Anyone?

  • Sorry, I do not follow your explanation. Please explain the junk entries and the how the prod became part of a prod test pair. – RLF Dec 26 '15 at 18:19
  • junk is just a sample search value. It's not found in the table but I have included it to show the desired result, that the search should fall back to finding the NULL values in the table. So I'm using NULL as a wildcard (I could just as easily used "*" or similar). prod / test search was done to illustrate that host takes precedence over env. – delatbabel Dec 27 '15 at 2:17
  • I have edited my test answers -- junk/www does not match anything so falls back to the global NULL/NULL wildcard. Would it actually make better sense to store "*" in the table as a wildcard rather than NULL? Also, note that prod/test matches the wildcards prod/NULL as well as NULL/test but the NULL/test takes precedence. – delatbabel Dec 27 '15 at 3:21
  • 1
    Perhaps you should add a derived column to represent the precedence. Something like: COALESCE(host, env) to give you the preferred lead tag. No, I do not think "*" is better than NULL in this case since you are juggling the precedence. COALESCE works through the precedence of values to choose the "answer" for precedence. – RLF Dec 27 '15 at 22:40
  • 1
    Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Dec 27 '15 at 23:20
1

So you're going to pass in two optional parameters:

  • @host
  • @env

Example sql (to be put in a stored proc):

Select top 1 * from dbo.FilterTable ft
Where 
    ((@host is not null and ft.host = @host) or ft.host is null)
    And ((@env is not null and ft.environment = @env) or ft.environment is null)
Order by 
    Case
        When ft.host is not null and ft.environment is not null then 1
        When ft.host is not null then 2
        When ft.environment is not null then 3
        Else 4
    End asc
  • That makes sense and looks like it would probably work, I will give it a go and come back if I have any issues. – delatbabel Dec 28 '15 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.