· 7 years ago · Nov 11, 2018, 04:24 PM
1-- Auroris database install
2-- TODO: Rebuild the Auroris Database Schema
3
4-- Set default variables
5SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
6SET time_zone = "-07:00";
7
8
9
10-- Delete old table data
11DROP TABLE IF EXISTS `bans`;
12DROP TABLE IF EXISTS `igloos`;
13DROP TABLE IF EXISTS `penguins`;
14DROP TABLE IF EXISTS `postcards`;
15DROP TABLE IF EXISTS `puffles`;
16DROP TABLE IF EXISTS `social`;
17DROP TABLE IF EXISTS `chatlog`;
18DROP TABLE IF EXISTS `cfc`;
19DROP TABLE IF EXISTS `redemption`;
20DROP TABLE IF EXISTS `worlds`;
21
22
23
24-- Table structure for bans
25CREATE TABLE `bans` (
26 `ID` int(11) NOT NULL,
27 `Moderator` char(12) NOT NULL,
28 `Player` int(11) UNSIGNED NOT NULL,
29 `Comment` text NOT NULL,
30 `Expiration` int(8) NOT NULL,
31 `Time` int(8) NOT NULL,
32 `Type` smallint(3) UNSIGNED NOT NULL
33) ENGINE=InnoDB DEFAULT CHARSET=utf8;
34
35
36
37-- Table structure for penguins
38CREATE TABLE `penguins` (
39 `ID` int(10) UNSIGNED NOT NULL,
40 `Username` char(12) NOT NULL,
41 `Nickname` char(16) NOT NULL,
42 `Password` char(255) NOT NULL,
43 `LoginKey` char(32) NOT NULL,
44 `ConfirmationHash` char(32) NOT NULL,
45 `SWID` char(38) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
46 `Avatar` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
47 `AvatarAttributes` char(98) NOT NULL DEFAULT '{"spriteScale":100,"spriteSpeed":100,"ignoresBlockLayer":false,"invisible":false,"floating":false}',
48 `Email` char(254) NOT NULL,
49 `RegistrationDate` int(8) NOT NULL,
50 `Moderator` tinyint(1) NOT NULL DEFAULT '0',
51 `Inventory` text NOT NULL,
52 `CareInventory` text NOT NULL,
53 `Coins` mediumint(7) UNSIGNED NOT NULL DEFAULT '200000',
54 `Igloo` int(10) UNSIGNED NOT NULL COMMENT 'Current active igloo',
55 `Igloos` text NOT NULL COMMENT 'Owned igloo types',
56 `Floors` text NOT NULL COMMENT 'Owned floorings',
57 `Locations` text NOT NULL COMMENT 'Owned locations',
58 `Furniture` text NOT NULL COMMENT 'Furniture inventory',
59 `Color` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
60 `Head` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
61 `Face` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
62 `Neck` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
63 `Body` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
64 `Hand` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
65 `Feet` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
66 `Photo` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
67 `Flag` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
68 `Walking` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Puffle ID',
69 `Banned` varchar(20) NOT NULL DEFAULT '0' COMMENT 'Timestamp of ban',
70 `Stamps` text NOT NULL,
71 `StampBook` varchar(150) NOT NULL DEFAULT '1%1%1%1',
72 `EPF` varchar(9) NOT NULL DEFAULT '0,0,0',
73 `PuffleQuest` varchar(25) NOT NULL DEFAULT '0,1,|0;0;1403959119;',
74 `Online` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
75 `CurrentServer` varchar(255) NOT NULL -- ?
76) ENGINE=InnoDB DEFAULT CHARSET=utf8;
77
78
79
80-- Table structure for igloos
81CREATE TABLE `igloos` (
82 `ID` int(10) UNSIGNED NOT NULL,
83 `Owner` int(10) UNSIGNED NOT NULL,
84 `Type` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
85 `Floor` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
86 `Music` smallint(6) NOT NULL DEFAULT '0',
87 `Furniture` text NOT NULL,
88 `Location` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
89 `Likes` text NOT NULL,
90 `Locked` tinyint(1) NOT NULL DEFAULT '1'
91) ENGINE=InnoDB DEFAULT CHARSET=utf8;
92
93
94
95-- Table structure for puffles
96CREATE TABLE `puffles` (
97 `ID` int(10) UNSIGNED NOT NULL,
98 `Owner` int(10) UNSIGNED NOT NULL,
99 `Name` char(12) NOT NULL,
100 `AdoptionDate` int(8) NOT NULL,
101 `Type` tinyint(3) UNSIGNED NOT NULL,
102 `Hat` smallint(5) UNSIGNED NOT NULL,
103 `Food` tinyint(3) UNSIGNED NOT NULL DEFAULT '100',
104 `Play` tinyint(3) UNSIGNED NOT NULL DEFAULT '100',
105 `Rest` tinyint(3) UNSIGNED NOT NULL DEFAULT '100',
106 `Clean` tinyint(3) UNSIGNED NOT NULL DEFAULT '100',
107 `Backyard` tinyint(1) NOT NULL DEFAULT '0'
108) ENGINE=InnoDB DEFAULT CHARSET=utf8;
109
110
111
112-- Table structure for table postcards
113CREATE TABLE `postcards` (
114 `ID` int(10) UNSIGNED NOT NULL,
115 `Recipient` int(10) UNSIGNED NOT NULL,
116 `SenderName` char(12) NOT NULL,
117 `SenderID` int(10) UNSIGNED NOT NULL,
118 `Details` varchar(12) NOT NULL,
119 `Date` int(8) NOT NULL,
120 `Type` smallint(5) UNSIGNED NOT NULL,
121 `HasRead` tinyint(1) NOT NULL DEFAULT '0'
122) ENGINE=InnoDB DEFAULT CHARSET=utf8;
123
124
125
126-- Table structure for redemption tokens
127CREATE TABLE `redemption` (
128 `ID` int(10) UNSIGNED NOT NULL,
129 `Name` varchar(255) NOT NULL, -- ?
130 `Items` varchar(255) NOT NULL, -- ?
131 `Coins` varchar(255) NOT NULL, -- ?
132 `Expired` varchar(255) NOT NULL, -- ?
133 `Redeemed` varchar(255) NOT NULL -- ?
134) ENGINE=InnoDB DEFAULT CHARSET=utf8;
135
136
137
138-- Table structure for worlds (servers?)
139CREATE TABLE `worlds` (
140 `ID` int(10) UNSIGNED NOT NULL,
141 `worldID` int(10) UNSIGNED NOT NULL, -- ?
142 `worldName` varchar(20) NOT NULL, -- ?
143 `worldPopulation` int(10) UNSIGNED NOT NULL DEFAULT '0',
144 `getFilterVersion` varchar(255) NOT NULL -- ?
145) ENGINE=InnoDB DEFAULT CHARSET=utf8;
146
147
148
149-- Table structure for coins for change
150-- ID, donationTotal, "cat"+categoryID,
151CREATE TABLE `cfc` (
152 `ID` int(10) UNSIGNED NOT NULL,
153 `donationTotal` int(10) UNSIGNED NOT NULL
154) ENGINE=InnoDB DEFAULT CHARSET=utf8;
155
156
157
158-- Social
159CREATE TABLE `social` (
160 `ID` int(10) UNSIGNED NOT NULL,
161 `version` int(10) NOT NULL
162) ENGINE=InnoDB DEFAULT CHARSET=utf8;
163
164
165
166-- Chatlog
167CREATE TABLE `chatlog` (
168 `ID` int(10) UNSIGNED NOT NULL,
169 `PlayerID` int(10) UNSIGNED NOT NULL,
170 `PlayerNickname` char(16) NOT NULL,
171 `Message` varchar(255) NOT NULL, -- ?
172 `Timestamp` varchar(30) NOT NULL, -- ?
173 `Action` varchar(255) NOT NULL -- ?
174) ENGINE=InnoDB DEFAULT CHARSET=utf8;