BuildMonitorDB.pm

   1 #!/usr/bin/perl
   2 package BuildMonitorDB;
   3
   4 use strict; use warnings;
   5 use DBI;
   6 use Data::Dumper;
   7 use BSUtil;
   8 use BSConfig;
   9 use BSUtil;
  10 use File::Copy;
  11 use POSIX qw(strftime);
  12 use Time::HiRes qw( clock_gettime CLOCK_REALTIME);
  13 use URI::Escape;
  14 use BSRPC;
  15 use MIME::Base64;
  16 use JSON::XS;
  17
  18 my $bsdir = $BSConfig::bsdir || "/srv/obs";
  19 my $reporoot = "$bsdir/build";
  20 my $rundir = "$bsdir/run";
  21 my $extrepodir = "$bsdir/repos";
  22 my $g_dbh = undef;
  23
  24 # DB statement handle objects.
  25 my $project_build_check_sth;
  26 my $project_disable_last_flag_sth;
  27 my $check_build_target_failed_sth;
  28 my $update_build_target_succeeded_sth;
  29 my $project_build_start_sth;
  30 my $get_build_target_id_sth;
  31 my $project_build_finished_sth;
  32 my $compensate_project_build_status_sth;
  33 my $project_build_finished_if_not_started_check_sth;
  34 my $project_build_finished_if_not_started_sth;
  35 my $package_build_start_sth;
  36 my $package_build_finished_sth;
  37 my $search_not_succeeded_packages_sth;
  38 my $build_status_sth;
  39 my $build_status_exists_sth;
  40 my $check_prior_to_build_success_sth;
  41 my $update_build_start_time_sth;
  42 my $package_disable_last_flag_sth;
  43 my $insert_package_build_if_not_exist_sth;
  44 my $package_build_status_sth;
  45 my $package_build_broken_status_sth;
  46 my $package_build_broken_status_end_time_only_sth;
  47 my $check_package_build_sth;
  48 my $get_build_project_id_sth;
  49 my $get_build_project_id_R_sth;
  50 my $insert_build_project_sth;
  51 my $complete_build_project_sth;
  52 my $get_build_project_status_sth;
  53 my $get_project_id_sth;
  54 my $insert_project_sth;
  55 my $update_project_sth;
  56 my $delete_project_info_sth;
  57 my $get_package_id_sth;
  58 my $insert_package_info_sth;
  59 my $update_package_info_sth;
  60 my $delete_package_info_sth;
  61 my $update_latest_sr_sth;
  62 my $delete_packages_info_sth;
  63
  64 #declare global variable.
  65 my %build_target_id_cache;
  66 my %project_package_id_cache;
  67
  68 #-------------------------------------------------------------------------------
  69 # local functions
  70 #-------------------------------------------------------------------------------
  71 sub get_cur_time {
  72   my $real_time = clock_gettime(CLOCK_REALTIME);
  73
  74   return $real_time;
  75 }
  76
  77 sub is_main_project {
  78   my ($proj_name) = @_;
  79
  80   my @main_projects = (
  81     "Tizen:Base",
  82     "Tizen:Mobile",
  83     "Tizen:TV",
  84     "Tizen:Wearable",
  85     "Tizen:Common",
  86     "Tizen:IVI",
  87
  88     "Tizen:3.0:Base",
  89     "Tizen:3.0:Mobile",
  90     "Tizen:3.0:TV",
  91     "Tizen:3.0:Wearable",
  92     "Tizen:3.0:Common",
  93     "Tizen:3.0:IVI",
  94   );
  95
  96   for my $p (@main_projects) {
  97     if( $p eq $proj_name ) {
  98       return 1;
  99     }
 100   }
 101
 102   return 0;
 103 }
 104
 105 sub is_prerelease_project {
 106   my ($proj_name) = @_;
 107
 108   if( $proj_name =~ /home:prerelease/ ) {
 109     return 1;
 110   } elsif( $proj_name =~ /home:trbs/ ) {
 111     return 1;
 112   }
 113   return  0;
 114 }
 115
 116 sub pattern_match {
 117   my ($event_str, $config_str) = @_;
 118
 119   #print "pattern_match: event_str=[$event_str]\n";
 120   for my $p (@{$config_str}) {
 121     #print "  config_str=[$p]...\n";
 122     if ($event_str =~ /$p/) {
 123       return 1;
 124     }
 125   }
 126   return 0;
 127 }
 128
 129 sub check_proj_pattern {
 130   my ($proj_name) = @_;
 131
 132   if( pattern_match($proj_name, \@BSConfig::exclude_build_monitor_projects_pattern) ) {
 133     #print "[", __LINE__, "] BuildMonitor: Exclude project $proj_name\n";
 134     return 1;
 135   }
 136   if( !pattern_match($proj_name, \@BSConfig::include_build_monitor_projects_pattern) ) {
 137     #print "[", __LINE__, "] BuildMonitor: Not include project $proj_name\n";
 138     return 1;
 139   }
 140
 141   return 0;
 142 }
 143
 144 sub check_proj_pattern_for_relay {
 145   my ($proj_name) = @_;
 146
 147   if( pattern_match($proj_name, \@BSConfig::exclude_build_monitor_projects_pattern_for_relay) ) {
 148     #print "[", __LINE__, "] BuildMonitor: Exclude project $proj_name\n";
 149     return 1;
 150   }
 151   if( !pattern_match($proj_name, \@BSConfig::include_build_monitor_projects_pattern_for_relay) ) {
 152     #print "[", __LINE__, "] BuildMonitor: Not include project $proj_name\n";
 153     return 1;
 154   }
 155
 156   return 0;
 157 }
 158
 159 sub check_prior_to_build_success {
 160   my ($build_project_id, $build_target_id, $info_package_id, $package_build_time) = @_;
 161
 162   $check_prior_to_build_success_sth->execute($build_project_id, $build_target_id,
 163     $info_package_id, $package_build_time);
 164
 165   my $arr_ref = $check_prior_to_build_success_sth->fetchrow_arrayref;
 166   if( ! $arr_ref ) {
 167     # if not found, return 0.
 168     return 0;
 169   }
 170
 171   my $bpa_id = @$arr_ref[0];
 172
 173   print "[", __LINE__, "] returning bpa_id = $bpa_id\n";
 174
 175   # return bpa.id
 176   return $bpa_id;
 177 }
 178
 179 sub update_build_start_time {
 180   my ($bpa_id, $package_build_time) = @_;
 181
 182   $update_build_start_time_sth->execute($package_build_time, $bpa_id);
 183 }
 184
 185 #-------------------------------------------------------------------------------
 186 # event handlers
 187 #-------------------------------------------------------------------------------
 188 sub srcsrv_start {
 189   my ($paramRef) = @_;
 190
 191   # update all projects and packages info.
 192   print "[", __LINE__, "] SRCSRV_START!\n";
 193
 194   my $start_time = get_cur_time();
 195   initialize_projpack_info($paramRef->{'projpack'});
 196   my $elapsed_time = get_cur_time() - $start_time;
 197   print "[",__LINE__,"] took $elapsed_time seconds.\n";
 198
 199   return 1;
 200 }
 201
 202 sub srcsrv_create_project {
 203   my ($paramRef) = @_;
 204
 205   my $proj_name = $paramRef->{'project'};
 206   my $description = $paramRef->{'description'};
 207   my $link_proj = $paramRef->{'link_proj'};
 208
 209   if( check_proj_pattern($proj_name) ) {
 210     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 211     return 0;
 212   }
 213
 214   $description = substr($description, 0, 3000);
 215   print "[", __LINE__, "] create a project ($proj_name, $description)\n";
 216   insert_or_update_project_info($proj_name, $description, $link_proj);
 217
 218   return 1;
 219 }
 220
 221 sub srcsrv_update_project {
 222   my ($paramRef) = @_;
 223
 224   my $proj_name = $paramRef->{'project'};
 225   my $description = $paramRef->{'description'};
 226   my $link_proj = $paramRef->{'link_proj'};
 227
 228   if( check_proj_pattern($proj_name) ) {
 229     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 230     return 0;
 231   }
 232
 233   $description = substr($description, 0, 3000);
 234   print "[", __LINE__, "] update a project ($proj_name, $description)\n";
 235   insert_or_update_project_info($proj_name, $description, $link_proj);
 236
 237   return 1;
 238 }
 239
 240 sub srcsrv_delete_project {
 241   my ($paramRef) = @_;
 242
 243   my $proj_name = $paramRef->{'project'};
 244
 245   if( check_proj_pattern($proj_name) ) {
 246     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 247     return 0;
 248   }
 249
 250   print "[", __LINE__, "] delete a project ($proj_name )\n";
 251   delete_project_info($proj_name);
 252
 253   return 1;
 254 }
 255
 256 sub srcsrv_create_package {
 257   my ($paramRef) = @_;
 258
 259   my $proj_name = $paramRef->{'project'};
 260   my $pkg_name  = $paramRef->{'package'};
 261   my $description = $paramRef->{'description'};
 262
 263   if( check_proj_pattern($proj_name) ) {
 264     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 265     return 0;
 266   }
 267
 268   $description = substr($description, 0, 3000);
 269   print "[", __LINE__, "] create a package ($proj_name, $pkg_name, $description)\n";
 270   insert_or_update_package_info($proj_name, $pkg_name, $description);
 271
 272   return 1;
 273 }
 274
 275 sub srcsrv_update_package {
 276   my ($paramRef) = @_;
 277
 278   my $proj_name = $paramRef->{'project'};
 279   my $pkg_name  = $paramRef->{'package'};
 280   my $description = $paramRef->{'description'};
 281
 282   if( check_proj_pattern($proj_name) ) {
 283     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 284     return 0;
 285   }
 286
 287   $description = substr($description, 0, 3000);
 288   print "[", __LINE__, "] update a package ($proj_name, $pkg_name, $description)\n";
 289   insert_or_update_package_info($proj_name, $pkg_name, $description);
 290
 291   return 1;
 292 }
 293
 294 sub srcsrv_delete_package {
 295   my ($paramRef) = @_;
 296
 297   my $proj_name = $paramRef->{'project'};
 298   my $pkg_name  = $paramRef->{'package'};
 299
 300   if( check_proj_pattern($proj_name) ) {
 301     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 302     return 0;
 303   }
 304
 305   print "[", __LINE__, "] delete a package ($proj_name, $pkg_name)\n";
 306   delete_package_info($proj_name, $pkg_name);
 307
 308   return 1;
 309 }
 310
 311 sub srcsrv_request_accepted {
 312   my ($paramRef) = @_;
 313
 314   my $target_proj = $paramRef->{'targetproject'};
 315   my $source_proj = $paramRef->{'sourceproject'};
 316
 317   if( check_proj_pattern($target_proj) ) {
 318     print "[", __LINE__, "] Skip DB logging $target_proj.\n";
 319     return 0;
 320   }
 321
 322   print "[", __LINE__, "] request accepted src=$source_proj, target=$target_proj\n";
 323   insert_build_project_table($target_proj);
 324
 325   return 1;
 326 }
 327
 328 sub srcsrv_request_statechange {
 329   my ($paramRef) = @_;
 330
 331   if( $paramRef->{'state'} == 'accepted' ) {
 332     print "[", __LINE__, "] paramRef = ";
 333     print Dumper($paramRef);
 334     return srcsrv_request_accepted($paramRef);
 335   }
 336
 337   return 0;
 338 }
 339
 340 sub srcsrv_commit {
 341   my ($paramRef) = @_;
 342
 343   #my $proj_name = $paramRef->{'project'};
 344   #my $pack = $paramRef->{'pack'};
 345
 346   #my $build_project_id = get_build_project_id("test", $proj_name);
 347 #
 348   #my $param = {
 349     #'uri' => "$BSConfig::srcserver/source/$proj_name/_meta",
 350   #};
 351   #my $proj = BSRPC::rpc($param, $BSXML::proj);
 352   #my $repos = $proj->{'repository'};
 353   #for my $repo (@$repos) {
 354     #my $archs = $repo->{'arch'};
 355     #for my $arch (@$archs) {
 356       ##print "SRCSRV_COMMIT: $repo->{'name'}/$arch\n";
 357       #my $repo_name = $repo->{'name'};
 358       #print "[",__LINE__, "] SRCSRV_COMMIT. trigger make_dep_graph for $proj_name/$repo_name/$arch, $build_project_id\n";
 359       #trigger_make_dep_graph($build_project_id, $proj_name, $repo_name, $arch);
 360     #}
 361   #}
 362
 363   return 0;
 364 }
 365
 366 sub build_start {
 367   my ($paramRef) = @_;
 368
 369   my $proj_name = $paramRef->{'project'};
 370   my $repo = $paramRef->{'repository'};
 371   my $arch = $paramRef->{'arch'};
 372   my $pkg_name = $paramRef->{'package'};
 373   my $reason = $paramRef->{'reason'};
 374   my $time = $paramRef->{'time'};
 375   my $build_log_url = decode_base64($paramRef->{'build_log_url'});
 376
 377   my $config = "";
 378
 379   if( check_proj_pattern($proj_name) ) {
 380     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 381     return 0;
 382   }
 383
 384   package_build_start($config, $proj_name, $repo, $arch, $pkg_name, $time, $reason, $build_log_url);
 385
 386   return 1;
 387 }
 388
 389 sub build_unchanged {
 390   my ($paramRef) = @_;
 391
 392   my $proj_name = $paramRef->{'project'};
 393   my $repo = $paramRef->{'repository'};
 394   my $arch = $paramRef->{'arch'};
 395   my $pkg_name = $paramRef->{'package'};
 396   my $pre_install_time = $paramRef->{'pre_install_time'};
 397   my $install_time = $paramRef->{'install_time'};
 398   my $main_build_time = $paramRef->{'main_build_time'};
 399   my $time = $paramRef->{'time'};
 400
 401   my $status = "unchanged";
 402   my $config = "";
 403
 404   if( check_proj_pattern($proj_name) ) {
 405     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 406     return 0;
 407   }
 408
 409   package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
 410       $pre_install_time, $install_time, $main_build_time);
 411
 412   return 1;
 413 }
 414
 415 sub build_success {
 416   my ($paramRef) = @_;
 417
 418   my $proj_name = $paramRef->{'project'};
 419   my $repo = $paramRef->{'repository'};
 420   my $arch = $paramRef->{'arch'};
 421   my $pkg_name = $paramRef->{'package'};
 422   my $pre_install_time = $paramRef->{'pre_install_time'};
 423   my $install_time = $paramRef->{'install_time'};
 424   my $main_build_time = $paramRef->{'main_build_time'};
 425   my $time = $paramRef->{'time'};
 426
 427   my $status = "succeeded";
 428   my $config = "";
 429
 430   if( check_proj_pattern($proj_name) ) {
 431     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 432     return 0;
 433   }
 434
 435   package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
 436       $pre_install_time, $install_time, $main_build_time);
 437
 438   return 1;
 439 }
 440
 441 sub build_kill {
 442   my ($paramRef) = @_;
 443
 444   my $proj_name = $paramRef->{'project'};
 445   my $repo = $paramRef->{'repository'};
 446   my $arch = $paramRef->{'arch'};
 447   my $pkg_name = $paramRef->{'package'};
 448   my $pre_install_time = 0;
 449   my $install_time = 0;
 450   my $main_build_time = 0;
 451   my $time = $paramRef->{'time'};
 452
 453   my $status = "killed";
 454   my $config = "";
 455
 456   if( check_proj_pattern($proj_name) ) {
 457     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 458     return 0;
 459   }
 460
 461   package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
 462       $pre_install_time, $install_time, $main_build_time);
 463
 464   return 1;
 465 }
 466
 467 sub build_fail {
 468   my ($paramRef) = @_;
 469
 470   my $proj_name = $paramRef->{'project'};
 471   my $repo = $paramRef->{'repository'};
 472   my $arch = $paramRef->{'arch'};
 473   my $pkg_name = $paramRef->{'package'};
 474   my $pre_install_time = $paramRef->{'pre_install_time'};
 475   my $install_time = $paramRef->{'install_time'};
 476   my $main_build_time = $paramRef->{'main_build_time'};
 477   my $time = $paramRef->{'time'};
 478
 479   my $status = "failed";
 480   my $config = "";
 481
 482   if( check_proj_pattern($proj_name) ) {
 483     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 484     return 0;
 485   }
 486
 487   package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
 488       $pre_install_time, $install_time, $main_build_time);
 489
 490   return 1;
 491 }
 492
 493 sub build_broken {
 494   my ($paramRef) = @_;
 495
 496   my $proj_name = $paramRef->{'project'};
 497   my $repo = $paramRef->{'repository'};
 498   my $arch = $paramRef->{'arch'};
 499   my $pkg_name = $paramRef->{'package'};
 500   my $detail = $paramRef->{'detail'};
 501   my $time = $paramRef->{'time'};
 502
 503   my $status = "broken";
 504   my $config = "";
 505
 506   if( check_proj_pattern($proj_name) ) {
 507     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 508     return 0;
 509   }
 510
 511   package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);
 512
 513   return 1;
 514 }
 515
 516 sub build_unresolvable {
 517   my ($paramRef) = @_;
 518
 519   my $proj_name = $paramRef->{'project'};
 520   my $repo = $paramRef->{'repository'};
 521   my $arch = $paramRef->{'arch'};
 522   my $pkg_name = $paramRef->{'package'};
 523   my $detail = $paramRef->{'detail'};
 524   my $time = $paramRef->{'time'};
 525
 526   my $status = "unresolvable";
 527   my $config = "";
 528
 529   if( check_proj_pattern($proj_name) ) {
 530     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 531     return 0;
 532   }
 533
 534   package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);
 535
 536   return 1;
 537 }
 538
 539 sub build_disabled {
 540   my ($paramRef) = @_;
 541
 542   my $proj_name = $paramRef->{'project'};
 543   my $repo = $paramRef->{'repository'};
 544   my $arch = $paramRef->{'arch'};
 545   my $pkg_name = $paramRef->{'package'};
 546   my $detail = $paramRef->{'detail'};
 547   my $time = $paramRef->{'time'};
 548
 549   my $status = "disabled";
 550   my $config = "";
 551
 552   if( check_proj_pattern($proj_name) ) {
 553     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 554     return 0;
 555   }
 556
 557   print "[", __LINE__, "] BUILD_DISABLED$proj_name, $repo, $arch, $pkg_name\n";
 558   package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);
 559
 560   return 1;
 561 }
 562
 563 sub build_excluded {
 564   my ($paramRef) = @_;
 565
 566   my $proj_name = $paramRef->{'project'};
 567   my $repo = $paramRef->{'repository'};
 568   my $arch = $paramRef->{'arch'};
 569   my $pkg_name = $paramRef->{'package'};
 570   my $detail = $paramRef->{'detail'};
 571   my $time = $paramRef->{'time'};
 572
 573   my $status = "excluded";
 574   my $config = "";
 575
 576   if( check_proj_pattern($proj_name) ) {
 577     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 578     return 0;
 579   }
 580
 581   print "[", __LINE__, "] BUILD_EXCLUDED $proj_name, $repo, $arch, $pkg_name\n";
 582   package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);
 583
 584   update_project_build_status_if_failed($proj_name, $repo, $arch);
 585
 586   return 1;
 587 }
 588
 589 sub repo_publish_state {
 590   my ($paramRef) = @_;
 591
 592   my $proj_name = $paramRef->{'project'};
 593   my $repo = $paramRef->{'repo'};
 594   my $state = $paramRef->{'state'};
 595   my $arch = $paramRef->{'arch'};
 596   my $time = $paramRef->{'time'};
 597
 598   my $config = "";
 599
 600   if( check_proj_pattern($proj_name) ) {
 601     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 602     return 0;
 603   }
 604
 605   if( $state eq "published") {
 606     #if published, the project build is finished.
 607     project_build_finished($config, $proj_name, $repo, $arch, $time);
 608
 609     return 1;
 610   }
 611   return 0;
 612 }
 613
 614 sub repo_published {
 615   my ($paramRef) = @_;
 616
 617   my $proj_name = $paramRef->{'project'};
 618
 619   if( check_proj_pattern($proj_name) ) {
 620     print "[", __LINE__, "] Skip DB logging $proj_name.\n";
 621     return 0;
 622   }
 623
 624   build_project_completed($proj_name);
 625
 626   return 1;
 627 }
 628
 629 #-------------------------------------------------------------------------------
 630 sub trigger_make_dep_graph {
 631   my ($build_project_id, $proj_name, $repo, $arch) = @_;
 632
 633   # trigger making build_progress graph.
 634   my $jenkinsuri = "$BSConfig::jenkinsserver/job/make_dep_graph/buildWithParameters";
 635   my $param = {
 636     'request' => 'POST',
 637     'uri' => $jenkinsuri,
 638     'timeout' => 60,
 639     'maxredirects' => 1,
 640   };
 641   my $args = {
 642     'action' => 'build_progress',
 643     'build_project_id'=>$build_project_id,
 644     'obs_project' => $proj_name,
 645     'repo' => $repo,
 646     'arch' => $arch,
 647   };
 648   my @para = ("TRIGGER_INFO=".encode_base64(encode_json($args), ''));
 649   print "notify: trigger Jenkins uri=[$jenkinsuri] para=[@para]\n";
 650   eval {
 651     BSRPC::rpc( $param, undef, @para );
 652   };
 653   warn("Jenkins: $@") if $@;
 654 }
 655
 656 sub trigger_obs_published {
 657   my ($proj_name) = @_;
 658
 659   # trigger OBS_REPO_PUBLISHED.
 660   my $jenkinsuri = "$BSConfig::jenkinsserver/job/obs-event-dispatcher/buildWithParameters";
 661   my $param = {
 662     'request' => 'POST',
 663     'uri' => $jenkinsuri,
 664     'timeout' => 60,
 665     'maxredirects' => 1,
 666   };
 667   my $args = {
 668     'repo' => 'manually triggered by BuildMonitorDB',
 669     'project' => $proj_name,
 670     'event_type' => 'OBS_REPO_PUBLISHED'
 671   };
 672   my @para = ("project=$proj_name",
 673               "event_type=OBS_REPO_PUBLISHED",
 674               "para=".encode_base64(encode_json($args), ''));
 675   print "notify: trigger Jenkins uri=[$jenkinsuri] para=[@para]\n";
 676   eval {
 677     BSRPC::rpc( $param, undef, @para );
 678   };
 679   warn("Jenkins: $@") if $@;
 680 }
 681
 682 sub parse_reason {
 683   my ($tt) = @_;
 684
 685   my $explain="";
 686   my $detail="";
 687
 688   return $tt if ref($tt) ne 'HASH';
 689
 690   #print Dumper($tt);
 691   while( my ($key, $value) = each $tt ) {
 692
 693     if( $key eq 'explain' ) {
 694       $explain = $value;
 695     } elsif( $key eq 'packagechange' ) {
 696       $detail = $detail . parse_packagechange($value);
 697     } elsif( $key eq 'oldsource' ) {
 698     } else {
 699       print "undefined key-->\n";
 700       print Dumper($key);
 701       print Dumper($value);
 702     }
 703   }
 704
 705   return substr("$explain($detail)", 0, 100);
 706 }
 707
 708 sub parse_packagechange {
 709   my ($packages) = @_;
 710
 711   my $detail_str = "";
 712   my $str_prev = undef;
 713
 714   foreach my $hash_ref (@{$packages}) {
 715     if ($str_prev) {
 716       $detail_str = $detail_str . ",";
 717     }
 718     $str_prev="$hash_ref->{'key'}($hash_ref->{'change'})";
 719     $detail_str = $detail_str .  $str_prev;
 720   }
 721
 722   return $detail_str;
 723 }
 724
 725 #-------------------------------------------------------------------------------
 726 sub get_project_id {
 727   my ($project_name) = @_;
 728
 729   if( defined $project_package_id_cache{$project_name}{'id'} ) {
 730     return $project_package_id_cache{$project_name}{'id'};
 731   }
 732
 733   $get_project_id_sth->execute($project_name);
 734
 735   my $arr_ref = $get_project_id_sth->fetchrow_arrayref;
 736   if( !$arr_ref ) {
 737     return undef;
 738   }
 739
 740   #print Dumper($arr_ref);
 741   my $project_id=@$arr_ref[0];
 742
 743   $project_package_id_cache{$project_name}{'id'} = $project_id;
 744
 745   return $project_id;
 746 }
 747
 748 #-------------------------------------------------------------------------------
 749 sub get_package_id {
 750   my ($project_name, $package_name) = @_;
 751
 752   if( defined $project_package_id_cache{$project_name}{$package_name} ) {
 753     return $project_package_id_cache{$project_name}{$package_name};
 754   }
 755
 756   my $proj_id=get_project_id($project_name);
 757   if( ! $proj_id  ) {
 758     print "$project_name: No such project.\n";
 759     return undef;
 760   }
 761
 762   my $package_id;
 763   $get_package_id_sth->execute($proj_id, $package_name);
 764   my $arr_ref = $get_package_id_sth->fetchrow_arrayref;
 765   if( ! $arr_ref ) {
 766     $package_id = @$arr_ref[0];
 767     eval {
 768       $g_dbh->begin_work();
 769       $get_package_id_sth->execute($proj_id, $package_name);
 770       my $arr_ref = $get_package_id_sth->fetchrow_arrayref;
 771       if( ! $arr_ref ) {
 772 # if not found, create one.
 773         my $description = "";
 774         #print "[", __LINE__, "][jh0822][insert_package_info] $proj_id, $package_name, $description\n";
 775         $insert_package_info_sth->execute($proj_id, "$package_name", $description);
 776         $package_id = $insert_package_info_sth->{mysql_insertid};
 777         #print "newly created package_id($package_id) for $package_name.\n";
 778       } else {
 779         $package_id = @$arr_ref[0];
 780       }
 781       $g_dbh->commit();
 782     };
 783
 784     if($@) {
 785       print "rollback: $@\n";
 786       $g_dbh->rollback();
 787       $get_package_id_sth->execute($proj_id, $package_name);
 788       my $arr_ref = $get_package_id_sth->fetchrow_arrayref;
 789       $package_id = @$arr_ref[0];
 790     }
 791   } else {
 792     $package_id = @$arr_ref[0];
 793   }
 794
 795   $project_package_id_cache{$project_name}{$package_name} = $package_id;
 796
 797   #print "[", __LINE__, "][get_package_id] $package_name($package_id)\n";
 798   return $package_id;
 799 }
 800
 801 sub initialize_projpack_info {
 802   my ($projpack) = @_;
 803
 804   if( ref($projpack) ne 'HASH' ) {
 805     $projpack = decode_json(decode_base64($projpack));
 806   }
 807
 808   my $projects = $projpack->{'project'};
 809
 810   for my $proj (@$projects) {
 811     my $proj_name   = $proj->{'name'};
 812
 813     if( check_proj_pattern($proj_name) ) {
 814       next;
 815     }
 816
 817     my $description = "";
 818     $description = $proj->{'description'} if defined $proj->{'description'};
 819
 820     #print "[", __LINE__, "] insert project: $proj_name\n";
 821     insert_or_update_project_info($proj_name, $description, "");
 822
 823     my $packages    = $proj->{'package'};
 824     if( scalar(@$packages) > 0 ) {
 825       for my $pkg (@$packages) {
 826         my $pkg_name = $pkg->{'name'};
 827         #print "[", __LINE__, "] insert package: $proj_name, $pkg_name\n";
 828         insert_package_info($proj_name, $pkg_name, "");
 829       }
 830     }
 831   }
 832
 833 }
 834
 835 #-------------------------------------------------------------------------------
 836 sub insert_build_project_table {
 837   my ($proj_name) = @_;
 838
 839   my $proj_id=get_project_id($proj_name);
 840   my $failed = 1;
 841   my $ret = 0;
 842   while ($failed) {
 843     $failed = 0;
 844     eval {
 845       $g_dbh->begin_work();
 846
 847       $get_build_project_id_R_sth->execute($proj_id, "R");
 848       my $arr_ref = $get_build_project_id_R_sth->fetchrow_arrayref;
 849       if( ! $arr_ref ) {
 850         $insert_build_project_sth->execute($proj_id, "R");
 851         my $build_project_id = $insert_build_project_sth->{mysql_insertid};
 852         print "[", __LINE__, "] new build_project_id: $build_project_id for $proj_name\n";
 853       } else {
 854         print "[", __LINE__, "] already has an item ($proj_id, R) proj=$proj_name!\n";
 855         $ret = 1;
 856       }
 857
 858       $g_dbh->commit();
 859     };
 860
 861     if($@) {
 862       warn "[", __LINE__, "] Transaction aborted because $@\n";
 863       $g_dbh->rollback();
 864       $failed = 1;
 865       printf "[", __LINE__, "] waiting 10 seconds...\n";
 866       sleep(10);
 867     }
 868   }
 869
 870   return $ret;
 871 }
 872
 873 sub build_project_completed {
 874   my ($proj_name) = @_;
 875
 876   my $proj_id=get_project_id($proj_name);
 877   my $build_project_id = get_build_project_id("", $proj_name);
 878
 879   # check all packages built in this project.
 880   # if any last package build status is failed.
 881
 882   my $final_status = "C";
 883   my $failed_trans = 1;
 884   while($failed_trans) {
 885     $failed_trans = 0;
 886     eval {
 887       $g_dbh->begin_work();
 888       $build_status_sth->execute($build_project_id);
 889       my %build_status;
 890       while( my $arr_ref = $build_status_sth->fetchrow_arrayref ) {
 891         my $packid = @$arr_ref[0];
 892         my $status = @$arr_ref[1];
 893         my $repo = @$arr_ref[2];
 894         my $arch = @$arr_ref[3];
 895
 896         $build_status{$packid}{$repo}{$arch} = $status;
 897       }
 898
 899       my $previous_status = "";
 900       $get_build_project_status_sth->execute($build_project_id);
 901       while( my $arr_ref = $get_build_project_status_sth->fetchrow_arrayref ) {
 902         $previous_status = @$arr_ref[0];
 903       }
 904
 905       my $reason = "";
 906       for my $packid (keys %build_status) {
 907         for my $repo (keys $build_status{$packid} ) {
 908           for my $arch (keys $build_status{$packid}{$repo}) {
 909             if( $build_status{$packid}{$repo}{$arch} !~ /succeeded/i &&
 910                 $build_status{$packid}{$repo}{$arch} !~ /building/i &&
 911                 $build_status{$packid}{$repo}{$arch} !~ /excluded/i &&
 912                 $build_status{$packid}{$repo}{$arch} !~ /disabled/i ) {
 913               $final_status = "F";
 914               $reason = "$reason$packid($repo/$arch):$build_status{$packid}{$repo}{$arch}, ";
 915             }
 916           }
 917         }
 918       }
 919
 920       print "$proj_name: final_project_build_status=$final_status\n";
 921       print "  reason: $reason\n";
 922
 923       if( $final_status ne $previous_status ) {
 924         print "[",__LINE__, "] updating project_build_status $build_project_id, $final_status\n";
 925         $complete_build_project_sth->execute($final_status, $build_project_id);
 926       }
 927
 928       $g_dbh->commit();
 929     };
 930     if($@) {
 931       warn "[", __LINE__, "] Transaction aborted because $@\n";
 932       $g_dbh->rollback();
 933       $failed_trans = 1;
 934       printf "[", __LINE__, "] waiting 10 seconds...\n";
 935       sleep(10);
 936     }
 937   }
 938
 939   return $final_status;
 940 }
 941
 942 #-------------------------------------------------------------------------------
 943 sub insert_or_update_project_info {
 944   my ($project_name, $description, $link_proj) = @_;
 945
 946   my $pre_release_flag = 'N';
 947   my $active_flag = 'Y';
 948
 949   if( is_prerelease_project($project_name) ) {
 950     $pre_release_flag = 'Y';
 951   }
 952   my $ref_proj = "";
 953
 954   if( $link_proj =~ /ref:/ ) {
 955     $ref_proj = $link_proj;
 956   }
 957
 958   # Insert the project only if there is no project that has the same name.
 959   my $proj_id = get_project_id($project_name);
 960   if( $proj_id ) {
 961     #print "We have a project that has the same name: $project_name.\n";
 962     # if we have the same name, update description.
 963     $update_project_sth->execute($description, $pre_release_flag, $active_flag, $ref_proj, $project_name);
 964
 965     return;
 966   }
 967
 968   print "[", __LINE__, "][jh0822][insert_or_update_project_info] $project_name, $description, $pre_release_flag, $active_flag, $ref_proj\n";
 969   $insert_project_sth->execute($project_name, $description, $pre_release_flag, $active_flag, $ref_proj);
 970   $project_package_id_cache{$project_name}{'id'} = $insert_project_sth->{mysql_insertid};
 971
 972   if( is_prerelease_project($project_name) ) {
 973     insert_build_project_table($project_name);
 974   }
 975 }
 976
 977 #-------------------------------------------------------------------------------
 978 sub delete_project_info {
 979   my ($project_name) = @_;
 980
 981   my $proj_id=get_project_id($project_name);
 982   if( ! $proj_id ) {
 983     print "$project_name: No such project.\n";
 984
 985     return;
 986   }
 987
 988   $delete_packages_info_sth->execute($proj_id);
 989
 990   print "[", __LINE__, "][jh0822][delete_project_info] $project_name\n";
 991   $delete_project_info_sth->execute($project_name);
 992   delete $project_package_id_cache{$project_name};
 993 }
 994
 995 #-------------------------------------------------------------------------------
 996 sub delete_all_packages {
 997   my ($project_name) = @_;
 998
 999   my $proj_id=get_project_id($project_name);
1000   if( ! $proj_id ) {
1001     print "$project_name: No such project.\n";
1002
1003     return;
1004   }
1005
1006   print "[", __LINE__, "][jh0822][delete_all_packages] $project_name\n";
1007   $delete_packages_info_sth->execute($proj_id);
1008
1009 }
1010
1011 #-------------------------------------------------------------------------------
1012 sub insert_package_info {
1013   my ($project_name, $package_name, $description) = @_;
1014
1015   my $pkg_id = get_package_id($project_name, $package_name);
1016   if( $pkg_id ) {
1017     #print "$package_name: Already existing package name in $project_name.\n";
1018
1019     return;
1020   }
1021
1022   my $proj_id = get_project_id($project_name);
1023   if( ! $proj_id ) {
1024     print "$project_name: No such project name.\n";
1025
1026     return;
1027   }
1028
1029   $description = "" unless $description;
1030
1031   #print "[", __LINE__, "][jh0822][insert_package_info] $proj_id, $package_name, $description\n";
1032   $insert_package_info_sth->execute($proj_id, $package_name, $description);
1033   $project_package_id_cache{$project_name}{$package_name} = $insert_package_info_sth->{mysql_insertid};
1034 }
1035
1036 #-------------------------------------------------------------------------------
1037 sub insert_or_update_package_info {
1038   my ($project_name, $package_name, $description) = @_;
1039
1040   my $proj_id = get_project_id($project_name);
1041   if( ! $proj_id ) {
1042     print "$project_name: No such project name.\n";
1043
1044     return;
1045   }
1046
1047   if( ! defined $description ) { $description = ""; }
1048
1049   my $pkg_id = get_package_id($project_name, $package_name, $description);
1050   if( $pkg_id ) {
1051     #print "$package_name: Already existing package name in $project_name.\n";
1052     $update_package_info_sth->execute($description, $pkg_id);
1053
1054     return;
1055   }
1056
1057   #print "[", __LINE__, "][jh0822][insert_package_info] $proj_id, $package_name, $description\n";
1058   $insert_package_info_sth->execute($proj_id, "$package_name", "$description");
1059   $project_package_id_cache{$project_name}{$package_name} = $insert_package_info_sth->{mysql_insertid};
1060 }
1061
1062 #-------------------------------------------------------------------------------
1063 sub delete_package_info {
1064   my ($project_name, $package_name) = @_;
1065
1066   my $proj_id = get_project_id($project_name);
1067   if( ! $proj_id ) {
1068     print "$project_name: No such project name.\n";
1069
1070     return;
1071   }
1072
1073   my $pkg_id = get_package_id($project_name, $package_name);
1074   if( ! $pkg_id ) {
1075     print "$package_name: no such package in $project_name.\n";
1076
1077     return;
1078   }
1079
1080   $delete_package_info_sth->execute($pkg_id);
1081   delete $project_package_id_cache{$project_name}{$package_name};
1082 }
1083
1084 #-------------------------------------------------------------------------------
1085 sub update_latest_sr {
1086   my ($project_name, $package_name, $latest_sr_status_id) = @_;
1087
1088   my $proj_id = get_project_id($project_name);
1089   if( ! $proj_id ) {
1090     print "$project_name: No such project.\n";
1091     disconnect_db();
1092     return;
1093   }
1094
1095   my $pkg_id = get_package_id($project_name, $package_name);
1096   if( ! $pkg_id ) {
1097     print "$package_name: No such package in $project_name.\n";
1098     disconnect_db();
1099     return;
1100   }
1101
1102   print "[", __LINE__, "][jh0822][update_latest_sr] $latest_sr_status_id, $pkg_id\n";
1103   $update_latest_sr_sth->execute($latest_sr_status_id, $pkg_id);
1104
1105   disconnect_db();
1106 }
1107
1108 #-------------------------------------------------------------------------------
1109 sub project_build_start {
1110   my ($build_project_id, $proj_name, $repo, $arch, $state, $start_time) = @_;
1111
1112   if( $build_project_id <= 0 ) {
1113     print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
1114     return;
1115   }
1116
1117   if( defined $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} &&
1118       $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} eq 'building' ) {
1119     return;
1120   }
1121
1122   if( $state ne "Building" ) {
1123     print "[",__LINE__,"] state should be 'Building'!!!\n";
1124   }
1125
1126   $state = 'R';
1127
1128   $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} = 'building';
1129   # insert only if there is not a row that has start_time!=0 and end_time=0.
1130   my $build_target_id = 0;
1131   eval {
1132     $g_dbh->begin_work();
1133
1134     print "[", __LINE__, "][project_build_start] $build_project_id, $proj_name $repo, $arch, $state, $start_time\n";
1135     $project_build_check_sth->execute($build_project_id, $repo, $arch);
1136     my $arr_ref = $project_build_check_sth->fetchrow_arrayref;
1137     if ( ! $arr_ref ) {
1138
1139       # last_flag = 'N';
1140       $project_disable_last_flag_sth->execute($build_project_id, $repo, $arch);
1141
1142       # insert only if there is no statement that start_time=0.
1143       $project_build_start_sth->execute($build_project_id, $repo, $arch, $start_time, $state);
1144       $build_target_id = $project_build_start_sth->{mysql_insertid};
1145       $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} = $build_target_id;
1146       print "build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} = $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'}\n";
1147     } else {
1148       print Dumper($arr_ref);
1149       die "WARNING!! There should be only one build start for this build_project_id($build_project_id)!!\n";
1150     }
1151
1152     $g_dbh->commit();
1153   };
1154
1155   if($@) {
1156     warn "[", __LINE__, "] Transaction aborted because $@\n";
1157     $g_dbh->rollback();
1158     return ;
1159   }
1160
1161   if( is_main_project($proj_name) ) {
1162     print "[", __LINE__, "] trigger_make_dep_graph for main project $proj_name, $repo/$arch, $build_project_id\n";
1163     trigger_make_dep_graph($build_project_id, $proj_name, $repo, $arch);
1164   }
1165
1166 }
1167 #-------------------------------------------------------------------------------
1168 sub project_build_finished_if_not_started {
1169   my ($build_project_id, $proj, $repo, $arch, $start_time, $end_time) = @_;
1170
1171   if( $build_project_id <= 0 ) {
1172     print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
1173     return;
1174   }
1175
1176   my $build_target_id = get_build_target_id($build_project_id, $repo, $arch);
1177   my ($status, $status_detail) = get_buildmonitor_project_status($build_target_id, $proj, $repo, $arch);
1178   if (! $status_detail) { $status_detail=""; }
1179
1180   # if there is no row that has start_time!=0 and end_time=0,
1181   # This means the first package is broken or unresolvable.
1182   eval {
1183     $g_dbh->begin_work();
1184
1185     print "[", __LINE__, "][project_build_finished_if_not_started] $build_project_id, $repo, $arch, $status, $status_detail\n";
1186     $project_build_finished_if_not_started_check_sth->execute($build_project_id, $repo, $arch);
1187     my $arr_ref = $project_build_finished_if_not_started_check_sth->fetchrow_arrayref;
1188     if( ! $arr_ref ) {
1189
1190       # last_flag = 'N';
1191       $project_disable_last_flag_sth->execute($build_project_id, $repo, $arch);
1192
1193       print "[", __LINE__, "][project_build_finished_if_not_started] $build_project_id, $repo, $arch, $status, $status_detail\n";
1194       $project_build_finished_if_not_started_sth->execute($build_project_id, $repo, $arch, $start_time, $end_time, $status, $status_detail);
1195     }
1196
1197     $g_dbh->commit();
1198   };
1199
1200   $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} = 'done';
1201
1202   if($@) {
1203     warn "[", __LINE__, "] Transaction aborted because $@\n";
1204     $g_dbh->rollback();
1205   }
1206 }
1207
1208 #-------------------------------------------------------------------------------
1209 sub project_build_finished {
1210   my ($config, $projid, $repo, $arch, $time, $status, $detail) = @_;
1211
1212   return unless $g_dbh;
1213
1214   my $start_time = get_cur_time();
1215   my $build_project_id = get_build_project_id($config, $projid);
1216
1217   if( $build_project_id <= 0 ) {
1218     #print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
1219     return;
1220   }
1221
1222   if( ! defined $status ) {
1223     my $build_target_id = get_build_target_id($build_project_id, $repo, $arch);
1224     ($status, $detail) = get_buildmonitor_project_status($build_target_id, $projid, $repo, $arch);
1225   }
1226
1227   # if status='S' and end_time is not zero, do not update.
1228   # check out $project_build_finished_sql if you want to see how to do this.
1229
1230   $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} = 'done';
1231
1232   print "[", __LINE__, "][project_build_finished] $build_project_id, $projid, $repo, $arch, $status, $detail\n";
1233   $project_build_finished_sth->execute($time, $status, $detail, $build_project_id, "$repo", "$arch");
1234   my $elapsed_time = get_cur_time() - $start_time;
1235   print "[",__LINE__,"] took $elapsed_time seconds.\n";
1236 }
1237
1238 sub cancel_package_build {
1239   my ($config, $prp, $arch, $package_name) = @_;
1240
1241   return unless $g_dbh;
1242   my $start_time = get_cur_time();
1243   package_build_finished($config, $prp, $arch, $package_name, time(), "cancelled");
1244   my $elapsed_time = get_cur_time() - $start_time;
1245   print "[",__LINE__,"] took $elapsed_time seconds.\n";
1246 }
1247
1248 sub is_package_in_project {
1249   my ($package_name, $proj_name) = @_;
1250
1251   my $found = 0;
1252   my $param = {
1253     'uri' => "$BSConfig::srcserver/source/$proj_name/",
1254   };
1255   eval {
1256     my $proj = BSRPC::rpc($param, $BSXML::dir);
1257     my $entries = $proj->{'entry'};
1258
1259     for my $e (@$entries) {
1260       if( $e->{'name'} eq $package_name ) {
1261         $found = 1;
1262         last;
1263       }
1264     }
1265   };
1266   warn("BuildMonitor: $@") if $@;
1267
1268   return $found;
1269 }
1270
1271 #-------------------------------------------------------------------------------
1272 sub package_build_start {
1273   my ($config, $proj_name, $repo, $arch, $package_name, $time, $reason, $build_log_url) = @_;
1274
1275   return unless $g_dbh;
1276
1277   my $start_time = get_cur_time();
1278
1279   my $build_project_id = get_build_project_id($config, $proj_name);
1280   my $info_package_id = get_package_id($proj_name, $package_name);
1281
1282   if( $build_project_id <= 0 ) {
1283     print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
1284     print "[", __LINE__, "][package_build_start($package_name)] $build_project_id, $repo, $arch, $info_package_id\n";
1285     return;
1286   }
1287
1288   $build_log_url = "" if !defined $build_log_url;
1289
1290   if ( !defined $reason ) {
1291     $reason = "";
1292   } else {
1293     $reason=parse_reason($reason);
1294   }
1295   $reason = substr($reason, 0, 1000);
1296
1297   my $state = "Building";
1298   my $project_build_time = $time;
1299   my $package_build_time = $project_build_time;
1300
1301   # start the project build if this is the first building of a package in this project.
1302   project_build_start($build_project_id, $proj_name, $repo, $arch, $state, $project_build_time);
1303   my $build_target_id=get_build_target_id($build_project_id, $repo, $arch);
1304
1305   # At first, we need to check this BUILD_START is an event that should be processed before BUILD_SUCCESS.
1306   print "[", __LINE__, "] $build_project_id, $build_target_id, $info_package_id, $package_build_time\n";
1307   if( my $bpa_id = check_prior_to_build_success($build_project_id, $build_target_id, $info_package_id, $package_build_time) ) {
1308     print "[", __LINE__, "] update build time only!!! $bpa_id, $package_build_time\n";
1309     update_build_start_time($bpa_id, $package_build_time);
1310   } else {
1311
1312     insert_package_build($build_target_id, $info_package_id);
1313     print "[", __LINE__, "][package_build_start($package_name)] $reason, $build_target_id, $info_package_id, $build_log_url\n";
1314     $package_build_start_sth->execute($package_build_time, $state, $reason, $build_log_url, $build_target_id, $info_package_id);
1315   }
1316
1317   my $elapsed_time = get_cur_time() - $start_time;
1318   print "[",__LINE__,"] took $elapsed_time seconds.\n";
1319
1320   if( is_prerelease_project($proj_name) && is_package_in_project($package_name, $proj_name) ) {
1321     print "[$package_name] trigger_make_dep_graph... $proj_name, $repo/$arch, $build_project_id\n";
1322     trigger_make_dep_graph($build_project_id, $proj_name, $repo, $arch);
1323   }
1324 }
1325
1326 #-------------------------------------------------------------------------------
1327 sub get_build_target_id {
1328   my ($build_project_id, $repo, $arch) = @_;
1329
1330   if ( defined $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} ) {
1331     return $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'};
1332   }
1333
1334   $get_build_target_id_sth->execute($build_project_id, $repo, $arch);
1335   my $arr_ref = $get_build_target_id_sth->fetchrow_arrayref;
1336   return 0 if ! defined $arr_ref;
1337
1338   my $build_id = @$arr_ref[0];
1339
1340   $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} = $build_id;
1341
1342   return $build_id;
1343 }
1344
1345 #-------------------------------------------------------------------------------
1346 sub insert_package_build {
1347   my ($build_target_id, $info_package_id) = @_;
1348
1349   # last_flag = 'N';
1350   $package_disable_last_flag_sth->execute($build_target_id, $info_package_id);
1351
1352   print "[", __LINE__, "][insert_package_build] $build_target_id, $info_package_id\n";
1353   return $insert_package_build_if_not_exist_sth->execute($build_target_id, $info_package_id);
1354 }
1355
1356 #-------------------------------------------------------------------------------
1357 sub insert_package_build_if_not_exist {
1358   my ($build_target_id, $info_package_id, $current_status) = @_;
1359
1360   $check_package_build_sth->execute($build_target_id, $info_package_id);
1361   my $arr_ref = $check_package_build_sth->fetchrow_arrayref;
1362   if ( defined $arr_ref ) {
1363     # if the last status is the same as the current status, do not insert.
1364     if( @$arr_ref[0] eq $current_status ) {
1365       return 0;
1366     }
1367
1368     #going through...
1369   }
1370
1371   # last_flag = 'N';
1372   $package_disable_last_flag_sth->execute($build_target_id, $info_package_id);
1373
1374   #print "[", __LINE__, "][insert_package_build_if_not_exist] $build_target_id, $info_package_id\n";
1375   return $insert_package_build_if_not_exist_sth->execute($build_target_id, $info_package_id);
1376 }
1377
1378 #-------------------------------------------------------------------------------
1379 sub get_buildmonitor_project_status {
1380   my ($build_target_id, $projid, $repoid, $archid) = @_;
1381
1382   my $failed_detail = "";
1383   my $failed = 'S';
1384
1385   print "[",__LINE__,"] get_buildmonitor_project_status $projid, $repoid, $archid, $build_target_id...\n";
1386
1387   # commit the current transaction to see the lastest value.
1388   my $failed_trans = 1;
1389   while($failed_trans) {
1390     $failed_trans = 0;
1391     eval {
1392       $g_dbh->begin_work();
1393       $search_not_succeeded_packages_sth->execute($build_target_id);
1394       while( my $arr_ref = $search_not_succeeded_packages_sth->fetchrow_arrayref ) {
1395 # we found not succeeded packages in this build_target_id.
1396         $failed_detail = $failed_detail . @$arr_ref[0] . "(" . @$arr_ref[1] . ":" . @$arr_ref[2] . "),";
1397         $failed = 'F';
1398       }
1399       $g_dbh->commit();
1400     };
1401     if($@) {
1402       warn "[", __LINE__, "] Transaction aborted because $@\n";
1403       $g_dbh->rollback();
1404       $failed_trans = 1;
1405       printf "[", __LINE__, "] waiting 10 seconds...\n";
1406       sleep(10);
1407     }
1408   }
1409
1410   $failed_detail = substr($failed_detail, 0, 100);
1411
1412   print "[", __LINE__, "] $projid: $failed, $failed_detail\n";
1413
1414   return ($failed, $failed_detail);
1415 }
1416
1417 sub build_status_exists {
1418   my ($build_project_id, $repo, $arch, $info_package_id) = @_;
1419
1420   return 0 unless $g_dbh;
1421
1422   $build_status_exists_sth->execute($build_project_id, $repo, $arch, $info_package_id);
1423   my $arr_ref = $build_status_exists_sth->fetchrow_arrayref;
1424   if( ! $arr_ref ) {
1425     return 0;
1426   }
1427
1428   return 1;
1429 }
1430
1431 #-------------------------------------------------------------------------------
1432 sub package_build_status {
1433   my ($config, $proj, $repo, $arch, $package_name, $time, $status, $detail) = @_;
1434
1435   return unless $g_dbh;
1436   if ($status eq 'scheduled' ) {
1437     # This 'scheduled' state occurs after package_build_start, so do not insert this state to the table.
1438     return;
1439   }
1440   if ($status eq 'blocked' ) {
1441     # This 'blocked' state always changed to scheduling state, do not log.
1442     return;
1443   }
1444
1445   my $start_time = get_cur_time();
1446   my $build_project_id = get_build_project_id($config, $proj);
1447
1448   if( ! $detail ) { $detail = ""; }
1449
1450   if( $build_project_id <= 0 ) {
1451     #print Dumper($config);
1452     #print "[",__LINE__,"][$proj] build_project_id($build_project_id) is strange. do not execute.\n";
1453     #print "[", __LINE__, "][package_build_status] $status, $detail, $build_project_id, $repo, $arch, $info_package_id, $trigger_reason\n";
1454     return;
1455   }
1456
1457   if ($status eq 'done') {
1458     # do not insert 'done', this 'done' implies all of 'succeeded', 'failed', and 'unchanged'.
1459     $status = $detail;
1460     $detail = "";
1461   }
1462
1463   my $info_package_id = get_package_id($proj, $package_name);
1464
1465   my $state = "Building";
1466   project_build_start($build_project_id, $proj, $repo, $arch, $state, $start_time);
1467   my $build_target_id=get_build_target_id($build_project_id, $repo, $arch);
1468   print "[", __LINE__, "] $build_project_id, $build_target_id, $info_package_id, $start_time\n";
1469
1470   # for excluded, it is inserted only if there is any other package build_status.
1471   if( $status eq 'excluded' || $status eq 'disabled' ) {
1472     if( ! build_status_exists($build_project_id, $repo, $arch, $info_package_id) ) {
1473       return;
1474     }
1475   }
1476
1477   # if no row for this package_build. insert a row.
1478   my $affected_rows = insert_package_build_if_not_exist($build_target_id, $info_package_id, $status);
1479
1480   if( $affected_rows == 0 ) {
1481     if( $status eq "broken" ) {
1482       my $cur_time = $time;
1483       print "[", __LINE__, "][package_build_status] $status, $detail, $build_target_id, $info_package_id\n";
1484       $package_build_broken_status_end_time_only_sth->execute("$status", "$detail", $cur_time, $build_target_id, $info_package_id);
1485     }
1486   } else {
1487     if( $status eq "failed" || $status eq "broken"  || $status eq "unresolvable" ||
1488         $status eq "excluded" || $status eq "disabled" ) {
1489       # except "no source uploaded."
1490       if( $detail ne "no source uploaded" ) {
1491         my $cur_time = $time;
1492         print "[", __LINE__, "][package_build_status] $status, $detail, $build_project_id, $repo, $arch, $info_package_id\n";
1493         $package_build_broken_status_sth->execute("$status", "$detail", $cur_time-1, $cur_time, $build_target_id, $info_package_id);
1494         #project_build_finished_if_not_started($build_project_id,$proj,$repo,$arch,$cur_time-1, $cur_time);
1495       }
1496     } else {
1497       #print "[", __LINE__, "][package_build_status] $status, $detail, $build_project_id, $repo, $arch, $info_package_id\n";
1498       #$package_build_status_sth->execute("$status", "$detail", $build_target_id, $info_package_id);
1499     }
1500   }
1501   my $elapsed_time = get_cur_time() - $start_time;
1502   print "[",__LINE__,"] took $elapsed_time seconds.\n";
1503 }
1504
1505 sub check_build_target_failed {
1506   my ($build_target_id) = @_;
1507
1508   $check_build_target_failed_sth->execute($build_target_id);
1509   my $arr_ref = $check_build_target_failed_sth->fetchrow_arrayref;
1510   if( !$arr_ref ) {
1511     return 0;
1512   }
1513
1514   return @$arr_ref[0] eq 'F';
1515 }
1516
1517 sub update_build_target_succeeded {
1518   my ($build_target_id) = @_;
1519
1520   print "[", __LINE__, "] update build_target succeeded.\n";
1521   $update_build_target_succeeded_sth->execute($build_target_id);
1522 }
1523
1524 sub update_project_build_status_if_failed {
1525   my ($proj_name, $repo, $arch) = @_;
1526
1527   my $update_required = 0;
1528
1529   my $build_project_id = get_build_project_id("", $proj_name);
1530   my $build_target_id  = get_build_target_id($build_project_id, $repo, $arch);
1531
1532   # if the build_status of build_target is failed, we need to recalculate this.
1533   print "[", __LINE__, "] Check build target failed.\n";
1534   if( ! check_build_target_failed($build_target_id) ) {
1535     return;
1536   }
1537
1538   my ($status, $detail) = get_buildmonitor_project_status($build_target_id, $proj_name, $repo, $arch);
1539   print "[", __LINE__, "] buildmonitor_project_status = $status\n";
1540
1541   if( $status eq 'F' ) {
1542     return;
1543   }
1544
1545   # update build_target status
1546   update_build_target_succeeded($build_target_id);
1547
1548   # update build_project status.
1549   if( build_project_completed($proj_name) eq 'C' ) {
1550     # if final status is completed, send published event to jenkins to make
1551     # a snapshot for this version.
1552     trigger_obs_published($proj_name);
1553   }
1554 }
1555
1556 #-------------------------------------------------------------------------------
1557 sub get_rawlog_url {
1558   my ($proj_name, $repo, $arch, $pkg_name) = @_;
1559
1560   my @arr;
1561   if ( $BSConfig::obs_version eq "2.4" ) {
1562     # for obs 2.4
1563     @arr = (
1564         $BSConfig::obs_frontend_url,
1565         "package",
1566         "rawlog?arch=$arch&package=$pkg_name&project=$proj_name&repository=$repo"
1567         );
1568   } else {
1569     # for obs 2.7
1570     @arr = (
1571         $BSConfig::obs_frontend_url,
1572         "public/build",
1573         $proj_name,
1574         $repo,
1575         $arch,
1576         $pkg_name,
1577         "_log"
1578         );
1579   }
1580   my $url = join("/", @arr);
1581
1582   return $url;
1583 }
1584
1585 sub copy_build_log {
1586   my ($projid, $repo, $arch, $packid, $log_file, $status) = @_;
1587
1588   my $aextrep = "$projid/$repo/$arch";
1589   $aextrep =~ s/:/:\//g;
1590   my $build_log_dir = "buildlogs/$aextrep";
1591   my $build_log_machine_dir = "$extrepodir/$build_log_dir";
1592   mkdir_p($build_log_machine_dir);
1593
1594   my $datetime_str=strftime "%Y%m%d_%H:%M:%S", localtime;
1595   my $filename = "$packid-$datetime_str.log";
1596   my $dst_file_path = "$build_log_machine_dir/$filename";
1597
1598   # copy only if the package build is failed.
1599   if( $status ne "succeeded" ) {
1600     copy($log_file, $dst_file_path) or print "[",__LINE__,"] Copy failed: $!\n";
1601   }
1602
1603   my $log_url = "$BSConfig::repodownload/"."$build_log_dir/$filename";
1604
1605   return $log_url;
1606 }
1607 #-------------------------------------------------------------------------------
1608 sub package_build_finished {
1609   my ($config, $projid, $repo, $arch, $package_name, $time,
1610       $status, $pre_install_time, $install_time, $main_build_time) = @_;
1611
1612   return unless $g_dbh;
1613   my $start_time = get_cur_time();
1614   my $build_project_id = get_build_project_id($config, $projid);
1615
1616   my $detail = "";
1617
1618   if( $status eq 'unchanged' ) {
1619     $status = 'succeeded';
1620     $detail = 'unchanged';
1621   }
1622
1623   my $info_package_id=get_package_id($projid, $package_name);
1624   if ( $package_name =~ ".*_aggregate") {
1625       $pre_install_time = 0;
1626       $install_time = 0;
1627       $main_build_time = 0;
1628   } else {
1629     #home:prerelease:Tizen:Mobile:submit:tizen:20161101.025248
1630     #$build_log_url=copy_build_log($projid, $repo, $arch, $package_name, "$build_result_dir/logfile", $status);
1631     #$build_log_url=get_rawlog_url($projid, $repo, $arch, $package_name);
1632
1633     if ( ! defined $pre_install_time ) {
1634       $pre_install_time = 0;
1635       $install_time = 0;
1636       $main_build_time = 0;
1637     }
1638   }
1639
1640   if( $build_project_id <= 0 ) {
1641     #print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
1642     #print "[", __LINE__, "][package_build_finished($package_name)] $pre_install_time, $install_time, $main_build_time, $build_log_url, $status, $build_project_id, $repo, $arch, $info_package_id\n";
1643     return;
1644   }
1645
1646   # Since _aggregate packages does not trigger BUILD_START, it always calls package_build_start().
1647   if ( $package_name =~ ".*_aggregate" ) {
1648     package_build_start("", $projid, $repo, $arch, $package_name, $time);
1649   }
1650
1651   my $build_target_id=get_build_target_id($build_project_id, $repo, $arch);
1652   print "[", __LINE__, "][package_build_finished($package_name)] $pre_install_time, $install_time, $main_build_time, $status, $build_target_id, $repo, $arch, $info_package_id\n";
1653   my $transaction_aborted = 1;
1654   while( $transaction_aborted ) {
1655     $transaction_aborted = 0;
1656     eval {
1657       $g_dbh->begin_work();
1658
1659       # if there is no 'Building' rows, insert a row.
1660       # This happens when BUILD_SUCCESS is processed before BUILD_START is processed.
1661       if( insert_package_build_if_not_exist($build_target_id, $info_package_id, 'Building') ) {
1662         my $state = "Building";
1663         my $reason = "";
1664         my $build_log_url = "";
1665
1666         print "[", __LINE__, "][NO_BUILDING! package_build_start($package_name)] $reason, $build_target_id, $info_package_id, $build_log_url\n";
1667         $package_build_start_sth->execute(1, $state, $reason, $build_log_url, $build_target_id, $info_package_id);
1668       }
1669
1670       $package_build_finished_sth->execute($time, $pre_install_time, $install_time, $main_build_time, "$status", "$detail", $build_target_id, $info_package_id);
1671       $g_dbh->commit();
1672     };
1673     if($@) {
1674       $transaction_aborted = 1;
1675       warn "[", __LINE__, "] Transaction aborted because $@\n";
1676     }
1677   }
1678
1679   if( $status eq 'failed' ) {
1680     eval {
1681       $g_dbh->begin_work();
1682       # compensate project build status if it is failed.
1683       $compensate_project_build_status_sth->execute('F', "$package_name:Failed, ", $build_target_id, $time);
1684       $g_dbh->commit();
1685     };
1686     if($@) {
1687       warn "[", __LINE__, "] Transaction aborted because $@\n";
1688     }
1689   }
1690
1691   my $elapsed_time = get_cur_time() - $start_time;
1692   print "[",__LINE__,"] took $elapsed_time seconds.\n";
1693 }
1694
1695 #-------------------------------------------------------------------------------
1696 sub parse_statistics {
1697   my ($filename) = @_;
1698
1699   if ( ! defined $filename ) {
1700     return undef;
1701   }
1702
1703   #print "filename=$filename\n";
1704   #if( open(DATA1, "<$filename") ) {
1705     #while( <DATA1> ) {
1706       #print $_;
1707     #}
1708   #}
1709   my $statistics=BSUtil::readxml($filename, $BSXML::buildstatistics, 1);
1710
1711   #print Dumper($statistics);
1712
1713   my $preinstall=$statistics->{'times'}->{'preinstall'}->{'time'}->{'_content'};
1714   my $install   =$statistics->{'times'}->{'install'}->{'time'}->{'_content'};
1715   my $main      =$statistics->{'times'}->{'main'}->{'time'}->{'_content'};
1716
1717   return $preinstall, $install, $main;
1718 }
1719
1720 sub connect_db {
1721   my ($db_conf_file_user) = @_;
1722
1723   return $g_dbh if defined $g_dbh && $g_dbh->ping ;
1724
1725   my $db_conf_file = $db_conf_file_user || "db.conf";
1726   my %db_conn_info = get_db_connect_info($db_conf_file);
1727
1728   #print Dumper(%db_conn_info);
1729
1730   my $db=$db_conn_info{'DB'};
1731   my $host=$db_conn_info{'HOST'};
1732   my $port=$db_conn_info{'PORT'};
1733   my $user=$db_conn_info{'USER'};
1734   my $pass=$db_conn_info{'PASS'};
1735
1736   my $enabled=$db_conn_info{'ENABLED'};
1737   return if( $enabled && $enabled eq 'FALSE' );
1738
1739   my $dbh=DBI->connect("DBI:mysql:database=$db;host=$host;port=$port;mysql_connect_timeout=3",$user,$pass);
1740   if( ! $dbh ) {
1741     print "BuildMonitor DB connection failed. host=$host, port=$port, user=$user\n";
1742     return;
1743   }
1744
1745   $dbh->{mysql_auto_reconnect} = 1;
1746
1747   print "[BuildMonitor] DB is connected to $host, DB:$db\n";
1748
1749   $g_dbh = $dbh;
1750
1751   #-----------------------------------------------------------------------------
1752   # build_target TABLE
1753   my $project_build_check_sql=qq/SELECT * FROM build_target WHERE build_project_id=? AND repository=? AND arch=? AND start_time!=0 AND end_time=0;/;
1754   $project_build_check_sth=$g_dbh->prepare($project_build_check_sql);
1755
1756   my $project_disable_last_flag_sql=qq/UPDATE build_target SET last_flag='N' WHERE build_project_id=? AND repository=? AND arch=?;/;
1757   $project_disable_last_flag_sth=$g_dbh->prepare($project_disable_last_flag_sql);
1758
1759   my $check_build_target_failed_sql = qq/SELECT status FROM build_target WHERE id=?;/;
1760   $check_build_target_failed_sth = $g_dbh->prepare($check_build_target_failed_sql);
1761
1762   my $update_build_target_succeeded_sql = qq/UPDATE build_target SET status='S' WHERE id=?;/;
1763   $update_build_target_succeeded_sth = $g_dbh->prepare($update_build_target_succeeded_sql);
1764
1765   #my $project_build_start_sql=qq/UPDATE build_target SET start_time=FROM_UNIXTIME(?), status=? WHERE build_project_id=? and repository=? and arch=? and start_time=0;/;
1766   my $project_build_start_sql=qq/INSERT INTO build_target (build_project_id, repository, arch, start_time, status) VALUES (?,?,?,FROM_UNIXTIME(?),?);/;
1767   $project_build_start_sth=$g_dbh->prepare($project_build_start_sql);
1768
1769   my $get_build_target_id_sql=qq/SELECT id FROM build_target WHERE build_project_id=? AND repository=? AND arch=? ORDER BY id DESC LIMIT 1;/;
1770   $get_build_target_id_sth=$g_dbh->prepare($get_build_target_id_sql);
1771
1772   #my $project_build_finished_sql=qq/UPDATE build_target SET end_time=FROM_UNIXTIME(?), status=?, status_reason=? WHERE build_project_id=? and repository=? and arch=? and start_time!=0 ORDER BY id DESC LIMIT 1;/;
1773   my $project_build_finished_sql=qq/UPDATE build_target SET end_time=FROM_UNIXTIME(?), status=?, status_reason=? WHERE build_project_id=? and repository=? and arch=? and start_time!=0 AND end_time=0;/;
1774   $project_build_finished_sth=$g_dbh->prepare($project_build_finished_sql);
1775
1776   my $compensate_project_build_status_sql=qq/UPDATE build_target SET status=?, status_reason=CONCAT(?,status_reason) WHERE id=? and start_time!=0 AND end_time > ? AND last_flag='Y';/;
1777   $compensate_project_build_status_sth=$g_dbh->prepare($compensate_project_build_status_sql);
1778
1779   my $project_build_finished_if_not_started_check_sql=qq/SELECT * FROM build_target WHERE build_project_id=? AND repository=? AND arch=? AND start_time!=0 AND end_time=0;/;
1780   $project_build_finished_if_not_started_check_sth=$g_dbh->prepare($project_build_finished_if_not_started_check_sql);
1781
1782   my $project_build_finished_if_not_started_sql=qq/INSERT build_target (build_project_id, repository, arch, start_time, end_time, status, status_reason) VALUES(?, ?, ?, FROM_UNIXTIME(?), FROM_UNIXTIME(?), ?, ?);/;
1783   $project_build_finished_if_not_started_sth = $g_dbh->prepare($project_build_finished_if_not_started_sql);
1784
1785   #-----------------------------------------------------------------------------
1786   # build_package TABLE
1787   my $package_build_start_sql=qq/UPDATE build_package SET start_time=FROM_UNIXTIME(?), build_status=?, trigger_reason=?, build_log_url=? WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
1788   $package_build_start_sth=$g_dbh->prepare($package_build_start_sql);
1789
1790   my $package_build_status_sql=qq/UPDATE build_package SET build_status=?, status_reason=? WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
1791   $package_build_status_sth=$g_dbh->prepare($package_build_status_sql);
1792
1793   my $package_build_broken_status_sql=qq/UPDATE build_package SET build_status=?, status_reason=?, start_time=FROM_UNIXTIME(?), end_time=FROM_UNIXTIME(?) WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
1794   $package_build_broken_status_sth=$g_dbh->prepare($package_build_broken_status_sql);
1795
1796   my $package_build_broken_status_end_time_only_sql=qq/UPDATE build_package SET build_status=?, status_reason=?, end_time=FROM_UNIXTIME(?) WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
1797   $package_build_broken_status_end_time_only_sth=$g_dbh->prepare($package_build_broken_status_end_time_only_sql);
1798
1799   my $package_disable_last_flag_sql=qq/UPDATE build_package SET last_flag='N' WHERE build_target_id=? AND info_package_id=?;/;
1800   $package_disable_last_flag_sth=$g_dbh->prepare($package_disable_last_flag_sql);
1801
1802   my $insert_package_build_if_not_exist_sql=qq/INSERT INTO build_package (build_target_id, info_package_id) VALUES(?,?);/;
1803   $insert_package_build_if_not_exist_sth=$g_dbh->prepare($insert_package_build_if_not_exist_sql);
1804
1805   my $check_package_build_sql=qq/SELECT build_status FROM build_package WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
1806   $check_package_build_sth=$g_dbh->prepare($check_package_build_sql);
1807
1808   my $package_build_finished_sql=qq/UPDATE build_package SET end_time=FROM_UNIXTIME(?), pre_install_time=?, install_time=?, main_build_time=?, build_status=?, status_reason=? WHERE build_target_id=? AND info_package_id=? AND start_time!=0 AND end_time=0 ORDER BY id DESC LIMIT 1;/;
1809   $package_build_finished_sth=$g_dbh->prepare($package_build_finished_sql);
1810
1811   my $search_not_succeeded_packages_sql = qq/SELECT ip.package_name, bp.build_status, bp.status_reason FROM build_package bp, info_package ip WHERE bp.build_target_id=? AND bp.build_status != 'succeeded' AND bp.build_status != 'excluded' AND bp.build_status != 'disabled' AND ip.id = bp.info_package_id AND bp.last_flag='Y';/;
1812   $search_not_succeeded_packages_sth = $g_dbh->prepare($search_not_succeeded_packages_sql);
1813
1814   my $build_status_sql = qq/SELECT ipa.package_name, bpa.build_status, bt.repository, bt.arch FROM info_package ipa, build_package bpa, build_project bp, build_target bt WHERE bpa.build_target_id = bt.id AND bt.build_project_id = bp.id AND bpa.info_package_id = ipa.id AND bp.id=? ORDER BY bpa.id;/;
1815   $build_status_sth = $g_dbh->prepare($build_status_sql);
1816
1817   my $build_status_exists_sql = qq/SELECT bp.id FROM build_project bp, build_target bt, build_package bpa WHERE bp.id = bt.build_project_id AND bpa.build_target_id = bt.id AND bp.id = ?  AND bt.repository = ?  AND bt.arch = ?  AND bpa.info_package_id = ? LIMIT 1;/;
1818   $build_status_exists_sth = $g_dbh->prepare($build_status_exists_sql);
1819
1820   my $check_prior_to_build_success_sql =
1821 qq/
1822 SELECT bpa.id
1823 FROM build_project bp, build_target bt, build_package bpa
1824 WHERE bt.build_project_id = bp.id
1825 AND bpa.build_target_id = bt.id
1826 AND bp.id = ?
1827 AND bt.id = ?
1828 AND bpa.info_package_id = ?
1829 AND bpa.end_time > FROM_UNIXTIME(?)
1830 AND bpa.start_time = FROM_UNIXTIME(1)
1831 /;
1832   $check_prior_to_build_success_sth = $g_dbh->prepare($check_prior_to_build_success_sql);
1833
1834   my $update_build_start_time_sql =
1835 qq/
1836 UPDATE build_package SET start_time=FROM_UNIXTIME(?) WHERE id=?
1837 /;
1838   $update_build_start_time_sth = $g_dbh->prepare($update_build_start_time_sql);
1839
1840   #-----------------------------------------------------------------------------
1841   # build_project TABLE
1842   my $get_build_project_id_sql=qq/SELECT id FROM build_project WHERE info_project_id=? ORDER BY id DESC LIMIT 1;/;
1843   $get_build_project_id_sth=$g_dbh->prepare($get_build_project_id_sql);
1844
1845   my $get_build_project_id_R_sql=qq/SELECT id FROM build_project WHERE info_project_id=? AND status=?;/;
1846   $get_build_project_id_R_sth = $g_dbh->prepare($get_build_project_id_R_sql);
1847
1848   my $insert_build_project_sql=qq/INSERT INTO build_project (info_project_id, status) VALUES(?,?);/;
1849   $insert_build_project_sth=$g_dbh->prepare($insert_build_project_sql);
1850
1851   my $get_build_project_status_sql = qq/SELECT status FROM build_project WHERE id=?;/;
1852   $get_build_project_status_sth = $g_dbh->prepare($get_build_project_status_sql);
1853
1854   my $complete_build_project_sql=qq/UPDATE build_project SET status=? WHERE id=?;/;
1855   $complete_build_project_sth = $g_dbh->prepare($complete_build_project_sql);
1856
1857   #-----------------------------------------------------------------------------
1858   # info_project TABLE
1859   my $get_project_id_sql=qq/SELECT id FROM info_project WHERE project_name=? AND active_flag='Y' LIMIT 1;/;
1860   $get_project_id_sth=$g_dbh->prepare($get_project_id_sql);
1861
1862   my $insert_project_sql=qq/INSERT INTO info_project (project_name, description, pre_release_flag, active_flag, ref_proj) VALUES (?,?,?,?,?);/;
1863   $insert_project_sth=$g_dbh->prepare($insert_project_sql);
1864
1865   my $update_project_sql=qq/UPDATE info_project SET description=?, pre_release_flag=?, active_flag=?, ref_proj=? WHERE project_name=?;/;
1866   $update_project_sth=$g_dbh->prepare($update_project_sql);
1867
1868   #my $delete_project_info_sql=qq/DELETE FROM info_project WHERE project_name=?;/;
1869   my $delete_project_info_sql=qq/UPDATE info_project SET active_flag='N' WHERE project_name=? AND active_flag='Y';/;
1870   $delete_project_info_sth=$g_dbh->prepare($delete_project_info_sql);
1871
1872   #-----------------------------------------------------------------------------
1873   # info_package TABLE
1874   my $get_package_id_sql=qq/SELECT id FROM info_package WHERE info_project_id=? AND package_name=? AND active_flag='Y' LIMIT 1;/;
1875   $get_package_id_sth=$g_dbh->prepare($get_package_id_sql);
1876
1877   my $insert_package_info_sql=qq/INSERT INTO info_package (info_project_id, package_name, description, active_flag) VALUES (?,?,?, 'Y');/;
1878   $insert_package_info_sth=$g_dbh->prepare($insert_package_info_sql);
1879
1880   my $update_package_info_sql=qq/UPDATE info_package SET description=? WHERE id=? AND active_flag='Y';/;
1881   $update_package_info_sth=$g_dbh->prepare($update_package_info_sql);
1882
1883   my $delete_package_info_sql=qq/UPDATE info_package SET active_flag='N' WHERE id=? AND active_flag='Y';/;
1884   $delete_package_info_sth=$g_dbh->prepare($delete_package_info_sql);
1885
1886   my $update_latest_sr_sql=qq/UPDATE info_package SET latest_sr_status_id=? WHERE id=? AND active_flag='Y';/;
1887   $update_latest_sr_sth=$g_dbh->prepare($update_latest_sr_sql);
1888
1889   #my $delete_packages_info_sql=qq/DELETE FROM info_package WHERE info_project_id=?;/;
1890   my $delete_packages_info_sql=qq/UPDATE info_package SET active_flag='N' WHERE info_project_id=? AND active_flag='Y';/;
1891   $delete_packages_info_sth=$g_dbh->prepare($delete_packages_info_sql);
1892
1893   return $dbh;
1894 }
1895
1896 #-------------------------------------------------------------------------------
1897 sub disconnect_db {
1898   my ($dbh) = @_;
1899
1900   print "BuildMonitorDB disconnecting...\n";
1901
1902   $dbh->disconnect();
1903 }
1904
1905 #-------------------------------------------------------------------------------
1906 # Private modules.
1907 #-------------------------------------------------------------------------------
1908 sub trim { my $s = shift; $s =~ s/^\s+|\s+$//g; return $s };
1909
1910 #-------------------------------------------------------------------------------
1911 sub get_db_connect_info {
1912   my ($db_conf_file) = @_;
1913   my %db_conn_info;
1914
1915   open(DB_CONF_FILE, "<$db_conf_file") or die "$db_conf_file: No DB conf file.";
1916
1917   while(<DB_CONF_FILE>) {
1918     my $line = $_;
1919
1920     if( $line =~ /^DB:/ ) {
1921       my @sp= split /:/, $line;
1922       $db_conn_info{'DB'} = trim($sp[1]);
1923     } elsif( $line =~ /^HOST:/ ) {
1924       my @sp= split /:/, $line;
1925       $db_conn_info{'HOST'} = trim($sp[1]);
1926     } elsif( $line =~ /^PORT:/ ) {
1927       my @sp= split /:/, $line;
1928       $db_conn_info{'PORT'} = trim($sp[1]);
1929     } elsif( $line =~ /^USER:/ ) {
1930       my @sp= split /:/, $line;
1931       $db_conn_info{'USER'} = trim($sp[1]);
1932     } elsif( $line =~ /^PASS:/ ) {
1933       my @sp= split /:/, $line;
1934       $db_conn_info{'PASS'} = trim($sp[1]);
1935     } elsif( $line =~ /^ENABLED:/ ) {
1936       my @sp= split /:/, $line;
1937       $db_conn_info{'ENABLED'} = trim($sp[1]);
1938     }
1939   }
1940
1941   return %db_conn_info;
1942 }
1943
1944 sub get_build_project_id {
1945   my ($config_str, $proj_name) = @_;
1946
1947   my $info_project_id = get_project_id($proj_name);
1948   eval {
1949     $g_dbh->begin_work();
1950     $get_build_project_id_sth->execute($info_project_id);
1951     $g_dbh->commit();
1952   };
1953   my $arr_ref = $get_build_project_id_sth->fetchrow_arrayref;
1954   if ( defined $arr_ref ) {
1955     my $build_project_id = @$arr_ref[0];
1956     print "build_project_id $proj_name => $info_project_id => $build_project_id\n";
1957     return $build_project_id;
1958   }
1959
1960   if( $config_str eq "test" ) {
1961     return $info_project_id;
1962   }
1963
1964   if( ! $config_str ) {
1965     return 0;
1966   }
1967
1968   if( $config_str =~ /BuildProjectId:\s*([0-9]+)/ ) {
1969     if( ! $1 ) {
1970       return 0;
1971     }
1972
1973     return $1;
1974   }
1975
1976   return 0;
1977 }
1978 #-------------------------------------------------------------------------------
1979 # final statement.
1980 1;