/**
* Returns an unique Identifier for a record in a table
*
* @param string $db Database
* @param string $table Table
* @param array &$record Complete records
*
* @return string Where-Condition to identify the record
*/
function getRecordIdentifier($db, $table, &$record)
{
global $table_infos;
$where = '';
if (!isset($table_infos[$db]['tables'][$table]['keys'])) {
$table_infos = getTableInfo($db, $table);
$keys = getKeys($db, $table);
$table_infos[$db]['tables'][$table]['keys'] = $keys;
}
if (isset($table_infos[$db]['tables'][$table]['keys']['PRIMARY'])) {
// table has a primary key -> we can build the identifier by it
foreach ($table_infos[$db]['tables'][$table]['keys']['PRIMARY'] as $column => $val) {
$where .= $where > '' ? ' AND ' : '';
$where .= '`' . $column . '` = \'' . $record[$column] . '\'';
}
} else {
// shame on the table design -> no key given -> build key from all values of record
foreach ($record as $column => $val) {
$where .= $where > '' ? ' AND ' : '';
$where .= '`' . $column . '` = \'' . $record[$column] . '\'';
}
}
return $where;
}
/**
* @param $contactIDs
* @param $values
*/
function getContactInfo(&$contactIDs, &$values)
{
$fields = array('sort_name' => NULL, 'display_name' => NULL, 'contact_type' => NULL, 'legal_identifier' => NULL, 'external_identifier' => NULL, 'first_name' => NULL, 'last_name' => NULL, 'middle_name' => NULL, 'household_name' => NULL, 'organization_name' => NULL, 'legal_name' => NULL, 'job_title' => NULL);
getTableInfo($contactIDs, $values, 'civicrm_contact', $fields, 'id');
}
/**
* @param $contactIDs
* @param $values
*/
function getContactInfo(&$contactIDs, &$values)
{
$fields = array('sort_name' => NULL, 'display_name' => NULL, 'contact_type' => NULL, 'legal_identifier' => NULL, 'external_identifier' => NULL, 'source' => 'contact_source');
getTableInfo($contactIDs, $values, 'civicrm_contact', $fields, 'id');
$fields = array('first_name' => NULL, 'last_name' => NULL, 'middle_name' => NULL, 'job_title' => NULL);
getTableInfo($contactIDs, $values, 'civicrm_individual', $fields, 'contact_id');
$fields = array('household_name' => NULL);
getTableInfo($contactIDs, $values, 'civicrm_household', $fields, 'contact_id');
$fields = array('organization_name' => NULL, 'legal_name' => NULL, 'sic_code' => NULL);
getTableInfo($contactIDs, $values, 'civicrm_organization', $fields, 'contact_id');
$fields = array('note' => 'note_body', 'subject' => 'note_subject');
getTableInfo($contactIDs, $values, 'civicrm_note', $fields, 'entity_id', "entity_table = 'civicrm_contact'");
}
$dbs = array($_GET['drop_db']);
}
if ($do > '' && sizeof($dbs) > 0) {
$sql = array();
$associatedDatabase = array();
$tplSqlbrowserDbOperation = new MSDTemplate();
$tplSqlbrowserDbOperation->set_filenames(array('tplSqlbrowserDbOperation' => 'tpl/sqlbrowser/db/operation.tpl'));
$i = 0;
foreach ($dbs as $database) {
$database = base64_decode($database);
// truncate all tables but keep database
if ($do == 'db_truncate') {
$actionOutput = $lang['L_CLEAR_DATABASE'];
// clear table array to not delete other tables by accident
unset($tableInfos);
$tableInfos = getTableInfo($database);
$sql[] = 'SET FOREIGN_KEY_CHECKS=0';
$associatedDatabase[] = $database;
foreach ($tableInfos[$database]['tables'] as $table => $val) {
$query = $val['engine'] == 'VIEW' ? 'DROP VIEW ' : 'DROP TABLE ';
$sql[] = sprintf($query . '`%s`.`%s`', $database, $table);
$associatedDatabase[] = $database;
}
$sql[] = 'SET FOREIGN_KEY_CHECKS=1';
$associatedDatabase[] = $database;
}
// delete complete database
if ($do == 'db_delete') {
$actionOutput = $lang['L_DELETE_DATABASE'];
$sql[] = 'DROP DATABASE `' . $database . '`';
$associatedDatabase[] = $database;
/**
* Gets all information about a dump process and stores it in global $dump-Array
*
* @return void
*/
function prepareDumpProcess()
{
global $databases, $dump, $config, $tableInfos;
$dump['databases'] = array();
$dump['records_total'] = 0;
$dump['tables_total'] = 0;
$dump['datasize_total'] = 0;
// make copy of database-array to make changes for value "dump" just here
$dbs = $databases;
// first check if any db is marked to be dumped
$dbToDumpExists = false;
foreach ($dbs as $val) {
if (isset($val['dump']) && $val['dump'] == 1) {
// Db should be saved
$dbToDumpExists = true;
break;
}
}
// no db selected for dump -> set actual db to be dumped
if (!$dbToDumpExists) {
$dbs[$config['db_actual']]['dump'] = 1;
}
// find out which databases and tables should be saved
// dump=0 -> don't dump records
// dump=1 -> dump records using "INSERT INTO"
foreach ($dbs as $dbName => $val) {
if (isset($val['dump']) && $val['dump'] > 0) {
// db should be dumped
// now lets check which tables should be saved
// for now we save all tables -> later check prefixes etc...
$tableInfos = getTableInfo($dbName);
if (isset($tableInfos[$dbName])) {
if (!isset($dump['databases'][$dbName])) {
$dump['databases'][$dbName] = array();
}
// calculate sums
$dump['databases'][$dbName] = $tableInfos[$dbName];
$dump['databases'][$dbName]['prefix'] = '';
if (isset($databases[$dbName]['prefix'])) {
$dump['databases'][$dbName]['prefix'] = $databases[$dbName]['prefix'];
}
$dump['records_total'] += $dump['databases'][$dbName]['records_total'];
$dump['tables_total'] += $dump['databases'][$dbName]['table_count'];
$dump['datasize_total'] += $dump['databases'][$dbName]['datasize_total'];
// check if tables are selected ->
// then remove all others from array and correct sums
if ($dbName == $_SESSION['config']['db_actual'] && isset($dump['selected_tables']) && is_array($dump['selected_tables']) && count($dump['selected_tables']) > 0) {
foreach ($dump['databases'][$dbName]['tables'] as $tablename => $val) {
if (!in_array($tablename, $dump['selected_tables'])) {
$dump['databases'][$dbName]['tables'][$tablename]['dump_structure'] = 0;
$dump['databases'][$dbName]['tables'][$tablename]['dump_records'] = 0;
// remove table from todo-list
unset($dump['databases'][$dbName]['tables'][$tablename]);
// substract values of table from sums
$dump['tables_total']--;
$dump['databases'][$dbName]['table_count']--;
$dump['databases'][$dbName]['records_total'] -= $val['records'];
$dump['databases'][$dbName]['datasize_total'] -= $val['data_length'];
$dump['databases'][$dbName]['size_total'] -= $val['data_length'] + $val['index_length'];
$dump['datasize_total'] -= $val['data_length'];
$dump['records_total'] -= $val['records'];
}
}
}
}
}
}
// set db to be dumped first -> start index is needed
$dbNames = array_keys($dump['databases']);
$dump['db_actual'] = $dbNames[0];
}
$row = $res[0];
// mainatining functions with result
// (optimize, repair, analyze,..
$msgType = isset($row['Msg_type']) ? $row['Msg_type'] : '';
$msgTxt = isset($row['Msg_text']) ? $row['Msg_text'] : '';
$tplSqlbrowserTableOperation->assign_block_vars('ROW', array('ROWCLASS' => $i % 2 ? 'dbrow' : 'dbrow1', 'NR' => $i + 1, 'TABLENAME' => $table, 'ACTION' => isset($row['Op']) ? $row['Op'] : '', 'TYPE' => $msgType, 'MESSAGE' => $msgTxt));
}
} else {
// error
$tplSqlbrowserTableOperation->assign_block_vars('ERROR', array('ROWCLASS' => $i % 2 ? 'dbrow' : 'dbrow1', 'NR' => $i + 1, 'TABLENAME' => $table, 'ERROR' => mysql_error(), 'QUERY' => sprintf($query, $db, $table)));
}
$i++;
}
}
// Output list of tables of the selected database
$tableInfos = getTableInfo($db);
// extract sorted one-dimensional array with infos we need
$orderArray = get_orderarray($sortColumn . ',' . $sortDirection . '|name,a');
$sortedTableInfos = arfsort($tableInfos[$db]['tables'], $orderArray);
$tplSqlbrowserTableListTables = new MSDTemplate();
$tplSqlbrowserTableListTables->set_filenames(array('tplSqlbrowserTableListTables' => 'tpl/sqlbrowser/table/listTables.tpl'));
$numrows = $tableInfos[$db]['table_count'];
$up = $icon['arrow_up'];
$down = $icon['arrow_down'];
$sortName = $sortColumn == 'name' ? $sortDirection == 'd' ? $down : $up : '';
$sD = $sortDirection;
$sC = $sortColumn;
$tplSqlbrowserTableListTables->assign_vars(array('ICON_VIEW' => $icon['view'], 'ICON_EDIT' => $icon['edit'], 'ICON_OK' => $icon['ok'], 'ICON_NOT_OK' => $icon['not_ok'], 'ICON_DELETE' => $icon['delete'], 'ICON_TRUNCATE' => $icon['truncate'], 'ICON_UP' => $up, 'ICON_DOWN' => $down, 'ICON_PLUS' => $icon['plus'], 'ICON_MINUS' => $icon['minus'], 'ICON_CANCEL' => $icon['cancel'], 'DB_NAME' => $db, 'DB_NAME_URLENCODED' => base64_encode($db), 'TABLE_COUNT' => String::formatNumber($numrows), 'ICONPATH' => $config['files']['iconpath'], 'SORT_BY_COLUMN' => $sC, 'SORT_DIRECTION' => $sD, 'SORT_NAME' => $sortName, 'SORT_RECORDS' => $sC == 'records' ? $sD == 'D' ? $down : $up : '', 'SORT_DATA_LENGTH' => $sC == 'data_length' ? $sD == 'D' ? $down : $up : '', 'SORT_INDEX_LENGTH' => $sC == 'index_length' ? $sD == 'D' ? $down : $up : '', 'SORT_AUTO_INCREMENT' => $sC == 'auto_increment' ? $sD == 'D' ? $down : $up : '', 'SORT_DATA_FREE' => $sC == 'data_free' ? $sD == 'D' ? $down : $up : '', 'SORT_UPDATE_TIME' => $sC == 'update_time' ? $sD == 'd' ? $down : $up : '', 'SORT_ENGINE' => $sC == 'engine' ? $sD == 'd' ? $down : $up : '', 'SORT_COLLATION' => $sC == 'collation' ? $sD == 'd' ? $down : $up : '', 'SORT_COMMENT' => $sC == 'comment' ? $sD == 'd' ? $down : $up : '', 'CONFIRM_TRUNCATE_TABLES' => Html::getJsQuote($lang['L_CONFIRM_TRUNCATE_TABLES']), 'CONFIRM_DELETE_TABLES' => Html::getJsQuote($lang['L_CONFIRM_DELETE_TABLES'])));
if ($numrows > 1) {
$tplSqlbrowserTableListTables->assign_block_vars('MORE_TABLES', array());
} elseif ($numrows == 1) {
