Subversion Repositories phpLibraryV2

Rev

Rev 16 | Rev 34 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed

<?php

   /*
    * Class DBQuery
    * 
    * PHP Class as a wrapper around the mysqli class. Allows you to make
    * the connection and run queries and/or DDL's if needed with the
    * most common parameters (ie, not as flexible as mysqli itself).
    * Enhancements include:
    *
    * Logs all queries with an optional username except select statements
    * ie, anything that will change the system ( insert, delete, update,
    * create, drop, alter, etc... ). Statement are logged with a date/time
    * stamp, the user who performed the function, and the query executed
    * 
    * Errors are simply trapped and the message is stored in the public
    * member $error. Functions which find an error return boolean false
    *
    * Public Functions are:
    *    __construct   -- creates connection
    *    doSQL         -- executes an SQL query or DDL
    *    errors        -- returns all errors not yet cleared
    *                     can also directly access via public member errors
    *
    *    public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {


    * Author: R. W. Rodolico (rodo@dailydata.net)
    * Date: 2018-04-30
    * 
    */

   class DBQuery extends mysqli {

      /* everything is in the one $parameters array, which can then
       * be set/saved/loaded as needed
       */
      protected $parameters = array(
             // query(s) to be run
             'query' => '',
             // additional clause to be added to where on a statement
             'whereClause' => '',
             // additional order by clause to be added
             'orderBy' => '',
             // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select
             'rowsAffected' => '',
             // the value of the last row inserted
             'lastInsertKey' => '',
             // an array that contains the data retrieved by a query (select only)
             'returnData' => '',
             // an array that contains the meta data from the query for each column
             'columnMetaData' => '',
             // number of columns (fields) returned by query (select only)
             'numfields' => 0,
             // type of data returned, array of array, array of hash, or both
             'fetchType' => MYSQLI_ASSOC,
             // human readable form of fetchType
             'returntype' => 'associative',
             // array of any errors which occurred
             'error' => array(),
             // if set to a table name, all modifying queries are written to it
             // must contain, at a minimum, columns whenrun timestamp, whoran varchar and query text
             // can be created with $this->buildAuditTable
             'auditTable' => '_activity_log'
           );

      

      /*
       * static function which simply parses out an error and returns
       * a string suitable for printing. NOTE: it includes line returns
      */
      public static function error2String( $error ) {
         $return = array();
         for ( $i = 0; $i < count( $error ); $i++ ) {
            $return[] = implode( "\n", array( 'Error ' . $error[$i]['id'] . ' - ' . $error[$i]['message'], 'while executing query', $error[$i]['query'] ) );
         }
         return implode( "\n\n", $return ) . "\n";
      } // error2String
         
      // simply returns parameters for saving and reloading later
      public function save() {
         return $this->parameters;
      } // function save

      // loads parameters from $this->save(), or hand built
      public function load( $parameters = array() ) {
         $this->parameters = $parameters;
      } // function load

      // sets a key/value pair in $this->parameters
      // returns $value
      public function __set( $name, $value ) {
         $this->parameters[$name] = $value;
         return $value;
      }

      // gets the current value of $this->parameters[$name]
      public function __get( $name ) {
         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;
      }

      // returns true if $parameters[$name] is set
      public function __isset( $name ) {
         return isset( $this->parameters[$name] );
      }


      /*
       * function: buildAuditTable
       * parameters:
       *       $tablename - name of table to be built
       *       $createStatement - SQL DDL to build the table
       *
       * If $tablename is set, will use that, otherwise will use
       * $parameters[auditTable]. In either case, $parameters[auditTable]
       * is set to the value of the table used
       * 
       * if $createStatement is set, will be run WITHOUT MODIFICATION, and
       * $parameters[auditTable] is not set to anything (unless $tablename
       * is set)
       *
       * If $createStatement is not set, will use a default to build
       * a table from $parameters[auditTable].
       *
       * Can definitely blow up if the table name is not set both places
       * or if $createStatement is fubar
       */

      public function buildAuditTable( $tablename = '', $createStatement = '' ) {
         if ( $tablename ) // they sent us one, so set it
            $this->parameters[ 'auditTable' ] = $tablename;
         if ( ! $createStatement ) { // they did not set createStatement, so use our default
            $auditTable = $this->parameters['auditTable'];
            $createStatement = "
               create table $auditTable (
                  _activity_log_id int unsigned not null auto_increment,
                  timestamp  timestamp,
                  user       varchar(64),
                  query      text,
                  primary key(_activity_log_id)
               ) comment 'tracks queries which modify data'";
         } // if
         if ( parent::query( $createStatement ) === false ) {
            // on error, die
            print "Can not create audit table with query<br />\n$createStatement<br />\n";
            die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
         } // if error, die
      } // buildAuditTable
         
      /*
       * log queries to a table, file, or nothing
       * log contains date/time, username and query
       * to turn off logginging:
       * unset( $parameters['auditTable'] )
       */
      private function logIt( $username, $query, $recursion = false ) {
         if ( ! isset( $this->parameters['auditTable'] ) )
            return; 
         $username = $this->real_escape_string( $username );
         $query = $this->real_escape_string( $query );
         $logEntry = "insert into " . $this->parameters['auditTable'] . " (user, query) values ( '$username', '$query')";
         //print "Loggging\n$logEntry\n";
         if ( parent::query( $logEntry ) !== false ) { // good
            return;
         } else { // we had an error
            if ( ! $recursion && $this->errno == 1146 ) { // table doesn't exist, so let's create it
               $result = parent::query( "show tables like '" . $this->parameters['auditTable'] . "'" );
               if ( $result->num_rows == 0 ) {
                  $this->buildAuditTable( );
                  return $this->logIt( $username, $query, true );
               }
            } else {
               print "Trying to log transaction with query<br />\n$logEntry<br />\n";
               die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
            } // if..else
         } // if
      } // function logIt


      /*
       * doSQL
       * Parameters: $query - string or array of strings to be executed
       *             $parameters - hash used to pass additional parameters, to include
       *                           $parameters['username'] = 'fred'; // username for logging
       *                           $parameters['returntype'] = 'hash'; or array or both
       * 
       * executes one or more queries
       *
       * If the query is one of select, show, describe or explain, it must
       * be a single string. It will return the data results in a hash
       * containing
       *    'data'  - an array of array/hash/both depending on what you asked for
       *    'count' - number of results (ie, count(data)
       *    'meta'  - metadata for each column returned
       *    'numfields' - number of columns in result (ie, count(meta))
       *    'errors' - normally empty array of errors
       * 
       * if the query modifies data (ie, NOT above), query may be an array
       * which will be surrounded by a transaction and rolled back if
       * anything causes an error.
       *
       * These will return a hash containing
       *    'count' l- number of rows affected by last statement
       *    'last_insert_id' - last insert id created by BLOCK of queries
       *    'errors' - normally empty array of errors which occurred (caused a rollback)
       * 
       */
      public function doSQL( $query = null, $parameters = array() ) {
         $errors = array();
         if ( isset( $query ) ) {
            $this->parameters['query'] = $query;
         }
         // if it is a "selectstatement" it doesn't modify data
         // if query is an array, assume it modifies something
         // if it is a single statement, look for the regex
         $selectStatement = is_array( $this->parameters['query'] ) ?
            false :
            ( preg_match( '/^\s*(select)|(show)|(describe)|(explain)/i', $this->parameters['query'] ) === 1 );
        
         // different actions based on whether it modifies data or not
         if ( $selectStatement ) { // if a select, simply return the rows
            // dataset is small enough, we just read it into memory all at one time.
            // NOTE: fetch_all is nice, but tied to mysqlnd, which has reports of problems, so we do it the old way
            if ( $sth = parent::query( $this->parameters['query'], MYSQLI_USE_RESULT  ) ) {
               if ( $sth === false ) {
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error );
               } else {
                  $this->parameters['columnMetaData'] = $sth->fetch_fields(); // get metadata
                  $this->parameters['returnData'] = array(); // we'll put all the results in an array
                  // $fetchtype returns either an array of array, array of hash, or both. Default is array of hash
                  if ( isset( $this->parameters['returntype'] ) ) {
                     $this->parameters[ 'fetchType' ] = $this->parameters['returntype'] == 'array' ? MYSQLI_NUM : (
                           ( $this->parameters['returntype'] == 'both' ) ? MYSQLI_BOTH : MYSQLI_ASSOC
                           );
                  } else { // default is associative array (hash)
                     $this->parameters[ 'fetchType' ] =  MYSQLI_ASSOC;
                     $this->parameters['returntype'] = 'associative';
                  }
                  // slurp all the stuff in
                  while ( $values =  $sth->fetch_array( $this->parameters[ 'fetchType' ] ) ) {
                     $this->parameters[ 'returnData' ][] = $values;
                  }
                  $sth->free(); // cleanup memory, don't need two copies
               } // if we had no errors
            }
            $this->parameters[ 'rowsAffected' ] = count( $this->parameters[ 'returnData' ] );
            $this->parameters[ 'numfields' ] = count( $this->parameters['columnMetaData'] );
            $this->parameters[ 'lastInsertKey' ] = 0;
            $this->parameters[ 'error' ] = $errors;
         } else {
            if ( ! is_array( $this->parameters['query'] ) ) { // not an array, so make it one
               $temp = $this->parameters['query'];
               $this->parameters['query'] = array( $temp );
            }
            // do it in a transaction so we can back out on failure
            $this->autocommit(false);
            $allOk = true;
            for ( $i = 0; $i < count( $this->parameters['query'] ); $i++ ) {
               // debugging
               //print "$i\t" . $this->parameters['query'][$i] ."\n"; continue;
               // debugging
               $this->logIt( isset( $parameters['username'] ) ? $parameters['username'] : 'unknown', $this->parameters['query'][$i] );
               if ( parent::query( $this->parameters['query'][$i] ) === false ) { // we had an erorr
                  // record it
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error, 'query' => $this->parameters['query'][$i] );
                  $allOk = false;
                  // and bail
                  break;
               }
            }
            // if we made it through ok, commit, otherwise rollback
            $allOk ? $this->commit() : $this->rollback();
            // reset autocommit to true
            $this->autocommit(true);
            $this->parameters['query'] = $query;
            $this->parameters[ 'rowsAffected' ] = $this->affected_rows;
            $this->parameters[ 'lastInsertKey' ] = $this->insert_id;
            $this->parameters[ 'error' ] = $errors;
         } // if select .. else
         return $this->parameters;
      }  // function doSQL
      
      public function run () {
         return $this->doSQL( );
      }

      /*
       * function will return one and only one row, NOT as an array of array
       * but as a single row array
       * if more than one row is returned by query, error is set and function
       * returns false.
       * Otherwise, function returns true
       */
      public function getOneRow( $sql = null ) {
         if ( isset( $sql ) )
            $this->parameters[ 'query' ] = $sql;
         $save = $this->parameters[ 'useAssociativeArray' ];
         $useAssociativeArray = true;
         $this->run();
         $useAssociativeArray = $save;
         if ( $this->parameters[ 'rowsAffected' ] == 1 ) {
            $this->parameters[ 'returnData' ] = $this->parameters[ 'returnData' ][0];
            return true;
         } else
            $this->parameters['error'] = "$this->parameters[query] did not return a unique row in getOneRow";
         return false;
      } // getOneRow
         

      // function returns the first column of the first row of data returned from query
      // or null no value returned
      public function getOneDBValue( $sql = null ) {
         if ( isset( $sql ) )
            $this->parameters[ 'query' ] = $sql;
         $save = $this->parameters['returntype'];
         $this->parameters['returntype'] = 'array';
         $this->run();
         $this->parameters['returntype'] = $save;
//         print "<pre>" . print_r($this->parameters,true ) . "</pre>";
         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;
      }

      /*
       * function will attempt to make a constant ($value) safe for SQL depending on the type.
       * 
       * if $value is empty, $default is returned, as will happen if any of the
       * conversions (date, datetime, etc...) fail.
       * 
       * First, it will pass it through get_magic_quotes_gpc, 
       * then will run through mysql_real_escape_string
       * 
       * For strings, will encapsulate in quotes
       * Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes
       * DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS
       * Integer and Floats are passed through builtins intval and floatval
       * Boolean only checks the first character, a '0', 'f' and 'n' denoting false
       *    all else denoting true. The result is converted based on the variable
       *    $falsetrue, with the first char denoting false and the second denoting true
       */
      public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
         if (strlen($value) == 0) // simply set any empty values to null
            return $default;
         // print "Processing $value as $type with default $default<br>\n";
         switch ( strtolower( $type ) ) {
            case 'string' :
            case 's' : 
                     if ( get_magic_quotes_gpc() ) 
                        $value = stripslashes($value);
                     $value = $this->real_escape_string( $value );
                     $value = strlen( $value ) > 0 ? "'$value'" : $default;
                     break;
            case 'date' :
            case 'd' :
                     if ( $value != 'null' ) {
                        $result = strtotime( $value );
                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";
                     }
                     break;
            case 'datetime':
            case 'timestamp':
            case 'dt': 
                     if ( $value != 'null' ) {
                        $result = strtotime( $value );
                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";
                     }
                     break;
            case 'integer':
            case 'i' :  
                     $value = intval( $value );
                     break;
            case 'float':
            case 'f' :  
                     $value = floatval( $value );
                     break;
            case 'bool':
            case 'boolean':
            case 'b' :  // note, because of the way strpos works, you can not
                        // simply set $value based on the output; you MUST do
                        // as below; specifically check for false, then set the result
                        $value =  strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;
                        $value = substr( $falsetrue, $value, 0, 1 );
                        break;
         } // switch
         return $value;
      }


   } // class DBQuery

/*
 *    $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );

   if ($db->connect_error) {
       die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);
   }
   $result = $db->doSQL(
               array(
                     'drop table if exists temp',
                     'create table temp ( col1 int unsigned )',
                     "insert into temp values ('mike')"
                     )
         );
   if ( $result['error'] ) {
      print_r ( $result );
      die ( DBQuery::error2String( $result['error'] ) );
   } else {
      print "running select\n";
      $result = $db->doSQL( 'select * from temp' );
      print_r( $result );
   }
//   $return = $db->doSQL( "select device.device_id 'id',device.name 'name', device_type.name 'type' from device join device_type using (device_type_id) where device.removed_date is null and device_type.show_as_system = 'Y'" );
//   print_r( $return );
//   print_r( $db );
*/
?>