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