/** * 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'); }