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); } } }
/** * Creates a downloadable file from the given table of the users database * @param string $table table in the users database * @param string $suffix * @return array $response */ private function _createDownloadFile($table, $format, $regen = false) { // sanity check for format if (!in_array($format, Daiquiri_Config::getInstance()->query->download->adapter->enabled->toArray())) { throw new Exception('Error: format not valid.'); } // create link and file sysytem path for table dump $username = Daiquiri_Auth::getInstance()->getCurrentUsername(); $suffix = Daiquiri_Config::getInstance()->query->download->adapter->config->{$format}->suffix; $filename = $this->_generateFileName($table, $suffix); $url = '/query/download/file?table=' . $table . '&format=' . $format; $dir = Daiquiri_Config::getInstance()->query->download->dir . DIRECTORY_SEPARATOR . $username; $file = $dir . DIRECTORY_SEPARATOR . $filename; // get queue type and validate $queueType = strtolower(Daiquiri_Config::getInstance()->query->download->type); if ($queueType !== "direct" and $queueType !== "gearman") { throw new Exception('Download queue type not valid'); } // create dir if neccessary if (!is_dir($dir)) { if (mkdir($dir) === false) { return array('status' => 'error', 'error' => 'Configuration of download directory wrong, please contact support.'); } chmod($dir, 0775); } // delete the old file if regen is set if ($regen === true) { if (file_exists($file . ".lock")) { throw new Daiquiri_Exception_Forbidden(); } // delete the files... if (file_exists($file)) { unlink($file); } if (file_exists($file . ".err")) { unlink($file . ".err"); } } if (!file_exists($file) && ($queueType === "direct" || empty($queueType))) { //get the user db name $username = Daiquiri_Auth::getInstance()->getCurrentUsername(); $db = Daiquiri_Config::getInstance()->getUserDbName($username); // get the resource and create dump $resource = new Data_Model_Resource_Viewer(); $resource->init($db, $table); try { $resource->dumpTable($format, $file); } catch (Exception $e) { return array('status' => 'error', 'error' => array('form' => $e->getMessage() . ' Please contact support.')); } } if ((!file_exists($file) || file_exists($file . ".lock")) && $queueType === "gearman") { // check if gearman is up and running exec('pgrep gearmand', $output, $return); if ($return != 0) { throw new Exception('gearmand is not running.'); } // check if $restartGeamanManager = false; $pidfile = Daiquiri_Config::getInstance()->query->download->gearman->pid; if (file_exists($pidfile)) { $pid = file_get_contents($pidfile); exec('ps -p ' . $pid, $output, $return); if ($return != 0) { $restartGeamanManager = true; } } else { $restartGeamanManager = true; } if ($restartGeamanManager) { // check if we have write access to actually create this PID file if (!is_writable(dirname(Daiquiri_Config::getInstance()->query->download->gearman->pid))) { return array('status' => 'error', 'error' => 'Cannot write to the gearman PID file, please contact support.'); } $gearmanConf = Daiquiri_Config::getInstance()->query->download->gearman; // not there, start GearmanManager $cmd = escapeshellcmd($gearmanConf->manager) . ' -d' . ' -D ' . escapeshellcmd($gearmanConf->numThread) . ' -h ' . escapeshellcmd($gearmanConf->host) . ':' . escapeshellcmd($gearmanConf->port) . ' -P ' . escapeshellcmd($gearmanConf->pid) . ' -w ' . escapeshellcmd($gearmanConf->workerDir) . ' -r 1 > /tmp/Daiquiri_GearmanManager.log &'; shell_exec($cmd); // DOES NOT WORK IN NEWER PHP, NEED TO BE FIXED // http://stackoverflow.com/questions/12322811/call-time-pass-by-reference-has-been-removed // check if pid exists, if not, an error occured - wait for 10 seconds to start gearman manager $count = 0; while (!file_exists($gearmanConf->pid)) { $count += 1; sleep(1); if ($count > 10) { throw new Exception('Error: Could not start GearmanManager.'); } } } // check if lockfile is present and if not, create if (!file_exists($file . ".lock")) { if (file_exists($file . ".err")) { return array('status' => 'error', 'error' => 'An error file exists on the server, please contact support.'); } // write lock file touch($file . ".lock"); // get the user db name $username = Daiquiri_Auth::getInstance()->getCurrentUsername(); $db = Daiquiri_Config::getInstance()->getUserDbName($username); // get the resource and create dump $resource = new Data_Model_Resource_Viewer(); $resource->init($db, $table); try { $resource->dumpTableGearman($format, $file); } catch (Exception $e) { unlink($file . ".lock"); return array('status' => 'error', 'error' => array('form' => $e->getMessage() . ' Please contact support.')); } return array('status' => 'pending', 'format' => $format); } else { return array('status' => 'pending', 'format' => $format); } } return array('status' => 'ok', 'link' => Daiquiri_Config::getInstance()->getSiteUrl() . $url, 'format' => $format); }
/** * 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'); }