1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
import { Client, Connection } from "https://deno.land/x/mysql/mod.ts";
class SQLHandler {
private client: Client;
private hostname: string;
private username: string;
private password: string;
private backslash: RegExp;
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: string) {
// for some reason the deno sql module doesn't escape backslashes
return str.replace(this.backslash, "\\\\");
}
async transaction (processor: (c: Connection) => Promise<any>) {
return await this.client.transaction(processor);
}
async query (sql: string, params?: any[]) {
return await this.client.query(sql, params);
}
async do (sql: string, params?: any[]) {
return await this.client.execute(sql, params);
}
async close () {
return this.client.close();
}
}
export {
SQLHandler,
}
|
