MySQL-uMMORPG/uMMORPG_MySQL.sql at master · ADDonU/MySQL-uMMORPG · GitHub

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
-- For use in a tool like MySQL Workbench to create the database and tables

-- Uncomment DROP TABLE to delete the table if it exists and needs to be replaced

-- DROP TABLE accounts;

CREATE TABLE IF NOT EXISTS accounts (
	name VARCHAR(16) NOT NULL,
	password CHAR(40) NOT NULL,
	banned TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY(name)
);

-- DROP TABLE characters;

CREATE TABLE IF NOT EXISTS characters (
	name VARCHAR(16) NOT NULL,
	account VARCHAR(16) NOT NULL,
	class VARCHAR(16) NOT NULL,
	x FLOAT NOT NULL,
	y FLOAT NOT NULL,
	z FLOAT NOT NULL,
	level INT NOT NULL,
	health INT NOT NULL,
	mana INT NOT NULL,
	strength INT NOT NULL,
	intelligence INT NOT NULL,
	experience BIGINT NOT NULL,
	skillExperience BIGINT NOT NULL,
	gold BIGINT NOT NULL,
	coins BIGINT NOT NULL,
	online VARCHAR(16)
	deleted TINYINT UNSIGNED NOT NULL,

	PRIMARY KEY name,
    INDEX(account),
	FOREIGN KEY (account)
        REFERENCES accounts(name)
        ON DELETE CASCADE
);

-- DROP TABLE character_inventory;

CREATE TABLE IF NOT EXISTS character_inventory (
	character VARCHAR(16) NOT NULL,
	slot INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	valid TINYINT UNSIGNED NOT NULL,
	amount INT NOT NULL,
	petHealth INT NOT NULL,
	petLevel INT NOT NULL,
    petExperience INT NOT NULL,

	primary key (character, slot),
	FOREIGN KEY (characters)
        REFERENCES characters(name)
        ON DELETE CASCADE
);

-- DROP TABLE character_equipment;

CREATE TABLE IF NOT EXISTS character_equipment (
	character VARCHAR(16) NOT NULL,
	slot INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	valid TINYINT UNSIGNED NOT NULL,
	amount INT NOT NULL,

    primary key (character, slot),
	FOREIGN KEY (characters)
        REFERENCES characters(name)
        ON DELETE CASCADE
);

-- DROP TABLE character_skills;

CREATE TABLE IF NOT EXISTS character_skills (
	character VARCHAR(16) NOT NULL,
	name VARCHAR(50) NOT NULL,
	learned TINYINT UNSIGNED NOT NULL,
	level INT NOT NULL,
	castTimeEnd FLOAT NOT NULL,
	cooldownEnd FLOAT NOT NULL,
	buffTimeEnd FLOAT NOT NULL,

    INDEX(character),
	FOREIGN KEY (characters)
        REFERENCES characters(name)
        ON DELETE CASCADE
);

-- DROP TABLE character_quests;

CREATE TABLE IF NOT EXISTS character_quests (
	character VARCHAR(16) NOT NULL,
	name VARCHAR(50) NOT NULL,
	killed INT NOT NULL,
	completed TINYINT UNSIGNED NOT NULL,

    INDEX(character),
	FOREIGN KEY (characters)
        REFERENCES characters(name)
        ON DELETE CASCADE
);

-- DROP TABLE character_orders;

CREATE TABLE IF NOT EXISTS character_orders (
	orderid BIGINT NOT NULL AUTO_INCREMENT,
    character VARCHAR(16) NOT NULL,
    coins BIGINT NOT NULL,
    processed BIGINT NOT NULL,

	PRIMARY KEY(orderid),
    INDEX(character),
	FOREIGN KEY (characters)
        REFERENCES characters(name)
        ON DELETE CASCADE
);


-- DROP TABLE guild_info;

CREATE TABLE IF NOT EXISTS guild_info (
	name VARCHAR(16) NOT NULL,
	notice TEXT NOT NULL,
	PRIMARY KEY(name)
);

-- DROP TABLE guild_members;

CREATE TABLE IF NOT EXISTS guild_members (
	guild VARCHAR(16) NOT NULL,
	character VARCHAR(16) NOT NULL,
	rank INT NOT NULL,
  	PRIMARY KEY(guild, character),
	FOREIGN KEY (characters)
        REFERENCES characters(name)
        ON DELETE CASCADE
	FOREIGN KEY (guild)
        REFERENCES guild_info(name)
        ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------------------