summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rwxr-xr-xlib/mysql-functions2
-rw-r--r--misc/database-layout.dump72
2 files changed, 49 insertions, 25 deletions
diff --git a/lib/mysql-functions b/lib/mysql-functions
index 739074c..e8c1cf0 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -12,8 +12,6 @@ fi
# TODO: replace most/all insert-select queries by separate select and insert
# queries
-# TODO: remove all read references to `binary_packages`.`repositories`
-
# TODO: remove all write references to `binary_packages`.`repositories`
# TODO: remove `binary_packages`.`repositories` from the database
diff --git a/misc/database-layout.dump b/misc/database-layout.dump
index cd46731..6d0c060 100644
--- a/misc/database-layout.dump
+++ b/misc/database-layout.dump
@@ -3,8 +3,10 @@ BEGIN
INSERT IGNORE INTO `relevant_binary_packages` (`id`)
SELECT `binary_packages`.`id`
FROM `binary_packages`
+ JOIN `binary_packages_in_repositories`
+ ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
JOIN `repositories`
- ON `binary_packages`.`repository`=`repositories`.`id` AND `repositories`.`name`="build-list"
+ ON `binary_packages_in_repositories`.`repository`=`repositories`.`id` AND `repositories`.`name`="build-list"
JOIN `build_assignments`
ON `binary_packages`.`build_assignment`=`build_assignments`.`id`
JOIN `package_sources`
@@ -19,8 +21,10 @@ INSERT IGNORE INTO `relevant_binary_packages` (`id`)
FROM `relevant_binary_packages_copy`
JOIN `binary_packages`
ON `relevant_binary_packages_copy`.`id`=`binary_packages`.`id`
+ JOIN `binary_packages_in_repositories`
+ ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
JOIN `repositories`
- ON `binary_packages`.`repository`=`repositories`.`id` AND `repositories`.`name`="build-list"
+ ON `binary_packages_in_repositories`.`repository`=`repositories`.`id` AND `repositories`.`name`="build-list"
JOIN `dependencies`
ON `binary_packages`.`id`=`dependencies`.`dependent`
JOIN `dependency_types`
@@ -59,10 +63,14 @@ INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)
ON `a_ba`.`id`=`a_bp`.`build_assignment`
JOIN `binary_packages` AS `b_bp`
ON `b_ba`.`id`=`b_bp`.`build_assignment`
+ JOIN `binary_packages_in_repositories` AS `a_bpir`
+ ON `a_bp`.`id`=`a_bpir`.`package`
JOIN `repositories` AS `a_r`
- ON `a_bp`.`repository`=`a_r`.`id`
+ ON `a_bpir`.`repository`=`a_r`.`id`
+ JOIN `binary_packages_in_repositories` AS `b_bpir`
+ ON `b_bp`.`id`=`b_bpir`.`package`
JOIN `repositories` AS `b_r`
- ON `b_bp`.`repository`=`b_r`.`id`
+ ON `b_bpir`.`repository`=`b_r`.`id`
JOIN `repository_stabilities` AS `a_rs`
ON `a_r`.`stability`=`a_rs`.`id`
JOIN `repository_stabilities` AS `b_rs`
@@ -71,8 +79,10 @@ INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)
INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)
SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`
FROM `binary_packages`
+ JOIN `binary_packages_in_repositories`
+ ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
JOIN `repositories`
- ON `binary_packages`.`repository`=`repositories`.`id`
+ ON `binary_packages_in_repositories`.`repository`=`repositories`.`id`
JOIN `repository_stabilities`
ON `repositories`.`stability`=`repository_stabilities`.`id`
JOIN `build_assignments`
@@ -82,15 +92,17 @@ INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)
JOIN `upstream_repositories`
ON `package_sources`.`upstream_package_repository`=`upstream_repositories`.`id`
JOIN `repository_moves`
- ON `upstream_repositories`.`id`=`repository_moves`.`upstream_package_repository` AND `repository_moves`.`from_repository`=`binary_packages`.`repository`
+ ON `upstream_repositories`.`id`=`repository_moves`.`upstream_package_repository` AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`
WHERE `repository_stabilities`.`name` = `from_stability` AND (`from_stability`="staging" OR `binary_packages`.`is_tested`) AND NOT `binary_packages`.`has_issues`;
INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)
SELECT `r_bp`.`id`,`m_bp`.`id`
FROM `moveable_binary_packages`
JOIN `binary_packages` AS `m_bp`
ON `m_bp`.`id`=`moveable_binary_packages`.`id`
+ JOIN `binary_packages_in_repositories` AS `m_bpir`
+ ON `m_bp`.`id`=`m_bpir`.`package`
JOIN `repositories` AS `m_r`
- ON `m_bp`.`repository`=`m_r`.`id`
+ ON `m_bpir`.`repository`=`m_r`.`id`
JOIN `build_assignments` AS `m_ba`
ON `m_bp`.`build_assignment`=`m_ba`.`id`
JOIN `package_sources` AS `m_ps`
@@ -101,8 +113,10 @@ INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)
ON `m_ur`.`id`=`repository_moves`.`upstream_package_repository` AND `repository_moves`.`from_repository`=`m_r`.`id`
JOIN `repositories` AS `r_r`
ON `repository_moves`.`to_repository`=`r_r`.`id`
+ JOIN `binary_packages_in_repositories` AS `r_bpir`
+ ON `r_r`.`id`=`r_bpir`.`repository`
JOIN `binary_packages` AS `r_bp`
- ON `r_r`.`id`=`r_bp`.`repository` AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;
+ ON `r_bpir`.`package`=`r_bp`.`id` AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;
REPEAT
SET row_count_saved = 0;
DELETE
@@ -137,8 +151,10 @@ SELECT 1
FROM `install_target_providers`
JOIN `binary_packages` AS `prov_bp`
ON `install_target_providers`.`package`=`prov_bp`.`id`
+ JOIN `binary_packages_in_repositories` AS `prov_bpir`
+ ON `prov_bp`.`id`=`prov_bpir`.`package`
JOIN `repositories` AS `prov_r`
- ON `prov_bp`.`repository`=`prov_r`.`id`
+ ON `prov_bpir`.`repository`=`prov_r`.`id`
JOIN `repository_stability_relations`
ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`
WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable` AND NOT EXISTS (
@@ -183,10 +199,14 @@ SELECT 1
ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND `dependency_types`.`relevant_for_binary_packages`
JOIN `binary_packages` AS `req_bp`
ON `dependencies`.`dependent`=`req_bp`.`id`
+ JOIN `binary_packages_in_repositories` AS `repl_bpir`
+ ON `repl_bp`.`id`=`repl_bpir`.`package`
JOIN `repositories` AS `repl_r`
- ON `repl_bp`.`repository`=`repl_r`.`id`
+ ON `repl_bpir`.`repository`=`repl_r`.`id`
+ JOIN `binary_packages_in_repositories` AS `req_bpir`
+ ON `req_bp`.`id`=`req_bpir`.`package`
JOIN `repositories` AS `req_r`
- ON `req_bp`.`repository`=`req_r`.`id`
+ ON `req_bpir`.`repository`=`req_r`.`id`
JOIN `repository_stability_relations` AS `repl_rr`
ON `repl_rr`.`more_stable`=`repl_r`.`stability` AND `repl_rr`.`less_stable`=`req_r`.`stability`
WHERE NOT EXISTS (
@@ -199,8 +219,10 @@ SELECT 1
FROM `binary_packages` AS `subst_bp`
JOIN `install_target_providers` AS `subst_itp`
ON `subst_bp`.`id`=`subst_itp`.`package`
+ JOIN `binary_packages_in_repositories` AS `subst_bpir`
+ ON `subst_bp`.`id`=`subst_bpir`.`package`
JOIN `repositories` AS `subst_r`
- ON `subst_bp`.`repository`=`subst_r`.`id`
+ ON `subst_bpir`.`repository`=`subst_r`.`id`
JOIN `repository_stability_relations` AS `subst_rr`
ON `subst_rr`.`more_stable`=`subst_r`.`stability`
WHERE `subst_rr`.`less_stable`=`repl_r`.`stability` AND NOT EXISTS (
@@ -257,8 +279,10 @@ INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)
FROM `binary_packages`
JOIN `build_assignments`
ON `binary_packages`.`build_assignment`=`build_assignments`.`id` AND `build_assignments`.`is_broken`
+ JOIN `binary_packages_in_repositories`
+ ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
JOIN `repositories`
- ON `binary_packages`.`repository`=`repositories`.`id` AND `repositories`.`name`="build-list";
+ ON `binary_packages_in_repositories`.`repository`=`repositories`.`id` AND `repositories`.`name`="build-list";
REPEAT
INSERT IGNORE INTO `broken_packages_and_dependencies_old` (`id`)
SELECT `broken_packages_and_dependencies`.`id`
@@ -276,8 +300,10 @@ INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)
ON `dependencies`.`depending_on`=`install_target_providers`.`install_target`
JOIN `binary_packages` AS `new_bp`
ON `install_target_providers`.`package`=`new_bp`.`id`
+ JOIN `binary_packages_in_repositories` AS `new_bpir`
+ ON `new_bp`.`id`=`new_bpir`.`package`
JOIN `repositories` AS `new_repo`
- ON `new_bp`.`repository`=`new_repo`.`id` AND `new_repo`.`name` IN ("build-list","deletion-list");
+ ON `new_bpir`.`repository`=`new_repo`.`id` AND `new_repo`.`name` IN ("build-list","deletion-list");
UNTIL ROW_COUNT()=0
END REPEAT;
SELECT `package_sources`.`pkgbase`,`package_sources`.`git_revision`,`package_sources`.`mod_git_revision`,`upstream_repositories`.`name`
@@ -308,7 +334,6 @@ architectures CREATE TABLE `architectures` (
binary_packages CREATE TABLE `binary_packages` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`build_assignment` bigint(20) NOT NULL,
- `repository` mediumint(9) NOT NULL,
`epoch` mediumint(9) NOT NULL,
`pkgver` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
`pkgrel` mediumint(9) NOT NULL,
@@ -321,13 +346,11 @@ binary_packages CREATE TABLE `binary_packages` (
`sha512sum` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_moved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
- UNIQUE KEY `file_name` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`),
- UNIQUE KEY `content` (`build_assignment`,`sub_pkgrel`,`pkgname`,`architecture`,`repository`),
- KEY `repository` (`repository`),
+ UNIQUE KEY `file_name` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`),
+ UNIQUE KEY `content` (`build_assignment`,`sub_pkgrel`,`pkgname`,`architecture`),
KEY `architecture` (`architecture`),
- CONSTRAINT `binary_packages_ibfk_1` FOREIGN KEY (`repository`) REFERENCES `repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `binary_packages_ibfk_2` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `binary_packages_ibfk_3` FOREIGN KEY (`build_assignment`) REFERENCES `build_assignments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+ CONSTRAINT `binary_packages_ibfk_1` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `binary_packages_ibfk_2` FOREIGN KEY (`build_assignment`) REFERENCES `build_assignments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
binary_packages_in_repositories CREATE TABLE `binary_packages_in_repositories` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
@@ -637,7 +660,9 @@ INSERT INTO `repositories` VALUES
(8,'community-staging',3,'1',2),
(9,'build-list',5,'\0',1),
(10,'deletion-list',6,'\0',1),
- (11,'to-be-decided',7,'\0',1);
+ (11,'to-be-decided',7,'\0',1),
+ (12,'gnome-unstable',8,'1',2),
+ (13,'kde-unstable',8,'1',2);
INSERT INTO `repository_moves` VALUES
(1,6,3,3),
(2,6,3,4),
@@ -658,7 +683,8 @@ INSERT INTO `repository_stabilities` VALUES
(4,'standalone',NULL),
(5,'unbuilt','Packages: Build-list'),
(6,'forbidden',NULL),
- (7,'virtual',NULL);
+ (7,'virtual',NULL),
+ (8,'unstable',NULL);
INSERT INTO `repository_stability_relations` VALUES
(16,1,1),
(7,1,2),