/**
* Constructor. Sets resource object.
*/
public function __construct()
{
$this->setResource(Query_Model_Resource_AbstractQuery::factory());
$this->_cols = array('id', 'database', 'table', $this->getResource()->getTimeField(), 'status');
$resourceClass = get_class($this->getResource());
if ($resourceClass::$hasQueues) {
$this->_cols[] = 'queue';
}
}
/**
* Querys the database with a plain text query.
* @param string $sql the sql string
* @param bool $plan flag for plan creation
* @param string $table result table
* @param array $options for further options that are handeled by the queue
* @return array $response
*/
public function query($sql, $plan = false, $table, $options = array())
{
// init error array
$errors = array();
// check if there is a name for the new table
if (empty($table)) {
$tablename = false;
} else {
$tablename = $table;
}
// get group of the user
$usrGrp = Daiquiri_Auth::getInstance()->getCurrentRole();
if ($usrGrp !== null) {
$options['usrGrp'] = $usrGrp;
} else {
$options['usrGrp'] = "guest";
}
// if plan type direct, obtain query plan
if ($this->_processor->supportsPlanType("QPROC_SIMPLE") === true and $plan === false) {
$plan = $this->_processor->getPlan($sql, $errors);
if (!empty($errors)) {
return array('status' => 'error', 'errors' => $errors);
}
} else {
// if plan type is AlterPlan and no plan is available, throw error
if ($this->_processor->supportsPlanType("QPROC_ALTERPLAN") === true and $plan === false) {
$errors['planError'] = 'Query plan required. If you end up here, something went badly wrong';
return array('status' => 'error', 'errors' => $errors);
}
// split plan into lines
$processing = new Query_Model_Resource_Processing();
$noMultilineCommentSQL = $processing->removeMultilineComments($plan);
$multiLines = $processing->splitQueryIntoMultiline($noMultilineCommentSQL, $errors);
$plan = $multiLines;
}
// process sql string
$job = $this->_processor->query($sql, $errors, $plan, $tablename);
if (!empty($errors)) {
return array('status' => 'error', 'errors' => $errors);
}
// before submission, see if user has enough quota
if ($this->_checkQuota($this->_queue, $usrGrp)) {
$errors['quotaError'] = 'Your quota has been reached. Drop some tables to free space or contact the administrators';
return array('status' => 'error', 'errors' => $errors);
}
// submit job
$statusId = $this->_queue->submitJob($job, $errors, $options);
if (!empty($errors)) {
return array('status' => 'error', 'errors' => $errors);
}
// return with success
return array('status' => 'ok', 'job' => $job);
}
/**
* Construtor.
*/
public function __construct()
{
parent::__construct();
// get parallel query obejct
$this->_paraQuery = new ParallelQuery();
// get daiquiri config instance
$config = Daiquiri_Config::getInstance();
// check if server is a PaQu enabled server...
$adapter = Daiquiri_Config::getInstance()->getUserDbAdapter();
// check if PaQu spider plugin is installed.
$adapter->setFetchMode(Zend_Db::FETCH_ASSOC);
$pluginAvail = $adapter->fetchAll('SELECT name FROM mysql.func WHERE name="spider_bg_direct_sql";');
if (empty($pluginAvail) || $pluginAvail[0]['name'] !== "spider_bg_direct_sql") {
// throw new Exception('PaQu spider engine setup not correct.');
}
// set options in parallel query object
$this->_paraQuery->setEngine($config->query->userDb->engine);
$this->_paraQuery->setDB($config->query->scratchdb);
$this->_paraQuery->setConnectOnServerSite($config->query->processor->paqu->serverConnectStr);
$this->_paraQuery->setSpiderUsr($config->query->processor->paqu->spiderUsr);
$this->_paraQuery->setSpiderPwd($config->query->processor->paqu->spiderPwd);
// set the tables that only reside on the head node
$listOfHeadNodeTables = array();
// all tables in the user db are head node tables, therefore add the database name and a list
// of all tables in the database
$listOfHeadNodeTables[] = $this->_userDb;
// get list of tables in user database
$resource = Query_Model_Resource_AbstractQuery::factory();
$stmt = $resource->getAdapter()->query('SHOW TABLES;');
foreach ($stmt->fetchAll() as $row) {
$listOfHeadNodeTables[] = array_shift($row);
}
$this->_paraQuery->setHeadNodeTables($listOfHeadNodeTables);
if (empty($config->query->processor->paqu->federated)) {
$this->_paraQuery->setFedEngine("FEDERATED");
} else {
$this->_paraQuery->setFedEngine($config->query->processor->paqu->federated);
}
}
/**
* Given a table name, check if it already exists in the user database
* @param table name
* @return true or false
*/
public function tableExists($table)
{
$resource = Query_Model_Resource_AbstractQuery::factory();
$sql = "SHOW TABLES LIKE '{$table}';";
try {
$stmt = $resource->getAdapter()->query($sql);
} catch (Exception $e) {
//check if this is error 1051 Unknown table
if (strpos($e->getMessage(), "1051") === false) {
throw $e;
}
}
$rows = $stmt->fetchAll();
if (sizeof($rows) > 0) {
return true;
}
return false;
}
/**
* Submits a new query to the database.
* @param string $formstring name of the form to use
* @param array $formParams
* @return array $response
*/
public function submit($formstring, array $formParams = array())
{
// get the formclass
$formConfig = Daiquiri_Config::getInstance()->query->forms->{$formstring};
if ($formConfig === null || get_Class($formConfig) !== 'Zend_Config') {
throw new Exception('form options not found');
} else {
$formOptions = $formConfig->toArray();
$formOptions['name'] = $formstring;
}
// get queues
$resource = Query_Model_Resource_AbstractQuery::factory();
$queues = array();
$defaultQueue = false;
if ($resource::$hasQueues === true) {
try {
$queues = $resource->fetchQueues();
$defaultQueue = $resource->fetchDefaultQueue();
} catch (Exception $e) {
return array('status' => 'error');
}
$usrGrp = Daiquiri_Auth::getInstance()->getCurrentRole();
foreach ($queues as $key => $value) {
// show only the guest queue for the guest user:
if ($value['name'] !== "guest" && $usrGrp === "guest") {
unset($queues[$key]);
}
// remove the guest queue if this is a non guest user
if ($value['name'] === "guest" && $usrGrp !== "guest") {
unset($queues[$key]);
}
}
}
// get the form
$form = new $formConfig->class(array('formOptions' => $formOptions, 'queues' => $queues, 'defaultQueue' => $defaultQueue));
// init errors array
$errors = array();
// validate form
if (!empty($formParams)) {
if ($form->isValid($formParams)) {
// form is valid, get sql string from functions
$sql = $form->getQuery();
$tablename = $form->getTablename();
$queueId = $form->getQueue();
//clean from default flag
$queueId = str_replace("_def", "", $queueId);
if (empty($tablename)) {
$tablename = null;
}
$options = array();
if (!empty($queueId)) {
$options['queue'] = $queues[$queueId]['name'];
}
// validate query
$model = new Query_Model_Query();
if ($model->validate($sql, false, $tablename, $errors) !== true) {
// set description for form
$form->setDescription(implode('; ', $errors));
// construct response array
return array('form' => $form, 'formOptions' => $formOptions, 'status' => 'error', 'errors' => array('form' => $errors));
}
// take a detour to the query plan
if ($model->canShowPlan()) {
// store query, tablename and queue in session
Zend_Session::namespaceUnset('query_plan');
$ns = new Zend_Session_Namespace('query_plan');
$ns->sql = $sql;
$ns->tablename = $tablename;
if (isset($options['queue'])) {
$ns->queue = $options['queue'];
} else {
$ns->queue = null;
}
$ns->plan = $model->plan($sql, $errors);
if (!empty($errors)) {
return $this->getModelHelper('CRUD')->validationErrorResponse($form, $errors);
}
// construct response with redirect to plan
$baseurl = Daiquiri_Config::getInstance()->getSiteUrl();
return array('status' => 'plan', 'redirect' => $baseurl . '/query/form/plan?form=' . $formstring);
} else {
// submit query
$response = $model->query($sql, false, $tablename, $options);
if ($response['status'] === 'ok') {
// submitting the query was successful
return $response;
} else {
// set description for form
$form->setDescription(implode('; ', $response['errors']));
// construct response array
return array('form' => $form, 'formOptions' => $formOptions, 'status' => 'error', 'errors' => array('form' => $response['errors']));
}
}
} else {
return array('form' => $form, 'formOptions' => $formOptions, 'status' => 'error', 'errors' => $form->getMessages());
}
}
return array('form' => $form, 'formOptions' => $formOptions, 'status' => 'form');
}
/**
* Removes a job.
* @param type $id job id
* @param array $formParams
* @return array $response
*/
public function removeJob($id, array $formParams = array())
{
// set job resource
$this->setResource(Query_Model_Resource_AbstractQuery::factory());
// get job and check permissions
$row = $this->getResource()->fetchRow($id);
if (empty($row) || $row['user_id'] !== Daiquiri_Auth::getInstance()->getCurrentId()) {
throw new Daiquiri_Exception_NotFound();
}
// create the form object
$form = new Daiquiri_Form_Danger(array('submit' => 'Remove job'));
// valiadate the form if POST
if (!empty($formParams)) {
if ($form->isValid($formParams)) {
try {
$this->getResource()->removeJob($id);
return array('status' => 'ok');
} catch (Exception $e) {
return $this->getModelHelper('CRUD')->validationErrorResponse($form, $e->getMessage());
}
} else {
return $this->getModelHelper('CRUD')->validationErrorResponse($form);
}
}
return array('form' => $form, 'status' => 'form');
}
public function runJob(Uws_Model_Resource_JobSummaryType &$job)
{
// obtain queue information
$resource = Query_Model_Resource_AbstractQuery::factory();
$queues = array();
if ($resource::$hasQueues === true && isset($job->parameters['queue'])) {
$queues = $resource->fetchQueues();
// find the queue
foreach ($queues as $queue) {
if ($queue['name'] === $job->parameters['queue']->value) {
$job->executionDuration = $queue['timeout'];
break;
}
}
} else {
if ($resource::$hasQueues === true) {
// no queue has been specified, but we support queues - if executionDuration is 0, use default queue
// otherwise find the desired queue
$queues = $resource->fetchQueues();
if ($job->executionDuration === 0) {
// use default queue here
$queue = Daiquiri_Config::getInstance()->query->query->qqueue->defaultQueue;
foreach ($queues as $currQueue) {
if ($currQueue['name'] === $queue) {
$job->executionDuration = $currQueue['timeout'];
$job->addParameter("queue", $currQueue['name']);
break;
}
}
} else {
// find a queue that matches the request (i.e. is nearest to the request)
$maxQueueTimeout = 0;
$maxQueue = false;
$deltaQueue = 9.999999999999999E+33;
$queue = false;
foreach ($queues as $currQueue) {
if ($currQueue['timeout'] > $maxQueue) {
$maxQueueTimeout = $currQueue['timeout'];
$maxQueue = $currQueue;
}
if ($currQueue['timeout'] >= $job->executionDuration) {
$currDelta = $currQueue['timeout'] - $job->executionDuration;
if ($currDelta < $deltaQueue) {
$queue = $currQueue;
$deltaQueue = $currDelta;
}
}
}
if ($queue === false) {
$queue = $maxQueue;
}
$job->addParameter("queue", $currQueue['name']);
$job->executionDuration = $currQueue['timeout'];
}
}
}
// now check if everything is there that we need...
$tablename = null;
$sql = null;
$queue = null;
$errors = array();
if (!isset($job->parameters['query']) || $resource::$hasQueues === true && !isset($job->parameters['queue'])) {
// throw error
$job->addError("Incomplete job");
$resource = new Uws_Model_Resource_UWSJobs();
$resource->updateRow($job->jobId, array("phase" => "ERROR", "errorSummary" => Zend_Json::encode($job->errorSummary)));
return;
}
if (isset($job->parameters['table'])) {
$tablename = $job->parameters['table']->value;
}
$sql = $job->parameters['query']->value;
if ($resource::$hasQueues === true) {
$queue = $job->parameters['queue']->value;
}
// submit job
// validate query
$job->resetErrors();
$model = new Query_Model_Query();
try {
if ($model->validate($sql, false, $tablename, $errors) !== true) {
//throw error
foreach ($errors as $error) {
$job->addError($error);
}
$resource = new Uws_Model_Resource_UWSJobs();
$resource->updateRow($job->jobId, array("phase" => "ERROR", "errorSummary" => Zend_Json::encode($job->errorSummary)));
return;
}
} catch (Exception $e) {
// throw error
$job->addError($e->getMessage());
$resource = new Uws_Model_Resource_UWSJobs();
$resource->updateRow($job->jobId, array("phase" => "ERROR", "errorSummary" => Zend_Json::encode($job->errorSummary)));
return;
}
// submit query
if ($resource::$hasQueues === true) {
$response = $model->query($sql, false, $tablename, array("queue" => $queue, "jobId" => $job->jobId));
} else {
$response = $model->query($sql, false, $tablename, array("jobId" => $job->jobId));
}
if ($response['status'] !== 'ok') {
// throw error
foreach ($response['errors'] as $error) {
$job->addError($error);
}
$resource = new Uws_Model_Resource_UWSJobs();
$resource->updateRow($job->jobId, array("phase" => "ERROR", "errorSummary" => Zend_Json::encode($job->errorSummary)));
return;
}
// clean up stuff (basically just remove the job in the temorary UWS job store - if we are here
// everything has been handeled by the queue)
$resource = new Uws_Model_Resource_UWSJobs();
$resource->deleteRow($job->jobId);
}
