PHP SQLite3类代码示例

function get_db()
{
    global $authdb;
    $db = new SQLite3($authdb, SQLITE3_OPEN_READWRITE);
    $db->exec('PRAGMA foreign_keys = 1');
    return $db;
}

 public function mainAction()
 {
     $this->dbconf = Phalcon\DI::getDefault()->getConfig()->database;
     $file = $this->getDI()->getConfig()->dirs->config . DIRECTORY_SEPARATOR . 'schema' . DIRECTORY_SEPARATOR . $this->dbconf->adapter . '.sql';
     $this->schema = realpath($file);
     if ($this->schema === false) {
         throw new \Exception('Unsupported database adapter: ' . $this->dbconf->adapter);
     }
     echo "{$this->dbconf->adapter}\n";
     echo $this->dropDatabase();
     switch ($this->dbconf->adapter) {
         case 'mysql':
             echo $this->createDatabase();
             echo $this->loadSchema();
             echo $this->loadFixtures();
             break;
         case 'sqlite':
             $dbfile = $this->di->getConfig()->dirs->data . DIRECTORY_SEPARATOR . $this->di->get('config')->database->dbname . '.sqlite';
             $db = new SQLite3($dbfile);
             chmod($dbfile, 0664);
             $db->createFunction('MD5', 'md5');
             $db->exec(file_get_contents($this->schema));
             $db->exec(file_get_contents(preg_replace('/sqlite.sql$/', 'fixtures.sql', $this->schema)));
             break;
         default:
             throw new \Exception('Unsupported database adapter: ' . $this->dbconf->adapter);
             break;
     }
 }

/**
* Checks which files of a directory are missing in a SQLite3 database and returns a list of them.
*
* @arg dir The directory for which to check
* @arg dbfile The file containing the database
* @arg table The table name of the database
* @arg col The column containing the filenames
* @arg enckey The encryption key used for the database
* @returns A list of files missing from the database, or an empty list
*/
function missing_files_from_directory($dir, $dbfile, $table, $col, $enckey = NULL)
{
    $missing = array();
    $dirscan = scandir($dir, SCANDIR_SORT_ASCENDING);
    if ($dirscan == false) {
        // Either $dir is not a directory or scandir had no success
        return $missing;
    }
    try {
        if (is_string($enckey)) {
            $db = new SQLite3($dbfile, SQLITE3_OPEN_READONLY, $enckey);
        } else {
            $db = new SQLite3($dbfile, SQLITE3_OPEN_READONLY);
        }
    } catch (Exception $e) {
        // Database could not be opened; return empty array
        return $missing;
    }
    foreach ($dirscan as $file) {
        if (is_dir($file) || is_link($file)) {
            // Filtering out directories (. and ..) and links {
            continue;
        }
        if ($db->querySingle("SELECT EXISTS(SELECT * FROM " . $table . " WHERE " . $col . " = '" . SQLite3::escapeString($file) . "');")) {
            // if an entry exists, returns TRUE, otherwise FALSE; invalid or failing queries return FALSE
            continue;
        }
        // entry does not exist; add to array
        $missing[] = $file;
    }
    $db->close();
    sort($missing, SORT_LOCALE_STRING | SORT_FLAG_CASE);
    return $missing;
    // sort based on the locale, case-insensitive
}

/**
 * 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;
}

function check_links()
{
    //Before checking anything, check if laurentian.concat.ca is resolving.
    $catalogue_link = fopen("http://laurentian.concat.ca/", "r");
    if (!$catalogue_link) {
        die("There may be a problem with laurentian.concat.ca so this process is going to halt. If this persists, contact Kevin Beswick");
    }
    fclose($catalogue_link);
    //Check all links from database, if active, leave alone, if not active, delete them.
    $db_session = new SQLite3('reserves.db');
    $query = "SELECT bookbag_id from reserve";
    $results = $db_session->query($query) or die($db_session->lastErrorMsg());
    $begin_link = "http://laurentian.concat.ca/opac/extras/feed/bookbag/opac/";
    $end_link = "?skin=lul";
    $count = 0;
    while ($row = $results->fetchArray()) {
        $file = fopen($begin_link . $row["bookbag_id"] . $end_link, "r");
        if (!$file) {
            //remove from list
            $query = "DELETE from reserve where bookbag_id = " . $row["bookbag_id"];
            $db_session->exec($query) or die("not working... " . $db_session->lastErrorMsg());
            $count++;
        }
        fclose($file);
    }
    echo "Done removing dead links... " . $count . " were removed.";
}

function getCars($query)
{
    $db = new SQLite3('car.db');
    $results = $db->query($query);
    echo "<table class=\"search-results\">";
    echo "<th>Reg nummer</th>";
    echo "<th>Märke</th>";
    echo "<th>Modell</th>";
    echo "<th>Pris</th>";
    echo "<th>Antal mil</th>";
    echo "<th>Årsmodell</th>";
    echo "<th>Redigera</th>";
    $numRows = 0;
    while ($row = $results->fetchArray()) {
        $numRows++;
    }
    if ($numRows === 0) {
        echo "<tr><td colspan=\"8\">Inga resultat för: \"" . $_POST['search'] . "\"</td></tr>";
    } else {
        while ($row = $results->fetchArray()) {
            echo "<tr>\n                <td class=\"car-regnr\"><a data-id=\"{$row['id']}\" href=\"#\" class=\"car-info\">{$row['regnr']}</td>\n                <td class=\"car-brand\">{$row['brand']}</td>\n                <td class=\"car-model\">{$row['model']}</td>\n                <td class=\"car-price\">{$row['price']}</td>\n                <td class=\"car-milage\">{$row['milage']}</td>\n                <td class=\"car-year\">{$row['year']}</td>\n                <td class=\"car-edit-link\"><a data-id=\"{$row['id']}\" href=\"#\" class=\"car-info\">Redigera</a></td>\n                </tr>";
        }
        echo "</table>";
    }
    $db->close();
}

function anubisFILE($idmd5, $fileName)
{
    #Execute the Python Script
    #python /var/www/anubis/submit_to_anubis.py /var/www/mastiff/MD5/filename.VIR
    $command = 'python /var/www/anubis/submit_to_anubis.py -u ' . $anubisUser . ' -p ' . $anubisPass . ' "/var/www/mastiff/' . $idmd5 . '/' . $fileName . '"';
    $output = shell_exec($command);
    $anubisRes['out'] = $output;
    $pattern = '/https?\\:\\/\\/[^\\" ]+/i';
    preg_match($pattern, $output, $matches);
    #echo '<pre>';
    #	echo '$matches: ';
    #	var_dump($matches);
    #echo '</pre>';
    $anubisLink = $matches[0];
    $anubisLink = strstr($anubisLink, "\n", true);
    $anubisRes['link'] = $anubisLink;
    #Update the Database
    $db = new SQLite3('../mastiff/mastiff.db');
    $result = $db->exec('UPDATE mastiff SET anubis = "' . $anubisLink . '" WHERE md5 = "' . $idmd5 . '"');
    if (!$result) {
        $anubisRes['db'] = $db->lastErrorMsg();
    } else {
        $anubisRes['db'] = $db->changes() . ' Record updated successfully.';
    }
    return $anubisRes;
}

function ConvertDB($fname)
{
    //	echo "=> {$fname}\n";
    if (!file_exists($fname)) {
        exit(10);
    }
    $db = new SQLite3('base.sqlite');
    $db->exec("pragma synchronous = off;");
    $tablename = pathinfo($fname, PATHINFO_FILENAME);
    $pdx = new Paradox();
    $pdx->Open($fname);
    $db->exec(createdb_schema($tablename, $pdx->GetSchema()));
    $db->exec('DELETE FROM ' . $tablename);
    if ($records = $pdx->GetNumRows()) {
        $schema = $pdx->GetSchema();
        //		print_r($schema);
        for ($rec = 0; $rec < $records; $rec++) {
            $pdx->GetRow($rec);
            //			if ($rec > 2) break;
            $query = 'INSERT INTO `' . $tablename . '` VALUES (';
            $values = '';
            foreach ($pdx->row as $fieldName => $value) {
                switch ($schema[$fieldName]['type']) {
                    case 1:
                        $value = brackets(iconv('windows-1251', 'UTF-8', $value));
                        break;
                    case 2:
                        $value = brackets($pdx->GetStringfromDate($value));
                        break;
                    case 21:
                        $value = brackets($pdx->GetStringfromTimestamp($value));
                        break;
                    case 4:
                        $value = (int) $value;
                        break;
                    case 6:
                        $value = (double) $value;
                        break;
                    case 9:
                        $value = (int) $value;
                        break;
                    case 13:
                        $value = "X" . brackets(bin2hex($value));
                        break;
                    default:
                        $value;
                        break;
                }
                $values .= $value . ', ';
                //				print "{$schema[$fieldName]['type']}\t{$fieldName}\t{$value}\n";
            }
            $values = rtrim($values, ', ');
            $query .= $values . ");\n";
            //			print trim($query).PHP_EOL;
            $db->exec($query);
        }
        return true;
    }
    $pdx->Close();
}

 /**
  * @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();
 }

 public static function getDB()
 {
     $con = new SQLite3('db/newsdb.db');
     $con->exec('PRAGMA foreign_keys = ON;');
     return $con;
     #return new SQLite3('../sqlite/newsdb.db.new');
 }

function caching($comics_id, $zip_path, $image_ext)
{
    $comic = zip_open($zip_path);
    if (!is_resource($comic)) {
        die("[ERR]ZIP_OPEN : " . $zip_path);
    }
    $inzip_path = "";
    $count = 0;
    $files = null;
    $db = new SQLite3(DB);
    $db->exec("BEGIN DEFERRED;");
    while (($entry = zip_read($comic)) !== false) {
        $inzip_path = zip_entry_name($entry);
        $cache_name = md5($zip_path . "/" . $inzip_path) . '.' . get_ext($inzip_path);
        // 画像か否か
        if (!is_image($inzip_path, $image_ext)) {
            continue;
        }
        $data = zip_entry_read($entry, zip_entry_filesize($entry));
        $filepath = CACHE . '/' . $cache_name;
        file_put_contents($filepath, $data);
        $count++;
        query("INSERT INTO images (comics_id, page, filepath) VALUES (" . $comics_id . ", " . $count . ", '" . $filepath . "')", $db);
    }
    zip_close($comic);
    query("UPDATE comics SET pages = " . $count . " WHERE id = " . $comics_id, $db);
    $db->exec("COMMIT;");
}

    public function OpenLogFile()
    {
        $db = null;
        $filename = $this->logdir . "/" . $this->logfile;
        if (!file_exists($this->logdir)) {
            mkdir($this->logdir, 0755, true);
        }
        $db = new SQLite3($filename);
        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS account_attempt (
  time_attempted date not null default CURENT_TIMESTAMP,
  username text not null default '',
  email text not null default '',
  ip text not null default '',
  trigger text not null default '',
  confidence float not null default 0.0,
  accepted text default 'Y',
  verified text default 'N',
  primary key (time_attempted, username, email, ip)
)
SQL;
        $db->exec($sql);
        $sql = <<<IDXSQL
CREATE INDEX IF NOT EXISTS account_verification
ON account_attempt (accepted, verified)
IDXSQL;
        $db->exec($sql);
        return $db;
    }

function handle_admin()
{
    if (empty($_POST['admin_account']) == false && empty($_POST['admin_password']) == false) {
        $account = $_POST['admin_account'];
        $password = md5($_POST['admin_password']);
        //password: ccc95
        $file_path = "../sqlite/books_web.s3db";
        if (file_exists($file_path)) {
            $link = new SQLite3($file_path);
            $sql_cmd = "SELECT password FROM root_account WHERE password='{$password}'";
            $result = $link->query($sql_cmd);
            $i = 0;
            $row = array();
            while ($res = $result->fetchArray(SQLITE3_ASSOC)) {
                $row[$i]['password'] = $res['password'];
                $i++;
            }
            if (count($row) != 0) {
                $_SESSION['root'] = "root";
                return "admin login success.";
            } else {
                return "admin login failed.";
            }
            $link->close();
        } else {
            return "cannot link database.";
        }
    } else {
        return "post error";
    }
}

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());
    }
}

function get_mbtiles_data($file_path)
{
    $db_mbtiles = new SQLite3($file_path);
    // Min Zoom and Max Zoom
    $zoom_range_query = "select min(zoom_level), max(zoom_level) from tiles";
    $zoom_range_results = $db_mbtiles->query($zoom_range_query);
    $zoom_range = $zoom_range_results->fetchArray();
    $min_zoom = $zoom_range[0];
    $max_zoom = $zoom_range[1];
    // Get zoom, column, and row for each tile
    $query = "select zoom_level, avg(tile_column), avg(tile_row) from tiles group by zoom_level";
    $results = $db_mbtiles->query($query);
    $rows = array();
    while ($row = $results->fetchArray()) {
        $rows[] = $row;
    }
    $center_tile = $rows[floor(0.5 * count($rows))];
    $center_tile_zoom = $center_tile[0];
    $center_tile_x = floor($center_tile[1]);
    // Column
    $center_tile_y = round(pow(2, $center_tile_zoom) - $center_tile[2] - 1);
    // Converted Row
    $mbtiles_data = array("min_zoom" => $min_zoom, "max_zoom" => $max_zoom, "center_coordinates" => array("zoom" => $center_tile_zoom, "x" => intval($center_tile_x), "y" => intval($center_tile_y)));
    return $mbtiles_data;
}