/** * Sets the adapter and the tablename of the resource retroactively. * @param string $database name of the database * @param string $table name of the table */ public function init($database, $table = null) { // get the user adapter $username = Daiquiri_Auth::getInstance()->getCurrentUsername(); // check if this database is the user datasbase if ($database === Daiquiri_Config::getInstance()->getUserDbName($username)) { $adapter = Daiquiri_Config::getInstance()->getUserDbAdapter(); } else { // get the database id and check permission on database $databasesResource = new Data_Model_Resource_Databases(); $result = $databasesResource->checkACL($database, 'select'); if ($result !== true) { throw new Daiquiri_Exception_NotFound(); } // check permission on table access if ($table) { $tablesResource = new Data_Model_Resource_Tables(); $result = $tablesResource->checkACL($database, $table, 'select'); if ($result !== true) { throw new Daiquiri_Exception_NotFound(); } } // if everything went ok get adapter $adapter = Daiquiri_Config::getInstance()->getUserDbAdapter($database); } // set adapter and table $this->setAdapter($adapter); if ($table) { $this->setTablename($table); } }
/** * 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); }
/** * @brief checkDbTable method - checks whether user has access to a given database * and table * @param $database: database name * @param $table: table name * @param $permission: the desired permission * @return TRUE or FALSE * * Checks whether the user has access to the given database and table with the desired * permission. This uses the Data module for ACLing of the databases and tables. The information * stored in the database meta data store is needed for this. */ public function checkDbTable($database, $table, $permission) { // switch of security for debugging if (Daiquiri_Config::getInstance()->auth->debug === '1') { return true; } // check if this is the users database $userDB = Daiquiri_Config::getInstance()->getUserDbName($this->getCurrentUsername()); if ($database === $userDB) { return true; } // check in the data module first, if metadata exists and handle them // accordingly $databasesResource = new Data_Model_Resource_Databases(); if ($databasesResource->checkACL($database, $permission)) { if ($table === false) { return true; } else { // access to database granted, so let's check for table access $tablesResource = new Data_Model_Resource_Tables(); if ($tablesResource->checkACL($database, $table, $permission)) { return true; } } } // scratch database has read access $scratchDB = Daiquiri_Config::getInstance()->query->scratchdb; if (!empty($scratchDB) && $database === $scratchDB && ($permission === "select" || $permission === "set")) { return true; } return false; }
/** * Updates a column entry. * @param mixed $input int id or array with "db","table" and "column" keys * @param array $formParams * @return array $response */ public function update($input, array $formParams = array()) { if (is_int($input)) { $entry = $this->getResource()->fetchRow($input); } elseif (is_array($input)) { if (empty($input['db']) || empty($input['table']) || empty($input['column'])) { throw new Exception('Either int id or array with "db","table" and "column" keys must be provided as $input'); } $entry = $this->getResource()->fetchRowByName($input['db'], $input['table'], $input['column']); } else { throw new Exception('$input has wrong type.'); } if (empty($entry)) { throw new Daiquiri_Exception_NotFound(); } // get tables and ucds $tablesResource = new Data_Model_Resource_Tables(); $ucdsResource = new Daiquiri_Model_Resource_Table(); $ucdsResource->setTablename('Data_UCD'); // get roles $roles = array_merge(array(0 => 'not published'), Daiquiri_Auth::getInstance()->getRoles()); $form = new Data_Form_Columns(array('tables' => $tablesResource->fetchValues('name'), 'tableId' => $entry['table_id'], 'ucds' => $ucdsResource->fetchRows(), 'roles' => $roles, 'submit' => 'Update column entry', 'entry' => $entry)); // valiadate the form if POST if (!empty($formParams)) { if ($form->isValid($formParams)) { // get the form values $values = $form->getValues(); unset($values['ucd_list']); // check if the order needs to be set to NULL if ($values['order'] === '') { $values['order'] = NULL; } $values['database'] = $entry['database']; $values['table'] = $entry['table']; try { $this->getResource()->updateRow($entry['id'], $values); } catch (Exception $e) { return $this->getModelHelper('CRUD')->validationErrorResponse($form, $e->getMessage()); } return array('status' => 'ok'); } else { return $this->getModelHelper('CRUD')->validationErrorResponse($form); } } return array('form' => $form, 'status' => 'form'); }
/** * 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; }
function _parseSqlAll_getColsDaiquiri(&$sqlTree, &$node, $zendAdapter, $table, $alias) { $resParts = $this->_parseSqlAll_parseResourceName($table); // process the alias name $aliasParts = $this->_parseSqlAll_parseResourceName($alias); unset($aliasParts[0]); $aliasName = ""; foreach ($aliasParts as $part) { if ($aliasName === "") { $aliasName .= "`" . $part . "`"; } else { $aliasName .= ".`" . $part . "`"; } } // check if the given table resource is composed of DATABASE.TABLE if (count($resParts) !== 3) { throw new Exception("Cannot resolve table columns, table name is not valid."); } // check if this is a table of the user database $username = Daiquiri_Auth::getInstance()->getCurrentUsername(); if ($resParts[1] === Daiquiri_Config::getInstance()->getUserDbName($username)) { $resource = new Data_Model_Resource_Viewer(); $resource->init($resParts[1], $resParts[2]); $tableData = array('columns' => array()); foreach ($resource->fetchCols() as $col => $value) { if ($col !== 'row_id') { $tableData['columns'][] = array('name' => $col); } } } else { $tableResource = new Data_Model_Resource_Tables(); $tableData = $tableResource->fetchRowByName($resParts[1], $resParts[2], true); } if (empty($tableData)) { throw new Exception("Table {$table} does not exist."); } foreach ($tableData['columns'] as $count => $row) { if ($count == 0) { // this is the item we change if ($alias === false || empty($alias)) { $node['base_expr'] = "`" . $row['name'] . "`"; $node['no_quotes'] = array("delim" => ".", "parts" => array($row['name'])); } else { $node['base_expr'] = $aliasName . ".`" . $row['name'] . "`"; $node['no_quotes'] = array("delim" => ".", "parts" => array_merge($aliasParts, array($row['name']))); $node['alias'] = array("as" => true, "name" => "`" . str_replace(".", "__", str_replace("`", "", $node['base_expr'])) . "`", "base_expr" => "as `" . str_replace(".", "__", str_replace("`", "", $node['base_expr'])) . "`", "no_quotes" => array("delim" => ".", "parts" => array(str_replace(".", "__", str_replace("`", "", $node['base_expr']))))); } $node['delim'] = ","; $nodeTemplate = $node; array_push($sqlTree['SELECT'], $node); } else { $newNode = $nodeTemplate; // this is set on the first passing when count is 0 if ($alias === false || empty($alias)) { $newNode['base_expr'] = "`" . $row['name'] . "`"; $newNode['no_quotes'] = array("delim" => ".", "parts" => array($row['name'])); } else { $newNode['base_expr'] = $aliasName . ".`" . $row['name'] . "`"; $newNode['no_quotes'] = array("delim" => ".", "parts" => array_merge($aliasParts, array($row['name']))); $newNode['alias'] = array("as" => true, "name" => "`" . str_replace(".", "__", str_replace("`", "", $newNode['base_expr'])) . "`", "base_expr" => "as `" . str_replace(".", "__", str_replace("`", "", $newNode['base_expr'])) . "`", "no_quotes" => array("delim" => ".", "parts" => array(str_replace(".", "__", str_replace("`", "", $newNode['base_expr']))))); } array_push($sqlTree['SELECT'], $newNode); } } }
/** * Inserts one column entry. 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 (array_key_exists('comment', $data)) { $comment = $data['comment']; unset($data['comment']); } if (array_key_exists('database', $data)) { $database = $data['database']; unset($data['database']); } if (array_key_exists('table', $data)) { $table = $data['table']; unset($data['table']); } // store the values in the database $this->getAdapter()->insert('Data_Columns', $data); $id = $this->getAdapter()->lastInsertId(); if (Daiquiri_Config::getInstance()->data->writeToDB) { // get information about the table from the the input or the table resource if (isset($database) && isset($table)) { $tableData = array('database' => $database, 'name' => $table); } else { $tableResource = new Data_Model_Resource_Tables(); $tableData = $tableResource->fetchRow($data['table_id']); } unset($data['table_id']); if (isset($comment)) { $this->_writeColumnComment($tableData['database'], $tableData['name'], $data['name'], $data, $comment); } else { $this->_writeColumnComment($tableData['database'], $tableData['name'], $data['name'], $data); } } }