Esercitazione su Ruby / DBI

Questo capitolo ti insegna come accedere a un database usando Ruby. Il modulo Ruby DBI fornisce un'interfaccia indipendente dal database per gli script Ruby simile a quella del modulo Perl DBI.

DBI sta per Database Independent Interface per Ruby, il che significa che DBI fornisce un livello di astrazione tra il codice Ruby e il database sottostante, consentendo di cambiare le implementazioni del database molto facilmente. Definisce un insieme di metodi, variabili e convenzioni che forniscono un'interfaccia di database coerente, indipendentemente dal database effettivamente utilizzato.

DBI può interfacciarsi con quanto segue:

ADO (ActiveX Data Objects)
DB2
Frontbase
mSQL
MySQL
ODBC
Oracle
OCI8 (Oracle)
PostgreSQL
Proxy/Server
SQLite
SQLRelay

Architettura di un'applicazione DBI

DBI è indipendente da qualsiasi database disponibile nel backend. È possibile utilizzare DBI sia che si lavori con Oracle, MySQL o Informix, ecc. Questo è chiaro dal seguente diagramma dell'architettura.

L'architettura generale per Ruby DBI utilizza due livelli:

Il livello dell'interfaccia database (DBI). Questo livello è indipendente dal database e fornisce una serie di metodi di accesso comuni che vengono utilizzati allo stesso modo indipendentemente dal tipo di server di database con cui si sta comunicando.
Il livello del driver del database (DBD). Questo livello dipende dal database; driver diversi forniscono l'accesso a diversi motori di database. C'è un driver per MySQL, un altro per PostgreSQL, un altro per InterBase, un altro per Oracle e così via. Ogni driver interpreta le richieste dal livello DBI e le mappa su richieste appropriate per un dato tipo di server database.

Prerequisiti

Se vuoi scrivere script Ruby per accedere ai database MySQL, devi avere installato il modulo Ruby MySQL.

Questo modulo funge da DBD come spiegato sopra e può essere scaricato da https://www.tmtm.org/en/mysql/ruby/

Ottenere e installare Ruby / DBI

È possibile scaricare e installare il modulo Ruby DBI dal seguente percorso:

https://imgur.com/NFEuWe4/embed

Prima di iniziare questa installazione assicurati di avere i privilegi di root. Ora, segui i passaggi indicati di seguito:

Passo 1

$ tar zxf dbi-0.2.0.tar.gz

Passo 2

Vai nella directory di distribuzione dbi-0.2.0 e configuralo usando lo script setup.rb in quella directory. Il comando di configurazione più generale è simile a questo, senza argomenti che seguono l'argomento config. Questo comando configura la distribuzione per installare tutti i driver per impostazione predefinita.

$ ruby setup.rb config

Per essere più specifici, fornisci un'opzione --with che elenca le parti particolari della distribuzione che vuoi usare. Ad esempio, per configurare solo il modulo DBI principale e il driver di livello DBD MySQL, emettere il seguente comando:

$ ruby setup.rb config --with = dbi,dbd_mysql

Passaggio 3

Il passaggio finale è creare il driver e installarlo utilizzando i seguenti comandi:

$ ruby setup.rb setup
$ ruby setup.rb install

Connessione al database

Supponendo che lavoreremo con il database MySQL, prima di connetterci a un database assicurati di quanto segue:

Hai creato un database TESTDB.
Hai creato EMPLOYEE in TESTDB.
Questa tabella contiene i campi FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.
L'ID utente "testuser" e la password "test123" sono impostati per accedere a TESTDB.
Ruby Module DBI è installato correttamente sulla tua macchina.
Hai seguito il tutorial su MySQL per comprendere le basi di MySQL.

Di seguito è riportato l'esempio di connessione con il database MySQL "TESTDB"

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   # get server version string and display it
   row = dbh.select_one("SELECT VERSION()")
   puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Durante l'esecuzione di questo script, produce il seguente risultato sulla nostra macchina Linux.

Server version: 5.0.45

Se viene stabilita una connessione con l'origine dati, viene restituito un handle di database e salvato in dbh per un ulteriore utilizzo in caso contrario dbhè impostato su un valore nullo e e.err ed e :: errstr restituiscono rispettivamente il codice di errore e una stringa di errore.

Infine, prima di uscire, assicurarsi che la connessione al database sia chiusa e che le risorse siano rilasciate.

Operazione INSERT

L'operazione INSERT è richiesta quando si desidera creare i record in una tabella di database.

Una volta stabilita una connessione al database, siamo pronti per creare tabelle o record nelle tabelle del database utilizzando do metodo o prepare e execute metodo.

Utilizzando do Statement

Le istruzioni che non restituiscono righe possono essere emesse richiamando il dometodo di gestione del database. Questo metodo accetta un argomento stringa di istruzione e restituisce un conteggio del numero di righe interessate dall'istruzione.

dbh.do("DROP TABLE IF EXISTS EMPLOYEE")
dbh.do("CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )" );

Allo stesso modo, è possibile eseguire l' istruzione SQL INSERT per creare un record nella tabella EMPLOYEE.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
      VALUES ('Mac', 'Mohan', 20, 'M', 2000)" )
   puts "Record has been created"
   dbh.commit
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Usare prepara ed esegui

È possibile utilizzare i metodi di preparazione ed esecuzione della classe DBI per eseguire l'istruzione SQL tramite il codice Ruby.

La creazione di record richiede i seguenti passaggi:

Preparazione dell'istruzione SQL con l'istruzione INSERT. Questo sarà fatto usando ilprepare metodo.
Esecuzione di query SQL per selezionare tutti i risultati dal database. Questo sarà fatto usando ilexecute metodo.
Rilascio dell'handle di istruzione. Questo sarà fatto usandofinish API
Se tutto va bene, allora commit questa operazione altrimenti puoi rollback la transazione completa.

Di seguito è riportata la sintassi per utilizzare questi due metodi:

sth = dbh.prepare(statement)
sth.execute
   ... zero or more SQL operations ...
sth.finish

Questi due metodi possono essere utilizzati per passare bindvalori alle istruzioni SQL. Potrebbe esserci un caso in cui i valori da inserire non vengono forniti in anticipo. In tal caso, vengono utilizzati i valori di associazione. Un punto interrogativo (?) viene utilizzato al posto dei valori effettivi e quindi i valori effettivi vengono passati tramite l'API execute ().

Di seguito è riportato l'esempio per creare due record nella tabella EMPLOYEE -

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
      VALUES (?, ?, ?, ?, ?)" )
   sth.execute('John', 'Poul', 25, 'M', 2300)
   sth.execute('Zara', 'Ali', 17, 'F', 1000)
   sth.finish
   dbh.commit
   puts "Record has been created"
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Se sono presenti più INSERT contemporaneamente, preparare prima un'istruzione e quindi eseguirla più volte all'interno di un ciclo è più efficiente rispetto a invocare ogni volta attraverso il ciclo.

Operazione READ

READ Operare su qualsiasi database significa prelevare alcune informazioni utili dal database.

Una volta stabilita la connessione al database, siamo pronti per eseguire una query in questo database. Possiamo usare entrambido metodo o prepare e execute metodi per recuperare i valori da una tabella di database.

Il recupero dei record richiede i seguenti passaggi:

Preparazione di query SQL in base alle condizioni richieste. Questo sarà fatto usando ilprepare metodo.
Esecuzione di query SQL per selezionare tutti i risultati dal database. Questo sarà fatto usando ilexecute metodo.
Recuperare tutti i risultati uno per uno e stamparli. Questo sarà fatto usando ilfetch metodo.
Rilascio dell'handle di istruzione. Questo sarà fatto usando ilfinish metodo.

Di seguito è riportata la procedura per interrogare tutti i record dalla tabella DIPENDENTE con stipendio superiore a 1000.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare("SELECT * FROM EMPLOYEE WHERE INCOME > ?")
   sth.execute(1000)

   sth.fetch do |row|
   printf "First Name: %s, Last Name : %s\n", row[0], row[1]
   printf "Age: %d, Sex : %s\n", row[2], row[3]
   printf "Salary :%d \n\n", row[4]
end
   sth.finish
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Questo produrrà il seguente risultato:

First Name: Mac, Last Name : Mohan
Age: 20, Sex : M
Salary :2000

First Name: John, Last Name : Poul
Age: 25, Sex : M
Salary :2300

Esistono più metodi di scelta rapida per recuperare i record dal database. Se sei interessato, passa alla sezione Recupero dei risultati, altrimenti passa alla sezione successiva.

Operazione di aggiornamento

AGGIORNAMENTO Operare su qualsiasi database significa aggiornare uno o più record, già disponibili nel database. Di seguito è riportata la procedura per aggiornare tutti i record che hanno SEX come "M". Qui aumenteremo l'ETÀ di tutti i maschi di un anno. Questo richiederà tre passaggi:

Preparazione di query SQL in base alle condizioni richieste. Questo sarà fatto usando ilprepare metodo.
Esecuzione di query SQL per selezionare tutti i risultati dal database. Questo sarà fatto usando ilexecute metodo.
Rilascio dell'handle di istruzione. Questo sarà fatto usando ilfinish metodo.
Se tutto va bene allora commit questa operazione altrimenti puoi rollback la transazione completa.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ?")
   sth.execute('M')
   sth.finish
   dbh.commit
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Operazione DELETE

L'operazione DELETE è necessaria quando si desidera eliminare alcuni record dal database. Di seguito è riportata la procedura per eliminare tutti i record da DIPENDENTE di età superiore a 20. Questa operazione richiederà i passaggi seguenti.

Preparazione di query SQL in base alle condizioni richieste. Questo sarà fatto usando ilprepare metodo.
Esecuzione di una query SQL per eliminare i record richiesti dal database. Questo sarà fatto usando ilexecute metodo.
Rilascio dell'handle di istruzione. Questo sarà fatto usando ilfinish metodo.
Se tutto va bene allora commit questa operazione altrimenti puoi rollback la transazione completa.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare("DELETE FROM EMPLOYEE WHERE AGE > ?")
   sth.execute(20)
   sth.finish
   dbh.commit
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Esecuzione di transazioni

Le transazioni sono un meccanismo che garantisce la coerenza dei dati. Le transazioni dovrebbero avere le seguenti quattro proprietà:

Atomicity - O una transazione viene completata o non accade nulla.
Consistency - Una transazione deve iniziare in uno stato coerente e lasciare il sistema in uno stato coerente.
Isolation - I risultati intermedi di una transazione non sono visibili al di fuori della transazione corrente.
Durability - Una volta che una transazione è stata confermata, gli effetti sono persistenti, anche dopo un errore di sistema.

Il DBI fornisce due metodi per eseguire il commit o il rollback di una transazione. C'è un altro metodo chiamato transazione che può essere utilizzato per implementare le transazioni. Esistono due semplici approcci per implementare le transazioni:

Approccio I

Il primo approccio utilizza i metodi di commit e rollback di DBI per eseguire il commit o annullare esplicitamente la transazione -

dbh['AutoCommit'] = false # Set auto commit to false.
begin
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'")
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'")
   dbh.commit
rescue
   puts "transaction failed"
   dbh.rollback
end
dbh['AutoCommit'] = true

Approccio II

Il secondo approccio utilizza il metodo della transazione . Questo è più semplice, perché richiede un blocco di codice contenente le istruzioni che compongono la transazione. Il metodo di transazione esegue il blocco, quindi richiama automaticamente il commit o il rollback , a seconda che il blocco abbia successo o meno -

dbh['AutoCommit'] = false # Set auto commit to false.
dbh.transaction do |dbh|
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'")
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'")
end
dbh['AutoCommit'] = true

Operazione COMMIT

Commit è l'operazione, che dà un segnale verde al database per finalizzare le modifiche e, dopo questa operazione, nessuna modifica può essere ripristinata.

Ecco un semplice esempio per chiamare il file commit metodo.

dbh.commit

Operazione ROLLBACK

Se non sei soddisfatto di una o più modifiche e desideri ripristinarle completamente, utilizza il rollback metodo.

Ecco un semplice esempio per chiamare il file rollback metodo.

dbh.rollback

Disconnessione del database

Per disconnettere la connessione al database, utilizzare l'API disconnect.

dbh.disconnect

Se la connessione a un database viene chiusa dall'utente con il metodo di disconnessione, tutte le transazioni in sospeso vengono annullate dal DBI. Tuttavia, invece di dipendere da uno qualsiasi dei dettagli di implementazione di DBI, l'applicazione farebbe meglio a chiamare esplicitamente il commit o il rollback.

Gestione degli errori

Esistono molte fonti di errore. Alcuni esempi sono un errore di sintassi in un'istruzione SQL eseguita, un errore di connessione o la chiamata del metodo fetch per un handle di istruzione già annullato o finito.

Se un metodo DBI fallisce, DBI solleva un'eccezione. I metodi DBI possono sollevare uno qualsiasi dei diversi tipi di eccezione, ma le due classi di eccezione più importanti sono DBI :: InterfaceError e DBI :: DatabaseError .

Gli oggetti eccezione di queste classi hanno tre attributi denominati err , errstr e state , che rappresentano il numero di errore, una stringa di errore descrittiva e un codice di errore standard. Gli attributi sono spiegati di seguito:

err- Restituisce una rappresentazione intera dell'errore verificato o nullo se non è supportato dal DBD. Oracle DBD, ad esempio, restituisce la parte numerica di un messaggio di errore ORA-XXXX .
errstr - Restituisce una rappresentazione di stringa dell'errore verificato.
state- Restituisce il codice SQLSTATE dell'errore che si è verificato. SQLSTATE è una stringa di cinque caratteri. La maggior parte dei DBD non lo supporta e restituisce invece zero.

Hai visto il seguente codice sopra nella maggior parte degli esempi:

rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Per ottenere informazioni di debug su ciò che lo script sta facendo durante l'esecuzione, è possibile abilitare la traccia. Per fare ciò, devi prima caricare il modulo dbi / trace e poi chiamare il metodo trace che controlla la modalità trace e la destinazione di output -

require "dbi/trace"
..............

trace(mode, destination)

Il valore della modalità può essere 0 (disattivato), 1, 2 o 3 e la destinazione dovrebbe essere un oggetto IO. I valori predefiniti sono rispettivamente 2 e STDERR.

Blocchi di codice con metodi

Esistono alcuni metodi che creano gli handle. Questi metodi possono essere richiamati con un blocco di codice. Il vantaggio dell'utilizzo del blocco di codice insieme ai metodi è che forniscono l'handle al blocco di codice come parametro e puliscono automaticamente l'handle quando il blocco termina. Ci sono pochi esempi per comprendere il concetto.

DBI.connect- Questo metodo genera un handle di database e si consiglia di chiamare disconnect alla fine del blocco per disconnettere il database.
dbh.prepare- Questo metodo genera un handle di istruzione e si consiglia di terminare alla fine del blocco. All'interno del blocco, è necessario richiamare eseguire il metodo per eseguire l'istruzione.
dbh.execute- Questo metodo è simile, tranne per il fatto che non è necessario invocare l'esecuzione all'interno del blocco. L'handle dell'istruzione viene eseguito automaticamente.

Esempio 1

DBI.connect può prendere un blocco di codice, passargli l'handle del database e disconnettere automaticamente l'handle alla fine del blocco come segue.

dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") do |dbh|

Esempio 2

dbh.prepare può prendere un blocco di codice, passargli l'handle dell'istruzione e chiamare automaticamente finish alla fine del blocco come segue.

dbh.prepare("SHOW DATABASES") do |sth|
   sth.execute
   puts "Databases: " + sth.fetch_all.join(", ")
end

Esempio 3

dbh.execute può prendere un blocco di codice, passargli l'handle dell'istruzione e chiamare automaticamente finish alla fine del blocco come segue:

dbh.execute("SHOW DATABASES") do |sth|
   puts "Databases: " + sth.fetch_all.join(", ")
end

Il metodo di transazione DBI accetta anche un blocco di codice che è stato descritto sopra.

Funzioni e attributi specifici del driver

Il DBI consente ai driver del database di fornire ulteriori funzioni specifiche del database, che possono essere chiamate dall'utente tramite il metodo func di qualsiasi oggetto Handle.

Gli attributi specifici del driver sono supportati e possono essere impostati o ottenuti utilizzando il []= o [] metodi.

DBD :: Mysql implementa le seguenti funzioni specifiche del driver:

Sr.No.	Funzioni e descrizione
1	dbh.func(:createdb, db_name) Crea un nuovo database.
2	dbh.func(:dropdb, db_name) Elimina un database.
3	dbh.func(:reload) Esegue un'operazione di ricarica.
4	dbh.func(:shutdown) Arresta il server.
5	dbh.func(:insert_id) => Fixnum Restituisce il valore AUTO_INCREMENT più recente per una connessione.
6	dbh.func(:client_info) => String Restituisce le informazioni sul client MySQL in termini di versione.
7	dbh.func(:client_version) => Fixnum Restituisce le informazioni sul cliente in termini di versione. È simile a: client_info ma restituisce un fixnum invece di sting.
8	dbh.func(:host_info) => String Restituisce le informazioni sull'host.
9	dbh.func(:proto_info) => Fixnum Restituisce il protocollo utilizzato per la comunicazione.
10	dbh.func(:server_info) => String Restituisce le informazioni sul server MySQL in termini di versione.
11	dbh.func(:stat) => String Restituisce lo stato corrente del database.
12	dbh.func(:thread_id) => Fixnum Restituisce l'ID del thread corrente.

Esempio

#!/usr/bin/ruby

require "dbi"
begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") 
   puts dbh.func(:client_info)
   puts dbh.func(:client_version)
   puts dbh.func(:host_info)
   puts dbh.func(:proto_info)
   puts dbh.func(:server_info)
   puts dbh.func(:thread_id)
   puts dbh.func(:stat)
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   dbh.disconnect if dbh
end

Questo produrrà il seguente risultato:

5.0.45
50045
Localhost via UNIX socket
10
5.0.45
150621
Uptime: 384981  Threads: 1  Questions: 1101078  Slow queries: 4 \
Opens: 324  Flush tables: 1  Open tables: 64  \
Queries per second avg: 2.860