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
  19
  20 -- User information for each user regardless of type.
  21 --
  22 CREATE TABLE Users (
  23         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  24         AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1,
  25         Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0,
  26         Username VARCHAR(32) NOT NULL,
  27         Email VARCHAR(64) NOT NULL,
  28         Passwd CHAR(32) NOT NULL,
  29         Salt CHAR(32) NOT NULL DEFAULT '',
  30         ResetKey CHAR(32) NOT NULL DEFAULT '',
  31         RealName VARCHAR(64) NOT NULL DEFAULT '',
  32         LangPreference VARCHAR(5) NOT NULL DEFAULT 'en',
  33         IRCNick VARCHAR(32) NOT NULL DEFAULT '',
  34         PGPKey VARCHAR(40) NULL DEFAULT NULL,
  35         LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0,
  36         LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0,
  37         InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
  38         RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  39         PRIMARY KEY (ID),
  40         UNIQUE (Username),
  41         UNIQUE (Email),
  42         INDEX (AccountTypeID),
  43         FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION
  44 ) ENGINE = InnoDB;
  45 -- A default developer account for testing purposes
  46 INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES (
  47         1, 3, 'dev', 'dev@localhost', MD5('dev'));
  48 INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES (
  49         2, 2, 'tu', 'tu@localhost', MD5('tu'));
  50 INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES (
  51         3, 1, 'user', 'user@localhost', MD5('user'));
  52
  53
  54 -- Track Users logging in/out of AUR web site.
  55 --
  56 CREATE TABLE Sessions (
  57         UsersID INTEGER UNSIGNED NOT NULL,
  58         SessionID CHAR(32) NOT NULL,
  59         LastUpdateTS BIGINT UNSIGNED NOT NULL,
  60         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
  61         UNIQUE (SessionID)
  62 ) ENGINE = InnoDB;
  63
  64
  65 -- Categories for grouping packages when they reside in
  66 -- Unsupported or the AUR - based on the categories defined
  67 -- in 'extra'.
  68 --
  69 CREATE TABLE PackageCategories (
  70         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  71         Category VARCHAR(32) NOT NULL,
  72         PRIMARY KEY (ID)
  73 ) ENGINE = InnoDB;
  74 INSERT INTO PackageCategories (Category) VALUES ('none');
  75 INSERT INTO PackageCategories (Category) VALUES ('daemons');
  76 INSERT INTO PackageCategories (Category) VALUES ('devel');
  77 INSERT INTO PackageCategories (Category) VALUES ('editors');
  78 INSERT INTO PackageCategories (Category) VALUES ('emulators');
  79 INSERT INTO PackageCategories (Category) VALUES ('games');
  80 INSERT INTO PackageCategories (Category) VALUES ('gnome');
  81 INSERT INTO PackageCategories (Category) VALUES ('i18n');
  82 INSERT INTO PackageCategories (Category) VALUES ('kde');
  83 INSERT INTO PackageCategories (Category) VALUES ('lib');
  84 INSERT INTO PackageCategories (Category) VALUES ('modules');
  85 INSERT INTO PackageCategories (Category) VALUES ('multimedia');
  86 INSERT INTO PackageCategories (Category) VALUES ('network');
  87 INSERT INTO PackageCategories (Category) VALUES ('office');
  88 INSERT INTO PackageCategories (Category) VALUES ('science');
  89 INSERT INTO PackageCategories (Category) VALUES ('system');
  90 INSERT INTO PackageCategories (Category) VALUES ('x11');
  91 INSERT INTO PackageCategories (Category) VALUES ('xfce');
  92 INSERT INTO PackageCategories (Category) VALUES ('fonts');
  93
  94
  95 -- Information on package bases
  96 --
  97 CREATE TABLE PackageBases (
  98         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  99         Name VARCHAR(64) NOT NULL,
 100         CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1,
 101         NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
 102         OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
 103         SubmittedTS BIGINT UNSIGNED NOT NULL,
 104         ModifiedTS BIGINT UNSIGNED NOT NULL,
 105         SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL,     -- who submitted it?
 106         MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL,    -- User
 107         PRIMARY KEY (ID),
 108         UNIQUE (Name),
 109         INDEX (CategoryID),
 110         INDEX (NumVotes),
 111         INDEX (SubmitterUID),
 112         INDEX (MaintainerUID),
 113         FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION,
 114         -- deleting a user will cause packages to be orphaned, not deleted
 115         FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL,
 116         FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL
 117 ) ENGINE = InnoDB;
 118
 119
 120 -- Information about the actual packages
 121 --
 122 CREATE TABLE Packages (
 123         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 124         PackageBaseID INTEGER UNSIGNED NOT NULL,
 125         Name VARCHAR(64) NOT NULL,
 126         Version VARCHAR(32) NOT NULL DEFAULT '',
 127         Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package",
 128         URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org",
 129         License VARCHAR(40) NOT NULL DEFAULT '',
 130         PRIMARY KEY (ID),
 131         UNIQUE (Name),
 132         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 133 ) ENGINE = InnoDB;
 134
 135
 136 -- Information about groups
 137 --
 138 CREATE TABLE Groups (
 139         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 140         Name VARCHAR(64) NOT NULL,
 141         PRIMARY KEY (ID),
 142         UNIQUE (Name)
 143 ) ENGINE = InnoDB;
 144
 145
 146 -- Information about package-group-relations
 147 --
 148 CREATE TABLE PackageGroups (
 149         PackageID INTEGER UNSIGNED NOT NULL,
 150         GroupID INTEGER UNSIGNED NOT NULL,
 151         PRIMARY KEY (PackageID, GroupID),
 152         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 153         FOREIGN KEY (GroupID) REFERENCES Groups(ID) ON DELETE CASCADE
 154 ) ENGINE = InnoDB;
 155
 156
 157 -- Define the package dependency types
 158 --
 159 CREATE TABLE DependencyTypes (
 160         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 161         Name VARCHAR(32) NOT NULL DEFAULT '',
 162         PRIMARY KEY (ID)
 163 ) ENGINE = InnoDB;
 164 INSERT INTO DependencyTypes VALUES (1, 'depends');
 165 INSERT INTO DependencyTypes VALUES (2, 'makedepends');
 166 INSERT INTO DependencyTypes VALUES (3, 'checkdepends');
 167 INSERT INTO DependencyTypes VALUES (4, 'optdepends');
 168
 169
 170 -- Track which dependencies a package has
 171 --
 172 CREATE TABLE PackageDepends (
 173         PackageID INTEGER UNSIGNED NOT NULL,
 174         DepTypeID TINYINT UNSIGNED NOT NULL,
 175         DepName VARCHAR(255) NOT NULL,
 176         DepCondition VARCHAR(20),
 177         INDEX (PackageID),
 178         INDEX (DepName),
 179         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 180         FOREIGN KEY (DepTypeID) REFERENCES DependencyTypes(ID) ON DELETE NO ACTION
 181 ) ENGINE = InnoDB;
 182
 183
 184 -- Define the package relation types
 185 --
 186 CREATE TABLE RelationTypes (
 187         ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 188         Name VARCHAR(32) NOT NULL DEFAULT '',
 189         PRIMARY KEY (ID)
 190 ) ENGINE = InnoDB;
 191 INSERT INTO RelationTypes VALUES (1, 'conflicts');
 192 INSERT INTO RelationTypes VALUES (2, 'provides');
 193 INSERT INTO RelationTypes VALUES (3, 'replaces');
 194
 195
 196 -- Track which conflicts, provides and replaces a package has
 197 --
 198 CREATE TABLE PackageRelations (
 199         PackageID INTEGER UNSIGNED NOT NULL,
 200         RelTypeID TINYINT UNSIGNED NOT NULL,
 201         RelName VARCHAR(255) NOT NULL,
 202         RelCondition VARCHAR(20),
 203         INDEX (PackageID),
 204         INDEX (RelName),
 205         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
 206         FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
 207 ) ENGINE = InnoDB;
 208
 209
 210 -- Track which sources a package has
 211 --
 212 CREATE TABLE PackageSources (
 213         PackageID INTEGER UNSIGNED NOT NULL,
 214         Source VARCHAR(255) NOT NULL DEFAULT "/dev/null",
 215         INDEX (PackageID),
 216         FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE
 217 ) ENGINE = InnoDB;
 218
 219
 220 -- Track votes for packages
 221 --
 222 CREATE TABLE PackageVotes (
 223         UsersID INTEGER UNSIGNED NOT NULL,
 224         PackageBaseID INTEGER UNSIGNED NOT NULL,
 225         INDEX (UsersID),
 226         INDEX (PackageBaseID),
 227         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
 228         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 229 ) ENGINE = InnoDB;
 230 CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
 231
 232 -- Record comments for packages
 233 --
 234 CREATE TABLE PackageComments (
 235         ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 236         PackageBaseID INTEGER UNSIGNED NOT NULL,
 237         UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 238         Comments TEXT NOT NULL DEFAULT '',
 239         CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 240         DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 241         PRIMARY KEY (ID),
 242         INDEX (UsersID),
 243         INDEX (PackageBaseID),
 244         FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
 245         FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
 246         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 247 ) ENGINE = InnoDB;
 248
 249 -- Comment addition notifications
 250 --
 251 CREATE TABLE CommentNotify (
 252         PackageBaseID INTEGER UNSIGNED NOT NULL,
 253         UserID INTEGER UNSIGNED NOT NULL,
 254         FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
 255         FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 256 ) ENGINE = InnoDB;
 257 CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PackageBaseID);
 258
 259 -- Package name blacklist
 260 --
 261 CREATE TABLE PackageBlacklist (
 262         ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 263         Name VARCHAR(64) NOT NULL,
 264         PRIMARY KEY (ID),
 265         UNIQUE (Name)
 266 ) ENGINE = InnoDB;
 267
 268 -- Vote information
 269 --
 270 CREATE TABLE IF NOT EXISTS TU_VoteInfo (
 271   ID int(10) unsigned NOT NULL auto_increment,
 272   Agenda text NOT NULL,
 273   User VARCHAR(32) NOT NULL,
 274   Submitted bigint(20) unsigned NOT NULL,
 275   End bigint(20) unsigned NOT NULL,
 276   Quorum decimal(2, 2) unsigned NOT NULL,
 277   SubmitterID int(10) unsigned NOT NULL,
 278   Yes tinyint(3) unsigned NOT NULL default '0',
 279   No tinyint(3) unsigned NOT NULL default '0',
 280   Abstain tinyint(3) unsigned NOT NULL default '0',
 281   ActiveTUs tinyint(3) unsigned NOT NULL default '0',
 282   PRIMARY KEY  (ID),
 283   FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE
 284 ) ENGINE = InnoDB;
 285
 286 -- Individual vote records
 287 --
 288 CREATE TABLE IF NOT EXISTS TU_Votes (
 289   VoteID int(10) unsigned NOT NULL,
 290   UserID int(10) unsigned NOT NULL,
 291   FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
 292   FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 293 ) ENGINE = InnoDB;
 294
 295 -- Malicious user banning
 296 --
 297 CREATE TABLE Bans (
 298         IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0,
 299         BanTS TIMESTAMP NOT NULL,
 300         PRIMARY KEY (IPAddress)
 301 ) ENGINE = InnoDB;