Tietokantasuunnittelua

Tekijä: Marko Seppänen
Teostyyppi: harjoitustyö
Oppilaitos: Saimaan ammattikorkeakoulu
Julkaisuajankohta: Joulukuu 2009
Kurssi: Tietokantasuunnittelu

 

 

1 Johdanto

2 Käsiteanalyysi ja ER-kaavio (vaihe I)

3 Tietokannan suunnittelu (vaihe II)

4 Tietokannan toteutus (vaihe III)

4.1 Taulujen luonti

4.2 Taulujen täyttö

4.2.1 Lohkot

4.2.2 Heinänsiemenet

4.2.3 Heinäpaalit

4.3 Tiedon hakeminen tauluista

4.3.1 Vaihtoehtona kyselyiden luominen visuaalisavusteisesti

4.3.2 Ostettujen paalien hinta + tuotantokustannukset

5 Tietokannan takaisinmallinnus (bonusvaihe I)

6 Tietokantakonversioita (bonusvaihe II)

7 Lähteet


 


1  Johdanto

"Helly-Hansen von af gryn Bööbil peri iso-tädiltään kartanon ja 500ha (pelto)maata, joka on jaettu 30:een lohkoon ja aloitti heinänviljelyn. "

Näillä sanoin alkaa tehtävänanto, jonka tarkoituksena on määrittää kuvitellun asiakkaan tarpeet. Tarkoituksena on suunnitella tietokanta, jolla "HH bisneksensä hanskaa". Heinälaatuja on kuutisen kappaletta, apulannoitteitakin saatetaan käyttää, jotkut peltolohkot saattavat olla kesannolla, osa heinäpaaleista on kuivapaaleja ja osa hapotusainetta saaneita säilöpaaleja. Kaikilla tarvikemateriaaleilla ja myyntiartikkeleilla on oma vaihteleva hintansa, jne.

Alkuperäinen tehtävänkuvaus antaa (rivien välistä) ymmärtää, että harjoittelun nimissä on sallittua hiukan oikaista ja olla pohtimatta ihan jokaista pientä yksityiskohtaa, jottei toiminnan fokus kirpoaisi kokonaisuuden hahmottamisesta. Tämän päätelmän voi johtaa siitä, että on lukuisia variaabeleja, joihin tehtävänanto ei ota kantaa ja jotka voi ja käytännössä joutuu valitsemaan itse. Tarpeita kuvaava dokumentti lienee tarkoituksellisesti ylimalkainen ja deskriptiivisyyden asteeltaan ns. hutaisu, pyrkimyksenä ollen kehittää tietokannan suunnittelijan kykyä nähdä ja ymmärtää todelliset ja tulevat tiedostamattomat tarpeet tekstipohjaisen selvityksen takana.

Yleensä ottaen, tietokannan suunnittelua ei ohjaa mikään sellainen välttämättömyys, joka ohjaisi tiettyjen samojen lähtökohtien vallitessa samanlaiseen lopulliseeen tietokannan rakenteeseen ja sitä käsitteleviin SQL-lauseisiin. Vaihtoehtoisia toteutustapoja on runsaastikin, joista tässä dokumentissa esitellään eräs niistä.

2  Käsiteanalyysi ja ER-kaavio (vaihe I)

"Käsiteanalyysi (ER modelling, conceptual modelling, conceptual design, data modelling) on ensimmäisiä vaiheita tietokannan suunnittelussa. Käsiteanalyysin tavoitteena on määrittää ja kuvata havainnollisella kaaviolla tietokantaan tallennettavia tietoja, jotta lopulta voidaan perustaa tarpeita hyvin palveleva tietokanta. Käsiteanalyysissä kuvataan sitä reaalimaailman osaa eli kohdealuetta, jota on tarkoitus kuvata tietokannassa." (Hovi ym. 2005)

Omasta ER-kaaviostani, jonka pohjalta varsinaista tietokantaa lähdin suunnittelemaan, tuli allaolevanlainen. Jos sitä haluaisi käyttää kommunikoinnin apuvälineenä, se pitäisi vielä kertaalleen tehdä uusiksi, sen pohjalta suunniteltua tietokantaa vastaavaksi (monilukuisuuksien tarkistus, relaatiot, attribuutit, käsitteet). Mm. sadon käsitteen käyttö ja siihen liittyvät yhteydet menivät täysin uusiksi, kokoavaa tarvikkeen käsitettä ei enää ole, säilöpaalista tuli hapotuspaali, myyntipaali tuli lisäksi, mutta toisaalta ennen kuin yhtään tietoa hakevaa SQL-kyselyä oli tehty, tietokannan rakenne oli jo löytänyt lopullisen muotonsa.

3  Tietokannan suunnittelu (vaihe II)

Tietokannaksi valitsin MySQL:n. Kahdesta syystä: halusin ensin toteuttaa tietokannan entuudestaan tutulle tietokannalle ja vasta sen jälkeen ainakin toiselle sellaisella, josta halusin lisää tuntumaa (esim. Oracle Database); toisekseen MySQL Workbench -paketin tietokannan suunnittelutyökalu on vihdoinkin kehittynyt sille tasolle, että se on vakaa ja tarjoaa hyvän tuen yksityiskohtaiseen tietokannan suunnitteluun forward/reverse engineering -toiminnoilla.

Tietokantaa suunnitellessa kolmanteen normaalimuotoon suuntautumista tapahtui huomaamattaankin, kuten myös tulevan tiedon tarpeettomuuden toisteisuuden välttämistä. Hyödylliseksi ja suorastaan tarpeelliseksi osoittautui käytännön tilanteiden miettiminen. Esim. saadaanko yksittäisessä kuivapaalissa pysymään tarra, joka kertoo sen koodinumeron. Entä onko jokin siemenlaji erityisen monivuotinen ja kuinka paljon heinän korkeusvaihtelut tapaavatkaan olla? Monenlaisia oletuksia oli tehtävä, kuten se että heinän nimi yksilöi heinänsiemenet sillä tapaa, että sitä tuottavat tehtaat/laitokset tuottavat aina samaa tasalaatuisuutta.


4  Tietokannan toteutus (vaihe III)

 

4.1 Taulujen luonti

MySQL Workbenchin forward engineer -toiminto luo suunnitellun tietokannan pohjalta SQL-lauseita, jotka luovat varsinaisen tietokannan, johon tietoa voi tallentaa, josta tietoa hakea ja jossa olevaa tietoa voi päivittää. Jouhevuuden vuoksi en aina tietokannan perustuksia hienosäätäessä, korjaillut fyysisen tietokannan vajeita, vaan tyhjensin tietokannan ja ajoin mallin mukaiset taulujen luontilauseet sisään uudelleen. Koska kyse oli InnoDB-tietokantamoottoria käyttävästä tietokannasta, en voinut vain poistaa tauluja, sillä viite-eheyksien tarkistus olisi estänyt sen. Tyhjennystä ennen kytkin viiteavainen olemassaolon tarkistuksen toviksi pois päältä käskyllä "set FOREIGN_KEY_CHECKS = 0;". Tämän jälkeen poistin DROP TABLE ja DROP PROCEDURE -käskyillä käytetyt tauluja ja proseduurit. Tässä tapauksessa tämä vaihtoehto tuntui nopeammalta ja käytännöllisemmältä kuin mallin ja tietokannan manuaalinen synkronoiminen, mihin MySQL Workbench myös toiminnon tarjoaa.

Tyypillinen taulun luontilause näyttää seuraavanlaiselta:

CREATE TABLE IF NOT EXISTS `heinamies`.`LohkoSisalto` (

  `lohkonro` INT NOT NULL AUTO_INCREMENT ,

  `kayttoonotto` TIMESTAMP NOT NULL ,

  `FyysinenLohko_lohkoid` INT NOT NULL ,

  `Sato_satokoodi` CHAR(5) NOT NULL DEFAULT 'kevat' ,

  PRIMARY KEY (`lohkonro`) ,

  INDEX `fk_LohkoSisalto_FyysinenLohko1` (`FyysinenLohko_lohkoid` ASC) ,

  INDEX `fk_LohkoSisalto_Sato1` (`Sato_satokoodi` ASC) ,

  CONSTRAINT `fk_LohkoSisalto_FyysinenLohko1`

    FOREIGN KEY (`FyysinenLohko_lohkoid` )

    REFERENCES `heinamies`.`FyysinenLohko` (`lohkoid` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_LohkoSisalto_Sato1`

    FOREIGN KEY (`Sato_satokoodi` )

    REFERENCES `heinamies`.`Sato` (`satokoodi` )

    ON DELETE NO ACTION

    ON UPDATE CASCADE)

ENGINE = InnoDB;

 

4.2 Taulujen täyttö

 

4.2.1 Lohkot

Peltolohkojen mittasuhteita ei varsinaisesti tullut käytetyksi missään, pelkästään pinta-alaa, mutta tulipa kuvitellun tarpeen paremmaksi hahmottamiseksi laskeskeltua mahdollisia arvoja leveyksiksi ja pituuksiksi:

Tietokannassa on tauluina sekä FyysinenLohko, että LohkoSisalto. FyysistäLohkoa vastaavia LohkoSisaltoja tulisi kertymään useitakin, jopa samana vuonna, jos kyseisellä FyysiselläLohkolla viljeltäisiin heinää sekä keväällä että syksyllä. Täytin peltolohkojen ensikäyttöä varten tietokannan alla olevan mukaisesti eli esim. vasemmassa yläkulmassa olevaan lohkoon olisi laitettu heinänsiemeniksi siemenlaatua H1 (Nurmipuntarpää), sekä lannoitteena L1 (RaisioNPK). Paaleja kyseiseltä lohkolta saatiin sillä kertaa sattumalta täsmälleen saman verran kuin monelta muultakin lohkolta eli 500 kuivapaalia (20kg/kpl). Pienemmät paalimääriä kuvaavat arvot viittaavat säilöpaaleihin (200kg/kpl). Määrät laskettu paalien punnituista arvoista. Happoja on säilöpaaleihin ruiskutettu kolmea erilaista.

 

4.2.2 Heinänsiemenet

Heinänsiemenet lisättiin tietokantaan seuraavanlaisiin SQL-lausein:

INSERT INTO heina (nimi, tarve) VALUES ('Nurmipuntarpää',30);

INSERT INTO heina (nimi, tarve) VALUES ('Timotei',20);

INSERT INTO heina (nimi, tarve) VALUES ('Nurmilauha',30);

INSERT INTO heina (nimi, tarve) VALUES ('Punanata',20);

INSERT INTO heina (nimi, tarve) VALUES ('Niittymaarianheinä',30);

INSERT INTO heina (nimi, tarve) VALUES ('Nurmirölli',40);

 

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (350, NOW(), 400, 400, 'Nurmipuntarpää');

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (350, NOW(), 400, 400, 'Timotei');

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (350, NOW(), 400, 400, 'Nurmilauha');

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (350, NOW(), 400, 400, 'Punanata');

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (350, NOW(), 400, 400, 'Niittymaarianheinä');

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (350, NOW(), 400, 400, 'Nurmirölli');

INSERT INTO heinatarvike

  (ostohinta, ostopaiva, ostomaara, kiloajaljella, Heina_nimi)

  VALUES (300, NOW(), 400, 400, 'Nurmirölli');

 

INSERT INTO lohkosisalto_has_heinatarvike

  (LohkoSisalto_lohkonro, HeinaTarvike_ostoid)

  VALUES

  (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),

  (7,1),(8,2),(9,2),(10,2),(11,2),(12,2),

  (13,3),(14,3),(15,3),(16,2),(17,2),(18,2),

  (19,4),(20,4),(21,5),(22,6),(23,6),(24,6),

  (25,4),(26,5),(27,5),(28,6),(29,6),(29,7),(30,7);

 

4.2.3 Heinäpaalit

 

4.2.3.1  Leikatut heinäpaalit

Paaleja oli lisättäväksi niin paljon, että yksittäisiä tai yhdistettyjä INSERT-lauseita käytännöllisempää oli luoda niiden lisäämistä varten oma proseduusinsa.

DELIMITER |

 

CREATE PROCEDURE lisaaPaaleja

(

  IN  lohko        int,

  IN  paalikpl     int,

  IN  paalityyppi  char,

  IN  paalipaino   int

)

  MODIFIES SQL DATA

BEGIN

 

  DECLARE maara INT;

  SET maara = 1;

 

  WHILE (maara <= paalikpl) DO

    INSERT INTO paali (paino,LohkoSisalto_lohkonro,Paalityyppi_paalityyppi)

    VALUES (paalipaino,lohko,paalityyppi);

    SET maara = maara + 1;

  END WHILE;

 

END|

 

DELIMITER ;

Proseduuria kutsuttiin per lohko seuraavantapaisesti (parametreina lohkonro, paalimäärä, paalityyppi ja paino):

CALL lisaaPaaleja(12,500,'k',20);

CALL lisaaPaaleja(13,50,'s',200);

 

4.2.3.2  Hapotetut paalit

Oletuksena on ollut, että kaikki säilopaalit hapotetaan jollakin tietyllä hapolla ja tietyllä lohkolla käytetään vain yhtä happoa ja tietyltä lohkolta leikataan vain joko kuiva- tai säilöpaaleja.  Kolmeen lohkoon, joista kaksi voisi olla samalta fyysiseltä lohkolta, mutta kahdelta eri saman vuoden viljelykaudelta ja kolmas joltain muulta fyysiseltä lohkolta tai samalta fyysiseltä lohkolta, mutta eri vuodelta. Sitä ei tässä oteta huomioon, mikä on hapotuksen vaikutusaika, mutta periaatteessa kaikki yksityiskohdat on haluttaessa mahdollista ottaa huomioon.

INSERT INTO HapotusPaali (Paali_paalikoodi,HapotusaineTarvike_ostoid)

SELECT * FROM

(SELECT paali.paalikoodi, (SELECT ostoid FROM HapotusaineTarvike WHERE Hapotusaine_nimi = 'Happo1') AS happoostoid

FROM paali WHERE paali.LohkoSisalto_lohkonro IN (13,14,15)

) AS hapotettavatpaalit;

 

4.2.3.3  Myydyt paalit

Tässä asiakas on aiheuttanut myynnin, ostaessaan kahdelta eri lohkolta (7 ja 13) kahta erityyppistä paalia (20 kpl molempia paalityyppejä).

INSERT INTO Myynti (Asiakas_asiakasid) VALUES(1);

INSERT INTO MyyntiPaali (Paali_paalikoodi,Hinta_hintanro,Myynti_myyntiid)

SELECT * FROM

((SELECT

  paali.paalikoodi, (SELECT hintanro FROM Hinta WHERE Paalityyppi_paalityyppi = 's' LIMIT 0,1) AS hintanro,

(SELECT m.myyntiid FROM Myynti m WHERE m.Asiakas_asiakasid = 1 LIMIT 0,1) AS myyntiid

FROM paali

  LEFT JOIN myyntipaali ON (paali.paalikoodi = myyntipaali.Paali_paalikoodi)

WHERE paali.LohkoSisalto_lohkonro = 13 LIMIT 20)

UNION ALL

(SELECT

  paali.paalikoodi, (SELECT hintanro FROM Hinta WHERE Paalityyppi_paalityyppi = 'k' LIMIT 0,1),

(SELECT m.myyntiid FROM Myynti m WHERE m.Asiakas_asiakasid = 1 LIMIT 0,1) AS myyntiid

FROM paali

  LEFT JOIN myyntipaali ON (paali.paalikoodi = myyntipaali.Paali_paalikoodi)

WHERE paali.LohkoSisalto_lohkonro = 7 LIMIT 20)

) AS valitutpaalit;

 

4.3 Tiedon hakeminen tauluista

 

4.3.1 Vaihtoehtona kyselyiden luominen visuaalisavusteisesti

Jos SQL itsessään on hyvin ns. hallussa ja ymmärrys viiteavainten päälle on laadukasta, visuaalisavusteisesta kyselyiden luomisesta voi olla paljonkin apua. Toisaalta, jos tietokannan rakenne on tuttu, mahdollisuudesta rakentaa kyselyt autocomplete-avusteisesti on myös apua. SQL Maestro for MySQL:lästä löytyvät nämä molemmat avusteet. Visuaalisavusteisesti on sinänsä varsin kätevä tehdä hakuja liitoksineen, ryhmityksineen ja ehtoineen.

 

4.3.2 Ostettujen paalien hinta + tuotantokustannukset

Jokaiselle yksittäiselle paalille voidaan laskea sen tuottamiseen vaikuttaneiden tarvikkeiden (siemenet, mahdollinen lannoitus, sekä säilöpaaleilla hapotusaine) paalikohtainen kustannus. Alla käydään esimerkinomaisesti läpi yksittäisiä SQL-lauseita, joita hyödyntämällä johonkin tiettyyn myyntitapahtumaan liittyvät kustannukset ja paalien hinta saataisiin tietää. Tässä käytetään pelkästään SQL-lauseita; toisenlaisella lähestymistavalla (esim. Java-ohjelmointi huomioiden) saatettaisiin päätyä esim. vähempiin alikyselyihin.

Jos haluttaisiin välttää myyntiid-arvon hakemista aina uudelleen, kun sitä tarvitaan, sen voi tallettaa myös muuttujaan:

/* myyntiid (esim. 11) */

 

SET @myyntiid =

(SELECT myynti.myyntiid

FROM myynti INNER JOIN asiakas ON (myynti.Asiakas_asiakasid = asiakas.asiakasid)

WHERE asiakas.asiakasid = 1 LIMIT 1);

Lohkonumerot, joihin myyntitapahtumaan sisältyvät paalit (myyntipaalit) kuuluvat, saataisiin tietää seuraavanlaisesti:

/* lohkonrot paaleille (esim. 7 ja 13) */

 

SELECT DISTINCT

  paali.LohkoSisalto_lohkonro

FROM

  myyntipaali

  INNER JOIN paali ON (myyntipaali.Paali_paalikoodi = paali.paalikoodi)

WHERE

  myyntipaali.Myynti_myyntiid = @myyntiid;

Koska tuo voi palauttaa useitakin lohkonro-arvoja, ei vastausta voisi sijoitaa suoraan johonkin toiseen yhteyteen yksittäisenä INT-arvollisena muuttujana, vaan jokainen arvo olisi käytävä erikseen läpi. Oletettakoon tässä, että lohkonumero olisi lohko numero 13 ja käytetään sitä jatkossa.

SET @lohkonro = 13;

Kustannukset per lohko voitaisiin laskea jo ennakkoon valmiiksi ja tallettaa nämä tiedot johonkin lisätauluun, jottei niitä tarvitsisi prosessoida erikseen esiin joka kyselyn yhteydessä. Yksittäiselle lohkolle heinänsiementen aiheuttama kustannus laskettaisiin seuraavasti:

/* heinakustannusPerLohko */

 

SET @heinakustannusPerLohko =

(SELECT heinadata.tarve * heinadata.ka * (SELECT fyysinenlohko.pintaala FROM lohkosisalto

INNER JOIN fyysinenlohko ON (lohkosisalto.FyysinenLohko_lohkoid = fyysinenlohko.lohkoid)

WHERE lohkosisalto.lohkonro = @lohkonro) AS lohkoHeinakustannus

FROM

(

/* Saattaisi olla useampaa heinäsiemenerää samalla lohkolla,

joten lasketaan ostohinnoista keskiarvot per kilo. Ei kovin tyylikästi itseasiassa. */

SELECT DISTINCT

  heina.tarve, AVG(heinatarvike.ostohinta / heinatarvike.ostomaara) AS ka

FROM lohkosisalto_has_heinatarvike

  INNER JOIN heinatarvike ON (lohkosisalto_has_heinatarvike.HeinaTarvike_ostoid = heinatarvike.ostoid)

  INNER JOIN heina ON (heinatarvike.Heina_nimi = heina.nimi)

WHERE

  lohkosisalto_has_heinatarvike.LohkoSisalto_lohkonro = @lohkonro) AS heinadata

);

Kutakin kyseisellä lohkolla olevaa heinäpaalia kohden kustannus saataisiin selville näin:

/* heinakustannusPerPaali */

/* Jaetaan lohkon heinäkustannukset lohkolla olevien paalien määrällä. */

 

SET @heinakustannusPerPaali = @heinakustannusPerLohko /

(SELECT COUNT(paalikoodi) FROM paali WHERE paali.LohkoSisalto_lohkonro = @lohkonro);

Heinänsiementen aiheuttama lisähinta myyntipaaleilla:

/* heinäkustannukset */

 

SELECT (COUNT(myyntipaali.Paali_paalikoodi) * @heinakustannusPerPaali) AS heinakustannukset

FROM myyntipaali

  INNER JOIN paali ON (myyntipaali.Paali_paalikoodi = paali.paalikoodi)

WHERE

  myyntipaali.Myynti_myyntiid = @myyntiid AND

  paali.LohkoSisalto_lohkonro = @lohkonro;

Hapotusaineiden aiheuttama lisähinta selviäisi sekin helposti:

/* hapotusaineKustannus */

/* Lukuarvo 200 viittaa hapotusaineen tarpeeseen per 200 kg paalia. */

 

SELECT

  SUM((hapotusainetarvike.ostohinta / hapotusainetarvike.ostomaara) * hapotusaine.tarve * (paali.paino/200)) AS hapotusainekustannus

FROM

  myyntipaali

  INNER JOIN paali ON (myyntipaali.Paali_paalikoodi = paali.paalikoodi)

  INNER JOIN hapotuspaali ON (paali.paalikoodi = hapotuspaali.Paali_paalikoodi)

  INNER JOIN hapotusainetarvike ON (hapotuspaali.HapotusaineTarvike_ostoid = hapotusainetarvike.ostoid)

  INNER JOIN hapotusaine ON (hapotusainetarvike.Hapotusaine_nimi = hapotusaine.nimi)

WHERE

  myyntipaali.Myynti_myyntiid = @myyntiid;

Asiakkaan ostamien heinäpaalien hinta:

/* Paalien kokonaishinta */

 

SELECT

  SUM(hinta.kilohinta * paali.paino) AS kokonaishinta,

  hinta.Paalityyppi_paalityyppi

FROM

  myyntipaali

  INNER JOIN paali ON (myyntipaali.Paali_paalikoodi = paali.paalikoodi)

  INNER JOIN hinta ON (myyntipaali.Hinta_hintanro = hinta.hintanro)

WHERE

  myyntipaali.Myynti_myyntiid = @myyntiid

GROUP BY

  hinta.Paalityyppi_paalityyppi; 

Tarkkaavaisempi saattaisi tulkita, sinänsä aivan oikein, että heinäpaaleilla on ns. hatusta vetäisty hinta, jonka lopulliseen määrään vaikuttaa tuotantomateriaalien hinnan vaihtelu.

 

5  Tietokannan takaisinmallinnus (bonusvaihe I)

Kokeilin takaisinmallintaa (en. reverse engineering) toteutetun tietokannan malliksi eräisiin muihiin tarjolla oleviin mallinnusohjelmiin, jotka tarjosivat automaattitoiminnon tätä tarkoitusta varten. Järemmän sarjan ohjelmistojen hinnat alkaisivat tuhansista euroista, mutta nämä ovat saatavilla alle sadalla eurolla per ohjelma. Jokainen niistä pystyy latomaan tietokannan taulut relaatioineen ja monilukuisuuksineen ruudulle asemiinsa siirreltäviksi, mutta useimmissa niistä ei ole kunnollista automaattiasettelijaa. Toisaalta, kaikki kokeillut ohjelmat saivat poimituiksi sarakkeet ja viiteavaimet aivan oikein.

 

DbVisualizer 6.5.9

 

SQLyog 8.2

 

Aqua Data Studio 8.0.7

 

Power*Architect 0.9.14

 

 

Toad Data Modeler 3.4.16.7

 

6  Tietokantakonversioita (bonusvaihe II)

Saatuani "heinämies"-tietokannan toteutuksen vietyä siihen vaiheeseen, jossa sen looginen rakenne oli stabilisoitunut ja siihen talletetusta testisisällöstä saattoi tehdä monimutkaisia ja tarkoituksenmukaisesti toimivia hakuja, halusin kokeilla minkälaisia seikkoja joutuisi ottamaan huomioon, jos konvertoisin MySQL-pohjaisen toteutuksen muille SQL:lää hyödyntäville tietokannoille.

Valitsin kohdetietokannoiksi PostgreSQL:län (8.4), IBM DB2:n (9.7, personal) ja Oracle Database 11g:n. Aikaa kolmeen eri konversioon kului suunnilleen yhden tehdastyöpäivän tuntien verran, joka sisälsi palvelimien lataamiset internetistä, sekä niiden asentamisen käyttökuntoon. Kaikki konversiot onnistuivat, mutta tulevia tarpeita ennakoiden oli hyvä saada tuntumaa siitä minkälaisten seikkojen kohdalla jo tälläisen perustietokannan konvertoimisessa voi ilmetä hidasteita tai pähkäilyn tarvetta.

Olin oppinut pitämään SQL Maestro Groupin tuotteista, joten katsastin ensi alkuun heidän sovellustarjontansa. Heiltä löytyy jokaiselle valitsemalleni kolmelle tietokannalle räätälöity sovellus, josta löytyy toiminto "data pump".

Hivenen monimutkaisesti sanottuna "data pump" -toiminto ottaa Microsoftin ADO:n (ActiveX Data Objects) Connections-objektilla yhteyden MySQL-tietokantaan yhtenäisen yhteyskäytännön mahdollistavan OLE DB:n (Object Linking and Embedding, Database) kautta, käytännössä MSDASQL:n (Microsoft OLE DB Provider for ODBC) kautta. ODBC-ajuri on tietokantakohtainen ja se täytyy käydä lataamassa ja asentamassa MySQL:n verkkosivuilta: http://dev.mysql.com/downloads/connector/odbc/

Kohdetietokantaa varten ei tässä tapauksessa tarvinnut asentaa erillisiä ajureita. Konvertoijana toimiva sovellusohjelma suorittaa noudetulle tiedolle konversion kohdetietokannalle sopivaan muotoon – ainakin teoriassa. Sovellus tuottaa logit konversiosta, sekä kirjoittaa halutessa taulujen luomis- ja sisällön lisäys -käskyt tiedostoon. Näistä tiedostoista oli hyötyä siinä vaiheessa, kun konversio epäonnistui ja jouduin käyttämään leikkaa ja liimaa -tekniikkaa syöttääkseni tietoa kohdetietokantaan manuaalisesti SQL-lauseina. Kookkaamman tietokannan kohdalla tämä ei olisi kovin käypä vaihtoehto.

Tietokantani ei tuolloisessa vaiheessaan sisältänyt mitään erityisviritelmiä kuten triggereitä (proseduuritkin käytössä vain testitiedon syöttämisen apuna); se koostui tavallisista tauluista, tavallisine tietotyyppeineen (DECIMAL, CHAR, VARCHAR, INT, TIMESTAMP), tavallisine viiteavaimineen. Tästä huolimatta konversiot ontuivat jokaisen tietokannan kohdalla.

Osa konvertumattomuuksista johtui siitä, että kohdetietokanta ei yksinkertaisesti tukenut jotain toiminnetta, kuten esim. Oracle ei tue viiteavaimissa ON UPDATE -määritettä "cascade" (pitäisi toteuttaa triggereillä). Heikkona suoriutumisena taasen voi pitää sitä, että jos kohdetietokanta ei tue samanlaista yksilöllisten numeroiden generointia kuin MySQL:n AUTO_INCREMENT, niin voisihan sovellus kuitenkin sen tehdä, että se luo tilalle joko SEQUENCEn (PostgreSQL ja Oracle Database) tai IDENTITYn (IBM DB2). Ei tehnyt.

CREATE TABLE asiakas (
    asiakasid int AUTO_INCREMENT NOT NULL,

CREATE SEQUENCE asiakasidseq

    MINVALUE 1

    START WITH 1

    INCREMENT BY 1

    CACHE 20;

create table asiakas (

    asiakasid integer primary key generated by default AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),

Jostain syystä yhteenkään kolmesta tietokannasta ei VARCHAR-tietotyyppi (vaihtelevan mittainen merkkijono) konvertoitunut esim. muodossa VARCHAR2 (Oracle), vaan aina joko CHAR- tai CHARACTER-tyyppisenä (määrätyn pituinen merkkijono, käyttämätön merkkitila täytetään välilyönneillä).

DB2:n kohdalla saatoin viitata tauluun viittaamalla siihen sen skeeman kautta (esim. HEINAMIES.MYYNTI), mutta Oraclen tietokannan kohdalla meni tovi aikaa ennen kuin ymmärsin, että siinä viittaus pitää kehystää lainausmerkeillä (esim. "HEINAMIES"."MYYNTI"). Kaikki SQL Maestro Groupin Oracle-tietokannalle tarkoitetut sovellukset käyttävät tätä kirjoittaessa lainausmerkitöntä viittaustapaa, minkä seurauksena suora konversio on tuomittu epäonnistumaan. En tällä erää käynyt tutustumaan onko lainausmerkkikäytäntö tullut mukaan vasta esim. Oracle Database 11g:ssä, mutta ainakin kyseisen tietokannan web-pohjainen hallintaohjelmisto käyttää myös kaikissa omissa taulujen kyselyissään lainausmerkillistä tapaa.

PostgreSQL-konversio sujui erinomaisen sulavasti, eikä minun tarvinnut sen osalta tehdä mitään muuta kuin asettaa SEQUENCEt automaattisten uniikkien numeroiden generoimiseksi. IBM:n DB2:n kohdalla puutteena oli vastaavanlainen IDENTITYjen määrittämättömyys.

Jostain syystä DB2-konvertterisovelluksen tuottamasta SQL-scriptistä puuttuivat jokaiselta PRIMARY KEYksi aseteteltu sarakkeelta NOT NULL -määritys, vaikka se lähdetaulussa vielä oli. Tämän vuoksi automaattinen DB2:een konversio sai aikaiseksi lähinnä sen, että se loi taulut, joille ei oltu asetettu viiteavaimia. Lähes kaiken (loput taulut, taulujen sisältö ja viiteavainten asetus) tein manuaalisesti. Tosin, luotuun SQL-skriptitiedostoon sisältyivät kyllä kaikki viiteavainten, taulujen ja indeksien määritykset, joten niitä ei tarvinnut itse kirjoitella.

Jostain syystä DB2-konvertteri ei saanut irrotetuksi paali-taulusta kaikkia sen rivejä (muutama tuhat), joten minun täytyi exportata nämä tiedot MySQL-kannasta SQL-skriptiksi ja korvata siitä rivien päättymismerkit ";" merkillä "@", sekä lisätä taulujen nimen eteen viittaus skeemaan (INSERT INTO HEINAMIES.TAULU). Lisäksi DECIMAL-tietotyyppi muuntui DOUBLE PRECISION -tietotyyppiseksi, mitkä sitten itse muutin desimaalipilkulliseksi NUMBER-tietotyypiksi. Etsi ja korvaa -toiminto, sekä leikepöytä olivat varsin paljon käytössä.

Näiden ongelmien lisäksi ei tule enää mieleeni kuin yksi ongelma ja se kohdistuu Oracle Databaselle konvertoimiseen. Kaikki ne INSERT-lauseet, joissa sarakkeen sisältönä oli TIMESTAMP-tyyppistä tietoa, piti muuttaa sellaiseksi, että tietokanta annetun merkkijonon TIMESTAMP-tyyppiseksi ymmärtää. Tämän voisi toteuttaa käärimällä aikaa kuvaavan merkkijonon sopivaan funktioon tai sitten tekemällä kuten viisaammat internetissä kertoivat eli laittamalla eteen sanan TIMESTAMP eli esimerkiksi näin: " TIMESTAMP '2009-12-29 05:56:03'".

Eräs vältettävissä oleva Oracle Database -kohtainen ongelma, on sen tietokannassa käytettyjen identifioivien nimikkeiden (esim. sarakkaiden nimet ja viiteavaimet) pituuksien rajoitus (max 30 merkkiä). Koska työnkulku alkoi tulla tutuksi, tein MySQL Workbenchissa heinamies-tietokannasta version, jossa tämä rajoitus oli otettu huomioon. Tämä mahdollisti sen, että konversion ensin tyssätessä edellä mainittuun lainausmerkkiongelmaan, saattoi siinä ohessa luotua skriptitiedostoa käyttää, parin etsi ja korvaa -toiminnon jälkeen, taulujen, indeksien ja viiteavainten luomiseen yhdessä ajossa.

Päivän päätteeksi olin saavuttanut sen vaiheen, jossa kaikki kolme kohdetietokantaa muodostivat takaisinmallintaessa (en. reverse engineering) identtisen oloisen, visuaalisen, tauluista muodostuneen verkoston, kuten tämä DB2-tietokannasta tuotettu tässä:

 

7  Lähteet

Hovi Ari, Huotari Jouni, Lahdenmäki Tapio 2005. Tietokantojen suunnittelu & indeksointi. Porvoo: WSOY