There aren't any other subdirectories in support/. Reduce the nesting depth by moving schema/ to the top-level source directory.
Signed-off-by: Lukas Fleischer <[email protected]> --- INSTALL | 4 +- schema/aur-schema.sql | 228 +++++++++++++++++++++++++++++++ schema/gendummydata.py | 302 +++++++++++++++++++++++++++++++++++++++++ schema/reloadtestdb.sh | 29 ++++ support/schema/aur-schema.sql | 228 ------------------------------- support/schema/gendummydata.py | 302 ----------------------------------------- support/schema/reloadtestdb.sh | 29 ---- 7 files changed, 561 insertions(+), 561 deletions(-) create mode 100644 schema/aur-schema.sql create mode 100755 schema/gendummydata.py create mode 100755 schema/reloadtestdb.sh delete mode 100644 support/schema/aur-schema.sql delete mode 100755 support/schema/gendummydata.py delete mode 100755 support/schema/reloadtestdb.sh diff --git a/INSTALL b/INSTALL index e8bf797..cbb9f44 100644 --- a/INSTALL +++ b/INSTALL @@ -66,12 +66,12 @@ Setup on Arch Linux: mysql> quit - Load the schema file - $ mysql -uaur -p AUR < ~/aur/support/schema/aur-schema.sql + $ mysql -uaur -p AUR < ~/aur/schema/aur-schema.sql (give password 'aur' at the prompt) - Optionally load some test data for development purposes. # pacman -S words fortune-mod - $ cd ~/aur/support/schema/ + $ cd ~/aur/schema/ $ python gendummydata.py dummy-data.sql $ bzip2 dummy-data.sql $ bzcat dummy-data.sql.bz2 | mysql -uaur -p AUR diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql new file mode 100644 index 0000000..c01701c --- /dev/null +++ b/schema/aur-schema.sql @@ -0,0 +1,228 @@ +-- The MySQL database layout for the AUR. Certain data +-- is also included such as AccountTypes, etc. +-- +DROP DATABASE IF EXISTS AUR; +CREATE DATABASE AUR DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +USE AUR; + +-- 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'); + + +-- 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(64) NOT NULL, + Passwd CHAR(32) NOT NULL, + Salt CHAR(32) NOT NULL DEFAULT '', + ResetKey CHAR(32) NOT NULL DEFAULT '', + RealName VARCHAR(64) NOT NULL DEFAULT '', + LangPreference VARCHAR(5) NOT NULL DEFAULT 'en', + IRCNick VARCHAR(32) NOT NULL DEFAULT '', + PGPKey VARCHAR(40) NULL DEFAULT NULL, + LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, + LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, + LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, + RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (ID), + UNIQUE (Username), + UNIQUE (Email), + INDEX (AccountTypeID), + FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION +) ENGINE = InnoDB; +-- A default developer account for testing purposes +INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( + 1, 3, 'dev', 'dev@localhost', MD5('dev')); +INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( + 2, 2, 'tu', 'tu@localhost', MD5('tu')); +INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( + 3, 1, 'user', 'user@localhost', MD5('user')); + + +-- 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; + + +-- Categories for grouping packages when they reside in +-- Unsupported or the AUR - based on the categories defined +-- in 'extra'. +-- +CREATE TABLE PackageCategories ( + ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, + Category VARCHAR(32) NOT NULL, + PRIMARY KEY (ID) +) ENGINE = InnoDB; +INSERT INTO PackageCategories (Category) VALUES ('none'); +INSERT INTO PackageCategories (Category) VALUES ('daemons'); +INSERT INTO PackageCategories (Category) VALUES ('devel'); +INSERT INTO PackageCategories (Category) VALUES ('editors'); +INSERT INTO PackageCategories (Category) VALUES ('emulators'); +INSERT INTO PackageCategories (Category) VALUES ('games'); +INSERT INTO PackageCategories (Category) VALUES ('gnome'); +INSERT INTO PackageCategories (Category) VALUES ('i18n'); +INSERT INTO PackageCategories (Category) VALUES ('kde'); +INSERT INTO PackageCategories (Category) VALUES ('lib'); +INSERT INTO PackageCategories (Category) VALUES ('modules'); +INSERT INTO PackageCategories (Category) VALUES ('multimedia'); +INSERT INTO PackageCategories (Category) VALUES ('network'); +INSERT INTO PackageCategories (Category) VALUES ('office'); +INSERT INTO PackageCategories (Category) VALUES ('science'); +INSERT INTO PackageCategories (Category) VALUES ('system'); +INSERT INTO PackageCategories (Category) VALUES ('x11'); +INSERT INTO PackageCategories (Category) VALUES ('xfce'); +INSERT INTO PackageCategories (Category) VALUES ('fonts'); + + +-- Information about the actual packages +-- +CREATE TABLE Packages ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name VARCHAR(64) NOT NULL, + Version VARCHAR(32) NOT NULL DEFAULT '', + CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1, + Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package", + URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org";, + License VARCHAR(40) NOT NULL DEFAULT '', + NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0, + OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL, + SubmittedTS BIGINT UNSIGNED NOT NULL, + ModifiedTS BIGINT UNSIGNED NOT NULL, + SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL, -- who submitted it? + MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL, -- User + PRIMARY KEY (ID), + UNIQUE (Name), + INDEX (CategoryID), + INDEX (NumVotes), + INDEX (SubmitterUID), + INDEX (MaintainerUID), + FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION, + -- 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 +) ENGINE = InnoDB; + + +-- Track which dependencies a package has +-- +CREATE TABLE PackageDepends ( + PackageID INTEGER UNSIGNED NOT NULL, + DepName VARCHAR(64) NOT NULL, + DepCondition VARCHAR(20), + INDEX (PackageID), + INDEX (DepName), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + + +-- Track which sources a package has +-- +CREATE TABLE PackageSources ( + PackageID INTEGER UNSIGNED NOT NULL, + Source VARCHAR(255) NOT NULL DEFAULT "/dev/null", + INDEX (PackageID), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + + +-- Track votes for packages +-- +CREATE TABLE PackageVotes ( + UsersID INTEGER UNSIGNED NOT NULL, + PackageID INTEGER UNSIGNED NOT NULL, + INDEX (UsersID), + INDEX (PackageID), + FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; +CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageID); + +-- Record comments for packages +-- +CREATE TABLE PackageComments ( + ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, + PackageID INTEGER UNSIGNED NOT NULL, + UsersID INTEGER UNSIGNED NULL DEFAULT NULL, + Comments TEXT NOT NULL DEFAULT '', + CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0, + DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL, + PRIMARY KEY (ID), + INDEX (UsersID), + INDEX (PackageID), + FOREIGN KEY (UsersID) REFERENCES Users(ID) ON SET NULL, + FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE, + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + +-- Comment addition notifications +-- +CREATE TABLE CommentNotify ( + PkgID INTEGER UNSIGNED NOT NULL, + UserID INTEGER UNSIGNED NOT NULL, + FOREIGN KEY (PkgID) REFERENCES Packages(ID) ON DELETE CASCADE, + FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE +) ENGINE = InnoDB; +CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID); + +-- 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; + +-- Vote information +-- +CREATE TABLE IF NOT EXISTS TU_VoteInfo ( + ID int(10) unsigned NOT NULL auto_increment, + Agenda text NOT NULL, + User VARCHAR(32) NOT NULL, + Submitted bigint(20) unsigned NOT NULL, + End bigint(20) unsigned NOT NULL, + Quorum decimal(2, 2) unsigned NOT NULL, + SubmitterID int(10) 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 int(10) unsigned NOT NULL, + UserID int(10) 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 INTEGER UNSIGNED NOT NULL DEFAULT 0, + BanTS TIMESTAMP NOT NULL, + PRIMARY KEY (IPAddress) +) ENGINE = InnoDB; diff --git a/schema/gendummydata.py b/schema/gendummydata.py new file mode 100755 index 0000000..361d1f9 --- /dev/null +++ b/schema/gendummydata.py @@ -0,0 +1,302 @@ +#!/usr/bin/python3 +""" +usage: gendummydata.py outputfilename.sql +""" +# +# This script seeds the AUR database with dummy data for +# use during development/testing. It uses random entries +# from /usr/share/dict/words to create user accounts and +# package names. It generates the SQL statements to +# insert these users/packages into the AUR database. +# +import random +import time +import os +import sys +import io +import logging + +LOG_LEVEL = logging.DEBUG # logging level. set to logging.INFO to reduce output +SEED_FILE = "/usr/share/dict/words" +DB_HOST = os.getenv("DB_HOST", "localhost") +DB_NAME = os.getenv("DB_NAME", "AUR") +DB_USER = os.getenv("DB_USER", "aur") +DB_PASS = os.getenv("DB_PASS", "aur") +USER_ID = 5 # Users.ID of first bogus user +PKG_ID = 1 # Packages.ID of first package +MAX_USERS = 300 # how many users to 'register' +MAX_DEVS = .1 # what percentage of MAX_USERS are Developers +MAX_TUS = .2 # what percentage of MAX_USERS are Trusted Users +MAX_PKGS = 900 # how many packages to load +PKG_DEPS = (1, 5) # min/max depends a package has +PKG_SRC = (1, 3) # min/max sources a package has +PKG_CMNTS = (1, 5) # min/max number of comments a package has +CATEGORIES_COUNT = 17 # the number of categories from aur-schema +VOTING = (0, .30) # percentage range for package voting +OPEN_PROPOSALS = 5 # number of open trusted user proposals +CLOSE_PROPOSALS = 15 # number of closed trusted user proposals +RANDOM_TLDS = ("edu", "com", "org", "net", "tw", "ru", "pl", "de", "es") +RANDOM_URL = ("http://www.";, "ftp://ftp.";, "http://";, "ftp://";) +RANDOM_LOCS = ("pub", "release", "files", "downloads", "src") +FORTUNE_FILE = "/usr/share/fortune/cookie" + +# setup logging +logformat = "%(levelname)s: %(message)s" +logging.basicConfig(format=logformat, level=LOG_LEVEL) +log = logging.getLogger() + +if len(sys.argv) != 2: + log.error("Missing output filename argument") + raise SystemExit + +# make sure the seed file exists +# +if not os.path.exists(SEED_FILE): + log.error("Please install the 'words' Arch package") + raise SystemExit + +# make sure comments can be created +# +if not os.path.exists(FORTUNE_FILE): + log.error("Please install the 'fortune-mod' Arch package") + raise SystemExit + +# track what users/package names have been used +# +seen_users = {} +seen_pkgs = {} +user_keys = [] + +# some functions to generate random data +# +def genVersion(): + ver = [] + ver.append("%d" % random.randrange(0,10)) + ver.append("%d" % random.randrange(0,20)) + if random.randrange(0,2) == 0: + ver.append("%d" % random.randrange(0,100)) + return ".".join(ver) + "-%d" % random.randrange(1,11) +def genCategory(): + return random.randrange(1,CATEGORIES_COUNT) +def genUID(): + return seen_users[user_keys[random.randrange(0,len(user_keys))]] +def genFortune(): + return fortunes[random.randrange(0,len(fortunes))].replace("'", "") + + +# load the words, and make sure there are enough words for users/pkgs +# +log.debug("Grabbing words from seed file...") +fp = open(SEED_FILE, "r", encoding="utf-8") +contents = fp.readlines() +fp.close() +if MAX_USERS > len(contents): + MAX_USERS = len(contents) +if MAX_PKGS > len(contents): + MAX_PKGS = len(contents) +if len(contents) - MAX_USERS > MAX_PKGS: + need_dupes = 0 +else: + need_dupes = 1 + +# select random usernames +# +log.debug("Generating random user names...") +user_id = USER_ID +while len(seen_users) < MAX_USERS: + user = random.randrange(0, len(contents)) + word = contents[user].replace("'", "").replace(".","").replace(" ", "_") + word = word.strip().lower() + if word not in seen_users: + seen_users[word] = user_id + user_id += 1 +user_keys = list(seen_users.keys()) + +# select random package names +# +log.debug("Generating random package names...") +num_pkgs = PKG_ID +while len(seen_pkgs) < MAX_PKGS: + pkg = random.randrange(0, len(contents)) + word = contents[pkg].replace("'", "").replace(".","").replace(" ", "_") + word = word.strip().lower() + if not need_dupes: + if word not in seen_pkgs and word not in seen_users: + seen_pkgs[word] = num_pkgs + num_pkgs += 1 + else: + if word not in seen_pkgs: + seen_pkgs[word] = num_pkgs + num_pkgs += 1 + +# free up contents memory +# +contents = None + +# developer/tu IDs +# +developers = [] +trustedusers = [] +has_devs = 0 +has_tus = 0 + +# Just let python throw the errors if any happen +# +out = open(sys.argv[1], "w", encoding="utf-8") +out.write("BEGIN;\n") + +# Begin by creating the User statements +# +log.debug("Creating SQL statements for users.") +for u in user_keys: + account_type = 1 # default to normal user + if not has_devs or not has_tus: + account_type = random.randrange(1, 4) + if account_type == 3 and not has_devs: + # this will be a dev account + # + developers.append(seen_users[u]) + if len(developers) >= MAX_DEVS * MAX_USERS: + has_devs = 1 + elif account_type == 2 and not has_tus: + # this will be a trusted user account + # + trustedusers.append(seen_users[u]) + if len(trustedusers) >= MAX_TUS * MAX_USERS: + has_tus = 1 + else: + # a normal user account + # + pass + + s = ("INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd)" + " VALUES (%d, %d, '%s', '%[email protected]', MD5('%s'));\n") + s = s % (seen_users[u], account_type, u, u, u) + out.write(s) + +log.debug("Number of developers: %d" % len(developers)) +log.debug("Number of trusted users: %d" % len(trustedusers)) +log.debug("Number of users: %d" % (MAX_USERS-len(developers)-len(trustedusers))) +log.debug("Number of packages: %d" % MAX_PKGS) + +log.debug("Gathering text from fortune file...") +fp = open(FORTUNE_FILE, "r", encoding="utf-8") +fortunes = fp.read().split("%\n") +fp.close() + +# Create the package statements +# +log.debug("Creating SQL statements for packages.") +count = 0 +for p in list(seen_pkgs.keys()): + NOW = int(time.time()) + if count % 2 == 0: + muid = developers[random.randrange(0,len(developers))] + else: + muid = trustedusers[random.randrange(0,len(trustedusers))] + if count % 20 == 0: # every so often, there are orphans... + muid = "NULL" + + uuid = genUID() # the submitter/user + + s = ("INSERT INTO Packages (ID, Name, Version, CategoryID," + " SubmittedTS, SubmitterUID, MaintainerUID) VALUES " + " (%d, '%s', '%s', %d, %d, %d, %s);\n") + s = s % (seen_pkgs[p], p, genVersion(), genCategory(), NOW, uuid, muid) + + out.write(s) + count += 1 + + # create random comments for this package + # + num_comments = random.randrange(PKG_CMNTS[0], PKG_CMNTS[1]) + for i in range(0, num_comments): + now = NOW + random.randrange(400, 86400*3) + s = ("INSERT INTO PackageComments (PackageID, UsersID," + " Comments, CommentTS) VALUES (%d, %d, '%s', %d);\n") + s = s % (seen_pkgs[p], genUID(), genFortune(), now) + out.write(s) + +# Cast votes +# +track_votes = {} +log.debug("Casting votes for packages.") +for u in user_keys: + num_votes = random.randrange(int(len(seen_pkgs)*VOTING[0]), + int(len(seen_pkgs)*VOTING[1])) + pkgvote = {} + for v in range(num_votes): + pkg = random.randrange(1, len(seen_pkgs) + 1) + if pkg not in pkgvote: + s = ("INSERT INTO PackageVotes (UsersID, PackageID)" + " VALUES (%d, %d);\n") + s = s % (seen_users[u], pkg) + pkgvote[pkg] = 1 + if pkg not in track_votes: + track_votes[pkg] = 0 + track_votes[pkg] += 1 + out.write(s) + +# Update statements for package votes +# +for p in list(track_votes.keys()): + s = "UPDATE Packages SET NumVotes = %d WHERE ID = %d;\n" + s = s % (track_votes[p], p) + out.write(s) + +# Create package dependencies and sources +# +log.debug("Creating statements for package depends/sources.") +for p in list(seen_pkgs.keys()): + num_deps = random.randrange(PKG_DEPS[0], PKG_DEPS[1]) + this_deps = {} + i = 0 + while i != num_deps: + dep = random.choice([k for k in seen_pkgs]) + if dep not in this_deps: + s = "INSERT INTO PackageDepends VALUES (%d, '%s', NULL);\n" + s = s % (seen_pkgs[p], dep) + out.write(s) + i += 1 + + num_sources = random.randrange(PKG_SRC[0], PKG_SRC[1]) + for i in range(num_sources): + src_file = user_keys[random.randrange(0, len(user_keys))] + src = "%s%s.%s/%s/%s-%s.tar.gz" % ( + RANDOM_URL[random.randrange(0,len(RANDOM_URL))], + p, RANDOM_TLDS[random.randrange(0,len(RANDOM_TLDS))], + RANDOM_LOCS[random.randrange(0,len(RANDOM_LOCS))], + src_file, genVersion()) + s = "INSERT INTO PackageSources VALUES (%d, '%s');\n" + s = s % (seen_pkgs[p], src) + out.write(s) + +# Create trusted user proposals +# +log.debug("Creating SQL statements for trusted user proposals.") +count=0 +for t in range(0, OPEN_PROPOSALS+CLOSE_PROPOSALS): + now = int(time.time()) + if count < CLOSE_PROPOSALS: + start = now - random.randrange(3600*24*7, 3600*24*21) + end = now - random.randrange(0, 3600*24*7) + else: + start = now + end = now + random.randrange(3600*24, 3600*24*7) + if count % 5 == 0: # Don't make the vote about anyone once in a while + user = "" + else: + user = user_keys[random.randrange(0,len(user_keys))] + suid = trustedusers[random.randrange(0,len(trustedusers))] + s = ("INSERT INTO TU_VoteInfo (Agenda, User, Submitted, End," + " SubmitterID) VALUES ('%s', '%s', %d, %d, %d);\n") + s = s % (genFortune(), user, start, end, suid) + out.write(s) + count += 1 + +# close output file +# +out.write("COMMIT;\n") +out.write("\n") +out.close() +log.debug("Done.") diff --git a/schema/reloadtestdb.sh b/schema/reloadtestdb.sh new file mode 100755 index 0000000..ecaaaa8 --- /dev/null +++ b/schema/reloadtestdb.sh @@ -0,0 +1,29 @@ +#!/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 aur/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/support/schema/aur-schema.sql b/support/schema/aur-schema.sql deleted file mode 100644 index c01701c..0000000 --- a/support/schema/aur-schema.sql +++ /dev/null @@ -1,228 +0,0 @@ --- The MySQL database layout for the AUR. Certain data --- is also included such as AccountTypes, etc. --- -DROP DATABASE IF EXISTS AUR; -CREATE DATABASE AUR DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; -USE AUR; - --- 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'); - - --- 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(64) NOT NULL, - Passwd CHAR(32) NOT NULL, - Salt CHAR(32) NOT NULL DEFAULT '', - ResetKey CHAR(32) NOT NULL DEFAULT '', - RealName VARCHAR(64) NOT NULL DEFAULT '', - LangPreference VARCHAR(5) NOT NULL DEFAULT 'en', - IRCNick VARCHAR(32) NOT NULL DEFAULT '', - PGPKey VARCHAR(40) NULL DEFAULT NULL, - LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, - LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, - LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, - InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, - RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (ID), - UNIQUE (Username), - UNIQUE (Email), - INDEX (AccountTypeID), - FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION -) ENGINE = InnoDB; --- A default developer account for testing purposes -INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( - 1, 3, 'dev', 'dev@localhost', MD5('dev')); -INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( - 2, 2, 'tu', 'tu@localhost', MD5('tu')); -INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( - 3, 1, 'user', 'user@localhost', MD5('user')); - - --- 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; - - --- Categories for grouping packages when they reside in --- Unsupported or the AUR - based on the categories defined --- in 'extra'. --- -CREATE TABLE PackageCategories ( - ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, - Category VARCHAR(32) NOT NULL, - PRIMARY KEY (ID) -) ENGINE = InnoDB; -INSERT INTO PackageCategories (Category) VALUES ('none'); -INSERT INTO PackageCategories (Category) VALUES ('daemons'); -INSERT INTO PackageCategories (Category) VALUES ('devel'); -INSERT INTO PackageCategories (Category) VALUES ('editors'); -INSERT INTO PackageCategories (Category) VALUES ('emulators'); -INSERT INTO PackageCategories (Category) VALUES ('games'); -INSERT INTO PackageCategories (Category) VALUES ('gnome'); -INSERT INTO PackageCategories (Category) VALUES ('i18n'); -INSERT INTO PackageCategories (Category) VALUES ('kde'); -INSERT INTO PackageCategories (Category) VALUES ('lib'); -INSERT INTO PackageCategories (Category) VALUES ('modules'); -INSERT INTO PackageCategories (Category) VALUES ('multimedia'); -INSERT INTO PackageCategories (Category) VALUES ('network'); -INSERT INTO PackageCategories (Category) VALUES ('office'); -INSERT INTO PackageCategories (Category) VALUES ('science'); -INSERT INTO PackageCategories (Category) VALUES ('system'); -INSERT INTO PackageCategories (Category) VALUES ('x11'); -INSERT INTO PackageCategories (Category) VALUES ('xfce'); -INSERT INTO PackageCategories (Category) VALUES ('fonts'); - - --- Information about the actual packages --- -CREATE TABLE Packages ( - ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name VARCHAR(64) NOT NULL, - Version VARCHAR(32) NOT NULL DEFAULT '', - CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1, - Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package", - URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org";, - License VARCHAR(40) NOT NULL DEFAULT '', - NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0, - OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL, - SubmittedTS BIGINT UNSIGNED NOT NULL, - ModifiedTS BIGINT UNSIGNED NOT NULL, - SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL, -- who submitted it? - MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL, -- User - PRIMARY KEY (ID), - UNIQUE (Name), - INDEX (CategoryID), - INDEX (NumVotes), - INDEX (SubmitterUID), - INDEX (MaintainerUID), - FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION, - -- 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 -) ENGINE = InnoDB; - - --- Track which dependencies a package has --- -CREATE TABLE PackageDepends ( - PackageID INTEGER UNSIGNED NOT NULL, - DepName VARCHAR(64) NOT NULL, - DepCondition VARCHAR(20), - INDEX (PackageID), - INDEX (DepName), - FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - - --- Track which sources a package has --- -CREATE TABLE PackageSources ( - PackageID INTEGER UNSIGNED NOT NULL, - Source VARCHAR(255) NOT NULL DEFAULT "/dev/null", - INDEX (PackageID), - FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - - --- Track votes for packages --- -CREATE TABLE PackageVotes ( - UsersID INTEGER UNSIGNED NOT NULL, - PackageID INTEGER UNSIGNED NOT NULL, - INDEX (UsersID), - INDEX (PackageID), - FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, - FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE -) ENGINE = InnoDB; -CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageID); - --- Record comments for packages --- -CREATE TABLE PackageComments ( - ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, - PackageID INTEGER UNSIGNED NOT NULL, - UsersID INTEGER UNSIGNED NULL DEFAULT NULL, - Comments TEXT NOT NULL DEFAULT '', - CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0, - DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL, - PRIMARY KEY (ID), - INDEX (UsersID), - INDEX (PackageID), - FOREIGN KEY (UsersID) REFERENCES Users(ID) ON SET NULL, - FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE, - FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE -) ENGINE = InnoDB; - --- Comment addition notifications --- -CREATE TABLE CommentNotify ( - PkgID INTEGER UNSIGNED NOT NULL, - UserID INTEGER UNSIGNED NOT NULL, - FOREIGN KEY (PkgID) REFERENCES Packages(ID) ON DELETE CASCADE, - FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE -) ENGINE = InnoDB; -CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID); - --- 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; - --- Vote information --- -CREATE TABLE IF NOT EXISTS TU_VoteInfo ( - ID int(10) unsigned NOT NULL auto_increment, - Agenda text NOT NULL, - User VARCHAR(32) NOT NULL, - Submitted bigint(20) unsigned NOT NULL, - End bigint(20) unsigned NOT NULL, - Quorum decimal(2, 2) unsigned NOT NULL, - SubmitterID int(10) 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 int(10) unsigned NOT NULL, - UserID int(10) 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 INTEGER UNSIGNED NOT NULL DEFAULT 0, - BanTS TIMESTAMP NOT NULL, - PRIMARY KEY (IPAddress) -) ENGINE = InnoDB; diff --git a/support/schema/gendummydata.py b/support/schema/gendummydata.py deleted file mode 100755 index 361d1f9..0000000 --- a/support/schema/gendummydata.py +++ /dev/null @@ -1,302 +0,0 @@ -#!/usr/bin/python3 -""" -usage: gendummydata.py outputfilename.sql -""" -# -# This script seeds the AUR database with dummy data for -# use during development/testing. It uses random entries -# from /usr/share/dict/words to create user accounts and -# package names. It generates the SQL statements to -# insert these users/packages into the AUR database. -# -import random -import time -import os -import sys -import io -import logging - -LOG_LEVEL = logging.DEBUG # logging level. set to logging.INFO to reduce output -SEED_FILE = "/usr/share/dict/words" -DB_HOST = os.getenv("DB_HOST", "localhost") -DB_NAME = os.getenv("DB_NAME", "AUR") -DB_USER = os.getenv("DB_USER", "aur") -DB_PASS = os.getenv("DB_PASS", "aur") -USER_ID = 5 # Users.ID of first bogus user -PKG_ID = 1 # Packages.ID of first package -MAX_USERS = 300 # how many users to 'register' -MAX_DEVS = .1 # what percentage of MAX_USERS are Developers -MAX_TUS = .2 # what percentage of MAX_USERS are Trusted Users -MAX_PKGS = 900 # how many packages to load -PKG_DEPS = (1, 5) # min/max depends a package has -PKG_SRC = (1, 3) # min/max sources a package has -PKG_CMNTS = (1, 5) # min/max number of comments a package has -CATEGORIES_COUNT = 17 # the number of categories from aur-schema -VOTING = (0, .30) # percentage range for package voting -OPEN_PROPOSALS = 5 # number of open trusted user proposals -CLOSE_PROPOSALS = 15 # number of closed trusted user proposals -RANDOM_TLDS = ("edu", "com", "org", "net", "tw", "ru", "pl", "de", "es") -RANDOM_URL = ("http://www.";, "ftp://ftp.";, "http://";, "ftp://";) -RANDOM_LOCS = ("pub", "release", "files", "downloads", "src") -FORTUNE_FILE = "/usr/share/fortune/cookie" - -# setup logging -logformat = "%(levelname)s: %(message)s" -logging.basicConfig(format=logformat, level=LOG_LEVEL) -log = logging.getLogger() - -if len(sys.argv) != 2: - log.error("Missing output filename argument") - raise SystemExit - -# make sure the seed file exists -# -if not os.path.exists(SEED_FILE): - log.error("Please install the 'words' Arch package") - raise SystemExit - -# make sure comments can be created -# -if not os.path.exists(FORTUNE_FILE): - log.error("Please install the 'fortune-mod' Arch package") - raise SystemExit - -# track what users/package names have been used -# -seen_users = {} -seen_pkgs = {} -user_keys = [] - -# some functions to generate random data -# -def genVersion(): - ver = [] - ver.append("%d" % random.randrange(0,10)) - ver.append("%d" % random.randrange(0,20)) - if random.randrange(0,2) == 0: - ver.append("%d" % random.randrange(0,100)) - return ".".join(ver) + "-%d" % random.randrange(1,11) -def genCategory(): - return random.randrange(1,CATEGORIES_COUNT) -def genUID(): - return seen_users[user_keys[random.randrange(0,len(user_keys))]] -def genFortune(): - return fortunes[random.randrange(0,len(fortunes))].replace("'", "") - - -# load the words, and make sure there are enough words for users/pkgs -# -log.debug("Grabbing words from seed file...") -fp = open(SEED_FILE, "r", encoding="utf-8") -contents = fp.readlines() -fp.close() -if MAX_USERS > len(contents): - MAX_USERS = len(contents) -if MAX_PKGS > len(contents): - MAX_PKGS = len(contents) -if len(contents) - MAX_USERS > MAX_PKGS: - need_dupes = 0 -else: - need_dupes = 1 - -# select random usernames -# -log.debug("Generating random user names...") -user_id = USER_ID -while len(seen_users) < MAX_USERS: - user = random.randrange(0, len(contents)) - word = contents[user].replace("'", "").replace(".","").replace(" ", "_") - word = word.strip().lower() - if word not in seen_users: - seen_users[word] = user_id - user_id += 1 -user_keys = list(seen_users.keys()) - -# select random package names -# -log.debug("Generating random package names...") -num_pkgs = PKG_ID -while len(seen_pkgs) < MAX_PKGS: - pkg = random.randrange(0, len(contents)) - word = contents[pkg].replace("'", "").replace(".","").replace(" ", "_") - word = word.strip().lower() - if not need_dupes: - if word not in seen_pkgs and word not in seen_users: - seen_pkgs[word] = num_pkgs - num_pkgs += 1 - else: - if word not in seen_pkgs: - seen_pkgs[word] = num_pkgs - num_pkgs += 1 - -# free up contents memory -# -contents = None - -# developer/tu IDs -# -developers = [] -trustedusers = [] -has_devs = 0 -has_tus = 0 - -# Just let python throw the errors if any happen -# -out = open(sys.argv[1], "w", encoding="utf-8") -out.write("BEGIN;\n") - -# Begin by creating the User statements -# -log.debug("Creating SQL statements for users.") -for u in user_keys: - account_type = 1 # default to normal user - if not has_devs or not has_tus: - account_type = random.randrange(1, 4) - if account_type == 3 and not has_devs: - # this will be a dev account - # - developers.append(seen_users[u]) - if len(developers) >= MAX_DEVS * MAX_USERS: - has_devs = 1 - elif account_type == 2 and not has_tus: - # this will be a trusted user account - # - trustedusers.append(seen_users[u]) - if len(trustedusers) >= MAX_TUS * MAX_USERS: - has_tus = 1 - else: - # a normal user account - # - pass - - s = ("INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd)" - " VALUES (%d, %d, '%s', '%[email protected]', MD5('%s'));\n") - s = s % (seen_users[u], account_type, u, u, u) - out.write(s) - -log.debug("Number of developers: %d" % len(developers)) -log.debug("Number of trusted users: %d" % len(trustedusers)) -log.debug("Number of users: %d" % (MAX_USERS-len(developers)-len(trustedusers))) -log.debug("Number of packages: %d" % MAX_PKGS) - -log.debug("Gathering text from fortune file...") -fp = open(FORTUNE_FILE, "r", encoding="utf-8") -fortunes = fp.read().split("%\n") -fp.close() - -# Create the package statements -# -log.debug("Creating SQL statements for packages.") -count = 0 -for p in list(seen_pkgs.keys()): - NOW = int(time.time()) - if count % 2 == 0: - muid = developers[random.randrange(0,len(developers))] - else: - muid = trustedusers[random.randrange(0,len(trustedusers))] - if count % 20 == 0: # every so often, there are orphans... - muid = "NULL" - - uuid = genUID() # the submitter/user - - s = ("INSERT INTO Packages (ID, Name, Version, CategoryID," - " SubmittedTS, SubmitterUID, MaintainerUID) VALUES " - " (%d, '%s', '%s', %d, %d, %d, %s);\n") - s = s % (seen_pkgs[p], p, genVersion(), genCategory(), NOW, uuid, muid) - - out.write(s) - count += 1 - - # create random comments for this package - # - num_comments = random.randrange(PKG_CMNTS[0], PKG_CMNTS[1]) - for i in range(0, num_comments): - now = NOW + random.randrange(400, 86400*3) - s = ("INSERT INTO PackageComments (PackageID, UsersID," - " Comments, CommentTS) VALUES (%d, %d, '%s', %d);\n") - s = s % (seen_pkgs[p], genUID(), genFortune(), now) - out.write(s) - -# Cast votes -# -track_votes = {} -log.debug("Casting votes for packages.") -for u in user_keys: - num_votes = random.randrange(int(len(seen_pkgs)*VOTING[0]), - int(len(seen_pkgs)*VOTING[1])) - pkgvote = {} - for v in range(num_votes): - pkg = random.randrange(1, len(seen_pkgs) + 1) - if pkg not in pkgvote: - s = ("INSERT INTO PackageVotes (UsersID, PackageID)" - " VALUES (%d, %d);\n") - s = s % (seen_users[u], pkg) - pkgvote[pkg] = 1 - if pkg not in track_votes: - track_votes[pkg] = 0 - track_votes[pkg] += 1 - out.write(s) - -# Update statements for package votes -# -for p in list(track_votes.keys()): - s = "UPDATE Packages SET NumVotes = %d WHERE ID = %d;\n" - s = s % (track_votes[p], p) - out.write(s) - -# Create package dependencies and sources -# -log.debug("Creating statements for package depends/sources.") -for p in list(seen_pkgs.keys()): - num_deps = random.randrange(PKG_DEPS[0], PKG_DEPS[1]) - this_deps = {} - i = 0 - while i != num_deps: - dep = random.choice([k for k in seen_pkgs]) - if dep not in this_deps: - s = "INSERT INTO PackageDepends VALUES (%d, '%s', NULL);\n" - s = s % (seen_pkgs[p], dep) - out.write(s) - i += 1 - - num_sources = random.randrange(PKG_SRC[0], PKG_SRC[1]) - for i in range(num_sources): - src_file = user_keys[random.randrange(0, len(user_keys))] - src = "%s%s.%s/%s/%s-%s.tar.gz" % ( - RANDOM_URL[random.randrange(0,len(RANDOM_URL))], - p, RANDOM_TLDS[random.randrange(0,len(RANDOM_TLDS))], - RANDOM_LOCS[random.randrange(0,len(RANDOM_LOCS))], - src_file, genVersion()) - s = "INSERT INTO PackageSources VALUES (%d, '%s');\n" - s = s % (seen_pkgs[p], src) - out.write(s) - -# Create trusted user proposals -# -log.debug("Creating SQL statements for trusted user proposals.") -count=0 -for t in range(0, OPEN_PROPOSALS+CLOSE_PROPOSALS): - now = int(time.time()) - if count < CLOSE_PROPOSALS: - start = now - random.randrange(3600*24*7, 3600*24*21) - end = now - random.randrange(0, 3600*24*7) - else: - start = now - end = now + random.randrange(3600*24, 3600*24*7) - if count % 5 == 0: # Don't make the vote about anyone once in a while - user = "" - else: - user = user_keys[random.randrange(0,len(user_keys))] - suid = trustedusers[random.randrange(0,len(trustedusers))] - s = ("INSERT INTO TU_VoteInfo (Agenda, User, Submitted, End," - " SubmitterID) VALUES ('%s', '%s', %d, %d, %d);\n") - s = s % (genFortune(), user, start, end, suid) - out.write(s) - count += 1 - -# close output file -# -out.write("COMMIT;\n") -out.write("\n") -out.close() -log.debug("Done.") diff --git a/support/schema/reloadtestdb.sh b/support/schema/reloadtestdb.sh deleted file mode 100755 index f6a8ae2..0000000 --- a/support/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 aur/support/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.8.5.3
