def pre_start(self):
database = DictObject({
"type": "sqlite",
"file": "database.db",
"path": "/path/to/master/tyrbot/data/"
})
# database = DictObject({"type": "mysql",
# "name": "my-database",
# "username": "******",
# "password": "******",
# "host": "localhost"
# "port": 3306})
self.db2 = DB()
if database.type == "sqlite":
full_path = database.path + database.file
if os.path.isfile(full_path):
self.db2.connect_sqlite(full_path)
else:
raise Exception(f"File '{full_path}' does not exist")
elif database.type == "mysql":
self.db2.connect_mysql(database.host, database.port,
database.username, database.password,
database.name)
else:
raise Exception("Unknown database type '%s'" % database.type)
self.share_notes()
self.share_news()
self.share_quotes()
self.share_timers()
self.share_alts()
def prepare_DB():
db = DB(':memory:',
pipe_path = os.path.dirname(os.path.abspath(kwargs['dependence_file'])),
apps = {self.appname: self},
parameters = self.parameters.copy(),
dependencies = self.dependencies)
db.format()
db.mkOSSuploadSH()
return db
def test_handle_extended_like(self):
sql = "SELECT * FROM table WHERE param1 = ?"
params = ["param1"]
db = DB()
new_sql, new_params = db.handle_extended_like(sql, params)
self.assertEqual(1, len(new_params))
self.assertEqual("param1", new_params[0])
self.assertEqual("SELECT * FROM table WHERE param1 = ?", new_sql)
def pre_start(self):
self.bot_name = "bot_name"
database = DictObject({
"type": "mysql",
"name": "database_name",
"username": "******",
"password": "******",
"host": "localhost",
"port": 3306
})
self.db2 = DB()
self.db2.connect_mysql(database.host, database.port, database.username,
database.password, database.name)
def __init__(self,
namespace,
redis_host='192.168.11.108',
redis_port=6379,
redis_db=0,
db_host='192.168.11.195',
db_port=5432,
db_user='******',
db_password='******',
db_base='test'):
BasePipeline.__init__(self, namespace)
try:
redis_namespace = "%s:%s" % (namespace, "temp")
self._temp_redis_dict = RedisDict(redis_namespace,
host=redis_host,
port=redis_port,
db=redis_db)
self._db = DB(host=db_host,
port=db_port,
user=db_user,
password=db_password,
database=db_base)
except RedisError, e:
self.logger.error("init redis dict failed error:%s" % e)
raise e
def setLastRun(self):
now = DB.getTimestamp()
self.getDB().updateDocByID(self.id, {
"status": "finished",
"last_run": now
})
self.last_run = now
return True
def __init__(self, namespace, db_host="127.0.0.1", db_port=5432,
db_user="******", db_password="******", db_base="swift"):
"""初始化DealItemPipeline
主要是链接数据库
"""
BasePipeline.__init__(self, namespace)
self.logger.debug("init nuomi DealItemPipeline")
try:
self.item_db = DB(host=db_host, port=db_port, user=db_user,
password=db_password, database=db_base)
except DBError, e:
self.logger.error("db error %s" % e)
def buildDB(self, overwrite):
db = DB(
db_path = os.path.join(self.proj_path, 'snap.db'),
pipe_path = self.pipe_path,
apps = self.apps,
parameters = self.parameters,
dependencies = self.dependencies,
overwrite = overwrite)
db.format()
db.mkOssSyncSH()
db.add()
def connect_event(self, event_type, event_data):
self.db2 = DB()
# Optional: the name of the bot character that the budabot/bebot ran as
# if the bot name is the same, then you can leave this blank, otherwise you must fill in this value
bot_name = ""
# Optional: the org_id of the org
# the bot will use the org_id of the primary conn if this is not set, which is usually correct
org_id = 0
# if your budabot/bebot used mysql, then uncomment the second line below and fill out the appropriate values
# otherwise, if your budabot used sqlite, then uncomment the first line below and enter the path to the sqlite db file
# do NOT uncomment both of them
# REQUIRED: uncomment ONE of these two lines below
# self.db2.connect_sqlite("./data/budabot.db")
# self.db2.connect_mysql(host="localhost", port=3306, username="", password="", database_name="")
self.bot_name = bot_name.lower(
) if bot_name else self.bot.get_primary_conn().get_char_name().lower()
self.org_id = org_id if org_id else self.bot.get_primary_conn().org_id
self.dimension = self.bot.dimension
def test_handle_extended_like2(self):
sql = "SELECT * FROM table WHERE param0 = ? AND param1 <EXTENDED_LIKE=1> ? AND param2 = ? AND param3 <EXTENDED_LIKE=3> ? AND param4 = ?"
params = [
"param0", "search1 search2 search3", "param2", "param31 param32",
"param4"
]
db = DB()
new_sql, new_params = db.handle_extended_like(sql, params)
self.assertEqual(8, len(new_params))
self.assertEqual("param0", new_params[0])
self.assertEqual("%search1%", new_params[1])
self.assertEqual("%search2%", new_params[2])
self.assertEqual("%search3%", new_params[3])
self.assertEqual("param2", new_params[4])
self.assertEqual("%param31%", new_params[5])
self.assertEqual("%param32%", new_params[6])
self.assertEqual("param4", new_params[7])
self.assertEqual(
"SELECT * FROM table WHERE param0 = ? "
"AND (param1 LIKE ? AND param1 LIKE ? AND param1 LIKE ?) "
"AND param2 = ? "
"AND (param3 LIKE ? AND param3 LIKE ?) "
"AND param4 = ?", new_sql)
def __init__(self,
namespace,
db_host="127.0.0.1",
db_port=5432,
db_user="******",
db_password="******",
db_base="swift"):
"""init method
Args:
ignore
"""
BasePipeline.__init__(self, namespace)
self.logger.debug("init hotel code item pipeline")
try:
self.item_db = DB(host=db_host,
port=db_port,
user=db_user,
password=db_password,
database=db_base)
except DBError, e:
self.logger.error("db error %s" % e)
def getDB(self): return DB(self.col_name)
class MigrateController:
DATABASE_TYPE_MYSQL = "mysql"
DATABASE_TYPE_SQLITE = "sqlite"
def __init__(self):
self.logger = Logger(__name__)
def inject(self, registry):
self.bot = registry.get_instance("bot")
self.db = registry.get_instance("db")
self.character_service = registry.get_instance("character_service")
self.alts_service = registry.get_instance("alts_service")
self.pork_service = registry.get_instance("pork_service")
@event(event_type="connect",
description="Configure migration controller",
is_system=True)
def connect_event(self, event_type, event_data):
self.db2 = DB()
# Optional: the name of the bot character that the budabot/bebot ran as
# if the bot name is the same, then you can leave this blank, otherwise you must fill in this value
bot_name = ""
# Optional: the org_id of the org
# the bot will use the org_id of the primary conn if this is not set, which is usually correct
org_id = 0
# if your budabot/bebot used mysql, then uncomment the second line below and fill out the appropriate values
# otherwise, if your budabot used sqlite, then uncomment the first line below and enter the path to the sqlite db file
# do NOT uncomment both of them
# REQUIRED: uncomment ONE of these two lines below
# self.db2.connect_sqlite("./data/budabot.db")
# self.db2.connect_mysql(host="localhost", port=3306, username="", password="", database_name="")
self.bot_name = bot_name.lower(
) if bot_name else self.bot.get_primary_conn().get_char_name().lower()
self.org_id = org_id if org_id else self.bot.get_primary_conn().org_id
self.dimension = self.bot.dimension
# TODO in each command, check if db has been initialized properly first
@command(command="bebot",
params=[Const("migrate"), Const("alts")],
access_level="superadmin",
description="Migrate alts from a Bebot database")
def migrate_bebot_alts_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT a.alt, u1.char_id AS alt_char_id, a.main, u2.char_id AS main_char_id "
"FROM alts a "
f"LEFT JOIN {self.bot_name}_users u1 ON a.alt = u1.nickname "
f"LEFT JOIN {self.bot_name}_users u2 ON a.main = u2.nickname "
"WHERE a.confirmed = 1 "
"ORDER BY main, alt")
current_main = None
current_main_id = None
count_inactive = 0
count_success = 0
count_failure = 0
request.reply("Processing %s alt records..." % len(data))
for row in data:
if row.main != current_main:
current_main = row.main
current_main_id = self.resolve_to_char_id(
row.main, row.main_char_id)
if not current_main_id:
self.logger.warning(
f"Could not resolve main char '{current_main}' to char id")
count_inactive += 1
continue
alt_id = self.resolve_to_char_id(row.alt, row.alt_char_id)
if not alt_id:
self.logger.warning(
f"Could not resolve alt char '{row.alt}' to char id")
count_inactive += 1
continue
msg, result = self.alts_service.add_alt(current_main_id, alt_id)
if result:
count_success += 1
else:
count_failure += 1
return f"<highlight>{count_success}</highlight> alts were migrated successfully, " \
f"<highlight>{count_failure}</highlight> alts failed to be added, " \
f"and <highlight>{count_inactive}</highlight> chars were inactive and could not be resolved to char ids."
@command(command="bebot",
params=[Const("migrate"), Const("logon")],
access_level="superadmin",
description="Migrate logon messages from a Bebot database")
def migrate_bebot_logon_cmd(self, request, _1, _2):
data = self.db2.query(f"SELECT message, id FROM {self.bot_name}_logon")
request.reply("Processing %s logon records..." % len(data))
for row in data:
self.db.exec(
"INSERT INTO log_messages (char_id, logon) VALUES(?, ?)",
[row.id, row.message])
return f"Successfully migrated <highlight>%d</highlight> logon messages." % len(
data)
@command(command="budabot",
params=[Const("migrate"), Const("admins")],
access_level="superadmin",
description="Migrate admins from a Budabot database")
def migrate_budabot_admins_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT a.name, p.charid AS char_id, CASE WHEN adminlevel = 4 THEN 'admin' WHEN adminlevel = 3 THEN 'moderator' END AS access_level "
f"FROM admin_{self.bot_name} a LEFT JOIN players p ON (a.name = p.name AND p.dimension = ?) "
"WHERE p.charid > 0", [self.dimension])
with self.db.transaction():
for row in data:
char_id = self.resolve_to_char_id(row.name, row.char_id)
if char_id and row.access_level:
self.db.exec("DELETE FROM admin WHERE char_id = ?",
[char_id])
self.db.exec(
"INSERT INTO admin (char_id, access_level) VALUES (?, ?)",
[char_id, row.access_level])
return f"Successfully migrated <highlight>%d</highlight> admin characters." % len(
data)
@command(command="budabot",
params=[Const("migrate"), Const("banlist")],
access_level="superadmin",
description="Migrate ban list from a Budabot database")
def migrate_budabot_banlist_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT b.charid AS char_id, p.charid AS sender_char_id, time AS created_at, banend AS finished_at, reason "
f"FROM banlist_{self.bot_name} b JOIN players p ON (b.admin = p.name AND p.dimension = ?)"
"WHERE p.charid > 0", [self.dimension])
with self.db.transaction():
for row in data:
self.db.exec("DELETE FROM ban_list WHERE char_id = ?",
[row.char_id])
self.db.exec(
"INSERT INTO ban_list (char_id, sender_char_id, created_at, finished_at, reason, ended_early) VALUES (?, ?, ?, ?, ?, ?)",
[
row.char_id, row.sender_char_id, row.created_at,
row.finished_at, row.reason, 0
])
return f"Successfully migrated <highlight>%d</highlight> banned characters." % len(
data)
@command(command="budabot",
params=[Const("migrate"), Const("alts")],
access_level="superadmin",
description="Migrate alts from a Budabot database")
def migrate_budabot_alts_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT p1.charid AS main_char_id, p2.charid AS alt_char_id "
"FROM alts a JOIN players p1 ON (p1.name = a.main AND p1.dimension = ?) "
"JOIN players p2 ON (p2.name = a.alt AND p2.dimension = ?)"
"WHERE validated = 1 AND p1.charid > 0 AND p2.charid > 0 ORDER BY a.main ASC",
[self.dimension, self.dimension])
with self.db.transaction():
current_main = 0
group_id = 0
for row in data:
if row.main_char_id != current_main:
current_main = row.main_char_id
group_id = self.db.query_single(
"SELECT (COALESCE(MAX(group_id), 0) + 1) AS next_group_id FROM alts"
).next_group_id
self.db.exec("DELETE FROM alts WHERE char_id = ?",
[row.main_char_id])
self.db.exec(
"INSERT INTO alts (char_id, group_id, status) VALUES (?, ?, ?)",
[row.main_char_id, group_id, AltsService.MAIN])
self.db.exec("DELETE FROM alts WHERE char_id = ?",
[row.alt_char_id])
self.db.exec(
"INSERT INTO alts (char_id, group_id, status) VALUES (?, ?, ?)",
[row.alt_char_id, group_id, AltsService.CONFIRMED])
return f"Successfully migrated <highlight>%d</highlight> alt characters." % len(
data)
@command(command="budabot",
params=[Const("migrate"), Const("members")],
access_level="superadmin",
description="Migrate members from a Budabot database")
def migrate_budabot_members_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT m.name AS sender, p.charid AS char_id, m.autoinv AS auto_invite "
f"FROM members_{self.bot_name} m JOIN players p ON (m.name = p.name AND p.dimension = ?) "
"WHERE p.charid > 0", [self.dimension])
num = 0
for row in data:
char_id = self.resolve_to_char_id(row.sender, row.char_id)
if char_id:
num += 1
self.db.exec("DELETE FROM member WHERE char_id = ?",
[row.char_id])
self.db.exec(
"INSERT INTO member (char_id, auto_invite) VALUES (?, ?)",
[row.char_id, row.auto_invite])
return f"Successfully migrated <highlight>{num}</highlight> members."
@command(command="budabot",
params=[Const("migrate"), Const("quotes")],
access_level="superadmin",
description="Migrate quotes from a Budabot database")
def migrate_budabot_quotes_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT q.poster, p.charid AS char_id, q.id, q.msg, q.dt "
"FROM quote q LEFT JOIN players p ON (q.poster = p.name AND p.dimension = ?)",
[self.dimension])
count_inactive = 0
request.reply("Processing %s quote records..." % len(data))
for row in data:
char_id = self.resolve_to_char_id(row.poster, row.char_id)
if not char_id:
char_id = -1
count_inactive += 1
self.db.exec("DELETE FROM quote WHERE id = ?", [row.id])
self.db.exec(
"INSERT INTO quote (id, char_id, created_at, content) VALUES (?, ?, ?, ?)",
[row.id, char_id, row.dt, row.msg])
return f"Quotes successfully migrated. <highlight>{count_inactive}</highlight> posters were inactive and could not be resolved to char ids."
@command(command="budabot",
params=[Const("migrate"), Const("log_messages")],
access_level="superadmin",
description="Migrate log messages from a Budabot database")
def migrate_budabot_log_messages_cmd(self, request, _1, _2):
data = self.db2.query(
f"SELECT p2.charid AS char_id, p1.sender, p1.name, p1.value "
f"FROM preferences_{self.bot_name} p1 LEFT JOIN players p2 ON (p1.sender = p2.name AND p2.dimension = ?) "
"WHERE p1.name = 'logon_msg' OR p1.name = 'logoff_msg'",
[self.dimension])
count_inactive = 0
count_logon = 0
count_logoff = 0
request.reply("Processing %s log messages records..." % len(data))
for row in data:
char_id = self.resolve_to_char_id(row.sender, row.char_id)
if not char_id:
count_inactive += 1
else:
existing = self.db.query_single(
"SELECT 1 FROM log_messages WHERE char_id = ?", [char_id])
if not existing:
self.db.exec(
"INSERT INTO log_messages (char_id, logon, logoff) VALUES (?, NULL, NULL)",
[char_id])
if row.name == 'logon_msg' and row.value:
self.db.exec(
"UPDATE log_messages SET logon = ? WHERE char_id = ?",
[row.value, char_id])
count_logon += 1
elif row.name == 'logoff_msg' and row.value:
self.db.exec(
"UPDATE log_messages SET logoff = ? WHERE char_id = ?",
[row.value, char_id])
count_logoff += 1
return f"<highlight>{count_logon}</highlight> logon and <highlight>{count_logoff}</highlight> logoff messages successfully migrated. " \
f"<highlight>{count_inactive}</highlight> messages were from inactive characters that could not be resolved to char ids."
@command(command="budabot",
params=[Const("migrate"), Const("name_history")],
access_level="superadmin",
description="Migrate name history from a Budabot database")
def migrate_budabot_name_history_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT charid AS char_id, name, dt AS created_at FROM name_history"
)
request.reply(
"Processing %s name history records. This may take some time..." %
len(data))
with self.db.transaction():
for row in data:
self.db.exec(
"DELETE FROM name_history WHERE char_id = ? AND name = ?",
[row.char_id, row.name])
self.db.exec(
"INSERT INTO name_history (char_id, name, created_at) VALUES (?, ?, ?)",
[row.char_id, row.name, row.created_at])
return f"Successfully migrated <highlight>%d</highlight> name history records." % len(
data)
@command(command="budabot",
params=[Const("migrate"), Const("news")],
access_level="superadmin",
description="Migrate news from a Budabot database")
def migrate_budabot_news_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT n.name AS poster, p.charid AS char_id, news, sticky, time AS created_at, deleted AS deleted_at "
"FROM news n JOIN players p ON (n.name = p.name AND p.dimension = ?) WHERE p.charid > 0",
[self.dimension])
for row in data:
char_id = self.resolve_to_char_id(row.poster, row.char_id)
if not char_id:
char_id = -1
self.db.exec("DELETE FROM news WHERE char_id = ? AND news = ?",
[char_id, row.news])
self.db.exec(
"INSERT INTO news (char_id, news, sticky, created_at, deleted_at) VALUES (?, ?, ?, ?, ?)",
[
char_id, row.news, row.sticky, row.created_at,
row.deleted_at
])
return f"Successfully migrated <highlight>%d</highlight> news records." % len(
data)
@command(command="budabot",
params=[Const("migrate"), Const("notes")],
access_level="superadmin",
description="Migrate notes from a Budabot database")
def migrate_budabot_notes_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT n.added_by AS sender, p.charid AS char_id, n.note, n.dt AS created_at "
"FROM notes n JOIN players p ON (p.name = n.added_by AND p.dimension = ?) WHERE p.charid > 0",
[self.dimension])
num = 0
for row in data:
char_id = self.resolve_to_char_id(row.sender, row.char_id)
if char_id:
num += 1
self.db.exec(
"DELETE FROM notes WHERE char_id = ? AND note = ?",
[char_id, row.note])
self.db.exec(
"INSERT INTO notes (char_id, note, created_at) VALUES (?, ?, ?)",
[char_id, row.note, row.created_at])
return f"Successfully migrated <highlight>{num}</highlight> note records."
@command(command="budabot",
params=[Const("migrate"), Const("last_seen")],
access_level="superadmin",
description="Migrate last_seen data from a Budabot database")
def migrate_budabot_last_seen_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT o.name AS sender, p.charid AS char_id, logged_off AS last_seen "
f"FROM org_members_{self.bot_name} o JOIN players p ON (o.name = p.name AND p.dimension = ?) "
"WHERE p.charid > 0", [self.dimension])
num = 0
with self.db.transaction():
for row in data:
char_id = self.resolve_to_char_id(row.sender, row.char_id)
if char_id:
num += 1
self.db.exec("DELETE FROM last_seen WHERE char_id = ?",
[char_id])
self.db.exec(
"INSERT INTO last_seen (char_id, dt) VALUES (?, ?)",
[char_id, row.last_seen])
return f"Successfully migrated <highlight>{num}</highlight> last seen records."
@command(command="budabot",
params=[Const("migrate"), Const("cloak_status")],
access_level="superadmin",
description="Migrate cloak status records from a Budabot database"
)
def migrate_budabot_cloak_status_cmd(self, request, _1, _2):
if not self.org_id:
return "Could not migrate cloak status record since org id is not set."
data = self.db2.query(
"SELECT o.player AS name, p.charid AS char_id, action, time AS created_at "
f"FROM org_city_{self.bot_name} o JOIN players p ON (o.player = p.name AND p.dimension = ?) "
"WHERE p.charid > 0", [self.dimension])
num = 0
with self.db.transaction():
self.db.exec("DELETE FROM cloak_status WHERE org_id = ?",
[self.org_id])
for row in data:
char_id = self.resolve_to_char_id(row.name, row.char_id)
if char_id:
num += 1
self.db.exec(
"INSERT INTO cloak_status (char_id, action, created_at, org_id) VALUES (?, ?, ?, ?)",
[char_id, row.action, row.created_at, self.org_id])
return f"Successfully migrated <highlight>{num}</highlight> cloak status records."
@command(command="budabot",
params=[Const("migrate"), Const("org_activity")],
access_level="superadmin",
description="Migrate org activity records from a Budabot database"
)
def migrate_budabot_org_activity_cmd(self, request, _1, _2):
if not self.org_id:
return "Could not migrate cloak status record since org id is not set."
request.reply("Processing records. This may take some time...")
data = self.db2.query(
"SELECT o.actor AS actor_name, p1.charid AS actor_char_id, o.actee AS actee_name, p2.charid AS actee_char_id, action, time AS created_at "
"FROM org_history o JOIN players p1 ON (o.actor = p1.name AND p1.dimension = ?) JOIN players p2 ON (o.actee = p2.name AND p2.dimension = ?) "
"WHERE p1.charid > 0 AND p2.charid > 0",
[self.dimension, self.dimension])
num = 0
with self.db.transaction():
self.db.exec("DELETE FROM org_activity WHERE org_id = ?",
[self.org_id])
for row in data:
actor_char_id = self.resolve_to_char_id(
row.actor_name, row.actor_char_id)
actee_char_id = self.resolve_to_char_id(
row.actee_name, row.actee_char_id)
if actor_char_id and actee_char_id:
num += 1
self.db.exec(
"INSERT INTO org_activity (actor_char_id, actee_char_id, action, created_at, org_id) VALUES (?, ?, ?, ?, ?)",
[
actor_char_id, actee_char_id, row.action,
row.created_at, self.org_id
])
return f"Successfully migrated <highlight>{num}</highlight> org activity records."
@command(
command="budabot",
params=[Const("migrate"), Const("players")],
access_level="superadmin",
description="Migrate character info records from a Budabot database")
def migrate_budabot_player_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT * FROM players WHERE charid > 0 AND dimension = ?",
[self.dimension])
request.reply("Processing %s records. This may take some time..." %
len(data))
num = 0
with self.db.transaction():
for row in data:
if row.charid:
num += 1
self.db.exec("DELETE FROM player WHERE char_id = ?",
[row.charid])
self.db.exec(
"INSERT INTO player (ai_level, ai_rank, breed, char_id, dimension, faction, first_name, gender, head_id, last_name, "
"last_updated, level, name, org_id, org_name, org_rank_id, org_rank_name, profession, profession_title, pvp_rating, pvp_title, source) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
[
row.ai_level, row.ai_rank, row.breed, row.charid,
row.dimension, row.faction, row.firstname,
row.gender, row.head_id if row.head_id else 0,
row.lastname, row.last_update, row.level, row.name,
row.guild_id, row.guild, row.guild_rank_id or 0,
row.guild_rank, row.profession, row.prof_title,
row.pvp_rating if row.pvp_rating else 0,
row.pvp_title if row.pvp_title else "", row.source
])
# maybe this is needed also? self.db.exec("DELETE FROM player WHERE char_id = 4294967295")
return f"Successfully migrated <highlight>{num}</highlight> character info records."
def resolve_to_char_id(self, name, char_id):
if char_id and char_id > 0:
return char_id
char_id = self.character_service.resolve_char_to_id(name)
if char_id:
return char_id
char_info = self.pork_service.get_character_info(name)
if char_info:
return char_info.char_id
return None
def __init__(self, app):
Base.__init__(self, app)
self.db = DB(app)
self.bot_name = bot_name
def format_sql(self, sql, params=None):
sql = sql.replace("<myname>", self.bot_name)
return super().format_sql(sql, params)
# IMPORTANT: specify a bot name before running this script
bot_name = ""
old_db = BudabotDB(bot_name)
# IMPORTANT: connect old_db (budabot) using sqlite or mysql (uncomment ONE)
#old_db.connect_sqlite("./data/budabot.db")
#old_db.connect_mysql(host="localhost", username="", password="", database_name="")
new_db = DB()
# IMPORTANT: connect new_db (tyrbot) using sqlite or mysql (uncomment ONE)
#new_db.connect_sqlite("./data/database.db")
#new_db.connect_mysql(host="localhost", username="", password="", database_name="")
if not old_db.bot_name:
print("Error! Specify bot name")
exit(1)
if not old_db.get_type():
print("Error! Specify connection method for old_db")
exit(1)
if not new_db.get_type():
print("Error! Specify connection method for new_db")
exit(1)
import dash
import pdb
from dash.dependencies import Input, Output
import dash_core_components as dcc
import dash_html_components as html
from core.db import DB
from core.models import Instance
from core.formats import format_instance_tbl, format_history_price_tbl
db = DB(':memory:', None, None, {}, None, False)
db.mkInstance()
db.session.commit()
instances = db.session.query(Instance)
app = dash.Dash()
app.scripts.config.serve_locally = True
app.layout = html.Div([
dcc.Dropdown(
id='instance-type',
options = [{'label': i.name, 'value': i.name} for i in instances],
value = ['ecs.sn2.large'],
multi = True
),
dcc.Slider(
id='day',
min=0,
max=29,
step=1,
marks={i: i for i in range(30)},
value=0,
class MigrateController:
def __init__(self):
self.logger = Logger(__name__)
def inject(self, registry):
self.db = registry.get_instance("db")
self.character_service = registry.get_instance("character_service")
self.alts_service = registry.get_instance("alts_service")
self.pork_service = registry.get_instance("pork_service")
def pre_start(self):
self.bot_name = "bot_name"
database = DictObject({
"type": "mysql",
"name": "database_name",
"username": "******",
"password": "******",
"host": "localhost",
"port": 3306
})
self.db2 = DB()
self.db2.connect_mysql(database.host, database.port, database.username,
database.password, database.name)
@command(command="bebot",
params=[Const("migrate"), Const("alts")],
access_level="superadmin",
description="Migrate alts from a Bebot database")
def migrate_alts_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT a.alt, u1.char_id AS alt_char_id, a.main, u2.char_id AS main_char_id "
"FROM alts a "
f"LEFT JOIN {self.bot_name}_users u1 ON a.alt = u1.nickname "
f"LEFT JOIN {self.bot_name}_users u2 ON a.main = u2.nickname "
"WHERE a.confirmed = 1 "
"ORDER BY main, alt")
current_main = None
current_main_id = None
count_inactive = 0
count_success = 0
count_failure = 0
request.reply("Processing %s alt records..." % len(data))
for row in data:
if row.main != current_main:
current_main = row.main
current_main_id = self.resolve_to_char_id(
row.main, row.main_char_id)
if not current_main_id:
self.logger.warning(
f"Could not resolve main char '{current_main}' to char id")
count_inactive += 1
continue
alt_id = self.resolve_to_char_id(row.alt, row.alt_char_id)
if not alt_id:
self.logger.warning(
f"Could not resolve alt char '{row.alt}' to char id")
count_inactive += 1
continue
msg, result = self.alts_service.add_alt(current_main_id, alt_id)
if result:
count_success += 1
else:
count_failure += 1
return f"<highlight>{count_success}</highlight> alts were migrated successfully, " \
f"<highlight>{count_failure}</highlight> alts failed to be added, " \
f"and <highlight>{count_inactive}</highlight> chars were inactive and could not be resolved to char ids."
@command(command="bebot",
params=[Const("migrate"), Const("logon")],
access_level="superadmin",
description="Migrate logon messages from a Bebot database")
def migrate_logon_cmd(self, request, _1, _2):
data = self.db2.query(f"SELECT message, id FROM {self.bot_name}_logon")
request.reply("Processing %s logon records..." % len(data))
for row in data:
self.db.exec(
"INSERT INTO log_messages (char_id, logon) VALUES(?, ?)",
[row.id, row.message])
return f"Logon messages migrated successfully."
@command(command="budabot",
params=[Const("migrate"), Const("quotes")],
access_level="superadmin",
description="Migrate quotes from a Bebot database")
def migrate_quotes_cmd(self, request, _1, _2):
data = self.db2.query(
"SELECT p.charid AS char_id, q.id, q.msg, q.dt FROM quote q LEFT JOIN players p ON q.poster = p.name"
)
count_inactive = 0
request.reply("Processing %s quote records..." % len(data))
for row in data:
char_id = self.resolve_to_char_id(row.poster, row.char_id)
if not char_id:
char_id = -1
count_inactive += 1
self.db.exec(
"INSERT INTO quote (id, char_id, created_at, content) VALUES (?, ?, ?, ?)",
[row.id, char_id, row.dt, row.msg])
return f"Quotes successfully migrated. <highlight>{count_inactive}</highlight> posters were inactive and could not be resolved to char ids."
def resolve_to_char_id(self, name, char_id):
if char_id and char_id > 0:
return char_id
char_id = self.character_service.resolve_char_to_id(name)
if char_id:
return char_id
char_info = self.pork_service.get_character_info(name)
if char_info:
return char_info.char_id
return None
class TableShareController:
def inject(self, registry):
self.db = registry.get_instance("db")
def pre_start(self):
database = DictObject({
"type": "sqlite",
"file": "database.db",
"path": "/path/to/master/tyrbot/data/"
})
# database = DictObject({"type": "mysql",
# "name": "my-database",
# "username": "******",
# "password": "******",
# "host": "localhost"
# "port": 3306})
self.db2 = DB()
if database.type == "sqlite":
full_path = database.path + database.file
if os.path.isfile(full_path):
self.db2.connect_sqlite(full_path)
else:
raise Exception(f"File '{full_path}' does not exist")
elif database.type == "mysql":
self.db2.connect_mysql(database.host, database.port,
database.username, database.password,
database.name)
else:
raise Exception("Unknown database type '%s'" % database.type)
self.share_notes()
self.share_news()
self.share_quotes()
self.share_timers()
self.share_alts()
@timerevent(budatime="30m",
description="Copy alts from master bot",
run_at_startup=True)
def connect_event(self, event_type, event_data):
self.sync_alts()
def sync_alts(self):
self.db.exec("DELETE FROM alts")
for alt in self.db2.query("SELECT * FROM alts"):
self.db.exec(
"INSERT INTO alts (char_id, group_id, status) VALUES (?, ?, ?)",
[alt.char_id, alt.group_id, alt.status])
def share_notes(self):
self.add_db_to_instances(["notes_controller"])
def share_news(self):
self.add_db_to_instances(["news_controller"])
def share_quotes(self):
self.add_db_to_instances(["quote_controller"])
def share_timers(self):
self.add_db_to_instances(["timer_controller"])
def share_alts(self):
self.add_db_to_instances(["alts_service"])
def add_db_to_instances(self, instances):
for name in instances:
inst = Registry.get_instance(name)
inst.db = self.db2
def test_sqlite_transaction_commit_using_with(self):
self.delete_db_file()
db = DB()
db.connect_sqlite(self.DB_FILE)
db.exec("CREATE TABLE test1 (name VARCHAR, value VARCHAR)")
with db.transaction():
db.exec("INSERT INTO test1 (name, value) VALUES (?, ?)",
["tyrbot", "1"])
db.get_connection().close()
db.connect_sqlite(self.DB_FILE)
self.assertEqual([{
'name': 'tyrbot',
'value': '1'
}], db.query("SELECT * FROM test1"))
db.get_connection().close()
self.delete_db_file()
def test_sqlite_transaction_rollback(self):
self.delete_db_file()
db = DB()
db.connect_sqlite(self.DB_FILE)
db.exec("CREATE TABLE test1 (name VARCHAR, value VARCHAR)")
db.begin_transaction()
db.exec("INSERT INTO test1 (name, value) VALUES (?, ?)",
["tyrbot", "1"])
db.rollback_transaction()
db.get_connection().close()
db.connect_sqlite(self.DB_FILE)
self.assertEqual([], db.query("SELECT * FROM test1"))
db.get_connection().close()
self.delete_db_file()
import json
from core.db import DB
from urllib.parse import urlencode
from conf.py_global import CURL
'''
create table source_zto (id int(10) not null auto_increment, start varchar(50) not null comment '始发地', end varchar(50) not null comment '目的地', weightPrice varchar(10) not null comment '首重价格', addPrice varchar(10) not null comment '续重价格', primary key(id))engine=innodb default charset=utf8;
'''
db = DB('192.168.1.34', 'kfz', 'kongfz.com', 'express')
province = [
'黑龙江', '天津', '内蒙古', '上海', '河南', '湖南', '广西', '重庆', '福建', '江西', '青海', '西藏',
'陕西', '甘肃', '山东', '浙江', '湖北', '新疆', '海南', '北京', '辽宁', '安徽', '云南', '山西',
'广东', '宁夏', '河北', '贵州', '江苏', '吉林', '四川'
]
for i1 in range(len(province)):
for i2 in range(len(province)):
post_data = {
'code': 'undefined',
'dispProv': province[i2],
'dispCity': 'x',
'sendProv': province[i1],
'sendCity': 'x',
'weight': '2',
'_': '1482485209289'
}
data = CURL.get('http://www.zto.com/GuestService/GetPrice?' +
urlencode(post_data)).decode()
json_data = json.loads(data)
weight_price = json_data['Data']['firstMoney']
add_price = float(json_data['Data']['price']) - float(
def test_sqlite_transaction_rollback_using_with(self):
self.delete_db_file()
db = DB()
db.connect_sqlite(self.DB_FILE)
db.exec("CREATE TABLE test1 (name VARCHAR, value VARCHAR)")
try:
with db.transaction():
db.exec("INSERT INTO test1 (name, value) VALUES (?, ?)",
["tyrbot", "1"])
raise Exception("Testing")
except Exception:
pass
db.get_connection().close()
db.connect_sqlite(self.DB_FILE)
self.assertEqual([], db.query("SELECT * FROM test1"))
db.get_connection().close()
self.delete_db_file()
