schema/aur-schema.sql

   1 -- The MySQL database layout for the AUR.  Certain data
   2 -- is also included such as AccountTypes, etc.
   3 --
   4
   5 -- Define the Account Types for the AUR.
   6 --
   7 CREATE TABLE AccountTypes (
   8         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   9         AccountType VARCHAR(32) NOT NULL DEFAULT '',
  10         PRIMARY KEY (ID)
  11 ) ENGINE = InnoDB;
  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');
  16
  17
  18 -- User information for each user regardless of type.
  19 --
  20 CREATE TABLE Users (
  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,
  45         PRIMARY KEY (ID),
  46         UNIQUE (Username),
  47         UNIQUE (Email),
  48         FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION
  49 ) ENGINE = InnoDB;
  50 CREATE INDEX UsersAccountTypeID ON Users (AccountTypeID);
  51
  52
  53 -- SSH public keys used for the aurweb SSH/Git interface.
  54 --
  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
  61 ) ENGINE = InnoDB;
  62
  63
  64 -- Track Users logging in/out of AUR web site.
  65 --
  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,
  71         UNIQUE (SessionID)
  72 ) ENGINE = InnoDB;
  73
  74
  75 -- Information on package bases
  76 --
  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
  90         PRIMARY KEY (ID),
  91         UNIQUE (Name),
  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
  97 ) ENGINE = InnoDB;
  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);
 102
 103
 104 -- Keywords of package bases
 105 --
 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
 111 ) ENGINE = InnoDB;
 112
 113
 114 -- Information about the actual packages
 115 --
 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,
 123         PRIMARY KEY (ID),
 124         UNIQUE (Name),
 125         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 126 ) ENGINE = InnoDB;
 127
 128
 129 -- Information about licenses
 130 --
 131 CREATE TABLE Licenses (
 132         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 133         Name VARCHAR(255) NOT NULL,
 134         PRIMARY KEY (ID),
 135         UNIQUE (Name)
 136 ) ENGINE = InnoDB;
 137
 138
 139 -- Information about package-license-relations
 140 --
 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
 147 ) ENGINE = InnoDB;
 148
 149
 150 -- Information about groups
 151 --
 152 CREATE TABLE Groups (
 153         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 154         Name VARCHAR(255) NOT NULL,
 155         PRIMARY KEY (ID),
 156         UNIQUE (Name)
 157 ) ENGINE = InnoDB;
 158
 159
 160 -- Information about package-group-relations
 161 --
 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
 168 ) ENGINE = InnoDB;
 169
 170
 171 -- Define the package dependency types
 172 --
 173 CREATE TABLE DependencyTypes (
 174         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 175         Name VARCHAR(32) NOT NULL DEFAULT '',
 176         PRIMARY KEY (ID)
 177 ) ENGINE = InnoDB;
 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');
 182
 183
 184 -- Track which dependencies a package has
 185 --
 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
 195 ) ENGINE = InnoDB;
 196 CREATE INDEX DependsPackageID ON PackageDepends (PackageID);
 197 CREATE INDEX DependsDepName ON PackageDepends (DepName);
 198
 199
 200 -- Define the package relation types
 201 --
 202 CREATE TABLE RelationTypes (
 203         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 204         Name VARCHAR(32) NOT NULL DEFAULT '',
 205         PRIMARY KEY (ID)
 206 ) ENGINE = InnoDB;
 207 INSERT INTO RelationTypes VALUES (1, 'conflicts');
 208 INSERT INTO RelationTypes VALUES (2, 'provides');
 209 INSERT INTO RelationTypes VALUES (3, 'replaces');
 210
 211
 212 -- Track which conflicts, provides and replaces a package has
 213 --
 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
 222 ) ENGINE = InnoDB;
 223 CREATE INDEX RelationsPackageID ON PackageRelations (PackageID);
 224 CREATE INDEX RelationsRelName ON PackageRelations (RelName);
 225
 226
 227 -- Track which sources a package has
 228 --
 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
 234 ) ENGINE = InnoDB;
 235 CREATE INDEX SourcesPackageID ON PackageSources (PackageID);
 236
 237
 238 -- Track votes for packages
 239 --
 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
 246 ) ENGINE = InnoDB;
 247 CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
 248 CREATE INDEX VotesUsersID ON PackageVotes (UsersID);
 249 CREATE INDEX VotesPackageBaseID ON PackageVotes (PackageBaseID);
 250
 251 -- Record comments for packages
 252 --
 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,
 265         PRIMARY KEY (ID),
 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
 270 ) ENGINE = InnoDB;
 271 CREATE INDEX CommentsUsersID ON PackageComments (UsersID);
 272 CREATE INDEX CommentsPackageBaseID ON PackageComments (PackageBaseID);
 273
 274 -- Package base co-maintainers
 275 --
 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
 282 ) ENGINE = InnoDB;
 283 CREATE INDEX ComaintainersUsersID ON PackageComaintainers (UsersID);
 284 CREATE INDEX ComaintainersPackageBaseID ON PackageComaintainers (PackageBaseID);
 285
 286 -- Package base notifications
 287 --
 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
 293 ) ENGINE = InnoDB;
 294 CREATE UNIQUE INDEX NotifyUserIDPkgID ON PackageNotifications (UserID, PackageBaseID);
 295
 296 -- Package name blacklist
 297 --
 298 CREATE TABLE PackageBlacklist (
 299         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 300         Name VARCHAR(64) NOT NULL,
 301         PRIMARY KEY (ID),
 302         UNIQUE (Name)
 303 ) ENGINE = InnoDB;
 304
 305 -- Providers in the official repositories
 306 --
 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,
 312         PRIMARY KEY (ID)
 313 ) ENGINE = InnoDB;
 314 CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides);
 315
 316 -- Define package request types
 317 --
 318 CREATE TABLE RequestTypes (
 319         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 320         Name VARCHAR(32) NOT NULL DEFAULT '',
 321         PRIMARY KEY (ID)
 322 ) ENGINE = InnoDB;
 323 INSERT INTO RequestTypes VALUES (1, 'deletion');
 324 INSERT INTO RequestTypes VALUES (2, 'orphan');
 325 INSERT INTO RequestTypes VALUES (3, 'merge');
 326
 327 -- Package requests
 328 --
 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,
 340         PRIMARY KEY (ID),
 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
 344 ) ENGINE = InnoDB;
 345 CREATE INDEX RequestsUsersID ON PackageRequests (UsersID);
 346 CREATE INDEX RequestsPackageBaseID ON PackageRequests (PackageBaseID);
 347
 348 -- Vote information
 349 --
 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',
 362         PRIMARY KEY  (ID),
 363         FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE
 364 ) ENGINE = InnoDB;
 365
 366 -- Individual vote records
 367 --
 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
 373 ) ENGINE = InnoDB;
 374
 375 -- Malicious user banning
 376 --
 377 CREATE TABLE Bans (
 378         IPAddress VARCHAR(45) NOT NULL,
 379         BanTS TIMESTAMP NOT NULL,
 380         PRIMARY KEY (IPAddress)
 381 ) ENGINE = InnoDB;
 382
 383 -- Terms and Conditions
 384 --
 385 CREATE TABLE Terms (
 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,
 390         PRIMARY KEY (ID)
 391 ) ENGINE = InnoDB;
 392
 393 -- Terms and Conditions accepted by users
 394 --
 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
 401 ) ENGINE = InnoDB;
 402
 403 -- Rate limits for API
 404 --
 405 CREATE TABLE `ApiRateLimit` (
 406   IP VARCHAR(45) NOT NULL,
 407   Requests INT(11) NOT NULL,
 408   WindowStart BIGINT(20) NOT NULL,
 409   PRIMARY KEY (`ip`)
 410 ) ENGINE = InnoDB;
 411 CREATE INDEX ApiRateLimitWindowStart ON ApiRateLimit (WindowStart);