Database dump pull script (S3 -> MySQL)

9 files changed, 255 insertions, 0 deletions

diff --git a/.gitattributes b/.gitattributes
index e053f5a..d97aba2 100644
--- a/.gitattributes
+++ b/.gitattributes
@@ -1,2 +1,3 @@
 .envrc filter=git-crypt diff=git-crypt
 .htpasswd filter=git-crypt diff=git-crypt
+dumps/.s3config filter=git-crypt diff=git-crypt
diff --git a/Makefile b/Makefile
index ade5a4c..21024bc 100644
--- a/Makefile
+++ b/Makefile
@@ -6,4 +6,8 @@ pull:
 autocommit: pull
 	@cd http && git diff --quiet || (git add . && git commit -m 'Autocommit' && git push)
 
+dumps: FORCE
+	s3cmd -c dumps/.s3config sync ${LIGA_S3_BUCKET} dumps/sync/
+	./dumps/load-dumps.sh
+
 FORCE:
diff --git a/dumps/.mapping b/dumps/.mapping
new file mode 100644
index 0000000..19a1dc6
--- /dev/null
+++ b/dumps/.mapping
@@ -0,0 +1,6 @@
+dmp202021_e   sync/eliga2021.sql
+dmp202021_1s  sync/tel.sql
+dmp202021_2ne sync/nerr.sql
+dmp202021_2nw sync/20nwrr.sql
+dmp202021_2se sync/serrr.sql
+dmp202021_2sw sync/2swrr.sql
diff --git a/dumps/.queries b/dumps/.queries
new file mode 100644
index 0000000..2b06dea
--- /dev/null
+++ b/dumps/.queries
@@ -0,0 +1,2 @@
+SELECT shortname, rnd, segm FROM admin;
+UPDATE params SET path = "C:\\wwwliga\\";
diff --git a/dumps/.s3config b/dumps/.s3config
new file mode 100644
index 0000000..a6c0e60
--- /dev/null
+++ b/dumps/.s3config
diff --git a/dumps/load-dumps.sh b/dumps/load-dumps.sh
new file mode 100755
index 0000000..7695a40
--- /dev/null
+++ b/dumps/load-dumps.sh
@@ -0,0 +1,17 @@
+#!/bin/bash
+pushd $(dirname $0) >> /dev/null
+cat .mapping | while read DB DUMP
+do
+    grep -v 'CREATE DATABASE' $DUMP | grep -vi '^Use ' | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DB
+    if [ "$DB" != "dmp202021_e" ]
+    then
+        <logoh.sql mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DB
+    else
+        <logoh.en.sql mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DB
+    fi
+    cat .queries | while read -r QUERY
+    do
+        mysql ${LIGA_MYSQL_CONNECTION_OPTS} -N -e "$QUERY" $DB
+    done
+done
+popd >> /dev/null
diff --git a/dumps/logoh.en.sql b/dumps/logoh.en.sql
new file mode 100644
index 0000000..25f4821
--- /dev/null
+++ b/dumps/logoh.en.sql
@@ -0,0 +1,112 @@
+DELETE FROM logoh WHERE id > 0;
+UPDATE logoh SET dane = (SELECT fullname FROM admin) WHERE id = 0;
+INSERT INTO `logoh` (`id`, `dane`) VALUES
+(1, 'c-over'),
+(2, 'PLACE:'),
+(3, 'RESULT:'),
+(4, 'TOTAL:'),
+(5, ' at '),
+(6, 'total points'),
+(7, ' in progress'),
+(8, ' SEGMENT '),
+(9, ' to see travellers of this segment.'),
+(11, ', page generated on '),
+(12, 'PT'),
+(13, 'Board analysis by DeepFinesse'),
+(14, 'imps'),
+(15, ', optimized for 1024x768 resolution.'),
+(16, 'PLEASE NOTE:'),
+(17, ' TABLE '),
+(18, 'ROUND '),
+(19, 'VS'),
+(20, 'Butler results for NS in given room.'),
+(21, 'after round#'),
+(22, 'round#'),
+(23, 'Click table number on the right to see its travellers.'),
+(24, 'Artificial TD&#39;s scores marked ARB, AAA, AMP ... in contract field.'),
+(25, 'Points for not balancing TD&#39;s scores in &#34;TD&#34; column on the right.'),
+(26, 'Penalties/Awards in VP are NOT mentioned here.'),
+(27, 'after '),
+(28, '&nbsp;&nbsp;&nbsp;&nbsp;TD&nbsp;&nbsp;&nbsp;&nbsp;'),
+(29, 'CLOSED'),
+(30, 'OPEN'),
+(31, 'Scores with changed lines marked with *'),
+(33, ' lead '),
+(34, 'brd#'),
+(35, 'butler'),
+(36, 'by'),
+(37, 'contr.'),
+(38, 'lead'),
+(39, 'table'),
+(40, 'tr.'),
+(41, 'VP'),
+(42, 'Team'),
+(44, 'imp/brd'),
+(45, 'opponent'),
+(47, 'reason'),
+(48, 'round'),
+(49, 'score'),
+(50, 'boards'),
+(52, 'team'),
+(53, '&sum; VP'),
+(54, 'vp'),
+(55, 'Name'),
+(56, 'Away'),
+(57, 'Home'),
+(58, 'closed'),
+(59, 'open'),
+(60, 'total'),
+(61, 'Click on team to see its results and members.'),
+(62, 'total imp:'),
+(63, 'Butler'),
+(64, 'Brd#'),
+(65, 'place'),
+(67, 'Info'),
+(68, '# in team'),
+(70, 'CROSSTABLE'),
+(71, 'VP PENALTIES &amp; AWARDS'),
+(72, 'BUTLER, ROUND#'),
+(73, 'datum'),
+(75, 'Click on '),
+(76, 'Click on board number above to see it on the right.'),
+(77, 'Click on round below to see its results.'),
+(78, 'Click round on the left to see its results.'),
+(79, 'GROUP '),
+(80, 'INDIVIDUAL BUTLER CLASSIFICATION'),
+(81, 'Previous rounds:'),
+(83, 'diff'),
+(84, 'BOARD#'),
+(85, 'IMP:'),
+(87, 'PASS'),
+(88, '% of boards'),
+(89, 'PT:'),
+(90, 'players without chances to play at least '),
+(91, 'players without chances to play at least '),
+(92, '% of boards are not listed'),
+(93, 'Makeable tricks:'),
+(94, 'Players'),
+(95, 'LEADERBOARD'),
+(96, 'Total PT'),
+(97, 'STARTING ON'),
+(98, 'VP Table'),
+(99, '-oNS'),
+(100, '-oEW'),
+(101, '-cNS'),
+(102, '-cEW'),
+(103, 'master<br>points'),
+(104, 'congress<br>points'),
+(105, '&nbsp;'),
+(106, 'prize'),
+(107, 'LIST OF PLAYERS'),
+(109, 'Move mouse over contract to see the auction. Move mouse over score to see the play.'),
+(110, 'circle a letter or<br>circle both letters (NS or EW)<br>to use default positions'),
+(111, 'Trick#'),
+(112, 'OPEN ROOM:'),
+(113, 'CLOSED ROOM:'),
+(114, 'HOME TEAM:'),
+(115, 'AWAY TEAM:'),
+(118, 'signed for '),
+(119, 'Documentation for all tables.<br>Use IE only to print it.'),
+(120, 'Jan Romański&#39;2005'),
+(121, ' H'),
+(122, ' A');
diff --git a/dumps/logoh.sql b/dumps/logoh.sql
new file mode 100644
index 0000000..b22fe99
--- /dev/null
+++ b/dumps/logoh.sql
@@ -0,0 +1,112 @@
+DELETE FROM logoh WHERE id > 0;
+UPDATE logoh SET dane = (SELECT fullname FROM admin) WHERE id = 0;
+INSERT INTO `logoh` (`id`, `dane`) VALUES
+(1, 'c-over'),
+(2, 'MIEJSCE:'),
+(3, 'WYNIK:'),
+(4, 'RAZEM:'),
+(5, ' o '),
+(6, 'razem punkty'),
+(7, ' w toku'),
+(8, ' SEGMENT '),
+(9, ' żeby zobaczyć kontrolki tego segmentu.'),
+(11, ', strona wygenerowana '),
+(12, 'PT'),
+(13, 'Analiza rozdań programem DeepFinesse'),
+(14, 'impy'),
+(15, ', optymalizowana dla rozdzielczości 1024x768.'),
+(16, 'UWAGA:'),
+(17, ' STÓŁ '),
+(18, 'RUNDA '),
+(19, 'VS'),
+(20, 'Wyniki Butlera dla pary NS w danym pokoju.'),
+(21, 'po rundzie '),
+(22, 'runda '),
+(23, 'Kliknij numer stołu po prawej żeby obejrzeć kontrolki.'),
+(24, 'Wynik sędziowski jest oznaczony ARB, AAA, AMP ... w polu kontraktu.'),
+(25, 'Punkty za nierównoważące się wyniki sędziowskie w kolumnie &#34;SĘDZIA&#34; po prawej.'),
+(26, 'NIE ma tu kar/wyrównań w VP.'),
+(27, 'po '),
+(28, ' SĘDZIA '),
+(29, 'ZAMKNIĘTY'),
+(30, 'OTWARTY'),
+(31, 'Zapisy na złych liniach oznaczone *'),
+(33, 'wist'),
+(34, 'rozd#'),
+(35, 'butler'),
+(36, 'rg'),
+(37, 'kontr.'),
+(38, 'wist'),
+(39, 'stół'),
+(40, 'lew'),
+(41, 'VP'),
+(42, 'Drużyna'),
+(44, 'imp/rzd'),
+(45, 'przeciwnik'),
+(47, 'uwagi'),
+(48, 'runda'),
+(49, 'zapis'),
+(50, 'roz-<br>dania'),
+(52, 'team'),
+(53, '&sum; VP'),
+(54, 'vp'),
+(55, 'Nazwisko'),
+(56, 'Gość'),
+(57, 'Gosp.'),
+(58, 'zamknięty'),
+(59, 'otwarty'),
+(60, 'razem'),
+(61, 'Kliknij na team żeby zobaczyć jego wyniki i zawodników.'),
+(62, 'razem imp:'),
+(63, 'Butler'),
+(64, 'Rzd#'),
+(65, 'miejsce'),
+(67, 'Info'),
+(68, 'Nr w drużynie'),
+(70, 'KRZYŻÓWKA'),
+(71, 'KARY/WYRÓWNANIA W VP'),
+(72, 'BUTLER, RUNDA '),
+(73, 'średnia'),
+(75, 'Kliknij na '),
+(76, 'Kliknij numer rozdania powyżej, żeby je obejrzeć po prawej.'),
+(77, 'Kliknij na rundę poniżej, żeby zobaczyć jej wyniki.'),
+(78, 'Kliknij na rundę po lewej, żeby zobaczyć jej wyniki.'),
+(79, 'GRUPA '),
+(80, 'INDYWIDUALNA KLASYFIKACJA BUTLERA'),
+(81, 'Poprzednia runda:'),
+(83, 'różnica'),
+(84, 'ROZDANIE#'),
+(85, 'IMP:'),
+(87, 'PAS'),
+(88, '% rozdań'),
+(89, 'PT:'),
+(90, 'zawodnicy, którzy nie mają szans na rozegranie '),
+(91, 'bez zawodników, którzy nie mają szans na rozegranie '),
+(92, '% rozdań'),
+(93, 'Maksymalna liczba lew:'),
+(94, 'zawodnicy'),
+(95, 'WYNIKI'),
+(96, 'Razem PT'),
+(97, 'POCZĄTEK:'),
+(98, 'Tabela VP'),
+(99, '-oNS'),
+(100, '-oEW'),
+(101, '-zNS'),
+(102, '-zEW'),
+(103, 'PKL'),
+(104, 'PDF'),
+(105, '&nbsp;'),
+(106, 'NAGRODA'),
+(107, 'LISTA ZAWODNIKÓW'),
+(109, 'Przesuń mysz nad kontrakt, żeby zobaczyć licytację. Przesuń mysz nad zapis, żeby prześledzić rozgrywkę.'),
+(110, 'zakreśl właściwą literę lub<br>zakreśl obie litery (NS lub EW)<br>żeby użyć domyślnych pozycji'),
+(111, 'Lewa#'),
+(112, 'POKÓJ OTWARTY:'),
+(113, 'POKÓJ ZAMKNIĘTY:'),
+(114, 'GOSPODARZ:'),
+(115, 'GOŚĆ:'),
+(118, 'podpisano za '),
+(119, 'Dokumentacja ze wszystkich stółów.<br>Ewentualny wydruk tylko w Internet Explorerze.'),
+(120, 'Jan Romański&#39;2005'),
+(121, 'gosp'),
+(122, 'gość');
diff --git a/dumps/sync/.gitignore b/dumps/sync/.gitignore
new file mode 100644
index 0000000..d1b811b
--- /dev/null
+++ b/dumps/sync/.gitignore
@@ -0,0 +1 @@
+*.sql