schema/aur-schema.sql

   1 -- The MySQL database layout for the AUR.  Certain data
   2 -- is also included such as AccountTypes, etc.
   3 --
   4 DROP DATABASE IF EXISTS AUR;
   5 CREATE DATABASE AUR DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
   6 USE AUR;
   7
   8 -- Define the Account Types for the AUR.
   9 --
  10 CREATE TABLE AccountTypes (
  11         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  12         AccountType VARCHAR(32) NOT NULL DEFAULT '',
  13         PRIMARY KEY (ID)
  14 ) ENGINE = InnoDB;
  15 INSERT INTO AccountTypes (ID, AccountType) VALUES (1, 'User');
  16 INSERT INTO AccountTypes (ID, AccountType) VALUES (2, 'Trusted User');
  17 INSERT INTO AccountTypes (ID, AccountType) VALUES (3, 'Developer');
  18 INSERT INTO AccountTypes (ID, AccountType) VALUES (4, 'Trusted User & Developer');
  19
  20
  21 -- User information for each user regardless of type.
  22 --
  23 CREATE TABLE Users (
  24         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  25         AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1,
  26         Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0,
  27         Username VARCHAR(32) NOT NULL,
  28         Email VARCHAR(254) NOT NULL,
  29         HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0,
  30         Passwd CHAR(32) NOT NULL,
  31         Salt CHAR(32) NOT NULL DEFAULT '',
  32         ResetKey CHAR(32) NOT NULL DEFAULT '',
  33         RealName VARCHAR(64) NOT NULL DEFAULT '',
  34         LangPreference VARCHAR(5) NOT NULL DEFAULT 'en',
  35         IRCNick VARCHAR(32) NOT NULL DEFAULT '',
  36         PGPKey VARCHAR(40) NULL DEFAULT NULL,
  37         LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0,
  38         LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0,
  39         InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
  40         RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  41         CommentNotify TINYINT(1) NOT NULL DEFAULT 1,
  42         UpdateNotify TINYINT(1) NOT NULL DEFAULT 0,
  43         PRIMARY KEY (ID),
  44         UNIQUE (Username),
  45         UNIQUE (Email),
  46         INDEX (AccountTypeID),
  47         FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION
  48 ) ENGINE = InnoDB;
  49 -- A default developer account for testing purposes
  50 INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES (
  51         1, 3, 'dev', 'dev@localhost', MD5('dev'));
  52 INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES (
  53         2, 2, 'tu', 'tu@localhost', MD5('tu'));
  54 INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES (
  55         3, 1, 'user', 'user@localhost', MD5('user'));
  56
  57
  58 -- SSH public keys used for the aurweb SSH/Git interface.
  59 --
  60 CREATE TABLE SSHPubKeys (
  61         UserID INTEGER UNSIGNED NOT NULL,
  62         Fingerprint VARCHAR(44) NOT NULL,
  63         PubKey VARCHAR(4096) NOT NULL,
  64         PRIMARY KEY (Fingerprint),
  65         FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
  66 ) ENGINE = InnoDB;
  67
  68
  69 -- Track Users logging in/out of AUR web site.
  70 --
  71 CREATE TABLE Sessions (
  72         UsersID INTEGER UNSIGNED NOT NULL,
  73         SessionID CHAR(32) NOT NULL,
  74         LastUpdateTS BIGINT UNSIGNED NOT NULL,
  75         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
  76         UNIQUE (SessionID)
  77 ) ENGINE = InnoDB;
  78
  79
  80 -- Information on package bases
  81 --
  82 CREATE TABLE PackageBases (
  83         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  84         Name VARCHAR(255) NOT NULL,
  85         NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
  86         Popularity DECIMAL(10,6) UNSIGNED NOT NULL DEFAULT 0,
  87         OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
  88         FlaggerComment TEXT NOT NULL DEFAULT '',
  89         SubmittedTS BIGINT UNSIGNED NOT NULL,
  90         ModifiedTS BIGINT UNSIGNED NOT NULL,
  91         FlaggerUID INTEGER UNSIGNED NULL DEFAULT NULL,       -- who flagged the package out-of-date?
  92         SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL,     -- who submitted it?
  93         MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL,    -- User
  94         PackagerUID INTEGER UNSIGNED NULL DEFAULT NULL,      -- Last packager
  95         PRIMARY KEY (ID),
  96         UNIQUE (Name),
  97         INDEX (NumVotes),
  98         INDEX (SubmitterUID),
  99         INDEX (MaintainerUID),
 100         INDEX (PackagerUID),
 101         FOREIGN KEY (FlaggerUID) REFERENCES Users(ID) ON DELETE SET NULL,
 102         -- deleting a user will cause packages to be orphaned, not deleted
 103         FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL,
 104         FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL,
 105         FOREIGN KEY (PackagerUID) REFERENCES Users(ID) ON DELETE SET NULL
 106 ) ENGINE = InnoDB;
 107
 108
 109 -- Keywords of package bases
 110 --
 111 CREATE TABLE PackageKeywords (
 112         PackageBaseID INTEGER UNSIGNED NOT NULL,
 113         Keyword VARCHAR(255) NOT NULL DEFAULT '',
 114         PRIMARY KEY (PackageBaseID, Keyword),
 115         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 116 ) ENGINE = InnoDB;
 117
 118
 119 -- Information about the actual packages
 120 --
 121 CREATE TABLE Packages (
 122         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 123         PackageBaseID INTEGER UNSIGNED NOT NULL,
 124         Name VARCHAR(255) NOT NULL,
 125         Version VARCHAR(255) NOT NULL DEFAULT '',
 126         Description VARCHAR(255) NULL DEFAULT NULL,
 127         URL VARCHAR(255) NULL DEFAULT NULL,
 128         PRIMARY KEY (ID),
 129         UNIQUE (Name),
 130         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 131 ) ENGINE = InnoDB;
 132
 133
 134 -- Information about licenses
 135 --
 136 CREATE TABLE Licenses (
 137         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 138         Name VARCHAR(255) NOT NULL,
 139         PRIMARY KEY (ID),
 140         UNIQUE (Name)
 141 ) ENGINE = InnoDB;
 142
 143
 144 -- Information about package-license-relations
 145 --
 146 CREATE TABLE PackageLicenses (
 147         PackageID INTEGER UNSIGNED NOT NULL,
 148         LicenseID INTEGER UNSIGNED NOT NULL,
 149         PRIMARY KEY (PackageID, LicenseID),
 150         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 151         FOREIGN KEY (LicenseID) REFERENCES Licenses(ID) ON DELETE CASCADE
 152 ) ENGINE = InnoDB;
 153
 154
 155 -- Information about groups
 156 --
 157 CREATE TABLE Groups (
 158         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 159         Name VARCHAR(255) NOT NULL,
 160         PRIMARY KEY (ID),
 161         UNIQUE (Name)
 162 ) ENGINE = InnoDB;
 163
 164
 165 -- Information about package-group-relations
 166 --
 167 CREATE TABLE PackageGroups (
 168         PackageID INTEGER UNSIGNED NOT NULL,
 169         GroupID INTEGER UNSIGNED NOT NULL,
 170         PRIMARY KEY (PackageID, GroupID),
 171         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 172         FOREIGN KEY (GroupID) REFERENCES Groups(ID) ON DELETE CASCADE
 173 ) ENGINE = InnoDB;
 174
 175
 176 -- Define the package dependency types
 177 --
 178 CREATE TABLE DependencyTypes (
 179         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 180         Name VARCHAR(32) NOT NULL DEFAULT '',
 181         PRIMARY KEY (ID)
 182 ) ENGINE = InnoDB;
 183 INSERT INTO DependencyTypes VALUES (1, 'depends');
 184 INSERT INTO DependencyTypes VALUES (2, 'makedepends');
 185 INSERT INTO DependencyTypes VALUES (3, 'checkdepends');
 186 INSERT INTO DependencyTypes VALUES (4, 'optdepends');
 187
 188
 189 -- Track which dependencies a package has
 190 --
 191 CREATE TABLE PackageDepends (
 192         PackageID INTEGER UNSIGNED NOT NULL,
 193         DepTypeID TINYINT UNSIGNED NOT NULL,
 194         DepName VARCHAR(255) NOT NULL,
 195         DepCondition VARCHAR(255),
 196         DepArch VARCHAR(255) NULL DEFAULT NULL,
 197         INDEX (PackageID),
 198         INDEX (DepName),
 199         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 200         FOREIGN KEY (DepTypeID) REFERENCES DependencyTypes(ID) ON DELETE NO ACTION
 201 ) ENGINE = InnoDB;
 202
 203
 204 -- Define the package relation types
 205 --
 206 CREATE TABLE RelationTypes (
 207         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 208         Name VARCHAR(32) NOT NULL DEFAULT '',
 209         PRIMARY KEY (ID)
 210 ) ENGINE = InnoDB;
 211 INSERT INTO RelationTypes VALUES (1, 'conflicts');
 212 INSERT INTO RelationTypes VALUES (2, 'provides');
 213 INSERT INTO RelationTypes VALUES (3, 'replaces');
 214
 215
 216 -- Track which conflicts, provides and replaces a package has
 217 --
 218 CREATE TABLE PackageRelations (
 219         PackageID INTEGER UNSIGNED NOT NULL,
 220         RelTypeID TINYINT UNSIGNED NOT NULL,
 221         RelName VARCHAR(255) NOT NULL,
 222         RelCondition VARCHAR(255),
 223         RelArch VARCHAR(255) NULL DEFAULT NULL,
 224         INDEX (PackageID),
 225         INDEX (RelName),
 226         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 227         FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
 228 ) ENGINE = InnoDB;
 229
 230
 231 -- Track which sources a package has
 232 --
 233 CREATE TABLE PackageSources (
 234         PackageID INTEGER UNSIGNED NOT NULL,
 235         Source VARCHAR(255) NOT NULL DEFAULT "/dev/null",
 236         SourceArch VARCHAR(255) NULL DEFAULT NULL,
 237         INDEX (PackageID),
 238         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE
 239 ) ENGINE = InnoDB;
 240
 241
 242 -- Track votes for packages
 243 --
 244 CREATE TABLE PackageVotes (
 245         UsersID INTEGER UNSIGNED NOT NULL,
 246         PackageBaseID INTEGER UNSIGNED NOT NULL,
 247         VoteTS BIGINT UNSIGNED NULL DEFAULT NULL,
 248         INDEX (UsersID),
 249         INDEX (PackageBaseID),
 250         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
 251         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 252 ) ENGINE = InnoDB;
 253 CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
 254
 255 -- Record comments for packages
 256 --
 257 CREATE TABLE PackageComments (
 258         ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 259         PackageBaseID INTEGER UNSIGNED NOT NULL,
 260         UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 261         Comments TEXT NOT NULL DEFAULT '',
 262         CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 263         EditedTS BIGINT UNSIGNED NULL DEFAULT NULL,
 264         EditedUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 265         DelTS BIGINT UNSIGNED NULL DEFAULT NULL,
 266         DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 267         PinnedTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 268         PRIMARY KEY (ID),
 269         INDEX (UsersID),
 270         INDEX (PackageBaseID),
 271         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
 272         FOREIGN KEY (EditedUsersID) REFERENCES Users(ID) ON DELETE SET NULL,
 273         FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
 274         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 275 ) ENGINE = InnoDB;
 276
 277 -- Package base co-maintainers
 278 --
 279 CREATE TABLE PackageComaintainers (
 280         UsersID INTEGER UNSIGNED NOT NULL,
 281         PackageBaseID INTEGER UNSIGNED NOT NULL,
 282         Priority INTEGER UNSIGNED NOT NULL,
 283         INDEX (UsersID),
 284         INDEX (PackageBaseID),
 285         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
 286         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 287 ) ENGINE = InnoDB;
 288
 289 -- Package base notifications
 290 --
 291 CREATE TABLE PackageNotifications (
 292         PackageBaseID INTEGER UNSIGNED NOT NULL,
 293         UserID INTEGER UNSIGNED NOT NULL,
 294         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
 295         FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 296 ) ENGINE = InnoDB;
 297 CREATE UNIQUE INDEX NotifyUserIDPkgID ON PackageNotifications (UserID, PackageBaseID);
 298
 299 -- Package name blacklist
 300 --
 301 CREATE TABLE PackageBlacklist (
 302         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 303         Name VARCHAR(64) NOT NULL,
 304         PRIMARY KEY (ID),
 305         UNIQUE (Name)
 306 ) ENGINE = InnoDB;
 307
 308 -- Providers in the official repositories
 309 --
 310 CREATE TABLE OfficialProviders (
 311         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 312         Name VARCHAR(64) NOT NULL,
 313         Provides VARCHAR(64) NOT NULL,
 314         PRIMARY KEY (ID)
 315 ) ENGINE = InnoDB;
 316 CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides);
 317
 318 -- Define package request types
 319 --
 320 CREATE TABLE RequestTypes (
 321         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 322         Name VARCHAR(32) NOT NULL DEFAULT '',
 323         PRIMARY KEY (ID)
 324 ) ENGINE = InnoDB;
 325 INSERT INTO RequestTypes VALUES (1, 'deletion');
 326 INSERT INTO RequestTypes VALUES (2, 'orphan');
 327 INSERT INTO RequestTypes VALUES (3, 'merge');
 328
 329 -- Package requests
 330 --
 331 CREATE TABLE PackageRequests (
 332         ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 333         ReqTypeID TINYINT UNSIGNED NOT NULL,
 334         PackageBaseID INTEGER UNSIGNED NULL,
 335         PackageBaseName VARCHAR(255) NOT NULL,
 336         MergeBaseName VARCHAR(255) NULL,
 337         UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 338         Comments TEXT NOT NULL DEFAULT '',
 339         ClosureComment TEXT NOT NULL DEFAULT '',
 340         RequestTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 341         Status TINYINT UNSIGNED NOT NULL DEFAULT 0,
 342         PRIMARY KEY (ID),
 343         INDEX (UsersID),
 344         INDEX (PackageBaseID),
 345         FOREIGN KEY (ReqTypeID) REFERENCES RequestTypes(ID) ON DELETE NO ACTION,
 346         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
 347         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE SET NULL
 348 ) ENGINE = InnoDB;
 349
 350 -- Vote information
 351 --
 352 CREATE TABLE IF NOT EXISTS TU_VoteInfo (
 353         ID int(10) unsigned NOT NULL auto_increment,
 354         Agenda text NOT NULL,
 355         User VARCHAR(32) NOT NULL,
 356         Submitted bigint(20) unsigned NOT NULL,
 357         End bigint(20) unsigned NOT NULL,
 358         Quorum decimal(2, 2) unsigned NOT NULL,
 359         SubmitterID int(10) unsigned NOT NULL,
 360         Yes tinyint(3) unsigned NOT NULL default '0',
 361         No tinyint(3) unsigned NOT NULL default '0',
 362         Abstain tinyint(3) unsigned NOT NULL default '0',
 363         ActiveTUs tinyint(3) unsigned NOT NULL default '0',
 364         PRIMARY KEY  (ID),
 365         FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE
 366 ) ENGINE = InnoDB;
 367
 368 -- Individual vote records
 369 --
 370 CREATE TABLE IF NOT EXISTS TU_Votes (
 371         VoteID int(10) unsigned NOT NULL,
 372         UserID int(10) unsigned NOT NULL,
 373         FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
 374         FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 375 ) ENGINE = InnoDB;
 376
 377 -- Malicious user banning
 378 --
 379 CREATE TABLE Bans (
 380         IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0,
 381         BanTS TIMESTAMP NOT NULL,
 382         PRIMARY KEY (IPAddress)
 383 ) ENGINE = InnoDB;