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 = False; # TODO
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_decoration_level(
                decoration TEXT,
                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 TEXT,
                level INT,
                description TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_skill(
                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_tmp(
                id TEXT,
                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 TEXT,
                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_unit(
                id TEXT,
                family 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,
                hp INT,
                attack INT,
                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_unit_tmp(
                id TEXT,
                com2us_id TEXT,
                family 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,
                hp INT,
                attack INT,
                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_unit_skill(
                unit TEXT,
                skill TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_unit_source(
                unit TEXT,
                source INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_unit_essence(
                unit TEXT,
                element TEXT,
                grade TEXT,
                amount TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS k_fusion(
                id INT,
                product TEXT,
                stars INT,
                cost INT,
                ingredient TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS player(
                uid TEXT,
                name TEXT,
                mana INT,
                crystal INT,
                country TEXT,
                lang TEXT,
                level INT,
                experience INT,
                energy INT,
                energy_max INT,
                energy_per_min INT,
                arena_energy INT,
                arena_energy_max INT,
                rep TEXT,
                social_point INT,
                honor_point INT,
                guild_point INT,
                darkportal_energy INT,
                darkportal_energy_max INT,
                dimension_energy INT,
                dimension_energy_max INT,
                costume_point INT,
                costume_point_max INT,
                honor_medal INT,
                honor_mark INT,
                event_coin INT,
                storage_slots INT,
                island INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS scenario(
                uid TEXT,
                region INT,
                difficulty INT,
                cleared INT,
                max_cleared INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS defense(
                uid TEXT,
                unit TEXT,
                position INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS defense(
                uid TEXT,
                building INT,
                level INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS unit(
                uid TEXT,
                id TEXT,
                building TEXT,
                unit TEXT,
                level INT,
                stars INT,
                hp INT,
                attack INT,
                defense INT,
                speed INT,
                crit_rate INT,
                crit_damage INT,
                resistance INT,
                accuracy INT,
                experience INT,
                exp_gained INT,
                exp_gain_rate INT,
                costume INT,
                attribute INT,
                source INT,
                create_time DATETIME,
                homunculus INT,
                homunculus_name TEXT,
                lock INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS unit_skill(
                uid TEXT,
                unit TEXT,
                skill TEXT,
                level INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rune(
                uid TEXT,
                id TEXT,
                assigned_to TEXT,
                type TEXT,
                slot INT,
                stars INT,
                ancient INT,
                level INT,
                quality INT,
                original_quality INT,
                value 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_enchant INT,
                substat_1_grind INT,
                substat_2 TEXT,
                substat_2_value INT,
                substat_2_enchant INT,
                substat_2_grind INT,
                substat_3 TEXT,
                substat_3_value INT,
                substat_3_enchant INT,
                substat_3_grind INT,
                substat_4 TEXT,
                substat_4_value INT,
                substat_4_enchant INT,
                substat_4_grind INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS building(
                uid TEXT,
                building INT,
                gain INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS decoration(
                uid TEXT,
                decoration INT,
                level INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS inventory(
                uid TEXT,
                id TEXT,
                type TEXT,
                amount INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS summon_special(
                week TEXT,
                unit TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS guild(
                id TEXT,
                name TEXT,
                level INT,
                experience INT,
                recruiting INT,
                members INT,
                leader INT,
                comment TEXT,
                notice TEXT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS guild_member(
                id TEXT,
                guild TEXT,
                grade INT,
                name TEXT,
                level INT,
                rating INT,
                arena_score INT,
                joined TIMESTAMP,
                last_login TIMESTAMP,
                in_war INT,
                has_defense INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rune_craft(
                uid TEXT,
                id INT,
                type INT,
                quality INT,
                rune INT,
                stat INT,
                value INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run(
                uid TEXT,
                id INT,
                dtime TIMESTAMP,
                area INT,
                stage INT,
                difficulty INT,
                win INT,
                time INT,
                mana INT,
                energy INT,
                crystal INT,
                helper INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_party(
                run INT,
                unit INT,
                k_unit INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_rune(
                run INT,
                id INT,
                type TEXT,
                slot INT,
                stars INT,
                ancient INT,
                quality INT,
                value INT,
                efficiency INT,
                main_stat TEXT,
                main_stat_value INT,
                innate_stat TEXT,
                innate_stat_value INT,
                substat_1 TEXT,
                substat_1_value INT,
                substat_2 TEXT,
                substat_2_value INT,
                substat_3 TEXT,
                substat_3_value INT,
                substat_4 TEXT,
                substat_4_value INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_rune_craft(
                run INT,
                id INT,
                type INT,
                quality INT,
                rune INT,
                stat INT,
                value INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_item(
                run INT,
                item INT,
                quantity INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_unit(
                run INT,
                unit INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_sd(
                run INT,
                unit INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_unit_pieces(
                run INT,
                unit INT,
                quantity INT
            );
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS run_drop_shapeshifting(
                run INT,
                quantity 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_tmp", (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_tmp", (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"]
            if element == 'Water':
                element = 1;
            elif element == 'Fire':
                element = 2;
            elif element == 'Wind':
                element = 3;
            elif element == 'Light':
                element = 4;
            elif element == 'Dark':
                element = 5;
            else:
                element = None
            if attribute == 'HP':
                attribute = 1
            elif attribute == 'Attack Power':
                attribute = 2
            elif attribute == 'Defense':
                attribute = 3
            elif attribute == 'Attack Speed':
                attribute = 4
            elif attribute == 'Critical Rate':
                attribute = 5
            elif attribute == 'Critical DMG':
                attribute = 6
            elif attribute == 'Resistance':
                attribute = 7
            elif attribute == 'Accuracy':
                attribute = 8
            else:
                attribute = None
            if area == 'General':
                area = 1
            elif area == 'Element':
                area = 1
            elif area == 'Dungeon':
                area = 2
            elif area == 'Arena':
                area = 3
            elif area == 'Guild':
                area = 4
            else:
                area = 1
            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_unit, k_unit_essence, k_unit_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"]
            hp = mon["base_hp"]
            attack = mon["base_attack"]
            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_unit_tmp", (id, com2us_id, family_id, name, element, archetype, base_stars, natural_stars, obtainable, can_awaken, awaken_bonus, skill_ups_to_max, leader_skill, hp, attack, 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_unit_essence", (id, "Fire", "Low", awaken_mats_fire_low))
            if awaken_mats_fire_mid > 0:
                insert(db, "k_unit_essence", (id, "Fire", "Mid", awaken_mats_fire_mid))
            if awaken_mats_fire_high > 0:
                insert(db, "k_unit_essence", (id, "Fire", "High", awaken_mats_fire_high))
            if awaken_mats_water_low > 0:
                insert(db, "k_unit_essence", (id, "Water", "Low", awaken_mats_water_low))
            if awaken_mats_water_mid > 0:
                insert(db, "k_unit_essence", (id, "Water", "Mid", awaken_mats_water_mid))
            if awaken_mats_water_high > 0:
                insert(db, "k_unit_essence", (id, "Water", "High", awaken_mats_water_high))
            if awaken_mats_wind_low > 0:
                insert(db, "k_unit_essence", (id, "Wind", "Low", awaken_mats_wind_low))
            if awaken_mats_wind_mid > 0:
                insert(db, "k_unit_essence", (id, "Wind", "Mid", awaken_mats_wind_mid))
            if awaken_mats_wind_high > 0:
                insert(db, "k_unit_essence", (id, "Wind", "High", awaken_mats_wind_high))
            if awaken_mats_dark_low > 0:
                insert(db, "k_unit_essence", (id, "Dark", "Low", awaken_mats_dark_low))
            if awaken_mats_dark_mid > 0:
                insert(db, "k_unit_essence", (id, "Dark", "Mid", awaken_mats_dark_mid))
            if awaken_mats_dark_high > 0:
                insert(db, "k_unit_essence", (id, "Dark", "High", awaken_mats_dark_high))
            if awaken_mats_light_low > 0:
                insert(db, "k_unit_essence", (id, "Light", "Low", awaken_mats_light_low))
            if awaken_mats_light_mid > 0:
                insert(db, "k_unit_essence", (id, "Light", "Mid", awaken_mats_light_mid))
            if awaken_mats_light_high > 0:
                insert(db, "k_unit_essence", (id, "Light", "High", awaken_mats_light_high))
            if awaken_mats_magic_low > 0:
                insert(db, "k_unit_essence", (id, "Magic", "Low", awaken_mats_magic_low))
            if awaken_mats_magic_mid > 0:
                insert(db, "k_unit_essence", (id, "Magic", "Mid", awaken_mats_magic_mid))
            if awaken_mats_magic_high > 0:
                insert(db, "k_unit_essence", (id, "Magic", "High", awaken_mats_magic_high))
            for skill in mon["skills"]:
                insert(db, "k_unit_skill", (id, skill))
            for source in mon["source"]:
                insert(db, "k_unit_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(db):
    print("Unlinking inperfetc units...")
    cursor = db.cursor()
    cursor.execute('''
        UPDATE k_unit
        SET awakens_from = null
        WHERE awakens_from IN (SELECT id FROM k_unit WHERE name LIKE 'Imperfect %')
    ''');
    db.commit()
    cursor.close()

"""
Changes IDS from swarfarm formats to swex formats.

:param db: Sqlite database connection.
"""
def useSwexIds(db):
    print("Updating IDs...")
    cursor = db.cursor()
    #cursor.execute('UPDATE k_building_level SET building = (SELECT com2us_id FROM k_building WHERE building = id)')
    #cursor.execute('UPDATE k_building_tmp SET id = com2us_id')
    cursor.execute('UPDATE k_skill_level SET skill = (SELECT com2us_id FROM k_skill_tmp WHERE skill = id)')
    cursor.execute('UPDATE k_skill_effect SET skill = (SELECT com2us_id FROM k_skill_tmp WHERE skill = id)')
    cursor.execute('UPDATE k_unit_skill SET skill = (SELECT com2us_id FROM k_skill_tmp WHERE skill = id)')
    cursor.execute('UPDATE k_skill_tmp SET id = com2us_id')
    cursor.execute('UPDATE k_unit_skill SET unit = (SELECT com2us_id FROM k_unit_tmp WHERE unit = k_unit_tmp.id)')
    cursor.execute('UPDATE k_unit_source SET unit = (SELECT com2us_id FROM k_unit_tmp WHERE unit = k_unit_tmp.id)')
    cursor.execute('UPDATE k_unit_essence SET unit = (SELECT com2us_id FROM k_unit_tmp WHERE unit = k_unit_tmp.id)')
    cursor.execute('UPDATE k_fusion SET product = (SELECT com2us_id FROM k_unit_tmp WHERE product = k_unit_tmp.id)')
    cursor.execute('UPDATE k_fusion SET ingredient = (SELECT com2us_id FROM k_unit_tmp WHERE ingredient = k_unit_tmp.id)')
    cursor.execute('UPDATE k_unit_tmp SET awakens_from = (SELECT com2us_id FROM k_unit_tmp un WHERE k_unit_tmp.awakens_from = un.id)')
    cursor.execute('UPDATE k_unit_tmp SET awakens_to = (SELECT com2us_id FROM k_unit_tmp un WHERE k_unit_tmp.awakens_to = un.id)')
    cursor.execute('UPDATE k_unit_tmp SET id = com2us_id')
    cursor.execute('UPDATE k_unit_tmp SET element = (SELECT id FROM k_element WHERE k_element.name = k_unit_tmp.element)')
    cursor.execute('UPDATE k_unit_tmp SET archetype = (SELECT id FROM k_archetype WHERE k_archetype.name = k_unit_tmp.archetype) WHERE archetype IS NOT NULL')
    db.commit()
    #cursor.execute('INSERT INTO k_building SELECT id, area, affected_stat, element, name, description, max_level FROM k_building_tmp')
    cursor.execute('INSERT INTO k_skill SELECT id, name, description, slot, cooltime, hits, passive, aoe, max_level, multiplier_formula FROM k_skill_tmp')
    cursor.execute('INSERT INTO k_unit SELECT id, family, name, element, archetype, base_stars, natural_stars, obtainable, can_awaken, awaken_bonus, skill_ups_to_max, leader_skill, hp, attack, 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 FROM k_unit_tmp')
    db.commit()
    #cursor.execute('DROP TABLE k_building_tmp')
    cursor.execute('DROP TABLE k_skill_tmp')
    cursor.execute('DROP TABLE k_unit_tmp')
    cursor.close()

def staticData(db):
    print("Procesing data...")
    cursor = db.cursor()
    with open("data.sql", "r") as data:
        for line in data:
            if len(line) > 1:
                cursor.execute(line)
    db.commit()
    cursor.close()

"""
Begin script
"""
db = createDatabase('application/sw.sqlite')
staticData(db)
#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)
useSwexIds(db)