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