oracle2sqlite.php

<?php

use Isse\Db\SetUp;
require_once __DIR__ . '/../bootstrap/execsh.php';

$host = DB_HOST;
$db = DB_NAME;
$oracle_pdo = new PDO("oci:dbname=$host/$db", DB_USER, DB_PWD);
$oracle_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$oracle = SetUp::getEM()->getConnection();
$sm = $oracle->getSchemaManager();

$sqlite = new PDO('sqlite:isse2.sqlite');
$sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$tables = $sm->listTableNames();
$tables = array_map('strtolower', $tables);
sort($tables);
$grandes = array('doc_dados', 'emi', 'protocolos_registros', 'logs', 'sessions',
                 'auto_his', 'autos', 'autos_obs', 'mensagens', 'os_obs', 'web_dicas'); // data was too large for buffer and was truncated
$tables = array_diff($tables, $grandes);
//$tables = array('protocolos_registros');

foreach ($tables as $table) {
  echo $table.PHP_EOL;

  // Criar Tabela
  $columns = $sm->listTableColumns($table);
  $columnNames = array();
  foreach ($columns as $column) {
    $columnNames[] = $column->getName();
  }
  $columns = implode(', ', $columnNames);
  $sql = "CREATE TABLE IF NOT EXISTS $table ($columns)";
  $sqlite->query($sql);

  // Falta fazer resume
  $values = array_fill(0, count($columnNames), '?');
  $values = implode(', ', $values);
  $stmt = $sqlite->prepare("INSERT INTO $table ($columns) VALUES ($values)");

  $oracle_stmt = $oracle_pdo->prepare("SELECT $columns FROM $table");
  $oracle_stmt->execute();
  $oracle_stmt->setFetchMode(PDO::FETCH_NUM);

  $sqlite->exec("BEGIN");
  $i = 1;
  foreach ($oracle_stmt as $row) {
    if ($i % 10000 == 0) printf("\r\t%dK     \r", $i/1000);
    try {
        $stmt->execute(array_values($row));
    } catch (Exception $e) {
      print_r($row);
      print_r($columns);
      print_r($values);
      exit;
    }
    $i++;
  }
  $sqlite->exec("COMMIT");
}