February 2011 - Aur-dev - lists.archlinux.org

[aur-dev] Old, unapplied patches
by PyroPeter 09 Feb '11

09 Feb '11

Hello AUR developers, there is a great backlog of patches that work well, but are not applied. Among them are: * The right-to-left fixes for the hebrew translation * Making links in comments clickable * Deletion through the web interface It would be great if you could take some time to review and apply them. Regards, PyroPeter -- freenode/pyropeter ETAOIN SHRDLU

3 5

[aur-dev] [PATCH] Remove fulltext index from "Packages".
by Lukas Fleischer 09 Feb '11

09 Feb '11

Drop unused fulltext indexes from "Packages" table (all search routines use "LIKE" patterns) which prevent the use of InnoDB. Signed-off-by: Lukas Fleischer <archlinux(a)cryptocrack.de> --- As far as I can see, this is the only thing that prevents us from using InnoDB as the storage engine for all tables. Correct me if I missed something. UPGRADING | 10 +++++++++- support/schema/aur-schema.sql | 1 - 2 files changed, 9 insertions(+), 2 deletions(-) diff --git a/UPGRADING b/UPGRADING index 661a6c3..c649985 100644 --- a/UPGRADING +++ b/UPGRADING @@ -29,7 +29,15 @@ ALTER TABLE TU_VoteInfo MODIFY User VARCHAR(32) collate latin1_general_ci NOT NULL; ---- -2. You will need to update all packages which are stored in the incoming dir as +2. Drop all fulltext indexes from the "Packages" table: + +Please do this with care. `ALTER TABLE Packages DROP INDEX Name;` will work in +most cases but might remove the wrong index if your indexes have been created +in a non-standard order (e.g. during some update process). You'd better run +`SHOW INDEX FROM Packages;` before to ensure that your setup doesn't use a +different naming. + +3. You will need to update all packages which are stored in the incoming dir as in 1.8.0, source tarballs are no longer extracted automatically and PKGBUILDs are from now on located in the same subdirectories as the tarballs themselves. The following script will do the conversion automatically when being run inside diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index cddf00e..d37e1b2 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -106,7 +106,6 @@ CREATE TABLE Packages ( ModifiedTS BIGINT UNSIGNED NOT NULL, SubmitterUID INTEGER UNSIGNED NOT NULL DEFAULT 0, -- who submitted it? MaintainerUID INTEGER UNSIGNED NOT NULL DEFAULT 0, -- User - FULLTEXT (Name,Description), PRIMARY KEY (ID), UNIQUE (Name), INDEX (CategoryID), -- 1.7.4

1 0

[aur-dev] [PATCH] Use VARCHAR instead of CHAR where appropriate.
by Lukas Fleischer 09 Feb '11

09 Feb '11

Signed-off-by: Lukas Fleischer <archlinux(a)cryptocrack.de> --- UPGRADING | 16 ++++++++++++++++ support/schema/aur-schema.sql | 28 ++++++++++++++-------------- 2 files changed, 30 insertions(+), 14 deletions(-) diff --git a/UPGRADING b/UPGRADING index c067af5..661a6c3 100644 --- a/UPGRADING +++ b/UPGRADING @@ -11,6 +11,22 @@ ALTER TABLE Packages ADD OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL; UPDATE Packages SET OutOfDateTS = UNIX_TIMESTAMP() WHERE OutOfDate = 1; ALTER TABLE Packages DROP OutOfDate, DROP FSPath, DROP URLPath, DROP LocationID; DROP TABLE PackageLocations, PackageContents; +ALTER TABLE AccountTypes MODIFY AccountType VARCHAR(32) NOT NULL DEFAULT ''; +ALTER TABLE Users MODIFY Username VARCHAR(32) NOT NULL, + MODIFY Email VARCHAR(64) NOT NULL, + MODIFY RealName VARCHAR(64) NOT NULL DEFAULT '', + MODIFY LangPreference VARCHAR(5) NOT NULL DEFAULT 'en', + MODIFY IRCNick VARCHAR(32) NOT NULL DEFAULT ''; +ALTER TABLE PackageCategories MODIFY Category VARCHAR(32) NOT NULL; +ALTER TABLE Packages MODIFY Name VARCHAR(64) NOT NULL, + MODIFY Version VARCHAR(32) NOT NULL DEFAULT '', + MODIFY Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package", + MODIFY URL VARCHAR(255) NOT NULL DEFAULT "http://www.archlinux.org", + MODIFY License VARCHAR(40) NOT NULL DEFAULT ''; +ALTER TABLE PackageSources + MODIFY Source VARCHAR(255) NOT NULL DEFAULT "/dev/null"; +ALTER TABLE TU_VoteInfo + MODIFY User VARCHAR(32) collate latin1_general_ci NOT NULL; ---- 2. You will need to update all packages which are stored in the incoming dir as diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index cb5ea42..cddf00e 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -9,7 +9,7 @@ USE AUR; -- CREATE TABLE AccountTypes ( ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, - AccountType char(32) NOT NULL DEFAULT '', + AccountType VARCHAR(32) NOT NULL DEFAULT '', PRIMARY KEY (ID) ); INSERT INTO AccountTypes (ID, AccountType) VALUES (1, 'User'); @@ -23,14 +23,14 @@ 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 CHAR(32) NOT NULL, - Email CHAR(64) NOT NULL, + 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 CHAR(64) NOT NULL DEFAULT '', - LangPreference CHAR(5) NOT NULL DEFAULT 'en', - IRCNick CHAR(32) NOT NULL DEFAULT '', + RealName VARCHAR(64) NOT NULL DEFAULT '', + LangPreference VARCHAR(5) NOT NULL DEFAULT 'en', + IRCNick VARCHAR(32) NOT NULL DEFAULT '', LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, NewPkgNotify TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (ID), @@ -66,7 +66,7 @@ CREATE TABLE Sessions ( -- CREATE TABLE PackageCategories ( ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, - Category CHAR(32) NOT NULL, + Category VARCHAR(32) NOT NULL, PRIMARY KEY (ID) ); INSERT INTO PackageCategories (Category) VALUES ('none'); @@ -93,13 +93,13 @@ INSERT INTO PackageCategories (Category) VALUES ('xfce'); -- CREATE TABLE Packages ( ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name CHAR(64) NOT NULL, - Version CHAR(32) NOT NULL DEFAULT '', + Name VARCHAR(64) NOT NULL, + Version VARCHAR(32) NOT NULL DEFAULT '', CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1, - Description CHAR(255) NOT NULL DEFAULT "An Arch Package", - URL CHAR(255) NOT NULL DEFAULT "http://www.archlinux.org", + Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package", + URL VARCHAR(255) NOT NULL DEFAULT "http://www.archlinux.org", DummyPkg TINYINT UNSIGNED NOT NULL DEFAULT 0, -- 1=>dummy - License CHAR(40) NOT NULL DEFAULT '', + License VARCHAR(40) NOT NULL DEFAULT '', NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0, OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL, SubmittedTS BIGINT UNSIGNED NOT NULL, @@ -134,7 +134,7 @@ CREATE TABLE PackageDepends ( -- CREATE TABLE PackageSources ( PackageID INTEGER UNSIGNED NOT NULL, - Source CHAR(255) NOT NULL DEFAULT "/dev/null", + Source VARCHAR(255) NOT NULL DEFAULT "/dev/null", INDEX (PackageID) ); @@ -183,7 +183,7 @@ CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID); CREATE TABLE IF NOT EXISTS TU_VoteInfo ( ID int(10) unsigned NOT NULL auto_increment, Agenda text collate latin1_general_ci NOT NULL, - User char(32) collate latin1_general_ci NOT NULL, + User VARCHAR(32) collate latin1_general_ci NOT NULL, Submitted bigint(20) unsigned NOT NULL, End bigint(20) unsigned NOT NULL, SubmitterID int(10) unsigned NOT NULL, -- 1.7.4

3 3

[aur-dev] Question about sanitized data dumps
by elij 09 Feb '11

09 Feb '11

I have been [fooling around][1] with an aur-rpc reimplementation in ruby+redis with sphinx as the search engine. Loui [mentioned][2] he had been thinking about providing a dump of the existing database or something, to users who are interested in developing features and/or replacements. If so, and assuming the dump is sanitized in some way (remove real user email addresses, etc), I would certainly be interested. It would be nice to get some real data to play with. Is there any more information currently available on this? Thanks. [1]: https://github.com/cactus/spew-ruby [2]: http://mailman.archlinux.org/pipermail/aur-general/2011-January/013323.html

2 3

[aur-dev] Moving packages to Community
by Martti Kühne 07 Feb '11

07 Feb '11

Hello official and inofficial maintainers and AUR devs. Following the discussion in aur-general, I think some automation could solve many issues at once here. Justin Davis' community-blacklist patch is an approach in about the right direction.... although in relation to the discussion concerning these prevailing [more-official repo] adoption issues, it seems that it does not yet go far enough at the moment. There should be a redirection or at least a [we're-now-official] error page showing sort of "congratulations and sorry for the late notice" to the yet uninformed aur maintainer, if his package is not reachable any more because it was adopted in said [more-official repo] - along with this could be displayed the comments of the former package. As well, it could just become invisible in the package's search, and marked with a "I saw this" button in the former AUR maintainer's package list. When that button is clicked, the package would sink quietly into the official repo without anyone needing to complain. The only hurdle remaining is, that a package isn't up for deletion any more when it gets adopted to [more-official repo], because that could just be automated by the repo's backend maybe? I'm probably going to download the aur source inclusively Justin's approach to look into this a bit further. Even though my development skills aren't exactly in line with such a task, and if someone feels more confident, one may look into it h{im,er}self. I'd like to hear your comments on this well. cheers! mar77i

1 0

[aur-dev] AUR slow queries, stale database columns, etc.
by Dan McGee 04 Feb '11

04 Feb '11

LocationID, DummyPkg- are these used anymore on the Packages table? If not, getting rid of these would do wonders toward actually allowing MySQL to use indexes for some queries, which it has been unable to do for a long time. I've pasted some of the slow query log analysis below from stuff this month, it is pretty obvious where work should be focused. Killing these two fields and then replacing the SQL_CALC_FOUND_ROWS with a second count query should take care of nearly every slow query that currently happens. Also potentially worth attacking is the use of GREATEST(SubmittedTS,ModifiedTS) - since the wonderful MySQL has no functional indexes using this in a query as an order by clause results in full table scans. -Dan $ mysqldumpslow -s c sigurd-slow.log | head -n 100 Reading mysql slow query log from sigurd-slow.log Count: 3550 Time=2.08s (7390s) Lock=0.00s (3s) Rows=26.7 (94644), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY NumVotes DESC, Name ASC, CategoryID DESC LIMIT N, N Count: 2275 Time=1.38s (3143s) Lock=0.00s (0s) Rows=25.4 (57680), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS CommentNotify.UserID AS Notify, PackageVotes.UsersID AS Voted, Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = N) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = N) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY NumVotes DESC, Name ASC, CategoryID DESC LIMIT N, N Count: 350 Time=1.62s (566s) Lock=0.01s (1s) Rows=27.4 (9591), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY NumVotes ASC, Name ASC, CategoryID DESC LIMIT N, N Count: 307 Time=1.20s (368s) Lock=0.00s (0s) Rows=65.8 (20200), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS CommentNotify.UserID AS Notify, PackageVotes.UsersID AS Voted, Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = N) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = N) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY GREATEST(SubmittedTS,ModifiedTS) DESC, Name ASC, LocationID ASC LIMIT N, N Count: 220 Time=1.76s (386s) Lock=0.00s (0s) Rows=29.9 (6585), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY Name DESC, LocationID ASC, CategoryID DESC LIMIT N, N Count: 218 Time=1.31s (286s) Lock=0.00s (0s) Rows=28.1 (6122), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY Name ASC, LocationID ASC, CategoryID DESC LIMIT N, N Count: 187 Time=2.21s (413s) Lock=0.00s (0s) Rows=26.9 (5034), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY LocationID ASC, Name ASC, CategoryID DESC LIMIT N, N

3 6