Knowledge Base Nr: 00254 dbadmin.txt - http://www.swe-kaiser.de

Downloads:

php: oracle: jobs, trigger und prozeduren/funktionen auslesen (z.b. für backups)

  
  global $log;
  global $dbuser;
  global $dbdns;

function compareDumps()
{
  global $log;

  $dir_diff = strftime("./dump_%d_%m_%Y");   #nur dumps von geänderten sourcen
  $dir_act = "./dump";              #aktueller stand aller sourcen
  $dir_temp = "./dump_temp";        #temp. dumps

  if (!file_exists($dir_diff))
    mkdir($dir_diff);

  $changedFiles = 0;
  $dh_act = opendir($dir_act);
  $dh_temp = opendir($dir_temp);
  while ($file = readdir($dh_temp))
  {
      if (!file_exists("$dir_act/$file")) #neue source?
      {
        $changedFiles++;
        $log .= "Neue Funktion/Prozedur ($file)!<br>\n";

        if (!copy("$dir_temp/$file", "$dir_act/$file"))
          echo("<h1>ERROR: failed to copy $dir_temp/$file to $dir_act/$file!</h1>\n");
        if (!copy("$dir_temp/$file", "$dir_diff/$file"))
          echo("<h1>ERROR: failed to copy $dir_temp/$file to $dir_diff/$file!</h1>\n");
      }
      else
      {
        $new = file_get_contents("$dir_temp/$file");
        $old = file_get_contents("$dir_act/$file");
        if ($new != $old)
        {
          $changedFiles++;
          $log .= "Geänderte Funktion/Prozedur ($file)!<br>\n";

          if (!copy("$dir_temp/$file", "$dir_act/$file"))
            echo("<h1>ERROR: failed to copy $dir_temp/$file to $dir_act/$file!</h1>\n");
          if (!copy("$dir_temp/$file", "$dir_diff/$file"))
            echo("<h1>ERROR: failed to copy $dir_temp/$file to $dir_diff/$file!</h1>\n");
        }
      }
  }
  closedir($dh_act);
  closedir($dh_temp);

  if ($changedFiles == 0)
    rmdir($dir_diff);
}

function dumpTriggers()
{
  global $g_conn;
  global $dbsysusers;

  $select = "select OWNER,TRIGGER_NAME,trigger_body,DESCRIPTION from dba_triggers"
           ." where owner not in $dbsysusers"
           ." order by OWNER,TRIGGER_NAME";

#echo("<h1>$select</h1>");

  $stid3 = OCIParse($g_conn, $select);
  OCIExecute($stid3);
  OCISetPrefetch($stid3, 100);

  $dir = strftime("./dump_temp");
  if (!file_exists($dir))
    mkdir($dir);

  while ($succ = OCIFetchInto($stid3, $row2, OCI_RETURN_NULLS))
  {
    $filename = strftime("$dir/trigger.$row2[0].$row2[1].txt");
    $fp = fopen ($filename, "w+t");
    fputs($fp, "CREATE OR REPLACE TRIGGER $row2[3]");
    fputs($fp, $row2[2]);
    fclose($fp);
  }
}

function dumpProc($user, $procname)
{
  global $g_conn;
  global $dbsysusers;

  $select = "select text from all_source"
           ." where owner not in $dbsysusers"
           ." and owner = '$user' and name = '$procname'"
           ." order by name,type,line";

#echo("<h1>$select</h1>");

  $stid2 = OCIParse($g_conn, $select);
  OCIExecute($stid2);
  OCISetPrefetch($stid2, 100);

  $dir = "./dump_temp";
  $filename = strftime("$dir/$user.$procname.txt");
  $fp = fopen ($filename, "w+t");
  fputs($fp, "CREATE OR REPLACE ");
  while ($succ = OCIFetchInto($stid2, $row2, OCI_RETURN_NULLS))
  {
    fputs($fp, $row2[0]);
  }
  fclose($fp);
}

function fillDumpTriggerProcsTable($header, $select, $dump)
{
  global $g_conn;
  global $log;

#echo("<h1>$select</h1>");

  $dir = "./dump_temp";
  @unlink("$dir/*");
  @mkdir($dir);

  dumpTriggers();

  $result = '<table border="1">';

  $headerinfo = split("###", $header);
  $result .= "<tr>\n";
  foreach ($headerinfo as $item)
  {
    $result .= "<th>$item</th>\n";
  }
  $result .= "</tr>\n";

  $stid = OCIParse($g_conn, $select);
  OCIExecute($stid);
  OCISetPrefetch($stid, 100);

  while ($succ = OCIFetchInto($stid, $row, OCI_RETURN_NULLS))
  {
    $coloron = "";
    $coloroff = "";
    if ($row[2] == "INVALID")
    {
        $log .= "Funktion/Prozedur ungültig ($row[0].$row[1])!<br>\n";
        $coloron = "<font color=#ff0000><b>";
        $coloroff = "</b></font>";
    }

    $result .= "<tr><td>$coloron$row[0]$coloroff</td><td>$coloron$row[1]$coloroff</td><td>$coloron$row[2]$coloroff</td></tr>\n";

    #dump into file
    if ($dump)
      dumpProc($row[0], $row[1]);
  }

  if ($dump)
    compareDumps();

  $result .= "</table>";
  return $result;
}

##################### MAIN:

  connectDB();

  $job = $_REQUEST["job"];
  $trigger = $_REQUEST["trigger"];
  $user = $_REQUEST["user"];
  $proc = $_REQUEST["proc"];

  if ($_SERVER["REQUEST_URI"]) #aufruf von browser oder script?
    $dump = $_REQUEST["dump"];
  else
    $dump = 1;

  $jobs = fillTable("Job###User###Interval###Aktion"
                   ,"select job,LOG_USER,INTERVAL,what from dba_jobs"
                   ." order by job");

  $triggers = fillTable("Trigger###User###Status"
                       ,"select TRIGGER_NAME,OWNER,STATUS from dba_triggers"
                       ." where owner not in $dbsysusers");

  $users = "<option></option>";
  $users .= fillCombobox("select distinct username from all_users"
                        ." where username not in $dbsysusers"
                        ." order by username", $user);

  if ($user)
    $where = " and abc.owner = '$user'";
  else
    $where = " and abc.owner not in $dbsysusers";

  $log = "";
  $procs = fillDumpTriggerProcsTable("User###Name###Status"
                    ,"select distinct all_source.owner,name,status from all_source,"
                    ." (select object_name,status,owner from all_objects) abc"
                    ." where all_source.owner not in $dbsysusers"
                    ." and name = abc.object_name and abc.owner = all_source.owner"
                    ." $where"
                    ." order by owner,name"
                    , $dump);

  closeDB();