[1/4] Add sqlite3 schema for testing databases

diff --git a/schema/aur-schema-sqlite.sql b/schema/aur-schema-sqlite.sql
new file mode 100644
index 0000000..5d655e9
--- /dev/null
+++ b/schema/aur-schema-sqlite.sql
@@ -0,0 +1,368 @@ 
+-- The SQLite database layout for the AUR.  Certain data
+-- is also included such as AccountTypes, etc.
+--
+
+-- Define the Account Types for the AUR.
+--
+CREATE TABLE AccountTypes (
+  ID TINYINT UNSIGNED NOT NULL,
+  AccountType VARCHAR(32) NOT NULL DEFAULT '',
+  PRIMARY KEY (ID)
+);
+INSERT INTO AccountTypes (ID, AccountType) VALUES (1, 'User');
+INSERT INTO AccountTypes (ID, AccountType) VALUES (2, 'Trusted User');
+INSERT INTO AccountTypes (ID, AccountType) VALUES (3, 'Developer');
+INSERT INTO AccountTypes (ID, AccountType) VALUES (4, 'Trusted User & Developer');
+
+
+-- User information for each user regardless of type.
+--
+CREATE TABLE Users (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1,
+  Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0,
+  Username VARCHAR(32) NOT NULL,
+  Email VARCHAR(254) NOT NULL,
+  HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0,
+  Passwd CHAR(32) NOT NULL,
+  Salt CHAR(32) NOT NULL DEFAULT '',
+  ResetKey CHAR(32) NOT NULL DEFAULT '',
+  RealName VARCHAR(64) NOT NULL DEFAULT '',
+  LangPreference VARCHAR(6) NOT NULL DEFAULT 'en',
+  Timezone VARCHAR(32) NOT NULL DEFAULT 'UTC',
+  Homepage TEXT NULL DEFAULT NULL,
+  IRCNick VARCHAR(32) NOT NULL DEFAULT '',
+  PGPKey VARCHAR(40) NULL DEFAULT NULL,
+  LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0,
+  LastLoginIPAddress VARCHAR(45) NULL DEFAULT NULL,
+  LastSSHLogin BIGINT UNSIGNED NOT NULL DEFAULT 0,
+  LastSSHLoginIPAddress VARCHAR(45) NULL DEFAULT NULL,
+  InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
+  RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  CommentNotify TINYINT(1) NOT NULL DEFAULT 1,
+  UpdateNotify TINYINT(1) NOT NULL DEFAULT 0,
+  OwnershipNotify TINYINT(1) NOT NULL DEFAULT 1,
+  UNIQUE (Username),
+  UNIQUE (Email),
+  FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION
+);
+CREATE INDEX UsersAccountTypeID ON Users (AccountTypeID);
+
+
+-- SSH public keys used for the aurweb SSH/Git interface.
+--
+CREATE TABLE SSHPubKeys (
+  UserID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Fingerprint VARCHAR(44) NOT NULL,
+  PubKey VARCHAR(4096) NOT NULL,
+  FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
+);
+
+
+-- Track Users logging in/out of AUR web site.
+--
+CREATE TABLE Sessions (
+  UsersID INTEGER NOT NULL,
+  SessionID CHAR(32) NOT NULL,
+  LastUpdateTS BIGINT UNSIGNED NOT NULL,
+  FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+  UNIQUE (SessionID)
+);
+
+
+-- Information on package bases
+--
+CREATE TABLE PackageBases (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Name VARCHAR(255) NOT NULL,
+  NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
+  Popularity DECIMAL(10,6) NOT NULL DEFAULT 0,
+  OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
+  FlaggerComment TEXT NOT NULL DEFAULT '',
+  SubmittedTS BIGINT UNSIGNED NOT NULL,
+  ModifiedTS BIGINT UNSIGNED NOT NULL,
+  FlaggerUID INTEGER UNSIGNED NULL DEFAULT NULL,       -- who flagged the package out-of-date?
+  SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL,     -- who submitted it?
+  MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL,    -- User
+  PackagerUID INTEGER UNSIGNED NULL DEFAULT NULL,      -- Last packager
+  UNIQUE (Name),
+  FOREIGN KEY (FlaggerUID) REFERENCES Users(ID) ON DELETE SET NULL,
+  -- deleting a user will cause packages to be orphaned, not deleted
+  FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL,
+  FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL,
+  FOREIGN KEY (PackagerUID) REFERENCES Users(ID) ON DELETE SET NULL
+);
+CREATE INDEX BasesNumVotes ON PackageBases (NumVotes);
+CREATE INDEX BasesSubmitterUID ON PackageBases (SubmitterUID);
+CREATE INDEX BasesMaintainerUID ON PackageBases (MaintainerUID);
+CREATE INDEX BasesPackagerUID ON PackageBases (PackagerUID);
+
+
+-- Keywords of package bases
+--
+CREATE TABLE PackageKeywords (
+  PackageBaseID INTEGER UNSIGNED NOT NULL,
+  Keyword VARCHAR(255) NOT NULL DEFAULT '',
+  PRIMARY KEY (PackageBaseID, Keyword),
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+);
+
+
+-- Information about the actual packages
+--
+CREATE TABLE Packages (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  PackageBaseID INTEGER UNSIGNED NOT NULL,
+  Name VARCHAR(255) NOT NULL,
+  Version VARCHAR(255) NOT NULL DEFAULT '',
+  Description VARCHAR(255) NULL DEFAULT NULL,
+  URL VARCHAR(8000) NULL DEFAULT NULL,
+  UNIQUE (Name),
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+);
+
+
+-- Information about licenses
+--
+CREATE TABLE Licenses (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Name VARCHAR(255) NOT NULL,
+  UNIQUE (Name)
+);
+
+
+-- Information about package-license-relations
+--
+CREATE TABLE PackageLicenses (
+  PackageID INTEGER UNSIGNED NOT NULL,
+  LicenseID INTEGER UNSIGNED NOT NULL,
+  PRIMARY KEY (PackageID, LicenseID),
+  FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+  FOREIGN KEY (LicenseID) REFERENCES Licenses(ID) ON DELETE CASCADE
+);
+
+
+-- Information about groups
+--
+CREATE TABLE Groups (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Name VARCHAR(255) NOT NULL,
+  UNIQUE (Name)
+);
+
+
+-- Information about package-group-relations
+--
+CREATE TABLE PackageGroups (
+  PackageID INTEGER UNSIGNED NOT NULL,
+  GroupID INTEGER UNSIGNED NOT NULL,
+  PRIMARY KEY (PackageID, GroupID),
+  FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+  FOREIGN KEY (GroupID) REFERENCES Groups(ID) ON DELETE CASCADE
+);
+
+
+-- Define the package dependency types
+--
+CREATE TABLE DependencyTypes (
+  ID TINYINT UNSIGNED NOT NULL,
+  Name VARCHAR(32) NOT NULL DEFAULT '',
+  PRIMARY KEY (ID)
+);
+INSERT INTO DependencyTypes VALUES (1, 'depends');
+INSERT INTO DependencyTypes VALUES (2, 'makedepends');
+INSERT INTO DependencyTypes VALUES (3, 'checkdepends');
+INSERT INTO DependencyTypes VALUES (4, 'optdepends');
+
+
+-- Track which dependencies a package has
+--
+CREATE TABLE PackageDepends (
+  PackageID INTEGER UNSIGNED NOT NULL,
+  DepTypeID TINYINT UNSIGNED NOT NULL,
+  DepName VARCHAR(255) NOT NULL,
+  DepCondition VARCHAR(255),
+  DepArch VARCHAR(255) NULL DEFAULT NULL,
+  FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+  FOREIGN KEY (DepTypeID) REFERENCES DependencyTypes(ID) ON DELETE NO ACTION
+);
+CREATE INDEX DependsPackageID ON PackageDepends (PackageID);
+CREATE INDEX DependsDepName ON PackageDepends (DepName);
+
+
+-- Define the package relation types
+--
+CREATE TABLE RelationTypes (
+  ID TINYINT UNSIGNED NOT NULL,
+  Name VARCHAR(32) NOT NULL DEFAULT '',
+  PRIMARY KEY (ID)
+);
+INSERT INTO RelationTypes VALUES (1, 'conflicts');
+INSERT INTO RelationTypes VALUES (2, 'provides');
+INSERT INTO RelationTypes VALUES (3, 'replaces');
+
+
+-- Track which conflicts, provides and replaces a package has
+--
+CREATE TABLE PackageRelations (
+  PackageID INTEGER UNSIGNED NOT NULL,
+  RelTypeID TINYINT UNSIGNED NOT NULL,
+  RelName VARCHAR(255) NOT NULL,
+  RelCondition VARCHAR(255),
+  RelArch VARCHAR(255) NULL DEFAULT NULL,
+  FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+  FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
+);
+CREATE INDEX RelationsPackageID ON PackageRelations (PackageID);
+CREATE INDEX RelationsRelName ON PackageRelations (RelName);
+
+
+-- Track which sources a package has
+--
+CREATE TABLE PackageSources (
+  PackageID INTEGER UNSIGNED NOT NULL,
+  Source VARCHAR(8000) NOT NULL DEFAULT '/dev/null',
+  SourceArch VARCHAR(255) NULL DEFAULT NULL,
+  FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE
+);
+CREATE INDEX SourcesPackageID ON PackageSources (PackageID);
+
+
+-- Track votes for packages
+--
+CREATE TABLE PackageVotes (
+  UsersID INTEGER UNSIGNED NOT NULL,
+  PackageBaseID INTEGER UNSIGNED NOT NULL,
+  VoteTS BIGINT UNSIGNED NULL DEFAULT NULL,
+  FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+);
+CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
+CREATE INDEX VotesUsersID ON PackageVotes (UsersID);
+CREATE INDEX VotesPackageBaseID ON PackageVotes (PackageBaseID);
+
+-- Record comments for packages
+--
+CREATE TABLE PackageComments (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  PackageBaseID INTEGER UNSIGNED NOT NULL,
+  UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+  Comments TEXT NOT NULL DEFAULT '',
+  CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
+  EditedTS BIGINT UNSIGNED NULL DEFAULT NULL,
+  EditedUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+  DelTS BIGINT UNSIGNED NULL DEFAULT NULL,
+  DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+  PinnedTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
+  FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
+  FOREIGN KEY (EditedUsersID) REFERENCES Users(ID) ON DELETE SET NULL,
+  FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+);
+CREATE INDEX CommentsUsersID ON PackageComments (UsersID);
+CREATE INDEX CommentsPackageBaseID ON PackageComments (PackageBaseID);
+
+-- Package base co-maintainers
+--
+CREATE TABLE PackageComaintainers (
+  UsersID INTEGER UNSIGNED NOT NULL,
+  PackageBaseID INTEGER UNSIGNED NOT NULL,
+  Priority INTEGER UNSIGNED NOT NULL,
+  FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+);
+CREATE INDEX ComaintainersUsersID ON PackageComaintainers (UsersID);
+CREATE INDEX ComaintainersPackageBaseID ON PackageComaintainers (PackageBaseID);
+
+-- Package base notifications
+--
+CREATE TABLE PackageNotifications (
+  PackageBaseID INTEGER UNSIGNED NOT NULL,
+  UserID INTEGER UNSIGNED NOT NULL,
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
+  FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
+);
+CREATE UNIQUE INDEX NotifyUserIDPkgID ON PackageNotifications (UserID, PackageBaseID);
+
+-- Package name blacklist
+--
+CREATE TABLE PackageBlacklist (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Name VARCHAR(64) NOT NULL,
+  UNIQUE (Name)
+);
+
+-- Providers in the official repositories
+--
+CREATE TABLE OfficialProviders (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Name VARCHAR(64) NOT NULL,
+  Repo VARCHAR(64) NOT NULL,
+  Provides VARCHAR(64) NOT NULL
+);
+CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides);
+
+-- Define package request types
+--
+CREATE TABLE RequestTypes (
+  ID TINYINT UNSIGNED NOT NULL,
+  Name VARCHAR(32) NOT NULL DEFAULT '',
+  PRIMARY KEY (ID)
+);
+INSERT INTO RequestTypes VALUES (1, 'deletion');
+INSERT INTO RequestTypes VALUES (2, 'orphan');
+INSERT INTO RequestTypes VALUES (3, 'merge');
+
+-- Package requests
+--
+CREATE TABLE PackageRequests (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  ReqTypeID TINYINT UNSIGNED NOT NULL,
+  PackageBaseID INTEGER UNSIGNED NULL,
+  PackageBaseName VARCHAR(255) NOT NULL,
+  MergeBaseName VARCHAR(255) NULL,
+  UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+  Comments TEXT NOT NULL DEFAULT '',
+  ClosureComment TEXT NOT NULL DEFAULT '',
+  RequestTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
+  Status TINYINT UNSIGNED NOT NULL DEFAULT 0,
+  FOREIGN KEY (ReqTypeID) REFERENCES RequestTypes(ID) ON DELETE NO ACTION,
+  FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
+  FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE SET NULL
+);
+CREATE INDEX RequestsUsersID ON PackageRequests (UsersID);
+CREATE INDEX RequestsPackageBaseID ON PackageRequests (PackageBaseID);
+
+-- Vote information
+--
+CREATE TABLE IF NOT EXISTS TU_VoteInfo (
+  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  Agenda TEXT NOT NULL,
+  User VARCHAR(32) NOT NULL,
+  Submitted BIGINT UNSIGNED NOT NULL,
+  End BIGINT UNSIGNED NOT NULL,
+  Quorum DECIMAL(2, 2) NOT NULL,
+  SubmitterID INTEGER UNSIGNED NOT NULL,
+  Yes TINYINT UNSIGNED NOT NULL DEFAULT '0',
+  No TINYINT UNSIGNED NOT NULL DEFAULT '0',
+  Abstain TINYINT UNSIGNED NOT NULL DEFAULT '0',
+  ActiveTUs TINYINT UNSIGNED NOT NULL DEFAULT '0',
+  FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE
+);
+
+-- Individual vote records
+--
+CREATE TABLE IF NOT EXISTS TU_Votes (
+  VoteID INTEGER UNSIGNED NOT NULL,
+  UserID INTEGER UNSIGNED NOT NULL,
+  FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
+  FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
+);
+
+-- Malicious user banning
+--
+CREATE TABLE Bans (
+  IPAddress VARCHAR(45) NULL DEFAULT NULL,
+  BanTS TIMESTAMP NOT NULL,
+  PRIMARY KEY (IPAddress)
+);