/**
* Returns the columns of a given table and database.
* @param array $params get params of the request
* @return array
*/
public function cols(array $params = array())
{
// get db and table from params
if (empty($params['db']) || empty($params['table'])) {
return array('status' => 'error');
} else {
$db = $params['db'];
$table = $params['table'];
}
// init table
$this->getResource()->init($params['db'], $params['table']);
// get columns from the database
$colnames = array_keys($this->getResource()->fetchCols());
// obtain table metadata
$tablesResource = new Data_Model_Resource_Tables();
$tableMeta = $tablesResource->fetchRowByName($db, $table, true);
if ($tableMeta === false) {
// this table is not in the metadata table - let's see if we can get
// further information from the table itself
$descResource = new Data_Model_Resource_Description();
$descResource->init($params['db']);
$tableMeta = $descResource->describeTable($params['table']);
}
// construct metadata array
$meta = array();
foreach ($tableMeta['columns'] as $key => $colMeta) {
$meta[$colMeta['name']] = array('id' => $key, 'ucd' => explode(';', str_replace(' ', '', $colMeta['ucd'])));
}
// return columns of this table
$cols = array();
foreach ($colnames as $colname) {
$col = array('id' => $meta[$colname]['id'], 'name' => $colname, 'sortable' => true, 'ucfirst' => false, 'ucd' => $meta[$colname]['ucd']);
// add removenewline flag if this is set in the config
if (Daiquiri_Config::getInstance()->data->viewer->columnWidth) {
$col['width'] = Daiquiri_Config::getInstance()->data->viewer->columnWidth;
} else {
$col['width'] = 100;
}
// add removenewline flag if this is set in the config
if (Daiquiri_Config::getInstance()->data->viewer->removeNewline) {
$col['format'] = array('removeNewline' => true);
}
// append col to cols array
$cols[] = $col;
}
return array('status' => 'ok', 'cols' => $cols);
}
/**
* Inserts one table entry and, optionally, fills the columns with information from
* the database or a provided array.
* Returns the primary key of the new row.
* @param array $data row data
* @throws Exception
* @return int $id
*/
public function insertRow(array $data = array())
{
if (empty($data)) {
throw new Exception('$data not provided in ' . get_class($this) . '::' . __FUNCTION__ . '()');
}
if (isset($data['autofill'])) {
$autofill = $data['autofill'];
unset($data['autofill']);
}
if (isset($data['tableDescription'])) {
$tableDescription = $data['tableDescription'];
unset($data['tableDescription']);
}
// store row in database and get id
$this->getAdapter()->insert('Data_Tables', $data);
$id = $this->getAdapter()->lastInsertId();
if (isset($autofill) && !empty($autofill)) {
// get the additional resources
$columnResource = new Data_Model_Resource_Columns();
$databaseResource = new Data_Model_Resource_Databases();
// auto create entries for all columns
$row = $databaseResource->fetchRow($data['database_id']);
$database = $row['name'];
$table = $data['name'];
try {
if (empty($tableDescription)) {
$descResource = new Data_Model_Resource_Description();
$descResource->init($database);
$tableDescription = $descResource->describeTable($table);
}
foreach ($tableDescription['columns'] as $column) {
$column['table'] = $table;
$column['table_id'] = $id;
$column['database'] = $database;
$columnResource->insertRow($column);
}
} catch (Exception $e) {
$this->getAdapter()->delete('Data_Tables', array('`id` = ?' => $id));
throw $e;
}
}
return $id;
}
/**
* Inserts one database entry and, if set, the fills the columns and tables automatically.
* Returns the primary key of the new row.
* @param array $data row data
* @throws Exception
* @return int $id
*/
public function insertRow(array $data = array())
{
if (empty($data)) {
throw new Exception('$data not provided in ' . get_class($this) . '::' . __FUNCTION__ . '()');
}
if (isset($data['autofill'])) {
$autofill = $data['autofill'];
unset($data['autofill']);
}
// store row in database and get id
$this->getAdapter()->insert('Data_Databases', $data);
$id = $this->getAdapter()->lastInsertId();
if (isset($autofill) && !empty($autofill)) {
// get the additional resources
$descResource = new Data_Model_Resource_Description();
$tableResource = new Data_Model_Resource_Tables();
// auto create entries for all tables
try {
$descResource->init($data['name']);
foreach ($descResource->fetchTables() as $table) {
$desc = $descResource->describeTable($table);
$tableData = array('database_id' => $id, 'name' => $desc['name'], 'description' => $desc['description'], 'publication_role_id' => $data['publication_role_id'], 'publication_select' => $data['publication_select'], 'publication_update' => $data['publication_update'], 'publication_insert' => $data['publication_insert'], 'autofill' => true, 'tableDescription' => $desc);
$tableResource->insertRow($tableData);
}
} catch (Exception $e) {
// delete database entry again
$this->getAdapter()->delete('Data_Databases', array('`id` = ?' => $id));
throw $e;
}
}
return $id;
}
/**
* Writes the metadata for a column into the column comment in the database table.
* @param string $database
* @param string $table
* @param string $column
* @param array $data metadata to be stored
* @param string $oldComment existing comment string (from table description)
* @throws Exception
*/
private function _writeColumnComment($database, $table, $column, $data, $oldComment = false)
{
//check sanity of input
foreach ($data as $key => $value) {
if (is_string($data) && (strpos($data, "{") !== false || strpos($data, "}") !== false)) {
throw new Exception("Unsupported character {} in " . $key . ": " . $data);
}
}
// write metadata into comment field of the column (if supported)
$descResource = new Data_Model_Resource_Description();
$descResource->init($database);
if ($oldComment === false) {
$comment = $descResource->fetchColumnComment($table, $column);
$oldComment = $comment;
} else {
$comment = $oldComment;
}
$json = Zend_Json::encode($data);
// check if there is already a comment present with our metadata
$charPos = strpos($comment, "DQIMETA=");
if ($charPos !== false) {
// find end of json
$endPos = $descResource->findJSONEnd($comment, $charPos);
if ($endPos === false) {
throw new Exception("Cannot update MySQL meta data due to corruped column comment.");
}
$comment = substr($comment, 0, $charPos) . "DQIMETA=" . $json . substr($comment, $endPos + 1);
} else {
if (strlen($comment) > 0) {
$comment .= ", DQIMETA=" . $json;
} else {
$comment = "DQIMETA=" . $json;
}
}
// only do something if there is a change...
if ($comment !== $oldComment) {
$descResource->storeColumnComment($table, $column, $comment);
}
return true;
}
/**
* Returns one jobs for the current user.
* @param type $id job id
* @return array $response
*/
public function showJob($id)
{
// set job resource
$this->setResource(Query_Model_Resource_AbstractQuery::factory());
// get job and check permissions
$dbRow = $this->getResource()->fetchRow($id);
if (empty($dbRow)) {
throw new Daiquiri_Exception_NotFound();
}
if ($dbRow['user_id'] !== Daiquiri_Auth::getInstance()->getCurrentId()) {
throw new Daiquiri_Exception_Forbidden();
}
// create return array
$job = array('time' => $dbRow[$this->getResource()->getTimeField()], 'additional' => array());
foreach (array('id', 'database', 'table', 'status', 'error', 'username', 'timeQueue', 'timeQuery') as $key) {
if (isset($dbRow[$key])) {
$job[$key] = $dbRow[$key];
}
unset($dbRow[$key]);
}
// extract query, throw away the plan, if one is there
$queryArray = explode('-- The query plan used to run this query: --', $dbRow['query']);
$job['query'] = $queryArray[0];
unset($dbRow['query']);
// format plan
if (isset($queryArray[1])) {
$plan = str_replace("--------------------------------------------\n--\n--", '', $queryArray[1]);
$plan = trim($plan);
$plan = str_replace("\n-- ", ";\n", $plan);
$job['plan'] = $plan;
}
// get actial query if there is one
if (isset($dbRow['actualQuery'])) {
$job['actualQuery'] = str_replace("; ", ";\n", $dbRow['actualQuery']);
unset($dbRow['actualQuery']);
}
// fetch table statistics
if ($job['status'] == 'success') {
$stat = $this->getResource()->fetchTableStats($job['database'], $job['table']);
} else {
$stat = array();
}
// ret row count
if (isset($stat['tbl_row'])) {
$job['nrows'] = $stat['tbl_row'];
unset($stat['tbl_row']);
}
// create additional array
$translations = $this->getResource()->getTranslations();
foreach (array_merge($dbRow, $stat) as $key => $value) {
$job['additional'][] = array('key' => $key, 'name' => $translations[$key], 'value' => $value);
}
// add columns if the job was a success
if ($job['status'] == 'success') {
$descResource = new Data_Model_Resource_Description();
$descResource->init($job['database']);
$tableMeta = $descResource->describeTable($job['table']);
$job['cols'] = $tableMeta['columns'];
}
return array('job' => $job, 'status' => 'ok');
}
