Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
On Tue, Feb 1, 2011 at 3:38 AM, Tito Ciuro wrote: > Hello, > > The following code snippet runs fine on Mac OS X, but fails on the iOS > simulator: > >// Obtain a path for the database >NSString *docs = > [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, > YES) lastObject]; >NSString *path = [[docs stringByAppendingPathComponent:@ > "myDB.sqlite"]fileSystemRepresentation]; > >// Open the database >sqlite3 *db = NULL; >int statusOpen = sqlite3_open_v2( fileSystemRepresentation, &db, > SQLITE_OPEN_READWRITE | > SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL); > Do you need UTF8String for the sqlite3_open_v2() ? > >// Build the first statement >sqlite3_stmt *oneStatement = NULL; >const char *oneSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, > %@, %@, %@) VALUES (?,?,?,?);", NSFValues, NSFKey, NSFAttribute, NSFValue, > NSFDatatype]UTF8String]; >int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL), > &oneStatement, &oneSQL); > > >// Build the second statement >sqlite3_stmt *twoStatement = NULL; >const char *twoSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, > %@, %@, %@) VALUES (?,?,?,?);", NSFKeys, NSFKey, NSFPlist, NSFCalendarDate, > NSFObjectClass]UTF8String]; >int statusTwo = sqlite3_prepare_v2(db, twoSQL, (int)strlen(twoSQL), > &twoStatement, &twoSQL); > > What I see is that statusTwo returns 1, and I have no idea why. What is > really puzzling is that if I open the database in memory or temporary mode, > it works fine in both Mac OS X and iOS! > > So my question I have is, why would the second sqlite3_prepare_v2 statement > fail only on path-based iOS apps? :-/ > > Thanks in advance, > > -- Tito > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.5
SQLite version 3.7.5 is now available on the SQLite website http://www.sqlite.org/ Version 3.7.5 is a regularly scheduled bi-monthly release. However, upgrading is recommended due to an obscure bug fix that can under contrived conditions result in a corrupt database. See http://www.sqlite.org/src/tktview?name=5d863f876e for a description of the problem. This bug was found by code analysis and has never been observed in the wild. Version 3.7.5 also features the usual assortment of incremental improvements and enhancements. See http://www.sqlite.org/news.html and http://www.sqlite.org/releaselog/3_7_5.html for further information. Coverage testing remains at 100% and performance is improved over the previous release by 1% or 2%. As of this release, the popular ADO.NET provider for SQLite by Robert Simpson, System.Data.SQLite, is hosted on the SQLite website. See http://System.Data.SQLite.org/ for additional information. Release builds of System.Data.SQLite will appears on the SQLite download page over the course of the next week. As always, please send in reports of any problems you encounter. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 version 3.7.5: documentation of the "-heap" option
in the 3.7.5 releaselog (http://sqlite.org/releaselog/3_7_5.html) it is written that the "-heap" option was added to the CLI but if I follow http://sqlite.org/sqlite.html there is no further explanation. calling doesn't uncover information about this option too Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? 3.7.5 RC2 FTS4 division by zero exception
On Mon, Jan 31, 2011 at 6:44 PM, Samuel Adam wrote:
> On Mon, 31 Jan 2011 18:08:41 -0500, Ralf Junker wrote:
>
> > This SQL:
> >
> > drop table if exists t11;
> > CREATE VIRTUAL TABLE t11 USING fts4;
> > INSERT INTO t11 VALUES('quitealongstringoftext');
> > INSERT INTO t11 VALUES('anotherquitealongstringoftext');
> > UPDATE t11_stat SET value = X'';
>
> I actually think this is a documentation bug/failure-to-warn liability:
>
>WARNING: Deliberately junking FTS’ internal tables may
>not only corrupt your FTS table, but also cause SQLite
>to crash. Don’t do this. Also do not use the hair dryer
>while in the bathtub, attempt to stop the chainsaw’s chain
>with your hand, &c. Inserting peanuts into your SQLite
>database may cause it to contain nuts.
>
> Please add to FTS docs §6 so you can avoid being sued.
>
You are both right.
This problem cannot be hit unless you corrupt the shadow tables. On the
other hand, FTS4 ought to return SQLITE_CORRUPT rather than crash.
Because this problem cannot be hit without corrupting the shadow tables
first and in a very particular way, and because we already have the 3.7.5
release packaged and ready to push out to the website and to stop that and
patch this and retest would take a week, and because there are more pressing
concerns that are waiting on the 3.7.5 release, I'm going to defer fixing
this until 3.7.6.
Thanks for the bug report Ralf and for the analysis Samuel.
>
> > SELECT matchinfo(t11, 'nxa') FROM t11 WHERE t11 MATCH 'a*';
> >
> > leads to a division by zero exception in fts3_snippet.c, line 1171:
> >
> > pInfo->aMatchinfo[iCol] =
> > (u32)(((u32)(nToken&0x)+nDoc/2)/nDoc);
> >
> > nDoc is zero here, which is not tested for, but I believe it should.
> >
> > Ralf
>
> Does the crash also occur when the zero occurs naturally, or is that
> codepath not exercised? Perhaps an assert() is in order.
>
> Very truly,
>
> Samuel Adam
> 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
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? 3.7.5 RC2 FTS4 division by zero exception
On Mon, 31 Jan 2011 18:08:41 -0500, Ralf Junker wrote:
> This SQL:
>
> drop table if exists t11;
> CREATE VIRTUAL TABLE t11 USING fts4;
> INSERT INTO t11 VALUES('quitealongstringoftext');
> INSERT INTO t11 VALUES('anotherquitealongstringoftext');
> UPDATE t11_stat SET value = X'';
I actually think this is a documentation bug/failure-to-warn liability:
WARNING: Deliberately junking FTS’ internal tables may
not only corrupt your FTS table, but also cause SQLite
to crash. Don’t do this. Also do not use the hair dryer
while in the bathtub, attempt to stop the chainsaw’s chain
with your hand, &c. Inserting peanuts into your SQLite
database may cause it to contain nuts.
Please add to FTS docs §6 so you can avoid being sued.
> SELECT matchinfo(t11, 'nxa') FROM t11 WHERE t11 MATCH 'a*';
>
> leads to a division by zero exception in fts3_snippet.c, line 1171:
>
> pInfo->aMatchinfo[iCol] =
> (u32)(((u32)(nToken&0x)+nDoc/2)/nDoc);
>
> nDoc is zero here, which is not tested for, but I believe it should.
>
> Ralf
Does the crash also occur when the zero occurs naturally, or is that
codepath not exercised? Perhaps an assert() is in order.
Very truly,
Samuel Adam
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
[sqlite] Bug? 3.7.5 RC2 FTS4 division by zero exception
This SQL:
drop table if exists t11;
CREATE VIRTUAL TABLE t11 USING fts4;
INSERT INTO t11 VALUES('quitealongstringoftext');
INSERT INTO t11 VALUES('anotherquitealongstringoftext');
UPDATE t11_stat SET value = X'';
SELECT matchinfo(t11, 'nxa') FROM t11 WHERE t11 MATCH 'a*';
leads to a division by zero exception in fts3_snippet.c, line 1171:
pInfo->aMatchinfo[iCol] = (u32)(((u32)(nToken&0x)+nDoc/2)/nDoc);
nDoc is zero here, which is not tested for, but I believe it should.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Hello Simon, On Jan 31, 2011, at 8:29 PM, Simon Slavin wrote: > > On 31 Jan 2011, at 7:55pm, Tito Ciuro wrote: > >> What do you mean by "no further"? In my app, I use both statements. I have >> trimmed down the code in this email thread to show the error. Be sure I use >> these two statements later on. The reason I cache the statements is to save >> time during inserts, since I use them a lot. > > There's probably no reason not to do that. Sorry for any alarm. > >> If having two statements prepared is not permissible, should't it fail on >> Mac OS X as well? > > There are plenty of examples of SQLite stuff that succeeds in one compiler > and fails in another, or succeeds on one OS or File System and fails in > another. SQLite doesn't exhaustively error-check every operation because if > would slow it down too much. So the only things that are safe to do are the > things the documentation says you can do. > > But please ignore my previous question about having two simultaneous prepared > statements. > > Simon. No, problem, thanks for your help. After refactoring the code a bit and catching/reporting errors better, it started working again. I'm still not 100% sure why it didn't work before, which bugs me quite a bit. But, after spending some time testing it, I've had so far 100% success rate during testing and execution. Go figure... :-/ Best regards, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
On 31 Jan 2011, at 7:55pm, Tito Ciuro wrote: > What do you mean by "no further"? In my app, I use both statements. I have > trimmed down the code in this email thread to show the error. Be sure I use > these two statements later on. The reason I cache the statements is to save > time during inserts, since I use them a lot. There's probably no reason not to do that. Sorry for any alarm. > If having two statements prepared is not permissible, should't it fail on Mac > OS X as well? There are plenty of examples of SQLite stuff that succeeds in one compiler and fails in another, or succeeds on one OS or File System and fails in another. SQLite doesn't exhaustively error-check every operation because if would slow it down too much. So the only things that are safe to do are the things the documentation says you can do. But please ignore my previous question about having two simultaneous prepared statements. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 15:33:24 -0500, Gabe da Silveira wrote: > On Mon, Jan 31, 2011 at 7:42 PM, Samuel Adam wrote: > >> 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 . > > Agreed this would be nice, unfortunately we have an gaming console app > that's already been through the formal QA process based on a complex > SQL schema, and for which the developers are not under our budgetary > control. I'd say we have almost no chance of making this happen at > this point given the complexity of the application and database (the > two fts tables cut across a lot of concerns). I can’t help you there other than to say, give your boss the facts and point out that if QA approved that schema, QA failed its job. (Bear in mind that I’m the man who recently sued a state supreme court; characteristically any advice from me is focused on factual correctness solely, exclusively, and at the potential expense of other considerations such as keeping friends in a department which failed its job, or maintaining your state of employment.) You may also point out that you have a much bigger problem with your schema: > Also, I'm not sure how we would avoid the undefined case anyway, > because the primary keys for these things are all integers. Per my previous mail, FTS3 is quietly ignoring your PRIMARY KEY declaration on "id". To use your ID values as an INTEGER PRIMARY KEY, you will need to INSERT, SELECT, and otherwise manipulate your ID values using one of the identifiers “"docid" [] "rowid", "oid" [or] "_oid_"” according to FTS3 docs. Do the following query on your table to confirm this: SELECT "rowid", "id" FROM "Directors"; Per my tests, you will most probably find different values in each column. (If the values are same, this is a coincidence you cannot rely on.) The "rowid" is the actual PRIMARY KEY in an FTS3 table—and since you are not inserting values for it explicitly, it is automatically assigned. > Presumably this pass-through behavior is what allows integers to be > used as join columns even in an fts table. If this edge case > persists, where a bound integer ends up as a string internally, won't > joins fail as well? No. If you are using an integer PK to do the JOIN (which is a good idea), you need to join on "rowid" in your FTS3 table and an INTEGER PRIMARY KEY in your other table(s). With your current schema, your primary key constraint is not enforced; referential integrity is not guaranteed; and if bad data is accidentally inserted, joins (and other actions) may fail *even* on the box where your "id" is coming out as an INTEGER. > I'm working on a standalone test script to narrow down the problem... I suggest you refocus your efforts; unless you want to dig into FTS3 sources with an eye to patching for your needs, the problem is already diagnosed. Very truly, Samuel Adam 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
Re: [sqlite] integer or string in fts3 table
Here's a standalone script that provides an id field with an int on my local OS X machine as I desire, and creates an id field with text when run on the Gentoo machine as breaks the [difficult-to-patch] application: https://gist.github.com/aae2366637c94a2861ae Going home for the night now (London). Will play around with settings tomorrow... On Mon, Jan 31, 2011 at 8:33 PM, Gabe da Silveira wrote: > I'm working on a standalone test script to narrow down the problem... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, Jan 31, 2011 at 7:42 PM, Samuel Adam wrote: > 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 . Agreed this would be nice, unfortunately we have an gaming console app that's already been through the formal QA process based on a complex SQL schema, and for which the developers are not under our budgetary control. I'd say we have almost no chance of making this happen at this point given the complexity of the application and database (the two fts tables cut across a lot of concerns). Also, I'm not sure how we would avoid the undefined case anyway, because the primary keys for these things are all integers. Presumably this pass-through behavior is what allows integers to be used as join columns even in an fts table. If this edge case persists, where a bound integer ends up as a string internally, won't joins fail as well? I'm working on a standalone test script to narrow down the problem... more soon, and thanks for all your help, Gabe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Hi Simon, On Jan 31, 2011, at 5:49 PM, Simon Slavin wrote: > > On 31 Jan 2011, at 7:38pm, Tito Ciuro wrote: > >> So my question I have is, why would the second sqlite3_prepare_v2 statement >> fail only on path-based iOS apps? :-/ > > The other question is: Is it permissable to have two statements prepared but > no further, for the same database connection. What do you mean by "no further"? In my app, I use both statements. I have trimmed down the code in this email thread to show the error. Be sure I use these two statements later on. The reason I cache the statements is to save time during inserts, since I use them a lot. If having two statements prepared is not permissible, should't it fail on Mac OS X as well? Thanks, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
On 31 Jan 2011, at 7:38pm, Tito Ciuro wrote: > So my question I have is, why would the second sqlite3_prepare_v2 statement > fail only on path-based iOS apps? :-/ The other question is: Is it permissable to have two statements prepared but no further, for the same database connection. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 14:05:15 -0500, Gabe da Silveira wrote: [...] > On Mon, Jan 31, 2011 at 1:24 PM, Samuel Adam 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 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 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
[sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Hello, The following code snippet runs fine on Mac OS X, but fails on the iOS simulator: // Obtain a path for the database NSString *docs = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSString *path = [[docs stringByAppendingPathComponent:@"myDB.sqlite"]fileSystemRepresentation]; // Open the database sqlite3 *db = NULL; int statusOpen = sqlite3_open_v2( fileSystemRepresentation, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL); // Build the first statement sqlite3_stmt *oneStatement = NULL; const char *oneSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, %@, %@, %@) VALUES (?,?,?,?);", NSFValues, NSFKey, NSFAttribute, NSFValue, NSFDatatype]UTF8String]; int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL), &oneStatement, &oneSQL); // Build the second statement sqlite3_stmt *twoStatement = NULL; const char *twoSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, %@, %@, %@) VALUES (?,?,?,?);", NSFKeys, NSFKey, NSFPlist, NSFCalendarDate, NSFObjectClass]UTF8String]; int statusTwo = sqlite3_prepare_v2(db, twoSQL, (int)strlen(twoSQL), &twoStatement, &twoSQL); What I see is that statusTwo returns 1, and I have no idea why. What is really puzzling is that if I open the database in memory or temporary mode, it works fine in both Mac OS X and iOS! So my question I have is, why would the second sqlite3_prepare_v2 statement fail only on path-based iOS apps? :-/ Thanks in advance, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, Jan 31, 2011 at 5:34 PM, Samuel Adam wrote: > According to the docs, results from those queries should be reversed. I > suspect that Mr. da Silveira’s different platforms actually have different > SQLite versions, and that one version is consistent with the docs whereas > the other is not. But this thread has already seen the dangers of > speculation. Mr. da Silveira, can you confirm whether *all* your > platforms use 3.6.22 and have a proper FTS3 baked in? Grepping php -i further I found: On OS X (unquoted required): SQLite Library => 3.6.23.1 On Gentoo (quoted required): SQLite Library => 3.6.22 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
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 wrote: > On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin > 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 13:37:35 -0500, Samuel Adam
wrote:
> The FTS3
> columns declared INTEGER indeed seem to behave as regular INTEGER columns
> in regular, non-MATCH queries (although I did not test to see if affinity
> would coerce a '1' to INTEGER on insertion).
No coercion is done in the FTS3 table. Using the same FTS3 "Test" table
setup as in my previous mail, again in 3.7.2:
sqlite> INSERT INTO "Test" ("id", "Content", "count") VALUES(
...> '121',
...> 'The "id" and "count" columns were declared INTEGER.',
...> '12321');
sqlite> SELECT typeof("id"), typeof("Content"), typeof("count") FROM
"Test";
typeof("id") = text
typeof("Content") = text
typeof("count") = text
Thus is this table neither animal nor vegetable nor mineral.
sqlite> CREATE TABLE "Normal" ("id" INTEGER);
sqlite> INSERT INTO "Normal" ("id") VALUES ('121');
sqlite> SELECT typeof("id") FROM "Normal";
typeof("id") = integer
Very truly,
Samuel Adam
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
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 13:16:55 -0500, Scott Hess 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. Just to be clear: In the tests I posted earlier with both 3.7.2 and 3.6.22, INSERTing an INTEGER without parameter binding (which isn’t supported in the shell) resulted in an INTEGER coming back out. The FTS3 columns declared INTEGER indeed seem to behave as regular INTEGER columns in regular, non-MATCH queries (although I did not test to see if affinity would coerce a '1' to INTEGER on insertion). Is this the expected behavior? If so, it is a doc bug; if not, it’s a code bug. (By the way—and way off-topic—I just noticed on my last post that this list removes format=flowed from the Content-Type of mails. Is that intended? It rather uglifies things.) Very truly, Samuel Adam 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
Re: [sqlite] integer or string in fts3 table
On Mon, Jan 31, 2011 at 9:15 AM, Samuel Adam wrote: > On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam wrote: >> On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin >> wrote: >>> In the definition given in the original post, which I quoted, the table >>> was defined as follows: >>> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, sort_name TEXT NOT NULL, fanships_count INTEGER, image_url VARCHAR(255)); >>> >>> Under this situation, would SQLite not use 'id' as an alias to rowid ? > […] >> You were right; I was wrong. I saw FTS3 and somehow missed the PK >> definition. > > Spoke too soon. SQLite actually ignores the PK definition, and does *not* > use "id" as an alias for the rowid. Evidence coming in another post; I > think I found a bug. As noted in another post, in FTS3, all user-defined columns are explicitly TEXT, no constraints, and there are baked-in columns for rowid with alias docid. There is also a hidden column with the table's name used to refer to all matches in certain cases. I did spend some time thinking about how to allow specification of types and constraints, but it would add a substantial amount of complexity, with no real gain to the full-text-search functionality. It seemed more reasonable to concentrate on getting the full-text-search stuff right, and use existing SQLite functionality to handle the rest. For instance, you can have a separate table with the stuff FTS3 does not support, joined with the FTS3 table on rowid. You could perhaps even masquerade as a unified table using views and triggers (the fact that it's been a few years and nobody has written a virtual table wrapper for FTS3 which does this automatically makes me think that I probably was right in estimating this as a hard-to-solve problem). 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. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 12:34:24 -0500, Samuel Adam
wrote:
> According to the docs, results from those queries should be reversed. I
> suspect that Mr. da Silveira’s different platforms actually have
> different SQLite versions, and that one version is consistent with the
> docs whereas the other is not. But this thread has already seen the
> dangers of speculation. Mr. da Silveira, can you confirm whether *all*
> your platforms use 3.6.22 and have a proper FTS3 baked in?
My version hypothesis was flat-out incorrect. Repeating all tests in
3.6.22, the results are exactly the same as I showed with 3.7.2. First,
showing my compile options (this is on Windows):
sqlite-3.6.22>cl /nologo /Ox /fp:strict /Fesqlite3.6.22.exe
/DSQLITE_DEFAULT_FILE_FORMAT=4 /DSQLITE_DEFAULT_RECURSIVE_TRIGGERS
/DSQLITE_ENABLE_STAT2 /DSQLITE_ENABLE_FTS3
/DSQLITE_ENABLE_FTS3_PARENTHESIS /DSQLITE_ENABLE_UNLOCK_NOTIFY
/DSQLITE_ENABLE_COLUMN_METADATA /DSQLITE_SOUNDEX=1
/DSQLITE_ENABLE_MEMORY_MANAGEMENT /DSQLITE_ENABLE_RTREE shell.c sqlite3.c
shell.c
sqlite3.c
Generating Code...
sqlite-3.6.22>.\sqlite3.6.22.exe
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode line
sqlite> SELECT sqlite_source_id();
sqlite_source_id() = 2010-01-05 15:30:36
28d0d7710761114a44a1a3a425a6883c661f06e7
sqlite> CREATE VIRTUAL TABLE "Test" USING fts3(
...> "id" INTEGER PRIMARY KEY,
...> "Content" TEXT,
...> "count" INTEGER);
sqlite> INSERT INTO "Test" ("id", "Content", "count") VALUES(
...> 101,
...> 'This is a test.',
...> 999);
sqlite> SELECT "rowid" FROM "Test" WHERE "id" = 101;
rowid = 1
sqlite> SELECT typeof("id"), typeof("Content"), typeof("count") FROM
"Test";
typeof("id") = integer
typeof("Content") = text
typeof("count") = integer
sqlite> SELECT * FROM "Test" WHERE "id" = 101;
id = 101
Content = This is a test.
count = 999
sqlite> SELECT * FROM "Test" WHERE "id" = '101';
sqlite>
Very truly,
Samuel Adam
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
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 06:30:55 -0500, Gabe da Silveira
wrote:
> I have an issue with an fts3 table sqlite 3.6.22. I have a PHP script
> that builds an sqlite3 client database for a ps3 application.
> Depending on where I run the build the script (Gentoo or Mac OS X) I
> get a database file that has different semantics for a column declared
> as an integer pk:
>
> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT
> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL,
> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url
> VARCHAR(255));
Again I test hereby using the sqlite3 shell compiled with FTS3. First of
all, to set up a minimal test case:
sqlite> CREATE VIRTUAL TABLE "Test" USING fts3(
...> "id" INTEGER PRIMARY KEY,
...> "Content" TEXT,
...> "count" INTEGER);
sqlite> INSERT INTO "Test" ("id", "Content", "count") VALUES(
...> 101,
...> 'This is a test.',
...> 999);
Note that SQLite ignores the PRIMARY KEY definition, and does not use "id"
as an alias for the rowid:
sqlite> SELECT "rowid" FROM "Test" WHERE "id" = 101;
rowid = 1
Yet somehow, SQLite heeds the INTEGER definitions:
sqlite> SELECT typeof("id"), typeof("Content"), typeof("count") FROM
"Test";
typeof("id") = integer
typeof("Content") = text
typeof("count") = integer
Either the docs are wrong, or there is a bug. Quoting §1.2 of the FTS3
docs:
http://www.sqlite.org/fts3.html#section_1_2
If column names are explicitly provided for the FTS table as
part of the CREATE VIRTUAL TABLE statement, then a datatype
name may be optionally specified for each column. This is pure
syntactic sugar, the supplied typenames are not used by FTS or
the SQLite core for any purpose. The same applies to any
constraints specified along with an FTS column name - they are
parsed but not used or recorded by the system in any way.
(Ignoring PRIMARY KEY is consistent with the part about constraints, by
the way.)
The foregoing test results were obtained SQLite 3.7.2. Perhaps later I
should try also with the latest and/or 3.6.22 per OP.
Impact on Mr. da Silveira’s SELECT query:
sqlite> SELECT * FROM "Test" WHERE "id" = 101;
id = 101
Content = This is a test.
count = 999
sqlite> SELECT * FROM "Test" WHERE "id" = '101';
sqlite>
According to the docs, results from those queries should be reversed. I
suspect that Mr. da Silveira’s different platforms actually have different
SQLite versions, and that one version is consistent with the docs whereas
the other is not. But this thread has already seen the dangers of
speculation. Mr. da Silveira, can you confirm whether *all* your
platforms use 3.6.22 and have a proper FTS3 baked in?
Very truly,
Samuel Adam
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
Re: [sqlite] integer or string in fts3 table
On 31 Jan 2011, at 5:15pm, Samuel Adam wrote: > Spoke too soon. SQLite actually ignores the PK definition, and does *not* > use "id" as an alias for the rowid. Evidence coming in another post; I > think I found a bug. Oooh. Interesting. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam wrote: > On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin > wrote: > >> In the definition given in the original post, which I quoted, the table >> was defined as follows: >> >>> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT >>> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, >>> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, >>> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url >>> VARCHAR(255)); >> >> Under this situation, would SQLite not use 'id' as an alias to rowid ? […] > You were right; I was wrong. I saw FTS3 and somehow missed the PK > definition. Spoke too soon. SQLite actually ignores the PK definition, and does *not* use "id" as an alias for the rowid. Evidence coming in another post; I think I found a bug. Very truly, Samuel Adam 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
Re: [sqlite] Cause of "disk I/O errors"
Hello GHCS, Most common problem my users have is Virus scanners stepping on my writes. Might suggest they tell their Virus scanners to ignore your data folder. People do run out of disk space fairly frequently. I wouldn't discount it out of hand. C Sunday, January 30, 2011, 8:25:48 PM, you wrote: GS> Are there any common reasons for encountering a "disk I/O error" (code GS> 10) when creating records in an SQLite database on a Windows PC? My GS> product uses V3.6.23.1 and I did not have extended error codes turned on GS> in my releases up to now. GS> The problem in reproducing this is that the users can be running XP, GS> Vista, or Win7, and the amount and nature of the data being written is GS> entirely dependent on data provided by the user and run through my GS> processing program to create the database. When I've had someone upload GS> their raw data to me so I can try it here, it works every time (naturally). GS> So it seems it must be something about their particular machine GS> environment, but what? I'm sure that nobody is running out of disk space GS> these days. Any ideas on where to look or what to suggest to them that GS> they can do? -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bus error on sqlite3_close on Sun Sparc w/SQLite 3.7.4
On Wed, Jan 26, 2011 at 02:31:05PM -0500, Joe Mokos wrote: > I've written an application that uses SQLite 3.7.4. The application runs > fine on WinXP, AIX, HPUX and Linux. When I try to run on Solaris 5.8 SPARC > I get a bus error when I call sqlite3_close. I get the following stack > trace: > > > > (/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) where > > current thread: t@1 > > =>[1] t_delete(0x1b5ed0, 0xfefbc008, 0x40, 0x1b5e90, 0x1a3840, 0x38), at > 0xfef427bc > > [2] realfree(0x1b5ec8, 0xfefc2858, 0xfefbc008, 0x1b5e90, 0x3b, 0x1b5e98), > at 0xfef423dc > > [3] _free_unlocked(0xfefc27cc, 0xfefbc008, 0x2a57c8, 0xfefbc008, > 0xffbede4a, 0x0), at 0xfef42c1c > > [4] free(0x2a57c8, 0xfefc3a54, 0xfefbfc78, 0xfefbfc78, 0xe9, 0x2a57c8), at > 0xfef42b6c > > [5] sqlite3MemFree(0x2a57d0, 0x, 0xc, 0xfef91bf8, 0x1b5d40, > 0x2a57c8), at 0x5a8cc Faults from within the libc memory allocator are almost always the result of heap corruption, typically caused by buffer overruns. Check your code for potential buffer overruns. Solaris has good default memory error detection available within libumem. Check out: http://developers.sun.com/solaris/articles/libumem_library.html In combination with mdb, you can find the source of the nmemory allocation that is corrupted, which may give you a clue about where to look for overruns. Differences in malloc implementation between different OSes can mask bugs, as some implementations are quite tolerent of subtle bugs like off by one overruns. As Solaris has a slab based allocator by default, overunning one buffer puts you straight into the next buffer in the slab, corrupting memory that under Linux Windows or AIX might be dead space between blocks. Hope that helps, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin
wrote:
> In the definition given in the original post, which I quoted, the table
> was defined as follows:
>
>> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT
>> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
>> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL,
>> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url
>> VARCHAR(255));
>
> Under this situation, would SQLite not use 'id' as an alias to rowid ?
Ah yes, you are correct. Evidence, using sqlite3 shell compiled with FTS3:
sqlite> CREATE VIRTUAL TABLE "Test" USING fts3(
...> "id" INTEGER PRIMARY KEY NOT NULL,
...> "Content" TEXT);
sqlite> INSERT INTO "Test" ("id", "Content") VALUES(
...> 101,
...> 'This is a test.');
sqlite> .mode line
sqlite> SELECT typeof("id"), typeof("Content") FROM "Test";
typeof("id") = integer
typeof("Content") = text
Continuing on, to address the original poster’s question:
sqlite> SELECT * FROM "Test" WHERE "id" = 101;
id = 101
Content = This is a test.
sqlite> SELECT * FROM "Test" WHERE "id" = '101';
sqlite>
You were right; I was wrong. I saw FTS3 and somehow missed the PK
definition.
Of course, this still does not answer why the OP observed different
results on different platforms.
Very truly,
Samuel Adam
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
Re: [sqlite] integer or string in fts3 table
On 31 Jan 2011, at 2:26pm, Samuel Adam wrote:
> On Mon, 31 Jan 2011 08:44:22 -0500, Simon Slavin
> wrote:
>
>> On 31 Jan 2011, at 1:24pm, Samuel Adam wrote:
> [snip]
>>> Actually since this is an FTS3 table, the form with the quotes is the
>>> correct one. As Mr. da Silveira alluded later in his post, in FTS3, the
>>> row ID and only the row ID is an INTEGER
>>
>> But from his CREATE command, isn't the column called 'id' an alias to
>> the rowid ?
>
> FTS3 docs do not mention "id", as I quoted. Moreover, "id" is *not*
> normally an alias for rowid:
>
> sqlite> CREATE TABLE "Test" ("id" TEXT);
In the definition given in the original post, which I quoted, the table was
defined as follows:
> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT
> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL,
> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url
> VARCHAR(255));
Under this situation, would SQLite not use 'id' as an alias to rowid ?
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Next" record
Great, thanks Igor. On 31/01/2011 14:45, Igor Tandetnik wrote: > Ian Hardingham wrote: >> I wish to get the "next" record of a certain type, such that if we have >> row Id x, then: >> >> Select the minimum id such that id> x (with some WHERE conditions) >> If there is no such id (ie x is the largest with the conditions) then >> the first id with those conditions is returned. > select coalesce ( > (select id from mytable where id> :x and conditions order by id limit > 1), > (select id from mytable where conditions order by id limit 1) > ); > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Next" record
Ian Hardingham wrote: > I wish to get the "next" record of a certain type, such that if we have > row Id x, then: > > Select the minimum id such that id > x (with some WHERE conditions) > If there is no such id (ie x is the largest with the conditions) then > the first id with those conditions is returned. select coalesce ( (select id from mytable where id > :x and conditions order by id limit 1), (select id from mytable where conditions order by id limit 1) ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Next" record
Hey guys. I wish to get the "next" record of a certain type, such that if we have row Id x, then: Select the minimum id such that id > x (with some WHERE conditions) If there is no such id (ie x is the largest with the conditions) then the first id with those conditions is returned. I'm doing this with logic in code at the moment, but am curious whether there is an SQLite method for it. Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 08:44:22 -0500, Simon Slavin
wrote:
> On 31 Jan 2011, at 1:24pm, Samuel Adam wrote:
[snip]
>> Actually since this is an FTS3 table, the form with the quotes is the
>> correct one. As Mr. da Silveira alluded later in his post, in FTS3, the
>> row ID and only the row ID is an INTEGER
>
> But from his CREATE command, isn't the column called 'id' an alias to
> the rowid ?
FTS3 docs do not mention "id", as I quoted. Moreover, "id" is *not*
normally an alias for rowid:
sqlite> CREATE TABLE "Test" ("id" TEXT);
sqlite> INSERT INTO "Test" ("id") VALUES ('This is a test.');
sqlite> SELECT typeof("id") FROM "Test";
text
sqlite> CREATE TABLE "Test2" ("" TEXT);
sqlite> INSERT INTO "Test2" VALUES ('There should be no column named "id"
in thi
s table.');
sqlite> SELECT "rowid", "id", "" FROM "Test2";
1|id|There should be no column named "id" in this table.
(That last happens because SQLite helpfully reinterprets the double-quoted
non-identifier as a string value.)
> But what I did miss is that these are VIRTUAL tables, not real ones.
> And therefore you cannot test the contents of the table by opening the
> database with the command-line tool.
I forgot: I always compile my sqlite3 shell with FTS3, &c. It is easy
from the amalgamation: Just set defs correctly, compile sqlite3.c and
shell.c, and link. A configure script is not needed.
> Would it be possible to temporarily remove the word 'VIRTUAL' from the
> CREATE command ? Would the rest of the application stop working ? If
> this does work, then the contents of the table can be tested as I
> outlined earlier.
Well, then, FTS3 would no longer work; and any tests thereupon would be
irrelevant to actual usage by the original poster.
Samuel Adam
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
Re: [sqlite] integer or string in fts3 table
On 31 Jan 2011, at 1:24pm, Samuel Adam wrote: > On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin > wrote: > >> On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: >>> >>> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT >>> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, >>> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, >>> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url >>> VARCHAR(255)); [snip] >> >> Very strange. Naturally, the form where you need the quotes is wrong. >> You have explicitly defined 'id' as an INTEGER. > > Actually since this is an FTS3 table, the form with the quotes is the > correct one. As Mr. da Silveira alluded later in his post, in FTS3, the > row ID and only the row ID is an INTEGER But from his CREATE command, isn't the column called 'id' an alias to the rowid ? But what I did miss is that these are VIRTUAL tables, not real ones. And therefore you cannot test the contents of the table by opening the database with the command-line tool. Would it be possible to temporarily remove the word 'VIRTUAL' from the CREATE command ? Would the rest of the application stop working ? If this does work, then the contents of the table can be tested as I outlined earlier. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL for single user database on NFS and Solaris
On 01/31/2011 11:53 AM, Shawn Wilsher wrote: > On Sun, Jan 30, 2011 at 8:26 PM, Dan Kennedy wrote: >> If you set "PRAGMA locking_mode=EXCLUSIVE" before reading or >> writing the WAL-mode database it might work. > That would make us only be able to use one database connection though, > would it not? Right. At most a single connection at a time. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin wrote: > On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: > >> I have a PHP script >> that builds an sqlite3 client database for a ps3 application. >> Depending on where I run the build the script (Gentoo or Mac OS X) I >> get a database file that has different semantics for a column declared >> as an integer pk: >> >> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT >> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, >> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, >> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url >> VARCHAR(255)); >> >> For the database file build on OS X or Windows I need to run: >> >> SELECT * FROM Directors WHERE id=1 >> >> For the database file built on Gentoo I need to run: >> >> SELECT * FROM Directors WHERE id='1' >> >> Otherwise I get no results. > > Very strange. Naturally, the form where you need the quotes is wrong. > You have explicitly defined 'id' as an INTEGER. Actually since this is an FTS3 table, the form with the quotes is the correct one. As Mr. da Silveira alluded later in his post, in FTS3, the row ID and only the row ID is an INTEGER (“with aliases "docid" [] allowed as an alias along with the usual "rowid", "oid" and "_oid_"” per sqlite.org/fts3.html). So since the actual values *should* be TEXT, the first thing that comes to mind is an interaction with SQLite’s type affinities—but that doesn’t explain the difference on different platforms. Probative questions: * Is PHP’s PDO being used here, or PHP’s Sqlite3 class? * 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.) * 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.) * 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.) * 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. 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.) Very truly, Samuel Adam 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
Re: [sqlite] integer or string in fts3 table
On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: > I have a PHP script > that builds an sqlite3 client database for a ps3 application. > Depending on where I run the build the script (Gentoo or Mac OS X) I > get a database file that has different semantics for a column declared > as an integer pk: > > CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT > NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, > show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, > sort_name TEXT NOT NULL, fanships_count INTEGER, image_url > VARCHAR(255)); > > For the database file build on OS X or Windows I need to run: > > SELECT * FROM Directors WHERE id=1 > > For the database file built on Gentoo I need to run: > > SELECT * FROM Directors WHERE id='1' > > Otherwise I get no results. Very strange. Naturally, the form where you need the quotes is wrong. You have explicitly defined 'id' as an INTEGER. Probably the first thing to find out is whether it's the INSERT or the SELECT commands which are acting differently. I suspect that somehow somewhere you have some funny quote marks creeping in. On both copies of the database can you use the command-line tool to display the contents of the Directors TABLE and see if you get identical results ? Probably simplest to use '.dump Directors': http://www.sqlite.org/sqlite.html You could also swap the files (copy the one from Windows to Gentoo and vice versa), and see whether the need for quotes follows the databases around. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] integer or string in fts3 table
I have an issue with an fts3 table sqlite 3.6.22. I have a PHP script that builds an sqlite3 client database for a ps3 application. Depending on where I run the build the script (Gentoo or Mac OS X) I get a database file that has different semantics for a column declared as an integer pk: CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, sort_name TEXT NOT NULL, fanships_count INTEGER, image_url VARCHAR(255)); For the database file build on OS X or Windows I need to run: SELECT * FROM Directors WHERE id=1 For the database file built on Gentoo I need to run: SELECT * FROM Directors WHERE id='1' Otherwise I get no results. The script that's building the script is exactly the same, the only thing different is the environment; primarily PHP and Sqlite, but obviously there is the whole underlying OS layer. I don't even know where to begin debugging this (unfortunately I did not write the build script), I suspect it has something to do with PHP settings, but I have little experience with both PHP and Sqlite. The fts documentation page (http://www.sqlite.org/fts3.html) indicates column types are syntactic sugar, which seems relevant, but how can the same script output a string in one case and an integer in another? -- Gabe da Silveira http://darwinweb.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL for single user database on NFS and Solaris
On Sun, Jan 30, 2011 at 8:26 PM, Dan Kennedy wrote: > If you set "PRAGMA locking_mode=EXCLUSIVE" before reading or > writing the WAL-mode database it might work. That would make us only be able to use one database connection though, would it not? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
