From 1fe14899aa808f50144162d6b14fc583873d08fc Mon Sep 17 00:00:00 2001
From: Lukas Fleischer <archlinux@cryptocrack.de>
Date: Sun, 29 Jun 2014 23:09:35 +0200
Subject: Split UPGRADING

Split the upgrade instructions into several files, one file per version
in order to keep them small, readable and to avoid merge conflicts.

Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
---
 upgrading/1.2.10.txt        |   1 +
 upgrading/1.3.0.txt         |   2 +
 upgrading/1.5.2.txt         |  22 ++++
 upgrading/1.5.3.txt         |   2 +
 upgrading/1.7.0.txt         |   3 +
 upgrading/1.8.0.txt         |  80 ++++++++++++++
 upgrading/1.8.1.txt         |  56 ++++++++++
 upgrading/1.8.2.txt         |  18 ++++
 upgrading/1.9.0.txt         |  36 +++++++
 upgrading/1.9.1.txt         |   3 +
 upgrading/2.0.0.txt         |  14 +++
 upgrading/2.1.0.txt         |   2 +
 upgrading/2.2.0.txt         |  16 +++
 upgrading/2.3.0.txt         |  22 ++++
 upgrading/3.0.0.txt         | 249 ++++++++++++++++++++++++++++++++++++++++++++
 upgrading/3.1.0.txt         |  13 +++
 upgrading/3.2.0.txt         |  30 ++++++
 upgrading/longerpkgname.txt |   1 +
 18 files changed, 570 insertions(+)
 create mode 100644 upgrading/1.2.10.txt
 create mode 100644 upgrading/1.3.0.txt
 create mode 100644 upgrading/1.5.2.txt
 create mode 100644 upgrading/1.5.3.txt
 create mode 100644 upgrading/1.7.0.txt
 create mode 100644 upgrading/1.8.0.txt
 create mode 100644 upgrading/1.8.1.txt
 create mode 100644 upgrading/1.8.2.txt
 create mode 100644 upgrading/1.9.0.txt
 create mode 100644 upgrading/1.9.1.txt
 create mode 100644 upgrading/2.0.0.txt
 create mode 100644 upgrading/2.1.0.txt
 create mode 100644 upgrading/2.2.0.txt
 create mode 100644 upgrading/2.3.0.txt
 create mode 100644 upgrading/3.0.0.txt
 create mode 100644 upgrading/3.1.0.txt
 create mode 100644 upgrading/3.2.0.txt
 create mode 100644 upgrading/longerpkgname.txt

(limited to 'upgrading')

diff --git a/upgrading/1.2.10.txt b/upgrading/1.2.10.txt
new file mode 100644
index 0000000..1b94011
--- /dev/null
+++ b/upgrading/1.2.10.txt
@@ -0,0 +1 @@
+ALTER TABLE Packages MODIFY Description CHAR(255) NOT NULL DEFAULT "An Arch Package";
diff --git a/upgrading/1.3.0.txt b/upgrading/1.3.0.txt
new file mode 100644
index 0000000..b95496a
--- /dev/null
+++ b/upgrading/1.3.0.txt
@@ -0,0 +1,2 @@
+ALTER TABLE PackageDepends ADD COLUMN DepCondition VARCHAR(20) AFTER DepPkgID;
+ALTER TABLE Packages ADD License CHAR(40) NOT NULL DEFAULT '';
diff --git a/upgrading/1.5.2.txt b/upgrading/1.5.2.txt
new file mode 100644
index 0000000..424daac
--- /dev/null
+++ b/upgrading/1.5.2.txt
@@ -0,0 +1,22 @@
+1. Ensure Pear and File/Find.php are in the path. See web/README.txt.
+
+2. Update your running copy of support/scripts/newpackage-notify.
+
+3. Run this in web/lib:
+<?php
+
+# Run the script from within lib
+include('config.inc');
+include('aur.inc');
+
+$query = "UPDATE Packages SET " .
+         "FSPath = CONCAT('" . INCOMING_DIR . "', Name, '/', Name,
+'.tar.gz'), " .
+         "URLPath = CONCAT('" . URL_DIR . "', Name, '/', Name, '.tar.gz') " .
+         "WHERE DummyPKG = 0 AND LocationID = 2;";
+
+$dbh = db_connect();
+db_query($query, $dbh);
+
+$query = "ALTER TABLE Packages DROP COLUMN AURMaintainerUID;";
+db_query($query, $dbh);
diff --git a/upgrading/1.5.3.txt b/upgrading/1.5.3.txt
new file mode 100644
index 0000000..b38159f
--- /dev/null
+++ b/upgrading/1.5.3.txt
@@ -0,0 +1,2 @@
+1. Ensure this appears in config.inc:
+   define("DEFAULT_LANG", "en");
diff --git a/upgrading/1.7.0.txt b/upgrading/1.7.0.txt
new file mode 100644
index 0000000..4720da1
--- /dev/null
+++ b/upgrading/1.7.0.txt
@@ -0,0 +1,3 @@
+ALTER TABLE Users ADD Salt CHAR(32) NOT NULL DEFAULT '';
+ALTER TABLE Users ADD ResetKey CHAR(32) NOT NULL DEFAULT '';
+ALTER TABLE Users MODIFY LangPreference CHAR(5) NOT NULL DEFAULT 'en';
diff --git a/upgrading/1.8.0.txt b/upgrading/1.8.0.txt
new file mode 100644
index 0000000..7b1dcc0
--- /dev/null
+++ b/upgrading/1.8.0.txt
@@ -0,0 +1,80 @@
+1. Run the following MySQL statements:
+
+----
+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 "https://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;
+CREATE TABLE PackageBlacklist (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(64) NOT NULL,
+	PRIMARY KEY (ID),
+	UNIQUE (Name)
+);
+----
+
+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
+"$INCOMING_DIR":
+
+----
+#!/bin/bash
+
+for pkg in *; do
+	if [ -d "${pkg}" -a ! -f "${pkg}/PKGBUILD" ]; then
+		pkgbuild_file=$(find -P "${pkg}" -name PKGBUILD)
+		[ -n "${pkgbuild_file}" ] && \
+			cp "${pkgbuild_file}" "${pkg}/PKGBUILD"
+	fi
+done
+----
+
+4. (optional): 1.8.0 includes a helper utility called "aurblup" that can be
+used to prevent users from uploading source packages with names identical to
+packages in predefined binary repos, e.g. the official repositories of your
+distribution. In order to build and install aurblup, enter the following
+commands:
+
+	cd scripts/aurblup/
+	make config.h
+	$EDITOR config.h
+	make install  # as root
+
+Add something like "0 * * * * /usr/local/bin/aurblup" to root's crontab to make
+aurblup update the package blacklist every hour.
+
+NOTE: You can run aurblup as non-privileged user as well. Make sure that the
+user has read-write access to "/var/lib/aurblup/" (or whatever you defined with
+"ALPM_DBPATH") tho.
+
+5. (optional): As of 1.8.0, all MySQL tables should be InnoDB compatible. To
+convert a table, you can use this statement: `ALTER TABLE $foo ENGINE=InnoDB;`.
+If you want to stick with MyISAM or another storage engine that doesn't support
+transactions, you will need to disable the "MYSQL_USE_TRANSACTIONS" setting in
+"config.h" when setting up aurblup.
diff --git a/upgrading/1.8.1.txt b/upgrading/1.8.1.txt
new file mode 100644
index 0000000..48c2119
--- /dev/null
+++ b/upgrading/1.8.1.txt
@@ -0,0 +1,56 @@
+1. Drop foreign keys from the "Sessions" table:
+
+`ALTER TABLE Sessions DROP FOREIGN KEY Sessions_ibfk_1;` should work in most
+cases. Otherwise, check the output of `SHOW CREATE TABLE Sessions;` and use the
+foreign key name shown there.
+
+2. Run the following MySQL statements:
+
+----
+ALTER TABLE Sessions
+	ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE;
+ALTER TABLE PackageDepends
+	ADD FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (DepPkgID) REFERENCES Packages(ID) ON DELETE CASCADE;
+ALTER TABLE PackageSources
+	ADD FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE;
+ALTER TABLE TU_VoteInfo
+	ADD FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE;
+ALTER TABLE TU_Votes
+	ADD FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE;
+ALTER TABLE PackageComments
+	MODIFY DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL;
+UPDATE PackageComments SET DelUsersID = NULL WHERE DelUsersID = 0;
+ALTER TABLE Packages
+	MODIFY SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL,
+	MODIFY MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL;
+UPDATE Packages SET SubmitterUID = NULL WHERE SubmitterUID = 0;
+UPDATE Packages SET MaintainerUID = NULL WHERE MaintainerUID = 0;
+----
+
+3. (optional) If you converted your database from MyISAM to InnoDB during the
+upgrade process from 1.7.0 to 1.8.0 or from 1.8.0 to 1.8.1 without completely
+rebuilding the database from the MySQL schema, you should additionally run the
+following MySQL statements to add missing foreign keys:
+
+----
+ALTER TABLE Users
+	ADD FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION;
+ALTER TABLE Packages
+	ADD FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION,
+	ADD FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE NO ACTION,
+	ADD FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE NO ACTION;
+ALTER TABLE PackageVotes
+	ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE;
+ALTER TABLE PackageComments
+	ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE;
+ALTER TABLE CommentNotify
+	ADD FOREIGN KEY (PkgID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE;
+----
+
+4. Merge "web/lib/config.inc.proto" with "web/lib/config.inc".
diff --git a/upgrading/1.8.2.txt b/upgrading/1.8.2.txt
new file mode 100644
index 0000000..ba3aebd
--- /dev/null
+++ b/upgrading/1.8.2.txt
@@ -0,0 +1,18 @@
+1. Update the modified package timestamp for new packages.
+
+UPDATE Packages SET ModifiedTS = SubmittedTS WHERE ModifiedTS = 0;
+
+2. Move to new method of storing package depends.
+
+----
+ALTER TABLE PackageDepends ADD COLUMN DepName VARCHAR(64) NOT NULL DEFAULT '' AFTER PackageID;
+UPDATE PackageDepends SET DepName = (SELECT Name FROM Packages WHERE ID = DepPkgID);
+ALTER TABLE PackageDepends MODIFY DepName VARCHAR(64) NOT NULL;
+ALTER TABLE PackageDepends DROP FOREIGN KEY `PackageDepends_ibfk_2`;
+ALTER TABLE PackageDepends DROP COLUMN DepPkgID;
+DELETE FROM Packages WHERE DummyPkg = 1;
+ALTER TABLE Packages DROP COLUMN DummyPkg;
+----
+
+3. The File_Find PEAR module is no longer required. You can safely uninstall it
+if nothing else depends on it.
diff --git a/upgrading/1.9.0.txt b/upgrading/1.9.0.txt
new file mode 100644
index 0000000..45ac0eb
--- /dev/null
+++ b/upgrading/1.9.0.txt
@@ -0,0 +1,36 @@
+1. Translation files are now gettext compatible and need to be compiled after
+each AUR upgrade by running `make install` in the "po/" directory.
+
+2. Remove the "NewPkgNotify" column from the "Users" table:
+
+ALTER TABLE Users DROP COLUMN NewPkgNotify;
+
+3. Fix up issues with depends performance on large dataset.
+
+ALTER TABLE PackageDepends ADD INDEX (DepName);
+
+4. Rename "web/lib/config.inc" to "web/lib/config.inc.php".
+
+5. Merge "web/lib/config.inc.php.proto" with "web/lib/config.inc.php".
+
+6. Run the upload directory transform script ("scripts/uploadbuckets.sh") and
+rotate the converted directory structure into place.
+
+7. In order to to provide backward compatible package URLs, enable mod_rewrite
+and add the following to your Apache configuration (inside the "VirtualHost"
+container or optionally create a ".htaccess" file in the upload directory):
+
+----
+RewriteEngine on
+RewriteRule ^/packages/([^/]{1,2})([^/]*/(PKGBUILD|[^/]*\.tar\.gz|))$ /packages/$1/$1$2
+----
+
+The following equivalent rule can be used for lighttpd setups:
+
+----
+url.rewrite-once = ( "^/packages/([^/]{1,2})([^/]*/(PKGBUILD|[^/]*\.tar\.gz|))$" => "/packages/$1/$1$2" )
+----
+
+If you use a non-standard URL_DIR, slight modifications might be necessary.
+
+8. Merge "scripts/aurblup/config.h.proto" with "scripts/aurblup/config.h".
diff --git a/upgrading/1.9.1.txt b/upgrading/1.9.1.txt
new file mode 100644
index 0000000..ca80a47
--- /dev/null
+++ b/upgrading/1.9.1.txt
@@ -0,0 +1,3 @@
+1. Merge "web/lib/config.inc.php.proto" with "web/lib/config.inc.php".
+
+2. Install translations by running `make install` in "po/".
diff --git a/upgrading/2.0.0.txt b/upgrading/2.0.0.txt
new file mode 100644
index 0000000..807f5b3
--- /dev/null
+++ b/upgrading/2.0.0.txt
@@ -0,0 +1,14 @@
+1. Add new "Users" table login date and PGP key columns:
+
+----
+ALTER TABLE Users ADD COLUMN LastLogin BIGINT NOT NULL DEFAULT 0;
+ALTER TABLE Users ADD COLUMN PGPKey VARCHAR(40) NULL DEFAULT NULL;
+----
+
+2. Merge "web/lib/config.inc.php.proto" with "web/lib/config.inc.php".
+
+3. Enable the PDO MySQL extension (pdo_mysql.so) in "php.ini".
+
+4. Upgrade to PHP>=5.4.0 or enable "short_open_tag" in "php.ini".
+
+5. Install translations by running `make install` in "po/".
diff --git a/upgrading/2.1.0.txt b/upgrading/2.1.0.txt
new file mode 100644
index 0000000..5df5b88
--- /dev/null
+++ b/upgrading/2.1.0.txt
@@ -0,0 +1,2 @@
+1. Update your aurblup setup to match configuration changes. See commit
+6dc61e7d9e87ad6821869dab61e5f005af2e0252 for details.
diff --git a/upgrading/2.2.0.txt b/upgrading/2.2.0.txt
new file mode 100644
index 0000000..1cdf2b0
--- /dev/null
+++ b/upgrading/2.2.0.txt
@@ -0,0 +1,16 @@
+1. Add new "Users" table login IP address column:
+
+----
+ALTER TABLE Users
+	ADD COLUMN LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0;
+----
+
+2. Add a new "Bans" table:
+
+----
+CREATE TABLE Bans (
+	IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0,
+	BanTS TIMESTAMP NOT NULL,
+	PRIMARY KEY (IPAddress)
+) ENGINE = InnoDB;
+----
diff --git a/upgrading/2.3.0.txt b/upgrading/2.3.0.txt
new file mode 100644
index 0000000..d390d1c
--- /dev/null
+++ b/upgrading/2.3.0.txt
@@ -0,0 +1,22 @@
+1. Add registration and inactivity time stamps to the "Users" table:
+
+----
+ALTER TABLE Users
+	ADD COLUMN RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	ADD COLUMN InactivityTS BIGINT NOT NULL DEFAULT 0;
+----
+
+2. Add fields to store the total number of TUs and the quorum to the
+   "TU_VoteInfo" table:
+
+----
+ALTER TABLE TU_VoteInfo
+	ADD COLUMN ActiveTUs tinyint(3) unsigned NOT NULL default '0',
+	ADD COLUMN Quorum decimal(2, 2) unsigned NOT NULL;
+----
+
+3. Add a "fonts" category:
+
+----
+INSERT INTO PackageCategories (Category) VALUES ('fonts');
+----
diff --git a/upgrading/3.0.0.txt b/upgrading/3.0.0.txt
new file mode 100644
index 0000000..b134b79
--- /dev/null
+++ b/upgrading/3.0.0.txt
@@ -0,0 +1,249 @@
+1. Drop the user ID foreign key from the "PackageComments" table:
+
+`ALTER TABLE PackageComments DROP FOREIGN KEY PackageComments_ibfk_1;` should
+work in most cases. Otherwise, check the output of `SHOW CREATE TABLE
+PackageComments;` and use the foreign key name shown there.
+
+2. Add support for anonymous comments:
+
+----
+ALTER TABLE PackageComments
+	MODIFY UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+	ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL;
+----
+
+3. Create the PackageBases table:
+
+----
+CREATE TABLE PackageBases (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(64) NOT NULL,
+	CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1,
+	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,
+	MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL,
+	PackagerUID INTEGER UNSIGNED NULL DEFAULT NULL,
+	PRIMARY KEY (ID),
+	UNIQUE (Name),
+	INDEX (CategoryID),
+	INDEX (NumVotes),
+	INDEX (SubmitterUID),
+	INDEX (MaintainerUID),
+	INDEX (PackagerUID),
+	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,
+	FOREIGN KEY (PackagerUID) REFERENCES Users(ID) ON DELETE SET NULL
+) ENGINE = InnoDB;
+----
+
+4. Migrate data from Packages to PackageBases:
+
+----
+INSERT INTO PackageBases
+	SELECT ID, Name, CategoryID, NumVotes, OutOfDateTS, SubmittedTS,
+	ModifiedTS, SubmitterUID, MaintainerUID, NULL FROM Packages;
+----
+
+5. Delete unneeded foreign keys from Packages:
+
+First, drop the foreign keys on CategoryID, SubmitterUID and MaintainerUID. The
+following queries should work in most cases:
+
+----
+ALTER TABLE Packages
+	DROP FOREIGN KEY Packages_ibfk_1,
+	DROP FOREIGN KEY Packages_ibfk_2,
+	DROP FOREIGN KEY Packages_ibfk_3;
+----
+
+You can use `SHOW CREATE TABLE Packages;` to check whether you should use
+different names for your setup.
+
+6. Delete unneeded fields from Packages:
+
+----
+ALTER TABLE Packages
+	DROP COLUMN CategoryID,
+	DROP COLUMN NumVotes,
+	DROP COLUMN OutOfDateTS,
+	DROP COLUMN SubmittedTS,
+	DROP COLUMN ModifiedTS,
+	DROP COLUMN SubmitterUID,
+	DROP COLUMN MaintainerUID;
+----
+
+7. Add package base references to the Packages table:
+
+----
+ALTER TABLE Packages ADD COLUMN PackageBaseID INTEGER UNSIGNED NULL;
+UPDATE Packages SET PackageBaseID = ID;
+ALTER TABLE Packages
+	MODIFY PackageBaseID INTEGER UNSIGNED NOT NULL,
+	ADD FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE;
+----
+
+8. Delete foreign keys from PackageVotes, PackageComments and CommentNotify:
+
+----
+ALTER TABLE PackageVotes
+	DROP FOREIGN KEY PackageVotes_ibfk_1,
+	DROP FOREIGN KEY PackageVotes_ibfk_2;
+ALTER TABLE PackageComments
+	DROP FOREIGN KEY PackageComments_ibfk_3;
+ALTER TABLE CommentNotify
+	DROP FOREIGN KEY CommentNotify_ibfk_1,
+	DROP FOREIGN KEY CommentNotify_ibfk_2;
+----
+
+We highly recommend to use `SHOW CREATE TABLE PackageVotes;` etc. to check
+whether you should use different names for your setup.
+
+9. Delete indexes from PackageVotes and CommentNotify:
+
+----
+ALTER TABLE PackageVotes DROP INDEX VoteUsersIDPackageID;
+ALTER TABLE CommentNotify DROP INDEX NotifyUserIDPkgID;
+----
+
+10. Migrate PackageVotes, PackageComments and CommentNotify to refer to package
+bases:
+
+----
+ALTER TABLE PackageVotes ADD COLUMN PackageBaseID INTEGER UNSIGNED NOT NULL;
+UPDATE PackageVotes SET PackageBaseID = PackageID;
+ALTER TABLE PackageVotes DROP COLUMN PackageID;
+ALTER TABLE PackageComments ADD COLUMN PackageBaseID INTEGER UNSIGNED NOT NULL;
+UPDATE PackageComments SET PackageBaseID = PackageID;
+ALTER TABLE PackageComments DROP COLUMN PackageID;
+ALTER TABLE CommentNotify ADD COLUMN PackageBaseID INTEGER UNSIGNED NOT NULL;
+UPDATE CommentNotify SET PackageBaseID = PkgID;
+ALTER TABLE CommentNotify DROP COLUMN PkgID;
+----
+
+11. Recreate missing foreign keys and indexes:
+
+----
+ALTER TABLE PackageVotes
+	ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE;
+ALTER TABLE PackageComments
+	ADD FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE;
+ALTER TABLE CommentNotify
+	ADD FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
+	ADD FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE;
+CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
+CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PackageBaseID);
+----
+
+12. Create a new table to store package dependency types:
+
+----
+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');
+----
+
+13. Add a field to store the dependency type to the PackageDepends table:
+
+----
+ALTER TABLE PackageDepends ADD COLUMN DepTypeID TINYINT UNSIGNED NOT NULL;
+UPDATE PackageDepends SET DepTypeID = 1;
+ALTER TABLE PackageDepends
+	ADD FOREIGN KEY (DepTypeID) REFERENCES DependencyTypes(ID) ON DELETE NO ACTION;
+----
+
+14. Resize the package dependency name field:
+
+----
+ALTER TABLE PackageDepends MODIFY DepName VARCHAR(255) NOT NULL;
+----
+
+15. Create a new table to store package relation types:
+
+----
+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');
+----
+
+16. Create a new table to store package relations:
+
+----
+CREATE TABLE PackageRelations (
+	PackageID INTEGER UNSIGNED NOT NULL,
+	RelTypeID TINYINT UNSIGNED NOT NULL,
+	RelName VARCHAR(255) NOT NULL,
+	RelCondition VARCHAR(20),
+	INDEX (PackageID),
+	INDEX (RelName),
+	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
+) ENGINE = InnoDB;
+----
+
+17. Create tables to store package groups:
+
+----
+CREATE TABLE Groups (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(64) 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;
+----
+
+18. Create tables to store package licenses:
+
+----
+CREATE TABLE Licenses (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(64) 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;
+----
+
+19. Convert existing licenses to the new storage format:
+
+----
+INSERT INTO Licenses (Name) SELECT DISTINCT License FROM Packages;
+INSERT INTO PackageLicenses (PackageID, LicenseID)
+	SELECT Packages.ID, Licenses.ID FROM Packages
+	INNER JOIN Licenses ON Licenses.Name = Packages.License;
+----
+
+20. Delete the license column from the Packages table:
+
+----
+ALTER TABLE Packages DROP COLUMN License;
+----
diff --git a/upgrading/3.1.0.txt b/upgrading/3.1.0.txt
new file mode 100644
index 0000000..2fc1614
--- /dev/null
+++ b/upgrading/3.1.0.txt
@@ -0,0 +1,13 @@
+1. Increase the size of all fields containing package names, license names,
+group names or package versions:
+
+----
+ALTER TABLE PackageBases MODIFY Name VARCHAR(255) NOT NULL;
+ALTER TABLE Packages
+	MODIFY Name VARCHAR(255) NOT NULL,
+	MODIFY Version VARCHAR(255) NOT NULL DEFAULT '';
+ALTER TABLE Licenses MODIFY Name VARCHAR(255) NOT NULL;
+ALTER TABLE Groups MODIFY Name VARCHAR(255) NOT NULL;
+ALTER TABLE PackageDepends MODIFY DepCondition VARCHAR(255);
+ALTER TABLE PackageRelations MODIFY RelCondition VARCHAR(255);
+----
diff --git a/upgrading/3.2.0.txt b/upgrading/3.2.0.txt
new file mode 100644
index 0000000..cde9367
--- /dev/null
+++ b/upgrading/3.2.0.txt
@@ -0,0 +1,30 @@
+1. Add support for package requests to the database:
+
+----
+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 DEFAULT '',
+	RequestTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
+	Status TINYINT UNSIGNED NOT NULL DEFAULT 0,
+	PRIMARY KEY (ID),
+	INDEX (UsersID),
+	INDEX (PackageBaseID),
+	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
+) ENGINE = InnoDB;
+----
diff --git a/upgrading/longerpkgname.txt b/upgrading/longerpkgname.txt
new file mode 100644
index 0000000..a627496
--- /dev/null
+++ b/upgrading/longerpkgname.txt
@@ -0,0 +1 @@
+ALTER TABLE Packages MODIFY Name CHAR(64) NOT NULL;
-- 
cgit v1.2.3-54-g00ecf