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 --- 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