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