Google gears uses sqlite for it’s client side database. I’ve never used sqlite so I went to the sqlite website. I naturally downloaded all their documentation to file allowing me to browse it offline. All companies should provide this ability. However the documentation only covered C/C++ and TCL.
I really wanted to use PHP but decided to use C/C++ because an example from their site was ready to go. I copied their example but could not compile it. First it was simply locating the proper sqlite3.h file. This was just the start of many errors so I gave up. If it does not work straight out of the box on the latest Ubuntu then there is something very wrong. I googled for C/C++ examples elsewhere but without luck.
So I tried googling for PHP CLI examples under Ubuntu. Not much luck but there was something close at http://www.litewebsite.com/?c=49#sqlite3 so I snagged it. Here it is, modified to run from the command line, so I don’t have to search for a half decent starting example again:
#!/usr/bin/php -q
<?php
//# Much of this I got from:
//# http://www.litewebsite.com/?c=49#sqlite3
//# http://www.devshed.com/c/a/PHP/Working-with-Prepared-Queries-with-PDO-Objects-in-PHP-5/
ini_set('display_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);
function CountFromPage($page) {
try{
$dbh = new PDO('sqlite:pdoTutorial.sqlite3');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$dbh->beginTransaction();
$statement = $dbh->query('SELECT name FROM sqlite_master WHERE type = \'table\'');
$result = $statement->fetchAll();
if( sizeof($result) == 0 ){ // No database tables found? (Will need to refine this check for pageView table only.)
$dbh->exec('CREATE TABLE pageView(id INTEGER PRIMARY KEY AUTOINCREMENT, page CHAR(256), access INTEGER(10))');
}
// insert page visit in database with a prepared statement
$stmt = $dbh->prepare('INSERT INTO pageView (page, access) VALUES (?, ?)');
$stmt->bindParam(1, $page, PDO::PARAM_STR);
$stmt->bindParam(2, time(), PDO::PARAM_INT);
$stmt->execute();
// get page views from database
$pageVisit = $dbh->quote($page);
$sqlGetView = 'SELECT count(page) AS view FROM pageView WHERE page = '.$pageVisit.'';
$result = $dbh->query($sqlGetView);
$pageView = $result->fetch(); // store result in array
//$dbh->commit();
return($pageView['view']);
}catch( PDOException $exception ){
//$dbh->rollBack();
die($exception->getMessage());
}
}
if (2 != $_SERVER["argc"]) {
echo "\tWrong number of arguments:" . $_SERVER["argc"] . "\n";
echo "\tProper syntax is: \n" ;
echo "\t\t" . $_SERVER["argv"][0] . " page\n";
} else {
$page = $_SERVER["argv"][1];
echo 'Page "' . $page . '" has been viewed '. CountFromPage($page).' times.'."\n";
}
?>
I stuck it into a file called 1.php in my samba bin directory as my starting point. This way the database can be shared…
Here is the output when run:
~/samba/bin$ 1.php 11
Page “11” has been viewed 1 times.
~/samba/bin$ 1.php 11
Page “11” has been viewed 2 times.
~/samba/bin$ 1.php 12
Page “12” has been viewed 1 times.
~/samba/bin$ 1.php 12
Page “12” has been viewed 2 times.
~/samba/bin$ 1.php 11
Page “11” has been viewed 3 times.
Ivor O'Connor 