gramps/namaste
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
namaste/classes/gacPDO.class.inc
gramps 373ebc8c93 Archive: Namaste PHP AMQP framework v1.0 (2017-2020) 
952 days continuous production uptime, 40k+ tp/s single node.
Original corpo Bitbucket history not included — clean archive commit.
2026-04-05 09:49:30 -07:00

3880 lines
177 KiB
PHP
Raw Permalink Blame History

<?php
/**
* gacPDO.class -- instantiation class
*
* -- should only be invoked by gacFactory when instantiating a new class
* -- extends gaaNamasteCore
*
* Used for database access to mariaDB (mysql) at time of creation - however, being a PDO based class, we can use
* it for all of the PDO-supported databases. Plus way-better handling of dynamic (run-time) prepared queries.
*
* Note:
* -----
* Resource allocation (connecting to the db) is handled by the gasResourceManager class.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* HISTORY:
* ========
* 09-13-17 mks CORE-561: original coding begins
* 03-02-18 mks CORE-680: deprecated trace logging
* 03-23-18 mks CORE-852: migration support
* 08-02-18 mks CORE-774: PHP7.2 exception handling
* 01-08-20 mks DB-150: PHP7.4 class member type-casting
*
*/
class gacPDO extends gaaNamasteCore
{
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/// CLASS PROPERTIES ///
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
private ?object $dbcMain; // destructive database connection/resource
private ?object $dbcSlave; // non-destructive database connection/resource
private string $res; // console log output unique identifier/label
private bool $limitOverride; // used ad-hoc to override the system limit on query returns
private string $env; // defines the current environment
public array $dbObjects; // list of the stored-procedures, views, events, triggers, and functions
private bool $dbMainAvailable; // Boolean - indicates if the DB is currently available or not
private bool $dbSlaveAvailable; // Boolean - indicates if the slave DB is currently available or not
public array $queryVariables; // container of mixed types that correspond to a prepared query's fields
private bool $sidewaysDelete; // alert for an update that started as a delete request b/c soft deletes
protected ?array $aryQuery = null; // PDO uses arrays for queries - this is the container for the query
// exceptions to the query-builder
public ?string $queryOrderBy;
public ?string $queryGroupBy;
public int $queryLimit;
public int $querySkip;
public ?string $queryFields = '';
public ?array $queryHaving = null;
public int $recordsInserted;
public int $recordsDropped;
public string $where;
public string $dbEvent; // container for the current database event being processed
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/// PUBLIC METHODS BEGIN HERE ///
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* gacPDO.class constructor.
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_meta
* @param string $_id
*
* HISTORY:
* ========
* 09-13-17 mks CORE-562: original coding
* 11-29-17 mks CORE-593: PDO Slave coding update, validate main and slave resources
* 11-27-18 mks DB-51: code to load a single record if the constructor receives a record guid
* also note that mySQL now only supports GUIDs as primary keys (even if it has an
* auto-incrementing integer indexed field). Also, moved stuff around for loading
* template and PDO resources.
*
*/
public function __construct(array $_meta, string $_id = '')
{
register_shutdown_function(array($this, '__destruct'));
$this->res = 'PDOC: ';
try {
parent::__construct();
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
$this->sidewaysDelete = false;
$this->recordsInserted = 0;
$this->recordsDropped = 0;
if (!empty($_guid) and $this->debug) $this->logger->debug(STRING_RECEIVED_GUID . $_guid);
// meta data transfer
$this->templateClass = $_meta[META_TEMPLATE];
$this->client = STRING_UNDEFINED;
if (!empty($_meta)) {
$this->metaPayload = $_meta;
if (isset($this->metaPayload[META_CLIENT])) {
$this->client = $this->metaPayload[META_CLIENT];
}
}
if (isset($this->metaPayload[META_EVENT_GUID])) {
$this->eventGUID = $this->metaPayload[META_EVENT_GUID];
} else {
$this->logger->info(ERROR_EVENT_GUID_404 . $this->class);
}
if (!isset($this->logger)) {
try {
$this->logger = new gacErrorLogger($_meta[META_EVENT_GUID] ?? null);
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
}
$this->status = false;
$this->limitOverride = false;
$this->env = gasConfig::$settings[CONFIG_ID][CONFIG_ID_ENV];
// load the PDO configuration from the XML
$this->config =gasConfig::$settings[CONFIG_DATABASE][CONFIG_DATABASE_PDO];
if (empty($this->config)) {
$msg = ERROR_CONFIG_RESOURCE_404 . RESOURCE_PDO_MASTER;
$this->logger->warn($msg);
$this->eventMessages[] = $msg;
$this->state = STATE_RESOURCE_ERROR_PDO;
return;
}
// load the template
// if we don't have a tlti set in the meta payload then the instantiation request originated outside of
// the SMAX API then we can assume the GA TLTI as a default
$this->templateName = (isset($_meta[META_TLTI])) ? $_meta[META_TLTI] . $_meta[META_TEMPLATE] : STRING_CLASS_GAT . $_meta[META_TEMPLATE];
try {
if (!$this->loadTemplate()) {
$this->logger->warn(ERROR_TEMPLATE_INSTANTIATE . $_meta[META_TLTI] . $this->templateClass);
$this->state = STATE_TEMPLATE_ERROR;
return;
}
if (!$this->logger->setService($this->dbService)) {
$this->eventMessages[] = ERROR_SERVICE_404 . $this->dbService;
$this->logger->fatal(sprintf(INFO_LOC, basename(__FILE__), __LINE__) . ERROR_SERVICE_404 . $this->dbService);
return;
}
// establish and assign PDO connections
if (!$this->getPDOResources()) return;
// get index and some query details about the class directly from the database -- state/status set in both
// of these methods...
if ($this->collectionName != NONE) {
$this->buildIndexReference();
if ($this->status)
$this->setRowsReturnedLimit();
}
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
$this->class = $this->templateName; // set the class to the name of the requested data class
// if the constructor received an id parameter, a GUID, validate and load the record if it exists...
if (!empty($_id)) {
if (!validateGUID($_id)) {
$msg = ERROR_INVALID_GUID . $_id;
$this->eventMessages[] = $msg;
$this->logger->error($msg);
$this->state = STATE_DATA_ERROR;
return;
}
$tweakedPerms = false; // did we override the audit controls for the fetch?
$oldAudit = $this->skipReadAudit; // save the old setting b/c we'll override if audit is enabled
// DB-51: populating a data class on instantiation if GUID passed to constructor
$query = [ DB_TOKEN => [ OPERAND_NULL => [ OPERATOR_EQ => [ $_id ]]]];
// if this is a system request, disable auditing/journaling for the fetch only
if ($this->useAuditing == AUDIT_NONDESTRUCTIVE and $this->metaPayload[META_CLIENT] == CLIENT_SYSTEM) {
$this->skipReadAudit = true;
$tweakedPerms = true;
}
try {
$this->_fetchRecords([STRING_QUERY_DATA => $query]);
} catch (TypeError $t) {
$this->eventMessages[] = sprintf(STUB_LOC, basename(__FILE__), __METHOD__, __LINE__) . ERROR_TYPE_EXCEPTION;
$this->eventMessages[] = $t->getMessage();
$this->logger->error($t->getMessage());
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
if ($tweakedPerms) $this->skipReadAudit = $oldAudit;
}
}
/**
* _createRecord() -- public method
*
* This is the PDO method for adding new records to the SQL database.
*
* The method requires one input parameter: an array of 1 or more records to be added to the referenced table.
*
* The method algorithm is as follows:
*
* 1. load the payload data into the class member, which also performs the cache-mapping
* 2. inject the createdDate, status and guid into each record
* 3. validate the record count against the max-allowed records
* 4. parse the records to pad (insert null placeholders) for any skipped fields
* 5. build the prepared SQL query for the insert
* 6. call the function which executes the batch query insert
* 7. parse the return payload for cacheMapping and data return
*
* Almost all of the chunky bits listed are handled in either class or core methods and those are extensively
* documented in-case you're wondering where all the doc for this method is.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_data
* @param string $_preValidated must be one of three constant values: DATA_NORM (default), DATA_MIG, or DATA_WH
*
* HISTORY:
* ========
* 10-23-17 mks CORE-585: original coding
* 04-28-18 mks _INF-188: support for meta_limit_override in warehousing request
* 05-23-18 mks _INF-188: rewrote query generation to overcome mysql's inability to batch process
* 07-23-18 mks CORE-1097: compliance with new mongo-resource retrieval
* 09-04-18 mks DB-48: changed to match re-declared method abstraction in core
* 11-05-18 mks DB-55: audit/journaling support added
*
*/
public function _createRecord(array $_data, string $_preValidated = DATA_NORM):void
{
try {
// ensure we still have an active connection - reconnect if we do not
if (is_null($this->dbcMain)) {
if (isset($this->isWHRequest) and $this->isWHRequest) {
$this->dbcMain = gasResourceManager::fetchResource(RESOURCE_MONGO_MASTER, ENV_SEGUNDO);
$res = RESOURCE_WH_COOL_PDO_MASTER;
} else {
$this->dbcMain = gasResourceManager::fetchResource(RESOURCE_PDO_MASTER);
$res = RESOURCE_PDO_MASTER;
}
if (is_null($this->dbcMain)) {
$error = ERROR_RESOURCE_404 . $res;
$this->eventMessages[] = $error;
if ($this->debug) $this->logger->debug($error);
$this->state = STATE_RESOURCE_ERROR;
$this->status = false;
return;
}
}
// all incoming data has already been validated (migration mapped if external source, fetched from a
// namaste table otherwise) so only do validation if data is coming from an unknown (user request) source
if ($_preValidated == DATA_NORM) {
// transfer the client-data payload into the class $data property with validation
// if the method returns a false, a processing error happened so return immediately
// note that data-array validation is handled in this method...
if (!$this->loadPayloadData($_data, DB_EVENT_CREATE)) return;
// reset state/status members
$this->state = STATE_DATA_ERROR;
$this->status = false;
} else {
$this->data = $_data;
}
// inject record GUIDs, createdDate and status into each payload record
if (!$this->newRecordDataInjections()) return;
// forced validation of the the number of tuples in the current container
if (count($this->data) != $this->count) {
$this->logger->warn(ERROR_DATA_INCONSISTENT_COUNT);
$this->count = count($this->data);
}
$this->state = STATE_DB_ERROR;
$this->status = false;
if ($this->count == 0) {
$this->logger->info(ERROR_DATA_ARRAY_EMPTY);
return;
}
if (!isset($this->metaPayload[META_LIMIT_OVERRIDE])) {
// ensure we're not inserting more than the max # of records allowed b/c batch processing eats memory
if ($this->count > $this->recordLimit) {
$msg = ERROR_RECORD_LIMIT_EXCEEDED . $this->recordLimit;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->state = STATE_DATA_ERROR;
return;
}
} else {
$this->recordLimit = $this->metaPayload[META_LIMIT_OVERRIDE];
}
// prep the data for the insert command by padding missing columns in the data set with null values
// and re-ordering the data so that the field data is in the same order and the fields in the query
// DO NOT DO THIS IF THIS IS A WAREHOUSE REQUEST!
if (!isset($this->isWHRequest) or !$this->isWHRequest) $this->prepareDataForInsert();
// build the sql string for the insert
$sql = 'INSERT /* ' . __METHOD__ . AT . __LINE__ . ' */ ';
$sql .= 'INTO ' . $this->queryTable . ' (';
// _INF-188: changing PDO inserts to bulk mode b/c we can't guarantee that stupid mysql query data will
// all have the same columns and fields provided in the data array. (so much easier in mongo)
$queryList = null;
foreach ($this->data as $record) {
$query = $sql . implode(', ', array_keys($record)) . ') VALUES (';
for ($index = 0, $max = count($record); $index < $max; $index++)
$query .= '?, ';
$query = rtrim($query, ', ') . ')';
$queryList[] = $query;
}
$this->aryQuery = $queryList;
// process the query and the data payload (also assigns value to strQuery)
$this->executePreparedBatchInsertQuery();
// if we successfully created a record(s), process the return data set
if ($this->status) {
// if auditing is enabled, post the audit data built in executePreparedBatchInsertQuery()
// we want to do this before we filter the data...
if ($this->useAuditing) {
// if the audit event fails, we'll continue processing without raising an error (to the client)
if (!$this->registerAuditEvent(EVENT_NAME_AUDIT_CREATE)) {
$this->eventMessages[] = ERROR_AUDIT_GENERIC_FAIL;
consoleLog($this->res, CON_ERROR, ERROR_AUDIT_GENERIC_FAIL);
}
}
$rc = $this->returnFilteredData();
if (!$rc) {
// an error happened building the return data -- todo: how to handle this?
// do nothing for now b/c this usually works but we'll need to address it!!!
if ($this->debug) $this->logger->debug('RC: ' . $rc);
}
}
} catch (Throwable $t) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = $hdr . ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
}
/**
* _fetchRecords() -- public method, defined in namasteCore
*
* this is one of the critical core (CRUD) methods that's defined as a protected abstraction in the namaste core
* parent class - just remember, all of the instantiation classes have to follow the same core definitions...
*
* this method requires one input parameter -- and that's the data payload as received by the broker for a fetch
* request, specifically, the read broker.
*
* this is a long method, mainly because mySQL has so many options and there's a lot of private methods written
* just to support this method.
*
* What the method, then, basically does, is take the input payload and break out the key components - some are
* required and some are optional, and then parses said components to build a SQL query that will be submitted
* to the database via the PDO API. Each relevant section within the method is commented for clarity.
*
* Success or failure for this method is determined by the state/status variables. If cache-mapping is enabled,
* then we'll return the cache key, otherwise, the $data member will hold the data set. This is the calling
* client's responsibility to evaluate and process.
*
* Documentation:
*
*
* Notes:
* ------
* This method is covered under unit testing.
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_data
*
* HISTORY:
* ========
* 10-10-17 mks CORE-584: original coding completed
* 10-13-17 mks CORE-584: using core::returnFilteredData() for consistent post-query processing, and added
* functionality for fetching the number of records returned by the query which is
* NOT the same thing as the number of records returned.
* 04-28-18 mks INF-188: meta_limit_override support for warehousing
* 11-06-18 mks DB-82: support for auditing
* 11-07-18 mks DB-83: check for meta_client == audit --> if so, skip the audit publish event
* 01-16-19 mks DB-103: Added CLIENT_UNIT to the check to skip returning filtered data
* 01-28-19 mks DB-107: Added CLIENT_AUDIT to the check to skip returning filtered data
* 02-01-19 mks DB-107: Fixed error where count of records returned not being correctly populated
* 02-19-19 mks DB-116: Deprecated cache-mapping/cache-fetching, fixed group-by/order-by processing error,
* deprecated code for evaluating which view to use as this is now set during class
* instantiation for all PDO class schemas.
* 06-18-19 mks DB-122: Formalized the recordsInQuery/recordsReturned/recordsInCollection member
* assignments.
*
*/
public function _fetchRecords(array $_data):void
{
$this->status = false;
$this->state = STATE_DATA_ERROR;
$sort = null;
$this->queryGroupBy = null;
$this->queryOrderBy = null;
// break-out the query bits from the data payload...
// load WHERE clause data from payload
$query = (isset($_data[STRING_QUERY_DATA])) ? $_data[STRING_QUERY_DATA] : null;
try {
// if necessary, inject a status filter into the request payload
list($query, $injection) = $this->softDeleteStatusInjection($query);
} catch (TypeError $t)
{
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
if (!is_array($query) and !is_null($_data[STRING_QUERY_DATA])) {
$msg = sprintf(ERROR_PDO_QUERY_ELEMENT_DATA_TYPE, STRING_QUERY_DATA, gettype($query));
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
$oldQuery = $query;
// load HAVING data from payload
if (isset($_data[STRING_HAVING_DATA])) {
$this->queryHaving = $_data[STRING_HAVING_DATA];
if (!is_array($this->queryHaving)) {
$msg = sprintf(ERROR_PDO_QUERY_ELEMENT_DATA_TYPE, STRING_HAVING_DATA, gettype($this->queryHaving));
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
}
// load LIMIT value, if set: from meta-payload or from record limit
if (isset($this->metaPayload[META_LIMIT])) {
if (!is_numeric($this->metaPayload[META_LIMIT])) {
$msg = ERROR_DATA_RANGE . META_LIMIT;
$this->eventMessages[] = $msg;
$this->eventMessages[] = sprintf(INFO_DATA_RESET, META_LIMIT) . $this->recordLimit;
$this->queryLimit = $this->recordLimit;
} else {
$this->queryLimit = intval($this->metaPayload[META_LIMIT]);
if ($this->queryLimit < 0 or $this->queryLimit > $this->recordLimit) {
$this->eventMessages[] = sprintf(INFO_DATA_RESET, META_LIMIT) . $this->recordLimit;
$this->queryLimit = $this->recordLimit;
}
}
} elseif (isset($this->metaPayload[META_LIMIT_OVERRIDE]) and (1 === intval($this->metaPayload[META_LIMIT_OVERRIDE]))) {
$this->recordLimit = gasConfig::$settings[CONFIG_BROKER_SERVICES][CONFIG_WH_RECS_PER_XFER];
$this->queryLimit = gasConfig::$settings[CONFIG_BROKER_SERVICES][CONFIG_WH_RECS_PER_XFER];
} else {
$this->queryLimit = $this->recordLimit;
}
// load and validate the SKIP value
if (isset($this->metaPayload[META_SKIP])) {
if (!is_numeric($this->metaPayload[META_SKIP])) {
$msg = ERROR_DATA_RANGE . META_SKIP;
$this->eventMessages[] = $msg;
$this->eventMessages[] = sprintf(ERROR_DATA_TYPE_MISMATCH_DETAILS, META_SKIP, DATA_TYPE_INTEGER, gettype($this->metaPayload[META_SKIP]));
$this->querySkip = 0;
} elseif (isset($this->metaPayload[META_LIMIT]) and $this->metaPayload[META_LIMIT] < 0) {
$msg = ERROR_DATA_RANGE . COLON . META_SKIP;
$this->eventMessages[] = $msg;
// todo -- does this condition qualify as a query rejection?
$this->querySkip = 0;
} else {
$this->querySkip = intval($this->metaPayload[META_SKIP]);
}
} else {
$this->querySkip = 0;
}
try {
// build the where discriminant part of the query
$query = $this->queryBuilder($query);
if (is_null($query)) {
$this->eventMessages[] = ERROR_DATA_QUERY_BUILD;
return;
}
$this->where = $query;
// build the "having" clause
if (!is_null($this->queryHaving)) {
$this->queryHaving = $this->queryBuilder($this->queryHaving);
if (is_null($this->queryHaving)) {
$this->eventMessages[] = ERROR_DATA_HAVING_BUILD;
return;
}
}
// build the return field list
if (isset($_data[STRING_RETURN_DATA])) {
$this->queryFields = $this->buildReturnFieldList($_data[STRING_RETURN_DATA]);
} else {
$this->queryFields = $this->buildReturnFieldList();
}
if (empty($this->queryFields)) {
$msg = ERROR_DATA_QUERY_BUILD . STRING_RETURN_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
// build the order-by/group-by clauses
$groupByData = (isset($_data[STRING_GROUP_BY_DATA])) ? $_data[STRING_GROUP_BY_DATA] : array();
if (!isset($_data[STRING_ORDER_BY_DATA]) and isset($_data[STRING_SORT_DATA])) {
$orderByData = $_data[STRING_SORT_DATA];
} elseif (isset($_data[STRING_ORDER_BY_DATA])) {
$orderByData = $_data[STRING_ORDER_BY_DATA];
} else {
$orderByData = array();
}
// validate these as arrays...
if (!is_null($groupByData) and !is_array($groupByData)) {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_GROUP_BY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
if (!is_null($orderByData) and !is_array($orderByData)) {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_ORDER_BY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
// account for both input parameters being null
if (is_null($groupByData) and is_null($orderByData)) {
$this->queryOrderBy = null;
$this->queryGroupBy = null;
} else {
$this->buildGroupOrderBy($groupByData, $orderByData);
if ($this->state != STATE_SUCCESS) {
$this->eventMessages[] = ERROR_DATA_GROUP_ORDER_BY_BUILD;
if ($this->debug) $this->logger->debug(ERROR_DATA_GROUP_ORDER_BY_BUILD);
return;
}
}
if (!$this->status) {
if (!$injection) {
// we didn't inject a query so log the failure and return
$this->logger->error(ERROR_DATA_QUERY_BUILD);
$this->logger->error(json_encode($query));
return;
} elseif (!empty($oldQuery)) {
// attempt to build the original query
$query = $this->queryBuilder($oldQuery);
if ($this->debug) $this->logger->debug($query);
if (!$this->status) {
$this->logger->error(ERROR_DATA_QUERY_BUILD);
$this->logger->error(json_encode($oldQuery));
return;
}
}
}
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
// determine the "from" part of the query - if not given, this will default the basic view that is
// native to every class (via the template definition: $dbObjects)
// if (empty($_data[STRING_FROM_DATA])) {
// if (!isset($this->template->dbObjects[PDO_VIEWS][PDO_VIEW_BASIC . $this->collectionName])) {
// $this->queryTable = $this->collectionName;
// } else {
// $this->queryTable = PDO_VIEW_BASIC . $this->collectionName;
// }
// } else {
// // validate the name of the supplied view against the current view list
// if (array_key_exists($_data[STRING_FROM_DATA], $this->template->dbObjects[PDO_VIEWS])) {
// $this->queryTable = $_data[STRING_FROM_DATA];
// } else {
// // return an error since we can't assume the responsibility of deriving what the client intended
// $msg = sprintf(ERROR_VIEW_404, $_data[STRING_FROM_DATA], $this->class);
// $this->eventMessages[] = $msg;
// if ($this->debug) $this->logger->debug($msg);
// $this->state = STATE_VALIDATION_ERROR;
// return;
// }
// }
// reset from queryBuilder()
$this->status = false;
$this->state = STATE_DATA_ERROR;
// BUILD THE PDO QUERIES
// all namaste queries include a comment identifying where in the source code the query originated
// BUILDING the query-record counter: how many total records will be returned from this query
$rcQuery = 'SELECT /* QUERY_COUNT: ' . basename(__METHOD__) . AT . __LINE__ . '*/ ';
$rcQuery .= 'count(*) AS ' . STRING_NUM_RECS . ' ';
$rcQuery .= 'FROM ' . $this->queryTable . ' ';
// BUILDING the regular query
$this->strQuery = 'SELECT /* ' . basename(__METHOD__) . AT . __LINE__ . ' */ ';
$this->strQuery .= $this->queryFields . ' '; // assigns the return fields to the query
$this->strQuery .= 'FROM ' . $this->queryTable . ' '; // the view or table name
$this->strQuery .= 'WHERE ' . $this->where . ' '; // the query discriminant
$rcQuery .= 'WHERE ' . $this->where . ' ';
if (isset($this->queryGroupBy) and !is_null($this->queryGroupBy)) {
$this->strQuery .= 'GROUP BY ' . $this->queryGroupBy . ' ';
$rcQuery .= 'GROUP BY ' . $this->queryGroupBy . ' ';
}
if (isset($this->queryOrderBy) and !is_null($this->queryOrderBy)) {
$this->strQuery .= 'ORDER BY ' . $this->queryOrderBy . ' ';
$rcQuery .= 'ORDER BY ' . $this->queryOrderBy . ' ';
}
// limit, skip is always enabled set so check for non-0 values
// do NOT add the skip/limit params to the record-count query!
if ($this->querySkip and $this->queryLimit) {
$this->strQuery .= 'LIMIT ' . $this->querySkip . COMMA . $this->queryLimit;
} elseif ($this->queryLimit) {
$this->strQuery .= 'LIMIT ' . $this->queryLimit;
}
try {
// set the event
$this->dbEvent = DB_EVENT_SELECT;
if (is_null($this->queryVariables)) {
// non-prepared query
$this->executeNonPreparedQuery();
$this->getQueryCount($rcQuery);
} else {
// prepared query
$this->executePreparedQuery();
$this->getPreparedQueryCount($rcQuery);
}
// the query was successful! -- process the payload data (cacheMapping), generate data counters
if ($this->status) {
if (is_null($this->queryResults) or empty($this->queryResults)) {
// search was successful but returned no records
$this->state = STATE_NOT_FOUND;
$this->status = true;
$this->eventMessages[] = INFO_QUERY_RETURNED_NO_DATA;
return;
}
$this->data = $this->queryResults;
$this->getActiveRecordCount();
$this->count = (!empty($this->data)) ? count($this->data) : 0;
$this->recordsReturned = $this->count;
// If auditing is set to read level, then submit the audit request
if ($this->useAuditing == AUDIT_NONDESTRUCTIVE) {
$this->auditRecordList = $this->data;
if (!$this->registerAuditEvent(EVENT_NAME_AUDIT_FETCH)) {
$this->eventMessages[] = ERROR_AUDIT_GENERIC_FAIL;
consoleLog($this->res, CON_ERROR, ERROR_AUDIT_GENERIC_FAIL);
}
}
// check to see if the filterData bypass was set
if ((isset($this->metaPayload[META_DONUT_FILTER]) and $this->metaPayload[META_DONUT_FILTER] == 1) and
($this->metaPayload[META_CLIENT] == CLIENT_SYSTEM or
$this->metaPayload[META_CLIENT] == CLIENT_UNIT or
$this->metaPayload[META_CLIENT] == CLIENT_AUDIT))
return;
// otherwise return a filtered data-set according to cache settings
if (!$this->returnFilteredData()) {
$this->logger->warn(ERROR_RFD_CORE_FAIL);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
} // todo -- else???
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
}
/**
* _updateRecord() -- public method
*
* This method is normally called by the write broker for an update event - the method will process the client
* payload and will generate an update query that will affect 1, or more, rows in the PDO data set.
*
* The input parameter is the data payload as passed from the client. There must be, at a minimum, two sub-arrays
* present in the payload:
*
* STRING_QUERY_DATA for building the query where-discriminant
* STRING_UPDATE_DATA the key-value list of fields/values that will be updated
*
* Optional parameter:
*
* STRING_ORDER_BY_DATA for the order-by discriminant
*
* And, finally, if META_LIMIT is defined in the meta payload, the number of records that can be updated will
* be limited by this parameter. Note that, when validating META_LIMIT, we do not check an upper-boundry, only
* that the value is greater than 0.
*
* At the top of the method, we'll init some lvars and validate the input parameter as well as verifying that
* the required parameters are present and are present as array types.
*
* Next, we process the data payload and begin assembling the query components.
*
* If the current class uses soft-deletes and there's no STATUS field in the where discriminant, we're going to
* inject one (WHERE status != DELETED) into the query. todo: modify when status field is present
*
* In the fields to be updated, once that data has been validated and passes the protected fields check, add
* the lastAccessedDate to the list of column to be updated.
*
* Build the prepared query and pass it off to the execute prepared query method and update the cache (there's a
* to-do in that section of the code) before returning control back to the calling client.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_data
*
* HISTORY:
* ========
* 10-27-17 mks CORE-586: original coding
* 05-21-18 mks _INF-188: fixed error with updates for (non)cached soft delete updates
* 11-29-18 mks DB-55: re-ordered query-building (component) flow, added audit support
* 02-05-19 mks DB-107: fixed error where we weren't fetching a list of updated records prior to
* calling the audit event, causing the audit event to fail
* Qualified the status injection on status field not being in the update data
* 02-15-19 mks DB-116: removed cache-mapping support; this is now handled at broker-services level,
* pre-update, we're now establishing a list of tokens that will be impacted by the
* update query and, if the query is unbound/unspecific, then we'll inject this token
* list in the update, and the post-update fetch queries for consistency
* 11-06-20 mks DB-171: define meta data field for current service since we're calling validateMetaData
* directly without first calling the firstPassPayloadValidation() method which is
* how brokerService is normally set in the meta data payload
*
*/
public function _updateRecord(array $_data):void
{
$this->state = STATE_DATA_ERROR;
$this->status = false;
$where = null;
$dataCopy = null;
$tokenQuery = null;
$updateData = null;
$recordList = null;
$tokenList = null;
// ensure the $_data input parameter is an array
if (empty($_data) or !is_array($_data)) {
$this->eventMessages[] = ERROR_DATA_404;
if ($this->debug) $this->logger->debug(ERROR_DATA_MISSING_ARRAY . STRING_DATA);
return;
}
// check for required fields in the $_data payload and qualify them as arrays
if (!array_key_exists(STRING_QUERY_DATA, $_data)) {
$msg = ERROR_DATA_KEY_404 . STRING_QUERY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
} elseif (!is_array($_data[STRING_QUERY_DATA])) {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_QUERY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
if (!array_key_exists(STRING_UPDATE_DATA, $_data)) {
$msg = ERROR_DATA_KEY_404 . STRING_UPDATE_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
} elseif (!is_array($_data[STRING_UPDATE_DATA])) {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_UPDATE_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
// if the current class uses soft deletes, we need to inject a "AND STATUS <> DELETED" qualifier
if (!$this->useDeletes) { // if the current class uses soft-deletes
// inject a status filter if one doesn't already exist in the update payload and we're using a soft-delete
// class, and if there's no status field in the discriminant
if (!array_key_exists((DB_STATUS . $this->ext), $_data[STRING_UPDATE_DATA])
and (!array_key_exists(DB_STATUS, $_data[STRING_UPDATE_DATA]))
and (!array_key_exists((DB_STATUS . $this->ext), $_data[STRING_QUERY_DATA]))) {
$injected = true;
if (!isset($_data[STRING_QUERY_DATA][DB_STATUS])) {
$_data[STRING_QUERY_DATA][DB_STATUS] = [OPERAND_NULL => [OPERATOR_DNE => [STATUS_DELETED]]];
} else {
$injected = false;
}
if ($injected) {
$_data[STRING_QUERY_DATA][OPERAND_AND] = null;
}
} elseif ((isset($_data[STRING_UPDATE_DATA][DB_STATUS]) and $_data[STRING_UPDATE_DATA][DB_STATUS] == STATUS_DELETED)
or (isset($_data[STRING_UPDATE_DATA][DB_STATUS . $this->ext]) and $_data[STRING_UPDATE_DATA][DB_STATUS . $this->ext] == STATUS_DELETED)) {
$this->sidewaysDelete = true;
}
}
// start validating the query components - because PHP7 and type-cast parameters, these are exception wrapped
try {
// since we're doing an update, we need to fetch a list of the records that will be affected by the
// update so that, post-query, we can remove them from cache. If auditing is enabled, we'll use the data
// (from the original query) in audit-journal processing.
// Additionally, we're going to clone the current object so that current data is not affected and we'll
// copy over the payload from the cloned object to the current object.
$clone = clone $this;
$clone->cloneSquelch();
if ($clone->metaPayload[META_CLIENT] == CLIENT_AUDIT) $clone->useDeletes = true;
if (!isset($clone->metaPayload[META_BROKER_SERVICE]) or empty($clone->metaPayload[META_BROKER_SERVICE]))
$clone->metaPayload[META_BROKER_SERVICE] = (isset($this->dbService) and !empty($this->dbService)) ? $this->dbService : ENV_APPSERVER;
$clone->_fetchRecords($_data);
if (!$clone->status) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$this->eventMessages[] = ERROR_CLONE_QUERY;
$this->logger->error($hdr . ERROR_CLONE_QUERY . $clone->strQuery);
$this->state = STATE_DB_ERROR;
$clone->__destruct();
unset($clone);
return;
} elseif ($clone->count) {
// note that we're not de-allocating $clone on success... build the auditRecordList and TokenLists
$this->auditRecordList = $clone->data;
if (count($this->auditRecordList)) {
foreach ($this->auditRecordList as $record)
$this->tokenList[] = $record[(DB_TOKEN . $this->ext)];
if (count($this->tokenList))
$tokenQuery[CM_TST_TOKEN] = [ OPERAND_NULL => [ OPERATOR_IN => $this->tokenList ]];
}
} elseif ($clone->count == 0) {
$this->queryResults[] = INFO_QUERY_RETURNED_NO_DATA;
$clone->__destruct();
unset($clone);
$this->state = STATE_NOT_FOUND;
$this->status = true;
return;
}
// inject the token list into the submitted query as an IN component
if (!array_key_exists(CM_TST_TOKEN, $_data[STRING_QUERY_DATA]) and !empty($tokenQuery)) {
if ((count($_data[STRING_QUERY_DATA]) == 1) or ((count($_data[STRING_QUERY_DATA]) > 1) and !array_key_exists(OPERAND_AND, $_data[STRING_QUERY_DATA]))) {
// we've got a "simple" single-field query, or a complex query without and OPERATOR_AND so append:
$_data[CM_TST_TOKEN] = $tokenQuery[CM_TST_TOKEN];
// $_data[CM_TST_TOKEN] = [ OPERAND_NULL => [ OPERATOR_IN => $this->tokenList ]];
$_data[OPERAND_AND] = null;
} elseif (array_key_exists(OPERAND_AND, $_data[STRING_QUERY_DATA])) {
// we have a complex query into which we have to inject the token list before OPERATOR_AND
foreach ($_data[STRING_QUERY_DATA] as $key => $elementList) {
if ($key == OPERAND_AND) {
$dataCopy[CM_TST_TOKEN] = $tokenQuery[CM_TST_TOKEN];
// $dataCopy[CM_TST_TOKEN] = [ OPERAND_NULL => [ OPERATOR_IN => $this->tokenList ]];
$dataCopy[$key] = $elementList;
} else {
$dataCopy[$key] = $elementList;
}
}
$_data[STRING_QUERY_DATA] = $dataCopy;
}
}
// build the query discriminant (required parameter)
if (!$this->buildWhereDiscriminant($_data[STRING_QUERY_DATA])) return;
// validate the query update data (required parameter)
$updateData = $_data[STRING_UPDATE_DATA];
if (false === $this->checkProtectedFields($updateData)) {
$this->state = STATE_VALIDATION_ERROR;
$this->status = false;
return; // event messages and log errors happened in the method called above
}
// check to see if optional parameters were passed and, if they exist, validate
// if GROUP-BY data is provided, validate so that the query is stored in $this->queryOrderBy
if (isset($_data[STRING_ORDER_BY_DATA]) and !$this->buildOrderBy($_data[STRING_ORDER_BY_DATA])) return;
// load LIMIT value, if set: from meta-payload
if (isset($this->metaPayload[META_LIMIT])) {
if (!is_numeric($this->metaPayload[META_LIMIT])) {
$msg = ERROR_DATA_RANGE . META_LIMIT;
$this->eventMessages[] = $msg;
$this->eventMessages[] = sprintf(INFO_DATA_RESET, META_LIMIT) . $this->recordLimit;
$this->queryLimit = 0;
} else {
$this->queryLimit = intval($this->metaPayload[META_LIMIT]);
if ($this->queryLimit < 0) {
$this->eventMessages[] = sprintf(INFO_DATA_RESET, META_LIMIT) . $this->recordLimit;
$this->queryLimit = 0;
}
}
} else {
$this->queryLimit = 0;
}
// inject an update for the lastAccessedDate field which is protected and is why this comes
// after the checkProtectedFields() call...
$updateData[(DB_ACCESSED . $this->ext)] = date('Y-m-d G:i:s');
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
// checkpoint -- we've all the query elements -- start building the query
try {
$pp = $this->queryVariables; // positional parameters: temp assignment
$this->queryVariables = [];
$query = 'UPDATE /* ' . basename(__FILE__) . COLON . __METHOD__ . AT . __LINE__ . ' */ ';
$query .= $this->queryTable . ' SET ';
// todo - a view is not guaranteed to work for all update queries and needs to be addressed
// for more info: http://www.mysqltutorial.org/create-sql-updatable-views.aspx
foreach ($updateData as $column => $value) {
$query .= $column . ' = ?, ';
$this->queryVariables[] = $value;
}
$query = rtrim($query, ', ');
// merge the where-discriminant query variables onto the end of the query-variable container so that the
// order of the variable placement is consistent with the place-holders within the query string.
if (is_array($pp)) {
$this->queryVariables = array_merge($this->queryVariables, $pp);
unset($pp);
}
if (!is_null($this->where)) $query .= ' WHERE ' . $this->where . ' ';
// order-by and limit are supported in update queries even if you think they're not...
if (!empty($this->queryOrderBy)) $query .= 'ORDER BY ' . $this->queryOrderBy . ' ';
if (!empty($this->queryLimit)) $query .= 'LIMIT ' . $this->queryLimit;
$this->strQuery = $query;
// submit the query
$this->dbEvent = ($this->sidewaysDelete) ? DB_EVENT_DELETE : DB_EVENT_UPDATE;
$this->executePreparedQuery();
if ($this->status) {
// if auditing is enabled, copy of the original records and invoke the audit/journal processing
if ($this->useAuditing > AUDIT_NOT_ENABLED) {
$auditEvent = ($this->dbEvent == DB_EVENT_UPDATE) ? EVENT_NAME_AUDIT_UPDATE : EVENT_NAME_AUDIT_DELETE;
if (!$this->registerAuditEvent($auditEvent)) {
$this->eventMessages[] = ERROR_AUDIT_GENERIC_FAIL;
consoleLog($this->res, CON_ERROR, ERROR_AUDIT_GENERIC_FAIL);
}
}
// if this was a straight-update then fetch the updated records and restore the DB-EVENT.
if (!$this->sidewaysDelete) {
$payload = [
BROKER_DATA => [ STRING_QUERY_DATA => $tokenQuery ],
BROKER_META_DATA => $clone->metaPayload
];
if (!validateMetaData($payload, $this->eventMessages)) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = ERROR_META_VALIDATION_SECOND_PASS;
$this->eventMessages[] = $msg;
$this->logger->error($hdr . $msg);
$this->state = STATE_DATA_ERROR;
$this->status = false;
return;
}
// fetching the records will update them in cache
// change this to the IN query using the $auditRecordList
$clone->cloneSquelch();
$clone->_fetchRecords($payload[BROKER_DATA]);
if (!$clone->status) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = ERROR_PDO_FETCH;
$this->eventMessages[] = $msg;
$this->logger->error($hdr . $msg);
$this->logger->error($hdr . $clone->strQuery);
if (is_object($clone)) $clone->__destruct();
unset($clone);
$this->state = STATE_DB_ERROR;
$this->status = false;
return;
}
$this->eventMessages = array_merge($this->eventMessages, $clone->eventMessages);
$this->data = $clone->data;
$this->count = $clone->count;
if (!$this->returnFilteredData()) {
$this->eventMessages[] = ERROR_DATA_PROCESSING;
$this->state = STATE_DATA_ERROR;
}
if (is_object($clone)) $clone->__destruct();
unset($clone);
} else {
// if the update resulted in removing the record(s), we need to build a token list and cacheSmash
foreach ($this->auditRecordList as $record)
$tokenList[] = $record[DB_TOKEN . $this->ext];
if (!gasCache::smashCache($tokenList, $this->eventMessages))
$this->logger->error(ERROR_CACHE_SMASH_FAIL_SYSTEM . $this->strQuery);
}
} else {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = ERROR_PDO_QUERY . $query;
$this->logger->error($hdr . $msg);
$this->eventMessages[] = $msg;
$this->state = STATE_DATA_ERROR;
}
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
}
/**
* _deleteRecord() -- public method
*
* This is the method called by the brokers for a delete event. It is normally called by the delete-event broker
* when a PDO class is the targeted instantiation.
*
* This method first checks to see if the class has hard deletes enabled and, if it does not, it immediately passes
* the request over to the update method so that we can toggle the status of the records returned by the query to
* DELETED instead of permanently removing the records.
*
* If we're proceeding with the hard delete, we enter the validation phase of the $_data payload:
*
* 1. ensure we have a where clause that defines a range of records to be deleted - reject the request
* if this payload does not exist (limited effectiveness)
* todo: select query count of affected rows != count(*) for the table
* 2. build the where clause
* 3. build the group-by clause if provided
* 4. build the limit clause if provide
*
* Next, we need to build a select query based on the discriminants just validated, and fetch a list of the tokens
* representing records to be deleted. This is so that, post delete, we can update (remove) said records if they
* are cached individually.
*
* todo: batch-cached records -- how to handle? a record-registration process?
*
* Then, build the delete query and pass it off to the preparedQuery method for execution. On successful return,
* take the token list we generated in the select query and search cache for these records stored individually and
* remove them from cache if they exist.
*
* On successful processing, the class returns the number of rows deleted in $this->rowsAffected and a text-string
* to the same effect in $this->queryResults.
*
* Notes:
* ------
* multi-table deletes are not supported
* partition deletes are not supported
* table references are not supported
*
* reference: https://dev.mysql.com/doc/refman/5.7/en/delete.html
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_data -- payload data as received by the broker
*
* HISTORY:
* ========
* 10-25-17 mks CORE-587: original coding
* 10-26-17 mks CORE-586: code reduction
* 02-01-19 mks DB-107: fixed bug where the record to be deleted isn't pre-fetched, when we pass the
* soft-delete data class to the update command, we pull a copy of the record so that the
* update method will complete successfully.
* 02-21-19 mks DB-116: refactored to remove cache support, removed redundant fetch call, clone usage
*
*/
public function _deleteRecord(array $_data):void
{
$this->status = false;
$this->state = STATE_DATA_ERROR;
$where = null;
$tokenList = null;
// get a copy of the records that will be impacted by the delete request - return if the fetch request fails
// set-up a clone object for the fetch so as not to "pollute" the current class object.
$clone = clone $this;
$clone->cloneSquelch();
$clone->_fetchRecords($_data);
if (!$clone->status) {
$this->eventMessages[] = ERROR_PDO_FETCH;
$this->logger->info(ERROR_PDO_FETCH);
if (count($clone->eventMessages))
$this->eventMessages = array_merge($this->eventMessages, $clone->eventMessages);
if (is_object($clone)) $clone->__destruct();
unset($clone);
return;
}
if ($clone->count) {
$this->auditRecordList = $clone->data;
foreach ($this->auditRecordList as $record)
$this->tokenList[] = $record[(DB_TOKEN . $this->ext)];
} else {
// no records were returned in the query
$this->eventMessages[] = INFO_RECORD_NOT_FOUND;
$this->state = STATE_NOT_FOUND;
if (count($clone->eventMessages))
$this->eventMessages = array_merge($this->eventMessages, $clone->eventMessages);
if (is_object($clone)) $clone->__destruct();
unset($clone);
return;
}
if (is_object($clone)) $clone->__destruct();
unset($clone);
// next thing we want to do is check and see if hard-deletes are enabled for the current class because, if so,
// then we're going to inject a "status_ext = 'DELETED'" directive into the payload and then do a sideways
// hand-off to the _updateRecord() method instead.
if (!$this->useDeletes) {
if (isset($_data[STRING_UPDATE_DATA])) {
$msg = sprintf(ERROR_UPDATE_DATA_NOT_ALLOWED, STRING_DELETE);
$this->eventMessages[] = $msg;
$this->state = STATE_REQUEST_REJECTED;
$this->status = false;
if ($this->debug) $this->logger->debug($msg);
return;
}
// build and inject the status = deleted, lastAccessed = now() directives for the update query
// if you get tempted to add the lastAccessedData update here, don't...
$_data[STRING_UPDATE_DATA] = [ DB_STATUS => STATUS_DELETED ];
try {
$this->sidewaysDelete = true; // everything in update() procs off of this setting
if ($this->debug)
$this->logger->debug(sprintf(INFO_SIDEWAYS, DB_EVENT_DELETE, DB_EVENT_UPDATE));
// re-direct the delete request to the update method to toggle the record status
$this->_updateRecord($_data);
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
return; // pass-thru the state/status from update to the calling client
}
// at this stage, we're using a hard-delete class so start by qualifying the where clause
// if there is no "where" clause in the payload, reject the request b/c namaste does not allow
// delete-all-records types of queries...
if (!isset($_data[STRING_QUERY_DATA])) {
$msg = ERROR_DATA_KEY_404 . STRING_QUERY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
try {
if (!$this->buildWhereDiscriminant($_data[STRING_QUERY_DATA])) return;
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
if (is_null($this->where)) {
$this->eventMessages[] = ERROR_DATA_QUERY_BUILD;
if ($this->debug) $this->logger->debug(ERROR_DATA_QUERY_BUILD);
return;
}
try {
// if GROUP-BY data is provided, validate so that the query is stored in $this->queryOrderBy
if (isset($_data[STRING_ORDER_BY_DATA]) and !$this->buildOrderBy($_data[STRING_ORDER_BY_DATA])) return;
// load LIMIT value, if set: from meta-payload
if (isset($this->metaPayload[META_LIMIT])) {
if (!is_numeric($this->metaPayload[META_LIMIT])) {
$msg = ERROR_DATA_RANGE . META_LIMIT;
$this->eventMessages[] = $msg;
$this->eventMessages[] = sprintf(INFO_DATA_RESET, META_LIMIT) . $this->recordLimit;
$this->queryLimit = 0;
} else {
$this->queryLimit = intval($this->metaPayload[META_LIMIT]);
if ($this->queryLimit < 0) {
$this->eventMessages[] = sprintf(INFO_DATA_RESET, META_LIMIT) . $this->recordLimit;
$this->queryLimit = 0;
}
}
} else {
$this->queryLimit = 0;
}
// build the pdo query for delete
$query = 'DELETE /* ' . basename(__FILE__) . COLON . __METHOD__ . AT . __LINE__ . ' */ ';
$query .= 'FROM ' . $this->queryTable . ' ';
$query .= 'WHERE ' . $this->where . ' ';
if (!is_null($this->queryOrderBy)) {
$query .= 'ORDER BY ' . $this->queryOrderBy . ' ';
}
if ($this->queryLimit) {
$query .= 'LIMIT ' . $this->queryLimit;
}
$this->strQuery = $query;
// submit the query
$this->dbEvent = DB_EVENT_DELETE;
$this->executePreparedQuery();
if ($this->status) {
if (!$this->registerAuditEvent(EVENT_NAME_AUDIT_DELETE)) {
$this->eventMessages[] = ERROR_AUDIT_GENERIC_FAIL;
consoleLog($this->res, CON_ERROR, ERROR_AUDIT_GENERIC_FAIL);
}
if (!$this->returnFilteredData()) {
$this->eventMessages[] = ERROR_DATA_PROCESSING;
$this->state = STATE_DATA_ERROR;
}
// remove records from cache
@gasCache::smashCache($this->tokenList, $this->eventMessages);
}
} catch (Throwable | TypeError $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
/**
* _checkQuery() -- public function
*
* this function is mandated by the Namaste core abstraction. The method provides access to the query-builder
* functionality within the the data classes to validate a query submission from clients.
*
* The method requires a single input parameter: this is the array containing the query to be tested.
*
* The method returns a boolean indicating whether or not the query was successfully built... if it was built
* successfully, then store the query in the class member container.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_query
* @return bool
*
*
* HISTORY:
* ========
* 04-23-18 mks _INF-188: Original coding
*
*/
public function _checkQuery(array $_query): bool
{
try {
$query = $this->queryBuilder($_query);
if (!is_null($query)) {
$this->strQuery = $query;
return true;
}
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
return false;
}
/**
* _getQC() -- abstraction-declared public method
*
* This method is prototyped in the core abstraction.
*
* The method requires a single input parameter and this is the STRING_QUERY_DATA sub-array from the
* BROKER_DATA payload of a fetch event request.
*
* The method validates that the input data exists and is an array and then passes the parameter off to
* the query-builder method to build the SQL query.
*
* If the call to the query-builder method returns true, (which is just actually building the where clause), we
* will complete building the entire query and then submit it to the methods that exec the query -- which, in turn,
* is dependent on if we have a prepared (contains variables) or non-prepared query.
*
* If the query-execution method succeeds, we return a Boolean(true) to the calling client -- relevant data (the
* query count) is embedded in a class member variable and should be extracted by the calling client.
*
* In all cases where a Boolean(false) is returned, we populate the eventMessage stack and, if debug is enabled,
* also output a debug-log message.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_data
* @return bool
*
*
* HISTORY:
* ========
* 04-30-18 mks _INF-188: original coding
*
*/
public function _getQC(array $_data):bool
{
if (empty($_data) or (!is_array($_data))) {
$error = ERROR_DATA_MISSING_ARRAY . BROKER_DATA;
$this->eventMessages[] = $error;
if ($this->debug) $this->logger->debug($error);
$this->state = STATE_DATA_ERROR;
return false;
}
if (!isset($_data[STRING_QUERY_DATA]) or (!is_array($_data[STRING_QUERY_DATA]))) {
$error = ERROR_DATA_KEY_404 . STRING_QUERY_DATA;
$this->eventMessages[] = $error;
if ($this->debug) $this->logger->debug($error);
$this->state = STATE_DATA_ERROR;
return false;
}
try {
$where = $this->queryBuilder($_data[STRING_QUERY_DATA]);
if (is_null($where)) {
$this->state = STATE_DATA_ERROR;
$this->eventMessages[] = ERROR_DATA_QUERY_BUILD;
if ($this->debug) $this->logger->debug(ERROR_DATA_QUERY_BUILD);
return false;
}
$query = 'SELECT /* ' . basename(__FILE__) . COLON_NS . __LINE__ . ' */ count(*) AS ' . STRING_NUM_RECS . ' ';
$query .= 'FROM ' . $this->collectionName . ' WHERE ' . $where;
$this->dbEvent = DB_EVENT_SELECT;
$this->strQuery = $query;
$this->executePreparedQuery();
if (!$this->getPreparedQueryCount($query)) {
$error = ERROR_PDO_COUNT_FETCH_FAIL . $this->strQuery;
$this->eventMessages[] = $error;
if ($this->debug) $this->logger->debug($error);
$this->state = STATE_DB_ERROR;
return false;
}
$this->getActiveRecordCount();
return true;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
return false;
}
/**
* execSP() -- public method
*
* This method allows the brokers to exec a stored-procedure. There is one input parameter to the method and
* that is an associative array with the following structure:
*
* $_data = [ STRING_PROCEDURE_NAME => {string}, STRING_PARAM_LIST => {array} ]
*
* Where the STRING_PROCEDURE_NAME is the name of the stored procedure (obtainable from the schema template report)
* to be invoked and STRING_PARAM_LIST is an indexed-array of parameter values that will be passed through to
* the stored-procedure as function-parameter values.
*
* Validation:
* The method validates the following:
* -- that $_data is an array
* -- that $_data is an array with 2 elements
* -- that $_data is an array and one of the two elements is STRING_PROCEDURE_NAME
* -- that $_data is an array and one of the two elements is STRING_PARAM_LIST
* -- that the name of the stored-procedure to be invoked is a member of the current class' stored-procedure
* catalog (of sp names)
*
* Next, we parse all of the stored-procedure template-defined parameters to generate a count of the number of
* IN and OUT parameters, respectively, and also generate a list of all of the OUT parameter names.
*
* Processing:
* Once validation is complete, we have three different use cases for processing:
* -- no parameters are passed to the SP
* -- only IN parameters are passed to the SP
* -- the passed parameters contain an OUT param
*
* Each of these three use-cases requires a different way of invoking the stored procedure and fetching the
* result set.
*
* As part of the data-payload validation process, we're comparing the count of the number of parameters
* received by the user request to the count of the number of parameters declared in the SP defined in
* the template. Note that this will only apply to IN params.
*
* Once we know which branch to take in the processing tree, we execute the code to build the stored
* procedure SQL invocation query, bind parameters if present, and then exec the query and fetch the
* results-set.
*
* The results are returned in $this->queryResults and not $this->data b/c we cannot limit the return
* data to just table column values. (e.g.: aggregation queries). As such, there is no caching of the
* return data for stored-procedure calls nor is there a limit on the number of records returned.
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param $_data
*
* HISTORY:
* ========
* 11-10-17 mks CORE-588: original coding completed
*
*/
public function execSP($_data)
{
$this->state = STATE_DATA_ERROR;
$this->status = false;
$this->queryResults = [];
$startTime = floatval(0);
$outList = null;
// validate the input data as an array with two elements
if (!is_array($_data)) {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_QUERY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
if (count($_data) != 2) {
$msg = sprintf(ERROR_DATA_ARRAY_COUNT, 2, STRING_QUERY_DATA, count($_data));
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
// validate the data keys
if (!array_key_exists(STRING_PROCEDURE_NAME, $_data)) {
$msg = ERROR_DATA_KEY_404 . STRING_PROCEDURE_NAME;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
if (!array_key_exists(STRING_PARAM_LIST, $_data)) {
$msg = ERROR_DATA_KEY_404 . STRING_PARAM_LIST;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
// get the name of the stored-procedure or function and store the param list if it exists b/c null
// values are allowed (the client MUST still define the key using a null placeholder.)
$spName = $_data[STRING_PROCEDURE_NAME];
// ensure that we have a stored procedure by that name defined in the class template
if (!array_key_exists($spName, $this->template->dbObjects[PDO_PROCEDURES])) {
$msg = sprintf(ERROR_SP_404, $spName, $this->class);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->state = STATE_VALIDATION_ERROR;
return;
}
$spParamList = $_data[STRING_PARAM_LIST];
$spParamCount = (is_array($spParamList)) ? count($spParamList) : 0;
// init the param count vars (template vars)
$templateInParamCount = 0;
$templateOutParamCount = 0;
if (is_array($this->template->dbObjects[PDO_PROCEDURES][$spName][STRING_TYPE_LIST]))
$templateTotalParamCount = count($this->template->dbObjects[PDO_PROCEDURES][$spName][STRING_TYPE_LIST]);
else
$templateTotalParamCount = 0;
// get the parameter distribution/assignment for the SP
if (!is_null($this->template->dbObjects[PDO_PROCEDURES][$spName][STRING_TYPE_LIST])) {
foreach ($this->template->dbObjects[PDO_PROCEDURES][$spName][STRING_TYPE_LIST] as $spTemplateName => $templateData) {
switch (key($templateData)) {
case STRING_IN :
$templateInParamCount++;
break;
case STRING_INOUT :
$templateInParamCount++;
$templateOutParamCount++;
$outList[] = $spTemplateName;
break;
case STRING_OUT :
$templateOutParamCount++;
$outList[] = $spTemplateName;
break;
default :
$msg = ERROR_DATA_INVALID_KEY . key($templateData);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->state = STATE_VALIDATION_ERROR;
return;
break;
}
}
}
// init some common vars...
$query = 'CALL /* ' . basename(__FILE__) . COLON . __METHOD__ . AT . __LINE__ . '*/ ' . $spName . '(';
$this->dbEvent = $this->template->dbObjects[PDO_PROCEDURES][$spName][STRING_SP_EVENT_TYPE];
$this->queryVariables = [];
/** @var PDOStatement $statement */
$statement = null;
try {
/*
* we have to check for three different types of stored procedures:
*
* 1. SP with no input parameters (can exec as a non-prepared query
* 2. SP with only IN parameters
* 3. SP with OUT or INOUT parameters (may also have IN params)
*
* each of these three cases has to be coded differently because of the way PDO sets-up input parameters,
* and processes/delivers output results using OUT parameters.
*
* todo: check the return data set and return a row-count in-concurrence with the framework limit
*
*/
if (is_null($spParamList) and !$templateTotalParamCount) {
// if the param list is empty, and there are no return parameters, then this is a simple fetch SP
$this->strQuery = $query . ')';
$this->executeNonPreparedQuery();
if (!$this->status) return;
} elseif ($templateInParamCount and !$templateOutParamCount) {
// We have a stored procedure with only IN params
for ($index = 0; $index < $spParamCount; $index++) {
$query .= '?, ';
}
$query = rtrim($query, ', ');
$query .= ')';
$this->strQuery = $query;
// prepare the PDO query
if (!$this->prepareQuery($statement)) return;
// bind the PDO params
// don't care about the retVal since this SP has no OUT/INOUT params...
@$this->bindSPQV($statement, $spParamList, $spName);
// exec the PDO query
// exec the query
if ($this->useTimers) $startTime = gasStatic::doingTime();
$res = $statement->execute();
if ($this->useTimers) $this->logger->metrics($this->strQuery, gasStatic::doingTime($startTime));
if ($res === false) {
$msg = ERROR_PDO_EXEC . $this->strQuery;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->state = STATE_DB_ERROR;
return;
}
$data = $statement->fetchAll();
foreach ($data as $row) {
$this->queryResults[] = $row;
}
} elseif ($templateOutParamCount) {
// INOUT/OUT stored-procedure
// build the query string
for ($index = 0; $index < $spParamCount; $index++) {
$query .= '?, ';
}
foreach ($outList as $field) {
$query .= AT . $field . ', ';
}
$query = rtrim($query, ', ');
$query .= ')';
$this->strQuery = $query;
// prepare the query
if (!$this->prepareQuery($statement)) return;
// bind the PDO params
$rc = $this->bindSPQV($statement, $spParamList, $spName);
if (!$rc) return; // errors are assigned in bingSPQV()
// exec the query
if ($this->useTimers) $startTime = gasStatic::doingTime();
$res = $statement->execute();
if ($res === false) {
$msg = ERROR_PDO_EXEC . $this->strQuery;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->state = STATE_DB_ERROR;
return;
}
// build the second query to fetch the OUT param data
$query = 'SELECT /* ' . basename(__FILE__) . COLON . __METHOD__ . AT . __LINE__ . '*/ ';
foreach ($outList as $field) {
$query .= AT . $field . ' AS ' . $field . ', ';
}
$query = rtrim($query, ', ');
if ($this->useTimers) $startTime = gasStatic::doingTime();
$this->queryResults = $this->dbcMain->query($query)->fetchAll(PDO::FETCH_ASSOC);
if ($this->useTimers) $this->logger->metrics($query, gasStatic::doingTime($startTime));
// todo -- debug this and see what's stored in $data
} else {
// if code lands here, we've got an error in the submitted parameters
$this->eventMessages[] = ERROR_SP_PARAM_PROC_FAIL;
$this->logger->warn(ERROR_SP_PARAM_PROC_FAIL);
$this->state = STATE_FRAMEWORK_WARNING;
return;
}
$this->dbEvent = DB_EVENT_CALL_SP;
$this->state = STATE_SUCCESS;
$this->status = true;
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $query) . COLON . $p->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
/**
* setMigrationBit() -- public method
*
* This method is available to anyone to toggle the isMigration indicator. The method requires a boolean value
* as the sole input parameter and is enforced through typeErrors.
*
* The method returns void.
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param bool $_bit
*
* HISTORY:
* ========
* 03-24-18 mks CORE-852: initial coding
*
*/
public function setMigrationBit(bool $_bit): void
{
$this->isMigration = $_bit;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/// PROTECTED METHODS BEGIN HERE ///
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
protected function _lockRecord()
{
consoleLog($this->res, CON_SYSTEM, ERROR_EMPTY_METHOD);
}
protected function _releaseLock()
{
consoleLog($this->res, CON_SYSTEM, ERROR_EMPTY_METHOD);
}
protected function _isLocked()
{
consoleLog($this->res, CON_SYSTEM, ERROR_EMPTY_METHOD);
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/// PRIVATE METHODS BEGIN HERE ///
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* queryBuilder() -- private method
*
* Doc: https://givingassistant.atlassian.net/wiki/display/INF/Namaste+Data+Payloads
*
* this method requires a single input: the array containing the query elements as submitted by the client.
*
* This method has it's own wiki page - please refer to the wiki page for implementation/usage details.
*
* Basically, the query builder both processed and validates the query portion of a client query request.
* (Other methods handle validation of the projection and the extras.)
*
* For each level of the query tree, we start a loop for every element in that level. Each level is validated
* appropriately. For example, at the attribute level, we validate that the attribute is a declared index
* for the current class and, at the value level, we validate that the value type matches the type
* declared for that attribute.
*
* When we're processing a query tree with more than one branch, we store each branch in a collective array until
* we encounter an operand at the attribute level. The presence of the operand declares that the previous
* branches will be joined by this operand. This is stored in $complexQuery.
*
* Any errors, or rules violations, will cause an error message to be generated and copied to the eventMessages
* property and output to the log file, and execution will immediately return back to the calling client and,
* instead of an array return value, a null will be sent back to the client.
*
* The over success/fail of the method can be determined by:
*
* -- state property is set to 'success'
* -- status property is set to true
* -- a non-null array is returned
*
* If all of the above conditions are not met, then query building failed.
*
* -----------------------------------------------------------------------------------------------------------------
* NEW FOR PDO CLASSES: complex queries!
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* The PDO version of the query builder supports complex queries in the form of:
*
* -- ( Field(1) { operator } { value } {OPERAND} Field(2) { operator } { value } )
* { OPERAND }
* -- ( Field(3) { operator } { value } {OPERAND} Field(4) { operator } { value } )
* -----------------------------------------------------------------------------------------------------------------
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_query
* @return string|null
*
* HISTORY:
* ========
* 09-28-17 mks CORE-572: initial coding (port from gacMongo class)
* 10-24-17 mks CORE-587: fixed processing errors for single-field compound queries
*
*/
private function queryBuilder(array $_query): ?string
{
$this->state = STATE_VALIDATION_ERROR;
$this->status = false;
$this->queryVariables = [];
$query = '';
$queryThreads = null;
$joinOperand = null;
$complexQuery = null;
$haveQuery = false;
$validOperands = [
OPERAND_AND,
OPERAND_OR,
OPERAND_NOT,
OPERAND_NOR,
OPERAND_NULL
];
$opCodes = [
OPERATOR_EQ => PDO_EQ,
OPERATOR_NE => PDO_NE,
OPERATOR_DNE => PDO_NE,
OPERATOR_NIN => PDO_NIN,
OPERATOR_IN => PDO_IN,
OPERATOR_NULL => PDO_NULL,
OPERATOR_NOT_NULL => PDO_NOT_NULL,
OPERATOR_GT => PDO_GT,
OPERATOR_GTE => PDO_GTE,
OPERATOR_LTE => PDO_LTE,
OPERATOR_LT => PDO_LT,
OPERAND_AND => OPERAND_AND,
OPERAND_NOT => OPERAND_NOT,
OPERAND_OR => OPERAND_OR,
OPERAND_NOR => OPERAND_NOR
];
// validate the query array as an array
if (!is_array($_query)) {
$msg = ERROR_DATA_MISSING_ARRAY . STRING_QUERY;
$this->eventMessages[] = $msg;
$this->logger->data($msg);
return null;
}
// start parsing the array beginning with the attribute elements
foreach ($_query as $attribute => $operandLevel) { // (L1 -> L2)
$tmp = null;
$fieldName = '';
// not the first iteration so store the last built query thread
if (!empty($query)) {
$queryThreads[] = $query;
$query = '';
}
// test to see if we're working with a join operand instead of an attribute... if so, then we want
// to join all the previously-stored query strings with this join operand into a single query string...
if (in_array($attribute, $validOperands) and is_null($operandLevel)) {
// set the query join operand to create a compound or complex query
$joinOperand = $attribute;
// // join two groups of complex queries todo -- this next line is the one you want to remove
// if (strlen($complexQuery) and empty($queryThreads)) $complexQuery .= ' ' . $joinOperand . ' ';
// join the query threads to create a complex query
if (is_array($queryThreads)) {
for ($index = 0, $cc = count($queryThreads); $index < $cc; $index++) {
if (empty($complexQuery)) {
$complexQuery = $queryThreads[$index];
} else {
$complexQuery .= ' ' . $joinOperand . ' ' . $queryThreads[$index];
}
}
}
$queryThreads = null;
break;
}
// step 1 -- is in the field list?
if (in_array(($attribute . $this->ext), $this->fieldList)) {
// Check to see if this is non-mapped value with the extension...
$fieldName = $attribute . $this->ext;
} elseif (in_array($attribute, $this->fieldList)) {
// .. of if the extension was included...
$fieldName = $attribute;
} elseif (in_array($attribute, $this->cacheMap)) {
// or if the user submitted the cache-mapped field alias
$fieldName = array_search($attribute, $this->cacheMap);
}
// return if we couldn't resolve the attribute (field) name
if ($fieldName == '') {
$msg = sprintf(ERROR_QB_ATTRIBUTE_404, $attribute, $this->class);
$this->eventMessages[] = $msg;
$this->logger->data($msg);
$this->logger->data(json_encode($_query));
$this->state = STATE_DATA_ERROR;
return null;
}
// validate that the fieldName is an indexed discriminant -- if not, reject the query
if (!in_array($fieldName, $this->indexes)) {
$msg = sprintf(ERROR_QB_NOT_INDEXED_KEY, $attribute, $this->class);
$this->eventMessages[] = $msg;
$this->logger->data($msg);
$this->state = STATE_INDEX_ERROR;
return null;
}
// we've validated the attribute name - move into the operand processing
foreach ($operandLevel as $operand => $operatorLevel) { // (L2 -> L3)
// for the attribute, drop down (one level) and validate the operand
if (!in_array($operand, $validOperands)) {
// operand is invalid
$msg = ERROR_QB_INVALID_OPERAND . $operand;
$this->eventMessages[] = $msg;
$this->logger->error($msg);
$this->state = STATE_DATA_ERROR;
return null;
} elseif (count($operatorLevel) != 1 and $operand == OPERAND_NULL) {
// cannot submit a null operand with more than one operator => value elements
$msg = sprintf(ERROR_QB_VALUE_COUNT, $operand, 1);
$this->eventMessages[] = $msg;
$this->logger->error($msg);
$this->state = STATE_DATA_ERROR;
return null;
} else {
// remember the current operator (level 2)
$op = ($operand == OPERAND_NULL) ? OPERAND_NULL : $operand;
}
// process the operators (level 3) and their values (level 4)
$query = '(';
$loopCounter = count($operatorLevel);
$currentCount = 1;
foreach ($operatorLevel as $operator => $values) { // (L3 -> L4)
if (empty($opCodes[$operator])) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = ERROR_QB_UNKNOWN_OPERATOR . $operator;
$this->logger->data($hdr . $msg);
$this->eventMessages[] = $msg;
$this->state = STATE_DATA_ERROR;
return null;
}
$query .= $fieldName . ' ' . $opCodes[$operator] . ' ';
if ($opCodes[$operator] == PDO_NULL or $opCodes[$operator] == PDO_NOT_NULL) {
$haveQuery = true;
} else {
if ($opCodes[$operator] == PDO_IN or $opCodes[$operator] == PDO_NIN) {
$query .= '(';
}
foreach ($values as $value) {
if (gettype($value) != $this->fieldTypes[$fieldName]) {
$msg = sprintf(ERROR_DATA_TYPE_MISMATCH_DETAILS, $fieldName, $this->fieldTypes[$fieldName], gettype($value));
$this->logger->data(ERROR_DATA_FIELD_DROPPED);
$this->logger->data($msg);
$this->eventMessages[] = ERROR_DATA_FIELD_DROPPED;
$this->eventMessages[] = $msg;
continue 2;
}
// $query .= ($currentCount < $loopCounter) ? '?, ' : '?';
$query .= '?, ';
$this->queryVariables[] = $value;
$haveQuery = true;
}
$query = rtrim($query, ', ');
if ($opCodes[$operator] == PDO_IN or $opCodes[$operator] == PDO_NIN) {
$query .= ')';
}
}
if ($currentCount++ < $loopCounter) {
$query .= ' ' . $op . ' ';
}
}
$query = rtrim($query, ', ');
$query .= ')';
}
}
if ($haveQuery) {
$this->state = STATE_SUCCESS;
$this->status = true;
} else {
$this->state = STATE_DATA_ERROR;
}
return($complexQuery ?? $query);
}
/**
* buildGroupOrderBy() -- private method
*
* This method builds both the group and order-by clauses of the query. The method requires two input parameters,
* both of which are arrays and that represent the broker payloads for the group-by and order-by payloads,
* respectively. (Note that the input parameters are vetted as arrays in the calling client code.)
*
* First, we build the group-by piece of the query -- both query discriminants have the same format
* requirements (with the exception of the WITH ROLLUP option for group-by), and both can contain multiple
* fields.
*
* Each field is handed off to a sub-routine in the core that validates the field as a class member and returns
* the fully-qualified field name if processing completes successfully.
*
* For any error that would prevent successful generation/processing for either query discriminant, we log the
* processing/validation error and immediately return processing control to the calling client.
*
* All fields are also required to be index fields for the class. Not being an indexed field will cause the
* request to be rejected.
*
* If we have a valid group-by and no order-by, we're going to assign a null value to the order-by query so
* that, in the calling client, we append "ORDER BY null" to the end of the group-by query so as to reduce the
* overhead of group-by sorting.
*
* The method does not explicitly return any value - however, if processing was successfully completed, then
* we set the class state to STATE_SUCCESS -- which the calling client should validate on return from this method.
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array|null $_gb
* @param array|null $_ob
*
* HISTORY:
* ========
* 10-09-17 mks original coding
* 10-24-17 mks CORE-587: A parameter declaration can be made to accept null if the default value
* of the parameter is set to null
* 12-05-18 mks DB-55: fixed bug - it's ok to pass-in an empty group-by with a not-empty order-by
*
*/
private function buildGroupOrderBy(array $_gb = null, array $_ob = null): void
{
// groupBy: validate data and process
if (empty($_gb)) {
$this->queryGroupBy = null; // nothing to do so do nothing
} elseif (is_array($_gb) and count($_gb) != 1) {
$msg = sprintf(ERROR_DATA_ARRAY_COUNT, 1, STRING_GROUP_BY_DATA, count($_gb));
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
} elseif (is_array($_gb) and !empty($_gb)) {
// get the gb-key and validate as a valid column/member
foreach ($_gb as $field => $value) {
// check for the with-rollup option; if exists, should be last array item so exit loop
if (strtoupper($field) == STRING_WITH_ROLLUP) {
$this->queryGroupBy .= ' ' . STRING_WITH_ROLLUP;
break;
}
try {
$gbKey = $this->cmFieldValidation($field);
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
if (is_null($gbKey)) return;
// ensure that the group-by field is an indexed column
if (!in_array($gbKey, $this->indexes)) {
$msg = sprintf(ERROR_GB_NOT_INDEXED_KEY, $gbKey, $this->class);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->queryGroupBy = null;
return;
}
// validate the GB discriminants in the submitted payload
$this->queryGroupBy .= (empty($this->queryGroupBy)) ? $gbKey : ', ' . $gbKey;
if (!empty($_gb[$gbKey]) and strtoupper($_gb[$gbKey]) == STRING_SORT_ASC or strtoupper($_gb[$gbKey]) == STRING_SORT_DESC) {
$this->queryGroupBy .= $_gb[$gbKey];
}
}
}
// process order-by
if (is_null($_ob) or empty($_ob)) {
if (is_null($this->queryOrderBy)) {
$this->queryGroupBy = null;
$this->state = STATE_SUCCESS;
return;
}
// To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL:
$this->queryOrderBy .= ' ORDER BY null';
$this->queryGroupBy = null;
$this->state = STATE_SUCCESS;
return;
} elseif (is_array($_ob)) {
foreach ($_ob as $field => $value) {
try {
$obKey = $this->cmFieldValidation($field);
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
return;
}
if (is_null($obKey)) return;
// ensure that the order-by column is an indexed column
if (!in_array($obKey, $this->indexes)) {
$msg = sprintf(ERROR_GB_NOT_INDEXED_KEY, $obKey, $this->class);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->queryOrderBy = null;
return;
}
$this->queryOrderBy .= (empty($this->queryOrderBy)) ? $obKey : ', ' . $obKey;
if (!empty($_ob[$obKey]) and strtoupper($_ob[$field]) == STRING_SORT_ASC or strtoupper($_ob[$field]) == STRING_SORT_DESC) {
$this->queryOrderBy .= ' ' . $_ob[$field];
} else {
$msg = ERROR_DATA_ORDER_BY_INVALID_VALUE . $value;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
}
} else {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_ORDER_BY_DATA;
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return;
}
$this->state = STATE_SUCCESS;
}
/**
* buildReturnFieldList() -- private method
*
* This method is called from any generic CRUD query and is used to build an exact list of fieldNames to be
* returned from the class table. The input parameter to the method is the list of field names that was generated
* by the client - this list can be empty or an indexed array of field names.
*
* Since we don't know if we're dealing with cache-mapped names, or names that may or may-not have the extension
* properly appended, we're going to validate each field name against the cacheMap, the exposedFields list,
* or, finally, just the $fieldList. We'll also try to find the field using the provided field name with and
* without the class extension.
*
* If the $_fields data passed is empty, then were going to return the mySQL wildcard as the return string to
* return all fields. (Note that restricted fields will be filtered by the view invoked.)
*
* If the $_fields data passed is an array, iterate through every field name and validate it against the
* various member field lists until found. If the field name was not found, generate an event message.
*
* If not fields qualified, the method returns a null - otherwise it returns a string that will be used in
* the query.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array|null $_fields
* @return null|string
*
* HISTORY:
* ========
* 10-05-17 mks CORE-584: original coding
*
*/
private function buildReturnFieldList(array $_fields = null): ?string
{
$rds = ''; // return data set
if (is_null($_fields)) {
// return all the fields
return '*';
}
if (!is_array($_fields)) {
$this->eventMessages[] = ERROR_DATA_MISSING_ARRAY . STRING_RETURN_DATA;
if ($this->debug) $this->logger->debug(ERROR_DATA_MISSING_ARRAY . STRING_RETURN_DATA);
return null;
}
foreach ($_fields as $columnName) {
$found = false;
// auto-filter out the id/id_{ext} field if supplied
if ($columnName == PDO_ID or $columnName == (PDO_ID . $this->ext)) continue;
if ($this->useCache and in_array($columnName, $this->cacheMap)) {
$field = array_search($columnName, $this->cacheMap);
$rds .= (empty($rds)) ? $field : COMMA . $field;
$found = true;
} elseif (!empty($this->exposedFields)) {
if (in_array($columnName, $this->exposedFields)) {
$found = true;
$rds .= (empty($rds)) ? $columnName : COMMA . $columnName;
} elseif (in_array(($columnName . $this->ext), $this->exposedFields)) {
$found = true;
$rds .= (empty($rds)) ? ($columnName . $this->ext) : COMMA . ($columnName . $this->ext);
}
} else {
if (in_array($columnName, $this->fieldList)) {
$found = true;
$rds .= (empty($rds)) ? $columnName : COMMA . $columnName;
} elseif (in_array(($columnName . $this->ext), $this->fieldList)) {
$found = true;
$rds .= (empty($rds)) ? ($columnName . $this->ext): COMMA . ($columnName . $this->ext);
}
}
if (!$found) {
$msg = sprintf(ERROR_DATA_INVALID_CLASS_MEMBER, $columnName, $this->class);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
}
}
return($rds ?? null);
}
/**
* loadTemplate() -- private method
*
* this method is invoked by the constructor and serves to load the class template file, assimilating it into
* the current instantiation.
*
* template loads are done on the schema-instantiation level, instead of the core, because of the tiny changes in
* the template file(s) across various schemas. In other words, even though this code looks similar to the
* similarly-named methods in the other instantiation classes, it is not; do not move this method to the core.
*
* the method will load the class template and set the class member variables controlled/referenced by the
* template.
*
* successful loading of the template is determined by the return (boolean) value -- on error, a log message
* will be generated so it's up to the developer to check logs on fail-returns to see why their template
* file was not correctly assimilated.
*
* The template to be loaded is first derived in the constructor (post validation that the template file
* exists) and is pulled from the member variable (also set in the constructor) within this method.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @return bool
*
* HISTORY:
* ========
* 09-13-17 mks CORE-562: original coding
* 09-19-17 mks CORE-562: removing some field population b/c now handled in buildIndexReference()
* 06-08-18 mks CORE-1035: added migration and warehouse configuration data
* 06-12-18 mks CORE-1043: removed sql from dbObject in template prior to class assignment so as to
* maintain schema obfuscation.
* 05-09-18 mks DB-116: support for PDO view templates at load-time -- depending on the client type
* in $metaPayload, we assign the preferred view name for queries.
*/
private function loadTemplate():bool
{
try {
/** @var gatTestPDO template */
$this->template = new $this->templateName;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->warn($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
return false;
}
if (!is_object($this->template)) {
$this->logger->warn(ERROR_FILE_404 . $this->templateName);
$this->setState(ERROR_FILE_404 . $this->templateName);
return false;
}
if ($this->template->schema != TEMPLATE_DB_PDO) {
$this->logger->warn(ERROR_SCHEMA_MISMATCH . $this->template->schema . ERROR_STUB_EXPECTING . TEMPLATE_DB_PDO);
$this->setState(ERROR_SCHEMA_MISMATCH . $this->templateName);
return false;
}
// transfer meta data info to current instantiation
$this->dbService = $this->template->service;
$this->schema = $this->template->schema;
$this->collectionName = $this->template->collection . $this->template->extension;
// in this next section we establish the PDO view based on the requesting client type
if (isset($this->metaPayload) and isset($this->metaPayload[META_CLIENT])) {
switch ($this->metaPayload[META_CLIENT]) {
case CLIENT_AUDIT :
case CLIENT_SYSTEM :
$this->queryTable = PDO_VIEW_AUDIT . $this->collectionName;
break;
case CLIENT_CLIENT :
case CLIENT_CSR :
case CLIENT_UNIT :
case CLIENT_API :
default :
$this->queryTable = PDO_VIEW_BASIC . $this->collectionName;
break;
}
} else {
$this->eventMessages[] = ERROR_META_CLIENT_404;
return false;
}
$this->ext = $this->template->extension;
$this->useCache = $this->template->setCache;
$this->useDeletes = $this->template->setDeletes;
$this->useAuditing = $this->template->setAuditing;
$this->useJournaling = $this->template->setJournaling;
$this->allowUpdates = $this->template->setUpdates;
$this->useDetailedHistory = $this->template->setHistory;
$this->defaultStatus = $this->template->setDefaultStatus;
$this->searchStatus = $this->template->setSearchStatus;
$this->useLocking = $this->template->setLocking;
$this->useTimers = ($this->template->setTimers and gasConfig::$settings[CONFIG_DATABASE][CONFIG_DATABASE_QUERY_TIMERS]);
$this->pKey = $this->template->setPKey;
$this->useToken = $this->template->setTokens;
$this->debug = gasConfig::$settings[CONFIG_DEBUG];
$this->cacheExpiry = $this->template->cacheTimer;
// todo -- evaluate if we want to continue using the hidden-field functionality
if (isset($this->template->fields) and is_array($this->template->fields)) {
foreach ($this->template->fields as $key => $value) {
// if (!in_array($key, $this->hiddenColumns)) {
// $this->fieldList[] = ($key . $this->ext);
$this->fieldTypes[($key . $this->ext)] = $value;
// }
}
}
if (isset($this->template->protectedFields) and is_array($this->template->protectedFields)) {
foreach ($this->template->protectedFields as $field) {
if ($field != PDO_ID) {
$this->protectedFields[] = ($field . $this->ext);
}
}
}
if (isset($this->template->compoundIndexes) and is_array($this->template->compoundIndexes)) {
foreach ($this->template->compoundIndexes as $key => $subArray) {
foreach ($subArray as $keys) {
$this->compoundIndexes[$key][] = ($keys . $this->ext);
}
}
}
if (!is_null($this->template->cacheMap) and is_array($this->template->cacheMap)) {
foreach ($this->template->cacheMap as $key => $value) {
$this->cacheMap[($key . $this->ext)] = $value;
}
} else {
$this->cacheMap = [];
if (!empty($this->template->exposedFields) and is_array($this->template->exposedFields)) {
foreach ($this->template->exposedFields as $key) {
$this->exposedFields[] = ($key . $this->ext);
}
}
}
if (!is_null($this->template->binFields) and is_array($this->template->binFields)) {
foreach ($this->template->binFields as $key) {
$this->binaryFields[] = ($key . $this->ext);
}
}
// migration data
if (isset($this->template->migrationMap) and is_array($this->template->migrationMap)) {
$this->migrationConfig[STRING_MIGRATION_MAP] = $this->template->migrationMap;
} else {
$this->migrationConfig[STRING_MIGRATION_MAP] = sprintf(INFO_NOT_SET, STRING_MIGRATION_MAP);
}
$this->migrationConfig[STRING_MIGRATION_STATUS_KEY] = (isset($this->template->migrationStatusKV)) ? $this->template->migrationStatusKV : null;
$this->migrationConfig[STRING_MIGRATION_SORT_KEY] = (isset($this->template->migrationSortKey)) ? $this->template->migrationSortKey : null;
// warehouse data
$this->warehouseConfig = (isset($this->template->wareHouse)) ? $this->template->wareHouse : null;
if (is_array($this->template->dbObjects)) {
// remove sql schema -- should only be accessible to the deployment script and never exposed
// in a class instantiation object.
if (isset($this->template->dbObjects[PDO_SQL]) and is_array($this->template->dbObjects[PDO_SQL])) {
unset($this->template->dbObjects[PDO_SQL]);
}
foreach ($this->template->dbObjects[PDO_VIEWS] as &$view) {
if (array_key_exists(STRING_VIEW, $view)) {
unset($view[STRING_VIEW]);
}
}
// copy the cleaned template db objects into the current PDO class:
$this->dbObjects = $this->template->dbObjects;
}
if ($this->template->selfDestruct) {
$this->eventMessages[] = INFO_TEMPLATE_CLASS_DROPPED;
unset($this->template);
}
return true;
}
/**
* buildIndexReference() -- private method
*
* this method looks at the table defined in the current class instantiation and fetches the schema
* information about the table from mysql.
*
* The returned array structure for columns looks like this:
*
* Array
* (
* [Field] => email_usr
* [Type] => varchar(50)
* [Null] => NO
* [Key] => UNI
* [Default] =>
* [Extra] =>
* )
*
* We're looking for the column 'Key' to be not-empty as this indicates that the table column is indexed
* in some way. We're looking for values of UNI or PRI and these fields will populate the uniqueIndexes member.
*
* The second query we launch fetches the indexes from the table - we use this returned data to build the
* $indexes member list.
*
* The data ball we build, and will eventually cache, consists as as associate array of two elements - one
* containing the columns and the other containing the indexes for the class table.
*
* We want to save this information in an associative array so that, when we're parsing
* queries submitted to the mysql service, we can screen the query and prevent the execution of any
* query that does not use the indexed columns of the table.
*
* So, this method builds the following class data members:
*
* $indexes -- an indexed array listing all current indexes in the table regardless of type
* $uniqueIndexes -- an indexed array listing all unique indexes for the table
* $fieldList -- an indexed array listing all the fields (columns) for the table
*
* If query execution generates a mysql error, set a WARN message and return.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* HISTORY:
* ========
* 09-13-17 mks CORE-562: original coding
* 10-02-17 mks CORE-572: compensating for a mysql shortcoming where compound indexes aren't reported if
* their index-sequence is > 1 resulting in a significant refactoring.
* 03-01-18 mks CORE-689: explicitly setting state/status values
*
*/
private function buildIndexReference()
{
$data = null;
$fields = null;
$indexes = null;
$this->status = true;
$this->state = STATE_SUCCESS;
try {
// generate the cache key appropriate to the class and see if we've already cached this info
// based off a previous instantiation...
$cKey = CACHE_NAMASTE_KEY . '_' . CACHE_MYSQL_TABLE_SCHEMA . '_' . $this->collectionName;
if ($data = gasCache::get($cKey)) {
$data = json_decode(gzuncompress($data), true);
$checksum = $data[STRING_CHECKSUM];
unset($data[STRING_CHECKSUM]);
if (!gasCache::validateChecksum($data, $checksum, $this->eventMessages)) {
$msg = ERROR_CACHE_DATA_CHECKSUM . $cKey;
$this->logger->warn($msg);
$this->eventMessages[] = $msg;
$this->state = STATE_CACHE_ERROR;
$this->status = false;
}
} else {
// if we've not cached, or the cache processing failed, then fetch the data from the db
$this->dbEvent = DB_EVENT_NAMASTE_READ;
$this->strQuery = 'SHOW COLUMNS FROM ' . $this->collectionName;
$this->executeNonPreparedQuery();
if ($this->state != STATE_SUCCESS) {
$this->logger->warn(ERROR_SQL_FTL_COLUMNS);
$this->eventMessages[] = ERROR_SQL_FTL_INDEXES;
return;
} else {
foreach ($this->queryResults as $row) {
$data[STRING_COLUMNS][] = $row;
}
}
$this->strQuery = 'SHOW INDEX FROM ' . $this->collectionName;
$this->executeNonPreparedQuery();
if ($this->state != STATE_SUCCESS) {
$this->logger->warn(ERROR_SQL_FTL_INDEXES);
$this->eventMessages[] = ERROR_SQL_FTL_INDEXES;
return;
} else {
foreach ($this->queryResults as $row) {
$data[STRING_INDEXES][] = $row;
}
}
if (is_null(gasCache::add($cKey, gzcompress(json_encode($data, true))))) {
$this->state = STATE_CACHE_ERROR;
$this->status = false;
$msg = ERROR_CACHE_ADD_FAIL . $cKey;
$this->eventMessages[] = $msg;
$this->logger->warn($msg);
return;
}
}
// build the fieldList if we have data
if (!empty($data) and is_array($data)) {
foreach ($data[STRING_COLUMNS] as $row) {
$this->fieldList[] = $row[MYSQL_COLUMN_FIELD];
if (!empty($row[MYSQL_COLUMN_KEY])) {
if ($row[MYSQL_COLUMN_KEY] == MYSQL_INDEX_UNIQUE or $row[MYSQL_COLUMN_KEY] == MYSQL_INDEX_PRIMARY) {
$this->uniqueIndexes[] = $row[MYSQL_COLUMN_FIELD];
}
}
}
foreach ($data[STRING_INDEXES] as $row) {
if (!in_array($row[MYSQL_COLUMN_NAME], $this->indexes)) {
$this->indexes[] = $row[MYSQL_COLUMN_NAME];
}
}
} else {
$this->state = STATE_DATA_ERROR;
$this->status = false;
}
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
/**
* setRowsReturnedLimit() -- private method
*
* this function is called in the constructor for the current table instantiation.
*
* it looks at the information_schema table to get the average_row_length (arl) value for the table.
* this is a gross calculation - the more data in the table, the more accurate the value.
*
* if we can get the arl value from the information_schema, then divide this number into the system
* constant (max_data_returned) to see if the result is less-than or equal-to the system constant for the
* number of rows returned per query...
*
* if the calculated value is smaller, then allow the system constants to remain -- if not, adjust the system
* constant for the max_rows_returned so that the total amount of data remains under the system constant
* max_data_returned.
*
* @author mike@givingassistant.org
* @version 1.0
*
* HISTORY:
* ========
* 09-20-17 mks CORE-562: original coding
*
*/
private function setRowsReturnedLimit()
{
$key = PDO_DATA_DEFINITION . '_' . PDO_AVG_ROW_LEN . '_' . $this->collectionName;
$cacheData = null;
$this->dbEvent = MYSQL_EVENT_META;
try {
if ($cacheData = gasCache::get($key)) {
$cacheData = json_decode(gzuncompress($cacheData), true);
$this->recordLimit = $cacheData[PDO_RECORDS_PER_PAGE];
} else {
$schema = gasConfig::$settings[CONFIG_ID][CONFIG_ID_ENV] . UDASH;
$schema .= $this->config[CONFIG_DATABASE_PDO_APPSERVER][CONFIG_DATABASE_PDO_MASTER][CONFIG_DATABASE_PDO_DB];
// create the admin query
$this->strQuery = '-- noinspection SqlDialectInspection
SELECT AVG_ROW_LENGTH
FROM information_schema.tables
WHERE table_schema = "' . $schema . '"
AND table_name = "' . $this->collectionName . '"';
$this->dbEvent = DB_EVENT_NAMASTE_READ;
$this->executeNonPreparedQuery();
if ($this->state) {
if (($this->rowsAffected === 1) and (isset($this->queryResults[0][MYSQL_AVG_ROW_LENGTH]))) {
$arl = $this->queryResults[0][MYSQL_AVG_ROW_LENGTH];
if (($arl * $this->recordLimit) > MYSQL_MAX_DATA_RETURNED) {
$this->recordLimit = intval(MYSQL_MAX_DATA_RETURNED / $arl);
}
}
$cacheData[PDO_RECORDS_PER_PAGE] = $this->recordLimit;
if (!gasCache::add(PDO_DATA_DEFINITION . '_' . PDO_AVG_ROW_LEN . '_' . $this->collectionName, gzcompress(json_encode($cacheData, true)), gasCache::$cacheTTL)) {
$this->logger->warn('memcache:set failed - check log files');
}
}
}
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
/**
* executeNonPreparedQuery() -- private method
*
* this is the main method to execute all META and SELECT queries - any query that is not a prepared statement
* will execute here. Basically, namaste queries.
*
* upon invocation, the string passed (implicitly through the member variable: $query) will be cleaned through
* the common function, and then we'll evaluate the query based on the setting of the member variable $dbEvent.
* If $dbEvent is not META and not SELECT, then we're going to return with a WARN message requiring the client
* to use the prepared-query method.
*
* Next, parse the query and look for the "?" character - which is used as a place holder in prepared queries,
* and, if found, reject the request and return with a WARN message.
*
* Call a private method to see if the slave server is enabled and, if so, use it if the current query contains
* the SELECT keyword (meta queries will not use SELECT) and return the connection resource to a local variable.
*
* if query timers are enabled, then mark the start time and execute the query. record the end-time and log
* the query through the parent::method().
*
* Make a call to fetch the data as an associative array and post the results, along with the row count, to
* class variables.
*
* if the query generated an mysql error, generate an WARN message and return.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* HISTORY:
* --------
* 09-13-17 mks CORE-562: original coding
* 11-07-17 mks CORE-588: support for real-slaves
* 11-13-17 mks CORE-588: explicitly setting state/status, added error messages to eventMessages stack
* 11-29-17 mks CORE-593: allowing namaste system-read events to use the pdo slave resource
* 01-16-18 mks CORE-697: attempting to detect dropped PDO resource connection and reconnect
* 05-31-18 mks CORE-1011: update for new XML broker services configuration
*
*/
private function executeNonPreparedQuery(): void
{
$this->status = false;
$dbLink = $this->dbcMain;
$which = DB_MASTER;
// return immediately if this is a prepared query
if (stripos($this->strQuery, '?')) {
$this->eventMessages[] = ERROR_SQL_LOST_PREP_QUERY;
$this->eventMessages[] = $this->strQuery;
$this->logger->warn(ERROR_SQL_LOST_PREP_QUERY);
$this->logger->warn($this->strQuery);
$this->state = STATE_SCHEMA_ERROR;
return;
}
$startTime = floatval(0);
$this->queryResults = [];
// check to see if we can exec this schema command using the read-slave
/** @var $dbLink PDO */
if (gasResourceManager::$PDOSlaveEnabled and gasResourceManager::$PDOSlaveAvailable and ($this->dbEvent == DB_EVENT_SELECT or $this->dbEvent == DB_EVENT_NAMASTE_READ)) {
$dbLink = $this->dbcSlave;
$which = DB_SLAVE;
}
// if we've lost the connect (cannot ping) -- attempt to reconnect
if (!$this->ping($which)) {
$dbLink = gasResourceManager::reconnect($which);
if (is_null($dbLink)) {
$msg = ERROR_PDO_DROPPED;
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->logger->warn($msg);
$this->state = STATE_DB_ERROR;
return;
}
}
if ($this->debug) {
$this->logger->debug($this->strQuery);
}
// do not clean the query string if this is a system-generated query
if ($this->dbEvent != DB_EVENT_NAMASTE_READ and $this->dbEvent != DB_EVENT_NAMASTE_WRITE and $this->dbEvent != DB_EVENT_NAMASTE_READ) {
$this->strQuery = cleanQueryString($this->strQuery);
}
// last validation -- may change/delete this later depending if we have DESTRUCTIVE, non-prepared queries...
switch($this->dbEvent) {
case DB_EVENT_NAMASTE_READ :
case DB_EVENT_SELECT :
// do nothing
break;
default :
$this->logger->error(ERROR_SQL_NOT_PREP_STMNT);
$this->eventMessages[] = ERROR_SQL_NOT_PREP_STMNT;
return;
break;
}
try {
// start the query timer if supported
if ($this->useTimers) $startTime = gasStatic::doingTime();
$this->queryResults = [];
foreach ($dbLink->query($this->strQuery) as $row) {
$this->queryResults[] = $row;
}
// log the query time if supported
if ($this->useTimers) $this->logger->metrics($this->strQuery, gasStatic::doingTime($startTime));
$this->rowsAffected = (!empty($this->queryResults)) ? count($this->queryResults) : 0;
$this->state = STATE_SUCCESS;
$this->status = true;
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $this->strQuery);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
/**
* executePreparedQuery() -- private method
*
* This method has no input parameters and returns type void. Return values are implicitly returned via class
* members.
*
* The method is responsible for submitting a query, and it's array of positional parameters/variables to the
* PDO API for execution.
*
* On successful processing, we'll populate the $queryResults container with the data for processing by the
* calling client. The state/status variables will be set to indicate success and it's critical that either
* of these variable are evaluated by the calling client upon return.
*
* NOTES:
* ------
* PDO Fetch mode was set in the resourceManager on instantiation and is set to PDO::FETCH_ASSOC
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* HISTORY:
* ========
* 10-10-17 mks CORE-584: original coding
* 10-25-17 mks CORE-587: updated processing of successful results based on the database event type
* 10-27-17 mks CORE-586: update for update event
* 11-06-17 mks CORE-588: support for read-slave
* 11-29-17 mks CORE-593: support for read-slave on namaste-read system events
* 01-11-18 mks INF-147: support for namaste events in switch case
* 11-29-18 mks DB-55: de-janked which PDO resource to request when preparing query
* 01-16-19 mks DB-103: successful query returning no records now returns state = NOT_FOUND
*
*/
private function executePreparedQuery(): void
{
$this->status = false;
$this->state = STATE_DB_ERROR;
/** @var PDOStatement $ptrRecord */
$ptrRecord = null;
$whichPDOService = RES_PDO_MASTER;
if (!stripos($this->strQuery, '?')) {
$this->logger->warn(ERROR_SQL_NOT_PREP_QUERY);
$this->logger->warn($this->strQuery);
return;
}
if ($this->debug) $this->logger->debug($this->strQuery);
$startTime = floatval(0);
$this->queryResults = [];
// check to see if we can exec this schema command using the read-slave
if (gasResourceManager::$PDOSlaveEnabled and gasResourceManager::$PDOSlaveAvailable and ($this->dbEvent == DB_EVENT_SELECT or $this->dbEvent == DB_EVENT_NAMASTE_READ)) {
$whichPDOService = RES_PDO_SECONDARY;
}
try {
// start the query timer
if ($this->useTimers) $startTime = gasStatic::doingTime();
// prepare the query
if (!$this->prepareQuery($ptrRecord, $whichPDOService)) return;
// execute the query
$ptrRecord->execute($this->queryVariables);
// stop the query timer
if ($this->useTimers) $this->logger->metrics($this->strQuery, gasStatic::doingTime($startTime));
// based on query type, load the results
switch ($this->dbEvent) {
case DB_EVENT_UPDATE :
case DB_EVENT_NAMASTE_WRITE :
case DB_EVENT_DELETE :
$this->queryResults[] = sprintf(PDO_UP_DELETE_RESULTS, $ptrRecord->rowCount(), $this->dbEvent);
$this->rowsAffected = $ptrRecord->rowCount();
break;
case DB_EVENT_SELECT :
case DB_EVENT_NAMASTE_READ :
while ($row = $ptrRecord->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
$this->queryResults[] = $row;
}
$this->rowsAffected = (is_array($this->queryResults)) ? count($this->queryResults) : 0;
break;
}
$this->state = ($this->rowsAffected > 0) ? STATE_SUCCESS : STATE_NOT_FOUND;
$this->status = true;
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $this->strQuery);
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
/**
* ping() -- private method
*
* This is a health-check method to ensure that the PDO service (mysql) has not timed-out.
*
* The method takes a single, optional, input parameter which is the MASTER/SLAVE indicator switch. If no
* parameter is provided, then the switch defaults to the mysql master.
*
* We execute a simple query (select 1) and record the results returned. If the value returned is the excepted
* integer value of 1, return a boolean(true) value... otherwise, boolean(false) is returned.
*
* The query is exception wrapped which will trigger logging on the exception and return a boolean(false) back
* to the calling client.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param int $_which
* @return bool
*
*
* HISTORY:
* ========
* 01-16-18 mks CORE-697: Original coding
* 03-01-18 mks CORE-689: fixed bug where ping should have pre-check for the link resource != null
*
*/
private function ping(int $_which = DB_MASTER): bool
{
/** @var PDO $dbLink */
$dbLink = ($_which) ? $this->dbcMain : $this->dbcSlave;
if (!is_null($dbLink)) {
try {
$rc = intval($dbLink->query('SELECT 1')->fetchColumn(0));
return (1 === $rc);
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, 'SELECT 1');
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
return false;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_FAIL;
$this->status = false;
}
}
return false;
}
/**
* executePreparedBatchInsertQuery() -- private method
*
* This method is called by the _createRecord() method and is used for all new record inserts regardless of the
* record count (number of new records to insert). There are no input parameters to the method and the method
* returns type void.
*
* All data has been validated prior to reaching this point. So, what we should have ready to go is:
*
* -- the query strings stored as an array in $strQuery member
* -- the data payload stored as an array in $data member
*
* Because mysql is functionally bankrupt when it comes to batch-queries, we've generated an array of prepared
* queries ($this->strQuery), each row containing a number of positional parameters matching the corresponding
* row entry in $data.
*
* Once we've initialized some function variables and handled debug log events, if enabled, we're going
* to enter the exception block to process the prepared query and the new data to be inserted into the table.
*
* After calling the prepare() method on the query, we start the DB transaction and then loop through the records
* stored in $data -- we pull the values from the associative array and pass that array into the query-execute
* method.
*
* If no PDO exception is raised, then we commit the transaction and populate the query results into the
* relevant class members before returning.
*
* Note that, if an exception is trapped, the error output will only show the first query in the $strQuery
* array in the output...
*
* Success in processing is determined by the state/status member variables and, as always, is the calling clients
* responsibility to verify on return.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
*
* HISTORY:
* ========
* 10-23-17 mks CORE-585: original coding
* 01-16-18 mks CORE-697: support for prepareQuery() and console logging
* 03-27-18 mks CORE-852: added recordsInserted and recordsDropped support for migration report
* 05-23-18 mks _INF-188: support for pseudo-batch inserts
* 11-05-18 mks DB-55: support for audit/journaling, cleaned-up exception handling
* 01-16-19 mks DB-103: fixed bug in associative index keys for recovery query
* 01-22-20 mks DB-150: replaced strQuery with aryQuery because PHP7.4 strong type casting for class members
*
*/
private function executePreparedBatchInsertQuery(): void
{
/** @var PDOStatement $res */
$res = null;
$startTime = floatval(0);
if ($this->debug) $this->logger->debug(json_encode($this->aryQuery));
/** @var PDO $dbLink */
$dbLink = $this->dbcMain;
// validate we're working with queries stored as an array in $this->strQuery:
if (!is_array($this->aryQuery)) {
$error = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_STR_QUERY;
$this->logger->error($error);
$this->eventMessages[] = $error;
$this->state = STATE_DATA_ERROR;
$this->status = false;
return;
}
// ...and that each query is a prepared query:
$counter = 0;
foreach ($this->aryQuery as $query) {
if (!stripos($query, '?')) {
$this->logger->warn(ERROR_SQL_NOT_PREP_QUERY);
$this->logger->warn($query);
$this->eventMessages[] = ERROR_SQL_NOT_PREP_QUERY . $query;
$this->state = STATE_FRAMEWORK_WARNING;
$this->status = false;
return;
}
if ($this->useAuditing) {
$t = $this->data[$counter][(DB_TOKEN . $this->ext)];
if ($this->useJournaling) {
$q = [ DB_TOKEN => [ OPERAND_NULL => [ OPERATOR_EQ => [$t]]]]; // DB-103 fix
$u = [ DB_STATUS => STATUS_DELETED ];
if ($this->useDeletes) {
// hard deletes
$this->auditUndoQueries[] = [
BROKER_REQUEST => BROKER_REQUEST_DELETE,
BROKER_DATA => [ STRING_QUERY_DATA => $q ],
BROKER_META_DATA => $this->metaPayload
];
} else {
// soft delete
$this->auditUndoQueries[] = [
BROKER_REQUEST => BROKER_REQUEST_UPDATE,
BROKER_DATA => [ STRING_QUERY_DATA => $q, STRING_UPDATE_DATA => $u ],
BROKER_META_DATA => $this->metaPayload
];
}
}
$this->auditRecordList[] = $t;
$this->auditCreateQueries[] = [
BROKER_REQUEST => BROKER_REQUEST_CREATE,
BROKER_DATA => [$this->data[$counter++]],
BROKER_META_DATA => $this->metaPayload
];
}
}
$this->queryResults = [];
$this->dbEvent = DB_EVENT_BULK_CREATE;
try {
// start the query timer
if ($this->useTimers) $startTime = gasStatic::doingTime();
$dbLink->beginTransaction(); // BEGIN TRANSACTION
$count = 0;
foreach ($this->data as $record) {
/** @var PDOStatement $statement */
$statement = $this->dbcMain->prepare($this->aryQuery[$count++]);
$rc = $statement->execute(array_values($record));
if ($rc)
$this->recordsInserted++;
else
$this->recordsDropped++;
}
$dbLink->commit(); // END TRANSACTION
if ($this->useTimers) $this->logger->metrics($this->aryQuery[0] . ' x' . $count . ' ' . STRING_TIMES, gasStatic::doingTime($startTime));
$this->queryResults[] = sprintf(PDO_BULK_INSERT_RESULTS, $this->recordsInserted, $this->recordsDropped);
$this->state = STATE_SUCCESS;
$this->status = true;
$this->strQuery = json_encode($this->aryQuery);
} catch (PDOException | Throwable $p) {
$dbLink->rollBack();
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = sprintf(ERROR_PDO_EXCEPTION, $this->aryQuery[0]);
consoleLog(RES_PDO, CON_ERROR, $hdr . $msg);
consoleLog(RES_PDO, CON_ERROR, $hdr . $p->getMessage());
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($hdr . $msg);
$this->logger->warn($hdr . $p->getMessage());
}
$this->status = false;
$this->state = STATE_DB_ERROR;
}
}
/**
* getPreparedQueryCount() -- private method
*
* This method calculates the total number of rows returned from a prepared query. It's sole purpose is to fetch
* this value in order to aid in query pagination. As such, the query is slightly modified from the original
* query in that the skip/limit is removed, as is the query projection (replaced by count(*)).
*
* this method requires a single input parameter -- the query to be executed -- and returns a Boolean to indicate
* the processing success or the failure.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param $_query
* @return bool
*
*
* HISTORY:
* ========
* 10-13-17 mks CORE-584: original coding
*
*/
public function getPreparedQueryCount(string $_query): bool
{
$startTime = floatval(0);
/** @var $dbLink PDO */
$dbLink = $this->dbcMain;
$this->recordsInQuery = 0;
try {
if ($this->useTimers) $startTime = gasStatic::doingTime();
$ptrRecord = $dbLink->prepare($_query);
$ptrRecord->execute($this->queryVariables);
if ($this->useTimers) $this->logger->metrics($_query, gasStatic::doingTime($startTime));
foreach ($ptrRecord as $row) {
$this->recordsInQuery = $row[STRING_NUM_RECS];
}
if (is_null($this->recordsInQuery) or !is_numeric($this->recordsInQuery)) {
$this->eventMessages[] = ERROR_DATA_REC_COUNT_UNDEF;
if ($this->debug) $this->logger->debug(ERROR_DATA_REC_COUNT_UNDEF);
$this->recordsInQuery = 0;
}
return true;
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $_query);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn(STRING_QUERY . COLON . $_query);
if ($this->debug)
$this->logger->warn(STRING_QUERY_DATA . COLON . var_export($this->queryVariables, true));
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, STRING_QUERY . COLON . $_query);
if ($this->debug)
consoleLog($this->res, CON_ERROR, var_export($this->queryVariables, true));
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
return false;
} catch (Throwable $t) {
$msg = sprintf(ERROR_EXCEPTION . COLON . $this->strQuery[0]);
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $t->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($t->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $t->getMessage());
}
$this->status = false;
$this->state = STATE_DB_ERROR;
}
return false;
}
/**
* getQueryCount() -- private method
*
* This method is called to calculate the total number of rows returned by a previously-executed non-prepared
* query. We've removed the projection data from the query, replacing it with: count(*), and removed the
* skip/limit discriminant.
*
* The method requires a single, string-value, input parameter which is the modified (to get the row count) query.
* The method returns a boolean indicating processing success or failure.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param string $_query
* @return bool
*
*
* HISTORY:
* ========
* 10-13-17 mks original coding
*
*/
private function getQueryCount(string $_query): bool
{
$startTime = floatval(0);
/** @var $dbLink PDO */
$dbLink = $this->dbcMain;
try {
if ($this->useTimers) $startTime = gasStatic::doingTime();
// fetchColumn returns the value of a single column of a single row
$this->recordsInQuery = $dbLink->query($_query)->fetchColumn();
if ($this->useTimers) $this->logger->metrics($_query, gasStatic::doingTime($startTime));
return true;
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $_query);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn(STRING_QUERY . COLON . $_query);
if ($this->debug)
$this->logger->warn(STRING_QUERY_DATA . COLON . var_export($this->queryVariables, true));
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, STRING_QUERY . COLON . $_query);
if ($this->debug)
consoleLog($this->res, CON_ERROR, var_export($this->queryVariables, true));
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
return false;
} catch (Throwable $t) {
$msg = sprintf(ERROR_EXCEPTION . COLON . $this->strQuery[0]);
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $t->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($t->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $t->getMessage());
}
$this->status = false;
$this->state = STATE_DB_ERROR;
}
return false;
}
/**
* getRecordCount() -- private method
*
* this method is mainly invoked from the _fetchRecords() query -- since the client wants to know how many
* records there are in a collection whenever a read-query is performed, we're going to get that information
* within this method.
*
* There are no input parameters to the method and the method will return a boolean value to indicate the success
* or failure of executing the query.
*
* NOTES:
* ------
* todo -- We're using the table (collection) name and not the basic view, this should be changed to match the query
* todo -- should we be querying the read-slaves?
* If the table has hard-deletes enabled, filter the record count against the DELETED status
* Errors generated are stored in the event messages and are written to the logs as WARN level events
*
* @author mike@givingassistant.org
* @version 1.0
*
* @return bool
*
* HISTORY:
* ========
* 10-10-17 mks CORE-584: original coding
*
*/
private function getActiveRecordCount(): bool
{
/** @var $dbLink PDO */
$dbLink = $this->dbcMain;
$query = "SELECT /* " . basename(__FILE__) . COLON . __METHOD__ . COLON . __LINE__ . ' */ ';
$query .= 'count(1) AS recordCount FROM ' . $this->collectionName;
if (!$this->useDeletes) { // hard deletes
$query .= ' WHERE status' . $this->ext . ' ' . PDO_NE . ' "' . STATUS_DELETED . '"';
}
try {
// fetchColumn returns the value of a single column of a single row
$this->recordsInCollection = $dbLink->query($query)->fetchColumn();
return true;
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $this->strQuery);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
return false;
} catch (Throwable $t) {
$msg = sprintf(ERROR_EXCEPTION . COLON . $this->strQuery[0]);
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $t->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($t->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $t->getMessage());
}
$this->status = false;
$this->state = STATE_DB_ERROR;
}
return false;
}
/**
* prepareDataForInsert() -- private method
*
* This method is called when we're preparing a data payload for insert into the database. Because the SQL create
* command doesn't allow for column variance, we're going to pad missing data elements with null values creating,
* in effect, a homogeneous data payload so that we can execute a batch-insert command in one query.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
*
* HISTORY:
* ========
* 10-23-17 mks CORE-585: original coding
*
*/
private function prepareDataForInsert(): void
{
$data = [];
$counter = 0;
foreach ($this->data as &$record) {
foreach ($this->fieldList as $field) {
if ($field != (PDO_ID . $this->ext)) {
if (!array_key_exists($field, $record)) {
$record[$field] = null;
if ($this->debug) $this->logger->debug(sprintf(INFO_INSERTED_FIELD, $field));
}
$data[$counter][$field] = $record[$field];
}
}
$counter++;
}
$this->data = $data;
}
/**
* buildWhereDiscriminant() -- private method
*
* This method is called from the update and delete methods. The method requires a single input parameter:
*
* $_queryData: the query discriminant array passed in from the client
*
* This is a gateway function to queryBuilder() but is encapsulated in a TypeError exception trap and, to avoid
* code duplication in the two calling clients was broken-out into it's own method.
*
* The method returns a boolean value to indicate successful processing.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_queryData
* @return bool
*
* HISTORY:
* ========
* 10-26-17 mks CORE-586: original coding
*
*/
private function buildWhereDiscriminant(array $_queryData): bool
{
$rc = false;
try {
$this->where = $this->queryBuilder($_queryData);
$rc = true;
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_WARNING;
}
return $rc;
}
/**
* buildOrderBy() -- private method
*
* This method is called by the update and delete methods and is used to create the order-by part of a query.
*
* The method requires a single input parameter -- this is the order-by data payload as built by the end-user.
*
* The method is a gateway to the buildGroupOrderBy() method but, because it is exception wrapped for TypeErrors,
* it was moved to this method to reduce code duplication.
*
* The method returns a boolean to indicate success or fail in processing.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_orderByData
* @return bool
*
* HISTORY:
* ========
* 10-26-17 mks CORE-586: original coding
*
*/
private function buildOrderBy(array $_orderByData): bool
{
$rc = false;
try {
$this->buildGroupOrderBy(null, $_orderByData);
$rc = true;
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->status = false;
$this->state = STATE_FRAMEWORK_WARNING;
}
return $rc;
}
/**
* prepareQuery() -- private method
*
* This method is used from the stored-procedure/function builder methods and is used to submit a query
* to the pdo::prepare() method.
*
* The method accepts two input parameters. One of which is a PDO Statement class object, generated
* success or failure of successfully preparing the query. The other indicates which PDO resource to use
* for the query where the resource is defined in the dbCatalog.php file as either DB_MASTER (1) or DB_SLAVE (0)
* with the parameter default set to DB_MASTER.
*
* The method exists to reduce the code footprint of this class.
*
* If the prepare statement throws an exception, within the exception handler, we're going to attempt to
* reconnect to the PDO master database assuming that the error was from a dropped connection. Within the
* exception handler, after we make the call to create a new connection resource, we'll test the return value
* and, if it is not null, we'll re-attempt the prepare statement.
*
* Because you can't next exception-handling, we'll have to check that the prepare didn't return a Boolean(false)
* value meaning that the statement was successfully prepared, or generated an error message.
*
* Also added explicit console logging as part of the error-processing.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param PDOStatement $_statement
* @param int $_which
* @return bool
*
*
* HISTORY:
* ========
* 11-07-17 mks CORE-588: original coding
* 01-16-18 mks CORE-697: dropped PDO master connection handler, added second parameter
* 03-27-18 mks CORE-852: improved the handling of cascading exception trapping
* 11-29-18 mks DB-55: fixed processing and ack $_which param
*
*/
private function prepareQuery(PDOStatement &$_statement = null, int $_which = RES_PDO_MASTER): bool
{
$this->state = STATE_SUCCESS;
$this->status = false;
/** @var PDO $resPDO */
$resPDO = $this->dbcMain;
// don't execute a query against the PDO slave if the query isn't a select query
if ($_which === RES_PDO_SECONDARY) {
if ($this->dbEvent != DB_EVENT_SELECT) {
$this->logger->error(ERROR_PDO_SLAVE_ERROR . $this->dbEvent);
$this->eventMessages[] = INFO_PDO_SLAVE_SWITCH;
$_which = RES_PDO_MASTER;
}
$resPDO = $this->dbcSlave;
}
// if we lost the PDO connection, attempt to reconnect to the proper resource
if (empty($resPDO) or !isset($resPDO)) {
if ($resPDO == RES_PDO_MASTER) {
// attempt to reconnect to the PDO resource
try {
$resPDO = gasResourceManager::reconnect(($_which == RES_PDO_MASTER) ? RESOURCE_PDO_MASTER : RESOURCE_PDO_SECONDARY);
if ($_which == RES_PDO_MASTER)
$this->dbcMain = $resPDO;
else
$this->dbcSlave = $resPDO;
$this->logger->warn(ERROR_PDO_RECONNECT);
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available) {
$this->logger->fatal(ERROR_PDO_RECONNECT_FAIL);
$this->logger->fatal($msg);
} else {
consoleLog($this->res, CON_ERROR, $msg);
}
$this->status = false;
$this->state = STATE_FRAMEWORK_FAIL;
return false;
}
}
}
try {
// errors in this prepare handled by the exception
$_statement = $this->dbcMain->prepare($this->strQuery);
} catch (PDOException $p) {
// failed to prepare the query - assume a dropped connection but capture diagnostics
$msg = sprintf(ERROR_PDO_PREPARE, $this->strQuery);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
return false;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_WARNING;
return false;
}
if (!is_null($this->dbcMain)) {
try {
$_statement = $this->dbcMain->prepare($this->strQuery);
if ($_statement === false) {
$msg = sprintf(ERROR_PDO_PREPARE_2, $this->strQuery);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
$this->state = STATE_DB_ERROR;
} else {
$this->status = true;
return true;
}
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_PREPARE, $this->strQuery);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->warn($msg);
$this->logger->warn($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
$this->state = STATE_DB_ERROR;
return false;
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->state = STATE_FRAMEWORK_WARNING;
return false;
}
} // todo -- else?
return true;
}
/**
* bindSPQV() -- private function
*
* Bind Stored Procedure Query Variables
*
* This method requires three input parameters:
*
* $_statement -- which is a PDOStatement resource previous built (otherwise, we'd not have made it to this point)
* $_spParamList -- this is the indexed array of variable data supplied by the client in the broker call
* $_spName -- the name of the stored procedure to be invoked, as provided by the requesting client
*
* The method loops through the list of of the stored-procedure parameter types as defined in the template, for the
* current class, and calls the PDO::bindParam() method using positionally-dependent notation:
*
* PDO::bindParam( {intVal}, {Variable}, {PDO-TYPE} )
*
* ... where {intVal} is the position of the variable as it appears in the input-parameter list for the stored
* procedure using an index-starts-at-1 counter.
* ... {Variable} is the literal value received, sent from the client via a broker event
* ... {PDO-Type} is the pdo type declaration (e.g: PDO::PARAM_INT) pulled from the SP template definition
*
* NOTE:
* -----
* According to the PDO doc for PHP here: http://php.net/manual/en/pdo.constants.php
* There is not a declared type defined for float values -- however, there's a note for sqlLite that says to
* use PDO::PARAM_STR for floats...
*
* If the bindParam() method succeeds, we'll return boolean(true), otherwise a boolean(false) is returned.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param PDOStatement $_statement
* @param array $_spParamList
* @param string $_spName
* @return bool
*
*
* HISTORY:
* ========
* 11-08-17 mks CORE-588: original coding
*
*/
private function bindSPQV(PDOStatement $_statement, array $_spParamList, string $_spName): bool
{
$index = 0;
foreach ($this->template->dbObjects[PDO_PROCEDURES][$_spName][STRING_TYPE_LIST] as $key => $param) {
foreach ($param as $direction => $paramType) {
try {
if ($direction != STRING_OUT) {
$res = $_statement->bindParam(($index + 1), $_spParamList[$index], $paramType);
if ($res === false) {
$msg = sprintf(ERROR_PDO_BIND, (string)$_spParamList[$index], $this->strQuery, $index);
$this->eventMessages[] = $msg;
if ($this->debug) $this->logger->debug($msg);
return false;
}
}
} catch (PDOException $p) {
$msg = sprintf(ERROR_PDO_BIND, $_spParamList[$index], $this->strQuery, $index);
$this->eventMessages[] = $msg;
$this->eventMessages[] = $p->getMessage();
if (isset($this->logger) and $this->logger->available) {
$this->logger->error($msg);
$this->logger->error($p->getMessage());
} else {
consoleLog($this->res, CON_ERROR, $msg);
consoleLog($this->res, CON_ERROR, $p->getMessage());
}
return false;
} catch (TypeError $t) {
$msg = ERROR_TYPE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->status = false;
$this->state = STATE_FRAMEWORK_WARNING;
return false;
}
$index++;
}
}
return true;
}
/**
* getPDOResources() -- private method
*
* This method requires no input methods and returns a boolean which indicates if the PDO resource, minimally just
* the master, is available for queries.
*
* If the resourceManager has flagged the PDO resource as available, something that happens during bootstrapping,
* then we're going to verify that the connection is still active as, over a period of enough time, there is a risk
* that the PDO connection may timeout.
*
* First, then, is to ping the PDO master resource and, if it does not respond, attempt a reconnect. If we cannot
* reconnect, mark the connection as dropped/bad and return.
*
* If the connection is good, then repeat the process for the PDO slave resource. If the PDO slave resource, which
* must be enabled, is lost, we'll mark the resource as unavailable, but we will return a boolean(true) if the
* PDO-master resource is available.
*
* PDO MASTER PDO SLAVE RETURNS
* ---------------------------------------------
* available available true
* available not-available false
* not-available doesn't matter false
*
* Error messages are recorded to the console and admin logs, and to the eventMessages stack.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @return bool
*
*
* HISTORY:
* ========
* 01-18-18 mks CORE-697: original coding
* 03-01-18 mks CORE-689: fixed bug where dbSlave resource wasn't being stored in the member variable
* 05-09-18 mks _INF-188: segundo service support
* 08-30-18 mks DB-50: sneakerstrap patching for lite-initialization
*
*/
private function getPDOResources(): bool
{
if (!gasResourceManager::$PDOAvailable and !gasResourceManager::$available)
$this->dbcMain = gasResourceManager::fetchResource(RESOURCE_PDO_MASTER);
if (!gasResourceManager::$PDOSlaveAvailable and !gasResourceManager::$available)
$this->dbcSlave = gasResourceManager::fetchResource(RESOURCE_PDO_SECONDARY);
switch ($this->dbService) {
case CONFIG_DATABASE_SERVICE_APPSERVER :
$resDBMain = RESOURCE_PDO_MASTER;
$service = CONFIG_DATABASE_PDO_APPSERVER;
$resDBSlave = RESOURCE_PDO_SECONDARY;
break;
case CONFIG_DATABASE_SERVICE_SEGUNDO :
$resDBMain = RESOURCE_WH_COOL_PDO_MASTER;
$service = CONFIG_DATABASE_PDO_SEGUNDO;
$resDBSlave = RESOURCE_WH_COOL_PDO_SECONDARY;
break;
default :
$error = (isset($this->dbService)) ? (ERROR_SERVICE_404 . $this->dbService) : ERROR_SERVICE_UNK;
$this->eventMessages[] = $error;
$this->logger->warn($error);
return false;
break;
}
if (gasResourceManager::$PDOAvailable) {
try {
$this->dbcMain = gasResourceManager::fetchResource($resDBMain);
// check if we've lost the connect (cannot ping) -- attempt to reconnect to the master
if (!$this->ping(DB_MASTER)) {
$dbLink = gasResourceManager::reconnect($resDBMain);
if (is_null($dbLink)) {
$msg = ERROR_PDO_DROPPED;
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->logger->warn($msg);
$this->dbMainAvailable = false;
$this->state = STATE_RESOURCE_ERROR_PDO;
return false;
}
$this->dbcMain = $dbLink;
}
$this->dbMainAvailable = (is_object($this->dbcMain)) ? true : false;
if ($this->config[$service][CONFIG_DATABASE_PDO_USE_SECONDARY] and $this->dbMainAvailable) {
$this->dbcSlave = gasResourceManager::fetchResource($resDBSlave);
$this->dbSlaveAvailable = true;
// check to see that the slave connection is still active - attempt to reconnect on fail
if (!$this->ping(DB_SLAVE)) {
$this->dbcSlave = gasResourceManager::reconnect($resDBSlave);
if (is_null($this->dbcSlave)) {
$msg = ERROR_PDO_SLAVE_DROPPED;
consoleLog(RES_PDO, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
$this->logger->warn($msg);
$this->dbSlaveAvailable = false;
}
}
} else {
$this->dbSlaveAvailable = false;
$this->dbcSlave = null;
}
} catch (Throwable $t) {
$msg = ERROR_THROWABLE_EXCEPTION . COLON . $t->getMessage();
$this->eventMessages[] = $msg;
if (isset($this->logger) and $this->logger->available)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->status = false;
$this->state = STATE_FRAMEWORK_WARNING;
}
} else {
if (isset($this->logger) and $this->logger->available)
$this->logger->fatal(ERROR_RESOURCE_PDO_NOT_AVAIL);
else
consoleLog(RES_PDO, CON_SYSTEM, ERROR_RESOURCE_PDO_NOT_AVAIL);
$this->eventMessages[] = ERROR_RESOURCE_PDO_NOT_AVAIL;
$this->dbMainAvailable = false;
$this->dbSlaveAvailable = false;
$this->dbcSlave = null;
$this->dbcMain = null;
$this->state = STATE_RESOURCE_ERROR_PDO;
return false;
}
if ($this->dbMainAvailable) return true;
consoleLog(RES_PDO, CON_SYSTEM, ERROR_PDO_CONNECT);
return false;
}
/**
* checkProtectedFields() -- protected method
*
* This method checks the part of the query defined by STRING_UPDATE_DATA in an update event, for fields that
* the client wants to update, against the classes' protectedFields array.
*
* The method requires one input parameter: $_data -- an associative array of column names that have been
* cache-mapped-processed and their respective values (for the update query).
*
* If the $_data parameter submitted is not an array or is empty, return a false back to the calling client
* signalling that the data payload failed validation.
*
* If a field in $_data exists in the protectedFields list, then delete that key->value pair from the
* submitted array.
*
* If we remove all of the fields in the $_data array, then we'll return a false to the calling client to indicate
* that array failed (completely) validation.
*
* Otherwise, we have validate the update-portion of the query and so we'll return a Boolen true to the client.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param array $_data
* @return bool
*
* HISTORY:
* ========
* 09-11-17 mks CORE-558: original coding
* 10-26-17 mks CORE-586: moved to the core from mongo instantiation class
* 11-26-18 mks DB-90: fixed bug in that we're dropping a *row* of data instead of a protected field when
* there's a protected field violation (e.g.: just drop the field)
* 02-05-19 mks DB-107: bug: if last element in an array is unset b/c of protected field violation, then
* ($_data[0] = []) evals true... added check for this
* 03-29-19 mks DB-116: moved from core to PDO class and coded it specifically for a STRING_UPDATE_DATA
* payload, specific to PDO, check. Added check to ensure protected fields member
* exists and is correctly declared as an array
*
*/
protected function checkProtectedFields(array &$_data): bool
{
$loggerAvailable = (isset($this->logger) and $this->logger->available);
$msg = '';
if (empty($_data)) {
$msg = ERROR_DATA_ARRAY_EMPTY . COLON . STRING_DATA;
} elseif (!is_array($_data)) {
$msg = ERROR_DATA_ARRAY_NOT_ARRAY . STRING_DATA;
}
if (empty($this->protectedFields) or !is_array($this->protectedFields)) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = ERROR_PF_404;
$this->eventMessages[] = $msg;
$this->logger->warn($hdr . $msg);
return false;
}
if (!empty($msg)) {
if ($loggerAvailable)
$this->logger->error($msg);
else
consoleLog($this->res, CON_ERROR, $msg);
$this->eventMessages[] = $msg;
return false;
}
// we'll copy all the $_data over to $dataCopy
$dataCopy = null;
// scan each key in the submitted data (field => value) and if the field exists in the protected-fields
// member for the class, then drop the field from the query array, log a message to the event-messages
// stack.
foreach ($_data as $key => $value) {
if (in_array($key, $this->protectedFields) or in_array(($key . $this->ext), $this->protectedFields)) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = sprintf(ERROR_QB_PF_VIOL, $key);
$this->eventMessages[] = $msg;
if ($loggerAvailable) $this->logger->data($hdr . $msg);
} else {
if (in_array($key, $this->fieldList))
$dataCopy[$key] = $value;
elseif (in_array($key . $this->ext, $this->fieldList))
$dataCopy[($key . $this->ext)] = $value;
else {
// update field is not a valid member of the current data class, so drop the
// field from the update request while making note of the drop in eventMessages
$msg = ERROR_DATA_FIELD_NOT_MEMBER . $key;
$this->eventMessages[] = $msg;
}
}
}
$_data = $dataCopy;
return((empty($_data)) ? false : true);
}
}
Reference in New Issue View Git Blame Copy Permalink