/system/libraries/pdosqlite3.lib.php
PHP | 415 lines | 346 code | 13 blank | 56 comment | 15 complexity | 91e9cb548dcf10d1c3a4008a1ee5d9c5 MD5 | raw file
- <?php
- /**
- * pdosqlite3 class
- *
- * This class applies the PDO class to the SQLite3 database
- * type. PDO has a design weakness under normal use, where
- * its internal design uses a PDO object and a PDOStatement
- * object. If you used this class as is, your database
- * operations would be needlessly complex because of this.
- * So this class acts as a cover over the PDO classes, to make
- * the programmer's life easier.
- *
- */
- class pdosqlite3
- {
- var $handle, $dd = array();
- function __construct($dsn)
- {
- $parms = explode(':', $dsn);
- if (count($parms) === 1) {
- die('Configuration error: No database file specified.');
- }
- $filename = $parms[1];
- $this->db_status = file_exists($filename) ? TRUE : FALSE;
- try {
- $this->handle = new PDO($dsn);
- }
- catch (PDOException $e) {
- die($e->getMessage());
- // $this->db_status = FALSE;
- }
- }
- /**
- * Report status of database.
- *
- * Returns FALSE if database is missing or there was some problem in
- * opening it.
- *
- * @return bool
- */
- function status()
- {
- return $this->db_status;
- }
- function fatal($function, $sql)
- {
- error_log("DATE: " . date('c') . "\n", 3, 'error.log');
- error_log("ERROR: Failure in $function:\n", 3, 'error.log');
- error_log("SQL: $sql\n", 3, 'error.log');
- $pdo_message = var_export($this->handle->errorInfo()[2], TRUE);
- error_log("PDO error: $pdo_message\n", 3, 'error.log');
- $msg = "\nTRACE:\n--------------------------\n";
- $backtrace = debug_backtrace();
- foreach ($backtrace as $key => $trace) {
- if ($key != 0) {
- $msg .= "#$key ";
- if (array_key_exists('file', $trace)) {
- $msg .= $trace['file'];
- }
- if (array_key_exists('line', $trace)) {
- $msg .= ":$trace[line]";
- }
- if (array_key_exists('class', $trace)) {
- $msg .= ":$trace[class]";
- }
- if (array_key_exists('type', $trace)) {
- $msg .= " $trace[type] " ; // ->, ::, or nothing
- }
- if (array_key_exists('function', $trace)) {
- $msg .= " $trace[function](";
- if (array_key_exists('args', $trace)) {
- $first = true;
- foreach ($trace['args'] as $arg) {
- if (!$first) {
- $msg .= ', ';
- }
- if (is_object($arg))
- $msg .= get_class($arg) . ' OBJECT ';
- else {
- if (is_string($arg)) {
- $msg .= $arg;
- }
- else {
- $msg .= 'TYPE: ' . gettype($arg);
- }
- }
- if ($first)
- $first = false;
- }
- }
- $msg .= ')';
- }
- $msg .= "\n";
- }
- }
- $msg .= "\n";
- error_log($msg, 3, 'error.log');
- error_log("-\n", 3, 'error.log');
- die('FATAL database error, aborting. See error log.');
- }
-
- /**
- * Gather a data dictionary from the SQLite3 database.
- *
- * Because SQLite does not implement INFORMATION_SCHEMA, we have to
- * find some other way to obtain metadata about tables and columns.
- * This code is based on a StackOverflow answer.
- *
- * NOTE: I learned the hard way-- the pragma_table_info() and
- * similar functions were added in SQLite version 3.16.0. If PHP's
- * version of SQLite is less than this, the query will fail with
- * inscrutable errors. Run phpinfo() to determine your version of
- * PHP and the SQLite3 module.
- *
- * result table looks like this:
- *
- * table_name|column_name|column_type|is_nullable|column_default|pkey
- *
- * This routine is only called by self::prepare().
- *
- * @return array Array of column descriptions
- *
- */
- function datadict()
- {
- $sql = "SELECT m.name AS table_name, p.name AS column_name, p.type AS column_type, not `notnull` AS is_nullable, p.dflt_value AS column_default, p.pk AS pkey FROM sqlite_master AS m JOIN pragma_table_info(m.name) AS p WHERE m.type = 'table' ORDER BY m.name, p.cid";
- $this->dd = $this->query($sql)->fetch_all();
- }
- /**
- * Prettifies string values for use in queries.
- *
- * Adds single quotes before and after string values,
- * turns internal single quotes into double single quotes
- *
- * NOTE: This is a static function
- *
- * @param string $value The value to be quoted
- *
- * @return string The quoted value
- *
- */
- static public function quote($value)
- {
- $quoted = str_replace("'", "''", $value);
- return "'" . $quoted . "'";
- }
- /**
- * Discard fields in POST array not germane to a given table.
- *
- * Currently, this function discards all fields not part of the
- * table in question, and leaves only the fields which are actually
- * part of the table.
- *
- * Typically, for functions like insert(), the function is fed the
- * whole $_POST array. This function strips items from the array
- * which might be buttons or somesuch, and leaves only appropriate
- * fields.
- *
- * @param string $table which table in the $dd?
- * @param array $rec Associative array ['field_name' => field_value, * ...]
- *
- * @return array An array with appropriately "repaired" values
- *
- */
- function prepare($table, $rec)
- {
- if (empty($this->dd)) {
- $this->datadict();
- }
- $prepped = array();
- foreach ($this->dd as $column) {
- if ($column['table_name'] != $table) {
- continue;
- }
- $column_name = $column['column_name'];
- if (isset($rec[$column_name])) {
- $prepped[$column_name] = $rec[$column_name];
- }
- }
- return $prepped;
- }
- function begin_transaction()
- {
- $this->handle->beginTransaction();
- }
- function begin()
- {
- $this->handle->beginTransaction();
- }
- /**
- * Execute any sql statement
- *
- * NOTE: You may chain other routines from here.
- *
- * @param string $sql The SQL statement
- *
- * @return reference this object
- */
- function query($sql)
- {
- $this->result = $this->handle->query($sql);
- // this only happens on things like a query to a non-existent
- // table, or a query of a non-existent field
- if ($this->result === FALSE) {
- $this->fatal('PDO::query()', $sql);
- }
- return $this;
- }
- /**
- * Fetch a single record.
- *
- * @return array A single row of query results or FALSE if no rows
- *
- */
- function fetch()
- {
- // PDOStatement::fetch() returns FALSE on no results
- return $this->result->fetch(PDO::FETCH_ASSOC);
- }
- /**
- * Fetches an indexed/associative array of all results from a query
- *
- * @return array Indexed/associative array of results
- *
- * @return array Indexed array of records returned from prior query
- *
- */
- function fetch_all()
- {
- // PDOStatement::fetchAll returns an empty array on no results
- $recs = $this->result->fetchAll(PDO::FETCH_ASSOC);
- // change return to FALSE for consistency with fetch()
- return empty($recs) ? FALSE : $recs;
- }
- /**
- * Fetches the index number for the last record stored
- *
- * NOTE: This function does not deal with concurrency issues
- *
- * @param string $table Name of table to query
- *
- * @return integer Last ID
- *
- */
- function lastid($table)
- {
- $sql = "SELECT seq FROM sqlite_sequence WHERE name = '$table'";
- $seq_rec = $this->query($sql)->fetch();
- return ($seq_rec['seq']);
- }
- /**
- * Insert a record into a table.
- *
- * Special case of query(), where the input is, instead of a SQL
- * statement, an associative array of fieldnames and values.
- *
- * For string and some other data types, values must be quoted, or
- * SQLite3 will generate an exception.
- *
- * @param string $table The table to be inserted into
- * @param array $records The indexed/associated table of
- * fieldnames/values.
- *
- */
- function insert($table, $record)
- {
- if (empty($table)) {
- $this->fatal('Call to database::insert() with no table name', '--');
- }
- if (empty($record)) {
- $this->fatal('Call to database::insert() with no field data', '--');
- }
- $fields = array_keys($record);
- $fieldnames = implode(', ', $fields);
- $pms = array();
- foreach($record as $field => $value) {
- $pms[] = '?';
- }
- $placemarkers = implode(', ', $pms);
- $values = array_values($record);
- $sql = "INSERT INTO $table ($fieldnames) VALUES ($placemarkers)";
- $this->result = $this->handle->prepare($sql);
- if ($this->result === FALSE) {
- $this->fatal('PDO::prepare()', $sql);
- }
- $this->result->execute($values);
- }
- /**
- * Update a record in a table.
- *
- * Implements the UPDATE statement.
- *
- * NOTE: If a field should be quoted, ensure you do it beforehand.
- *
- * @param string $table Table name
- * @param array Associative array of fields and values
- * @param string Where clause
- */
- function update($table, $record, $where_clause)
- {
- if (empty($table))
- $this->fatal('Call to database::update() with no table name', '--');
- if (empty($record))
- $this->fatal('Call to database::update() with no field data', '--');
- if (empty($where_clause))
- $this->fatal('Call to database::update() with no where clause', '--');
- $fields = array_keys($record);
- $values = array_values($record);
- $max_terms = count($fields);
- for ($i = 0; $i < $max_terms; $i++) {
- $str = $fields[$i] . ' = ?';
- $terms[] = $str;
- }
- $fields_clause = implode(', ', $terms);
- $sql = "UPDATE $table SET $fields_clause WHERE $where_clause";
- $this->result = $this->handle->prepare($sql);
- if ($this->result === FALSE) {
- $this->fatal('PDO::prepare()', $sql);
- }
- $this->result->execute($values);
- }
- /**
- * Delete a record from a table.
- *
- * Simplified implementation of SQL DELETE command
- *
- * @param string $table The table
- * @param string $where_clause The WHERE clause
- */
- function delete($table, $where_clause = NULL)
- {
- if (empty($table)) {
- $this->dbh->fatal('Call to database::delete() with no table name', '--');
- }
- if (!is_null($where_clause)) {
- $sql = "DELETE FROM $table WHERE $where_clause";
- }
- else {
- $sql = "DELETE FROM $table";
- }
- $this->query($sql);
- }
- function commit()
- {
- $this->handle->commit();
- }
- function end()
- {
- $this->handle->commit();
- }
- function rollback()
- {
- $this->handle->rollBack();
- }
- function version()
- {
- return 2.5;
- }
- }