• Articolo

Il presente articolo è stato tradotto automaticamente.

Il programmatore al lavoro

Tutto su SQLite

Ted Neward

Scaricare il codice di esempio

Ted NewardMantenendo il tema di questo problema, è possibile restituire le radici di SQL e i database relazionali.Naturalmente, pertanto sembrerebbe apropos scrivere qualcosa su SQL Server, che fornisce informazioni sul relativo nuovi miglioramenti delle prestazioni o set di funzionalità o whatnot, ma semplicemente non è mio stile.Non visualizzare più errato, è un grande database di SQL Server e altamente consigliato per gli scenari aziendali “ grande ferro ” ma non ogni problema richiede (come un amico una volta inserirlo) un “ honkin grande ’ database centralizzato. ”

Infatti, gli sviluppatori lungo utilizza database relazionali semplicemente come un luogo “ inserire qualcosa di speciale per la prossima volta ” — roba come configurazione opzioni, impostazioni utente, i valori di internazionalizzazione e così via.È talvolta utile inserire in un'istanza di SQL Server centralizzata, in alcuni casi, in particolare in scenari rich client (e soprattutto gli scenari di rich client Microsoft Silverlight o telefono Windows 7), mantenendo una connessione costante all'istanza di SQL Server è non fattibile nel migliore dei casi e spesso flat-out semplicemente impossibili.

Gli sviluppatori non necessariamente desiderano abbandonare la potenza e la flessibilità di un database relazionale, ma anche SQL Server Express è talvolta troppo pesante un'installazione.Che cos'è necessario eseguire?

Passare la luce, ovviamente: SQLite, per la precisione.

Introduzione a SQLite

Come indicato dal relativo sito Web (sqlite.org) “ SQLite è una libreria software che implementa un motore di database SQL autonomo, senza server, configurazione zero, transazionale. ” Gli elementi chiave in tale istruzione imperniati sostantivo “ libreria ”. A differenza di SQL Server, che utilizza un assembly sul client di inviare richieste a un server per l'analisi e l'esecuzione, SQLite vive interamente all'interno del processo client, rendendo un database “ incorporato ”.Il footprint di esecuzione di un database SQLite durante l'utilizzo è un unico file stored someplace sul client file system e in genere è ugualmente small footprint dell'installazione.

Tutto questo, il database SQLite è molto ricco di funzionalità, che supporta la maggior parte della specifica SQL-92, meno alcune cose (descritti in dettaglio nel sito SQLite Web) come RIGHT e FULL OUTER JOIN, ALTER TABLE, alcuni trigger supporto, GRANT/REVOKE e la scrittura di viste.Cosa sorprendente è quanto è supportata, incluse le transazioni e una vasta gamma di tipi di dati.Sebbene sia probabile che oltre credibilità prevede che uno schema di database di SQL Server verrà porta a SQLite senza alcuna modifica, è ragionevole presupporre che un semplice (ovvero non usufruire delle funzioni o tipi specifici di SQL Server) dello schema verrà porta con problemi minimi.In questo modo ideale per gli scenari in cui è necessario solo un “ leggero SQL ” SQLite.

Nel caso in cui vi sono problemi sulla relativa applicabilità o stabilità, SQLite lentamente sta effettuando il modo in cui in una vasta gamma di ambienti “ leggeri ”, ovvero viene già visualizzato all'interno del browser Mozilla Firefox (per il supporto HTML 5), nonché gli ambienti Symbian, iOS e Android, tra gli altri.In altre parole, si tratta come il “ altra metà ” del mondo sviluppo (, non-­ basato su Microsoft) è il database leggero.SQLite continua a usufruire dello sviluppo e bug correzione, che effettua una scommessa abbastanza sicuro come un motore SQL minimo.

Naturalmente, database non sarebbe completato senza un tipo di interfaccia dell'amministratore e non disappoint SQLite.È uno strumento della riga di comando della console per l'accesso e la modifica dei database SQLite, ma che non può stupire che molto al ruolo sysadmin.Fortunatamente, la community open source include numerosi strumenti SQLite (un lungo elenco di essi è sul sito SQLite Web), ma se è necessario uno strumento simile a Analizzatore Query rapido, tenta SQLite Administrator, uno strumento gratuito per il download all'indirizzo sqliteadmin.orbmu2k.de .

Goin ’ native

SQLite è stato progettato per essere un database per gli sviluppatori di codice nativo, motivo per cui viene implementato come una DLL C/C ++ nativa dall'inizio.Questa caratteristica nativo di SQLite è un blessing e un'imprecazione: blessing, che Taglia fuori parecchio overhead (come quella di attraversare la rete nuovamente al server e viceversa) dal tempo totale necessario per eseguire un'istruzione SQL specificata; ma curse in quanto il database originale SQLite è una DLL C/C ++ nativo, ottenere da un'applicazione basata su .NET Framework Microsoft può essere complessa.

Fortunatamente, gli sviluppatori esperti di .NET Framework comprende l'accesso a una DLL nativa è semplicemente un esercizio nelle dichiarazioni P/Invoke che è relativamente semplice creare una classe wrapper attorno le dichiarazioni native esposte nella DLL SQLite.Infatti, per le nozioni di base, come con così tante cose nella community open source viene già stata eseguita; passare switchonthecode.com/tutorials/csharp-tutorial-writing-a-dotnet-wrapper-for-sqlite e trovare un working set di dichiarazioni P/Invoke già posizionati verso il basso, visualizzate in di Figura 1.

Figura 1 di dichiarazioni P/Invoke

namespace SQLiteWrapper
{
  public class SQLiteException : Exception
  {
    public SQLiteException(string message) :
      base(message)
      { }
  }

  public class SQLite
  {
    const int SQLITE_OK = 0;
    const int SQLITE_ROW = 100;
    const int SQLITE_DONE = 101;
    const int SQLITE_INTEGER = 1;
    const int SQLITE_FLOAT = 2;
    const int SQLITE_TEXT = 3;
    const int SQLITE_BLOB = 4;
    const int SQLITE_NULL = 5;

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
      static extern int sqlite3_open(string filename, out IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_close")]
      static extern int sqlite3_close(IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2")]
      static extern int sqlite3_prepare_v2(IntPtr db, string zSql,
        int nByte, out IntPtr ppStmpt, IntPtr pzTail);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_step")]
      static extern int sqlite3_step(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize")]
      static extern int sqlite3_finalize(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg")]
      static extern string sqlite3_errmsg(IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count")]
      static extern int sqlite3_column_count(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name")]
      static extern string sqlite3_column_origin_name(
        IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type")]
      static extern int sqlite3_column_type(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int")]
      static extern int sqlite3_column_int(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text")]
      static extern string sqlite3_column_text(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double")]
      static extern double sqlite3_column_double(IntPtr stmHandle, int iCol);
  }
}

Alta fedeltà di P/Invoke per C/C ++ API rende questo un processo relativamente semplice, ovvero l'API SQLite utilizza un puntatore raw per rappresentare il database stesso, che P/Invoke visualizzata come un System.IntPtr, e tanto spesso l'API SQLite utilizza un puntatore a un int come parametro in modo che è possibile modificare il contenuto, descritto mediante P/Invoke con C# “ out ” parola chiave.(Per ulteriori informazioni su P/Invoke, vedere pinvoke.codeplex.com ).

Farà riferimento al sito SQLite Web per la maggior parte delle informazioni dettagliate su come utilizzare l'API SQLite, ma una rapida panoramica della modalità aprire un database, eseguire una query e quindi chiudere il database conterrebbe è simile a di Figura 2.

Figura 2 apre un database, una query in esecuzione e la chiusura del database

static void NativeMain()
 {
   // Open the database--db is our "handle" to it
   IntPtr db;
   if (SQLiteNative.sqlite3_open(@"cities.sqlite", out db) 
     == SQLiteNative.SQLITE_OK)
     {
       // Prepare a simple DDL "CREATE TABLE" statement
       string query = 
         "CREATE TABLE City " + 
         "(name TEXT, state TEXT, population INTEGER)";
       IntPtr stmHandle;
       if (SQLiteNative.sqlite3_prepare_v2(db, query, query.Length,
         out stmHandle, IntPtr.Zero) != SQLiteNative.SQLITE_OK)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
       if (SQLiteNative.sqlite3_step(stmHandle) != 
         SQLiteNative.SQLITE_DONE)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
       if (SQLiteNative.sqlite3_finalize(stmHandle) != 
         SQLiteNative.SQLITE_OK)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }

     // ... Now that we've created a table, we can insert some
     // data, query it back and so on

     // Close the database back up
     SQLiteNative.sqlite3_close(db);
     }
  }

Sto Feelin ’ Mighty Low

La cosa più importante di questa API è che un po' è sul lato a Low livello.Se sei un vecchio hack di c ++ come me, questa potrebbe essere una buona cosa, noi fornendo un'eccellente opportunità per reminisce sui giorni precedenti valida, quando uomini erano uomini, memoria era gestita manualmente e donne swooned in parti cocktail sul nostro con tema horror storie di ricerca a puntatori untamed wilds di Windows 95 … ma per il resto di questi C# whipper-snappers, questi Giorgio-come-Latelys che effettivamente desiderano lavorare produttivi, è un po' troppo Low per il terreno così dire.Cosa è necessario è un wrapper di astrazione buona tale API per rendere più gestibile e tagliare verso il basso del numero di righe di codice necessarie per utilizzarlo.

Questa disposizione del in un'unica classe non che è difficile, soprattutto perché System.Data fornisce alcune classi buona in grado di gestire la maggior parte dell'interazione utente API.Mostrare i dettagli completi di tale classe wrapper, denominata SQLite, è un po' troppo lunga per includere qui ma dichiarazioni mostrate in di Figura 3 forniscono un'indicazione piuttosto chiara di come è previsto da utilizzare.

Figura 3 di dichiarazioni della classe wrapper SQLite

public class SQLite : IDisposable
  {
    private IntPtr _db; //pointer to SQLite database
    private bool _open; //whether or not the database is open

    /// <summary>
    /// Opens or creates SQLite database with the specified path
    /// </summary>
    /// <param name="path">Path to SQLite database</param>
    public void OpenDatabase(string path);

    /// <summary>
    /// Closes the SQLite database
    /// </summary>
    public void CloseDatabase();

    /// <summary>
    /// Executes a query that returns no results
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    public void ExecuteNonQuery(string query);

    /// <summary>
    /// Executes a query and stores the results in
    /// a DataTable
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    /// <returns>DataTable of results</returns>
    public DataTable ExecuteQuery(string query);
  }

Utilizzarlo, quindi, sarebbe simile nell'esempio in di Figura 4.

Figura 4 con la classe wrapper SQLite

static void NativeWrapperMain()
  {
    using (SQLite db = new SQLite("persons.sqlite"))
    {
      db.ExecuteNonQuery("CREATE Table Persons " +
        "(first TEXT, last TEXT, age INTEGER)");

      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Aaron', 'Erickson', 38)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Rick', 'Minerich', 29)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Talbott', 'Crowell', 35)");

      DataTable table = db.ExecuteQuery("SELECT * FROM Persons");

      foreach (DataRow row in table.Rows)
      {
        Console.WriteLine("{0} {1} {2}", row[0], row[1], row[2]);
      }
    }
  }

Ovviamente esistono ulteriori operazioni che è possibile aggiungere alla classe wrapper SQLite in di Figura 4, ma ha già la funzionalità necessaria barebones, grazie in parte alla natura meraviglioso indipendente dal database dei principali classi DataTable, DataRow/DataColumn System.Data.

Astrazioni astrazioni

Alcuni aspetti vantaggio del database SQLite è la progettazione di Low livello e l'implementazione, significa incorporabile “ semplice ” coinvolti in uso è abbastanza chiaro.Aggiungere le classi wrapper, assicurarsi che la DLL SQLite è in una posizione accessibile al programma (in genere, inserirlo nella directory del file eseguibile) e ora si stanno scrivendo istruzioni SQL come un campione.Supponendo che è ciò che si desidera eseguire, ovviamente.

Ma è probabile che la maggior parte significativa degli sviluppatori di .NET Framework sono di norma nella gestione di un database SQL interamente “ manualmente ” tramite API di console, non sapeva come farlo o desidera tralasciare questo mondo.Moderno ambiente di .NET Framework fornisce ad un'ampia gamma di strumenti per la creazione e la gestione dello schema relazionale che tornando a questo approccio manuale ritiene positivamente primitivo e, soprattutto, unproductive.

Inoltre, Microsoft ha già trascorso sforzo creando un'API che descrive in modo efficace la maggior parte delle cose che un programmatore desidera eseguire in un database relazionale e vengono generati molti di tali strumenti (LINQ to SQL, Entity Framework e anche la finestra di progettazione di Visual Studio) di API.Riferimenti, naturalmente, ADO.NET e il relativo modello di provider.Non hanno la possibilità di diapositiva SQLite “ sotto ” ADO.NET significa che tutto quel modernità non è disponibile per lo sviluppatore utilizza SQLite e che feels come un difetto piuttosto significativo.La soluzione è quindi generare un provider ADO.NET per SQLite.

Come abbiamo già abbiamo scoperto, uno degli aspetti interessante sulle community open source è che sia veramente buone probabilità che qualsiasi cerchi di effettuare, qualcuno ha già fatto e ciò non è diverso.System.Data.SQLite, disponibile per il download all'indirizzo sqlite.phxsoftware.com, è un provider ADO.NET 3.5 completo, ovvero tutto ciò che gli sviluppatori possono eseguire con un provider di database relazionale tradizionale client/server è disponibile per SQLite gli sviluppatori, incluso tutto nella finestra di progettazione Visual Studio supportano LINQ e Entity Framework.

Utilizzo di System.Data.SQLite è piuttosto semplice.Ottenere il download (sia l'origine, pertanto è possibile creare personalmente e scrivere il codice per verificare il funzionamento di tutto in memoria, questo è un buon esempio per lavorare da imparare a creare un provider ADO.NET, se siete curiosi, o acquisire solo i file binari se si desidera ottenere rapidamente più “ done ”).Quindi rilasciare i bit in una posizione sul disco rigido, System.Data.SQLite.dll riferimento dal progetto e vita è buona.Non sorprende che le classi API live in System.Data.SQLite e una volta che si fa riferimento, è possibile scrivere buon codice ADO.NET ol'a fronte del database, come illustrato in di Figura 5.

Figura 5 con System.Data.SQLite

static void ManagedWrapperMain()
{
  var connStr = new SQLiteConnectionStringBuilder() 
    { DataSource = "persons.sqlite" };
  using (SQLiteConnection conn = new SQLiteConnection(connStr.ToString()))
  {
    conn.Open();
    SQLiteCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM Persons";
    var ct = cmd.ExecuteScalar();
    Console.WriteLine("Count = {0}", ct);

    cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Persons";
    SQLiteDataReader reader = cmd.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(reader);
    foreach (DataRow row in dt.Rows)
    {
      Console.WriteLine("{0} {1} {2}", row[0], row[1], row[2]);
    }
  }
}

Fin qui, tutto bene. Quando il codice viene eseguito da un Visual Studio 2005 o 2008 progetto, tutto funziona flawlessly. Ma quando il codice viene eseguito da Visual Studio 2010, un errore fornito, che dichiara “ eccezione non gestita: System.IO.FileLoadException: Modalità mista assembly creati con la versione v2.0.50727 del runtime e non può essere caricato in Common Language runtime 4.0 senza ulteriori informazioni sulla configurazione. ” Un assembly a modalità mista, per coloro che non hanno sentito il termine prima, è un assembly che contiene gestite Microsoft Intermediate Language e native x 86 istruzioni in linguaggio assembly. Naturalmente, non è consigliabile, su due livelli — uno, il problema ovvio è dobbiamo ottenere il codice di lavoro e due, se questo è un assembly misto, per creare alcuni problemi quando si utilizza SQLite in altri ambienti, ad esempio ASP.NET.

Il primo problema è facilmente risolto aggiungendo un file app.config indica 4.0 CLR per caricare l'assembly misti:

<?xml version="1.0"encoding="utf-8" ?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
</configuration>

Un problema più grave è che un numero di ambienti Don ’t supportano gli assembly misti e in ogni caso, vi è un certo aesthetic coinvolti qui.Per diversi motivi, sarebbe preferibile una soluzione gestita da tutti, ma poiché la DLL SQLite è codice nativo, che sarebbe difficile.Cosa sarebbe bello è una porta del codice SQLite base di C#, mantenere vicino al C originale possibile.

Managed tutto

Ancora una volta, la community open source fornisce quando richiesto e in questo caso, fornito un progetto denominato “ C#-SQLite, ” disponibile all'indirizzo code.google.com/p/csharp-sqlite .È stato avviato apparentemente “ esercizio per imparare il linguaggio C# ” dal porting del codice sopra e wiki associato dispone di alcune informazioni su ciò che l'autore ha per gestire la porta, ma il risultato è che ora abbiamo esattamente cosa è necessario: una versione di SQLite gestiti tutti.

Utilizzarlo, è necessario scaricare le origini del progetto, aprire il progetto e kicking disattivare una generazione.Numero di progetti open source, C#, ad esempio - SQLite è costituita da più progetti, ma ciascuno di essi è racchiuso il proprio file di soluzione, potrebbe essere necessario aprire più soluzioni.(O semplicemente avviano le generazioni dalla riga di comando con MSBuild, qualunque sia adatto.)

Una volta creato, aggiungere C# - SQLite assembly (Community.C ­­ Sharp ­ SQLite) al progetto e per il supporto ADO.NET, add C#-anche client SQLite assembly (Community.CsharpSqlite.SQLiteClient.dll).Ancora una volta, tutte le funzionalità di SQLite sono disponibili a noi tramite un provider ADO.NET che è possibile riscrivere quasi esatto stesso codice mostrato precedente (vedere di Figura 6).

Figura 6 con C#-SQLite

Static void AllManagedMain()
{
  SqliteConnection conn = 
    New SqliteConnection(@"Version=3,uri=file:persons.sqlite");
  conn.Open();
  try
  {
    SqliteCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM Persons";
    var ct = cmd.ExecuteScalar();
    Console.WriteLine("Count = {0}", ct);

    cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Persons";
    SqliteDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
      Console.WriteLine("{0} {1} {2}", reader[0], reader[1], reader[2]);
    }
  }
  finally
  {
    conn.Close();
  }
}

Si noti come le API sono quasi identiche alla versione precedente a modalità mista (solo i nomi di classe sono cambiati e anche allora è semplicemente una domanda del caso: “ SQLite ” vs.“ Sqlite ” come prefisso, ad esempio), ma ora si ottengono tutti i vantaggi SQLite senza i potenziali problemi di protezione (se presente) di una DLL in modalità nativa.

Limiti

Nonostante la natura meraviglioso di SQLite, è importante comprendere le limitazioni se la decisione tra SQLite e SQL Server deve essere eseguita con un livello di integrità.SQLite non potrà fornire tutte le funzionalità di SQL Server, lontano da esso.Database SQLite non desidera anche gli sviluppatori mediante l'utilizzo di più thread, molto meno accedervi da più thread.Infatti, è possibile affermare che se due programmi desidera accedere a un database SQLite simultaneamente, è probabilmente momento eseguire l'aggiornamento a un'istanza di SQL Server (Express o altrimenti).

Aree principali del SQLite “ win ” sarà in molte aree stesse che i file di Access utilizzati per occupare con una sintassi di SQL-92 accanto a completare per eseguire il backup, con possibilità di leggere i file di database utilizzati da altri ambienti (Python, Perl e così via).Utilizzo dal client Silverlight o telefono è un'area molto interessante, in particolare per l'archiviazione locale, rispettare un database SQLite in Silverlight l'archiviazione isolata sarebbe offrono agli sviluppatori un portatile (che possono essere trasmessi con il codice Silverlight) database in cui archiviare i dati locali, ad esempio.Utilizzare con cautela e SQLite Arrotonda out continuum un database relazionale delle opzioni della funzionalità di peso.

Anche se è presente un particolare argomento si desidera vedere esplorate, Don ’t esitare a me eliminare una nota.In modalità reale, è dopotutto la colonna.

Codifica felice!

Ted Neward  è un'entità con Neward & Associates, un'impresa indipendenti ed è specializzato in sistemi piattaforma .NET Framework e Java enterprise.Ha scritto più di 100 articoli, è un MVP C#, relatore di INETA autore e coautore di libri di una dozzina, compresa la prossima “ Professional F # 2.0 ” (Wrox).Egli consulta e mentors regolarmente.Contattarlo all' ted@tedneward.com e leggere il suo blog all'indirizzo blogs.tedneward.com .