| index : builder | |
| Archlinux32 build system | gitolite user |
| summaryrefslogtreecommitdiff |
| -rw-r--r-- | misc/database-layout.dmf | 288 | ||||
| -rw-r--r-- | misc/database-layout.dump | 106 |
diff --git a/misc/database-layout.dmf b/misc/database-layout.dmf index b74c264..f53237b 100644 --- a/misc/database-layout.dmf +++ b/misc/database-layout.dmf @@ -208,8 +208,8 @@ </item> <item type="database-table" id="{9085c36f-5884-41c8-9ca8-9d53973e657d}"> <position> - <x>737</x> - <y>38</y> + <x>777</x> + <y>32</y> </position> <table> <name>statitstics</name> @@ -540,12 +540,6 @@ <primary-key>False</primary-key> </column> <column> - <name>repository</name> - <data-type>MEDIUMINT</data-type> - <required>True</required> - <primary-key>False</primary-key> - </column> - <column> <name>epoch</name> <data-type>MEDIUMINT</data-type> <required>True</required> @@ -605,13 +599,19 @@ <required>False</required> <primary-key>False</primary-key> </column> + <column> + <name>last_moved</name> + <data-type>DATETIME</data-type> + <required>True</required> + <primary-key>False</primary-key> + </column> </column-list> </table> </item> <item type="database-table" id="{f8caa2d5-11ba-485c-b273-8183713896bf}"> <position> - <x>554</x> - <y>117</y> + <x>549</x> + <y>105</y> </position> <table> <name>install_target_providers</name> @@ -671,6 +671,12 @@ <required>True</required> <primary-key>False</primary-key> </column> + <column> + <name>architecture</name> + <data-type>SMALLINT</data-type> + <required>True</required> + <primary-key>False</primary-key> + </column> </column-list> </table> </item> @@ -832,8 +838,8 @@ </item> <item type="database-table" id="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"> <position> - <x>563</x> - <y>42</y> + <x>557</x> + <y>30</y> </position> <table> <name>install_targets</name> @@ -1118,6 +1124,69 @@ </column-list> </table> </item> + <item type="database-table" id="{b33d4844-b4e4-4ef6-944b-36b4113c2b82}"> + <position> + <x>543</x> + <y>182</y> + </position> + <table> + <name>binary_packages_in_repositories</name> + <color>#ffffff</color> + <column-list> + <column> + <name>id</name> + <data-type>BIGINT</data-type> + <required>True</required> + <primary-key>True</primary-key> + </column> + <column> + <name>package</name> + <data-type>BIGINT</data-type> + <required>True</required> + <primary-key>False</primary-key> + </column> + <column> + <name>repository</name> + <data-type>MEDIUMINT</data-type> + <required>True</required> + <primary-key>False</primary-key> + </column> + </column-list> + </table> + </item> + <item type="database-relationship" id="{25ec7ee3-9041-4ef4-a5b8-c41a06385813}"> + <line> + <connector-list> + <connector> + <position> + <x>529</x> + <y>318.906</y> + </position> + <angle>180</angle> + <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/> + </connector> + <connector> + <position> + <x>509.547</x> + <y>345.906</y> + </position> + <angle>0</angle> + <hub owner="{04f424bf-6465-478b-9529-0038615b51d9}"/> + </connector> + </connector-list> + </line> + <relationship> + <cardinality>OneToOne</cardinality> + <modality> + <child>Mandatory</child> + <parent>Mandatory</parent> + </modality> + <columns> + <child>4</child> + <parent>0</parent> + </columns> + </relationship> + </item> <item type="database-relationship" id="{3539786a-db3b-4c5c-ba35-e89fb027cda8}"> <line> <connector-list> @@ -1140,7 +1209,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1173,7 +1242,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1206,7 +1275,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1239,7 +1308,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1272,7 +1341,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1305,7 +1374,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1338,7 +1407,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1371,7 +1440,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1404,7 +1473,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1437,7 +1506,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1470,7 +1539,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1503,7 +1572,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1536,7 +1605,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1569,7 +1638,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1602,7 +1671,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1635,7 +1704,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1668,7 +1737,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1701,7 +1770,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1734,7 +1803,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1767,40 +1836,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> - <modality> - <child>Mandatory</child> - <parent>Mandatory</parent> - </modality> - <columns> - <child>2</child> - <parent>0</parent> - </columns> - </relationship> - </item> - <item type="database-relationship" id="{75410d72-ad2c-4357-aab5-e97f9fb74da5}"> - <line> - <connector-list> - <connector> - <position> - <x>511.719</x> - <y>191.875</y> - </position> - <angle>0</angle> - <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/> - </connector> - <connector> - <position> - <x>613.703</x> - <y>273</y> - </position> - <angle>90</angle> - <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/> - </connector> - </connector-list> - </line> - <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1833,7 +1869,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1849,8 +1885,8 @@ <connector-list> <connector> <position> - <x>554</x> - <y>148.906</y> + <x>549</x> + <y>136.906</y> </position> <angle>180</angle> <hub owner="{f8caa2d5-11ba-485c-b273-8183713896bf}"/> @@ -1866,7 +1902,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1899,7 +1935,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1915,16 +1951,16 @@ <connector-list> <connector> <position> - <x>632.109</x> - <y>117</y> + <x>627.109</x> + <y>105</y> </position> <angle>90</angle> <hub owner="{f8caa2d5-11ba-485c-b273-8183713896bf}"/> </connector> <connector> <position> - <x>613.016</x> - <y>91.8125</y> + <x>607.016</x> + <y>79.8125</y> </position> <angle>270</angle> <hub owner="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"/> @@ -1932,7 +1968,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1956,8 +1992,8 @@ </connector> <connector> <position> - <x>563</x> - <y>66.9062</y> + <x>557</x> + <y>54.9062</y> </position> <angle>180</angle> <hub owner="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"/> @@ -1965,7 +2001,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -1982,7 +2018,7 @@ <connector> <position> <x>698.406</x> - <y>311.906</y> + <y>318.906</y> </position> <angle>0</angle> <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/> @@ -1998,7 +2034,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -2031,7 +2067,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -2064,13 +2100,13 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> </modality> <columns> - <child>11</child> + <child>10</child> <parent>0</parent> </columns> </relationship> @@ -2080,7 +2116,7 @@ <connector-list> <connector> <position> - <x>559.755</x> + <x>638.51</x> <y>395</y> </position> <angle>90</angle> @@ -2088,8 +2124,8 @@ </connector> <connector> <position> - <x>585.469</x> - <y>350.812</y> + <x>641.938</x> + <y>364.812</y> </position> <angle>270</angle> <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/> @@ -2097,7 +2133,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -2113,7 +2149,7 @@ <connector-list> <connector> <position> - <x>638.51</x> + <x>559.755</x> <y>395</y> </position> <angle>90</angle> @@ -2121,8 +2157,8 @@ </connector> <connector> <position> - <x>641.938</x> - <y>350.812</y> + <x>585.469</x> + <y>364.812</y> </position> <angle>270</angle> <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/> @@ -2130,7 +2166,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -2163,7 +2199,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -2196,7 +2232,7 @@ </connector-list> </line> <relationship> - <cardinality>OneToMany</cardinality> + <cardinality>OneToOne</cardinality> <modality> <child>Mandatory</child> <parent>Mandatory</parent> @@ -2207,5 +2243,71 @@ </columns> </relationship> </item> + <item type="database-relationship" id="{c672e45e-2bd4-4ff1-9b42-fdd2443cae6a}"> + <line> + <connector-list> + <connector> + <position> + <x>543</x> + <y>213.906</y> + </position> + <angle>180</angle> + <hub owner="{b33d4844-b4e4-4ef6-944b-36b4113c2b82}"/> + </connector> + <connector> + <position> + <x>511.719</x> + <y>191.875</y> + </position> + <angle>0</angle> + <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/> + </connector> + </connector-list> + </line> + <relationship> + <cardinality>OneToOne</cardinality> + <modality> + <child>Mandatory</child> + <parent>Mandatory</parent> + </modality> + <columns> + <child>1</child> + <parent>0</parent> + </columns> + </relationship> + </item> + <item type="database-relationship" id="{0213a360-5a3b-484f-82c7-320003506c8a}"> + <line> + <connector-list> + <connector> + <position> + <x>647.922</x> + <y>245.812</y> + </position> + <angle>270</angle> + <hub owner="{b33d4844-b4e4-4ef6-944b-36b4113c2b82}"/> + </connector> + <connector> + <position> + <x>613.703</x> + <y>273</y> + </position> + <angle>90</angle> + <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/> + </connector> + </connector-list> + </line> + <relationship> + <cardinality>OneToMany</cardinality> + <modality> + <child>Optional</child> + <parent>Optional</parent> + </modality> + <columns> + <child>2</child> + <parent>0</parent> + </columns> + </relationship> + </item> </item-list> </diagram> diff --git a/misc/database-layout.dump b/misc/database-layout.dump index fffdef4..dc0b537 100644 --- a/misc/database-layout.dump +++ b/misc/database-layout.dump @@ -44,6 +44,30 @@ CREATE TEMPORARY TABLE `moveable_binary_packages_copy` (`id` BIGINT, `to_reposit DROP TEMPORARY TABLE IF EXISTS `moveable_binary_packages_copy2`; DROP TEMPORARY TABLE IF EXISTS `replaced_binary_packages_copy2`; CREATE TEMPORARY TABLE `replaced_binary_packages_copy2` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`)); +DROP TEMPORARY TABLE IF EXISTS `package_blobs`; +CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`)); +INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`) + SELECT `a_ps`.`id`,`b_ps`.`id` + FROM `package_sources` AS `a_ps` + JOIN `package_sources` AS `b_ps` + ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10 + JOIN `build_assignments` AS `a_ba` + ON `a_ps`.`id`=`a_ba`.`package_source` + JOIN `build_assignments` AS `b_ba` + ON `b_ps`.`id`=`b_ba`.`package_source` + JOIN `binary_packages` AS `a_bp` + ON `a_ba`.`id`=`a_bp`.`build_assignment` + JOIN `binary_packages` AS `b_bp` + ON `b_ba`.`id`=`b_bp`.`build_assignment` + JOIN `repositories` AS `a_r` + ON `a_bp`.`repository`=`a_r`.`id` + JOIN `repositories` AS `b_r` + ON `b_bp`.`repository`=`b_r`.`id` + JOIN `repository_stabilities` AS `a_rs` + ON `a_r`.`stability`=`a_rs`.`id` + JOIN `repository_stabilities` AS `b_rs` + ON `b_r`.`stability`=`b_rs`.`id` + WHERE `a_rs`.`name` = `from_stability` AND `b_rs`.`name` = `from_stability`; INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`) SELECT `binary_packages`.`id`,`repository_moves`.`to_repository` FROM `binary_packages` @@ -80,6 +104,7 @@ INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`) JOIN `binary_packages` AS `r_bp` ON `r_r`.`id`=`r_bp`.`repository` AND `r_bp`.`pkgname`=`m_bp`.`pkgname`; REPEAT +SET row_count_saved = 0; DELETE FROM `replaced_binary_packages_copy`; INSERT IGNORE INTO `replaced_binary_packages_copy` @@ -108,7 +133,7 @@ DELETE `replaced_binary_packages`,`moveable_binary_packages` JOIN `dependency_types` ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND `dependency_types`.`relevant_for_binary_packages` WHERE NOT EXISTS ( -SELECT * +SELECT 1 FROM `install_target_providers` JOIN `binary_packages` AS `prov_bp` ON `install_target_providers`.`package`=`prov_bp`.`id` @@ -117,15 +142,15 @@ SELECT * 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 ( -SELECT * +SELECT 1 FROM `replaced_binary_packages_copy` WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`)) AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `install_target_providers` JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package` WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`); -SET @row_count_saved = ROW_COUNT(); +SET row_count_saved = row_count_saved + ROW_COUNT(); DELETE FROM `replaced_binary_packages_copy`; INSERT IGNORE INTO `replaced_binary_packages_copy` @@ -151,7 +176,7 @@ DELETE `replaced_binary_packages`,`moveable_binary_packages` ON `repl_bp`.`id`=`install_target_providers`.`package` JOIN `dependencies` ON `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `replaced_binary_packages_copy` WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`) JOIN `dependency_types` @@ -165,12 +190,12 @@ SELECT * 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 ( -SELECT * +SELECT 1 FROM `moveable_binary_packages_copy` JOIN `install_target_providers` AS `subst_itp` ON `moveable_binary_packages_copy`.`id`=`subst_itp`.`package` WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`) AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `binary_packages` AS `subst_bp` JOIN `install_target_providers` AS `subst_itp` ON `subst_bp`.`id`=`subst_itp`.`package` @@ -179,10 +204,45 @@ SELECT * 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 ( -SELECT * +SELECT 1 FROM `replaced_binary_packages_copy2` WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`) AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`); -UNTIL row_count_saved=0 AND ROW_COUNT()=0 +SET row_count_saved = row_count_saved + ROW_COUNT(); +DELETE + FROM `replaced_binary_packages_copy`; +INSERT IGNORE INTO `replaced_binary_packages_copy` + SELECT `replaced_binary_packages`.* + FROM `replaced_binary_packages`; +DELETE + FROM `replaced_binary_packages_copy2`; +INSERT IGNORE INTO `replaced_binary_packages_copy2` + SELECT `replaced_binary_packages`.* + FROM `replaced_binary_packages`; +DELETE + FROM `moveable_binary_packages_copy`; +INSERT IGNORE INTO `moveable_binary_packages_copy` + SELECT `moveable_binary_packages`.* + FROM `moveable_binary_packages`; +DELETE `replaced_binary_packages`,`moveable_binary_packages` + FROM `replaced_binary_packages` + RIGHT JOIN `moveable_binary_packages` + ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id` + JOIN `binary_packages` + ON `binary_packages`.`id`=`moveable_binary_packages`.`id` + JOIN `build_assignments` + ON `binary_packages`.`build_assignment`=`build_assignments`.`id` + JOIN `package_blobs` + ON `build_assignments`.`package_source`=`package_blobs`.`ps_a` + JOIN `build_assignments` AS `bl_ba` + ON `bl_ba`.`package_source`=`package_blobs`.`ps_b` + JOIN `binary_packages` AS `bl_bp` + ON `bl_ba`.`id`=`bl_bp`.`build_assignment` + WHERE NOT EXISTS ( +SELECT 1 + FROM `moveable_binary_packages_copy` + WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`); +SET row_count_saved = row_count_saved + ROW_COUNT(); +UNTIL row_count_saved=0 END REPEAT; DROP TEMPORARY TABLE `moveable_binary_packages_copy`; DROP TEMPORARY TABLE `replaced_binary_packages_copy`; @@ -259,6 +319,7 @@ binary_packages CREATE TABLE `binary_packages` ( `architecture` smallint(6) NOT NULL, `is_to_be_deleted` bit(1) NOT NULL, `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`), @@ -268,6 +329,16 @@ binary_packages CREATE TABLE `binary_packages` ( 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 ) 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, + `package` bigint(20) NOT NULL, + `repository` mediumint(9) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `content` (`package`,`repository`), + KEY `binary_packages_in_repositories_ibfk_2` (`repository`), + CONSTRAINT `binary_packages_in_repositories_ibfk_1` FOREIGN KEY (`package`) REFERENCES `binary_packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `binary_packages_in_repositories_ibfk_2` FOREIGN KEY (`repository`) REFERENCES `repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci build_assignments CREATE TABLE `build_assignments` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `package_source` bigint(20) NOT NULL, @@ -276,7 +347,7 @@ build_assignments CREATE TABLE `build_assignments` ( `is_broken` bit(1) NOT NULL, `priority` smallint(6) NOT NULL, `is_black_listed` text COLLATE utf8mb4_unicode_ci, - `return_date` datetime DEFAULT NULL, + `return_date` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`package_source`,`architecture`), KEY `architecture` (`architecture`), @@ -335,7 +406,7 @@ email_actions CREATE TABLE `email_actions` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci email_log CREATE TABLE `email_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, - `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `success` bit(1) NOT NULL, `action` mediumint(9) DEFAULT NULL, `count` mediumint(9) DEFAULT NULL, @@ -359,7 +430,7 @@ failed_builds CREATE TABLE `failed_builds` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `build_slave` mediumint(9) NOT NULL, `build_assignment` bigint(20) NOT NULL, - `date` datetime NOT NULL, + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `reason` smallint(6) NOT NULL, `log_file` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), @@ -414,7 +485,7 @@ package_sources CREATE TABLE `package_sources` ( `upstream_package_repository` smallint(6) NOT NULL, `uses_upstream` bit(1) NOT NULL, `uses_modification` bit(1) NOT NULL, - `commit_time` datetime NOT NULL, + `commit_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `content` (`pkgbase`,`git_revision`,`mod_git_revision`), KEY `upstream_package_repository` (`upstream_package_repository`), @@ -431,10 +502,13 @@ repositories CREATE TABLE `repositories` ( `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `stability` mediumint(9) NOT NULL, `is_on_master_mirror` bit(1) NOT NULL, + `architecture` smallint(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `stability` (`stability`), - CONSTRAINT `repositories_ibfk_1` FOREIGN KEY (`stability`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + KEY `architecture` (`architecture`), + CONSTRAINT `repositories_ibfk_1` FOREIGN KEY (`stability`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `repositories_ibfk_2` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci repository_moves CREATE TABLE `repository_moves` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, @@ -476,7 +550,7 @@ ssh_keys CREATE TABLE `ssh_keys` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ssh_log CREATE TABLE `ssh_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, - `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `build_slave` mediumint(9) DEFAULT NULL, `action` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `parameters` text COLLATE utf8mb4_unicode_ci NOT NULL, @@ -486,7 +560,7 @@ ssh_log CREATE TABLE `ssh_log` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci statistics CREATE TABLE `statistics` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, - `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `stable_packages_count` mediumint(9) NOT NULL, `pending_tasks_count` mediumint(9) NOT NULL, `pending_packages_count` mediumint(9) NOT NULL, |