Projekt: Hesla Jednoty bratrské/2016/HesSQL.pm

HesSQL.pm

#! /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;