@@ -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
@@ -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
@@ -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
new file mode 100644
@@ -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)
new file mode 100644
@@ -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',
+)
deleted file mode 100644
@@ -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
deleted file mode 100644
@@ -1,415 +0,0 @@
---
-
---
-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');
-
-
---
-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);
-
-
---
-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;
-
-
---
-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;
-
-
---
-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);
-
-
---
-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;
-
-
---
-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;
-
-
---
-CREATE TABLE Licenses (
- ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name VARCHAR(255) NOT NULL,
- PRIMARY KEY (ID),
- UNIQUE (Name)
-) ENGINE = InnoDB;
-
-
---
-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;
-
-
---
-CREATE TABLE `Groups` (
- ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name VARCHAR(255) NOT NULL,
- PRIMARY KEY (ID),
- UNIQUE (Name)
-) ENGINE = InnoDB;
-
-
---
-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;
-
-
---
-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');
-
-
---
-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);
-
-
---
-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');
-
-
---
-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);
-
-
---
-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);
-
-
---
-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);
-
---
-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);
-
---
-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);
-
---
-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);
-
---
-CREATE TABLE PackageBlacklist (
- ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name VARCHAR(64) NOT NULL,
- PRIMARY KEY (ID),
- UNIQUE (Name)
-) ENGINE = InnoDB;
-
---
-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);
-
---
-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');
-
---
-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);
-
---
-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;
-
---
-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;
-
---
-CREATE TABLE Bans (
- IPAddress VARCHAR(45) NOT NULL,
- BanTS TIMESTAMP NOT NULL,
- PRIMARY KEY (IPAddress)
-) ENGINE = InnoDB;
-
---
-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;
-
---
-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;
-
---
-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);
deleted file mode 100755
@@ -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."
@@ -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/
@@ -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