Piibel ja SQL

Kuidas ma SQL serveriga Piiblit lugesin

Töö ja õpingutega seoses oli mul tarvis ja on ka edaspidi vaja Piiblit lugeda. Ja mitte lihtsalt vaid pisut ka loetut analüüsida. Piinlik tunnistada, et aega süstemaatiliselt ja mõttega järjepanu lugeda on vähevõitu – mõtlesin siis, et kasutan oma oskust ja vahendeid ja teen omal pisut lihtsamaks.

Töövahendid, mis mulle appi tulid olid – SQL Server, Excel ja PowerQuery ning PowerShell.

Aga et kõik ilusasti ära rääkida ja üles tunnistada – alustame algusest.

Piibel Veebis

Kõigepealt oli hea meel leida Piibliseltsi tõlkeversioon veebist – http://piibel.net – täielikul ja täiesti loetaval kujul. Tõsi ta on, siin on vaid eestikeelne versioon – on terve hulk veebilehti, kust saaks heebrea-, kreeka-, ladina-, saksa- ja ingliskeelseid tõlkeid. Nende analüüsimise ja koosvaatlemise jätan järgmiseks korraks.

PiibelDB2

Head sõbrad (kuulsin, et veebiversiooni hoitakse üleval entusiasmist) on veebilehe ehitanud väga hästi ja ülevaatlikult ja mis kõige toredam – lisaks inim-loetavale ka nn masin-loetaval kujul. Lisaks HTML-lehtedele ka XML-formaadis – vaja oli vaid selgeks teha veebiaadresside süsteem ja see oma huvides tööle panna.

PiibelDB3

Aadresside süsteem on iseenesest lihtne:

http://piibel.net/#q=Ps%208 – päring ’PS 8’ viitab Psalmid peatükk 8

http://piibel.net/#q=Ps%208:7 – päring ’PS 8:7’ viitab sama peatüki 7 värsile

http://piibel.net/.xml?q=Ps%208 – sama peatükk XML-formaadis

http://piibel.net/.xml?q=Ps%208:7 – sama värss XML-formaadis

Nüüd oleks vaid vaja koostada Piibli raamatute (ja lühendite) loetelu ning leida iga raamatu peatükkide arv.

Piibel Excelisse

Raamatute loendiga läks hõlpsasti – see on veebilehel täiesti olemas – ripploend soovitava raamatu leidmiseks. Kuidas seda sealt aga kätte saada?

PiibelDB1

Kirjeldan lihtsat sammhaaval teekonda:

  1. Piiblilehel vajutan F12 – avaneb arendaja keskkond. Ära ehmu!
  2. Leiad DOM Exploreri nupu ja seejärel viin kursori ripploendile. Klõps ja alumine sigrimigri (loe HTML kood) keritakse õigesse kohta.
  3. Loendile vastav element kokku, paremklõpsates pihku (Copy käsk – CTRL-C ei toiminud) ja sealt (Paste) Excelisse

PiibelDB4

Nüüd on loend Excelis ja vaja tabeliks vormida. Hea on, kui sul on Excelile (ver 2013) lisatud PowerQuery – otsi ja paigalda, ei maksa midagi aga rõõmu kui palju.

Edasi peab teadma, mida teha

  1. PowerQuery ribalt valin From Table – defineerin oma XMLiga pesa tabeliks, ütlen et päist mul ei ole – ta paneb ise.
  2. Seejärel otsin päringu editori teisenduste (transform) ribalt käsu PARSE from XML. Tulemuseks tabel ühe veeru ja ühe reaga.
  3. Kaks korda laiendan tabelit ja ongi käes raamatute loend. Selle laen Excelisse

Nüüd tuleks tabelit veidi täiendada – tabelis on raamatute nimed koos lühendiga („Esimene Moosese raamat(1Ms)“). Sellest tuleks teha kaks veergu – raamatu nimi ja lühend. Selle lahutamise oleks võinud ju ka PowerQueris teha, sama saaks ka paari valemiga. Seekord olin laisk:

  1. Lisasin veeru nimega Raamat, kirjutasin selle esimesele reale „Esimene Moosese raamat“. Teisele reale sain vaevu kirjutada „T“ ja juba Excel pakkus, et teeb selle minu eest lõpuni.
  2. Lisasin veel ühe veeru nimega Lühend, kirjutasin selle esimesele reale „1Ms“ ja teisel real piisas kirjutada „2“ – taas Excel teadis, kuidas lõpuni teha.

Viimase sammuna tuli tabelisse saada iga raamatu peatükkide arv – seekord jätan saladuseks, kuidas ma seda tegin, aga valmis ta sain (tegelikult lugesin peatükid käsitsi üle – võtsin ette iga raamatu esimese peatüki ja sellelt liikusin eelmisele, mis oligi eelmise raamatu viimane peatükk).

Piibel andmebaasi

Nüüd tuleks siis piiblisalmid lugeda arvutisse ja sealt andmebaasi nii et edasi võtsin ette SQL Management Studio ja ehitasin omale andmebaasi:

CREATE DATABASE Piibel
go
USE Piibel
go
CREATE TABLE dbo.Raamatud    -- siia tabelisse raamatute loetelu
(
	kood int IDENTITY(1,1) PRIMARY KEY,
	Lühend char(5) NULL,
	Nimetus nvarchar(100) NULL,
	Peatükke int NULL,
)

CREATE TABLE dbo.testxml   -- siia tabelisse Piibli peatükid
(
	kood int IDENTITY(1,1) PRIMARY KEY,
	filedata xml NULL,
)

Ning lõpuks protseduuri, mis kõik veebist loetud (see tuleb veel teha) failid loeb andmebaasi:

USE [Piibel]
go
CREATE proc [dbo].[LaePiibel]
as

declare @x nvarchar(max) ;

with numbrid as

	(select ROW_NUMBER() over (order by r1.kood) n from raamatud r1, raamatud r2)

select @x =
(
	select
	' insert testxml (filedata) SELECT convert(xml, bulkcolumn, 2) FROM OPENROWSET ( BULK ''c:\piibel\' + rtrim(r.Lühend) + '-' + rtrim(cast (n.n as char(3))) + '.xml'', SINGLE_BLOB) as T (bulkcolumn); '

	from raamatud r
	join numbrid n on n.n <= r.Peatükke
	order by r.kood, n
	for xml path('')
);

/* -- seda rida saab kasutada testimiseks (lisa -- rea algusse)
select
-- */ execute
(@x)

Ära küsi kuidas, aga see protseduur toimib. Enne tuleb jahh luua kaust (minul siis c:\piibel) ja lugeda sinna veebist XML failid

PowerShelliga Piiblit lugema

PowerShellis alustasin sellest, et kõigepealt tegin C-kettale kausta c:\Piibel

Seejärel koostasin funktsiooni get-piibel, millele antakse ette kaks parameetrit – raamatu lühend (veebiaadressis vajalik) ja peatükkide arv:

function get-piibel ($raamat, $peatükke )
{

1..$peatükke | ForEach-Object {
	$source = "http://piibel.net/.xml?q=" + $raamat + "%20" + $_.ToString() + "&bv=EST_97&bv=EST_68"
	$destination = "c:\piibel\" + $raamat + "-" + $_.ToString() + ".xml"

	Invoke-WebRequest $source -OutFile $destination

	}
}

Need viimased kaks parameetrit veebiaadressi lõppu tagavad, et alla laetakse kaks erinevat tõlkeversiooni – hiljem hea võrrelda.

Ja nüüd jälle minu laiskus – Exceli tabelis lisandub uus veerg PShell valemiga:

="get-piibel " & [@Lühend] & " " & [@Peatükke]

Veerg kätte (Ctrl-tühik, Ctrl-C) ja PowerShelli aknasse (Ctrl-V) ning käima – piibel hakkab laaduma mu arvutisse ja aeg on pisut hinge tõmmata – võtab pisut aega.

Kuna seesama tabel (raamatunimi, lühend jms) tuleks kanda ka SQL-i, siis samamoodi kui veerg PSchell tekitasin ma veeru sql valemiga:

 
="insert Raamatud (Nimetus, lühend, peatükke) values (N'"&ConcatRange(Table1_2[@[Nimetus]:[Peatükke]];"', N'";TRUE)&"')"

Selles valemis see ConcatRange on mu oma selleks otstarbeks tehtud funktsioon – nii saab hõlpsasti kokku panna suvalise pikkusega insert-lauseid, et siis seal va SQL Manageris kasutada – ikka jälle veerg kätte (Ctrl-tühik, Ctrl-C) ja siis Management Studios lahti (Ctrl-V) ja käima (F5)

ConcatRange funktsioon ise on selline – huviline saab kasutada:

Public Function ConcatRange(r As Range, Delimiter As String, SQL As Boolean) As String

Dim x As Range
Dim s As String
s = ""

For Each x In r.Cells
	s = s & IIf(SQL, Replace(x.Value, "'", "''"), x.Value) & Delimiter
Next

ConcatRange = Left(s, Len(s) - Len(Delimiter))

End Function

Nüüd peaks olema XML failid mul arvutis ja raamatute tabel andmebaasis – saab käivitada varem tehtud protseduuri LaePiibel ning naasta andmebaasi poolele.

Tagasi andmebaasis

Kuna raamatute tabel on nüüd laetud ja piiblifailid samuti – vaatame, mis nendega edasi teha. Tegu on XML kirjetega ja neist tuleks andmed välja lugeda – et see toimiks kiiremini, teeme mõned XML-indeksid. See kiirendab oluliselt XML-kirjete töötlemist ja hilisemad päringukomponendid (XQUERY) teisendatakse lihtsateks relatsioonilisteks komponentideks. XML indeksid ei ole muud kui lahti lammutatud XML hoituna relatsioonilisena.

CREATE PRIMARY XML INDEX xml0 ON dbo.testxml(filedata)
go
CREATE XML INDEX xml1 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR PATH
CREATE XML INDEX xml2 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR VALUE
CREATE XML INDEX xml3 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR PROPERTY

Järgmisena teeme mõned päringud ja salvestame nad view-dena

View ‘värsid’ lubab mul lugeda välja kõik raamatute värsid, neid filtreerida raamatute, tõlkeversiooni ja peatükkide kaupa:

CREATE view [dbo].[värsid]
with schemabinding
as

select

	xx.value('./@id', 'int') nr
	, xx.value('./@heading', 'nvarchar(100)') Pealkiri
	, xx.value('.', 'nvarchar(max)') Värss
	, xx.value('../@id', 'int') Peatükk
	, xx.value('../../@title', 'nvarchar(200)') Raamat
	, xx.value('../../@abbrev', 'nvarchar(200)') Lühend
	, xx.value('../../../@title', 'nvarchar(200)') Piibel
	, right(xx.value('../../../@title', 'nvarchar(200)'),4) PiibliAasta
from dbo.testxml Q
cross apply Q.filedata.nodes('//verse') as T2(xx)

Teine pisut keerukam, lubab mul kahte tõlkeversiooni omavahel kõrvutada:

create view [dbo].[võrdlus]
as

select

	v.Raamat, v.Peatükk, v.nr
	, max(case when piibliaasta = 1968 then v.Värss end) Piibel68
	, max(case when piibliaasta = 1997 then v.Värss end) Piibel97
from värsid v
group by
	v.Raamat, v.Peatükk, v.nr

Nüüd tahaks veel mõned päringud teha, mis aitaks otsida kindlaid nimesid või termineid erinevaist raamatuist. LIKE-operaator on siin pisut paha – esiteks liialt aeglane ja teiseks juba tüütu on erinevaid käändeid ja sõnavorme päringusse kirjutada. Heal meelel kasutaks täistekst indeksit, aga XML tabelile (XML indeksitele) on seda üsna tülikas seadistada (vist ei saagi päris korrektselt). Seepärast koostasin tabeli, kuhu salvestan VIEW ’Värsid’ tulemuse. Kuna täistekst index eeldab tabelil unikaalset indeksit, see peaks olema mõistlikus järjestuses, siis selle tabeli tekitamiseks panin kokku päringu:

select *, identity(integer, 1,1) reanr
into t_värsid
from värsid;
go
create unique clustered index x0 on t_värsid (reanr)

Nüüd saab tabelile lisada juba täistekst indeksid ja teha täistekst predikaatidega päringuid

CREATE FULLTEXT CATALOG [FTCatalog] WITH ACCENT_SENSITIVITY = ON AS DEFAULT;
go
CREATE FULLTEXT INDEX ON dbo.t_värsid(Värss) KEY INDEX x0;

Nüüd saab küsida kõikvõimalke toredaid küsimusi kus CONTAINS-predikaadid, nagu:

select
--top 100
* 
from t_värsid v 
join raamatud r on v.Lühend = r.Lühend

where 1 = 1 -- see tingimus on, et saaks teisi sõltumatult välja kommenteerida

and contains (värss, '"ing*"')
--and contains(värss, '"saatan*"')
--and contains(värss, '"kurat*" or "kurad*"')
--and contains(värss, '"deemon*"')
--and contains(värss, '"jumala lapsed" or "jumala pojad"')
--and contains(värss, 'Jophiel')

and PiibliAasta = 1997

order by r.kood, v.Peatükk, v.nr

Lõpetuseks

Kes nüüd luges ja kaasa mõtles ja proovis, sai ehk endalegi samasuguse andmebaasi, kes aga lihtsalt luges ja tahab proovida, saab omale andmebaasi alla laadida ja paigaldada (RESTORE) aadressilt:

http://bit.ly/PiibelDB

Sinna panen kas mõned SQL scriptid ja Exceli tabeli piibliraamatutega.

This entry was posted in Excel, MS SQL Server, Piibel, PowerShell and tagged , , , . Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s