ASIX-M10-UF2. Drivers per accedir a una base de dades: MySQL i PostgreSQL. 2 casos

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Notes

L'empresa/organització, per motius tecnològics, ha de migrar a PostgresSQL. Veure com fem la migració de dades de MySQL a Postgres.

Farem dos casos.

En el primer cas tenim un script php que accedeix a una base de dades mysql. Veure com gràcies a la utiltizació dels drivers nadius, el codi PHP resta bàsicament intacte i podem accedir a la nova base de dades mysql-

Segon cas. Els operadors de la base de dades fan servir Squirel per accedir de forma gràfica a la base de dades. Veurem com el canvi de Mysql a PostgreSQL és transparent per l'usuari final. L'usuari final no té perquè ni saber que s'ha produït un canvi tecnològic.

Introducció

(TBD)

Migració de MySQL a PostgreSQL

Si volem convertir una base de dades MySQL a PostreSQL:

En la secció MySQL veiem que tenim no una opció, sinó vàries. Escollim la primera opció: MySQL/PostgreSQL Converter from Lanyrd:

NOTA: La migració de bases de dades entre diferents tecnologies no deixa de tenir un punt artesanal. En el procés de migrar la base de dades langtrainer el professor ha trobat tres tipus de problemes, que són:

  1. El camp TRANSLATION.probability no pot ser tinyint(4).
  2. hi ha un problema amb el camp word.day, que no pot ser en format català ('03/12/2014'). La migració amb el mysqldump es fa malament.
  3. el tercer problema, i més important, és que sembla ser que el postgres es fa un embolic amb els noms de taules en majúscules.

Per tot això, regenera de nou la base de dades langtrainer del mysql amb el següent script:

/*
login(id_login (PK), login, pwd, name, mail, default_lang_w, default_lang_t)
language(id_language (PK), language, abr)
word(id_word (PK), id_language(FK), id_login(FK), word, probability,day,day_learnt)
translation(id_translation (PK), id_language (FK), id_word (FK), translation, type, sample)
on tipus és : verb, adjective, phraseal verb
quiz(id_quiz,id_login,date_time,number_quest,result)
quiz_detail(id_quiz,id_quest,id_word,result)
STATS(id_login, date_time)
*/

DROP TABLE quiz_detail;
DROP TABLE quiz;
DROP TABLE translation;
DROP TABLE word;
DROP TABLE login;
DROP TABLE language;

CREATE TABLE language(
id_language smallint primary key,
language varchar(20) not null,
abr varchar(3) not null
);

CREATE TABLE login(
id_login smallint primary key,
login varchar(10) not null,
pwd varchar(50) not null,
name varchar(30) not null,
mail varchar(30),
default_lang_w smallint not null,
default_lang_t smallint not null
);

ALTER TABLE login ADD FOREIGN KEY(default_lang_w) REFERENCES language(id_language) ON DELETE CASCADE;
ALTER TABLE login ADD FOREIGN KEY(default_lang_t) REFERENCES language(id_language) ON DELETE CASCADE;

CREATE TABLE word(
id_word smallint primary key,
id_language smallint not null,
id_login smallint not null,
word varchar(25) not null,  
probability smallint not null default 100,
day date not null,
day_learnt date
);

ALTER TABLE word ADD FOREIGN KEY(id_language) REFERENCES language(id_language) ON DELETE CASCADE;
ALTER TABLE word ADD FOREIGN KEY(id_login) REFERENCES login(id_login) ON DELETE CASCADE;

CREATE TABLE translation(
id_translation smallint primary key, 
id_language smallint not null,
id_word smallint not null, 
translation varchar(255) not null,
type varchar(15),
sample tinytext
);
# noun, adjective, verb, adverb, preposition, phrasal verb, acronym

ALTER TABLE translation ADD FOREIGN KEY(id_language) REFERENCES language(id_language) ON DELETE CASCADE;
ALTER TABLE translation ADD FOREIGN KEY(id_word) REFERENCES word(id_word) ON DELETE CASCADE;

CREATE TABLE quiz(
id_quiz smallint primary key,
id_login smallint not null,
day datetime not null,
number_quest smallint,
result decimal(4,2)
);

ALTER TABLE quiz ADD FOREIGN KEY(id_login) REFERENCES login(id_login) ON DELETE CASCADE;

CREATE TABLE quiz_detail(
id_quiz smallint,
id_quest smallint not null,
id_word smallint not null,
result char(1),
PRIMARY KEY (id_quiz, id_quest)
);

ALTER TABLE quiz_detail ADD FOREIGN KEY(id_quiz) REFERENCES quiz(id_quiz) ON DELETE CASCADE;
ALTER TABLE quiz_detail ADD FOREIGN KEY(id_word) REFERENCES word(id_word) ON DELETE CASCADE;

INSERT INTO language(id_language,language, abr) VALUES(1,'English','ENG');
INSERT INTO language(id_language,language, abr) VALUES(2,'Català','CAT');

INSERT INTO login(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(1,'admin','a5302fbf1b07ed51d09716ba5907a179','Admin',1,2);


INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(1,1,1,'deserve (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(2,1,1,'weird',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(3,1,1,'gills',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(4,1,1,'draw breath (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(5,1,1,'draw (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(6,1,1,'snout',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(7,1,1,'wound',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(8,1,1,'dismiss (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(9,1,1,'shame',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(10,1,1,'hump',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(11,1,1,'lump',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(12,1,1,'spit',100,'2009/09/01');


INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(1,2,1,'merèixer, ser digne de','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(2,2,2,'rar, extrany','a',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(3,2,3,'branquies, agalles','n','who has either grown gills from spending 45 years underwater or learned to draw breath only once every few weeks: que, després de 45 anys sota l''aigua ha desenvolupat brànquies, o bé ha après a agafar aire només una vegada cada poques setmanes.');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(4,2,4,'agafar aire','v','who has either grown gills from spending 45 years underwater or learned to draw breath only once every few weeks: que, després de 45 anys sota l''aigua ha desenvolupat brànquies, o bé ha après a agafar aire només una vegada cada poques setmanes.');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(5,2,5,'dibuixar','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(6,2,5,'Estirar (pull), treure, atraure','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(7,2,6,'morro, hocico','n',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(8,2,7,'ferida','n',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(9,2,7,'ferir (pp, ferit)','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(10,2,8,'acomiadar, destituir. Refusar','v','the shark dismissed me as worthy of neither fearing nor eating: el tauró em va refusar de considerar-me ni per fer-li por ni per menjar-me');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(11,2,9,'vergonya, llàstima','n',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(12,2,10,'pujol, puig','n','4000 islands, islets, cays, humps, lumps, and spits');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(13,2,11,'terròs, tros','n','4000 islands, islets, cays, humps, lumps, and spits');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(14,2,12,'escupinada, saliva','n','4000 islands, islets, cays, humps, lumps, and spits');

S'han fet tres canvis: convertir el nom de les taules a minúscules; canviar el camp translation.probability a smallint; i un format anglès per al camp word.day.

Amb la nova base de dades mysql, ja podem fer ara la conversió. Bé, comencem:

Primer de tot fem una còpia de la base de dades, amb l'opció --compatible=postgresql:

$ mysqldump --compatible=postgresql --default-character-set=utf8 -r langtrainer.mysql -u root -p -v langtrainer

$ joe langtrainer.mysql

Mirem el contingut del fitxer langtrainer.mysql, i veiem que té la sintaxi de MySQL Executem el scrìpt python que fa la conversió entre formats de scripts:

$ python db_converter.py langtrainer.mysql langtrainer.psql
Line 200 (of 200: 100.00%) [6 tables] [6 inserts] [ETA: 0 min 0 sec]

$ joe langtrainer.psql

Abans de tot hem de crear la base de dades de Postgres langtrainer. Per fer-ho compatible amb el cas del MySQL, crearem l'usuari alumne/keiL2lai per accedir a aquesta base de dades.

$ su postgres
$ psql -h localhost -p 5432 -U postgres -d postgres
postgres=# CREATE DATABASE langtrainer WITH OWNER postgres ENCODING 'UTF8';
postgres=# CREATE USER alumne;
postgres=# ALTER USER alumne WITH ENCRYPTED PASSWORD 'keiL2lai';
postgres=# GRANT ALL PRIVILEGES ON DATABASE langtrainer TO alumne;
postgres=# \q

i ara ja podem entrar (però encara no hi ha cap taula)

$ psql -h localhost -p 5432 -U alumne -d langtrainer

Per importar la base de dades a partir del script, fem servir l'opció -f:

$ psql -h localhost -p 5432 -U alumne -d langtrainer -f langtrainer.psql

NOTA. Al final de l'execució has de veure el missatge de COMMIT, sense missatges d'error.

Ara ja tenim la base de dades importada correctament:

$ psql -h localhost -p 5432 -U alumne -d langtrainer
langtrainer=> select * from word;

Migració de PostgreSQL a MySQL

També és possible fer una migració de PostgreSQL a MySQL:

En aquest cas migraríem la base de dades project que tenim al PostgreSQL, al MySQL.

(TBD)

Cas 1. Script PHP

Formes nadiues

Per connectar-nos al mysql des de PHP necessitem el paquet:

Script langtrainer_mysql_nadiu.php:

<?php
$servername = "localhost";
$username = "alumne";
$password = "keiL2lai";
$dbname = "langtrainer";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "select w.id_word, word, translation from word w, translation t where w.id_word=t.id_word";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo $row["id_word"]. ". " . $row["word"]. ": " . $row["translation"]. "<br />";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?> 

Per connectar-nos al PostgreSQL des de PHP necessitem el paquet:

sudo apt-get install php5-pgsql (recorda de reiniciar l'Apache per tal de què la llibreria estigui disponible)

Script langtrainer_pgsql_nadiu.php:

<?php
$servername = "localhost";
$username = "alumne";
$password = "keiL2lai";
$dbname = "langtrainer";

$conn = pg_pconnect("host=$servername user=$username password=$password dbname=$dbname");

if (!$conn) {
  echo "An error occurred.\n";
  exit;
}

$result = pg_query($conn, "select w.id_word, word, translation from word w, translation t where w.id_word=t.id_word");
if (!$result) {
  echo "An error occurred.\n";
  exit;
}

while ($row = pg_fetch_row($result)) {
  echo "$row[0]. $row[1]: $row[2]";
  echo "<br />\n";
}
?>

Interfície PDO: PHP Data Objects

PDO (no cal instal.lar cap paquet, ja ve amb el nucli de PHP5) ofereix una capa intermitja entre PHP i l'accés a bases de dades. L'avantatge és que reutilitzar el codi és molt ràpd i fàcil. El petit inconvenient és que, com a capa intermitja que fiquem, en teoria l'accés a les dades és més lent.

Històricament sempre hi ha hagut capes intermitfges per accedir a les dades, de manera que els desenvolupadors no han d'estar pensant si es connecten a mysql, oracle, postgresql, Microsoft Access, etc. La tecnologia més coneguda era ODBC (Open Database Connectivity), amb una reguitzell molt gran de drivers. També existeix JDBC (Java database connectivity), amb un bon grapat de drivers jdbc.

Per tant, migrar de mysql a postgres és automàtic si les nostres aplicacions web s'han construït sobre PDO. Només cal canviar una línia, com veiem en els següents dos scriipts.

Script langtrainer_mysql_pdo.php:

<?php
$servername = "localhost";
$username = "alumne";
$password = "keiL2lai";
$dbname = "langtrainer";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("select w.id_word, word, translation from word w, translation t where w.id_word=t.id_word");
    $stmt->execute();
 
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['id_word'].'. '.$row['word'].': '.$row['translation'].'<br />';
    }

}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

?> 

I per convertir l'anterior codi a una connexió contra el Postgres, NOMÉS cal canviar una sola paraula: mysql per pgsql en la cadena de connexió:

Script langtrainer_pgsql_pdo.php:

<?php
$servername = "localhost";
$username = "alumne";
$password = "keiL2lai";
$dbname = "langtrainer";

try {
    $conn = new PDO("pgsql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("select w.id_word, word, translation from word w, translation t where w.id_word=t.id_word");
    $stmt->execute();
 
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['id_word'].'. '.$row['word'].': '.$row['translation'].'<br />';
    }

}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

?> 

Cas 2. SquirrelSQL Client

Instal.lació de Squirrel SQL Client

Squirrel (http://squirrel-sql.sourceforge.net/) és un client gràfic de SQL que existeix de fa temps, que està basat en JAVA. Existeixen diferents drivers de JAVA per accedir a diferents SGBD. Nosaltres configurarem Squirrel per accedir a bases de dades MySQL i PostgreSQl. Per fer-ho, necessitarem els corresponents drivers JDBC.

Referències:

El primer de tot serà instal.lar JAVA en la teva màquina si no ho tens fet.

Ja podem descarregar el Squirrel:

Descarreguem el fitxer java -jar squirrel-sql-3.7.1-standard.jar.

Assegura't de què tens la variable JAVA_HOME en el teu sistema. Això dependrà de com hagis fet la instal.lació de JAVA: (fica, si cal, les rutes que es corresponen a la teva màquina)

$ export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-amd64
$ export PATH="$PATH:$JAVA_HOME/bin" 

Comença el procés d'instal.lació:

$ java -jar squirrel-sql-3.7.1-standard.jar

Has d'escollir un directori d'instal.lació , per ex

Marcar MySQL i PostgreSQL, que són les bases de dades amb les quals treballaràs. (tanmateix, marcar-ho no significa que es descarreguin els drivers (?)).

Pots fer que es creï una drecera en el menú d'instal.lació, per tal d'arrencar Squirrel amb un sol click.

Un cop a dins del programa, podem anar a la pestanya Drivers. Anem a configurar el driver de mysql. La url té la forma:

jdbc:mysql://<hostname>[,<failoverhost>][<:3306>]/<dbname>[?<param1>=<value1>][&<param2>=<value2>]

identifica les parts.

Per instal.lar el driver de JAVA per a MySQL existeix un paquet:

libmysql-java - Java database (JDBC) driver for MySQL

Tanmateix, el descarregarem de la pàgina oficial dels desenvolupadors de MySQL:

tot i que ens demanen registrar-nos a Oracle (actualment MySQL és propietat d'Oracle). També el podem descarregar d'aquí:

I escollim una versió del driver, per ex,

Descomprimim, i a dins trobem el fitxer mysql-connector-java-5.1.34-bin.jar, aquest és el fitxer que necessitarem.

Anem al Squirel, a la pestanya drivers:

Si tot va bé, ens ha de sortir un check conforme que la connexió amb el driver és correcta.

Ara ja podem fer un àlies per connectar-nos a la nostra base de dades langtrainer.

I ara ja podem accedir a la base de dades, veure els objectes de què consta. També tens una pestanya SQL. Perd una mica de temps familiaritzan-te amb l'entorn.

Per a la instal.lació del driver JDBC de Postgres funciona de la mateixa manera, i s'instal.la sense problemes. Al Google cerquem per postgres jdbc driver, i la primera entrada ens porta a:

Hem de saber quina versió del Postgres tenim, i la versió de la JVM. Instal.lem JDBC4 Postgresql Driver, Version 9.3-1102. De fet no és una instal.lació, senzillament descarreguem a la nostra màquina el fitxer .jar.

En el Squirrel habilitem el driver JDBC de Postgres (pestanya Extra Class Path). Creem un nou àlies: langtrainer_postgres amb les dades de connexió pertinents a la base de dades langtrainer. I hauria de funcionar sense problemes igual que va funcionar la connexió a mysql.

En conclusió, hem de tenir dos àlies:


creat per Joan Quintana Compte, novembre 2014

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines