src/game/server/database/build/linux/database_update.pl

   1 #!/usr/bin/perl
   2
   3 # Script to create/initialze/update the database
   4 # Update the database to the latest version by applying delta files
   5
   6 use strict;
   7 use Getopt::Long;
   8
   9 &main;
  10
  11 sub main
  12 {
  13
  14     &GetOptions("delta","drop","create","toversion=s","fromversion=s","packages","noversioncheck","help","username:s","loginusername:s","goldusername:s","password:s","service:s","buildcluster","grantpermissions","grantgoldpermissions","configurelimits","addclusterforlogin","createnewcluster", "newschema", "dbausername:s","dbapassword:s","queries","execquery:s");
  15
  16     if ($::opt_help || !($::opt_drop || $::opt_create || $::opt_delta || $::opt_packages || $::opt_grantpermissions || $::opt_grantgoldpermissions || $::opt_configurelimits || $::opt_addclusterforlogin || $::opt_createnewcluster||$::opt_queries||$::opt_execquery))
  17     {
  18         print "Usage: database_update.pl [COMMAND] [OPTION]... \n";
  19         print "Create a new database or update an existing database to the latest version.\n";
  20         print "\n";
  21         print "Commands:\n";
  22         print "  --create                Create a new database.\n";
  23         print "  --delta                 Update the database by applying deltas\n";
  24         print "  --drop                  Drop an existing database.\n";
  25         print "  --packages              Update the PL/SQL packages. (Done automatically by other options.)\n";
  26         print "  --createnewcluster      Do all the steps needed to create a new live cluster.\n";
  27         print "  --grantpermissions      Grant permissions on all the objects in the schema to public\n";
  28         print "  --grantgoldpermissions  Grant permissions on all the objects in the gold schema to public (useful after importing the gold schema).\n";
  29         print "  --configurelimits       Prompt for character and account limits stored in the login database,\n";
  30         print "                            e.g. number of users per cluster\n";
  31         print "  --addclusterforlogin    Add a cluster to the login database (will prompt for the name)\n";
  32         print "  --help                  Display these options.\n";
  33         print "\n";
  34         print "Options:\n";
  35         print "  --fromversion=VER       Override checking the database and specify the\n";
  36         print "                            starting version instead.\n";
  37         print "  --toversion=VER         Specify the version to which to update the database.\n";
  38         print "                            (Default:  latest version.)\n";
  39         print "  --noversioncheck        Don't check the version number after running each update script.\n";
  40         print "  --username=NAME         Specify the database user name. (Defaults to \$USER.)\n";
  41         print "  --loginusername=NAME    Specify the login database user name, required for --configurelimits and --addclusterforlogin\n";
  42         print "  --goldusername=NAME     Specify the gold database user name, required for --grantgoldpermissions\n";
  43         print "  --password=PASSWORD     Specify the database password.  (Defaults to \"changeme\".)\n";
  44         print "  --service=NAME          Specify the database service name.  (Defaults to \"swodb\".)\n";
  45         print "  --buildcluster          Use this option if you set usename = buildcluster\n";
  46         print "  --newschema             Add this option to auto create schema if it doesn't exist\n";
  47         print "  --dbausername           Must be used with newschema option\n";
  48         print "  --dbapassword           Must be used with newschema option\n";
  49         print "  --queries               Run one time queries from list in automated_queries table\n";
  50         print "  --execquery             Execute query\n";
  51         print "\n";
  52
  53         exit;
  54     }
  55
  56     if ($::opt_createnewcluster)
  57     {
  58         # this is equivalent to setting the following options individually
  59         $::opt_create=1;
  60         $::opt_grantpermissions=1;
  61         $::opt_grantgoldpermissions=1;
  62         $::opt_configurelimits=1;
  63         $::opt_addclusterforlogin=1;
  64     }
  65
  66     #check for usernames that must be specified
  67     if ($::opt_loginusername eq "" && ($::opt_configurelimits || $::opt_addclusterforlogin))
  68     {
  69         die "The login database user name must be specified with --loginusername=NAME\n";
  70     }
  71     if ($::opt_goldusername eq "" && ($::opt_grantgoldpermissions))
  72     {
  73         die "The gold database user name must be specified with --goldusername=NAME\n";
  74     }
  75
  76     &doDrop if ($::opt_drop);
  77     &doCreate if ($::opt_create);
  78     if ($::opt_delta)
  79     {
  80         $::didAnUpdate = &doDeltaUpdates($::opt_fromversion,$::opt_toversion,$::opt_noversioncheck);
  81     }
  82
  83     if ($::didAnUpdate || $::opt_packages)
  84     {
  85         &updatePackages;
  86     }
  87
  88     if ($::opt_grantpermissions)
  89     {
  90         &doGrantPermissions;
  91     }
  92
  93     if ($::opt_grantgoldpermissions)
  94     {
  95         &doGrantGoldPermissions;
  96     }
  97
  98     if ($::opt_configureforlogin)
  99     {
 100         &doConfigureForLogin;
 101     }
 102
 103     if ($::opt_configurelimits)
 104     {
 105         &doConfigureLimits;
 106     }
 107
 108     if ($::opt_addclusterforlogin)
 109     {
 110         &doAddClusterForLogin;
 111     }
 112
 113     if ($::opt_queries)
 114     {
 115         &doRunOneTimeQueries;
 116     }
 117
 118     if ($::opt_execquery)
 119     {
 120         &doExecQuery;
 121     }
 122
 123 exit 0;
 124
 125 }
 126
 127 sub doDrop
 128 {
 129     system (&sqlplus." @../../queries/drop_all_objects.sql");
 130 }
 131
 132 sub doCreate
 133 {
 134     if ($::opt_toversion)
 135     {
 136         die "Cannot specify --toversion with --create\n";
 137     }
 138
 139     if ($::opt_newschema)
 140     {
 141         my($user);
 142         $user=$::opt_username;
 143         $user=$ENV{"USER"} if ($user eq "");
 144
 145         my $pwd = $::opt_password;
 146         $pwd=$ENV{"DB_PASSWORD"} if ($pwd eq "");
 147         $pwd="changeme" if ($pwd eq "");
 148
 149         open (OUTFILE,"| ".&sqlplusForDBA." > /dev/null");
 150         print OUTFILE "CREATE USER $user PROFILE DEFAULT IDENTIFIED BY $pwd DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;\n";
 151         print OUTFILE "GRANT CREATE TYPE TO $user;\n";
 152         print OUTFILE "GRANT UNLIMITED TABLESPACE TO $user;\n";
 153         print OUTFILE "GRANT SWG_GENERAL TO $user;\n";
 154         print OUTFILE "exit;\n";
 155         close (OUTFILE);
 156     }
 157
 158     doCreatesInDirectory("../../shared_schema/");
 159     doCreatesInDirectory("../../schema/");
 160     doCreatesInDirectory("../../login_schema/");
 161     doCreatesInDirectory("../../sp_character_schema/");
 162
 163     &updatePackages;
 164
 165     doDataSetupInDirectory("../../shared_data/");
 166     doDataSetupInDirectory("../../data/");
 167     doDataSetupInDirectory("../../login_data/");
 168     doDataSetupInDirectory("../../sp_character_data/");
 169 }
 170
 171 sub doDataSetupInDirectory
 172 {
 173     my($directory)=@_;
 174
 175     open (CREATELIST,"ls ".$directory."*.sql|");
 176     while (<CREATELIST>)
 177     {
 178         print $_;
 179         chop;
 180         system (&sqlplus." < $_ > /tmp/database_update.tmp");
 181         &checkError("/tmp/database_update.tmp");
 182     }
 183     close (CREATELIST);
 184
 185 }
 186
 187 sub doCreatesInDirectory
 188 {
 189     my($directory)=@_;
 190
 191     open (CREATELIST,"ls ".$directory."*.type|");
 192     while (<CREATELIST>)
 193     {
 194         print $_;
 195         chop;
 196         system (&sqlplus." < $_ > /tmp/database_update.tmp");
 197         &checkError("/tmp/database_update.tmp");
 198     }
 199     close (CREATELIST);
 200
 201     open (CREATELIST,"ls ".$directory."*.tab|");
 202     while (<CREATELIST>)
 203     {
 204         print $_;
 205         chop;
 206         system (&sqlplus." < $_ > /tmp/database_update.tmp");
 207         &checkError("/tmp/database_update.tmp");
 208     }
 209     close (CREATELIST);
 210
 211     open (CREATELIST,"ls ".$directory."*.view|");
 212     while (<CREATELIST>)
 213     {
 214         print $_;
 215         chop;
 216         system (&sqlplus." < $_ > /tmp/database_update.tmp");
 217         &checkError("/tmp/database_update.tmp");
 218     }
 219     close (CREATELIST);
 220
 221     open (CREATELIST,"ls ".$directory."*.seq|");
 222     while (<CREATELIST>)
 223     {
 224         print $_;
 225         chop;
 226         system (&sqlplus." < $_ > /tmp/database_update.tmp");
 227         &checkError("/tmp/database_update.tmp");
 228     }
 229     close (CREATELIST);
 230
 231     open (CREATELIST,"ls ".$directory."*.fkey|");
 232     while (<CREATELIST>)
 233     {
 234         print $_;
 235         chop;
 236         system (&sqlplus." < $_ > /tmp/database_update.tmp");
 237         &checkError("/tmp/database_update.tmp");
 238     }
 239     close (CREATELIST);
 240 }
 241
 242 # Return the sqlplus command string
 243 sub sqlplus
 244 {
 245     my ($user,$pwd,$db);
 246     $user=$::opt_username;
 247     $pwd=$::opt_password;
 248     $db=$::opt_service;
 249     $user=$ENV{"USER"} if ($user eq "");
 250     $pwd=$ENV{"DB_PASSWORD"} if ($pwd eq "");
 251     $pwd="changeme" if ($pwd eq "");
 252     $db="swodb" if ($db eq "");
 253
 254     $user =~ tr/A-Z/a-z/;
 255     if (($user eq "buildcluster") && !($::opt_buildcluster))
 256     {
 257         die "Cannot run on buildcluster unless you use the --buildcluster option\n";
 258     }
 259
 260     return "sqlplus $user/$pwd\@$db";
 261 }
 262
 263 # Return the sqlplus command string with silent option
 264 sub sqlplusSilent
 265 {
 266     my ($user,$pwd,$db);
 267     $user=$::opt_username;
 268     $pwd=$::opt_password;
 269     $db=$::opt_service;
 270     $user=$ENV{"USER"} if ($user eq "");
 271     $pwd=$ENV{"DB_PASSWORD"} if ($pwd eq "");
 272     $pwd="changeme" if ($pwd eq "");
 273     $db="swodb" if ($db eq "");
 274
 275     $user =~ tr/A-Z/a-z/;
 276     if (($user eq "buildcluster") && !($::opt_buildcluster))
 277     {
 278         die "Cannot run on buildcluster unless you use the --buildcluster option\n";
 279     }
 280
 281     return "sqlplus -s $user/$pwd\@$db";
 282 }
 283
 284
 285 # Return the sqlplus command string for the login schema
 286 sub sqlplusForLogin
 287 {
 288     my ($user,$pwd,$db);
 289     $user=$::opt_loginusername;
 290     $pwd=$::opt_password;
 291     $db=$::opt_service;
 292     $pwd=$ENV{"DB_PASSWORD"} if ($pwd eq "");
 293     $pwd="changeme" if ($pwd eq "");
 294     $db="swodb" if ($db eq "");
 295
 296     $user =~ tr/A-Z/a-z/;
 297     if (($user eq "buildcluster") && !($::opt_buildcluster))
 298     {
 299         die "Cannot run on buildcluster unless you use the --buildcluster option\n";
 300     }
 301
 302     if ($user eq "")
 303     {
 304         die "Must specify the login user name using --loginusername\n";
 305     }
 306
 307     return "sqlplus $user/$pwd\@$db";
 308 }
 309
 310 # Return the sqlplus command string for the gold schema
 311 sub sqlplusForGold
 312 {
 313     my ($user,$pwd,$db);
 314     $user=$::opt_goldusername;
 315     $pwd=$::opt_password;
 316     $db=$::opt_service;
 317     $pwd=$ENV{"DB_PASSWORD"} if ($pwd eq "");
 318     $pwd="changeme" if ($pwd eq "");
 319     $db="swodb" if ($db eq "");
 320
 321     $user =~ tr/A-Z/a-z/;
 322     if (($user eq "buildcluster") && !($::opt_buildcluster))
 323     {
 324         die "Cannot run on buildcluster unless you use the --buildcluster option\n";
 325     }
 326
 327     if ($user eq "")
 328     {
 329         die "Must specify the gold user name using --goldusername\n";
 330     }
 331
 332     return "sqlplus $user/$pwd\@$db";
 333 }
 334
 335 # Return the sqlplus command string for dba
 336 sub sqlplusForDBA
 337 {
 338     my ($user,$pwd,$db);
 339     $user=$::opt_dbausername;
 340     $pwd=$::opt_dbapassword;
 341     $db=$::opt_service;
 342     $pwd=$ENV{"DB_PASSWORD"} if ($pwd eq "");
 343     $pwd="changeme" if ($pwd eq "");
 344     $db="swodb" if ($db eq "");
 345
 346     if (!($::opt_newschema))
 347     {
 348         die "Only --newschema may use DBA!\n";
 349     }
 350
 351     if ($user eq "")
 352     {
 353         die "Must specify the dba user name using --newschema\n";
 354     }
 355
 356     return "sqlplus $user/$pwd\@$db";
 357 }
 358
 359 sub doDeltaUpdates
 360 {
 361     my($fromversion,$toversion,$noversioncheck)=@_;
 362     my($updated,$results,$error,$newversion,$version,$invalidlogin,$user,$sqlcommand);
 363     $updated=0;
 364     if ($fromversion)
 365     {
 366         $version=$fromversion;
 367     }
 368     else
 369     {
 370         $version=&getVersionNumber;
 371     }
 372
 373     while ((($toversion ne "") && ($version < $toversion)) ||
 374            (($toversion eq "") && (-e "../../updates/".($version+1).".sql")))
 375     {
 376         $updated = 1;
 377         print "Updating database to version ".($version+1)."\n";
 378         $results="";
 379         open (INFILE,&sqlplus." < ../../updates/".($version+1).".sql|");
 380         $error=0;
 381         $invalidlogin=0;
 382         while ($_=<INFILE>)
 383         {
 384             $results.=$_;
 385             $error=1 if (/ERROR/);
 386             $invalidlogin=1 if (/invalid username\/password; logon denied/);
 387         }
 388         close (INFILE);
 389
 390         if ($error==1)
 391         {
 392             print $results."\n";
 393             if ($invalidlogin = 1)
 394             {
 395                 $sqlcommand=&sqlplus;
 396                 $user=$1 if ($sqlcommand =~ /sqlplus (\w+)\//);
 397                 system("echo need to create login $user | /bin/mail -s 'create login $user' aus-db\@soe.sony.com");
 398             }
 399             open (OUTFILE,"|".&sqlplus." > /dev/null");
 400             print OUTFILE "update version_number set version_number=$version;\n";
 401             print OUTFILE "exit;\n";
 402             close (OUTFILE);
 403             die "Could not update database from version $version to version ".($version+1)."\n";
 404         }
 405
 406         if (!$noversioncheck)
 407         {
 408             $newversion=&getVersionNumber();
 409             if ($newversion != $version+1)
 410             {
 411                 die "The database reports version number $newversion, but we were expecting version number ".($version+1).".\nThe database may be corrupt, or the update script may be incorrect.\n";
 412             }
 413             $version=$newversion;
 414         }
 415     }
 416
 417     return $updated;
 418 }
 419
 420 sub updatePackages
 421 {
 422     #reload all the packages
 423
 424     updatePackagesInDirectory("../../shared_packages/");
 425     updatePackagesInDirectory("../../packages/");
 426     updatePackagesInDirectory("../../login_packages/");
 427     updatePackagesInDirectory("../../sp_character_packages/");
 428 }
 429
 430 sub updatePackagesInDirectory
 431 {
 432     my($directory)=@_;
 433     my ($user,$db,$time);
 434     $time = time();
 435     $user=$::opt_username;
 436     $db=$::opt_service;
 437     $user=$ENV{"USER"} if ($user eq "");
 438     $db="swodb" if ($db eq "");
 439
 440     #headers
 441     open (PACKAGELIST,"ls ".$directory."*.plsqlh|");
 442     while ($_=<PACKAGELIST>)
 443     {
 444         chop;
 445         print "$_\n";
 446         open (TASK,"|".&sqlplus." > /tmp/$user.$db.packages.$time.tmp");
 447         print TASK "\@".$_."\n";
 448         print TASK "exit\n";
 449         close (TASK);
 450         &checkError ("/tmp/$user.$db.packages.$time.tmp",1);
 451     }
 452     close(PACKAGELIST);
 453
 454     #package bodies
 455     open (PACKAGELIST,"ls ".$directory."*.plsql|");
 456     while ($_=<PACKAGELIST>)
 457     {
 458         chop;
 459         print "$_\n";
 460         open (TASK,"|".&sqlplus." > /tmp/$user.$db.packages.$time.tmp");
 461         print TASK "\@".$_."\n";
 462         print TASK "exit\n";
 463         close (TASK);
 464         &checkError ("/tmp/$user.$db.packages.$time.tmp",1);
 465     }
 466     close(PACKAGELIST);
 467 }
 468
 469 sub getVersionNumber
 470 {
 471     my ($version);
 472     $version = -1;
 473     open (INFILE,&sqlplus." < getversion.sql|");
 474
 475     while ($_=<INFILE>)
 476     {
 477         if (/VERSION_NUMBER/)
 478         {
 479             die unless ($_=<INFILE>);
 480             die unless ($_=<INFILE>);
 481             chop;
 482             $version = $_ + 0;
 483         }
 484     }
 485     close (INFILE);
 486
 487     die "Could not determine current version number.\n" if ($version == -1);
 488     return $version;
 489 }
 490
 491 sub checkError
 492 {
 493     my ($filename,$dontdie)=@_;
 494
 495     open (CHECKFILE,$filename);
 496     while (<CHECKFILE>)
 497     {
 498         if (/ERROR/ || /created with compilation errors/ )
 499         {
 500             print;
 501             while (<CHECKFILE>)
 502             {
 503                 last if (/Disconnected from Oracle/);
 504                 print;
 505             }
 506             close CHECKFILE;
 507             unlink("$filename");
 508             die unless $dontdie;
 509             last;
 510         }
 511     }
 512     close (CHECKFILE);
 513     unlink("$filename");
 514 }
 515
 516 sub doGrantPermissions
 517 {
 518     system (&sqlplus." @../../queries/grant_all_permissions_to_public.sql");
 519 }
 520
 521 sub doGrantGoldPermissions
 522 {
 523     system (&sqlplusForGold." @../../queries/grant_all_permissions_to_public.sql");
 524 }
 525
 526 sub doConfigureLimits
 527 {
 528     print "\nSQL/Plus will prompt for some values:\n";
 529     print "\tmax_characters_per_account is the number of characters that a single account may have, across all clusters\n";
 530     print "\tmax_characters_per_cluster is the number of characters that a single cluster may have\n";
 531     print "\tcharacter_slots is the number of characters that a single account may have on one cluster\n";
 532     system (&sqlplusForLogin." @../../queries/configure_character_limits.sql");
 533 }
 534
 535 sub doAddClusterForLogin
 536 {
 537    system (&sqlplusForLogin." @../../queries/add_cluster.sql");
 538 }
 539
 540 sub doExecQuery
 541 {
 542    my($execquery, $time, $seq);
 543
 544    #$seq = &getQuerySeq;
 545    $seq = 0;
 546    $time = time();
 547    if ($seq)
 548    {
 549      open (OUTFILE,"| ".&sqlplusSilent." > /tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 550      print OUTFILE "insert into automated_queries\n";
 551      print OUTFILE "(ID, STARTTIME, TYPE, QUERY_TEXT)\n";
 552      print OUTFILE "VALUES\n";
 553      print OUTFILE "($seq, util.unix_to_oracle_date($time,-8), 'file','$::opt_execquery');\n";
 554      print OUTFILE "commit;\n";
 555      print OUTFILE "exit;\n";
 556      close (OUTFILE);
 557      &checkError("/tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 558    }
 559    else
 560    {
 561      print "Couldn't get next sequnce number for query tracking\n";
 562    }
 563
 564    print " ../../queries/$::opt_execquery\t Started: ",scalar localtime(time()),"\n";
 565    open (TASK,"|".&sqlplus." > /tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 566    print TASK "set serveroutput on\n";
 567    print TASK "set timing on\n";
 568    print TASK "\@../../queries/$::opt_execquery\n";
 569    print TASK "exit;\n";
 570    close (TASK);
 571    system("cat /tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 572    &checkError("/tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 573
 574    if ($seq)
 575    {
 576      $time = time();
 577      open (OUTFILE,"| ".&sqlplusSilent." > /tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 578      print OUTFILE "update automated_queries\n";
 579      print OUTFILE "set ENDTIME = util.unix_to_oracle_date($time,-8),\n";
 580      print OUTFILE "status = 'finished'\n";
 581      print OUTFILE "where ID = $seq;\n";
 582      print OUTFILE "commit;\n";
 583      print OUTFILE "exit;\n";
 584      close (OUTFILE);
 585      &checkError("/tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 586      print "\n";
 587      print " ../../queries/$::opt_execquery\t Finished: ",scalar localtime(time()),"\n";
 588      print "Query text .......\n";
 589      system("cat ../../queries/$::opt_execquery");
 590
 591    }
 592    system("rm -f /tmp/execquery.$::opt_username.$::opt_service.$time.tmp");
 593
 594 }
 595
 596 sub doRunOneTimeQueries
 597 {
 598    my($row, $rowindex,$colindex, $column, @columns, @data, $time);
 599
 600    $rowindex = 0;
 601    $colindex = 0;
 602
 603    print scalar localtime(time()),": Checking for one time queries.\n";
 604
 605    open (OUTFILE,"| ".&sqlplusSilent." > /tmp/OneTimeQueries.tmp");
 606    print OUTFILE "set pagesize 0\n";
 607    print OUTFILE "set head off\n";
 608    print OUTFILE "set appi off\n";
 609    print OUTFILE "set show off\n";
 610    print OUTFILE "set sqlbl off\n";
 611    print OUTFILE "set sqln off\n";
 612    print OUTFILE "set feed off\n";
 613    print OUTFILE "select rtrim(to_char(id)) || ';' ||rtrim(to_char(util.oracle_to_unix_date(starttime,-8))) || ';' || rtrim(status) || ';' || rtrim(type) \n";
 614    print OUTFILE "|| ';' || rtrim(to_char(run_serial)) || ';' || rtrim(to_char(exec_order))  || ';' || rtrim(query_text) from automated_queries\n";
 615    print OUTFILE "where status = 'ready' order by exec_order;\n";
 616    print OUTFILE "exit;\n";
 617    close (OUTFILE);
 618
 619
 620    open(INFILE,"cat /tmp/OneTimeQueries.tmp |");
 621    while ($row = <INFILE>)
 622    {
 623      @columns = split(/;/,$row);
 624      foreach $column (@columns)
 625      {
 626        $data[$rowindex][$colindex] = $column;
 627
 628        $colindex++;
 629      }
 630
 631      #print scalar localtime(time()),": Query ID: $data[$rowindex][0] - run as $data[$rowindex][3]\n";
 632      #print scalar localtime(time()),": Query Text: $data[$rowindex][6]";
 633      if ($data[$rowindex][2] ne "ready")
 634      {
 635            print scalar localtime(time()),": Query $data[$rowindex][0] status is $data[$rowindex][2] ... query will NOT re-execute!\n";
 636      }
 637      else
 638      {
 639            $time = time();
 640            #print scalar localtime(time()),": Query $data[$rowindex][0] status is $data[$rowindex][2] starting execute!\n";
 641            open (OUTFILE,"| ".&sqlplusSilent." > /tmp/database_update.tmp");
 642            print OUTFILE "update automated_queries set starttime = util.unix_to_oracle_date($time,-8) where ID = $data[$rowindex][0];\n";
 643            print OUTFILE "commit;\n";
 644            print OUTFILE "exit;\n";
 645            close (OUTFILE);
 646            &checkError("/tmp/database_update.tmp");
 647
 648            #print scalar localtime(time()),": Query $data[$rowindex][0] will be executed serially!\n";
 649            print " ../../queries/$data[$rowindex][6]";
 650            open (TASK,"|".&sqlplus." > /tmp/database_update.tmp");
 651            print TASK "\@../../queries/$data[$rowindex][6]\n";
 652            print TASK "exit\n";
 653            close (TASK);
 654            &checkError("/tmp/database_update.tmp");
 655
 656            $time = time();
 657            open (OUTFILE,"| ".&sqlplusSilent." > /tmp/database_update.tmp");
 658            print OUTFILE "update automated_queries set endtime = util.unix_to_oracle_date($time,-8) where ID = $data[$rowindex][0];\n";
 659            print OUTFILE "commit;\n";
 660            print OUTFILE "exit;\n";
 661            close (OUTFILE);
 662            #print scalar localtime(time()),": Query $data[$rowindex][0] execution complete!\n";
 663            &checkError("/tmp/database_update.tmp");
 664
 665            open (OUTFILE,"| ".&sqlplusSilent." > /tmp/database_update.tmp");
 666            print OUTFILE "update automated_queries set status = 'finished' where ID = $data[$rowindex][0];\n";
 667            print OUTFILE "commit;\n";
 668            print OUTFILE "exit;\n";
 669            close (OUTFILE);
 670            &checkError("/tmp/database_update.tmp");
 671
 672            #print scalar localtime(time()),": Query $data[$rowindex][0] status is finished with no errors!\n";
 673
 674      }
 675
 676      $colindex = 0;
 677      $rowindex++;
 678    }
 679
 680    close (INFILE);
 681
 682    print scalar localtime(time()),": There were $rowindex item(s) processed.\n";
 683
 684 }
 685
 686 sub getQuerySeq
 687 {
 688    my ($line,$time);
 689
 690    $time = time();
 691
 692    open (OUTFILE,"| ".&sqlplusSilent." > /tmp/seq.$::opt_username.$::opt_service.$time.tmp");
 693    print OUTFILE "set pagesize 0\n";
 694    print OUTFILE "set head off\n";
 695    print OUTFILE "set appi off\n";
 696    print OUTFILE "set show off\n";
 697    print OUTFILE "set sqlbl off\n";
 698    print OUTFILE "set sqln off\n";
 699    print OUTFILE "set feed off\n";
 700    print OUTFILE "select rtrim(to_char(seq_queries.nextval)) from dual; \n";
 701    print OUTFILE "exit;\n";
 702    close (OUTFILE);
 703
 704    open(INFILE,"cat /tmp/seq.$::opt_username.$::opt_service.$time.tmp|");
 705    $line = <INFILE>;
 706    close (INFILE);
 707    $_=$line;
 708    if (/seq_queries/)
 709    {
 710         return 0;
 711    }
 712    system("rm -f /tmp/seq.$::opt_username.$::opt_service.$time.tmp");
 713    return $line;
 714
 715 }