gramps/namaste
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
namaste/utilities/mysqlConfig.php
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

1064 lines
53 KiB
PHP
Raw Permalink Blame History

<?php
/**
* mysqlConfig.php -- mysql template deployment script
*
* Whenever you stage a new version of Namaste to an environment, you will need to run this script. This script
* will perform the following function:
*
* Rudimentary Template Validation
* -------------------------------
* The script will only process PDO templates (mariaDB) within the current environment. Within each template, the
* script will stop execution if certain fields are missing or in the incorrect format.
*
* Create New Tables
* -----------------
* When a new table is required, the script will create the table from the SQL stored in the template.
*
* Update Existing Tables
* ----------------------
* There is a separate update section for each table and this will always be executed.
*
* Install Database Objects
* ------------------------
* The script will install all database objects defined in the template. This includes views, stored procedures and
* functions, events and triggers.
*
* Because we're anticipating schema changes, all database objects will be dropped and re-created. However, SQL code
* in the PDO_SQL code blocks will only be executed when the defined release version (PDO_VERSION) matches the version
* value defined in the XML configuration file.
*
*
* Finally, this script can be run without the brokers being active in the current environment.
*
*
* PROGRAMMER'S NOTES:
* -------------------
* When defining a stored object, other than a view, that requires a DELIMITER statement, you must defines, separately,
* the DROP {$OBJECT} statement. Additionally, PHP does not support DELIMITER statements so your database object
* code cannot contain DELIMITER re-definitions.
*
* I made several comments about "current environment". As of this writing, PDO (mariaDB) is supported in both the
* namaste (Prime) and Segundo environment. That means you must run this script for each environment, in each
* environment, in order to correctly deploy.
*
* All non-standard functions referenced in this program are in this listing, included at the end of the file.
*
* Debug Run Options:
* ------------------
* Run -> Edit Configurations -> Arguments
*
* Put this in the text input: -k --env=dev --user=mshallop --password=einstein
*
* To debug w/out creating an application account: -k --env=dev
*
*
* @author mike@givingassistant.org
* @version 1.0
*
*
* HISTORY:
* ========
* 07-02-18 mks CORE-1045: original coding
* 05-21-19 mks DB-116: processing of templates updates s.t. template processing only happens in the
* environment for which it is declared
* 07-15-19 mks DB-124: support for arguments, limited app to cli, support for dynamically dropping indexes
* 08-26-19 mks DB-111: re-write to support creation of a new *database* and database user, support for
* environments hosted off localhost -> env-based processing, refactored output to consoleLog only
* 10-09-19 mks DB-136: fixed bug where db name was getting appended to previous db name on every iteration
* of the drop-index loop
*/
$_REDIRECT=false; // enable stdout
require_once(dirname(__DIR__) . '/config/sneakerstrap.inc'); // load env
global $topDir;
// exit with an error message if the app was launched by a browser
if (!empty($_REQUEST)) echo 'This app can only be run from the command line.' && exit;
if (empty($argv)) printHelp($argv[0]) && exit;
// ---------------------------------------------------------------------------------------------------------------------
// lvar initialization
$shortOptions = 'e:t::k::u::p::h::'; // get the cli options
$longOptions = [ 'env:', 'template::', 'keep::', 'user::', 'password::', 'help::'];
$options = getopt($shortOptions, $longOptions);
$deleteDevTables = true;
$singleFile = false;
$couldBeADuplicateEnvUser = false;
$env = NONE;
$isNewInstallation = false;
$templateDirectory = $topDir . DIR_CLASSES . DIR_TEMPLATE . '/';
//var_export($options);exit;
$errors = null;
$res = 'PDOC: '; // PDO Config
/** @var PDO $resDB */
$resDB = null;
$processTemplate = false;
$error404 = ERROR_CONFIG_RESOURCE_404 . CONFIG_BROKER_SERVICES . ARROW;
$dbObjects = [ PDO_VIEWS => STRING_VIEW, PDO_PROCEDURES => STRING_PROCEDURE, PDO_FUNCTIONS => STRING_FUNCTION,
PDO_EVENTS => STRING_EVENT, PDO_TRIGGERS => STRING_TRIGGER ];
// generate a list of environments that are currently local and active
foreach (gasConfig::$settings[CONFIG_REGISTERED_SERVICES] as $service => $enabled)
if ($enabled)
$envList[] = $service;
$aryPDOData = [ ENV_ADMIN => null, ENV_APPSERVER => null, ENV_SEGUNDO => null, ENV_TERCERO => null];
$config = gasConfig::$settings[CONFIG_DATABASE][CONFIG_DATABASE_PDO];
if ($config[CONFIG_DATABASE_PDO_ENABLED] !== 1) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . 'PDO is disabled in the current Namaste deployment...');
exit(1);
}
consoleLog($res, CON_SUCCESS, 'Starting mysql template processing...');
$curSvcEnv = gasConfig::$settings[CONFIG_ID][CONFIG_ID_ENV];
$currentReleaseVersion = number_format((float) gasConfig::$settings[CONFIG_ID][CONFIG_ID_VER], 1);
// process the options:
// ensure we have the required parameter (-e/--env) and subsequent argument
if ((!isset($options['e']) and !isset($options['env'])) or (isset($options['h']) or isset($options['help']))) {
printHelp($argv[0]);
exit();
}
// display the disclaimer
printDisclaimer();
// todo -- uncomment the next line!
//readline(); // wait until the user presses the enter key - discard the input
// ---------------------------------------------------------------------------------------------------------------------
// process the cli input params...
$env = (isset($options['e'])) ? $options['e'] : ((isset($options['env'])) ? $options['env'] : '');
if ($env == NONE or ($env != 'dev' and $env != 'prod')) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . 'Environment is invalid - must be either dev or prod.');
printHelp($argv[0]) && exit;
}
// check the template name if the -t option was specified
if (isset($options['t']) or isset($options['template'])) {
$file = (isset($options['t'])) ? $options['t'] : ((isset($options['template'])) ? $options['template'] : '');
if (empty($file)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . 'Template file name is blank.');
printHelp($argv[0]) && exit;
}
// we have a file name - validate that the file exists
if (!file_exists($topDir . DIR_CLASSES . DIR_TEMPLATE . SLASH . $file)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $file . ' could not be found in the template directory.');
printHelp($argv[0]) && exit;
}
$singleFile = true;
}
if (isset($options['k']) or isset($options['keep'])) $deleteDevTables = false;
// ---------------------------------------------------------------------------------------------------------------------
// extract the cli admin-account info from the command line parameters...
// this is the user account that will create the db and bind the new user to the db...
// so make sure that this user has db-level grant auths!
$adminUser = (isset($options[STRING_USER])) ? $options[STRING_USER] : '';
$adminPass = (isset($options[STRING_PASSWORD])) ? $options[STRING_PASSWORD] : '';
// connect to the PDO resources based on configured environments - note that this call is NOT using resource
// manager... the framework does not have to be running for this application to run
try {
if (!getPDOResources($adminUser, $adminPass)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_CONNECT);
exit;
}
} catch (TypeError $t) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_RESOURCE_404 . RESOURCE_PDO_MASTER);
exit;
}
// create a new database and namaste user account if they do not already exist
if (isset($options['u']) or isset($options['user'])) {
if (!isset($options['p']) and !isset($options['password'])) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . 'You must specify a password when providing a super user account name.');
printHelp($argv[0]);
exit;
}
try {
foreach ($envList as $env) {
/** @var PDO $ptr */ // <-- so we don't get an IDE warning...
$ptr = $aryPDOData[$env][STRING_DBR];
if (is_null($ptr)) continue;
// if the request including the option to create a new Namaste (application) user account and password,
// then check to see if user account already exists and, if so, stop execution...
/** @noinspection SqlResolve */
$query = "SELECT Host FROM mysql.user WHERE User='" . $aryPDOData[$env][STRING_USER] . "'";
foreach ($ptr->query($query, PDO::FETCH_ASSOC) as $row)
$results[] = $row['Host'];
if (in_array($aryPDOData[$env][STRING_HOST], $results) or in_array(OPERATOR_REGEX, $results)) {
$host = (in_array($aryPDOData[$env][STRING_HOST], $results)) ? $aryPDOData[$env][STRING_HOST] : OPERATOR_REGEX;
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
$msg = sprintf(INFO_DB_DUP_ENV_USER, $aryPDOData[$env][STRING_USER]) . $host;
consoleLog($res, CON_ERROR, $hdr . $msg);
$couldBeADuplicateEnvUser = true;
}
$ptr->beginTransaction();
/*
* Namaste Super-User Account Creation
* ------------------------------------
* The following code creates the Namaste Super-User which the requesting user identified using the --user
* and --password input parameters. Because mySQL user management is a bit convoluted, here's how it's
* broken-down for this app:
*
* 1. Create the application user which is "{userName}@{host}" where userName and host is pulled from both
* the XML (host). This user is an application-user who's privileges are restricted to the named database
* only. The user account is created using the password passed in via the command line parameter.
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* IF THE $couldBeADuplicateEnvUser variable is false:
* 2. Create the database user based off the Namaste application user but create the user using the "%"
* host designation - we're creating this database-level user using the same password passed via the
* XML that we used to create the application user.
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* 3. Grant all privileges to the named (XML) Namaste database to the application user.
* 4. Grant replication-slave privileges to the database-user level account so that the application can
* access the read-slave for read requests.
* 5. Flush the privileges (because of the create command).
* 6. Use the targeted (switch to) database.
*/
$sql = "CREATE DATABASE " . $aryPDOData[$env][STRING_DBN] . ";";
if (!$couldBeADuplicateEnvUser)
$sql .= "CREATE USER '" . $aryPDOData[$env][STRING_USER] . "'@'" . $aryPDOData[$env][STRING_HOST] . "' IDENTIFIED BY '" . $aryPDOData[$env][STRING_PASS] . "';
CREATE USER '" . $aryPDOData[$env][STRING_USER] . "'@'%' IDENTIFIED BY '" . $aryPDOData[$env][STRING_PASS] . "';";
$sql .= "GRANT ALL PRIVILEGES ON " . $aryPDOData[$env][STRING_DBN] . ".* to '" . $aryPDOData[$env][STRING_USER] . "'@'" . $aryPDOData[$env][STRING_HOST] . "' IDENTIFIED BY '" . $aryPDOData[$env][STRING_PASS] . "';
GRANT REPLICATION SLAVE ON *.* to '" . $aryPDOData[$env][STRING_USER] . "'@'% IDENTIFIED BY '" . $aryPDOData[$env][STRING_PASS] . "';
FLUSH PRIVILEGES;
USE " . $aryPDOData[$env][STRING_DBN] . ";";
$ptr->exec($sql) or $ptr->rollBack() and die(print_r($ptr->errorInfo(), true));
$isNewInstallation = true;
$ptr->commit();
}
} catch (PDOException | Throwable $e) {
$ptr->rollBack();
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, ERROR_EXCEPTION);
consoleLog($res, $e->getMessage());
exit;
}
}
// generate the file list... honoring the user's request if they want to process a single template file
if ($singleFile) {
// single file
$fileList = [ $templateDirectory . $file ];
} else {
// all template files
$fileList = glob($templateDirectory . '*.class.inc');
}
// todo: check the $isNewInstallation var: if true, then we need to apply ALL of the version changes starting with 1.0
// I already created a JIRA ticket for the above issue: DB-132.
foreach ($envList as $environment) {
// if the current env is set and if that env has enabled PDO then...
if (!empty($aryPDOData[$environment])) {
foreach ($fileList as $filename) {
/** @var PDO $resDB */
$resDB = $aryPDOData[$environment][STRING_DBR];
$transactionStarted = false;
/** @var gatTestPDO $currentTemplate */
$currentTemplate = basename($filename);
$currentTemplate = preg_replace("/" . STRING_CLASS_FILE_EXT . "/", "", $currentTemplate);
$currentTemplate = new $currentTemplate();
// if not a PDO template, quietly move to next template
if ($currentTemplate->schema != TEMPLATE_DB_PDO) {
if (is_object($currentTemplate)) $currentTemplate->__destruct();
unset($currentTemplate);
continue;
} elseif (empty($currentTemplate->dbObjects) or !is_array($currentTemplate->dbObjects)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
$msg = $hdr . ERROR_SQL_TEMPLATE_DBO_404;
consoleLog($res, CON_ERROR, $msg);
continue;
}
consoleLog($res, CON_SUCCESS, sprintf(INFO_PROCESSING_TEMPLATE, $currentTemplate->collection, $currentTemplate->service));
// set the correct PDO resource
if ($currentTemplate->service == $environment) {
$processTemplate = true;
$database = gasConfig::$settings[CONFIG_ID][CONFIG_ID_ENV] . UDASH;
$database .= $config[$environment][CONFIG_DATABASE_PDO_MASTER][CONFIG_DATABASE_PDO_DB];
} else {
continue;
}
consoleLog($res, CON_SUCCESS, 'Processing PDO templates for env: ' . $environment);
// ---------------------------------------------------------------------------------------------------------
// Step 1: check to see if the current table exists and START THE TRANSACTION! <------------ pay attention
// ---------------------------------------------------------------------------------------------------------
try {
// set attribute to allow multi-queries because transaction
$resDB->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
$query = 'SHOW TABLES LIKE "' . $currentTemplate->collection . $currentTemplate->extension . '"';
$tableExists = $resDB->query($query)->rowCount() > 0;
if (!$resDB->inTransaction()) $resDB->beginTransaction();
} catch (PDOException | Throwable $p) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_EXCEPTION);
consoleLog($res, CON_ERROR, $hdr . $p->getMessage());
exit;
}
// Step 2: loop through PDO_SQL until we get to the current version - we'll only execute the SQL in this block!
foreach ($currentTemplate->dbObjects[PDO_SQL] as $sqlBlock) {
// check the release version and exit if the release version is missing from the template block
if (!isset($sqlBlock[PDO_VERSION])) {
// if there is no PDO_VERSION section set within *any* PDO_SQL block, it's a malformed template so exit...
$msg = sprintf(ERROR_SQL_TEMPLATE_DBO_VER_404, $filename);
try {
@doRollBack($resDB, $msg);
continue;
} catch (PDOException | TypeError $t) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $t->getMessage());
@doRollBack($resDB, $hdr . $t->getMessage());
continue;
}
} else {
// compare the current version block against the current release version -- skip block if not equal
if (!empty($sqlBlock[PDO_VERSION])) {
$rcCompare = strcmp($currentReleaseVersion, number_format((float)$sqlBlock[PDO_VERSION], 1));
} else {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
$msg = sprintf(ERROR_TEMPLATE_MISSING_FIELD, $filename, PDO_VERSION);
consoleLog($res, CON_ERROR, $hdr . $msg);
continue;
}
if ($rcCompare !== 0) continue;
// pull the table name from the sqlBlock
$currentTable = $sqlBlock[PDO_TABLE];
// at this point, we have a PDO_SQL block that matches the current release version
/*
* Step 3: Start the transaction -- ALL SQL commands must process successfully or issue a rollback.
*
* However...
*
* Some databases, including MySQL, automatically issue an implicit COMMIT when a database
* definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a
* transaction. The implicit COMMIT will prevent you from rolling back any other changes within
* the transaction boundary.
*/
try {
if (!$resDB->inTransaction()) {
if (false === $resDB->beginTransaction()) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_ST_FAIL);
continue;
} else {
$transactionStarted = true;
}
}
} catch (PDOException | Throwable $p) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $p->getMessage());
continue;
}
// Step 4: If the current table DNE, then pull the FC query for the current release version and execute
if (!$tableExists) {
// if the table does not exist, check the template $dbObjects[PDO_SQL][PDO_VERSION] to see if there is a
// corresponding content for the PDO_SQL_FC declaration. If so, then this is the table CREATE syntax and
// we need to execute this SQL.
if (isset($sqlBlock[PDO_SQL_FC]) and !empty($sqlBlock[PDO_SQL_FC])) {
if (!loadQuery($sqlBlock[PDO_SQL_FC])) {
doRollBack($resDB, ERROR_PDO_FC_CREATE . COMMA . ERROR_PDO_CURRENT_TABLE . $currentTable);
continue;
}
consoleLog($res, CON_SUCCESS, sprintf(INFO_PDO_DEPLOY, PDO_TABLE, $sqlBlock[PDO_TABLE]));
} else {
$msg = ERROR_PDO_CREATE_404 . $currentTemplate->collection;
consoleLog($res, CON_ERROR, $msg);
}
} elseif (intval($currentReleaseVersion) == 1) {
// Step 5.1:
// we're installing version 1.0 of Namaste and the table exists... this means that the table
// does exist and needs to be dropped - which is common in development environments so, if
// there exists a drop component (and there should be) execute this first before executing the
// SQL create statement:
if (array_key_exists(STRING_DROP_CODE_DEV, $sqlBlock) and !empty($sqlBlock[STRING_DROP_CODE_DEV]) and
(isset($sqlBlock[PDO_SQL_FC]) and !empty($sqlBlock[PDO_SQL_FC]))) {
// do not delete the table if the user launched with -k|--keep option
if ($deleteDevTables) {
if (!loadQuery($sqlBlock[STRING_DROP_CODE_DEV])) {
doRollBack($resDB, ERROR_PDO_DROP_DEV . COMMA . ERROR_PDO_CURRENT_TABLE . $currentTable);
continue;
}
if (!loadQuery($sqlBlock[PDO_SQL_FC])) {
doRollBack($resDB, ERROR_PDO_FC_CREATE . COMMA . ERROR_PDO_CURRENT_TABLE . $currentTable);
continue;
}
if (!isset($sqlBlock[PDO_SQL_UPDATE]) or empty($sqlBlock[PDO_SQL_UPDATE])) {
doRollBack($resDB, ERROR_PDO_UPDATE_404 . COMMA . ERROR_PDO_CURRENT_TABLE . $currentTable);
continue;
}
consoleLog($res, CON_SUCCESS, sprintf(INFO_PDO_DEPLOY, PDO_TABLE, $sqlBlock[PDO_TABLE]));
} else {
// Step 5.2: Call the dropIndexes function to drop the table indexes and tweak the
// update query in the sql block to remove the add primary key directive
if (!dropIndexes($database, $currentTable, true)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_INDEX_DROP . $currentTable);
continue;
}
consoleLog($res, CON_SUCCESS, INFO_PDO_INDEXES_DROPPED . $currentTable);
}
} else {
// Step 5.2:
// actually, if the table exists and there's no drop command defined in the template, or
// there's no create-table command defined, we can't continue so error-out and quit.
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
$msg = $hdr . ERROR_PDO_DROP_404 . $currentTemplate->collection;
consoleLog($res, CON_ERROR, $msg);
@doRollBack($resDB, $msg);
continue;
}
}
// Step 6: exec the update block if it exists for the current release version
if (isset($sqlBlock[PDO_SQL_UPDATE]) and !empty($sqlBlock[PDO_SQL_UPDATE])) {
if (!loadQuery($sqlBlock[PDO_SQL_UPDATE])) {
doRollBack($resDB, sprintf(ERROR_PDO_UPDATE_FAIL, $currentTable, $currentReleaseVersion));
continue;
}
} // if there's no update block for the current release for the current template, that's ok!
}
}
// sanity check: if we've not started a transaction, continue to the next template file
if (!$resDB->inTransaction()) continue;
// Step 6: Execute all the create-objects SQL statements found in the template. The release version is immaterial.
// process views, procedures, functions, events and triggers (defined as an associative array above)
foreach ($dbObjects as $dbObjectName => $dbObjectLabel) {
if (isset($currentTemplate->dbObjects[$dbObjectName]) and is_array($currentTemplate->dbObjects[$dbObjectName]) and !empty($currentTemplate->dbObjects[$dbObjectName])) {
foreach ($currentTemplate->dbObjects[$dbObjectName] as $name => $data) {
// transaction-wrap all the function calls
try {
// it is possible to reach this point without having previously started a transaction so,
// pause to see if one has been started and, if not, start one...
if (!$resDB->inTransaction()) {
if (false === $resDB->beginTransaction()) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_ST_FAIL);
} else {
$transactionStarted = true;
}
}
// stored procedures, et all, require separate drop statements b/c PHP doesn't support embedded delimiter statements
if (isset($data[STRING_DROP_CODE_DEV]) and is_string($data[STRING_DROP_CODE_DEV])) {
if (!loadQuery($data[STRING_DROP_CODE_DEV])) exit(1);
}
if (isset($data[$dbObjectLabel]) and is_string($data[$dbObjectLabel])) {
if (!loadQuery($data[$dbObjectLabel])) exit(1);
consoleLog($res, CON_SUCCESS, sprintf(INFO_PDO_DEPLOY, $dbObjectName, $name));
}
} catch (PDOException | Throwable $p) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $p->getMessage());
}
}
} else {
consoleLog($res, CON_SUCCESS, sprintf(INFO_PDO_NO_DEPLOY, $currentTemplate->collection . $currentTemplate->extension, $dbObjectName));
}
}
// Step 7: commit all of the sql we just executed before proceeding to the next template file...
try {
$rc = $resDB->commit();
if (!$rc) {
consoleLog($res, CON_ERROR, ERROR_PDO_COMMIT);
@doRollBack($resDB, ERROR_PDO_COMMIT);
exit(1);
}
} catch (PDOException $p) {
consoleLog($res, CON_ERROR, $p->getMessage());
@doRollBack($resDB, $p->getMessage());
exit(1);
}
}
}
} // end env loop
// if we reach this point, all templates were processed successfully - log a termination message and exit
consoleLog($res, CON_SUCCESS, SUCCESS_PDO_TEMPLATE_PROCESSING . (string) $currentReleaseVersion);
exit(0);
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PROGRAM ENDS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// LOCAL FUNCTIONS
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/**
* loadQuery() -- local function
*
* I call this code twice so I have it in a function -- there is one input parameter to the function - this is the
* query to be loaded and sent for execution.
*
* The function accesses most of it's data from the heap by declaring it's lvars as globals - mostly because there
* are five lvars and I didn't want to define them as a parameter list.
*
* The purpose, then, of this function is to execute the passed query that was extracted from the template file and
* used to drop/create the mysql table.
*
* The method requires that we be covered under a transaction before executing the passed query. If we're not under
* a transaction, as indicated by the appropriate and brilliantly-named global, then stop execution immediately and
* return a false status after making a console log entry.
*
* If the sql fails to exec, or if any error or exception is raised, then we call the rollback function and return
* a Boolean false back to the calling client. If the query executed successfully, a Boolean true is returned.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param string $_query -- the query to be executed
* @param bool $_needResults -- if this is a query that returns results, override the default
* @return bool
*
*
* HISTORY:
* ========
* 05-22-19 mks DB-122: original coding
* 07-18-19 mks DB-124: support for queries where result data is generated
* 08-07-19 mks DB-124: enforcing transaction state
*
*/
function loadQuery(string $_query, bool $_needResults = false): bool
{
global $res, $resDB;
if (!$resDB->inTransaction()) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_NO_TRANS);
return false;
}
try {
$rc = ($_needResults) ? runQuery($resDB, $_query) : execQuery($resDB, $_query);
if (!$rc) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
$msg = $hdr . ERROR_PDO_QUERY . $_query;
@doRollBack($resDB, $msg);
return false;
}
return true;
} catch (TypeError $t) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $t->getMessage());
@doRollBack($resDB, $t->getMessage());
return false;
}
}
/**
* doRollBack() -- local function
*
* This function executes the transaction rollback command. Since there are so many places in the main processing
* where a transaction failure is trapped, it made sense to put the code that actually rolls back the transaction
* into it's own method.
*
* The method requires two input parameters:
*
* $_rc -- the PDO resource
* $_msg - the transaction msg (why we're rolling back)
*
* If the rollback command fails, we'll output additional diagnostics.
*
* On exit, it sets the global variable: $transactionStarted to false.
*
* PROGRAMMER'S NOTES:
* -------------------
* Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language
* (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will
* prevent you from rolling back any other changes within the transaction boundary.
*
* The method is type void.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param PDO $_rc
* @param string $_msg
*
*
* HISTORY:
* ========
* 06-21-18 mks CORE-1045: original coding completed
*
*/
function doRollBack(PDO $_rc, string $_msg): void
{
global $res, $transactionStarted;
try {
if (false === $_rc->rollBack()) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_ROLLBACK);
}
consoleLog($res, CON_ERROR, $_msg);
consoleLog($res, CON_ERROR, INFO_TRX_ROLLBACK);
} catch (PDOException $p) {
consoleLog($res, CON_ERROR, $p->getMessage());
}
$transactionStarted = false;
}
/**
* dropIndexes() -- local function
*
* This method requires two input parameters:
*
* $_database -- the name of the current database being processed
* $_currentTable -- omg! a self-documenting variable!
*
* We declare the $sqlBlock (pulled from the template) and the resource variable ($res) as global so that they can be
* accessed within the function. If there's a index block defined in the template, then we're going to build the drop
* index query by parsing out the database and tables names replacing them with the parameters passed into the function.
*
* We submit the query and, if the query failed to execute, return a boolean false. Otherwise, output a console
* message and return a boolean true to the caller.
*
* Programmer's Notes:
* -------------------
* This function was created b/c there are two conditions in which we'll eliminate indexes. We do NOT want to run the
* drop-indexes command when we're in a dev env and the user has not specified the --keep option.
*
* There's also place-holder code in this method for removing the auto-increment feature of the primary key (id_{ext})
* presumably so that all indexes can be dropped and restored. I'd assume that this would be necessary under the
* following conditions:
*
* 1. you're in a dev env
* 2. you used the --keep|-k option to keep the table data
* 3. you're on version 1.0
*
* If ALL of the above conditions are met, then we'll have to remove the auto-increment attribute from the primary
* key field and then drop the index. mariaDB does not allow you to drop the index unless this attribute has been
* removed first. I'd assume that I'd add new params to the method, a switch telling the function that I want to
* exec the drop-autoincrement attribute.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param string $_database -- string containing the current database name
* @param string $_currentTable -- string containing the current table name
* @param bool $_dropAI -- flag, defaults to false, indicating if we're dropping the AI attribute from the pKey
* @return bool
*
* HISTORY:
* ========
* 07-31-19 mks DB-124: original coding
*
*/
function dropIndexes(string $_database, string $_currentTable, bool $_dropAI = false): bool
{
/** var gatTestPDO $currentTemplate */
global $currentTemplate;
global $sqlBlock, $res;
if (!isset($sqlBlock[STRING_DROP_CODE_IDX]) or empty($sqlBlock[STRING_DROP_CODE_IDX])) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_DATA_ARRAY_EMPTY . COLON . STRING_DROP_CODE_IDX);
return false;
}
/* -----------------------------------------------------------------------------------------------------------------
* the code in the next block is provided in-case there's every a time where we want to drop the primary key
* when doing a sql-update. The primary key cannot be dropped like a regular index because of the auto-increment
* attribute. So, this block executes a query that will drop the auto-increment attribute from the id_{ext}
* column.
-----------------------------------------------------------------------------------------------------------------*/
if ($_dropAI) {
// we're not dropping the column, just the auto-increment attribute...
$pkQuery = 'ALTER TABLE ' . STRING_TABLE_NAME_TAG . ' CHANGE XXXidXXX XXXidXXX int(11)';
// constant for the above place-holder is: STRING_TABLE_ID_TAG
$pKey = $currentTemplate->collection . $currentTemplate->extension;
if (empty($pKey)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PKEY_ID . STRING_TEMPLATE);
return false;
}
// replace the table name with the current table name
$pkQuery = str_replace(STRING_TABLE_NAME_TAG, $pKey, $pkQuery);
// replace the id-field with the current table extension
$pkQuery = str_replace(STRING_TABLE_ID_TAG, (PKEY_ID . $currentTemplate->extension), $pkQuery);
if (empty($pkQuery)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_QUERY_BUILD);
return false;
} else {
// execute the query to drop the auto-increment attribute from the primary key
if (!loadQuery($pkQuery)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_DROP_AI . $_currentTable);
return false;
}
// if we're dropping the auto-increment, we need to remove the ADD PRIMARY KEY... text from the sql
// update string in the template so that we don't fail at adding the indexes
$key = 'id' . $currentTemplate->extension;
$targetString = 'ADD PRIMARY KEY (`' . $key . '`),';
$sqlBlock[PDO_SQL_UPDATE] = str_replace($targetString, '', $sqlBlock[PDO_SQL_UPDATE]);
consoleLog($res, CON_SUCCESS, INFO_PDO_AI_ATTR_DROPPED);
}
}
/* ----------------------------------END DROP AUTO-INCREMENT QUERY------------------------------------------------*/
// clear out the comment tag
$query = str_replace('--', '', $sqlBlock[STRING_DROP_CODE_IDX]);
// first, we need to parse and replace the placeholder strings with the actual values
$query = str_replace(STRING_DB_NAME_TAG, $_database, $query);
$query = str_replace(STRING_TABLE_NAME_TAG, $_currentTable, $query);
if (!loadQuery($query, true)) {
return false;
}
consoleLog($res, CON_SUCCESS, sprintf(INFO_PDO_DEPLOY, $query, $sqlBlock[PDO_TABLE]));
return true;
}
/**
* execQuery() -- local function
*
* this function handles all non-transaction and non-db-validation queries. All queries for creating and maintaining
* tables are static and do not require preparation.
*
* Since we're just executing DDL statements (CREATE, ALTER), then there's no return data, per se, only information
* is if the query successfully executed or not.
*
* Input parameters to the method are:
*
* $_rc -- this is the PDO resource (provisioned by the gasResourceManager)
* $_query -- this is the SQL query(ies) that will be executed
*
* If a query raises a PDO exception, we'll trap the exception and, if transactions are currently active, then
* we'll invoke the roll-back function (above).
*
* The method returns a BOOLEAN indicating if the query(ies) successfully executed to completion or not.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param PDO $_rc
* @param string $_query
* @return mixed
*
*
* HISTORY:
* ========
* 06-21-16 mks CORE-1045: original coding completed
* 07-18-19 mks DB-124: added ability to return query data for the drop-indexing command
*
*/
function execQuery(PDO $_rc, string $_query)
{
global $eos, $res, $transactionStarted;
try {
$results = $_rc->exec($_query);
return (!($results === false));
} catch (PDOException $p) {
if ($transactionStarted) {
try {
@doRollBack($_rc, $p->getMessage());
} catch (TypeError $t) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $t->getMessage());
}
} else {
$msg = sprintf(ERROR_PDO_EXCEPTION, $_query);
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $eos. $msg . $_query);
consoleLog($res, CON_ERROR, $p->getMessage());
}
return false;
}
}
/**
* runQuery() -- local function
*
* This method is similar to the execQuery() function except that we prepare the query, tokenizing it, before
* submitting the query to the sql engine. If you ever submit a query that requires params, you'll have to modify
* this function by passing the placement variable as an argument to execute() assuming you've also added the array
* variable (containing the parameter values) as an input parameter.
*
* The method currently requires two input parameters:
*
* $_rc - the database resource pointer
* $_query - the query to be tokenized and executed
*
* Since the query is going to be a query pulled from the template, we're going to first parse and remove all the
* new-line chars from the string. Next, we're going to build a system query that allows us to exec queries longer
* (in length) than 10K.
*
* Then we prepare and execute the query. We fetch and return the results and check the results for errors, reporting
* if same was found.
*
* The method returns a boolean value, true if everything was successful, otherwise false.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param PDO $_rc
* @param string $_query
* @return bool
*
*
* HISTORY:
* ========
* 07-31-19 mks DB-124: original coding
* 10-09-19 mks DB-136: fix for PHP warning
*
*/
function runQuery(PDO $_rc, string $_query): bool
{
global $eos, $res;
// clean-up the newline chars from the query string
$query = str_replace("\n","", $_query);
// remove multiple space chars from the query string
$query = preg_replace('/\s\s+/', ' ', $query);
if (empty($query)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_QUERY_BUILD);
return false;
}
// plan for overflow of the query if the table uses a lot of indexing, just in case...
$sysQ = 'SET SESSION group_concat_max_len=10240';
try {
if (false === $_rc->exec($sysQ)) {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . ERROR_PDO_QUERY . $sysQ);
return false;
}
$tmp = $_rc->prepare($query);
$tmp->execute();
$results = $tmp->fetchAll();
if ($results !== false) {
$dropQuery = (isset($results) and isset($results[0]) and is_array($results[0])) ? reset($results[0]) : null;
if (is_null($dropQuery)) return true; // no indexes to drop
$results = $_rc->query($dropQuery);
if (false === $results) return false;
// need to clean up any unique fields still hanging around, including the primary key
} else
return false;
} catch (PDOException | Throwable $e) {
$msg = sprintf(ERROR_PDO_EXCEPTION, $_query);
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . $eos. $msg . $_query);
consoleLog($res, CON_ERROR, $e->getMessage());
return false;
}
return true;
}
/**
* getPDOResources() -- internal method
*
* This is a "new" function courtesy of DB-111 and requires two input parameters:
*
* $_admin -- this is a string containing the name of the admin user for the mysql env. Optimally, this should be a
* user account carrying global permissions allowing them to create new databases.
* $_pass -- this is a string containing the password for the global admin user.
*
* If nulls are passed (which is possible if the requesting user does not provide this information via argv), then
* we'll attempt to establish a database connection using the user/password account information stored in the XML
* for the service we're attempting the connection. We'll also connect to the destination resource specifying
* the database name for that resource.
*
* If the user/password is passed to the method, the assumption is that we need to create the database and the user
* on the destination resource. We'll connect to the remote mysql resource without specifying the db name assuming
* that will be created, along with the Namaste-admin-user account, post-invocation.
*
* This method uses a global array container - we loop through an array of valid envs and pull out the PDO host data
* for that env and, if the env is set/enabled, then assign the param values to self-descriptive keys in the container.
* This data includes the db resource pointer.
*
* If the connection attempt throws an exception, we process the exception and return a boolean(false) to the
* calling client.
*
* If an unknown env is in the environment list, we proc an error and return a boolean(false).
*
* Otherwise, we return a boolean true and implicitly return the PDO connectors and the array containers for each
* environment.
*
* Also, if Namaste and Admin are not enabled, generate an error and return a boolean(false) as these two envs are
* minimally required for Namaste.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param string $_admin
* @param string $_pass
* @return bool
*
*
* HISTORY:
* ========
* 08-29-19 mks DB-111: original coding
*
*/
function getPDOResources(string $_admin, string $_pass): bool
{
global $res, $envList, $aryPDOData;
$createDB = (empty($_admin) and empty($_pass)) ? false : true;
foreach ($envList as $env) {
if (isset(gasConfig::$settings[CONFIG_DATABASE][CONFIG_DATABASE_PDO][$env])) {
$cfgOld = gasConfig::$settings[CONFIG_DATABASE][CONFIG_DATABASE_PDO][$env];
$cfg = $cfgOld[CONFIG_DATABASE_PDO_MASTER];
$aryPDOData[$env][STRING_HOST] = $cfg[CONFIG_DATABASE_PDO_HOSTNAME];
$aryPDOData[$env][STRING_PORT] = $cfg[CONFIG_DATABASE_PDO_PORT];
$aryPDOData[$env][STRING_USER] = $cfg[CONFIG_DATABASE_PDO_USERNAME];
$aryPDOData[$env][STRING_PASS] = $cfg[CONFIG_DATABASE_PDO_PASSWORD];
$aryPDOData[$env][STRING_DBN] = gasConfig::$settings[CONFIG_ID][CONFIG_ID_ENV] . UDASH . $cfg[CONFIG_DATABASE_PDO_DB];
if ($createDB)
$dbs = 'mysql:host=' . $aryPDOData[$env][STRING_HOST] . COLON . $aryPDOData[$env][STRING_PORT];
else
$dbs = 'mysql:host=' . $aryPDOData[$env][STRING_HOST] . COLON . $aryPDOData[$env][STRING_PORT] . ';dbname=' . $aryPDOData[$env][STRING_DBN];
} else {
continue; // skip all processing for the current env b/c not configured
}
try {
switch ($env) {
case ENV_APPSERVER :
case ENV_SEGUNDO :
if ($cfgOld[CONFIG_DATABASE_PDO_ENABLED]) {
$aryPDOData[$env][STRING_DBR] = new PDO($dbs, (empty($_admin)) ? $aryPDOData[$env][STRING_USER] : $_admin, (empty($_pass)) ? $aryPDOData[$env][STRING_PASS] : $_pass);
} else {
$hdr = basename(__FILE__) . AT . __LINE__ . COLON;
consoleLog($res, CON_ERROR, $hdr . sprintf(ERROR_SQL_ENV_NOT_ENABLED, $env));
return false;
}
break;
case ENV_TERCERO :
case ENV_ADMIN :
if ($cfgOld[CONFIG_DATABASE_PDO_ENABLED]) {
$aryPDOData[$env][STRING_DBR] = new PDO($dbs, (empty($_admin)) ? $aryPDOData[$env][STRING_USER] : $_admin, (empty($_pass)) ? $aryPDOData[$env][STRING_PASS] : $_pass);
} // no error messages if not enabled as segundo/tercero configs are not required like namaste/admin
break;
default :
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = $hdr . sprintf(ERROR_UNKNOWN_KEY, $env, 'envList');
consoleLog($res, CON_ERROR, $msg);
return false;
break;
}
} catch (PDOException | Throwable $p) {
$hdr = basename(__METHOD__) . AT . __LINE__ . COLON;
$msg = $hdr . sprintf(ERROR_ARRAY_KEY_UNK, $env);
consoleLog($res, CON_ERROR, $msg);
$msg = $hdr . $p->getMessage();
consoleLog($res, CON_ERROR, $msg);
return false;
}
}
return true;
}
/**
* printHelp() -- internal function
*
* This function displays the help screen for this app. It prints the help message to stdout and exists returning void.
*
* The input parameter is meant to be $argv[0]: the name of the script file.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
* @param string $_appName -- the name of the application, meant to be argv[0]
*
*
* HISTORY:
* ========
* 07-15-19 mks DB-124: original coding
*
*/
function printHelp(string $_appName): void
{
echo PHP_EOL;
echo "\e[1m" . $_appName . "\e[0m" . PHP_EOL . PHP_EOL;
echo "\e[1mUsage:\e[0m" . PHP_EOL;
echo '------' . PHP_EOL;
echo $_appName . ' is a mySQL/PDO deployment script that analyzes the data class templates and executes the' . PHP_EOL;
echo 'SQL code embedded in the templates to create (and/or drop) new tables, install indexes and database' . PHP_EOL;
echo 'objects (views, stored-procedures, functions, etc.).' . PHP_EOL . PHP_EOL;
echo 'The following are valid options for this program:' . PHP_EOL . PHP_EOL;
echo "\t" . ' --env={dev|prod} REQUIRED: deploy into a dev (tables are truncated) or prod (table data kept) environments' . PHP_EOL;
echo "\t" . ' --template={fileName} OPTIONAL: Deploy only for the template specified (e.g.: gatTestPDO.class.inc)' . PHP_EOL;
echo "\t" . ' --user={username} OPTIONAL: If you need to create a new database, specify the super user name' . PHP_EOL;
echo "\t" . ' --password={password} OPTIONAL: If you specify the super user name, specify the super user password' . PHP_EOL;
echo "\t" . ' -k | --keep OPTIONAL: Deploy into dev env but keep the table data (no table truncation)' . PHP_EOL;
echo "\t" . ' -h | --help Displays this help screen' . PHP_EOL . PHP_EOL . PHP_EOL;
echo "\e[91mWhen you deploy in a development capacity: tables are destroyed and re-created dropping all records.\e[0m" . PHP_EOL;
echo "\e[91mWhen you deploy in a production capacity: tables are retained but indexes are dropped and re-created.\e[0m" . PHP_EOL . PHP_EOL . PHP_EOL;
echo 'When specifying the super user data, note that this is NOT the same as the gaAdmin account user defined in the XML.' . PHP_EOL;
echo 'The super-user is a database account with admin privileges that can create new databases and users.' . PHP_EOL . PHP_EOL;
echo 'In both modes, all database objects (views, stored-procedures, etc.) are dropped and re-created.' . PHP_EOL . PHP_EOL;
echo 'All database modifications are done under the protection of a transaction. Either all of the table' . PHP_EOL;
echo 'changes will be saved and committed, or none will be. You will have to validate by reading the report' . PHP_EOL;
echo 'for each table that is processed by the program to determine success or failure.' . PHP_EOL . PHP_EOL;
echo "\e[1mExamples:\e[0m" . PHP_EOL;
echo '---------' . PHP_EOL;
echo "\e[37m" . $_appName . ' -e dev -t gatProductRegistrations.class.inc -k' . "\e[0m" . PHP_EOL;
echo 'This command will deploy into a dev environment but will limit the deployment to only processing the SQL directives' . PHP_EOL;
echo 'for the gatProductRegistrations.class.inc class. (You have to provide the fully-qualified template file name!)' . PHP_EOL;
echo 'The -k option tells the app to keep the table data; do not truncate the table.' . PHP_EOL . PHP_EOL;
echo "\e[37m" . $_appName . ' -e prod' . "\e[0m" . PHP_EOL;
echo 'Standard non-development deployment. All PDO (sql) tables will be updated but no data will be deleted.' . PHP_EOL . PHP_EOL;
echo "\e[37m" . $_appName . "\e[0m" . PHP_EOL;
echo 'Displays this message.' . PHP_EOL . PHP_EOL;
}
/**
* function printDisclaimer() -- internal function
*
* This function simply prints-out a disclaimer to the cli user stating that processing commands in this script is
* expected to take some reasonable amounts of time, depending on the amount of data in the table(s) being processed.
*
* The user is invited to display some patience during processing and is told to press the control-c button if they
* wish to stop the program.
*
*
* @author mike@givingassistant.org
* @version 1.0
*
*
* HISTORY:
* ========
* 08-07-19 mks DB-124: original coding
*
*/
function printDisclaimer()
{
echo <<<EOT
You are executing a script which will request several schema changes to your database tables. Some of these commands
will require several seconds, or minutes, PER TABLE, to successfully complete! Please do not abort execution of this
script. Monitor the console log output for status messages so that you know processing is on-going.
There will be no further output to the console -- MONITOR CONSOLE LOG for program output.
Press [Enter] to accept and begin processing. Press Ctrl+C to stop this program now.
EOT;
}
Reference in New Issue View Git Blame Copy Permalink