#! /usr/bin/perl -w
# společná část programů (struktury MySQL databáze):
# hes-sql-load.pl # načtení databáze z tabulky `import` - německý originál losungen
# hes-sql-transl.pl # převod do češtiny
# version 0.1 – rok 2016
package HesSQL;
use Exporter;
@ISA = qw(Exporter);
@EXPORT_OK = qw($dbh $bib_dbh $transbook $nbook $bookname $bibleverse $import_r $day_cs_r $day_w $dayname_w
$losung_w $reading_w $song_w $comment_w $drittetext_w $day_r $losung_r $reading_r $seq_w);
#use strict; # nezvládnul jsem názvy globálů
use DBI;
#use Symbol;
#qualify ($dbh);
my $year = '16';
my $hes_dbname = "hes$year";
my $bib_dbname = "bible";
my $hostname = 'localhost';
my $hes_dsn = "DBI:mysql:database=$hes_dbname; host=$hostname";
my $bib_dsn = "DBI:mysql:database=$bib_dbname; host=$hostname";
my $user = '******';
my $password = '************';
$dbh = DBI->connect($hes_dsn, $user, $password, { RaiseError=>1,AutoCommit=>0 })
|| die "!!Chyba připojení k databázi hesel $hes_dbname č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0
$bib_dbh = DBI->connect($bib_dsn, $user, $password, { RaiseError=>1,AutoCommit=>0 })
|| die "!!Chyba připojení k biblické databázi $bib_dbname č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0
$dbh->do("SET NAMES `utf8`");
$bib_dbh->do("SET NAMES `utf8`");
### BIBLE ###
# převod německého názvu knihy z Losungen na český, používaný v Heslech
$transbook = $bib_dbh->prepare("SELECT `name` FROM `book` WHERE `trans`='hes' AND b IN
(SELECT `b` FROM `book` WHERE `trans`='los' AND `name`=?)");
#číslo knihy podle jejího názvu v Losungen
$nbook = $bib_dbh->prepare("SELECT `b` FROM `book` WHERE `trans`='los' AND `name`=?");
#název knihy podle čísla
$bookname = $bib_dbh->prepare("SELECT `name` FROM `book` WHERE `b`=? AND `trans`=? AND `long`=?");
# biblický verš
$bibleverse = $bib_dbh->prepare("SELECT `text` FROM `verse` WHERE `id`=?");
### HESLA ###
$import_r = $dbh->prepare("SELECT * FROM `import` ORDER BY `Datum`");
# table day_cs: text_de, sel, date, which, text, meaning, src
$day_cs_r = $dbh->prepare("SELECT `which` FROM `day_cs` WHERE `text_de`=?");
# table day: sel, date, seq, which, nr, lang, text, meaning, src;
# which = sunday, holiday, important, week
$day_w = $dbh->prepare("INSERT INTO day VALUES (?,?,?,?,?,?,?,?,?,?)"); # 10 columns
# table dayname: date, lang, text;
$dayname_w = $dbh->prepare("INSERT INTO dayname VALUES (?,?,?)"); # 3 columns
# table losung: sel, date, seq, which, nr, lang, transl, source, intro, text;
# which = YEAR, MONTH, week, sunday, holiday, important, OT, NT
$losung_w = $dbh->prepare("INSERT INTO losung VALUES (?,?,?,?,?,?,?,?,?,?)"); # 10 columns !!
# table reading: sel, date, seq, which, lang, source; which = SR,CR,WP,Ev,Ep,Ps,Pr,PF,DL,x1,x2,x3
$reading_w = $dbh->prepare("INSERT INTO reading VALUES (?,?,?,?,?,?)"); # 6 columns
# table song: sel, date, seq, which, lang, book, nr, strophe, strophe2, text; which = Ld, WL
$song_w = $dbh->prepare("INSERT INTO song VALUES (?,?,?,?,?,?,?,?,?,?)"); # 10 columns !!
# table comment: sel, date, lang, histdatetxt, text
$comment_w = $dbh->prepare("INSERT INTO comment VALUES (?,?,?,?,?)"); # 5 columns
# table drittetext: sel, date, lang, source, song, author, text
$drittetext_w = $dbh->prepare("INSERT INTO drittetext VALUES (?,?,?,?,?,?,?)");# 7 columns
##
$day_r = $dbh->prepare
("SELECT D.date, D.dow, D.seq, D.which, D.nr, D.text, C.sel, C.text, C.meaning, C.src FROM `day` as D
LEFT JOIN `day_cs` as C ON D.text = C.text_de
WHERE D.lang='de' ORDER BY `date`,`which`, `seq`"); # naše preferované pořadí
$losung_r = $dbh->prepare("SELECT * FROM `losung` WHERE `lang`='de' ORDER BY `date`,`which`");
$reading_r = $dbh->prepare("SELECT * FROM `reading` WHERE `lang`='de' ORDER BY `date`,`which`");
# $song_r =$dbh->prepare("SELECT * FROM `song` WHERE `lang`='de' ORDER BY `date`,`which`");
# $comment_r =$dbh->prepare("SELECT * FROM `comment` WHERE `lang`='de' ORDER BY `date`,`which`");
# $drittetext_r=$dbh->prepare("SELECT * FROM `drittetext` WHERE `lang`='de' ORDER BY `date`,`which`");
# table seq: ori, cs # změna pořadí u cs
$seq_w = $dbh->prepare("INSERT INTO seq VALUES (?,?)"); # 2 columns
1;
