Tools for building 32-bit archlinux packages from archlinux.org's official, 64-bit tested PKGBUILDs et al.

mysql-functions 58KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667
  1. #!/bin/sh
  2. # contains functions used to access mysql db
  3. # shellcheck disable=SC2016,SC2039,SC2119,SC2120
  4. if [ -z "${base_dir}" ]; then
  5. # just to make shellcheck happy
  6. . '../lib/load-configuration'
  7. fi
  8. # base64_encode_each encode each line of stdin with base64
  9. base64_encode_each() {
  10. local line
  11. while read -r line; do
  12. printf '%s' \
  13. "${line}" | \
  14. base64 -w0
  15. printf '\n'
  16. done
  17. }
  18. # mysql_run_query
  19. # wrapper function to query mysql
  20. mysql_run_query() {
  21. local query_stdin
  22. local query_stdout
  23. local query_stderr
  24. local file_name_extra
  25. local file
  26. local files
  27. local number
  28. file_name_extra=''
  29. if [ "x$1" = 'xunimportant' ]; then
  30. shift
  31. file_name_extra='unimportant_'
  32. elif [ -s "${work_dir}/build-master-sanity" ]; then
  33. file_name_extra='was_insane_'
  34. fi
  35. # we save the query in a file and delete that file if the query succeeded
  36. query_stdin=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.$(date +'%Y-%m-%dT%T').XXXXXX.stdin")
  37. query_stdout="${query_stdin%.stdin}.stdout"
  38. query_stderr="${query_stdin%.stdin}.stderr"
  39. cat > "${query_stdin}"
  40. for _ in {1..10}; do
  41. if [ -f "${query_stdout}" ]; then
  42. wait_some_time 10 10
  43. fi
  44. ${mysql_command} -N --raw --batch "$@" \
  45. < "${query_stdin}" \
  46. > "${query_stdout}" \
  47. 2>> "${query_stderr}" \
  48. && rm "${query_stdin}" "${query_stderr}"
  49. if ! [ -f "${query_stdin}" ]; then
  50. # success!
  51. break
  52. fi
  53. done
  54. # a present query_file means there was an error
  55. if [ -f "${query_stdin}" ]; then
  56. >&2 printf 'I (%s) could not complete a mysql query:\n' \
  57. "$(hostname)"
  58. >&2 sed 's/^/mysql< /' "${query_stdin}"
  59. >&2 sed 's/^/mysql>2 /' "${query_stderr}"
  60. files="${query_stdin} ${query_stdout} ${query_stderr}$(
  61. sed -n '
  62. s/^.*INFILE "\(\S\+\)".*$/\1/
  63. T
  64. p
  65. ' "${query_stdin}" | \
  66. sponge | \
  67. cat -n | \
  68. while read -r number file; do
  69. sed -i '
  70. s@"'"$(str_to_regex "${file}")"'"@"'"${query_stdin%.stdin}.infile.${number}"'"@g
  71. ' "${query_stdin}"
  72. cp "${file}" "${query_stdin%.stdin}.infile.${number}"
  73. printf ' %s' "${query_stdin%.stdin}.infile.${number}"
  74. done
  75. )"
  76. if ${i_am_the_master}; then
  77. if [ ! -s "${work_dir}/build-master-sanity" ] && \
  78. [ -z "${file_name_extra}" ]; then
  79. printf '\001ACTION failed to execute a mysql query - can you have a look at "%s"?.\001\n' \
  80. "${query_stdin##*/}" \
  81. | irc_say
  82. fi
  83. for file in ${files}; do
  84. cp "${file}" "${webserver_directory}/mysql-queries/${file##*/}.txt"
  85. chmod go+r "${webserver_directory}/mysql-queries/${file##*/}.txt"
  86. done
  87. fi
  88. if [ -z "${file_name_extra}" ]; then
  89. echo 'A mysql query failed.' > \
  90. "${work_dir}/build-master-sanity"
  91. else
  92. # shellcheck disable=SC2086
  93. rm -f ${files}
  94. fi
  95. return 2
  96. fi
  97. # shellcheck disable=SC2094
  98. {
  99. rm "${query_stdout}"
  100. cat
  101. } < \
  102. "${query_stdout}"
  103. }
  104. # mysql_add_package_source $pkgbase $git_revision $mod_git_revision $upstream_package_repository $srcinfo_file
  105. # shellcheck disable=SC2086
  106. mysql_add_package_source() {
  107. local pkgbase
  108. local git_revision
  109. local mod_git_revision
  110. local upstream_package_repository
  111. local srcinfo_file
  112. pkgbase="$1"
  113. git_revision="$2"
  114. mod_git_revision="$3"
  115. upstream_package_repository="$4"
  116. srcinfo_file="$5"
  117. local uses_upstream
  118. local uses_modification
  119. local repo
  120. local upstream_package_repository_id
  121. if grep -qx 'PKGBUILD_mod = \S\+' "${srcinfo_file}"; then
  122. uses_modification=1
  123. else
  124. uses_modification=0
  125. fi
  126. if grep -qx 'PKGBUILD = \S\+' "${srcinfo_file}"; then
  127. uses_upstream=1
  128. else
  129. uses_upstream=0
  130. fi
  131. upstream_package_repository_id=$(
  132. {
  133. printf 'SELECT `upstream_repositories`.`id`'
  134. printf ' FROM `upstream_repositories`'
  135. printf ' WHERE `upstream_repositories`.`name` = from_base64("%s")' \
  136. "$(
  137. printf '%s' "${upstream_package_repository}" | \
  138. base64 -w0
  139. )"
  140. printf ' LIMIT 1;\n'
  141. } | \
  142. mysql_run_query
  143. )
  144. if [ -z "${upstream_package_repository_id}" ]; then
  145. >&2 printf 'Cannot find upstream package repository "%s" in the database.\n' \
  146. "${upstream_package_repository}"
  147. exit 2
  148. fi
  149. {
  150. printf 'INSERT INTO `package_sources`'
  151. printf ' (`pkgbase`,`git_revision`,`mod_git_revision`,`upstream_package_repository`,`uses_upstream`,`uses_modification`)'
  152. printf ' VALUES ('
  153. printf 'from_base64("%s"),' \
  154. "$(
  155. printf '%s' "${pkgbase}" | \
  156. base64 -w0
  157. )" \
  158. "$(
  159. printf '%s' "${git_revision}" | \
  160. base64 -w0
  161. )" \
  162. "$(
  163. printf '%s' "${mod_git_revision}" | \
  164. base64 -w0
  165. )"
  166. printf '%s,' \
  167. "${upstream_package_repository_id}" \
  168. "${uses_upstream}" \
  169. "${uses_modification}" | \
  170. sed 's/,$/)/'
  171. printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`package_sources`.`id`);\n'
  172. printf 'SELECT LAST_INSERT_ID();\n'
  173. } | \
  174. mysql_run_query
  175. }
  176. # mysql_generate_package_metadata $current_repository_id $package $git_revision $mod_git_revision $repository
  177. # generate the meta data of a package (dependencies, built packages, ...) in the database
  178. mysql_generate_package_metadata() {
  179. mysql_load_min_and_max_versions
  180. ( # new shell is intentional
  181. current_repository_id="$1"
  182. package="$2"
  183. git_revision="$3"
  184. mod_git_revision="$4"
  185. repository="$5"
  186. if [[ "${current_repository_id}" = *[!0-9]* ]]; then
  187. >&2 printf 'mysql_generate_package_metadata(): invalid current_repository_id="%s".\n' \
  188. "${current_repository_id}"
  189. exit 2
  190. fi
  191. temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_generate_package_metadata.XXXXXXXXXX' --tmpdir)
  192. trap 'rm -rf --one-file-system "${temp_dir}"' EXIT
  193. if ! make_source_info "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"; then
  194. >&2 printf '"make_source_info %s %s %s %s %s" failed.\n' "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"
  195. exit 2
  196. fi
  197. # remove empty lines and unsupported architectures
  198. sed -i '
  199. /^[^=]*=\s*$/d
  200. /^\s*arch = /{
  201. / \(i[46]86\|pentium4\|any\)$/!d
  202. }
  203. ' "${temp_dir}/SRCINFO"
  204. if [ ! -s "${temp_dir}/SRCINFO" ]; then
  205. >&2 printf '"make_source_info" had empty output - eh, what?\n'
  206. exit 2
  207. fi
  208. printf '\n\n' >> "${temp_dir}/SRCINFO"
  209. pkgbase=$(
  210. grep '^pkgbase = ' "${temp_dir}/SRCINFO" | \
  211. cut -d' ' -f3
  212. )
  213. if [ -z "${pkgbase}" ]; then
  214. >&2 printf '"make_source_info" did not return a "pkgbase" - eh, what?\n'
  215. exit 2
  216. fi
  217. # add the package source
  218. package_source_id=$(
  219. mysql_add_package_source "${pkgbase}" "${git_revision}" "${mod_git_revision}" "${repository}" "${temp_dir}/SRCINFO"
  220. )
  221. {
  222. sed -n '
  223. s/^\tarch = //
  224. T
  225. p
  226. ' "${temp_dir}/SRCINFO" | \
  227. sort -u | \
  228. grep -vxF 'any' || \
  229. echo 'any'
  230. } > \
  231. "${temp_dir}/architectures"
  232. # select any specific arch (which will be building the 'any' part of a split package)
  233. any_arch=$(
  234. tail -n1 "${temp_dir}/architectures"
  235. )
  236. # iterate over all pkgnames
  237. grep '^pkgname = ' "${temp_dir}/SRCINFO" | \
  238. cut -d' ' -f3 | \
  239. while read -r pkgname; do
  240. # iff this pkgname defines some arch, ...
  241. if sed -n '
  242. /^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ p
  243. ' "${temp_dir}/SRCINFO" | \
  244. grep -q '^\sarch = '; then
  245. # ... it will override all archs defined in pkgbase
  246. sed '
  247. /^pkgbase = /,/^$/ {
  248. /^\sarch = /d
  249. }
  250. ' "${temp_dir}/SRCINFO"
  251. else
  252. cat "${temp_dir}/SRCINFO"
  253. fi | \
  254. sed -n '
  255. /^pkgbase = \|^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ {
  256. s/^pkgname/\t\0/
  257. /^\S/d
  258. /^\s*$/d
  259. s/^\s*//
  260. p
  261. }
  262. ' > \
  263. "${temp_dir}/SRCINFO.tmp"
  264. epoch=$(
  265. {
  266. grep -m1 '^epoch = ' "${temp_dir}/SRCINFO.tmp" || \
  267. echo 'epoch = 0'
  268. } | \
  269. cut -d' ' -f3
  270. )
  271. pkgver=$(
  272. grep -m1 '^pkgver = ' "${temp_dir}/SRCINFO.tmp" | \
  273. cut -d' ' -f3
  274. )
  275. pkgrel=$(
  276. grep -m1 '^pkgrel = ' "${temp_dir}/SRCINFO.tmp" | \
  277. cut -d' ' -f3
  278. )
  279. # iterate over all archs
  280. grep '^arch = ' "${temp_dir}/SRCINFO.tmp" | \
  281. cut -d' ' -f3 | \
  282. while read -r arch; do
  283. build_arch=$(
  284. # this binary package is either built by the build_assignment
  285. # with the identical arch (if existent) or by the one
  286. # with arch=$any_arch
  287. grep -xF "${arch}" "${temp_dir}/architectures" || \
  288. echo "${any_arch}"
  289. )
  290. sed '
  291. s/^\(\S\+\)_'"${arch}"' = /\1 = /
  292. s/^pkgname = \(\S\+\)$/\0\nprovides = \1='"${epoch}"':'"${pkgver}"'\ndepends = base\nmakedepends = base-devel/
  293. ' "${temp_dir}/SRCINFO.tmp" | \
  294. sed '
  295. s/^arch = \S\+$/arch/
  296. t
  297. s/^provides = /provides\t/
  298. t mangle_version
  299. s/^groups = /groups\t/
  300. t mangle_version
  301. s/^makedepends = /makedepends\t/
  302. t mangle_version
  303. s/^checkdepends = /checkdepends\t/
  304. t mangle_version
  305. s/^depends = /rundepends\t/
  306. t mangle_version
  307. d
  308. :mangle_version
  309. # TODO: we might want to keep the pkgrel part and only remove the
  310. # sub_pkgrel part - but then we need to include pkgrels on the provide
  311. # side, too(?)
  312. s/\(=\|<\|<=\|>=\|>\)\([^[:space:]-]\+\)\(-\S*\)\?$/\t\1\t\2/
  313. t split_epoch
  314. h
  315. s/$/\t>=\t'"${min_version}"'/
  316. s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/
  317. p
  318. g
  319. s/$/\t<=\t'"${max_version}"'/
  320. /^\S\+depends\s/d
  321. :split_epoch
  322. s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/
  323. t
  324. s/\s\S\+$/\t0\0/
  325. ' | \
  326. sed '
  327. s/^/'"$(
  328. printf '%s\t' \
  329. "${pkgname}" \
  330. "${arch}" \
  331. "${epoch}" \
  332. "${pkgver}" \
  333. "${pkgrel}" \
  334. "${build_arch}"
  335. )"'/
  336. '
  337. done
  338. rm "${temp_dir}/SRCINFO.tmp"
  339. done | \
  340. sort -u > \
  341. "${temp_dir}/database-input"
  342. {
  343. printf 'CREATE TEMPORARY TABLE `links`('
  344. printf '`pkgname` VARCHAR(64),'
  345. printf '`architecture` VARCHAR(16),'
  346. printf '`epoch` MEDIUMINT,'
  347. printf '`pkgver` VARCHAR(64),'
  348. printf '`pkgrel` MEDIUMINT,'
  349. printf '`build_architecture` VARCHAR(16),'
  350. printf '`type` VARCHAR(16),'
  351. printf '`install_target_name` VARCHAR(128),'
  352. printf '`version_relation` VARCHAR(2),'
  353. printf '`install_target_epoch` MEDIUMINT,'
  354. printf '`install_target_version` VARCHAR(64)'
  355. printf ');\n'
  356. printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `links`;\n' \
  357. "${temp_dir}/database-input"
  358. printf 'SET @sub_pkgrel=('
  359. printf 'SELECT IFNULL('
  360. printf 'MAX('
  361. printf '`binary_packages`.`sub_pkgrel`+'
  362. printf 'IF(`binary_packages_in_repositories`.`repository` IN (%s,%s),0,1)' \
  363. "${repository_ids__any_build_list}" \
  364. "${repository_ids__any_to_be_decided}"
  365. printf '),'
  366. printf '0'
  367. printf ')'
  368. printf ' FROM `links`'
  369. printf ' JOIN `architectures`'
  370. printf ' ON `architectures`.`name`=`links`.`architecture`'
  371. mysql_join_architectures_binary_packages
  372. printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
  373. 'pkgname' 'pkgname' \
  374. 'epoch' 'epoch' \
  375. 'pkgver' 'pkgver' \
  376. 'pkgrel' 'pkgrel'
  377. printf ' LEFT'
  378. mysql_join_binary_packages_binary_packages_in_repositories
  379. printf ');\n'
  380. printf 'INSERT IGNORE INTO `build_assignments` ('
  381. printf '`package_source`,'
  382. printf '`architecture`'
  383. printf ') SELECT'
  384. printf ' %s,`architectures`.`id`' \
  385. "${package_source_id}"
  386. printf ' FROM `links`'
  387. printf ' JOIN `architectures`'
  388. printf ' ON `architectures`.`name`=`links`.`build_architecture`'
  389. printf ' WHERE `links`.`type`="arch";\n'
  390. # we delete unbuilt binary_packages of identical version prior to
  391. # inserting, so we can _update_ a build list entry (e.g. because
  392. # the source changed, but the version stays the same)
  393. printf 'DELETE `binary_packages`'
  394. printf ' FROM `links`'
  395. printf ' JOIN `architectures`'
  396. printf ' ON `links`.`architecture`=`architectures`.`name`'
  397. printf ' JOIN `architectures` AS `ba_a`'
  398. printf ' ON `ba_a`.`name`=`links`.`build_architecture`'
  399. mysql_join_architectures_build_assignments 'ba_a'
  400. mysql_join_build_assignments_binary_packages
  401. printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
  402. 'pkgname' 'pkgname' \
  403. 'epoch' 'epoch' \
  404. 'pkgver' 'pkgver' \
  405. 'pkgrel' 'pkgrel'
  406. printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel'
  407. printf ' AND `binary_packages`.`architecture`=`architectures`.`id`'
  408. printf ' WHERE `links`.`type`="arch"'
  409. # the below test should always give "true", but it is a useful
  410. # safeguard to not delete built packages from the database
  411. printf ' AND NOT EXISTS('
  412. printf 'SELECT 1'
  413. printf ' FROM `binary_packages_in_repositories`'
  414. mysql_join_binary_packages_in_repositories_repositories
  415. printf ' WHERE `repositories`.`is_on_master_mirror`'
  416. printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  417. printf ');\n'
  418. printf 'INSERT IGNORE INTO `binary_packages` ('
  419. printf '`build_assignment`,'
  420. printf '`pkgname`,'
  421. printf '`epoch`,'
  422. printf '`pkgver`,'
  423. printf '`pkgrel`,'
  424. printf '`sub_pkgrel`,'
  425. printf '`architecture`'
  426. printf ') SELECT'
  427. printf ' `build_assignments`.`id`,'
  428. printf '`links`.`%s`,' \
  429. 'pkgname' \
  430. 'epoch' \
  431. 'pkgver' \
  432. 'pkgrel'
  433. printf '@sub_pkgrel,'
  434. printf '`architectures`.`id`'
  435. printf ' FROM `links`'
  436. printf ' JOIN `architectures`'
  437. printf ' ON `links`.`architecture`=`architectures`.`name`'
  438. printf ' JOIN `architectures` AS `ba_a`'
  439. printf ' ON `ba_a`.`name`=`links`.`build_architecture`'
  440. mysql_join_architectures_build_assignments 'ba_a'
  441. printf ' AND `build_assignments`.`package_source`=%s' \
  442. "${package_source_id}"
  443. printf ' WHERE `links`.`type`="arch";\n'
  444. printf 'INSERT IGNORE INTO `binary_packages_in_repositories` ('
  445. printf ' `package`,'
  446. printf '`repository`'
  447. printf ') SELECT'
  448. printf ' `binary_packages`.`id`,'
  449. printf '%s' \
  450. "${current_repository_id}"
  451. printf ' FROM `links`'
  452. printf ' JOIN `architectures`'
  453. printf ' ON `architectures`.`name`=`links`.`architecture`'
  454. mysql_join_architectures_binary_packages
  455. printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel'
  456. printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
  457. 'pkgname' 'pkgname' \
  458. 'epoch' 'epoch' \
  459. 'pkgver' 'pkgver' \
  460. 'pkgrel' 'pkgrel'
  461. printf ';\n'
  462. if [ "${current_repository_id}" -eq "${repository_ids__any_build_list}" ] || \
  463. [ "${current_repository_id}" -eq "${repository_ids__any_to_be_decided}" ]; then
  464. printf 'DELETE `binary_packages_in_repositories`'
  465. printf ' FROM `links`'
  466. printf ' JOIN `architectures`'
  467. printf ' ON `architectures`.`name`=`links`.`architecture`'
  468. mysql_join_architectures_binary_packages
  469. printf ' AND `binary_packages`.`pkgname`=`links`.`pkgname`'
  470. printf ' AND ('
  471. printf '`binary_packages`.`sub_pkgrel`!=@sub_pkgrel'
  472. printf ' OR `binary_packages`.`%s`!=`links`.`%s`' \
  473. 'epoch' 'epoch' \
  474. 'pkgver' 'pkgver' \
  475. 'pkgrel' 'pkgrel'
  476. printf ')'
  477. mysql_join_binary_packages_binary_packages_in_repositories
  478. printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
  479. "${current_repository_id}"
  480. printf ';\n'
  481. printf 'DELETE `binary_packages`'
  482. printf ' FROM `links`'
  483. printf ' JOIN `architectures`'
  484. printf ' ON `architectures`.`name`=`links`.`architecture`'
  485. mysql_join_architectures_binary_packages
  486. printf ' AND `binary_packages`.`pkgname`=`links`.`pkgname`'
  487. printf ' AND ('
  488. printf '`binary_packages`.`sub_pkgrel`!=@sub_pkgrel'
  489. printf ' OR `binary_packages`.`%s`!=`links`.`%s`' \
  490. 'epoch' 'epoch' \
  491. 'pkgver' 'pkgver' \
  492. 'pkgrel' 'pkgrel'
  493. printf ') LEFT'
  494. mysql_join_binary_packages_binary_packages_in_repositories
  495. printf ' WHERE `binary_packages_in_repositories`.`id` IS NULL'
  496. printf ' AND `binary_packages`.`sha512sum` IS NULL;\n'
  497. fi
  498. printf 'INSERT IGNORE INTO `install_targets` (`name`)'
  499. printf ' SELECT `links`.`install_target_name`'
  500. printf ' FROM `links`;\n'
  501. printf 'INSERT IGNORE INTO `versions` (`epoch`,`version`)'
  502. printf ' SELECT'
  503. printf ' `links`.`install_target_epoch`,'
  504. printf '`links`.`install_target_version`'
  505. printf ' FROM `links`'
  506. printf ' WHERE `links`.`install_target_epoch` IS NOT NULL'
  507. printf ' AND `links`.`install_target_version` IS NOT NULL'
  508. printf ';\n'
  509. for link in 'groups' 'provides' 'makedepends' 'checkdepends' 'rundepends'; do
  510. case "${link}" in
  511. 'groups'|'provides')
  512. printf 'INSERT IGNORE INTO `install_target_providers` ('
  513. printf '`package`,'
  514. printf '`install_target`,'
  515. printf '`version`,'
  516. printf '`install_target_is_group`'
  517. printf ') SELECT'
  518. printf ' `binary_packages`.`id`,'
  519. printf '`install_targets`.`id`,'
  520. printf '`versions`.`id`,'
  521. if [ "${link}" = 'group' ]; then
  522. printf '1'
  523. else
  524. printf '0'
  525. fi
  526. ;;
  527. 'makedepends'|'checkdepends'|'rundepends')
  528. printf 'INSERT IGNORE INTO `dependencies` ('
  529. printf '`dependent`,'
  530. printf '`depending_on`,'
  531. printf '`dependency_type`,'
  532. printf '`version`,'
  533. printf '`version_relation`'
  534. printf ') SELECT'
  535. printf ' `binary_packages`.`id`,'
  536. printf '`install_targets`.`id`,'
  537. printf '`dependency_types`.`id`,'
  538. printf '`versions`.`id`,'
  539. printf '`links`.`version_relation`'
  540. ;;
  541. esac
  542. printf ' FROM `links`'
  543. printf ' JOIN `architectures`'
  544. printf ' ON `architectures`.`name`=`links`.`architecture`'
  545. mysql_join_architectures_binary_packages
  546. printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel'
  547. printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
  548. 'pkgname' 'pkgname' \
  549. 'epoch' 'epoch' \
  550. 'pkgver' 'pkgver' \
  551. 'pkgrel' 'pkgrel'
  552. printf ' JOIN `install_targets`'
  553. printf ' ON `install_targets`.`name`=`links`.`install_target_name`'
  554. printf ' JOIN `versions`'
  555. printf ' ON `versions`.`epoch`=`links`.`install_target_epoch`'
  556. printf ' AND `versions`.`version`=`links`.`install_target_version`'
  557. if [ "${link}" = 'makedepends' ] || \
  558. [ "${link}" = 'checkdepends' ] || \
  559. [ "${link}" = 'rundepends' ]; then
  560. printf ' JOIN `dependency_types`'
  561. printf ' ON `dependency_types`.`name`="%s"' \
  562. "${link%depends}"
  563. fi
  564. printf ' WHERE `links`.`type`="%s";\n' \
  565. "${link}"
  566. done
  567. printf 'DROP TEMPORARY TABLE `links`;\n'
  568. } | \
  569. mysql_run_query
  570. )
  571. }
  572. # mysql_sanity_check
  573. # do a sanity check on the mysql database
  574. mysql_sanity_check() {
  575. {
  576. printf 'SELECT CONCAT('
  577. printf '"\\"",'
  578. printf '`ba_arch`.`name`,'
  579. printf '"\\" build-assignment building \\"",'
  580. printf '`bp_arch`.`name`,'
  581. printf '"\\" binary package: ",'
  582. printf '`binary_packages`.`pkgname`'
  583. printf ')'
  584. printf ' FROM `binary_packages`'
  585. mysql_join_binary_packages_build_assignments
  586. mysql_join_binary_packages_architectures '' 'bp_arch'
  587. mysql_join_build_assignments_architectures '' 'ba_arch'
  588. printf ' LEFT JOIN `architecture_compatibilities`'
  589. printf ' ON `architecture_compatibilities`.`runs_on`=`build_assignments`.`architecture`'
  590. printf ' AND `architecture_compatibilities`.`built_for`=`binary_packages`.`architecture`'
  591. printf ' AND `architecture_compatibilities`.`fully_compatible`'
  592. printf ' WHERE `architecture_compatibilities`.`id` IS NULL;\n'
  593. printf 'SELECT DISTINCT CONCAT("package multiple times in equally stable repositories: ",`a_ra`.`name`,"/{",`a_r`.`name`,",",`b_r`.`name`,"}/",`a`.`pkgname`)'
  594. printf ' FROM `binary_packages` AS `a`'
  595. mysql_join_binary_packages_binary_packages_in_repositories 'a' 'a_bir'
  596. mysql_join_binary_packages_in_repositories_repositories 'a_bir' 'a_r'
  597. mysql_join_repositories_architectures 'a_r' 'a_ra'
  598. printf ' JOIN `repositories` AS `b_r`'
  599. printf ' ON `a_r`.`stability`=`b_r`.`stability`'
  600. printf ' AND `a_r`.`architecture`=`b_r`.`architecture`'
  601. mysql_join_repositories_binary_packages_in_repositories 'b_r' 'b_bir'
  602. mysql_join_binary_packages_in_repositories_binary_packages 'b_bir' 'b'
  603. printf ' AND `a`.`pkgname`=`b`.`pkgname`'
  604. printf ' AND `a`.`id`!=`b`.`id`'
  605. printf ' AND ('
  606. printf '`a_r`.`architecture`!=%s' \
  607. "${architecture_ids__any}"
  608. printf ' OR `a`.`architecture`=`b`.`architecture`'
  609. printf ')'
  610. printf ' WHERE `a_r`.`stability` NOT IN (%s,%s)' \
  611. "${repository_stability_ids__forbidden}" \
  612. "${repository_stability_ids__virtual}"
  613. printf ' AND `b_r`.`stability` NOT IN (%s,%s);\n' \
  614. "${repository_stability_ids__forbidden}" \
  615. "${repository_stability_ids__virtual}"
  616. printf 'SELECT DISTINCT CONCAT("split-package with differing sub_pkgrels on the build-list: ",`a`.`pkgname`)'
  617. printf ' FROM `binary_packages` AS `a`'
  618. printf ' JOIN `binary_packages` AS `b`'
  619. printf ' ON `a`.`build_assignment`=`b`.`build_assignment`'
  620. mysql_join_binary_packages_binary_packages_in_repositories 'a' 'a_bir'
  621. mysql_join_binary_packages_binary_packages_in_repositories 'b' 'b_bir'
  622. printf ' WHERE `a`.`sub_pkgrel`!=`b`.`sub_pkgrel`'
  623. printf ' AND `%s_bir`.`repository`=%s' \
  624. 'a' "${repository_ids__any_build_list}" \
  625. 'b' "${repository_ids__any_build_list}"
  626. printf ';\n'
  627. printf 'SELECT DISTINCT CONCAT("non-virtual binary-package without checksum: ",'
  628. mysql_package_name_query
  629. printf ') FROM `binary_packages`'
  630. mysql_join_binary_packages_architectures
  631. mysql_join_binary_packages_binary_packages_in_repositories
  632. mysql_join_binary_packages_in_repositories_repositories
  633. printf ' WHERE `repositories`.`is_on_master_mirror`'
  634. printf ' AND `binary_packages`.`sha512sum` IS NULL;\n'
  635. } | \
  636. mysql_run_query | \
  637. sed '
  638. s,^,<font color="#FF0000">,
  639. s,$,</font>,
  640. '
  641. ( # new shell is intentional
  642. temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_sanity_check.XXXXXXXXXX' --tmpdir)
  643. trap 'rm -rf --one-file-system "${temp_dir}"' EXIT
  644. {
  645. printf 'SELECT DISTINCT `architectures`.`name`'
  646. printf ' FROM `architectures`'
  647. printf ' WHERE `architectures`.`id`!=%s;\n' \
  648. "${architecture_ids__any}"
  649. } | \
  650. mysql_run_query | \
  651. while read -r arch; do
  652. for dir in $(ls_master_mirror "${arch}"); do
  653. ls_master_mirror "${arch}/${dir}" | \
  654. sed '
  655. /\.pkg\.tar\.xz$/!d
  656. s|^|'"${arch}"'/'"${dir}"'/|
  657. '
  658. done
  659. done | \
  660. sort > \
  661. "${temp_dir}/master-mirror-listing"
  662. {
  663. printf 'SELECT CONCAT(`r_a`.`name`,"/",`repositories`.`name`,"/",'
  664. mysql_package_name_query
  665. printf ') FROM `binary_packages_in_repositories`'
  666. mysql_join_binary_packages_in_repositories_binary_packages
  667. mysql_join_binary_packages_architectures
  668. mysql_join_binary_packages_in_repositories_repositories
  669. mysql_join_repositories_architectures '' 'r_a'
  670. printf ' WHERE `repositories`.`is_on_master_mirror`'
  671. } | \
  672. mysql_run_query | \
  673. sed '
  674. s,\t,/,
  675. s,\t,/,
  676. s,\t,-,
  677. s,\t,:,
  678. s,\t,-,
  679. s,\t,.,
  680. s,\t,-,
  681. ' | \
  682. sort > \
  683. "${temp_dir}/mysql-packages"
  684. diff -u \
  685. "${temp_dir}/master-mirror-listing" \
  686. "${temp_dir}/mysql-packages"
  687. # shellcheck disable=SC2041
  688. ls_master_mirror 'pool' | \
  689. sed '
  690. /\.pkg\.tar\.xz\(\.sig\)\?$/ !d
  691. ' | \
  692. sort -u > \
  693. "${temp_dir}/master-mirror-pool"
  694. {
  695. printf 'SELECT '
  696. mysql_package_name_query
  697. printf ' FROM `binary_packages`'
  698. mysql_join_binary_packages_architectures
  699. printf ' WHERE NOT EXISTS ('
  700. printf 'SELECT 1'
  701. printf ' FROM `binary_packages_in_repositories`'
  702. mysql_join_binary_packages_in_repositories_repositories
  703. printf ' WHERE NOT `repositories`.`is_on_master_mirror`'
  704. printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  705. printf ') OR EXISTS ('
  706. printf 'SELECT 1'
  707. printf ' FROM `binary_packages_in_repositories`'
  708. mysql_join_binary_packages_in_repositories_repositories
  709. printf ' WHERE `repositories`.`is_on_master_mirror`'
  710. printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  711. printf ')'
  712. } | \
  713. mysql_run_query | \
  714. sed '
  715. s/^.*$/\0\n\0.sig/
  716. ' | \
  717. sort > \
  718. "${temp_dir}/mysql-packages-pool"
  719. diff -u \
  720. "${temp_dir}/master-mirror-pool" \
  721. "${temp_dir}/mysql-packages-pool"
  722. )
  723. }
  724. # mysql_find_build_assignment_loops
  725. # recreate the `build_dependency_loops` table
  726. mysql_find_build_assignment_loops() {
  727. ( # new shell is intentional
  728. tmp_file=$(mktemp 'tmp.mysql-functions.mysql_find_build_assignment_loops.XXXXXXXXXX' --tmpdir)
  729. trap 'rm "${tmp_file}"' EXIT
  730. {
  731. printf 'SELECT DISTINCT `architectures`.`id`'
  732. printf ' FROM `architectures`'
  733. printf ' WHERE `architectures`.`id`!=%s;\n' \
  734. "${architecture_ids__any}"
  735. } | \
  736. mysql_run_query | \
  737. while read -r arch_id; do
  738. {
  739. printf 'SELECT DISTINCT'
  740. printf ' `ncy_ba`.`id`,'
  741. printf '`nt_ba`.`id`'
  742. printf ' FROM `dependencies`'
  743. mysql_join_dependencies_dependency_types
  744. mysql_join_dependencies_install_target_providers_with_versions
  745. mysql_join_install_target_providers_binary_packages '' 'ncy_bp'
  746. mysql_join_dependencies_binary_packages '' 'nt_bp'
  747. for which in 'ncy' 'nt'; do
  748. mysql_join_binary_packages_binary_packages_in_repositories "${which}"'_bp' "${which}"'_bpir'
  749. mysql_join_binary_packages_build_assignments "${which}"'_bp' "${which}"'_ba'
  750. printf ' JOIN `architecture_compatibilities` AS `%s_ac`' \
  751. "${which}"
  752. printf ' ON `%s_ac`.`built_for`=`%s_ba`.`architecture`' \
  753. "${which}" "${which}"
  754. printf ' AND `%s_ac`.`runs_on`=%s' \
  755. "${which}" "${arch_id}"
  756. printf ' AND `%s_ac`.`fully_compatible`' \
  757. "${which}"
  758. done
  759. printf ' WHERE `nt_bpir`.`repository`=%s' \
  760. "${repository_ids__any_build_list}"
  761. printf ' AND `ncy_bpir`.`repository`=%s' \
  762. "${repository_ids__any_build_list}"
  763. printf ' AND `dependency_types`.`relevant_for_building`'
  764. printf ' AND ('
  765. printf '`dependency_types`.`relevant_for_binary_packages`'
  766. printf ' OR `nt_bp`.`pkgname` LIKE "haskell-%%"'
  767. printf ' OR NOT EXISTS ('
  768. printf 'SELECT 1'
  769. printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`'
  770. mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
  771. printf ' AND `subst_r`.`is_on_master_mirror`'
  772. mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
  773. mysql_join_binary_packages_build_assignments 'subst_bp' 'subst_ba'
  774. printf ' JOIN `architecture_compatibilities` AS `subst_ac`'
  775. printf ' ON `subst_ac`.`built_for`=`subst_ba`.`architecture`'
  776. printf ' AND `subst_ac`.`runs_on`=%s' \
  777. "${arch_id}"
  778. printf ' AND `subst_ac`.`fully_compatible`'
  779. mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp'
  780. mysql_join_install_target_providers_versions 'subst_itp' 'subst_itp_v'
  781. printf ' WHERE `subst_itp`.`install_target`=`dependencies`.`depending_on`'
  782. printf ' AND '
  783. mysql_query_ordering_correct \
  784. '`dependencies_versions`.`order`' \
  785. '`subst_itp_v`.`order`' \
  786. '`dependencies`.`version_relation`'
  787. printf ')'
  788. printf ');\n'
  789. } | \
  790. mysql_run_query | \
  791. tr '\t' ' ' | \
  792. tsort 2>&1 >/dev/null | \
  793. sed 's/^tsort:\s*//'
  794. done | \
  795. awk '
  796. BEGIN {
  797. i=0
  798. };
  799. /^-: input contains a loop:$/ {
  800. i=i+1
  801. };
  802. !/^-: input contains a loop:$/ {
  803. print i "\t" $1
  804. }
  805. ' > \
  806. "${tmp_file}"
  807. {
  808. printf 'DELETE FROM `build_dependency_loops`;\n'
  809. printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `build_dependency_loops` (`loop`,`build_assignment`);\n' \
  810. "${tmp_file}"
  811. } | \
  812. mysql_run_query
  813. )
  814. }
  815. # mysql_cleanup [dry]
  816. # clean up left overs from mysql database
  817. mysql_cleanup() {
  818. local operator
  819. if [ "$#" = '0' ]; then
  820. operator='DELETE'
  821. elif [ "$#" = '1' ] && [ "x$1" = 'xdry' ]; then
  822. operator='SELECT COUNT(1)'
  823. else
  824. >&2 echo 'Unknown parameter'
  825. >&2 echo 'Call "mysql_clean_up" or "mysql_clean_up dry".'
  826. exit 2
  827. fi
  828. {
  829. # remove to-be-decided binary_packages_in_repositories
  830. printf '%s ' \
  831. "${operator}"
  832. if [ "${operator}" = 'DELETE' ]; then
  833. printf '`binary_packages_in_repositories` '
  834. fi
  835. printf 'FROM `binary_packages`'
  836. mysql_join_binary_packages_binary_packages_in_repositories
  837. printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
  838. "${repository_ids__any_to_be_decided}"
  839. # remove virtual binary_packages w/o binary_packages_in_repositories
  840. printf '%s ' \
  841. "${operator}"
  842. if [ "${operator}" = 'DELETE' ]; then
  843. printf '`binary_packages` '
  844. fi
  845. printf 'FROM `binary_packages`'
  846. printf ' WHERE `binary_packages`.`sha512sum` IS NULL'
  847. printf ' AND NOT EXISTS ('
  848. printf 'SELECT 1'
  849. printf ' FROM `binary_packages_in_repositories` '
  850. printf 'WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  851. printf ');\n'
  852. # remove build_assignments w/o binary_package
  853. printf '%s FROM `build_assignments` ' \
  854. "${operator}"
  855. printf 'WHERE NOT EXISTS '
  856. printf '('
  857. printf 'SELECT 1'
  858. printf ' FROM `binary_packages` '
  859. printf 'WHERE `binary_packages`.`build_assignment`=`build_assignments`.`id`'
  860. printf ');\n'
  861. # remove failed_builds with unbroken build_assignments
  862. printf '%s ' \
  863. "${operator}"
  864. if [ "${operator}" = 'DELETE' ]; then
  865. printf '`failed_builds` '
  866. fi
  867. printf 'FROM `failed_builds` '
  868. mysql_join_failed_builds_build_assignments
  869. printf 'WHERE NOT `build_assignments`.`is_broken`'
  870. printf ';\n'
  871. # remove package_sources w/o build_assignment
  872. printf '%s FROM `package_sources` ' \
  873. "${operator}"
  874. printf 'WHERE NOT EXISTS '
  875. printf '('
  876. printf 'SELECT 1'
  877. printf ' FROM `build_assignments` '
  878. printf 'WHERE `build_assignments`.`package_source`=`package_sources`.`id`'
  879. printf ');\n'
  880. # remove jobs from build slaves that are not on the build-list
  881. if [ "${operator}" = 'DELETE' ]; then
  882. printf 'UPDATE `binary_packages_in_repositories`'
  883. else
  884. printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`)'
  885. printf ' FROM `binary_packages_in_repositories`'
  886. fi
  887. mysql_join_binary_packages_in_repositories_binary_packages
  888. printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
  889. "${repository_ids__any_build_list}"
  890. printf ' RIGHT'
  891. mysql_join_binary_packages_build_slaves
  892. if [ "${operator}" = 'DELETE' ]; then
  893. printf ' SET `build_slaves`.`currently_building`=NULL'
  894. fi
  895. printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
  896. printf ' AND `binary_packages_in_repositories`.`id` IS NULL;\n'
  897. # remove build orders from build slaves which have not connected within 1h
  898. if [ "${operator}" = 'DELETE' ]; then
  899. printf 'UPDATE `build_slaves`'
  900. printf ' SET `build_slaves`.`currently_building`=NULL'
  901. else
  902. printf 'SELECT COUNT(1)'
  903. printf ' FROM `build_slaves`'
  904. fi
  905. printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
  906. printf ' AND TIMEDIFF(NOW(),('
  907. printf 'SELECT MAX(`ssh_log`.`date`)'
  908. printf ' FROM `ssh_log`'
  909. printf ' WHERE `ssh_log`.`build_slave`=`build_slaves`.`id`'
  910. printf ')) > "1:00:00";\n'
  911. # remove `last_action`, `trials` and `logged_lines` from buildslaves without an assignment
  912. if [ "${operator}" = 'DELETE' ]; then
  913. printf 'UPDATE `build_slaves`'
  914. printf ' SET `build_slaves`.`last_action`=NULL,'
  915. printf ' `build_slaves`.`logged_lines`=NULL,'
  916. printf ' `build_slaves`.`trials`=NULL'
  917. else
  918. printf 'SELECT COUNT(1)'
  919. printf ' FROM `build_slaves`'
  920. fi
  921. printf ' WHERE `build_slaves`.`currently_building` IS NULL'
  922. printf ' AND (`build_slaves`.`last_action` IS NOT NULL'
  923. printf ' OR `build_slaves`.`logged_lines` IS NOT NULL'
  924. printf ' OR `build_slaves`.`trials` IS NOT NULL);\n'
  925. # shrink the priorities
  926. printf 'CREATE TEMPORARY TABLE `ps%s`(`old_p` SMALLINT, `new_p` SMALLINT, UNIQUE KEY `old_p`(`old_p`));\n' \
  927. '' '_2' '_3'
  928. printf 'INSERT INTO `ps`(`old_p`) VALUES (0);\n'
  929. printf 'INSERT IGNORE INTO `ps`(`old_p`)'
  930. printf ' SELECT `build_assignments`.`priority`'
  931. printf ' FROM `build_assignments`;\n'
  932. for copy in '2' '3'; do
  933. printf 'INSERT INTO `ps_%s`(`old_p`)' \
  934. "${copy}"
  935. printf ' SELECT `ps`.`old_p`'
  936. printf ' FROM `ps`;\n'
  937. done
  938. printf 'UPDATE `ps`'
  939. printf ' LEFT JOIN ('
  940. printf 'SELECT'
  941. printf ' `ps_2`.`old_p` AS `limit_p`,'
  942. printf 'COUNT(`ps_3`.`old_p`) AS `count_p`'
  943. printf ' FROM `ps_2`'
  944. printf ' JOIN `ps_3`'
  945. printf ' ON `ps_3`.`old_p`<`ps_2`.`old_p`'
  946. printf ' GROUP BY `ps_2`.`old_p`'
  947. printf ') AS `ps_q`'
  948. printf ' ON `ps_q`.`limit_p`=`ps`.`old_p`'
  949. printf ' SET `ps`.`new_p`=IFNULL(`ps_q`.`count_p`,0)'
  950. printf ';\n'
  951. printf 'UPDATE `build_assignments`'
  952. printf ' JOIN `ps`'
  953. printf ' ON `build_assignments`.`priority`=`ps`.`old_p`'
  954. printf ' SET `build_assignments`.`priority`=`ps`.`new_p`;\n'
  955. printf 'DROP TEMPORARY TABLE `ps%s`;\n' \
  956. '' '_2' '_3'
  957. } | \
  958. mysql_run_query 'unimportant'
  959. }
  960. # mysql_query_has_pending_dependencies builder-architecture.id `build_assignment`.`id`
  961. # print a mysql query giving whether dependencies are pending
  962. mysql_query_has_pending_dependencies() {
  963. # we have pending dependencies ...
  964. printf 'EXISTS ('
  965. printf 'SELECT 1'
  966. printf ' FROM `binary_packages` AS `todos`'
  967. mysql_join_binary_packages_binary_packages_in_repositories 'todos' 'todo_bpirs'
  968. printf ' AND `todo_bpirs`.`repository`=%s' \
  969. "${repository_ids__any_build_list}"
  970. # ... if any dependency ...
  971. mysql_join_binary_packages_dependencies 'todos' 'l_deps'
  972. mysql_join_dependencies_versions 'l_deps' 'l_dep_vs'
  973. mysql_join_dependencies_dependency_types 'l_deps' 'l_dep_ts'
  974. mysql_join_dependencies_install_targets 'l_deps'
  975. # ... is relevant for building ...
  976. printf ' AND `l_dep_ts`.`relevant_for_building`'
  977. printf ' WHERE `todos`.`build_assignment`=%s' \
  978. "$2"
  979. printf ' AND IF('
  980. # ... and if (a) relevant for binary_packages ...
  981. printf '`l_dep_ts`.`relevant_for_binary_packages`'
  982. # (which includes makedependencies which are haskell packages!)
  983. printf ' OR `install_targets`.`name` LIKE "haskell-%%",'
  984. # ... has some unbuilt provider ...
  985. printf 'EXISTS ('
  986. printf 'SELECT 1'
  987. printf ' FROM `install_target_providers` AS `l_itps`'
  988. mysql_join_install_target_providers_versions 'l_itps' 'l_itp_vs'
  989. mysql_join_install_target_providers_binary_packages 'l_itps' 'dep_bp'
  990. mysql_join_binary_packages_binary_packages_in_repositories 'dep_bp' 'dep_bpir'
  991. printf ' AND `dep_bpir`.`repository`=%s' \
  992. "${repository_ids__any_build_list}"
  993. printf ' JOIN `architecture_compatibilities`'
  994. printf ' ON `architecture_compatibilities`.`built_for`=`dep_bp`.`architecture`'
  995. printf ' AND `architecture_compatibilities`.`fully_compatible`'
  996. printf ' WHERE `l_itps`.`install_target`=`l_deps`.`depending_on`'
  997. printf ' AND `dep_bp`.`build_assignment`!=%s' \
  998. "$2"
  999. printf ' AND `architecture_compatibilities`.`runs_on`=%s' \
  1000. "$1"
  1001. # version is irelevant: either the package is built and we don't
  1002. # care or the package is not built and we don't know
  1003. printf '),'
  1004. # ... or (b) not relevant for binary_packages (e.g. a makedepends) has
  1005. # no built provider
  1006. printf 'NOT EXISTS ('
  1007. printf 'SELECT 1'
  1008. printf ' FROM `install_target_providers` AS `l_itps`'
  1009. mysql_join_install_target_providers_versions 'l_itps' 'l_itp_vs'
  1010. mysql_join_install_target_providers_binary_packages 'l_itps' 'dep_bp'
  1011. mysql_join_binary_packages_binary_packages_in_repositories 'dep_bp' 'dep_bpir'
  1012. mysql_join_binary_packages_in_repositories_repositories 'dep_bpir' 'dep_r'
  1013. printf ' WHERE `l_itps`.`install_target`=`l_deps`.`depending_on`'
  1014. printf ' AND ('
  1015. printf '`dep_r`.`is_on_master_mirror`'
  1016. printf ' OR `dep_bp`.`build_assignment`=%s' \
  1017. "$2"
  1018. printf ') AND `dep_r`.`architecture`=%s' \
  1019. "$1"
  1020. printf ' AND '
  1021. mysql_query_ordering_correct \
  1022. '`l_dep_vs`.`order`' \
  1023. '`l_itp_vs`.`order`' \
  1024. '`l_deps`.`version_relation`'
  1025. printf ')'
  1026. printf ')'
  1027. printf ')'
  1028. }
  1029. # mysql_query_generate_packages_with_pending_dependencies
  1030. # print the query to generate the temporary table
  1031. # `packages_with_pending_dependencies`
  1032. mysql_query_generate_packages_with_pending_dependencies() {
  1033. printf 'CREATE TEMPORARY TABLE `install_target_statuses`('
  1034. printf '`id` BIGINT NOT NULL,'
  1035. printf '`version_relation` VARCHAR(2) NOT NULL,'
  1036. printf '`version_order` BIGINT NOT NULL,'
  1037. printf '`builder_architecture` SMALLINT NOT NULL,'
  1038. printf '`exists_built` BIT NOT NULL,'
  1039. printf '`exists_unbuilt` BIT NOT NULL,'
  1040. printf 'UNIQUE KEY `content`(`id`,`builder_architecture`),'
  1041. printf 'KEY(`id`),'
  1042. printf 'KEY(`builder_architecture`)'
  1043. printf ');\n'
  1044. printf 'INSERT IGNORE INTO `install_target_statuses`('
  1045. printf '`id`,'
  1046. printf '`version_relation`,'
  1047. printf '`version_order`,'
  1048. printf '`builder_architecture`,'
  1049. printf '`exists_built`,'
  1050. printf '`exists_unbuilt`'
  1051. printf ') SELECT '
  1052. printf '`dependencies`.`%s`,' \
  1053. 'depending_on' \
  1054. 'version_relation'
  1055. printf '`versions`.`order`,'
  1056. printf '`architectures`.`id`,0,0'
  1057. printf ' FROM `dependencies`'
  1058. mysql_join_dependencies_versions
  1059. printf ' JOIN `architectures`;\n'
  1060. printf 'UPDATE `install_target_statuses`'
  1061. mysql_join_install_targets_install_target_providers 'install_target_statuses'
  1062. mysql_join_install_target_providers_versions
  1063. printf ' AND'
  1064. mysql_query_ordering_correct \
  1065. '`install_target_statuses`.`version_order`' \
  1066. '`versions`.`order`' \
  1067. '`install_target_statuses`.`version_relation`'
  1068. mysql_join_install_target_providers_binary_packages
  1069. printf ' AND ('
  1070. printf '`binary_packages`.`architecture`=`install_target_statuses`.`builder_architecture`'
  1071. printf ' OR `binary_packages`.`architecture`=%s' \
  1072. "${architecture_ids__any}"
  1073. printf ' OR `install_target_statuses`.`builder_architecture`=%s' \
  1074. "${architecture_ids__any}"
  1075. printf ')'
  1076. mysql_join_binary_packages_binary_packages_in_repositories
  1077. printf ' SET `install_target_statuses`.`exists_unbuilt`=1'
  1078. printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
  1079. "${repository_ids__any_build_list}"
  1080. printf 'UPDATE `install_target_statuses`'
  1081. mysql_join_install_targets_install_target_providers 'install_target_statuses'
  1082. mysql_join_install_target_providers_versions
  1083. printf ' AND'
  1084. mysql_query_ordering_correct \
  1085. '`install_target_statuses`.`version_order`' \
  1086. '`versions`.`order`' \
  1087. '`install_target_statuses`.`version_relation`'
  1088. mysql_join_install_target_providers_binary_packages
  1089. printf ' AND ('
  1090. printf '`binary_packages`.`architecture`=`install_target_statuses`.`builder_architecture`'
  1091. printf ' OR `binary_packages`.`architecture`=%s' \
  1092. "${architecture_ids__any}"
  1093. printf ' OR `install_target_statuses`.`builder_architecture`=%s' \
  1094. "${architecture_ids__any}"
  1095. printf ')'
  1096. mysql_join_binary_packages_binary_packages_in_repositories
  1097. mysql_join_binary_packages_in_repositories_repositories
  1098. printf ' SET `install_target_statuses`.`exists_built`=1'
  1099. printf ' WHERE `repositories`.`is_on_master_mirror`;\n'
  1100. printf 'CREATE TEMPORARY TABLE `packages_with_pending_dependencies`('
  1101. printf '`builder_architecture` SMALLINT NOT NULL,'
  1102. printf '`build_assignment` BIGINT NOT NULL,'
  1103. printf 'UNIQUE KEY `content`(`builder_architecture`,`build_assignment`),'
  1104. printf 'KEY(`builder_architecture`),'
  1105. printf 'KEY(`build_assignment`)'
  1106. printf ');\n'
  1107. printf 'INSERT IGNORE INTO `packages_with_pending_dependencies`('
  1108. printf '`builder_architecture`,'
  1109. printf '`build_assignment`'
  1110. printf ') SELECT'
  1111. printf ' `install_target_statuses`.`builder_architecture`,'
  1112. printf '`build_assignments`.`id`'
  1113. printf ' FROM `install_target_statuses`'
  1114. printf ' JOIN `install_targets`'
  1115. printf ' ON `install_targets`.`id`=`install_target_statuses`.`id`'
  1116. mysql_join_install_targets_dependencies
  1117. printf ' AND `dependencies`.`version_relation`=`install_target_statuses`.`version_relation`'
  1118. mysql_join_dependencies_versions
  1119. printf ' AND `versions`.`order`=`install_target_statuses`.`version_order`'
  1120. mysql_join_dependencies_dependency_types
  1121. printf ' AND `dependency_types`.`relevant_for_building`'
  1122. printf ' AND ('
  1123. printf 'NOT `install_target_statuses`.`exists_built`'
  1124. printf 'OR ('
  1125. printf '`install_target_statuses`.`exists_unbuilt`'
  1126. printf ' AND ('
  1127. printf '`dependency_types`.`relevant_for_binary_packages`'
  1128. printf ' OR `install_targets`.`name` LIKE "haskell-%%"'
  1129. printf ')'
  1130. printf ')'
  1131. printf ')'
  1132. mysql_join_dependencies_binary_packages
  1133. mysql_join_binary_packages_binary_packages_in_repositories
  1134. printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
  1135. "${repository_ids__any_build_list}"
  1136. mysql_join_binary_packages_build_assignments
  1137. printf ' WHERE `build_assignments`.`architecture`=`install_target_statuses`.`builder_architecture`'
  1138. printf ' OR `build_assignments`.`architecture`=%s' \
  1139. "${architecture_ids__any}"
  1140. printf ' OR `install_target_statuses`.`builder_architecture`=%s' \
  1141. "${architecture_ids__any}"
  1142. printf ';\n'
  1143. printf 'DROP TEMPORARY TABLE `install_target_statuses`;\n'
  1144. }
  1145. # mysql_query_is_part_of_loop `build_assignment`.`id`
  1146. # print a mysql query giving whether the package is part of a loop
  1147. mysql_query_is_part_of_loop() {
  1148. printf 'EXISTS ('
  1149. printf 'SELECT 1'
  1150. printf ' FROM `build_dependency_loops`'
  1151. printf ' WHERE `build_dependency_loops`.`build_assignment`=%s' \
  1152. "$1"
  1153. printf ')'
  1154. }
  1155. # mysql_query_select_pkgbase_and_revision
  1156. # print the part of a mysql query giving:
  1157. # pkgbase git_revision mod_git_revision upstream_package_repository
  1158. mysql_query_select_pkgbase_and_revision() {
  1159. printf '`package_sources`.`%s`,' \
  1160. 'pkgbase' \
  1161. 'git_revision' \
  1162. 'mod_git_revision'
  1163. printf '`upstream_repositories`.`name`'
  1164. printf ' FROM `build_assignments`'
  1165. mysql_join_build_assignments_package_sources
  1166. mysql_join_package_sources_upstream_repositories
  1167. }
  1168. # mysql_package_name_query [binary_packages] [architectures]
  1169. # print a mysql query of the full name of a package file
  1170. mysql_package_name_query() {
  1171. local bp_name="${1:-binary_packages}"
  1172. local a_name="${2:-architectures}"
  1173. printf 'CONCAT('
  1174. printf '`%s`.`pkgname`,"-",' "${bp_name}"
  1175. printf 'IF(`%s`.`epoch`=0,"",CONCAT(`%s`.`epoch`,":")),' "${bp_name}" "${bp_name}"
  1176. printf '`%s`.`pkgver`,"-",' "${bp_name}"
  1177. printf '`%s`.`pkgrel`,' "${bp_name}"
  1178. printf 'IF(`%s`.`sub_pkgrel_omitted`,"",CONCAT(".",`%s`.`sub_pkgrel`)),"-",' "${bp_name}" "${bp_name}"
  1179. printf '`%s`.`name`,".pkg.tar.xz"' "${a_name}"
  1180. printf ')'
  1181. }
  1182. # mysql_join_*_*
  1183. # print 'JOIN' part of mysql query to connect the respective tables
  1184. # these functions take 2 optional arguments, acting as aliases for
  1185. # the tables
  1186. # mysql_join__generic $table_a $column_a $table_b $column_b
  1187. # create mysql_join_${table_a}_${table_b}() function
  1188. mysql_join__generic() {
  1189. eval "$(
  1190. printf 'mysql_join_%s_%s() {\n' "$1" "$3"
  1191. printf ' printf '"'"' JOIN `%s`'"'"'\n' "$3"
  1192. printf ' if [ -n "$2" ]; then\n'
  1193. printf ' printf '"'"' AS `%%s`'"'"' "$2"\n'
  1194. printf ' fi\n'
  1195. printf ' if [ -n "$1" ]; then\n'
  1196. printf ' printf '"'"' ON `%%s`.`%s`='"'"' "$1"\n' "$2"
  1197. printf ' else\n'
  1198. printf ' printf '"'"' ON `%s`.`%s`='"'"'\n' "$1" "$2"
  1199. printf ' fi\n'
  1200. printf ' if [ -n "$2" ]; then\n'
  1201. printf ' printf '"'"'`%%s`.`%s`'"'"' "$2"\n' "$4"
  1202. printf ' else\n'
  1203. printf ' printf '"'"'`%s`.`%s`'"'"'\n' "$3" "$4"
  1204. printf ' fi\n'
  1205. printf '}\n'
  1206. )"
  1207. }
  1208. for link in \
  1209. 'allowed_email_actions:action:email_actions' \
  1210. 'allowed_email_actions:gpg_key:gpg_keys' \
  1211. \
  1212. 'binary_packages:architecture:architectures' \
  1213. 'binary_packages:build_assignment:build_assignments' \
  1214. \
  1215. 'binary_packages_in_repositories:package:binary_packages' \
  1216. 'binary_packages_in_repositories:repository:repositories' \
  1217. \
  1218. 'build_assignments:architecture:architectures' \
  1219. 'build_assignments:package_source:package_sources' \
  1220. \
  1221. 'build_dependency_loops:build_assignment:build_assignments' \
  1222. 'build_dependency_loops:build_assignment build_assignment:binary_packages' \
  1223. \
  1224. 'build_slaves:currently_building:build_assignments' \
  1225. 'build_slaves:currently_building build_assignment:binary_packages' \
  1226. 'build_slaves:ssh_key:ssh_keys' \
  1227. \
  1228. 'dependencies:depending_on:install_targets' \
  1229. 'dependencies:dependent:binary_packages' \
  1230. 'dependencies:dependent package:binary_packages_in_repositories' \
  1231. 'dependencies:dependency_type:dependency_types' \
  1232. 'dependencies:version:versions' \
  1233. \
  1234. 'email_log:action:email_actions' \
  1235. 'email_log:gpg_key:gpg_keys' \
  1236. \
  1237. 'failed_builds:reason:fail_reasons' \
  1238. 'failed_builds:build_assignment:build_assignments' \
  1239. 'failed_builds:build_slave:build_slaves' \
  1240. \
  1241. 'gpg_keys:owner:persons' \
  1242. \
  1243. 'install_target_providers:package:binary_packages' \
  1244. 'install_target_providers:package package:binary_packages_in_repositories' \
  1245. 'install_target_providers:install_target:install_targets' \
  1246. 'install_target_providers:install_target depending_on:dependencies' \
  1247. 'install_target_providers:version:versions' \
  1248. \
  1249. 'package_sources:upstream_package_repository:upstream_repositories' \
  1250. 'package_sources:pkgbase pkgbase:toolchain_order' \
  1251. \
  1252. 'repositories:stability:repository_stabilities' \
  1253. 'repositories:architecture:architectures' \
  1254. \
  1255. 'repository_moves:upstream_package_repository:upstream_repositories' \
  1256. 'repository_moves:upstream_package_repository upstream_package_repository:package_sources' \
  1257. \
  1258. 'ssh_keys:owner:persons' \
  1259. \
  1260. 'ssh_log:build_slave:build_slaves' \
  1261. \
  1262. 'upstream_repositories:git_repository:git_repositories'; do
  1263. # A join for these cannot be done, because it's not clear on what to join:
  1264. # 'repository_stability_relations:more_stable:repository_stabilities'
  1265. # 'repository_stability_relations:less_stable:repository_stabilities'
  1266. table_b="${link##*:}"
  1267. table_a="${link%:*}"
  1268. column_b="${table_a##*:}"
  1269. table_a="${table_a%:*}"
  1270. column_a="${column_b% *}"
  1271. if [ "${column_a}" = "${column_b}" ]; then
  1272. column_b='id'
  1273. else
  1274. column_b="${column_b##* }"
  1275. fi
  1276. mysql_join__generic "${table_a}" "${column_a}" "${table_b}" "${column_b}"
  1277. mysql_join__generic "${table_b}" "${column_b}" "${table_a}" "${column_a}"
  1278. done
  1279. # mysql_join_install_target_providers_dependencies_with_versions() and
  1280. # mysql_join_dependencies_install_target_providers_with_versions() also
  1281. # join via `versions`
  1282. mysql_join_install_target_providers_dependencies_with_versions() {
  1283. mysql_join_install_target_providers_dependencies "$1" "$2"
  1284. mysql_join_install_target_providers_versions "$1" "${1:-install_target_providers}_versions"
  1285. mysql_join_dependencies_versions "$2" "${2:-dependencies}_versions"
  1286. printf ' AND '
  1287. mysql_query_ordering_correct \
  1288. '`'"${2:-dependencies}_versions"'`.`order`' \
  1289. '`'"${1:-install_target_providers}_versions"'`.`order`' \
  1290. '`'"${2:-dependencies}"'`.`version_relation`'
  1291. }
  1292. mysql_join_dependencies_install_target_providers_with_versions() {
  1293. local relation
  1294. mysql_join_dependencies_install_target_providers "$1" "$2"
  1295. mysql_join_install_target_providers_versions "$2" "${2:-install_target_providers}_versions"
  1296. mysql_join_dependencies_versions "$1" "${1:-dependencies}_versions"
  1297. printf ' AND '
  1298. mysql_query_ordering_correct \
  1299. '`'"${1:-dependencies}_versions"'`.`order`' \
  1300. '`'"${2:-install_target_providers}_versions"'`.`order`' \
  1301. '`'"${1:-dependencies}"'`.`version_relation`'
  1302. }
  1303. # mysql_retrieve_static_information
  1304. # retrieve some static information from the database:
  1305. # - ids of architectures -> $architecture_ids__$arch
  1306. # - ids of non-any architectures -> $non_any_architecture_ids
  1307. # - ids of package repositories -> $repository_ids__$arch_$repo
  1308. # - names, paths and heads of git_repositories
  1309. # - ids of repository_stabilities
  1310. mysql_retrieve_static_information() {
  1311. eval "$(
  1312. {
  1313. printf 'SELECT REPLACE(CONCAT('
  1314. printf '"architecture_ids__",'
  1315. printf '`architectures`.`name`,"=",'
  1316. printf '`architectures`.`id`'
  1317. printf '),"-","_")'
  1318. printf ' FROM `architectures`;\n'
  1319. printf 'SELECT CONCAT('
  1320. printf '"non_any_architecture_ids=",'
  1321. printf 'GROUP_CONCAT('
  1322. printf '`architectures`.`id`'
  1323. printf ' ORDER BY `architectures`.`id`'
  1324. printf '))'
  1325. printf ' FROM `architectures`'
  1326. printf ' WHERE `architectures`.`name`!="any";\n'
  1327. printf 'SELECT REPLACE(CONCAT('
  1328. printf '"repository_ids__",'
  1329. printf '`architectures`.`name`,"_",'
  1330. printf '`repositories`.`name`,"=",'
  1331. printf '`repositories`.`id`'
  1332. printf '),"-","_")'
  1333. printf ' FROM `repositories`'
  1334. mysql_join_repositories_architectures
  1335. printf ';\n'
  1336. printf 'SELECT CONCAT('
  1337. printf '"repo_names=\\"",'
  1338. printf 'REPLACE(GROUP_CONCAT(`git_repositories`.`name`),","," "),'
  1339. printf '"\\"")'
  1340. printf ' FROM `git_repositories`'
  1341. printf ' GROUP BY "1";\n'
  1342. printf 'SELECT CONCAT('
  1343. printf '"repo_paths__",'
  1344. printf '`git_repositories`.`name`,"=",'
  1345. printf '"\\"",`git_repositories`.`directory`,"\\";'
  1346. printf 'repo_heads__",'
  1347. printf '`git_repositories`.`name`,"=",'
  1348. printf '"\\"",`git_repositories`.`head`,"\\"")'
  1349. printf ' FROM `git_repositories`;\n'
  1350. printf 'SELECT CONCAT('
  1351. printf '"repository_stability_ids__",'
  1352. printf '`repository_stabilities`.`name`,"=",'
  1353. printf '`repository_stabilities`.`id`)'
  1354. printf ' FROM `repository_stabilities`;\n'
  1355. } | \
  1356. mysql_run_query
  1357. )"
  1358. }
  1359. # mysql_query_and_delete_unneeded_binary_packages
  1360. # print a query which lists and deletes binary_packages which are not
  1361. # linked from binary_packages_in_repositories
  1362. mysql_query_and_delete_unneeded_binary_packages() {
  1363. printf 'SELECT CONCAT("pool/",'
  1364. mysql_package_name_query
  1365. printf ') FROM `binary_packages`'
  1366. mysql_join_binary_packages_architectures
  1367. printf ' WHERE NOT EXISTS ('
  1368. printf 'SELECT 1'
  1369. printf ' FROM `binary_packages_in_repositories`'
  1370. printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  1371. printf ');\n'
  1372. printf 'DELETE `binary_packages`'
  1373. printf ' FROM `binary_packages`'
  1374. printf ' WHERE NOT EXISTS ('
  1375. printf 'SELECT 1'
  1376. printf ' FROM `binary_packages_in_repositories`'
  1377. printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  1378. printf ');\n'
  1379. }
  1380. # mysql_sort_versions [-f]
  1381. # sort the table of versions, iff versions without order exist or -f was
  1382. # given
  1383. mysql_sort_versions() {
  1384. ( # new shell is intentional
  1385. tmp_file=$(mktemp 'tmp.mysql_sort_versions.XXXXXXXXXX' --tmpdir)
  1386. trap 'rm "${tmp_file}"' EXIT
  1387. if [ "x$1" != 'x-f' ] && \
  1388. {
  1389. printf 'SELECT'
  1390. printf ' COUNT(1)'
  1391. printf ' FROM `versions`'
  1392. printf ' WHERE `versions`.`order` IS NULL'
  1393. } | \
  1394. mysql_run_query | \
  1395. grep -qxF '0'; then
  1396. # nothing to do
  1397. exit 0
  1398. fi
  1399. {
  1400. printf 'SELECT '
  1401. printf '`versions`.`%s`,' \
  1402. 'id' \
  1403. 'epoch' \
  1404. 'version' | \
  1405. sed 's/,$//'
  1406. printf ' FROM `versions`;\n'
  1407. } | \
  1408. mysql_run_query | \
  1409. expand_version 3 | \
  1410. sort -k2n,2 -k3V,3 | \
  1411. cut -f1 | \
  1412. cat -n | \
  1413. awk '{print $1 " " $2}' | \
  1414. tr ' ' '\t' > \
  1415. "${tmp_file}"
  1416. max_order=$(
  1417. {
  1418. printf 'SELECT'
  1419. printf ' GREATEST(%s,' \
  1420. "$(
  1421. wc -l < "${tmp_file}"
  1422. )"
  1423. printf 'MAX(`versions`.`order`))'
  1424. printf ' FROM `versions`;\n'
  1425. } | \
  1426. mysql_run_query
  1427. )
  1428. {
  1429. printf 'CREATE TEMPORARY TABLE `vs`('
  1430. printf '`id` BIGINT,'
  1431. printf '`ord` BIGINT,'
  1432. printf 'UNIQUE KEY(`id`),'
  1433. printf 'UNIQUE KEY(`ord`)'
  1434. printf ');\n'
  1435. printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `vs`(`ord`,`id`);\n' \
  1436. "${tmp_file}"
  1437. printf 'UPDATE `versions`'
  1438. printf ' SET `versions`.`order`=`versions`.`order`+1+%s;\n' \
  1439. "${max_order}"
  1440. printf 'COMMIT;\n'
  1441. printf 'UPDATE `versions`'
  1442. printf ' JOIN `vs`'
  1443. printf ' ON `vs`.`id`=`versions`.`id`'
  1444. printf ' SET `versions`.`order`=`vs`.`ord`;\n'
  1445. } | \
  1446. mysql_run_query
  1447. )
  1448. }
  1449. # mysql_load_min_and_max_versions
  1450. # load the minimal and maximal versions into min_version and max_version
  1451. mysql_load_min_and_max_versions() {
  1452. if [ -z "${min_version}" ]; then
  1453. min_version=$(
  1454. {
  1455. printf 'SELECT CONCAT(`versions`.`epoch`,":",`versions`.`version`)'
  1456. printf ' FROM `versions`'
  1457. printf ' WHERE `versions`.`order` IS NOT NULL'
  1458. printf ' ORDER BY `versions`.`order`'
  1459. printf ' LIMIT 1'
  1460. } | \
  1461. mysql_run_query
  1462. )
  1463. fi
  1464. if [ -z "${max_version}" ]; then
  1465. max_version=$(
  1466. {
  1467. printf 'SELECT CONCAT(`versions`.`epoch`,":",`versions`.`version`)'
  1468. printf ' FROM `versions`'
  1469. printf ' WHERE `versions`.`order` IS NOT NULL'
  1470. printf ' ORDER BY `versions`.`order` DESC'
  1471. printf ' LIMIT 1'
  1472. } | \
  1473. mysql_run_query
  1474. )
  1475. fi
  1476. if [ -z "${min_version_id}" ]; then
  1477. min_version_id=$(
  1478. {
  1479. printf 'SELECT `versions`.`id`'
  1480. printf ' FROM `versions`'
  1481. printf ' WHERE `versions`.`order` IS NOT NULL'
  1482. printf ' ORDER BY `versions`.`order`'
  1483. printf ' LIMIT 1'
  1484. } | \
  1485. mysql_run_query
  1486. )
  1487. fi
  1488. if [ -z "${max_version_id}" ]; then
  1489. max_version_id=$(
  1490. {
  1491. printf 'SELECT `versions`.`id`'
  1492. printf ' FROM `versions`'
  1493. printf ' WHERE `versions`.`order` IS NOT NULL'
  1494. printf ' ORDER BY `versions`.`order` DESC'
  1495. printf ' LIMIT 1'
  1496. } | \
  1497. mysql_run_query
  1498. )
  1499. fi
  1500. }
  1501. # mysql_query_remove_old_binary_packages_from_build_list
  1502. # print a query to remove old versions of packages from the build-list
  1503. mysql_query_remove_old_binary_packages_from_build_list() {
  1504. printf 'DELETE `d_bpir`'
  1505. printf ' FROM `binary_packages_in_repositories` AS `d_bpir`'
  1506. mysql_join_binary_packages_in_repositories_binary_packages 'd_bpir' 'd_bp'
  1507. printf ' JOIN `versions` as `d_v`'
  1508. printf ' ON `d_bp`.`epoch`=`d_v`.`epoch`'
  1509. printf ' AND `d_bp`.`pkgver`=`d_v`.`version`'
  1510. printf ' JOIN `binary_packages` AS `n_bp`'
  1511. printf ' ON `d_bp`.`pkgname`=`n_bp`.`pkgname`'
  1512. printf ' JOIN `versions` as `n_v`'
  1513. printf ' ON `n_bp`.`epoch`=`n_v`.`epoch`'
  1514. printf ' AND `n_bp`.`pkgver`=`n_v`.`version`'
  1515. mysql_join_binary_packages_binary_packages_in_repositories 'n_bp' 'n_bpir'
  1516. printf ' WHERE `d_bpir`.`repository`=%s' \
  1517. "${repository_ids__any_build_list}"
  1518. printf ' AND `n_bpir`.`repository`=%s' \
  1519. "${repository_ids__any_build_list}"
  1520. printf ' AND ('
  1521. printf '`n_v`.`order`>`d_v`.`order`'
  1522. printf ' OR ('
  1523. printf '`n_v`.`order`=`d_v`.`order`'
  1524. printf ' AND ('
  1525. printf '`n_bp`.`pkgrel`>`d_bp`.`pkgrel`'
  1526. printf ' OR ('
  1527. printf '`n_bp`.`pkgrel`=`d_bp`.`pkgrel`'
  1528. printf ' AND `n_bp`.`sub_pkgrel`>`d_bp`.`sub_pkgrel`'
  1529. printf ')'
  1530. printf ')'
  1531. printf ')'
  1532. printf ');\n'
  1533. printf 'COMMIT;\n'
  1534. mysql_query_and_delete_unneeded_binary_packages | \
  1535. grep -v '^SELECT '
  1536. }
  1537. # mysql_query_ordering_correct dependency.version.order install_target_provider.version.order dependency.version_relation
  1538. # print a query yielding wether the ordering is correct
  1539. mysql_query_ordering_correct() {
  1540. local relation
  1541. printf '('
  1542. for relation in '<' '<=' '>' '>=' '='; do
  1543. printf '('
  1544. printf '%s="%s"' \
  1545. "${3}" \
  1546. "${relation}"
  1547. printf ' AND %s%s%s' \
  1548. "${2}" \
  1549. "${relation}" \
  1550. "${1}"
  1551. printf ') OR '
  1552. done | \
  1553. sed '
  1554. s/ OR $//
  1555. '
  1556. printf ')'
  1557. }
  1558. # mysql_determine_majority_build_slave_architecture_id
  1559. # retreive the `architectures`.`id` of the majority of the currently
  1560. # active build slaves
  1561. mysql_determine_majority_build_slave_architecture_id() {
  1562. {
  1563. printf 'SELECT'
  1564. printf ' COUNT(DISTINCT `ssh_log`.`id`) AS `count`,'
  1565. printf '`architectures`.`id`'
  1566. printf ' FROM `ssh_log`'
  1567. printf ' JOIN `architectures`'
  1568. printf ' ON `ssh_log`.`parameters`=CONCAT(`architectures`.`name`," ")'
  1569. printf ' WHERE `action`="get-assignment"'
  1570. printf ' AND `ssh_log`.`date`>ADDTIME(NOW(),"-1 00:00:00")'
  1571. printf ' GROUP BY `ssh_log`.`parameters`'
  1572. printf ' ORDER BY `count` DESC'
  1573. printf ' LIMIT 1'
  1574. } | \
  1575. mysql_run_query | \
  1576. cut -f2
  1577. }