Jak migrovat z MySQL na Oracle 10g databázi + PHP

Před pár měsíci jsem zde popisoval 10 bodů jak migrovat z MySQL na SQL Server 2008 + IIS7 + PHP. SQL Server 2008 považuji za současnou nejlepší databázi. Pro jeden projekt si klient přál použít Oracle 10g (má v ní obrovské skladiště jiných dat), takže bylo nutné pracovat s etalonem a matkou všech databází, a to na úrovni poměrně starého OCI8 rozhraní v PHP verze 5.1.6.

Náš redační systém tak už běží na MySQL 4.x (mysql), MySQL 5.x (mysqli), SQLite 2.0, SQLite 3.0 (přes PDO), SQL Server 2008 databázi (přes sqlsrv) a nově i na Oracle 10g (přes oci8).

Práce s Oraclem je v PHP poměrně dobře zaběhnutá a stabilní, přesto se hodí sumarizace hlavních rozdílů, pokud předěláváte redakční systém (kromě doplnění databázového layeru) z MySQL na Oracle.

1. Neexistuje LIMIT start, offset

Podobně jako SQL Server, tak ani Oracle nemá pohodlné stránkování v SQL přes LIMIT start, offset příkaz, jako umí (jinak velice primitivní) MySQL. Obejít se to dá ale snadno za použití sub-selectu a magické funkce ROWNUM, tedy obdoby ROW_NUMBER() z SQL Serveru:

SELECT * FROM (
	SELECT ROWNUM as CisloSloupce, fieldsX ORDER BY fieldsY ) 
	WHERE CisloSloupce BETWEEN ($offset+1)  AND ($offset+$limit)
	

Dobré je upravit si vaši metodu pro SQL příkaz v databázovém layeru, která SQL dotaz s LIMIT klauzulí automaticky modifikuje na vnořený SELECT, takže se nemusí nic dalšího přepisovat v CMS.

2. Číslo posledního vloženého záznamu s autoincrementem

Oracle stále nezná autoincrement pro numerickou položku v tabulce, takže ono často používané automatické sekvenční číslování si musíte zajistit přes sekvence a BEFORE INSERT triggery.

Vlastní sekvence musí být samozřejmě unikátní pro každou tabulku:

CREATE SEQUENCE TABLE_NAM_SEQ 
	INCREMENT BY 1 
	MAXVALUE 999999999999999999999999999 
	MINVALUE 1 
	CACHE 20 ;
					

Následně už vám stačí jen krátký trigger, který před uložením záznamu (BEFORE INSERT) naplní dané "autoincrement"pole v tabulce:

CREATE OR REPLACE TRIGGER "SCHEME"."TRIGGER_NAME" 
before insert on TABLE_NAME
for each row 
begin 
	IF :new.UID IS NULL THEN
		SELECT TABLE_NAME_SEQ.nextval INTO :new.UID FROM dual;
	END IF;
end;
ALTER TRIGGER "SCHEME"."TRIGGER_NAME" ENABLE

3. Práce s textem delším než 4.000 bajtů jedině přes LOBy a objekty

Pokud používáte OCI8, tak práce s delším textem je poměrně nepohodlná. Nemůžete použít VARCHAR2 datový typ, ale musíte jít do CLOBu, a k tomu se přistupuje z PHP trochu obtížněji.

Před prvním použitím je potřeba CLOB inicializovat pomocí field=EMPTY_CLOB(). Následně se tato položka v tabulce nebude chovat jako řetězcová proměnná, ale jako objekt, který má metody load(), save() a další.

Hezky je to popsané v této dokumentaci.

Na CLOB nemůžete udělat běžný INSERT či UPDATE, ale zásadně zavolat save() metodu daného objektu. Podobně pro jeho výpis pomocí echo zavoláte load() metodu.

Můžete si vše zjednodušit třeba funkcí, která provede inicializaci i uložení CLOB objektu za vás, stačí jí předat název tabulky, název položky, WHERE podmínku pro aktualizaci a vlastní obsah, a která se postará o vše potřebné (pro účely zjednodušení tohoto příkladu je odstraněna kontrola možných chyb v SQL dotazech):

function sql_clob($activedb, $table, $field, $where, &$content) {
	$field = strtoupper($field);

	// SELECT CLOBu
	$result = oci_parse($activedb, 
	"SELECT $field FROM $table WHERE $where FOR UPDATE");
	oci_execute($result, OCI_DEFAULT);
	if ( false === ($row = oci_fetch_assoc($result) ) ) {
		oci_rollback($activedb);
		die( 'Chyba pro SQL dotazu.');
	}

	// CLOB neni inicializovan ?
	if (!is_object($row[$field])) {
		$temp = @oci_parse($activedb, 
			"UPDATE $table SET $field=EMPTY_CLOB() WHERE $where");
		oci_execute($temp, OCI_COMMIT_ON_SUCCESS);
		@oci_free_statement($temp);
		$result = @oci_parse($activedb, $query);
		@oci_execute($result, OCI_DEFAULT);
		$row = @oci_fetch_assoc($result);
	}

	// vymaz CLOBu
	$row[$field]->truncate();

	// nahrani dat do CLOBu
	if ( !$row[$field]->save($content) )
		oci_rollback($activedb);
	else
		oci_commit($activedb);

	oci_free_statement($result);
	$row[$field]->free();
}

4. Práce s datem, tabulka DUAL

Práce s datem je v Oracle podobná jako v jiných databázích, namísto GETDATE() v SQL Serveru zde existuje SYSDATE, dále je zde běžné používat virtuální tabulku DUAL, která má právě jeden "univerzální virtuální záznam".

Je tedy možné psát třeba toto:

SELECT TABLE_NAME_SEQ.nextval INTO :new.UID FROM dual;

5. Neexistuje row count pro SELECT query v PHP

Standardně PHP nenabízí funkci pro zjištění počtu položek v daném dotazu (nejde o buffered query). Musíte to tedy obejít přes jiný SQL dotaz a udělat si třeba toto:

SELECT COUNT(*) FROM table WHERE condition

6. INSTEAD OF triggery na VIEW

Jednou z mnoha pěkných věcí, které Oracle umí, jsou INSTEAD OF (UPDATE / DELETE / INSERT) triggery na VIEW.

Vezměte si, že máte klientem danou tabulku jeho současných uživatelů, kterou nemůžete rozšířit (GRANT máte jen na SELECT; používá ji 10 dalších aplikací) o svoje vlastní potřebné položky (například o práva a role uživatelů ve vaší aplikaci).

Elegantní a rychlá cesta jak toho dosáhnout je vytvořit si svoji vlastní tabulku, která bude obsahovat tato doplňková data, poté si vytvořit VIEW, které sváže vaše pomocná data a data z primární tabulky klienta do jedné struktury, pojmenovat veškeré položky ve VIEW tak, aby s nimi pracoval redakční systém beze jakýchkoliv změn, a pro vlastní INSERT, UPDATE a DELETE příkazy si napsat triggery, který ponechají tabulku klienta beze změn, ale aktualizují tu vaši.

Triviální operací tak dosáhnete napojení CMS na zcela jinou databázi uživatelů, aniž dlouho programujete a ladíte.

7. Názvy polí v tabulkách jsou zásadně UPPERCASE

Oracle veškeré názvy polí v tabulkách, i názvy tabulek samotných, převádí na UPPERCASE.

Vadit vám to bude, pokud v redakčním systému natvrdo přistupujete například k $row['field'], toto nebude fungovat. Musíte naprosto všude používat $row['FIELD'].

Další info

Oracle je databáze, která je stavěná na masivní používání PL/SQL. Takže se nebojte výraznou část aplikační logiky z PHP přepsat přímo na úroveň databáze. Pokud pracujete s daty z mnoha zdrojů, je dobré používat SYNONYM a mapovat si veškerá data do svého schématu.

Samozřejmě je nezbytné používat CHECK CONSTRAINTS, FOREIGN KEYS a další prostředky pro zajištění korektní struktury databáze za každé situace.

Používání transakcí je samozřejmostí.

Připojení se k Oracle z PHP může vypadat nějak takto:

$SQL_DATA = array(
	'username' => 'jmeno',
	'password' => 'heslo',
	'db' => '(DESCRIPTION =
		(ADDRESS = 
			(PROTOCOL = TCP)
			(HOST = 192.168.1.100)
			(PORT = 1521)
		)
		(CONNECT_DATA =(SID = orcl))
	)',
	'charset' => 'UTF8',
	'session' => OCI_DEFAULT
);

$activedb = @oci_connect(
	$SQL_DATA['username'], 
	$SQL_DATA['password'], 
	$SQL_DATA['db'], 
	$SQL_DATA['charset'], 
	$SQL_DATA['session']);