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