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