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); } } }
/** * Returns all databases and tables which the user has access to. * @return array $response */ public function databases() { // get all databases from database model $databasesModel = new Data_Model_Databases(); $rows = array(); foreach ($databasesModel->getResource()->fetchRows() as $row) { $database = $databasesModel->getResource()->fetchRow($row['id'], true, true); $database['publication_role'] = Daiquiri_Auth::getInstance()->getRole($database['publication_role_id']); foreach ($database['tables'] as $key => $table) { $database['tables'][$key]['publication_role'] = Daiquiri_Auth::getInstance()->getRole($table['publication_role_id']); } $rows[] = $database; } // check permissions and build array $databases = array(); foreach ($rows as $database) { if (Daiquiri_Auth::getInstance()->checkPublicationRoleId($database['publication_role_id'])) { $db = array('id' => $database['id'], 'name' => $database['name'], 'value' => $databasesModel->getResource()->quoteIdentifier($database['name']), 'tooltip' => $database['description'], 'tables' => array()); foreach ($database['tables'] as $table) { if (Daiquiri_Auth::getInstance()->checkPublicationRoleId($table['publication_role_id'])) { $t = array('id' => $table['id'], 'name' => $table['name'], 'value' => $databasesModel->getResource()->quoteIdentifier($database['name'], $table['name']), 'tooltip' => $table['description'], 'columns' => array()); foreach ($table['columns'] as $column) { $tooltip = array(); if (!empty($column['description'])) { $tooltip[] = $column['description']; } if (!empty($column['type'])) { $tooltip[] = "<i>Type:</i> {$column['type']}"; } if (!empty($column['unit'])) { $tooltip[] = "<i>Unit:</i> {$column['unit']}"; } if (!empty($column['ucd'])) { $tooltip[] = "<i>UCD:</i> {$column['ucd']}"; } $t['columns'][] = array('id' => $column['id'], 'name' => $column['name'], 'value' => $databasesModel->getResource()->quoteIdentifier($column['name']), 'tooltip' => implode('<br />', $tooltip)); } $db['tables'][] = $t; } } $databases[] = $db; } } // get current username and the user db $username = Daiquiri_Auth::getInstance()->getCurrentUsername(); $userDbName = Daiquiri_Config::getInstance()->getUserDbName($username); // prepare auto increment counters $table_id = 1; $column_id = 1; // prepate userdb array $userdb = array('id' => 'userdb', 'name' => $userDbName, 'value' => $databasesModel->getResource()->quoteIdentifier($userDbName), 'tooltip' => 'Your personal database', 'tables' => array()); // get tables of this database $resource = new Data_Model_Resource_Viewer(); $resource->init($userdb['name']); $usertables = $resource->fetchTables(); // find all the user tables that are currently open and cannot be queried for information // get the user adapter $adapter = Daiquiri_Config::getInstance()->getUserDbAdapter(); $lockedTables = $adapter->query('SHOW OPEN TABLES IN `' . $userdb['name'] . '` WHERE In_use > 0')->fetchAll(); foreach ($lockedTables as $table) { $key = array_search($table['Table'], $usertables); if ($key !== false) { unset($usertables[$key]); } } foreach ($usertables as $usertable) { $table = array('id' => 'userdb-table-' . $table_id++, 'name' => $usertable, 'value' => $databasesModel->getResource()->quoteIdentifier($userDbName, $usertable), 'columns' => array()); try { $resource->init($userdb['name'], $usertable); } catch (Exception $e) { continue; } $usercolumns = array_keys($resource->fetchCols()); foreach ($usercolumns as $usercolumn) { $table['columns'][] = array('id' => 'userdb-column-' . $column_id++, 'name' => $usercolumn, 'value' => $databasesModel->getResource()->quoteIdentifier($usercolumn)); } $userdb['tables'][] = $table; } $databases[] = $userdb; return array('databases' => $databases, 'status' => 'ok'); }