X-Git-Url: https://git.mxchange.org/?p=mailer.git;a=blobdiff_plain;f=inc%2Fsql-functions.php;h=267dcb4fb8be6e4f4fb43a12a1e47c2dc0070bba;hp=15fe91d98c0d5edfde224d217f11f13f2b5ab843;hb=49acdb7a7adbcf25a8e8683b5581bfcec72b23bd;hpb=263a089d8a499e0e26d0af9e7aa7639f88b8ca60
diff --git a/inc/sql-functions.php b/inc/sql-functions.php
index 15fe91d98c..267dcb4fb8 100644
--- a/inc/sql-functions.php
+++ b/inc/sql-functions.php
@@ -6,19 +6,13 @@
* -------------------------------------------------------------------- *
* File : sql-functions.php *
* -------------------------------------------------------------------- *
- * Short description : All MySQL-related functions *
+ * Short description : SQL functions to handle queries *
* -------------------------------------------------------------------- *
- * Kurzbeschreibung : Alle MySQL-Relevanten Funktionen *
- * -------------------------------------------------------------------- *
- * $Revision:: $ *
- * $Date:: $ *
- * $Tag:: 0.2.1-FINAL $ *
- * $Author:: $ *
- * Needs to be in all Files and every File needs "svn propset *
- * svn:keywords Date Revision" (autoprobset!) at least!!!!!! *
+ * Kurzbeschreibung : SQL-Funktionen fuer Queries *
* -------------------------------------------------------------------- *
* Copyright (c) 2003 - 2009 by Roland Haeder *
- * For more information visit: http://www.mxchange.org *
+ * Copyright (c) 2009 - 2015 by Mailer Developer Team *
+ * For more information visit: http://mxchange.org *
* *
* This program is free software; you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
@@ -43,16 +37,18 @@ if (!defined('__SECURITY')) {
// Init SQLs array
function initSqls () {
- setSqlsArray(array());
+ // Init generic array
+ setSqlsArray(array('generic' => array()));
}
-// Checks wether the sqls array is initialized
+// Checks whether the sqls array is initialized
function isSqlsInitialized () {
return ((isset($GLOBALS['sqls'])) && (is_array($GLOBALS['sqls'])));
}
// Setter for SQLs array
function setSqlsArray ($SQLs) {
+ //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'count()='.count($SQLs));
$GLOBALS['sqls'] = (array) $SQLs;
}
@@ -68,8 +64,8 @@ function getSqls () {
// Add an SQL to the list
function addSql ($sql) {
- //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf("sql=%s, count=%d", $sql, countSqls()));
- $GLOBALS['sqls']['generic'][] = (string) $sql;
+ //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf('sql=%s, count=%d', $sql, countSqls()));
+ array_push($GLOBALS['sqls']['generic'], $sql);
}
// Merge SQLs together
@@ -105,16 +101,16 @@ function countSqls () {
if (isSqlsInitialized()) {
// Then count it
$count = count($GLOBALS['sqls']);
- //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf("count=%d", $count));
+ //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf('count=%d', $count));
} // END - if
// Return it
return $count;
}
-// Checks wether the SQLs array is filled
-function isSqlsValid () {
- //* DEBUG: */ print __FUNCTION__.':'.intval(isSqlsInitialized()).'/'.countSqls().'/'.getCurrentExtensionName().'
';
+// Checks whether the SQLs array is filled
+function ifSqlsRegistered () {
+ //* DEBUG: */ debugOutput(__FUNCTION__ . ':' . intval(isSqlsInitialized()) . '/' . countSqls() . '/' . getCurrentExtensionName());
return (
(
isSqlsInitialized()
@@ -124,5 +120,698 @@ function isSqlsValid () {
);
}
+// Generates an updating SQL query from given array
+function getUpdateSqlFromArray ($array, $tableName, $whereColumn, $whereData, $excludedFields, $multiDimId = NULL) {
+ // Begin SQL query
+ $SQL = 'UPDATE `{?_MYSQL_PREFIX?}_' . $tableName . '` SET ';
+
+ // Insert all data
+ foreach ($array as $entry => $value) {
+ // Skip login/id entry
+ if (in_array($entry, $excludedFields)) {
+ continue;
+ } // END - if
+
+ // Is there a non-string (e.g. number, NULL, SQL function or back-tick at the beginning?
+ if (is_null($multiDimId)) {
+ // Handle one-dimensional data
+ if (is_null($value)) {
+ // NULL detected
+ $SQL .= '`' . $entry . '`=NULL,';
+ } elseif ((substr($value, -2, 2) == '()') || (substr($value, 0, 1) == '`')) {
+ // SQL function needs no ticks (')
+ $SQL .= '`' . $entry . '`=' . sqlEscapeString($value) . ',';
+ } elseif ('' . bigintval($value, TRUE, FALSE) . '' == '' . $value . '') {
+ // No need for ticks (')
+ $SQL .= '`' . $entry . '`=' . $value . ',';
+ } elseif ('' . (float) $value . '' == '' . $value . '') {
+ // Float number detected
+ $SQL .= '`' . $entry . '`=' . sprintf(getConfig('FLOAT_MASK'), $value) . ',';
+ } else {
+ // Strings need ticks (') around them
+ $SQL .= '`' . $entry . "`='" . sqlEscapeString($value) . "',";
+ }
+ } else {
+ // Handle multi-dimensional data
+ if (is_null($value[$multiDimId])) {
+ // NULL detected
+ $SQL .= '`' . $entry . '`=NULL,';
+ } elseif ((substr($value[$multiDimId], -2, 2) == '()') || (substr($value[$multiDimId], 0, 1) == '`')) {
+ // SQL function needs no ticks (')
+ $SQL .= '`' . $entry . '`=' . sqlEscapeString($value[$multiDimId]) . ',';
+ } elseif (('' . bigintval($value[$multiDimId], TRUE, FALSE) . '' == '' . $value[$multiDimId] . '')) {
+ // No need for ticks (')
+ $SQL .= '`' . $entry . '`=' . $value[$multiDimId] . ',';
+ } elseif ('' . (float) $value[$multiDimId] . '' == '' . $value[$multiDimId] . '') {
+ // Float number detected
+ $SQL .= '`' . $entry . '`=' . sprintf(getConfig('FLOAT_MASK'), $value[$multiDimId]) . ',';
+ } else {
+ // Strings need ticks (') around them
+ $SQL .= '`' . $entry . "`='" . sqlEscapeString($value[$multiDimId]) . "',";
+ }
+ }
+ } // END - foreach
+
+ // Remove last 2 chars and finish query
+ $SQL = substr($SQL, 0, -1) . ' WHERE `' . $whereColumn . '`=' . $whereData . ' LIMIT 1';
+
+ // Return SQL query
+ return $SQL;
+}
+
+// "Getter" for an "INSERT INTO" SQL query
+function getInsertSqlFromArray ($array, $tableName) {
+ // Init SQL
+ $SQL = 'INSERT INTO `{?_MYSQL_PREFIX?}_' . $tableName . '` (`' . implode('`, `', array_keys($array)) . '`) VALUES (';
+
+ // Walk through all entries
+ foreach ($array as $key => $value) {
+ // Log debug message
+ //* NOISY-DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',key=' . $key . ',value=' . $value);
+
+ // Add all entries
+ if (is_null($value)) {
+ // Add NULL
+ $SQL .= 'NULL,';
+ } elseif (substr($value, -2, 2) == '()') {
+ // SQL function needs no ticks (')
+ $SQL .= sqlEscapeString($value) . ',';
+ } elseif ('' . bigintval($value, TRUE, FALSE) . '' == '' . $value . '') {
+ // Number detected, no need for ticks (')
+ $SQL .= bigintval($value) . ',';
+ } elseif ('' . (float) $value . '' == '' . $value . '') {
+ // Float number detected
+ $SQL .= sprintf(getConfig('FLOAT_MASK'), $value) . ',';
+ } else {
+ // Everything else might be a string, so add ticks around it
+ $SQL .= chr(39) . sqlEscapeString($value) . chr(39) . ',';
+ }
+ } // END - foreach
+
+ // Finish SQL query
+ $SQL = substr($SQL, 0, -1) . ')';
+
+ // Return SQL query
+ //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',sql=' . $SQL);
+ return $SQL;
+}
+
+// Function to unset __is_sql_link_up
+function unsetSqlLinkUp ($file, $line) {
+ // Unset it
+ //* DEBUG: */ logDebugMessage($file, $line, __FUNCTION__ . ': Called!');
+ setSqlLink($file, $line, NULL);
+}
+
+// Initializes the SQL link by bringing it up if set
+function initSqlLink () {
+ // "Unset" the link
+ unsetSqlLinkUp(__FUNCTION__, __LINE__);
+
+ // Is the configuration data set?
+ if ((!empty($GLOBALS['mysql']['host'])) && (!empty($GLOBALS['mysql']['login'])) && (!empty($GLOBALS['mysql']['dbase']))) {
+ // Remove cache
+ unsetSqlLinkUp(__FUNCTION__, __LINE__);
+
+ // Connect to DB
+ sqlConnectToDatabase($GLOBALS['mysql']['host'], $GLOBALS['mysql']['login'], $GLOBALS['mysql']['password'], __FUNCTION__, __LINE__);
+
+ // Is the link valid?
+ if (isSqlLinkUp()) {
+ // Enable exit on error
+ enableExitOnError();
+
+ // Is it a valid resource?
+ if (sqlSelectDatabase($GLOBALS['mysql']['dbase'], __FUNCTION__, __LINE__) === TRUE) {
+ // Set database name (required for ext-optimize and ifSqlTableExists())
+ setConfigEntry('__DB_NAME', $GLOBALS['mysql']['dbase']);
+
+ // Remove MySQL array from namespace
+ unset($GLOBALS['mysql']);
+
+ // Load cache
+ loadIncludeOnce('inc/load_cache.php');
+ } else {
+ // Wrong database?
+ reportBug(__FUNCTION__, __LINE__, 'Wrong database selected.');
+ }
+ } else {
+ // No link to database!
+ reportBug(__FUNCTION__, __LINE__, 'Database link is not yet up.');
+ }
+ } else {
+ // Maybe you forgot to enter your database login?
+ reportBug(__FUNCTION__, __LINE__, 'Database login is missing.');
+ }
+}
+
+// Imports given SQL dump from given (relative) path and adds them to $sqlPool
+function importSqlDump ($path, $dumpName, $sqlPool) {
+ // Construct FQFN
+ $FQFN = getPath() . $path . '/' . $dumpName . '.sql';
+
+ // Is the file readable?
+ if (!isFileReadable($FQFN)) {
+ // Not found, which is bad
+ reportBug(__FUNCTION__, __LINE__, sprintf('SQL dump %s/%s.sql is not readable.', $path, $dumpName));
+ } // END - if
+
+ // Then read it
+ $fileContent = readSqlDump($FQFN);
+
+ // Merge it with existing SQL statements
+ mergeSqls(explode(";\n", $fileContent), $sqlPool);
+}
+
+// SQL string escaping
+function sqlQueryEscaped ($sqlString, $data, $file, $line, $run = TRUE, $strip = TRUE, $secure = TRUE) {
+ // Link is there?
+ if ((!isSqlLinkUp()) || (!is_array($data))) {
+ // Link is down or data is not an array
+ //* DEBUG: */ logDebugMessage($file, $line, 'isSqlLinkUp()=' . intval(isSqlLinkUp()) . ',data[]=' . gettype($data) . ',sqlString=' . $sqlString . ': ABORTING!');
+ return FALSE;
+ } // END - if
+
+ // Init array for escape'd data with SQL string
+ $dataSecured = array(
+ '__sql_string' => $sqlString
+ );
+
+ // Escape all data
+ foreach ($data as $key => $value) {
+ //* DEBUG: */ logDebugMessage(basename($file) . '/' . __FUNCTION__, $line . '/' . __LINE__, 'key=' . $key . ',value=' . $value . ',run=' . intval($run) . ',strip=' . intval($strip) . ',secure=' . intval($secure));
+ $dataSecured[$key] = sqlEscapeString($value, $secure, $strip);
+ //* DEBUG: */ logDebugMessage(basename($file) . '/' . __FUNCTION__, $line . '/' . __LINE__, 'dataSecured[key]=' . $dataSecured[$key]);
+ } // END - foreach
+
+ // Generate query
+ $query = call_user_func_array('sprintf', $dataSecured);
+
+ if ($run === TRUE) {
+ // Run SQL query (default)
+ return sqlQuery($query, $file, $line);
+ } else {
+ // Return secured string
+ return $query;
+ }
+}
+
+// SELECT query string from table, columns and so on... ;-)
+function getSqlResultFromArray ($table, $columns, $idRow, $id, $file, $line) {
+ // Is columns an array?
+ if (!is_array($columns)) {
+ // No array
+ reportBug(__FUNCTION__, __LINE__, sprintf('columns is not an array. %s != array, file=%s, line=%s',
+ gettype($columns),
+ basename($file),
+ $line
+ ));
+
+ // Abort here with 'false'
+ return FALSE;
+ } // END - if
+
+ // Is this is a simple array?
+ if ((is_array($columns[0])) && (isset($columns[0]['column']))) {
+ // Begin with SQL query
+ $sql = 'SELECT ';
+
+ // No, it comes from XML, so get it back from it
+ $sql .= getSqlPartFromXmlArray($columns);
+
+ // Finalize it
+ $sql .= " FROM `{?_MYSQL_PREFIX?}_%s` WHERE `%s`='%s' LIMIT 1";
+ } else {
+ // Yes, prepare the SQL statement
+ $sql = 'SELECT `' . implode('`, `', $columns) . "` FROM `{?_MYSQL_PREFIX?}_%s` WHERE `%s`='%s' LIMIT 1";
+ }
+
+ // Return the result
+ return sqlQueryEscaped($sql,
+ array(
+ $table,
+ $idRow,
+ bigintval($id),
+ ), $file, $line
+ );
+}
+
+// ALTER TABLE wrapper function
+function sqlQueryAlterTable ($sql, $file, $line, $enableCodes = TRUE) {
+ // Abort if link is down
+ if (!isSqlLinkUp()) return FALSE;
+
+ // This is the default result...
+ $result = FALSE;
+
+ // Determine index/fulltext/unique word
+ $isAlterIndex = (
+ (
+ isInString('INDEX', $sql)
+ ) || (
+ isInString('KEY', $sql)
+ ) || (
+ isInString('FULLTEXT', $sql)
+ ) || (
+ isInString('UNIQUE', $sql)
+ )
+ );
+
+ // Extract table name
+ $tableArray = explode(' ', $sql);
+ $tableName = str_replace('`', '', $tableArray[2]);
+
+ // Debug log
+ //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sql=' . $sql . ',tableName=' . $tableName . ',tableArray=
' . print_r($tableArray, TRUE) . ',isAlterIndex=' . intval($isAlterIndex)); + + // Shall we add/drop? + if (((isInString('ADD', $sql)) || (isInString('DROP', $sql)) || (isInString('CHANGE', $sql))) && ($isAlterIndex === FALSE)) { + // Try two columns, one should fix + foreach (array(4,5) as $idx) { + // If an entry is not set, abort here + if (!isset($tableArray[$idx])) { + // Debug log this + logDebugMessage(__FUNCTION__, __LINE__, 'columnName=' . $columnName . ',idx=' . $idx . ',sql=' . $sql . ' is missing!'); + break; + } // END - if + + // And column name as well + $columnName = $tableArray[$idx]; + + // Debug log + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'columnName=' . $columnName . ',idx=' . $idx . ',sql=' . $sql . ',hasZeroNums=' . intval(ifSqlTableColumnExists($tableName, $columnName))); + + // Is there no entry on ADD or an entry on DROP/CHANGE? + if (((!ifSqlTableColumnExists($tableName, $columnName)) && (isInString('ADD', $sql))) || ((ifSqlTableColumnExists($tableName, $columnName)) && ((isInString('DROP', $sql)) || ((isInString('CHANGE', $sql)) && ($idx == 4) && ((!ifSqlTableColumnExists($tableName, $tableArray[5])) || ($columnName == $tableArray[5])))))) { + // Do the query + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'Executing: ' . $sql); + $result = sqlQuery($sql, $file, $line, FALSE); + + // Skip further attempt(s) + break; + } elseif ((((ifSqlTableColumnExists($tableName, $columnName)) && (isInString('ADD', $sql))) || ((!ifSqlTableColumnExists($tableName, $columnName)) && ((isInString('DROP', $sql))) || (isInString('CHANGE', $sql)))) && ($columnName != 'KEY')) { + // Abort here because it is alreay there + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'Skipped: sql=' . $sql . ',columnName=' . $columnName . ',idx=' . $idx); + break; + } elseif ((!ifSqlTableColumnExists($tableName, $columnName)) && (isInString('DROP', $sql))) { + // Abort here because we tried to drop a column which is not there (never created maybe) + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'No drop: ' . $sql); + break; + } elseif ($columnName != 'KEY') { + // Something didn't fit, we better log it + logDebugMessage(__FUNCTION__, __LINE__, 'Possible problem: ' . $sql . ',hasZeroNums=' . intval(ifSqlTableColumnExists($tableName, $columnName)) . ''); + } + } // END - foreach + } elseif ((getTableType() == 'InnoDB') && (isInString('FULLTEXT', $sql))) { + // Skip this query silently because InnoDB does not understand fulltext indexes + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf('Skipped FULLTEXT: sql=%s,tableName=%s,hasZeroNums=%d,file=%s,line=%s', $sql, $tableName, intval((is_bool($result)) ? 0 : ifSqlTableColumnExists($columnName)), $file, $line)); + } elseif ($isAlterIndex === TRUE) { + // And column name as well without backticks + $keyName = str_replace('`', '', $tableArray[5]); + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'keyName=' . $keyName . ',tableArray=
' . print_r($tableArray, TRUE) . ''); + + // Is this "UNIQUE" or so? FULLTEXT has been handled the elseif() block above + if (in_array(strtoupper($tableArray[4]), array('INDEX', 'UNIQUE', 'KEY', 'FULLTEXT'))) { + // Init loop + $begin = 1; + $keyName = ','; + while (isInString(',', $keyName)) { + // Use last + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'keyName=' . $keyName . 'begin=' . $begin . ' - BEFORE'); + $keyName = str_replace('`', '', $tableArray[count($tableArray) - $begin]); + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'keyName=' . $keyName . 'begin=' . $begin . ' - BETWEEN'); + + // Remove brackes + $keyName = str_replace(array('(', ')'), array('', ''), $keyName); + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'keyName=' . $keyName . 'begin=' . $begin . ' - AFTER'); + + // Continue + $begin++; + } // END while + } // END - if + + // Shall we run it? + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ', tableArray[3]=' . $tableArray[3] . ',keyName=' . $keyName); + if (($tableArray[3] == 'ADD') && (!ifSqlTableIndexExist($tableName, $keyName))) { + // Send it to the sqlQuery() function to add it + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sql=' . $sql . ' - ADDING!'); + $result = sqlQuery($sql, $file, $line, $enableCodes); + } elseif (($tableArray[3] == 'DROP') && (ifSqlTableIndexExist($tableName, $keyName))) { + // Send it to the sqlQuery() function to drop it + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sql=' . $sql . ' - DROPPING!'); + $result = sqlQuery($sql, $file, $line, $enableCodes); + } else { + // Not executed + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'Not executed: ' . $sql); + } + } else { + // Other ALTER TABLE query + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, $sql); + $result = sqlQuery($sql, $file, $line, $enableCodes); + } + + // Return result + return $result; +} + +// Getter for SQL link +function getSqlLink () { + // Init link + $link = NULL; + + // Is it in the globals? + if (isset($GLOBALS['__sql_link'])) { + // Then take it + $link = $GLOBALS['__sql_link']; + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'link[]=' . gettype($link) . ' - FROM GLOBALS!'); + } // END - if + + // Return it + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'link[]=' . gettype($link) . ' - EXIT!'); + return $link; +} + +// Setter for link +// Do *not* add debug lines here. This will cause and endless loop +function setSqlLink ($file, $line, $link) { + // Is this a resource or null? + if ((ifFatalErrorsDetected()) && (isInstaller())) { + // This may happen in installation phase + return; + } elseif ((!is_resource($link)) && (!is_null($link)) && (!$link instanceof mysqli)) { + // This should never happen! + reportBug($file . ':' . __FUNCTION__, $line . ':' . __LINE__, sprintf('Type of link is not resource, null or mysqli class, type=%s', gettype($link))); + } // END - if + + // Set it + $GLOBALS['__sql_link'] = $link; + + // Re-init cache + $GLOBALS['__is_sql_link_up'] = ((function_exists('isValidSqlLink')) && (isValidSqlLink($link))); +} + +// Checks if the link is up +function isSqlLinkUp () { + // Is there cached this? + if (!isset($GLOBALS['__is_sql_link_up'])) { + // Something bad went wrong + reportBug(__FUNCTION__, __LINE__, 'Called before setSqlLink() was called!'); + } // END - if + + // Return the result + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, '__is_sql_link_up=' . intval($GLOBALS['__is_sql_link_up']) . ' - EXIT!'); + return $GLOBALS['__is_sql_link_up']; +} + +// Wrapper function to make code more readable +function ifSqlHasZeroNumRows ($result) { + // Just pass it through + return (sqlNumRows($result) === 0); +} + +// Wrapper function to make code more readable +function ifSqlHasZeroAffectedRows () { + // Just pass it through + return (sqlAffectedRows() === 0); +} + +// Private function to prepare the SQL query string +function sqlPrepareQueryString ($sqlString, $enableCodes = TRUE) { + // Debug message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sqlString=' . $sqlString . ',enableCodes=' . intval($enableCodes) . ' - ENTERED!'); + + // Is it already cached? + if (!isset($GLOBALS['sql_strings']['' . $sqlString . ''])) { + // Preserve escaping and compile URI codes+config+expression code + $sqlString2 = str_replace(chr(92), '{BACKLASH}', $sqlString); + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sqlString2=' . $sqlString2); + $sqlString2 = FILTER_COMPILE_EXPRESSION_CODE(FILTER_COMPILE_CONFIG($sqlString2)); + + // Debug message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sqlString2=' . $sqlString2); + + // Do final compilation and revert {BACKSLASH} + $GLOBALS['sql_strings']['' . $sqlString . ''] = doFinalCompilation($sqlString2, FALSE, $enableCodes); + $GLOBALS['sql_strings']['' . $sqlString . ''] = str_replace('{BACKLASH}', chr(92), $GLOBALS['sql_strings']['' . $sqlString . '']); + } else { + // Log message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sqlString=' . $sqlString . ' - CACHE!'); + } + + // Debug message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'sqlString=' . $sqlString . ',enableCodes=' . intval($enableCodes) . ',returned sql_string=' . $GLOBALS['sql_strings']['' . $sqlString . ''] . ' - EXIT!'); + + // Return it + return $GLOBALS['sql_strings']['' . $sqlString . '']; +} + +// Creates a MySQL TIMESTAMP compatible string from given Uni* timestamp +function getSqlTimestampFromUnix ($timestamp) { + return generateDateTime($timestamp, '7'); +} + +// Check if there is a SQL table created +function ifSqlTableExists ($tableName) { + // Make sure double-prefixes are being removed + $tableName = str_replace('{?_MYSQL_PREFIX?}_', '', $tableName); + + // Log message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ' - ENTERED!'); + + // Is there cache? + if (!isset($GLOBALS[__FUNCTION__][$tableName])) { + // Check if the table is there + $result = sqlQueryEscaped("SHOW TABLES FROM `{?__DB_NAME?}` WHERE `Tables_in_{?__DB_NAME?}`='{?_MYSQL_PREFIX?}_%s'", + array($tableName), __FUNCTION__, __LINE__); + + // Is a link there? + if (!isValidSqlLink($result)) { + // Is installation phase? + if (isInstaller()) { + // Then silently abort here + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'result[]=' . gettype($result) . ',isLinkUp=' . intval(isSqlLinkUp()) . ',tableName=' . $tableName . ' - Returning FALSE ...'); + return FALSE; + } else { + // Please report this + reportBug(__FUNCTION__, __LINE__, 'result[]=' . gettype($result) . ' is not a resource.'); + } + } // END - if + + // Is there an entry? + $GLOBALS[__FUNCTION__][$tableName] = (sqlNumRows($result) == 1); + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',numRows=' . intval($GLOBALS[__FUNCTION__][$tableName])); + } // END - if + + // Return cache + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',result=' . intval($GLOBALS[__FUNCTION__][$tableName]) . ' - EXIT!'); + return $GLOBALS[__FUNCTION__][$tableName]; +} + +// Is a table column there? +function ifSqlTableColumnExists ($tableName, $columnName, $forceFound = FALSE) { + // Remove back-ticks + $columnName = str_replace('`', '', $columnName); + + // Debug message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',columnName=' . $columnName . ' - ENTERED!'); + + // If the table is not there, it is okay + if (!ifSqlTableExists($tableName)) { + // Then abort here + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'Table ' . $tableName . ' does not exist, columnName=' . $columnName . ',forceFound=' . intval($forceFound)); + return (($forceFound === FALSE) && (isInstaller())); + } // END - if + + // Get column information + $result = sqlQueryEscaped("SHOW COLUMNS FROM `%s` LIKE '%s'", + array( + $tableName, + $columnName + ), __FUNCTION__, __LINE__); + + // Is a link there? + if (!isValidSqlLink($result)) { + // Is installation phase? + if (isInstaller()) { + // Then silently abort here + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'result[]=' . gettype($result) . ',isLinkUp=' . intval(isSqlLinkUp()) . ',tableName=' . $tableName . ',columnName=' . $columnName . ' - Returning FALSE ...'); + return $forceFound; + } else { + // Please report this + reportBug(__FUNCTION__, __LINE__, 'result[]=' . gettype($result) . ' is not a resource.'); + } + } // END - if + + // Determine it + $doesExist = (!ifSqlHasZeroNumRows($result)); + + // Free result + sqlFreeResult($result); + + // Return cache + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',columnName=' . $columnName . ',doesExist=' . intval($doesExist) . ' - EXIT!'); + return $doesExist; +} + +// Checks depending on the mode if the index is there +function ifSqlTableIndexExist ($tableName, $keyName, $forceFound = FALSE) { + // Remove back-ticks + $keyName = str_replace('`', '', $keyName); + + // Debug message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',keyName=' . $keyName . ' - ENTERED!'); + + // If the table is not there, it is okay + if (!ifSqlTableExists($tableName)) { + // Then abort here + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'Table ' . $tableName . ' does not exist, keyName=' . $keyName . ',forceFound=' . intval($forceFound)); + return (($forceFound === FALSE) && (isInstaller())); + } // END - if + + // Show indexes + $result = sqlQueryEscaped("SHOW INDEX FROM `%s`", array($tableName), __FUNCTION__, __LINE__); + + // Is a link there? + if (!isValidSqlLink($result)) { + // Is installation phase? + if (isInstaller()) { + // Then silently abort here + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'result[]=' . gettype($result) . ',isLinkUp=' . intval(isSqlLinkUp()) . ',tableName=' . $tableName . ',keyName=' . $keyName . ' - Returning FALSE ...'); + return $forceFound; + } else { + // Please report this + reportBug(__FUNCTION__, __LINE__, 'result[]=' . gettype($result) . ' is not a resource.'); + } + } // END - if + + // The key is not found by default + $doesExist = FALSE; + + // Walk through all + while ($content = sqlFetchArray($result)) { + // Is it the requested one? + if ($content['Key_name'] == $keyName) { + // Then it is found and exit + $doesExist = TRUE; + break; + } // END - if + } // END - while + + // Free result + sqlFreeResult($result); + + // Return cache + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',keyName=' . $keyName . ',doesExist=' . intval($doesExist) . ' - EXIT!'); + return $doesExist; +} + +// Init database layer +function initDatabaseLayer () { + // Set all required variables: + $GLOBALS['last_sql_error'] = ''; +} + +// Get last SQL error +function getLastSqlError () { + return $GLOBALS['last_sql_error']; +} + +// Gets an array (or false if none is found) from all supported engines +function getArrayFromSupportedSqlEngines ($requestedEngine = 'ALL') { + // Init array + $engines = array(); + + // This also worked, now we need to check if the selected database type is supported + $result = sqlQuery('SHOW ENGINES', __FUNCTION__, __LINE__); + + // Are there entries? (Bad if not) + if (!ifSqlHasZeroNumRows($result)) { + // Load all and check for active entries + while ($content = sqlFetchArray($result)) { + // Debug message + //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'support=' . $requestedEngine . ',Engine=' . $content['Engine'] . ',Support=' . $content['Support']); + + // Is this supported? + if ((($requestedEngine == 'ALL') || ($content['Engine'] == $requestedEngine)) && (in_array($content['Support'], array('YES', 'DEFAULT')))) { + // Add it + array_push($engines, $content); + } elseif (isDebugModeEnabled()) { + // Log it away in debug mode + logDebugMessage(__FUNCTION__, __LINE__, 'Engine ' . $content['Engine'] . ' is not supported (' . $content['Supported'] . ' - ' . $requestedEngine . ')'); + } + } // END - if + } else { + // No engines! :( + $engines = FALSE; + } + + // Free result + sqlFreeResult($result); + + // Return result + return $engines; +} + +// "Getter" for result from given table and field/type LIKEs +function sqlGetResultFromLikeColumnsType ($tableName, $field, $type) { + // The table should be there + assert(ifSqlTableExists($tableName)); + + // Default no field set + $fieldSql = ''; + if (!empty($field)) { + // Then use it + $fieldSql = "`Field` LIKE '" . $field . "' AND"; + } // END - if + + // Show them + return sqlQueryEscaped("SHOW COLUMNS FROM + `{?_MYSQL_PREFIX?}_%s` +WHERE + " . $fieldSql . " + `Type` LIKE '%s%%'", + array( + $tableName, + $type + ), __FUNCTION__, __LINE__ + ); +} + +// Log SQL errors to debug.log in installation phase or call reportBug() +function logSqlError ($file, $line, $message) { + // Remember plain error in last_sql_error + setSqlError($file, $line, $message); + + // Is login set? + if (!empty($GLOBALS['mysql']['login'])) { + // Secure login name in message + $message = str_replace($GLOBALS['mysql']['login'], '***', $message); + } // END - if + + // Is database password set? + if (!empty($GLOBALS['mysql']['password'])) { + // Secure password in message + $message = str_replace($GLOBALS['mysql']['password'], '***', $message); + } // END - if + + // Is database name set? + if (!empty($GLOBALS['mysql']['dbase'])) { + // Secure database name in message + $message = str_replace($GLOBALS['mysql']['dbase'], '***', $message); + } // END - if + + // Is there installation phase? + if (isInstaller()) { + /* + * In installation phase, we don't want SQL errors abort e.g. connection + * tests, so just log it away. + */ + logDebugMessage($file, $line, $message); + } else { + // Regular mode, then call reportBug() + reportBug($file, $line, $message); + } +} + // [EOF] ?>