SQL select

[j]

... Jde o to, aby si podmínku vytvořil v jednoduchým klikátku uživatel, který má k dispozici tlačítka "přidat vlastnost" "AND" "OR" "(" a ")" - mohl si naklikat cokoliv, třeba "(A and (B or C) and (D or E)) or G" - prostě cokoliv. ...

Tohle myslis vazne? heh ... to ti nikdy fungovat nebude. A jako bonus vyrobis do systemu diru jak hrom. Nehlede na to, ze stejne nikdy zadny usery ktery neumej psat primo SQL nenaucis to pouzivat. Podotykam, ze podobnou vec ktera se presne o totez snazi adminuju. Defakto to vypada tak, ze ja a kolegove si pisem rovnou SQLko, protoze je to radove rychlejsi nez to, ktery ten bazmek vygeneruje, a useri nezvladaj z 80% udelat ani filtr metodou vyberu pole, zvolim jestli =/like/...

[Reklama]

[Pavel Stěhule]

...
nicméně dobré řešení asi neexistuje - způsob jaký používáte se označuje jako EAV a je to známý antipattern - taky proto se Vám s tím špatně dělá.
...

Dlouho jsem s DB nic pořádného nedělal, ale zajímá mne to:
Pavle, co je v tomto případě ten antipattern?
Struktura té tabulky?
A pokud ano, jak by se to mělo řešit správně (mne teď v těch hicech nic nenapadá + viz výše), stačí třeba jen nějaký link na něco k přečtení ...

Relační databáze předpokládají, že význam je určený sloupcem - mám sloupec příjmení, mám sloupec jméno. Jakmile se sémantika určuje další hodnotou, nebo několika dalšími hodnotami, tak SQL jako celek přestane fungovat - dotazy přestanou být názorné a čitelné, odhady založené na statistikách budou ustřelovat - a z SQL databáze se stane polofunkční key/value databáze - protože celý ten aparát - SQL, optimalizace, exekuce je navržená na nějaký způsob uložení dat, a pro něj je 30 let optimalizována.

Jak by se to mělo řešit správně - předně, musím si uvědomit, co chci dělat - pokud pracuji s extrémně dynamickými daty, které za pár týdnů zahodím, tak bych asi nepoužil relační databází, ale šel bych do NoSQL databáze - relační databáze, jsou navržené tak, aby dokázaly rychle zpracovat velké množství dat se stabilní strukturou - evidence, sklady, řízení výroby. Pokud ale stabilní strukturu nemám - pracuji s velkým množstvím dynamicky měnících se atributů - vyhledávání v katalogu produktů, atd - tak bych měl použít dokumentovou databázi, která je postavená úplně jinak, a na jiný typ dotazů. EAV je snaha o napsání si vlastní dokumentové databáze - bohužel už je to nad SQL databází a tam už programátor nemá možnost to udělat efektivně.

Pro projekty, které nejsou velké asi budou postačovat extenze do stávajících relačních databází, jako je v Postgresu Jsonb, Hstore, které částečně umožňují kombinovanou práci. U velkých projektů (desítky, spíš stovky GB) je výhodnější použít víc specializovaných databází - finance, majetek řeším v relační SQL db, vyhledávání v katalogu v NoSQL databázi, a cache řeším nějakou paměťovou databází, analytiku pak sloupcovou. Jednak se mi dekomponuje prostředí, druhak díky použití optimalizovaných databází a jazyků nemusím vymýšlet desítky berliček a workaroundů, a každý blok pak je výrazně jednodušší a čitelnější. Nelze si vystačit jenom s jedním stylem - protože jdou proti sobě.

[Tuxik]

Obecné elegantní řešení na takové obecné dotazování není.

Pomůže při importu uložit výsledek tohoto dotazu do speciální tabulky, řádně oindexovat a dotazy dělat tam.

select coalesce(table.id, table2.id, table3.id) as id, table.vlastnost as vlastnost_a, table2.vlastnost as vlastnost_b, table3.vlastnost as vlastnost_c
full outer join table as table2 on table2.id=table.id and table2.vlastnost='B'
full outer join table as table3 on table3.id=table.id and table3.vlastnost='C'
where table.vlastnost='A'

To by fungovalo s malym poctem vlastnosti, ale tech vlastnosti je hodne (cca 200) a jejich pocet se muze menit.

[Tuxik]

Relační databáze předpokládají, že význam je určený sloupcem - mám sloupec příjmení, mám sloupec jméno. Jakmile se sémantika určuje další hodnotou, nebo několika dalšími hodnotami, tak SQL jako celek přestane fungovat - dotazy přestanou být názorné a čitelné, odhady založené na statistikách budou ustřelovat - a z SQL databáze se stane polofunkční key/value databáze - protože celý ten aparát - SQL, optimalizace, exekuce je navržená na nějaký způsob uložení dat, a pro něj je 30 let optimalizována.

dekuji za pekny popis situace Databaze, ze ktere to taham neni moje a v podstate mam jen dve moznosti - bud ji celou z SQL predelat na neco jinyho a zpracovavat posleze, nebo udelat nejakou lehkou prasarnu, coz je asi v tomhle pripade jednodussi cesta a moc velky zmatky nehrozi. Databaze neni moc velka (250MB vcetne obrazku v BLOBech) a predpokladam, ze poroste pomaleji, nez vypocetni vykon

[Kolemjdoucí]

vlastnosti je hodne (cca 200) a jejich pocet se muze menit.

Tak to asi musí být to předchozí řešení s inner joiny a dotaz se musí dynamicky sestavovat podle zadaných podmínek.

[Reklama]

[hawran diskuse]

Relační databáze ...

Díky za nakopnutí.
(Ale pořádně se nad tím zamyslím, až venku nebude 40 ve stínu ...)

[kafcha]

mozna trosku mimo tema, jak nejlepe sber nasledujicich dat

mam mista kde se sleduji veliciny

CREATE TABLE mista
(
  id serial NOT NULL,
  nazev text,
 CONSTRAINT pk_mista PRIMARY KEY (id)
)

veliciny ktere se sleduji
CREATE TABLE veliciny
(
  id serial NOT NULL,
  nazev text,
  mj character(3),
  CONSTRAINT pk_veliciny PRIMARY KEY (id)
)


vazbu mista-veliciny
CREATE TABLE misto_velicina
(
  id serial,
  id_misto integer,
  id_velic integer,
 CONSTRAINT pk_misto_velicina PRIMARY KEY (id)
)

a samotna ulozena data
CREATE TABLE cteni
(
  id serial NOT NULL,
  dt timestamp without time zone ,
  id_misto integer NOT NULL,
  id_velic integer NOT NULL,
  hodnota numeric(8,3),
  CONSTRAINT pk_cteni PRIMARY KEY (id)
)

pokud nevim kolik bude mist,kolik bude velicin a kde se bude co sledovat je nejaka lepsi moznost jak mit data ulozena?
a co kdyz budu chtit sledovat i veliciny ktere by nemely ciselnou hodnotu?
mk

[Tuxik]

Tohle myslis vazne? heh ... to ti nikdy fungovat nebude. A jako bonus vyrobis do systemu diru jak hrom. Nehlede na to, ze stejne nikdy zadny usery ktery neumej psat primo SQL nenaucis to pouzivat. Podotykam, ze podobnou vec ktera se presne o totez snazi adminuju. Defakto to vypada tak, ze ja a kolegove si pisem rovnou SQLko, protoze je to radove rychlejsi nez to, ktery ten bazmek vygeneruje, a useri nezvladaj z 80% udelat ani filtr metodou vyberu pole, zvolim jestli =/like/...

Jo, myslim to vazne a funguje to. Neni to nic zase tak zasadniho, jedinej uzivatel bude manzelka, ktere bud ty zavorky a dva operatory vysvetlim, nebo to holt udelam za ni :-D

Jinak to neni zadnej "system", ale jednoucelova aplikace pro presuny a synchronizace dat z databaze dodavatele do e-shopu. Nebavilo me se koukat, jak to manzelka prepisuje vsechno rucne, tak jsem to chtel malinko zautomatizovat. Nejvetsi problem je v tom, ze v puvodni databazi muze byt jedna polozka v libovolnem poctu ruznych kategorii, coz jsem potreboval predelat, idealne na zaklade definice "urcita kombinace kategorii v DB"="jedna kategorie v E-shopu", coz skoncilo na nadefinovani te kombinace kategorii DB (respektive neskoncilo, je to nekonecny, aneb "udelej to jinak, neslo by to takhle, pak to upravime, znovu a lepe")

Navic me celkem zajimalo, jestli to zvladnu i z puvodniho FireBirdu (z ciste vyzkumnych a masochistickych duvodu) bez prevodu cele DB do MySQL. Takze ted je z toho krasnej slepenec HTML/PHP/JS/MySQL/FireBird plnej zakomentovanych slepych vetvi, promenych pokus1 az pokusmilion a ted uz to musim jenom cely uhladit (v tomto pripade spis prepsat) :-D Ale jinak dobry, pekne sem si zablbnul, neco se naucil, par lidi na rootu zamestnal a i pres nazory "to nejde" jsem to dokazal rozchodit. Mam z toho dobry pocit. :-D

[Tuxik]

mozna trosku mimo tema, jak nejlepe sber nasledujicich dat

osobne bych pridal do tabulky "veliciny" polozku "typ", ktera by urcovala, jestli bude velicina ciselna nebo textova a v samotnych datech bych udelal dve pole hodnota_num a hodnota_txt s tim, ze by se zapisovalo vzdy jen do jenoho, pripadne uplne rozdelit tabulku s datama na cteni_num a cteni_txt

jeste me napadlo ukladat velicinu vzdy jako text, s tim, ze uspech konverze text->num by urcil sam o sobe, jakeho typu jsou data, ale mohl by s tim nastat problem, pokud by z nejakeho duvodu hodnota v textove velicine byla cislo (to musis posoudit ty, jestli je to mozne)

Ale jsou tu urcite lepsi lidi na DB a nedokazu realne posoudit, jaky by s tim mohly byt problemy, jak to bude s vykonem pro vetsi mnozstvi dat atd...

[Ondrej]

Relační databáze předpokládají, že význam je určený sloupcem (...)

Jak by se to mělo řešit správně - předně, musím si uvědomit, co chci dělat - pokud pracuji s extrémně dynamickými daty, které za pár týdnů zahodím, tak bych asi nepoužil relační databází, ale šel bych do NoSQL databáze - relační databáze, jsou navržené tak, aby dokázaly rychle zpracovat velké množství dat se stabilní strukturou (...)

Pro projekty, které nejsou velké asi budou postačovat extenze do stávajících relačních databází, jako je v Postgresu Jsonb, Hstore, které částečně umožňují kombinovanou práci (...)

No dobře, to je určitě pravda. EAV je snaha ukládat data s proměnnou strukturou do databáze s neměnnou strukturou. Pak jsem odkázán na to, že sémantiku uložím jako hodnotu. Na druhou stranu se s tím člověk setká opravdu často, skoro v každé databázi, se kterou jsem se setkal, je pár takových tabulek. Jsonb nebo Hstore by pro nově navrhovanou databázi byly asi ideální, akorát nevím, jaká je podpora v ORM nástrojích? Můžete doporučit nějaké zdroje k tomu? Zajímá mě hlavně java.

Sám jsem nedávno vymýšlel jeden takový dynamický EAV, musel jsem přidat přes triggery aktualizované indexové tabulky, aby bylo hledání dost rychlé, následné složení všech dat náležejících k jedné entitě ale zůstalo stále dost pomalé, takže bych měl přidat ještě další tabulku, kde budou data entity pohromadě už připravená... => jinými slovy jsem implementoval něco, co bych u Jsonb nebo Hstore měl už asi automaticky hotové. Existuje tam možnost nějaké validace dokumentů? Cílem by v mém případě bylo, aby část databáze byla dynamicky definovatelná, aby si strukturu mohl uživatel sám nadefinovat a vytvořené dokumenty vůči definici pak validovat.

Uvítám nějaké zdroje k výše řečenému.

[Pavel Stěhule]

No dobře, to je určitě pravda. EAV je snaha ukládat data s proměnnou strukturou do databáze s neměnnou strukturou. Pak jsem odkázán na to, že sémantiku uložím jako hodnotu. Na druhou stranu se s tím člověk setká opravdu často, skoro v každé databázi, se kterou jsem se setkal, je pár takových tabulek. Jsonb nebo Hstore by pro nově navrhovanou databázi byly asi ideální, akorát nevím, jaká je podpora v ORM nástrojích? Můžete doporučit nějaké zdroje k tomu? Zajímá mě hlavně java.
Uvítám nějaké zdroje k výše řečenému.

Javě a ORM se vyhýbám obloukem, takže o nich nic nevím. V tuhle chvíli jsou Hstore i Jsonb dost jednoduché typy, které podporují základní operace, a jejich jediná výhoda je, že drží efektivně atributy pohromadě. Pro Jsonb se připravuje několik extenzí, které výrazně zvýší možnost dotazování - http://pgxn.org/dist/jsquery/1.0.0/ a o podpoře schémat (zatím jsou to spíš pokusy https://github.com/petrounias/json-schema-toolkit ) už jsem také slyšel (zatím je k dispozici validace pomocí contraintů a výrazů http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-data-validation.html). Hstore se už rozšiřovat nebude - je to předchozí generace Jsonb

[podlesh]

Jsonb nebo Hstore by pro nově navrhovanou databázi byly asi ideální, akorát nevím, jaká je podpora v ORM nástrojích? Můžete doporučit nějaké zdroje k tomu? Zajímá mě hlavně java.

V ORM nástrojích typicky je možné definovat vlastní typy, takže tam jen doplníš převod z/do byte[] (nebo stringu, pokud místo jsonb použiješ normální json nebo xml).

Triviální (ale reálný) příklad pro JSON:

Kód: [Vybrat]

    @Column(name = "custom_data")
    @Lob
    @Type(type = "org.codehaus.jackson.node.ObjectNode")
    private ObjectNode customData;


[3ugeene]

zrovna před chvílí jsem řešil ten samý problém, konečné funkční řešení pro PHP a MySQL (za použití dibi):

Kód: [Vybrat]

    public function getItemIDsBySetupIDs(array $setup_ids)
    {
        $data = $this->connection
            ->select('DISTINCT housing_id')
            ->from('housings_x_setups')
            ->where('housing_setup_id IN %in', $setup_ids)
            ->groupBy('housing_id')
            ->having('COUNT(housing_setup_id) = %u', sizeof($setup_ids))
            ->fetchPairs('housing_id', 'housing_id');

        return $data ?: [0];
    }

[Ivan]

Jsonb nebo Hstore by pro nově navrhovanou databázi byly asi ideální, akorát nevím, jaká je podpora v ORM nástrojích? Můžete doporučit nějaké zdroje k tomu? Zajímá mě hlavně java.

V ORM nástrojích typicky je možné definovat vlastní typy, takže tam jen doplníš převod z/do byte[] (nebo stringu, pokud místo jsonb použiješ normální json nebo xml).

Triviální (ale reálný) příklad pro JSON:

Kód: [Vybrat]

    @Column(name = "custom_data")
    @Lob
    @Type(type = "org.codehaus.jackson.node.ObjectNode")
    private ObjectNode customData;


To co popisujes funguje jen pro konverzi BLOB => Java class. Pokud mas nejaky komplexni datavy typ v databazi a chces ho prevest na tridu, tak musis pouzit posledni verze JPA (2.1) - Attribute Converter anebo Entity Listener.

[3ugeene]

a DISTINCT tam bejt nemá