Início / dba / Perguntas / 124600
Accepted
delatbabel
Asked: 2015-12-27 05:09:11 +0800 CST

Tabela chave/valor com limitadores

  • 772

Tenho certeza que alguém deve ter encontrado isso ou um problema como esse antes, mas não estou encontrando nada em uma pesquisa no Google ou DBA Exchange.

Eu tenho uma tabela armazenando pares chave/valor. Existem também 2 colunas limitantes na tabela, host e ambiente, ambas anuláveis. A maneira como as tabelas de limitação funcionam é que, se houver uma entrada na coluna do host, a chave/valor se aplicará a esse host; da mesma forma, se houver uma entrada na coluna do ambiente, essa entrada terá efeito apenas para esse ambiente. Se uma chave/valor for procurado com um host ou ambiente, os valores limitados prevalecem sobre os valores em que host=NULL ou ambiente=NULL. Além disso, o host tem precedência sobre o ambiente.

Então, como exemplo, aqui estão algumas entradas da tabela:

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

Dados esses dados de exemplo, o que estou procurando é uma instrução (ou instruções) SQL que retornará esses dados com base nestes critérios:

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

Isso faz sentido?

Posso dividir a pesquisa em várias seleções, tentar cada uma, verificar se consigo um resultado etc., mas espero conseguir isso da maneira mais eficiente possível.

Idealmente, gostaria que o tipo de pesquisa fosse facilmente extensível se mais limitadores fossem adicionados posteriormente (por exemplo, grupo, namespace, etc).

Alguém?

performance query-performance

1 respostas

  1. Best Answer

    Então você vai passar dois parâmetros opcionais:

    • @hospedeiro
    • @env

    Exemplo sql (para ser colocado em um procedimento armazenado):

    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
    • 1

relate perguntas