/**
* get database connection information
* @param $f3
* @param bool|false $exec
* @return array
*/
protected function checkDatabase($f3, $exec = false)
{
foreach ($this->databases as $dbKey => $dbData) {
$dbLabel = '';
$dbName = '';
$dbUser = '';
$dbConfig = [];
// DB connection status
$dbConnected = false;
// DB type (e.g. MySql,..)
$dbDriver = 'unknown';
// enable database ::setup() function in UI
$dbSetupEnable = false;
// check of everything is OK (connection, tables, columns, indexes,..)
$dbStatusCheckCount = 0;
// db queries for column fixes (types, indexes, unique)
$dbColumnQueries = [];
// tables that should exist in this DB
$requiredTables = [];
// check DB for valid connection
$db = DB\Database::instance()->getDB($dbKey);
switch ($dbKey) {
case 'PF':
$dbLabel = 'Pathfinder';
$dbName = Controller::getEnvironmentData('DB_NAME');
$dbUser = Controller::getEnvironmentData('DB_USER');
// enable (table) setup for this DB
$dbSetupEnable = true;
// get table data from model
foreach ($dbData['models'] as $model) {
$tableConfig = call_user_func($model . '::resolveConfiguration');
$requiredTables[$tableConfig['table']] = ['model' => $model, 'name' => $tableConfig['table'], 'fieldConf' => $tableConfig['fieldConf'], 'exists' => false, 'empty' => true, 'foreignKeys' => []];
}
break;
case 'CCP':
$dbLabel = 'EVE-Online [SDE]';
$dbName = Controller::getEnvironmentData('DB_CCP_NAME');
$dbUser = Controller::getEnvironmentData('DB_CCP_USER');
// get table model from static table array
foreach ($dbData['tables'] as $tableName) {
$requiredTables[$tableName] = ['exists' => false, 'empty' => true];
}
break;
}
if ($db) {
// db connect was successful
$dbConnected = true;
$dbDriver = $db->driver();
$dbConfig = $this->checkDBConfig($f3, $db);
// get tables
$schema = new SQL\Schema($db);
$currentTables = $schema->getTables();
// check each table for changes
foreach ($requiredTables as $requiredTableName => $data) {
$tableExists = false;
$tableEmpty = true;
// Check if table status is OK (no errors/warnings,..)
$tableStatusCheckCount = 0;
$currentColumns = [];
if (in_array($requiredTableName, $currentTables)) {
// Table exists
$tableExists = true;
// get existing table columns and column related constraints (if exists)
$tableModifierTemp = new MySQL\TableModifier($requiredTableName, $schema);
$currentColumns = $tableModifierTemp->getCols(true);
// get row count
$countRes = $db->exec("SELECT COUNT(*) `num` FROM " . $db->quotekey($requiredTableName));
$tableEmpty = $countRes[0]['num'] > 0 ? false : true;
} else {
// table missing
$dbStatusCheckCount++;
$tableStatusCheckCount++;
}
foreach ((array) $data['fieldConf'] as $columnName => $fieldConf) {
$columnStatusCheck = true;
$foreignKeyStatusCheck = true;
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['requiredType'] = $fieldConf['type'];
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['requiredIndex'] = $fieldConf['index'] ? '1' : '0';
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['requiredUnique'] = $fieldConf['unique'] ? '1' : '0';
if (array_key_exists($columnName, $currentColumns)) {
// column exists
// get tableModifier -> possible column update
$tableModifier = new MySQL\TableModifier($requiredTableName, $schema);
// get new column and copy Schema from existing column
$col = new MySQL\Column($columnName, $tableModifier);
$col->copyfrom($currentColumns[$columnName]);
$currentColType = $currentColumns[$columnName]['type'];
$currentColIndexData = call_user_func($data['model'] . '::indexExists', [$columnName]);
$currentColIndex = is_array($currentColIndexData);
$hasIndex = $currentColIndex ? '1' : '0';
$hasUnique = $currentColIndexData['unique'] ? '1' : '0';
$changedType = false;
$changedUnique = false;
$changedIndex = false;
// set (new) column information -------------------------------------------------------
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['exists'] = true;
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['currentType'] = $currentColType;
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['currentIndex'] = $hasIndex;
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['currentUnique'] = $hasUnique;
// check constraint -------------------------------------------------------------------
if (isset($fieldConf['constraint'])) {
// add or update constraints
foreach ((array) $fieldConf['constraint'] as $constraintData) {
$constraint = $col->newConstraint($constraintData);
$foreignKeyExists = $col->constraintExists($constraint);
$requiredTables[$requiredTableName]['foreignKeys'][] = ['exists' => $foreignKeyExists, 'keyName' => $constraint->getConstraintName()];
$col->addConstraint($constraint);
if (!$foreignKeyExists) {
$tableStatusCheckCount++;
$foreignKeyStatusCheck = false;
}
}
}
// check type changed -----------------------------------------------------------------
if ($fieldConf['type'] !== 'JSON' && !$schema->isCompatible($fieldConf['type'], $currentColType)) {
// column type has changed
$changedType = true;
$columnStatusCheck = false;
$tableStatusCheckCount++;
}
// check if column unique changed -----------------------------------------------------
$indexUpdate = false;
$indexKey = (bool) $hasIndex;
$indexUnique = (bool) $hasUnique;
if ($currentColIndexData['unique'] != $fieldConf['unique']) {
$changedUnique = true;
$columnStatusCheck = false;
$tableStatusCheckCount++;
$indexUpdate = true;
$indexUnique = (bool) $fieldConf['unique'];
}
// check if column index changed ------------------------------------------------------
if ($currentColIndex != $fieldConf['index']) {
$changedIndex = true;
$columnStatusCheck = false;
$tableStatusCheckCount++;
$indexUpdate = true;
$indexKey = (bool) $fieldConf['index'];
}
// build table with changed columns ---------------------------------------------------
if (!$columnStatusCheck || !$foreignKeyStatusCheck) {
if (!$columnStatusCheck) {
// IMPORTANT: setType is always required! Even if type has not changed
$col->type($fieldConf['type']);
// update/change/delete index/unique keys
if ($indexUpdate) {
if ($hasIndex) {
$tableModifier->dropIndex($columnName);
}
if ($indexKey) {
$tableModifier->addIndex($columnName, $indexUnique);
}
}
$tableModifier->updateColumn($columnName, $col);
}
$buildStatus = $tableModifier->build($exec);
if (is_array($buildStatus) || is_string($buildStatus)) {
// query strings for change available
$dbColumnQueries = array_merge($dbColumnQueries, (array) $buildStatus);
}
}
// set (new) column information -------------------------------------------------------
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['changedType'] = $changedType;
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['changedUnique'] = $changedUnique;
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['changedIndex'] = $changedIndex;
} elseif (!isset($fieldConf['has-manny']) && isset($fieldConf['type'])) {
// column not exists but it is required!
// columns that do not match this criteria ("mas-manny") are "virtual" fields
// and can be ignored
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['currentType'] = '';
$columnStatusCheck = false;
$tableStatusCheckCount++;
}
$requiredTables[$requiredTableName]['fieldConf'][$columnName]['statusCheck'] = $columnStatusCheck;
}
$dbStatusCheckCount += $tableStatusCheckCount;
$requiredTables[$requiredTableName]['empty'] = $tableEmpty;
$requiredTables[$requiredTableName]['exists'] = $tableExists;
$requiredTables[$requiredTableName]['statusCheckCount'] = $tableStatusCheckCount;
}
} else {
// DB connection failed
$dbStatusCheckCount++;
}
if ($exec) {
$f3->reroute('@setup');
}
$this->databases[$dbKey]['info'] = ['db' => $db, 'label' => $dbLabel, 'driver' => $dbDriver, 'name' => $dbName, 'user' => $dbUser, 'dbConfig' => $dbConfig, 'setupEnable' => $dbSetupEnable, 'connected' => $dbConnected, 'statusCheckCount' => $dbStatusCheckCount, 'columnQueries' => $dbColumnQueries, 'tableData' => $requiredTables];
}
return $this->databases;
}
/**
* erase all model data, handle with care
* @param null $db
* @param null $table
*/
public static function setdown($db = null, $table = null)
{
$self = get_called_class();
if (is_null($db) || is_null($table)) {
$df = $self::resolveConfiguration();
}
if (!is_object($db = is_string($db = $db ?: $df['db']) ? \Base::instance()->get($db) : $db)) {
trigger_error(self::E_CONNECTION);
}
if (strlen($table = strtolower($table ?: $df['table'])) == 0) {
trigger_error(self::E_NO_TABLE);
}
if (isset($df) && !empty($df['fieldConf'])) {
$fields = $df['fieldConf'];
} else {
$fields = array();
}
$deletable = array();
$deletable[] = $table;
foreach ($fields as $key => $field) {
$field = static::resolveRelationConf($field);
if (array_key_exists('has-many', $field)) {
if (!is_array($relConf = $field['has-many'])) {
continue;
}
$rel = $relConf[0]::resolveConfiguration();
// check if foreign conf matches m:m
if (array_key_exists($relConf[1], $rel['fieldConf']) && !is_null($relConf[1]) && key($rel['fieldConf'][$relConf[1]]) == 'has-many') {
// compute mm table name
$deletable[] = isset($relConf[2]) ? $relConf[2] : static::getMMTableName($rel['table'], $relConf[1], $table, $key, $rel['fieldConf'][$relConf[1]]['has-many']);
}
}
}
if ($db instanceof Jig) {
/** @var Jig $db */
$dir = $db->dir();
foreach ($deletable as $item) {
if (file_exists($dir . $item)) {
unlink($dir . $item);
}
}
} elseif ($db instanceof SQL) {
/** @var SQL $db */
$schema = new Schema($db);
$tables = $schema->getTables();
foreach ($deletable as $item) {
if (in_array($item, $tables)) {
$schema->dropTable($item);
}
}
} elseif ($db instanceof Mongo) {
/** @var Mongo $db */
foreach ($deletable as $item) {
$db->selectCollection($item)->drop();
}
}
}
/**
* get all table names from a DB
* @param string $database
* @return array|bool
*/
public function getTables($database = 'PF')
{
$schema = new SQL\Schema($this->getDB($database));
return $schema->getTables();
}
