rowboat_json_to_sql.py

# -*- coding: utf-8 -*-

# MIT License

# Copyright(c)2019 Nadie#0063

# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files(the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

# origional converter (json to sql) was made by SnowyLuma https://gist.github.com/SnowyLuma/55a3c831a060a090b1ce7f32e276c258

# get sessioncookie from browser for discord auth (to get infraction data), 
# maxpage is the last page of infractions(empty array), 
# check for dups is for checking the clone that the infraction data is transfered to is there already. 
# (sessioncookies should not match) dlink is dashboard link (no / @ end)

guild_id = ''

dlink_old = ''
sessioncookie_old = ''

check_for_dupes = False
dlink_new = ''
sessioncookie_new = ''

# DONT TOUCH ANYTHING AFTER THIS LINE #
import requests
import json
import datetime
import sys
import time

#functions
def get_storagetype(dashlink, gid, sessioncookie):
    r = requests.get("{}/api/guilds/{}/infractions?page=1&limit=1&sorted=[]&filtered=[]".format(dashlink, gid), cookies=dict(session=sessioncookie))
    r.raise_for_status()
    if 'infractions' not in r.json():
        return True
    else:
        return False

def get_infdata(dashlink, gid, sessioncookie, storagetype):
    data = []
    page = 0
    while True:
        page +=1
        r = requests.get("{}/api/guilds/{}/infractions?page={}&limit=1000&sorted=[]&filtered=[]".format(dashlink, gid, page), cookies=dict(session=sessioncookie))
        r.raise_for_status()
        if storagetype:
            for inf in r.json():
                data.append(inf)
            if len(r.json()) == 0:
                break
        else:
            for inf in r.json()["infractions"]:
                data.append(inf)
            if len(r.json()['infractions']) == 0:
                break
        time.sleep(3) # so clone owners dont get that mad >:C
    return data

def checkdupe(entry, data):
    for x in data:
        if x["reason"] == entry["reason"] and x["user"]["id"] == entry['user']["id"] and x["actor"]["id"] == entry['actor']["id"] and x["type"]["id"] == entry["type"]["id"] and x["created_at"] == entry["created_at"]:
            return True
    return False

#data
olddata = get_infdata(dlink_old, guild_id, sessioncookie_old, get_storagetype(dlink_old, guild_id, sessioncookie_old))
newdata = get_infdata(dlink_new, guild_id, sessioncookie_new, get_storagetype(dlink_new, guild_id, sessioncookie_new)) if check_for_dupes else []
users = {}
output = []
usercount = 0
infcount = 0
dupecount = 0

for entry in reversed(olddata):
    if check_for_dupes is True and checkdupe(entry, newdata) is True:
        dupecount+=1
    else:
        infcount+=1
        guild_id = entry['guild']['id']
        user_id = entry['user']['id']
        actor_id = entry['actor']['id']

        # we only want unique users so we store them first
        users[user_id] = entry['user']
        users[actor_id] =  entry['actor']

        type_ = entry['type']['id']

        if entry['reason'] is not None:
            # replacing ' with '' to prevent escaping
            _reason = entry['reason'].replace('\'', '\'\'')
            reason = f'\'{_reason}\''
        else:
            reason = 'null'

        # Sun, 24 Jun 2018 15:05:46 GMT
        if entry['expires_at'] is not None:
            _expires_at = datetime.datetime.strptime(entry['expires_at'][:-4], '%a, %d %b %Y %H:%M:%S')
            expires_at = f'\'{_expires_at}\'::timestamp'
        else:
            expires_at = 'null'

        _created_at = datetime.datetime.strptime(entry['created_at'][:-4], '%a, %d %b %Y %H:%M:%S')
        created_at = f'\'{_created_at}\'::timestamp'

        active = str(entry['active']).upper()

        line = (
            f'INSERT INTO infractions (guild_id, user_id, actor_id, type, reason, metadata, expires_at, created_at, active) '
            f'VALUES ({guild_id}, {user_id}, {actor_id}, {type_}, {reason}, \'{{}}\', {expires_at}, {created_at}, {active});'
        )

        output.append(line)
output.append('')

for user_id in users:
    usercount+=1
    _username = users[user_id]['username'].replace('\'', '\'\'')
    username = f'\'{_username}\''

    if users[user_id]['avatar'] is not None:
        _avatar = users[user_id]['avatar']
        avatar = f'\'{_avatar}\''
    else:
        avatar = 'null'

    discriminator = users[user_id]['discriminator']
    bot = str(users[user_id]['bot']).upper()

    line = (
        f'INSERT INTO users (user_id, username, discriminator, avatar, bot, created_at, admin) '
        f'VALUES ({user_id}, {username}, {discriminator}, {avatar}, {bot}, now(), FALSE) '
        f'ON CONFLICT (user_id) DO NOTHING;'
    )

    output.append(line)

with open('{}.sql'.format(guild_id), 'w', encoding='utf-8') as f:
    f.write('\n'.join(output))

print("Amount of infractions imported - {}".format(infcount))
print("Amount of users imported - {}".format(usercount))
print("Amount of duped infractions - {}".format(dupecount))