Fórum Root.cz
Hlavní témata => Vývoj => Téma založeno: Jan Chrobak 28. 01. 2025, 21:03:22
-
Zdravím.
Potřeboval bych poradit.
Existuje možnost udělat SELECT záznamů z tabulky s diakritikou i bez?
Potřeboval bych aby SQL dotazy
SELECT * FROM profile WHERE last_name LIKE 'jašková';
a
SELECT * FROM profile WHERE last_name LIKE 'jaskova';
vraceli stejný výsledek.
DB: Sqlite3
-
Urcite nie. "a" a "á" su naprosto odlisne znaky. Musis to transliterovat pred tym, nez to ulozis do db.
-
a pochopitelně mít uložené oba sloupce.
-
https://www.sqlite.org/datatype3.html#collation
-
https://www.sqlite.org/datatype3.html#collation
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".
-
Urcite nie. "a" a "á" su naprosto odlisne znaky. Musis to transliterovat pred tym, nez to ulozis do db.
To chápu. Myslel jsem že existuje nějaký extension pro sqlite který udělá transliterace "on the fly".
Každopádně, děkuji za odpovědi.
-
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".
Nikolivek, collation to resi.
https://dba.stackexchange.com/questions/190969/ignore-accents-in-where-clause
Samozrejme za predpokladu ze to prislusna databaze umi.
Da se to pochopitelne obejit, jen pak zalezi na tom jak moc je tam zaznamu, a jak moc jde o vykon toho obchazeni. Pro cestinu je to celkem jednoduchy, ale obchazet to obecne by bylo fakt drahy.
-
https://www.sqlite.org/datatype3.html#collation
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".
Staci si to precist a overite si, ze nemate pravdu.
-
https://www.sqlite.org/datatype3.html#collation
Collation pomoze len pri "a" vs "A" ale nie "a" vs "á".
Staci si to precist a overite si, ze nemate pravdu.
Napriklad "á" nie je sucastou ascii, ktore sa prave spomina v uvedenom odkaze, ale az extended ascii https://www.ascii-code.com/CP1252/225 Navyse sa v uvedenom odkaze vyslovene uvadza "Hence the 26 upper case characters of ASCII are folded to their lower case equivalents", cize jasne sa hovori o normalnom ascii, ktore "á" nema. V povodnej otazke je napriklad "š" ktore tiez v ascii nie je.
Ako bolo uvedene vyssie, collation ma aj akcenty, ale kedy "a", "á" a "A" mozu byt definovane ako ekvivalenty. Co je pri radeni vysledkov uzitocne, ale nemyslim(nehovorim ze viem, lebo som to netestoval), ze tu je to aplikovatelne na index pre select query kedy sa hlada ekvivalent. Mozno ak sa spravi select s TRANSLATE() alebo nejakym inym modifikatorom, tak by to islo vyriesit ale to sme potom znovu pri tej transliteracii, ktoru je lepsie riesit priamo na urovni dat uchovanych v db prave na ucely indexovania.
-
Spránná odpověď pro SQLite je, že to nativně na rozdíl od větších databází neumí.
SQLite zná jen 3 varianty collation (BINARY, NOCASE, RTRIM) a ani jedna z nich neignoruje diakritiku.
Možná řešení:
- Použití vlastní collation – můžeš vytvořit vlastní porovnávací funkci v C pomocí sqlite3_create_collation, která normalizuje text (např. odstraní diakritiku).
- Normalizace textu v aplikaci – před uložením do databáze (např. převod na ASCII v C#, Pythonu, PHP…).
- Použití externí knihovny – některé SQLite rozšíření (např. ICU) podporují pokročilejší collation.
- Přejít na jinou databázi (PostgreSQL, MySql, MariaDb, ...), která to umí
Kdysi jsem taky trochu víc řešil výběr správné collation v MariaDB, když jsem požadoval shodu např i pro O = Ø, ae = æ. A našel :)
-
Zdravím.
Potřeboval bych poradit.
Existuje možnost udělat SELECT záznamů z tabulky s diakritikou i bez?
Potřeboval bych aby SQL dotazy
SELECT * FROM profile WHERE last_name LIKE 'jašková';
a
SELECT * FROM profile WHERE last_name LIKE 'jaskova';
vraceli stejný výsledek.
DB: Sqlite3
A co naistalovat sqlite3-pcre a pouzit REGEXP ?
sqlite> select * from TMP_TABLE where name REGEXP 'ja(s|š)kov(a|á)';
1|jaskova
2|jašková
-
...
A co naistalovat sqlite3-pcre a pouzit REGEXP ?
To je presne jeden z prikladu tech drahych reseni jak to obejit.
Ostatne sqlite je proto "lite", ze se nepredpoklada, ze od toho bude kdokoli chtit cokoli jinyho nez nejzakladnejsi primitiva. Jinak receno jediny spravny reseni je vymenit tu databazi.
BTW: Chtel bych videt, jak ten vyraz budes konstruovat, protoze to co dostanes bude "ěšžžůéíť" ... takze budes muset mit nekde definovanou jeste taky nejakou prekladovou tabulku.
-
Možná řešení:
- Použití vlastní collation – můžeš vytvořit vlastní porovnávací funkci v C pomocí sqlite3_create_collation, která normalizuje text (např. odstraní diakritiku).
- Normalizace textu v aplikaci – před uložením do databáze (např. převod na ASCII v C#, Pythonu, PHP…).
- Použití externí knihovny – některé SQLite rozšíření (např. ICU) podporují pokročilejší collation.
- Přejít na jinou databázi (PostgreSQL, MySql, MariaDb, ...), která to umí
Možná se pletu, kdyžtak mě opravte, ale collation se nepoužívá na operátory like nebo glob, ne? Jen na rovnost, nerovnost.
Takže by to nefungovalo třeba na "napovídání", kdy tam třeba uživatel může napsat jen část toho řetězce a rovnout to filtruje. Třeba to tazatel nechce, nevím.
Druhá varianta mi přijde nejschůdnější a bude s největší pravděpodobností i nejrychlejší.
Už jsem to někde takhle dělal, dokonce do samostatné tabulky, kde bylo ID a názvy bez diakritiky, co s tou původní pak byla jen provázaná.
Netuším, co tazatel používá za jazyk/prostředí, ale pokud by se chtěl z jakéhokoliv důvodu vyhnout duplikaci dat, tak už mi přijde možná lepší custom skalární funkce, která prostě vstupní řetězec zbaví diakritiky (mě na se tohle osvědčilo přes nějakou unicode knihovnu převést řetězec do NFKD denormalizovaného tvaru, což rozdělí všechny znaky s diakritikou na základní písmeno a akcent, a pak se zbavit všech znaků s kódem >127.)
U custom collation se posílají dvě hodnoty a vrací se výsledek, tady by ten overhead mohl být podobný (i když záleží jestli by to dělal třeba v Pythonu nebo C) a kolik má dat, akorát by to mohlo fungovat vcelku univerzálně s jakýmkoliv operátorem.
...
BTW: Chtel bych videt, jak ten vyraz budes konstruovat, protoze to co dostanes bude "ěšžžůéíť" ... takze budes muset mit nekde definovanou jeste taky nejakou prekladovou tabulku.
Tak taky by se to dal ten regex udělat. Vezme se vstupní řetězec, zbaví se diakritiky (viz to NFKD výše). Pak se po znacích porovná vstupní řetězec s tím bez akcentů a zkonstruuje se případně ten regex výraz.
Jak to bude rychlé/pomalé, je samozřejmě otázka testu. I ty varianty s custom collation nebo skalární funkcí budou mít svůj overhead.
Jak jsem zmiňoval a bylo to i v předchozích postech, já bych se klonil k druhému uložení sloupce bez diakrtitiky, pokud by to mělo být co nejefektivnější.
-
...
Jak jsem zmiňoval a bylo to i v předchozích postech, já bych se klonil k druhému uložení sloupce bez diakrtitiky, pokud by to mělo být co nejefektivnější.
Jenze tohle ti bude fungovat jen a pouze s nekolika malo jazyky.
-
SQLite nepodporuje unaccent ako PosgreSQL treba si vypomoct funkciou,
alebo mozes pozuit extension ktora tuto funkciu doplna
https://github.com/nalgeon/sqlean
-
Jenze tohle ti bude fungovat jen a pouze s nekolika malo jazyky.
To je jasné. Je to primárně o způsobu toho převodu na řetězec bez diakritiky, pro češtinu a příbuzné jazyky i západoevropské jazyky to může být prakticky buď v pohodě nebo tolerovatelné..
Samozřejmě pokud by se tam v těch collation mapách byly i složitější vazby, jako např. opisy v jiných abecedách 'ö' == 'oe', tak to takhle jednoduše řešit nepůjde. Můžeš si to dál zkomplikovat i asijskými jazyky, co mají víc abeced, případně nějakou ustálenou romanizaci (spasibo). Pokud se tohle chce pokrývat i v pokročilejších databázích, které mají sice různé rozsáhle collationy, ale jedna generická varianta to nedá rovnou, tak se to musí přepínat dopředu (třeba předpokladem očekávané collation podle jazykové verze UI).
Jinak hlavně nevíme vůbec nic dalšího k tomu projektu. Ty ukázkové query jsou divné (Chce opravdu používat like bez těch %_ wildcards nebo jen hledat přesnou shodu?). Jak to má být velké - jestli je to databáze místního klubu chovatelů a zahrádkářů, co bude mít napřesrok 232 řádek a je tam jen Čeština, nebo tam jde trochu o výkon a musí řešit ty cizí jazyky. V jakém to dělá prostředí, jestli může a dávalo by mu smysl zvolit jiný databázový engine.
Takhle tady klidně můžeme spekulovat a vymýšlet hypotetické cesty a jejich případné nevýhody do nekonečna ;)
-
Jinak receno jediny spravny reseni je vymenit tu databazi.
Ale tazatel potrebuje SQLite
-
Zdravím.
Potřeboval bych poradit.
Existuje možnost udělat SELECT záznamů z tabulky s diakritikou i bez?
Potřeboval bych aby SQL dotazy
SELECT * FROM profile WHERE last_name LIKE 'jašková';
a
SELECT * FROM profile WHERE last_name LIKE 'jaskova';
vraceli stejný výsledek.
DB: Sqlite3
A co naistalovat sqlite3-pcre a pouzit REGEXP ?
sqlite> select * from TMP_TABLE where name REGEXP 'ja(s|š)kov(a|á)';
1|jaskova
2|jašková
V inej databaze (DB2, PostgreSQL, alebo Oracle) by som pouzil funkciu translate(), napr. takto:
select * from TMP_TABLE
where translate(name, 'áéíóúščťžý', 'aeiousctzy') = 'jaskova'
;
Ale v SQLite bohuzial translate() nie je.
Nasiel som Oracle, PostgreSQL functions for SQLite: https://sqlite-libs.cs.ksu.edu/
Stiahol som to z https://github.com/sfaroult/sqlite_libs
Skompiloval som moduly orafn.so a pgfn.so avsak translate() ani v jednom z tychto modulov nefunguje spravne 'á' sice prelozi na 'a', ale 'š' neprelozi:
sqlite> .load ./orafn.so
sqlite> select
...> *,
...> translate(name, 'áéíóúščťžý', 'aeiousctzy') as name_t
...> from TMP_TABLE
...> ;
1|jaskova|jaskova
2|jašková|jaškova
Takze translate() vyzera byt v SQLite nepouzitelne (ale mozno existuje nejaka ina implementacia).
Ja by som pouzil to co funguje, t.j. hore uvedeny REGEXP
-
...
Nasiel som Oracle, PostgreSQL functions for SQLite: https://sqlite-libs.cs.ksu.edu/
Stiahol som to z https://github.com/sfaroult/sqlite_libs
Skompiloval som moduly orafn.so a pgfn.so
...
Modul orafn.so obsahuje unaccent() a to vyzera, ze funguje
sqlite> .load ./orafn.so
sqlite> select
...> name,
...> unaccent(name) as name_u
...> from TMP_TABLE
...> where unaccent(name) = 'jaskova'
...> ;
jaskova|jaskova
jašková|jaskova
-
...
Nasiel som Oracle, PostgreSQL functions for SQLite: https://sqlite-libs.cs.ksu.edu/
Stiahol som to z https://github.com/sfaroult/sqlite_libs
Skompiloval som moduly orafn.so a pgfn.so
To vypadá v pohodě, jsou tam docela rozsáhlé ty transformační dvojice nagenerované z názvů znaků v Unicode.
https://github.com/sfaroult/sqlite_libs/blob/c9d3937ce1756eab4c8d9dab5aede889393830e8/ksu/ksu_unicode.c#L470
Jestli se mu to podaří sestavit a natáhnout tu extensionu v jeho prostředí (ne všude je to povolené - např. Python na některých plaformách), tak by to mohlo situaci vcelku hezky pořešit. :)
-
...
Jestli se mu to podaří sestavit a natáhnout tu extensionu v jeho prostředí
...
Samozrejme podla zakona schvalnosti i ja som narazil na problemy:
1) musel som zmodifikovat vygenerovany makefile,
co som popisal tu https://github.com/sfaroult/sqlite_libs/issues/2
2) Pouzivam dbeaver community edition a tam sa mi nedari tu extension naloadovat.