PREMESSA
Alcune cose scritte per un database MySQL possono riscritte in maniera più generica, adatta ad un maggior numero di database.
Questo si può fare sfruttando le librerie e le funzioni messe a disposizione dalla ADODB.
Per alcuni costrutti invece ciò non si può fare.
Quando ciò accade è necessario implementare una struttura di controllo (generalmente un IF) che verifichi il tipo di database e adotti, di conseguenza, le istruzioni comprensibili per quella base dati.
Le regole qui di seguito elencate derivano dal lavoro pratico e dai problemi riscontrati nel trasportare il CMS Envolution da una base dati MySQL ad una basedati Oracle8i.
Alcuni punti derivano dalle differenze intrinseche dei 2 database, altri derivano dalla specificità
dei costrutti del linguaggio php usato e altre ancora derivano da annotazioni sul codice codice SQL usato.
[1]
Oracle tratta le stringhe nulle come 'NULL' e non come '' (stringhe vuote), perciò quando si vuole fare un controllo su una stringa vuota bisogna usare 'IS NULL' o 'IS NOT NULL'.
Esempio funzionante con entrambi i DB:
$querylang = "AND ($column[sublanguage]='$currentlang'
OR $column[sublanguage]=''
OR $column[sublanguage] IS NULL)";
Esempio di query che vanno personalizzate a seconda del database:
[mysql]
SELECT - FROM - WHERE - AND $column[optiontext] NOT LIKE "";
[oracle]
SELECT - FROM - WHERE - AND $column[optiontext] IS NOT NULL;
Attenzione a quando si devono inserire delle stringhe in un campo LOB di Oracle.
La query:
INSERT INTO envo_subjects (subid, subtext, subactive) VALUES (0, '', 1)
dove subtext è un CLOB, in Oracle dà errore
Bisogna usare le funzioni empty_clob() ed empty_blob():
INSERT INTO envo_subjects (subid, subtext, subactive) VALUES (0, empty_clob(), 1)
e poi usare funzioni quali updateClob updateBlob (vedi [7]) se si vuole inserire un valore.
[2]
In Oracle le stringhe da inserire nel DB vanno delimitate da apici singoli e non doppi, per MySql è la medesima cosa.
La seguente riga dà errore con DB Oracle:
$myquery2 = buildSimpleQuery('users', array('uid', 'name', 'uname', 'femail', 'url','user_regdate'),
'pn_uname NOT LIKE "Anonymous"' , 'pn_user_regdate DESC' , 10);
perchè c'è "Anonymous".
La riga corretta (che funziona con entrambi i database) è:
$myquery2 = buildSimpleQuery('users', array('uid', 'name', 'uname', 'femail', 'url','user_regdate'),
'pn_uname NOT LIKE \'Anonymous\'' , 'pn_user_regdate DESC' , 10);
[3]
L'istruzione MySQL 'NOT LIKE' funziona con Oracle, ma non va applicata alle stringhe vuote (vedi [1])
Esempio funzionante:
SELECT COUNT(*) FROM envo_users where pn_uname NOT LIKE 'Anonymous';
[4]
Non bisogna usare le funzioni del php rivolte specificatamente ad una database mysql come:
mysql_query
mysql_numrows
mysql_fetch_array
mysql_free_result
....e molte altre ancora.
CASO 1 -> mysql_query
Esegue la query indicata.
Esempio
[mysql]
$result = mysql_query($query);
va sostituita con la più generica (ma ugualmente valida):
$result = $dbconn->Execute($query);
if ($dbconn->ErrorNo() != 0) {
return;
}
CASO 2 -> mysql_numrows
restituisce il numero di righe ottenute con una select
Esempio
[mysql]
$result = $dbconn->Execute("Select * from Table;");
if ($dbconn->ErrorNo() != 0) {
return;
}
$num = mysql_numrows($result);
va sostituita con la più generica (ma ugualmente valida):
$result = $dbconn->Execute("Select * from Table;");
if ($dbconn->ErrorNo() != 0) {
return;
}
$num = $result->RecordCount();
CASO 3 -> mysql_result
'mysql_result' va sostituita molto attentamente con l'istruzione list(...)
Una query generica del tipo:
SELECT * FROM table
restituirà più colonne e più righe.
Per scandire le righe non conviene usare il for (con Oracle il for potrebbe non riconoscere l'EOF nella $result e non uscire mai dal ciclo) ma un ciclo while del tipo:
while (!$result->EOF){
list(.....) = $result->fields;
$result->MoveNext();
}
Per scandire le colonne bisogna mettere i valori opportuni nel list(....)
L'istruzione list contiene la sequenza di tutte le colonne restituite dalla query, riga per riga.
Perciò se voglio leggere il terzo valore devo necessariamente leggere anche i primi 2 (anche se non mi servono!!!).
Esempio:
[mysql]
$result = mysql_query("SELECT * FROM ".$table." WHERE pn_id='".$modID."'");
$num = mysql_numrows($result);
for ($i = 0 ;$i < $num; $i++){
$modDir = mysql_result($result, $i, "pn_directory");
$modName = mysql_result($result, $i, "pn_name");
$modDesc = mysql_result($result, $i, "pn_description");
$modVers = mysql_result($result, $i, "pn_version");
$modType = mysql_result($result, $i, "pn_type");
$modState = mysql_result($result, $i, "pn_state");
}
[tutti i database]
$result = $dbconn->Execute("SELECT * FROM ".$table." WHERE pn_id='".$modID."'");
$num = $result->RecordCount();
while (!$result->EOF){
list($pn_id, $modName, $modType, $pn_displayName, $modDesc, $pn_regid, $modDir, $modVers,
$pn_adm_cap, $pn_usr_cap, $modState) = $result->fields;
$result->MoveNext();
}
CASO 4 -> mysql_insert_id
restituisce l'ultimo numero generato dall'autoincrement di una tabella MySQL.
Più genericamente si può fare:
$result2 = $dbconn->Execute("SELECT MAX(".$column['id'].") FROM ".$table);
list($maxId)=$result2->fields;
return $maxId;
[5]
I cicli for che usano $result->MoveNext() come incremento della variabile del ciclo non si devono usare.
Questo per lo stesso motivo per cui non si devono usare i cicli while(list...
(ci sono problemi con EOF)
quindi
il seguente ciclo:
for (; !$result->EOF; $result->MoveNext()) {
list($smodname, $tmodname) = $result->fields;
...
...
}
deve essere sostituito con un ciclo del tipo:
while(!$result->EOF){
list($smodname, $tmodname) = $result->fields;
...
...
$result->MoveNext()
}
NB: l'istruzione $result->MoveNext() va messa a fine ciclo!!
[6]
Quando si incontrano costrutti SQL specifici di un database Mysql devono essere convertiti negli analoghi costrutti Oracle (quando ciò è fattibie)
Alcuni costrutti 'traducibili' sono INNER JOIN, LEFT JOIN.
Esempio, la seguente query mysql:
SELECT A.pn_source, A.pn_target, COUNT(B.pn_constant), COUNT(C.pn_translation), SUM(pn_level)
FROM envo_languages_file A,
envo_languages_constant B LEFT JOIN envo_languages_translation C
ON B.pn_constant=C.pn_constant AND pn_language='eng'
WHERE A.pn_source=B.pn_file GROUP by B.pn_file
deve essere tradotta in oracle come:
SELECT B.pn_file, A.pn_target, COUNT(B.pn_constant), COUNT(C.pn_level), SUM(C.pn_level)
FROM envo_languages_file A,
envo_languages_constant B,
envo_languages_translation C
WHERE B.pn_constant (+) =C.pn_constant
AND C.pn_language='eng'
AND A.pn_source=B.pn_file
GROUP by B.pn_file
Ovviamente mysql non capirebbe la seconda query, perciò devo usare un costrutto
if (strcmp($dbtype,'oci8')== 0) {
perciò l'esempio completo è:
if (strcmp($dbtype,'oci8')== 0) {
$query="SELECT b.pn_language, COUNT(b.pn_level), SUM(b.pn_level)
FROM ENVO_LANGUAGES_CONSTANT A , ENVO_LANGUAGES_TRANSLATION b
WHERE A.pn_constant (+) =b.pn_constant AND b.pn_language IS NOT NULL
GROUP BY b.pn_language";
}else{
$query="SELECT pn_language, COUNT(B.pn_translation), SUM(pn_level)
FROM $pntable[languages_constant] A LEFT JOIN $pntable[languages_translation] B
ON A.pn_constant=B.pn_constant WHERE pn_language IS NOT NULL GROUP BY pn_language";
}
N.B.
Alcune query che usano il comando 'left join' potrebbero dover venir suddivise in due o più query Oracle.
[7]
Oracle 8 non supporta l'istruzione LIMIT di mysql.
Al posto di questa funzione la libreria ADODB mette a disposizione la funzione SelectLimit
(si trova nel file /pnadodb/adodb-inc.php ).
Esempio:
[mysql]
$myquery = "SELECT $storiescolumn[sid],
$storiescolumn[alanguage],
$topicscolumn[topicname]
FROM $pntable[stories]
ORDER BY $storiescolumn[time] DESC LIMIT $admart";
}
$result = $dbconn->Execute($myquery);
[Oracle]
$myquery = "SELECT $storiescolumn[sid],
$storiescolumn[alanguage],
$topicscolumn[topicname]
FROM $pntable[stories]
ORDER BY $storiescolumn[time] DESC";
}
$result = $dbconn->SelectLimit($myquery,$admart);
[8] CLOB BLOB
INSERT E UPDATE di campi CLOB/BLOB
I campi 'text', 'longtext' o simili di MySQL conviene vengano tradotti in Oracle come CLOB o BLOB.
Questi ultimi 2 tipi di dati non possono essere trattati come normali char e quindi tutte le operazioni
che insistono su di essi devono essere modificate.
Con Oracle non si può inserire un valore nullo ('') in un LOB (ma si può inserire un valore non nullo!!).
Quindi le insert e le update che passano valori in campi LOB devono essere modificate per assicurarsi che tali campi non ricevano valori nulli.
Esempio
[mySQL]
$sql = "INSERT INTO $pntable[message]
($column[mid],
$column[title],
$column[content], -----> CLOB
$column[date])
VALUES
(" . $nextid . ",
'" . $title . "',
'" . $content . "',
'" . $mdate . "')";
$result = $dbconn->Execute($sql);
va convertita in:
[ORACLE]
if (strcmp($dbtype,'oci8')== 0) {
$sql = "INSERT INTO $pntable[message]
($column[mid],
$column[title],
$column[content],
$column[date])
VALUES
(" . $nextid . ",
'" . $title . "',
empty_clob(),
'" . $mdate . "')";
$result = $dbconn->Execute($sql);
$dbconn->updateClob($pntable[message],$column[content],$content,clausola WHERE);
}else{
$sql = "INSERT INTO $pntable[message]
($column[mid],
$column[title],
$column[content],
$column[date])
VALUES
(" . $nextid . ",
'" . $title . "',
'" . $content . "',
'" . $mdate . "')";
$result = $dbconn->Execute($sql);
}
la funzione UPDATECLOB è:
updateclob(nome tabella, nome colonna clob , valore da inserire, clausola where) e si trova nella libreria oci8.php
CARATTERI SPECIALI nei CLOB
Quando si inseriscono dei caratteri speciali in un campo CLOB bisogna fare attenzione ad alcune cose.
[\n] (newLine)
Va sostituito con chr(10) altrimenti Oracle traduce \n come una stringa di due caratteri e non come una newLine.
Se una insert MySQL è :
INSERT INTO envo_blocks VALUES('2', 'style:=1/ndisplaymodules:=0/ncontent:=');
dove il secondo campo è un CLOB, in oracle deve essere tradotta come:
INSERT INTO envo_blocks VALUES('2', 'style:=1'|| chr(10)||'displaymodules:=0'|| chr(10)||'content:=');
[&]
Va sostituito con chr(38) perchè in oracle indica una variabile e quindi (TOAD) chiede che ne venga inserito il valore tramite una finestra di popup.
Se una insert MySQL è del tipo :
INSERT INTO envo_blocks VALUES('16', ' Burt & Sons ');
dove il secondo campo è un CLOB, in oracle deve essere tradotta come:
INSERT INTO envo_blocks VALUES('16', ' Burt'|| chr(38)||'Sons ');
[\"]
In MySQL i doppi apici sono preceduti da uno '\' (backward slash) per sottolineare che non sono gli apici di fine striga.
In Oracle le stringhe sono delimitate da apici singoli, quindi non bisogna mettere uno '\' davanti ai doppi apici.
Esempio:
In MySQL ho:
INSERT INTO envo_module_vars VALUES('195', '/PNConfig', 'storyorder', 's:1:\"0\";');
In Oracle questo va tradotto come:
INSERT INTO envo_module_vars VALUES('195', '/PNConfig', 'storyorder', 's:1:"0";');
SUGGERIMENTO
Se non si conosce il codice asci del carattere speciale basta fare una insert del tipo
INSERT INTO tabella VALUES('', ascii('carattere'));
poi vado a leggere nella tabella il numero che oracle ha inserito al posto di ascii('carattere').
VALORI NEI BLOB
Oracle si aspetta che in un BLOB venga inserito un valore esadecimale.
Se una insert MySQL è :
INSERT INTO envo_session_info VALUES('85', 'PNSVrand|i:238842564;PNSVlang|s:3:"eng";');
dove il secondo campo è un BLOB, in oracle bisogna tradurre la riga come:
INSERT INTO envo_session_info VALUES('85', empty_blob());
e poi inserire il valore del BLOB, in un secondo tempo, usando meccanismi opportuni (ad esempio la funzione UpdateBlob() della libreria oci8)
ATTENZIONE oracle non esegue funzioni come COUNT su campi LOB.
[9]
funzione GenID(nome tabella)
IMPORTANTE se associata alle funzioni UpdateCLOB/UpdateBLOB
Questa funzione restituisce la chiave più grande della tabella che gli si passa
In Oracle bisogna passargli il nome della sequence che agisce su quella tabella e non il nome della tabella stessa.
Nelle insert su tabelle Oracle in cui si inserisce un LOB vuoto e non si fa la UpdateBLO ()/UpdateCLOB la funzione genID() non serve. Questo perchè il numero dell'id viene creato dalla sequence (il numero passato dalla genID sarà sempre 0 e viene ignorato)
Con una base date MySQl si fa:
[mysql]
$new_sid = $dbconn->GenId($pntable['stories']);
$insert = $dbconn->Execute("INSERT INTO $pntable[stories] ($scol[sid], $scol[cid], $scol[aid], $scol[title],
$scol[hometext], $scol[alanguage] )
VALUES ($new_sid, '$catid', '$aid', '$title', '$hometext', '$alanguage')");
dove hometext è un CLOB
In Oracle avremo:
list($dbnam,$sequence)=explode("_", $pntable['message']);
$sequence=$sequence.'_sq';
$new_sid = $dbconn->GenId($sequence);
$query1="INSERT INTO $pntable[stories] ($scol[sid], $scol[cid], $scol[aid], $scol[title],
$scol[hometext], $scol[alanguage] )
VALUES ($new_sid, '$catid', '$aid', '$title', empty_clob(), '$alanguage')";
$insert = $dbconn->Execute($query1);
$dbconn->updateClob($pntable[stories],$scol[hometext],$hometext,$scol[sid].'='.$new_sid);
In questo esempio il new_sid creato viene usato nella condizione WHERE della funzione updateClob.
$pntable['message'] è il nome della tabella che per convenzione è nomeDB_nomeTabella, perciò gli si applica la funzione explode().
[10]
Con un database Oracle assumeremo le seguenti convenzioni:
nomi delle sequence: nomeTabella_sq
nomi dei trigger: nomeTabella_tr
dove nomeTabella è il nome della tabella a cui ci si riferisce e non contiene il nome del database come prefisso.
Esempio:
avremo : members_sq
e non : envoDB_members_sq
N.B. I nomi delle tabelle, delle sequence e dei trigger in Oracle sono lunghi al massimo 30 caratteri
ATTENZIONE
La variabile $sequence creata è valida se al str_replace viene passata una stringa del tipo nomeTabella.nomeColonnaChiavePrimaria
[11] DATE
Le date di oracle hanno un formato diverso da quelle di Mysql.
Perciò quando si crea il database oracle bisogna sempre mettere la riga:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
che specifica il formato delle date.
Tutte le insert MySQl del tipo:
INSERT INTO envo_blocks VALUES('20000101000100');
andranno convertite in oracle come:
INSERT INTO envo_blocks VALUES('2000-01-01 00:01:00');
[12]
Ore e giorni
Le insert che inseriscono la data attuale in MySQL usano la funzione now().
In Oracle questa funzione va sostituita con 'sysdate'
APPUNTI per avere un codice più veloce
(1) APICI DOPPI O SINGOLI??
Il motore del php parsifica tutto ciò che trova all'interno dei doppi apici, quindi:
echo 'This is my var:', $var, '!!';
è molto meglio di
echo "This is my var: $var !!";
Questo discorso vale ovunque, nelle funzioni, negli echo, nelle stringhe etc
SetCookie('name', 'Bill');
è non:
SetCookie("name", "Bill");
(2)
Inizializzare sempre tutte le variabili e comunque usarne il meno possibile.
Invece di:
$a='pippo';
echo($a);
è molto meglio:
echo('pippo');
NB Una variabile locale è 2 volte più veloce di una globale.
(3)
STR_REPLACE() è più veloce di PREG_REPLACE().
(4)
echo () è più veloce di print() che è più veloce di printf()
(5)
Un ciclo while è preferibile ad un ciclo for.
