[sqlite] Improve documentation of collation callback?

[James Berry]

Documentation of the parameters to the collation callback could be improved:

(http://www.sqlite.org/c3ref/create_collation.html)

 (1) A function prototype for the callback with named parameters is not given, 
so one has to guess what the parameters are used for.
 (2) It’s also not specified what the int lengths represent: bytes, characters?
 (3) Nor is it specified what the function result is: <0, 0, >0? or -1, 0, 1?
 (4) Also, I assume that since lengths are given it should never be relied on 
that the data pointers are null-terminated… is that correct?

Thanks!

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3_create_collation

[James Berry]

On Apr 2, 2014, at 1:26 PM, Donald Steele  wrote:

> What I’m talking about that is part of Xcode is the SQLite framework not 
> APIs. The access to SQLite is done with the Obj C APIs as defined by SQLite. 
> I am a fairly new developer and may be having some problems keeping up with 
> folks with more time. So if I’m using incorrect terms that is my excuse.
> 
> If was more experienced the documentation on SQLite’s web site would make 
> more sense and I would not be here asking dumb questions.

Ok, so I’ll try to give you a hand…

First off, there are no Obj-C APIs for SQLite built into iOS. It sounds like 
you are calling the C APIs. Also, SQLite is not bundled into iOS as a Framework 
(which suggests Obj-C APIs), but as a library. The differences may be subtle, 
but calling them the wrong things is confusing to those who understand the 
differences.

I think we’ve established that you’d like to use a natural sort algorithm to 
order your results. We have told you the function you need to use to register 
this (sqlite3_create_collation), and given you some pointers to where you might 
find such a function, in plain C. You might also be able to do compares with 
NSString using the NSNumericSearch option. But in any event, you’ll have to 
write and register a collation function. We’ve also shown examples of how you 
would invoke the collation from your SQL.

There are two holes that I see:

(1) Nobody has written a collocation function for you.
(2) I have a hint that you’re a little confused at the parameters that 
should be passed to sqlite3_create_collation, perhaps.

The documentation of the collation callback could be improved, I believe. 
Exactly what the parameters are isn’t well documented that I can see. I believe 
they are: (1) the application data pointer, (2) the length of string “a" (in 
bytes or characters?), (3) the pointer to string “a”, (4) the length of string 
“b”, (5) the pointer to string “b”. Again, those parameters aren’t clearly 
specified in the SQLite docs, and I haven’t gone looking for example code, or 
the sources, but I assume those are the answers. One question that remains for 
me is whether the string parameters (3 and 5) would be null-terminated if 
they’re UTF-8, for instance.

Given a collation callback, then:

int naturalStringCompare(void* appdata, int lenA, const void* strA, int 
lenB, const void* strB)
{
/* logic to compare strA and strB here... */
return result;
}

you would register this using:

sqlite3_create_collation(sqlite3, “naturalSortOrder”, SQLITE_UTF8, nil, 
naturalStringCompare);

Do you have explicit questions about that?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3_create_collation

[James Berry]

On Apr 2, 2014, at 1:12 PM, Donald Steele  wrote:

> Correct. I’m using basically SQLite’s Obj C APIs. the SQLite3 framework is 
> built into X code which saves set up time at the beginning.

There are no built-in Obj-C APIs to SQLite. Are you using Core Data, which is 
an entirely different API that uses SQLite internally but doesn’t expose any 
SQLite API? Please be clear on this point: if you’re using Core Data then you 
probably want to be asking questions on a Core Data list, as using SQLite API 
directly will be problematic.

James

> 
> 
> On Apr 2, 2014, at 12:58 PM, Eric Sink  wrote:
> 
>> Does this mean all your interaction with SQLite is happening through Core
>> Data?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3_create_collation

[James Berry]

On Apr 2, 2014, at 7:36 AM, James Berry <ja...@jberry.us> wrote:

> On Apr 2, 2014, at 7:28 AM, Donald Steele <xln...@sbcglobal.net> wrote:
> 
>> I read some where in my searches that Apple has “canned” versions of that 
>> method but I can’t find those either.
>> 
>> Could someone direct me to some basic tutorials or explanations where I can 
>> get a better understanding before I dive in to create my own collation?
> 
> Can you give us some examples of the data that’s not sorting properly?
> 
> Is the issue that mixed alpha and numeric fields don’t sort as you expect 
> (for instance, that  “I-5” sorts after “I-400”?)

One thing you might want to review is whether the results of the natural sort 
algorithm here: http://sourcefrog.net/projects/natsort/ would be what you want. 
If so, you should be able to incorporate that algorithm, or a variant in a 
convenient language, as part of your collation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3_create_collation

[James Berry]

On Apr 2, 2014, at 7:28 AM, Donald Steele  wrote:

> Greetings I am an iOS developer that is much more comfortable with iOS than 
> SQLite. I have an iOS app that uses a database with several tables. These 
> table the app user uses as directories. The user moves from screen to screen 
> making selections from lists generated from my Sqlite database (tables). Many 
> of those lists are “sorted” by using order by. In most cases that works fine. 
> BUT I have two columns that order by gives “odd” results. The columns are 
> highways & exits. Exits I have used Zero fill to get the results i need. 
> (Altho I would prefer not using zero fill since it “looks odd”). 
> 
> From what I can find out Sqlite uses an ASCII sort in order by, I think what 
> I need is a normal or what I call alpha-numeric sort. I gather the way to do 
> that is to use the SQlite3_create_collation method.  Frankly I can’t get my 
> arms around this method nor can I find any good explanations or even exampls 
> that make sense. 
> 
> I read some where in my searches that Apple has “canned” versions of that 
> method but I can’t find those either.
> 
> Could someone direct me to some basic tutorials or explanations where I can 
> get a better understanding before I dive in to create my own collation?

Can you give us some examples of the data that’s not sorting properly?

Is the issue that mixed alpha and numeric fields don’t sort as you expect (for 
instance, that  “I-5” sorts after “I-400”?)

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[James Berry]

On Sep 10, 2013, at 12:26 PM, Richard Hipp  wrote:

> SURVEY QUESTION:
> 
> The question for today is what to call this magic hint function:
> 
> (1)  unlikely(EXPR)
> (2)  selective(EXPR)
> (3)  seldom(EXPR)
> (4)  seldom_true(EXPR)
> (5)  usually_not_true(EXPR)
> 
> Please feel free to suggest other names if you think of any.


I like the optional second parameter. Apart from the obvious change to 
likelihood, which somebody else suggested, but which is less self documenting 
in the one-argument case, I'd suggest that you actually add two words: likely 
and unlikely, and whose second parameters are the inverse of each other, 
crossing at 0.5. So an expression could then, in the simplest case, be labeled 
LIKELY or UNLIKELY, with second parameter defaulting to 1.0 (or to whatever 
value you feel is appropriate), but allowing the user to specify a lesser 
likelihood by lowering that value. LIKELY(expr, 0) would mean the same as 
UNLIKELY(expr) and UNLIKELY(expr, 1), and UNLIKELY(expr, 0) would be the same 
as LIKELY(expr, 1), etc.

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fts4 contentless tables, multiple inserts to same doc id?

[James Berry]

Is the following intended to be legal and possible for a contentless fts table: 
to do multiple inserts with the same docid, but to different columns?

It seems to work, and I like it, but it wasn't entirely expected.

sqlite> CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b);

sqlite> INSERT INTO t1(docid, a) VALUES (1, "avery");
sqlite> INSERT INTO t1(docid, b) VALUES (1, "billy");

sqlite> SELECT docid FROM t1 WHERE a MATCH 'avery';
1
sqlite> SELECT docid FROM t1 WHERE b MATCH 'avery';
sqlite> SELECT docid FROM t1 WHERE b MATCH 'billy';
1
sqlite> SELECT docid FROM t1 WHERE t1 MATCH 'billy';
1
sqlite> SELECT docid FROM t1 WHERE t1 MATCH 'avery';
1

Thanks,

James

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[James Berry]

Gilles,

I haven't filed a bug on this, but from discussion with others it appears that 
Apple at least know about this compiler bug. I'm not at all sure that they 
intend to put the resources into fixing it any time soon: arm6 is an outdated 
architecture for them, as they haven't shipped devices with arm6 for a number 
of years. Furthermore, there is little good argument for using thumb mode with 
arm6, as it really hurts floating point performance on arm6, given that 
floating point must be emulated. Given those reasons, I suspect that this is 
not a high priority for Apple to fix.

James


On Nov 30, 2011, at 11:04 PM, Gilles Vollant wrote:

> Thank you a lot for your answer.
> Do you known more about this bug? Is there any other documentation?
> Does it affect other code? Will apple fix it soon?
> 
> My currenct solution was using sqlite 3.7.1
> 
> Regards
> Gilles Vollant
> 
> 
> Le jeudi 1 décembre 2011, James Berry <ja...@jberry.us> a écrit :
>> Gilles,
>> 
>> This is due to bugs in llvm when compiling for arm6 with thumb mode
> enabled. Passing -mno-thumb to the compiler will solve the problem.
>> 
>> James
>> 
>> On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:
>> 
>>> Hello,
>>> 
>>> I'm using a recompiled version of SQLite in one project (I need FTS3 and
> now
>>> FTS4 support, which is not avaiable in the sqlite included in iOS).
>>> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
>>> 
>>> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
>>> targeting armv6 (the project must be compatible with iPhone 3G) with
>>> optimisation has a bug with sqLite source.
>>> 
>>> Please note that :
>>> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
>>> problem
>>> - there is no problem with armv7 (iPhone 3GS and higher) compiler
>>> - there is no problem in debug mode (or release with optimization fully
>>> disabled)
>>> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
>>> 
>>> 
>>> you can download this project, do a Release build to iOS device and start
> on
>>> iOS device, then tap on the "i". This demo application crash in very
> simple
>>> sql code.
>>> (it crashes when compiling a very simple SQL command):
>>> 
>>> http://gvollant.free.fr/sqliteCrashXcode.zip
>>> 
>>> I used
>>> 
>>> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
>>> which crashes
>>> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
>>> crashes
>>> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which
> run
>>> well
>>> 
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[James Berry]

(you only need to turn off thumb mode for the arm6 architecture) -jdb

On Nov 30, 2011, at 8:02 PM, James Berry wrote:

> Gilles,
> 
> This is due to bugs in llvm when compiling for arm6 with thumb mode enabled. 
> Passing -mno-thumb to the compiler will solve the problem.
> 
> James
> 
> On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:
> 
>> Hello,
>> 
>> I'm using a recompiled version of SQLite in one project (I need FTS3 and now
>> FTS4 support, which is not avaiable in the sqlite included in iOS).
>> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
>> 
>> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
>> targeting armv6 (the project must be compatible with iPhone 3G) with
>> optimisation has a bug with sqLite source.
>> 
>> Please note that :
>> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
>> problem
>> - there is no problem with armv7 (iPhone 3GS and higher) compiler
>> - there is no problem in debug mode (or release with optimization fully
>> disabled)
>> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
>> 
>> 
>> you can download this project, do a Release build to iOS device and start on
>> iOS device, then tap on the "i". This demo application crash in very simple
>> sql code.
>> (it crashes when compiling a very simple SQL command):
>> 
>> http://gvollant.free.fr/sqliteCrashXcode.zip
>> 
>> I used
>> 
>> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
>> which crashes
>> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
>> crashes
>> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which run
>> well
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[James Berry]

Gilles,

This is due to bugs in llvm when compiling for arm6 with thumb mode enabled. 
Passing -mno-thumb to the compiler will solve the problem.

James

On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:

> Hello,
> 
> I'm using a recompiled version of SQLite in one project (I need FTS3 and now
> FTS4 support, which is not avaiable in the sqlite included in iOS).
> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
> 
> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
> targeting armv6 (the project must be compatible with iPhone 3G) with
> optimisation has a bug with sqLite source.
> 
> Please note that :
> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
> problem
> - there is no problem with armv7 (iPhone 3GS and higher) compiler
> - there is no problem in debug mode (or release with optimization fully
> disabled)
> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
> 
> 
> you can download this project, do a Release build to iOS device and start on
> iOS device, then tap on the "i". This demo application crash in very simple
> sql code.
> (it crashes when compiling a very simple SQL command):
> 
> http://gvollant.free.fr/sqliteCrashXcode.zip
> 
> I used
> 
> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
> which crashes
> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
> crashes
> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which run
> well
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[James Berry]

On Nov 3, 2011, at 6:19 AM, James Berry wrote:

> I've seen this same problem. It crashes seemingly due to bugs in llvm-clang 
> when compiled for arm6 if, as you say, optimizations at any level are turned 
> on. I've worked around this issue by turning off optimizations for arm6. 
> Sqlite3, by the way, is not the only bit of my iOS app that encounters 
> problems with that arm6 optimization combination. arm6, fortunately, is 
> required by fewer and fewer devices these days.
> 
> Report bugs to apple, if you care.

Btw, somebody mentioned to me this morning that these issues can be worked 
around by disabling thumb mode when compiling for arm6 (as an alternative to 
disabling optimizations). That would be the -mno-thumb switch to the compiler. 
Note that these issues only manifest when compiling using the llvm/clang 
compiler.

James

> 
> 
> On Nov 3, 2011, at 1:39 AM, Price,Ray wrote:
> 
>> Hi All,
>> 
>> I'm having a VERY odd problem with Sqlite at the moment.  I have an 
>> application that works fine and has been working find for over a year, but 
>> since updating to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but 
>> ONLY on older devices still running iOS 3.1.3.
>> 
>> However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
>> goes away, but this is obviously FAR from ideal.
>> 
>> Has anyone else experienced this?  Is there anything I can do to narrow down 
>> the problem, or is there anyone I could report this to?
>> 
>> Thanks
>> Ray
>> 
>> 
>> 
>> 
>> This e-mail message, including any attachments, is for the sole use of the 
>> person to whom it has been sent, and may contain information that is 
>> confidential or legally protected. If you are not the intended recipient or 
>> have received this message in error, you are not authorized to copy, 
>> distribute, or otherwise use this message or its attachments. Please notify 
>> the sender immediately by return e-mail and permanently delete this message 
>> and any attachments. Gartner makes no warranty that this e-mail is error or 
>> virus free.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[James Berry]

I've seen this same problem. It crashes seemingly due to bugs in llvm-clang 
when compiled for arm6 if, as you say, optimizations at any level are turned 
on. I've worked around this issue by turning off optimizations for arm6. 
Sqlite3, by the way, is not the only bit of my iOS app that encounters problems 
with that arm6 optimization combination. arm6, fortunately, is required by 
fewer and fewer devices these days.

Report bugs to apple, if you care.

James


On Nov 3, 2011, at 1:39 AM, Price,Ray wrote:

> Hi All,
> 
> I'm having a VERY odd problem with Sqlite at the moment.  I have an 
> application that works fine and has been working find for over a year, but 
> since updating to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but 
> ONLY on older devices still running iOS 3.1.3.
> 
> However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
> goes away, but this is obviously FAR from ideal.
> 
> Has anyone else experienced this?  Is there anything I can do to narrow down 
> the problem, or is there anyone I could report this to?
> 
> Thanks
> Ray
> 
> 
> 
> 
> This e-mail message, including any attachments, is for the sole use of the 
> person to whom it has been sent, and may contain information that is 
> confidential or legally protected. If you are not the intended recipient or 
> have received this message in error, you are not authorized to copy, 
> distribute, or otherwise use this message or its attachments. Please notify 
> the sender immediately by return e-mail and permanently delete this message 
> and any attachments. Gartner makes no warranty that this e-mail is error or 
> virus free.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] apple-osx branch

[James Berry]

Richard,

Looking at the development timeline, apple-osx branch jumps out recently, and 
raises some questions for me:

 (1) If we're building sqlite for delivery on os-x, should we be using code 
from that branch, rather than the regular distribution?

 (2) If yes to 1, then does that also apply to iOS?

 (3) Can you characterize what's different about apple-osx branch?

Thanks,

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] strftime function: requesting %U support

[James Berry]

It would be neat if the sqlite strftime function could support the %U 
conversion, to give Week of Year, where a week starts on Sunday.

As it is, there's a bit of an impedance mismatch, as %w gives day of week where 
0 == Sunday, but the supported %W conversion gives Week of Year, where week 
starts on Monday.

James

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deleting the sqlite journal file?

[James Berry]

Richard,

On Jan 21, 2011, at 5:41 PM, Richard Hipp wrote:

> On Fri, Jan 21, 2011 at 8:27 PM, James Berry <ja...@jberry.us> wrote:
> 
>>   (1) Is there any API I can/should use to predictably get the name of
>> the journal file so that I can delete it, without "knowing" what is should
>> be?
>> 
> 
> The name of the journal is always the name of the original database file
> with either "-journal" or "-wal" appended.  Delete those two files when you
> delete the original database and you are always safe.  To change the journal
> filenames in any way would result in an incompatible file format, since it
> would mean that newer versions of SQLite would not be able to recover from
> crashes on older versions of SQLite.  We work really, really hard to avoid
> incompatibilities, so you may safely assume that the journal filenames will
> remain unchanged.
> 
> 
>> 
>>   (2) Are there any changes that can/should be made to sqlite3 so that
>> it can identify the bogus journal in this scenario and discard it?
>> 
> 
> We've thought about that before, but never could come up with a reasonable
> suggestion.
> 
> 
>> 
>>   (3) Should there be a flag to open_v2, or something, that tells it
>> to discard any extant journal?
>> 
> 
> Having that option on open_v2() would just confuse people into actually
> using it, which means they would more often end up deleting hot journal
> files that they really needed for crash recovery.  Such a flag would result
> in far more problems that it solves, I believe.

I see your point here. What if there were a separate api that I could hand a 
file to,
prior to calling open, that would cleanse the file. Though I can look for 
-journal
and -wal today, I don't know to look for -wbl and -swirl tomorrow.

Granted, if you add sqlite_cleanse(dbPath) you might have as many people
calling that as you would have using a new flag to open_v2.

Thanks for a piece of software I marvel at (nearly every day ;)

James




> 
> 
>> 
>> Thanks,
>> 
>> James
>> ___
>> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deleting the sqlite journal file?

[James Berry]

I ran into an interesting problem today having to do with a left-over journal 
file.

When I first initialize my app, my general strategy is this:

- Delete src.db, tmp.db, dst.db
- Copy a static copy of my database (src.db) to a well-known temporary 
place (tmp.db).
- Make some changes to the database (add some indexes, etc)
- If everything has succeeded, copy tmp.db -> dst.db

The theory (was) that if there are any crashes during the initialization 
process, etc, that dst.db will never be valid and initialization will start 
from ground zero on the next launch.

This fails, for the simply reason that if a crash occurs, a single file is 
left-over at the next startup: dst.db-journal. And when the journal is there, 
in conjunction with a fresh copy of the src.db, sqlite fails in various 
manners, as might (or might not) be expected.

That background out of the way, three questions:

(1) Is there any API I can/should use to predictably get the name of 
the journal file so that I can delete it, without "knowing" what is should be?

(2) Are there any changes that can/should be made to sqlite3 so that it 
can identify the bogus journal in this scenario and discard it?

(3) Should there be a flag to open_v2, or something, that tells it to 
discard any extant journal?

Thanks,

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[James Berry]

On Jan 19, 2011, at 10:03 AM, Richard Hipp wrote:

> On Wed, Jan 19, 2011 at 12:35 PM, James Berry <ja...@jberry.us> wrote:
> 
>> I'm trying to understand whether there's any problem with committing a
>> transaction while in the process of stepping over results.
> 
> The ability to do this was added to SQLite in version 3.6.5 in November of
> 2008.

Thanks Richard, for the clarification.

James



>> BEGIN DEFERRED TRANSACTION
>> 
>> SELECT * FROM a
>> sqlite3_step over select results
>> {
>>   COMMIT
>>   BEGIN DEFERRED TRANSACTION
>> 
>>   INSERT INTO b
>> }
>> 
>> COMMIT

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[James Berry]

On Jan 19, 2011, at 9:43 AM, Igor Tandetnik wrote:

> On 1/19/2011 12:35 PM, James Berry wrote:
>> I'm trying to understand whether there's any problem with committing a 
>> transaction while in the process of stepping over results.
> 
> I believe COMMIT would fail while there is an outstanding statement on 
> the connection.

If that's the case, then is the following pseudo code any better?

SELECT * FROM a
count == 0
sqlite3_step over select results
{
if (count == 0)
BEGIN DEFERRED TRANSACTION
else if (count % transactionSize == 0) {
COMMIT
BEGIN DEFERRED TRANSACTION
}
++count

INSERT INTO b
}

COMMIT


> 
>> The codes seems to be working for me
> 
> Check return values from various SQLite calls.
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[James Berry]

I'm trying to understand whether there's any problem with committing a 
transaction while in the process of stepping over results.

The following loop, in some kind of weird hybrid pseudo-code, tries to 
illustrate what I'm doing: while stepping over results from a select statement, 
doing inserts into another table. During that process, I want to break up the 
current transaction.

Is there any effect on the outer select/step by committing and restarting the 
transaction while the outer statement is still being used? (In my actual 
use-case, the inner commit/begin is not performed on every iteration).

The codes seems to be working for me, but I want to make sure it's not working 
in the realm of undefined operation.

James


BEGIN DEFERRED TRANSACTION

SELECT * FROM a
sqlite3_step over select results
{
COMMIT
BEGIN DEFERRED TRANSACTION

INSERT INTO b
}

COMMIT


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in porter stemmer

[James Berry]

drh,

Thanks for the response: it's nice to know that the report was actually seen.

It would be hubris indeed to claim to fix an implementation bug in Porter's 
code. The code in sqlite didn't match any of Porter's code I could find, so I 
assumed it came from elsewhere: but maybe I missed something. In any event, the 
authorship wasn't clear to me from the sources. The real point that I had 
missed was that, as Shane Harrelson points out, step 1c changed between the 
original porter stemmer and the porter2 stemmer; the step I quote below, and 
which I "fixed", is in the porter2 algorithm, which in this case introduces an 
improvement from porter. So in essence I guess my patch moves porter a bit 
closer to porter2.

I understand the complication that changes to the stemmer would cause an 
incompatibility. It might be interesting to implement the porter2 algorithm for 
fts4; I'm not sure how the two compare in terms of performance. 

Thanks again,

James


On Feb 24, 2010, at 7:05 AM, D. Richard Hipp wrote:

> We got the Porter stemmer code directly from Martin Porter.
> 
> I'm sorry it does not work like you want it to.  Unfortunately, we  
> cannot change it now without introducing a serious incompatibility  
> with the millions and millions of applications already in the field  
> that are using the existing implementation.
> 
> FTS3 has a pluggable stemmer module.  You can write your own stemmer  
> that works "correctly" if you like, and link it in for use in your  
> applications.  We will also investigate making your recommended  
> changes for FTS4.  However, in order to maintain backwards  
> compatibility of FTS3, we cannot change the stemmer algorithm, even to  
> fix a "bug".
> 
> On Feb 24, 2010, at 9:59 AM, James Berry wrote:
> 
>> Can somebody please clarify the bug reporting process for sqlite? My  
>> understanding is that it's not possible to file bug reports  
>> directly, and that the advise is to write to the user list first.  
>> I've done that (below) but have no response so far and am concerned  
>> that this means the bug report will just be forgotten others, as  
>> well as by me.
>> 
>> How does this bug move from a message on a list to a ticket (and  
>> ultimately a patch, we hope) in the system?
>> 
>> James
>> 
>> On Feb 22, 2010, at 2:51 PM, James Berry wrote:
>> 
>>> I'm writing to report a bug in the porter-stemmer algorithm  
>>> supplied as part of the FTS3 implementation.
>>> 
>>> The stemmer has an inverted logic error that prevents it from  
>>> properly stemming words of the following form:
>>> 
>>> dry -> dri
>>> cry -> cri
>>> 
>>> This means, for instance, that the following words don't stem the  
>>> same:
>>> 
>>> dried -> dri   -doesn't match-   dry
>>> cried -> cry   -doesn't match-   cry
>>> 
>>> The bug seems to have been introduced as a simple logic error by  
>>> whoever wrote the stemmer code. The original description of step 1c  
>>> is here: http://snowball.tartarus.org/algorithms/english/stemmer.html
>>> 
>>> Step 1c:
>>> replace suffix y or Y by i if preceded by a non-vowel which is  
>>> not the first letter of the word (so cry -> cri, by -> by, say ->  
>>> say)
>>> 
>>> But the code in sqlite reads like this:
>>> 
>>> /* Step 1c */
>>> if( z[0]=='y' && hasVowel(z+1) ){
>>>  z[0] = 'i';
>>> }
>>> 
>>> In other words, sqlite turns the y into an i only if it is preceded  
>>> by a vowel (say -> sai), while the algorithm intends this to be  
>>> done if it is _not_ preceded by a vowel.
>>> 
>>> But there are two other problems in that same line of code:
>>> 
>>> (1) hasVowel checks whether a vowel exists anywhere in the string,  
>>> not just in the next character, which is incorrect, and goes  
>>> against the step 1c directions above. (amplify would not be  
>>> properly stemmed to amplifi, for instance)
>>> 
>>> (2) The check for the first letter is not performed (for words  
>>> like "by", etc)
>>> 
>>> I've fixed both of those errors in the patch below:
>>> 
>>> /* Step 1c */
>>> -  if( z[0]=='y' && hasVowel(z+1) ){
>>> + if( z[0]=='y' && isConsonant(z+1) && z[2] ){
>>>   z[0] = 'i';
>>> }
>>> 
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in porter stemmer

[James Berry]

Can somebody please clarify the bug reporting process for sqlite? My 
understanding is that it's not possible to file bug reports directly, and that 
the advise is to write to the user list first. I've done that (below) but have 
no response so far and am concerned that this means the bug report will just be 
forgotten others, as well as by me.

How does this bug move from a message on a list to a ticket (and ultimately a 
patch, we hope) in the system?

James

On Feb 22, 2010, at 2:51 PM, James Berry wrote:

> I'm writing to report a bug in the porter-stemmer algorithm supplied as part 
> of the FTS3 implementation.
> 
> The stemmer has an inverted logic error that prevents it from properly 
> stemming words of the following form:
> 
>   dry -> dri
>   cry -> cri
> 
> This means, for instance, that the following words don't stem the same:
> 
>   dried -> dri   -doesn't match-   dry
>   cried -> cry   -doesn't match-   cry
> 
> The bug seems to have been introduced as a simple logic error by whoever 
> wrote the stemmer code. The original description of step 1c is here: 
> http://snowball.tartarus.org/algorithms/english/stemmer.html
> 
>   Step 1c:
>   replace suffix y or Y by i if preceded by a non-vowel which is 
> not the first letter of the word (so cry -> cri, by -> by, say -> say)
>   
> But the code in sqlite reads like this:
> 
>  /* Step 1c */
>  if( z[0]=='y' && hasVowel(z+1) ){
>z[0] = 'i';
>  }
> 
> In other words, sqlite turns the y into an i only if it is preceded by a 
> vowel (say -> sai), while the algorithm intends this to be done if it is 
> _not_ preceded by a vowel.
> 
> But there are two other problems in that same line of code:
> 
>   (1) hasVowel checks whether a vowel exists anywhere in the string, not 
> just in the next character, which is incorrect, and goes against the step 1c 
> directions above. (amplify would not be properly stemmed to amplifi, for 
> instance)
> 
>   (2) The check for the first letter is not performed (for words like 
> "by", etc)
> 
> I've fixed both of those errors in the patch below:
> 
>   /* Step 1c */
> -  if( z[0]=='y' && hasVowel(z+1) ){
> + if( z[0]=='y' && isConsonant(z+1) && z[2] ){
> z[0] = 'i';
>   }
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in porter stemmer

[James Berry]

I'm writing to report a bug in the porter-stemmer algorithm supplied as part of 
the FTS3 implementation.

The stemmer has an inverted logic error that prevents it from properly stemming 
words of the following form:

dry -> dri
cry -> cri

This means, for instance, that the following words don't stem the same:

dried -> dri   -doesn't match-   dry
cried -> cry   -doesn't match-   cry

The bug seems to have been introduced as a simple logic error by whoever wrote 
the stemmer code. The original description of step 1c is here: 
http://snowball.tartarus.org/algorithms/english/stemmer.html

Step 1c:
replace suffix y or Y by i if preceded by a non-vowel which is 
not the first letter of the word (so cry -> cri, by -> by, say -> say)

But the code in sqlite reads like this:

  /* Step 1c */
  if( z[0]=='y' && hasVowel(z+1) ){
z[0] = 'i';
  }

In other words, sqlite turns the y into an i only if it is preceded by a vowel 
(say -> sai), while the algorithm intends this to be done if it is _not_ 
preceded by a vowel.

But there are two other problems in that same line of code:

(1) hasVowel checks whether a vowel exists anywhere in the string, not 
just in the next character, which is incorrect, and goes against the step 1c 
directions above. (amplify would not be properly stemmed to amplifi, for 
instance)

(2) The check for the first letter is not performed (for words like 
"by", etc)

I've fixed both of those errors in the patch below:

   /* Step 1c */
-  if( z[0]=='y' && hasVowel(z+1) ){
+ if( z[0]=='y' && isConsonant(z+1) && z[2] ){
 z[0] = 'i';
   }

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] contribution: fts3 porter stemmer enhancements to handle common european accents

[James Berry]

I'd like to contribute for potential inclusion, or to help out others in the 
community, a small set of enhancements I've made to the porter tokenizer. This 
implementation shares most of its code with the current porter tokenizer, as 
the changes are really just in the tokenizer prior to the stemming operation. 
This small patch implements an additional tokenizer, which I am calling 
"porterPlus", for lack of further inspiration.

The code is based on several observations made while attempting to use the 
current porter tokenizer on a common english/utf-8 dataset:

 - There are a limited number of accented characters common in english text.

 - If the accents simply weren't there, the words would be stemmed 
appropriately, but the porter stemmer gives up on a word when it sees any utf-8 
characters, leading to perceived failures in the search queries.

 - The porter stemmer, by its very nature, is not intended to work for 
non-english text, so we can write off the major part of the the utf-8 character 
set, while concentrating on major improvements to those characters involved in 
common european languages, particularly those that have been adopted into 
english usage.

 - Additionally, there are a number of punctuation characters commonly rendered 
in utf-8 that are missed by the regular porter tokenizer  (hyphen and 
typographic quotes are good examples).

This small patch does the following:

- Defines a new tokenizer "porterPlus" which shares most of its code 
with the regular porter tokenizer

- Identifies a small subset of utf-8 characters for special handling. 
In the case of common accented varieties of regular ascii characters, the 
accents are dropped, leaving the unaccented character only. For instance, sauté 
is converted to saute. The resultant word is passed as usual into the porter 
stemmer.

- Also identifies a small subset of utf-8 characters to treat as 
delimiters, as they would otherwise be treated as part of another token, 
leading to search failures. (hyphen, typographic quotes, etc).

In our use so far, these small changes have meant that we now normalize away 
all of the important utf-8 characters in our input text, which gives us 100% 
searchability of significant input tokens.

The patch (to the 3.6.22 amalgamation) is attached.

James

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on Tiger.

[James Berry]

On Apr 29, 2005, at 4:06 PM, Bill Bumgarner wrote:
Tiger ships with SQLite 3.1.3 + a couple of tweaks.   Specifically,  
the SQLite3 on Tiger supports locking on network filesystems,  
including AFP and Samba.
Have these enhancements been published in the form of patches that  
might be considered for integration into the standard SQLite3  
distribution?

-jdb

Re: [sqlite] Bound parameters not working with prepared statement

[James Berry]

On Apr 15, 2005, at 4:02 PM, Clay Dowling wrote:
James Berry wrote:
It would be useful to use bound parameters in such cases.
Might it make sense to coerce the value at runtime into a string   
value in such a case? I believe a similar restriction (ticket  
#1096:  limit and offset) was recently lifted to allow bound  
parameters in  those cases, where only an integer is allowed.
In this case we're dealing with a bit of code that is probably not  
frequently executed.  snprintf is probably your friend here, at  
least for the time being.
I'm not the OP, but I'm simply suggesting that it would be, in  
general, really nice if the architecture could allow bound parameters  
for many more of these cases. Not to the point of keyword  
substitution (that would change meaning of a statement) but for any  
parameter.

Yes, snprintf and friends can be used, but the ability to use bound  
parameters can really clean up code, and help also to separate SQL  
snippets from logic code.

It's not a must have, but a nice nicety.
-jdb

Re: [sqlite] Bound parameters not working with prepared statement

[James Berry]

On Apr 15, 2005, at 4:16 PM, D. Richard Hipp wrote:
On Fri, 2005-04-15 at 15:49 -0700, Cory Nelson wrote:
It seems when a bind a string to "attach ? as dbname", it is never
translated into the final statement.  Is this supposed to happen?
SQLite only allows bound parameters in places where it is legal
to put an expression.  Remember that bound parameters can be
filled in with NULL, integers, floating point numbers, or strings.
And so bound parameters can only be used in places were all those
values are legal options.  You can not use a floating point number,
an integer, or a NULL as the name of a database file in an ATTACH
statement - that would make no sense.  Hence, bound parameters are
not allowed in that context.
It would be useful to use bound parameters in such cases.
Might it make sense to coerce the value at runtime into a string  
value in such a case? I believe a similar restriction (ticket #1096:  
limit and offset) was recently lifted to allow bound parameters in  
those cases, where only an integer is allowed.

-jdb

Re: [sqlite] sluggish operation on os x?

[James Berry]

On Feb 21, 2005, at 9:40 AM, Curtis King wrote:
I noticed this as well, so I profiled my call and found sync was 
taking forever. I removed the following fcntl call, rc = fcntl(fd, 
F_FULLFSYNC, 0);. Performance was back to normal.
Here are some comments about F_FULLFSYNC, off the darwin list just two 
days ago. They mention why it's there, but don't mention  how slow the 
performance might be...

-jdb
From:   [EMAIL PROTECTED]
Subject: Re: bad fsync? (A.M.)
Date: February 19, 2005 5:59:21 PM PST
To:   [EMAIL PROTECTED]
Cc:   [EMAIL PROTECTED]
MySQL makes the following claim at:
http://dev.mysql.com/doc/mysql/en/news-4-1-9.html
"InnoDB: Use the fcntl() file flush method on Mac OS X versions 10.3
and up. Apple had disabled fsync() in Mac OS X for internal disk
drives, which caused corruption at power outages."
First of all, is this accurate? A pointer to some docs or a tech note
on this would be helpful.
The comments about fsync() are wrong...
On MacOS X, fsync() always has and always will flush all file data
from host memory to the drive on which the file resides.  The behavior
of fsync() on MacOS X is the same as it is on every other version of
Unix since the dawn of time (well, since the introduction of fsync
anyway :-).
I believe that what the above comment refers to is the fact that
fsync() is not sufficient to guarantee that your data is on stable
storage and on MacOS X we provide a fcntl(), called F_FULLFSYNC,
to ask the drive to flush all buffered data to stable storage.
Let me explain in more detail.  With fsync() even though the OS
writes the data through to the disk and the disk says "yes I wrote
the data", the data is not actually on permanent storage.  Unless
you explicitly disable it, all disks have a write buffer which holds
data you've written.  The disk buffers the data you wrote until it
decides to flush it to the platters (and the writes may not be in
the order you wrote them).  If you lose power or the system crashes
before the data is written, you can wind up in a situation where only
some of your data is actually on disk.  What is worse is that even if
you write blocks A, B and C, call fsync() and then write block D you
may find after rebooting that blocks A and D are on disk but B and C
are not (in fact any ordering of A, B, C, and D is possible).
While this may seem like a rare case it is not.  In fact if you sit
down and pull the plug on a system you can make it happen in one or
two plug pulls.  I have even gone so far as to watch this behavior
with a logic analyzer on the ATA bus: I saw the data for two writes
come across the ATA cable, the drive replied and said the writes were
successful and then when we rebooted the data from the second write
was correct on disk but the data from the first write was not.
To deal with this we introduced the F_FULLFSYNC fcntl which will ask
the drive to flush all of its buffered data to disk.  When an app
needs to guarantee that data is on disk it should use F_FULLFSYNC.
In most cases you do not need such a heavy handed operation and
fsync() is good enough.  But in an app like a database, it is
essential if you want transactional integrity.
Now, a little bit more detail: on ATA drives we implement F_FULLFSYNC
with the FLUSH_TRACK_CACHE command.  All drives sold by Apple will
honor this command.  Unfortunately quite a few firewire drive vendors
disable this command and do not pass it to the drive.  This means that
most external firewire drives are not reliable if you lose power or
the system crashes.  We can't work-around that unless we ask the drive
to disable the write cache completely (which hurts performance quite
badly -- and even that may not be enough as some drives will ignore
that request too).
So in summary, I believe that the comments in the MySQL news posting
are slightly confused.  On MacOS X fsync() behaves the same as it does
on all Unices.  That's not good enough if you really care about data
integrity and so we also provide the F_FULLFSYNC fcntl.  As far as I
know, MacOS X is the only OS to provide this feature for apps that
need to truly guarantee their data is on disk.
Hope this clears things up.
--dominic
___
Do not post admin requests to the list. They will be ignored.
Darwin-dev mailing list  ([EMAIL PROTECTED])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/darwin-dev/james%40jberry.us
This email sent to [EMAIL PROTECTED]

Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?

[James Berry]

Eric,
No, you're not missing anything. I asked this same question about a 
month ago. Dr. Hipp replied that argument substitution is not allowed 
in those cases, because it's allowed only where any of the datatypes 
allowed for substitution would be legal (blob, int, string, null). As 
limit and offset take only integers, the prepared statement parser 
wouldn't know ahead of time that the syntax was correct.

While I understand that argument, I, too, think this is unfortunate. I 
wonder if this could be relaxed such the arguments would simply be 
coerced at execution time into integer, no matter what the actual 
argument type.

-jdb
On Feb 1, 2005, at 8:41 PM, Eric Scouten wrote:
When I attempt to prepare the following statement using 
sqlite3_prepare:

   SELECT id FROM testEntity LIMIT 5 OFFSET ?;
I get the following error back from SQLite:
   near "?": syntax error
Is this really not allowed? If so, that seems a bit odd to me. This 
seems like a classic use-case for prepared statements (re-running the 
same query with different offsets).

Or is there something else I'm missing?
-Eric
(FWIW, I'm using SQLite 3.0.8 on Mac OS X.)

Re: [sqlite] table locked?

[James Berry]

On Dec 31, 2004, at 9:11 AM, Thomas Fjellstrom wrote:
On December 31, 2004 06:37 am, D. Richard Hipp wrote:
Even in version 3.0.8, if you have a query running on a table
that has not yet returned SQLITE_DONE, then you are prohibited
from making changes to that table.
So something as simple as: (riped out of delete_key())
Yes, it does fail, and I just ran into this in some code I was writing. 
It's a pain -- I'm having to do a bunch of extra data buffering to get 
around this. Is this common with SQL? I hadn't run into it with MySQL.

-jdb

MadSQLiteResult *res = conf->db->preparef("SELECT id FROM conf_key 
WHERE parent_id=%i;", kid);
if(!res)
 return false; while(res->step() == MadDB_ROW) {
 if(!delete_key(res->columnInt(0)))
 return false;
}
delete res;
conf->db->queryf("DELETE FROM conf_key WHERE id=%i", kid);

will fail? :(
Thanks, and sorry for the half written duplicate :(
-- Thomas Fjellstrom
[EMAIL PROTECTED]
http://strangesoft.net

Re: [sqlite] Questions about binding

[James Berry]

On Dec 24, 2004, at 9:56 AM, Dennis Cote wrote:
James Berry wrote:
I think I was most confused by was the use of the word "index" in the
documentation where associated with the bind calls. Index is a
misnomer. It should really be something like "parameter number" as
I've used it above, since index to me implies the index of the
parameter reference within the SQL.
Index was the correct term when it was first used, since SQLite only 
supported positional parameters at that time. The parameter number was 
the index of the parameter in the SQL statement.

Things have gotten more complicated since then. Now, the term index 
really refers to the internal array of parameter values that is 
maintained for each statement.

I don't think it means much to talk about the "parameter number" of a 
named parameter. They should be refered to by their name. To bind a 
value to a named parameter you need to ask SQLite for the index number 
to use for that variable when you make the bind call.
I agree that index makes a certain amount of sense if/once you 
understand the internal implementation. But unless you're looking at 
the code, there's nothing very "indexy" about it, particularly if you 
mix parameter types, or use numbered parameters indiscriminately. Not 
that I would suggest someone do so. And also that indexes in C start at 
0, not 1.

So I came up with the term "parameter number" because it refers to a 
more generic mapping of parameter <==> number. Let's face it: 
parameters are referred to by number through the bind interface, not by 
index. And the mapping of parameter to number is complex. The fact that 
it's called an index is just the implementation leaking through into 
the interface.

Anyway, call it what you want, we just need to be clear that it's not 
an index as one would normally think if it. It's a number predictively 
assigned to the parameter...a parameter number ;)

James
For numbered parameters, the parameter's number is the index by design 
(but it could have been different).

For positional parameters, you still need to count the parameters in 
the SQL statement to determine its index.

I think index is still the best term, it is just that you need to be 
clear that it is an index into the array of bound values, not into the 
text of the SQL statement.

Dennis Cote

Re: [sqlite] Questions about binding

[James Berry]

Dennis,
Thanks. You nicely clarified what the documentation didn't. I'd 
actually been looking through the code when I got your mail, so I can 
verify what you say. I'll summarize:

 Accepted parameter binding syntax:
 ?  - Positional parameter. This reference is assigned the next unused 
parameter number, starting from 1

 ?n  - Numbered parameter. n must be a integer <= 999. This reference 
is assigned parameter number n.

 :a  - Named parameter. a must be made up of one or more (idChar).
 If the the name "a" has never been seen before, this reference is 
assigned the next
 unused parameter number; otherwise it uses the parameter number 
previously assigned to "a".

 (the form :a: is, as you say, not supported, though described in the 
documentation).

 $a - TCL-style variable reference. With regard to parameter number, 
treated like the
 named parameters above.

 As in TCL, the variable name allows several forms:
 $a - a is one or more of alphanumeric and _, or two colons.
 thus $foo and $foo::bar are valid.
 - may end with a TCL array index of form: (iii), where iii is a legal 
identifier.
 thus $foo(1) and $foo(xyz) and $foo::bar(xyz123) and even 
$foo::bar::car(xyz) are all allowed.
 ${a}  - a is any set of characters except NUL. Nested braces are 
allowed.
 thus ${foo bar 123} is legal.

I think I was most confused by was the use of the word "index" in the 
documentation where associated with the bind calls. Index is a 
misnomer. It should really be something like "parameter number" as I've 
used it above, since index to me implies the index of the parameter 
reference within the SQL.

James
On Dec 24, 2004, at 8:25 AM, Dennis Cote wrote:
James Berry wrote:
(1) Form of wildcards:
 ?
 ?N
 :N:
 $N
 At various places in the documentation, all of these seem to be
mentioned, though not all consistently. At times N is only a numeric
integer, while at others it is a fully alphanumeric identifier. The
last ($) form, is hinted at only in the header file, near as I can
tell.
Are all of these forms allowed?
SQLite now supports four types of parameters in its queries, 
positional parameters, numbered parameters, named parameters, and tcl 
parameters. While all these forms are allowed, it is expected that 
different users will use different styles at different times. The 
compiler is happy to mix tham all together, but users generally 
shouldn't.

Positional parameters use the "?" character by itself. Each one is 
internally assigned a number as it is encountered while compiling the 
SQL statement. This internal number is used by the VDBE code to refer 
to the parameters bound value. Since each positional parameter has its 
own number, each one's value must be bound seperately.

Numbered parameters used the form "?N" where N is a string of digit 
characters. The number N is used directly by the compiled VDBE code to 
refer to the parameters value. This allows the same parameter to be 
used at multiple locations in the SQL code, and yet have its value set 
by one bing call.

Named parameters use the form ":alpha" where alpha is a string of 
characters that form a valid identifier. Each identifier is extracted 
as the SQL is compiled, and then next unused id is assigned to each 
unique identifier. If in identifer has already been assigned a number, 
the same value is used for all subsequent appearances in the query. 
This allows named parameters to be used multiple times in a single 
query and have all bound with a single API call.

The tcl parameter format is "$tcl_var". I don't use tcl so I'm not 
sure what all the allowed tcl_var formats are. They are used to allow 
tcl variables to be used directly in SQL code (at least I think thats 
what they do).

For both named and tcl parameters the identifier text after the : or $ 
must matched
exactly for the same parameter to be used.

There are still referneces in the documentation to a :N: format for 
numbered parameters that is no longer supported.


(2) Wildcard/index mapping:
 I'm also confused by the mapping in the API between the wildcard and
the bind index.
 INSERT INTO t (a,b,c,d,e) VALUES (?,?1,?,?666,$foo);
The documentation suggests that the second value would be set the same
as the first.
 - If I do a sqlite3_bind_int(stmt, 1, 99), will the 1st and 2nd
values both be set to 99?

Yes. The first positional parameter is assigned number 1. The second 
is a numbered parameter with the same number 1. They refer to the same 
bound value.


- To set the 3rd value do I then use index 2, or 3? (In other words,
does the aliasing of 2nd value mean that it doesn't count in the
indexing scheme?)
The third parameter would use number 2 (the first unused id number at 
this point).


- Does the 4th value get set with index 666, or is just an illegal
index, or does it get indexed at 4? Or 3?
The fourth get index number 666. There is a maximum value of 999 
allowed by the compiler. Larger valu

[sqlite] Questions about binding

[James Berry]

I'm somewhat confused by binding in the C++ interface. The 
documentation seems not to be entirely consistent.

My questions fall into several areas:
(1) Form of wildcards:
 ?
 ?N
 :N:
 $N
 At various places in the documentation, all of these seem to be 
mentioned, though not all consistently. At times N is only a numeric 
integer, while at others it is a fully alphanumeric identifier. The 
last ($) form, is hinted at only in the header file, near as I can 
tell.

Are all of these forms allowed?
(2) Wildcard/index mapping:
 I'm also confused by the mapping in the API between the wildcard and 
the bind index.

 INSERT INTO t (a,b,c,d,e) VALUES (?,?1,?,?666,$foo);
The documentation suggests that the second value would be set the same 
as the first.

 - If I do a sqlite3_bind_int(stmt, 1, 99), will the 1st and 2nd values 
both be set to 99?

 - To set the 3rd value do I then use index 2, or 3? (In other words, 
does the aliasing of 2nd value mean that it doesn't count in the 
indexing scheme?)

 - Does the 4th value get set with index 666, or is just an illegal 
index, or does it get indexed at 4? Or 3?

 - I assume the 5th value gets indexed at 5, if it's even legal, though 
I've thrown out a bunch of other possibilities above.

Clarification?
(3) Binding of blob data:
If I bind some arbitary bind data, I assume I shouldn't (and wouldn't 
want to) do any escaping or quoting of null values, etc. Is that 
correct?

Thanks for any answers. I've read the docs, and headers, many times, 
but can't seem to form a mental picture of how it really works. I may 
need to read the code next. As I mentioned above, the docs aren't 
self-consistent about these things, which makes it even more 
challenging.

James.

Re: [sqlite] Pragma default_synchronous?

[James Berry]

On Dec 23, 2004, at 4:41 PM, D. Richard Hipp wrote:
PRAGME default_sychronous went away with version 3.
Thanks. Ah, a documentation bug.
-jdb

[sqlite] Pragma default_synchronous?

[James Berry]

PRAGMA default_synchronous doesn't seem to have any effect on my sqlite 
3.0.8 installation.

 PRAGMA default_synchronous;
doesn't return a result in the sqlite3 shell, and PRAGMA 
default_synchronous=OFF doesn't seem to have any effect, either.

In contrast, PRAGMA synchronous does seem to be working.
Can anybody give me any ideas? Is it supposed to work? Is there a 
misspelling somewhere?

-jdb