From e561163f93c9f7a3d2aea49543b5b9599e54ea51 Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
Date: Thu, 6 Apr 2017 01:53:44 +0200
Subject: Documentation tips + example config for players histories

---
 docs/building.md      | 18 ++++++++++
 docs/db-structure.sql | 95 +++++++++++++++++++++++++++++++++++++++++++++++++++
 docs/useful-sql.md    | 21 ++++++++++++
 3 files changed, 134 insertions(+)
 create mode 100644 docs/building.md
 create mode 100644 docs/db-structure.sql
 create mode 100644 docs/useful-sql.md

(limited to 'docs')

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
+);
+```
-- 
cgit v1.2.3