This repository has been archived by the owner on Mar 14, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 149
/
README.DATABASE
131 lines (84 loc) · 5.94 KB
/
README.DATABASE
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
This file contains list of all requirements that database must fit for OTServ. They are grouped in two parts:
I. Database storage system requirements.
II. Database definition requirements.
If you are trying to run OTServ on database system that is not currently supported by SVN:
- Make sure that database system provides all required features.
If you are trying to create OTServ distro:
- Make sure that your provided sample database schema defines valid database.
If you are trying to run OTServ:
- Make sure you are running it on system that provides all required features.
----------
I. Database storage system requirements.
This part lists features that database system must provide in order to implement OTServ database in it.
----------
1. Triggers.
Triggers are actions that database system executes automaticly on given event.
OTSserv moved alot of work to database side which makes us sure that database will take care about it's own integrity. It also makes OTServ code smaller as it doesn't need to contain many redundant stuff.
It is practicly impossible to implement OTServ database on system that doesn't provide triggers.
Note: MySQL contains various storage engines. It is not possible to implement OTServ database on MyISAM engine! Make sure that you use InnoDB engine in MySQL!
2. Foreign keys.
Foreign keys connects data between different tables.
Foreign keys makes us sure that each record that requires data from another table will be connected with that data and that there won't be any garbage caused by lost connection between data.
If your database doesn't support foreign keys you can try to implement them using triggers (like it is done for SQLite for example).
----------
II. Database definition requirements.
This part specifies database behaviour which all your databases must follow if you want to run OTServ on them.
----------
Important note: ALL those actions must be implemented WITHIN database, not on top of driver layer. Those actions must be performed for ALL queries, not only those from OTServ. That guarantees that database will keep it's integrity even if accessed from third party software.
Important note: It is important for triggers if they are executed BEFORE or AFTER event.
* Foreign key accounts.group_id.
- It must not be possible to create account with key of unexisting group.
- It must not be possible to delete group which contains any account assigned to it.
* Unique key players.name.
- It must not be possible to create more then one player with same name.
* Foreign key players.account_id.
- It must not be possible to create player with key of unexisting account.
- When account is deleted all it's players must be also deleted.
* Foreign key players.group_id.
- It must not be possible to create player with key of unexisting group.
- It must not be possible to delete group which contains any player assigned to it.
* Foreign key guild_ranks.guild_id.
- It must not be possible to create rank with key of unexisting guild.
- When guild is deleted all it's ranks must be also deleted.
* Foreign key player_viplist.player_id.
- It must not be possible to create viplist for player which not exists.
- When player is deleted his/her entire viplist must be also deleted.
* Foreign key player_viplist.vip_id.
- It must not be possible to create viplist entry with player which not exists.
- When player is deleted entries in all viplists that points to him/her must also be deleted.
* Foreign key player_spells.player_id.
- It must not be possible to save spell with key of unexisting player.
- When player is deleted all it's spells must also be deleted.
* Foreign key player_storage.player_id.
- It must not be possible to save storage value with key of unexisting player.
- When player is deleted all it's storage values must also be deleted.
* Foreign key player_skills.player_id.
- It must not be possible to save skill with key of unexisting player.
- When player is deleted all it's skills must also be deleted.
* Foreign key player_items.player_id.
- It must not be possible to save item with key of unexisting player.
- When player is deleted all it's items must also be deleted.
* Unique key players_items.player_id, player_items.sid.
- Each player can contain only one item with given sid.
* Foreign key house_lists.house_id.
- It must not be possible to save list with key of unexisting house.
- When house is deleted all it's lists must also be deleted.
* Foreign key tile_items.tile_id.
- It must not be possible to save tile item with key of unexisting tile.
- When tile is deleted all it's items must also be deleted.
* Foreign key player_depotitems.player_id.
- It must not be possible to save depot item with key of unexisting player.
- When player is deleted all it's depot items must also be deleted.
* Unique key player_depotitems.player_id, player_depotitems.sid.
- Each player can contain only one item with given sid.
* Trigger before delete on accounts.
- Before deleting account all bans on this account (with it's key and type 3) must be deleted.
* Trigger before delete on guilds.
- Before guild is deleted all it's members must have deleted rank assignments and guild nicks.
* Trigger before delete on players.
- Before deleting player all bans on this player (with it's key and type 2) must be deleted.
- Before player is deleted all houses rent by him/her must be freed.
* Trigger after insert on guild.
- After guild is created default ranks for leader (level 3), vice-leaver (level 2) and member (level 1) must be created.
* Trigger after insert on players.
- After player is created database must create skills with ids 0-6 for him with default values.