Postgresql e Visual Studio 2005

Premessa
Dopo l’esperienza con MySql ho voluto rifare la stessa esperienza con Postgresql
E’ giunto ora alla release 8.2.4, circoscritto fino a qualche release fa ad ambienti Unix/Linux è sbarcato da qualche tempo anche su Windows in modo nativo.

Per chi non lo conoscesse, Postgresql è un database estremamente robusto e versatile, nato inizialmente con il nome Postgres da Post (dopo, successivo a) + gres, per ricordare le sue origini da Ingres, database commerciale per Unix abbastanza noto 20 anni fa), il termine Sql è stato aggiunto successivamente all’adesione allo standard SQL.

Licenze di Postgresql
La licenza che accompagna Postgresql è la BSD, nata originariamente come “Berkeley Software Distribution” sul leggendario PDP11 che tanto ha dato (unixcvi) alla storia dell’informatica.
La licenza BSD non pone limitazioni all’uso di quanto “sotto licenza BSD” anche in campo commerciale, ma impone di citare sempre l’autore (non mi sembra una grande limitazione, ma tant’è che alcuni grandi produttori di hw/sw hanno pensato bene di togliere i riferimenti alla licenza e all’autore violando in questo modo la licenza!).

Configurazione hardware e software
A disposizione per la mia prova:

  • Linux Fedora Core 6 installato su assemblato equipaggiato con un processore Intel Pentium4 2.26 gHz 2GB ram
  • Database Postgresql versione 8.1.8 installato su Linux
  • Windows Xp Sp2 installato su Acer Travelmate 8100
  • Visual Studio 2005 Team Edition for Software Developers
  • Npgsql, driver nativo di Postgresql per .NET
  • PgAdmin III per Windows

Installazione e configurazione di Postgresql
Ho provveduto ad installare Postgresql per linux dal cd di installazione e l’ho successivamente aggiornato con Yum.
Finita l’installazione sono entrato con l’utente di default “postgres” partendo dalla shell di root.

[root@lucy share]# psql -U postgres
Welcome to psql 8.1.8, the PostgreSQL interactive terminal. 

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

Verificando che il tutto funzioni, ho creato un nuovo database “dbprova” a cui ho concesso i permessi.
Su Linux inoltre è necessario impostare il file /var/lib/pgsql/data/pg_hba.conf
(ovviamente /var/lib/pgsql/data è la cartella dove è posizionato il mio database)

# IPv4 local connections:
# inizio aggiunte 
hostnossl all all 127.0.0.1/32 ident sameuser
hostnossl all all 192.168.1.1/32 trust sameuser
hostnossl all all 192.168.1.2/32 trust sameuser
hostnossl all all 192.168.1.3/32 trust sameuser
hostnossl all all 192.168.1.4/32 trust sameuser
#fine aggiunte

Installazione di Npgsql
E’ disponibile un driver ADO.NET managed “npgsql“, sia per il Framework 1.1 che per il Framework 2.0 nelle versioni “Windows” e “Mono” (applicazioni su linux con o senza la GUI di Microsoft), come per tutti i “prodotti” a licenza GNU sono disponibili anche i sorgenti.

Il driver .NET si installa “scompattandolo” nella cartella; essendo già dotato di uno “strong name”, è installabile nella GAC tramite Gacutil.exe.
Tra le varie cose che accompagnano Npgsql troviamo anche un comodissimo help in formato html, la cui lettura credo sia indispensabile per poter affrontare e risolvere velocemente i problemi di configurazione e collegamento ai dati.
Personalmente, avendo scelto la versione “beta”, che verrà al più presto rimpiazzata con un’altra beta o con una release, ho preferito non installare la libreria nella GAC, ma referenziarla all’interno del mio esperimento.

Installazione di PgAdmin III
PgAdmin III è un manager per il database Postgresql che consente di mantenere il server di Postgresql in tutti i suoi aspetti, dalla definizione ad esempio di campi e tabelle, a triggers e stored procedure, alla manutenzione del database con salvataggi e ripristini, allle query interattive.
L’installazione avviene semplicemente scaricando e facendo partire nel modo più classico il modulo di installazione.
Parlando di manager, ne esiste un secondo con interfaccia Web, che però non ho mai installato né provato.

Con pgAdmin III ho generato la tabella tableprova.
Questo lo script di generazione:

-- Table: tableprova
-- DROP TABLE tableprova;
CREATE TABLE tableprova
(
id serial NOT NULL,
descrizione character varying(50),
valore numeric(10,3)
) 
WITH OIDS;
ALTER TABLE tableprova OWNER TO postgres;

Come si può notare il campo id è stato definito come serial, di conseguenza il database ha automaticamente generato una nuova sequenza, che esplicito, nel caso non venga creata automaticamente:

-- Sequence: tableprova_id_seq
-- DROP SEQUENCE tableprova_id_seq;
CREATE SEQUENCE tableprova_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 17
CACHE 1;
ALTER TABLE tableprova_id_seq OWNER TO postgres;

Lato Visual Studio 2005
Non esiste un addon per Visual Studio 2005. Quindi è esclusa la possibilità di generare un TableAdapter con il Wizard partendo da una connessione con Postgresql.
Con Visual Express 2005 è impossibile utilizzare il wizard con database diversi da Access e/o Sql Express in locale.
Ho scelto pertanto di generare un database Access “locale” cui ho inserito la tabella che serviva per il mio esperimento.

Fatto questo primo passo ho provveduto a generare un nuovo progetto Windows Forms.

Ho allegato il database (aggiunta nuovo elemento esistente), il wizard successivamente ha generato un dataset tipizzato, ed ha aggiunto “app.config” con la relativa stringa di connessione.

Sulla toolbar mi sono ritrovato il tableadapter, ho selezionato e trascinato entrambi sulla form.

Adesso inizia la parte più difficile!.
Ovviamente i riferimenti generati dal wizard su Access vanno cambiati nei punti “giusti”, in modo tale che il tutto funzioni senza problemi.
E’ vietatissimo rigenerare di nuovo il dataset. Nel caso, farsi una copia del progetto, copia che verrà utilizzata per fare i cambiamenti con il wizard in sicurezza.

Ho tolto il database di Access dal progetto, non servirà oltre, almeno per me è stato così.

Ho inserito Npgsql tra i riferimenti del progetto.

Ho modificato il file “app.config“, sostituendo la stringa di connessione per access con quella per Postgresql, che nel mio scenario è: “Server=lucy;Port=5432;User Id=postgres;Trusted=Yes;Database=dbprova“.
(Nota: lucy è il mio server Linux in onore della Lucy dei mitici “Peanuts“)
Nella documentazione di Npgsql sono documentati tutti i parametri necessari per la configurazione.

Poi ho aperto il modulo dbprovaDataSet.Designer e ho cambiato tutti i riferimenti a System.Data.OleDb con i riferimenti a Npgsql e in particolar modo NpgsqlConnection al posto diOleDbConnectionNpgsqlDataAdapter al posto di OleDbDataAdapterNpgsqlCommand al posto di OleDbCommand.

Inoltre ho provveduto a riscrivere e/o modificare tutto il codice di definizione dei Command (UpdateCommandInsertCommandSelectCommand e DeleteCommand) necessari affinchè il tutto funzioni.

Questo è il codice relativo per VB.NET (come al solito il codice è indentato per ragioni tipografiche)

  Private Sub InitAdapter()
    Me._adapter = New Npgsql.NpgsqlDataAdapter
    Dim tableMapping As System.Data.Common.DataTableMapping = New System.Data.Common.DataTableMapping
    tableMapping.SourceTable = "Table"
    tableMapping.DataSetTable = "tableprova"
    tableMapping.ColumnMappings.Add("id", "id")
    tableMapping.ColumnMappings.Add("descrizione", "descrizione")
    tableMapping.ColumnMappings.Add("valore", "valore")
    Me._adapter.TableMappings.Add(tableMapping)
    Me._adapter.DeleteCommand = New Npgsql.NpgsqlCommand
    Me._adapter.DeleteCommand.Connection = Me.Connection
    Me._adapter.DeleteCommand.CommandText = "DELETE FROM tableprova WHERE (id = :Original_id)"
    Me._adapter.DeleteCommand.CommandType = System.Data.CommandType.Text
    Me._adapter.DeleteCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":Original_id", _
                                             NpgsqlTypes.NpgsqlDbType.[Integer], 0, "id", _
                                             System.Data.ParameterDirection.Input, False, _
                                             CByte(0), CByte(0), _
    System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.InsertCommand = New Npgsql.NpgsqlCommand
    Me._adapter.InsertCommand.Connection = Me.Connection
    Me._adapter.InsertCommand.CommandText = _
                    "INSERT INTO tableprova (descrizione, valore) VALUES (:descrizione, :valore)"
    Me._adapter.InsertCommand.CommandType = System.Data.CommandType.Text
    Me._adapter.InsertCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":descrizione", _
                                             NpgsqlTypes.NpgsqlDbType.Varchar, 50, "descrizione"))
    Me._adapter.InsertCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":valore", _
                                             NpgsqlTypes.NpgsqlDbType.[Double], 0, "valore"))
    Me._adapter.UpdateCommand = New Npgsql.NpgsqlCommand
    Me._adapter.UpdateCommand.Connection = Me.Connection
    Me._adapter.UpdateCommand.CommandText = _
      "UPDATE tableprova SET descrizione = :descrizione, valore = :valore WHERE id = :o riginal_id"
    Me._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text
    Me._adapter.UpdateCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":descrizione", _
                                             NpgsqlTypes.NpgsqlDbType.Varchar, 50, "descrizione"))
    Me._adapter.UpdateCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":valore", _
                                             NpgsqlTypes.NpgsqlDbType.[Double], 0, "valore"))
    Me._adapter.UpdateCommand.Parameters.Add(New Npgsql.NpgsqlParameter(":original_id", _
                                             NpgsqlTypes.NpgsqlDbType.[Integer], 0, "id", _
                                             System.Data.ParameterDirection.Input, False, _
                                             CByte(0), CByte(0), _
                                             System.Data.DataRowVersion.Original, Nothing))

  End Sub

Questo invece lo stesso codice per C#

    private void InitAdapter()
    {
      this._adapter = new Npgsql.NpgsqlDataAdapter();
      System.Data.Common.DataTableMapping tableMapping = new System.Data.Common.DataTableMapping();
      tableMapping.SourceTable = "Table";
      tableMapping.DataSetTable = "tableprova";
      tableMapping.ColumnMappings.Add("id", "id");
      tableMapping.ColumnMappings.Add("descrizione", "descrizione");
      tableMapping.ColumnMappings.Add("valore", "valore");
      this._adapter.TableMappings.Add(tableMapping);
      this._adapter.DeleteCommand = new Npgsql.NpgsqlCommand();
      this._adapter.DeleteCommand.Connection = this.Connection;
      this._adapter.DeleteCommand.CommandText = "DELETE FROM tableprova WHERE (id = :Original_id)";
      this._adapter.DeleteCommand.CommandType = System.Data.CommandType.Text;
      this._adapter.DeleteCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":Original_id", 
                                                 NpgsqlTypes.NpgsqlDbType.Integer, 0, "id", 
                                                 System.Data.ParameterDirection.Input, false, 
                                                 (byte)0, (byte)0, 
                                                 System.Data.DataRowVersion.Original, null));
      this._adapter.InsertCommand = new Npgsql.NpgsqlCommand();
      this._adapter.InsertCommand.Connection = this.Connection;
      this._adapter.InsertCommand.CommandText = 
                     "INSERT INTO tableprova (descrizione, valore) VALUES (:descrizione, :valore)";
      this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
      this._adapter.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":descrizione", 
                                                 NpgsqlTypes.NpgsqlDbType.Varchar, 0, "descrizione"));
      this._adapter.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":valore", 
                                                 NpgsqlTypes.NpgsqlDbType.Numeric, 0, "valore"));
      this._adapter.UpdateCommand = new Npgsql.NpgsqlCommand();
      this._adapter.UpdateCommand.Connection = this.Connection;
      this._adapter.UpdateCommand.CommandText = 
                                            "UPDATE tableprova SET descrizione = :descrizione, " + 
                                            "valore = :valore WHERE (id = :o riginal_id)";
      this._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
      this._adapter.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":descrizione", 
                                                 NpgsqlTypes.NpgsqlDbType.Varchar, 0, "descrizione"));
      this._adapter.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":valore", 
                                                 NpgsqlTypes.NpgsqlDbType.Numeric, 0, "valore"));
      this._adapter.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter(":original_id", 
                                                 NpgsqlTypes.NpgsqlDbType.Integer, 0, "id", 
                                                 System.Data.ParameterDirection.Input, false, 
                                                 (byte)0, (byte)0, 
                                                 System.Data.DataRowVersion.Original, null));

    }

Originariamente sono partito da questa definizione (solo C#, vi risparmio il blocco originale in Vb.Net).

    private void InitAdapter()
    {
      this._adapter = new System.Data.OleDb.OleDbDataAdapter();
      System.Data.Common.DataTableMapping tableMapping = new System.Data.Common.DataTableMapping();
      tableMapping.SourceTable = "Table";
      tableMapping.DataSetTable = "tableprova";
      tableMapping.ColumnMappings.Add("id", "id");
      tableMapping.ColumnMappings.Add("descrizione", "descrizione");
      tableMapping.ColumnMappings.Add("valore", "valore");
      this._adapter.TableMappings.Add(tableMapping);
      this._adapter.DeleteCommand = new System.Data.OleDb.OleDbCommand();
      this._adapter.DeleteCommand.Connection = this.Connection;
      this._adapter.DeleteCommand.CommandText = 
            "DELETE FROM `tableprova` WHERE ((`id` = ?) AND ((? = 1 AND `descrizione` IS NULL)" +
            " OR (`descrizione` = ?)) AND ((? = 1 AND `valore` IS NULL) OR (`valore` = ?)))";
      this._adapter.DeleteCommand.CommandType = System.Data.CommandType.Text;
      this._adapter.DeleteCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("Original_id", 
                                      System.Data.OleDb.OleDbType.Integer, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "id", 
                                      System.Data.DataRowVersion.Original, false, null));
      this._adapter.DeleteCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("IsNull_descrizione", 
                                      System.Data.OleDb.OleDbType.Integer, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "descrizione", 
                                      System.Data.DataRowVersion.Original, true, null));
      this._adapter.DeleteCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("Original_descrizione", 
                                      System.Data.OleDb.OleDbType.VarWChar, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "descrizione", 
                                      System.Data.DataRowVersion.Original, false, null));
      this._adapter.DeleteCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("IsNull_valore", 
                                      System.Data.OleDb.OleDbType.Integer, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "valore", 
                                      System.Data.DataRowVersion.Original, true, null));
      this._adapter.DeleteCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("Original_valore", 
                                      System.Data.OleDb.OleDbType.Numeric, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(10)), ((byte)(3)), "valore", 
                                      System.Data.DataRowVersion.Original, false, null));
      this._adapter.InsertCommand = new System.Data.OleDb.OleDbCommand();
      this._adapter.InsertCommand.Connection = this.Connection;
      this._adapter.InsertCommand.CommandText = 
                                "INSERT INTO `tableprova` (`descrizione`, `valore`) VALUES (?, ?)";
      this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
      this._adapter.InsertCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("descrizione", 
                                      System.Data.OleDb.OleDbType.VarWChar, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "descrizione", 
                                      System.Data.DataRowVersion.Current, false, null));
      this._adapter.InsertCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("valore", 
                                      System.Data.OleDb.OleDbType.Numeric, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(10)), ((byte)(3)), "valore", 
                                      System.Data.DataRowVersion.Current, false, null));
      this._adapter.UpdateCommand = new System.Data.OleDb.OleDbCommand();
      this._adapter.UpdateCommand.Connection = this.Connection;
      this._adapter.UpdateCommand.CommandText = 
            "UPDATE `tableprova` SET `descrizione` = ?, `valore` = ? WHERE ((`id` = ?) AND ((?" +
            " = 1 AND `descrizione` IS NULL) OR (`descrizione` = ?)) AND ((? = 1 AND `valore`" +
            " IS NULL) OR (`valore` = ?)))";
      this._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("descrizione", 
                                      System.Data.OleDb.OleDbType.VarWChar, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "descrizione", 
                                      System.Data.DataRowVersion.Current, false, null));
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("valore", 
                                      System.Data.OleDb.OleDbType.Numeric, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(10)), ((byte)(3)), "valore", 
                                      System.Data.DataRowVersion.Current, false, null));
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("Original_id", 
                                      System.Data.OleDb.OleDbType.Integer, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "id", 
                                      System.Data.DataRowVersion.Original, false, null));
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("IsNull_descrizione", 
                                      System.Data.OleDb.OleDbType.Integer, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "descrizione", 
                                      System.Data.DataRowVersion.Original, true, null));
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("Original_descrizione", 
                                      System.Data.OleDb.OleDbType.VarWChar, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "descrizione", 
                                      System.Data.DataRowVersion.Original, false, null));
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("IsNull_valore", 
                                      System.Data.OleDb.OleDbType.Integer, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(0)), ((byte)(0)), "valore", 
                                      System.Data.DataRowVersion.Original, true, null));
      this._adapter.UpdateCommand.Parameters.Add(
                                      new System.Data.OleDb.OleDbParameter("Original_valore", 
                                      System.Data.OleDb.OleDbType.Numeric, 0, 
                                      System.Data.ParameterDirection.Input, 
                                      ((byte)(10)), ((byte)(3)), "valore", 
                                      System.Data.DataRowVersion.Original, false, null));
    }

Come si può vedere il segnaposto per i parametri per NpgsqlParameter è :nomecampo, inoltre l’apice singolo che delimita campi e tabelle è mal digerito e segnalato come errore di run-time da Postgresql.

Aggiunti alcuni controlli per far stare in piedi la nostra struttura lato interfaccia utente, questo il risultato:

Recupero Id
Come già detto, il campo id (codice) definito nella tabella è di tipo “sequence“, questo fa sì che l’id ”vero” è attribuito dal database facendo scattare un “trigger” sul campo che richiama una funzione di incremento.
Attualmente, per il nostro lavoro ho utilizzato currval che recupera l’ultimo id attribuito, mentre nextval fa incrementare il contatore prima di restituire l’id.
La procedura per recuperare l’id prevede l’aggiunta di un evento “RowUpdated” e del relativo gestore:

    void Adapter_RowUpdated(object sender, NpgsqlRowUpdatedEventArgs e)
    {
      // testiamo se il tipo di operazione \'e8 inserimento (altrimenti avremmo dei problemi!!)
      if (e.StatementType == StatementType.Insert)
      {
        Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("SELECT currval('tableprova_id_seq')", 
                                                            provaTableAdapter.Connection);
        // recuperiamo il valore di ritorno da executescalar
        System.Int64 chiave = (Int64)cmd.ExecuteScalar();
        // aggiornamento dell'id "vero" attribuito dal database
        e.Row["id"] = chiave;
      }
    }
Both comments and pings are currently closed.

Comments are closed.