First of all, thank ou both Simon and Samuel for such thorough consideration of this problem. I've been in meetings all afternoon (London time) and will respond to each email where appropriate in turn...
On Mon, Jan 31, 2011 at 1:24 PM, Samuel Adam <[email protected]> wrote: > On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin <[email protected]> > wrote: > * Is PHP’s PDO being used here, or PHP’s Sqlite3 class? PDO > * Are weird PHP settings re magic quotes, etc. different between the > platforms? (That could make the quoted string not work on one > platform—but it doesn’t explain in the other direction.) magic_quotes_gpc is on on Gentoo where the db is generated such that it requires quotes (which I understand is what it should be, though our application was built with the assumption that it shouldn't be quoted, and that is the hard one to change because it's a ps3 application distributed through the PSN store requiring a heavyweight patch process). Of course there is no web request involved, so magic_quotes_gpc seems irrelevant. I'll try to produce an isolated test case. > * Is the database being populated with parameter binding, or not? If > so, > is id being bound as SQLITE3_INTEGER? (I don’t know off the top of my > head what that will do with an FTS3 table.) Yes, PDO binding. Here's the actual code to show how parameters are bound at the PHP level: https://gist.github.com/23ce0a99a0c0a1c059d2 > * Are the sqlite3 library versions the same on all platforms? (AFAIK > fts3 has been under heavy development. The whole problem could simply be > a difference in behavior between versions.) Here's the problem. I work at a startup using OS X for development and Gentoo for servers. We build the database on these two environments. We are working on a ps3 application with an external team using Windows and a PS3 devkit, deploying an application to retail PS3s. I only have access to the former environment, and fortunately the change in behavior was between my two environments. Grepping php -i I get On OS X (requires no quotes around id): PECL Module version => 2.0-dev $Id: sqlite.c 298697 2010-04-28 12:10:10Z iliaa $ On Gentoo (requires quotes around id): PECL Module version => 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.3 2007/12/31 07:20:10 sebastian Exp $ PECL Module version => 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.11 2008/12/01 12:28:27 felipe Exp $ > * By the way, I don’t know whether the sqlite3 bundled in PHP even > includes/builds with fts3. I have no idea either if this affects the PDO > driver, or if that uses a system-installed library. You may want to check > to see where your PHP is getting its sqlite3 with fts3 support. I believe we had to specifically recompile sqlite3 to get fts3 on gentoo. On OS X it worked out of the box, although interestingly the built-in sqlite3 CLI client somehow does not have fts3, because I get an error when I try to use the database file locally. I guess the library that PHP is linked to must be compiled differently, because the DB was definitely building correctly (aside from the small semantic error). > Better approach: Cut PHP from the equation and examine the databases in > the sqlite3 shell. Core function typeof() might be helpful to see if the > tables actually contain different data, as they most probably do not. > (Best approach: Cut PHP from the equation, period.) Good tip, I was not aware of typeof(). The data is indeed differnt: https://gist.github.com/b4008e84726e2014b16a _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
