1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
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;
----
|
