diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/queries.py | 17 | ||||
| -rw-r--r-- | db/schema.sql | 8 |
2 files changed, 20 insertions, 5 deletions
diff --git a/db/queries.py b/db/queries.py index 7ad56ff..a770667 100644 --- a/db/queries.py +++ b/db/queries.py @@ -3,12 +3,21 @@ import operator import db def search(q): + like_str = '{}%'.format(q) with db.cursor() as c: + alliances = db.query(c, ''' + SELECT DISTINCT alliance_id, alliance_name FROM characters + WHERE alliance_name LIKE ? LIMIT 25 + ''', like_str) corps = db.query(c, ''' SELECT DISTINCT corporation_id, corporation_name FROM characters - WHERE corporation_name LIKE ? - ''', '%{}%'.format(q)) - return {'corporations': corps} + WHERE corporation_name LIKE ? LIMIT 25 + ''', like_str) + chars = db.query(c, ''' + SELECT DISTINCT character_id, character_name FROM characters + WHERE character_name LIKE ? LIMIT 25 + ''', like_str) + return {'alliances': alliances, 'corporations': corps, 'characters': chars} def corporation(corp_id): with db.cursor() as c: @@ -20,7 +29,7 @@ def corporation(corp_id): JOIN kill_costs ON kill_costs.kill_id = kills.kill_id JOIN eve.mapSolarSystems ON solar_system_id = solarSystemID JOIN eve.mapRegions ON mapSolarSystems.regionID = mapRegions.regionID - WHERE corporation_id = ? + WHERE corporation_id = ? LIMIT 100 ''', corp_id) kill_ids = list(map(operator.itemgetter('kill_id'), kills)) char_rows = db.query(c, ''' diff --git a/db/schema.sql b/db/schema.sql index 3c632a9..af21bf5 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -30,7 +30,13 @@ CREATE TABLE `characters` ( `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`) + CONSTRAINT `fk_char_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`), + INDEX `character_id` (`character_id`), + INDEX `character_name` (`character_name`), + INDEX `alliance_id` (`alliance_id`), + INDEX `alliance_name` (`alliance_name`), + INDEX `corporation_id` (`corporation_id`), + INDEX `corporation_name` (`corporation_name`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; CREATE TABLE `items` ( |