/**
* 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);
}
/**
* Creates a new table in the database with the given sql query.
* SIDE EFFECT: changes $job array and fills in the missing data
* @param array $job object that hold information about the query
* @param array $errors holding any error that occurs
* @param array $options any options that a specific implementation of submitJob needs to get
* @return int $status
*/
public function submitJob(&$job, array &$errors, $options = false)
{
// switch to user adapter
$this->setAdapter(Daiquiri_Config::getInstance()->getUserDbAdapter());
// get adapter config
$config = $this->getAdapter()->getConfig();
// get tablename
$table = $job['table'];
// check if the table already exists
if ($this->_tableExists($table)) {
$errors['submitError'] = "Table '{$table}' already exists";
return false;
}
// create the actual sql statement
$actualQuery = $job['fullActualQuery'];
unset($job['fullActualQuery']);
// fire up the database
// determining the DB adapter that is used. if we have thought about that one, use direct querying
// without using prepared statement (not that fast and uses memory)
// if not, fall back to prepared statements querying (using adapter->query abstractions of ZEND)
$adaptType = get_class($this->getAdapter());
// if query syntax is checked server side without executing query (like using paqu_validateSQL in MySQL),
// we just fire up the query. if not, we need to split multiline queries up and check for any exception
// raised by the server
if (Daiquiri_Config::getInstance()->query->validate->serverSide) {
if (strpos(strtolower($adaptType), "pdo") !== false) {
try {
$stmt = $this->getAdapter()->getConnection()->exec($actualQuery);
} catch (Exception $e) {
$errors['submitError'] = $e->getMessage();
}
} else {
// fallback version
try {
$stmt = $this->getAdapter()->query($actualQuery);
} catch (Exception $e) {
$errors['submitError'] = $e->getMessage();
}
$stmt->closeCursor();
}
} else {
// split the query into multiple queries...
$processing = new Query_Model_Resource_Processing();
$multiLine = $processing->splitQueryIntoMultiline($actualQuery, $errors);
foreach ($multiLine as $query) {
if (strpos(strtolower($adaptType), "pdo") !== false) {
try {
$stmt = $this->getAdapter()->getConnection()->exec($query);
} catch (Exception $e) {
$errors['submitError'] = $e->getMessage();
break;
}
} else {
try {
$stmt = $this->getAdapter()->query($query);
} catch (Exception $e) {
$errors['submitError'] = $e->getMessage();
break;
}
}
}
if (strpos(strtolower($adaptType), "pdo") === false) {
$stmt->closeCursor();
}
}
// if error has been raised just report it and don't add a job
if (!empty($errors)) {
return Query_Model_Resource_DirectQuery::$_status['error'];
}
// switch to user adapter (it could have been changed by the query, due to a "USE" statement)
$this->setAdapter(Daiquiri_Config::getInstance()->getUserDbAdapter());
// check if it worked
if (in_array($table, $this->getAdapter()->listTables())) {
// set status
$statusId = Query_Model_Resource_DirectQuery::$_status['success'];
} else {
$statusId = Query_Model_Resource_DirectQuery::$_status['error'];
}
if (!empty($options) && array_key_exists('jobId', $options)) {
$job['id'] = "{$options['jobId']}";
}
$job['database'] = $config['dbname'];
$job['host'] = $config['host'];
$job['time'] = date("Y-m-d\\TH:i:s");
$job['user_id'] = Daiquiri_Auth::getInstance()->getCurrentId();
$job['status_id'] = $statusId;
// switch to web adapter
$this->setAdapter(Daiquiri_Config::getInstance()->getWebAdapter());
// insert job into jobs table
$this->getAdapter()->insert('Query_Jobs', $job);
// get Id of the new job
$job['id'] = $this->getAdapter()->lastInsertId();
// get username and status
$statusStrings = array_flip(Query_Model_Resource_DirectQuery::$_status);
$job['status'] = $statusStrings[$statusId];
$job['username'] = Daiquiri_Auth::getInstance()->getCurrentUsername();
return $statusId;
}