-
Notifications
You must be signed in to change notification settings - Fork 0
/
afal-schema.sql.mysql
109 lines (109 loc) · 4.11 KB
/
afal-schema.sql.mysql
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
create table fr_character (
char_name varchar(128) not null primary key,
status enum ( 'Unknown', 'active', 'inactive', 'dead', 'dummy' ) not null default 'active',
association text,
fullname text,
player text,
race text,
gender enum ( 'Unknown', 'Male', 'Female', 'Herm', 'Male?', 'Female?', 'No Gender'),
class text,
alignment text,
picture_url text,
large_picture_url text,
equipment text,
char_acteristics text,
note text,
hidden_note text,
cash_cp integer
) engine=innodb;
create table fr_marching (
marching_name varchar(128) not null,
char_name varchar(128) not null references fr_character,
over tinyint unsigned not null,
down tinyint unsigned not null,
width tinyint unsigned not null default 1,
height tinyint unsigned not null default 1,
primary key(marching_name, char_name),
foreign key (char_name) references fr_character(char_name)
) engine=innodb;
create table fr_party (
party_name varchar(128) primary key,
type enum ('Unknown', 'AFAL', 'Character', 'NPCs') not null,
date integer unsigned,
note text
) engine=innodb;
create table fr_char_party (
char_name varchar(128) not null references fr_character,
party_name varchar(128) not null references fr_party,
share double unsigned not null default 1.0,
primary key(char_name, party_name),
foreign key (party_name) references fr_party(party_name),
foreign key (char_name) references fr_character(char_name)
)engine=innodb;
create table fr_item (
item_name varchar(128) primary key,
found_by varchar(128) not null references fr_party,
owned_by varchar(128) not null references fr_party,
held_by varchar(128) references fr_character,
date_found integer unsigned not null,
date_xfrd integer unsigned,
note text,
value_cp integer unsigned,
foreign key (found_by) references fr_party(party_name),
foreign key (owned_by) references fr_party(party_name),
foreign key (held_by) references fr_character(char_name)
)engine=innodb;
create table fr_debt (
debt_id integer unsigned primary key auto_increment,
date integer unsigned not null,
debtor varchar(128) not null references fr_character,
debtee varchar(128) not null references fr_character,
repay_order integer unsigned not null default 0,
share double unsigned not null default 1.0,
item varchar(128) references fr_item,
initial_cp integer unsigned not null,
owed_cp integer unsigned not null,
foreign key (debtor) references fr_character(char_name),
foreign key (debtee) references fr_character(char_name),
foreign key (item) references fr_item(item_name)
)engine=innodb, auto_increment=100000;
create table fr_journal (
journal_id integer unsigned not null primary key auto_increment,
part_of integer unsigned references fr_journal,
ourtime timestamp not null default CURRENT_TIMESTAMP,
date integer unsigned not null,
cash_cp integer unsigned,
virtual_cp integer unsigned,
description text not null,
foreign key (part_of) references fr_journal(journal_id)
)engine=innodb, auto_increment=1000001;
create table fr_journ_by (
made_by varchar(128) not null references fr_party,
journal_id integer unsigned not null references fr_journal,
primary key(made_by, journal_id),
foreign key (made_by) references fr_party(party_name),
foreign key (journal_id) references fr_journal(journal_id)
)engine=innodb;
create table fr_journ_to (
made_to varchar(128) not null references fr_party,
journal_id integer unsigned references fr_journal,
primary key(made_to, journal_id),
foreign key (made_to) references fr_party(party_name),
foreign key (journal_id) references fr_journal(journal_id)
)engine=innodb;
create table fr_money_type (
money_id integer unsigned not null primary key auto_increment,
coin varchar(128) unique not null,
copper_equiv integer unsigned not null,
priority integer unsigned not null,
abbrev text,
note text
)engine=innodb, auto_increment=10000000;
create table fr_char_money (
owner varchar(128) not null references fr_party,
money_id integer unsigned not null references money_type,
quantity integer unsigned not null,
primary key(owner,money_id),
foreign key (owner) references fr_party(party_name),
foreign key (money_id) references fr_money_type(money_id)
)engine=innodb;