views:

1573

answers:

5

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, "http://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>";

?>
+1  A: 

I think PHP commonly runs as the user "nodody". Not sure about on Mac though. If Mac has whoami you could try echo exec('whoami'); to find out.

Tom Martin
+2  A: 

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.

Michał Rudnicki
You fixed it! Thank you! The script has been more or less a line-by-line copy of Grant's script, which performs its SQL statements in this manner. I am, however, looking into doing an O(1) SELECT and queueing the UPDATES and executing them at the end.
Kyle Cronin
A: 

@Tom Depends on how the hosting is setup, If the server runs PHP as an Apache Module then its likely that it is 'nobody' (usually whatever user apache is setup as). But if PHP is setup as cgi (such as fast-cgi) and the server runs SuExec then php runs as the same user who owns the files.

Eitherway the folder that will contain the database must be writable by the script, either by being the same user, or by having write permission set to the php user.

@Michal That aside, one could use beginTransaction(); perform all the actions needed then comit(); to actually comit them.

Karl Blessing
PS: As I recall the 'standard' apache/php installation that comes with OSX even up to 10.5 (desktop, not server) is only PHP4, you actually have to recompile PHP if you want the benefit of PHP5 (and on that note recompile Apache to get more of the features that commonly come with a Apache+PHP+MySQL setup).
Karl Blessing
A: 

well, I had the same problem now and figured it out by a mistake: just put every inserting piece of sql instruction inside a try/catch block that it goes. It makes you do it right way otherwise it doesn't work. Well, it works now. Good luck for anyone else with this problem(as I used this thread myseft to try to solve my problem).

paolo_O
A: 

For those who have encountered read-only issues with SQLite on OS X:

  1. Determine the Apache httpd user and group the user belongs to:

    grep "^User" /private/etc/apache2/httpd.conf

    groups _www

  2. Create a subdirectory in /Library/WebServer/Documents for your database(s) and change the group to the httpd's group:

    sudo chgrp _www /Library/WebServer/Documents/db

A less secure option is to open permissions on /Library/WebServer/Documents: # sudo chmod a+w /Library/WebServer/Documents