akoimeexx

Small PDO->SQLITE class wrapper

Sep 3rd, 2012
316
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 4.67 KB | None | 0 0
  1. <?php
  2.     /**
  3.      * Abstract class to keep track of and provide static access to
  4.      * DB datatypes. Only real convenient const is SQLITE_PK,
  5.      * however this can be expanded on. Used more as a reference.
  6.      */
  7.     abstract class DB_DATATYPES {
  8.         const SQLITE_NULL = "NULL";
  9.         const SQLITE_PK = "INTEGER PRIMARY KEY";
  10.         const SQLITE_INTEGER = "INTEGER";
  11.         const SQLITE_REAL = "REAL";
  12.         const SQLITE_TEXT = "TEXT";
  13.         const SQLITE_BLOB = "BLOB";
  14.     }
  15.    
  16.     /**
  17.      * Wrapper class for PDO->SQLITE connections.
  18.      *     provides CRUD functions for recordsets as well as
  19.      *     tables.
  20.      *     Also included is a wrapper function to send raw query
  21.      *     strings directly to the PDO object.
  22.      *
  23.      *     Caveat: No string escapes are done in the class. Sanitize
  24.      *             yo shit.
  25.      */
  26.     class PDO_SQLITE {
  27.         // Database object, accessible only through the class
  28.         protected $db_hwd = null;
  29.        
  30.         /**
  31.          * Constructor/destructor functions. If we fail at
  32.          * opening a database connection, destroy this object.
  33.          */
  34.         function __construct($filename) {
  35.             try { $this->db_hwd = new PDO("sqlite:$filename"); }
  36.             catch (PDOException $e) { $this->db_hwd = null; }
  37.            
  38.             if($this->db_hwd === null) {
  39.                 $this->__destruct();
  40.             }
  41.         }
  42.         public function __destruct() {
  43.             unset($this->db_hwd);
  44.             unset($this);
  45.         }
  46.        
  47.         /**
  48.          * CRUD function block for recordsets.
  49.          *     create(): INSERT INTO a table.
  50.          *     read(): SELECT * FROM a table.
  51.          *     update(): UPDATE a table recordset.
  52.          *     delete(): DELETE FROM a table.
  53.          */
  54.         public function create($table, array $record) {
  55.             foreach($record as $field_name=>$value) {
  56.                 $implodable_fields[] = "'" . $field_name . "'";
  57.                 $implodable_values[] = "'" . $value . "'";
  58.             }
  59.             $q = "INSERT INTO $table (" . implode(", ", $implodable_fields) . ") VALUES (" . implode(", ", $implodable_values) . ");";
  60.             return $this->db_hwd->exec($q);
  61.         }
  62.         public function read($table, array $needles = null) {
  63.             $q = "SELECT * FROM $table;";
  64.             if(is_array($needles) && count($needles) > 0) {
  65.                 foreach($needles as $needle=>$value) {
  66.                     $implodable[] = "$needle='$value'";
  67.                 }
  68.                 $q = "SELECT * FROM $table WHERE " . implode(" AND ", $implodable) . ";";
  69.             }
  70.             return $this->db_hwd->query($q);
  71.         }
  72.         public function update($table, $record, $needles, $limit = 1) {
  73.             foreach($record as $field_name=>$value) {
  74.                 $implodable_record[] = "$field_name='$value'";
  75.             }
  76.             foreach($needles as $needle=>$value) {
  77.                 $implodable_needles[] = "$needle='$value'";
  78.             }
  79.             $q = "UPDATE $table SET " . implode(", ", $implodable_record) . " WHERE " . implode(" AND ", $implodable_needles) . " LIMIT $limit;";
  80.             return $this->db_hwd->exec($q);
  81.         }
  82.         public function delete($table, $needles, $limit = 1) {
  83.             foreach($needles as $needle=>$value) {
  84.                 $implodable_needles[] = "$needle='$value'";
  85.             }
  86.             $q = "DELETE FROM $table WHERE " . implode(" AND ", $implodable_needles) . " LIMIT $limit";
  87.             return $this->db_hwd->exec($q);
  88.         }
  89.  
  90.         /**
  91.          * CRUD function block for tables.
  92.          *     table_create(): CREATE TABLE a new table.
  93.          *     table_read(): SELECT * FROM sqlite_master to
  94.          *                   return the table structure in
  95.          *                   CREATE TABLE sql statement format.
  96.          *     table_update(): ALTER TABLE to add a new column.
  97.          *     table_delete(): Little Bobby Drop Tables,
  98.          *                     we call him.
  99.          */
  100.         public function table_create($table, array $fields) {
  101.             foreach($fields as $field_name=>$field_type) {
  102.                 $implodable[] = "$field_name $field_type";
  103.             }
  104.             $q = "CREATE TABLE $table (" . implode(", ", $implodable) . ");";
  105.             return $this->db_hwd->exec($q);
  106.         }
  107.         public function table_read($table) {
  108.             $q = "SELECT * FROM sqlite_master WHERE name='$table' AND type='table';";
  109.             return $this->db_hwd->query($q);
  110.         }
  111.         public function table_update($table, array $field) {
  112.             /**
  113.              * Caveat:
  114.              *     SQLite can only rename tables or add
  115.              *     columns with ALTER TABLE, and only allows
  116.              *     one column added per call. This function
  117.              *     WILL return boolean false if multiple
  118.              *     fields are passed into $field.
  119.              */
  120.             foreach($field as $field_name=>$field_type) {
  121.                 $implodable[] = "$field_name $field_type";
  122.             }
  123.             $q = "ALTER TABLE $table ADD COLUMN " . implode(", ", $implodable) . ";";
  124.             return $this->db_hwd->exec($q);
  125.         }
  126.         public function table_delete($table) {
  127.             return $this->db_hwd->exec("DROP TABLE $table;");
  128.         }
  129.        
  130.        
  131.         /**
  132.          * Raw Query function, on the off chance a non-standard
  133.          * query needs to be executed on the database.
  134.          */
  135.         public function raw_query($q) {
  136.             return $this->db_hwd->query($q);
  137.         }
  138.     }
  139. ?>
Advertisement
Add Comment
Please, Sign In to add comment