1 -- The MySQL database layout for the AUR. Certain data
2 -- is also included such as AccountTypes, etc.
5 -- Define the Account Types for the AUR.
7 CREATE TABLE AccountTypes (
8 ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
9 AccountType VARCHAR(32) NOT NULL DEFAULT '',
12 INSERT INTO AccountTypes (ID, AccountType) VALUES (1, 'User');
13 INSERT INTO AccountTypes (ID, AccountType) VALUES (2, 'Trusted User');
14 INSERT INTO AccountTypes (ID, AccountType) VALUES (3, 'Developer');
15 INSERT INTO AccountTypes (ID, AccountType) VALUES (4, 'Trusted User & Developer');
18 -- User information for each user regardless of type.
21 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
22 AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1,
23 Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0,
24 Username VARCHAR(32) NOT NULL,
25 Email VARCHAR(254) NOT NULL,
26 HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0,
27 Passwd VARCHAR(255) NOT NULL,
28 Salt CHAR(32) NOT NULL DEFAULT '',
29 ResetKey CHAR(32) NOT NULL DEFAULT '',
30 RealName VARCHAR(64) NOT NULL DEFAULT '',
31 LangPreference VARCHAR(6) NOT NULL DEFAULT 'en',
32 Timezone VARCHAR(32) NOT NULL DEFAULT 'UTC',
33 Homepage TEXT NULL DEFAULT NULL,
34 IRCNick VARCHAR(32) NOT NULL DEFAULT '',
35 PGPKey VARCHAR(40) NULL DEFAULT NULL,
36 LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0,
37 LastLoginIPAddress VARCHAR(45) NULL DEFAULT NULL,
38 LastSSHLogin BIGINT UNSIGNED NOT NULL DEFAULT 0,
39 LastSSHLoginIPAddress VARCHAR(45) NULL DEFAULT NULL,
40 InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
41 RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
42 CommentNotify TINYINT(1) NOT NULL DEFAULT 1,
43 UpdateNotify TINYINT(1) NOT NULL DEFAULT 0,
44 OwnershipNotify TINYINT(1) NOT NULL DEFAULT 1,
48 FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION
50 CREATE INDEX UsersAccountTypeID ON Users (AccountTypeID);
53 -- SSH public keys used for the aurweb SSH/Git interface.
55 CREATE TABLE SSHPubKeys (
56 UserID INTEGER UNSIGNED NOT NULL,
57 Fingerprint VARCHAR(44) NOT NULL,
58 PubKey VARCHAR(4096) NOT NULL,
59 PRIMARY KEY (Fingerprint),
60 FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
64 -- Track Users logging in/out of AUR web site.
66 CREATE TABLE Sessions (
67 UsersID INTEGER UNSIGNED NOT NULL,
68 SessionID CHAR(32) NOT NULL,
69 LastUpdateTS BIGINT UNSIGNED NOT NULL,
70 FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
75 -- Information on package bases
77 CREATE TABLE PackageBases (
78 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
79 Name VARCHAR(255) NOT NULL,
80 NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
81 Popularity DECIMAL(10,6) UNSIGNED NOT NULL DEFAULT 0,
82 OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
83 FlaggerComment TEXT NOT NULL,
84 SubmittedTS BIGINT UNSIGNED NOT NULL,
85 ModifiedTS BIGINT UNSIGNED NOT NULL,
86 FlaggerUID INTEGER UNSIGNED NULL DEFAULT NULL, -- who flagged the package out-of-date?
87 SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL, -- who submitted it?
88 MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL, -- User
89 PackagerUID INTEGER UNSIGNED NULL DEFAULT NULL, -- Last packager
92 FOREIGN KEY (FlaggerUID) REFERENCES Users(ID) ON DELETE SET NULL,
93 -- deleting a user will cause packages to be orphaned, not deleted
94 FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL,
95 FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL,
96 FOREIGN KEY (PackagerUID) REFERENCES Users(ID) ON DELETE SET NULL
98 CREATE INDEX BasesNumVotes ON PackageBases (NumVotes);
99 CREATE INDEX BasesSubmitterUID ON PackageBases (SubmitterUID);
100 CREATE INDEX BasesMaintainerUID ON PackageBases (MaintainerUID);
101 CREATE INDEX BasesPackagerUID ON PackageBases (PackagerUID);
104 -- Keywords of package bases
106 CREATE TABLE PackageKeywords (
107 PackageBaseID INTEGER UNSIGNED NOT NULL,
108 Keyword VARCHAR(255) NOT NULL DEFAULT '',
109 PRIMARY KEY (PackageBaseID, Keyword),
110 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
114 -- Information about the actual packages
116 CREATE TABLE Packages (
117 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
118 PackageBaseID INTEGER UNSIGNED NOT NULL,
119 Name VARCHAR(255) NOT NULL,
120 Version VARCHAR(255) NOT NULL DEFAULT '',
121 Description VARCHAR(255) NULL DEFAULT NULL,
122 URL VARCHAR(8000) NULL DEFAULT NULL,
125 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
129 -- Information about licenses
131 CREATE TABLE Licenses (
132 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
133 Name VARCHAR(255) NOT NULL,
139 -- Information about package-license-relations
141 CREATE TABLE PackageLicenses (
142 PackageID INTEGER UNSIGNED NOT NULL,
143 LicenseID INTEGER UNSIGNED NOT NULL,
144 PRIMARY KEY (PackageID, LicenseID),
145 FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
146 FOREIGN KEY (LicenseID) REFERENCES Licenses(ID) ON DELETE CASCADE
150 -- Information about groups
152 CREATE TABLE Groups (
153 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
154 Name VARCHAR(255) NOT NULL,
160 -- Information about package-group-relations
162 CREATE TABLE PackageGroups (
163 PackageID INTEGER UNSIGNED NOT NULL,
164 GroupID INTEGER UNSIGNED NOT NULL,
165 PRIMARY KEY (PackageID, GroupID),
166 FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
167 FOREIGN KEY (GroupID) REFERENCES Groups(ID) ON DELETE CASCADE
171 -- Define the package dependency types
173 CREATE TABLE DependencyTypes (
174 ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
175 Name VARCHAR(32) NOT NULL DEFAULT '',
178 INSERT INTO DependencyTypes VALUES (1, 'depends');
179 INSERT INTO DependencyTypes VALUES (2, 'makedepends');
180 INSERT INTO DependencyTypes VALUES (3, 'checkdepends');
181 INSERT INTO DependencyTypes VALUES (4, 'optdepends');
184 -- Track which dependencies a package has
186 CREATE TABLE PackageDepends (
187 PackageID INTEGER UNSIGNED NOT NULL,
188 DepTypeID TINYINT UNSIGNED NOT NULL,
189 DepName VARCHAR(255) NOT NULL,
190 DepDesc VARCHAR(255) NULL DEFAULT NULL,
191 DepCondition VARCHAR(255),
192 DepArch VARCHAR(255) NULL DEFAULT NULL,
193 FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
194 FOREIGN KEY (DepTypeID) REFERENCES DependencyTypes(ID) ON DELETE NO ACTION
196 CREATE INDEX DependsPackageID ON PackageDepends (PackageID);
197 CREATE INDEX DependsDepName ON PackageDepends (DepName);
200 -- Define the package relation types
202 CREATE TABLE RelationTypes (
203 ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
204 Name VARCHAR(32) NOT NULL DEFAULT '',
207 INSERT INTO RelationTypes VALUES (1, 'conflicts');
208 INSERT INTO RelationTypes VALUES (2, 'provides');
209 INSERT INTO RelationTypes VALUES (3, 'replaces');
212 -- Track which conflicts, provides and replaces a package has
214 CREATE TABLE PackageRelations (
215 PackageID INTEGER UNSIGNED NOT NULL,
216 RelTypeID TINYINT UNSIGNED NOT NULL,
217 RelName VARCHAR(255) NOT NULL,
218 RelCondition VARCHAR(255),
219 RelArch VARCHAR(255) NULL DEFAULT NULL,
220 FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
221 FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
223 CREATE INDEX RelationsPackageID ON PackageRelations (PackageID);
224 CREATE INDEX RelationsRelName ON PackageRelations (RelName);
227 -- Track which sources a package has
229 CREATE TABLE PackageSources (
230 PackageID INTEGER UNSIGNED NOT NULL,
231 Source VARCHAR(8000) NOT NULL DEFAULT '/dev/null',
232 SourceArch VARCHAR(255) NULL DEFAULT NULL,
233 FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE
235 CREATE INDEX SourcesPackageID ON PackageSources (PackageID);
238 -- Track votes for packages
240 CREATE TABLE PackageVotes (
241 UsersID INTEGER UNSIGNED NOT NULL,
242 PackageBaseID INTEGER UNSIGNED NOT NULL,
243 VoteTS BIGINT UNSIGNED NULL DEFAULT NULL,
244 FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
245 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
247 CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
248 CREATE INDEX VotesUsersID ON PackageVotes (UsersID);
249 CREATE INDEX VotesPackageBaseID ON PackageVotes (PackageBaseID);
251 -- Record comments for packages
253 CREATE TABLE PackageComments (
254 ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
255 PackageBaseID INTEGER UNSIGNED NOT NULL,
256 UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
257 Comments TEXT NOT NULL,
258 RenderedComment TEXT NOT NULL,
259 CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
260 EditedTS BIGINT UNSIGNED NULL DEFAULT NULL,
261 EditedUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
262 DelTS BIGINT UNSIGNED NULL DEFAULT NULL,
263 DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
264 PinnedTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
266 FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
267 FOREIGN KEY (EditedUsersID) REFERENCES Users(ID) ON DELETE SET NULL,
268 FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
269 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
271 CREATE INDEX CommentsUsersID ON PackageComments (UsersID);
272 CREATE INDEX CommentsPackageBaseID ON PackageComments (PackageBaseID);
274 -- Package base co-maintainers
276 CREATE TABLE PackageComaintainers (
277 UsersID INTEGER UNSIGNED NOT NULL,
278 PackageBaseID INTEGER UNSIGNED NOT NULL,
279 Priority INTEGER UNSIGNED NOT NULL,
280 FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
281 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
283 CREATE INDEX ComaintainersUsersID ON PackageComaintainers (UsersID);
284 CREATE INDEX ComaintainersPackageBaseID ON PackageComaintainers (PackageBaseID);
286 -- Package base notifications
288 CREATE TABLE PackageNotifications (
289 PackageBaseID INTEGER UNSIGNED NOT NULL,
290 UserID INTEGER UNSIGNED NOT NULL,
291 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
292 FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
294 CREATE UNIQUE INDEX NotifyUserIDPkgID ON PackageNotifications (UserID, PackageBaseID);
296 -- Package name blacklist
298 CREATE TABLE PackageBlacklist (
299 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
300 Name VARCHAR(64) NOT NULL,
305 -- Providers in the official repositories
307 CREATE TABLE OfficialProviders (
308 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
309 Name VARCHAR(64) NOT NULL,
310 Repo VARCHAR(64) NOT NULL,
311 Provides VARCHAR(64) NOT NULL,
314 CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides);
316 -- Define package request types
318 CREATE TABLE RequestTypes (
319 ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
320 Name VARCHAR(32) NOT NULL DEFAULT '',
323 INSERT INTO RequestTypes VALUES (1, 'deletion');
324 INSERT INTO RequestTypes VALUES (2, 'orphan');
325 INSERT INTO RequestTypes VALUES (3, 'merge');
329 CREATE TABLE PackageRequests (
330 ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
331 ReqTypeID TINYINT UNSIGNED NOT NULL,
332 PackageBaseID INTEGER UNSIGNED NULL,
333 PackageBaseName VARCHAR(255) NOT NULL,
334 MergeBaseName VARCHAR(255) NULL,
335 UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
336 Comments TEXT NOT NULL,
337 ClosureComment TEXT NOT NULL,
338 RequestTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
339 Status TINYINT UNSIGNED NOT NULL DEFAULT 0,
341 FOREIGN KEY (ReqTypeID) REFERENCES RequestTypes(ID) ON DELETE NO ACTION,
342 FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
343 FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE SET NULL
345 CREATE INDEX RequestsUsersID ON PackageRequests (UsersID);
346 CREATE INDEX RequestsPackageBaseID ON PackageRequests (PackageBaseID);
350 CREATE TABLE IF NOT EXISTS TU_VoteInfo (
351 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
352 Agenda TEXT NOT NULL,
353 User VARCHAR(32) NOT NULL,
354 Submitted BIGINT UNSIGNED NOT NULL,
355 End BIGINT UNSIGNED NOT NULL,
356 Quorum DECIMAL(2, 2) UNSIGNED NOT NULL,
357 SubmitterID INTEGER UNSIGNED NOT NULL,
358 Yes TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
359 No TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
360 Abstain TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
361 ActiveTUs TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
363 FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE
366 -- Individual vote records
368 CREATE TABLE IF NOT EXISTS TU_Votes (
369 VoteID INTEGER UNSIGNED NOT NULL,
370 UserID INTEGER UNSIGNED NOT NULL,
371 FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
372 FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
375 -- Malicious user banning
378 IPAddress VARCHAR(45) NOT NULL,
379 BanTS TIMESTAMP NOT NULL,
380 PRIMARY KEY (IPAddress)
383 -- Terms and Conditions
386 ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
387 Description VARCHAR(255) NOT NULL,
388 URL VARCHAR(8000) NOT NULL,
389 Revision INTEGER UNSIGNED NOT NULL DEFAULT 1,
393 -- Terms and Conditions accepted by users
395 CREATE TABLE AcceptedTerms (
396 UsersID INTEGER UNSIGNED NOT NULL,
397 TermsID INTEGER UNSIGNED NOT NULL,
398 Revision INTEGER UNSIGNED NOT NULL DEFAULT 0,
399 FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
400 FOREIGN KEY (TermsID) REFERENCES Terms(ID) ON DELETE CASCADE
403 -- Rate limits for API
405 CREATE TABLE `ApiRateLimit` (
406 IP VARCHAR(45) NOT NULL,
407 Requests INT(11) NOT NULL,
408 WindowStart BIGINT(20) NOT NULL,
411 CREATE INDEX ApiRateLimitWindowStart ON ApiRateLimit (WindowStart);