Maisema Nummen Pitkäjärvi

  Pääsivulle  |  TIETOTEKNIIKAN PÄÄSIVULLE

Tietokannat

SQL-kyselyt ja relaatiotietokannat

Relaatiotietokannan sisältöä muokataan ja tarkastellaan yleisimmin SQL-kyselykielellä. Tästä on joskus sanottu, että se on yksi epäloogisimmista kielistä. Mutta sitä ei tarvitse kovin paljon opiskella, kun jo pystyy tekemään relaatiotietokannoille kaikki olennaisimmat asiat.

Hommaa mutkistaa se, että eri relaatiotietokannat haluavat käytettävän hieman toisistaan poikkeavia avainsanoja ja kyselyn logiikoita, esimerkiksi: Microsoft Access, SQL Server, SQL Server Compact, SQLite, Oracle. Skandit tuottavat yllätyksiä, joita pitää käsitellä eri tavoin eri tietokannoissa.

SQLite

SQLite on nyt minun tietokantamoottorini tärkeimmissä sovelluksissani. Tietokanta on yhtenä tiedostona. Tuki on riittävä sekä Windows- että Mac-ympäristöissä, ja sama tietokantatiedosto toimii odotetulla tavalla molemmissa ympäristöissä. Kyselyjä voi tehdä tarvittaessa komentokehotteesta esimerkiksi sitä varten, että saa testatuksi toimiiko jokin kysely ennenkuin jättää sen sovellukseen. Tätä kirjoitettaessa (7.2.2018) uusin stabiili versio on 3.22.0. Macin käyttöjärjestelmässä (macOS High Sierra) on käytössä versio 3.19.3.

Huippukirja SQLite:n opiskeluun:
Grant Allen and Mike Owens: The Definitive Guide to SQLite (Apress, Second edition 2010)

SQLite:n SQL-kielessä on erinomaisia rakenteita, jotka tehostavat tietojen hakua, ja niitä kannattaa käyttää!

Kenttien tietotyypit ovat INTEGER, REAL, TEXT, BLOB ja NULL. Koska kenttien tietotyyppinä ei ole DateTime, niin käytän sen sijaan tietotyyppiä TEXT, ja laitan päivämäärän sinne muodossa 2010-09-20T08:22:00Z tai ilman aikaa jos pelkkä päivämäärä riittää.

Jos tietokannassa kentän määre on esimerkiksi TEXT tai INTEGER, niin sinne voi myös tallentaa NULL (tai muitakin). Kantaa luettaessa Windowsissa (using System.Data.SQLite) tämän voi erottaa (myReader.IsDBNull(ii)), mutta macOS:n Swiftissä (FMDB-kirjasto) vain TEXTin osalta. Swiftissä INTEGER-kentän NULL-arvo antaa luettaessa arvon 0 (Int32), mutta TEXT-kentän lukeminen antaa tietotyypin String? ja saadaan ero onko meillä nil vai merkkijono.

SQLite ei osaa kaikilta osin käsitellä skandeja oikein, kun kyse on niiden muunnoksista ä -> Ä ja Ä -> ä. Esimerkiksi UPPER(), LOWER() ja LIKE eivät toimi odotetulla tavalla. Mutta aakkostus (ORDER BY) toimii oikein myös skandien osalta, joten Collation-asiasta ei tarvitse erikseen huolehtia, mikä olisikin hyvin hankalaa. Esimerkki skandiongelmasta: jos meillä on tietokannassa paikkakunnan nimi "Vähä-Äiniö" ja sovelluksessa käyttäjä antaa hakuehdon "äiniö", niin seuraava SQL-lauseke ei löydä haluttua paikkakuntaa:
SELECT Paikkanimi FROM Paikat WHERE Paikkanimi LIKE '%äiniö%'

Myöskään ei voi käyttää UPPER()- tai LOWER()-funktioita korjaamaan tilannetta. Olen hoitanut tilannetta C#- ja Swift-apufuktioilla, joka antaa hakusanasta kaikki skandivaihtoehdot: äiniö, Äiniö, äiniÖ, ÄiniÖ, ja WHERE-ehdossa käytetään vaihtoehtoina näitä kaikkia.
 
Olen koonnut tärkeimmät SQLite-komennot tänne.

SQLite:n käyttöönotto ja käyttäminen eri ympäristöissä: Windows, macOS

Microsoft Access

Microsoft Access on erinomainen tietokantatyökalu. Hienoin piirre on hyvin havainnollinen tapa suunnitella tietokantoja ja kyselyitä. Kaaviokuvan ja SQL-lausekkeen välillä voi vaihtaa nopeasti. Sisäänrakennettu VisualBasic-ohjelmointimahdollisuus antaa hyvät edellytykset pienten sovellusten tekemiseen. Huonoin puoli on kallis hinta. Se on mahdollista hankkia vain kalleimpien Office-pakettien mukana. Niinpä olen luopunut sen käyttämisestä sellaisissa omissa projekteissani, joiden tuotoksia on tarkoitus antaa muillekin.

Töissä Access oli minun lähes jokapäiväinen työkaluni erilaisissa tietojen jäsentely- ja konvertointitarpeissa. Tein iltaharrastuksena laajojakin VisualBasic-sovelluksia liittyen ao. tietokantaan. Minulle syntyi ohjelmakirjastoja, joita kopioin kannasta toiseen. Sovellukset liittyivät usein tietokantamuunnoksiin, mutta tein pari itsenäistä sovellustakin kotikäyttöön ja tilapäiseen työkäyttöön.

Access on ns. henkilökohtainen tietokanta, eli se soveltuu huonosti tilanteeseen, jossa samaa palvelimella olevaa kantaa käyttää useampi henkilö. Yhtaikainen saman taulun päivitys voi tuoda ongelmia.

C#-koodissa connection string Access2000-kantaan on:
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + polkuz + ";User ID=;Password=;";

C#-koodissa connection string Access2007- ja Access 2010-kantoihin on:
string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + polkuz + ";Persist Security Info=False;";

SQL Server

SQL Server oli työpaikallani keskeisin tietokantamoottori isoissa yhteisissä tietokannoissa. Erilaiset tietojen tuonnit ja tarkastelut Accessin avulla olivat nopeita ja helppoja. Kotona tutustuin tuotteeseen (SQL Server 2008 Express) rakentamalla albumisovellukseni muutamaksi vuodeksi sen pohjalle. SQL Server -kantaa perustettaessa on ilmoitettava aikooko käyttää suomalaista aakkosjärjestystä (Collation). Jo perustetussa tietokannassa asiaa ei voi enää muuttaa. Varmistukset ja kopioinnit olivat hankalia. Koska albumisovellusta piti jakaa myös lapsillemme, oli SQL Server liian hankala kotikäyttöön ja lopetin pian sen käyttämisen.

C#-koodissa connection string SQL Server Express -kantaan on versiosta riippuen:
string connstr = @"Provider=sqloledb;Server=MANSKU\SQLEXPRESS;Database=Albumi;Trusted_Connection=yes;";
string connstr = "Provider=sqloledb;Data Source=(local);Initial Catalog=Albumi;Integrated Security=SSPI;";
Server-kohdassa on ensin työaseman nimi ja perässä SQL Serverin määrittelyssä kannalle annettu nimi.

SQL Server Compact on monipuolinen tietokanta ja kotikäytössä siinä kätevä, että tietokanta on yhtenä tiedostona. Olen omissa sovelluksissani käyttänyt sitä vuosina 2009-2014. Visual C# Expressissä oli aiemmin myös hyvä hallinto-osio, jossa pääsi tekemään SQL-kyselyjä suoraan kantaan, mutta se katosi jossain päivitysvaiheessa. Tällä hetkellä se puuttuu Visual C# Express 2010 -versiosta (Windows 8), eikä varmaan tule takaisinkaan, koska Microsoft on lopettamassa tukeaan SQL Server Compactille. Sen viimeisin versio on vuodelta 2013 ja tiettävästi kaikki tuki loppuu vuonna 2021.

C#-koodissa connection string SQL Server Compact -kantaan (sdf-tiedostoon) on:
string connstr = @"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" + polkuz + ";";


Tietokannan muunnos SQL Server Compact -> SQLite

Ensin on suunniteltava SQLite-kannan taulujen rakenteet. Minulle sopi eräässä projektissani hyvin samannimiset taulut samalla periaaterakenteella, mutta muutoksia tuli jonkin verran, koska SQLite:ssa tietotyyppejä on vähemmän ja toiminnassa on hieman eroja. DateTime:n korvasin TEXTillä. Lisäksi SQL Server Compact -kannan nvarchar-kentissä olevat tyhjät kentät (null) korvasin SQLite:n tietotyypillä NULL. Eli esimerkiksi sarakkeeseen Lisatiedot kirjoitin tekstiä (TEXT) jos sitä oli ja null jos sitä ei ollut, enkä siis tyhjää merkkijonoa "".

DateTime-kentässä ollut tieto tekstiksi, mallina 2010-09-20T08:22:00Z.

Perustetaan kanta komennolla sqlite3 testi.db komentokehotteessa.

Tässä vaiheessa voisi määritellä Collationin, mutta skandien osalta siitä ei ole hyötyä, ellei ole itse tehnyt sitä varten funktioita (mikä on vaivalloista).

Vielä ei kantaa syntynyt, vaan ensin pitää tehdä taulu. Sitä varten tehdään tekstitiedosto (vaikkapa teetaulut.sql), jossa on komennot kannan taulujen tekemiseen, tässä esimerkiksi yksi rivi eli yksi tauluista:
CREATE TABLE Bitmapit ( idBitmap INTEGER PRIMARY KEY, iKuva INTEGER, Bitmap BLOB, Timestamp TEXT );

Ajetaan taulujentekoskripti:
.read teetaulut.sql

Poistu sqlite3-shellistä komennolla .exit

Nyt meillä on tyhjä kanta. Ota siitä heti kopio, luultavasti tarvitset sitä vielä, koska ei konversio kuitenkaan heti onnistu.

Tee sovellus (esim. C#-sovellus), joka lukee rivi kerrallaan datat SQL Server Compact -kannasta ja kirjoittaa ne tarvittaessa muunnettuna SQLite-kantaan. Minulla kirjoitettavaa oli eri tauluissa yhteensä noin 60 000 riviä. Konversioajo olisi kestänyt tunteja, totesin pienellä testillä. Tämä on liian pitkä aika, koska konversio ei kuitenkaan onnistu ekalla kerralla.

Perustin RAM Diskin käyttäen sovellusta Dataram RAMdisk, kooksi otin 3000 Mb (Samsung-läppärissäni on 16 gigaa keskusmuistia). Vein levylle muunnossovelluksen, tietokantatiedostot ja kirjastotiedoston System.Data.SQLite.dll. Nyt konversioajo vei vain runsaat neljä minuuttia, eli sopivan lyhyen ajan.

Itse sovelluksessa (minulla Albumi- ja Herbaariosovellukset) oli
- vaihdettava kaikki SQL Server Compact -rakenteisiin viittaavat määritteet vastaaviksi SQLite-määritteiksi, esimerkiksi SqlCeConnection ja SqlCeDataReader -> SQLiteConnection ja SQLiteDataReader. Tämä tapahtuu nopeasti etsi-korvaa-menetelmällä
- käytävä läpi kaikki SQL-lausekkeet
- tutkittava ja korjattava kaikki kohdat joista kannasta haetaan päivämäärää ja aikaa.

Lopuksi varsinainen sovellus tuli testata monipuolisesti.

Tietokantakonversio Accessin avulla

Työpaikalla uusittiin vuoden 2007 tienoilla diaaritietokanta ja vanhasta tuotiin uuteen vain keskeneräiset asiat. Kuitenkin virkailijoilla oli tarvetta katsella vanhoja diaaritietoja, mutta se oli käytännössä mahdotonta, koska vanhimmat tiedot olivat tietokannassa, jonka käyttöliittymää ei enää voinut käyttää. Tietohallintoa kuormitti tietokyselyt. Itse tietokantatiedostot kyllä olivat tallella Oraclen useissa erillisissä taulurakenteeltaan erilaisissa relaatiotietokannoissa. Tarvittiin siis katselusovellus ja yhdistelmätietokanta, johon on tuotava tiedot vanhoista erillisistä tietokannoista. Lyhyt keskustelu erään it-firman kanssa osoitti, että projektista olisi ostettuna tullut hyvin kallis vaikeasti selitettävien muunnoslogiikoiden ja uuden käyttöliittymän rakentamisen takia.

Koska vanhastaan tunsin substanssin, niin päätin tehdä homman itse harrastuksena. Tekninen idea oli yksinkertainen: tuon taulut Oracle-kannoista MS Office Accessiin, päätin millainen on lopullinen taulurakenne, ja perättäisin taulumuunnoksin saatoin alkuperäisen datan lopulliseen muotoon. Access oli lopulliseksi kannaksi riittävä, koska tietokantaan ei tehdä muutoksia eikä käyttäjiä ole useita. Muunnokset piti tehdä niin, että oli helppo tarkastaa että datasisältö ei muuttunut. Muunnosten välillä korjasin datan muotoa VisualBasic-koodein ja paikoin käsin. Muunnosvaiheita oli kymmeniä, mutta ne olivat luontevia ja nopeita. 

Lopullinen sovellus oli yksinkertainen Visual C# Expressillä tehty Windows Forms -sovellus, jossa oli hakuehtoikkuna ja muutama hakutuloksia esittelevä ikkuna. Tämä oli noin kahden viikon iltaprojekti. Yksi helposti korjattava bugi löytyi myöhemmin käyttöliittymästä.

Ylipäätään tietokantamuunnokset on tehokas tehdä perättäisillä taulukonversioilla Accessissa. Virheiden jäljille pääsee helposti. Kun välivaiheet on tallella ja vaiheet dokumentoitu, virhetilanteessa voi palata korjaamaan ja tehdä viimeisiä vaiheita uudestaan.


--------------------------------
(sivua muokattu 4.9.2018)