Execute the provided SQL

Parameters

$sql_file: Contains SQL statements to be executed

Related topics

File

tripal_core/includes/tripal_core.chado_install.inc, line 575

Functions to install chado schema through Drupal

Code

function tripal_core_install_sql($sql_file) {

  $chado_local = chado_dbschema_exists('chado');

  // determine the schema name.
  $chado_schema = tripal_get_schema_name('chado');
  $chado_dot = $chado_schema . '.';

  if ($chado_local) {
    db_query("set search_path to $chado_schema");
  }
  print "Loading $sql_file...\n";
  $lines = file($sql_file, FILE_SKIP_EMPTY_LINES);

  if (!$lines) {
    return 'Cannot open $schema_file';
  }

  $stack = array();
  $in_string = 0;
  $in_function = FALSE;
  $query = '';
  $i = 0;
  $success = 1;
  foreach ($lines as $line_num => $line) {
    $i++;
    $type = '';
    // find and remove comments except when inside of strings
    if (preg_match('/--/', $line) and !$in_string and !preg_match("/'.*?--.*?'/", $line)) {
      $line = preg_replace('/--.*$/', '', $line); // remove comments
    }
    if (preg_match('/\/\*.*?\*\//', $line)) {
      $line = preg_replace('/\/\*.*?\*\//', '', $line); // remove comments
    }
    // skip empty lines
    if (preg_match('/^\s*$/', $line) or strcmp($line, '') == 0) {
      continue;
    }
    // Find SQL for new objects
    if (preg_match('/^\s*CREATE\s+TABLE/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'table';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*ALTER\s+TABLE\s+/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'alter_table';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*SET/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'set';
    }
    if (preg_match('/^\s*CREATE\s+SCHEMA/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'schema';
    }
    if (preg_match('/^\s*CREATE\s+SEQUENCE/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'sequence';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*CREATE\s+(?:OR\s+REPLACE\s+)*VIEW/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'view';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*COMMENT/i', $line) and !$in_string and sizeof($stack) == 0 and !$in_function) {
      $stack[] = 'comment';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*CREATE\s+(?:OR\s+REPLACE\s+)*FUNCTION/i', $line) and !$in_string and !$in_function) {
      $in_function = TRUE;
      $stack[] = 'function';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*CREATE\s+INDEX/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'index';
    }
    if (preg_match('/^\s*INSERT\s+INTO/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'insert';
      $line = preg_replace("/public\./", $chado_dot, $line);
    }
    if (preg_match('/^\s*CREATE\s+TYPE/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'type';
    }
    if (preg_match('/^\s*GRANT/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'grant';
    }
    if (preg_match('/^\s*CREATE\s+AGGREGATE/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'aggregate';
    }
    if (preg_match('/^\s*DROP\s+FUNCTION/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'drop_function';
    }
    if (preg_match('/^\s*DROP\s+VIEW/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'drop_view';
    }
    if (preg_match('/^\s*DROP\s+INDEX/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'drop_index';
    }
    if (preg_match('/^\s*DROP\s+SEQUENCE/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'drop_seq';
    }
    if (preg_match('/^\s*ALTER\s+TYPE\s+/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'alter_type';
    }
    if (preg_match('/^\s*ALTER\s+SEQUENCE\s+/i', $line) and !$in_string and !$in_function) {
      $stack[] = 'alter_seq';
    }

    // determine if we are in a string that spans a line
    $matches = preg_match_all("/[']/i", $line, $temp);
    $in_string = $in_string - ($matches % 2);
    $in_string = abs($in_string);

    // if we've reached the end of an object then pop the stack
    if (strcmp($stack[sizeof($stack) -1], 'table') == 0 and preg_match('/\);\s*$/', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'alter_table') == 0 and preg_match('/;\s*$/', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'set') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'schema') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'sequence') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'view') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'comment') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'function') == 0) {

      if (preg_match('/LANGUAGE.*?;\s*$/i', $line)) {
        $type = array_pop($stack);
        $in_function = FALSE;
        //print "FUNCTION DONE ($i): $line";
      }
      else if (preg_match('/\$_\$;\s*$/i', $line)) {
        $type = array_pop($stack);
        $in_function = FALSE;
        //print "FUNCTION DONE ($i): $line";
      }
      else if (preg_match('/\$\$;\s*$/i', $line)) {
        $type = array_pop($stack);
        $in_function = FALSE;
        // print "FUNCTION DONE ($i): $line";
      }
      else {
        // print "FUNCTION ($i): $line";
      }
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'index') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'insert') == 0 and preg_match('/\);\s*$/', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'type') == 0 and preg_match('/\);\s*$/', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'grant') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'aggregate') == 0 and preg_match('/\);\s*$/', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'drop_function') == 0 and preg_match('/;\s*$/i', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'drop_view') == 0 and preg_match('/;\s*$/i', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'drop_index') == 0 and preg_match("/;\s*$/i", $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'drop_seq') == 0 and preg_match("/;\s*$/i", $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'alter_type') == 0 and preg_match('/;\s*$/i', $line)) {
      $type = array_pop($stack);
    }
    elseif (strcmp($stack[sizeof($stack) -1], 'alter_seq') == 0 and preg_match('/;\s*$/i', $line)) {
      $type = array_pop($stack);
    }
    // if we're in a recognized SQL statement then let's keep track of lines
    if ($type or sizeof($stack) > 0) {
      $query .= "$line";
    }
    else {
      throw new Exception("UNHANDLED $i, $in_string: $line");
    }
    if (preg_match_all("/\n/", $query, $temp) > 1000) {
      throw new Exception("SQL query is too long.  Terminating:\n$query\n");
    }
    if ($type and sizeof($stack) == 0) {
      //print "Adding $type: line $i\n";
      // rewrite the set search_path to make 'public' be 'chado', but only if the
      // chado schema exists
      if (strcmp($type, 'set') == 0 and $chado_local) {
        $query = preg_replace("/public/m", $chado_schema, $query);
      }

      // execute the statement
      try {
        $result = db_query($query);
      }
      catch (Exception $e) {
        $error = $e->getMessage();
        throw new Exception("FAILED. Line  $i, $in_string\n$error:\n$query\n\n");
      }

      if (!$result) {
        $error = pg_last_error();
        throw new Exception("FAILED. Line  $i, $in_string\n$error:\n$query\n\n");
      }
      $query = '';
    }
  }
  tripal_core_chado_install_done();
  return $success;
}