$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; }