WebSVN – php_users – Diff – /trunk/UsersDataSourceMySQLi.class.php

  */
 class usersDataSource {
    /**
-    * @var string[] $dbDefinition Contains the configuration for the class
+    * @var string[] $configuration Contains the configuration for the class
+    *
     * May be modified by the calling program. Must be replicated in userDataSource class
     */
-   private $dbDefinition = array(
+   protected $configuration = array(
       'tables' => array(
          'users'  => array(
             'table'     => '_users',   // table name for user records
             'id'        => '_user_id', // ID column name
             'display'   => array(      // fields which are displayed to select
+               )
+            )
+         )
       );
       /** @var mysqli $dbConnection Holds the mysqli database connection */
-      private $dbConnection = false;
+      protected $dbConnection = false;
       /**
        * constructor for an instance of the class
+       *
        * If $dbConnection is not null, will be used for database access
        * If $dbLoginInfo is not null, will override $dbConnection, make
        * a new connection and use that.
+       *
-       * If $dbDef is set, will be merged with $dbDefinition
+       * If $dbDef is set, will be merged with $configuration
+       *
        * @param mysqli $dbConnection Existing mysqli database connection
-       * @param string[] $dbDef Array to be merged with $dbDefinition
+       * @param string[] $dbDef Array to be merged with $configuration
        * @param string[] $dbLoginInfo Array containing username, hostname, etc.. to make mysqli connection_aborted
+       *
        * @return null
+       *
        */
       public function __construct( $dbConnection = null, $dbDef = array(), $dbLoginInfo = array() ) {
          $this->dbConnection = $dbConnection;
          if ( $dbDef ) {
-            $this->dbDefinition = array_merge_recursive( $this->dbDefinition, $dbDef );
+            $this->configuration = array_merge_recursive( $this->configuration, $dbDef );
+         }
          if ( $dbLoginInfo ) {
             $this->setDBConnection( $dbLoginInfo );
+         }
+      }
        * puts single quotes around it
+       *
        * @param string $string The string to be fixed
        * @return string A copy of the string, ready for SQL
        */
-      private function escapeString ( $string ) {
+      protected function escapeString ( $string ) {
          $string = $this->dbConnection->real_escape_string( $string );
          if ( ! is_numeric( $string ) )
             $string = "'$string'";
          return $string;
+      }
        * parameters set to null, will retrieve all columns and records
        * Setting $field and $toFind create a where clause, and setting
        * $fieldList as a has (ie, 'fieldname' => 1) will limit the
        * fields returned
+       *
-       * @param string $field A valid field definition from $dbDefinition
+       * @param string $field A valid field definition from $configuration
        * @param string $toFind The string to find, ie where $field = $username
        * @param string[] $fieldList a hash where the keys make a list of columns to return. If empty, returns all columns
+       *
        * @return string A cleaned and formatted SQL Query
+       *
       */
-      private function buildQuery( $whereFields, $fieldList = null ) {
+      protected function buildQuery( $whereFields, $fieldList = null ) {
          // always get the ID field
-         $fields = array( $this->dbDefinition['tables']['users']['id'] . ' id');
+         $fields = array( $this->configuration['tables']['users']['id'] . ' id');
          // Get the rest of the available fields
-         foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $value ) {
+         foreach ( $this->configuration['tables']['users']['fields'] as $key => $value ) {
             // do not use this one if $fieldList doesn't have it
             if ( ! isset( $fieldList ) || isset( $fieldList[$key] ) )
                $fields[] = $value['dbColumn'] . ' ' . $key;
+         }
          // Change it into something SQL can handle
          $query = implode( ',', $fields );
          // now, build the rest of the query
-         $query = "select $query from " . $this->dbDefinition['tables']['users']['table'];
+         $query = "select $query from " . $this->configuration['tables']['users']['table'];
          if ( isset( $whereFields ) ) {
             $temp = array();
             foreach ( $whereFields as $key => $value ) {
                $temp[] = (
                   $key == 'id' ?
-                  $this->dbDefinition['tables']['users']['id'] :
+                  $this->configuration['tables']['users']['id'] :
-                  $this->dbDefinition['tables']['users']['fields'][$key]['dbColumn']
+                  $this->configuration['tables']['users']['fields'][$key]['dbColumn']
                   ) . '= ' . $this->escapeString( $value );
+            }
             $query .= ' where ' . implode( ' and ', $temp );
+         }
          //print "<p>$query</p>";
+       *
       */
       public function getARecord( $whereFields, $fieldList = null ) {
          // run the query, placing value in $result
          mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
-         $result = $this->dbConnection->query( $this->buildQuery( $whereFields, $fieldList ) );
+         $result = $this->doSQL( $this->buildQuery( $whereFields, $fieldList ) );
          if ( $result ) { // got one, so return it
             return $result->fetch_assoc();
+         }
          // WTFO? nothing, so return empty array
          return array();
       /**
        * Gets the entire record for a user
+       *
        * NOTE: this does not actually get all columns. getARecord only gets
-       * the columns defined in $dbDefinition
+       * the columns defined in $configuration
+       *
        * @param string $username the value of the login field to find
+       *
        * @return string[] fieldname=>value array of found record
        */
        * Make the database connection
+       *
        * @param string[] $parameters Parameters for makeing the connection
        * @return mysqli|false
        */
-      private function setDBConnection ( $parameters ) {
+      protected function setDBConnection ( $parameters ) {
          if ( !isset($parameters['username'], $parameters['password'],$parameters['database']  )) {
             return false;
+         }
          if ( !isset( $parameters['host'] ) ) {
             $parameters['host'] = 'localhost';
          mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
          $this->dbConnection = new mysqli( $parameters['host'], $parameters['username'], $parameters['password'],$parameters['database'] );
+      }
       /**
-       * Convenience function to create the table
+       * Convenience function to create the tables defined in $configuration
+       *
-       * Using $dbDefinition, build the table (replacing the current one)
+       * Using $configuration, build the table (replacing the current one)
        * then add $username with $password, setting as admin
+       *
-       * @param string $username The username to create
-       * @param string $password The password for this record
-       * @param bool $admin Whether the user is an admin or not
-       *
-       * @return bool true if table was created
-       *
       */
-      public function buildTable( $username, $password, $admin = true ) {
+      public function buildTable( ) {
          if ( $this->dbConnection ) {
+            foreach ( $this->configuration['tables'] as $table => $tableRecord ) {
-            $password = password_hash( $password, PASSWORD_DEFAULT );
+               //print "<pre>Building " . $table . "\n</pre>";
-            $fields = array( $this->dbDefinition['tables']['users']['id'] . ' int unsigned not null auto_increment' );
+               $fields = array( $tableRecord['id'] . ' int unsigned not null auto_increment' );
-            foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
+               foreach ( $tableRecord['fields'] as $key => $record ) {
+                  //print "<pre>\tColumn " . $key . ' using ' . print_r( $record, true)  . "\n</pre>";
-               $fieldDef = $record['dbColumn'];
+                  $fieldDef = $record['dbColumn'];
-               $fieldDef .= ' ' . $record['type'];
+                  $fieldDef .= ' ' . $record['type'];
-               if ( isset( $record['size'] ) ) {
+                  if ( isset( $record['size'] ) ) {
-                  $fieldDef .= '(' . $record['size'] . ')';
+                     $fieldDef .= '(' . $record['size'] . ')';
-               }
+                  }
-               if ( isset( $record['required'] ) ) {
+                  if ( isset( $record['required'] ) ) {
-                  $fieldDef .= $record['required'] ? ' not null ' : '';
+                     $fieldDef .= $record['required'] ? ' not null ' : '';
-               }
+                  }
-               if ( isset( $record['default'] ) ) {
+                  if ( isset( $record['default'] ) ) {
-                  $fieldDef .= sprintf( " default '%s'", $record['default'] );
+                     $fieldDef .= sprintf( " default '%s'", $record['default'] );
+                  }
+                  if ( isset( $record['comment'] ) ) {
+                     $fieldDef .= "comment '" . $record['comment'] . "'";
+                  }
+                  $fields[] = $fieldDef;
+               }
+               $fields[] = 'primary key (' . $tableRecord['id'] . ')';
-               if ( isset( $record['comment'] ) ) {
+               $query = implode( ',', $fields );
-                  $fieldDef .= "comment '" . $record['comment'] . "'";
+               $query = 'create or replace table ' . $tableRecord['table'] .
-               }
+                     "($query)";
+               //print '<pre>' . $query . "\n</pre>";
-               $fields[] = $fieldDef;
+               $this->doSQL( $query );
+            }
-            $fields[] = 'primary key (' . $this->dbDefinition['tables']['users']['id'] . ')';
-            $query = implode( ',', $fields );
-            $query = 'create or replace table ' . $this->dbDefinition['tables']['users']['table'] .
-                  "($query)";
-            $this->dbConnection->query( $query );
-            $query = "insert into _users( login, password, isAdmin ) values ( '$username', '$password', '$admin' )";
-            $this->dbConnection->query( $query );
-         }
-         return false;
+         } // foreach table
       } // buildTable
+      /**
+       * Convenience function to initialize tables to values
+       *
+       * @param string[] $initValues Array of tablenames, column names and values
+       *
+      */
+      public function initTables ( $initValues ) {
+         foreach ( $initValues as $table => $fieldDef ) {
+            $columns = array();
+            $values = array();
+            foreach ( $fieldDef as $columnName => $columnValue ) {
+               $columns[] = $this->tableColumnName( $table, $columnName );
+               $values[] = $this->escapeString( $columnValue );
+            }
+            $query = sprintf( "insert into %s (%s) values (%s)",
+                  $this->configuration['tables'][$table]['table'],
+                  implode( ",", $columns ),
+                  implode( ',', $values )
+                  );
+            //print '<pre>' . $query . "\n</pre>";
+            $this->doSQL( $query );
+         }
+      }
+      protected function tableColumnName ( $table, $field ) {
+         return $this->configuration['tables'][$table]['fields'][$field]['dbColumn'];
+      }
       /**
        * Tests that the database connection works and the table is built
+       *
        * @return boolean True if table exists (does not verify columns)
        */
       public function test() {
-         $result = $this->dbConnection->query( sprintf( "show tables like '%s'", $this->dbDefinition['tables']['users']['table'] ) );
+         $result = $this->doSQL( sprintf( "show tables like '%s'", $this->configuration['tables']['users']['table'] ) );
          return $result !== false && $result->num_rows;
       } // test
       /**
        * updates row in database with $newData
             $newData[$key] = $this->escapeString( $value );
+         }
          if ( $newData ) { // make sure they sent us something
             if ( $newData['id'] > 0 ) { // we are doing an update
                $fields = array();
-               foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
+               foreach ( $this->configuration['tables']['users']['fields'] as $key => $record ) {
                   if ( isset( $newData[$key] ) ) {
                      $fields[] = $record['dbColumn'] . " = $newData[$key]";
                   } // if
+               }
-               $query = 'update ' . $this->dbDefinition['tables']['users']['table'] . ' set ' .
+               $query = 'update ' . $this->configuration['tables']['users']['table'] . ' set ' .
                   implode( ',', $fields ) .
-                  ' where ' . $this->dbDefinition['tables']['users']['id'] . ' = ' .
+                  ' where ' . $this->configuration['tables']['users']['id'] . ' = ' .
                   $this->dbConnection->real_escape_string( $newData['id'] );
             } else { // we are doing an insert
                $columns = array();
                $values = array();
-               foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
+               foreach ( $this->configuration['tables']['users']['fields'] as $key => $record ) {
                   if ( isset( $newData[$key] ) ) {
                      $columns[] = $record['dbColumn'];
                      $values[] = $newData[$key];
                   } // if
+               }
-               $query = 'insert into ' . $this->dbDefinition['tables']['users']['table'] .
+               $query = 'insert into ' . $this->configuration['tables']['users']['table'] .
                   '(' . implode( ',', $columns ) . ') values (' .
                   implode( ',', $values ) . ')';
+            }
             //print "<p>$query</p>";
-            return $this->dbConnection->query( $query );
+            return $this->doSQL( $query );
+         }
       } // update
       /**
        * retrieves all users from the database
        */
       public function getAllUsers() {
          $query = $this->buildQuery( null, null, array('login' => 1) );
          //print "<p>$query</p>\n";
          mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
-         $result = $this->dbConnection->query( $query );
+         $result = $this->doSQL( $query );
          if ( $result ) {
             return $result->fetch_all(MYSQLI_ASSOC);
+         }
          return array();
+      }
+      /**
+       * Executes an SQL statement, returning the result
+       *
+       * This simply runs mysqli::query, and returns the value of that
+       *
+       * Created for testing and debugging, if the second parameter is
+       * true, will NOT execute the query, but will instead display the
+       * query passed.
+       *
+       * @parameter string $query SQL Query to execute
+       * @parameter boolean $testing If set to true, displays query instead of executing it
+       *
+       * @returns mysqli_result
+       */
+      protected function doSQL( $query, $testing = false ) {
+         if ( $testing ) {
+            print "<pre>$query</pre>"; return;
+         } else {
+            mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
+            $result = $this->dbConnection->query( $query );
+            return $result;
+         }
+      }
+      /**
+       * Gets a single field from a table
+       *
+       * Builds a query similar to
+       * select $returnColumn from $tableName where $fieldName = $value
+       * executes it, and returns the first column of the first
+       * row returned, or null if it does not exist.
+       *
+       * @parameter string $tableName Name of database table
+       * @parameter string $returnColumn Column to return
+       * @parameter string $fieldName Name of column to search for value
+       * @parameter string $value The value to match
+       *
+       * @returns string $returnColumn of first row, or null if none
+       */
+      protected function getAField( $tableName, $returnColumn, $fieldName, $value ) {
+         $value = $this->escapeString( $value );
+         $result = $this->doSQL( "select $returnColumn from $tableName where $fieldName = $value" );
+         $field = $result->fetch_array(MYSQLI_NUM);
+         return $field ? $field[0] : null;
+      }
+}
 ?>

Subversion Repositories php_users

(root)/trunk/UsersDataSourceMySQLi.class.php – Rev 10 → 16