On Mon, 31 Jan 2011 14:05:15 -0500, Gabe da Silveira <g...@websaviour.com> wrote: [...] > On Mon, Jan 31, 2011 at 1:24 PM, Samuel Adam <a...@certifound.com> wrote: [...] >> * 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
I am not too familiar with PDO. For the reasons set forth below, however, the important part is the parameter binding. (For the list archives, the code you linked indeed binds as INTEGER.) > Good tip, I was not aware of typeof(). The data is indeed differnt: > > https://gist.github.com/b4008e84726e2014b16a Since you are using parameter binding, I believe Scott Hess answered this earlier. On Mon, 31 Jan 2011 13:16:55 -0500, Mr. Hess <sh...@google.com> wrote: > The FTS3 code mostly passes values directly down to and up from the > underlying tables, so if you bound an integer, it is likely to come > back out as an integer when you query. But since FTS3 doesn't make > any explicit provision for this, I wouldn't recommend depending on it > in your code, unless you're happy with periodically finding the edge > cases where your integer unexpectedly turns into text. FTS3 accesses > the data as TEXT only, without regard to type. In C parlance, what you are doing is undefined behavior. On one box, you found “the edge [case] where your integer unexpectedly turns into text”, and on the other box, you didn’t. I suggested rewriting your schema. Non-TEXT data which will not be subjected to a MATCH search is best stored in another table and JOINed with the FTS3 table, as Mr. Hess also explained. Also, specifications such as VARCHAR(255) are not meaningful to SQLite3; see http://www.sqlite.org/datatype3.html . As an added bonus, this investigation uncovered behavior which is either an FTS3 bug or a doc bug, per my previous mails. Very truly, Samuel Adam <a...@certifound.com> 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
