Here it is:
final class Database extends PDO {
private
$authorizations = [],
$queries = [];
// START PUBLIC OVERLOADS
public function __construct(
$dsn,
$username,
$password,
$options = [],
$tablePrefix = false
) {
if (defined('DB_CONSTRUCTED')) Bomb::paladin(
'HACKING ATTEMPT DETECTED',
'Attempt to create "Database" more than once'
);
define('DB_CONSTRUCTED', 1);
$options[PDO::ATTR_STATEMENT_CLASS] = [ 'DatabaseStatement', [ $this ] ];
try {
parent::__construct($dsn, $username, $password, $options);
} catch (PDOException $e) {
Bomb::paladin('Database Connection Error', $e );
}
define('DB_TABLE_PREFIX', $tablePrefix ? $tablePrefix . '_' : '');
define('DB_QUERY_DIR', 'database/' . SQL_ENGINE . '/queries/');
} // Database::__construct
public function exec($name, $module = 'common', $tableName = false) {
try {
$stmt = parent::exec($this->namedQuery($name, $module, $tableName));
} catch (PDOException $e) {
Bomb::paladin(
'dbExecError', [ $name, $module, $e->getMessage() ]
);
}
if ($this->errorCode() > 0) Bomb::paladin(
'dbExecError', [ $name, $module, $stmt->errorInfo()[2] ]
);
return $stmt;
} // Database::exec
public function prepare($name, $module = 'common', $tableName = false) {
try {
$stmt = parent::prepare($this->namedQuery($name, $module, $tableName));
} catch (PDOException $e) {
Bomb::paladin(
'dbExecError', [ $name, $module, $e->getMessage() ]
);
}
if ($this->errorCode() > 0) Bomb::paladin(
'dbPrepareError', [ $name, $module, $stmt->errorInfo()[2] ]
);
return $stmt;
} // Database::prepare
public function query($name, $module = 'common', $tableName = false) {
try {
$stmt = parent::query($this->namedQuery($name, $module, $tableName));
} catch (PDOException $e) {
Bomb::paladin(
'dbExecError', [ $name, $module, $e->getMessage() ]
);
}
if ($this->errorCode() > 0) Bomb::paladin(
'dbQueryError', [ $name, $module, $stmt->errorInfo()[2] ]
);
return $stmt;
} // Database::query
// END PUBLIC OVERLOADS
// START PUBLIC EXTENSIONS
public function prepExec($data = [], $name, $module = 'common', $tableName = false) {
$stmt = $this->prepare($name, $module, $tableName);
$stmt->execute($data);
return $stmt;
} // Database::prepExec
public function rowCount($tableName) {
$stmt = $this->query('rowCount', 'common', $tableName);
return $result->fetchColumn();
} // Database::rowCount
public function safeName($name) {
return $name = preg_replace('/[^a-z0-9_]/', '', $name);
} // Database::safeName
public function tableExists($name) {
$stmt = $this->query('tableExists', 'common', $name);
return $stmt->fetch();
} // Database::tableExists
// END PUBLIC EXTENSIONS
// START PRIVATE EXTENSIONS
private function namedQuery($name, $module = "common", $tableName = false) {
if (!$this->auth($name, $module)) Bomb::paladin(
'unauthorizedQuery', [ $name, $module ]
);
if (!array_key_exists($module, $this->queries)) {
$this->queries[$module] = file_exists(
$fn = DB_QUERY_DIR . $module . '/' . $module . '.queries.ini.php'
) ? parse_ini_file($fn) : [];
}
if (!array_key_exists($name, $this->queries[$module])) {
if (file_exists(
$fn = DB_QUERY_DIR . $module . '/' . $module . '.' . $name . '.sql'
)) $this->queries[$module][$name] = file_get_contents($fn);
else Bomb::paladin('queryNotFound', [ $name, $module ]);
}
$query = str_replace(
'!PREFIX!',
DB_TABLE_PREFIX,
$this->queries[$module][$name]
);
if ($tableName) {
if ($this->safeName($name)) {
$query = str_replace('!TABLE!', $tableName, $query);
} else Bomb::paladin('invalidTableName', [ $tableName ]);
}
return $query;
} // Database::namedQuery
private function auth($name, $module) {
if ($module === 'common') return true;
if (!array_key_exists($module, $this->authorizations)) {
if (file_exists(
$fn = DB_QUERY_DIR . $module . '/' . $module . '.auth.ini.php'
)) $this->authorizations[$module] = parse_ini_file($fn);
else return true;
}
if (
array_key_exists('ALL', $this->authorizations[$module]) &&
$this->authorizations[$module]['ALL'] === 0
) return $module === ACTION;
)
return (
array_key_exists($name, $this->authorizations[$module]) &&
$this->authorizations[$module][$name] !== 0
);
} // Database::auth
// END PUBLIC EXTENSIONS
} // Database
I extend PDO rather than make a new class, because we want to prevent conventional calls as cleanly as possible.
We have two private variables for caching. $authorizations is for the auth check, $queries is for query strings.
We overload the contstructor to add our auto table prefix support, to automatically add our try/catch on the parent to Bomb if need be, to prevent Database from being initialized more than once (which should NOT be done), and to set up DEFINE of non-security related immutables. As the old joke goes use define a lot. NO, A LOT!!!
Let's get into some of the tricky stuff.
$options[PDO::ATTR_STATEMENT_CLASS] = [ 'DatabaseStatement', [ $this ] ];
This overrides the default PDOStatement to use our own extension of it. I create this extension so that
1) PDOSTATEMENT::$pdo is PRIVATE!
2) We can "lock" statements to prevent further "execute". This is handy if we end up passing the PDOStatement to the template logic to reduce the memory footprint that ->fetchAll can induce.
It's amazing how many systems go bits-up on that
DatabaseStatement going like this:
class DatabaseStatement extends PDOStatement {
private
$pdo,
$locked = false;
protected final function __construct($pdo) {
$this->pdo = $pdo;
} // DatabaseStatement:__construct
public final function execute($input_parameters = Null) {
if ($this->locked) return false;
return parent::execute($input_parameters);
} // DatabaseStatement::execute
public final function lock() {
$this->locked = true;
}
} // DatabaseStatement
Pretty simple stuff. Why they don't offer locking out of box is beyond me. Just begs for a accidental re-use.
I'm gonna go through "Database"'s methods out of code order, as they're ordered by public/private and then by name... not by functionality/dependency. I prefer the latter, but this isn't about me.
PRIVATE EXTENSIONS
private function namedQuery($name, $module = "common", $tableName = false)
This pulls our named queries from the /sql/sqlengine/queries directory. First we do an authorization check, and if that fails we bomb. (we'll cover auth next).
If the module is not in the $this->queries cache we see if a module .queries.ini.php file exists, and load it. If it's not set and the file doesn't exist, make it an empty array.
Next if the query name doesn't exist in the cache, we see if there's a .sql file specific to that one query. If so, load it. Otherwise bomb as the named query doesn't exist.
We get past that we plug in our !PREFIX! and !TABLE! bombing if $tableName contains anything other than [a-z0-9)]. Again, notice no uppercase allowed!
We get past that without bombing, $query is ready to be returned to the caller.
private function auth($name, $module)
The query authorization function for checking if ACTION is allowed isn't too tricky. "common" are always authorized so short-circuit out for those first.
If the module doesn't exist, see if there's a module file and load it. If there is no authorization file, we'll assume AUTHORIZED.
If "ALL" is set and is zero, it means nothing is authorized except ACTION.
Finally if the $name is set and it's non-zero, return true.
PUBLIC EXTENSIONS
public function prepExec($data = [], $name, $module = 'common', $tableName = false)
Simple function to do a prepare and an exec in one pass.
public function rowCount($tableName)
Can you guess what this does? Tells you how many tables a row has.
public function safeName($name)
Returns boolean if any characers other than a-z0-9_ are present in the passed string.
public function tableExists($name)
Again, aren't good naming conventions a blessing?
PUBLIC OVERLOADS
public function exec($name, $module = 'common', $tableName = false)
public function prepare($name, $module = 'common', $tableName = false)
public function query($name, $module = 'common', $tableName = false)
Kissing cousins, they all just call ->namedQuery to get the proper query string, and Bombs if there's an error. They work for now, but could really use some DRY loving.
... and that's basically the database code. Untested, might have typo's or flubs as this is a clean-room re implementation of what I usually do. Case testing and creating the installer go hand in hand... because there's noplace better to test it than with real operations.