function getTableList()
{
global $TABLE_PREFIX, $APP;
// get table names
$mysqlTables = getMysqlTablesWithPrefix();
$schemaTables = getSchemaTables();
// create multi query
$tables = array();
$tableRowCounts = array();
foreach ($schemaTables as $tableName) {
$tableNameWithPrefix = getTableNameWithPrefix($tableName);
if (in_array($tableNameWithPrefix, $mysqlTables)) {
$rowCount = mysql_count($tableNameWithPrefix);
}
$localTableSchema = loadSchema($tableName);
array_push($tables, array('tableName' => $tableName, 'menuName' => @$localTableSchema['menuName'], 'menuType' => @$localTableSchema['menuType'], 'menuOrder' => @$localTableSchema['menuOrder'], 'menuHidden' => @$localTableSchema['menuHidden'], 'tableHidden' => @$localTableSchema['tableHidden'], '_indent' => @$localTableSchema['_indent'], 'recordCount' => $rowCount));
}
// sort table list
uasort($tables, '_sortMenusByOrder');
//
return $tables;
}
function _upgradeToVersion1_10()
{
global $SETTINGS, $APP, $TABLE_PREFIX;
if ($SETTINGS['programVersion'] >= '1.10') {
return;
}
### Update Access Levels
_upgradeToVersion1_10_accessLevels();
// update mysql tables, schema, schema preset files
$schemaDirs = array(DATA_DIR . '/schema', DATA_DIR . '/schemaPresets');
$fieldsToMaintainOrder = array('num', 'createdDate', 'createdByUserNum', 'updatedDate', 'updatedByUserNum');
foreach ($schemaDirs as $schemaDir) {
foreach (getSchemaTables($schemaDir) as $tableName) {
$schema = loadSchema($tableName, $schemaDir);
$escapedTableName = mysql_escape(getTableNameWithPrefix($tableName));
$isPreset = $schemaDir == DATA_DIR . '/schemaPresets';
// skip tables
if ($tableName == 'uploads') {
continue;
}
if ($tableName == '_accesslist') {
continue;
}
// add fields
$schema['num']['order'] = "1";
$schema['createdDate'] = array('order' => '2', 'type' => 'none', 'label' => "Created", 'isSystemField' => '1');
$schema['createdByUserNum'] = array('order' => '3', 'type' => 'none', 'label' => "Created By", 'isSystemField' => '1');
$schema['updatedDate'] = array('order' => '4', 'type' => 'none', 'label' => "Last Updated", 'isSystemField' => '1');
$schema['updatedByUserNum'] = array('order' => '5', 'type' => 'none', 'label' => "Last Updated By", 'isSystemField' => '1');
//
foreach (array_keys($schema) as $fieldname) {
$fieldSchema =& $schema[$fieldname];
if (!is_array($fieldSchema)) {
continue;
}
// fields are stored as arrays, other entries are table metadata, skip metadata
if (!in_array($fieldname, $fieldsToMaintainOrder)) {
$fieldSchema['order'] = @$fieldSchema['order'] + 6;
}
### Change column type for checkbox fields
if (@$fieldSchema['type'] == 'checkbox' && !$isPreset) {
mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = 0 WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `{$fieldname}` tinyint(1) unsigned NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
}
### Change column type for datetime fields
if (@$fieldSchema['type'] == 'date' && !$isPreset) {
mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = '0000-00-00 00:00:00' WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `{$fieldname}` datetime NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
}
// Rename autoPublish fields
if ($fieldname == 'autoPublishStartDate' && !@$schema['publishDate']) {
$schema['publishDate'] = $fieldSchema;
unset($schema[$fieldname]);
if (!$isPreset) {
mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = '0000-00-00 00:00:00' WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `publishDate` datetime NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
}
}
if ($fieldname == 'autoPublishEndDate' && !@$schema['removeDate']) {
$schema['removeDate'] = $fieldSchema;
unset($schema[$fieldname]);
if (!$isPreset) {
mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = '0000-00-00 00:00:00' WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `removeDate` datetime NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
}
}
if ($fieldname == 'autoPublishNeverExpires' && !@$schema['neverRemove']) {
$schema['neverRemove'] = $fieldSchema;
unset($schema[$fieldname]);
if (!$isPreset) {
mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = 0 WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `neverRemove` tinyint(1) unsigned NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__);
}
}
}
uasort($schema, '__sortSchemaFieldsByOrder');
// sort schema keys
saveSchema($tableName, $schema, $schemaDir);
}
}
//
createMissingSchemaTablesAndFields();
// create missing fields
clearAlertsAndNotices();
// don't show "created table/field" alerts
saveAndRefresh('1.10');
// uncomment this after next update
}
function mysql_insert($tableName, $colsToValues, $tempDisableMysqlStrictMode = false)
{
//
$tableName = getTableNameWithPrefix($tableName);
$set = mysql_getMysqlSetValues($colsToValues);
$insert = "INSERT INTO `{$tableName}` SET {$set}";
//
if ($tempDisableMysqlStrictMode) {
mysqlStrictMode(false);
}
mysql_query($insert) or dieAsCaller("MySQL Error: " . mysql_error() . "\n");
$recordNum = mysql_insert_id();
if ($tempDisableMysqlStrictMode) {
mysqlStrictMode(true);
}
return $recordNum;
}
function incrementCounterField($tablename, $fieldname, $recordNumber)
{
global $VIEWER_NAME;
// error checking
if (!$tablename) {
die(__FUNCTION__ . ": No 'tablename' value specified!");
}
if (!$fieldname) {
die(__FUNCTION__ . ": No 'fieldname' value specified!");
}
if (!$recordNumber) {
die(__FUNCTION__ . ": No 'recordNumber' value specified!");
}
// update counter
$escapedTableName = mysql_escape(getTableNameWithPrefix($tablename));
$query = "UPDATE `{$escapedTableName}` SET `{$fieldname}` = IFNULL(`{$fieldname}`,0) + 1";
$query .= " WHERE `num` = '" . mysql_escape($recordNumber) . "'";
$result = @mysql_query($query);
if (!$result) {
die(__FUNCTION__ . " MySQL Error: " . htmlencode(mysql_error()) . "\n");
}
if (!mysql_affected_rows()) {
die(__FUNCTION__ . ": Couldn't find record '" . htmlencode($recordNumber) . "'!");
}
}
require_once "lib/menus/database/editTable_functions.php";
$tableDetails = getTableDetails();
$errors = getTableDetailErrors($schema);
if ($errors) {
alert($errors);
}
showHeader();
?>
<form method="post" action="?" autocomplete="off">
<input type="submit" style="width: 0px; height: 0px; position: absolute; border: none; padding: 0px" /> <!-- bugfix: hitting enter in textfield submits first submit button on form -->
<input type="hidden" name="menu" value="database" />
<input type="hidden" name="_defaultAction" value="editTable" />
<input type="hidden" name="tableName" id="tableName" value="<?php
echo htmlencode(getTableNameWithPrefix($_REQUEST['tableName']));
?>
" />
<input type="hidden" name="menuOrder" value="<?php
echo htmlencode(@$schema['menuOrder']);
?>
" />
<?php
echo security_getHiddenCsrfTokenField();
?>
<div class="content-box list-tables">
<div class="content-box-header">
<h3>
<?php
function getRecord($options)
{
global $VIEWER_NAME, $TABLE_PREFIX;
$VIEWER_NAME = "Page Viewer ({$options['tableName']})";
// error checking
$requiredOptions = array('tableName');
$validOptions = array('tableName', 'recordNum', 'where', 'titleField', 'orderBy');
$errors = _getOptionErrors($requiredOptions, $validOptions, $options);
if ($errors) {
die("{$VIEWER_NAME} errors<br/>\n{$errors}");
}
// set defaults
$schema = loadSchema($options['tableName']);
if (!@$options['recordNum']) {
$options['recordNum'] = getLastNumberInUrl();
}
if (@$schema['menuType'] == 'single') {
$options['recordNum'] = "1";
}
// always load record 1 for single menus
// get where condition
$whereConditions = '';
$escapedRecordNum = mysql_escape((int) $options['recordNum']);
if ($options['where']) {
$whereConditions = $options['where'];
} elseif ($options['recordNum']) {
$whereConditions = "num = '{$escapedRecordNum}'";
}
// get record
$fullTableName = getTableNameWithPrefix($options['tableName']);
$escapedTableName = mysql_escape($fullTableName);
$where = _addWhereConditionsForSpecialFields($schema, $whereConditions, $options);
$orderBy = @$options['orderBy'] ? "ORDER BY {$options['orderBy']}" : '';
$query = "SELECT * FROM `{$escapedTableName}` {$where} {$orderBy} LIMIT 0, 1";
$result = mysql_query($query) or die("{$VIEWER_NAME}: MySQL Error: " . htmlencode(mysql_error()) . "\n");
$record = mysql_fetch_assoc($result);
// add _link field
if ($record) {
$filenameValue = getFilenameFieldValue($record, @$options['titleField']);
$record['_link'] = _getLink($_SERVER['SCRIPT_NAME'], $filenameValue, $record['num'], @$options['useSeoUrls']);
}
// define upload fields
if ($record) {
foreach ($schema as $fieldname => $fieldSchema) {
if (!is_array($fieldSchema)) {
continue;
}
// not a field definition, table metadata field
if (@$fieldSchema['type'] != 'upload') {
continue;
}
// skip all but upload fields
$record[$fieldname] = "Use getUploads() function to list uploads (See code generator).\n";
}
}
//
return $record;
}
function saveTableDetails()
{
global $TABLE_PREFIX, $schema, $APP, $tableName, $tableNameWithPrefix;
$oldSchemaFilepath = DATA_DIR . '/schema/' . getTableNameWithoutPrefix($_REQUEST['tableName']) . ".ini.php";
$newSchemaFilepath = DATA_DIR . '/schema/' . getTableNameWithoutPrefix($_REQUEST['newTableName']) . ".ini.php";
//
security_dieUnlessPostForm();
security_dieUnlessInternalReferer();
security_dieOnInvalidCsrfToken();
//
disableInDemoMode('', 'database/listTables.php');
// error checking
$errors = '';
if ($_REQUEST['newTableName'] == '') {
$errors .= "You must specify a tablename!<br/>\n";
}
if (preg_match("/dragSortOrder/", @$_REQUEST['listPageFields']) || preg_match("/dragSortOrder/", $_REQUEST['listPageOrder'])) {
if (!preg_match("/^dragSortOrder/", @$_REQUEST['listPageFields'])) {
$errors .= "If used, dragSortOrder must be the first field in 'ListPage Fields'!<br/>\n";
}
if (!preg_match("/^dragSortOrder/", $_REQUEST['listPageOrder'])) {
$errors .= "If used, dragSortOrder must be the first field in 'Order By'!<br/>\n";
}
}
if (@$_REQUEST['tableName'] && !$schema) {
$errors .= "Error updating schema file. Please wait a few seconds and try again.<br/>\n";
}
if (!is_writable(DATA_DIR . '/schema/')) {
$errors .= "Schema dir '/data/schema/' isn't writable. Please update permissions.<br/>\n";
} elseif (!is_writable($oldSchemaFilepath)) {
$errors .= "Schema file '/data/schema/" . basename($oldSchemaFilepath) . "' isn't writable. Please update permissions.<br/>\n";
}
// v2.53 - require urls to start with scheme:// or / (to ensure links are valid when moving between sites)
$fieldNamesToLabels = array();
$fieldNamesToLabels['_listPage'] = 'List Page Url';
$fieldNamesToLabels['_detailPage'] = 'Detail Page Url';
$fieldNamesToLabels['_previewPage'] = 'Preview Page Url';
foreach ($fieldNamesToLabels as $name => $label) {
$startsWithHttpOrSlash = preg_match("|^(\\w+:/)?/|", @$_REQUEST[$name]);
if (@$_REQUEST[$name] && !$startsWithHttpOrSlash) {
$errors .= t("{$label} must start with /") . "<br/>\n";
}
}
//
if ($errors) {
alert($errors);
return;
}
// force add table prefix (if not specified)
$_REQUEST['newTableName'] = getTableNameWithPrefix($_REQUEST['newTableName']);
### rename table
if ($_REQUEST['tableName'] != $_REQUEST['newTableName']) {
$error = getTablenameErrors($_REQUEST['newTableName']);
if ($error) {
alert($error);
return;
}
// rename mysql table
$result = mysql_query("RENAME TABLE `" . mysql_escape($_REQUEST['tableName']) . "`\n TO `" . mysql_escape($_REQUEST['newTableName']) . "`") or die("Error renaming MySQL table:\n\n" . htmlencode(mysql_error()) . "\n");
// rename schema file
rename_winsafe($oldSchemaFilepath, $newSchemaFilepath) or die("Error renaming schema file!");
// update uploads table with new table name
$where = array('tableName' => getTableNameWithoutPrefix($_REQUEST['tableName']));
// old tableName
$colsToValues = array('tableName' => getTableNameWithoutPrefix($_REQUEST['newTableName']));
// new tableName
$result = mysql_update('uploads', null, $where, $colsToValues);
// update tableName form field
$_REQUEST['tableName'] = $_REQUEST['newTableName'];
// update globals with new tablename
$tableName = $_REQUEST['tableName'];
// sic
$tableNameWithPrefix = $_REQUEST['tableName'];
}
### update schema fields
$schema['menuName'] = $_REQUEST['menuName'];
$schema['_indent'] = @$_REQUEST['_indent'];
$schema['menuType'] = $_REQUEST['menuType'];
$schema['menuOrder'] = $_REQUEST['menuOrder'];
if ($_REQUEST['menuType'] != 'link') {
$schema['menuHidden'] = $_REQUEST['menuHidden'];
$schema['listPageFields'] = @$_REQUEST['listPageFields'];
$schema['listPageOrder'] = $_REQUEST['listPageOrder'];
$schema['listPageSearchFields'] = $_REQUEST['listPageSearchFields'];
$schema['_perPageDefault'] = @$_REQUEST['_perPageDefault'];
$schema['_maxRecords'] = $_REQUEST['_maxRecords'];
$schema['_maxRecordsPerUser'] = $_REQUEST['_maxRecordsPerUser'];
$schema['_disableAdd'] = $_REQUEST['_disableAdd'];
$schema['_disableView'] = $_REQUEST['_disableView'];
$schema['_disableModify'] = $_REQUEST['_disableModify'];
$schema['_disableErase'] = $_REQUEST['_disableErase'];
$schema['_disablePreview'] = $_REQUEST['_disablePreview'];
$schema['_filenameFields'] = @$_REQUEST['_filenameFields'];
$schema['_listPage'] = @$_REQUEST['_listPage'];
$schema['_detailPage'] = $_REQUEST['_detailPage'];
$schema['_previewPage'] = $_REQUEST['_previewPage'];
$schema['_hideRecordsFromDisabledAccounts'] = $_REQUEST['_hideRecordsFromDisabledAccounts'];
$schema['_requiredPlugins'] = @$_REQUEST['_requiredPlugins'];
}
if ($_REQUEST['menuType'] == 'link') {
$schema['_url'] = $_REQUEST['_url'];
$schema['_linkTarget'] = @$_REQUEST['_linkTarget'];
$schema['_linkMessage'] = @$_REQUEST['_linkMessage'];
$schema['_iframeHeight'] = @$_REQUEST['_iframeHeight'];
unset($schema['_targetBlank']);
// unset old schema value (if it exists)
}
if ($_REQUEST['menuType'] == 'category') {
$schema['_maxDepth'] = $_REQUEST['_maxDepth'];
}
saveSchema($_REQUEST['tableName'], $schema);
//
notice("Table details for '" . htmlencode($schema['menuName']) . "' have been saved.");
}
function backupDatabase($filenameOrPath = '', $selectedTable = '')
{
global $TABLE_PREFIX;
$prefixPlaceholder = '#TABLE_PREFIX#_';
set_time_limit(60 * 5);
// v2.51 - allow up to 5 minutes to backup/restore database
session_write_close();
// v2.51 - End the current session and store session data so locked session data doesn't prevent concurrent access to CMS by user while backup in progress
// error checking
if ($selectedTable != '') {
$schemaTables = getSchemaTables();
if (preg_match("/[^\\w\\d\\-\\.]/", $selectedTable)) {
die(__FUNCTION__ . " : \$selectedTable contains invalid chars! " . htmlencode($selectedTable));
}
if (!in_array($selectedTable, $schemaTables)) {
die("Unknown table selected '" . htmlencode($selectedTable) . "'!");
}
}
// open backup file
$hostname = preg_replace('/[^\\w\\d\\-\\.]/', '', @$_SERVER['HTTP_HOST']);
if (!$filenameOrPath) {
$filenameOrPath = "{$hostname}-v{$GLOBALS['APP']['version']}-" . date('Ymd-His');
if ($selectedTable) {
$filenameOrPath .= "-{$selectedTable}";
}
$filenameOrPath .= ".sql.php";
}
$outputFilepath = isAbsPath($filenameOrPath) ? $filenameOrPath : DATA_DIR . "/backups/{$filenameOrPath}";
// v2.60 if only filename provided, use /data/backup/ as the basedir
$fp = @fopen($outputFilepath, 'x');
if (!$fp) {
// file already exists - avoid race condition
session_start();
return false;
}
// create no execute php header
fwrite($fp, "-- <?php die('This is not a program file.'); exit; ?>\n\n");
# prevent file from being executed
// get tablenames to backup
if ($selectedTable) {
$tablenames = array(getTableNameWithPrefix($selectedTable));
} else {
$skippedTables = array('_cron_log', '_error_log', '_outgoing_mail', '_nlb_log');
// don't backup these table names
$skippedTables = applyFilters('backupDatabase_skippedTables', $skippedTables);
// let users skip tables via plugins
$skippedTables = array_map('getTableNameWithPrefix', $skippedTables);
// add table_prefix to all table names (if needed)
$allTables = getMysqlTablesWithPrefix();
$tablenames = array_diff($allTables, $skippedTables);
// remove skipped tables from list
}
// backup database
foreach ($tablenames as $unescapedTablename) {
$escapedTablename = mysql_escape($unescapedTablename);
$tablenameWithFakePrefix = $prefixPlaceholder . getTableNameWithoutPrefix($escapedTablename);
// create table
fwrite($fp, "\n--\n");
fwrite($fp, "-- Table structure for table `{$tablenameWithFakePrefix}`\n");
fwrite($fp, "--\n\n");
fwrite($fp, "DROP TABLE IF EXISTS `{$tablenameWithFakePrefix}`;\n\n");
$result = mysql_query("SHOW CREATE TABLE `{$escapedTablename}`");
list(, $createStatement) = mysql_fetch_row($result) or die("MySQL Error: " . htmlencode(mysql_error()));
$createStatement = str_replace("TABLE `{$TABLE_PREFIX}", "TABLE `{$prefixPlaceholder}", $createStatement);
fwrite($fp, "{$createStatement};\n\n");
if (is_resource($result)) {
mysql_free_result($result);
}
// create rows
fwrite($fp, "\n--\n");
fwrite($fp, "-- Dumping data for table `{$tablenameWithFakePrefix}`\n");
fwrite($fp, "--\n\n");
$result = mysql_query("SELECT * FROM `{$escapedTablename}`") or die("MySQL Error: " . htmlencode(mysql_error()));
while ($row = mysql_fetch_row($result)) {
$values = '';
foreach ($row as $value) {
if (is_null($value)) {
$values .= 'NULL,';
} else {
$values .= '"' . mysql_real_escape_string($value) . '",';
}
}
$values = chop($values, ',');
// remove trailing comma
fwrite($fp, "INSERT INTO `{$tablenameWithFakePrefix}` VALUES({$values});\n");
}
if (is_resource($result)) {
mysql_free_result($result);
}
}
//
fwrite($fp, "\n");
$result = fwrite($fp, "-- Dump completed on " . date('Y-m-d H:i:s O') . "\n\n");
if ($result === false) {
die(__FUNCTION__ . ": Error writing backup file! {$php_errormsg}");
}
fclose($fp) || die(__FUNCTION__ . ": Error closing backup file! {$php_errormsg}");
//
@session_start();
// hide error: E_WARNING: session_start(): Cannot send session cache limiter - headers already sent
return $outputFilepath;
}
