summaryrefslogtreecommitdiffstats
path: root/support/schema
AgeCommit message (Collapse)AuthorFilesLines
2011-04-13Make "gendummydata.py" Python 3 compliant.Lukas Fleischer2-15/+15
* Transform into valid Python 3.x code using 2to3. * Change shebang from "/usr/bin/python2" to "/usr/bin/python3". * Invoke with "python3" instead of "python2" in "reloadtestdb.sh". Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-04-12Fix dependency generation in "gendummydata.py".Lukas Fleischer1-2/+2
Package dependencies are no longer stored as references to the "Packages" table but kept directly in "PackageDepends", so the dummy data generation script should be fixed to create package names instead of references, also. Regression introduced in commit 7c91c592458b7532806ef75fe09146f82f085f3b. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-04-12Fix syntax error in "CREATE TABLE" statement in the SQL schema.Lukas Fleischer1-1/+1
Regression introduced in commit 7c91c592458b7532806ef75fe09146f82f085f3b. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-04-12wrap long SQL commands to improve formatting and readabilityelij1-11/+23
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-04-12replace print statements with logging module in gendummydataelij1-40/+22
use the logging module instead of writing directly to stderr this makes the code cleaner as it removes the numerous tests for the value of DBUG, yet allows devs to control the level of output verbosity. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-04-12remove mysql dependency from gendummydataelij1-41/+3
- remove need to use mysql for escaping the sql -- removing single quote should be enough - instead of using sql to fetch categories from a live database, simply consider categories an integer range, specified to the size of that in the aur-schema. Lukas: Add "CATEGORIES_COUNT" initialization. Fix random number range used in genCategory() (AUTO_INCREMENT columns are 1-based by default, not 0-based). Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-04-03Remove Dummy Package conceptDan McGee1-4/+1
Instead, we just store dependencies directly in the PackageDepends table. Since we don't use this info anywhere besides the package details page, there is little value in precalculating what is in the AUR vs. what is not. An upgrade path is provided via several SQL statements in the UPGRADING document. There should be no user-visible change from this, but the DB schema gets a bit more sane and we no longer have loads of junk packages in our tables that are never shown to the end user. This should also help the MySQL query planner in several cases as we no longer have to be careful to exclude dummy packages on every query. Signed-off-by: Dan McGee <dan@archlinux.org> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-03-30gendummydata.py: Do not touch output file until we need it.Lukas Fleischer1-5/+5
Ensures there's no leftover (empty) file if something during initialization fails. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-03-04Ensure users can be deleted when foreign keys are presentDan McGee1-2/+3
This change is necessary to prevent this: mysql> delete from Users where ID = 112; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`aur`.`Packages`, CONSTRAINT `Packages_ibfk_2` FOREIGN KEY (`SubmitterUID`) REFERENCES `Users` (`ID`) ON DELETE NO ACTION) As a bonus, due to foreign keys, orphaning of packages will be automatic. Signed-off-by: Dan McGee <dan@archlinux.org> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-03-04Allow DB connection values to come from the environmentDan McGee3-21/+33
Stop hardcoding everything everywhere for those of us that don't use the localhost/aur/aur/AUR setup. Also allow for the dummy data to be created in the reload script if it does not exist. Finally, remove two assumptions that the AUR database already exists. Signed-off-by: Dan McGee <dan@archlinux.org> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-03-04Use a single transaction to write dummy dataDan McGee1-0/+2
This is immensely faster when using InnoDB since we don't need to sync after each and every INSERT statement. Signed-off-by: Dan McGee <dan@archlinux.org> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-28Remove database recreation code from "support/schema/reloadtestdb.sh".Lukas Fleischer1-6/+0
Database is being dropped and recreated in the schema, so there's no need to do this in "reloadtestdb.sh" as well. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-28Specify utf8 in database schema.Loui Chang1-4/+4
Signed-off-by: Loui Chang <louipc.ist@gmail.com> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-27Fix some minor bugs in "support/schema/gendummydata.py".Lukas Fleischer1-2/+2
The dummy data generation script used to create wrong package IDs for both "PackageVotes" and "PackageDepends" tables which led to errors when reloading the test data (constraints failed). This is fixed by no longer creating entries with zero ("0") package IDs. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-27Add "ENGINE = InnoDB" to "CREATE TABLE" statements in the SQL schema.Lukas Fleischer1-13/+13
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-27Define "Packages.SubmitterUID" and "Packages.MaintainerUID" as "NULL".Lukas Fleischer2-3/+8
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-27Define "PackageComments.DelUsersID" as "NULL".Lukas Fleischer1-1/+1
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-26Add missing foreign keys and constraints to the DB.Lukas Fleischer1-5/+11
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-11Use VARCHAR instead of CHAR in "PackageBlacklist" table.Lukas Fleischer1-1/+1
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-11Add a package name blacklist.Lukas Fleischer1-0/+9
Can be used to blacklist package names for normal users. TUs and developers are not affected. This is especially useful if used together with a cron job that updates the blacklist periodically, e.g. to reject packages which are available in the binary repos (FS#12902). Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-10Remove unused fulltext index from "Packages" table.Lukas Fleischer1-1/+0
Drop fulltext indexes, which prevent the use of InnoDB, from "Packages" table. All search routines use "LIKE" patterns, so fulltext search has actually never been used. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-09Use VARCHAR instead of CHAR where appropriate.Lukas Fleischer1-14/+14
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-01Drop "PackageContents" table and references.Lukas Fleischer2-39/+0
We don't even touch source tarballs anymore - except for extracting the PKGBUILD, so this is no longer needed. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-01Remove "FSPath" column from "Packages" table.Lukas Fleischer1-1/+0
This field is not used anymore, so drop it from the table and remove all references. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-02-01Drop PackageLocations table and referencesDan McGee2-97/+29
We don't need this anymore since all packages managed here are well...managed here. Rip out all of the places we were using this field, many of which depended on the magic value '2' anyway. On the display side of things, we had a column that was always showing 'unsupported' that is now gone, and you can no longer sort by this column. Signed-off-by: Dan McGee <dan@archlinux.org> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-01-24Use "python2" shebang for "newpackage-notify" and "gendummydata.py".Lukas Fleischer1-1/+1
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2011-01-24Build URLs from package names (fixes FS#15308, FS#19327).Lukas Fleischer2-15/+8
Drop the "URLPath" field from the "Packages" table, build URLs from package names instead. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
2010-11-10Add timestamp when a package is flagged out-of-date (FS#20848).Lukas Fleischer1-2/+1
Signed-off-by: Loui Chang <louipc.ist@gmail.com> - resolve conflict and omit i18n changes.
2010-04-17Support for storing salted passwordsDenis1-0/+1
To upgrade existing databases: ALTER TABLE Users ADD Salt CHAR(32) NOT NULL DEFAULT ''; Signed-off-by: Loui Chang <louipc.ist@gmail.com>
2009-11-29aur-schema.sql: Expand LangPreference field to allow for longer lang codes.Loui Chang1-1/+1
To upgrade existing databases: ALTER TABLE Users MODIFY LangPreference CHAR(5) NOT NULL DEFAULT 'en'; Signed-off-by: Loui Chang <louipc.ist@gmail.com>
2009-11-13Implement 'Password Reset' facility (FS#3061)Evangelos Foutras1-1/+1
This works by adding a new field to the 'Users' table called 'ResetKey', which is a 32 characters long, random string. When the user requests a password reset, a new 'reset key' is generated and sent to the user's e-mail address in the form of a link in the following format: http://aur.archlinux.org/passreset.php?resetkey=<reset key> When the above link is followed, the user is presented with a form to verify his/her e-mail address and specify the new desired password. If the e-mail address matches the reset key in the database, the new password is assigned to the account. If there is an error, a relevant message is displayed and the user is prompted to re-enter the required information. Upon successful completion of this procedure, the ResetKey field in the database is blanked and the specific key cannot be reused. One SQL query is needed to add the ResetKey field to the 'Users' table: ALTER TABLE `Users` ADD `ResetKey` CHAR(32) NOT NULL DEFAULT ''; Signed-off-by: Loui Chang <louipc.ist@gmail.com>
2009-06-18Add multicolumn indexes on votes and notifications tablesDan McGee1-1/+2
To put a long story short, when we do joins on these tables in our pkg_search_page() function, we always join on both the user ID and package ID columns. By creating multicolumn indices, we can always get the exact row we are looking for in the table. The benefits of adding a unique index should also speak for themselves, as we previously did not have this on either of these tables. This is part one of a two-part series to address the fact that this query was often showing up in our slow query logs. Signed-off-by: Dan McGee <dan@archlinux.org> Signed-off-by: Loui Chang <louipc.ist@gmail.com>
2008-03-27Remove references to AURMaintainerUIDCallan Barrett2-6/+3
This (should) get rid of anything to do with the unused column AURMaintainerUID in the scripts and schema files Signed-off-by: Callan Barrett <wizzomafizzo@gmail.com> Signed-off-by: Simo Leone <simo@archlinux.org>
2008-03-23Generate valid dummy data entriesLoui Chang1-12/+5
Signed-off-by: Loui Chang <louipc.ist@gmail.com> Signed-off-by: Simo Leone <simo@archlinux.org>
2008-03-23Make all references to AUR database consistentLoui Chang1-3/+3
Signed-off-by: Loui Chang <louipc.ist@gmail.com> Signed-off-by: Simo Leone <simo@archlinux.org>
2008-01-20Bug in uploading PKGBUILDs and aur schemaCallan Barrett1-2/+2
This fixes a bug where TUs and devs couldn't upload packages because the query would have an extra column in it to mark the pkgbuild safe automatically, guessing it got missed when the safe flags were removed. Also fixes a screw up I made with the schema file when I added the tables for the voting app. Signed-off-by: Callan Barrett <wizzomafizzo@gmail.com>
2008-01-20Add support for variable length votes in voting applicationCallan Barrett1-0/+1
Signed-off-by: Callan Barrett <wizzomafizzo@gmail.com>
2008-01-20AUR Voting ApplicationCallan Barrett1-0/+21
Added support for TU voting through AUR Signed-off-by: Callan Barrett <wizzomafizzo@gmail.com>
2008-01-20Fix for safe-removal.eliott1-2/+0
2008-01-20Hand diff of simo's patch to remove flag safe functionality.eliott1-2/+0
Simo's original commit text: The idea of safe flagging is unclear, poorly named, misunderstood, and not even used. At the time this patch was created, less than a third of the packages in unsupported were flagged safe, and less than a tenth of users knew how to interpret it. The safe flag has been replaced by a disclaimer on the main page.
2008-01-20Remove dummy data from GITDan McGee1-0/+0
There is no need to check in dummy data if it is available by running the script that sat right next to it in the repo. Doh! Signed-off-by: Dan McGee <dan@archlinux.org>
2008-01-19Added AurJSON code.eliott1-0/+1
Added a JSON interface to the aur. This should make it easier for developers to integrate command line utilities and poll against the AUR itself.
2007-09-17committed old schema change from old versionpjmattal1-1/+1
2007-02-01Added the DepCondition field to the table PackageDepends.dsa1-0/+1
2006-08-22Applied license patch from dsa (closes #4085).swiergot1-0/+1
2006-08-10of course, max length for Description must be 255, not 256pjmattal1-1/+1
2006-08-03upped length on Description in Package table to 256 from 128pjmattal1-1/+1
as per discussions with simo and dsa
2005-07-29committed schema change for swiergot's checking patchpjmattal1-0/+4
2005-06-08SQL CHANGES: New table CommentNotify with fields:simo1-0/+9
PkgID UserID This implements emailing comment notifications, including a user option to enable/disable it on the package page. It uses php's mail() function to do it and sends to everyone on the notify list as BCC. This needs some more testing before public consumption.
2005-05-08Added ModifiedTS to Packages databasesimo1-0/+1
Implemented ModifiedTS such that: -ModifiedTS changed ONLY when package updated -SubmittedTS changed ONLY when new package Also made DummyPkg fixups in tupkgupdate