protected function extract()
 {
     $this->comment('Extracting definitions ...');
     $db = new \SQLite3($this->versionDb);
     // extract Grimoire definition
     $grimoire = $db->prepare("SELECT json FROM DestinyGrimoireDefinition WHERE id = 0")->execute()->fetchArray(SQLITE3_ASSOC);
     $this->export("Grimoire", 0, json_decode($grimoire['json'], true));
     // extract all the other definitions
     $map = ["ActivityBundle" => "bundleHash", "Activity" => "activityHash", "ActivityType" => "activityTypeHash", "Class" => "classHash", "Combatant" => "combatantHash", "Destination" => "destinationHash", "DirectorBook" => "bookHash", "EnemyRace" => "raceHash", "Faction" => "factionHash", "Gender" => "genderHash", "GrimoireCard" => "cardId", "HistoricalStats" => "statId", "InventoryBucket" => "bucketHash", "InventoryItem" => "itemHash", "Place" => "placeHash", "Progression" => "progressionHash", "Objective" => "objectiveHash", "Race" => "raceHash", "SandboxPerk" => "perkHash", "ScriptedSkull" => "skullHash", "SpecialEvent" => "eventHash", "Stat" => "statHash", "StatGroup" => "statGroupHash", "TalentGrid" => "gridHash", "UnlockFlag" => "flagHash", "VendorCategory" => "categoryHash", "Vendor" => "summary.vendorHash"];
     foreach ($map as $folder => $key) {
         $table = "Destiny{$folder}Definition";
         $result = $db->prepare("SELECT json FROM {$table}")->execute();
         while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
             $json = json_decode($row['json'], true);
             $hash = (string) array_get($json, $key);
             $this->export($folder, $hash, $json);
         }
         $this->line($folder);
     }
     $db->close();
 }
예제 #2
1
파일: DB.php 프로젝트: nasaa0528/core
 /**
  * remove session(s) from database
  * @param $sessionids session ids ( or id )
  */
 public function removeSession($sessionids)
 {
     if ($this->handle != null) {
         if (is_array($sessionids)) {
             $tmpids = $sessionids;
         } else {
             $tmpids = array($sessionids);
         }
         $this->handle->begin();
         $stmt = $this->handle->prepare('DELETE FROM captiveportal WHERE sessionid = :sessionid');
         foreach ($tmpids as $session) {
             $this->handle->executePrepared($stmt, array('sessionid' => $session), array("sessionid" => \PDO::PARAM_STR));
             $stmt->execute();
         }
         $this->handle->commit();
     }
 }
function main()
{
    global $G;
    message("PHP testing sandbox (%s) version %s", $G['ME'], VERSION);
    try {
        $db = new SQLite3(DATABASE);
        $db->exec('DROP TABLE IF EXISTS t');
        $db->exec('CREATE TABLE t (a, b, c)');
        message('Table t sucessfully created');
        $sth = $db->prepare('INSERT INTO t VALUES (?, ?, ?)');
        $sth->bindValue(1, 'a');
        $sth->bindValue(2, 'b');
        $sth->bindValue(3, 'c');
        $sth->execute();
        $sth->bindValue(1, 1);
        $sth->bindValue(2, 2);
        $sth->bindValue(3, 3);
        $sth->execute();
        $sth->bindValue(1, 'one');
        $sth->bindValue(2, 'two');
        $sth->bindValue(3, 'three');
        $sth->execute();
        $sth = $db->prepare('SELECT * FROM t');
        $result = $sth->execute();
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            message('%s, %s, %s', $row['a'], $row['b'], $row['c']);
        }
    } catch (Exception $e) {
        message($e->getMessage());
    }
}
예제 #4
0
파일: DB.php 프로젝트: jlaso/simple-stats
 /**
  * @param StatsModel $model
  * @param string $data
  * @param int $count
  * @return \SQLite3Result
  */
 public function insertData(StatsModel $model, $data, $count = 1)
 {
     $statement = $this->conn->prepare(sprintf("INSERT INTO `%s` (`date`, `count`, `data`) VALUES (%d, :count, :data);", $model->getEvent(), intval(date('U'))));
     $statement->bindValue(':data', $data, SQLITE3_TEXT);
     $statement->bindValue(':count', $count, SQLITE3_INTEGER);
     return $statement->execute();
 }
 /**
  *
  *
  * @return: bool whether the user has been registered in the database
  */
 public function isVisitorRegistered($vid, $ip)
 {
     $sql = 'SELECT * FROM visitor WHERE vid=:vid AND ip=:ip';
     $sth = $this->db->prepare($sql);
     $sth->bindValue(':vid', $vid, SQLITE3_TEXT);
     $sth->bindValue(':ip', $ip, SQLITE3_TEXT);
     $result = $sth->execute();
     if (!$result->fetchArray()) {
         return false;
     } else {
         return true;
     }
 }
예제 #6
0
파일: SQLite3Engine.php 프로젝트: brick/geo
 /**
  * {@inheritdoc}
  */
 protected function executeQuery($query, array $parameters)
 {
     if (isset($this->statements[$query])) {
         $statement = $this->statements[$query];
     } else {
         // Temporary set the error reporting level to 0 to avoid any warning.
         $errorReportingLevel = error_reporting(0);
         $statement = $this->sqlite3->prepare($query);
         // Restore the original error reporting level.
         error_reporting($errorReportingLevel);
         $errorCode = $this->sqlite3->lastErrorCode();
         if ($errorCode !== 0) {
             $exception = new SQLite3Exception($this->sqlite3->lastErrorMsg(), $errorCode);
             if ($errorCode === 1) {
                 // SQL error cause by a missing function, this must be reported with a GeometryEngineException.
                 throw GeometryEngineException::operationNotSupportedByEngine($exception);
             } else {
                 // Other SQLite3 error; we cannot trigger the original E_WARNING, so we throw this exception instead.
                 throw $exception;
             }
         } else {
             $this->statements[$query] = $statement;
         }
     }
     $index = 1;
     foreach ($parameters as $parameter) {
         if ($parameter instanceof Geometry) {
             if ($parameter->isEmpty()) {
                 $statement->bindValue($index++, $parameter->asText(), SQLITE3_TEXT);
                 $statement->bindValue($index++, $parameter->SRID(), SQLITE3_INTEGER);
             } else {
                 $statement->bindValue($index++, $parameter->asBinary(), SQLITE3_BLOB);
                 $statement->bindValue($index++, $parameter->SRID(), SQLITE3_INTEGER);
             }
         } else {
             if ($parameter === null) {
                 $type = SQLITE3_NULL;
             } elseif (is_int($parameter)) {
                 $type = SQLITE3_INTEGER;
             } elseif (is_float($parameter)) {
                 $type = SQLITE3_FLOAT;
             } else {
                 $type = SQLITE3_TEXT;
             }
             $statement->bindValue($index++, $parameter, $type);
         }
     }
     $result = $statement->execute();
     return $result->fetchArray(SQLITE3_NUM);
 }
예제 #7
0
 /**
  * @param string[] $headers
  * @param string $body
  * @return bool Success
  */
 public function store($headers, $body)
 {
     if (!is_array($headers)) {
         $headers = [$headers];
     }
     $body = strval($body);
     $tableName = $this->_tableName;
     $statement = $this->_databaseHandle->prepare("INSERT INTO {$tableName} (id, headers, body) VALUES (null, :headers, :body)");
     $statement->bindValue(':headers', json_encode($headers), SQLITE3_TEXT);
     $statement->bindValue(':body', $body, SQLITE3_TEXT);
     $result = $statement->execute();
     $this->_lastNativeResultFromStore = $result;
     $this->_lastSuccessFromStore = $result !== false;
     return $this->_lastSuccessFromStore;
 }
예제 #8
0
/**
 * Get a list of venues which are whitin $distance of location ($lat, long)
 * and have a category similar to $keyword
 * 
 * returns list of venue information
 */
function locationKeywordSearch($lat, $long, $keyword, $distance, $limit = 25)
{
    $_SESSION['lat'] = $lat;
    $_SESSION['long'] = $long;
    $_SESSION['keyword'] = $keyword;
    global $databaseName;
    $db = new SQLite3($databaseName);
    // Attach methods haversineGreatCircleDistance,stringSimilarity to
    // Database engine so that we can use them in our query
    $db->createFunction("DISTANCE", "haversineGreatCircleDistance");
    $db->createFunction("SIMILARITY", "stringSimilarity");
    // Search query: get venue information for venues close to location, with categories most similar to the keyword/phrase
    $statement = $db->prepare('SELECT venueId, name, lat, long, categories, address, DISTANCE(lat, long) as distance, SIMILARITY(categories)' . " as similarity FROM Venues WHERE distance < :dist ORDER BY similarity DESC LIMIT :limit");
    // Bind some parameters to the query
    $statement->bindValue(':limit', $limit);
    $statement->bindValue(':dist', $distance, SQLITE3_INTEGER);
    // Obtain the venues from the db and put them in a list
    $qry = $statement->execute();
    $venues = [];
    while ($venue = $qry->fetchArray()) {
        $venues[] = $venue;
    }
    $db->close();
    return $venues;
}
예제 #9
0
파일: Sqlite.php 프로젝트: popphp/pop-cache
 /**
  * Check if cache table exists
  *
  * @return void
  */
 protected function checkTable()
 {
     $tables = [];
     $sql = "SELECT name FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%' " . "UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table', 'view') ORDER BY 1";
     if ($this->isPdo) {
         $sth = $this->sqlite->prepare($sql);
         $sth->execute();
         $result = $sth;
         while (($row = $result->fetch(\PDO::FETCH_ASSOC)) != false) {
             $tables[] = $row['name'];
         }
     } else {
         $result = $this->sqlite->query($sql);
         while (($row = $result->fetchArray(SQLITE3_ASSOC)) != false) {
             $tables[] = $row['name'];
         }
     }
     // If the cache table doesn't exist, create it.
     if (!in_array($this->table, $tables)) {
         $sql = 'CREATE TABLE IF NOT EXISTS "' . $this->table . '" ("id" VARCHAR PRIMARY KEY NOT NULL UNIQUE, "start" INTEGER, "expire" INTEGER, "lifetime" INTEGER, "value" BLOB, "time" INTEGER)';
         if ($this->isPdo) {
             $sth = $this->sqlite->prepare($sql);
             $sth->execute();
         } else {
             $this->sqlite->query($sql);
         }
     }
 }
예제 #10
0
 /**
  * Wait for a message in the queue and save the message to a safety queue
  *
  * @param int $timeout
  * @return Message
  */
 public function waitAndReserve($timeout = null)
 {
     $timeout = $timeout !== null ? $timeout : $this->defaultTimeout;
     for ($time = 0; $time < $timeout; $time++) {
         $row = @$this->connection->querySingle('SELECT rowid, payload FROM queue ORDER BY rowid ASC LIMIT 1', true);
         if ($row !== []) {
             $message = $this->decodeMessage($row['payload']);
             $message->setIdentifier($row['rowid']);
             $encodedMessage = $this->encodeMessage($message);
             $preparedDelete = $this->connection->prepare('DELETE FROM queue WHERE rowid=:rowid');
             $preparedDelete->bindValue(':rowid', $row['rowid']);
             $preparedInsert = $this->connection->prepare('INSERT INTO processing (rowid, payload) VALUES (:rowid, :payload)');
             $preparedInsert->bindValue(':rowid', $row['rowid']);
             $preparedInsert->bindValue(':payload', $encodedMessage);
             try {
                 $this->connection->query('BEGIN IMMEDIATE TRANSACTION');
                 $preparedDelete->execute();
                 $preparedInsert->execute();
                 $this->connection->query('COMMIT');
             } catch (\Exception $exception) {
                 $this->connection->query('ROLLBACK');
             }
             return $message;
         }
         sleep(1);
     }
     return null;
 }
예제 #11
0
 public function init($myrole, $drivers)
 {
     $this->_out->logNotice(">>>init " . get_class($this) . " compare driver");
     $this->_out->logDebug("opening DB file: " . $this->_options['file']);
     $this->_open();
     if ($this->_options['prefix']) {
         $this->_prefix = $this->_options['prefix'];
     } else {
         $this->_prefix = $this->_engine->getUniqueKey();
     }
     $this->_out->logDebug("we will use table name prefix: '{$this->_prefix}'");
     // TODO check that sqlite 3 is installed
     // TODO check mandatory options
     // TODO complain about not allowed options
     // TODO $options['prefix'] can't start with _ it would interfear with testing mode
     if ($this->_testing) {
         // TODO maybe using transaction ROLLBACK in shutdown is better
         $this->_prefix = "_" . $this->_prefix;
         $this->_out->logDebug("we are in testing mode so prefix is changed to: '{$this->_prefix}'");
     }
     $this->_createStructure($this->_options['rebuild']);
     $this->_dbIndexOn(false);
     // prepared statements
     $this->_prepFromRemote2 = $this->_db->prepare($this->_sql("INSERT INTO {$this->_prefix} (path, isDir, remote, rmd5, rtime, rsize) VALUES(:path, :isDir, :isRemote, :md5, :time, :size)", "prepare SQL: "));
     $this->_prepFromRemote1 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET isDir=:isDir, remote=:isRemote, rsize=:size, rmd5=:md5, rtime=:time WHERE path=:path", "prepare SQL: "));
     $this->_prepFromLocalFull2 = $this->_db->prepare($this->_sql("INSERT INTO {$this->_prefix} (path, isDir, local, lmd5, ltime, lsize) VALUES(:path, :isDir, :isLocal, :md5, :time, :size)", "prepare SQL: "));
     $this->_prepFromLocalFull1 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET isDir=:isDir, local=:isLocal, lsize=:size, lmd5=:md5, ltime=:time WHERE path=:path", "prepare SQL: "));
     $this->_prepFromLocal2 = $this->_db->prepare($this->_sql("INSERT INTO {$this->_prefix} (path, isDir, local, ltime, lsize) VALUES(:path, :isDir, :isLocal, :time, :size)", "prepare SQL: "));
     $this->_prepFromLocal1 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET isDir=:isDir, local=:isLocal, lsize=:size, ltime=:time WHERE path=:path", "prepare SQL: "));
     $this->_prepFromLocalMd5 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET lmd5=:md5 WHERE path=:path", "prepare SQL: "));
     $this->_prepFromRemoteMd5 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET rmd5=:md5, rts=CASE WHEN rts is null THEN ltime ELSE rts END WHERE path=:path", "prepare SQL: "));
     // remote has done updates
     $this->_prepRemoteHasDeleted = $this->_db->prepare($this->_sql("DELETE FROM {$this->_prefix} WHERE path=:path", "prepare SQL: "));
     $this->_prepRemoteHasUploaded = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET remote=1, rmd5=lmd5, rsize=lsize, rts=ltime WHERE path=:path", "prepare SQL: "));
 }
예제 #12
0
public  function _gc($maxlifetime) {
        $stmt=parent::prepare("delete
                                 from http_sessions
                                 where datetime())-strftime('%s',alive)>:lifetime");
        $stmt->bindValue(':lifetime', ini_get('session.gc_maxlifetime'), SQLITE3_INTEGER);
        $stmt->execute();
        return parent::changes();}
예제 #13
0
function parseCSV($file)
{
    $db = new SQLite3('band2.db');
    if ($_POST['tableOption'] == 2) {
        echo "Delete table<br>";
        $query = "DELETE FROM " . $_POST['tableSelect'] . ";";
        echo $query;
        $stmt = $db->prepare($query);
        $stmt->execute();
    }
    if ($_POST['tableOption'] !== 0) {
        if (($handle = fopen($file, "r")) !== FALSE) {
            if ($_POST['tableSelect'] === "Student") {
                echo "Student";
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Student (section, studentid, firstname, lastname) VALUES(:1, :2, :3, :4);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_TEXT);
                    $stmt->bindValue(':2', $data[1], SQLITE3_INTEGER);
                    $stmt->bindValue(':3', $data[2], SQLITE3_TEXT);
                    $stmt->bindValue(':4', $data[3], SQLITE3_TEXT);
                    $stmt->execute();
                    $stmt = $db->prepare("INSERT INTO Attendance (studentid) VALUES ({$data['1']});");
                    $stmt->execute();
                }
            }
            if ($_POST['tableSelect'] == "Instrument") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Instrument (instrumentserial, instrumentManufacturer, instrumentname) VALUES(:1, :2, :3);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_TEXT);
                    $stmt->bindValue(':2', $data[1], SQLITE3_TEXT);
                    $stmt->bindValue(':3', $data[2], SQLITE3_TEXT);
                    $stmt->execute();
                }
            }
            if ($_POST['tableSelect'] == "Uniform") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Uniform (itemtype, itemnumber) VALUES(:1, :2);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_INTEGER);
                    $stmt->bindValue(':2', $data[1], SQLITE3_INTEGER);
                    $stmt->execute();
                }
            }
            fclose($handle);
        }
    }
}
예제 #14
0
 /**
  * Creates a new Statement that uses the given connection handle and SQL statement.
  *
  * @param \SQLite3 $conn
  * @param string   $prepareString
  */
 public function __construct(\SQLite3 $conn, $prepareString)
 {
     $this->_conn = $conn;
     $this->_stmt = $conn->prepare($prepareString);
     if (false === $this->_stmt) {
         throw new Exception($this->errorInfo(), $this->errorCode());
     }
 }
예제 #15
0
 /**
  * Clean up outdated entries by checking against the ttl.
  * @return boolean TRUE on success
  * @throws CoreXEngine\Cache\AccessException if we want to write a read-only file
  * @throws CoreXEngine\Cache\Exception for no special typed problem
  */
 public function clean()
 {
     $statement = $this->database->prepare("DELETE FROM {$this->table} WHERE c_ttl IS NOT NULL AND c_ttl < :ttl");
     $statement->bindValue(':ttl', time(), SQLITE3_INTEGER);
     @$statement->execute();
     $this->checkError();
     return true;
 }
예제 #16
0
 /**
  * Prepares a statement or uses an instance from the cache
  *
  * @param string $strQuery
  *
  * @return SQLite3Stmt
  */
 private function getPreparedStatement($strQuery)
 {
     $strName = md5($strQuery);
     if (isset($this->arrStatementsCache[$strName])) {
         return $this->arrStatementsCache[$strName];
     }
     $objStmt = $this->linkDB->prepare($strQuery);
     $this->arrStatementsCache[$strName] = $objStmt;
     return $objStmt;
 }
예제 #17
0
 /**
  * A cache file has been accessed
  *
  * Method call, when a cache file has been read. This method ist used to
  * basically update the LRU information of cache entries.
  * 
  * @param string $path 
  * @param int $time
  * @return void
  */
 public function accessed($path, $time = null)
 {
     // If no time has been given, default to current time.
     $time = $time === null ? time() : $time;
     // Insert new cached item into meta data storage
     $query = $this->db->prepare('UPDATE metadata SET accessed = :accessed WHERE path = :path');
     $query->bindValue(':path', $path);
     $query->bindValue(':accessed', $time);
     $query->execute();
 }
예제 #18
0
function parseCSV($file)
{
    echo "Function<br>";
    $db = new SQLite3('band2.db');
    echo "DB<br>";
    if ($option === 2) {
        $stmt->{$db}->prepare("DELETE FROM {$table};");
        $stmt->execute();
    }
    if ($option != 0) {
        if (($handle = fopen($file, "r")) !== FALSE) {
            if ($table == "Student") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Student (section, studentid, firstname, lastname) VALUES(:1, :2, :3, :4);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_TEXT);
                    $stmt->bindValue(':2', $data[1], SQLITE3_INTEGER);
                    $stmt->bindValue(':3', $data[2], SQLITE3_TEXT);
                    $stmt->bindValue(':4', $data[3], SQLITE3_TEXT);
                    $stmt->execute();
                }
            }
            if ($table == "Instrument") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Instrument (instrumentserial, instrumentManufacturer, instrumentname) VALUES(:1, :2, :3);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_TEXT);
                    $stmt->bindValue(':2', $data[1], SQLITE3_TEXT);
                    $stmt->bindValue(':3', $data[2], SQLITE3_TEXT);
                    $stmt->execute();
                }
            }
            if ($table == "Uniform") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Uniform (itemtype, itemnumber) VALUES(:1, :2);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_INTEGER);
                    $stmt->bindValue(':2', $data[1], SQLITE3_INTEGER);
                    $stmt->execute();
                }
            }
            fclose($handle);
        }
    }
}
예제 #19
0
 public function addComment($code, $comment)
 {
     $this->db->exec('BEGIN');
     $sql = 'UPDATE companyDetails SET comment=:comment WHERE proposedCode=:code';
     $stmt = $this->db->prepare($sql);
     $stmt->bindValue(':comment', trim($comment));
     $stmt->bindValue(':code', trim($code));
     $result = $stmt->execute();
     var_dump($result->numColumns());
     $this->db->exec('COMMIT');
 }
예제 #20
0
 public function allTimePlays(SSLTrack $track)
 {
     $statement = $this->dbo->prepare("SELECT COUNT(*) FROM history WHERE title=:title AND artist=:artist AND played=1");
     $statement->bindValue('title', $track->getTitle());
     $statement->bindValue('artist', $track->getArtist());
     $results = $statement->execute()->fetchArray();
     if ($results === false) {
         return 0;
     }
     return $results[0];
 }
 /**
  * Execute a prepared statement.
  *
  * @param string $statementQuery The statement query
  * @param array $parameters The statement parameters as map
  * @return \SQLite3Stmt
  */
 public function executeStatement($statementQuery, array $parameters)
 {
     $statement = $this->connection->prepare($statementQuery);
     foreach ($parameters as $parameterName => $parameterValue) {
         $statement->bindValue($parameterName, $parameterValue);
     }
     $result = $statement->execute();
     $resultArray = array();
     while ($resultRow = $result->fetchArray(SQLITE3_ASSOC)) {
         $resultArray[] = $resultRow;
     }
     return $resultArray;
 }
예제 #22
0
 public function updatePlayer(IPlayer $player, $lastIP = null, $loginDate = null)
 {
     $name = trim(strtolower($player->getName()));
     if ($lastIP !== null) {
         $prepare = $this->database->prepare("UPDATE players SET lastip = :lastip WHERE name = :name");
         $prepare->bindValue(":name", $name, SQLITE3_TEXT);
         $prepare->bindValue(":lastip", $lastIP, SQLITE3_TEXT);
         $prepare->execute();
     }
     if ($loginDate !== null) {
         $prepare = $this->database->prepare("UPDATE players SET logindate = :logindate WHERE name = :name");
         $prepare->bindValue(":name", $name, SQLITE3_TEXT);
         $prepare->bindValue(":logindate", $loginDate, SQLITE3_TEXT);
         $prepare->execute();
     }
 }
 /**
  * @param \SQLite3        $db
  * @param string          $table
  * @param array           $fieldsFilter
  * @param array           $fieldsFullText
  * @param boolean         $useuid
  * @param OutputInterface $output
  *
  * @throws \Exception
  */
 public function __construct($db, $table, array $fieldsFilter, array $fieldsFullText, $useuid, OutputInterface $output)
 {
     $this->output = $output;
     $this->db = $db;
     $this->fieldsFilter = $fieldsFilter;
     $this->fieldsFullText = $fieldsFullText;
     $this->useuid = $useuid;
     $tableFilter = "{$table}F";
     $tableFullText = "{$table}FT";
     if ($useuid) {
         $useuid = 'uid UNIQUE,';
     }
     if (sizeof($fieldsFilter) > 0) {
         $sqlfieldsFilter = implode("','", array_keys($fieldsFilter));
         $createSQLFilter = "CREATE TABLE {$tableFilter}(docid INTEGER PRIMARY KEY, {$useuid} json, '{$sqlfieldsFilter}')";
     } else {
         $createSQLFilter = "CREATE TABLE {$tableFilter}(docid INTEGER PRIMARY KEY, {$useuid} json)";
     }
     if ($this->db->exec($createSQLFilter) === false) {
         $this->output->writeln($createSQLFilter);
         $this->output->writeln($this->db->lastErrorCode() . " : " . $this->db->lastErrorMsg());
         throw new \Exception("cannot create table : " . $tableFilter);
     }
     if (sizeof($fieldsFullText) <= 0) {
         throw new \Exception("You must have at least one field full text");
     }
     $sqlfieldsFullText = implode("','", array_keys($fieldsFullText));
     $createSQLFullText = "CREATE VIRTUAL TABLE {$tableFullText} USING fts4('{$sqlfieldsFullText}');";
     if ($this->db->exec($createSQLFullText) === false) {
         $this->output->writeln($createSQLFullText);
         $this->output->writeln($this->db->lastErrorCode() . " : " . $this->db->lastErrorMsg());
         throw new \Exception("cannot create table : " . $tableFullText);
     }
     $values = implode(",", array_fill(0, sizeof($this->fieldsFilter), '?'));
     if ($this->useuid) {
         $prepareInsert = "INSERT INTO {$tableFilter} VALUES (?, ?, ?, {$values})";
     } else {
         $prepareInsert = "INSERT INTO {$tableFilter} VALUES (?, ?, {$values})";
     }
     $this->stmtInsertFilter = $this->db->prepare($prepareInsert);
     $values = implode(",", array_fill(0, sizeof($this->fieldsFullText), '?'));
     $prepareInsert = "INSERT INTO {$tableFullText}(docid,'{$sqlfieldsFullText}') VALUES (?,{$values})";
     $this->stmtInsertFullText = $this->db->prepare($prepareInsert);
 }
예제 #24
0
 public function run()
 {
     $project = $this->config->project;
     if ($project == 'default') {
         throw new ProjectNeeded();
     }
     if (!file_exists($this->config->projects_root . '/projects/' . $project . '/')) {
         throw new NoSuchProject($this->config);
     }
     $this->addSnitch();
     $sqliteFile = $this->config->projects_root . '/projects/' . $this->config->project . '/magicnumber.sqlite';
     if (file_exists($sqliteFile)) {
         unlink($sqliteFile);
     }
     $sqlite = new \SQLite3($sqliteFile);
     $types = array('Integer', 'String', 'Real');
     foreach ($types as $type) {
         $query = 'g.V().hasLabel("' . $type . '").groupCount("a").by("code").cap("a");';
         $res = $this->gremlin->query($query);
         $res = $res->results;
         $sqlite->exec('CREATE TABLE ' . $type . ' (id INTEGER PRIMARY KEY, value STRING, count INTEGER)');
         $stmt = $sqlite->prepare('INSERT INTO ' . $type . ' (value, count) VALUES(:value, :count)');
         $total = 0;
         foreach ($res[0] as $value => $count) {
             $stmt->bindValue(':value', $value, \SQLITE3_TEXT);
             $stmt->bindValue(':count', $count, \SQLITE3_INTEGER);
             $stmt->execute();
             ++$total;
         }
         display("{$type} : {$total}\n");
     }
     // export big arrays (more than 10)
     $res = $this->gremlin->query('g.V().hasLabel("Functioncall").has("token", "T_ARRAY").where( __.out("ARGUMENTS").has( "count", is(gte(10))) ).values("fullcode")');
     $res = $res->results;
     $outputFile = fopen($this->config->projects_root . '/projects/' . $this->config->project . '/bigArrays.txt', 'w+');
     foreach ($res as $v) {
         fwrite($outputFile, $v . "\n");
     }
     fclose($outputFile);
     $this->removeSnitch();
     display("array : " . count($res) . "\n");
 }
예제 #25
0
 /**
  *	Customised query method, with built in prepared statements if necessary.
  */
 public function query($sQuery, array $aArguments = null)
 {
     ++$this->count;
     if ($aArguments === null) {
         return parent::query($sQuery);
     }
     $pStatement = parent::prepare($sQuery);
     $iFragmentPosition = 0;
     $iArgumentType = null;
     foreach ($aArguments as $sKey => &$mArgument) {
         if (is_string($mArgument)) {
             $iArgumentType = SQLITE3_TEXT;
         } elseif (is_integer($mArgument)) {
             $iArgumentType = SQLITE3_INTEGER;
         } elseif (is_bool($mArgument)) {
             $mArgument = (int) $mArgument;
             $iArgumentType = SQLITE3_INTEGER;
         } elseif (is_float($mArgument)) {
             $iArgumentType = SQLITE3_FLOAT;
         } elseif (is_object($mArgument)) {
             $pReflection = new ReflectionClass($mArgument);
             if ($pReflection->hasMethod("__toString")) {
                 $mArgument = (string) $mArgument;
                 $iArgumentType = SQLITE3_TEXT;
             } else {
                 $mArgument = serialize($mArgument);
                 $iArgumentType = SQLITE3_BLOB;
             }
         } else {
             $mArgument = serialize($mArgument);
             $iArgumentType = SQLITE3_BLOB;
         }
         if (is_numeric($sKey)) {
             $pStatement->bindParam(++$iFragmentPosition, $mArgument, $iArgumentType);
         } else {
             $pStatement->bindParam((string) $sKey, $mArgument, $iArgumentType);
         }
     }
     return $pStatement->execute();
 }
예제 #26
0
파일: index.php 프로젝트: 4000Eyes/effres
function getTitlesFromCache($keyword)
{
    $sql = "SELECT title_name FROM eff_title where lower(title_name) like :query order by title_name";
    error_log("I am inside the function");
    try {
        $dbhandle = new SQLite3('../../effres/data/effres.db');
        if (!$dbhandle) {
            die($error);
        }
        $stmt = $dbhandle->prepare($sql);
        $keyword = "%" . $keyword . "%";
        $stmt->bindValue("query", $keyword);
        $result = $stmt->execute();
        //$rows = count($result);
        //error_log("After execution" . $rows);
        while ($row = $result->fetchArray()) {
            json_encode($row['title_name']);
        }
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
    }
}
예제 #27
0
/**
 *This function attempts to verify a user's priveleges and connect them to
 * the database.
 * @param type $db_path The path to the database.
 * @param type $privelege The user's access rights.
 * @return SQLite3 A handle to the database if priveleges are sufficient or false otherwise.
 */
function connect($db_path, $privelege)
{
    // Check to see that all parameters have been set
    if (!(isset($db_path, $privelege) && is_string($db_path) && is_string($privelege))) {
        die('Invalid parameters.');
    }
    $dbh = new SQLite3($db_path);
    // Check that the database was accessed succesffuly
    if (!$dbh) {
        die('Database cannot be accessed.');
    }
    // Query the database for the permissions of the current user
    $statement = $dbh->prepare('SELECT UserType FROM User WHERE Username=:name');
    if (!$statement) {
        $dbh->close();
        die('Query not recognized.');
    }
    $statement->bindParam(':name', $_SESSION['name'], SQLITE3_TEXT);
    $result = $statement->execute();
    if (!$result) {
        $statement->close();
        $dbh->close();
        die("Invalid query.");
    }
    $row = $result->fetchArray(SQLITE3_ASSOC);
    // If the user's account type does not have the priveleges required...
    if ($row['UserType'] != $privelege) {
        //If session not registered
        header("location:login.php?msg=You must have {$privelege} priveleges to access this page.");
        // Redirect to login.php page
        return false;
    } else {
        //Continue to current page
        header('Content-Type: text/html; charset=utf-8');
    }
    $result->finalize();
    $statement->close();
    return $dbh;
}
 public function preparedQuery($sql, $parameters, $errorLevel = E_USER_ERROR)
 {
     // Type check, identify, and prepare parameters for passing to the statement bind function
     $parsedParameters = $this->parsePreparedParameters($parameters);
     // Prepare statement
     $statement = @$this->dbConn->prepare($sql);
     if ($statement) {
         // Bind and run to statement
         for ($i = 0; $i < count($parsedParameters); $i++) {
             $value = $parsedParameters[$i]['value'];
             $type = $parsedParameters[$i]['type'];
             $statement->bindValue($i + 1, $value, $type);
         }
         // Return successful result
         $handle = $statement->execute();
         if ($handle) {
             return new SQLite3Query($this, $handle);
         }
     }
     // Handle error
     $values = $this->parameterValues($parameters);
     $this->databaseError($this->getLastError(), $errorLevel, $sql, $values);
     return null;
 }
예제 #29
0


	<header>
		<a href="mapas.php"><img  style="margin-top:20px;margin-left:30px;widht:130px;height:130px" src="icones/logo.png"></a>
		<a href="sobre.html"><img align="right" style="margin-top:40px;margin-right:50px" src="icones/sobre_escuro.png"></a>
		<a href="favoritos.php"><img align="right" style="margin-top:37px;margin-right:40px" src="icones/favoritos_escuro.png"></a>
		<a href="locais.php"><img align="right" style="margin-top:34px;margin-right:40px" src="icones/locais_claro.png"></a>
		<a href="receitas.php"><img align="right" style="margin-top:38px;margin-right:40px" src="icones/receitas_escuro.png"></a>
		<a href="mapas.html"><img align="right" style="margin-top:42px;margin-right:40px" src="icones/mapa_escuro.png"></a>
	</header>
	<div class="local">
		<?php 
$id = $_GET["id"];
$db = new SQLite3('nutrimapa.sqlite') or die('Unable to open database');
$statement = $db->prepare('SELECT * FROM locais WHERE id = :id;');
$endereco = $db->query('SELECT * FROM enderecos WHERE lid =' . $id);
$statement->bindValue(':id', $id);
$result = $statement->execute();
$row = $result->fetchArray();
echo "<div id=\"imagem_local\">";
echo "<h1 id=\"plocal\">{$row['nome']}</h1><br>";
echo "<img src=\"imglojas/{$row['imagem']}\" width =\"300\" height=\"267\">";
echo "</div>";
echo "<div id=\"description\">";
echo "<h2 id=\"textlocal\"> {$row['categoria']} </h2>";
echo "<p id=\"intro\">{$row['descricao']}<br><br><br></p>";
while ($row2 = $endereco->fetchArray()) {
    echo "<p id=\"address\">{$row2['endereco']} -</p>";
    echo "<p id=\"address2\">{$row2['horario']}<br><br></p>";
}
 /**
  * @param $ip
  * @return bool whether the IP is already registered
  */
 public function touchIP($ip)
 {
     $op = $this->db->prepare("SELECT * FROM ips WHERE ip = :ip;");
     $op->bindValue(":ip", $bin = implode("", array_map(function ($intStr) {
         return chr(intval($intStr));
     }, explode(".", $ip))));
     $exists = is_array($op->execute()->fetchArray(SQLITE3_ASSOC));
     if ($exists) {
         return true;
     }
     $op = $this->db->prepare("INSERT INTO ips VALUES (:ip);");
     $op->bindValue(":ip", $bin);
     $op->execute();
     return false;
 }