|
0
|
1 CREATE TABLE IF NOT EXISTS mk_account ( |
|
|
2 ac_id serial, |
|
|
3 ac_name varchar[32], |
|
|
4 ac_email varchar[128], |
|
|
5 ac_firstname varchar[32], |
|
|
6 ac_lastname varchar[32], |
|
|
7 ac_salt varchar[8], |
|
|
8 ac_password varchar[256], |
|
|
9 ac_joindate date, |
|
|
10 CONSTRAINT pk_ac_id PRIMARY KEY(ac_id) |
|
|
11 ); |
|
|
12 |
|
|
13 CREATE TABLE IF NOT EXISTS mk_log_history ( |
|
|
14 log_id serial, |
|
|
15 log_ac_id serial, |
|
|
16 CONSTRAINT pk_log_id PRIMARY KEY(log_id), |
|
|
17 CONSTRAINT fk_log_ac_id FOREIGN KEY(log_ac_id) REFERENCES mk_account(ac_id) |
|
|
18 ); |
|
|
19 |
|
|
20 CREATE TABLE IF NOT EXISTS mk_character ( |
|
|
21 ch_id serial, |
|
|
22 ch_ac_id serial, |
|
|
23 ch_class varchar[32], |
|
|
24 ch_createdate date, |
|
|
25 CONSTRAINT pk_ch_id PRIMARY KEY(ch_id), |
|
|
26 CONSTRAINT fk_ch_ac_id FOREIGN KEY(ch_ac_id) REFERENCES mk_account(ac_id) |
|
|
27 ); |
|
|
28 |
|
|
29 CREATE TABLE IF NOT EXISTS mk_log_history_item ( |
|
|
30 lhi_id serial, |
|
|
31 lhi_log_id serial, |
|
|
32 lhi_date time, |
|
|
33 CONSTRAINT pk_lhi_id PRIMARY KEY(lhi_id), |
|
|
34 CONSTRAINT fk_lhi_log_id FOREIGN KEY(lhi_log_id) REFERENCES mk_log_history(log_id) |
|
|
35 ); |
|
|
36 |
|
|
37 CREATE TABLE IF NOT EXISTS mk_status ( |
|
|
38 st_id serial, |
|
|
39 st_ch_id serial, |
|
|
40 st_hp int, |
|
|
41 st_mp int, |
|
|
42 CONSTRAINT pk_st_id PRIMARY KEY(st_id), |
|
|
43 CONSTRAINT fk_st_ch_id FOREIGN KEY(st_ch_id) REFERENCES mk_character(ch_id) |
|
|
44 ); |
|
|
45 |
|
|
46 CREATE TABLE IF NOT EXISTS mk_points ( |
|
|
47 pt_id serial, |
|
|
48 pt_st_id serial, |
|
|
49 pt_avail smallint, |
|
|
50 pt_force smallint, |
|
|
51 pt_defense smallint, |
|
|
52 pt_agility smallint, |
|
|
53 CONSTRAINT pk_pt_id PRIMARY KEY(pt_id), |
|
|
54 CONSTRAINT fk_st_id FOREIGN KEY(pt_st_id) REFERENCES mk_status(st_id) |
|
|
55 ); |
|
|
56 |
|
|
57 CREATE TABLE IF NOT EXISTS mk_build ( |
|
|
58 bd_id serial, |
|
|
59 bd_ac_id serial, |
|
|
60 bd_position int, |
|
|
61 bd_title varchar[32], |
|
|
62 bd_password varchar[25], |
|
|
63 bd_kind varchar[32], |
|
|
64 CONSTRAINT pk_bd_id PRIMARY KEY(bd_id), |
|
|
65 CONSTRAINT fk_bd_ac_id FOREIGN KEY(bd_ac_id) REFERENCES mk_account(ac_id) |
|
|
66 ); |
|
|
67 |
|
|
68 CREATE TABLE IF NOT EXISTS mk_chest ( |
|
|
69 cht_id serial, |
|
|
70 cht_bd_id serial, |
|
|
71 cht_name varchar[32], |
|
|
72 CONSTRAINT pk_cht_id PRIMARY KEY(cht_id), |
|
|
73 CONSTRAINT fk_cht_bd_id FOREIGN KEY(cht_bd_id) REFERENCES mk_build(bd_id) |
|
|
74 ); |
|
|
75 |
|
|
76 CREATE TABLE IF NOT EXISTS mk_inventory ( |
|
|
77 inv_id serial, |
|
|
78 inv_ch_id serial, |
|
|
79 CONSTRAINT pk_inv_id PRIMARY KEY(inv_id), |
|
|
80 CONSTRAINT fk_inv_ch_id FOREIGN KEY(inv_ch_id) REFERENCES mk_character(ch_id) |
|
|
81 ); |
|
|
82 |
|
|
83 CREATE TABLE IF NOT EXISTS mk_artefact ( |
|
|
84 atf_id serial, |
|
|
85 atf_ch_id serial, |
|
|
86 atf_name varchar[32], |
|
|
87 CONSTRAINT pk_atf_id PRIMARY KEY(atf_id), |
|
|
88 CONSTRAINT fk_atf_ch_id FOREIGN KEY(atf_ch_id) REFERENCES mk_character(ch_id) |
|
|
89 ); |
|
|
90 |
|
|
91 CREATE TABLE IF NOT EXISTS mk_quest ( |
|
|
92 qs_id serial, |
|
|
93 qs_ch_id serial, |
|
|
94 qs_name varchar[32], |
|
|
95 CONSTRAINT pk_qs_id PRIMARY KEY(qs_id), |
|
|
96 CONSTRAINT fk_qs_ch_id FOREIGN KEY(qs_ch_id) REFERENCES mk_character(ch_id) |
|
|
97 ); |
|
|
98 |
|
|
99 CREATE TABLE IF NOT EXISTS mk_inventory_object ( |
|
|
100 invo_id serial, |
|
|
101 invo_inv_id serial, |
|
|
102 invo_name varchar[32], |
|
|
103 invo_count smallint, |
|
|
104 CONSTRAINT pk_invo_id PRIMARY KEY(invo_id), |
|
|
105 CONSTRAINT fk_invo_inv_id FOREIGN KEY(invo_inv_id) REFERENCES mk_inventory(inv_id) |
|
|
106 ); |
|
|
107 |
|
|
108 CREATE TABLE IF NOT EXISTS mk_quest_property ( |
|
|
109 qp_id serial, |
|
|
110 qp_qs_id serial, |
|
|
111 qp_name varchar[32], |
|
|
112 qp_value text, |
|
|
113 CONSTRAINT pk_qp_id PRIMARY KEY(qp_id), |
|
|
114 CONSTRAINT fk_qp_qs_id FOREIGN KEY(qp_qs_id) REFERENCES mk_quest(qs_id) |
|
|
115 ); |
