Mnoho projektov, ktoré vyžadujú na svoj beh databázu sú písané len pre jeden typ. Je to najmä MySQL, ktorá má na serveroch najväčšie zastúpenie, no nie vždy je dostupná a preto sa vytvárajú aplikácie, ktoré dokážu pracovať na viacerých typoch databáz. Samozrejme na to existujú špeciálne napísané funkcie, ktoré prevádzajú SQL príkaz podľa zvolenej DB. Je však tiež dobré poznať niektoré rozdiely v použití syntaxe a pokúsiť sa písať príkazy aspoň do podoby, pri ktorej sú vykonateľné na viacerých DB.
Ja som si zobral na mušku práve MySQL a PostgreSQL, ktoré sú najviac využívanými a dajú sa pomerne dobre zlúčiť. Iste v OpenSource komunite sa tiež dosť vyskytujú databázy ako Firebird a SQLite, ale tými sa teraz zaoberať nebudem.
Začnime teda preberať niektoré rozdiely a možné zlučiteľnosti týchto dvoch databáz. Najprv je však nutné pripomenúť, že PostgreSQL nepozná obrátené apostrofy (`), preto všetky príklady budú vypisované bez nich. Aj MySQL zvládne zápis bez týchto obrátených apostrofov. Taktiež zabudnite na komentáre pomocou #. Všetky vytvárajte pomocou dvoch pomlčiek --, ktoré pracujú rovnako v oboch databázach.
Podmienka LIMIT
Veľmi častou využívaná vec ak chceme obmedziť počet výsledkov. MySQL vždy využívalo syntax v tomto štýle:
LIMIT [zacat_od],[pocet] -- priklad: LIMIT 10,5PostgreSQL má inú podobu tejto podmienky avšak od verzie MySQL 4.0.6 je tento zápis tiež prístupný a možno by som povedal, že aj zrozumiteľnejší. Takže po novom to bude vyzerať takto:
LIMIT [pocet] OFFSET [zacat_od] -- priklad LIMIT 5 OFFSET 10
Zápis času (DATETIME vs TIMESTAMP)
PostgreSQL oproti MySQL nepozná typ timestamp a namiesto toto používa TIMESTAMP. Samozrejme aj MySQL pozná tento typ, lenže so zmenami verzie sa dosť menila jeho podoba. Od verzie 4.1 sa ustálilo a TIMESTAMP má rovnakú podobu ako timestamp len s tým rozdielom, že má obmedzený rozsah rokov od 1970 po 2037. Pri reálnom projekte ho nie je možné presiahnuť vzľadom k životnosti projektu.
Oproti timestamp má však výhodu v tom, že ako predvolenú hodnotu možno použiť CURRENT_TIMESTAMP, čím si môžeme uľahčiť zápis do DB, pretože nemusíme posielať čas vytvorenia ale pri zápise si pridá aktuálny bežiaci na servery.
Avšak TIMESTAMP u oboch sú rozdielne. PostgreSQL zapisuje aj mikrosekundy a časové pásmo(štandardne vypnuté).
Vytvára sa to nasledovne:
--spolocne CREATE TABLE tabulka ( datum TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );Zápis musí mať tvar RRRR-MM-DD HH:MM:SS. Dajú sa pekne použiť vstavané SQL funkcie NOW() a LOCALTIMESTAMP, ktoré vracajú rovnaký výsledok.
Výstupný formát dátumu
Tuto vidím rozdiel medzi MySQL a PostgreSQL, pretože každý používa rozdielne postupy ako formátovať čas uložený v databáze.
MySQL používa naríklad:
DATE_FORMAT (datum, '%H:%s - %d.%m.%Y') --vystup HH:MM - DD.MM.RRRRKdežto PostgreSQL má takýto tvar:
TO_CHAR(datum, 'HH24:MI - DD.MM.YYYY') --vystup HH:MM - DD.MM.RRRRMožnú alternatívu ako to vyriešiť vidím v tom, že by sa v scriptoch nachádzal prepínač (switch), ktorý by podľa zvolenej DB vyberal správnu podobu tohoto prevodníka.
INTEGER stĺpce
Jediný rozdiel v zápise je ten, že PostgreSQL nedefinuje počet miest, koľko má obsahovať číselný stĺpec.
--mysql varianta CREATE TABLE uzivatelia ( cislo SMALLINT(4) ); --postgresql CREATE TABLE uzivatelia ( cislo SMALLINT );
Konfiguračná tabuľka a TINYINT
Postgre nepozná klauzulu TINYINT, ktorá sa používa najmä na ukladanie nastavenia. Táto tabuľka väčšinou obsahuje dve hodnoty: 1-zapnuté alebo 0-vypnuté. Má výhodu v tom, že je veľkosťou len 1 bajtová. Rovnakým typom stĺpca by sa dal použiť BOOLEAN (BOOL), ktorý je od MySQL 4.1 využívaný ako synonymum pre TINYINT(1), avšak v budúcnosti má tento typ plne vyhovovať SQL norme.
CREATE TABLE nastavenie ( zobraz_kom BOOLEAN NOT NULL DEFAULT 1 ); --alt. postgre CREATE TABLE nastavenie ( zobraz_kom SMALLINT NOT NULL DEFAULT 1 ); --alt. mysql CREATE TABLE nastavenie ( zobraz_kom TINYINT(1) UNSIGNED NOT NULL DEFAULT 1 );Je dobré si pamätať že ak zvolíte v PostgreSQL typ BOOLEAN musia sa do neho zapisovať hodnoty v apostrofoch, inak to vyhodí chybu. Teda nasledovne
INSERT INTO nastavenie (zobraz_kom) VALUES ( '1' )Návratová hodnota BOOLEANu z Postgre je t (TRUE) alebo f (FALSE). Odporúčam však kvôli rovnakým návratovým hodnotám (1,0) používať u MySQL TINYINT(1) a u PostgreSQL SMALLINT.
Ako na AUTO_INCREMENT a UNSIGNED
PostgreSQL nepozná hojne využívanú klauzulu AUTO_INCREMENT, ktorá sa dá v MySQL použiť na hocijaký rozmer INTEGER stĺpca, ale používa vlastné typy: SERIAL a BIGSERIAL. SERIAL je podobný INT(10) a BIGSERIAL je ako BIGINT.
--mysql CREATE TABLE tabulka ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ); --postgre CREATE TABLE tabulka ( id SERIAL PRIMARY KEY );Nie je potreba pridávať NOT NULL, pretože tá je dávaná pre tento typ automaticky.
Spočítanie počtu riadkov
Určite ste sa veľa krát stretli s nutnosťou zistiť počet všetkých výsledkov aby sa dalo použiť stránkovanie.
Osobne mám v záľube, vždy keď sa dá (od verzie MySQL 4.0.14) používať u MySQL jednu dôvtipnú vymoženosť spočítavania riadkov pri vyžívaní LIMITu. Spočíva v tom, že aj keď je výsledok obmedzený LIMITom, tak zistí počet všetkých výsledkov bez ohľadu na toto obmedzenie.
Stačí do SELECTu pridať jedno slovo SQL_CALC_FOUND_ROWS a následne ho cez daľšie volanie získať pomocou SELECT FOUND_ROWS(). Dá sa na to použit mysql_result, pretože získavame len jedno čislo a teda netreba varianty pomocou poľa. Môže to mať následnú podobu:
-- volanie do DB
SELECT
SQL_CALC_FOUND_ROWS
id,
meno,
prispevok
FROM komentare
WHERE id_clanku = 2006070501
ORDER BY datum
LIMIT 10 OFFSET 120
-- ziskanie poctu vsetkych riadkov
phprs_sql_result(phprs_sql_query("SELECT FOUND_ROWS()"), 0);
Treba si zapamätať, že za tou klauzulou sa nepíše čiarka.SELECT COUNT(*) FROM komentare WHERE id_clanku = 2006070501Preto môj osobný názor je ten, že ak budete tvoriť projekty aj s podporou inej DB, bolo by vhodné vytvoriť prepínač alebo podmienku na zvolenie uváženého postupu podľa typu databázy.
Konflikty s rezervovanými menami
Každá databáza si vyhradzuje právo rezervovať si určité slová ktoré slúžia ako funkcie alebo typy. Nájsť ich môžeme tu:
PostgreSQL - registrované kľúčové slová
MySQL - registrované kľúčové slová
A tu nastáva práve jeden rozkol v slove USER. phpRS používa tento názov na prezývku autora(mod., admina). Dá sa to vyriešiť tým, že sa slovo USER uzavrie do uvodzoviek a vtedy ho začne vyhodnocovať ako názov stĺpca. Podobne to je aj pri tvorbe databáze.
SELECT idu, "user", password, jmeno, admin, pravo_vydavat, blokovat, jazyk_prostredi FROM rs_user WHERE "user" = 'admin' AND admin >= 0Avšak nastáva chyba, ktorú asi vidíme neradi. MySQL nepovoľuje používať uvodozvky a tým pádom vráti nulový počet riadkov. Je dobré sa týmto rezervovaným slovám vyhýbať, pretože môžu spôsobiť takéto nemilé problémy v zlučiteľnosti.
Hodnota NULL v INSERT(UPDATE) príkaze
Tuto by som chcel upozorniť na to, že hodnota NULL je sama o sebe neistá vec a nespráva sa všade rovnako. Prípad kedy sa hojne používa je vtedy keď ju zapisujeme do AUTO_INCREMENTovaného poľa v MySQL.
U Postgre táto možnosť neplatí a vráti nám chybu, že sa nemôže zapisovať NULL hodnota do NOT NULL stĺpca. Tam sa odporúča použiť namiesto toho slovko DEFAULT, ktoré od verzie 4.0.3 funguje aj v MySQL. Osobne považujem nevypisovať názvy stĺpcov za veľké sťaženie pri programovaní. S výpisom by syntax vyzerala nasledovne:
--tabulka obsahuje: id, meno, prispevok INSERT INTO komentare (meno,prispevok) VALUES ( 'nejake_meno', 'nejaky_text' );Nevypisovať stĺpec id je výhodou v tom, že databáza sama najlepšie vie aké nasledujúce číslo má pridať a tým nemôže nastať kolízia. Toto nie je jediná výhoda vymenovávania stĺpcov. Zabezpečí sa tým aj konzistencia stĺpcov, to znamená že aj keď pridáme nejaký nový stĺpec tak sa nám nerozhodí celý projekt, pretože sa bude vždy zapisovať len do tých stĺpcov, ktoré sme vymenovali a nedôjde k chybe kvôli malej nerovnosti v databáze. Teda pri vypisovaní stĺpcov záleži len na poradí vymenovania stĺpcov a nie na poradí v akom sú uložené v DB.
Transakcie
Tu to zhrniem len krátko. Tu sú obe DB na tom takmer rovnako. Obe poznajú dva rozdielne tvary ako sa spúšťajú transakcie: BEGIN alebo START TRANSACTION.
--spustenie transakcie BEGIN | START [TRANSACTION] --potvrdenie COMMIT --zrusenie ROLLBACK )
Indexy
Medzi MySQL a PostgreSQL je malý rozdiel pri tvorbe INDEXOV, takže tu sa bude musieť vytvárať rozdielna podoba príkazov. Okrem UNIQUE a PRIMARY KEY indexov sa priamo v prikaze CREATE TABLE nedajú vytvoriť klasické indexy. Na to je iný postup:
CREATE INDEX meno_indexu ON meno_tabulky (meno_stlpca); --priklad CREATE INDEX tabulka_datum ON komentare (datum);Jedna nutná pripomienka, ktorú si pamatajte: V PostgreSQL sa nemôžu názvy INDEXov opakovať, aj keď sa každý nachádza v inej tabuľke. Takže je dobré ich odlišovať názvom tabuľky, čím sa nebudú nikdy opakovať. MySQL považuje túto syntax za prekonanú a ak chcete pridať dodatočne INDEX treba to robiť pomocou ALTER TABLE
Rozdiely kódovania
Pri inštalácii niektorých pluginov som natrafil na nepeknú skúsenosť u PostgreSQL. Pokiaľ sa nezhoduje kódovanie DB a kódovanie textov, ktoré sú do databázy posielané vráti sa chyba. MySQL to zapíše aj keď sa kódovania nezhodujú.
Podľa týchto úprav sa mi podarilo rozbehať čistú inštaláciu phpRS (2.8.0) aj pod databázou PostgreSQL, ktorá je však funkčná len na verzii 8.1 a vyššej. Preberať ďalšie rozdiely databáz nebolo náplňou tohto článku.
Kto sa vyzná v PHP a databázach môže si phpRS upraviť na podporu PostgreSQL. Pre takých tu prikladám upravené súbory: .sql a ovladací php script. Do config.php potom stačí pridať nový typ, najlepšie 'postgre'.