Web SQL Database API for PHP

websql.php

<?php

/* usage:

$db = new Database("mysql:host=" . Config::$dbHost . ";dbname=" . Config::$dbName . ";", Config::$dbName, Config::$dbUser, Config::$dbPass);
$db->changeVersion("", "1", function ($tx) {
  $tx->executeSql("CREATE TABLE IF NOT EXISTS `examples` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `example` mediumtext,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=121 ;");
}, function ($error) {
  if ($error->getCode() !== SQLError::VERSION_ERR) {
    throw $error;
  }
});
$db->transaction(function ($tx) {
  $id = 1;
  $tx->executeSql("SELECT * FROM `examples` WHERE `id` = ?", array($id), function ($tx, $result) {
    foreach ($result->rows as $row) {
      echo $row->id;
      echo ":";
      echo $row->example;
      echo "\n";
    }
  });
});
*/

class SQLTransaction {
  private $db = null;
  private $queue = null;
  public $state = 0;
  public function __construct($db, $queue) {
    $this->db = $db;
    $this->queue = $queue;
  }
  public function executeSql($sqlStatement, $args = null, $successCallback = null, $errorCallback = null) {
    // $sqlStatement should not contain COMMIT, ROLLBACK, CREATE, ...
    if ($this->state !== 0) {
      throw new Exception();
    }
    $statement = $this->db->prepare($sqlStatement);
    if ($args !== null) {
      foreach ($args as $index => $v) {
        $statement->bindValue($index + 1, $v);
      }
    }
    $x = new SQLTransactionQueueItem();
    $x->statement = $statement;
    $x->successCallback = $successCallback;
    $x->errorCallback = $errorCallback;
    $this->queue->enqueue($x);
  }
}

class SQLTransactionQueueItem {
  public $statement = null;
  public $successCallback = null;
  public $errorCallback = null;
}

class SQLError extends Exception {
  public const UNKNOWN_ERR = 0;
  public const DATABASE_ERR = 1;
  public const VERSION_ERR = 2;
  public const TOO_LARGE_ERR = 3;
  public const QUOTA_ERR = 4;
  public const SYNTAX_ERR = 5;
  public const CONSTRAINT_ERR = 6;
  public const TIMEOUT_ERR = 7;
  public function __construct($message, $code = 0, Exception $previous = null) {
    parent::__construct($message, $code, $previous);
  }
}

class SQLResultSetRowListIterator implements Iterator {
  private $rowList = null;
  private $position = 0;
  public function __construct($rowList) {
    $this->rowList = $rowList;
  }
  public function rewind() {
  }
  public function current() {
    return $this->last;
  }
  public function key() {
    return $this->position;
  }
  public function next() {
    ++$this->position;
  }
  public function valid() {
    $this->last = $this->rowList->item($this->position);
    return $this->last !== null;
  }
}

class SQLResultSetRowList implements IteratorAggregate {
  private $statement = null;
  private $lastIndex = -1;
  public function __construct($statement) {
    $this->statement = $statement;
  }
  public function item($index) {
    if ($index !== $this->lastIndex + 1) {
      throw new Exception("please only call item method for an index greater than previous one by one");
    }
    $c = $this->statement->fetchObject();
    if ($c !== false) {
      $this->lastIndex = $index;
    }
    return $c !== false ? $c : null;
  }
  public function getIterator() {
    return new SQLResultSetRowListIterator($this);
  }
}

class SQLResultSet {
  public $insertId = 0;
  public $rowsAffected = 0;
  public $rows = null;
  public function __construct($insertId, $rowsAffected, $rows) {
    $this->insertId = $insertId;
    $this->rowsAffected = $rowsAffected;
    $this->rows = $rows;
  }
}

class Database {
  private $dbName = null;
  private $db = null;
  private $expectedVersion = "";
  private $informationTableName = "webdatabase_information";
  public function __construct($dsn, $dbName, $user, $password) {
    $db = new PDO($dsn, $user, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES utf8");
    $db->exec("CREATE TABLE IF NOT EXISTS `" . $this->informationTableName .  "` (`key` VARCHAR(255), `value` VARCHAR(255), PRIMARY KEY (`key`))");
    $this->dbName = $dbName;
    $this->db = $db;
    $this->expectedVersion = "";
  }
  public function readTransaction($callback, $errorCallback = null, $successCallback = null) {
    $this->transactionSteps($callback, $errorCallback, $successCallback, null, null);
  }
  public function transaction($callback, $errorCallback = null, $successCallback = null) {
    $this->transactionSteps($callback, $errorCallback, $successCallback, null, null);
  }
  public function changeVersion($oldVersion, $newVersion, $callback, $errorCallback = null, $successCallback = null) {
    $this->transactionSteps($callback, $errorCallback, $successCallback, $oldVersion, $newVersion);
  }
  private function transactionSteps($callback, $errorCallback, $successCallback, $oldVersion, $newVersion) {
    $db = $this->db;
    $expectedVersion = $this->expectedVersion;

    $queue = new SplQueue();
    $db->beginTransaction();
    $tx = new SQLTransaction($db, $queue);
    $error = null;

    $currentVersion = "";
    $s = $db->prepare("SELECT value FROM `" . $this->informationTableName . "` WHERE `key` = ?");
    $s->bindValue(1, $this->dbName);
    $s->execute();
    $c = $s->fetchObject();
    if ($c !== false) {
      $currentVersion = $c->value;
    }
    if ($oldVersion !== null && $newVersion !== null) {
      if ($oldVersion !== $currentVersion) {
        $error = new SQLError("", SQLError::VERSION_ERR);
      }
    }

    if ($expectedVersion !== "" && $expectedVersion !== $currentVersion) {
      $error = new SQLError("", SQLError::VERSION_ERR);
    }

    if ($error === null) {
      if ($callback !== null) {
        $callback($tx);
      }

      $flag = true;
      while ($flag && !$queue->isEmpty()) {
        $x = $queue->dequeue();
        try {
          $x->statement->execute();
        } catch (PDOException $e) {
          $code = $e->getCode();
          $c = SQLError::DATABASE_ERR;
          if ($code === "23000") {
            $c = SQLError::CONSTRAINT_ERR;
          }
          if ($code === "2A000" || $code === "37000" || $code === "42000") {
            $c = SQLError::SYNTAX_ERR;
          }
          $error = new SQLError($e->getMessage(), $c);
        }
        if ($error === null) {
          if ($x->successCallback !== null) {
            $insertId = $db->lastInsertId();
            $rowsAffected = $x->statement->rowCount();
            $rowList = new SQLResultSetRowList($x->statement);
            $resultSet = new SQLResultSet($insertId, $rowsAffected, $rowList);
            $z = $x->successCallback;
            $z($tx, $resultSet);
          }
        }
        if ($error !== null) {
          if ($x->errorCallback !== null) {
            $z = $x->errorCallback;
            $flag = $z($tx, $error) === false;
          } else {
            $flag = false;
          }
        }
      }

      $tx->state = 1;

      if ($error === null) {

        if ($oldVersion !== null && $newVersion !== null) {
          $s = $db->prepare("REPLACE INTO `" . $this->informationTableName . "`(`key`, `value`) VALUES (?, ?)");
          $s->bindValue(1, $this->dbName);
          $s->bindValue(2, $newVersion);
          $s->execute();
          $expectedVersion = $newVersion;
        }
   
        $db->commit();
        
        if ($successCallback !== null) {
          $successCallback();
        }

      }
    }

    if ($error !== null) {
      $db->rollBack();
      if ($errorCallback !== null) {
        $errorCallback($error);
      } else {
        throw $error;//?
      }
    }
  }
}

?>

Hi Yaffle,

Thanks for the API. I have just one question, is the API for MySQL database or WebSQL database?

@tokelor,
it is a wrapper around PDO, it can be useful only if you want to write a code in PHP similar to client-side javascript.