public static function getExternalFileIdThroughAddress(WOOOF $wo, $tableId, $columnId, $rowId) { // 0300 if (!$wo->hasContent($tableId)) { $wo->logError(self::_ECP . "0310 No value defined for 'tableId'"); return false; } if (!$wo->hasContent($columnId)) { $wo->logError(self::_ECP . "0312 No value defined for 'columnId'"); return false; } if (!$wo->hasContent($rowId)) { $wo->logError(self::_ECP . "0314 No value defined for 'rowId'"); return false; } $tableId = trim($wo->db->escape($tableId)); $columnId = trim($wo->db->escape($columnId)); $rowId = trim($wo->db->escape($rowId)); $sql = "\n\t \tselect\n\t \t\ttmd.tableName, cmd.name columnName\n\t \tfrom\n\t \t\t__tableMetaData tmd, __columnMetaData cmd\n\t \twhere\n\t \t\ttmd.id = '{$tableId}' and\n\t \t\tcmd.id = '{$columnId}' and\n\t \t\tcmd.tableId = tmd.id\n\t\t"; $res1 = $wo->db->query($sql); if ($res1 === FALSE) { return FALSE; } if (($data1 = $wo->fetchAssoc($res1)) === NULL) { $wo->logError(self::_ECP . "0320 Bad tableId [{$tableId}] and columnId [{$columnId}] combination."); return false; } // TODO: Should check that column is a FILE column ??? $sql = "\n\t \tselect\n\t \t\tef.*\n\t \tfrom\n\t \t\t`{$data1['tableName']}` t, __externalFiles ef\n\t \twhere\n\t \t\tt.id = '{$rowId}' and\n\t \t\tef.id = t.{$data1['columnName']}\n\t\t"; $res2 = $wo->db->query($sql); if ($res2 === FALSE) { return FALSE; } if (($fileData = $wo->fetchAssoc($res2)) === NULL) { $wo->logError(self::_ECP . "0330 Bad tableId [{$tableId}] columnId [{$columnId}] and rowId [{$rowId}] combination."); return false; } /* id char(10) entryDate varchar(255) fileName char(40) originalFileName varchar(255) */ return $fileData; }
/** * * @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; }