Rev 7 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
* Users class needs a way of accessing a data source. This is for a
* mysqli instance. Create an instance of this, then pass that instance
* to the Users class
*/
class usersDataSource {
private $dbDefinition = 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
'login'
),
'password' => array( // These fields are stored encrypted
'pass'
),
'fields' => array(
'login' => array(
'dbColumn' => 'login', // login name column name
'type' => 'varchar',
'size' => 64,
'required' => true,
'editable' => true
),
'pass' => array(
'dbColumn' => 'password', // password column name
'type' => 'varchar',
'size' => 128,
'required' => true,
'editable' => true
),
'admin' => array(
'dbColumn' => 'isAdmin',
'type' => 'boolean',
'required' => true,
'default' => '0'
),
'enabled' => array(
'dbColumn' => 'enabled',
'type' => 'boolean',
'required' => true,
'default' => '1'
)
)
)
)
);
private $dbConnection = false;
public function __construct( $dbConnection = null, $dbDef = array(), $dbLoginInfo = array() ) {
$this->dbConnection = $dbConnection;
if ( $dbDef ) {
$this->dbDefinition = array_merge_recursive( $this->dbDefinition, $dbDef );
}
if ( $dbLoginInfo ) {
$this->setDBConnection( $dbLoginInfo );
}
}
private function escapeString ( $string ) {
$string = $this->dbConnection->real_escape_string( $string );
if ( ! is_numeric( $string ) )
$string = "'$string'";
return $string;
}
/**
* Create a query to retrieve info from databawse
*
* Builds a query to retrieve records from the database. With all
* 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, which may not be a column in the table
* @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 ) {
// always get the ID field
$fields = array( $this->dbDefinition['tables']['users']['id'] . ' id');
// Get the rest of the available fields
foreach ( $this->dbDefinition['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'];
if ( isset( $whereFields ) ) {
$temp = array();
foreach ( $whereFields as $key => $value ) {
$temp[] = (
$key == 'id' ?
$this->dbDefinition['tables']['users']['id'] :
$this->dbDefinition['tables']['users']['fields'][$key]['dbColumn']
) . '= ' . $this->escapeString( $value );
}
$query .= ' where ' . implode( ' and ', $temp );
}
//print "<p>$query</p>";
return $query;
}
/**
* Get a record from the database
*
* Gets a single record from the database which matches $field containing
* $username. If more than one record is returned, will return the first
* one
*
* @param string[] $whereFields column=>value pairs for where clause
* @param string[] $fieldList a list of columns to return. If empty, returns all columns
*
* @return string[] a hash containing fieldname=>value pairs
*
*/
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 ) );
if ( $result ) { // got one, so return it
return $result->fetch_assoc();
}
// WTFO? nothing, so return empty array
return array();
}
public function getPassword( $username ) {
return $this->getARecord( array('login' => $username,'enabled' => 1), array('pass' => 1 ) );
}
public function getRecord ( $username ) {
return $this->getARecord( array( 'login' => $username ) );
}
private 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
*
* Using $dbDefinition, 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 ) {
if ( $this->dbConnection ) {
$password = password_hash( $password, PASSWORD_DEFAULT );
$fields = array( $this->dbDefinition['tables']['users']['id'] . ' int unsigned not null auto_increment' );
foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
$fieldDef = $record['dbColumn'];
$fieldDef .= ' ' . $record['type'];
if ( isset( $record['size'] ) ) {
$fieldDef .= '(' . $record['size'] . ')';
}
if ( isset( $record['required'] ) ) {
$fieldDef .= $record['required'] ? ' not null ' : '';
}
if ( isset( $record['default'] ) ) {
$fieldDef .= sprintf( " default '%s'", $record['default'] );
}
if ( isset( $record['comment'] ) ) {
$fieldDef .= "comment '" . $record['comment'] . "'";
}
$fields[] = $fieldDef;
}
$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;
} // buildTable
/**
* Tests that the database connection works and the table is built
*
*/
public function test() {
$result = $this->dbConnection->query( sprintf( "show tables like '%s'", $this->dbDefinition['tables']['users']['table'] ) );
return $result !== false && $result->num_rows;
} // test
public function update ( $newData ) {
$query = '';
foreach ( $newData as $key => $value ) {
$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 ) {
if ( isset( $newData[$key] ) ) {
$fields[] = $record['dbColumn'] . " = $newData[$key]";
} // if
}
$query = 'update ' . $this->dbDefinition['tables']['users']['table'] . ' set ' .
implode( ',', $fields ) .
' where ' . $this->dbDefinition['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 ) {
if ( isset( $newData[$key] ) ) {
$columns[] = $record['dbColumn'];
$values[] = $newData[$key];
} // if
}
$query = 'insert into ' . $this->dbDefinition['tables']['users']['table'] .
'(' . implode( ',', $columns ) . ') values (' .
implode( ',', $values ) . ')';
}
//print "<p>$query</p>";
return $this->dbConnection->query( $query );
}
} // update
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 );
if ( $result ) {
return $result->fetch_all(MYSQLI_ASSOC);
}
return array();
}
}
?>