cyberpresidentvanellope/Yogstation
1
0
Fork 0
mirror of https://github.com/yogstation13/Yogstation.git synced 2025-02-26 09:04:50 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
master
Yogstation/SQL/feedback_conversion_2017-11-12.py
Mqiib caf9257395 Sidewinder Again: Now With Sprites! (#18007) 
* AND WE'RE BACK

* SO MUCH SIMPLER

* better facing

* Update mecha.dm

* SPACES?

* e

* construction time

hopefully

* haha oops

* e

* SPRITED

* Gets rid of useless thing

* Update all_nodes.dm
2023-02-23 23:26:36 +00:00

554 lines
31 KiB
Python
Raw Permalink Blame History

#Python 3+ Script for jsonifying feedback table data as of 2017-11-12 made by Jordie0608
#Apologies for the boilerplated and squirrely code in parts, this has been my first foray into python
#
#Before starting ensure you have installed the mysqlclient package https://github.com/PyMySQL/mysqlclient-python
#It can be downloaded from command line with pip:
#pip install mysqlclient
#
#tgstation no longer supports MySQL which has been superseded by MariaDB, a drop-in replacement.
#Before running this script you will need to migrate to MariaDB.
#Migrating is very easy to do, for details on how see: https://mariadb.com/kb/en/library/upgrading-from-mysql-to-mariadb/
#
#You will also have to create a new feedback table for inserting converted data to per the schema:
#CREATE TABLE `feedback_new` (
# `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
# `datetime` datetime NOT NULL,
# `round_id` int(11) unsigned NOT NULL,
# `key_name` varchar(32) NOT NULL,
# `key_type` enum('text', 'amount', 'tally', 'nested tally', 'associative') NOT NULL,
# `version` tinyint(3) unsigned NOT NULL,
# `json` json NOT NULL,
# PRIMARY KEY (`id`)
#) ENGINE=MyISAM
#This is to prevent the destruction of legacy data and allow rollbacks to be performed in the event of an error during conversion
#Once conversion is complete remember to rename the old and new feedback tables; it's up to you if you want to keep the old table
#
#To view the parameters for this script, execute it with the argument --help
#All the positional arguments are required, remember to include prefixes in your table names if you use them
#An example of the command used to execute this script from powershell:
#python feedback_conversion_2017-11-12.py "localhost" "root" "password" "feedback" "SS13_feedback" "SS13_feedback_new"
#I found that this script would complete conversion of 10000 rows approximately every 2-3 seconds
#Depending on the size of your feedback table and the computer used it may take several minutes for the script to finish
#
#The script has been tested to complete with tgstation's feedback table as of 2017-10-23 01:34:06
#Due to the complexity of data that has potentially changed formats multiple times and suffered errors when recording I cannot guarantee it'll always execute successfully
#In the event of an error the new feedback table is automatically truncated
#The source table is never modified so you don't have to worry about losing any data due to errors
#Note that some feedback keys are renamed or coalesced into one, additionnaly some have been entirely removed
#
#While this script can be run with your game server(s) active, it may interfere with other database operations and any feedback created after the script has started won't be converted
import MySQLdb
import argparse
import json
import re
import sys
from datetime import datetime
def parse_text(details):
if not details:
return
if details.startswith('"') and details.endswith('"'):
details = details[1:-1] #the first and last " aren't removed by splitting the dictionary
details = details.split('" | "')
else:
if "_" in details:
details = details.split(' ')
return details
def parse_tally(details):
if not details:
return
overflowed = None
if len(details) >= 65535: #a string this long means the data hit the 64KB character limit of TEXT columns
overflowed = True
if details.startswith('"') and details.endswith('"'):
details = details[details.find('"')+1:details.rfind('"')] #unlike others some of the tally data has extra characters to remove
split_details = details.split('" | "')
else:
split_details = details.split(' ')
if overflowed:
split_details = split_details[:-1] #since the string overflowed the last element will be incomplete and needs to be ignored
details = {}
for i in split_details:
increment = 1
if '|' in i and i[i.find('|')+1:]:
increment = float(i[i.find('|')+1:])
i = i[:i.find('|')]
if i in details:
details[i] += increment
else:
details[i] = increment
for i in details:
details[i] = '{0:g}'.format(details[i]) #remove .0 from floats that have it to conform with DM
return details
def parse_nested(var_name, details):
if not details:
return
#group by data before pipe
if var_name in ("admin_toggle", "preferences_verb", "changeling_objective", "cult_objective", "traitor_objective", "wizard_objective", "mining_equipment_bought", "vending_machine_usage", "changeling_powers", "wizard_spell_improved", "testmerged_prs"):
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
for i in split_details:
if "|" in i and i[:i.find('|')] not in details:
details[i[:i.find('|')]] = {}
elif "|" not in i and i[i.find('|')+1:] not in details:
details[i[i.find('|')+1:]] = 0
for i in split_details:
if "|" in i:
if details[i[:i.find('|')]] is not dict:
continue
if i[i.find('|')+1:] in details[i[:i.find('|')]]:
details[i[:i.find('|')]][i[i.find('|')+1:]] += 1
else:
details[i[:i.find('|')]][i[i.find('|')+1:]] = 1
else:
if i in details and type(details[i]) is not dict: #sometimes keys that should have a value after a pipe just don't and would otherwise error here
details[i] += 1
return details
#group by data after pipe
elif var_name in ("cargo_imports", "traitor_uplink_items_bought", "export_sold_cost", "item_used_for_combat", "played_url"):
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
for i in split_details:
if i == i[i.rfind('|')+1:]: #there's no pipe and data to group by, so we fill it in
i = "{0}|missing data".format(i)
details[i[i.rfind('|')+1:]] = {}
for i in split_details:
if i == i[i.rfind('|')+1:]:
i = "{0}|missing data".format(i)
if i[:i.find('|')] in details[i[i.rfind('|')+1:]]:
details[i[i.rfind('|')+1:]][i[:i.find('|')]] += 1
else:
details[i[i.rfind('|')+1:]][i[:i.find('|')]] = 1
return details
elif var_name == "hivelord_core":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
for i in split_details:
if i[:i.find('|')] not in details:
details[i[:i.find('|')]] = {}
if "used" in i:
if "used" not in details:
details[i[:i.find('|')]]["used"] = {}
for i in split_details:
if "used" in i:
if "used" not in details[i[:i.find('|')]]:
details[i[:i.find('|')]]["used"] = {}
details[i[:i.find('|')]]["used"][i[i.rfind('|')+1:]] = 1
else:
if i[i.rfind('|')+1:] in details[i[:i.find('|')]]["used"]:
details[i[:i.find('|')]]["used"][i[i.rfind('|')+1:]] += 1
else:
details[i[:i.find('|')]]["used"][i[i.rfind('|')+1:]] = 1
elif "|" in i:
if i[i.find('|')+1:] in details[i[:i.find('|')]]:
details[i[:i.find('|')]][i[i.find('|')+1:]] += 1
else:
details[i[:i.find('|')]][i[i.find('|')+1:]] = 1
return details
elif var_name == "job_preferences":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('|-" | "|')
else:
split_details = details.split('|- |')
details = {}
for i in split_details:
if i.startswith('|'):
i = i[1:]
if i[:i.find('|')] not in details:
details[i[:i.find('|')]] = {}
for i in split_details:
if i.startswith('|'):
i = i[1:]
if i.endswith('-'):
i = i[:-2]
sub_split = i.split('|')
job = sub_split[0]
sub_split = sub_split[1:]
for o in sub_split:
details[job][o[:o.find('=')].lower()] = o[o.find('=')+1:]
return details
def parse_associative(var_name, details):
if not details:
return
if var_name == "colonies_dropped":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('|')
details = {}
details["1"] = {"x" : split_details[0], "y" : split_details[1], "z" : split_details[2]}
return details
elif var_name == "commendation":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
if '}" | "{' in details:
split_details = details.split('}" | "{')
else:
split_details = details.split('} {')
details = {}
for i in split_details:
params = []
sub_split = i.split(',')
for o in sub_split:
o = re.sub('[^A-Za-z0-9 ]', '', o[o.find(':')+1:]) #remove all the formatting and escaped characters from being pre-encoded as json
params.append(o)
details[len(details)+1] = {"commender" : params[0], "commendee" : params[1], "medal" : params[2], "reason" : params[3]}
return details
elif var_name == "high_research_level":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
levels = {}
for i in split_details:
x = {i[:-1] : i[-1:]}
levels.update(x)
details["1"] = levels
return details
def parse_special(var_name, var_value, details):
#old data is essentially a tally in text form
if var_name == "immortality_talisman":
if details.startswith('"') and details.endswith('"'):
split_details = details.split('" | "')
else:
split_details = details.split(' ')
return len(split_details)
#now records channel names, so we have to fill in whats missing
elif var_name == "newscaster_channels":
details = ["missing data"]
details *= var_value
return details
#all the channels got renamed, plus we ignore any with an amount of zero
elif var_name == "radio_usage":
if details.startswith('"') and details.endswith('"'):
details = details[details.find('C'):-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
new_keys = {"COM":"common", "SCI":"science", "HEA":"command", "MED":"medical", "ENG":"engineering", "SEC":"security", "DTH":"centcom", "SYN":"syndicate", "SRV":"service", "CAR":"supply", "OTH":"other", "PDA":"PDA", "RC":"request console"}
for i in split_details:
if i.endswith('0'):
continue
if i[:i.find('-')] not in new_keys:
continue
details[new_keys[i[:i.find('-')]]] = i[i.find('-')+1:]
return details
#all of the data tracked by this is invalid due to recording the incorrect type
elif var_name == "shuttle_gib":
return {"missing data":1}
#all records have a prefix of 'slimebirth_' that needs to be removed
elif var_name == "slime_babies_born":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
for i in split_details:
if i[i.find('_')+1:].replace('_', ' ') in details:
details[i[i.find('_')+1:].replace('_', ' ')] += 1
else:
details[i[i.find('_')+1:].replace('_', ' ')] = 1
return details
#spaces were replaced by underscores, we need to undo this
elif var_name == "slime_core_harvested":
if details.startswith('"') and details.endswith('"'):
details = details[1:-1]
split_details = details.split('" | "')
else:
split_details = details.split(' ')
details = {}
for i in split_details:
if i.replace('_', ' ') in details:
details[i.replace('_', ' ')] += 1
else:
details[i.replace('_', ' ')] = 1
return details
def parse_multirow(var_name, var_value, details, multirows_completed):
if var_name in ("ahelp_close", "ahelp_icissue", "ahelp_reject", "ahelp_reopen", "ahelp_resolve", "ahelp_unresolved"):
ahelp_vars = {"ahelp_close":"closed", "ahelp_icissue":"IC", "ahelp_reject":"rejected", "ahelp_reopen":"reopened", "ahelp_resolve":"resolved", "ahelp_unresolved":"unresolved"}
details = {ahelp_vars[var_name]:var_value}
del ahelp_vars[var_name]
query_where = "round_id = {0} AND (".format(query_row[2])
for c, i in enumerate(ahelp_vars):
if c:
query_where += " OR "
query_where += "var_name = \"{0}\"".format(i)
query_where += ")"
cursor.execute("SELECT var_name, var_value FROM {0} WHERE {1}".format(current_table, query_where))
rows = cursor.fetchall()
if rows:
for r in rows:
details[ahelp_vars[r[0]]] = r[1]
keys = list(ahelp_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
elif var_name in ("alert_comms_blue", "alert_comms_green"):
level_vars = {"alert_comms_blue":"1", "alert_comms_green":"0"}
details = {level_vars[var_name]:var_value}
del level_vars[var_name]
i = list(level_vars)[0]
cursor.execute("SELECT var_value FROM {0} WHERE round_id = {1} AND var_name = \"{2}\"".format(current_table, query_row[2], i))
row = cursor.fetchone()
if row:
details[level_vars[i]] = row[0]
keys = list(level_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
elif var_name in ("alert_keycard_auth_bsa", "alert_keycard_auth_maint"):
auth_vars = {"alert_keycard_auth_maint":("emergency maintenance access", "enabled"), "alert_keycard_auth_bsa":("bluespace artillery", "unlocked")}
i = list(auth_vars[var_name])
details = {i[0]:{i[1]:var_value}}
del auth_vars[var_name]
i = list(auth_vars)[0]
cursor.execute("SELECT var_value FROM {0} WHERE round_id = {1} AND var_name = \"{2}\"".format(current_table, query_row[2], i))
row = cursor.fetchone()
if row:
o = list(auth_vars[i])
details[o[0]] = {o[1]:row[0]}
keys = list(auth_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
elif var_name in ("arcade_loss_hp_emagged", "arcade_loss_hp_normal", "arcade_loss_mana_emagged", "arcade_loss_mana_normal", "arcade_win_emagged", "arcade_win_normal"):
result_vars = {"arcade_loss_hp_emagged":("loss", "hp", "emagged"), "arcade_loss_hp_normal":("loss", "hp", "normal"), "arcade_loss_mana_emagged":("loss", "mana", "emagged"), "arcade_loss_mana_normal":("loss", "mana", "normal"), "arcade_win_emagged":("win", "emagged"), "arcade_win_normal":("win", "normal")}
i = list(result_vars[var_name])
del result_vars[var_name]
if i[0] == "loss":
details = {i[0]:{i[1]:{i[2]:var_value}}}
else:
details = {i[0]:{i[1]:var_value}}
query_where = "round_id = {0} AND (".format(query_row[2])
for c, i in enumerate(result_vars):
if c:
query_where += " OR "
query_where += "var_name = \"{0}\"".format(i)
query_where += ")"
cursor.execute("SELECT var_name, var_value FROM {0} WHERE {1}".format(current_table, query_where))
rows = cursor.fetchall()
if rows:
for r in rows:
i = list(result_vars[r[0]])
if i[0] not in details:
details[i[0]] = {}
if i[0] == "loss":
if i[1] not in details[i[0]]:
details[i[0]][i[1]] = {}
details[i[0]][i[1]][i[2]] = r[1]
else:
details[i[0]][i[1]] = r[1]
keys = list(result_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
elif var_name in ("cyborg_engineering", "cyborg_janitor", "cyborg_medical", "cyborg_miner", "cyborg_peacekeeper", "cyborg_security", "cyborg_service", "cyborg_standard"):
module_vars = {"cyborg_engineering":"Engineering", "cyborg_janitor":"Janitor", "cyborg_medical":"Medical", "cyborg_miner":"Miner", "cyborg_peacekeeper":"Peacekeeper", "cyborg_security":"Security", "cyborg_service":"Service", "cyborg_standard":"Standard"}
details = {module_vars[var_name]:var_value}
del module_vars[var_name]
query_where = "round_id = {0} AND (".format(query_row[2])
for c, i in enumerate(module_vars):
if c:
query_where += " OR "
query_where += "var_name = \"{0}\"".format(i)
query_where += ")"
cursor.execute("SELECT var_name, var_value FROM {0} WHERE {1}".format(current_table, query_where))
rows = cursor.fetchall()
if rows:
for r in rows:
details[module_vars[r[0]]] = r[1]
keys = list(module_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
elif var_name in ("escaped_human", "escaped_total", "round_end_clients", "round_end_ghosts", "survived_human", "survived_total"):
round_vars = {"escaped_human":("escapees", "human"), "escaped_total":("escapees", "total"), "round_end_clients":("clients"), "round_end_ghosts":("ghosts"), "survived_human":("survivors", "human"), "survived_total":("survivors", "total")}
if var_name in ("round_end_clients", "round_end_ghosts"):
i = round_vars[var_name]
details = {i:var_value}
else:
i = list(round_vars[var_name])
details = {i[0]:{i[1]:var_value}}
del round_vars[var_name]
query_where = "round_id = {0} AND (".format(query_row[2])
for c, i in enumerate(round_vars):
if c:
query_where += " OR "
query_where += "var_name = \"{0}\"".format(i)
query_where += ")"
cursor.execute("SELECT var_name, var_value FROM {0} WHERE {1}".format(current_table, query_where))
rows = cursor.fetchall()
if rows:
for r in rows:
if r[0] in ("round_end_clients", "round_end_ghosts"):
i = round_vars[r[0]]
details[i] = r[1]
else:
i = list(round_vars[r[0]])
if i[0] not in details:
details[i[0]] = {}
details[i[0]][i[1]] = r[1]
keys = list(round_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
elif var_name in ("mecha_durand_created", "mecha_firefighter_created", "mecha_gygax_created", "mecha_honker_created", "mecha_odysseus_created", "mecha_phazon_created", "mecha_ripley_created", "mecha_sidewinder_created"):
mecha_vars ={"mecha_durand_created":"Durand", "mecha_firefighter_created":"APLU \"Firefighter\"", "mecha_gygax_created":"Gygax", "mecha_honker_created":"H.O.N.K", "mecha_odysseus_created":"Odysseus", "mecha_phazon_created":"Phazon", "mecha_ripley_created":"APLU \"Ripley\"", "mecha_sidewinder_created":"Sidewinder"}
details = {mecha_vars[var_name]:var_value}
del mecha_vars[var_name]
query_where = "round_id = {0} AND (".format(query_row[2])
for c, i in enumerate(mecha_vars):
if c:
query_where += " OR "
query_where += "var_name = \"{0}\"".format(i)
query_where += ")"
cursor.execute("SELECT var_name, var_value FROM {0} WHERE {1}".format(current_table, query_where))
rows = cursor.fetchall()
if rows:
for r in rows:
details[mecha_vars[r[0]]] = r[1]
keys = list(mecha_vars.keys())
keys.append(var_name)
multirows_completed += keys
return details
def pick_parsing(var_name, var_value, details, multirows_completed):
if var_name in text_keys:
return parse_text(details)
elif var_name in amount_keys:
return var_value
elif var_name in simple_tallies:
return parse_tally(details)
elif var_name in nested_tallies:
return parse_nested(var_name, details)
elif var_name in associatives:
return parse_associative(var_name, details)
elif var_name in special_cases:
return parse_special(var_name, var_value, details)
elif var_name in multirow:
return parse_multirow(var_name, var_value, details, multirows_completed)
else:
return False
if sys.version_info[0] < 3:
raise Exception("Python must be at least version 3 for this script.")
text_keys = ["religion_book", "religion_deity", "religion_name", "shuttle_fasttravel", "shuttle_manipulator", "shuttle_purchase", "shuttle_reason", "station_renames"]
amount_keys = ["admin_cookies_spawned", "cyborg_ais_created", "cyborg_frames_built", "cyborg_mmis_filled", "newscaster_newspapers_printed", "newscaster_stories", "nuclear_challenge_mode"]
simple_tallies = ["admin_secrets_fun_used", "admin_verb", "assembly_made", "brother_success", "cell_used", "changeling_power_purchase", "changeling_success", "chaplain_weapon", "chemical_reaction", "circuit_printed", "clockcult_scripture_recited", "contamination", "cult_runes_scribed", "engine_started", "event_admin_cancelled", "event_ran", "food_harvested", "food_made", "gun_fired", "handcuffs", "item_deconstructed", "item_printed", "jaunter", "lazarus_injector", "megafauna_kills", "mining_voucher_redeemed", "mobs_killed_mining", "object_crafted", "ore_mined", "pick_used_mining", "slime_cores_used", "surgeries_completed", "time_dilation_current", "traitor_random_uplink_items_gotten", "traitor_success", "voice_of_god", "warp_cube", "wisp_lantern", "wizard_spell_learned", "wizard_success", "zone_targeted"]
nested_tallies = ["admin_toggle", "cargo_imports", "changeling_objective", "changeling_powers", "cult_objective", "export_sold_cost", "hivelord_core", "item_used_for_combat", "job_preferences", "mining_equipment_bought", "played_url", "preferences_verb", "testmerged_prs", "traitor_objective", "traitor_uplink_items_bought", "vending_machine_usage", "wizard_objective", "wizard_spell_improved"]
associatives = ["colonies_dropped", "commendation", "high_research_level"]
special_cases = ["immortality_talisman", "newscaster_channels", "radio_usage", "shuttle_gib", "slime_babies_born", "slime_core_harvested"]
multirow = ["ahelp_close", "ahelp_icissue", "ahelp_reject", "ahelp_reopen", "ahelp_resolve", "ahelp_unresolved", "alert_comms_blue", "alert_comms_green", "alert_keycard_auth_bsa", "alert_keycard_auth_maint", "arcade_loss_hp_emagged", "arcade_loss_hp_normal", "arcade_loss_mana_emagged", "arcade_loss_mana_normal", "arcade_win_emagged", "arcade_win_normal", "cyborg_engineering", "cyborg_janitor", "cyborg_medical", "cyborg_miner", "cyborg_peacekeeper", "cyborg_security", "cyborg_service", "cyborg_standard", "escaped_human", "escaped_total", "mecha_durand_created", "mecha_firefighter_created", "mecha_gygax_created", "mecha_honker_created", "mecha_odysseus_created", "mecha_phazon_created", "mecha_ripley_created", "mecha_sidewinder_created", "round_end_clients", "round_end_ghosts", "survived_human", "survived_total"]
renames = {"ahelp_stats":["ahelp_close", "ahelp_icissue", "ahelp_reject", "ahelp_reopen", "ahelp_resolve", "ahelp_unresolved"], "ais_created":["cyborg_ais_created"], "arcade_results":["arcade_loss_hp_emagged", "arcade_loss_hp_normal", "arcade_loss_mana_emagged", "arcade_loss_mana_normal", "arcade_win_emagged", "arcade_win_normal"], "cyborg_modules":["cyborg_engineering", "cyborg_janitor", "cyborg_medical", "cyborg_miner", "cyborg_peacekeeper", "cyborg_security", "cyborg_service", "cyborg_standard"], "immortality_talisman_uses":["immortality_talisman"], "keycard_auths":["alert_keycard_auth_bsa", "alert_keycard_auth_maint"], "mechas_created":["mecha_durand_created", "mecha_firefighter_created", "mecha_gygax_created", "mecha_honker_created", "mecha_odysseus_created", "mecha_phazon_created", "mecha_ripley_created", "mecha_sidewinder_created"], "mmis_filled":["cyborg_mmis_filled"], "newspapers_printed":["newscaster_newspapers_printed"], "round_end_stats":["escaped_human", "escaped_total", "round_end_clients", "round_end_ghosts", "survived_human", "survived_total"], "security_level_changes":["alert_comms_blue", "alert_comms_green"]}
key_types = {"amount":["ais_created", "immortality_talisman_uses", "mmis_filled", "newspapers_printed", "admin_cookies_spawned", "cyborg_frames_built", "newscaster_stories", "nuclear_challenge_mode"],
"associative":["colonies_dropped", "commendation", "high_research_level"],
"nested tally":["admin_toggle", "arcade_results", "cargo_imports", "changeling_objective", "changeling_powers", "cult_objective", "export_sold_cost", "hivelord_core", "item_used_for_combat", "job_preferences", "keycard_auths", "mining_equipment_bought", "played_url", "preferences_verb", "round_end_stats", "testmerged_prs", "traitor_objective", "traitor_uplink_items_bought", "vending_machine_usage", "wizard_objective", "wizard_spell_improved"],
"tally":[ "admin_secrets_fun_used", "admin_verb", "ahelp_stats", "assembly_made", "brother_success", "cell_used", "changeling_power_purchase", "changeling_success", "chaplain_weapon", "chemical_reaction", "circuit_printed", "clockcult_scripture_recited", "contamination", "cult_runes_scribed", "cyborg_modules", "engine_started", "event_admin_cancelled", "event_ran", "food_harvested", "food_made", "gun_fired", "handcuffs", "item_deconstructed", "item_printed", "jaunter", "lazarus_injector", "mechas_created", "megafauna_kills", "mining_voucher_redeemed", "mobs_killed_mining", "object_crafted", "ore_mined", "pick_used_mining", "radio_usage", "security_level_changes", "shuttle_gib", "slime_babies_born", "slime_cores_used", "slime_core_harvested", "surgeries_completed", "time_dilation_current", "traitor_random_uplink_items_gotten", "traitor_success", "voice_of_god", "warp_cube", "wisp_lantern", "wizard_spell_learned", "wizard_success", "zone_targeted"],
"text":["shuttle_fasttravel", "shuttle_manipulator", "shuttle_purchase", "shuttle_reason", "newscaster_channels", "religion_book", "religion_deity", "religion_name", "station_renames"]}
multirows_completed = []
query_values = ""
current_round = 0
parser = argparse.ArgumentParser()
parser.add_argument("address", help="MySQL server address (use localhost for the current computer)")
parser.add_argument("username", help="MySQL login username")
parser.add_argument("password", help="MySQL login username")
parser.add_argument("database", help="Database name")
parser.add_argument("curtable", help="Name of the current feedback table (remember prefixes if you use them)")
parser.add_argument("newtable", help="Name of the new table to insert to, can't be same as the source table (remember prefixes)")
args = parser.parse_args()
db=MySQLdb.connect(host=args.address, user=args.username, passwd=args.password, db=args.database)
cursor=db.cursor()
cursor.execute("SELECT @@GLOBAL.version_comment")
db_version = "".join([x for x in cursor.fetchone()])
database_mysql = False
if 'MySQL' in db_version:
database_mysql = True
elif 'mariadb' not in db_version:
choice = input("Unable to determine database version installed, are you using MySQL? Type Yes or No and press enter...").lower()
if choice == "yes":
database_mysql = True
if database_mysql == True:
print("WARNING Database detected to be MySQL: tgstation no longer supports MySQL which has been superseded by MariaDB, a drop-in replacement.\nBefore running this script you will need to migrate to MariaDB.\nMigrating is very easy to do, for details on how see: https://mariadb.com/kb/en/library/upgrading-from-mysql-to-mariadb/")
input("Press enter to quit...")
quit()
current_table = args.curtable
new_table = args.newtable
cursor.execute("SELECT max(id), max(round_id) FROM {0}".format(current_table))
query_id = cursor.fetchone()
max_id = query_id[0]
max_round_id = query_id[1]
start_time = datetime.now()
print("Beginning conversion at {0}".format(start_time.strftime("%Y-%m-%d %H:%M:%S")))
try:
for current_id in range(max_id):
if current_id % 10000 == 0:
cur_time = datetime.now()
print("Reached row ID {0} Duration: {1}".format(current_id, cur_time - start_time))
cursor.execute("SELECT * FROM {0} WHERE id = {1}".format(current_table, current_id))
query_row = cursor.fetchone()
if not query_row:
continue
else:
if current_round != query_row[2] or current_round == max_round_id:
multirows_completed.clear()
if query_values:
query_values = query_values[:-1]
query_values += ';'
cursor.execute("INSERT INTO {0} (datetime, round_id, key_name, key_type, version, json) VALUES {1}".format(new_table, query_values))
query_values = ""
current_round = query_row[2]
if query_row[3] in multirows_completed:
continue
parsed_data = pick_parsing(query_row[3], query_row[4], query_row[5], multirows_completed)
if not parsed_data:
continue
json_data = {}
json_data["data"] = parsed_data
new_key = query_row[3]
for r in renames:
if new_key in renames[r]:
new_key = r
break
new_key_type = None
for t in key_types:
if new_key in key_types[t]:
new_key_type = t
break
dequoted_json = re.sub("\'", "\\'", json.dumps(json_data))
query_values += "('{0}',{1},'{2}','{3}',{4},'{5}'),".format(query_row[1], query_row[2], new_key, new_key_type, 1, dequoted_json)
end_time = datetime.now()
print("Conversion completed at {0}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
print("Script duration: {0}".format(end_time - start_time))
except Exception as e:
cursor.execute("SELECT round_id FROM {0} WHERE id = {1}".format(current_table, current_id-1))
query_round_id = cursor.fetchone()
end_time = datetime.now()
print("Error encountered on row ID {0} at {1}".format(current_id, datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
print("Note SQL insertion errors will be due to data from round ID {0}".format(query_round_id[0])) #since data is inserted when the round id changes on a new row
print("Script duration: {0}".format(end_time - start_time))
cursor.execute("TRUNCATE {0} ".format(new_table))
raise e
cursor.close()
Reference in New Issue View Git Blame Copy Permalink