Mercurial > malikania

comparison database/postgresql/script/init.sql @ 0:8991989c4708

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial import
author David Demelier <markand@malikania.fr>
date Tue, 22 Mar 2016 18:26:05 +0100
parents
children 99792c6c8b06
comparison
equal deleted inserted replaced
-1:000000000000 0:8991989c4708
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 );