Compare commits
2 Commits
feature/TT
...
feature/TT
| Author | SHA1 | Date | |
|---|---|---|---|
|
c7f3dd212e | ||
|
|
20c2c665d4 |
@@ -1,46 +0,0 @@
|
||||
<?php
|
||||
|
||||
declare(strict_types=1);
|
||||
|
||||
namespace tests;
|
||||
|
||||
use PHPUnit\Framework\TestCase;
|
||||
|
||||
/**
|
||||
* Test class for DB\SqLite
|
||||
* This will only test the SqLite parts
|
||||
* @coversDefaultClass \CoreLibs\DB\SqLite
|
||||
* @testdox \CoreLibs\SqLite method tests for extended DB interface
|
||||
*/
|
||||
final class CoreLibsDBESqLiteTest extends TestCase
|
||||
{
|
||||
/**
|
||||
* Undocumented function
|
||||
*
|
||||
* @return void
|
||||
*/
|
||||
protected function setUp(): void
|
||||
{
|
||||
if (!extension_loaded('sqlite')) {
|
||||
$this->markTestSkipped(
|
||||
'The SqLite extension is not available.'
|
||||
);
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Undocumented function
|
||||
*
|
||||
* @testdox DB\SqLite Class tests
|
||||
*
|
||||
* @return void
|
||||
*/
|
||||
public function testSqLite()
|
||||
{
|
||||
$this->markTestIncomplete(
|
||||
'DB\SqLite Tests have not yet been implemented'
|
||||
);
|
||||
}
|
||||
}
|
||||
|
||||
// __END__
|
||||
@@ -1,157 +0,0 @@
|
||||
<?php // phpcs:ignore warning
|
||||
|
||||
/**
|
||||
* @phan-file-suppress PhanTypeSuspiciousStringExpression
|
||||
*/
|
||||
|
||||
declare(strict_types=1);
|
||||
|
||||
// turn on all error reporting
|
||||
error_reporting(E_ALL | E_STRICT | E_ERROR | E_WARNING | E_PARSE | E_COMPILE_ERROR);
|
||||
|
||||
ob_start();
|
||||
|
||||
// basic class test file
|
||||
define('USE_DATABASE', true);
|
||||
define('DATABASE', 'sqlite' . DIRECTORY_SEPARATOR);
|
||||
// sample config
|
||||
require 'config.php';
|
||||
// define log file id
|
||||
$LOG_FILE_ID = 'classTest-db';
|
||||
ob_end_flush();
|
||||
|
||||
$sql_file = BASE . MEDIA . DATABASE . "class_test.db.sqlite.sq3";
|
||||
|
||||
use CoreLibs\DB\SqLite;
|
||||
use CoreLibs\Debug\Support;
|
||||
use CoreLibs\Convert\SetVarType;
|
||||
|
||||
$log = new CoreLibs\Logging\Logging([
|
||||
'log_folder' => BASE . LOG,
|
||||
'log_file_id' => $LOG_FILE_ID,
|
||||
'log_per_date' => true,
|
||||
]);
|
||||
// db connection and attach logger
|
||||
$db = new CoreLibs\DB\SqLite($log, "sqlite:" . $sql_file);
|
||||
$db->log->debug('START', '=============================>');
|
||||
|
||||
$PAGE_NAME = 'TEST CLASS: DB: SqLite';
|
||||
print "<!DOCTYPE html>";
|
||||
print "<html><head><title>" . $PAGE_NAME . "</title></head>";
|
||||
print "<body>";
|
||||
print '<div><a href="class_test.php">Class Test Master</a></div>';
|
||||
|
||||
print "<hr>";
|
||||
|
||||
echo "Create Tables on demand<br>";
|
||||
|
||||
$query = <<<SQL
|
||||
CREATE TABLE IF NOT EXISTS test (
|
||||
test_id INTEGER PRIMARY KEY,
|
||||
c_text TEXT,
|
||||
c_integer INTEGER,
|
||||
c_integer_default INTEGER DEFAULT -1,
|
||||
c_bool BOOLEAN,
|
||||
c_datetime TEXT,
|
||||
c_datetime_microseconds TEXT,
|
||||
c_datetime_default TEXT DEFAULT CURRENT_TIMESTAMP,
|
||||
c_date TEXT,
|
||||
c_julian REAL,
|
||||
c_unixtime DATETIME,
|
||||
c_unixtime_alt DATETIME,
|
||||
c_numeric NUMERIC,
|
||||
c_real REAL,
|
||||
c_blob
|
||||
)
|
||||
SQL;
|
||||
$db->dbExec($query);
|
||||
// **********************
|
||||
$query = <<<SQL
|
||||
CREATE TABLE IF NOT EXISTS test_no_pk (
|
||||
c_text TEXT,
|
||||
c_integer INTEGER
|
||||
)
|
||||
SQL;
|
||||
$db->dbExec($query);
|
||||
|
||||
print "<hr>";
|
||||
|
||||
$table = 'test';
|
||||
echo "Table info for: " . $table . "<br>";
|
||||
|
||||
if (($table_info = $db->dbShowTableMetaData($table)) === false) {
|
||||
print "Read problem for: $table<br>";
|
||||
} else {
|
||||
print "TABLE INFO: <pre>" . print_r($table_info, true) . "</pre><br>";
|
||||
}
|
||||
|
||||
print "<hr>";
|
||||
|
||||
echo "Insert into 'test'<br>";
|
||||
|
||||
$query = <<<SQL
|
||||
INSERT INTO test (
|
||||
c_text, c_integer, c_bool,
|
||||
c_datetime, c_datetime_microseconds, c_date,
|
||||
c_julian, c_unixtime, c_unixtime_alt,
|
||||
c_numeric, c_real, c_blob
|
||||
) VALUES (
|
||||
?, ?, ?,
|
||||
?, ?, ?,
|
||||
julianday(?), ?, unixepoch(?),
|
||||
?, ?, ?
|
||||
)
|
||||
SQL;
|
||||
$db->dbExecParams($query, [
|
||||
'test', rand(1, 100), true,
|
||||
date('Y-m-d H:i:s'), date_format(date_create("now"), 'Y-m-d H:i:s.u'), date('Y-m-d'),
|
||||
// julianday pass through
|
||||
date('Y-m-d H:i:s'),
|
||||
// use "U" if no unixepoch in query
|
||||
date('U'), date('Y-m-d H:i:s'),
|
||||
1.5, 10.5, 'Anything'
|
||||
]);
|
||||
|
||||
print "<hr>";
|
||||
|
||||
echo "Insert into 'test_no_pk'<br>";
|
||||
|
||||
$query = <<<SQL
|
||||
INSERT INTO test_no_pk (
|
||||
c_text, c_integer
|
||||
) VALUES (
|
||||
?, ?
|
||||
)
|
||||
SQL;
|
||||
$db->dbExecParams($query, ['test no pk', rand(100, 200)]);
|
||||
|
||||
print "<hr>";
|
||||
|
||||
$query = <<<SQL
|
||||
SELECT test_id, c_text, c_integer, c_integer_default, c_datetime_default
|
||||
FROM test
|
||||
SQL;
|
||||
while (is_array($row = $db->dbReturnArray($query))) {
|
||||
print "ROW: PK(test_id): " . $row["test_id"]
|
||||
. ", Text: " . $row["c_text"] . ", Int: " . $row["c_integer"]
|
||||
. ", Int Default: " . $row["c_integer_default"]
|
||||
. ", Date Default: " . $row["c_datetime_default"]
|
||||
. "<br>";
|
||||
}
|
||||
|
||||
echo "<hr>";
|
||||
|
||||
$query = <<<SQL
|
||||
SELECT rowid, c_text, c_integer
|
||||
FROM test_no_pk
|
||||
SQL;
|
||||
|
||||
while (is_array($row = $db->dbReturnArray($query))) {
|
||||
print "ROW[CURSOR]: PK(rowid): " . $row["rowid"]
|
||||
. ", Text: " . $row["c_text"] . ", Int: " . $row["c_integer"]
|
||||
. "<br>";
|
||||
}
|
||||
|
||||
print "</body></html>";
|
||||
|
||||
// __END__
|
||||
@@ -197,8 +197,10 @@ class Login
|
||||
// login html, if we are on an ajax page
|
||||
/** @var string|null */
|
||||
private ?string $login_html = '';
|
||||
/** @var bool */
|
||||
/** @var bool flag set on run */
|
||||
private bool $login_is_ajax_page = false;
|
||||
/** @var bool flag set on load */
|
||||
private bool $login_is_ajax_page_option = false;
|
||||
|
||||
// logging
|
||||
/** @var array<string> list of allowed types for edit log write */
|
||||
@@ -268,8 +270,6 @@ class Login
|
||||
}
|
||||
// init error array
|
||||
$this->loginInitErrorMessages();
|
||||
// acess right list
|
||||
$this->loginLoadAccessRightList();
|
||||
// log allowed write flags
|
||||
$this->loginSetEditLogWriteTypeAvailable();
|
||||
|
||||
@@ -342,6 +342,7 @@ class Login
|
||||
* locale_path <string>: absolue path to the locale folder
|
||||
* site_locale <string>: what locale to load
|
||||
* site_domain <string>: what domain (locale file name) to use
|
||||
* ajax_page <bool>: if we are loading from an AJAX page (eg backend)
|
||||
*
|
||||
* @param array<string,mixed> $options Options array from class load
|
||||
* @return bool True on ok, False on failure
|
||||
@@ -361,6 +362,15 @@ class Login
|
||||
$options['debug'] = false;
|
||||
}
|
||||
|
||||
// AUTO LOGIN
|
||||
if (
|
||||
!isset($options['ajax_page']) ||
|
||||
!is_bool($options['ajax_page'])
|
||||
) {
|
||||
$options['ajax_page'] = false;
|
||||
}
|
||||
$this->login_is_ajax_page_option = $options['ajax_page'];
|
||||
|
||||
// AUTO LOGIN
|
||||
if (
|
||||
!isset($options['auto_login']) ||
|
||||
@@ -691,6 +701,34 @@ class Login
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* get the default ACL list type
|
||||
* if not set loads it from DB
|
||||
*
|
||||
* @return array<string,int>
|
||||
*/
|
||||
private function loginGetAccessRightListType(): array
|
||||
{
|
||||
if (empty($this->default_acl_list_type)) {
|
||||
$this->loginLoadAccessRightList();
|
||||
}
|
||||
return $this->default_acl_list_type;
|
||||
}
|
||||
|
||||
/**
|
||||
* get the default ACL list
|
||||
* if not set loads from DB
|
||||
*
|
||||
* @return array<string|int, mixed>
|
||||
*/
|
||||
private function loginGetAccessRightList(): array
|
||||
{
|
||||
if (empty($this->default_acl_list)) {
|
||||
$this->loginLoadAccessRightList();
|
||||
}
|
||||
return $this->default_acl_list;
|
||||
}
|
||||
|
||||
/**
|
||||
* Improves the application's security over HTTP(S) by setting specific headers
|
||||
*
|
||||
@@ -1540,6 +1578,10 @@ class Login
|
||||
$this->acl['unit'] = [];
|
||||
$this->acl['unit_legacy'] = [];
|
||||
$this->acl['unit_detail'] = [];
|
||||
// integrate the type acl list, but only for the keyword -> level
|
||||
$this->acl['min'] = $this->loginGetAccessRightListType();
|
||||
// set the full acl list too (lookup level number and get level data)
|
||||
$this->acl['acl_list'] = $this->loginGetAccessRightList();
|
||||
|
||||
// PER ACCOUNT (UNIT/edit access)->
|
||||
foreach ($_SESSION['LOGIN_UNIT'] as $ea_cuid => $unit) {
|
||||
@@ -1561,7 +1603,7 @@ class Login
|
||||
'name' => $unit['name'],
|
||||
'uid' => $unit['uid'],
|
||||
'cuuid' => $unit['cuuid'],
|
||||
'level' => $this->default_acl_list[$this->acl['unit'][$ea_cuid]]['name'] ?? -1,
|
||||
'level' => $this->acl['acl_list'][$this->acl['unit'][$ea_cuid]]['name'] ?? -1,
|
||||
'level_number' => $this->acl['unit'][$ea_cuid],
|
||||
'default' => $unit['default'],
|
||||
'data' => $unit['data'],
|
||||
@@ -1582,10 +1624,6 @@ class Login
|
||||
}
|
||||
// set the default edit access
|
||||
$this->acl['default_edit_access'] = $_SESSION['LOGIN_UNIT_DEFAULT_EACUID'];
|
||||
// integrate the type acl list, but only for the keyword -> level
|
||||
$this->acl['min'] = $this->default_acl_list_type;
|
||||
// set the full acl list too (lookup level number and get level data)
|
||||
$this->acl['acl_list'] = $this->default_acl_list;
|
||||
// debug
|
||||
// $this->debug('ACL', $this->print_ar($this->acl));
|
||||
}
|
||||
@@ -2519,7 +2557,12 @@ HTML;
|
||||
// or need to pass it back
|
||||
// to the continue AJAX class for output back to the user
|
||||
$this->login_is_ajax_page = false;
|
||||
if ($ajax_page === true || !empty($GLOBALS['AJAX_PAGE'])) {
|
||||
if (
|
||||
$ajax_page === true ||
|
||||
$this->login_is_ajax_page_option == true ||
|
||||
// this is deprecated
|
||||
!empty($GLOBALS['AJAX_PAGE'])
|
||||
) {
|
||||
$this->login_is_ajax_page = true;
|
||||
}
|
||||
|
||||
@@ -3147,6 +3190,8 @@ HTML;
|
||||
*/
|
||||
public function loginGetAclList(?int $level = null): array
|
||||
{
|
||||
// make sure it is loaded
|
||||
$this->loginGetAccessRightList();
|
||||
// if no level given, return full list
|
||||
if (empty($level)) {
|
||||
return $this->default_acl_list;
|
||||
@@ -3169,6 +3214,9 @@ HTML;
|
||||
*/
|
||||
public function loginGetAclListFromType(string $type): int|bool
|
||||
{
|
||||
// make sure it is loaded
|
||||
$this->loginGetAccessRightListType();
|
||||
// if not et return false
|
||||
if (!isset($this->default_acl_list_type[$type])) {
|
||||
return false;
|
||||
}
|
||||
|
||||
@@ -1,90 +0,0 @@
|
||||
<?php
|
||||
|
||||
/**
|
||||
* AUTHOR: Clemens Schwaighofer
|
||||
* CREATED: Ymd
|
||||
* DESCRIPTION:
|
||||
* DescriptionHere
|
||||
*/
|
||||
|
||||
declare(strict_types=1);
|
||||
|
||||
namespace CoreLibs\DB\Interface;
|
||||
|
||||
interface DatabaseInterface
|
||||
{
|
||||
/**
|
||||
* Table meta data
|
||||
* Note that if columns have multi
|
||||
*
|
||||
* @param string $table
|
||||
* @return array<array<string,mixed>>|false
|
||||
*/
|
||||
public function dbShowTableMetaData(string $table): array|false;
|
||||
|
||||
/**
|
||||
* for reading or simple execution, no return data
|
||||
*
|
||||
* @param string $query
|
||||
* @return int|false
|
||||
*/
|
||||
public function dbExec(string $query): int|false;
|
||||
|
||||
/**
|
||||
* Run a simple query and return its statement
|
||||
*
|
||||
* @param string $query
|
||||
* @return \PDOStatement|false
|
||||
*/
|
||||
public function dbQuery(string $query): \PDOStatement|false;
|
||||
|
||||
/**
|
||||
* Execute one query with params
|
||||
*
|
||||
* @param string $query
|
||||
* @param array<mixed> $params
|
||||
* @return \PDOStatement|false
|
||||
*/
|
||||
public function dbExecParams(string $query, array $params): \PDOStatement|false;
|
||||
|
||||
/**
|
||||
* Prepare query
|
||||
*
|
||||
* @param string $query
|
||||
* @return \PDOStatement|false
|
||||
*/
|
||||
public function dbPrepare(string $query): \PDOStatement|false;
|
||||
|
||||
/**
|
||||
* execute a cursor
|
||||
*
|
||||
* @param \PDOStatement $cursor
|
||||
* @param array<mixed> $params
|
||||
* @return bool
|
||||
*/
|
||||
public function dbCursorExecute(\PDOStatement $cursor, array $params): bool;
|
||||
|
||||
/**
|
||||
* return array with data, when finshed return false
|
||||
* also returns false on error
|
||||
*
|
||||
* TODO: This is currently a one time run
|
||||
* if the same query needs to be run again, the cursor_ext must be reest
|
||||
* with dbCacheReset
|
||||
*
|
||||
* @param string $query
|
||||
* @param array<mixed> $params
|
||||
* @return array<mixed>|false
|
||||
*/
|
||||
public function dbReturnArray(string $query, array $params = []): array|false;
|
||||
|
||||
/**
|
||||
* get current db handler
|
||||
* this is for raw access
|
||||
*
|
||||
* @return \PDO
|
||||
*/
|
||||
public function getDbh(): \PDO;
|
||||
}
|
||||
|
||||
// __END__
|
||||
@@ -1,432 +0,0 @@
|
||||
<?php
|
||||
|
||||
/**
|
||||
* AUTHOR: Clemens Schwaighofer
|
||||
* CREATED: 2024/8/21
|
||||
* DESCRIPTION:
|
||||
* SQL Lite interface
|
||||
* Note: This is a very simple library and in future should perhaps merge with the master
|
||||
* CoreLibs SQL interface
|
||||
*
|
||||
* TODO: This should move to the CoreLibs\DB\IO class as a sub type for "sqlite" next to "pgsql"
|
||||
*/
|
||||
|
||||
declare(strict_types=1);
|
||||
|
||||
namespace CoreLibs\DB;
|
||||
|
||||
use CoreLibs\Create\Hash;
|
||||
|
||||
class SqLite implements Interface\DatabaseInterface
|
||||
{
|
||||
/** @var \CoreLibs\Logging\Logging logging */
|
||||
public \CoreLibs\Logging\Logging $log;
|
||||
|
||||
/** @var string database connection string */
|
||||
private string $dsn;
|
||||
/** @var \PDO database handler */
|
||||
private \PDO $dbh;
|
||||
/** @var PDOStatement|false one cursor, for internal handling */
|
||||
// private \PDOStatement|false $cursor;
|
||||
/** @var array<string,mixed> extended cursoers string index with content */
|
||||
private array $cursor_ext = [];
|
||||
|
||||
/**
|
||||
* init database system
|
||||
*
|
||||
* @param \CoreLibs\Logging\Logging $log
|
||||
* @param string $dsn
|
||||
*/
|
||||
public function __construct(
|
||||
\CoreLibs\Logging\Logging $log,
|
||||
string $dsn
|
||||
) {
|
||||
$this->log = $log;
|
||||
// open new connection
|
||||
if ($this->__connectToDB($dsn) === false) {
|
||||
throw new \ErrorException("Cannot load database: " . $dsn, 1);
|
||||
}
|
||||
}
|
||||
|
||||
// *********************************************************************
|
||||
// MARK: PRIVATE METHODS
|
||||
// *********************************************************************
|
||||
|
||||
/**
|
||||
* Get a cursor dump with all info
|
||||
*
|
||||
* @param \PDOStatement $cursor
|
||||
* @return string|false
|
||||
*/
|
||||
private function __dbGetCursorDump(\PDOStatement $cursor): string|false
|
||||
{
|
||||
// get the cursor info
|
||||
ob_start();
|
||||
$cursor->debugDumpParams();
|
||||
$cursor_dump = ob_get_contents();
|
||||
ob_end_clean();
|
||||
return $cursor_dump;
|
||||
}
|
||||
|
||||
/**
|
||||
* fetch rows from a cursor (post execute)
|
||||
*
|
||||
* @param \PDOStatement $cursor
|
||||
* @return array<mixed>|false
|
||||
*/
|
||||
private function __dbFetchArray(\PDOStatement $cursor): array|false
|
||||
{
|
||||
try {
|
||||
// on empty array return false
|
||||
// TODO make that more elegant?
|
||||
return empty($row = $cursor->fetch(mode:\PDO::FETCH_NAMED)) ? false : $row;
|
||||
} catch (\PDOException $e) {
|
||||
$this->log->error(
|
||||
"Cannot fetch from cursor",
|
||||
[
|
||||
"dsn" => $this->dsn,
|
||||
"DumpParams" => $this->__dbGetCursorDump($cursor),
|
||||
"PDOException" => $e
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
}
|
||||
|
||||
// MARK: open database
|
||||
|
||||
/**
|
||||
* Open database
|
||||
* reports errors for wrong DSN or failed connection
|
||||
*
|
||||
* @param string $dsn
|
||||
* @return bool
|
||||
*/
|
||||
private function __connectToDB(string $dsn): bool
|
||||
{
|
||||
// check if dsn starts with ":"
|
||||
if (!str_starts_with($dsn, "sqlite:")) {
|
||||
$this->log->error(
|
||||
"Invalid dsn string",
|
||||
[
|
||||
"dsn" => $dsn
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
// TODO: if not ":memory:" check if path to file is writeable by system
|
||||
// avoid double open
|
||||
if (!empty($this->dsn) && $dsn == $this->dsn && $this->dbh instanceof \PDO) {
|
||||
$this->log->info(
|
||||
"Connection already establisehd with this dsn",
|
||||
[
|
||||
"dsn" => $dsn,
|
||||
]
|
||||
);
|
||||
return true;
|
||||
}
|
||||
// TODO: check that folder is writeable
|
||||
// set DSN and open connection
|
||||
$this->dsn = $dsn;
|
||||
try {
|
||||
$this->dbh = new \PDO($this->dsn);
|
||||
} catch (\PDOException $e) {
|
||||
$this->log->error(
|
||||
"Cannot open database",
|
||||
[
|
||||
"dsn" => $this->dsn,
|
||||
"PDOException" => $e
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
return true;
|
||||
}
|
||||
|
||||
// *********************************************************************
|
||||
// MARK: PUBLIC METHODS
|
||||
// *********************************************************************
|
||||
|
||||
// MARK: db meta data (table info)
|
||||
|
||||
/**
|
||||
* Table meta data
|
||||
* Note that if columns have multi
|
||||
*
|
||||
* @param string $table
|
||||
* @return array<array<string,mixed>>|false
|
||||
*/
|
||||
public function dbShowTableMetaData(string $table): array|false
|
||||
{
|
||||
$table_info = [];
|
||||
$query = <<<SQL
|
||||
SELECT
|
||||
ti.cid, ti.name, ti.type, ti.'notnull', ti.dflt_value, ti.pk,
|
||||
il_ii.idx_name, il_ii.idx_unique, il_ii.idx_origin, il_ii.idx_partial
|
||||
FROM
|
||||
sqlite_schema AS m,
|
||||
pragma_table_info(m.name) AS ti
|
||||
LEFT JOIN (
|
||||
SELECT
|
||||
il.name AS idx_name, il.'unique' AS idx_unique, il.origin AS idx_origin, il.partial AS idx_partial,
|
||||
ii.cid AS tbl_cid
|
||||
FROM
|
||||
sqlite_schema AS m,
|
||||
pragma_index_list(m.name) AS il,
|
||||
pragma_index_info(il.name) AS ii
|
||||
WHERE m.name = ?1
|
||||
) AS il_ii ON (ti.cid = il_ii.tbl_cid)
|
||||
WHERE
|
||||
m.name = ?1
|
||||
SQL;
|
||||
while (is_array($row = $this->dbReturnArray($query, [$table]))) {
|
||||
$table_info[] = [
|
||||
'cid' => $row['cid'],
|
||||
'name' => $row['name'],
|
||||
'type' => $row['type'],
|
||||
'notnull' => $row['notnull'],
|
||||
'dflt_value' => $row['dflt_value'],
|
||||
'pk' => $row['pk'],
|
||||
'idx_name' => $row['idx_name'],
|
||||
'idx_unique' => $row['idx_unique'],
|
||||
'idx_origin' => $row['idx_origin'],
|
||||
'idx_partial' => $row['idx_partial'],
|
||||
];
|
||||
}
|
||||
|
||||
if (!$table_info) {
|
||||
return false;
|
||||
}
|
||||
return $table_info;
|
||||
}
|
||||
|
||||
// MARK: db exec
|
||||
|
||||
/**
|
||||
* for reading or simple execution, no return data
|
||||
*
|
||||
* @param string $query
|
||||
* @return int|false
|
||||
*/
|
||||
public function dbExec(string $query): int|false
|
||||
{
|
||||
try {
|
||||
return $this->dbh->exec($query);
|
||||
} catch (\PDOException $e) {
|
||||
$this->log->error(
|
||||
"Cannot execute query",
|
||||
[
|
||||
"dsn" => $this->dsn,
|
||||
"query" => $query,
|
||||
"PDOException" => $e
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
}
|
||||
|
||||
// MARK: db query
|
||||
|
||||
/**
|
||||
* Run a simple query and return its statement
|
||||
*
|
||||
* @param string $query
|
||||
* @return \PDOStatement|false
|
||||
*/
|
||||
public function dbQuery(string $query): \PDOStatement|false
|
||||
{
|
||||
try {
|
||||
return $this->dbh->query($query, \PDO::FETCH_NAMED);
|
||||
} catch (\PDOException $e) {
|
||||
$this->log->error(
|
||||
"Cannot run query",
|
||||
[
|
||||
"dsn" => $this->dsn,
|
||||
"query" => $query,
|
||||
"PDOException" => $e
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
}
|
||||
|
||||
// MARK: db prepare & execute calls
|
||||
|
||||
/**
|
||||
* Execute one query with params
|
||||
*
|
||||
* @param string $query
|
||||
* @param array<mixed> $params
|
||||
* @return \PDOStatement|false
|
||||
*/
|
||||
public function dbExecParams(string $query, array $params): \PDOStatement|false
|
||||
{
|
||||
// prepare query
|
||||
if (($cursor = $this->dbPrepare($query)) === false) {
|
||||
return false;
|
||||
}
|
||||
// execute the query, on failure return false
|
||||
if ($this->dbCursorExecute($cursor, $params) === false) {
|
||||
return false;
|
||||
}
|
||||
return $cursor;
|
||||
}
|
||||
|
||||
/**
|
||||
* Prepare query
|
||||
*
|
||||
* @param string $query
|
||||
* @return \PDOStatement|false
|
||||
*/
|
||||
public function dbPrepare(string $query): \PDOStatement|false
|
||||
{
|
||||
try {
|
||||
// store query with cursor so we can reference?
|
||||
return $this->dbh->prepare($query);
|
||||
} catch (\PDOException $e) {
|
||||
$this->log->error(
|
||||
"Cannot open cursor",
|
||||
[
|
||||
"dsn" => $this->dsn,
|
||||
"query" => $query,
|
||||
"PDOException" => $e
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* execute a cursor
|
||||
*
|
||||
* @param \PDOStatement $cursor
|
||||
* @param array<mixed> $params
|
||||
* @return bool
|
||||
*/
|
||||
public function dbCursorExecute(\PDOStatement $cursor, array $params): bool
|
||||
{
|
||||
try {
|
||||
return $cursor->execute($params);
|
||||
} catch (\PDOException $e) {
|
||||
// write error log
|
||||
$this->log->error(
|
||||
"Cannot execute prepared query",
|
||||
[
|
||||
"dsn" => $this->dsn,
|
||||
"params" => $params,
|
||||
"DumpParams" => $this->__dbGetCursorDump($cursor),
|
||||
"PDOException" => $e
|
||||
]
|
||||
);
|
||||
return false;
|
||||
}
|
||||
}
|
||||
|
||||
// MARK: db return array
|
||||
|
||||
/**
|
||||
* Returns hash for query
|
||||
* Hash is used in all internal storage systems for return data
|
||||
*
|
||||
* @param string $query The query to create the hash from
|
||||
* @param array<mixed> $params If the query is params type we need params
|
||||
* data to create a unique call one, optional
|
||||
* @return string Hash, as set by hash long
|
||||
*/
|
||||
public function dbGetQueryHash(string $query, array $params = []): string
|
||||
{
|
||||
return Hash::__hashLong(
|
||||
$query . (
|
||||
$params !== [] ?
|
||||
'#' . json_encode($params) : ''
|
||||
)
|
||||
);
|
||||
}
|
||||
|
||||
/**
|
||||
* resets all data stored to this query
|
||||
* @param string $query The Query whose cache should be cleaned
|
||||
* @param array<mixed> $params If the query is params type we need params
|
||||
* data to create a unique call one, optional
|
||||
* @return bool False if query not found, true if success
|
||||
*/
|
||||
public function dbCacheReset(string $query, array $params = []): bool
|
||||
{
|
||||
$query_hash = $this->dbGetQueryHash($query, $params);
|
||||
// clears cache for this query
|
||||
if (empty($this->cursor_ext[$query_hash]['query'])) {
|
||||
$this->log->error('Cannot reset cursor_ext with given query and params', [
|
||||
"query" => $query,
|
||||
"params" => $params,
|
||||
]);
|
||||
return false;
|
||||
}
|
||||
unset($this->cursor_ext[$query_hash]);
|
||||
return true;
|
||||
}
|
||||
|
||||
/**
|
||||
* return array with data, when finshed return false
|
||||
* also returns false on error
|
||||
*
|
||||
* TODO: This is currently a one time run
|
||||
* if the same query needs to be run again, the cursor_ext must be reest
|
||||
* with dbCacheReset
|
||||
*
|
||||
* @param string $query
|
||||
* @param array<mixed> $params
|
||||
* @return array<mixed>|false
|
||||
*/
|
||||
public function dbReturnArray(string $query, array $params = []): array|false
|
||||
{
|
||||
$query_hash = $this->dbGetQueryHash($query, $params);
|
||||
if (!isset($this->cursor_ext[$query_hash])) {
|
||||
$this->cursor_ext[$query_hash] = [
|
||||
// cursor null: unset, if set \PDOStatement
|
||||
'cursor' => null,
|
||||
// the query used in this call
|
||||
'query' => $query,
|
||||
// parameter
|
||||
'params' => $params,
|
||||
// how many rows have been read from db
|
||||
'read_rows' => 0,
|
||||
// when fetch array or cache read returns false
|
||||
// in loop read that means dbReturn retuns false without error
|
||||
'finished' => false,
|
||||
];
|
||||
if (!empty($params)) {
|
||||
if (($cursor = $this->dbExecParams($query, $params)) === false) {
|
||||
return false;
|
||||
}
|
||||
} else {
|
||||
if (($cursor = $this->dbQuery($query)) === false) {
|
||||
return false;
|
||||
}
|
||||
}
|
||||
$this->cursor_ext[$query_hash]['cursor'] = $cursor;
|
||||
}
|
||||
// flag finished if row is false
|
||||
$row = $this->__dbFetchArray($this->cursor_ext[$query_hash]['cursor']);
|
||||
if ($row === false) {
|
||||
$this->cursor_ext[$query_hash]['finished'] = true;
|
||||
} else {
|
||||
$this->cursor_ext[$query_hash]['read_rows']++;
|
||||
}
|
||||
return $row;
|
||||
}
|
||||
|
||||
// MARK other interface
|
||||
|
||||
/**
|
||||
* get current db handler
|
||||
* this is for raw access
|
||||
*
|
||||
* @return \PDO
|
||||
*/
|
||||
public function getDbh(): \PDO
|
||||
{
|
||||
return $this->dbh;
|
||||
}
|
||||
}
|
||||
|
||||
// __END__
|
||||
Reference in New Issue
Block a user