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