fix collation for search and importer api-only

also, importer whitespace

3 files changed, 54 insertions, 96 deletions

diff --git a/db/importer.py b/db/importer.py
deleted file mode 100755
index 4f8f4b4..0000000
--- a/db/importer.py
+++ /dev/null
@@ -1,42 +0,0 @@
-#!/usr/bin/env python3
-
-import json
-from pprint import pprint
-
-import __init__ as db
-
-with db.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
index 7c601ce..3c632a9 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -10,7 +10,7 @@ CREATE TABLE `kills` (
 	`kill_time` datetime NOT NULL,
 	`moon_id` int unsigned NOT NULL,
 	PRIMARY KEY (`kill_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 
 CREATE TABLE `characters` (
 	`id` int unsigned NOT NULL AUTO_INCREMENT,
@@ -31,7 +31,7 @@ CREATE TABLE `characters` (
 	`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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 
 CREATE TABLE `items` (
 	`id` int unsigned NOT NULL AUTO_INCREMENT,
@@ -43,17 +43,17 @@ CREATE TABLE `items` (
 	`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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 
 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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 
 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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
diff --git a/importer.py b/importer.py
index 44c95dc..a6d6232 100755
--- a/importer.py
+++ b/importer.py
@@ -8,59 +8,59 @@ from pprint import pprint
 import db
 
 def insert_kill(c, kill):
-    db.execute(c, 'INSERT INTO kills (kill_id, solar_system_id, kill_time, moon_id) VALUES(?, ?, ?, ?)',
-                    kill['killID'], kill['solarSystemID'], kill['killTime'], kill['moonID'])
+	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
-    )
+	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
-    )
+	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
+	)
 
-    db.conn.commit()
+	db.conn.commit()
 
 def main():
-    conn = HTTPSConnection('zkillboard.com', timeout=10)
-    with db.ykill_cursor() as c:
-        groups = db.query(c, 'SELECT groupID FROM eve.invGroups WHERE categoryID = ?', 6)
-        groups = list(map(operator.itemgetter('groupID'), groups))
-        for i in range(0, len(groups), 10):
-            query_groups = map(str, groups[i:i+10])
-            conn.request('GET', '/api/groupID/{}/api-only'.format(','.join(query_groups)))
-            response = conn.getresponse()
-            if response.status != 200:
-                raise Exception('got {} {} from zkb'.format(response.status, response.reason))
-            kills = json.loads(response.read().decode('utf-8'))
-            response.close()
-            print('inserting', len(kills), 'kills')
-            for kill in kills:
-                insert_kill(c, kill)
+	conn = HTTPSConnection('zkillboard.com', timeout=10)
+	with db.cursor() as c:
+		groups = db.query(c, 'SELECT groupID FROM eve.invGroups WHERE categoryID = ?', 6)
+		groups = list(map(operator.itemgetter('groupID'), groups))
+		for i in range(0, len(groups), 10):
+			query_groups = map(str, groups[i:i+10])
+			conn.request('GET', '/api/losses/api-only/groupID/{}'.format(','.join(query_groups)))
+			response = conn.getresponse()
+			if response.status != 200:
+				raise Exception('got {} {} from zkb'.format(response.status, response.reason))
+			kills = json.loads(response.read().decode('utf-8'))
+			response.close()
+			print('inserting', len(kills), 'kills')
+			for kill in kills:
+				insert_kill(c, kill)
 
 if __name__ == '__main__':
-    main()
+	main()