/**
*
* @param WOOOF $wo
* @param string $p_database
* @param string $p_objectName
* @param string $action // Optional, default 'refresh'. Other values: 'show', 'indexes'.
* @param string $p_objectNameFrom // Optional, default ''. Different name to look in the db.
* @return boolean
*/
public static function reverseEngineerObject(WOOOF $wo, $p_database, $p_objectName, $action = 'refresh', $p_objectNameFrom = '')
{
// $action: refresh, show, indexes, ascii
if (!in_array($action, array('refresh', 'show', 'indexes', 'ascii'))) {
$wo->logError(self::_ECP . "7010 Bad action param value [{$action}]");
return false;
}
//$sql = "DESCRIBE ".$p_database.".".$p_objectName." ";
//$p_objectName = 'v_applicationfk'; // just testing...
echo '<h4>/*' . __CLASS__ . '.' . __FUNCTION__ . ': ' . $p_objectName . ' */</h4>';
if (!$wo->hasContent($p_objectNameFrom)) {
$p_objectNameFrom = $p_objectName;
}
$sql = "\n\t\tSELECT *\n\t\tFROM INFORMATION_SCHEMA.TABLES\n\t\tWHERE table_schema = '{$p_database}'\n\t\tAND table_name = '{$p_objectNameFrom}'\n\t\t";
if (!($objectResult = $wo->db->query($sql))) {
return false;
}
if ($action != 'indexes' && $action != 'ascii') {
echo "<h5>{$p_objectNameFrom}</h5>";
}
$objectInfo = $wo->fetchAssoc($objectResult);
if ($objectInfo === NULL) {
$wo->logError(self::_ECP . "7100 [{$p_objectNameFrom}] does not exist in Database [{$p_database}]!!!");
return FALSE;
}
if ($action == 'ascii') {
echo "SET FOREIGN_KEY_CHECKS=0;<br>";
}
$objColNames = array();
/*
'TABLE_CATALOG' => string 'def' (length=3)
'TABLE_SCHEMA' => string 'ait_mba' (length=7)
'TABLE_NAME' => string '__bannedips' (length=11)
'TABLE_TYPE' => string 'BASE TABLE' (length=10) or VIEW
'ENGINE' => string 'MyISAM' (length=6)
'VERSION' => string '10' (length=2)
'ROW_FORMAT' => string 'Fixed' (length=5)
'TABLE_ROWS' => string '0' (length=1)
'AVG_ROW_LENGTH' => string '0' (length=1)
'DATA_LENGTH' => string '0' (length=1)
'MAX_DATA_LENGTH' => string '24769797950537727' (length=17)
'INDEX_LENGTH' => string '1024' (length=4)
'DATA_FREE' => string '0' (length=1)
'AUTO_INCREMENT' => null
'CREATE_TIME' => string '2015-06-01 17:46:40' (length=19)
'UPDATE_TIME' => string '2015-06-01 17:46:40' (length=19)
'CHECK_TIME' => null
'TABLE_COLLATION' => string 'utf8_general_ci' (length=15)
'CHECKSUM' => null
'CREATE_OPTIONS' => string '' (length=0)
'TABLE_COMMENT' => string '' (length=0)
*/
if ($action != 'indexes' && $action != 'ascii') {
echo '<b>' . $objectInfo['TABLE_TYPE'] . '</b>';
}
$isView = false;
$viewDefinition = '';
if ($objectInfo['TABLE_TYPE'] == 'VIEW') {
$isView = true;
}
if ($isView) {
$sql = "\n\t\t \tSELECT *\n\t\t \tFROM INFORMATION_SCHEMA.VIEWS\n\t\t \tWHERE table_schema = '{$p_database}'\n\t\t \tAND table_name = '{$p_objectNameFrom}'\n\t\t ";
if (($viewResult = $wo->db->query($sql)) === FALSE) {
return FALSE;
}
$objectInfo = $wo->fetchAssoc($viewResult);
/*
'TABLE_CATALOG' => string 'def' (length=3)
'TABLE_SCHEMA' => string 'ait_mba' (length=7)
'TABLE_NAME' => string 'v_applicationfk' (length=15)
'VIEW_DEFINITION' => string 'select `a`.`id` AS `id`,`a`.`isDeleted` AS `isDeleted`,`a`.`statusId` AS `statusId`,`a`.`submittedDate` AS `submittedDate`,`a`.`appTypeId` AS `appTypeId`,`a`.`amount` AS `amount`,`a`.`studentOrganisation` AS `studentOrganisation`,`a`.`companyName` AS `companyName`,`a`.`noOfApplicants` AS `noOfApplicants`,`a`.`invoiceName` AS `invoiceName`,`a`.`invoiceVAT` AS `invoiceVAT`,`a`.`invoiceProfesssion` AS `invoiceProfesssion`,`a`.`isIssueInvoice` AS `isIssueInvoice`,`a`.`invoiceDOY` AS `invoiceDOY`,`a`.`invoiceAdd'... (length=1164)
'CHECK_OPTION' => string 'NONE' (length=4)
'IS_UPDATABLE' => string 'YES' (length=3)
'DEFINER' => string 'root@localhost' (length=14)
'SECURITY_TYPE' => string 'DEFINER' (length=7)
'CHARACTER_SET_CLIENT' => string 'utf8' (length=4)
'COLLATION_CONNECTION' => string 'utf8_general_ci' (length=15)
*/
$viewDefinition = $objectInfo['VIEW_DEFINITION'];
// CAUTION: from tables are prefixed with current schema. Non-portable.
// So:
$viewResult = $wo->db->query("show create view `{$p_objectName}`");
if ($viewResult === FALSE) {
return FALSE;
}
$viewDefinition = $wo->fetchAssoc($viewResult)['Create View'];
$viewDefinition = substr($viewDefinition, strpos($viewDefinition, "` AS select `") + 5);
} else {
$viewDefinition = $objectInfo['VIEW_DEFINITION'] = '';
}
if ($action == 'show') {
echo WOOOF_Util::do_dump($objectInfo);
}
$mdTableColsExistArray = self::getMDTColsExist($wo, $p_database);
if ($mdTableColsExistArray === FALSE) {
return FALSE;
}
$mdColumnColsExistArray = self::getMDCColsExist($wo, $p_database);
if ($mdColumnColsExistArray === FALSE) {
return FALSE;
}
$sql = "\n\t\t\tselect id\n\t\t from __tableMetaData\n\t\t where tablename = '{$p_objectName}'\n\t\t";
$objId = $wo->db->getSingleValueResult($sql);
if ($objId === FALSE) {
return FALSE;
}
/*
id char(10)
tableName varchar(255)
description varchar(255)
subtableDescription varchar(255)
orderingColumnForListings
*/
$objExisted = true;
if ($objId === NULL) {
$objExisted = false;
if ($action != 'indexes' && $action != 'ascii') {
echo '<br>Object is new!<br>';
}
if ($action == 'refresh') {
echo 'Will add it.<br>';
$objId = $wo->db->getNewId('__tableMetaData');
$description = WOOOF_Util::fromCamelCase($p_objectName);
$sql = "\n\t\t \t\tinsert into __tableMetaData \n\t\t \t\tset id = '{$objId}', \n\t\t \t\ttableName = '{$p_objectName}', \n\t\t \t\tdescription = '{$description}',\n\t\t \t\tappearsInAdminMenu = '1'\n\t\t \t";
if ($mdTableColsExistArray['isView']) {
$sql .= ", isView = '" . ($isView ? '1' : 0) . "'";
}
if ($mdTableColsExistArray['viewDefinition']) {
$sql .= ", viewDefinition = '" . $wo->db->escape($viewDefinition) . "'";
}
if (!$isView && $mdTableColsExistArray['dbEngine']) {
$sql .= ", dbEngine = '" . $wo->db->escape($objectInfo['ENGINE']) . "'";
}
//echo $sql.'<br>';
$res = $wo->db->query($sql);
if ($res === FALSE) {
return FALSE;
}
//$cmdTable = new WOOOF_dataBaseTable($wo->db, '__tableMetaData');
//$objId = $cmdTable->insertFromArray(
// array( 'tableName' => $p_objectName )
//);
//if ( $objId === FALSE ) { return FALSE; }
}
} else {
// object exists
if ($action != 'indexes' && $action != 'ascii') {
echo '<br>Object metaData exist.<br>';
}
if ($action == 'refresh') {
echo "Will refresh object's metaData.";
$sql = "update __tableMetaData set ";
$smgToUpdate = false;
if ($mdTableColsExistArray['isView']) {
$smgToUpdate = true;
$sql .= "isView = " . ($isView ? '1' : 0) . "\t";
if ($isView) {
$sql .= ", \tviewDefinition = '" . $wo->db->escape($viewDefinition) . "'\n\t\t\t \t\t";
}
}
// isView column exists in metaData
if (!$isView && $mdTableColsExistArray['dbEngine']) {
$sql .= ", dbEngine = '" . $wo->db->escape($objectInfo['ENGINE']) . "'";
$smgToUpdate = true;
}
if ($smgToUpdate) {
$sql .= " where id = '{$objId}'";
//echo '<br>' . $sql.'<br>';
$res = $wo->db->query($sql);
if ($res === FALSE) {
return FALSE;
}
}
}
// refresh
}
// object existed or is new
if ($action != 'indexes' && $action != 'ascii') {
echo '<h4>Columns</h4>';
}
$sql = "\n\t\t\tSELECT \n\t\t\t\tCOLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, \n\t\t\t\tCHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, \n\t\t\t\tCOLUMN_DEFAULT, COLLATION_NAME\n\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\t\tWHERE table_schema = '{$p_database}'\n\t\t\tAND table_name = '{$p_objectNameFrom}'\n\t ";
if (!($columnsResult = $wo->db->query($sql))) {
return false;
}
$ordering = 0;
while ($columnInfo = $wo->fetchAssoc($columnsResult)) {
$ordering += 10;
/*
INFORMATION_SCHEMA Name SHOW Name Remarks
TABLE_CATALOG def
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME Field
ORDINAL_POSITION see notes
COLUMN_DEFAULT Default
IS_NULLABLE Null
DATA_TYPE Type
CHARACTER_MAXIMUM_LENGTH Type
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION Type
NUMERIC_SCALE Type
CHARACTER_SET_NAME
COLLATION_NAME Collation
COLUMN_TYPE Type MySQL extension
COLUMN_KEY Key MySQL extension
EXTRA Extra MySQL extension
PRIVILEGES Privileges MySQL extension
COLUMN_COMMENT Comment MySQL extension
*/
$colName = $columnInfo['COLUMN_NAME'];
$colType = $columnInfo['DATA_TYPE'];
$colTypeWOOOF = WOOOF_dataBaseColumnTypes::getColumnTypeReverse($colType);
if ($colTypeWOOOF === FALSE) {
if ($action == 'refresh') {
$wo->logError(self::_ECP . "0066 Column type [{$colType}] is non-WOOOF compatible.");
return FALSE;
}
}
$objColNames[] = $colName;
if ($action == 'show') {
echo WOOOF_Util::do_dump($columnInfo);
continue;
}
if ($action == 'indexes' && !$isView) {
if ($colName != 'id' and strtolower(substr($colName, -2)) == 'id') {
echo "alter table {$p_objectName} add index `{$colName}` (`{$colName}`);" . '<br>';
}
}
if ($action == 'ascii' && !$isView) {
if ($colType == 'char' && $columnInfo['COLLATION_NAME'] != 'ascii_bin') {
echo "alter table {$p_objectName} modify column `{$colName}` " . $colType . '(' . $columnInfo['CHARACTER_MAXIMUM_LENGTH'] . ') ' . " COLLATE ascii_bin " . ($columnInfo['IS_NULLABLE'] == 'NO' ? 'NOT NULL' : 'NULL') . ';' . '<br>';
}
}
$colExisted = false;
$colId = null;
if ($objExisted) {
$sql = "\n\t\t\t\t\tselect id\n\t\t\t \tfrom __columnMetaData\n\t\t\t \twhere tableId = '{$objId}' and name = '{$colName}'\n\t\t\t\t";
$colId = $wo->db->getSingleValueResult($sql);
if ($colId === FALSE) {
return FALSE;
}
$colExisted = $colId !== NULL;
}
if ($action == 'refresh') {
$length = in_array($colType, array('char', 'varchar')) ? $columnInfo['CHARACTER_MAXIMUM_LENGTH'] : $columnInfo['NUMERIC_PRECISION'];
if (is_numeric($columnInfo['NUMERIC_SCALE']) && $columnInfo['NUMERIC_SCALE'] != '0') {
$length .= ',' . $columnInfo['NUMERIC_SCALE'];
}
$noNull = $columnInfo['IS_NULLABLE'] == 'YES' ? 0 : 1;
$defaultValue = $columnInfo['COLUMN_DEFAULT'];
$collation = $columnInfo['COLLATION_NAME'];
if (!$colExisted) {
$colId = $wo->db->getNewId('__columnMetaData');
$presentationType = WOOOF_columnPresentationTypes::textBox;
if (strtolower(substr($colName, -4)) == 'date' or strtolower(substr($colName, -4)) == 'time') {
$presentationType = WOOOF_columnPresentationTypes::dateAndTime;
} elseif (in_array(strtoupper($colType), array('TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT'))) {
$presentationType = WOOOF_columnPresentationTypes::textArea;
}
$colDescr = WOOOF_Util::fromCamelCase($colName);
/*
$cmdTable = new WOOOF_dataBaseTable($wo->db, '__columnMetaData');
$colId = $cmdTable->insertFromArray(
array(
'tableId' => $objId,
'name' => $columnInfo['COLUMN_NAME'],
'type' => $columnInfo['DATA_TYPE'],
'length' => $length,
'noNull' => $noNull,
'defaultValue' => $defaultValue,
)
);
if ( $colId === FALSE ) { return FALSE; }
*/
$sql = "\n\t\t \t\t\tinsert into __columnMetaData\n\t\t \t\t\tset\n\t\t \t\t\tid = '{$colId}',\n\t\t \t\t\ttableId = '{$objId}',\n\t\t \t\t\tname = '{$colName}',\n\t\t \t\t\tdescription = '{$colDescr}',\n\t\t \t\t\ttype = '{$colTypeWOOOF}',\n\t\t \t\t\tlength = '{$length}',\n\t\t \t\t\tnotNull = {$noNull},\n\t\t \t\t\tdefaultValue = '{$defaultValue}',\n\t\t \t\t\tappearsInLists = '1',\n\t\t \t\t\tisInvisible = '0',\n\t\t \t\t\tordering = {$ordering},\n\t\t \t\t\tpresentationType = '{$presentationType}'\n\t\t \t\t";
if ($mdColumnColsExistArray['colCollation']) {
$sql .= ", colCollation = '{$collation}' ";
}
} else {
// existed
$sql = "\n\t\t \t\t\tupdate __columnMetaData \n\t\t \t\t\tset\n\t\t \t\t\t\ttype = '{$colTypeWOOOF}',\n\t\t \t\t\t\tlength = '{$length}',\n\t\t \t\t\t\tnotNull = {$noNull},\n\t\t \t\t\t\tdefaultValue = '{$defaultValue}'\n\t\t \t\t";
if ($mdColumnColsExistArray['colCollation']) {
$sql .= ", colCollation = '{$collation}' ";
}
$sql .= "\n\t\t \t\t\twhere id = '{$colId}'\n\t\t\t \t";
}
// col existed or not
//echo $sql.'<br>';
$res = $wo->db->query($sql);
if ($res === FALSE) {
return FALSE;
}
}
// refresh
/*
id char(10)
tableId char(10)
name varchar(255)
description varchar(255)
type varchar(255)
length varchar(255) NULL
presentationType int(10) [1]
isReadOnly char(1) [0]
notNull
*/
}
// foreach column of object
if ($objExisted) {
if ($action == 'refresh') {
$sql = "select id, name from __columnMetaData where tableId='{$objId}'";
if (($res = $wo->db->query($sql)) === FALSE) {
return FALSE;
}
while (($aMDCol = $wo->fetchAssoc($res)) != NULL) {
if (!in_array($aMDCol['name'], $objColNames)) {
echo "need to drop column {$aMDCol['name']} from metadata<br>";
$sql = "delete from __columnMetaData where id='{$aMDCol['id']}'";
//echo $sql . '<br>';
if ($wo->db->query($sql) === FALSE) {
return FALSE;
}
}
}
// foreach dropped column
}
// refresh
}
// objExisted
if ($action == 'ascii') {
echo "SET FOREIGN_KEY_CHECKS=1;<br>";
}
return true;
}
