-
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdb.sql
151 lines (143 loc) · 5.09 KB
/
db.sql
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
CREATE TABLE public.accounts (
id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
email varchar(50) NULL,
pass varchar(70) NULL,
admin_level int4 DEFAULT 0 NOT NULL,
created timestamp NULL,
last_login timestamp NULL,
banned bool NULL,
xcoins int8 NULL,
CONSTRAINT accounts_pk PRIMARY KEY (id)
);
CREATE INDEX accounts_id_idx ON public.accounts USING btree (id);
CREATE TABLE public."characters" (
id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
account_id int8 NOT NULL,
"name" varchar(20) NULL,
id1 int4 DEFAULT 0 NULL,
id2 int4 DEFAULT 0 NULL,
id3 int4 DEFAULT 0 NULL,
"level" int4 DEFAULT 1 NULL,
strength int4 DEFAULT 10 NULL,
vitality int4 DEFAULT 10 NULL,
dexterity int4 DEFAULT 10 NULL,
intelligence int4 DEFAULT 10 NULL,
magic int4 DEFAULT 10 NULL,
charisma int4 DEFAULT 10 NULL,
experience int8 DEFAULT 0 NULL,
gender int2 DEFAULT 0 NULL,
skin int4 DEFAULT 0 NULL,
hairstyle int4 DEFAULT 0 NULL,
haircolor int4 DEFAULT 0 NULL,
underwear int4 DEFAULT 0 NULL,
apprcolor int4 DEFAULT 0 NULL,
appr1 int4 DEFAULT 0 NULL,
appr2 int4 DEFAULT 0 NULL,
appr3 int4 DEFAULT 0 NULL,
appr4 int4 DEFAULT 0 NULL,
nation varchar(10) DEFAULT 'NONE'::bpchar NULL,
maploc varchar(10) DEFAULT 'default'::bpchar NULL,
locx int4 DEFAULT '-1'::integer NULL,
locy int4 DEFAULT '-1'::integer NULL,
profile varchar(255) DEFAULT '___________'::bpchar NULL,
adminlevel int4 DEFAULT 0 NULL,
contribution int4 DEFAULT 0 NULL,
leftspectime int4 DEFAULT 0 NULL,
lockmapname varchar(10) NULL,
lockmaptime int4 DEFAULT 0 NULL,
guild_id int8 DEFAULT '-1'::integer NULL,
fightnum int4 DEFAULT 0 NULL,
fightdate int4 DEFAULT 0 NULL,
fightticket int4 DEFAULT 0 NULL,
questnum int4 DEFAULT 0 NULL,
questid int4 DEFAULT 0 NULL,
questcount int4 DEFAULT 0 NULL,
questrewardtype int4 DEFAULT 0 NULL,
questrewardamount int4 DEFAULT 0 NULL,
questcompleted int4 DEFAULT 0 NULL,
eventid int4 DEFAULT 0 NULL,
warcon int4 DEFAULT 0 NULL,
crusadejob int4 DEFAULT 0 NULL,
crusadeid int4 DEFAULT 0 NULL,
crusadeconstructpoint int4 DEFAULT 0 NULL,
reputation int4 DEFAULT 0 NULL,
hp int4 DEFAULT 0 NULL,
mp int4 DEFAULT 0 NULL,
sp int4 DEFAULT 0 NULL,
ek int4 DEFAULT 0 NULL,
pk int4 DEFAULT 0 NULL,
rewardgold int4 DEFAULT 0 NULL,
downskillid int4 DEFAULT '-1'::integer NULL,
hunger int4 DEFAULT 100 NULL,
leftsac int4 DEFAULT 0 NULL,
leftshutuptime int4 DEFAULT 0 NULL,
leftreptime int4 DEFAULT 3600 NULL,
leftforcerecalltime int4 DEFAULT 0 NULL,
leftfirmstaminatime int4 DEFAULT 0 NULL,
leftdeadpenaltytime int4 DEFAULT 0 NULL,
magicmastery bpchar(100) DEFAULT '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'::bpchar NULL,
party_id int8 DEFAULT '-1'::integer NULL,
itemupgradeleft int4 DEFAULT 0 NULL,
totalek int4 DEFAULT 0 NULL,
totalpk int4 DEFAULT 0 NULL,
mmr int4 DEFAULT 1000 NULL,
altmmr int4 DEFAULT 1000 NULL,
head_appr int4 DEFAULT 0 NULL,
body_appr int4 DEFAULT 0 NULL,
arm_appr int4 DEFAULT 0 NULL,
leg_appr int4 DEFAULT 0 NULL,
gold int8 DEFAULT 0 NULL,
luck int4 DEFAULT 0 NULL,
world_name varchar DEFAULT 'xtremelow'::bpchar NOT NULL,
lastsavedate int8 DEFAULT 0 NULL,
blockdate int8 DEFAULT 0 NULL,
CONSTRAINT characters_pk PRIMARY KEY (id)
);
ALTER TABLE public."characters" ADD CONSTRAINT characters_fk FOREIGN KEY (account_id) REFERENCES public.accounts(id) ON DELETE CASCADE;
CREATE TABLE public.guilds (
id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
"name" varchar(20) NULL,
owner_id int8 NULL,
created timestamp DEFAULT now() NULL,
gold int8 DEFAULT 0 NULL,
warehouse_level int4 DEFAULT 0 NULL,
motd varchar(10000) NULL,
login_message varchar(500) NULL,
nation varchar(10) NULL
);
CREATE TABLE public.items (
id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
char_id int8 NOT NULL,
"name" varchar(30) NULL,
count int8 NULL,
"type" int4 NULL,
id1 int4 NULL,
id2 int4 NULL,
id3 int4 NULL,
color int4 NULL,
effect1 int4 NULL,
effect2 int4 NULL,
effect3 int4 NULL,
durability int4 NULL,
"attribute" int8 NULL,
socket1 int4 NULL,
socket2 int4 NULL,
socket3 int4 NULL,
itemposx int4 DEFAULT 0 NULL,
itemposy int4 DEFAULT 0 NULL,
itemloc public.item_location DEFAULT 'bag'::item_location NOT NULL,
item_id int4 NOT NULL,
equipped bool DEFAULT false NOT NULL,
CONSTRAINT items_pkey PRIMARY KEY (id, char_id)
);
CREATE TABLE public.skills (
id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
char_id int8 NOT NULL,
skill_id int4 NULL,
mastery int4 NULL,
experience int8 NULL,
CONSTRAINT skills_pkey PRIMARY KEY (id)
);
CREATE INDEX skills_char_id_idx ON public.skills USING btree (char_id);
-- public.skills foreign keys
ALTER TABLE public.skills ADD CONSTRAINT skills_fk FOREIGN KEY (char_id) REFERENCES public."characters"(id) ON DELETE CASCADE;