The existing aur-schema.sql file is specific to MySQL, this patch adds a second
schema file for use to create sqlite3 testing databases.
Signed-off-by: Mark Weiman <mark.weiman(a)markzz.com>
---
schema/aur-schema-sqlite.sql | 368 +++++++++++++++++++++++++++++++++++++++++++
1 file changed, 368 insertions(+)
create mode 100644 schema/aur-schema-sqlite.sql
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)
+);
--
2.12.0