SQLite/PHP read-only?

Question

I've been trying to use SQLite with the PDO wrapper in PHP with mixed success. I can read from the database fine, but none of my updates are being committed to the database when I view the page in the browser. Curiously, running the script from my shell does update the database. I suspected file permissions as the culprit, but even with the database providing full access (chmod 777) the problem persists. Should I try changing the file owner? If so, what to?

By the way, my machine is the standard Mac OS X Leopard install with PHP activated.

@Tom Martin

Thank you for your reply. I just ran your code and it looks like PHP runs as user _www. I then tried chowning the database to be owned by _www, but that didn't work either.

I should also note that PDO's errorInfo function doesn't indicate an error took place. Could this be a setting with PDO somehow opening the database for read-only? I've heard that SQLite performs write locks on the entire file. Is it possible that the database is locked by something else preventing the write?

I've decided to include the code in question. This is going to be more or less a port of Grant's script to PHP. So far it's just the Questions section:

<?php

$db = new PDO('sqlite:test.db');

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://stackoverflow.com/users/658/kyle");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_COOKIE, "shhsecret=1293706652");
$page = curl_exec($ch);

preg_match('/summarycount">.*?([,\d]+)<\/div>.*?Reputation/s', $page, $rep);
$rep = preg_replace("/,/", "", $rep[1]);

preg_match('/iv class="summarycount".{10,60} (\d+)<\/d.{10,140}Badges/s', $page, $badge);
$badge = $badge[1];

$qreg = '/question-summary narrow.*?vote-count-post"><strong.*?>(-?\d*).*?\/questions\/(\d*).*?>(.*?)<\/a>/s';
preg_match_all($qreg, $page, $questions, PREG_SET_ORDER);

$areg = '/(answer-summary"><a href="\/questions\/(\d*).*?votes.*?>(-?\d+).*?href.*?>(.*?)<.a)/s';
preg_match_all($areg, $page, $answers, PREG_SET_ORDER);

echo "<h3>Questions:</h3>\n";
echo "<table cellpadding=\"3\">\n";

foreach ($questions as $q)
{
    $query = 'SELECT count(id), votes FROM Questions WHERE id = '.$q[2].' AND type=0;';
    $dbitem = $db->query($query)->fetch(PDO::FETCH_ASSOC);
    if ($dbitem['count(id)'] > 0)
    {
        $lastQ = $q[1] - $dbitem['votes'];
        if ($lastQ == 0)
        {
            $lastQ = "";
        }
        $query = "UPDATE Questions SET votes = '$q[1]' WHERE id = '$q[2]'";
        $db->exec($query);
    }
    else
    {
        $query = "INSERT INTO Questions VALUES('$q[3]', '$q[1]', 0, '$q[2]')";
        echo "$query\n";
        $db->exec($query);
        $lastQ = "(NEW)";
    }
    echo "<tr><td>$lastQ</td><td align=\"right\">$q[1]</td><td>$q[3]</td></tr>\n";
}

echo "</table>";

?>

Answer 1

Kyle, in order for PDO/Sqlite to work you need write permission to directory where your database resides.

Also, I see you perform multiple selects in loop. This may be ok if you are building something small and not heavy loaded. Otherwise I'd suggest building single query that returns multiple rows and process them in separate loop.

Answer 2

I found the answer on the PHP manual "The folder that houses the database file must be writeable."