public static function deleteHost(Host $host)
{
if (empty($host->id)) {
throw new InvalidArgumentException("HostId is empty!");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_HOSTS', 'Host'))->where($qb->expr()->equal(new Field('id'), $host->id));
$sql = MySqlDbManager::getQueryObject();
$sql->exec($qb->getSQL());
}
public static function setControllerTemplateByHost(Host $host, $controller, $template)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
if (!empty($controller) or !empty($template)) {
$qb->insert(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->values(array('host_id' => $host->id, 'controller' => $controller, 'template' => $template))->onDuplicateKeyUpdate()->set(new Field('controller'), $controller)->set(new Field('template'), $template);
} else {
$qb->delete(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->where($qb->expr()->equal(new Field('host_id'), $host->id));
}
$sql->exec($qb->getSQL());
return $sql->affected();
}
public function fillUsersGps($userId, $leafId)
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_USERS_GPS'))->where($qb->expr()->equal(new Field('user_id'), $userId));
$this->query->exec($qb->getSQL());
$gpsTree = $this->getNodeTree($leafId);
foreach ($gpsTree as $treeNode) {
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_USERS_GPS'))->values(array('user_id' => $userId, 'node_id' => $treeNode["node_id"]));
$this->query->exec($qb->getSQL());
}
}
public function deleteAllAliasesForTextValue(TextValue $textValue)
{
if (empty($textValue->id)) {
throw new InvalidArgumentException("Text Value ID have to be specified");
}
if (!is_numeric($textValue->id)) {
throw new InvalidArgumentException("Text Value ID have to be integer");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_TEXTS_ALIASES'))->where($qb->expr()->equal(new Field("value_id"), $textValue->id));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
public function deleteGroup(TextsGroup $group)
{
if (empty($group->id)) {
throw new InvalidArgumentException("Group ID have to be specified");
}
if (!is_numeric($group->id)) {
throw new InvalidArgumentException("Group ID have to be integer");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_TEXTS_GROUPS'))->where($qb->expr()->equal(new Field("id"), $group->id));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
public function hookInvalidLoginAttempt($params)
{
if ($this->config->AuxConfig->loginBruteForceProtectionEnabled) {
if (isset($_SERVER['REMOTE_ADDR'])) {
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$sql->exec($qb->select(new Field('count'))->from(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->where($qb->expr()->equal(new Field('ip'), $_SERVER['REMOTE_ADDR']))->getSQL());
$failedAuthCount = $sql->fetchField('count');
$newFailedAuthCount = $failedAuthCount + 1;
if ($newFailedAuthCount >= $this->config->AuxConfig->failedLoginLimit) {
Reg::get(ConfigManager::getConfig("Security", "RequestLimiter")->Objects->RequestLimiter)->blockIP();
$qb = new QueryBuilder();
$sql->exec($qb->delete(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->where($qb->expr()->equal(new Field('ip'), $_SERVER['REMOTE_ADDR']))->getSQL());
throw new RequestLimiterTooManyAuthTriesException("Too many unsucessful authorization tries.");
}
$qb = new QueryBuilder();
$sql->exec($qb->insert(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->values(array('ip' => $_SERVER['REMOTE_ADDR']))->onDuplicateKeyUpdate()->set(new Field('count'), $qb->expr()->sum(new Field('count'), 1))->getSQL());
}
}
}
/**
* CleanUp codes from DB that are too old
*/
public function cleanUp()
{
$time = 60 * 60 * 24 * $this->config->cleanUpTimeOut;
$qb = new QueryBuilder();
$orX = new Orx();
$andX1 = new Andx();
$andX2 = new Andx();
$andX1->add($qb->expr()->less(new Func('UNIX_TIMESTAMP', new Field('issue_date')), $qb->expr()->diff(new Func('UNIX_TIMESTAMP', new Func('NOW')), $time)));
$andX1->add($qb->expr()->equal(new Field('not_cleanable'), 0));
$andX1->add($qb->expr()->isNull(new Field('valid_until')));
$andX2->add($qb->expr()->isNotNull(new Field('valid_until')));
$andX2->add($qb->expr()->less(new Field('valid_until'), new Func('NOW')));
$orX->add($andX1);
$orX->add($andX2);
$qb->delete(Tbl::get('TBL_ONE_TIME_CODES'))->where($orX);
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
/**
* Set user answers by their ids
*
* @param array $answers an array containing user's answers
*/
public function setAnswersByIds($answers)
{
if (is_array($answers)) {
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_PROFILE_SAVE'))->where($qb->expr()->equal(new Field("user_id"), $this->userId));
$this->query->exec($qb->getSQL());
foreach ($answers as $answer) {
if (is_numeric($answer)) {
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_PROFILE_SAVE'))->values(array("user_id" => $this->userId, "profile_id" => $answer));
$this->query->exec($qb->getSQL());
}
}
$this->initUserAnswers();
} else {
throw new UnexpectedValueException("\$answers have to array");
}
}
/**
* Save custom entered field
*
* @param int $user_id
* @param int $field_id
* @param string $value
* @return bool
*/
public function saveField($user_id, $field_id, $value)
{
$value = addslashes($value);
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_CUST_SAVE'))->where($qb->expr()->equal(new Field('user_id'), $user_id))->andWhere($qb->expr()->equal(new Field('field_id'), $field_id));
$this->query->exec($qb->getSQL());
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_CUST_SAVE'))->values(array('user_id' => $user_id, 'field_id' => $field_id, 'text' => $value));
if ($this->query->exec($qb->getSQL())) {
return true;
}
return false;
}
public function clearTimedOutSessions()
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_CHAT_SESSIONS'))->where($qb->expr()->equal(new Field('closed'), 1))->andWhere($qb->expr()->greaterEqual($qb->expr()->diff(new Func('NOW'), new Field('closed_date')), $qb->expr()->prod($this->sessionClearTimeout, 60)));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
/**
* Delete current host and Lang id alias value for config DB
* @param ConfigDB $configDB
* @param unknown_type $aliasHostLangId
* @throws InvalidArgumentException
*/
public static function deleteDBConfigAlias(ConfigDB $configDB, $aliasHostLangId)
{
if (empty($configDB)) {
throw new InvalidArgumentException("ConfigDB object is empty!");
}
if (!is_numeric($configDB->id)) {
throw new InvalidArgumentException("ConfigDB object's id is not numeric!");
}
if (!is_numeric($aliasHostLangId)) {
throw new InvalidArgumentException("Alias Host Language id is not numeric!");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get("TBL_CONFIGS"))->where($qb->expr()->equal(new Field('alias_of'), $configDB->id))->andWhere($qb->expr()->equal(new Field('host_lang_id'), $aliasHostLangId));
$sql = MySqlDbManager::getQueryObject();
$sql->exec($qb->getSQL());
}
public function wipeConversationMessage($conversationMessageId)
{
if (empty($conversationMessageId) or !is_numeric($conversationMessageId)) {
throw new InvalidIntegerArgumentException("\$conversationMessageId have to be non zero integer.");
}
// Get message
$filter = new ConversationMessagesFilter();
$filter->setId($conversationMessageId);
$message = $this->getConversationMessage($filter, true);
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_CONVERSATION_MESSAGES'))->where($qb->expr()->equal(new Field('id'), $message->id));
$affected = $this->query->exec($qb->getSQL())->affected();
$this->correctConversationReadStatus($message->uuid, $message->senderId);
$this->correctConversationHasAttachment($message->uuid, $message->senderId);
$this->correctConversationReadStatus($message->uuid, $message->receiverId);
$this->correctConversationHasAttachment($message->uuid, $message->receiverId);
return $affected;
}
/**
* Unblock blocked IP
* @param string $ip
*/
public function unblockIP($ip = null)
{
if ($ip === null) {
$ip = $_SERVER['REMOTE_ADDR'];
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_SECURITY_FLOODER_IPS'))->where($qb->expr()->equal(new Field("ip"), $ip));
$this->query->exec($qb->getSQL());
}
public static function deleteHostsLanguage(Host $host, Language $language)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_HOST_LANGUAGE'))->where($qb->expr()->equal(new Field('host_id'), $host->id))->andWhere($qb->expr()->equal(new Field('lang_id'), $language->id));
$sql->exec($qb->getSQL());
}
public function clearTimedOutInvitations()
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_CHAT_INVITATIONS'))->where($qb->expr()->greaterEqual($qb->expr()->diff(new Func('NOW'), new Field('date')), $qb->expr()->prod($this->invitationClearTimeout, 60)));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
/**
* Delete alias with given id
*
* @param int $id
* @return bool
*/
public function deleteAlias($id)
{
if (empty($id) or !is_numeric($id)) {
throw new InvalidArgumentException("\$id have to be non zero integer");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_ALIAS'))->where($qb->expr()->equal(new Field("id"), $id));
$this->query->exec($qb->getSQL());
}
/**
* deletes an already created answer
*
* @param integer $profile_id
*/
public function deleteOptionById($profile_id)
{
if (empty($profile_id) or !is_numeric($profile_id)) {
throw new InvalidArgumentException("\$profile have to be numeric id of the profile");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_PROFILE_KEYS'))->where($qb->expr()->equal(new Field("id"), $profile_id));
$this->query->exec($qb->getSQL());
}
public function deletPhotoFromDB(UserPhoto $photo)
{
if (empty($photo->id)) {
throw new InvalidArgumentException("UserPhoto object has no id!");
}
if (empty($photo->userId)) {
$photo = $this->getPhoto($photo->id);
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_USERS_PHOTOS'))->where($qb->expr()->equal(new Field('id'), $photo->id))->limit(1);
$this->query->exec($qb->getSQL());
$this->correctDefaultPhoto($photo->userId);
}
public function removeUserFromGroup(User $user, UserGroup $group)
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_USERS_GROUPS', 'UserManager'))->where($qb->expr()->equal(new Field('user_id'), $user->id))->andWhere($qb->expr()->equal(new Field('group_id'), $group->id));
return $this->query->exec($qb->getSQL())->affected();
}
/**
* Remove country blacklist
*
* @param string $countryCode
* @throws RuntimeException
*/
public function removeFromBlackListCountry($countryCode)
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_SECURITY_BLACKLISTED_COUNTRIES', 'IpFilter'))->where($qb->expr()->equal(new Field("country"), $countryCode));
$this->query->exec($qb->getSQL());
if ($this->query->affected() == 0) {
throw new RuntimeException("Given country {$countryCode} is not blacklisted!");
}
}
/**
* Delete saved crop settings from DB
*
* @param string $fileName
* @throws InvalidArgumentException
*/
public function deleteCropSettings($fileName)
{
if (empty($fileName)) {
throw new InvalidArgumentException("\$fileName have to be non empty string");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get("TBL_CROP_SETTINGS"))->where($qb->expr()->equal(new Field('filename'), $fileName));
$this->query->exec($qb->getSQL());
}
/**
* Generated from @assert delete('user', array(array('id', 1), array('name', 'test')))->text() [==] "DELETE FROM `user` WHERE `id` = 1 AND `name` = :name1".
*
* @covers Kotchasan\Database\QueryBuilder::delete
*/
public function testDelete()
{
$this->assertEquals("DELETE FROM `user` WHERE `id` = 1 AND `name` = :name1", $this->object->delete('user', array(array('id', 1), array('name', 'test')))->text());
}
public function removePermissionFromGroup(Permission $perm, UserGroup $group)
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_GROUPS_PERMISSIONS', 'UserManager'))->where($qb->expr()->equal(new Field('permission_id'), $perm->id))->andWhere($qb->expr()->equal(new Field('group_id'), $group->id));
return $this->query->exec($qb->getSQL())->affected();
}
/**
* Delete given user from DB
*
* @param User $user
* @return integer Affected
*/
public function deleteUser(User $user)
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_USERS'))->where($qb->expr()->equal(new Field('id'), $user->id));
return $this->query->exec($qb->getSQL())->affected();
}
public function clearGarbage()
{
$db = MySqlDbManager::getDbObject();
$db->lockTables(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS'), "w");
$qb = new QueryBuilder();
$qb->select(new Field("system_filename"))->from(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS', 'ConversationAttachmentManager'))->where($qb->expr()->isNull(new Field('message_id')))->andWhere($qb->expr()->greater($qb->expr()->diff(new Func("NOW"), new Field('date')), 60 * 60 * 24 * $this->config->attachmentsClearTimeout));
$this->query->exec($qb->getSQL());
while (($row = $this->query->fetchRecord()) != null) {
try {
@unlink($this->config->uploadDir . $row['system_filename']);
} catch (ErrorException $e) {
}
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS', 'ConversationAttachmentManager'))->where($qb->expr()->isNull(new Field('message_id')))->andWhere($qb->expr()->greater($qb->expr()->diff(new Func("NOW"), new Field('date')), 60 * 60 * 24 * $this->config->attachmentsClearTimeout));
$deletedCount = $this->query->exec($qb->getSQL())->affected();
$db->unlockTables();
return $deletedCount;
}
/**
* delete all required authorization users
* @return Ambigous <boolean, number>
*/
public function deleteAuthorizationUsers()
{
$qb = new QueryBuilder();
$qb->delete(TBL::get('TBL_AUTH_USERS', 'YubikeyUserAuthorization'));
return $this->query->exec($qb->getSQL())->affected();
}
public function deleteTextValue(TextValue $textValue)
{
if (empty($textValue->id)) {
throw new InvalidArgumentException("No ID specified in TextValue object");
}
if (!is_numeric($textValue->id)) {
throw new InvalidArgumentException("Text ID have to be integer");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_TEXTS_VALUES'))->where($qb->expr()->equal(new Field('id'), $textValue->id));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
/**
* Move finished Job from active jobs table to archive table
* Helper function
* @access private
* @param JobQueueObj $job
*
*/
private function moveJobToArchive(JobQueueObj $job)
{
if (!is_numeric($job->id)) {
throw JobQueueException("Job id is not numeric!");
}
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_JOB_QUEUE_ARCHIVE'))->values(array("name" => $job->name, "properties" => serialize($job->properties), "status" => $job->status, "start_date" => $job->startDate, "log_message" => $job->logMessage));
$this->query->exec($qb->getSQL());
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_JOB_QUEUE'))->where($qb->expr()->equal(new Field('id'), $job->id));
$this->query->exec($qb->getSQL());
}
