array( 'method' => 'POST', 'header' => 'Content-type: application/x-www-form-urlencoded', 'content' => $postData )))); } function IsLoggedIn() { global $accessKey, $TDConfig; if(Session($accessKey) == "granted") return true; if(!ConfigFileExists()) { if(Session($accessKey) == "installing") return true; // you are currently setting up, carry on! if(!file_exists(dirname(__FILE__) . '/trueDAT4InstallInProgress')) { $_SESSION[$accessKey] = "installing"; // you're first in, welcome! touch(dirname(__FILE__) . '/trueDAT4InstallInProgress'); // make our lock return true; } return false; // bugger off: install is in progress, and it's not with you! } if($TDConfig['authentication']['authMode'] == 'skip') { $_SESSION[$accessKey] = "granted"; return true; } if($TDConfig['authentication']['authMode'] == 'session') { eval("\$result = " . $TDConfig['authentication']['sessionExpression'] . ";"); // an expression like $_SESSION['someVariable'] == someValue if($result) $_SESSION[$accessKey] = "granted"; return $result; } return false; } function DisplayTrueDAT4() { global $trueDATBaseURL; $configExists = ConfigFileExists(); // if(!$configExists) { $trueDATBaseURL = TRUEDAT4_BASEURL; } // starter default! $isLoggedIn = IsLoggedIn(); ?> trueDAT4 by JPL Consulting
Loading...
version

You must login via the host application in order to access trueDAT.

Come on in.

Username
Password

This feature is not yet built.

For now you can manaully edit trueDAT4Config.php if you need to change settings.

A configuration of this installation is currently in progress. Please try back later.

If you're the one trying to configure this installation, you can delete the file named trueDAT4InstallInProgress in this directory to start again.

Looks like you're just getting started with trueDAT.

To set up, all we need do to is establish the database you'll be connecting to and how you want to authorize users to use this installation.

First, the database connection:

Database Type:
Database Host:
Username:
Password:
Database Name:
 
"/define\('DB_HOST',$w'([^']+)'\);/", 'username' => "/define\('DB_USER',$w'([^']+)'\);/", 'password' => "/define\('DB_PASSWORD',$w'([^']+)'\);/", 'schema' => "/define\('DB_NAME',$w'([^']+)'\);/", ); break; case 'Drupal': // db_url = 'mysqli://LocalDevUser:ABC123@localhost/drupal_play'; $theFile = 'sites/default/settings.php'; $regExpSet = array( 'host' => "/\n\\\$db_url = '.+@($s)\//", 'username' => "/\n\\\$db_url = '.+\/\/($s):/", 'password' => "/\n\\\$db_url = '.+:([^\)]+)@/", 'schema' => "/\n\\\$db_url = '.+\/($s)';/", ); break; case 'Joomla': $theFile = 'configuration.php'; $regExpSet = array( 'host' => "/public \\\$host = '($s)'/", 'username' => "/public \\\$user = '($s)'/", 'password' => "/public \\\$password = '([^']+)'/", 'schema' => "/public \\\$db = '($s)'/", ); break; case 'Magento': $theFile = 'app/etc/local.xml'; $regExpSet = array( 'host' => "/<\/host>/", 'username' => "/<\/username>/", 'password' => "/<\/password>/", 'schema' => "/<\/dbname>/", ); break; case 'JPL': // this is how I roll $theFile = 'includes/universalJPLPackageSettings.php'; $regExpSet = array( 'host' => "/define\('DB_HOST',$w'([^']+)'\);/", 'username' => "/define\('DB_USERNAME',$w'([^']+)'\);/", 'password' => "/define\('DB_PASSWORD',$w'([^']+)'\);/", 'schema' => "/define\('DB_NAME',$w'([^']+)'\);/", ); break; } return $theFile ? FindDBConfigSettings($theFile, $regExpSet) : false; } function FindDBConfigSettings($theFile, $regExpSet) { $fileText = FindFileText($theFile); if(!$fileText) return false; $resultSet = array(); foreach($regExpSet as $field => $pattern) { if(preg_match($pattern, $fileText, $matchSet)) $resultSet[$field] = $matchSet[1]; } return (count($resultSet) > 0 ? $resultSet : false); // return if we got any matches... better than nothing! } function FindFileText($theFile) { $levelsUp = 0; for($levelsUp = 0; $levelsUp < 5; $levelsUp++) { $fileTarget = dirname(__FILE__) . str_repeat("/..", $levelsUp) . "/$theFile"; if(file_exists($fileTarget)) return GetFileText($fileTarget); } return false; // couldn't find up our directory tree, bummer! } function ProcessFirstConfig1() { global $TDConfig; $TDConfig = BuildTDConfigFromFirstConfigRequest(); $connData = $TDConfig['connections'][GetCurrentDBConnection()]; if($connData['host'] == '') { echo "Please indicate the database host."; } elseif($connData['username'] == '') { echo "Please indicate the username."; } elseif($connData['password'] == '') { echo "Please indicate the password."; } elseif($connData['schema'] == '') { echo "Please indicate the database name."; } elseif(OpenTDDBConnection(false)) { if(GetSQLValueTD("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=" . SQLValue($connData['schema'])) == false) echo "The database name you indicated does not exist."; else echo "ok"; } else { echo "

Connecting to your database using the parameters provided failed. Please try again!

"; } } function ProcessFirstConfig2() { switch(Request('authMode')) { case "localUNPW": if(Request('username') == '') echo "Please enter a username."; elseif(Request('password') == '') echo "Please enter a password."; else echo "ok"; break; case "session": if(Request('sessionName') == '') echo "Please enter the name of the session variable to base authentication on."; elseif(Request('sessionValue') == '') echo "Please enter the value for session-based authentication."; else echo "ok"; break; case "skip": echo "ok"; break; case "remoteUNPW": $targetURL = Request('authBaseURL'); $result = @file_get_contents($targetURL); if($result === false) echo "Our HTTP request to $targetURL seems to have failed, " . "could not reach that script for authentication."; else echo "ok"; break; default: echo "Please chooose an authentication method."; } } function ProcessFirstConfig3() { $TDConfig = BuildTDConfigFromFirstConfigRequest(); // First ensure (crudely) that the indicated base is going to work: $baseURL = $TDConfig['options']['baseURL']; if(@file_get_contents(HTTPifyURL($baseURL) . "scripts/trueDAT4.js") === false) { ?>

Problem.

The resource base URL you indicated,

doesn't seem to have what we need.

" target="_blank">trueDAT4.js can't be found where it should be relative to that URL.

Either make sure you've installed trueDAT's supporting files at that URL (such that the above link works for you), or indicate another URL.

Small snag.

It looks like your server settings are such that we cannot write the configuration file out to the directory, so we need you to do it.

Take the following code, paste it into a new file. Save it as trueDAT4Config.php, and then upload it to the same directory.



 

Success

Congratulations, trueDAT is all configured and ready to go.

Still not there.

Try again!

Success

Congratulations, trueDAT is all configured and ready to go.

Request('db_type'), 'host' => Request('db_host'), 'username' => Request('db_username'), 'password' => Request('db_password'), 'schema' => Request('db_schema'), ); } $TDConfig = array( 'connections' => array( // multiple connections configurable, but just one for now $connection ), 'authentication' => array( 'authMode' => Request('authMode'), 'username' => Request('username'), 'passwordHash' => sha1(Request('password')), 'sessionExpression' => "Session('" . Request('sessionName') . "') " . Request('sessionCompare') . " " . ProperPHPLiteral(Request('sessionValue')), 'authBaseURL' => EnsureEndsWith(Request('authBaseURL'), '/'), ), 'options' => array( 'currentConnection' => 0, 'baseURL' => EnsureEndsWith(Request('baseURL'), '/'), 'suggestItems' => "tables&SPs&columns", 'statementDelimiter' => "\nGO\n", 'timeElapsedDisplayThreshold' => 1, 'enableForeignKeySurfing' => false, ), ); return $TDConfig; } function ProperPHPLiteral($valueString) { if(ProperNumber($valueString, 'nan') !== 'nan') return $valueString; // is a number, no problem. if(in_array(strtolower($valueString), array('true', 'false'))) return $valueString; // is a boolean, no problem return "\"". str_replace(array("\\", '"'), array("\\\\", '\"'), $valueString) . "\""; } function printArrayAsPhpCode($array, $depth = 1) { if(count($array) == 0) return "array()"; // empty, super simple! $hasKeys = !(array_values($array) === $array); $result = "array(" . ($hasKeys ? "\n" : ''); foreach ($array as $key => $value) { if(is_int($value) || is_float($value)) { $phpValue = $value; } elseif(is_null($value)) { $phpValue = 'null'; } elseif(is_array($value)) { $phpValue = printArrayAsPhpCode($value, $depth+1); } elseif(is_string($value)) { $phpValue = "\"" . str_replace(array("\\", '"', "\n"), array("\\\\", '\"', '\n'), $value) . "\""; } elseif(is_bool($value)) { $phpValue = ($value ? 'true' : 'false'); } else { trigger_error("Unsupported type of \$value, in index $key. gettype() =" . gettype($value)); } $result .= $hasKeys ? str_repeat("\t", $depth) . "\"$key\" => $phpValue,\n" : "$phpValue, "; } $result = substr($result, 0, strlen($result) - 2); // Remove last comma. $result .= ($hasKeys ? "\n" . str_repeat("\t", $depth-1) : '') . ")"; // close out the array return $result; } function DisplayApp() { global $trueDATBaseURL; ?>
    DESC
  • Tab 1
    Truncate to chars.  
dismiss

Welcome to trueDAT4

IDnamesortOrderisActiveisAwesome
1Mr. TAlways #1, yo.TrueTrue
2John2TrueTrue
4Lee3TrueTrue
5Rob4TrueTrue
3Tom5FalseTrue
$connData['username'], 'PWD' =>$connData['password'], 'Database' => $connData['schema']); $connectionInfo = array('Database' => $connData['schema']); $TDDB_connection = sqlsrv_connect($connData['host'], $connectionInfo); if(!$TDDB_connection) { if($dieOnFail) { var_dump($connData); echo('Connection to MSSQL at ' . $connData['host'] . ' failed!'); die( print_r( sqlsrv_errors(), true)); } else return false; } break; case "MySQL": $TDDB_connection = mysqli_connect($connData['host'], $connData['username'], $connData['password'], $connData['schema']); if(!$TDDB_connection) { if($dieOnFail) die('Connection to MySQL at ' . $connData['host'] . ' failed!'); else return false; } mysqli_query($TDDB_connection, "SET sql_mode='NO_BACKSLASH_ESCAPES'"); // to avoid \' shenanigans break; default: // unsupported DB type! return false; } $TDDB_connectionIsOpen = true; return true; } function CloseTDDBConnection() { global $TDDB_connectionIsOpen, $TDDB_connection; if(!$TDDB_connectionIsOpen) return; switch(GetCurrentDBType()) { case "MySQL": mysqli_close($TDDB_connection); break; case "MSSQL": break; // later. } $TDDB_connectionIsOpen = false; } function ExecuteSQLTD2($SQL, $dieOnFail = true) { echo "$SQL"; return ExecuteSQLTD($SQL, $dieOnFail); } function ExecuteSQLTD($SQL, $dieOnFail = true) { OpenTDDBConnection(); global $TDDB_connection; switch(GetCurrentDBType()) { case "MySQL": $xRS = mysqli_query($TDDB_connection, $SQL); if(!$xRS && $dieOnFail) { die("
Invalid SQL:
$SQL

" . mysqli_error($TDDB_connection)); } break; case "MSSQL": $xRS = sqlsrv_query($TDDB_connection, $SQL, array(), array("Scrollable" => 'static')); if(!$xRS && $dieOnFail) { die("
Invalid SQL:
$SQL

" . mysqli_error($TDDB_connection)); } break; } return $xRS; } function rs_num_rows($xRS) { switch(GetCurrentDBType()) { case "MySQL": return is_bool($xRS) ? 0 : mysqli_num_rows($xRS); break; case "MSSQL": return sqlsrv_num_rows($xRS); break; } } function rs_num_fields($xRS) { switch(GetCurrentDBType()) { case "MySQL": return is_bool($xRS) ? 0 : mysqli_num_fields($xRS); break; case "MSSQL": return sqlsrv_num_fields($xRS); break; } } function rs_get_field_names($xRS) { $resultSet = array(); switch(GetCurrentDBType()) { case "MySQL": $fieldCount = mysqli_num_fields($xRS); for($fLoop = 0; $fLoop < $fieldCount; $fLoop++) { $theField = mysqli_fetch_field_direct($xRS, $fLoop); $resultSet[] = $theField->name; } break; case "MSSQL": $fieldSet = sqlsrv_field_metadata($xRS); foreach($fieldSet as $field) { // var_dump($field); $resultSet[] = $field['Name']; } break; } // var_dump($resultSet); return $resultSet; } function rs_fetch_array($xRS) { switch(GetCurrentDBType()) { case "MySQL": return mysqli_fetch_array($xRS); break; case "MSSQL": return sqlsrv_fetch_array($xRS, SQLSRV_FETCH_BOTH); break; } } function rs_fetch_fields($xRS) { $result = array(); switch(GetCurrentDBType()) { case "MySQL": $fieldCount = mysqli_num_fields($xRS); for($fLoop = 0; $fLoop < $fieldCount; $fLoop++) { $result[] = mysqli_fetch_field_direct($xRS, $fLoop); } break; case "MSSQL": $result = sqlsrv_field_metadata($xRS); break; } return $result; } function GetTableColumnRS($tableName, $extraWhere = '1=1') { switch(GetCurrentDBType()) { case "MySQL": return ExecuteSQLTD("SHOW COLUMNS FROM $tableName"); break; case "MSSQL": return ExecuteSQLTD( "SELECT syscolumns.name AS column_name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'U' AND $extraWhere AND sysobjects.name=" . SQLValue($tableName) . " ORDER BY syscolumns.colid"); break; } } function GetRSFieldSet($xRS) { $fieldSet = array(); switch(GetCurrentDBType()) { case "MySQL": $fieldCount = mysqli_num_fields($xRS); for($fLoop = 0; $fLoop < $fieldCount; $fLoop++) { $field = mysqli_fetch_field_direct($xRS, $fLoop); $fieldSet[] = array( 'type' => ConvertMySQLiTypeCode($field->type), 'name' => $field->name, 'table'=> $field->table); } break; case "MSSQL": $rawFields = sqlsrv_field_metadata($xRS); foreach($rawFields as $field) { $fieldSet[] = array( 'type' => GetTypeLabelForMSSQLTypeCode($field['Type']), 'name' => $field['Name'], 'table'=> null); } break; } return $fieldSet; } function ConvertMySQLiTypeCode($typeCode) { // return $typeCode; switch($typeCode) { case 16: return 'boolean'; case 1: case 2: case 3: case 6: case 8: case 9: return 'int'; case 4: case 5: case 246: return 'number'; case 10: case 12: case 7: case 11: case 13: return 'datetime'; case 252: case 253: case 254: return 'string'; } } function GetNextResultRecordSet(&$xRS) { switch(GetCurrentDBType()) { case "MySQL": return false; break; case "MSSQL": return sqlsrv_next_result($xRS); break; } } function GetTypeLabelForMSSQLTypeCode($type) { if($type == -7) return 'boolean'; if(in_array($type, array(1, -8, -10, -9, -1, 12, -152))) return 'text'; if(in_array($type, array(91, 92, 93, -154))) return 'datetime'; if(in_array($type, array(3, 6, 4, 3, 2, 7, 5, 3, -2, -6))) return 'number'; return 'text'; } function SetLimitSyntax($SQL, $limit) { if($limit <= 0) return $SQL; switch(GetCurrentDBType()) { case "MySQL": return "$SQL LIMIT $limit"; break; case "MSSQL": $result = str_replace("SELECT ", "SELECT TOP $limit ", $SQL); return $result; break; } } function FormatMSSQLErrors($errors) { foreach ($errors as $error) { echo "SQLSTATE: ".$error['SQLSTATE']."
"; echo "Code: ".$error['code']."
"; echo "Message: ".$error['message']."
"; } } function FormatRSDate($xR, $index) { switch(GetCurrentDBType()) { case "MySQL": $result = MySQLDateToString($xR[$index]); break; case "MSSQL": if(is_null($xR[$index])) return ''; // var_dump($xR[$index]); // FUNKY: when a var_dump is done, $xR[$index]->date) formats and outputs... otherwise blank?!? $dateValue = ProperDate($xR[$index]->date); $result = date($dateValue % 60*60*24 == 0 ? 'm/d/Y' : ($dateValue % 60 == 0 ? 'm/d/Y g:ia' : 'm/d/Y g:i:sa'), $dateValue); break; } return str_replace(' ', ' ', $result); } function GetSQLValueTD2($SQL, $f = '', $ordinal = 0) { echo "$SQL"; return GetSQLValueTD($SQL, $f, $ordinal); } function GetSQLValueTD($SQL, $fallback = '', $ordinal = 0) { $xR = rs_fetch_array(ExecuteSQLTD($SQL)); return $xR ? $xR[$ordinal] : $fallback; } function GetCurrentDBConnection() { global $TDConfig; return ProperInt($TDConfig['options']['currentConnection']); } function GetCurrentDBName() { global $TDConfig; return $TDConfig['connections'][GetCurrentDBConnection()]['schema']; } function GetCurrentDBType() { global $TDConfig; return $TDConfig['connections'][GetCurrentDBConnection()]['type']; } /*==== End SECTION :: Database Interactions ================================*/ function ExportToCSV() { $xRS = ExecuteSQLTD($_REQUEST["SQL"]); WriteAndDeliverRSAsCSV($xRS, "trueDATExport.csv"); } function PerformSQLExecution() { // Split on our statementDelimiter (approximate SQL multi-query behaviour) global $TDConfig; $statementDelimiter = $TDConfig['options']['statementDelimiter']; $rawSQLSet = explode($statementDelimiter, str_replace("\'", "'", $_POST["SQL"])); $SQLSet = array(); foreach($rawSQLSet as $SQL) { // filter out empties by hand... array_filter doesn't reindex, ugh! if(strlen(trim($SQL)) > 0) $SQLSet[] = $SQL; } $resultSet = array(); for($sLoop = 0; $sLoop < sizeof($SQLSet); $sLoop++) { tic(); $tRS = ExecuteSQLTD($SQLSet[$sLoop]); $timeElapsed = toc(); $hasResultRows = !is_bool($tRS); do { if($sLoop == 0) { if($hasResultRows) { $recordCount = rs_num_rows($tRS); $resultCountMessage = ($recordCount <= 0 ? '' : " : $recordCount record" . ConditionalMark($recordCount != 1, "s") . " returned"); $resultMessage = "Execution Result $resultCountMessage"; } else $resultMessage = "Execution Result"; echo "

$resultMessage

"; } echo "
"; if($sLoop > 0) echo "
Next Query: " . TruncatedString($SQLSet[$sLoop], 100) . "
"; if($hasResultRows) { DrawButton('Hide', 'loadShowHideColumnForm(this);', 'right'); DrawButton('Sort', 'makeColumnsSortable(this);', 'right'); if(rs_num_rows($tRS) > 0) DrawHiddenButton("Edit", "toggleEditMode(this);", 'edit'); DrawHiddenButton("Add New", "enterAddMode(this);", 'edit'); echo "
"; } $columnDataTypeSet = array(); $columnTableSet = array(); // the names of the tables that each column belongs to echo ""; if($hasResultRows) { $fieldSet = GetRSFieldSet($tRS); $fieldCount = count($fieldSet); // Render the header row: echo ""; foreach($fieldSet as $field) { $columnDataTypeSet[] = $field['type']; $columnTableSet[] = $field['table']; echo ""; } echo ""; $showHTMLWhiteSpace = RequestCheckbox("showHTMLWhiteSpace") || BeginsWith(strtoupper($SQLSet[$sLoop]), 'SHOW CREATE TABLE '); // Render the data rows: while($tR = rs_fetch_array($tRS)) { echo "\n"; for($fLoop = 0; $fLoop < $fieldCount; $fLoop++) { if($columnDataTypeSet[$fLoop] == 'boolean' || $tR[$fLoop] == chr(0x01) || $tR[$fLoop] == chr(0x00)) { if(is_null($tR[$fLoop])) $displayValue = ''; elseif($tR[$fLoop] == chr(0x01) || $tR[$fLoop] == chr(0x00)) $displayValue = ($tR[$fLoop] == chr(0x01) ? "True" : "False"); else $displayValue = (ProperInt($tR[$fLoop]) == 1 ? 'True' : 'False'); if($columnDataTypeSet[$fLoop] != 'boolean') $columnDataTypeSet[$fLoop] = 'boolean'; // correct for "unknown" datatype, ugh! } elseif($columnDataTypeSet[$fLoop] == 'datetime') { $displayValue = FormatRSDate($tR, $fLoop); } else $displayValue = htmlspecialchars(TruncatedString($tR[$fLoop], RequestInt("truncateLength", 0)), ENT_IGNORE); if($showHTMLWhiteSpace) $displayValue = HTMLWhiteSpace($displayValue); echo ""; } echo "\n"; } } else { // display number of rows affected global $TDDB_connection; $ar = mysqli_affected_rows($TDDB_connection); echo ""; } ?>
{$field['name']}
$displayValue
" . (ProperInt($ar, 'x') != 'x' ? $ar . " record" . PluralS($ar) . " affected" : "Execution Successful") . "
$TDConfig['options']['timeElapsedDisplayThreshold']) { ?> seconds elapsed.
$SQLSet[$sLoop], 'columnDataTypeSet' => $columnDataTypeSet, 'columnTableSet' => $columnTableSet); } while($hasMore = GetNextResultRecordSet($tRS)); } // next item in the SQLSet, in case split on $statementDelimiter yielded multiple queries ?> $deleteSQL"; else echo 'ok'; } function FetchTableField() { echo GetSQLValueTD("SELECT " . Request("columnName") . " FROM " . Request("tableName") . " WHERE " . GetTablePrimaryKey(Request("tableName")) . '=' . RequestInt("theID", 0)); } function UpdateTableField() { $theNewValue = Request(Request("activeInput")); $theSQLValue = $theEchoValue = ''; if($theNewValue == "*NULL*") // it's like magic, yo. $theSQLValue = "NULL"; else { switch(strtolower(Request("dataType"))) { case "boolean": $theSQLValue = SQLBit(strlen($theNewValue) > 0); $theEchoValue = (strlen($theNewValue) > 0 ? 'True' : 'False'); break; case "int": $theSQLValue = ProperInt($theNewValue, "null"); $theEchoValue = ProperInt($theNewValue, ""); break; case "float": case "currency": case "real": case "number": case "double": $theSQLValue = ProperNumber($theNewValue, "null"); $theEchoValue = ProperNumber($theNewValue, ""); break; case "date": $theSQLValue = SQLDate(ProperDate($theNewValue, "null")); $theEchoValue = ProperDate($theNewValue, ""); break; case "datetime": // MySQL $theSQLValue = SQLDate(ProperDate($theNewValue, "null")); $theEchoValue = MySQLDateToString(trim($theSQLValue, "\'")); break; case "string": case "blob": $theSQLValue = SQLValue($theNewValue); $theEchoValue = htmlspecialchars($theNewValue); break; } } $updateSQL = "UPDATE " . Request("tableName") . " SET " . Request("columnName") . "=$theSQLValue WHERE " . GetTablePrimaryKey(Request("tableName")) . "=" . RequestInt("theID", 0); ExecuteSQLTD($updateSQL, false); global $TDDB_connection; if(mysqli_error($TDDB_connection) != 0) // uh oh, we'll display what went wrong echo "Error: " . mysqli_error($TDDB_connection) . "
...no UPDATE occurred.
" . $updateSQL; else echo $theEchoValue; } function AddTableRow() { $tableName = Request("tableName"); $columnNameList = Request('columnNameList'); $columnNameSet = explode(', ', $columnNameList); $columnDataTypeSet = explode(', ', Request('columnDataTypeList')); $valueSet = array(); $displayValueSet = array(); for($i = 1; $i < count($columnDataTypeSet); $i++) { // $i=1 to skip ID column, per the convention $rawValue = Request("newField$i"); switch($columnDataTypeSet[$i]) { case 'boolean': $thisValue = SQLBit($rawValue); $displayValueSet[] = ProperBoolean($rawValue) ? 'True' : 'False'; break; case 'datetime': $thisValue = SQLDate($rawValue); $displayValueSet[] = $rawValue; break; default: $thisValue = SQLValue($rawValue); $displayValueSet[] = $rawValue; break; } $valueSet[] = $thisValue; } $insertSQL = "INSERT INTO $tableName($columnNameList) VALUES(" . implode(', ', $valueSet) . ")"; ExecuteSQLTD($insertSQL, false); global $TDDB_connection; if(mysqli_errno($TDDB_connection) != 0) { // uh oh, we'll display what went wrong and then quit ?> Error in the SQL that was generated, no INSERT occurred:

" . mysqli_insert_id($TDDB_connection) . ""; for($c = 0; $c < sizeof($columnNameSet); $c++) { echo "" . htmlspecialchars($displayValueSet[$c]) . ""; } } } /**************************************************************************** // SECTION::Table Transfer */ function DrawTableTransferInterface() { ?>
Export Tables

Limit: (zero for all)
Import Tables
Upload an export .zip file:
App.roar.alert('Please upload a .zip file.');"; return; } while($zipFile = zip_read($zip)) { $importTableSet[] = TrimTrailing(zip_entry_name($zipFile), ".csv"); } ?>


open($zipFileName, ZIPARCHIVE::CREATE | ZIPARCHIVE::OVERWRITE); foreach($tableSet as $tableName) { $xRS = ExecuteSQLTD(SetLimitSyntax("SELECT * FROM $tableName", $limit)); WriteRecordSetAsCSV($xRS, $tempFileName); clearstatcache(); $zip->addFromString("$tableName.csv", GetFileText($tempFileName)); } $zip->close(); unlink($tempFileName); // exit(); DeliverFileAsInlineDownload($zipFileName, 'trueDATTableTransfer.zip'); unlink($zipFileName); } function PerformTableTransferUpload() { DeleteTableTransferFile(); // only 1 uploaded at a time! $filename = '.trueDATTableTransfer_' . makeRandomHash(10) . '_uploadFile'; $success = move_uploaded_file($_FILES['theFile']['tmp_name'], $filename); ?> Commence Table Transfer Import"; while($zipFile = zip_read($zip)) { $tableName = substr(zip_entry_name($zipFile), 0, strlen(zip_entry_name($zipFile))-4); // trim off ".csv" // echo "$tableName
"; if(in_array($tableName, $tableSet)) { ImportTableFromCSV($tableName, zip_entry_read($zipFile, zip_entry_filesize($zipFile))); } } zip_close($zip); ExecuteSQLTD("SET FOREIGN_KEY_CHECKS=$oldForeignKeyChecks"); echo "
Table Transfer Import complete! " . JSLink('dismiss', "window.top.completeTableTransferImport();", 'button'); } function ImportTableFromCSV($tableName, $CSVData) { echo date('g:i:sa') . ": Importing $tableName... "; // Save out our CSV data to file so we can take advantage of fgetcsv: // (there's probably less roundabout way to do this!) $tempFileName = "tableTransferInputTemp_" . makeRandomHash(10) . ".csv"; $fh = fopen($tempFileName, 'w'); fwrite($fh, $CSVData); fclose($fh); // Get the columns for this table: $cRS = ExecuteSQLTD("SHOW COLUMNS FROM $tableName"); $dataTypeSet = array(); while($cR = rs_fetch_array($cRS)) { $dataTypeSet[$cR['Field']] = $cR['Type']; } $fh = fopen($tempFileName, 'r'); $fieldNameSet = fgetcsv($fh, 0); $fieldNameList = implode(', ', $fieldNameSet); ExecuteSQLTD("TRUNCATE TABLE $tableName"); $rowCount = 0; while(($data = fgetcsv($fh, 0)) !== FALSE) { $valueSet = array(); foreach($data as $index => $value) { $dataType = $dataTypeSet[$fieldNameSet[$index]]; if(BeginsWith($dataType, 'bit')) $theSQLValue = ($value == '' ? 'NULL' : SQLBit(StringProperBoolean($value))); elseif(BeginsWith($dataType, 'int') || BeginsWith($dataType, 'decimal')) $theSQLValue = ($value == '' ? 'NULL' : $value); elseif(BeginsWith($dataType, 'date')) $theSQLValue = ($value == '' ? 'NULL' : SQLDate($value)); else $theSQLValue = SQLValue($value); $valueSet[] = $theSQLValue; } ExecuteSQLTD("INSERT INTO $tableName ($fieldNameList) VALUES(" . implode(', ', $valueSet) . ")"); $rowCount++; } fclose($fh); unlink($tempFileName); echo $rowCount . " record" . PluralS($rowCount) . " done!
"; } function DeleteTableTransferFile() { if($uploadFileName = GetUploadedTableTransferFileName()) unlink($uploadFileName); } function GetUploadedTableTransferFileName() { return GetUploadedFileName("/\\.trueDATTableTransfer_.{10}_uploadFile/"); } /* // End SECTION::Table Transfer ****************************************************************************/ /**************************************************************************** // SECTION::CSV Queries */ function DrawCSVQueryInterface() { ?>
Upload a .csv file:
Insertable CSV fields:
"; foreach($fieldNameSet as $fieldName) { if(strlen($fieldName) < 40) // ensure that our CSV was legit/this is a plausible field name: echo JSLink("<$$fieldName>", 'insertCSVField(' . JSValue($fieldName) . ')', 'button'); } ?>
CSV Query:

For first rows     OR

"; } set_time_limit(9999); BeginIFrame(); echo "

CSV Query " . ($execute ? "Execution" : "Preview") . "

"; $rowCount = 0; $failCount = 0; global $TDDB_connection; while(($CSVSet = fgetcsv($fh, 0)) !== FALSE && ($limit <= 0 || $rowCount < $limit)) { // Generate this SQL as a plug-and-chug substitution from the CSV row data: $SQL = $CSVSQL; foreach($tagSet as $index => $tag) { $SQL = str_replace($tag, SQLSafe($CSVSet[$index]), $SQL); } $failure = false; $rowCount++; if($execute) { if(!ExecuteSQLTD($SQL, false)) { $failure = true; $failCount++; } } if($verbose || !$execute || $failure) { if($failure) { echo "$SQL
Error: " . mysqli_error($TDDB_connection) . "

"; } else { echo "$SQL
"; } } if($failCount == $rowCount && $rowCount >= 3) { echo "This doesn't seem to be going well--we'll wrap up early and allow you to re-tool!
"; break; } } fclose($fh); echo "
CSV Query Complete. " . JSLink('dismiss', "window.top.completeCSVQuery();", 'button'); } function DeleteCSVFile() { if($uploadFileName = GetUploadedCSVFileName()) unlink($uploadFileName); } function GetUploadedCSVFileName() { return GetUploadedFileName("/\\.trueDATCSV_.{10}_uploadFile/"); } /* // End SECTION::CSV Queries ****************************************************************************/ /**************************************************************************** // SECTION::Value Finder */ function DrawValueFinderInterface() { ?>
Find A Value
Find a string:  
Find a number:
Find a date:

Value Finder Results

"Type LIKE '%char%' OR Type LIKE '%text%'", 'number' => "Type LIKE 'int%' OR Type LIKE 'float%' OR Type LIKE 'decimal%'", 'date' => "Type IN ('date', 'datetime', 'timestamp')", ); // Iterate through every table, query appropriate columns for each based on $which type of search: $hasResults = false; $tRS = ExecuteSQLTD("SHOW TABLES"); while($tR = rs_fetch_array($tRS)) { $tableName = $tR[0]; // Iterate through columns to find relevant ones: $valueClauseSet = array(); $cRS = ExecuteSQLTD("SHOW COLUMNS FROM `$tableName` WHERE {$whichTypeClauseSet[$which]}"); while($cR = mysqli_fetch_assoc($cRS)) { $valueClauseSet[] = "`{$cR['Field']}`" . (($which == 'string' && $like) ? " LIKE " : " = ") . $value; } if(count($valueClauseSet) > 0) { // this table is worth searching in! $valueClause = implode(" OR ", $valueClauseSet); $resultCount = GetSQLValueTD("SELECT COUNT(*) FROM $tableName WHERE $valueClause"); if($resultCount > 0) { if(!$hasResults) { echo ""; $hasResults = true; } // Now sort out which column(s) led to us getting results in this table: $relevantClauseSet = array(); $relevantFieldSet = array(); foreach($valueClauseSet as $fieldClause) { if(GetSQLValueTD("SELECT COUNT(*) FROM $tableName WHERE $fieldClause") > 0) { $relevantClauseSet[] = $fieldClause; preg_match("/^`(.+)`/", $fieldClause, $matchSet); // what was that field name again? $relevantFieldSet[] = $matchSet[1]; } } $relevantClause = implode("\n OR ", $relevantClauseSet); $theSQL = JSValue("SELECT * FROM $tableName\n WHERE $relevantClause"); ?> "; else echo "The $which $value could not be found.
"; ?>
Value Find complete. read()) !== false) { if(preg_match($fileNamePattern, $entry)) { // found it! $result = $entry; break; } } $theFolder->close(); return $result; } function BeginIFrame() { global $trueDATBaseURL; ?> GetCurrentDBType(), 'statementDelimiter' => $TDConfig['options']['statementDelimiter'], 'tableSet' => $tableSet, 'tableLabelSet' => $tableLabelSet, 'tablePrimaryKeySet' => $primaryKeySet, 'suggestionSet' => $suggestionSet, 'foreignKeySet' => $foreignKeySet, 'SPSet' => $SPSet); echo json_encode($DBData); } function GetStoredProcedureDefinition() { $SPName = StraightRequestText('SPName'); global $TDConfig; $statementDelimiter = $TDConfig['options']['statementDelimiter']; switch(GetCurrentDBType()) { case 'MySQL': $xR = mysqli_fetch_array(ExecuteSQLTD("SHOW CREATE PROCEDURE $SPName")); $spText = "DROP PROCEDURE IF EXISTS $statementDelimiter$delim{$xR[2]}"; break; case 'MSSQL': break; } echo $spText; } function MySQLDateToString($dateValue) { $dateParts = preg_split('/[: -]/', $dateValue . " 00"); if(sizeof($dateParts) >= 6) { list($year, $month, $day, $hour, $min, $sec) = $dateParts; return "$month/$day/$year" . ($hour + $min + $sec > 0 ? " $hour:$min" : '') . ($sec == "00" ? "" : ":$sec"); } return $dateValue; // doesn't parse as a date, so... } function JSLink($label, $onClick, $class = '') { return "$label"; } /*============================================================================ SECTION :: Utilities */ /*============================================================================ SECTION :: General */ function currentPageURL() { $URLPartSet = explode('/', $_SERVER['SERVER_NAME'] . $_SERVER['PHP_SELF']); return join('/', array_splice($URLPartSet, 0, -1)) . '/'; } function RequestInt($RVN, $badReturnValue = 0) { return ProperInt(Request($RVN), $badReturnValue); } function RequestNumber($RVN, $badReturnValue = 0) { return ProperNumber(Request($RVN), $badReturnValue); } function ProperInt($theValue, $badReturnValue = 0) { if(is_numeric($theValue)) return intval($theValue); elseif(is_string($theValue) && is_numeric(str_replace(',', '', $theValue))) return intval(str_replace(',', '', $theValue)); else return $badReturnValue; } function ProperNumber($theValue, $badReturnValue = 0) { if(is_numeric($theValue)) return (float)$theValue; elseif(is_string($theValue) && is_numeric(str_replace(',', '', $theValue))) return (float)(str_replace(',', '', $theValue)); else return $badReturnValue; } function ProperBoolean($value, $badReturnValue=false) { if($value === true || (is_string($value) && strtolower($value) == 'true')) return true; elseif($value === false || (is_string($value) && strtolower($value) == 'false')) return false; else return $badReturnValue; } function StringProperBoolean($value, $badReturnValue=false) { if(ProperInt($value, "nan") != "nan") { return ProperInt($value, "nan") != 0; } if(strtolower($value) == 'true') return true; elseif(strtolower($value) == 'false') return false; else return $badReturnValue; } function Request($RVN, $emptyValue = '') { if(isset($_GET[$RVN])) return $_GET[$RVN]; elseif(isset($_POST[$RVN])) return $_POST[$RVN]; else return $emptyValue; } function StraightRequestText($RVN, $maxLength = 0) { $value = Request($RVN); if(is_array($value)) $value = join(", ", $value); return str_replace("\'", "'", StraightText($value, $maxLength)); } function RequestCheckbox($RVN) { if(!isset($_REQUEST[$RVN])) return false; else return (strlen($_REQUEST[$RVN]) > 0); } function RequestDate($RVN, $badReturnValue=false) { return ProperDate(Request($RVN), $badReturnValue); } function HTMLWhiteSpace($theString) { return str_replace(array("\r\n", "\n", "\n", ' ', "\t"), array('
', '
', '
', '  ', '    '), $theString); } function TagHTMLEncode($value) { return str_replace("&", "&", htmlspecialchars($value)); } function StraightText($value, $maxLength = 0) { $result = str_replace(array(chr(145), chr(146), chr(147), chr(148)), array('\'', '\'', '\"', '\"'), $value); // do away with all smart quotes $result = trim(strip_tags($result)); if(ProperInt($maxLength, 0) > 0) // user has indicated a limit on length, so... $result = substr($result, 0, $maxLength); return $result; } function Session($SVN, $badReturnValue = '') { if(!isset($_SESSION[$SVN])) return $badReturnValue; else return $_SESSION[$SVN]; } function GetFileText($fileSpec, $badReturnValue = '') { if(!file_exists($fileSpec)) return $badReturnValue; $fh = fopen($fileSpec, 'r'); flock($fh, LOCK_SH); $result = fread($fh, filesize($fileSpec)); fclose($fh); return $result; } /*==== End SECTION :: General ==============================================*/ /*============================================================================ SECTION :: JavaScript Output */ function JSSafe($theString) { $result = $theString; $result = str_replace("\r\n", "\r", $result); $result = str_replace("\n", "\r", $result); $result = str_replace("\\", "\\\\", $result); $result = str_replace("'", "\\'", $result); $result = str_replace("\"", "\\\"", $result); $result = str_replace("\r", "\\n", $result); return $result; } function JSValue($theString) { return "'" . JSSafe($theString) . "'"; } function JSBoolean($value) { return ($value ? 'true' : 'false'); } /*==== End SECTION :: JavaScript Output ====================================*/ /*============================================================================ SECTION :: QueryString Manipulation */ function QueryStringEncode($s) { return str_replace(array("%", "&", "?", "="), array("%25", "%26", "%3F", "%3D"), $s); } function QueryStringUnencode($s) { return str_replace(array("%25", "%26", "%3F", "%3D"), array("%", "&", "?", "="), $s); } function ArrayToQueryString($theArray, $ignoreEmpties = false) { $result = ""; foreach($theArray as $key => $value) { if(strlen($value) > 0 || !$ignoreEmpties) $result .= "&$key=" . QueryStringEncode($value); } return ltrim($result, "&"); } function QueryStringToArray($queryString) { if(strpos($queryString, '=') === false) return array(); // special case for empty $fieldSet = explode("&", $queryString); $result = array(); for($fLoop = 0; $fLoop < sizeof($fieldSet); $fLoop++) { $thisNVP = explode("=", $fieldSet[$fLoop]); // name/value pair $result[$thisNVP[0]] = QueryStringUnencode($thisNVP[1]); } return $result; } /*==== End SECTION :: QueryString Manipulation =============================*/ /*============================================================================ SECTION :: Performance Timing/Tuning */ $ticTimers = array(); function tic($key = "**DEFAULT**") { global $ticTimers; $ticTimers[$key] = time()+microtime(); } function toc($key = "**DEFAULT**") { global $ticTimers; if($ticTimers[$key]) return number_format((time()+microtime()) - $ticTimers[$key], 7); else return -1; } /*==== End SECTION :: Performance Timing/Tuning ============================*/ /*============================================================================ SECTION :: String Helpers */ function ConditionalMark($theBoolean, $theMark) { return $theBoolean ? $theMark : ''; } function CheckedMark($isChecked) { return ($isChecked || ord($isChecked) == 1) ? " checked" : ''; } function SelectedMark($isSelected) { return $isSelected ? " selected" : ''; } function DisabledMark($isDisabled) { return $isDisabled ? " disabled" : ''; } function PluralS($theNumber) { return ($theNumber==1 ? '' : 's'); } function TruncatedString($theString, $charLimit) { if($charLimit <= 0 || strlen($theString . "") <= $charLimit) return $theString; else return substr($theString, 0, $charLimit) . "..."; } function BeginsWith($theString, $targetPrefix) { if(is_array($targetPrefix)) { foreach($targetPrefix as $prefix) { if(BeginsWith($theString, $prefix)) return true; } return false; } return (strncmp($theString, $targetPrefix, strlen($targetPrefix)) == 0); } function EnsureBeginsWith($theString, $prefix) { $result = $theString; if(!BeginsWith($result, $prefix)) $result = $prefix . $result; return $result; } function EndsWith($theString, $targetSuffix) { return (substr($theString, strlen($theString) - strlen($targetSuffix) ) == $targetSuffix); } function EnsureEndsWith($theString, $prefix) { $result = $theString; if(!EndsWith($result, $prefix)) $result = $result . $prefix; return $result; } function TrimLeading($theString, $leadingString) { if(BeginsWith($theString, $leadingString)) return substr($theString, strlen($leadingString)); else return $theString; } function TrimTrailing($theString, $trailingString) { if(EndsWith($theString, $trailingString)) return substr($theString, 0, strlen($theString) - strlen($trailingString)); else return $theString; } function HTTPifyURL($URL) { $URL = TrimLeading($URL, '//'); if(!BeginsWith($URL, 'https://')) $URL = EnsureBeginsWith($URL, 'http://'); return $URL; } function makeRandomHash($lenth = 5, $charRange = false) { // makes a random alpha numeric string of a given length if(!$charRange) $charRange = array_merge(range('A', 'Z'), range('a', 'z'), range(0, 9)); $result = ''; for($c=0;$c < $lenth; $c++) { $result .= $charRange[mt_rand(0, count($charRange)-1)]; } return $result; } /*==== End SECTION :: String Helpers =======================================*/ /*============================================================================ SECTION :: SQL Helpers */ function SQLValue($s) { return "'" . str_replace("'", "''", $s) . "'"; } function SQLSafe($s) { return str_replace("'", "''", $s); } function SQLBit($boolValue) { return ($boolValue ? 1 : 0); } function SQLDate($dateValue) { $timeStamp = ProperDate($dateValue, 0); return SQLValue(date('Y-m-d H:i:s', $timeStamp)); // YYYY-MM-DD HH:MM:SS } function ProperDate($dateValue, $badReturnValue = false, $formatString = false) { if(is_integer($dateValue)) // already a date, in PHP world $result = $dateValue; elseif(ProperInt($dateValue) > 0) { $result = ProperInt($dateValue); } else { $result = strtotime($dateValue); if($result === false) $result = $badReturnValue; } if($formatString && $result != $badReturnValue) $result = date($formatString, $result); return $result; } function XXFormatRSDate($formatString, $DBDate, $badReturnValue = '') { if(!strtotime($DBDate)) return $badReturnValue; return date($formatString, strtotime($DBDate)); } function RSBool($RSBitField) { return ord($RSBitField) == 1 || $RSBitField == '1'; } /*==== End SECTION :: SQL Helpers ===========================================*/ /*============================================================================ SECTION :: CSV Helpers */ function CSVValue($value) { if(is_null($value)) return ''; if(strpos($value, "\"") !== false || strpos($value, ",") !== false || strpos($value, "\n") !== false || strpos($value, "\r") !== false) return "\"" . str_replace("\"", "\"\"", $value) . "\""; else return $value; } function WriteRecordSetAsCSV($xRS, $fileName, $includeHeader = true, $fieldOutputDescriptor = null, $delim = " ") { $fh = fopen($fileName, 'w') or die("can't open $fileName for WriteRecordSetAsCSV"); if($includeHeader) { // includeHeader can either be true (meaning generate it for me) or a custom string fwrite($fh, (is_string($includeHeader) ? $includeHeader : HeaderCSVLineOfRecordSet($xRS)) . "\r\n"); } if(!$fieldOutputDescriptor) { $fieldSet = rs_get_field_names($xRS); $fieldOutputDescriptor = implode($delim, $fieldSet); } $fieldOutputSet = explode($delim, $fieldOutputDescriptor); while($xR = rs_fetch_array($xRS)) { $dataSet = array(); for($fLoop = 0; $fLoop < count($fieldOutputSet); $fLoop++) { $dataSet[] = CSVValue(ExtractFormattedValueFromRS($fieldOutputSet[$fLoop], $xR)); } fwrite($fh, implode(',', $dataSet) . "\r\n"); } fclose($fh); } function WriteAndDeliverRSAsCSV(&$xRS, $fileName, $iH = true, $fOD = null, $d = ' ') { WriteRecordSetAsCSV($xRS, $fileName, $iH, $fOD, $d); DeliverFileAsInlineDownload($fileName); unlink($fileName); } function ExtractFormattedValueFromRS($fieldDescriptor, $xR) { $fieldDescriptorSet = explode(':', $fieldDescriptor, 2); $fieldName = $fieldDescriptorSet[0]; $result = $xR[$fieldName]; // and possibly to be formatted! if(count($fieldDescriptorSet) > 1) { // /// "name:200 URL:500 privacyURL:500 tagLine:500 bid:n priority:i isActive:b"); $dataType = Left($fieldDescriptorSet[1], 1); $specifier= rtrim(ltrim(substr($fieldDescriptorSet[1], 1), "("), ")"); // whatever follows first char, ()'s optional! switch($dataType) { // our formatting instructions case '$': // money $result = "$" . number_format($result, 2); break; case 'd': // date $result = date(($specifier ? $specifier : "m/d/y"), $result); break; case 'b': // boolean $YNSet = explode('/', $specifier . '/'); $result = RSBool($result) ? $YNSet[0] || "True" : $YNSet[1] || "False"; break; } } if($result == chr(0x01) || $result == chr(0x00)) // handle bit fields to make more friendly $result = ($result == chr(0x01) ? "True" : "False"); return $result; } function HeaderCSVLineOfRecordSet($xRS) { $fieldSet = rs_get_field_names($xRS); // echo implode(',', $fieldSet); exit(); return implode(',', $fieldSet); for($fLoop = 0; $fLoop < $fieldCount; $fLoop++) { $theField = rs_get_field_names($xRS, $fLoop); $fieldSet[] = $theField->name; } return implode(',', $fieldSet); } function DeliverFileAsInlineDownload($file, $displayFileName = false) { if(!$displayFileName) $displayFileName = basename($file); if(file_exists($file)) { header('Content-Description: File Transfer'); header('Content-Type: application/octet-stream'); header('Content-Disposition: attachment; filename=' . $displayFileName); header('Content-Transfer-Encoding: binary'); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); header('Content-Length: ' . filesize($file)); flush(); readfile($file); } } /*==== End SECTION :: CSV Helpers ===========================================*/
TableRecordsActions