diff options
Diffstat (limited to 'schema')
| -rw-r--r-- | schema/Makefile | 12 | ||||
| -rw-r--r-- | schema/aur-schema.sql | 415 | ||||
| -rwxr-xr-x | schema/reloadtestdb.sh | 29 |
3 files changed, 0 insertions, 456 deletions
diff --git a/schema/Makefile b/schema/Makefile deleted file mode 100644 index 62d08567..00000000 --- a/schema/Makefile +++ /dev/null @@ -1,12 +0,0 @@ -aur-schema-sqlite.sql: aur-schema.sql - sed \ - -e 's/ ENGINE = InnoDB//' \ - -e 's/ [A-Z]* UNSIGNED NOT NULL AUTO_INCREMENT/ INTEGER NOT NULL/' \ - -e 's/([0-9, ]*) UNSIGNED / UNSIGNED /' \ - -e 's/ MySQL / SQLite /' \ - $< >$@ - -clean: - rm -rf aur-schema-sqlite.sql - -.PHONY: clean diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql deleted file mode 100644 index 1f86df20..00000000 --- a/schema/aur-schema.sql +++ /dev/null @@ -1,415 +0,0 @@ --- The MySQL 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 AUTO_INCREMENT, - AccountType VARCHAR(32) NOT NULL DEFAULT '', - PRIMARY KEY (ID) -) ENGINE = InnoDB; -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 UNSIGNED NOT NULL AUTO_INCREMENT, - AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1, - Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0, - Username VARCHAR(32) NOT NULL, - Email VARCHAR(254) NOT NULL, - BackupEmail VARCHAR(254) NULL DEFAULT NULL, - HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0, - Passwd VARCHAR(255) 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, - PRIMARY KEY (ID), - UNIQUE (Username), - UNIQUE (Email), - FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION -) ENGINE = InnoDB; -CREATE INDEX UsersAccountTypeID ON Users (AccountTypeID); - - --- SSH public keys used for the aurweb SSH/Git interface. --- -CREATE TABLE SSHPubKeys ( - UserID INTEGER UNSIGNED NOT NULL, - Fingerprint VARCHAR(44) NOT NULL, - PubKey VARCHAR(4096) NOT NULL, - PRIMARY KEY (Fingerprint), - FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - - --- Track Users logging in/out of AUR web site. --- -CREATE TABLE Sessions ( - UsersID INTEGER UNSIGNED NOT NULL, - SessionID CHAR(32) NOT NULL, - LastUpdateTS BIGINT UNSIGNED NOT NULL, - FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, - UNIQUE (SessionID) -) ENGINE = InnoDB; - - --- Information on package bases --- -CREATE TABLE PackageBases ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(255) NOT NULL, - NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0, - Popularity DECIMAL(10,6) UNSIGNED NOT NULL DEFAULT 0, - OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL, - FlaggerComment TEXT NOT NULL, - 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 - PRIMARY KEY (ID), - 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 -) ENGINE = InnoDB; -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 -) ENGINE = InnoDB; - - --- Information about the actual packages --- -CREATE TABLE Packages ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - 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, - PRIMARY KEY (ID), - UNIQUE (Name), - FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - - --- Information about licenses --- -CREATE TABLE Licenses ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(255) NOT NULL, - PRIMARY KEY (ID), - UNIQUE (Name) -) ENGINE = InnoDB; - - --- 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 -) ENGINE = InnoDB; - - --- Information about groups --- -CREATE TABLE `Groups` ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(255) NOT NULL, - PRIMARY KEY (ID), - UNIQUE (Name) -) ENGINE = InnoDB; - - --- 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 -) ENGINE = InnoDB; - - --- Define the package dependency types --- -CREATE TABLE DependencyTypes ( - ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(32) NOT NULL DEFAULT '', - PRIMARY KEY (ID) -) ENGINE = InnoDB; -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, - DepDesc VARCHAR(255) NULL DEFAULT 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 -) ENGINE = InnoDB; -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 AUTO_INCREMENT, - Name VARCHAR(32) NOT NULL DEFAULT '', - PRIMARY KEY (ID) -) ENGINE = InnoDB; -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 -) ENGINE = InnoDB; -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 -) ENGINE = InnoDB; -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 -) ENGINE = InnoDB; -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 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, - PackageBaseID INTEGER UNSIGNED NOT NULL, - UsersID INTEGER UNSIGNED NULL DEFAULT NULL, - Comments TEXT NOT NULL, - RenderedComment TEXT NOT NULL, - 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, - PRIMARY KEY (ID), - 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 -) ENGINE = InnoDB; -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 -) ENGINE = InnoDB; -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 -) ENGINE = InnoDB; -CREATE UNIQUE INDEX NotifyUserIDPkgID ON PackageNotifications (UserID, PackageBaseID); - --- Package name blacklist --- -CREATE TABLE PackageBlacklist ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(64) NOT NULL, - PRIMARY KEY (ID), - UNIQUE (Name) -) ENGINE = InnoDB; - --- Providers in the official repositories --- -CREATE TABLE OfficialProviders ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(64) NOT NULL, - Repo VARCHAR(64) NOT NULL, - Provides VARCHAR(64) NOT NULL, - PRIMARY KEY (ID) -) ENGINE = InnoDB; -CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides); - --- Define package request types --- -CREATE TABLE RequestTypes ( - ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(32) NOT NULL DEFAULT '', - PRIMARY KEY (ID) -) ENGINE = InnoDB; -INSERT INTO RequestTypes VALUES (1, 'deletion'); -INSERT INTO RequestTypes VALUES (2, 'orphan'); -INSERT INTO RequestTypes VALUES (3, 'merge'); - --- Package requests --- -CREATE TABLE PackageRequests ( - ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, - 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, - ClosureComment TEXT NOT NULL, - RequestTS BIGINT UNSIGNED NOT NULL DEFAULT 0, - ClosedTS BIGINT UNSIGNED NULL DEFAULT NULL, - ClosedUID INTEGER UNSIGNED NULL DEFAULT NULL, - Status TINYINT UNSIGNED NOT NULL DEFAULT 0, - PRIMARY KEY (ID), - 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, - FOREIGN KEY (ClosedUID) REFERENCES Users(ID) ON DELETE SET NULL -) ENGINE = InnoDB; -CREATE INDEX RequestsUsersID ON PackageRequests (UsersID); -CREATE INDEX RequestsPackageBaseID ON PackageRequests (PackageBaseID); - --- Vote information --- -CREATE TABLE IF NOT EXISTS TU_VoteInfo ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Agenda TEXT NOT NULL, - User VARCHAR(32) NOT NULL, - Submitted BIGINT UNSIGNED NOT NULL, - End BIGINT UNSIGNED NOT NULL, - Quorum DECIMAL(2, 2) UNSIGNED NOT NULL, - SubmitterID INTEGER UNSIGNED NOT NULL, - Yes TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', - No TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', - Abstain TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', - ActiveTUs TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY (ID), - FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - --- 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 -) ENGINE = InnoDB; - --- Malicious user banning --- -CREATE TABLE Bans ( - IPAddress VARCHAR(45) NOT NULL, - BanTS TIMESTAMP NOT NULL, - PRIMARY KEY (IPAddress) -) ENGINE = InnoDB; - --- Terms and Conditions --- -CREATE TABLE Terms ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Description VARCHAR(255) NOT NULL, - URL VARCHAR(8000) NOT NULL, - Revision INTEGER UNSIGNED NOT NULL DEFAULT 1, - PRIMARY KEY (ID) -) ENGINE = InnoDB; - --- Terms and Conditions accepted by users --- -CREATE TABLE AcceptedTerms ( - UsersID INTEGER UNSIGNED NOT NULL, - TermsID INTEGER UNSIGNED NOT NULL, - Revision INTEGER UNSIGNED NOT NULL DEFAULT 0, - FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, - FOREIGN KEY (TermsID) REFERENCES Terms(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - --- Rate limits for API --- -CREATE TABLE `ApiRateLimit` ( - IP VARCHAR(45) NOT NULL, - Requests INT(11) NOT NULL, - WindowStart BIGINT(20) NOT NULL, - PRIMARY KEY (`ip`) -) ENGINE = InnoDB; -CREATE INDEX ApiRateLimitWindowStart ON ApiRateLimit (WindowStart); diff --git a/schema/reloadtestdb.sh b/schema/reloadtestdb.sh deleted file mode 100755 index e839dcec..00000000 --- a/schema/reloadtestdb.sh +++ /dev/null @@ -1,29 +0,0 @@ -#!/bin/bash -e - -DB_NAME=${DB_NAME:-AUR} -DB_USER=${DB_USER:-aur} -# Password should allow empty definition -DB_PASS=${DB_PASS-aur} -DB_HOST=${DB_HOST:-localhost} -DATA_FILE=${DATA_FILE:-dummy-data.sql} - -echo "Using database $DB_NAME, user $DB_USER, host $DB_HOST" - -mydir=$(pwd) -if [ $(basename $mydir) != "schema" ]; then - echo "you must be in the aurweb/schema directory to run this script" - exit 1 -fi - -echo "recreating database..." -mysql -h $DB_HOST -u $DB_USER -p$DB_PASS < aur-schema.sql - -if [ ! -f $DATA_FILE ]; then - echo "creating dumy-data..." - python3 gendummydata.py $DATA_FILE -fi - -echo "loading dummy-data..." -mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME < $DATA_FILE - -echo "done." |