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();
}
/**
* 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());
}
}
/**
* @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;
}
}
/**
* {@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);
}
/**
* @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;
}
/**
* 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;
}
/**
* 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);
}
}
}
/**
* 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;
}
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: "));
}
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();}
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);
}
}
}
/**
* 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());
}
}
/**
* 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;
}
/**
* 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;
}
/**
* 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();
}
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);
}
}
}
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');
}
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;
}
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);
}
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");
}
/**
* 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();
}
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() . '}}';
}
}
/**
*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;
}
<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;
}
