SWDB/install_base.py

#!/usr/bin/python3

import urllib3
import sys
import os
import requests
import urllib.request
import sqlite3
import wget
import json

OPT_DOWNLOAD = True;
OPT_DOWNLOAD_OVERWRITE = False;

"""
Initializes the database file and creates all the tables.

:param name: The path to the sqlite database.
:returns: Connection to the database.
:raises IntegrityError: The queryes couldn't bre executed.
:raises IOError: The sqlite file couldn't be created.
"""
def createDatabase(name):
    global OPT_CREATE_DB
    print('Configuring database...')
    try:
        db = sqlite3.connect(name)
        cursor = db.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_building(
                id INT,
                com2us_id INT,
                area TEXT,
                affected_stat TEXT,
                element TEXT,
                name TEXT,
                description TEXT,
                max_level INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_building_level(
                building INT,
                level INT,
                bonus INT,
                cost INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_effect(
                id INT,
                name TEXT,
                is_buff INT,
                description TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_skill_level(
                skill INT,
                level INT,
                description TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_skill(
                id INT,
                com2us_id TEXT,
                name TEXT,
                description TEXT,
                slot INT,
                cooltime INT,
                hits INT,
                passive INT,
                aoe INT,
                max_level INT,
                multiplier_formula TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_skill_effect(
                skill INT,
                effect INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_leader_skill(
                id INT,
                attribute TEXT,
                amount INT,
                area TEXT,
                element TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_source(
                id INT,
                name TEXT,
                description TEXT,
                farmable_source INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_monster(
                id INT,
                com2us_id TEXT,
                family_id TEXT,
                name TEXT,
                element TEXT,
                archetype TEXT,
                base_stars INT,
                natural_stars INT,
                obtainable INT,
                can_awaken INT,
                awaken_bonus TEXT,
                skill_ups_to_max INT,
                leader_skill INT,
                base_hp INT,
                base_attack INT,
                base_defense INT,
                speed INT,
                crit_rate INT,
                crit_damage INT,
                resistance INT,
                accuracy INT,
                raw_hp INT,
                raw_attack INT,
                raw_defense INT,
                max_lvl_hp INT,
                max_lvl_attack INT,
                max_lvl_defense INT,
                awakens_from INT,
                awakens_to INT,
                fusion_food INT,
                homunculus INT,
                craft_cost INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_monster_skill(
                monster INT,
                skill INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_monster_source(
                monster INT,
                source INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_monster_essence(
                monster INT,
                element TEXT,
                grade TEXT,
                amount TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_fusion(
                id INT,
                product INT,
                stars INT,
                cost INT,
                ingredient INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_craft(
                id INT,
                name INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS monster(
                id TEXT,
                com2us_id TEXT,
                monster INT,
                stars INT,
                level INT,
                skill_1_level INT,
                skill_2_level INT,
                skill_3_level INT,
                skill_4_level INT,
                base_hp INT,
                base_attack INT,
                base_defense INT,
                base_speed INT,
                base_crit_rate INT,
                base_crit_damage INT,
                base_resistance INT,
                base_accuracy INT,
                rune_hp INT,
                rune_attack INT,
                rune_defense INT,
                rune_speed INT,
                rune_crit_rate INT,
                rune_crit_damage INT,
                rune_resistance INT,
                rune_accuracy INT,
                avg_rune_efficiency INT,
                fodder INT,
                in_storage INT,
                ignore_for_fusion INT,
                priority INT,
                notes INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rune(
                id TEXT,
                com2us_id TEXT,
                assigned_to TEXT,
                type TEXT,
                slot INT,
                stars INT,
                level INT,
                quality INT,
                original_quality INT,
                value INT,
                substat_upgrades_remaining INT,
                efficiency INT,
                max_efficiency INT,
                main_stat TEXT,
                main_stat_value INT,
                innate_stat TEXT,
                innate_stat_value INT,
                substat_1 TEXT,
                substat_1_value INT,
                substat_1_craft INT,
                substat_2 TEXT,
                substat_2_value INT,
                substat_2_craft INT,
                substat_3 TEXT,
                substat_3_value INT,
                substat_3_craft INT,
                substat_4 TEXT,
                substat_4_value INT,
                substat_4_craft INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS monster_rune(
                monster TEXT,
                rune INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS building(
                building INT,
                level INT
            );
        ''')
        db.commit()
        cursor.close()
        return db
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + str(e))
        raise
    except IOError as e:
        print("I/O Error creating database " + name + ": " + str(e))
        raise


"""
Inserts a row into the database.

:param db: Connection to the database.
:param table: Name of the table to insert into.
:param values: List of values to insert.
:raises IntegrityError: The insert query was unsuccesfull.
"""
def insert(db, table, values):
    cursor = db.cursor()
    placeholders = ''
    for x in range(0, len(values)):
        placeholders = placeholders + '?, '
    placeholders = placeholders[:len(placeholders) - 2]
    query = 'INSERT INTO ' + table + ' VALUES (' + placeholders + ')'
    try:
        cursor.execute(query, values)
    except sqlite3.IntegrityError as e:
        print('Error Inserting into ' + table + ' with values (' + str(values) + '): ' + str(e))
        raise
    cursor.close;

"""
Dowwnloads a file.

If OPT_DOWNLOAD is set to false, it will do nothing (but it will still return
true).
If OPT_DOWNLOAD_OVERWRITE it will only download the file if the target doesn't
exist (but it will still return true if it does).

:param url: File url.
:param target: Full or relative path (whit filename to save the url).
:returns: True on success, false on error.
"""
def download(url, target):
    if OPT_DOWNLOAD == False:
        return True
    if OPT_DOWNLOAD_OVERWRITE == False and os.path.isfile(target):
        return True
    try:
        urllib.request.urlretrieve(url, target)
        return True
    except:
        return False

"""
Parses building date (tables k_building, k_building_level).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, buildings.
"""
def parseBuildings(db, url):
    print("Parsing buildings...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for building in data["results"]:
            id = building["id"]
            com2us_id = building["com2us_id"]
            area = building["area"]
            affected_stat = building["affected_stat"]
            element = building["element"]
            name = building["name"]
            description = building["description"]
            max_level = building["max_level"]
            insert(db, "k_building", (id, com2us_id, area, affected_stat, element, name, description, max_level))
            lv = 0
            for bonus in building["stat_bonus"]:
                insert(db, "k_building_level", (id, lv, bonus, building["upgrade_cost"][lv]))
                lv = lv + 1
        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses skill data (tables k_skill, k_skill_level, k_skill_effect).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, skills.
"""
def parseSkills(db, url):
    print("Parsing skills...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for skill in data["results"]:
            id = skill["id"]
            com2us_id = skill["com2us_id"]
            name = skill["name"]
            description = skill["description"]
            slot = skill["slot"]
            cooltime = skill["cooltime"]
            hits = skill["hits"]
            passive = skill["passive"]
            aoe = skill["aoe"]
            max_level = skill["max_level"]
            multiplier_formula = skill["multiplier_formula"]
            insert(db, "k_skill", (id, com2us_id, name, description, slot, cooltime, hits, passive, aoe, max_level, multiplier_formula))
            insert(db, "k_skill_level", (id, 1, ""))
            if (max_level > 1):
                lv = 2
                for level in skill["level_progress_description"]:
                    insert(db, "k_skill_level", (id, str(lv), level))
                    lv = lv + 1
            for effect in skill["effects"]:
                insert(db, "k_skill_effect", (id, effect["effect"]["id"]))
            image = "https://swarfarm.com/static/herders/images/skills/" + skill["icon_filename"]
            download(image, 'public/img/content/skill/' + str(id).zfill(4) + '.png')

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses leader skills (table k_leader_skill).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, leader-skills.
"""
def parseLeaderSkills(db, url):
    print("Parsing leader skills...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for skill in data["results"]:
            id = skill["id"]
            attribute = skill["attribute"]
            amount = skill["amount"]
            area = skill["area"]
            element = skill["element"]
            insert(db, "k_leader_skill", (id, attribute, amount, area, element))

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses skill effects (table k_effect).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, skill-efects.
"""
def parseEffects(db, url):
    print("Parsing skill effects...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for effect in data["results"]:
            id = effect["id"]
            name = effect["name"]
            is_buff = effect["is_buff"]
            description = effect["description"]
            insert(db, "k_effect", (id, name, is_buff, description))

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses monster sources (table k_source).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, craft-materials.
"""
def parseSources(db, url):
    print("Parsing sources...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for source in data["results"]:
            id = source["id"]
            name = source["name"]
            description = source["description"]
            farmable_source = source["farmable_source"]
            insert(db, "k_source", (id, name, description, farmable_source))

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses monster data (tables k_monster, k_monster_essence, k_monster_source,
k_monsster_skill).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, monsters.
"""
def parseMonsters(db, url):
    print("Parsing monsters...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for mon in data["results"]:
            id = mon["id"]
            url = mon["url"]
            com2us_id = mon["com2us_id"]
            family_id = mon["family_id"]
            name = mon["name"]
            image_filename = mon["image_filename"]
            element = mon["element"]
            archetype = mon["archetype"]
            base_stars = mon["base_stars"]
            natural_stars = mon["natural_stars"]
            obtainable = mon["obtainable"]
            can_awaken = mon["can_awaken"]
            awaken_level = mon["awaken_level"]
            awaken_bonus = mon["awaken_bonus"]
            skill_ups_to_max = mon["skill_ups_to_max"]
            if mon["leader_skill"] != None:
                leader_skill = mon["leader_skill"]["id"]
            else:
                leader_skill = None
            homunculus_skills = mon["homunculus_skills"]
            base_hp = mon["base_hp"]
            base_attack = mon["base_attack"]
            base_defense = mon["base_defense"]
            speed = mon["speed"]
            crit_rate = mon["crit_rate"]
            crit_damage = mon["crit_damage"]
            resistance = mon["resistance"]
            accuracy = mon["accuracy"]
            raw_hp = mon["raw_hp"]
            raw_attack = mon["raw_attack"]
            raw_defense = mon["raw_defense"]
            max_lvl_hp = mon["max_lvl_hp"]
            max_lvl_attack = mon["max_lvl_attack"]
            max_lvl_defense = mon["max_lvl_defense"]
            awakens_from = mon["awakens_from"]
            awakens_to = mon["awakens_to"]
            awaken_mats_fire_low = mon["awaken_mats_fire_low"]
            awaken_mats_fire_mid = mon["awaken_mats_fire_mid"]
            awaken_mats_fire_high = mon["awaken_mats_fire_high"]
            awaken_mats_water_low = mon["awaken_mats_water_low"]
            awaken_mats_water_mid = mon["awaken_mats_water_mid"]
            awaken_mats_water_high = mon["awaken_mats_water_high"]
            awaken_mats_wind_low = mon["awaken_mats_wind_low"]
            awaken_mats_wind_mid = mon["awaken_mats_wind_mid"]
            awaken_mats_wind_high = mon["awaken_mats_wind_high"]
            awaken_mats_light_low = mon["awaken_mats_light_low"]
            awaken_mats_light_mid = mon["awaken_mats_light_mid"]
            awaken_mats_light_high = mon["awaken_mats_light_high"]
            awaken_mats_dark_low = mon["awaken_mats_dark_low"]
            awaken_mats_dark_mid = mon["awaken_mats_dark_mid"]
            awaken_mats_dark_high = mon["awaken_mats_dark_high"]
            awaken_mats_magic_low = mon["awaken_mats_magic_low"]
            awaken_mats_magic_mid = mon["awaken_mats_magic_mid"]
            awaken_mats_magic_high = mon["awaken_mats_magic_high"]
            fusion_food = mon["fusion_food"]
            homunculus = mon["homunculus"]
            craft_cost = mon["craft_cost"]
            insert(db, "k_monster", (id, com2us_id, family_id, name, element, archetype, base_stars, natural_stars, obtainable, can_awaken, awaken_bonus, skill_ups_to_max, leader_skill, base_hp, base_attack, base_defense, speed, crit_rate, crit_damage, resistance, accuracy, raw_hp, raw_attack, raw_defense, max_lvl_hp, max_lvl_attack, max_lvl_defense, awakens_from, awakens_to, fusion_food, homunculus, craft_cost))
            if awaken_mats_fire_low > 0:
                insert(db, "k_monster_essence", (id, "Fire", "Low", awaken_mats_fire_low))
            if awaken_mats_fire_mid > 0:
                insert(db, "k_monster_essence", (id, "Fire", "Mid", awaken_mats_fire_mid))
            if awaken_mats_fire_high > 0:
                insert(db, "k_monster_essence", (id, "Fire", "High", awaken_mats_fire_high))
            if awaken_mats_water_low > 0:
                insert(db, "k_monster_essence", (id, "Water", "Low", awaken_mats_water_low))
            if awaken_mats_water_mid > 0:
                insert(db, "k_monster_essence", (id, "Water", "Mid", awaken_mats_water_mid))
            if awaken_mats_water_high > 0:
                insert(db, "k_monster_essence", (id, "Water", "High", awaken_mats_water_high))
            if awaken_mats_wind_low > 0:
                insert(db, "k_monster_essence", (id, "Wind", "Low", awaken_mats_wind_low))
            if awaken_mats_wind_mid > 0:
                insert(db, "k_monster_essence", (id, "Wind", "Mid", awaken_mats_wind_mid))
            if awaken_mats_wind_high > 0:
                insert(db, "k_monster_essence", (id, "Wind", "High", awaken_mats_wind_high))
            if awaken_mats_dark_low > 0:
                insert(db, "k_monster_essence", (id, "Dark", "Low", awaken_mats_dark_low))
            if awaken_mats_dark_mid > 0:
                insert(db, "k_monster_essence", (id, "Dark", "Mid", awaken_mats_dark_mid))
            if awaken_mats_dark_high > 0:
                insert(db, "k_monster_essence", (id, "Dark", "High", awaken_mats_dark_high))
            if awaken_mats_light_low > 0:
                insert(db, "k_monster_essence", (id, "Light", "Low", awaken_mats_light_low))
            if awaken_mats_light_mid > 0:
                insert(db, "k_monster_essence", (id, "Light", "Mid", awaken_mats_light_mid))
            if awaken_mats_light_high > 0:
                insert(db, "k_monster_essence", (id, "Light", "High", awaken_mats_light_high))
            if awaken_mats_magic_low > 0:
                insert(db, "k_monster_essence", (id, "Magic", "Low", awaken_mats_magic_low))
            if awaken_mats_magic_mid > 0:
                insert(db, "k_monster_essence", (id, "Magic", "Mid", awaken_mats_magic_mid))
            if awaken_mats_magic_high > 0:
                insert(db, "k_monster_essence", (id, "Magic", "High", awaken_mats_magic_high))
            for skill in mon["skills"]:
                insert(db, "k_monster_skill", (id, skill))
            for source in mon["source"]:
                insert(db, "k_monster_source", (id, source["id"]))
            # TODO: craft_materials
            image = "https://swarfarm.com/static/herders/images/monsters/" + mon["image_filename"]
            download(image, 'public/img/content/monster/' + str(id).zfill(4) + '.png')

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses fusion data (table k_fusion).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, fusions.
"""
def parseFusions(db, url):
    print("Parsing fusions...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        for fusion in data["results"]:
            id = fusion["id"]
            product = fusion["product"]
            stars = fusion["stars"]
            cost = fusion["cost"]
            for ingredient in fusion["ingredients"]:
                insert(db, "k_fusion", (id, product, stars, cost, ingredient))

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Parses crafting materials (table k_craft).

:param db: Sqlite database connection.
:param url: Swarfarm APIv2, craft-materials.
"""
def parseCraft(db, url):
    print("Parsing crafting materials...")
    http = urllib3.PoolManager()
    mlist = str(http.request('GET', url).data.decode('utf-8'))
    data = json.loads(mlist)
    while True:
        first = False
        for crat in data["results"]:
            id = crat["id"]
            name = crat["name"]
            insert(db, "k_craft", (id, name))

        # Next page
        db.commit()
        if data["next"] != None:
            mlist = str(http.request('GET', data["next"]).data.decode('utf-8'))
            data = json.loads(mlist)
        else:
            break

"""
Removes references to imperfect monsters.

Imperfect monsters are only available in Japanese servers due to legal reasons,
they are not needed for this app. They wont be deleted, but awakening chains
wil be cut.

:param db: Sqlite database connection.
"""
def removeImperfect():
    cursor = db.cursor()
    cursor.execute('''
        UPDATE k_monster
        SET awakens_from = null
        WHERE awakens_from IN (SELECT id FROM k_monster WHERE name LIKE 'Imperfect %')
    ''');
    db.commit()
    cursor.close()



"""
Begin script
"""
db = createDatabase('application/sw.sqlite')
parseBuildings(db, 'https://swarfarm.com/api/v2/buildings/')
parseEffects(db, 'https://swarfarm.com/api/v2/skill-effects/')
parseSkills(db, 'https://swarfarm.com/api/v2/skills/')
parseLeaderSkills(db, 'https://swarfarm.com/api/v2/leader-skills/')
parseSources(db, 'https://swarfarm.com/api/v2/monster-sources/')
parseMonsters(db, 'https://swarfarm.com/api/v2/monsters/')
parseFusions(db, 'https://swarfarm.com/api/v2/fusions/')
parseCraft(db, 'https://swarfarm.com/api/v2/craft-materials/')
removeImperfect(db)