Bible: MySQL: Nasypání bible do databáze MySQL.

V minulém kursu jsme si vyzkoušeli, jak vytvořit databázi biblických veršů v textovém formátu. Dnes tyto verše "nasypeme" do relační databáze. Jako příklad použijeme dnes asi nejrozšířenější a nejdostupnější databázový stroj MySQL. Neměl by být problém implementovat stejný postup např. pro PostgreSQL či jiné relační databáze. U objektových databází bude situace jiná, těm by se měl věnovat jiný článek.

Struktura

Na prvním místě musíme navrhnout vhodnou strukturu databáze. Struktura relační databáze se odvíjí od návrhů jednotlivých tabulek o jejich vzájemných vazeb. Do jisté míry může tato struktura bude modelovat situaci struktury reálného světa.

V zásadě jsou možné dva přístupy:

promyšlený
živelný

Při promyšleném návrhu musíme mít poměrně přesnou představu o tom, co všechno bude naše databáze obsahovat a co od ní budeme očekávat. Existuje množství různých dobrých rad a postupů a vědeckých pojednání, týkajících se různých stupňů a metod tzv. normalizace databáze. Upozorňuje se na to, že špatně navržená databáze nám dříve či později velmi zkomplikuje život.

S tím nechceme na tomto místě polemizovat, pouze chceme odejmout pejorativní nádech slovu "živelný". Živelný ještě nemusí znamenat unáhlený, překotný, zbrklý, nesmyslný, bezmyšlenkovitý. Tomu se chceme vyhnout. Možná namísto "živelný" použít "životný" a nebo "takový, jak jde život", "otevřený".

Někdy se může stát, že návrhem databáze strávíme příliš mnoho času, a pak zjistíme, že jsme stejně na něco zapomněli, a naše složitá konstrukce může jít do háje. Proto je dobré zachovávat určitou míru otevřenosti, a i když třeba některé vlastnosti nechceme hned implementovat, je dobré si takové možnosti uvědomit a nezavírat před nimi dveře. Slova "živelný" a "promyšlený" si potom nemusí nutně odporovat, ale bude se jednat o vícerozměrný přístup. Je možné se naučit vytvářet databázové struktury "za pochodu", aniž by se nám zhroutila celá stavba v případě, že bude nutné v naší struktuře provádět úpravy a změny.

V případě bible je potřeba si uvědomit její strukturu. Víme, že bible je sestává ze Starého a Nového zákona, případně Apokryfů, a každá tato část je souborem mnoha knih, které si dělíme na kapitoly, a ty zase na jednotlivé verše, a ještě z těch můžeme vybírat určité části.

V případě, že bychom konstruovali objektovou databázi či databázi ve formátu XML, pravděpodobně by odpovídal náš databázový model tomuto členění.

V případě relační databáze, jakou je i MySQL, je základem všeho tabulka, jejíž struktura je vyjádřena počtem, typem a dalšími atributy svých sloupců. Do takto vytvořené prázdné tabulky je potom možné vkládat jednotlivé záznamy jakožto řádky. (Dalšími kroky je pak např. vytváření vazeb čili relací mezi jednotlivými tabulkami).

Teď je zapotřebí rozhodnout, jaká bude "základní organizační jednotka" v naší biblické databázi, na kterou budeme chtít hledat a na jakou se budeme odkazovat. Můžeme si zvolit např. ten nejnižší element celé struktury, za který budeme považovat jedno slovo textu. Takový postup má jistě své zdůvodnění např. při budování konkordance, která je seznamem všech slov a odkazů na místa, kde se jaké slovo vyskytuje v bibli. Přestavme si ale např. to, že bychom měli tuto databázi naplnit daty. Data máme uložena v textovém souboru, kde každý řádek odpovídá jednomu verši (viz Bible: Databáze‎). Museli bychom vytvořit program, který celý text rozbije na jednotlivá slova, ta ukládat do tabulky jednotlivých slov spolu s určením, kde se to které slovo vyskytuje. Možná by to mohla být idální koncepce při tvorbě konkordance, ale opětné skládání celé bible z jednotlivých slov by mohlo být těžkopádné. Dalším problémem by mohla být obtížnost ladění takového programu: kde hledat případnou chybu, když se z výstupu začne sypat hromada nahodilých slov? Je chyba už v načtení vstupních dat, anebo je chyba jinde?

Při hledání optimální struktury nehledíme pouze na strukturu reálné předlohy (v našem případě papírové bible, kterou máme otevřenu na stole), ale i na strukturu těch dat, která máme k disposici a která nám tuto reálnou předlohu prostředkují. Jiná by mohla být i situace, kdybychom teprve celou bibli přepisovali z vytištěné knihy do počítače: Tam by nám tabulka vyskytujících se slov mohla pomoci např. při kontrole pravopisu.

Pokud ale máme k disposici takovou strukturu vstupních dat, že je textový soubor členěn na řádky, které odpovídají jednotlivým veršům, bude nejpřirozenější cestou vytvořit tabulku, kde řádky v tabulce budou odpovídat řádkům vstupního souboru, tedy opět jednotlivým veršům. Proto bychom tabulku mohli nazvat třeba "verše". Aby se v naší databázi vyznali i uživatelé, kteří nehovoří česky, nazveme ji "verse" (tj. anglicky "verš").

Každý řádek takové tabulky bude obsahovat jeden biblický verš. K tomu, abychom jej mohli identifikovat a později vyhledat potřebujeme nějaký jednoznačný identifikátor, v databázové hantýrce řečeno "index" neboli "klíč". Takový máme i na začátku každého řádku v textovém souboru. Naše tabulka verse může mít pro začátek dva sloupce: id jako identifikátor a "text" jako text verše.

Slova jako "verse", "id", "text" chápeme jako označení či jméno tabulky či jméno pro sloupce. V reálném světě píšeme jména lidí, zvířat a věcí (měst, řek, hor, ...) s velkými počátečními písmeny a tak bývá zvykem i zde psát jména tabulek a sloupců s velkými písmeny na začátku (alespoň někteří autoři to tak doporučují). Záleží na nás, jak si je zvykneme psát. Některé verze jazyka SQL ale nerozlišují mezi malými a velkými písmeny, proto je lépe: psát to alespoň všude stejně a vyhnout se slovům, která by se lišila pouze velikostí písmen.

Ještě je zapotřebí správně nadimenzovat velikost sloupce text. Jaký je nejdelší řádek v naší textové databázi? Zjistíme např. jednoduchým prográmkem maxlenght.pl, který si napíšeme v jazyce Perl:

#! /usr/bin/perl -w

my $maxlength = 0;
my $maxline = '';

while (<>) {
  if (length > $maxlength) {
     $maxlength = length;
     $maxline = $_;     
  }
}

print "Maxlength = $maxlength\n";
print "$maxline";

Příkaz

./maxlenght.pl cs_kra

nám pak dá odpověď:

Maxlength = 405 17/8:9 Takž svolali písaře královské v ten čas měsíce třetího, jenž jest měsíc Siban, dvadcátého třetího dne téhož měsíce, a psáno jest všecko tak, jakž přikázal Mardocheus, k Židům a knížatům, i vývodám a hejtmanům krajin, kteréž jsou od Indie až do země Mouřenínské, sto dvadceti sedm krajin, do každé krajiny písmem jejím, každému národu jazykem jeho, též i Židům písmem jejich a jazykem jejich.

Ale pozor! Abychom zjistili maximální délky v kódování UTF-8, musíme použít:

#! /usr/bin/perl -w

use Encode;

my $maxlength = 0;
my $maxline = '';

while (<>) {
  if (length(Encode::encode_utf8($_)) > $maxlength) {
     $maxlength = length;
     $maxline = $_;     
  }
}

print "Maxlength = $maxlength\n";
print "$maxline";

a pak dostaneme výsledek:

Maxlength = 449

Vytvoříme si tedy (připadně necháme si vytvořit administrátorem databázového systému) databázi, kterou nazveme např. bible, přihlásíme se k ní prostřednictvím řádkového klienta mysql a v databázi vytvoříme tabulku verse s uvedenou strukturou asi takto:

USE `bible`;
CREATE `verse` (
  `id` varchar(20) NOT NULL,
  `text` varchar(500),
  PRIMARY KEY  (`id`)
);

Kromě řádkového klienta mysql můžeme použít např. webové rozhraní phpMyAdmin, potom se ale hůře popisuje, kde na co "kliknout". Proto předpokládáme, že ti, kteří jsou zvyklí raději "klikat" než psát, si intuitivně najdou, na co kliknout, a nám se budou lépe psát provedené příkazy v jazyce MySQL.

Vstup dat

Načíst data z textového souboru do databáze lze vícero způsoby, např. řádkovým příkazem mysqlimport anebo SQL příkazem LOAD DATA INFILE. My si k tomu účelu vytvoříme krátký prográmek ve skriptovacím jazyce Perl, ve kterém si ukážeme způsob manipulace s MySQL databází z jiných programových prostředí. Tento vstup dat má navíc schopnost účinnější kontroly vstupních dat a ošetření případných chyb.

#! /usr/bin/perl -w

# bib_txt2sql.pl        převede biblické texty do MySQL databáze

use DBI;
use strict;

my $dbname      = 'bible';
my $hostname    = 'localhost';
my $dsn         = "DBI:mysql:database=$dbname; host=$hostname";
my $user        = 'mujlogin';
my $password    = 'mojeheslo';

my $dbh = DBI->connect($dsn, $user, $password, { RaiseError=>1,AutoCommit=>0 })
  || die "Chyba připojení k databázi č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0

my $sth = $dbh->prepare("INSERT INTO verse VALUES (?,?)");

my $line = 0;
my $in_header=1;        # Na začátku je header, který se přeskakuje až do doby, kdy je prvním znakem na řádku číslice

while (<>) {
   my($id_ori, $verse);   # originální identifikátor, text verše
   $line++;
   if ($in_header) {
       if (/^\d/) { $in_header = 0;}    # první číslice na začátku řádky: skončil header
       else { next;}
   }
   chomp;               # odstraní znak NewLine na konci řádky
   / +/;                # možný oddělovač: obyčejná mezera - první výskyt
   $id_ori = $`;
   $verse = $';
   if (! $verse) { die "prázdný verš, $line\n";}
   $verse =~ s/ +$//g;          # mezery na konci pryč

   if ($id_ori =~ m|(\d+)/(\d+):(\d+)|) {       # originální identifikátor ve tvaru 1/2:33
       $b = $1; $ch = $2; $v = $3;
   }
   else { die "Chybný identifikátor ---$id_ori--- line $line\n";}  # Chyba - ukončí se program

   $sth->execute($transl, $id_ori);     # vloží záznam do databáze 
}

$sth->finish;
$dbh->disconnect;

Různé tabulky se od sebe zpravidla liší různou strukturou. Představíme si, že budeme chtít v naší databázi mít i jiné překaldy bible nebo a bible v různých jazycích.

(... doplnit další úvahy...)

Konečná struktura tabulky tedy bude:

CREATE TABLE  `verse` (
  `trans` varchar(3) NOT NULL,
  `id` varchar(20) NOT NULL,
  `b` smallint(6) NOT NULL,
  `ch` smallint(6) NOT NULL,
  `v` mediumint(9) NOT NULL,
  `text` varchar(500) NOT NULL,
  PRIMARY KEY  (`id`)
);

Vstup dat - jako argument se uvede zkratka překladu, třeba kra nebo cep:

#! /usr/bin/perl -w

# bib_txt2sql.pl        převede biblické texty do MySQL databáze

use DBI;
use strict;

my $dbname      = 'bible';
my $hostname    = 'localhost';
my $dsn         = "DBI:mysql:database=$dbname; host=$hostname"; #
my $user        = 'mujlogin';
my $password    = 'mojeheslo';

my $transl = '';
if (!($transl = shift @ARGV)) { die "First argument must be the abbrev. of the translation!\n";}

my $dbh = DBI->connect($dsn, $user, $password, { RaiseError=>1,AutoCommit=>0 })
  || die "Chyba připojení k databázi č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0

#$dbh->do("SET NAMES `utf8`");
$dbh->do("SET NAMES `latin2`");

my $sth = $dbh->prepare("INSERT INTO verse VALUES (?,?,?,?,?,?)");

my $line = 0;
my $in_header=1;        # Na začátku je header, který se přeskakuje

while (<>) {
   my($id_ori, $verse, $b, $ch, $v );   # originální identifikátor, text verše, číslo knihy, kapitoly, verše 
   $line++;
   if ($in_header) {
       if (/^\d/) { $in_header = 0;}    # první číslice na začátku řádky: skončil header
       else { next;}
   }
   chomp;
   / +/;                # možný oddělovač: obyčejná mezera - první výskyt
   $id_ori = $`;
   $verse = $';
   if (! $verse) { die "prázdný verš, $line\n";}
   $verse =~ s/ +$//;          # mezery na konci pryč
   $verse =~ s/ +/ /;          # víc mezer redukovat na jednu

   if ($id_ori =~ m|(\d+)/(\d+):(\d+)|) {       # originální identifikátor ve tvaru 1/2:33
       $b = $1; $ch = $2; $v = $3;
   }
   else { die "Chybný identifikátor ---$id_ori--- line $line\n";}
   if ($transl eq 'kra') {$id_ori .= ' K';}     # identifikace kraličky

   $sth->execute($transl, $id_ori, $b, $ch, $v, $verse); 
   if($ch==1 && $v==1) {print "$b:$ch,$v\n";}          # Zobrazí progresi po knihách
}

$sth->finish;
$dbh->disconnect;

Dimenzování polí

2010-03-04

Nejdelší verš najdeme prográmkem:

#! /usr/bin/perl -w
# maxlenght.pl

my $maxlength = 0;
my $maxline = '';
 
while (<>) {
    if (length > $maxlength) {
	$maxlength = length;
	$maxline = $_;     
    }
}
 
print "Maxlength = $maxlength\n";
print "$maxline";

Zkusíme vyhledat nejdelší verš Kraličky:

$ ./maxlenght.pl cs_kra_utf8

Maxlength = 459
17/8:9   Takž svolali písaře královské v ten čas měsíce třetího, jenž jest měsíc Siban, dvadcátého třetího dne téhož měsíce, a psáno jest všecko tak, jakž přikázal Mardocheus, k Židům a knížatům, i vývodám a hejtmanům krajin, kteréž jsou od Indie až do země Mouřenínské, sto dvadceti sedm krajin, do  každé krajiny písmem jejím, každému národu jazykem jeho, též i Židům  písmem jejich a jazykem jejich.

Což je Ester 8,9 K.

Takže stačí nadimenzovat textové pole MySQL databáze na délku VARCHAR(500).

Ale pozor! Pokud je kódování unicode, může být problém, neboť řetěce unicodových znaků jsou při uložení databázi delší, než je počet znaků. Úprava prográmku:

#! /usr/bin/perl -w

use Encode;
 
my $maxlength = 0;
my $maxline = '';
 
while (<>) {
    if (length(Encode::encode_utf8($_)) > $maxlength) {
	$maxlength = length;
	$maxline = $_;     
    }
}
 
print "Maxlength = $maxlength\n";
print "$maxline";

Jen nechápu, že mi z tohoto výstupu vylezl jiný, poměrně krátký verš:

$ ./maxlenghtu.pl cs_kra_utf8

Maxlength = 287
9/9:24  Když tedy přinesl kuchař plece i s tím, což se ho přídrželo, položil Samuel před Saule, a řekl: Teď, což pozůstalo, vezmi sobě, jez; až k této chvíli zajisté zachováno jest to pro tebe, jakž jsem řekl: Lidu jsem pozval. I jedl Saul s Samuelem v ten den.

Zaplnění databáze

Verse:

překlad	veršů
kra	31 172
cep	31 170
celkem	62342

Tabulka knih

2010-03-05

Ještě potřebujeme vygenerovat tabulku biblických knih, aby bylo jasné, jaké číslo knihy odpovídá jaké knize. Problém je, že různí překlady používají různé názvy knih. Proto by tabulka měla obsahovat názvy knih různých překladů a kromě toho i zkratky, kterých se používá.

Použijeme tabulku biblických knih, kterou jsme používali v Projekt: Hesla Jednoty bratrské/2010, viz Projekt: Hesla Jednoty bratrské/kni.tab.

Do biblické databáze pak přidáme další tabulku:

--
-- Struktura tabulky `book`
--

CREATE TABLE IF NOT EXISTS `book` (
  `b` tinyint(2) NOT NULL,
  `trans` varchar(3) collate utf8_czech_ci NOT NULL,
  `long` tinyint(1) NOT NULL,
  `name` varchar(25) collate utf8_czech_ci NOT NULL,
  PRIMARY KEY  (`b`,`trans`,`long`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Názvy biblických knih';

A zde je skript, který data z textové tabulky hes10kni.tab uloží do databázové tabulky book

#! /usr/bin/perl -w
 
# bib_book2sql.pl      převede tabulku biblických knih knitab do MySQL databáze
 
use DBI;
use strict;
 
my $dbname      = 'bible';
my $hostname    = 'localhost';
my $dsn         = "DBI:mysql:database=$dbname; host=$hostname"; #
my $user        = 'mujlogin';
my $password    = 'mojeheslo';
 
 
my $dbh = DBI->connect($dsn, $user, $password, { RaiseError=>1,AutoCommit=>0 })
    || die "Chyba připojení k databázi č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0
 
$dbh->do("SET NAMES `utf8`");
#$dbh->do("SET NAMES `latin2`");

my $sth = $dbh->prepare("INSERT INTO `book` VALUES (?,?,?,?)");	# b, trans, long, name
my $ceph = $dbh->prepare("SELECT `name` FROM `book` WHERE `trans`='cep' AND `long`=0 AND `b`=?");
my $xmlh = $dbh->prepare("SELECT `name` FROM `book` WHERE `trans`='xml' AND `long`=0 AND `b`=?");


sub knitab {		# načte data z knitab
  open(KNITAB, "hes10kni.tab") || die  "Nemuzu otevrit seznam knih 'hes10kni.tab' $!";

  while(<KNITAB>){
    next if /\#/;
    chomp;
    s/\x09/ /g;            # Tabelatory pryc
    s/ +/ /g;
    my ($b, $odd, $cep, $hes, $xml, $los) = split(/ /);
    #$zkr =~ tr/ÁÉĚÍÓÚŮÝŽŠČŘĎŤŇáéěíóúůýžščřďťň/AEEIOUUYZSCRDTNaeeiouuyzscrdtn/;
    #$zkr =~ tr/A-Z/a-z/;
    last if $b > 66;				# jen do Zjevení
    print STDERR "$b\n"; 
    $sth->execute($b, 'cep', '0', $cep);		# Český ekumenický - zkratka
    $sth->execute($b, 'hes', '1', $hes);		# Hesla - jak to dosud píšeme (dlouze)
    $sth->execute($b, 'xml', '0', $xml);		# anglická zkratka, používaná v losung.xml
    $sth->execute($b, 'los', '1', $los);		# dlouhý název, používaný v originále Losungen
  }
  close KNITAB;
}

sub bnames_cs {		# načte názvy z ICASI
  open(BNAMES, "bnames.cs") || die "Neotevřu bnames $!\n";
  while(<BNAMES>){
  next if(/^$/ || /^#/);
  chomp;
  /="(.*)"/ or die "divný řádek $_\n";
  my($key,$value) = ($`, $1);
  #print "$key : '$value'\n";
  for($key){
    if   (/bname_(\d+)/)	{;}	# {print "$1 = '$value'\n"; $sth->execute($1, 'cep', '1', $value); }
    elsif(/short_(\d+)a/)	{
        #print "$1 = '$value'\n";
        $ceph->execute($1);
        my($cep) = $ceph->fetchrow_array;
        print("$1\t$cep\t$value");
        if($cep ne $value){print "\t!!!";}
        print "\n";
    }
    elsif(/short_(\d+)b/)	{;}
    elsif(/short_(\d+)c/)	{;}
    else {die "divný klíč $_\n";}
    }
  }
  close BNAMES;
}

sub bnames_en {		# načte názvy z ICASI
  open(BNAMES, "bnames.en") || die "Neotevřu bnames $!\n";
  while(<BNAMES>){
  next if(/^$/ || /^#/);
  chomp;
  /="(.*)"/ or die "divný řádek $_\n";
  my($key,$value) = ($`, $1);
  #print "$key : '$value'\n";
  for($key){
    if   (/bname_(\d+)/)	{;}	# {print "$1 = '$value'\n"; $sth->execute($1, 'cep', '1', $value); }
    elsif(/short_(\d+)a/)	{
        #print "$1 = '$value'\n";
        $xmlh->execute($1);
        my($xm) = $xmlh->fetchrow_array;
        print("$1\t$xm\t$value");
        if($xm ne $value){print "\t!!!";}
        print "\n";
    }
    elsif(/short_(\d+)b/)	{;}
    elsif(/short_(\d+)c/)	{;}
    else {die "divný klíč $_\n";}
    }
  }
  close BNAMES;
}


#knitab();
#bnames_cs();
bnames_en();

$dbh->disconnect;

Související články

Předcházející článek: Bible: Databáze