Rev 16 | Rev 18 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
require_once( 'UsersDataSourceMySQLi.class.php' );
/*
Copyright (c) 2021, Daily Data, Inc. Redistribution and use in
source and binary forms, with or without modification, are permitted
provided that the following conditions are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/*
* UsersDataSourceMySQLi.class.php
*
* Authors: R. W. Rodolico
*
*/
/**
* usersPermissionsDataSource class
*
* usersPermissionsDataSource provides the data access capabilities for
* the usersPermissions class
* class.
*
* To build a data access class for UsersPermissions, the following 5
* methods must exist.
* getPassword(username)
* getRecord(username)
* getAllUsers()
* getARecord
* update
*
* Additionally, where appropriate, the following function is useful
* buildTable()
*
* This particular instance provides an interface to MySQL using
* the mysqli libraries.
*
* Create an instance of this, then pass the variable to several Users
* calls.
*
* @author R. W. Rodolico <rodo@unixservertech.com>
*
* @version 0.9.0 (beta)
* @copyright 2021 Daily Data, Inc.
*
*/
class usersPermissionsDataSource extends usersDataSource {
/**
* 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 $configuration
*
* @param mysqli $dbConnection Existing mysqli database connection
* @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->configuration['tables']['permissions categories'] = array(
'table' => '_permissions_categories', // table name for user records
'id' => '_permission_category_id', // ID column name
'display' => array( // fields which are displayed to select
'description'
),
'fields' => array(
'name' => array(
'dbColumn' => 'name',
'type' => 'varchar',
'size' => 16,
'required' => true
)
)
); // adding permissions table
$this->configuration['tables']['permissions'] = array(
'table' => '_permissions', // table name for user records
'id' => '_permission_id', // ID column name
'display' => array( // fields which are displayed to select
'description'
),
'fields' => array(
'name' => array(
'dbColumn' => 'name',
'type' => 'varchar',
'size' => 16,
'required' => true
),
'description' => array(
'dbColumn' => 'description',
'type' => 'varchar',
'size' => 64,
'required' => true
),
'permission category' => array(
'dbColumn' => '_permission_category_id',
'type' => 'int unsigned',
'required' => true,
),
'default' => array(
'dbColumn' => 'default_value',
'type' => 'boolean',
'required' => true
),
)
); // adding permissions table
$this->configuration['tables']['users permissions'] = array(
'table' => '_users_permissions', // table name for user records
'id' => '_user_permission_id', // ID column name
'fields' => array(
'user_id' => array(
'dbColumn' => '_user_id',
'type' => 'int unsigned',
'required' => true,
'references' => 'users',
),
'permission_id' => array(
'dbColumn' => '_permission_id',
'type' => 'int unsigned',
'required' => true,
'references' => 'permissions',
),
'value' => array(
'dbColumn' => 'value',
'type' => 'boolean',
'required' => true,
),
)
); // adding permissions table
$this->configuration['views']['users permissions'] = array( 'name' => '_view_users_permissions' );
parent::__construct( $dbConnection, $dbDef, $dbLoginInfo );
} // constructor
/**
* Function will build tables when called
*
* Calls parent::buildTable first, then creates a view. The query
* for the view is created using sprintf so we can allow the developers
* to modify the table names, etc...
*
*/
public function buildTable() {
parent::buildTable();
// add a unique constraint on users permissions for user id and permission id
$query = sprintf( 'alter table %s add unique key (%s,%s)',
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['user_id']['dbColumn'],
$this->configuration['tables']['users permissions']['fields']['permission_id']['dbColumn']
);
$this->doSQL( $query );
// create a convenience view
$query = sprintf( "create or replace view %s as
select
%s.%s users_id, /* users id */
%s.%s user, /* users login */
%s.%s permission_id, /* permissions id */
%s.%s permission, /* permissions name */
%s.%s description, /* permissions description */
%s.%s category, /* permissions_categories name */
ifnull(%s.%s,0) value /* actual value */
from
%s /* users */
join %s /* permissions (permissions_id */
left join %s using (%s,%s) /* users_permissions (users_id) */
join %s using (%s) /* permissions_categories( permissions_categories_id) */",
$this->configuration['views']['users permissions']['name'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['fields']['login']['dbColumn'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['name']['dbColumn'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['description']['dbColumn'],
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['fields']['name']['dbColumn'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['value']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['id']
);
$this->doSQL( $query );
}
/**
* Adds a permission to the permissions table
*
* This simply adds a permission to the permissions table, adding the
* category if it doesn't exist already.
*
* @parameter string $category Category to place the permission into
* @parameter string $name The short name of the permission
* @parameter string $description The long (display) name for the permission
* @parameter boolean $defaultValue The default value for the permission
*/
public function addPermission ( $category, $name, $description, $defaultValue = 0 ) {
$categoryID = $this->getAField(
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['id'],
$this->configuration['tables']['permissions categories']['fields']['name']['dbColumn'],
$category
);
if ( ! $categoryID ) { // we did not find the category, so add it
$this->doSQL( sprintf( "insert into %s ( %s ) values ( %s )",
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['fields']['name']['dbColumn'],
$this->escapeString($category)
)
);
$categoryID = $this->getAField(
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['id'],
$this->configuration['tables']['permissions categories']['fields']['name']['dbColumn'],
$category
);
} // if category not found
$query = sprintf( "insert into %s ( %s,%s,%s,%s ) values ( %s,%s,%s,%s )",
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['name']['dbColumn'],
$this->configuration['tables']['permissions']['fields']['description']['dbColumn'],
$this->configuration['tables']['permissions']['fields']['permission category']['dbColumn'],
$this->configuration['tables']['permissions']['fields']['default']['dbColumn'],
$this->escapeString($name),
$this->escapeString($description),
$this->escapeString($categoryID),
$defaultValue
);
$this->doSQL( $query );
$permissionsID = $this->getAField(
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions']['fields']['name']['dbColumn'],
$this->escapeString($name)
);
//insert into _users_permissions (_user_id,_permission_id,value) select _user_id,$permissionsID,$defaultValue from _users
} // addPermission
/**
* Sets any unset user permissions
*
* This will set any missing permissions which have a true value in the
* users permissions join table. This will NOT modify any existing records
* but will add new records if they do not exist in the table.
*
* @parameters string $user_id If set, will limit to only one user (not implemented)
*/
public function setUsersPermissions ( $user_id = null ) {
$query = sprintf(
"insert into %s (%s,%s,%s)
select
%s.%s,
%s.%s,
%s.%s
from
%s
join %s
where
%s.%s
and not exists
(
select 1
from %s
where
%s.%s = %s.%s
and %s.%s = %s.%s
)",
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['user_id']['dbColumn'],
$this->configuration['tables']['users permissions']['fields']['permission_id']['dbColumn'],
$this->configuration['tables']['users permissions']['fields']['value']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['default']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['default']['dbColumn'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['user_id']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['permission_id']['dbColumn'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id']
);
$this->doSQL($query, true );
} // setUsersPermissions
public function getARecord( $whereFields, $fieldList = null ) {
$id = isset( $whereFields['id'] ) ? $whereFields['id'] : null;
$result = parent::getARecord( $whereFields, $fieldList );
if ( $result && isset( $id ) ) {
$result['permissions'] = $this->getPermissions( $id );
}
return $result;
}
public function getPermissions ( $id ) {
$query = (
$id == -1 ?
"select name permission,default_value value from _permissions" :
"select permission,value from _view_users_permissions where users_id = $id"
);
$results = $this->doSQL( $query );
$values = $results->fetch_all(MYSQLI_ASSOC);
return array_column( $values, 'value', 'permission' );
}
public function getFullPermissions( $id ) {
$query = (
$id == -1 ?
"select _permission_id permission_id, _permissions.name permission, description, _permissions_categories.name category, default_value value from _permissions join _permissions_categories using (_permission_category_id)" :
"select permission_id,permission,description,category,value from _view_users_permissions where users_id = $id order by category,description"
);
$results = $this->doSQL( $query );
return $results->fetch_all(MYSQLI_ASSOC);
}
public function updatePermissions ( $userID,$newData ) {
$query = '';
foreach ( $newData as $key => $value ) {
$query = "insert into _permissions ( _user_id,_permission_id,value) select $userID, _permission_id, $value from _permissions where name = 'key' on duplicate key update value = $value";
$this->doSQL( $query, true );
}
} // update
}
?>