sunders/data_handler/utils/create_camera_update_statements.php

<?php

include "config.php";

$elementTypes = array();
$count = 0;
$countDelete = 0;
$countModifyDelete = 0;
$countModify = 0;
$countCreate = 0;
$mode = '';
$curNodeAttrs = null;
$curNodeTags = null;
$id = 0;
$latitude = 0;
$longitude = 0;

$sql_statement = "BEGIN TRANSACTION;\n";

$mysqli = new mysqli(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB);

if($mysqli->connect_errno) {
  echo "Error while connecting to DB : $mysqli->error \n" ;
  exit(1);
}


if (! ($deleteStmt = $mysqli->prepare("DELETE FROM position WHERE id=?"))) {
  echo "Error while preparing delete position statement : " . $mysqli->error ;
  exit(1);
}
$deleteStmt->bind_param('d', $id);

if (! ($deleteTagStmt = $mysqli->prepare("DELETE FROM tag WHERE id=?"))) {
  echo "Error while preparing delete tag statement : " . $mysqli->error ;
  exit(1);
}
$deleteTagStmt->bind_param('d', $id);

if (USE_STATISTICS) {
  if (! ($deleteStatsStmt = $mysqli->prepare("DELETE FROM statistics WHERE id=?"))) {
    echo "Error while preparing delete statistics statement : " . $mysqli->error ;
    exit(1);
  }
  $deleteStatsStmt->bind_param('d', $id);
}

if (! ($insertStmt = $mysqli->prepare("INSERT INTO position (id, latitude, longitude) VALUES (?, ?, ?)"))) {
  echo "Error while preparing insert position statement : " . $mysqli->error ;
  exit(1);
}
$insertStmt->bind_param('dii', $id, $latitude, $longitude);

if (! ($insertTagStmt = $mysqli->prepare("INSERT INTO tag (id, k, v) VALUES (?, ?, ?)"))) {
  echo "Error while preparing insert tag statement : " . $mysqli->error ;
  exit(1);
}
$insertTagStmt->bind_param('dss', $id, $k, $v);


function startElement ($parser, $name, $attrs) {
  global $elementTypes, $count, $mode, $curNodeAttrs, $curNodeTags;
  $count++;

  if (array_key_exists($name, $elementTypes)) {
    $elementTypes[$name] += 1;
  } else {
    $elementTypes[$name] = 1;
  }

  if ($name == 'modify' || $name == 'delete' or $name == 'create') {
    $mode = $name;

  } else if ($name == 'node') {
    $curNodeAttrs = $attrs;
    $curNodeTags = array();

  } else if ($name == 'tag') {
    $curNodeTags[$attrs['k']] = $attrs['v'];
  }
}

function endElement ($parser, $name) {
  if (USE_STATISTICS) {
    global $mode, $deleteStmt, $deleteTagStmt, $deleteStatsStmt, $insertStmt, $insertTagStmt, $insertStatsStmt, $selectStmt, $id, $latitude, $longitude, $k, $v, $ts, $version, $curNodeAttrs, $curNodeTags, $mysqli, $countDelete, $countModifyDelete, $countModify, $countCreate, $sql_statement;
  } else {
    global $mode, $deleteStmt, $deleteTagStmt, $insertStmt, $insertTagStmt, $selectStmt, $id, $latitude, $longitude, $k, $v, $curNodeAttrs, $curNodeTags, $mysqli, $countDelete, $countModifyDelete, $countModify, $countCreate, $sql_statement;
  }

  if ($name == 'modify' || $name == 'delete' or $name == 'create') {
    $mode = '';
  } else if ($name == 'node') {
    if ($mode == 'delete') {
      $id = $curNodeAttrs['id'];

      if (! $deleteTagStmt->execute()) {
        echo "***** Error : Deleting tags $id : ". $deleteTagStmt->error . "\n";
      }
      
      if (! $deleteStmt->execute()) {
        echo "***** Error : Deleting $id : ". $deleteStmt->error . "\n";
      }
      if ($deleteStmt->affected_rows > 0) {
         if (! $deleteTagStmt->execute()) {
        echo "***** Error : Deleting tags $id : ". $deleteTagStmt->error . "\n";
      }
      
      if (! $deleteStmt->execute()) {
        echo "***** Error : Deleting $id : ". $deleteStmt->error . "\n";
      }
        $sql_statement = $sql_statement . "DELETE FROM position WHERE id=$id;\n";
        $sql_statement = $sql_statement . "DELETE FROM tag WHERE id=$id;\n";
      }

    } else if ($mode == 'modify' || $mode == 'create') {
      if (! empty($curNodeTags)
          && array_key_exists('man_made', $curNodeTags)
          && $curNodeTags['man_made'] == 'surveillance') {

        

        $id = $curNodeAttrs['id'];

        if ($mode == 'modify') {
          if (! $deleteTagStmt->execute()) {
            echo "***** Error : Deleting tags $id for modification : ". $deleteTagStmt->error . "\n";
          }
          if (! $deleteStmt->execute()) {
            echo "***** Error : Deleting $id for modification : ". $deleteStmt->error . "\n";
          }
          $sql_statement = $sql_statement . "DELETE FROM position WHERE id=$id;\n";
          $sql_statement = $sql_statement . "DELETE FROM tag WHERE id=$id;\n";
        } else {
        }

        $latitude = (int) ($curNodeAttrs['lat'] * 10000000);
        $longitude = (int) ($curNodeAttrs['lon'] * 10000000);
        $sql_statement = $sql_statement . "INSERT INTO position (id, latitude, longitude) VALUES ($id, $latitude , $longitude);\n";
        

        $k = 'lat';
        $v = $curNodeAttrs['lat'];
        $sql_statement = $sql_statement . "INSERT INTO tag (id, k, v) VALUES ($id, '$k', '$v');\n";
        

        $k = 'lon';
        $v = $curNodeAttrs['lon'];
        $sql_statement = $sql_statement . "INSERT INTO tag (id, k, v) VALUES ($id, '$k', '$v');\n";


        $k = 'userid';
        $v = $curNodeAttrs['user'];
        $sql_statement = $sql_statement . "INSERT INTO tag (id, k, v) VALUES ($id, '$k', '$v');\n";


        $k = 'version';
        $v = $curNodeAttrs['version'];
        $sql_statement = $sql_statement . "INSERT INTO tag (id, k, v) VALUES ($id, '$k', '$v');\n";
       


        $k = 'timestamp';
        $v = $curNodeAttrs['timestamp'];
        $sql_statement = $sql_statement . "INSERT INTO tag (id, k, v) VALUES ($id, '$k', '$v');\n";

        foreach($curNodeTags as $k => $v) {
        $sql_statement = $sql_statement . "INSERT INTO tag (id, k, v) VALUES ($id, '$k', '$v');\n";
          
        }
      } else if ($mode == 'modify') {
        // delete former surveillance nodes that were modified to non-surveillance nodes
        $id = $curNodeAttrs['id'];

        if (! $deleteTagStmt->execute()) {
          echo "***** Error : Deleting tags $id for non-surveillance node : ". $deleteTagStmt->error . "\n";
        }
        if (! $deleteStmt->execute()) {
          echo "***** Error : Deleting $id for non-surveillance node : ". $deleteStmt->error . "\n";
        }

        if ($deleteStmt->affected_rows > 0) {
          $sql_statement = $sql_statement . "DELETE FROM position WHERE id=$id;\n";
          $sql_statement = $sql_statement . "DELETE FROM tag WHERE id=$id;\n";
        }
      }
    }

    $curNodeAttrs = null;
    $curNodeTags = null;
  }
}

$file="change_file.osc";

$xml_parser = xml_parser_create();
xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, false);
xml_set_element_handler($xml_parser, "startElement", "endElement");

if (!($fp = fopen($file, "r"))) {
  die("could not open XML input");
}

while ($data = fread($fp, 4096)) {
  if (!xml_parse($xml_parser, $data, feof($fp))) {
    die(sprintf("XML error: %s at line %d",
                xml_error_string(xml_get_error_code($xml_parser)),
                xml_get_current_line_number($xml_parser)));
  }
}

$sql="UPDATE sync_state SET v='" . $_SERVER['argv'][1] ."' WHERE k='sequenceNumber';";
$mysqli->query($sql);
$sql_statement = $sql_statement . "UPDATE sync_state SET v='" . $_SERVER['argv'][1] ."' WHERE k='sequenceNumber';\n";
$sql_statement = $sql_statement . "COMMIT;";
$sql_file = "/migrations/".$_SERVER['argv'][1].".sql";

file_put_contents($sql_file, $sql_statement);
$mysqli->close();

?>