Migrate the database schema to SQLAlchemy

The new schema was generated with sqlacodegen and then manually adjusted
to fit schema/aur-schema.sql faithfully, both in the organisation of the
code and in the SQL generated by SQLAlchemy.

Initializing the database now requires the new tool aurweb.initdb.
References to aur-schema.sql have been updated and the old schema
dropped.

Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>

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