From ddc20f684c75e060a642f4e0481bea32aab53d65 Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
Date: Wed, 7 Jun 2017 02:24:51 +0200
Subject: SQL import scripts for CSV data
---
.gitattributes | 2 +-
bin/import-cezar-db.sh | 9 +++++++++
bin/import-rankings.sh | 16 ++++++++++++++++
config/import-db.json | Bin 0 -> 165 bytes
4 files changed, 26 insertions(+), 1 deletion(-)
create mode 100755 bin/import-cezar-db.sh
create mode 100755 bin/import-rankings.sh
create mode 100644 config/import-db.json
diff --git a/.gitattributes b/.gitattributes
index 17366f1..3419812 100644
--- a/.gitattributes
+++ b/.gitattributes
@@ -1 +1 @@
-config/db.json filter=git-crypt diff=git-crypt
+config/*db.json filter=git-crypt diff=git-crypt
diff --git a/bin/import-cezar-db.sh b/bin/import-cezar-db.sh
new file mode 100755
index 0000000..4db5261
--- /dev/null
+++ b/bin/import-cezar-db.sh
@@ -0,0 +1,9 @@
+#!/bin/bash
+DBCONFIG=($(jq -r '.[]' config/import-db.json))
+mysql -h ${DBCONFIG[2]} -P ${DBCONFIG[3]} -u ${DBCONFIG[0]} --password=${DBCONFIG[1]} -e "SET foreign_key_checks = 0;
+LOAD DATA LOCAL INFILE '$1'
+REPLACE
+INTO TABLE players
+FIELDS TERMINATED BY ';' ENCLOSED BY '\"';
+SET foreign_key_checks = 1
+" ${DBCONFIG[4]}
diff --git a/bin/import-rankings.sh b/bin/import-rankings.sh
new file mode 100755
index 0000000..d73af64
--- /dev/null
+++ b/bin/import-rankings.sh
@@ -0,0 +1,16 @@
+#!/bin/bash
+DBCONFIG=($(jq -r '.[]' config/import-db.json))
+mysql -h ${DBCONFIG[2]} -P ${DBCONFIG[3]} -u ${DBCONFIG[0]} --password=${DBCONFIG[1]} -e "SET foreign_key_checks = 0;
+DELETE FROM temp_rankings;
+LOAD DATA LOCAL INFILE '$1'
+REPLACE
+INTO TABLE temp_rankings
+FIELDS TERMINATED BY ',';
+INSERT INTO rankings (
+ SELECT pid, \`date\`, place, score, region, flags
+ FROM temp_rankings
+ JOIN players
+ ON players.id = temp_rankings.pid
+);
+SET foreign_key_checks = 1
+" ${DBCONFIG[4]}
diff --git a/config/import-db.json b/config/import-db.json
new file mode 100644
index 0000000..6e34fa0
Binary files /dev/null and b/config/import-db.json differ
--
cgit v1.2.3