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;

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

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

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

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

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

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


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')
            if id > 1215:
                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

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

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






"""
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/')