Hanko uimaranta

  Pääsivulle  |  TIETOTEKNIIKAN PÄÄSIVULLE

SQLite Windows-ympäristössä

Vaihtoehtoiset asennustavat Visual Studioon

Vaihtoehtoiset tavat järjestää SQLite-tietokannat käytettäväksi Visual Studio -ohjelmoinnissa kehityskoneeseen:
a)   "Laaja pakettien asennus" eli asennetaan  sqlite-netFx46-setup-bundle-x86-2015-1.0.107.0.exe
      Tämä asentaa Ohjelmat-kansioon tarpeelliset dll-tiedostot

b)  Asennetaan vain muutamat kirjastot, eli "Vähimmäisasennus", esimerkiksi: 
     sqlite-netFx46-binary-Win32-2015-1.0.107.0.zip
     tämä puretaan Visual Studion kansioon esimerkiksi Projects \ External_105
     tämä on suositeltavin tapa useimmissa tapauksissa

c)  Haetaan SQLite-kirjastot NuGet-pakettina:
    -    Solution Explorer oikeassa laidassa, valitse hiiren oikealla näppäimellä projektisi nimi
    -    Tulee valikko, josta valitse 'Manage NuGet Package'
    -    Avautuu luettelo jossa on yläreunassa mm. 'Installed'
    -    Valitse 'Browse' ja aseta sopiva hakuehto
         Sieltä löytyy mm. System.Data.SQLite

Edellä on mainittu kaksi erilaista asennuspakettia. Niitä löytyy täältä.

"setup-bundle" tarkoittaa laajaa asennuspakettia käyttöjärjestelmän käytettäväksi
"binary" tarkoittaa vähimmäisasennuksen pakettia
"netFx46" viittaa dotnet-Frameworkin versioon
"x86" ja "Win32" tarkoittavat 32-bittistä käyttöjärjestelmää, "x64" tarkoittaa 64-bittistä
"1.0.107.0" jne tarkoittaa SQLite:n versionumeroa, normaalisti valitse uusin

Vähimmäisasennus

1. Lataa edellämainitusta latauspaikasta zip-paketti. Lue huolella ao.sivulla minkä paketin sieltä lataat. Huomaa, että 32-bittinen paketti toimii myös 64-bittisessä kehityskoneessa ja loppukäyttäjän koneessa. Itse olen käyttänyt 32-bittistä pakettia ja dotnet Framework 4.6 löytyy Windows 8.1- ja Windows 10-koneista (on siellä uudempikin).

2. Tee Visual Studion Projects-kansioon uusi kansio esim. Externals_107 lataamasi SQLite-version mukaisesti. Pura zip-paketti sinne. Käynnistä Visual Studio ja avaa projekti missä tarvitset SQLitea.

3. Jos projektisi käytössä on aiempi SQLite-versio, niin poista projektilta sen Reference (katso kuva seuraavassa kohdassa)

4. Lisää Reference lataamaasi SQLite-pakettiin (katso oheinen kuva). Hiiren oikealla painikkeella klikkaa nimeä References, seuraavaksi Add reference -> Browse ja mene kansioon johon juuri purit zip-paketin. Sieltä valitse "System.Data.SQLite.dll"
Reference-kuva

Lopputuloksena Reference-ryhmässä on viittaus paikoillaan:
Reference-kuva

Varmista, että Properties-ikkunassa on asetuksena Copy Local = True:
Properties-ikkuna

Projektisi ominaisuuksiin pitää asettaa lataamasi paketin tyyppi, menusta esimerkiksi Project Properties -> Build -> Platform target = x86

Käynnistä Visual Studio uudestaan ja avaa projektisi. Nyt SQLite on käytössä. Avaa projektisi bin\ Debug-kansio. Sieltä löytyy SQLite-paketin tiedostoja. Sinne pitää itse kopioida kaksi tiedostoa sieltä minne alussa avasit zip-paketin:
interop-tiedostot

Loppukäyttäjän koneeseen

Loppukäyttäjän koneeseen riittää  asentaa seuraavat kaksi dll-tiedostoa  samaan kansioon sovellustiedoston ja muiden ehkä tarvittavien dll-tiedostojen kanssa:
- System.Data.SQLite.dll
- SQLite.Interop.dll

Jos teet sovelluksestasi lopulta asennuspaketin, testaa, että em. tiedostot todella tulevat mukaan.

TIETOKANNAN KÄSITTELY KOMENTORIVILLÄ

Täältä löytyvät työkalut, joilla voit tutkia ja käsitellä tietokantatiedostoa komentokehotteessa (ns. dos-ikkunassa). Erityisen hyödyllinen on sqlite3.exe, jolla voi testata ohjelmakoodiin sijoitettavia SQL-lausekkeita.

Tällä hetkellä (11.2.2018) uusin versio työkalupaketista on:
sqlite-tools-win32-x86-3220000.zip
Työkalut ovat komentorivillä käytettäviä sqlite-apusovelluksia, jotka ovat samaa versiota kuin SQLite-paketit 1.0.107.0

sqlite3.exe
SQLite-tietokannan katselu ja muokkaus SQL-komennoilla. Kopioi tutkittava tietokanta samaan kansioon sqlite3.exe:n kanssa.
Ohjeet täällä  

sqldiff.exe
Kertoo kahden sqlite-tietokannan eroavaisuudet ym. Ohjeet täällä

sqlite3_analyzer
Kertoo lukumääriä ja kokoja tietokantatiedoston sisällöstä varsin teknisesti
Ohjeet täällä


TIETOKANNAN KÄYTTÄMINEN KOODISSA

Tietokannan avaaminen

Voit vapaasti valita tietokantatiedoston tiedostopäätteen. Olen itse käyttänyt päätettä 'db'

Yhteys tietokantaan, tietokannan avaaminen ja sulkeminen:
// yhteys tietokantaan
SQLiteConnection conlite = null;
// polkuz on tietokantatiedoston täysi polku
conlite = new SQLiteConnection($"Data Source={polkuz};Version=3;");
conlite.Open();
// yhteyden sulkeminen:
conlite.Close();
conlite = null;

Tai jos haluat käyttää connection stringiä erillisenä:
SQLiteConnection conlite = new SQLiteConnection();
string constr = @"Data Source=E:\Herbaario\Kuvat\Herbaario_testi.db;Version=3;";
conlite.ConnectionString = constr;

Omissa sovelluksissani olen käyttänyt ini-tiedostoa, jonka sisällön sovellus lukee aluksi. Siellä on määritelty ympäristökohtaisia tietoja, kuten juuri tietokantatiedoston nimi ja sijainti. Kun sovellus siirretään toiselle tietokoneelle, muokataan ini-tiedosto uutta ympäristöä vastaavaksi.

Tietokantayhteyden avaaminen ja sulkeminen vie paljon aikaa! Suunnittele sovellus niin, ettei turhaan välillä suljeta!

Näin saat sovelluksen sisällä helposti tietokannan polun, kun tietokantayhteys on muodostettu:
string kantapolkuz = conlite.FileName;

Luetaan tietokannasta tietoja

// Nyt on jo yhteys conlite avattu
int tulos = 0;  // miten lukeminen onnistui
SQLiteCommand kome = null;
SQLiteDataReader rdr = null;
string sqz = "SELECT idKuva, Aihe FROM Kuvat";
try
{
   kome = new SQLiteCommand(sqz, conlite);
   rdr = kome.ExecuteReader();
   while (rdr.Read())
   {
      int ida = (rdr.IsDBNull(0)) ? -1 : rdr.GetInt32(0); 
      string aihez = (rdr.IsDBNull(1)) ? "" : rdr.GetString(1);
     // tee tiedoilla jotain
   }
}
catch (Exception ex)
{
   string msgz = $"{sqz}\nTietokannan tai lukijan käsittelyssä tuli ongelma."
   string z = $"{msgz}\n{ex.GetType()}:{ex.Message}";
   MessageBox.Show(z, "(H03-07)");
   tulos = -1;
}
finally
{
   if (rdr != null)
      rdr.Close();
   if (kome != null)
      kome.Dispose();
}
if (tulos == -1)
{  // toimenpiteet kun ongelma
}

Joskus allaoleva rakenne on toimiva ja kompakti. Ei tarvitse huolehtia resurssien sulkemisesta. Virhetilanteen pyydystäminen ei aina onnistu reader-luupista:
using (SQLiteCommand kome = new SQLiteCommand(sqz, conlite))
{
   using (SQLiteDataReader rdr = kome.ExecuteReader())
   {
      while (rdr.Read())
      {
          ....
      }
   }
}

Jos tiedät lukevasi yhden integerin tietokannasta, niin ExecuteScalar() on usein käytännöllinen, esimerkiksi:
int tulos = 0;   // haun tulos
string sqz = "SELECT count(*) AS Kpl FROM Kasvit";  // aina löytyy tulos
// string sqz = "SELECT iarvo FROM Lajit WHERE ryhma = 7";  // ei tulosta, ryhmää 7 ei ole
SQLiteCommand kome = new SQLiteCommand(conlite, sqz);
object arvo = kome.ExecuteScalar();
if (arvo == DBNull.Value || arvo == null)
   tulos = -999;  // ei hakutulosta (sovittu arvo sovelluksessa)
else
   tulos = Convert.ToInt32(arvo);

Kirjoitetaan tietokantaan tietoja

Kirjoitetaan tietokantaan uusi rivi (INSERT), tehdään ensin SQL-lauseke
string sqz = "INSERT INTO Kuvat (Otsake, Koko, Lati, Longi) ";
sqz += "VALUES('Lomalla', 2239, '62.30987', '22.8765')";
Usein kannattaa käyttää StringBuilderia.

Päivitetään tietokannan aiempaa riviä, tehdään ensin SQL-lauseke
string sqz = "UPDATE Kuvat SET Paikka = 'Mikkeli', Koko = 1902 WHERE idKuva = 2944";

Ja itse tietokannan päivitys. Tämä kannattaa usein tehdä koodissa omaksi funktiokseen
SQLiteCommand kome = null;
int itu = -2;  // tulos
try
{
   kome = new SQLiteCommand(sqz, conlite);
   itu = kome.ExecuteNonQuery();
   if (sqz.StartsWith("DROP", true, null) || sqz.StartsWith("CREATE", true, null))  // ignore case
      itu = 0;
}
catch (Exception ex)
{
   MessageBox.Show($"Kysely epäonnistui:\n{sqz}\n{ex.Message}", "(M09-03)");
   itu = -2;
}
finally
{
   if (kome != null)
      kome.Dispose();
}
if (itu == -2)    // tarkoittaa että tapahtui jokin virhe
{
   MessageBox.Show($"Kysely epäonnistui:\n{sqz}", "(M09-04)");
}
// itu = -2  jokin virhe tapahtui (oma vakio)
// itu = kpl muutetut rivit jos UPDATE, INSERT, DELETE
// itu = 0 jos CREATE ja se onnistui
// itu = -1 muille komennoille

INSERTin jälkeen on helppo selvittää lisätyn rivin avainkentän arvo:
int uusid = (int)conlite.LastInsertRowId;

Blob-kentän lukeminen ja kirjoittaminen 

// luetaan blob tietokannasta johonkin
string sqz = "SELECT ... length(Bitmap), Bitmap ..."  // neljäs ja viides kenttä SELECT-lausekkeessa
SQLiteCommand kome = new SQLiteCommand(sqz, conlite);
SQLiteDataReader rdr = kome.ExecuteReader();
int blobkoko = (rdr.IsDBNull(4)) ? 0 : rdr.GetInt32(4);
if (blobkoko = 0)
{ // virhe...
}
Byte[] blobi = new Byte[blobkoko];
long pyteja = rdr.GetBytes(5, 0, blobi, 0, blobkoko);  // luetaan itse blob
// tehdään blobilla jotain ...

// kirjoita blobi tietokantaan (conlite):
byte[] bytet = ....  // tehty esim. kuvasta
sqz = "INSERT INTO Bitmapit (iKuva, Bitmap) VALUES (999, @bytet)";  // 999 vain esim
SQLiteCommand kome = conlite.CreateCommand();
kome.CommandText = sqz;
kome.Parameters.Add("@bytet", DbType.Binary, (int)(bytet.Length)).Value = bytet;
int itu = kome.ExecuteNonQuery();     // viedään thumbnail tauluun
kome.Dispose();
if (itu != 1) 
{ // ... epäonnistui 
}

Transaction

Transaktio on hyödyllinen perättäisten SQL-lausekkeiden toteuttamisessa seuraavanlaisissa tapauksissa:
1) Perättäisiä lausekkeita on paljon, kuten usein ohjelmaloopissa, tai
2) Jos peräkkäiset lausekkeet saa toteuttaa ehdolla, että viimeinenkin lauseke onnistuu

Rakenne sovelluksessa on periaatteessa seuraava:
- nimetään transaktiolle joukko sql-lausekkeita (jotka tulee testatuksi)
- jos joku lauseke epäonnistuu tehdään aikaisempien peruutus eli Rollback()
- kun kaikki onnistuivat, tehdään varsinainen toteutus eli Commit()

Ohjelmarakenteena transaktio voidaan toteuttaa kahdella erityyppisellä tavalla: using-rakenne, ja ns. normaalirakenne. Seuraavassa on esitetty molemmat.

// ensin rakenne 'using', se hoitaa myös Rollbackin vaikka komentoa ei tarvitse erikseen antaa
int ida = 234;  // esimerkissä käytetty muuttuja
string virhez = "";
bool onok = false;  // true = kaikki meni hyvin
using (SQLiteTransaction tr = conlite.BeginTransaction())
{
    using (SQLiteCommand kome = conlite.CreateCommand())
    {
        kome.Transaction = tr;
        kome.CommandText = $"DELETE FROM Albumit WHERE idAlb = {ida}";
        int itu = kome.ExecuteNonQuery();
        if (itu == 1)
        {
            kome.CommandText = $"DELETE FROM HenkAlbt WHERE iAlbumi = {ida}";
            itu = kome.ExecuteNonQuery();
            if (itu >= 0)
            {
                kome.CommandText = $"DELETE FROM SisAlbt WHERE iAlbumi = {ida}";
                itu = kome.ExecuteNonQuery();
                if (itu >= 0)
                    onok = true;
                else
                    virhez = "SisAlbt-siivous epäonnistui";
            }
            else
                virhez = "HenkAlbt-siivous epäonnistui";
        }
        else
            virhez = "Albumin poisto epäonnistui";
    }
    if (onok)
        tr.Commit();   // kaikki hyvin, kaikkien toteutus
    else
    {
        MessageBox.Show($"Poistaminen epäonnistui:\n{virhez}", "(D15-07) Ilmoitus");
    }
}

Seuraavana perinteisempi ohjelmarakenne. Siinä poistutaan throw-komennolla ohjelamluupista, jos tulee ongelmia, esimerkiksi:
throw new SQLiteException("Homma epäonnistui");
Tämä antaa catch:lle
ex.Message = "unknown error\r\nHomma epäonnistui"
Näkyvää virheilmoitusta varten tätä voidaan paikata:
string zz = ex.Message.Replace("unknown error\r\n", " ");

Ja sitten ohjelmakoodi kokonaisuudessaan:
int itu = 0;
int iar = 234;  // esimerkissä käytetty arvo
int onok = 0;  // tulos -1 (ongelma), 0 (ei muutoksia), 1 (kaikki ok)
SQLiteTransaction tr = null;
SQLiteCommand kome = null;
try
{
   tr = conlite.BeginTransaction();
   kome = new SQLiteCommand(conlite)
   {
      Transaction = tr,
      CommandText = $"DELETE FROM Henkilot WHERE idHlo = {iar}"
   };
   itu = kome.ExecuteNonQuery();
   if (itu == 1)
   {
      kome.CommandText = $"DELETE FROM HenkAlbt WHERE iHlo = {iar}";
      itu = kome.ExecuteNonQuery();
      if (itu >= 0)
      {
         tr.Commit();  // nyt toteutetaan SQL-lauseet
         onok = 1;
      }
      else
         throw new SQLiteException("HenkAlbt-siivous epäonnistui");
   }
   else
     throw new SQLiteException("Henkilot-taulusta ei saatu poistettua");
}
catch (SQLiteException ex)
{
   MessageBox.Show($"Poistaminen epäonnistui:\n{ex.Message}", "(E16-07) Ilmoitus");
   onok = -1;
   if (tr != null)
   {
      try
      {
         tr.Rollback();
         MessageBox.Show("Poistokäskyt peruutettiin.", "(E16-08) Ilmoitus");
      }
      catch (SQLiteException ex2)
      {
         MessageBox.Show($"Poiston peruutus epäonnistui:\n{ex2.Message}", "(E16-09) Ilmoitus");
      }
      finally
      {
         tr.Dispose();
         tr = null;
      }
   }
}
finally
{
   if (kome != null)
      kome.Dispose();
   if (tr != null)
      tr.Dispose();
}
// onok = -1 (ongelma), onok = 0 (ei muutoksia), onok = 1 (kaikki ok)



--------------------------------
(sivua muokattu  11.2.2018)