Class for DATABASE-INTERFACE using the PDO functions

DEPENDENCIES:

     - logging [optional]
 

VERSION:

4.1 (CURRENT) makes the 2nd argument of the method countRecords  optional

4.0.1 corrects a bug in the  pdo_num_rows  method

    public $dbHandler;                        // Object created by call to new PDO()
    
    public $dbaseServer;                    // Database server (e.g. "localhost" or "mysql.mysite.com")
    public $dbase;                            // Name of default database schema
    public $dbUser;                            // Database username
    public $pass;                            // Password for the given database user

    public $errorSummary = "";                // Short version of the last database error, if applicable
    public $errorDetails = "";                // Longer version of the last database error, if applicable
    
    public $dbLogFile = "";                    // Optional [VERY RECOMMENDED] file in which to keep log of database errors (re-tries and outright failures)

    public function setConnectionParameters($dbaseServer, $dbase, $dbUser, $pass)
    // Permits the setting of all 4 connection parameters at once

    
    
    public function dbaseStart($retry = false)
    /*  Create a new database connection.  Any existing old connection gets cleared (forced restart.)

        If successful, it saves the newly-created handle and returns true;
        in case of failure, it tries a second time, then it sets some error-reporting properties (plus optional file log) and returns false.
        
        ARGUMENTS
            retry        flag indicating whether this is the second (and last) attempt
     */
   

    
    public function selectSQL($sql, $bindings = false) 
    /*    Run the specified SELECT query, with or without bindings.
    
        ARGUMENTS
            If the optional argument $bindings is specified, the SQL must contain one or more "?" (without quotes), 
                and $bindings must contain an array of values, or a single value.
                Example1: $sql = "SELECT * FROM mytable WHERE x = ? AND y = ?"
                                    and
                            $bindings = array($value1, $value2)
                            
                            A single binding value can also be passed without an array. [i.e. a scalar $s gets converted to array($s)]
            
                Example2: $sql = "SELECT * FROM mytable WHERE x = :p1 AND y = :p2"
                                    and
                            $bindings = array(':p1' => $value1, ':p2' => $value2))
        
                            
        RETURN a (possibly emtpy) traversable associative/num dataset (aka "PDO statement".)    
            In case of ERROR, false is returned, and the "errorSummary" and "errorDetails" properties are set; also, the error is logged.
            The retry flag in the database object determines if a 2nd attempt is made in case of error.
        
        NOTES
        =====
        If an ASSOCIATIVE dataset is desired instead of an associative/numeric one, just do:
            $result = selectSQL($sql);
            $result->setFetchMode(PDO::FETCH_ASSOC);
            [note: with an associative array, one cannot use the list() construct]
            
        Likewise, if desiring a purely NUMERIC dataset, do:
            $result = selectSQL($sql);
            $result->setFetchMode(PDO::FETCH_NUM);
            
        
        If one wishes to determine how many records were retrieved, use pdo_num_rows() IF using a MySQL database
        
        
        TYPICAL CALLING CODE:
        ====================
        $sql = "SELECT a, b FROM table";
        $result = $dbaseHandler->selectSQL($sql);
            
        
        EXAMPLES OF HOW TO USE THE RESULT:
        =================================    
        (1)
            foreach ($result as $row)  {
                $myFieldValue = $row["myFieldName"];    // An associative/numeric dataset is returned by default (see notes above.)  Note that $row[0], etc., will also work
        
                echo "myFieldValue: $myFieldValue<br>";
            }
        
        (2)
            while($row = $result->fetch())
                echo $row['field1'];                 // $row[0], etc., will also work
            
        (3)
            while($row = $result->fetch(PDO::FETCH_ASSOC))    // The PDO::FETCH_ASSOC part is not strictly necessary, because the default is PDO::FETCH_BOTH
                echo $row['field1'];  
                
        (4)
            while($row = $result->fetch(PDO::FETCH_NUM))    // The PDO::FETCH_NUM part is not strictly necessary, because the default is PDO::FETCH_BOTH
                echo $row[0]; 
            
        (5)
            $resultArray = $result->fetchall();        // Transform the result into an array (containing all of the result-set rows);
                                                    //        for the individual entry (i.e. each dbase record) if one wishes a purely associative array, use fetchall(PDO::FETCH_ASSOC); 
                                                    //        for a purely numeric arrays, use fetchall(PDO::FETCH_NUM)
                                                    //    An empty array is returned if there are zero results to fetch, or FALSE on failure
                                                    //  For convenience, the methods selectSQLarrayNum() and selectSQLarrayAssoc() are also available
            foreach($resultArray as $row)  {
                ...     // like before
            }
            
        (6)
            $sql = "SELECT a, b FROM table";
            $result = $dbObject->selectSQL($sql);
            foreach ($result as $row)  {
                list($a, $b) = $row;
                echo "a: $a | b: $b<br>";
            }
            
        (7)
            $sql = "SELECT a FROM table";
            $result = $dbObject->selectSQL($sql);
            $resultArray = $result->fetchall(PDO::FETCH_COLUMN);    // An array with all the returned entries from the single column
            
        (8)
            $sql = "SELECT f0, f1, f2, f3 FROM table";
            $result = $dbObject->selectSQL($sql);
            while(($columnValue = $result->fetchcolumn(2)) !== false)    // Do NOT use on Boolean fields.  See http://php.net/manual/en/pdostatement.fetchcolumn.php
                echo $columnValue;
            // fetchColumn returns a single column from the next row of a result set 
            
        (9)
            Use fetch(PDO::FETCH_CLASS) or fetch(PDO::FETCH_INTO) to save the results into objects
     */ 

    public function selectSQLarrayNum($sql, $bindings = false)
    /*     Shortcut to invoking selectSQL() and then converting the entries of the returned dataset into a numeric array.
        An empty array is returned if there are zero results to fetch, or FALSE on failure.
        
        Example of return value:
            Array(
                Array ( [0] => 123  [1] => 222) 
                Array ( [0] => 666  [1] => 333)
            }
        
        Example of usage:
                    $resultArray = selectSQLarrayNum($sql);
                    foreach($resultArray as $row)  {
                        echo $row[0] . "<br>";
                    }
     */
 

    public function selectSQLarrayAssoc($sql, $bindings = false)
    /*     Shortcut to invoking selectSQL() and then converting the entries of the returned dataset into an associative array.
        An empty array is returned if there are zero results to fetch, or FALSE on failure.
        
        Example of return value:
            Array(
                Array ( [field1] => 123  [field2] => 222) 
                Array ( [field1] => 666  [field2] => 333)
            }
            
        Example of usage:
                    $resultArray = selectSQLarrayNum($sql);
                    foreach($resultArray as $row)  {
                        echo $row["field1"] . "<br>";
                    }
     */
 

    public function selectSQLFirstRecord($sql, $bindings = false)
    /*    Run the specified SQL SELECT statement, and return the first row of the result (as an assoc/num array.) 
        A "LIMIT 1" statement is automatically added to the SQL.  WARNING: make sure that the $sql passed to it does not already have  "LIMIT" statement; if it does, use the method selectSQLOneRecord() instead
        In case of no records, return null.
        In case of error, return false.
        
        Typically used with SELECT statements that return a single record.
        Note: pdo_num_rows() cannot be used after this function call, 
                because the PDOstatement is not returned (maybe it could be saved in the object?)
    
        
        EXAMPLE 1:
            $sql = "SELECT a, b, c FROM myTable";
            list($a, $b, $c) = $db->selectSQLFirstRecord($sql);
    
        EXAMPLE 2:
            $sql = "SELECT * FROM myTable";
            $resultRow = $db->selectSQLFirstRecord($sql);
            $a = $resultRow["a"];
            $b = $resultRow["b"];
            $c = $resultRow["c"];
     */

    
    
    
    public function selectSQLOneRecord($sql, $bindings = false)
    /*    Run the specified SQL SELECT statement, and return the first row of the result (as an assoc/num array.) 
        In case of no records, return null.
        In case of error, return false.
        
        Typically used with SELECT statements that return a single record.
        Note: pdo_num_rows() cannot be used after this function call, 
                because the PDOstatement is not returned (maybe it could be saved in the object?)
    
        
        EXAMPLE 1:
            $sql = "SELECT a, b, c FROM myTable ORDER BY a LIMIT 1";
            list($a, $b, $c) = $db->selectSQLFirstRecord($sql);
    
        EXAMPLE 2:
            $sql = "SELECT * FROM myTable";
            $resultRow = $db->selectSQLFirstRecord($sql);
            $a = $resultRow["a"];
            $b = $resultRow["b"];
            $c = $resultRow["c"];
     */

    
        
    
    public function selectSQLOneValue($sql, $bindings = false)
    /*    Run the specifed SQL query (expected to be a SELECT statement.)
    
        RETURN the first (zero-th) column of the first (zero-th) row.
        In case of no records or error, return null.  
        The returned value might be a null if that's what contained in the database or returned by a database function in the SELECT statement.
        Typically used with SELECT statements that return a single value, such as a count(*)
        In case of error, return false.
        
        EXAMPLE:
            $myCount = selectSQLOneValue("SELECT count(*) FROM myTable WHERE ID > 7");
            
        IMPORTANT NOTE: Aggregate functions sometimes return null.  For example, "SELECT max(ID) myTable WHERE ID > 100" will return null if no records satisfy the condition;
            the calling function may test for this with the "===" identity check, as follows :   
            if ($result === null) // to distinguish no records found vs. a returned value of zero
        
        Note: pdo_num_rows() cannot be used after this function call, 
                because the PDOstatement is not returned (maybe it could be saved in the object?)
     */

    
    
    
    public function selectSQLOneColumn($sql, $columnPosition, $bindings = false)
    /*     Return a numeric array with the values of the specified column from the result of the SQL query;
        in case of error, return false.
        Column positions are counted from zero (i.e., 0 corresponds to the first column)
        WARNING: do NOT use on Boolean fields.  See http://php.net/manual/en/pdostatement.fetchcolumn.php
     */

    
    
    
    public function isInTable($table, $field, $value)
    /*     Return true if the given value is found at least once (in any row) in the specified field in the given table;
        false, otherwise
        
        EXAMPLE:
            isInTable("myTable", "field1", 123)
     */
 

    
    public function pdo_num_rows($PDOstatement)
    /*     This function emulates mysql_num_rows(), but only works for MySQL databases.
    
        1)     It returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the specified PDOStatement object. 
        
        2)     If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. 
            However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. 
            (See  http://php.net/manual/en/pdostatement.rowcount.php  and  http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers)
            
        In case of error, it return false
     */

    public function pdo_result($PDOstatement, $rowCount, $field)
    /* Emulates mysql_result()
     */
 

    public function countRecords($table, $selectSubquery = "")
    /*     Return the number of records in the given table when the specified subquery (the part *after* the WHERE in the sql query) is applied;
        if the subquery is a blank string or missing, then the total number of records in the table is returned.
        In case of error, return false.  A === false check on the return value should be done to distinguish zero records from an error condition.
        
        EXAMPLE:  $number_of_records = countRecords("myTable" , "`ID` > `0");
     */

    public function modificationSQL($sql, $bindings = false) 
    /*    Run the specified "modification" SQL (i.e. UPDATE, INSERT, DELETE , CREATE TABLE , or DROP TABLE.)
            
        If the optional argument $bindings is specified, the SQL must contain one or more "?" (without quotes), 
            and $bindings must contain an array of values, or a single value.
            Example1:     $sql = "UPDATE myTable SET field = ? WHERE ID = ?";
                                and
                        $bindings = array($value1, $value2);
                        
                        A single binding value can also be passed without an array. [i.e. a scalar $s gets converted to array($s)]
        
            Example2:     $sql = "UPDATE myTable SET x = :p1 AND y = :p2";
                                and
                        $bindings = array(":p1" => $value1, ":p2" => $value2);
                        
            Example3:    $sql = "INSERT INTO myTable (`col1`, `col2`)
                                                VALUES (:username, :email)";
                                and
                        $bindings = array(":username" => "me myself", ":email" => "a@b.com");
        
        RETURN VALUE
            In case of successful insert, update or delete operations, return the number of affected rows
            In case of successful create/drop table operations, return 0
            In case of error, -1 is returned, error messages are logged, and some error properties get set
     */ 
    

    public function retrieveInsertID()
    /* Return the auto-increment value from the last INSERT operation
     */

    
    
    
    public function createTable($tableName, $columnDefinitions, $primaryKey, $otherKeys = false, $tableOptions = "ENGINE=InnoDB , DEFAULT CHARSET=latin1")
    /*    Create a mySQL table.
        If successful, RETURN true; otherwise, return false and set error properties.

        See https://dev.mysql.com/doc/refman/5.5/en/create-table.html
    
        EXAMPLES of arguments:
            $tableName                    "myTable"
            $columnDefinitions            "`ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
                                         `field1` varchar(16) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,  
                                           `field2` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
                                          `field3` smallint(5) unsigned DEFAULT NULL,                                                          
                                           `field4` text CHARACTER SET latin1 COLLATE latin1_general_ci, 
                                           `timeStamp` timestamp DEFAULT CURRENT_TIMESTAMP
                                         
            $primaryKey                    "`field1`, `field2`"
            
            $otherKeys  [OPTIONAL]        "UNIQUE KEY `someNameForKey` (`field3`,`field4`)"
                                        "KEY `sessionID` (`sessionID`), KEY `subID` (`subID`)"
                                        
            $tableOptions  [OPTIONAL]    "ENGINE=MyISAM , DEFAULT CHARSET=latin1", AUTO_INCREMENT=69
     */
  
    
    
    public function getFieldNames($tableName)
    /*    Return an array of field names in the given table (or false in case of failure).
        Only tested on MySQL.
        
        ARGUMENT:
            $tableName    A string with the table's name.  Example: "myTable" (do not wrap the name in database back quotes)
     */
    

    public function tableExists($table)
    // Return true iff the specified table already exists in the current database

    
    
    public function allTables()
    // Return a numeric array with the names of all the tables in the current database, or false in case of error

    
    
    
    public function extractForeignKeys($tableName)
    /*     Look up and return all the foreign keys of the given table.
        This function may only work on MySQL.
        
        RETURN: a numeric array whose entries are associative arrays of the form  [columnName, foreignTable, foreignColumn]
                If no foreign keys are present, an empty array is returned, or false in case of error.
                Example:
                    Array ( [0] => Array ( [columnName] => customerID [foreignTable] => customers [foreignColumn] => ID ) 
                            [1] => Array ( [columnName] => shipperID  [foreignTable] => shippers  [foreignColumn] => ID ) 
                          ) 
     */
    
    
    
    public function mapFieldsToForeignKeys($tableName)
    /* 
        RETURN:
            If no foreign keys are present, an empty array is returned, or false in case of error.
            
            Example:
                Array( "customerID" => Array( [0] =>"customers" , [1] => "ID") ,
                       "shipperID"  => Array( [0] =>"shippers"  , [1] => "ID")
                      )
     */

    
    
    /*************************************
           DEBUGGING METHODS
     *************************************/

    public function debugPrintSQL($sql, $bindings, $expandSQL = true)
    /*     Return an HTML-formatted string with the SQL and the bindings.
        If requested, reconstruct and print out the full SQL (typically for debugging purposes) after expanding the bindings.  TO-DO: take care of named bindings
     */

    public function debugPrintTextSQL($sql, $bindings, $expandSQL = true)
    /*     Return a plain-text string the SQL and the bindings.
        If requested, reconstruct and print out the full SQL (typically for debugging purposes) after expanding the bindings.  TO-DO: take care of named bindings
     */

    public function debugLogSQL($sql, $bindings, $expandSQL = true)
    /*     Write to the log file a plain-text string the SQL and the bindings.
        If requested, reconstruct and log the full SQL (typically for debugging purposes) after expanding the bindings.  TO-DO: take care of named bindings
     */