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