/**
* setAutocommit
*
* @param Zend_Db_Adapter_Abstract $adapter
* @param boolean $on
*/
public static function setAutocommit($adapter, $on)
{
if ($on) {
$adapter->query('SET AUTOCOMMIT=1;');
} else {
$adapter->query('SET AUTOCOMMIT=0;');
}
}
/**
* Should handle execution of the task, taking as much (optional) parameters as needed
*
* The parameters should be optional and failing to provide them should be handled by
* the task
*
* @param int $patchLevel Only execute patches for this patchlevel
*/
public function execute($patchLevel = null)
{
//Update the patchlevel only when we have executed at least one patch
$batch = $this->getBatch();
if ($batch->getCounter('executed')) {
$this->db->query('INSERT IGNORE INTO gems__patch_levels (gpl_level, gpl_created) VALUES (?, CURRENT_TIMESTAMP)', $patchLevel);
}
}
/**
* Inserts a table row with specified data.
*
* @param mixed $table The table to insert data into.
* @param array $bind Column-value pairs.
* @return int The number of affected rows.
*/
function insert($table, array $bind)
{
$sql = $this->insertSql($table, $bind);
// execute the statement and return the number of affected rows
$stmt = $this->wrappedAdapter->query($sql, array_values($bind));
$result = $stmt->rowCount();
return $result;
}
/**
* @param $shadowPath
* @param $newPath
* @param $oldPath
* @param $newShadowPath
* @param $oldShadowPath
*/
public function copyTreeByShadowPath($shadowPath, $newPath, $oldPath, $newShadowPath, $oldShadowPath)
{
$select = $this->_db->select()->from($this->_tablePrefix . $this->_tableName, array('path' => new Zend_Db_Expr($this->_db->quoteInto($this->_db->quoteInto('REPLACE(path, ?', $oldPath) . ', ?)', $newPath)), 'shadow_path' => new Zend_Db_Expr($this->_db->quoteInto($this->_db->quoteInto('REPLACE(shadow_path, ?', $oldShadowPath) . ', ?)', $newShadowPath)), 'record_id' => 'record_id', 'creation_time' => new Zend_Db_Expr('NOW()')))->where($this->_db->quoteInto($this->_db->quoteIdentifier('shadow_path') . ' like ?', $shadowPath . '/%'));
$stmt = $this->_db->query($select);
$entries = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
foreach ($entries as $entry) {
$entry['id'] = Tinebase_Record_Abstract::generateUID();
$this->_db->insert($this->_tablePrefix . $this->_tableName, $entry);
}
}
/**
* return device for this user
*
* @param string $userId
* @param string $deviceId
* @throws Syncope_Exception_NotFound
* @return Syncope_Model_Device
*/
public function getUserDevice($ownerId, $deviceId)
{
$select = $this->_db->select()->from($this->_tablePrefix . 'device')->where('owner_id = ?', $ownerId)->where('deviceid = ?', $deviceId);
$stmt = $this->_db->query($select);
$device = $stmt->fetchObject('Syncope_Model_Device');
if (!$device instanceof Syncope_Model_IDevice) {
throw new Syncope_Exception_NotFound('device not found');
}
return $device;
}
public function __construct()
{
$this->_oConfig = Zend_Registry::get(REGISTRY_CONFIG);
$this->_oDBAdapter = Zend_Db::factory($this->_oConfig->db->adapter, $this->_oConfig->db->config->toArray());
if ($this->_oConfig->db->adapter == 'PDO_MYSQL') {
$this->_oDBAdapter->query("SET NAMES 'utf8'");
$this->_oDBAdapter->query("SET CHARACTER SET utf8");
}
$this->_sPrefix = $this->_oConfig->db->prefix;
}
/**
* import dump in database
* @param $name
* @param null $module
* @throws Zend_Exception
*/
public function import($name, $module = null)
{
$path = $this->getDumpsDirectoryPath($module);
if (file_exists($path . DIRECTORY_SEPARATOR . $name)) {
$dump = file_get_contents($path . DIRECTORY_SEPARATOR . $name);
return $this->_db->query($dump);
} else {
throw new Zend_Exception("Dump file not found!");
}
}
/**
* Test the connection, and if needed, reconnect.
*
* @return void
*/
protected function _test()
{
try {
$this->_adapter->query('SELECT 1');
} catch (Exception $e) {
// there is an error, so we reconnect
$this->_adapter = null;
$this->_connect();
}
}
/**
* Code to run after deleting an associated attachment.
*/
public function attachmentPostDelete(array $attachment, Zend_Db_Adapter_Abstract $db)
{
$db->query('
UPDATE xf_custom_field_attachment
SET attach_count = IF(attach_count > 0, attach_count - 1, 0)
WHERE field_attachment_id = ?
', $attachment['content_id']);
$db->query('
DELETE FROM xf_custom_field_attachment
WHERE field_attachment_id = ? AND attach_count = 0
', $attachment['content_id']);
}
/**
*
* @param string $tableName
* @param array $config
* @return boolean
*/
private function setConfigTable($tableName, $config)
{
$sql = "SELECT utc.comments \n FROM user_tab_comments utc\n WHERE utc.table_name = :tableName\n AND utc.TABLE_TYPE = 'TABLE'";
$rows = $this->_dbAdapter->fetchAll($sql, array('tableName' => $tableName));
if (count($rows) > 0) {
$comments = $rows[0]['comments'];
$strZF = substr($comments, strpos($comments, '<?zf'), strpos($data, 'zf?>') + 4);
$comments = str_replace($strZF, '', $comments);
$comments .= '<?zf ' . Zend_Json::decode($config) . ' zf?>';
$sql = "comment on table " . $tableName . " is :comment ";
$this->_dbAdapter->query($sql, array('comment' => $comments));
return true;
}
}
protected function _bootstrap(array $config)
{
if ($this->_sourceDb) {
// already run
return;
}
@set_time_limit(0);
$this->_config = $config;
$this->_sourceDb = Zend_Db::factory('mysqli', array('host' => $config['db']['host'], 'port' => $config['db']['port'], 'username' => $config['db']['username'], 'password' => $config['db']['password'], 'dbname' => $config['db']['dbname'], 'charset' => str_replace('-', '', $config['db']['charset'])));
if (empty($config['db']['charset'])) {
$this->_sourceDb->query('SET character_set_results = NULL');
}
$this->_prefix = preg_replace('/[^a-z0-9_]/i', '', $config['db']['prefix']);
$this->_charset = $config['charset'];
}
/**
* update foreign key values
*
* @param string $_mode create|update
* @param Tinebase_Record_Interface $_record
*/
protected function _updateForeignKeys($_mode, Tinebase_Record_Interface $_record)
{
if (!empty($this->_foreignTables)) {
foreach ($this->_foreignTables as $modelName => $join) {
if (!(isset($join['field']) || array_key_exists('field', $join))) {
continue;
}
$idsToAdd = array();
$idsToRemove = array();
if (!empty($_record->{$modelName})) {
$idsToAdd = Tinebase_Record_RecordSet::getIdsFromMixed($_record->{$modelName});
}
$transactionId = Tinebase_TransactionManager::getInstance()->startTransaction(Tinebase_Core::getDb());
if ($_mode == 'update') {
$select = $this->_db->select();
$select->from(array($join['table'] => $this->_tablePrefix . $join['table']), array($join['field']))->where($this->_db->quoteIdentifier($join['table'] . '.' . $join['joinOn']) . ' = ?', $_record->getId());
Tinebase_Backend_Sql_Abstract::traitGroup($select);
$stmt = $this->_db->query($select);
$currentIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
$stmt->closeCursor();
$idsToRemove = array_diff($currentIds, $idsToAdd);
$idsToAdd = array_diff($idsToAdd, $currentIds);
}
if (!empty($idsToRemove)) {
$where = '(' . $this->_db->quoteInto($this->_db->quoteIdentifier($this->_tablePrefix . $join['table'] . '.' . $join['joinOn']) . ' = ?', $_record->getId()) . ' AND ' . $this->_db->quoteInto($this->_db->quoteIdentifier($this->_tablePrefix . $join['table'] . '.' . $join['field']) . ' IN (?)', $idsToRemove) . ')';
$this->_db->delete($this->_tablePrefix . $join['table'], $where);
}
foreach ($idsToAdd as $id) {
$recordArray = array($join['joinOn'] => $_record->getId(), $join['field'] => $id);
$this->_db->insert($this->_tablePrefix . $join['table'], $recordArray);
}
Tinebase_TransactionManager::getInstance()->commitTransaction($transactionId);
}
}
}
/**
* Retrive table partical data SQL insert
*
* @param string $tableName
* @param int $count
* @param int $offset
* @return string
*/
public function getTableDataSql($tableName, $count, $offset = 0)
{
$sql = null;
$quotedTableName = $this->_read->quoteIdentifier($tableName);
$select = $this->_read->select()->from($tableName)->limit($count, $offset);
$query = $this->_read->query($select);
while ($row = $query->fetch()) {
if (is_null($sql)) {
$sql = 'INSERT INTO ' . $quotedTableName . ' VALUES ';
} else {
$sql .= ',';
}
//$sql .= $this->_read->quoteInto('(?)', $row);
$rowData = array();
foreach ($row as $v) {
if (is_null($v)) {
$value = 'NULL';
} elseif (is_numeric($v) && $v == intval($v)) {
$value = $v;
} else {
$value = $this->_read->quoteInto('?', $v);
}
$rowData[] = $value;
}
$sql .= '(' . join(',', $rowData) . ')';
}
if (!is_null($sql)) {
$sql .= ';' . "\n";
}
return $sql;
}
/**
* @param string|int $eId
* @param string|int $pId
* @param string|int $aId
* @return void
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
* @SuppressWarnings(PHPMD.NPathComplexity)
* @SuppressWarnings(PHPMD.ExcessiveMethodLength)
* @SuppressWarnings(PHPMD.UnusedLocalVariable)
* @SuppressWarnings(PHPMD.ExitExpression)
*/
public function moveNodes($eId, $pId, $aId = 0)
{
$eInfo = $this->getNodeInfo($eId);
if ($pId != 0) {
$pInfo = $this->getNodeInfo($pId);
}
if ($aId != 0) {
$aInfo = $this->getNodeInfo($aId);
}
$level = $eInfo[$this->_level];
$leftKey = $eInfo[$this->_left];
$rightKey = $eInfo[$this->_right];
if ($pId == 0) {
$levelUp = 0;
} else {
$levelUp = $pInfo[$this->_level];
}
$rightKeyNear = 0;
$leftKeyNear = 0;
if ($pId == 0) {
//move to root
$rightKeyNear = $this->_db->fetchOne('SELECT MAX(' . $this->_right . ') FROM ' . $this->_table);
} elseif ($aId != 0 && $pId == $eInfo[$this->_pid]) {
// if we have after ID
$rightKeyNear = $aInfo[$this->_right];
$leftKeyNear = $aInfo[$this->_left];
} elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) {
// if we do not have after ID
$rightKeyNear = $pInfo[$this->_left];
} elseif ($pId != $eInfo[$this->_pid]) {
$rightKeyNear = $pInfo[$this->_right] - 1;
}
$skewLevel = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
$skewTree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
echo "alert('" . $rightKeyNear . "');";
if ($rightKeyNear > $rightKey) {
// up
echo "alert('move up');";
$skewEdit = $rightKeyNear - $leftKey + 1;
$sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_right . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' < ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewTree . ', ' . $this->_right . ')), ' . $this->_level . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_left . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKeyNear . ', ' . $this->_left . ' + ' . $skewTree . ', ' . $this->_left . '))' . ' WHERE ' . $this->_right . ' > ' . $rightKeyNear . ' AND ' . $this->_left . ' < ' . $eInfo[$this->_right];
} elseif ($rightKeyNear < $rightKey) {
// down
echo "alert('move down');";
$skewEdit = $rightKeyNear - $leftKey + 1 - $skewTree;
$sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_left . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKey . ', ' . $this->_left . ' - ' . $skewTree . ', ' . $this->_left . ')), ' . $this->_level . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_right . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' <= ' . $rightKeyNear . ', ' . $this->_right . ' - ' . $skewTree . ', ' . $this->_right . '))' . ' WHERE ' . $this->_right . ' > ' . $leftKey . ' AND ' . $this->_left . ' <= ' . $rightKeyNear;
}
$this->_db->beginTransaction();
try {
$this->_db->query($sql);
$this->_db->commit();
} catch (\Exception $e) {
$this->_db->rollBack();
echo $e->getMessage();
echo "<br>\r\n";
echo $sql;
echo "<br>\r\n";
exit;
}
echo "alert('node added')";
}
/**
* Create Schema
*
* @throws \InvalidArgumentException
* @return AdapterInterface
*/
public function createSchema()
{
$sql = $this->createStatement;
if ($sql === null) {
switch (get_class($this->adapter)) {
case 'Zend_Db_Adapter_Pdo_Mssql':
$createStatement = static::MSSQL_CREATE_STATEMENT;
break;
case 'Zend_Db_Adapter_Pdo_Mysql':
case 'Zend_Db_Adapter_Mysqli':
$createStatement = static::MYSQL_CREATE_STATEMENT;
break;
case 'Zend_Db_Adapter_Pdo_Pgsql':
$createStatement = static::PGSQL_CREATE_STATEMENT;
break;
case 'Zend_Db_Adapter_Pdo_Sqlite':
$createStatement = static::SQLITE_CREATE_STATEMENT;
break;
default:
throw new \InvalidArgumentException('Please provide a valid SQL statement for your database system in the config file as phpmig.createStatement');
break;
}
$sql = sprintf($createStatement, $this->tableName);
}
try {
$this->adapter->query($sql);
} catch (\Zend_Db_Statement_Exception $exception) {
throw new \InvalidArgumentException('Please provide a valid SQL statement for your database system in the config file as phpmig.createStatement');
}
return $this;
}
/**
* Support method for fetching rows.
*
* @param string|array $where OPTIONAL An SQL WHERE clause.
* @param string|array $order OPTIONAL An SQL ORDER clause.
* @param int $count OPTIONAL An SQL LIMIT count.
* @param int $offset OPTIONAL An SQL LIMIT offset.
* @return array The row results, in FETCH_ASSOC mode.
*/
protected function _fetch($where = null, $order = null, $count = null, $offset = null)
{
// selection tool
$select = $this->_db->select();
// the FROM clause
$select->from($this->_name, $this->_cols, $this->_schema);
// the WHERE clause
$where = (array) $where;
foreach ($where as $key => $val) {
// is $key an int?
if (is_int($key)) {
// $val is the full condition
$select->where($val);
} else {
// $key is the condition with placeholder,
// and $val is quoted into the condition
$select->where($key, $val);
}
}
// the ORDER clause
if (!is_array($order)) {
$order = array($order);
}
foreach ($order as $val) {
$select->order($val);
}
// the LIMIT clause
$select->limit($count, $offset);
// return the results
$stmt = $this->_db->query($select);
$data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
return $data;
}
protected function _bootstrap(array $config)
{
if ($this->_sourceDb || $this->_vBDb) {
return;
}
@set_time_limit(0);
$this->_config = $config;
$this->_sourceDb = Zend_Db::factory('mysqli', array('host' => $config['db']['host'], 'port' => $config['db']['port'], 'username' => $config['db']['username'], 'password' => $config['db']['password'], 'dbname' => $config['db']['dbname'], 'charset' => $config['db']['charset']));
$this->_vBDb = Zend_Db::factory('mysqli', array('host' => $config['vb']['host'], 'port' => $config['vb']['port'], 'username' => $config['vb']['username'], 'password' => $config['vb']['password'], 'dbname' => $config['vb']['dbname'], 'charset' => $config['vb']['charset']));
if (empty($config['db']['charset'])) {
$this->_sourceDb->query('SET character_set_results = NULL');
}
if (empty($config['vb']['charset'])) {
$this->_vBDb->query('SET character_set_results = NULL');
}
$this->_prefix = preg_replace('/[^a-z0-9_]/i', '', $config['db']['prefix']);
$this->_vBPrefix = preg_replace('/[^a-z0-9_]/i', '', $config['vb']['prefix']);
if (!empty($config['db']['charset'])) {
$this->_charset = $config['db']['charset'];
}
if (!empty($config['vb']['charset'])) {
$this->_charset = $config['vb']['charset'];
}
define('IMPORT_LOG_TABLE', $this->_config['importLog']);
}
public function inspect()
{
$insp = new Inspection('Db Connection');
try {
$this->getDbAdapter()->getConnection();
$config = $this->dbAdapter->getConfig();
$insp->write(sprintf('Connection to %s as %s on %s:%s successful', $config['dbname'], $config['username'], $config['host'], $config['port']));
switch ($this->dbType) {
case 'mysql':
$rows = $this->dbAdapter->query('SHOW VARIABLES WHERE variable_name ' . 'IN (\'version\', \'protocol_version\', \'version_compile_os\');')->fetchAll();
$sqlinsp = new Inspection('MySQL');
foreach ($rows as $row) {
$sqlinsp->write($row->variable_name . ': ' . $row->value);
}
$insp->write($sqlinsp);
break;
case 'pgsql':
$row = $this->dbAdapter->query('SELECT version();')->fetchAll();
$sqlinsp = new Inspection('PostgreSQL');
$sqlinsp->write($row[0]->version);
$insp->write($sqlinsp);
break;
}
} catch (Exception $e) {
return $insp->error(sprintf('Connection failed %s', $e->getMessage()));
}
return $insp;
}
/**
* get array of ids which got send to the client for a given class
*
* @param Syncope_Model_IDevice|string $_deviceId
* @param Syncope_Model_IFolder|string $_folderId
* @return Syncope_Model_SyncState
*/
public function validate($_deviceId, $_folderId, $_syncKey)
{
$deviceId = $_deviceId instanceof Syncope_Model_IDevice ? $_deviceId->id : $_deviceId;
$folderId = $_folderId instanceof Syncope_Model_IFolder ? $_folderId->id : $_folderId;
$select = $this->_db->select()->from($this->_tablePrefix . 'synckey')->where($this->_db->quoteIdentifier('device_id') . ' = ?', $deviceId)->where($this->_db->quoteIdentifier('counter') . ' = ?', $_syncKey)->where($this->_db->quoteIdentifier('type') . ' = ?', $folderId);
$stmt = $this->_db->query($select);
$state = $stmt->fetchObject('Syncope_Model_SyncState');
$stmt = null;
# see https://bugs.php.net/bug.php?id=44081
if (!$state instanceof Syncope_Model_ISyncState) {
return false;
}
$this->_convertFields($state);
// check if this was the latest syncKey
$select = $this->_db->select()->from($this->_tablePrefix . 'synckey')->where($this->_db->quoteIdentifier('device_id') . ' = ?', $deviceId)->where($this->_db->quoteIdentifier('counter') . ' = ?', $_syncKey + 1)->where($this->_db->quoteIdentifier('type') . ' = ?', $folderId);
$stmt = $this->_db->query($select);
$moreRecentState = $stmt->fetchObject('Syncope_Model_SyncState');
$stmt = null;
# see https://bugs.php.net/bug.php?id=44081
// found more recent synckey => the last sync repsone got not received by the client
if ($moreRecentState instanceof Syncope_Model_ISyncState) {
// undelete entries marked as deleted in syncope_content table
$this->_db->update($this->_tablePrefix . 'content', array('is_deleted' => 0), array('device_id = ?' => $deviceId, 'folder_id = ?' => $folderId, 'creation_synckey = ?' => $state->counter, 'is_deleted = ?' => 1));
// remove entries added during latest sync in syncope_content table
$this->_db->delete($this->_tablePrefix . 'content', array('device_id = ?' => $deviceId, 'folder_id = ?' => $folderId, 'creation_synckey > ?' => $state->counter));
} else {
// finaly delete all entries marked for removal in syncope_content table
$this->_db->delete($this->_tablePrefix . 'content', array('device_id = ?' => $deviceId, 'folder_id = ?' => $folderId, 'is_deleted = ?' => 1));
}
// remove all other synckeys
$this->_deleteOtherStates($state);
return $state;
}
/**
* Truncate a given table.
*
* @param Zend_Db_Adapter_Abstract $db
* @param string $tableName
* @return void
*/
protected function _truncate(Zend_Db_Adapter_Abstract $db, $tableName)
{
$tableName = $db->quoteIdentifier($tableName);
if ($db instanceof Zend_Db_Adapter_Pdo_Sqlite) {
$db->query('DELETE FROM ' . $tableName);
} else {
if ($db instanceof Zend_Db_Adapter_Db2) {
/*if(strstr(PHP_OS, "WIN")) {
$file = tempnam(sys_get_temp_dir(), "zendtestdbibm_");
file_put_contents($file, "");
$db->query('IMPORT FROM '.$file.' OF DEL REPLACE INTO '.$tableName);
unlink($file);
} else {
$db->query('IMPORT FROM /dev/null OF DEL REPLACE INTO '.$tableName);
}*/
#require_once "Zend/Exception.php";
throw Zend_Exception("IBM Db2 TRUNCATE not supported.");
} else {
if ($this->_isMssqlOrOracle($db)) {
$db->query('TRUNCATE TABLE ' . $tableName);
} else {
if ($db instanceof Zend_Db_Adapter_Pdo_Pgsql) {
$db->query('TRUNCATE ' . $tableName . ' CASCADE');
} else {
$db->query('TRUNCATE ' . $tableName);
}
}
}
}
}
/**
* Code to run after deleting an associated attachment.
*
* @see XenForo_AttachmentHandler_Abstract::attachmentPostDelete()
*/
public function attachmentPostDelete(array $attachment, Zend_Db_Adapter_Abstract $db)
{
$db->query('
UPDATE xf_user_profile
SET about_attach_count = IF(about_attach_count > 0, about_attach_count - 1, 0)
WHERE user_id = ?
', $attachment['content_id']);
}
/**
* Support method for fetching rows.
*
* @param Zend_Db_Table_Select $select query options.
* @return array An array containing the row results in FETCH_ASSOC mode.
*/
protected function _fetch(Zend_Db_Table_Select $select)
{
$this->notify('preFetch', $this, $select);
$stmt = $this->_db->query($select);
$data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
$this->notify('postFetch', $this, $data);
return $data;
}
/**
* Create copy of current table.
*
* @param string $newName New table name
* @param bool $withContent Also copy current table content to new table
* @return Days_Db
*/
public function copy($newName, $withContent = false)
{
$this->_db->query("CREATE TABLE `{$newName}` LIKE `{$this->_name}`");
if ($withContent) {
$this->_db->query("INSERT INTO `{$newName}` SELECT * FROM `{$this->_name}` ");
}
return $this;
}
/**
* Code to run after deleting an associated attachment.
*
* @see XenForo_AttachmentHandler_Abstract::attachmentPostDelete()
*/
public function attachmentPostDelete(array $attachment, Zend_Db_Adapter_Abstract $db)
{
$db->query('
UPDATE xf_conversation_message
SET attach_count = IF(attach_count > 0, attach_count - 1, 0)
WHERE message_id = ?
', $attachment['content_id']);
}
public function attachmentPostDelete(array $attachment, Zend_Db_Adapter_Abstract $db)
{
$db->query('
UPDATE xf_resource_update
SET attach_count = IF(attach_count > 0, attach_count - 1, 0)
WHERE resource_update_id = ?
', $attachment['content_id']);
}
/**
* DB2 catalog lookup for describe table
*
* @param string $tableName
* @param string $schemaName OPTIONAL
* @return array
*/
public function describeTable($tableName, $schemaName = null)
{
$sql = "SELECT DISTINCT c.tabschema, c.tabname, c.colname, c.colno,\n c.typename, c.default, c.nulls, c.length, c.scale,\n c.identity, tc.type AS tabconsttype, k.colseq\n FROM syscat.columns c\n LEFT JOIN (syscat.keycoluse k JOIN syscat.tabconst tc\n ON (k.tabschema = tc.tabschema\n AND k.tabname = tc.tabname\n AND tc.type = 'P'))\n ON (c.tabschema = k.tabschema\n AND c.tabname = k.tabname\n AND c.colname = k.colname)\n WHERE " . $this->_adapter->quoteInto('UPPER(c.tabname) = UPPER(?)', $tableName);
if ($schemaName) {
$sql .= $this->_adapter->quoteInto(' AND UPPER(c.tabschema) = UPPER(?)', $schemaName);
}
$sql .= " ORDER BY c.colno";
$desc = array();
$stmt = $this->_adapter->query($sql);
/**
* To avoid case issues, fetch using FETCH_NUM
*/
$result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
/**
* The ordering of columns is defined by the query so we can map
* to variables to improve readability
*/
$tabschema = 0;
$tabname = 1;
$colname = 2;
$colno = 3;
$typename = 4;
$default = 5;
$nulls = 6;
$length = 7;
$scale = 8;
$identityCol = 9;
$tabconstype = 10;
$colseq = 11;
foreach ($result as $key => $row) {
list($primary, $primaryPosition, $identity) = array(false, null, false);
if ($row[$tabconstype] == 'P') {
$primary = true;
$primaryPosition = $row[$colseq];
}
/**
* In IBM DB2, an column can be IDENTITY
* even if it is not part of the PRIMARY KEY.
*/
if ($row[$identityCol] == 'Y') {
$identity = true;
}
$desc[$this->_adapter->foldCase($row[$colname])] = array('SCHEMA_NAME' => $this->_adapter->foldCase($row[$tabschema]), 'TABLE_NAME' => $this->_adapter->foldCase($row[$tabname]), 'COLUMN_NAME' => $this->_adapter->foldCase($row[$colname]), 'COLUMN_POSITION' => $row[$colno] + 1, 'DATA_TYPE' => $row[$typename], 'DEFAULT' => $row[$default], 'NULLABLE' => (bool) ($row[$nulls] == 'Y'), 'LENGTH' => $row[$length], 'SCALE' => $row[$scale], 'PRECISION' => $row[$typename] == 'DECIMAL' ? $row[$length] : 0, 'UNSIGNED' => false, 'PRIMARY' => $primary, 'PRIMARY_POSITION' => $primaryPosition, 'IDENTITY' => $identity);
}
return $desc;
}
/**
* get array of ids which got send to the client for a given class
*
* @param Syncope_Model_IDevice|string $_deviceId
* @param Syncope_Model_IFolder|string $_folderId
* @return array
*/
public function getFolderState($_deviceId, $_folderId)
{
$deviceId = $_deviceId instanceof Syncope_Model_IDevice ? $_deviceId->id : $_deviceId;
$folderId = $_folderId instanceof Syncope_Model_IFolder ? $_folderId->id : $_folderId;
$select = $this->_db->select()->from($this->_tablePrefix . 'content', 'contentid')->where($this->_db->quoteIdentifier('device_id') . ' = ?', $deviceId)->where($this->_db->quoteIdentifier('folder_id') . ' = ?', $folderId)->where($this->_db->quoteIdentifier('is_deleted') . ' = ?', 0);
$stmt = $this->_db->query($select);
$result = $stmt->fetchAll(Zend_Db::FETCH_COLUMN);
return $result;
}
/**
* Executes the current select object and returns the result
*
* @param integer $fetchMode OPTIONAL
* @return PDO_Statement|Zend_Db_Statement
*/
public function query($fetchMode = null)
{
$stmt = $this->_adapter->query($this);
if ($fetchMode == null) {
$fetchMode = $this->_adapter->getFetchMode();
}
$stmt->setFetchMode($fetchMode);
return $stmt;
}
/**
* Garbage collection
*
* @param int $sessMaxLifeTime ignored
* @return boolean
*/
public function gc($sessMaxLifeTime)
{
if ($this->_automaticCleaningFactor > 0) {
if ($this->_automaticCleaningFactor == 1 || rand(1, $this->_automaticCleaningFactor) == 1) {
$this->_write->query("DELETE FROM `{$this->_sessionTable}` WHERE `session_expires` < ?", array(time()));
}
}
return true;
}
public function updateTable($table, $conditionExpr, $valueExpr)
{
if (strpos($table, '/') !== false) {
$table = $this->getTable($table);
}
$sql = 'update ' . $table . ' set ' . $valueExpr . ' where ' . $conditionExpr;
$this->_conn->query($sql);
return $this;
}
