$pageLocation = '1';
$requestedAction = 'users';
$wo = new WOOOF();
if (!$wo->constructedOk) {
$wo->handleShowStopperError("1000 Failed to init WOOOF.");
}
$database = $wo->db->getDatabaseName();
$dbString = "{$database}@" . $wo->getConfigurationFor('databaseHost')[$wo->getConfigurationFor('defaultDBIndex')];
echo "<h1>Create users</h1>";
echo "<h2>Db: {$dbString}</h2>";
// array( array( 0: loginName, 1: password, 2:string[]|string (of role names) 3: id (may be '' ) 4: checkPassword (default true) ), ... )
// The following is an example. Edit as desired.
// PLEASE, SET THE FOLLOWING
$newUsers = array(array('sysJohnL', '12345678A', array('Normal User', 'System Operator')), array('sysApapanto', '12345678A', array('Normal User', 'System Operator')));
$newUsers = array();
// COMMENT AFTER CHANGING $newUsers above
$commitEach = false;
// set to true to save users one by one. set to false to save them all or none!
$succ = WOOOF_User::createMultipleUsers($wo, $newUsers, $newUserIds, $commitEach);
//var_dump($succ, $newUsers, $newUserIds);
echo "<h2>Given Users</h2>";
echo WOOOF_Util::do_dump($newUsers);
echo "<h2>Created Users</h2>";
echo WOOOF_Util::do_dump($newUserIds);
if ($succ === TRUE) {
$wo->db->commit();
echo "<h2>Ok</h2>";
} else {
$wo->db->rollback();
echo "<h2>Failed</h2>";
}
/**
*
* @param string $rowId
* @return boolean
*/
public function deleteRow($rowId, $callLevel = 1)
{
// CAUTION: infinite recursion is possible!
// Static properties:
// $deleteRowFilesToRemove; // array( id =>isImage, ... )
// $deleteRowRowIds; // array( table => array( rowId, ... ), ... )
$wo = WOOOF::$instance;
$wo->debug("deleteRow: {$this->tableName} [{$rowId}] [{$callLevel}]");
if ($callLevel == 1) {
$this::$deleteRowFilesToRemove = array();
$this::$deleteRowRowIds = array();
}
if (isset($this::$deleteRowRowIds[$this->tableName][$rowId])) {
$wo->logError(self::_ECP . "0530 deleteRow: Detected cycle on {$this->tableName}.{$rowId}");
return true;
// break the cycle
}
// add to memory
$this::$deleteRowRowIds[$this->tableName] = $rowId;
$theRow = $this->getRow($rowId);
if ($theRow === FALSE) {
return FALSE;
}
if ($theRow === NULL) {
$wo->logError(self::_ECP . "0540 Delete row [{$rowId}] not found");
return false;
}
if ($this->hasDeletedColumn) {
$res = $this->dataBase->query('update ' . $this->tableName . ' set isDeleted=\'1\' where id=\'' . WOOOF::$instance->cleanUserInput($rowId) . '\'');
if ($res === FALSE) {
return FALSE;
}
} else {
if ($this->hasGhostTable) {
//TODO: ghost table stuf goes here
}
// antonis
// Handle possible File (externalFiles entries) and Picture fields
// Remove any relevant external files as well.
for ($i = 0; $i < count($this->columns) / 2; $i++) {
$metadata = $this->columns[$i]->getColumnMetaData();
if ($metadata['presentationType'] == WOOOF_columnPresentationTypes::picture or $metadata['presentationType'] == WOOOF_columnPresentationTypes::file) {
WOOOF_Util::do_dump($metadata['name']);
// add to memory
$this::$deleteRowFilesToRemove[] = array($theRow[$metadata['name']], $metadata['presentationType'] == WOOOF_columnPresentationTypes::picture);
}
// file or image field
}
// for all columns
$res = $this->dataBase->query('delete from ' . $this->tableName . ' where id=\'' . WOOOF::$instance->cleanUserInput($rowId) . '\'');
if ($res === FALSE) {
return FALSE;
}
}
// antonis
// Handle possible Detail records
//
// CAUTION: tablesGroupedByThis should be the space separated list of all tables with groupedByTable == this.table
// CAUTION: this is manually done in dbManager
//
if ($wo->hasContent($this->tablesGroupedByThis)) {
$subTableNames = explode(' ', $this->tablesGroupedByThis);
foreach ($subTableNames as $aSubTableName) {
$subTable = new WOOOF_dataBaseTable($wo->db, $aSubTableName);
if (!$subTable->constructedOk) {
return FALSE;
}
if ($subTable->groupedByTable != $this->tableName) {
$wo->logError(self::_ECP . "0550 SubTable name mismatch!");
return FALSE;
}
$res = $subTable->getResult(array($subTable->localGroupColumn => $rowId), null, null, null, null, true, false);
if ($res === FALSE) {
return FALSE;
}
foreach ($subTable->resultRows as $aSubTableRow) {
$succ = $subTable->deleteRow($aSubTableRow['id'], $callLevel + 1);
if ($succ === FALSE) {
return FALSE;
}
}
}
// foreach subTable
}
// there exist sub tables
if (isset($this->resultRows[$rowId])) {
foreach ($this->resultRows as $key => $value) {
if ($value['id'] == $rowId) {
unset($this->resultRows[$key]);
}
}
}
if ($callLevel == 1) {
$wo->debug("deleteRow: Completed for all cases.");
foreach ($this::$deleteRowFilesToRemove as $aFileData) {
$succ = WOOOF_ExternalFiles::deleteExternalFile($wo, $aFileData[0], $aFileData[1]);
// ???ignore false results (like failing to delete row from externaal file.
}
//debug
//echo WOOOF_Util::do_dump($this::$deleteRowFilesToRemove);
//echo WOOOF_Util::do_dump($this::$deleteRowRowIds);
}
return TRUE;
}
/**
*
* @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;
}
public function showMetaData($in)
{
$wo = $this->wo;
$tableName = $this->tableName;
$out = '';
if (!WOOOF::hasContent($tableName)) {
$wo->log(WOOOF_loggingLevels::WOOOF_ERROR, "1000 No value for tableName");
return false;
}
$out .= "<h1>Meta Data for [{$tableName}]</h1>";
$sql = "\n\t\t\tselect * from __tableMetaData\n\t\t\twhere tableName = '{$tableName}'\n";
$res = $wo->db->query($sql);
if ($res === FALSE) {
return FALSE;
}
$aRow = $wo->fetchAssoc($res);
if ($aRow === NULL) {
$out .= "No metadata found for [{$tableName}] in __tableMetaData";
return $out;
}
$out .= "<h2>Table meta data</h2>";
$out .= WOOOF_Util::do_dump($aRow);
$sql = "\n\t\t\tselect * from __v_columnMetaData\n\t\t\twhere T_name = '{$tableName}'\n\t\t\torder by C_ordering, C_name\n";
$res = $wo->db->query($sql);
if ($res === FALSE) {
return FALSE;
}
$out .= "<h2>Column(s) meta data</h2>";
while (($aRow = $wo->fetchAssoc($res)) !== NULL) {
$out .= WOOOF_Util::do_dump($aRow);
}
return $out;
}
