summaryrefslogtreecommitdiffstats
path: root/schema
AgeCommit message (Collapse)AuthorFilesLines
2020-02-27Migrate the database schema to SQLAlchemyFrédéric Mangano-Tarumi3-456/+0
The new schema was generated with sqlacodegen and then manually adjusted to fit schema/aur-schema.sql faithfully, both in the organisation of the code and in the SQL generated by SQLAlchemy. Initializing the database now requires the new tool aurweb.initdb. References to aur-schema.sql have been updated and the old schema dropped. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2020-02-02Add support for backup email addressesLukas Fleischer1-0/+1
Support secondary email addresses that can be used to recover an account in case access to the primary email address is lost. Reset keys for an account are always sent to both the primary and the backup email address. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2019-11-23Store timestamp and user ID when closing requestsLukas Fleischer1-1/+4
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2019-01-14Quote MySql 8.0 reserved keywordsFlorian Pritz1-2/+2
Signed-off-by: Florian Pritz <bluewind@xinu.at> Signed-off-by: Eli Schwartz <eschwartz@archlinux.org>
2018-03-14schema/Makefile: Replace MySQL with SQLite in commentnodivbyzero1-0/+1
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2018-02-24Add rate limit support to APIFlorian Pritz1-0/+10
This allows us to prevent users from hammering the API every few seconds to check if any of their packages were updated. Real world users check as often as every 5 or 10 seconds. Signed-off-by: Florian Pritz <bluewind@xinu.at> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-08-25Do not hardcode path to the Python interpreterLukas Fleischer1-1/+1
Use `/usr/bin/env python3` instead of `/usr/bin/python3` in the shebang of Python scripts. This adds support for non-standard Python interpreter paths such as the paths used in virtualenv environments. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-05-02gendummydata.py: Fix NOT NULL constraint problemsMark Weiman1-3/+3
PackageBases.FlaggerComment and PackageComments.RenderedComment cannot be NULL and would cause problems in the output file for sqlite users. This patch adds empty strings ("") as values for these fields. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-04-30Add support for Terms of Service documentsLukas Fleischer1-0/+20
This allows for adding Terms of Service documents to the database that registered users need to accept before using the AUR. A revision field can be used to indicate whether a document was updated. If it is increased, all users are again asked to accept the new terms. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-04-23Render comments when storing them in the databaseLukas Fleischer1-0/+1
Instead of converting package comments from plain text to HTML code when they are displayed, do the conversion when the comment is posted and store the rendered result in the database. The conversion itself is done by a Python script which uses Bleach for sanitizing the text. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-04-19Store dependency descriptions in a separate columnLukas Fleischer1-0/+1
Split optional dependency descriptions from dependency names before storing them in the database and use a separate column to store the descriptions. This allows us to simplify and optimize the SQL queries in pkg_dependencies() as well as pkg_required(). Suggested-by: Florian Pritz <bluewind@xinu.at> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-04-16schema: Fix invalid NULL on primary keyFlorian Pritz1-1/+1
>From the mysql 5.7 breaking change page: Columns in a PRIMARY KEY must be NOT NULL, but if declared explicitly as NULL produced no error. Now an error occurs. For example, a statement such as CREATE TABLE t (i INT NULL PRIMARY KEY) is rejected. The same occurs for similar ALTER TABLE statements. (Bug #13995622, Bug #66987, Bug #15967545, Bug #16545198) References: http://stackoverflow.com/a/22314073 Signed-off-by: Florian Pritz <bluewind@xinu.at> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-04-16schema: Remove invalid default values for TEXT columnsFlorian Pritz1-4/+4
When running in strict mode, mysql throws an error upon encountering these definitions since they are not supported. References: https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html Signed-off-by: Florian Pritz <bluewind@xinu.at> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-03-01gendummydata.py: Fix to make it less db specificMark Weiman1-6/+9
Sqlite3 does not support the MD5 function like MySQL does, instead of the database program hash the passwords, have Python's hashlib module do it instead. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-03-01Fix quote for Source column defaultMark Weiman1-1/+1
Signed-off-by: Mark Weiman <mark.weiman@markzz.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-03-01Add a Makefile to build an SQLite-compatible schemaLukas Fleischer1-0/+11
Allow for automatically converting the schema into a schema that works with SQLite by running `make` from the schema/ subdirectory. Use the new Makefile in the test suite. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-03-01aur-schema.sql: Do not recreate the databaseLukas Fleischer1-3/+0
Modify the schema such that it only creates the necessary tables, indices and predefined data. This makes it easier to import the schema into a database with a name other than "AUR". Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-02-24Use bcrypt to hash passwordsLukas Fleischer1-1/+1
Replace the default hash function used for storing passwords by password_hash() which internally uses bcrypt. Legacy MD5 hashes are still supported and are immediately converted to the new format when a user logs in. Since big parts of the authentication system needed to be rewritten in this context, this patch also includes some simplification and refactoring of all code related to password checking and resetting. Fixes FS#52297. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-01-25Store banned IP addresses as plain textLukas Fleischer1-1/+1
Inspired by commit 32c8d0c (Store last login address as plain text, 2016-03-13). Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-01-25git-serve: Save last SSH login date and IP addressLukas Fleischer1-0/+2
In addition to logging the last login date and IP address on the web interface, store the time stamp and IP address of the last SSH login in the database. This simplifies user banning if one of the new SSH interface features, such as the voting mechanism implemented in 7ee2fdd (git-serve: Add support for (un-)voting, 2017-01-23), is abused. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2017-01-20Add user set timezonesMark Weiman1-0/+1
Currently, aurweb displays all dates and times in UTC time. This patch adds a capability for each logged in user to set their preferred timezone. Implements FS#48729. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-10-11Make URL columns 8000 characters wideLukas Fleischer1-2/+2
According to RFC 7230, URLs can be up too 8000 characters long. Resize all URL fields accordingly. Also, add a test to verify that URLs with more than 8000 characters are rejected by the update hook. Reported-by: Andreas Linz <klingt.net@gmail.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-08-05Cleanup database schemaLukas Fleischer1-37/+30
* Remove test accounts. * Create indices using CREATE INDEX. * Always use INTEGER UNSIGNED for IDs. * Always use BIGINT UNSIGNED for timestamps. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-07-24Make LangPreference DB field wide enough to fit es_419Johannes Löthberg1-1/+1
The language code for Latin American Spanish is es_419, which is longer than the 5 characters previously allowed. Signed-off-by: Johannes Löthberg <johannes@kyriasis.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-06-25Add missing database and account fieldsJohannes Löthberg1-0/+1
Commits 6ec4a35 (Send notifications when changing ownership, 2016-02-21) and e3670ef (Add a homepage field to accounts, 2016-06-02) forgot to change some usages of display_account_form() and process_account_form() to account for the new parameter. The former also forgot to add the new column to the database schema. Signed-off-by: Johannes Löthberg <johannes@kyriasis.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-06-08Add a homepage field to accountsLukas Fleischer1-0/+1
Allow users to add a link to their homepage to their profile. Implements FS#22774. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-05-18Add repository information to official providersLukas Fleischer1-0/+1
When updating the list of packages provided by the official repositories, also save the repository names.
2016-03-15Resize the LastLoginIPAddress columnLukas Fleischer1-1/+1
Make sure that all valid IPv6 addresses fit into the LastLoginIPAddress field. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-03-13Store last login address as plain textLukas Fleischer1-1/+1
Directly store the information contained in $_SERVER['REMOTE_ADDR'] instead of using ip2long() which does not support IPv6 addresses. Note that the LastLoginIPAddress field is designed to be used by the administrator on rare occasions only (e.g. to fight spam) and is not displayed anywhere. Fixes FS#48557. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-02-07Add support for package update notificationsLukas Fleischer1-0/+1
Introduce a new notification option to receive notifications when a new commit is pushed to a package repository. Implements FS#30109. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-02-07Add global comment notification settingLukas Fleischer1-0/+1
Add a configuration option to the account edit page that allows for globally enabling/disabling package base comment notifications. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2016-02-07Rename the CommentNotify table to PackageNotificationsLukas Fleischer1-3/+3
As a preparatory step to adding support for package notifications on events other than comments, rename the database table accordingly. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-12-15Change FlaggerComment to TEXTMark Weiman1-1/+1
Makes FlaggerComments a TEXT field to be more consistent with package comments. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-12-13Store current date and time when deleting commentsLukas Fleischer1-0/+1
Instead of modifying EditedTS when a comment is deleted, use a separate field DelTS. Use this field to determine whether a comment has been deleted, instead of checking DelUsersID which might be unset when the corresponding user is deleted. Fixes FS#47362. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-12-13Save comment when closing requestsLukas Fleischer1-0/+1
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-12-12Implement capability to pin comments above othersMark Weiman1-0/+1
Adds capability to pin comments before others. Implements FS#10863. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-11-12Shorten Email column to 254 charactersStefan Auditor1-1/+1
Using unique indexes on VARCHAR fields with a character count of more than 255 produces an error in MySQL with InnoDB tables and UTF-8 encoding. Also, as per https://www.rfc-editor.org/errata_search.php?eid=1690, the maximum length for email addresses is limited to 254 characters. Fixes FS#47038. Signed-off-by: Stefan Auditor <stefan.auditor@erdfisch.de> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-10-25Support long email addressesLukas Fleischer1-1/+1
According to RFC 3696 (and the associated errata), an email address can be up to 256 characters long. Change the database field and the length limit on all input fields accordingly. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-10-21Track providers in the official repositoriesLukas Fleischer1-0/+10
Maintain a list of virtual provisions of packages from the official binary package repositories. The list can be updated using the aurblup script, e.g. via a cronjob. This allows for adding proper links to package dependencies: If an AUR package depends on a package from the official repositories (or on a name provided by a package from the official repositories), add a link to the corresponding archweb package details page. If an AUR package depends on another AUR package (or on a name provided by another AUR package), add a link to the corresponding aurweb package details page. Otherwise, just display the name and do not add a link at all. Fixes FS#46549. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-09-20Add option to hide one's email addressMarcel Korpel1-0/+1
Implements FS#42343. Signed-off-by: Marcel Korpel <marcel.korpel@gmail.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-09-19Fix type of FlaggerUID in table PackageBasesMarcel Korpel1-1/+1
Signed-off-by: Marcel Korpel <marcel.korpel@gmail.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-09-11Require comments when flagging packages out-of-dateLukas Fleischer1-0/+1
Implements FS#42827. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-08-30Remember user ID when flagging package basesLukas Fleischer1-0/+2
Add a new FlaggerUID field to the database and use it to store the user ID of the account who recently flagged a package out-of-date. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-08-08Support comment editing in the backendMarcel Korpel1-0/+3
Create two new actions, do_AddComment and do_EditComment. When editing or deleting a comment, a timestamp is added. Signed-off-by: Marcel Korpel <marcel.korpel@gmail.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-06-27Add support for multiple SSH public keysLukas Fleischer1-1/+11
Attaching more than one SSH public key to the same account is useful, e.g. if one uses different machines to access the AUR SSH interface. Multiple keys can now be specified by adding multiple lines to the text area on the account edit form. Implements FS#45469. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> Acked-by: Leonidas Spyropoulos <artafinde@gmail.com>
2015-06-20Make url and pkgdesc fields optionalJohannes Löthberg1-2/+2
The url and pkgdesc PKGBUILD variables are optional, so they should be in the AUR as well. Signed-off-by: Johannes Löthberg <johannes@kyriasis.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-06-17reloadtestdb.sh: Use aurweb as main directoryMarcel Korpel1-1/+1
By default, aurweb is cloned into the aurweb directory nowadays. Signed-off-by: Marcel Korpel <marcel.korpel@gmail.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-06-16gendummydata.py: Remove CategoryID from dummy dataMarcel Korpel1-3/+3
Signed-off-by: Marcel Korpel <marcel.korpel@gmail.com> Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-06-15Store 10 significant digits of the popularity scoreLukas Fleischer1-1/+1
Even if we only display two digits after the decimal point, storing more digits internally allows us to order the search results more accurately. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
2015-06-14Adding PackagerUID to the generated dummy dataLeonidas Spyropoulos1-2/+4
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-06-05 00:14:41 +0200