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. --- INSTALL | 12 +- TESTING | 23 +-- aurweb/db.py | 27 +++ aurweb/initdb.py | 47 +++++ aurweb/schema.py | 387 ++++++++++++++++++++++++++++++++++++++ schema/Makefile | 12 -- schema/aur-schema.sql | 415 ----------------------------------------- schema/reloadtestdb.sh | 29 --- test/Makefile | 6 +- test/setup.sh | 5 +- 10 files changed, 481 insertions(+), 482 deletions(-) create mode 100644 aurweb/initdb.py create mode 100644 aurweb/schema.py delete mode 100644 schema/Makefile delete mode 100644 schema/aur-schema.sql delete mode 100755 schema/reloadtestdb.sh diff --git a/INSTALL b/INSTALL index 7170aea1..68fe5dcd 100644 --- a/INSTALL +++ b/INSTALL @@ -45,16 +45,16 @@ read the instructions below. if the defaults file does not exist) and adjust the configuration (pay attention to disable_http_login, enable_maintenance and aur_location). -4) Create a new MySQL database and a user and import the aurweb SQL schema: +4) Install Python modules and dependencies: - $ mysql -uaur -p AUR </srv/http/aurweb/schema/aur-schema.sql - -5) Install Python modules and dependencies: - - # pacman -S python-mysql-connector python-pygit2 python-srcinfo + # pacman -S python-mysql-connector python-pygit2 python-srcinfo python-sqlalchemy # pacman -S python-bleach python-markdown # python3 setup.py install +5) Create a new MySQL database and a user and import the aurweb SQL schema: + + $ python -m aurweb.initdb + 6) Create a new user: # useradd -U -d /srv/http/aurweb -c 'AUR user' aur diff --git a/TESTING b/TESTING index b0a5f628..190043f9 100644 --- a/TESTING +++ b/TESTING @@ -9,26 +9,27 @@ INSTALL. $ git clone git://git.archlinux.org/aurweb.git -2) Install php and necessary modules: +2) Install the necessary packages: - # pacman -S php php-sqlite sqlite words fortune-mod + # pacman -S --needed php php-sqlite sqlite words fortune-mod python python-sqlalchemy Ensure to enable the pdo_sqlite extension in php.ini. -3) Prepare the testing database: - - $ cd /path/to/aurweb/schema - $ make - $ ./gendummydata.py out.sql - $ sqlite3 ../aurweb.sqlite3 < aur-schema-sqlite.sql - $ sqlite3 ../aurweb.sqlite3 < out.sql - -4) Copy conf/config.defaults to conf/config and adjust the configuration +3) Copy conf/config.defaults to conf/config and adjust the configuration (pay attention to disable_http_login, enable_maintenance and aur_location). Be sure to change backend to sqlite and name to the file location of your created test database. +4) Prepare the testing database: + + $ cd /path/to/aurweb/ + $ python -m aurweb.initdb + + $ cd /path/to/aurweb/schema + $ ./gendummydata.py out.sql + $ sqlite3 path/to/aurweb.sqlite3 < out.sql + 5) Run the PHP built-in web server: $ AUR_CONFIG='/path/to/aurweb/conf/config' php -S localhost:8080 -t /path/to/aurweb/web/html diff --git a/aurweb/db.py b/aurweb/db.py index c6d4de11..1ccd9a07 100644 --- a/aurweb/db.py +++ b/aurweb/db.py @@ -11,6 +11,33 @@ except ImportError: import aurweb.config +def get_sqlalchemy_url(): + """ + Build an SQLAlchemy for use with create_engine based on the aurweb configuration. + """ + import sqlalchemy + aur_db_backend = aurweb.config.get('database', 'backend') + if aur_db_backend == 'mysql': + return sqlalchemy.engine.url.URL( + 'mysql+mysqlconnector', + username=aurweb.config.get('database', 'user'), + password=aurweb.config.get('database', 'password'), + host=aurweb.config.get('database', 'host'), + database=aurweb.config.get('database', 'name'), + query={ + 'unix_socket': aurweb.config.get('database', 'socket'), + 'buffered': True, + }, + ) + elif aur_db_backend == 'sqlite': + return sqlalchemy.engine.url.URL( + 'sqlite', + database=aurweb.config.get('database', 'name'), + ) + else: + raise ValueError('unsupported database backend') + + class Connection: _conn = None _paramstyle = None diff --git a/aurweb/initdb.py b/aurweb/initdb.py new file mode 100644 index 00000000..e3e96503 --- /dev/null +++ b/aurweb/initdb.py @@ -0,0 +1,47 @@ +import aurweb.db +import aurweb.schema + +import argparse +import sqlalchemy + + +def feed_initial_data(conn): + conn.execute(aurweb.schema.AccountTypes.insert(), [ + {'ID': 1, 'AccountType': 'User'}, + {'ID': 2, 'AccountType': 'Trusted User'}, + {'ID': 3, 'AccountType': 'Developer'}, + {'ID': 4, 'AccountType': 'Trusted User & Developer'}, + ]) + conn.execute(aurweb.schema.DependencyTypes.insert(), [ + {'ID': 1, 'Name': 'depends'}, + {'ID': 2, 'Name': 'makedepends'}, + {'ID': 3, 'Name': 'checkdepends'}, + {'ID': 4, 'Name': 'optdepends'}, + ]) + conn.execute(aurweb.schema.RelationTypes.insert(), [ + {'ID': 1, 'Name': 'conflicts'}, + {'ID': 2, 'Name': 'provides'}, + {'ID': 3, 'Name': 'replaces'}, + ]) + conn.execute(aurweb.schema.RequestTypes.insert(), [ + {'ID': 1, 'Name': 'deletion'}, + {'ID': 2, 'Name': 'orphan'}, + {'ID': 3, 'Name': 'merge'}, + ]) + + +def run(args): + engine = sqlalchemy.create_engine(aurweb.db.get_sqlalchemy_url(), + echo=(args.verbose >= 1)) + aurweb.schema.metadata.create_all(engine) + feed_initial_data(engine.connect()) + + +if __name__ == '__main__': + parser = argparse.ArgumentParser( + prog='python -m aurweb.initdb', + description='Initialize the aurweb database.') + parser.add_argument('-v', '--verbose', action='count', default=0, + help='increase verbosity') + args = parser.parse_args() + run(args) diff --git a/aurweb/schema.py b/aurweb/schema.py new file mode 100644 index 00000000..b1261e86 --- /dev/null +++ b/aurweb/schema.py @@ -0,0 +1,387 @@ +from sqlalchemy import CHAR, Column, ForeignKey, Index, MetaData, String, TIMESTAMP, Table, Text, text +from sqlalchemy.dialects.mysql import BIGINT, DECIMAL, INTEGER, TINYINT +from sqlalchemy.ext.compiler import compiles + + +@compiles(TINYINT, 'sqlite') +def compile_tinyint_sqlite(type_, compiler, **kw): + """TINYINT is not supported on SQLite. Substitute it with INTEGER.""" + return 'INTEGER' + + +metadata = MetaData() + +# Define the Account Types for the AUR. +AccountTypes = Table( + 'AccountTypes', metadata, + Column('ID', TINYINT(unsigned=True), primary_key=True), + Column('AccountType', String(32), nullable=False, server_default=text("''")), + mysql_engine='InnoDB', +) + + +# User information for each user regardless of type. +Users = Table( + 'Users', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('AccountTypeID', ForeignKey('AccountTypes.ID', ondelete="NO ACTION"), nullable=False, server_default=text("1")), + Column('Suspended', TINYINT(unsigned=True), nullable=False, server_default=text("0")), + Column('Username', String(32), nullable=False, unique=True), + Column('Email', String(254), nullable=False, unique=True), + Column('BackupEmail', String(254)), + Column('HideEmail', TINYINT(unsigned=True), nullable=False, server_default=text("0")), + Column('Passwd', String(255), nullable=False), + Column('Salt', CHAR(32), nullable=False, server_default=text("''")), + Column('ResetKey', CHAR(32), nullable=False, server_default=text("''")), + Column('RealName', String(64), nullable=False, server_default=text("''")), + Column('LangPreference', String(6), nullable=False, server_default=text("'en'")), + Column('Timezone', String(32), nullable=False, server_default=text("'UTC'")), + Column('Homepage', Text), + Column('IRCNick', String(32), nullable=False, server_default=text("''")), + Column('PGPKey', String(40)), + Column('LastLogin', BIGINT(unsigned=True), nullable=False, server_default=text("0")), + Column('LastLoginIPAddress', String(45)), + Column('LastSSHLogin', BIGINT(unsigned=True), nullable=False, server_default=text("0")), + Column('LastSSHLoginIPAddress', String(45)), + Column('InactivityTS', BIGINT(unsigned=True), nullable=False, server_default=text("0")), + Column('RegistrationTS', TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP")), + Column('CommentNotify', TINYINT(1), nullable=False, server_default=text("1")), + Column('UpdateNotify', TINYINT(1), nullable=False, server_default=text("0")), + Column('OwnershipNotify', TINYINT(1), nullable=False, server_default=text("1")), + Index('UsersAccountTypeID', 'AccountTypeID'), + mysql_engine='InnoDB', +) + + +# SSH public keys used for the aurweb SSH/Git interface. +SSHPubKeys = Table( + 'SSHPubKeys', metadata, + Column('UserID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Column('Fingerprint', String(44), primary_key=True), + Column('PubKey', String(4096), nullable=False), + mysql_engine='InnoDB', +) + + +# Track Users logging in/out of AUR web site. +Sessions = Table( + 'Sessions', metadata, + Column('UsersID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Column('SessionID', CHAR(32), nullable=False, unique=True), + Column('LastUpdateTS', BIGINT(unsigned=True), nullable=False), + mysql_engine='InnoDB', +) + + +# Information on package bases +PackageBases = Table( + 'PackageBases', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Name', String(255), nullable=False, unique=True), + Column('NumVotes', INTEGER(unsigned=True), nullable=False, server_default=text("0")), + Column('Popularity', DECIMAL(10, 6, unsigned=True), nullable=False, server_default=text("0")), + Column('OutOfDateTS', BIGINT(unsigned=True)), + Column('FlaggerComment', Text, nullable=False), + Column('SubmittedTS', BIGINT(unsigned=True), nullable=False), + Column('ModifiedTS', BIGINT(unsigned=True), nullable=False), + Column('FlaggerUID', ForeignKey('Users.ID', ondelete='SET NULL')), # who flagged the package out-of-date? + # deleting a user will cause packages to be orphaned, not deleted + Column('SubmitterUID', ForeignKey('Users.ID', ondelete='SET NULL')), # who submitted it? + Column('MaintainerUID', ForeignKey('Users.ID', ondelete='SET NULL')), # User + Column('PackagerUID', ForeignKey('Users.ID', ondelete='SET NULL')), # Last packager + Index('BasesMaintainerUID', 'MaintainerUID'), + Index('BasesNumVotes', 'NumVotes'), + Index('BasesPackagerUID', 'PackagerUID'), + Index('BasesSubmitterUID', 'SubmitterUID'), + mysql_engine='InnoDB', +) + + +# Keywords of package bases +PackageKeywords = Table( + 'PackageKeywords', metadata, + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='CASCADE'), primary_key=True, nullable=False), + Column('Keyword', String(255), primary_key=True, nullable=False, server_default=text("''")), + mysql_engine='InnoDB', +) + + +# Information about the actual packages +Packages = Table( + 'Packages', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='CASCADE'), nullable=False), + Column('Name', String(255), nullable=False, unique=True), + Column('Version', String(255), nullable=False, server_default=text("''")), + Column('Description', String(255)), + Column('URL', String(8000)), + mysql_engine='InnoDB', +) + + +# Information about licenses +Licenses = Table( + 'Licenses', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Name', String(255), nullable=False, unique=True), + mysql_engine='InnoDB', +) + + +# Information about package-license-relations +PackageLicenses = Table( + 'PackageLicenses', metadata, + Column('PackageID', ForeignKey('Packages.ID', ondelete='CASCADE'), primary_key=True, nullable=False), + Column('LicenseID', ForeignKey('Licenses.ID', ondelete='CASCADE'), primary_key=True, nullable=False), + mysql_engine='InnoDB', +) + + +# Information about groups +Groups = Table( + 'Groups', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Name', String(255), nullable=False, unique=True), + mysql_engine='InnoDB', +) + + +# Information about package-group-relations +PackageGroups = Table( + 'PackageGroups', metadata, + Column('PackageID', ForeignKey('Packages.ID', ondelete='CASCADE'), primary_key=True, nullable=False), + Column('GroupID', ForeignKey('Groups.ID', ondelete='CASCADE'), primary_key=True, nullable=False), + mysql_engine='InnoDB', +) + + +# Define the package dependency types +DependencyTypes = Table( + 'DependencyTypes', metadata, + Column('ID', TINYINT(unsigned=True), primary_key=True), + Column('Name', String(32), nullable=False, server_default=text("''")), + mysql_engine='InnoDB', +) + + +# Track which dependencies a package has +PackageDepends = Table( + 'PackageDepends', metadata, + Column('PackageID', ForeignKey('Packages.ID', ondelete='CASCADE'), nullable=False), + Column('DepTypeID', ForeignKey('DependencyTypes.ID', ondelete="NO ACTION"), nullable=False), + Column('DepName', String(255), nullable=False), + Column('DepDesc', String(255)), + Column('DepCondition', String(255)), + Column('DepArch', String(255)), + Index('DependsDepName', 'DepName'), + Index('DependsPackageID', 'PackageID'), + mysql_engine='InnoDB', +) + + +# Define the package relation types +RelationTypes = Table( + 'RelationTypes', metadata, + Column('ID', TINYINT(unsigned=True), primary_key=True), + Column('Name', String(32), nullable=False, server_default=text("''")), + mysql_engine='InnoDB', +) + + +# Track which conflicts, provides and replaces a package has +PackageRelations = Table( + 'PackageRelations', metadata, + Column('PackageID', ForeignKey('Packages.ID', ondelete='CASCADE'), nullable=False), + Column('RelTypeID', ForeignKey('RelationTypes.ID', ondelete="NO ACTION"), nullable=False), + Column('RelName', String(255), nullable=False), + Column('RelCondition', String(255)), + Column('RelArch', String(255)), + Index('RelationsPackageID', 'PackageID'), + Index('RelationsRelName', 'RelName'), + mysql_engine='InnoDB', +) + + +# Track which sources a package has +PackageSources = Table( + 'PackageSources', metadata, + Column('PackageID', ForeignKey('Packages.ID', ondelete='CASCADE'), nullable=False), + Column('Source', String(8000), nullable=False, server_default=text("'/dev/null'")), + Column('SourceArch', String(255)), + Index('SourcesPackageID', 'PackageID'), + mysql_engine='InnoDB', +) + + +# Track votes for packages +PackageVotes = Table( + 'PackageVotes', metadata, + Column('UsersID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='CASCADE'), nullable=False), + Column('VoteTS', BIGINT(unsigned=True)), + Index('VoteUsersIDPackageID', 'UsersID', 'PackageBaseID', unique=True), + Index('VotesPackageBaseID', 'PackageBaseID'), + Index('VotesUsersID', 'UsersID'), + mysql_engine='InnoDB', +) + + +# Record comments for packages +PackageComments = Table( + 'PackageComments', metadata, + Column('ID', BIGINT(unsigned=True), primary_key=True), + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='CASCADE'), nullable=False), + Column('UsersID', ForeignKey('Users.ID', ondelete='SET NULL')), + Column('Comments', Text, nullable=False), + Column('RenderedComment', Text, nullable=False), + Column('CommentTS', BIGINT(unsigned=True), nullable=False, server_default=text("0")), + Column('EditedTS', BIGINT(unsigned=True)), + Column('EditedUsersID', ForeignKey('Users.ID', ondelete='SET NULL')), + Column('DelTS', BIGINT(unsigned=True)), + Column('DelUsersID', ForeignKey('Users.ID', ondelete='CASCADE')), + Column('PinnedTS', BIGINT(unsigned=True), nullable=False, server_default=text("0")), + Index('CommentsPackageBaseID', 'PackageBaseID'), + Index('CommentsUsersID', 'UsersID'), + mysql_engine='InnoDB', +) + + +# Package base co-maintainers +PackageComaintainers = Table( + 'PackageComaintainers', metadata, + Column('UsersID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='CASCADE'), nullable=False), + Column('Priority', INTEGER(unsigned=True), nullable=False), + Index('ComaintainersPackageBaseID', 'PackageBaseID'), + Index('ComaintainersUsersID', 'UsersID'), + mysql_engine='InnoDB', +) + + +# Package base notifications +PackageNotifications = Table( + 'PackageNotifications', metadata, + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='CASCADE'), nullable=False), + Column('UserID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Index('NotifyUserIDPkgID', 'UserID', 'PackageBaseID', unique=True), + mysql_engine='InnoDB', +) + + +# Package name blacklist +PackageBlacklist = Table( + 'PackageBlacklist', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Name', String(64), nullable=False, unique=True), + mysql_engine='InnoDB', +) + + +# Providers in the official repositories +OfficialProviders = Table( + 'OfficialProviders', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Name', String(64), nullable=False), + Column('Repo', String(64), nullable=False), + Column('Provides', String(64), nullable=False), + Index('ProviderNameProvides', 'Name', 'Provides', unique=True), + mysql_engine='InnoDB', +) + + +# Define package request types +RequestTypes = Table( + 'RequestTypes', metadata, + Column('ID', TINYINT(unsigned=True), primary_key=True), + Column('Name', String(32), nullable=False, server_default=text("''")), + mysql_engine='InnoDB', +) + + +# Package requests +PackageRequests = Table( + 'PackageRequests', metadata, + Column('ID', BIGINT(unsigned=True), primary_key=True), + Column('ReqTypeID', ForeignKey('RequestTypes.ID', ondelete="NO ACTION"), nullable=False), + Column('PackageBaseID', ForeignKey('PackageBases.ID', ondelete='SET NULL')), + Column('PackageBaseName', String(255), nullable=False), + Column('MergeBaseName', String(255)), + Column('UsersID', ForeignKey('Users.ID', ondelete='SET NULL')), + Column('Comments', Text, nullable=False), + Column('ClosureComment', Text, nullable=False), + Column('RequestTS', BIGINT(unsigned=True), nullable=False, server_default=text("0")), + Column('ClosedTS', BIGINT(unsigned=True)), + Column('ClosedUID', ForeignKey('Users.ID', ondelete='SET NULL')), + Column('Status', TINYINT(unsigned=True), nullable=False, server_default=text("0")), + Index('RequestsPackageBaseID', 'PackageBaseID'), + Index('RequestsUsersID', 'UsersID'), + mysql_engine='InnoDB', +) + + +# Vote information +TU_VoteInfo = Table( + 'TU_VoteInfo', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Agenda', Text, nullable=False), + Column('User', String(32), nullable=False), + Column('Submitted', BIGINT(unsigned=True), nullable=False), + Column('End', BIGINT(unsigned=True), nullable=False), + Column('Quorum', DECIMAL(2, 2, unsigned=True), nullable=False), + Column('SubmitterID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Column('Yes', TINYINT(3, unsigned=True), nullable=False, server_default=text("'0'")), + Column('No', TINYINT(3, unsigned=True), nullable=False, server_default=text("'0'")), + Column('Abstain', TINYINT(3, unsigned=True), nullable=False, server_default=text("'0'")), + Column('ActiveTUs', TINYINT(3, unsigned=True), nullable=False, server_default=text("'0'")), + mysql_engine='InnoDB', +) + + +# Individual vote records +TU_Votes = Table( + 'TU_Votes', metadata, + Column('VoteID', ForeignKey('TU_VoteInfo.ID', ondelete='CASCADE'), nullable=False), + Column('UserID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + mysql_engine='InnoDB', +) + + +# Malicious user banning +Bans = Table( + 'Bans', metadata, + Column('IPAddress', String(45), primary_key=True), + Column('BanTS', TIMESTAMP, nullable=False), + mysql_engine='InnoDB', +) + + +# Terms and Conditions +Terms = Table( + 'Terms', metadata, + Column('ID', INTEGER(unsigned=True), primary_key=True), + Column('Description', String(255), nullable=False), + Column('URL', String(8000), nullable=False), + Column('Revision', INTEGER(unsigned=True), nullable=False, server_default=text("1")), + mysql_engine='InnoDB', +) + + +# Terms and Conditions accepted by users +AcceptedTerms = Table( + 'AcceptedTerms', metadata, + Column('UsersID', ForeignKey('Users.ID', ondelete='CASCADE'), nullable=False), + Column('TermsID', ForeignKey('Terms.ID', ondelete='CASCADE'), nullable=False), + Column('Revision', INTEGER(unsigned=True), nullable=False, server_default=text("0")), + mysql_engine='InnoDB', +) + + +# Rate limits for API +ApiRateLimit = Table( + 'ApiRateLimit', metadata, + Column('IP', String(45), primary_key=True), + Column('Requests', INTEGER(11), nullable=False), + Column('WindowStart', BIGINT(20), nullable=False), + Index('ApiRateLimitWindowStart', 'WindowStart'), + mysql_engine='InnoDB', +) 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." diff --git a/test/Makefile b/test/Makefile index 4ce9b9be..f559e169 100644 --- a/test/Makefile +++ b/test/Makefile @@ -1,10 +1,6 @@ -FOREIGN_TARGETS = ../schema/aur-schema-sqlite.sql T = $(sort $(wildcard t[0-9][0-9][0-9][0-9]-*.sh)) -check: $(FOREIGN_TARGETS) $(T) - -$(FOREIGN_TARGETS): - $(MAKE) -C $(dir $@) $(notdir $@) +check: $(T) clean: $(RM) -r test-results/ diff --git a/test/setup.sh b/test/setup.sh index 5c761f22..12f6edcc 100644 --- a/test/setup.sh +++ b/test/setup.sh @@ -110,10 +110,7 @@ SSH_TTY=/dev/pts/0 export SSH_CLIENT SSH_CONNECTION SSH_TTY # Initialize the test database. -DBSCHEMA="$TOPLEVEL/schema/aur-schema-sqlite.sql" -[ -f "$DBSCHEMA" ] || error 'SQLite database schema not found' -rm -f aur.db -sqlite3 aur.db <"$DBSCHEMA" +python -m aurweb.initdb echo "INSERT INTO Users (ID, UserName, Passwd, Email, LangPreference, AccountTypeID) VALUES (1, 'user', '!', 'user@localhost', 'en', 1);" | sqlite3 aur.db echo "INSERT INTO Users (ID, UserName, Passwd, Email, LangPreference, AccountTypeID) VALUES (2, 'tu', '!', 'tu@localhost', 'en', 2);" | sqlite3 aur.db -- 2.25.0