Dotaz: Nastavení databáze na nerozlišování velikosti písmen.

[Hunt]

Ahoj. Nevíte jak to udělat?

---

---

Odpovědi

[Hunt]

Jsem zapomněl napsat to hlavní. Jedná se o postgres.

[axel]

Jeste jsi zapomnel napsat, co si pod tim predstavujes. Zkus vysvetlit, ceho chces dosahnout, a podle toho pak lze poradit, jak se to udela...

[Hunt]

Jak mám udělat v postgres, abych vyhledával všechny uživatele třeba "Wall" ale i "wall" ale už né "wal" protože jsem zjistil že postgres rozlišuje velikosti písmen. Našel jsem že jde udělat select * from uzivatele where nick ~* 'wall'; ale to mi najde i uživatele wal a když to udělám takhle select * from uzivatele where nick = 'wall'; tak zase mi to nenajde Wall. Potřeboval bych to nějak spojit. Ale to nejde... mohl bych udělat lower(nick) ale to prý zpomaluje databázi. Jsem zvyklej u MySQL že to nerozlišuje. Tady jo.

[Kit]

CREATE TABLE uzivatel(nick text);
INSERT INTO uzivatel values ('Wall');
INSERT INTO uzivatel values ('wall');
INSERT INTO uzivatel values ('WaLl');
INSERT INTO uzivatel values ('Wal');
INSERT INTO uzivatel values ('wal');
INSERT INTO uzivatel values ('wAl');
SELECT * FROM uzivatel WHERE nick ILIKE 'wal';

[Hunt]

Děkuju ILIKE. Super dekuji moc... a neni to nějaký zpomalující nebo tak něco? Nemá to prostě nějakou jinou svízel na kterou bych mohl narazit? Už jsem si zvykl, že všechno má věčinou nějakou svízel :D . To postgres je pro mě nové.

[Kit]

Svízel začne, když se začnou používat žolíky. Pro pár tisíc uživatelů bych to neřešil, milióny bych si normálně otestoval přímo na konkrétním stroji.

[Hunt]

Tak děkuji. Až bude milion uživatelů zaplatim si na to programátora který je skutečně programátor :D ... Jenom ještě jsem se chtěl zeptat co myslíte termínem "žolík" ?

[Kit]

Žolíky jsou zástupné znaky. Například "_" nahrazuje libovolný znak a "%" libovolnou posloupnost znaků. Mělo by se jimi šetřit, zejména na začátku hledaného stringu. Je také dobré tyto znaky ošetřit před zlobivými uživateli, aby nám systematicky nepřetěžovali databázi.

[NN]

divokou kartu ;)

[Hunt]

Aha... tak to asi vůbec nepoužívám, takže mě to zatím netrápí. Děkuji Vám za pomoc.

[Jakub Lucký]

nepomůže?

[Kit]

To je zrovna špatně. Správné řešení ILIKE je až na konci.

[Hunt]

A kde nakonci? Když to ILIKE dám na konec příkazu tak hodí error. NEbo jsem to nepochopil jak to myslíte teď.

[Kit]

Na konci odkazované diskuze.

Syntaxe už zde byla popsána a předpokládal jsem, že je už všem zúčastněným jasná. Pokud ne, tak stačí nakouknout do referenční příručky. Místo ILIKE lze použít i operátor ~~*

[okbob]

ILIKE je špatné řešení.

[Hunt]

to nepomůže když si někdo dá nick waLL

[okbob]

Používání ILIKE nebo ILIKE operátoru (navíc bez žolíků) je ošklivé a matoucí.

V Postgresu je extenze citext, která obsahuje datový typ citext - case insensitive text, což je to co chcete

• http://www.depesz.com/2008/08/10/waiting-for-84-case-insensitive-text-citext/
• http://www.postgresql.org/docs/9.1/static/citext.html

[Kit]

Pěkné. Jen se nesmí zapomenout na

CREATE EXTENSION "citext";

[okbob]

Samozřejmě - je to extenze. Ale pokud si ji nainstalujete to template1, tak na ni nezapomenete.

Navíc je případná chyba se jednoduše identifikuje - takže jakýkoliv unit test ji najde v několika prvních řádcích.

[Hund]

Toto tady mě mate. Proč je používání ILIKE ošklivé a matoucí? Záleží mi na tom abych to napsal správně.

[Kit]

Typ citext má výhodu v tom, že index je již optimalizován pro vyhledávání case_insensitive a hodí se tedy pro větší počet záznamů. Pokud však sloupec nebude indexován (do několika tisíc položek to zpravidla není nutné) je ILIKE jednodušší na použití - není třeba přidávat extension.

ILIKE se používá zpravidla ve spojení se žolíky. Nejsou však nezbytně nutné.

[axel]

ILIKE nepouzije index, coz muze byt fatalni vykonnostni problem. Specialne v pripade, ze ve skutecnosti neni zadny like potreba, ale jde o dotaz na presnou shodu bez ohledu na case (scenar vyberu konkretniho uzivatele). Pak je nejvhodnejsi funkcni index pro lower a pouziti lower. Nebo dokonce pridani sloupce s lower hodnotou (to bude fungovat vsude, ne kazda DB ma funkcni indexy nebo specialni rozsireni pro ignore case).

[Hund]

No já jsem teď zkoušel ten CITEXT podle návodu tady http://www.postgresql.org/docs/9.1/static/citext.html a vyhodí mi to ERROR: type "citext" does not exist

[Tarmaq]

a pouzil jsi vyse zminene CREATE EXTENSION "citext";?

[Hund]

Ano ale nevim jestli správně... když jsem to hodil normálně do command line tak : ERROR: permission denied to create extension "citext"

[Tarmaq]

zrejme uzivatel, pod kterym to spoustis, nema dostatecna opravneni..

[Hund]

Jo takhle... to bude určitě pravda no. To mě nenapadlo.

[Hund]

No ale nějak mi to nejde... dám jako ROOT

create database nova_database encoding 'utf8' owner hunt;

pak

CREATE EXTENSION "citext";

a pak jako hunt se snažim udělat nick CITEXT UNIQUE not null, a prostě ERROR: type "citext" does not exist

[axel]

Uz jsi nekdy zadal chybovou hlasku do google? Zkus to!

[Hund]

Ano. Prostě jsem to neposkládal. Používám pořád google.

[okbob]

Protože ten správný uživatel s právy na instalaci extenze (tzv superuser) je "postgres"

melo by fungovat:

su
su postgres
psql vasedb
CREATE EXTENSION "citext";

Postgres neběží s právy roota jelikož by to bylo bezpečnostní riziko - takže má vlastního "roota" čímž je uživatel Postgres.

Původně jsem to tady nechtěl vytahovat, ale možná by Vám pomohlo školení - 23. října - PostgreSQL efektivne - Základní administrace PostgreSQL, vysvětlení základních funkcí PostgreSQL ovlivňujících chování MVCC, TOAST - cílem školení je vysvětlit nejdůležitější funkce PostgreSQL, tak aby účastník školení dokázal efektivně nasadit a provozovat PostgreSQL (ROOT). Školím pro iinfo.

[Hund]

No ale takhle v podstatě jsem to dělal, jak jste napsal... s tim rootem jsem to napsal špatně... to je zvyk z MySQL pochopitelně že postrgres, tak to testuju na windows, ale hodil jsem :

postgres=# create database nova_database encoding 'utf8' owner hunt; CREATE DATABASE postgres=# CREATE EXTENSION "citext"; CREATE EXTENSION postgres=#

a pak jsem se přihlásil na hunt a chtěl jsem udělat

nick_uzivatele CITEXT UNIQUE not null,

což by měl bejt stejnej postup ne? Nebo já už nevim, začínám toužit po MySQL.

[Šangala]

Malá chybička - extenzi jste vytvořil v databázi postgres - extenze jsou per db, takže musíte být v cílové databázi nebo si ji nainstalujte do databáze template1. Potom ji budete mít automaticky ve všech nově vytvořených databázích.

psql postgres
postgres=# create database nova_database encoding 'utf8' owner hunt; 
CREATE DATABASE 
--- prepnout se do cilove db
\c nova_database
CREATE EXTENSION "citext"; 
CREATE EXTENSION

Jakmile se v pg rozkoukáte, tak to je v pohodě - úplně nejhorší na Postgresu je, že to nemá stejné ovládání jako MySQL.

Jak vidím, měl bych do článku http://postgres.cz/wiki/P%C5%99echod_z_MySQL přidat ještě část o case sensitivity

[Šangala]

Ještě mně tam napiš něco napiš, a to co mám dělat když migruji SMALLINT unsigned nebo i INT unsigned a nechci databázi velkou jak kráva( rýpu?), štve mě to a už několikrát jsem byl nešťastný, že se mi DB sakramentsky zvětšila (bo právě proto byly použity 2^16 a 2^32 datové typy, bo to je pro aplikaci platný rozsah).
A taky jak s verzí > 9 udělit oprávnění více uživatelům(rolím) ke všem tabulkám (ve schema-tu) - bo to člověka fakt zarazí, a je dobré mít návod.
A jako třešničku:

CREATE TABLE tx ( x SMALLINT, PRIMARY KEY (x));
INSERT INTO tx (x) VALUES(32767);
SELECT x*2 AS val FROM tx;

To jsou tři věci, které jsem tam nenašel, a které mě při migraci často zabolí (pominu-li, že mysql akceptuje INSERT bez INTO líní lidé jsou…).

[okbob]

co např. http://pgxn.org/dist/varint/0.1.0/? Podobných extenzí existuje vícero, stačí hledat - případně si ji napsat. Je to docela jednoduché, a dá se jako základ vzít některá existující.

I 9.1 podporuje

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT SELECT ON ALL TABLES IN SCHEMA xxxx TO GROUP nejaka_grupa

případně:

do $$
  declare users text[] = ARRAY['pavel','postgres']; 
  u varchar; r record; 
begin
  for r in 
    select * 
       from information_schema.tables 
      where table_schema = 'public' 
  loop
    for u in select * from unnest(users) 
    loop
      execute format('grant select on %I.%I to %I', r.table_schema, r.table_name, u);
    end loop; 
  end loop; 
end;$$ ;

Kdybych z nějakého důvodu nemohl použít skupiny (třeba z vlastní lenosti).

99% z MySQL lze do Pg nějak převést - to 1 procento nejde - v několika málo ohledech tyhle dvě databáze mají úplně jinou filozofii, jiná řešení a měly (a mají) jiné priority vývoje.

[Šangala]

Myslel jsem aby jsi to doplnil na wiki, bo to jsou věci, s kterými se přechodník setká a potrápí jej

Ad. unsigned - dík, zajímavé řešení, ale jen pro situace kdy tabulka obsahuje velké rozpětí hodnot (tedy spíše spoustu malých a pár velkých) jinak je to nastejno jak užití vyššího datového typu nebo je to o kus horší (už 15bits ve 4B blééé)…

Ad. Vím - jsem to musel už řešit několikrát, stojí za zmínku i ALTER DEFAULT PRIVILEGES IN SCHEMA… - což je už konečně to co je „třeba“ :), ale chtěl jsem napsat verzi < 9 (, ale nějak se mi pomíchali &gt; &lt; ), člověku dělající v MySQL to přijde velmi neohrabané a je zmaten - obdobnou fci používám, jen má nevýhodu, že po každém doplnění tabulek, se nesmí zapomenout ji použít.

Ad. případ (třešnička) implicitního ne-přetypování na vyšší typ, tak na to spousta „obyčejných“ lidí co dělali jen s MySQL čučí jak čáp do trubky…

[okbob]

S přechodem na větší typ to je diskutabilní - datový typ musí být určen před vlastním během - a zbytečně jít do většího typu může způsobovat jiné výkonnostní problémy, takže tam neexistuje ve všech ohledech výhodnější řešení - a v MySQL vybrali A, a v PG B, přičemž každá varianta má svoje. Vzhledem k designu a podpoře přetížení se v PG musí víc explicitně přetypovávat - takže v PG se prostě typuje - a uznávám, že to může být šok pro někoho, kdo přechází z jakékoliv jiné DB. Typový systém je v PG bohatší a umožňuje docela hezké kousky, které ostatní db nezvládají (přetěžování, polymorfní typy, variadické funkce), ale je jiný, a složitější pro začátečníky.

[Šangala]

Ju. Aby jsme si rozuměli, nechtěl jsem rozbíhat nic ve smyslu MySql vs. PostgreSql a obhajobu či hanění řešení. Jen když jsi zmínil doplnění wiki, já si ji přečetl, tak jsem navrhl doplnit další rozdíly, které považuji za důležité.

Přiznávám, že absence unsigned nedokážu brát s nadhledem a jsem schopen/ochoten flejmovat na toto téma .

[okbob]

:)

Taky si myslím. Za celou dobu, co tu dělám s Postgresem, se na mne s dotazem ohledně unsigned typů obrátili možná tak dva lidi, a z toho jeden jste Vy.

Skoro každý program má pár vychytávek, které se tam dostaly při ranném vývoji, kdy se snažíte vyhovět každému (z těch málo prvních) zákazníků, a vůbec nemyslíte na zítřek nebo na nějakou koncepci. Před 10-12 lety bylo o dost jednodušší dostat do PG jakokoukoliv funkci, stačilo že vypadala dostatečně cool. Teď už jak se hodně důkladně rozvažuje, co přidat a co ne - bo to bude kód na minimálně 10 let a je hodně obtížná cesta zpět, pokud by se vymyslela nějaká bejkárna. Je to hodně konzervativní a pokud něco není přímo vyžadaváno ANSI SQL, tak dá hodně práce to protlačit.

[okbob]

Pošli mi text, který tam mám dát. S tímhle jsem nikdy neválčil - a tudíž nemám tu správnou motivaci a znalosti, bych to napsal.

[Šangala]

Tak tomu jsem se chtěl vyhnout , ale zesmolím a pošlu, řekněme do týdne…

[Hund]

Děkuju Vám. Už jsem to rozchodil.

[okbob]

To je správně - běžný uživatel nemá práva instalovat extenze. Prostřednictvím chybně napsaných extenzí lze snadno získat vyšší práva - a tudíž je vždy na administrátoru databáze, aby rozhodl a dovolil, které extenze se budou instalovat. Extenze z balíku postgresql-contrib jsou bezpečné - nicméně zatím stále platí omezení na superusera.

[okbob]

LIKE (ILIKE) má jinou sémantiku - říkám tím, že budu vyhledávat na základě částečné shody, nikoliv ekvivalence. Kromě toho, že člověk ze sebe dělá tatara, tak se vám bez dalšího zvláštního zápisu (který si málokdo dohledá) nikdy nechytne index, což vede k pomalejšímu zpracování.

Ve vašem případě je to podobné, jako kdybyste v běžném kódu místo A + B napsal A - -B, výsledek je stejný, ale každý bude muset přemýšlet, proč jste to napsal, tak jak jste to napsal - a než člověk přijde na to, že to je nezkušenost autora (případně trollování), z které automaticky nikoho nepodezíráte, tak se spálí hromada času.

[Tarmaq]

jestli tomu rozumim spravne, pokud teda je pouzit datovy typ citext, tak ani pak neni treba pouzivat ILIKE, protoze i LIKE by mel nad citex sloupcem fungovat case insensitive - je to tak?

[okbob]

ano

[okbob]

viz http://postgres.cz/wiki/Přechod z MySQL#Identifikátory a řetězce

Funguje to dobře - pozor bez indexu jsou operace na citext typu pomalejší než na varcharu cca o 30% (což znamená, že by se měl používat s indexem nad 10 K řádků). Case insensitive porovnání je náročnější než case sensitive - což je u důvod, proč je to navrženo, tak jak je to navrženo.