start over

3 files changed, 116 insertions, 0 deletions

diff --git a/db/__init__.py b/db/__init__.py
new file mode 100644
index 0000000..ead5232
--- /dev/null
+++ b/db/__init__.py
@@ -0,0 +1,15 @@
+import oursql
+
+from config import db as dbconfig
+
+conn = oursql.connect(host=dbconfig.host, db=dbconfig.database, user=dbconfig.user, passwd=dbconfig.password, autoreconnect=True)
+
+def ykill_cursor():
+	return conn.cursor(oursql.DictCursor)
+
+def execute(cursor, sql, *values):
+	cursor.execute(sql, values)
+
+def query(cursor, sql, *values):
+	execute(cursor, sql, *values)
+	return cursor.fetchall()
diff --git a/db/importer.py b/db/importer.py
new file mode 100755
index 0000000..39f97ce
--- /dev/null
+++ b/db/importer.py
@@ -0,0 +1,42 @@
+#!/usr/bin/env python3
+
+import json
+from pprint import pprint
+
+import __init__ as db
+
+with db.ykill_cursor() as c:
+	db.execute(c, 'INSERT INTO kills (kill_id, solar_system_id, kill_time, moon_id) VALUES(?, ?, ?, ?)',
+			kill['killID'], kill['solarSystemID'], kill['killTime'], kill['moonID'])
+
+	victim = kill['victim']
+	parambatch = [(
+		kill['killID'], 1, victim['characterID'], victim['characterName'], victim['shipTypeID'],
+		victim['allianceID'], victim['allianceName'], victim['corporationID'], victim['corporationName'], victim['factionID'], victim['factionName'],
+		victim['damageTaken'], None, None, None,
+	)]
+	for attacker in kill['attackers']:
+		parambatch.append((
+			kill['killID'], 0, attacker['characterID'], attacker['characterName'], attacker['shipTypeID'],
+			attacker['allianceID'], attacker['allianceName'], attacker['corporationID'], attacker['corporationName'], attacker['factionID'], attacker['factionName'],
+			attacker['damageDone'], attacker['finalBlow'], attacker['securityStatus'], attacker['weaponTypeID'],
+		))
+	c.executemany('''
+		INSERT INTO characters (
+			kill_id, victim, character_id, character_name, ship_type_id,
+			alliance_id, alliance_name, corporation_id, corporation_name, faction_id, faction_name,
+			damage, final_blow, security_status, weapon_type_id
+		) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+		''', parambatch
+	)
+
+	parambatch = []
+	for item in kill['items']:
+		parambatch.append((kill['killID'], item['typeID'], item['flag'],
+			item['qtyDropped'], item['qtyDestroyed'], item['singleton']))
+	c.executemany('''
+		INSERT INTO items (
+			kill_id, type_id, flag, dropped, destroyed, singleton
+		) VALUES(?, ?, ?, ?, ?, ?)
+		''', parambatch
+	)
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..7c601ce
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,59 @@
+DROP TABLE IF EXISTS `characters`;
+DROP TABLE IF EXISTS `items`;
+DROP TABLE IF EXISTS `item_costs`;
+DROP TABLE IF EXISTS `kill_costs`;
+DROP TABLE IF EXISTS `kills`;
+
+CREATE TABLE `kills` (
+	`kill_id` int unsigned NOT NULL,
+	`solar_system_id` int NOT NULL,
+	`kill_time` datetime NOT NULL,
+	`moon_id` int unsigned NOT NULL,
+	PRIMARY KEY (`kill_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+
+CREATE TABLE `characters` (
+	`id` int unsigned NOT NULL AUTO_INCREMENT,
+	`kill_id` int unsigned NOT NULL,
+	`victim` tinyint(1) NOT NULL,
+	`character_id` int unsigned NOT NULL,
+	`character_name` varchar(64) NOT NULL,
+	`ship_type_id` int NOT NULL,
+	`alliance_id` int unsigned NOT NULL,
+	`alliance_name` varchar(64) NOT NULL,
+	`corporation_id` int unsigned NOT NULL,
+	`corporation_name` varchar(64) NOT NULL,
+	`faction_id` int NOT NULL,
+	`faction_name` varchar(64) NOT NULL,
+	`damage` int DEFAULT NULL,
+	`final_blow` tinyint(1) DEFAULT NULL,
+	`security_status` float DEFAULT NULL,
+	`weapon_type_id` int DEFAULT NULL,
+	PRIMARY KEY (`id`),
+	CONSTRAINT `fk_char_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+
+CREATE TABLE `items` (
+	`id` int unsigned NOT NULL AUTO_INCREMENT,
+	`kill_id` int unsigned NOT NULL,
+	`type_id` int NOT NULL,
+	`flag` tinyint(3) unsigned NOT NULL,
+	`dropped` int unsigned NOT NULL,
+	`destroyed` int unsigned NOT NULL,
+	`singleton` tinyint(4) NOT NULL,
+	PRIMARY KEY (`id`),
+	CONSTRAINT `fk_item_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+
+CREATE TABLE `item_costs` (
+	`type_id` int NOT NULL UNIQUE,
+	`cost` bigint unsigned NOT NULL,
+	PRIMARY KEY (`type_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+
+CREATE TABLE `kill_costs` (
+	`kill_id` int unsigned NOT NULL UNIQUE,
+	`cost` bigint unsigned NOT NULL,
+	PRIMARY KEY (`kill_id`),
+	CONSTRAINT `fk_kill_cost_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;