private function _initSessionTable() { $sql = 'CREATE TABLE `' . $this->_tableName . '` ( `session_id` varchar(100) NOT NULL default "", `session_data` text NOT NULL, `expires` int(11) NOT NULL default "0", PRIMARY KEY (`session_id`) ) ENGINE = MyIsam DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;'; return Db::exec($sql, $this->_db) == 1; }
protected static function write($msg) { $config = self::config(); if (!isset($config['db']['log'])) { $config['db']['log'] = array('type' => 'sqlite', 'file' => 'alm.log.sqlite'); self::config('set', $config); } $sql = 'CREATE TABLE IF NOT EXISTS "log" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "date" TEXT NOT NULL, "alert" TEXT );'; Db::query($sql, 1, 'log'); $sql = 'INSERT INTO "log" ("id", "date", "alert") VALUES (null, :date, :alert);'; $var = array(':date' => date("Y-m-d H:i:s"), ':alert' => $msg); return Db::exec($sql, $var, 0, 'log'); }
/** ajout d'un utilisateur à la base et auto login * @param $pseudo String pseudo unique * @param $pass String pass non encodé * @return string */ public static function add_user($pseudo, $pass) { if (!self::exists_in_database('pseudo', $pseudo, 'users')) { $sql = "INSERT INTO users (pseudo, pass, last_refresh) VALUES (:pseudo, :pass, NOW())"; $req = Db::prepare($sql); $req->bindValue(':pseudo', trim($pseudo), PDO::PARAM_STR); $req->bindValue(':pass', sha1(trim($pass)), PDO::PARAM_STR); $req->execute(); $user_id = Db::getLastInsertId(); // on crée la ligne des modifiers en fonction Db::exec("INSERT INTO modifiers SET user_id = {$user_id}"); $_SESSION['user'] = ['pseudo' => htmlentities($pseudo), 'id' => $user_id]; $url = 'Location:' . _ROOT_ . 'empire'; header("{$url}"); die; } return "un utilisateur porte déjà ce nom"; }
public function delete() { $sql = "DELETE FROM {$this->table} WHERE {$this->primary} = {$this->id}"; Db::exec($sql); }
/** * test_ExecAndTransaction_ExecWithInTrans() * * exec()のテスト(トランザクションあり) */ public function test_ExecAndTransaction_ExecWithInTrans() { $params = ["driver" => $GLOBALS['DB_DRIVER'], "user" => $GLOBALS['DB_USER'], "pass" => $GLOBALS['DB_PASSWORD'], "dbname" => $GLOBALS['DB_DBNAME'], "host" => $GLOBALS['DB_HOST'], "persistent" => false]; $instance = new Db(); $instance->connect($params); // commit pattern $this->assertTrue($instance->beginTransaction()); $this->assertTrue($instance->inTransaction()); $this->assertEquals(1, $instance->exec('INSERT INTO risoluto_db_test(id, column1, column2) values ("10", "TEST_A", "TEST_B");')); $this->assertEquals(2, $this->getconnection()->getRowCount('risoluto_db_test')); $this->assertEquals(10, $instance->lastInsertId()); $this->assertEquals(10, $instance->lastInsertId('id')); $this->assertTrue($instance->commit()); $this->assertFalse($instance->inTransaction()); $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test')); // Rollback pattern $before_val = $this->getconnection()->createQueryTable('risoluto_db_test', 'SELECT id, column1, column2 FROM risoluto_db_test WHERE id="10";'); $this->assertTrue($instance->beginTransaction()); $this->assertTrue($instance->inTransaction()); $this->assertEquals(1, $instance->exec('UPDATE risoluto_db_test SET column1="TEST_C", column2="TEST_C" WHERE id="10";')); $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test')); $this->assertEquals(1, $instance->exec('DELETE FROM risoluto_db_test WHERE id="10";')); $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test')); $this->assertTrue($instance->rollBack()); $this->assertFalse($instance->inTransaction()); $after_val = $this->getconnection()->createQueryTable('risoluto_db_test', 'SELECT id, column1, column2 FROM risoluto_db_test WHERE id="10";'); $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test')); $this->assertTablesEqual($before_val, $after_val); // Cleaning $this->assertEquals(1, $instance->exec('DELETE FROM risoluto_db_test WHERE id="10";')); $this->assertEquals(2, $this->getconnection()->getRowCount('risoluto_db_test')); $instance->disConnect(); unset($instance); }
public function delete() { $sql = 'DELETE FROM ' . static::$table . ' WHERE ' . static::$idFieldName . '=:id'; $db = new Db(); $idFieldName = static::$idFieldName; $db->exec($sql, [':id' => $this->{$idFieldName}]); }
/** * Executes a database update query. * * @param string $updateSql Update SQL query. * @param int|false $errorToIgnore A MySQL error code to ignore. * @param string $file The Update file that's calling this method. */ public static function executeMigrationQuery($updateSql, $errorToIgnore, $file) { try { Db::exec($updateSql); } catch (\Exception $e) { self::handleQueryError($e, $updateSql, $errorToIgnore, $file); } }
/** * Perform cleanup of all items in arrays. */ private function doClean() { $this->numnuked += count($this->cleanup['nuke']); $this->nummiscd += count($this->cleanup['misc']); if (!$this->echoonly) { $releases = new Releases(); $db = new Db(); foreach (array_keys($this->cleanup['nuke']) as $id) { $releases->delete($id); } if (count($this->cleanup['misc'])) { $sql = 'update releases set categoryID = ' . Category::CAT_MISC_OTHER . ' where categoryID != ' . Category::CAT_MISC_OTHER . ' and id in (' . implode(array_keys($this->cleanup['misc']), ',') . ')'; $db->exec($sql); } } $this->cleanup = array('nuke' => array(), 'misc' => array()); }
public static function delete($table, $condition = array()) { $sql = "delete from `" . $table . "`"; if (count($condition)) { $where = array(); foreach ($condition as $key => $value) { $where[] = '`' . $key . '` = ?'; $args[] = $value; } $sql .= " where " . implode(' and ', $where); } return Db::exec($sql, $args); }
/** * Performs database update(s) * * @param string $file Update script filename * @param array $sqlarray An array of SQL queries to be executed * @throws UpdaterErrorException */ static function updateDatabase($file, $sqlarray) { foreach ($sqlarray as $update => $ignoreError) { try { Db::exec($update); } catch (\Exception $e) { if ($ignoreError === false || !Db::get()->isErrNo($e, $ignoreError)) { $message = $file . ":\nError trying to execute the query '" . $update . "'.\nThe error was: " . $e->getMessage(); throw new UpdaterErrorException($message); } } } }
if (!file_exists($nogrouppath)) { mkdir($nogrouppath); } if (!file_exists($nogrouppath . basename($nzbFile))) { rename($nzbFile, $nogrouppath . basename($nzbFile)); } $missinggroups = array_merge($missinggroups, $nzbInfo->groups); } continue; } else { if ($usefilename) { $relguid = md5(uniqid()); $name = $releases->cleanReleaseName(str_replace(".nzb", "", basename($nzbFile))); $catId = $cat->determineCategory($groupName, $name); $relid = $releases->insertRelease($name, $nzbInfo->filecount, $groupID, $relguid, $catId, "", date("Y-m-d H:i:s", $nzbInfo->postedlast), $nzbInfo->poster, "", $page->site); $db->exec(sprintf("update releases set totalpart = %d, size = %s, completion = %d, GID=%s where ID = %d", $nzbInfo->filecount, $nzbInfo->filesize, $nzbInfo->completion, $db->escapeString($nzbInfo->gid), $relid)); $nzbfilename = $nzb->getNZBPath($relguid, $page->site->nzbpath, true); $fp = _gzopen($nzbfilename, "w"); if ($fp) { gzwrite($fp, $nzbInfo->toNzb()); gzclose($fp); echo sprintf("%0" . $digits . "d %.2f%% Imported %s\n", $items - $num, $num / $items * 100, $name); if ($movefiles) { if (!file_exists($importedpath)) { mkdir($importedpath); } if (!file_exists($importedpath . basename($nzbFile))) { rename($nzbFile, $importedpath . basename($nzbFile)); } } } else {
/** * Download a range of usenet messages. Store binaries with subjects matching a * specific pattern in the database. */ function scan($nntp, $groupArr, $first, $last, $type = 'update') { $db = new Db(); $releaseRegex = new ReleaseRegex(); $n = $this->n; $this->startHeaders = microtime(true); if ($this->compressedHeaders) { $nntpn = new Nntp(); $nntpn->doConnect(5, false, true); $response = $nntpn->_sendCommand('XFEATURE COMPRESS GZIP'); if ($nntpn->isError($response) || $response != 290) { $response2 = $nntpn->_sendCommand('XZVER'); if ($nntpn->isError($response2) || $response2 != 412) { $msgs = $nntp->getOverview($first . "-" . $last, true, false); $nntpn->doQuit(); } else { $msgs = $nntp->getXOverview($first . "-" . $last, true, false); $nntpn->doQuit(); } } else { $msgs = $nntp->getOverview($first . "-" . $last, true, false); $nntpn->doQuit(); } } else { $msgs = $nntp->getOverview($first . "-" . $last, true, false); } if ($nntp->isError($msgs) && ($msgs->code == 400 || $msgs->code == 503)) { echo "NNTP connection timed out. Reconnecting...{$n}"; if (!$nntp->doConnect()) { // TODO: What now? echo "Failed to get NNTP connection.{$n}"; return; } $nntp->selectGroup($groupArr['name']); if ($this->compressedHeaders) { $nntpn = new Nntp(); $nntpn->doConnect(5, false, true); $response = $nntpn->_sendCommand('XFEATURE COMPRESS GZIP'); if ($nntpn->isError($response) || $response != 290) { $response2 = $nntpn->_sendCommand('XZVER'); if ($nntpn->isError($response2) || $response2 != 412) { $msgs = $nntp->getOverview($first . "-" . $last, true, false); $nntpn->doQuit(); } else { $msgs = $nntp->getXOverview($first . "-" . $last, true, false); $nntpn->doQuit(); } } else { $msgs = $nntp->getOverview($first . "-" . $last, true, false); $nntpn->doQuit(); } } else { $msgs = $nntp->getOverview($first . "-" . $last, true, false); } } $rangerequested = range($first, $last); $msgsreceived = array(); $msgsblacklisted = array(); $msgsignored = array(); $msgsinserted = array(); $msgsnotinserted = array(); $timeHeaders = number_format(microtime(true) - $this->startHeaders, 2); if ($nntp->isError($msgs)) { echo "Error {$msgs->code}: {$msgs->message}{$n}"; echo "Skipping group{$n}"; return false; } $this->startUpdate = microtime(true); if (is_array($msgs)) { //loop headers, figure out parts foreach ($msgs as $msg) { if (!isset($msg['Number'])) { continue; } $msgsreceived[] = $msg['Number']; $msgPart = $msgTotalParts = 0; $pattern = '|\\((\\d+)[\\/](\\d+)\\)|i'; preg_match_all($pattern, $msg['Subject'], $matches, PREG_PATTERN_ORDER); $matchcnt = sizeof($matches[0]); for ($i = 0; $i < $matchcnt; $i++) { $msgPart = $matches[1][$i]; $msgTotalParts = $matches[2][$i]; } if (!isset($msg['Subject']) || $matchcnt == 0) { $msgsignored[] = $msg['Number']; continue; } if ((int) $msgPart > 0 && (int) $msgTotalParts > 0) { $subject = utf8_encode(trim(preg_replace('|\\(' . $msgPart . '[\\/]' . $msgTotalParts . '\\)|i', '', $msg['Subject']))); if (!isset($this->message[$subject])) { $this->message[$subject] = $msg; $this->message[$subject]['MaxParts'] = (int) $msgTotalParts; $this->message[$subject]['Date'] = strtotime($this->message[$subject]['Date']); } if ((int) $msgPart > 0) { $this->message[$subject]['Parts'][(int) $msgPart] = array('Message-ID' => substr($msg['Message-ID'], 1, -1), 'number' => $msg['Number'], 'part' => (int) $msgPart, 'size' => $msg['Bytes']); $this->message[$subject]['PartNumbers'][(int) $msgPart] = $msg['Number']; } } } unset($msg); unset($msgs); $count = 0; $updatecount = 0; $partcount = 0; $rangenotreceived = array_diff($rangerequested, $msgsreceived); if ($type != 'partrepair') { echo "Received " . sizeof($msgsreceived) . " articles of " . ($last - $first + 1) . " requested, " . sizeof($msgsignored) . " not binaries {$n}"; } if ($type == 'update' && sizeof($msgsreceived) == 0) { echo "Error: Server did not return any articles.{$n}"; echo "Skipping group{$n}"; return false; } if (sizeof($rangenotreceived) > 0) { switch ($type) { case 'backfill': //don't add missing articles break; case 'partrepair': case 'update': default: $this->addMissingParts($rangenotreceived, $groupArr['ID']); break; } echo "Server did not return " . count($rangenotreceived) . " article(s).{$n}"; } if (isset($this->message) && count($this->message)) { $groupRegexes = $releaseRegex->getForGroup($groupArr['name']); //insert binaries and parts into database. when binary already exists; only insert new parts foreach ($this->message as $subject => $data) { //Filter binaries based on black/white list if ($this->isBlackListed($data, $groupArr['name'])) { $msgsblacklisted[] = count($data['Parts']); if ($type == 'partrepair') { $partIds = array(); foreach ($data['Parts'] as $partdata) { $partIds[] = $partdata['number']; } $db->exec(sprintf("DELETE FROM partrepair WHERE numberID IN (%s) AND groupID=%d", implode(',', $partIds), $groupArr['ID'])); } continue; } if (isset($data['Parts']) && count($data['Parts']) > 0 && $subject != '') { //Check for existing binary $binaryID = 0; $binaryHash = md5($subject . $data['From'] . $groupArr['ID']); $res = $db->queryOneRow(sprintf("SELECT ID FROM binaries WHERE binaryhash = %s", $db->escapeString($binaryHash))); if (!$res) { //Apply Regexes $regexMatches = array(); foreach ($groupRegexes as $groupRegex) { $regexCheck = $releaseRegex->performMatch($groupRegex, $subject); if ($regexCheck !== false) { $regexMatches = $regexCheck; break; } } $sql = ''; if (!empty($regexMatches)) { $relparts = explode("/", $regexMatches['parts']); $sql = sprintf("INSERT INTO binaries (name, fromname, date, xref, totalparts, groupID, procstat, categoryID, regexID, reqID, relpart, reltotalpart, binaryhash, relname, dateadded) VALUES (%s, %s, FROM_UNIXTIME(%s), %s, %s, %d, %d, %s, %d, %s, %d, %d, %s, %s, now())", $db->escapeString($subject), $db->escapeString(utf8_encode($data['From'])), $db->escapeString($data['Date']), $db->escapeString($data['Xref']), $db->escapeString($data['MaxParts']), $groupArr['ID'], Releases::PROCSTAT_TITLEMATCHED, $regexMatches['regcatid'], $regexMatches['regexID'], $db->escapeString($regexMatches['reqID']), $relparts[0], $relparts[1], $db->escapeString($binaryHash), $db->escapeString(str_replace('_', ' ', $regexMatches['name']))); } elseif ($this->onlyProcessRegexBinaries === false) { $sql = sprintf("INSERT INTO binaries (name, fromname, date, xref, totalparts, groupID, binaryhash, dateadded) VALUES (%s, %s, FROM_UNIXTIME(%s), %s, %s, %d, %s, now())", $db->escapeString($subject), $db->escapeString(utf8_encode($data['From'])), $db->escapeString($data['Date']), $db->escapeString($data['Xref']), $db->escapeString($data['MaxParts']), $groupArr['ID'], $db->escapeString($binaryHash)); } elseif ($type == 'partrepair') { $partIds = array(); foreach ($data['Parts'] as $partdata) { $partIds[] = $partdata['number']; } $db->exec(sprintf("DELETE FROM partrepair WHERE numberID IN (%s) AND groupID=%d", implode(',', $partIds), $groupArr['ID'])); continue; } if ($sql != '') { $binaryID = $db->queryInsert($sql); $count++; if ($count % 500 == 0) { echo "{$count} bin adds..."; } } } else { $binaryID = $res["ID"]; $updatecount++; if ($updatecount % 500 == 0) { echo "{$updatecount} bin updates..."; } } if ($binaryID != 0) { $partParams = array(); $partNumbers = array(); $totsize = 0; foreach ($data['Parts'] as $partdata) { $partcount++; $totsize += $partdata['size']; $partParams[] = sprintf("(%d, %s, %s, %s, %s)", $binaryID, $db->escapeString($partdata['Message-ID']), $db->escapeString($partdata['number']), $db->escapeString(round($partdata['part'])), $db->escapeString($partdata['size'])); $partNumbers[] = $partdata['number']; } $partSql = "INSERT INTO parts (binaryID, messageID, number, partnumber, size) VALUES " . implode(', ', $partParams); $pidata = $db->queryInsert($partSql, false); if (!$pidata) { $msgsnotinserted = array_merge($msgsnotinserted, $partNumbers); } else { $msgsinserted = array_merge($msgsinserted, $partNumbers); } // update bin size $upsql = sprintf("update binaries set size = size + %d where ID = %d", $totsize, $binaryID); $db->exec($upsql); } } } //TODO: determine whether to add to missing articles if insert failed if (sizeof($msgsnotinserted) > 0) { echo 'WARNING: ' . count($msgsnotinserted) . ' Parts failed to insert' . $n; $this->addMissingParts($msgsnotinserted, $groupArr['ID']); } if ($count >= 500 || $updatecount >= 500) { echo $n; } //line break for bin adds output } $timeUpdate = number_format(microtime(true) - $this->startUpdate, 2); $timeLoop = number_format(microtime(true) - $this->startLoop, 2); if (sizeof($msgsblacklisted) > 0) { echo "Blacklisted " . array_sum($msgsblacklisted) . " parts in " . sizeof($msgsblacklisted) . " binaries" . $n; } if ($type != 'partrepair') { echo number_format($count) . ' new, ' . number_format($updatecount) . ' updated, ' . number_format($partcount) . ' parts.'; echo " {$timeHeaders} headers, {$timeUpdate} update, {$timeLoop} range.{$n}"; } unset($this->message); unset($data); return $last; } else { echo "Error: Can't get parts from server (msgs not array) {$n}"; echo "Skipping group{$n}"; return false; } }
/** * Bulk updates objects in collection * * @param array $field_values * @return mixed */ public function update(array $field_values) { $this->remove('select'); $this->sql_accum[] = array('stmt' => 'update', 'sql' => null, 'sort' => 1, 'val' => $field_values); $sql = $this->compile(); $tbl = $this->getStatement('table'); OrmCache::clearCacheForTable($tbl['sql']); return Db::exec($sql, $this->values_accum); }
public function testExecFailThrowsQueryException() { $this->mockPdo->method('prepare')->willThrowException(new \PDOException()); $this->expectException(\Starlit\Db\Exception\QueryException::class); $this->db->exec('NO SQL'); }