From 1af05ee74d0b2ec4e132617ab71bedd30fbfe753 Mon Sep 17 00:00:00 2001 From: gumi Date: Wed, 22 Jan 2020 14:55:34 -0500 Subject: [frob] add ROCKSDB as export target much slower than dumping to a .sql file and then importing in mariadb, but whatever --- server/frob/accreg.ts | 104 ++++++++++++++++++++++++++++ server/frob/char.ts | 70 ++++++++++++++++++- server/frob/index.ts | 84 +++++++++++++++++++---- server/frob/login.ts | 138 +++++++++++++++++++++++++++++++++++++ server/frob/party.ts | 128 ++++++++++++++++++++++++++++++++++ server/frob/sql.ts | 182 +++++++++++++++++++++++++++++++++++++++++++++++++ server/frob/storage.ts | 29 +++++++- 7 files changed, 718 insertions(+), 17 deletions(-) create mode 100644 server/frob/accreg.ts create mode 100644 server/frob/login.ts create mode 100644 server/frob/party.ts create mode 100644 server/frob/sql.ts diff --git a/server/frob/accreg.ts b/server/frob/accreg.ts new file mode 100644 index 0000000..d951d77 --- /dev/null +++ b/server/frob/accreg.ts @@ -0,0 +1,104 @@ +class AccregParser { + private reg_line = + "^" + + "(?[0-9]+)\t" + + "(?((?[^,]+),(?[-0-9]+) )*)" + // some chars have negative variables (overflows) + "$"; + private vars_line = "(?[^,]+),(?[-0-9]+) "; + private reg_regex: RegExp; + private reg_regex_vars: RegExp; + private encoder; + + constructor () { + this.reg_regex = new RegExp(this.reg_line); + this.reg_regex_vars = new RegExp(this.vars_line, "g"); + this.encoder = new TextEncoder(); + } + + private parseLine (line: string) { + const match = this.reg_regex.exec(line); + + if (!(match instanceof Object) || !Reflect.has(match, "groups")) { + console.error("\nline does not match the reg regex:", line); + throw new SyntaxError(); + } + + const groups = (match as any).groups; + let variables = []; + + if (groups.variables.length > 1) { + let match_vars = this.reg_regex_vars.exec(groups.variables); + + while (match_vars !== null) { + variables.push((match_vars as any).groups); + match_vars = this.reg_regex_vars.exec(groups.variables); + } + } + + groups.variables = variables; + + Deno.write(Deno.stdout.rid, this.encoder.encode(`\r⌛ processing variables of account ${groups.account_id}...`)); + return groups; + } + + public async * readDB () { + const decoder = new TextDecoder("utf-8"); + console.info("\r \nwalking through accreg.txt..."); + const file = await Deno.open("world/save/accreg.txt"); + const buf = new Uint8Array(1024); + let accumulator = ""; + + while (true) { + const nread = await Deno.read(file.rid, buf); + + if (nread === Deno.EOF) { + break; + } + + const str = decoder.decode(buf); + + if (nread < 1024) { + for (let c of str) { + if (c === "\n") { + yield this.parseLine(accumulator); + break; + } else { + accumulator += c; + } + } + break; + } + + for (let c of str) { + if (c === "\n") { + yield this.parseLine(accumulator); + accumulator = ""; + } else { + accumulator += c; + } + } + } + } +} + +class AccregSQL { + private sql; + + constructor (sql) { + this.sql = sql; + } + + async write (acc: any) { + for (const variable of acc.variables) { + await this.sql.do("INSERT INTO `acc_reg` ?? values?", [ + ["account_id", "name", "value"], + [acc.account_id, variable.var_name, variable.value] + ]); + } + } +} + +export { + AccregParser, + AccregSQL, +} diff --git a/server/frob/char.ts b/server/frob/char.ts index 67751dc..dfb6837 100644 --- a/server/frob/char.ts +++ b/server/frob/char.ts @@ -22,12 +22,20 @@ class CharParser { "(?((?[^,]+),(?[-0-9]+) )*)\t" + // some chars have negative variables (overflows) "$"; private char_items_line = "[0-9]+,(?[0-9]+),(?[0-9]+),(?[0-9]+),[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+ "; + private char_skills_line = "(?[0-9]+),(?[0-9]+) "; + private char_vars_line = "(?[^,]+),(?[-0-9]+) "; private char_regex: RegExp; private char_regex_items: RegExp; + private char_regex_skills: RegExp; + private char_regex_vars: RegExp; + private encoder; constructor () { this.char_regex = new RegExp(this.char_line); this.char_regex_items = new RegExp(this.char_items_line, "g"); + this.char_regex_skills = new RegExp(this.char_skills_line, "g"); + this.char_regex_vars = new RegExp(this.char_vars_line, "g"); + this.encoder = new TextEncoder(); } private parseLine (line: string) { @@ -40,6 +48,8 @@ class CharParser { const groups = (match as any).groups; let items = []; + let skills = []; + let variables = []; if (groups.items.length > 1) { let match_items = this.char_regex_items.exec(groups.items); @@ -51,12 +61,36 @@ class CharParser { } groups.items = items; + + if (groups.skills.length > 1) { + let match_skills = this.char_regex_skills.exec(groups.skills); + + while (match_skills !== null) { + skills.push((match_skills as any).groups); + match_skills = this.char_regex_skills.exec(groups.skills); + } + } + + groups.skills2 = skills; + + if (groups.variables.length > 1) { + let match_vars = this.char_regex_vars.exec(groups.variables); + + while (match_vars !== null) { + variables.push((match_vars as any).groups); + match_vars = this.char_regex_vars.exec(groups.variables); + } + } + + groups.variables2 = variables; + + Deno.write(Deno.stdout.rid, this.encoder.encode(`\r⌛ processing char ${groups.char_id}...`)); return groups; } public async * readDB () { const decoder = new TextDecoder("utf-8"); - console.info("\nwalking through athena.txt..."); + console.info("\r \nwalking through athena.txt..."); const file = await Deno.open("world/save/athena.txt"); const buf = new Uint8Array(1024); let accumulator = ""; @@ -137,7 +171,7 @@ class CharWriter { } async finalize(dry_run: boolean = false) { - console.info("appending %newid%..."); + console.info("\rappending %newid%... "); await Deno.write(this.file.rid, this.encoder.encode(`${this.highest + 1}\t%newid%\n`)); this.file.close(); @@ -151,7 +185,39 @@ class CharWriter { } } +class CharSQL { + private sql; + + constructor (sql) { + this.sql = sql; + } + + async write (char: any) { + char.name = this.sql.escape(char.name); + await this.sql.do("INSERT INTO `char` ?? values?", [ + ["char_id","account_id","char_num","name","class","base_level","job_level","base_exp","job_exp","zeny","str","agi","vit","int","dex","luk","status_point","skill_point","party_id","hair","hair_color","partner_id","sex"], + [char.char_id,char.account_id,char.char_num,char.name,char.species,char.base_level,char.job_level,char.base_exp,char.job_exp,char.zeny,char.str,char.agi,char.vit,char.int,char.dex,char.luk,char.status_point,char.skill_point,char.party_id,char.hair,char.hair_color,char.partner_id,char.sex], + ]); + + for (const item of char.items) { + await this.sql.do("INSERT INTO `inventory` ?? values?", [ + ["char_id", "nameid", "amount", "equip"], + [char.char_id, item.nameid, item.amount, item.equip] + ]); + } + + for (const variable of char.variables2) { + // those are always char variables since acc vars are in accreg.txt + await this.sql.do("INSERT INTO `char_reg` ?? values?", [ + ["char_id", "name", "value"], + [char.char_id, variable.var_name, variable.value] + ]); + } + } +} + export { CharParser, CharWriter, + CharSQL, } diff --git a/server/frob/index.ts b/server/frob/index.ts index ea1e33b..fc3ac09 100644 --- a/server/frob/index.ts +++ b/server/frob/index.ts @@ -1,14 +1,27 @@ // this script removes specified items from inventories and storage -import { CharParser, CharWriter } from "./char.ts"; -import { StorageParser, StorageWriter } from "./storage.ts"; +import { SQLHandler } from "./sql.ts" +import { LoginParser, LoginSQL } from "./login.ts"; +import { CharParser, CharWriter, CharSQL } from "./char.ts"; +import { AccregParser, AccregSQL } from "./accreg.ts"; +import { PartyParser, PartySQL } from "./party.ts"; +import { StorageParser, StorageWriter, StorageSQL } from "./storage.ts"; import { ItemDB } from "./itemdb.ts"; const args: string[] = Deno.args.slice(1); const to_remove: Set = new Set(); +const sql = new SQLHandler(); +const login_parser = new LoginParser(); const char_parser = new CharParser(); +const accreg_parser = new AccregParser(); +const party_parser = new PartyParser(); const storage_parser = new StorageParser(); const char_writer = new CharWriter(); +const char_SQL = new CharSQL(sql); +const login_SQL = new LoginSQL(sql); +const accreg_SQL = new AccregSQL(sql); +const party_SQL = new PartySQL(sql); const storage_writer = new StorageWriter(); +const storage_SQL = new StorageSQL(sql); const item_db = new ItemDB(); const stats = { @@ -30,6 +43,7 @@ const stats = { const flags = { dry_run: false, + sql: false, }; @@ -84,6 +98,9 @@ const flags = { case "dry-run": flags.dry_run = true; break; + case "dump": + case "sql": + flags.sql = true; case "clean": case "clean-only": args.length = 0; @@ -119,9 +136,31 @@ const flags = { } } - console.info("\nThe following items will be removed:"); - for (let item of to_remove) { - console.info(`[${item}]: ${itemToString(item)}`); + if (to_remove.size > 0) { + console.info("\nThe following items will be removed:"); + for (let item of to_remove) { + console.info(`[${item}]: ${itemToString(item)}`); + } + } + + if (flags.sql) { + console.log(""); + await sql.init(); + } + + console.log(""); + + // account: + if (flags.sql) { + for await (const acc of login_parser.readDB()) { + if (acc === null + || acc.logincount < 1 // don't keeep accounts that never logged in + || +acc.state === 5 // don't keep permabanned accounts + ) { + continue; + } + await login_SQL.write(acc); + } } // inventory: @@ -131,15 +170,15 @@ const flags = { for (let item of char.items) { if (!items_by_id.has(+item.nameid)) { - console.log(`removing ${+item.amount || 1}x non-existant item ID ${item.nameid} from inventory of character ${char.name} [${char.account_id}:${char.char_id}]`); + console.log(`\rremoving ${+item. amount || 1}x non-existant item ID ${item.nameid} from inventory of character ${char.name} [${char.account_id}:${char.char_id}]`); stats.inventory.pruned += +item.amount; mod = true; } else if (+item.amount < 1) { - console.log(`removing stub of item ${itemToString(item.nameid)} [${item.nameid}] from inventory of character ${char.name} [${char.account_id}:${char.char_id}]`); + console.log(`\rremoving stub of item ${itemToString(item.nameid)} [${item.nameid}] from inventory of character ${char.name} [${char.account_id}:${char.char_id}]`); stats.inventory.stub++; mod = true; } else if (to_remove.has(+item.nameid)) { - console.log(`removing ${item.amount}x ${itemToString(item.nameid)} [${item.nameid}] from inventory of character ${char.name} [${char.account_id}:${char.char_id}]`); + console.log(`\rremoving ${item.amount}x ${itemToString(item.nameid)} [${item.nameid}] from inventory of character ${char.name} [${char.account_id}:${char.char_id}]`); stats.inventory.removed += +item.amount; mod = true; } else { @@ -152,10 +191,27 @@ const flags = { char.items = items_filtered; await char_writer.write(char); + + if (flags.sql) + await char_SQL.write(char); } await char_writer.finalize(!!flags.dry_run); + // char-server-bound account variables + if (flags.sql) { + for await (const acc of accreg_parser.readDB()) { + await accreg_SQL.write(acc); + } + } + + // party and party leaders + if (flags.sql) { + for await (const party of party_parser.readDB()) { + await party_SQL.write(party); + } + } + // storage: for await (let storage of storage_parser.readDB()) { let items_filtered = []; // this is not a Set because some items don't stack @@ -163,17 +219,17 @@ const flags = { for (let item of storage.items) { if (!items_by_id.has(+item.nameid)) { - console.log(`removing ${+item.amount || 1}x non-existant item ID ${item.nameid} from storage of account ${storage.account_id}`); + console.log(`\rremoving ${+item.amount || 1}x non-existant item ID ${item.nameid} from storage of account ${storage.account_id}`); stats.storage.pruned += +item.amount; storage.storage_amount--; mod = true; } else if (+item.amount < 1) { - console.log(`removing stub of item ${itemToString(item.nameid)} [${item.nameid}] from storage of account ${storage.account_id}`); + console.log(`\rremoving stub of item ${itemToString(item.nameid)} [${item.nameid}] from storage of account ${storage.account_id}`); stats.storage.stub++; storage.storage_amount--; mod = true; } else if (to_remove.has(+item.nameid)) { - console.log(`removing ${item.amount}x ${itemToString(item.nameid)} [${item.nameid}] from storage of account ${storage.account_id}`); + console.log(`\rremoving ${item.amount}x ${itemToString(item.nameid)} [${item.nameid}] from storage of account ${storage.account_id}`); stats.storage.removed += +item.amount; storage.storage_amount--; mod = true; @@ -196,6 +252,7 @@ const flags = { if (storage.storage_amount >= 1) { await storage_writer.write(storage); + await storage_SQL.write(storage); } else { console.log(`storage of account ${storage.account_id} is now empty: removing it from the storage db`); stats.storage.wiped++; @@ -203,8 +260,9 @@ const flags = { } await storage_writer.finalize(!!flags.dry_run); + await sql.close(); - console.info("\n=== all done ==="); + console.info("\r \n=== all done ==="); console.info(`removed ${stats.inventory.removed} existant, ${stats.inventory.pruned} non-existant and ${stats.inventory.stub} stub items from the inventory of ${stats.inventory.chars} characters`); console.info(`removed ${stats.storage.removed} existant, ${stats.storage.pruned} non-existant and ${stats.storage.stub} stub items from the storage of ${stats.storage.accounts} accounts`); console.info(`removed ${stats.storage.wiped} empty storage entries from the storage db`); @@ -213,4 +271,6 @@ const flags = { if (flags.dry_run) { console.warn("(DRY RUN) no file modified"); } + + Deno.exit(0); })() diff --git a/server/frob/login.ts b/server/frob/login.ts new file mode 100644 index 0000000..fa130cc --- /dev/null +++ b/server/frob/login.ts @@ -0,0 +1,138 @@ +class LoginParser { + private login_line = + "^" + + "(?[0-9]+)\t" + + "(?[^\t]+)\t" + + "(?(?:!(?.{5})\\$(?[a-f0-9]{24}))|(?:!1a2b3c4d\\+))\t" + // KILL IT WITH FIRE! + "(?[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}.[0-9]{3})\t" + // YYYY-mm-dd HH:MM:SS.sss + "(?M|F|S|N)\t" + + "(?[0-9]+)\t" + + "(?[0-9]+)\t" + + "(?[^@]+@[^\t]+)\t" + + "-\t" + // unused error_message + "0\t" + // unused + "(?[^\t]+)\t" + + "(?[-!])\t" + // ! means salted hash, - means ??? + "(?[0-9]+)\t" + + "$"; + private login_regex: RegExp; + private encoder; + + constructor () { + this.login_regex = new RegExp(this.login_line); + this.encoder = new TextEncoder(); + } + + private inet_aton(ip: string) { + const a = ip.split('.'); + const dv = new DataView(new ArrayBuffer(4)); + dv.setUint8(0, +a[0]); + dv.setUint8(1, +a[1]); + dv.setUint8(2, +a[2]); + dv.setUint8(3, +a[3]); + return dv.getUint32(0); + } + + private parseLine (line: string) { + const match = this.login_regex.exec(line); + + if (!(match instanceof Object) || !Reflect.has(match, "groups")) { + console.error("\nline does not match the account regex:", line); + throw new SyntaxError(); + } + + const groups = (match as any).groups; + + if (+groups.logincount === 0) { + groups.lastlogin = null; + } else { + groups.lastlogin = new Date(groups.lastlogin).toISOString().slice(0, 19).replace("T", " "); + } + + if (groups.memo !== "!") { + console.warn(`\nunsupported password mode (${groups.memo}) for account ${groups.account_id}`); + return null; + } + + if (groups.email === "a@a.com") { + groups.email = null; + } + + groups.ip = this.inet_aton(groups.ip); + + if (+groups.ban_until_time >= 0xFFFFFFFF) { + groups.ban_until_time = 0; + groups.state = 5; // perma ban instead + } + + Deno.write(Deno.stdout.rid, this.encoder.encode(`\r⌛ processing login data of account ${groups.account_id}...`)); + return groups; + } + + public async * readDB () { + const decoder = new TextDecoder("utf-8"); + console.info("\r \nwalking through account.txt..."); + const file = await Deno.open("login/save/account.txt"); + const buf = new Uint8Array(1024); + let accumulator = ""; + + while (true) { + const nread = await Deno.read(file.rid, buf); + + if (nread === Deno.EOF) { + break; + } + + const str = decoder.decode(buf); + + if (nread < 1024) { + for (let c of str) { + if (c === "\n") { + if (accumulator.slice(0, 2) !== "//") { + yield this.parseLine(accumulator); + } + break; + } else { + accumulator += c; + } + } + break; + } + + for (let c of str) { + if (c === "\n") { + if (accumulator.slice(0, 2) !== "//") { + yield this.parseLine(accumulator); + } + accumulator = ""; + } else { + accumulator += c; + } + } + } + } +} + +class LoginSQL { + private sql; + + constructor (sql) { + this.sql = sql; + } + + async write (acc: any) { + if (acc === null) { + return Promise.resolve(false); + } + + await this.sql.do("INSERT INTO `login` ?? values?", [ + ["account_id", "userid", "user_pass", "lastlogin", "logincount", "state", "email", "last_ip", "unban_time"], + [+acc.account_id, acc.userid, acc.pass, acc.lastlogin, +acc.logincount, +acc.state, acc.email, acc.ip, acc.ban_until_time] + ]); + } +} + +export { + LoginParser, + LoginSQL, +} diff --git a/server/frob/party.ts b/server/frob/party.ts new file mode 100644 index 0000000..cdc0580 --- /dev/null +++ b/server/frob/party.ts @@ -0,0 +1,128 @@ +class PartyParser { + private party_line = + "^" + + "(?[0-9]+)\t" + + "(?[^\t]*)\t" + // bug: name can be empty + "(?[01]),(?(?:[01]|65535))\t" + // bug: item share can be 0xFFFF + "(?((?[0-9]+),(?[01])\t(?[^\t]+)\t)*)" + + "$"; + private member_line = "(?[0-9]+),(?[01])\t(?[^\t]+)\t"; + private party_regex: RegExp; + private party_regex_members: RegExp; + private encoder; + + constructor () { + this.party_regex = new RegExp(this.party_line); + this.party_regex_members = new RegExp(this.member_line, "g"); + this.encoder = new TextEncoder(); + } + + private parseLine (line: string) { + const match = this.party_regex.exec(line); + + if (!(match instanceof Object) || !Reflect.has(match, "groups")) { + console.error("\nline does not match the regex:", line); + throw new SyntaxError(); + } + + const groups = (match as any).groups; + let members = []; + + if (groups.members.length > 1) { + let match_members = this.party_regex_members.exec(groups.members); + + while (match_members !== null) { + members.push((match_members as any).groups); + match_members = this.party_regex_members.exec(groups.members); + } + } + + if (+groups.item_share === 65535) { + groups.item_share = 1; // old bug that was fixed in tmwa but not in the db + } + + groups.members = members; + + if (groups.name.length == 0) { + console.warn(`\rdiscarding party ${groups.party_id}: no name `); + return null; + } else if (groups.members.length == 0) { + console.warn(`\rdiscarding party ${groups.party_id}: no members `); + return null; + } + + Deno.write(Deno.stdout.rid, this.encoder.encode(`\r⌛ processing members of party ${groups.party_id}...`)); + return groups; + } + + public async * readDB () { + const decoder = new TextDecoder("utf-8"); + console.info("\r \nwalking through party.txt..."); + const file = await Deno.open("world/save/party.txt"); + const buf = new Uint8Array(1024); + let accumulator = ""; + + while (true) { + const nread = await Deno.read(file.rid, buf); + + if (nread === Deno.EOF) { + break; + } + + const str = decoder.decode(buf); + + if (nread < 1024) { + for (let c of str) { + if (c === "\n") { + yield this.parseLine(accumulator); + break; + } else { + accumulator += c; + } + } + break; + } + + for (let c of str) { + if (c === "\n") { + yield this.parseLine(accumulator); + accumulator = ""; + } else { + accumulator += c; + } + } + } + } +} + +class PartySQL { + private sql; + + constructor (sql) { + this.sql = sql; + } + + async write (party: any) { + if (party === null) + return Promise.resolve(false); // we cannot handle duplicate parties + + party.name = this.sql.escape(party.name); + + await this.sql.do("INSERT INTO `party` ?? values?", [ + ["party_id", "name", "exp_share", "item_share"], + [party.party_id, party.name, +party.exp_share, +party.item_share] + ]); + + for (const member of party.members) { + await this.sql.do("UPDATE `char` SET ?? = ? WHERE ?? = ? AND ?? = ?", [ + "party_isleader", +member.leader, + "party_id", +party.party_id, + "account_id", +member.account_id ]); + } + } +} + +export { + PartyParser, + PartySQL, +} diff --git a/server/frob/sql.ts b/server/frob/sql.ts new file mode 100644 index 0000000..60c4bbe --- /dev/null +++ b/server/frob/sql.ts @@ -0,0 +1,182 @@ +import { Client } from "https://deno.land/x/mysql/mod.ts"; + +class SQLHandler { + private client; + private hostname; + private username; + private password; + private backslash; + + constructor (hostname: string = "127.0.0.1", username: string = "evol", password: string = "evol") { + this.client = new Client(); + [this.hostname, this.username, this.password] = [hostname, username, password]; + + // escape regexes + this.backslash = /\\/g; + } + + async init () { + await this.client.connect({ + hostname: this.hostname, + username: this.username, + password: this.password, + }); + + // INSTALL SONAME 'ha_rocksdb'; + + console.log("Initializing database..."); + await this.do("CREATE DATABASE IF NOT EXISTS legacy"); + await this.do("USE legacy"); + + console.log("Initializing tables..."); + await this.do("DROP TABLE IF EXISTS `login`"); + await this.do("DROP TABLE IF EXISTS `char`"); + await this.do("DROP TABLE IF EXISTS `inventory`"); + await this.do("DROP TABLE IF EXISTS `storage`"); + await this.do("DROP TABLE IF EXISTS `global_acc_reg`"); + await this.do("DROP TABLE IF EXISTS `acc_reg`"); + await this.do("DROP TABLE IF EXISTS `char_reg`"); + await this.do("DROP TABLE IF EXISTS `party`"); + await this.do(` + CREATE TABLE \`login\` ( + account_id INT(11) UNSIGNED NOT NULL, + revolt_id INT(11) UNSIGNED NULL, -- id of the new account on revolt + userid VARCHAR(23) NOT NULL DEFAULT '', + user_pass VARCHAR(32) NOT NULL DEFAULT '', + lastlogin DATETIME NULL, + -- sex, + logincount INT(9) UNSIGNED NOT NULL DEFAULT '0', + state INT(11) UNSIGNED NOT NULL DEFAULT '0', + email VARCHAR(39) NULL, + -- error_message, + -- connect_until_time, + last_ip INT(4) UNSIGNED NOT NULL DEFAULT 0, + -- memo, + unban_time INT(11) UNSIGNED NOT NULL DEFAULT '0', + PRIMARY KEY (account_id), + UNIQUE KEY revolt (revolt_id), + KEY userid (userid) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`char\` ( + char_id INT(11) UNSIGNED NOT NULL, + revolt_id INT(11) UNSIGNED NULL, -- id of the new char on revolt + account_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + char_num TINYINT(1) NOT NULL DEFAULT '0', + \`name\` VARCHAR(30) NOT NULL DEFAULT '', + class SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0', + base_level SMALLINT(6) UNSIGNED NOT NULL DEFAULT '1', + job_level SMALLINT(6) UNSIGNED NOT NULL DEFAULT '1', + base_exp BIGINT(20) UNSIGNED NOT NULL DEFAULT '0', + job_exp BIGINT(20) UNSIGNED NOT NULL DEFAULT '0', + zeny INT(11) UNSIGNED NOT NULL DEFAULT '0', + \`str\` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + agi SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + vit SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + \`int\` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + dex SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + luk SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0', + status_point INT(11) UNSIGNED NOT NULL DEFAULT '0', + skill_point INT(11) UNSIGNED NOT NULL DEFAULT '0', + party_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + party_isleader BIT(1) NOT NULL DEFAULT 0, + hair TINYINT(4) UNSIGNED NOT NULL DEFAULT '0', + hair_color SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0', + partner_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + sex ENUM('M','F','N','S') NOT NULL DEFAULT 'N', + PRIMARY KEY (char_id), + UNIQUE KEY revolt (revolt_id), + UNIQUE KEY name_key (name), + KEY account_id (account_id), + KEY party_id (party_id) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`inventory\` ( + id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + char_id INT(11) UNSIGNED NOT NULL, + nameid INT(11) UNSIGNED NOT NULL DEFAULT '0', + amount INT(11) UNSIGNED NOT NULL DEFAULT '0', + equip INT(11) UNSIGNED NOT NULL DEFAULT '0', + PRIMARY KEY (id), + KEY char_id (char_id) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`storage\` ( + id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + account_id INT(11) UNSIGNED NOT NULL, + nameid INT(11) UNSIGNED NOT NULL DEFAULT '0', + amount INT(11) UNSIGNED NOT NULL DEFAULT '0', + PRIMARY KEY (id), + KEY account_id (account_id) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`global_acc_reg\` ( + account_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + \`name\` VARCHAR(32) NOT NULL DEFAULT '', + \`value\` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (account_id,\`name\`), + KEY account_id (account_id) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`acc_reg\` ( + account_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + \`name\` VARCHAR(32) NOT NULL DEFAULT '', + \`value\` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (account_id,\`name\`), + KEY account_id (account_id) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`char_reg\` ( + char_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + \`name\` VARCHAR(32) NOT NULL DEFAULT '', + \`value\` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (char_id,\`name\`), + KEY char_id (char_id) + ) ENGINE=ROCKSDB; + `); + await this.do(` + CREATE TABLE \`party\` ( + party_id INT(11) UNSIGNED NOT NULL DEFAULT '0', + revolt_id INT(11) UNSIGNED NULL, -- id of the new party on revolt + \`name\` VARCHAR(24) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', + exp_share BIT(1) NOT NULL DEFAULT 0, + item_share BIT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (party_id), + UNIQUE KEY revolt (revolt_id), + UNIQUE KEY name_key (name) + ) ENGINE=ROCKSDB; + `); // some old parties have weird names + } + + escape (str) { + // for some reason the deno sql module doesn't escape backslashes + return str.replace(this.backslash, "\\\\"); + } + + async transaction (fn) { + return await this.client.transaction(fn); + } + + async query (...args) { + return await this.client.query(...args); + } + + async do (...args) { + return await this.client.execute(...args); + } + + + async close () { + return this.client.close(); + } +} + +export { + SQLHandler, +} diff --git a/server/frob/storage.ts b/server/frob/storage.ts index 315d656..04559d6 100644 --- a/server/frob/storage.ts +++ b/server/frob/storage.ts @@ -6,10 +6,12 @@ class StorageParser { private storage_items_line = "[0-9]+,(?[0-9]+),(?[0-9]+),(?[0-9]+),[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+,[0-9]+ "; private storage_regex: RegExp; private storage_regex_items: RegExp; + private encoder; constructor () { this.storage_regex = new RegExp(this.storage_line); this.storage_regex_items = new RegExp(this.storage_items_line, "g"); + this.encoder = new TextEncoder(); } private parseLine (line: string) { @@ -33,12 +35,14 @@ class StorageParser { } groups.items = items; + + Deno.write(Deno.stdout.rid, this.encoder.encode(`\r⌛ processing storage of account ${groups.account_id}... `)); return groups; } public async * readDB () { const decoder = new TextDecoder("utf-8"); - console.info("\nwalking through storage.txt..."); + console.info("\r \nwalking through storage.txt..."); const file = await Deno.open("world/save/storage.txt"); const buf = new Uint8Array(1024); let accumulator = ""; @@ -93,7 +97,7 @@ class StorageWriter { async write (storage: any) { let line = `${storage.account_id},${storage.storage_amount}\t`; - for (let item of storage.items) { + for (const item of storage.items) { line += `0,${item.nameid},${item.amount},${item.equip},0,0,0,0,0,0,0 `; } @@ -109,14 +113,33 @@ class StorageWriter { if (dry_run) { Deno.removeSync("world/save/storage.txt.tmp"); } else { - console.info("overwriting storage.txt..."); + console.info("\roverwriting storage.txt... "); await Deno.rename("world/save/storage.txt", "world/save/storage.txt_pre-frob"); await Deno.rename("world/save/storage.txt.tmp", "world/save/storage.txt"); } } } +class StorageSQL { + private sql; + + constructor (sql) { + this.sql = sql; + } + + async write (acc: any) { + for (const item of acc.items) { + await this.sql.do("INSERT INTO `storage` ?? values?", [ + ["account_id", "nameid", "amount"], + [acc.account_id, item.nameid, item.amount] + ]); + } + } +} + + export { StorageParser, StorageWriter, + StorageSQL, } -- cgit v1.2.3-60-g2f50