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