summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--dates.json3
-rw-r--r--docs/building.md18
-rw-r--r--docs/db-structure.sql95
-rw-r--r--docs/useful-sql.md21
4 files changed, 137 insertions, 0 deletions
diff --git a/dates.json b/dates.json
new file mode 100644
index 0000000..e3fbcd5
--- /dev/null
+++ b/dates.json
@@ -0,0 +1,3 @@
+{
+ "2016-12-31": ""
+}
diff --git a/docs/building.md b/docs/building.md
new file mode 100644
index 0000000..32de2d4
--- /dev/null
+++ b/docs/building.md
@@ -0,0 +1,18 @@
+To build initial ranking:
+
+```
+python ranking.py DATE > http/FILENAME.html
+```
+
+To build subsequent rankings:
+
+```
+python ranking.py DATE PREVIOUS_DATE > http/FILENAME.html
+```
+
+To build players' pages:
+```
+python players.py http/players/ [DATES_CONFIG]
+```
+
+Name, surname and club are always used from the current `players` table. Regions, genders and age categories are read per-ranking.
diff --git a/docs/db-structure.sql b/docs/db-structure.sql
new file mode 100644
index 0000000..7310b80
--- /dev/null
+++ b/docs/db-structure.sql
@@ -0,0 +1,95 @@
+-- --------------------------------------------------------
+
+--
+-- Struktura tabeli dla tabeli `players`
+--
+-- Obraz CSV z Cezara, jeden-do-jednego.
+-- Do tej tabeli można (i należy) importować CSV z Cezara.
+-- W PHPMyAdminie:
+-- * wybrać import z CSV
+-- * zaznaczyć aktualizację rekordu w przypadku duplikatu klucza głównego
+-- * wybrać separator pól - średnik
+--
+
+CREATE TABLE `players` (
+ `id` int(11) NOT NULL,
+ `name` varchar(50) COLLATE utf8_unicode_520_ci NOT NULL,
+ `surname` varchar(50) COLLATE utf8_unicode_520_ci NOT NULL,
+ `rank` decimal(3,1) NOT NULL,
+ `region` varchar(2) COLLATE utf8_unicode_520_ci NOT NULL,
+ `flags` varchar(2) COLLATE utf8_unicode_520_ci NOT NULL,
+ `club` varchar(100) COLLATE utf8_unicode_520_ci NOT NULL,
+ `discount` varchar(1) COLLATE utf8_unicode_520_ci NOT NULL,
+ `paid` varchar(1) COLLATE utf8_unicode_520_ci NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabeli dla tabeli `rankings`
+--
+-- Tabela źródłowa dla generatorów tabelek.
+--
+
+CREATE TABLE `rankings` (
+ `pid` int(11) NOT NULL,
+ `date` date NOT NULL,
+ `place` int(11) NOT NULL,
+ `score` decimal(6,2) NOT NULL,
+ `region` varchar(2) COLLATE utf8_unicode_520_ci NOT NULL,
+ `flags` varchar(2) COLLATE utf8_unicode_520_ci NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabeli dla tabeli `temp_rankings`
+--
+-- Tabela, do której można importować ranking bez potrzeby wypełniania
+-- w CSV źródłowej województw, płci i wieku.
+--
+
+CREATE TABLE `temp_rankings` (
+ `pid` int(11) NOT NULL,
+ `date` date NOT NULL,
+ `place` int(11) NOT NULL,
+ `score` decimal(6,2) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci;
+
+--
+-- Indeksy dla zrzutów tabel
+--
+
+--
+-- Indexes for table `players`
+--
+ALTER TABLE `players`
+ ADD PRIMARY KEY (`id`);
+
+--
+-- Indexes for table `rankings`
+--
+ALTER TABLE `rankings`
+ ADD PRIMARY KEY (`pid`,`date`);
+
+--
+-- Indexes for table `temp_rankings`
+--
+ALTER TABLE `temp_rankings`
+ ADD PRIMARY KEY (`pid`,`date`);
+
+--
+-- Ograniczenia dla zrzutów tabel
+--
+
+--
+-- Ograniczenia dla tabeli `rankings`
+--
+ALTER TABLE `rankings`
+ ADD CONSTRAINT `rankings_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `players` (`id`) ON UPDATE CASCADE;
+
+--
+-- Ograniczenia dla tabeli `temp_rankings`
+--
+ALTER TABLE `temp_rankings`
+ ADD CONSTRAINT `temp_rankings_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `players` (`id`) ON UPDATE CASCADE;
diff --git a/docs/useful-sql.md b/docs/useful-sql.md
new file mode 100644
index 0000000..32a8f5b
--- /dev/null
+++ b/docs/useful-sql.md
@@ -0,0 +1,21 @@
+Update categories from current `players` table:
+
+```
+UPDATE rankings
+JOIN players
+ ON players.id = rankings.pid
+SET rankings.region = players.region,
+ rankings.flags = players.flags
+WHERE rankings.date = '#DATE#';
+```
+
+After importing ranking CSV to `temp_rankings`, copy to `rankings` with current categories:
+
+```
+INSERT INTO rankings (
+ SELECT pid, `date`, place, score, region, flags \
+ FROM temp_rankings
+ JOIN players
+ ON players.id = temp_rankings.pid
+);
+```