SQLITE_DETERMINISTIC

[Andrew Dalke]

I started using APSW last week for a prototype and really enjoyed it. Thanks for all of the work that went into it!

I noticed that createscalarfunction has no way to pass SQLITE_DETERMINISTIC bit to the underlying sqlite3_create_function. That bit was added in 2014-02-03.

I implemented it as an optional third parameter to createscalarfunction.

I have almost no experience with git or github. My diff is at:

https://github.com/adalke/apsw/commit/a87f125090f53b5c1c37a7a394e12832aba55cc1

One obvious problem is that it won't work for older versions of SQLite.

I can think of a couple of options: 1) if that bit isn't defined then the function will raise an exception if the 'deterministic' parameter is set. Or, 2) since that bit is only advisory, I could have it ignore that parameter. My personal preference is #2.

Either way, I'll need some advice on how to do the testing. I added tests which assume 'deterministic' will change the behavior under test. Should I use apsw.sqlitelibversion() to check if the SQLite library is too old to support the bit, or is there a better way to test for that feature?

Let me know what I can do to improve this patch. (Perhaps I should start by making this a pull request? Once I figure out how...)

Cheers,

Andrew
da...@dalkescientific.com

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/09/2015 09:56 PM, Andrew Dalke wrote:
> One obvious problem is that it won't work for older versions of
> SQLite.

That isn't a problem for APSW :-) Contrary to what many modules do,
APSW makes no effort at maintaining compatibility with older SQLite
versions. There are many reasons, mostly summarised as there is no
benefit to doing so.

In any event I'll add this functionality in the next few days as part
of the APSW 3.8.9 release. The hard part isn't implementing things,
but knowing what should be done, where you have been a great help!

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlUn/kMACgkQmOOfHg372QRJCACg0e57T4g2LAGkdYbZ2HpULmvM
dssAoMPBw1cQpDSh9XioCB6dZNmUELWO
=H8hT
-----END PGP SIGNATURE-----

[Andrew Dalke]

Roger Binns wrote:
> That isn't a problem for APSW :-) ... APSW makes no effort at maintaining compatibility with older SQLite
versions

Oh! Then I made a poor inference. The download/compile comments say that using the system SQLite instead of the latest version is a possibility. I assumed that meant support for older versions of SQLite, since it's rare for the system to be at the latest version.

> In any event I'll add this functionality in the next few days as part
> of the APSW 3.8.9 release.  The hard part isn't implementing things,
> but knowing what should be done, where you have been a great help!

Thanks!

On the same note, I see that SQLite 3.8.2 (2013-12-06) added a new field to sqlite3_index_info.

    sqlite3_int64 estimatedRows;     /* Estimated number of rows returned */

While I don't need it, and don't have a clue of it would make a difference in my code, I could use it if it were there.

Cheers,

        Andrew

        da...@dalkescientific.com

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/11/2015 11:04 PM, Andrew Dalke wrote:
> Oh! Then I made a poor inference.

Not really. It is more normal for projects that use others to support
a variety of versions, until doing so isn't possible or too much
effort. APSW used to take that approach, but it wasn't worthwhile.

It is *very* strongly encouraged to include SQLite with your app,
rather than trying to have a single central copy used by all apps.
Since SQLite is used by so many apps, the single central copy approach
keeps breaking them, typically because they depend on various pragmas,
APIs or compile flags. The SQLite team ended up producing the
amalgamation, which makes it one source file and hence really easy to use:

https://www.sqlite.org/amalgamation.html

> The download/compile comments say that using the system SQLite
> instead of the latest version is a possibility. I assumed that
> meant support for older versions of SQLite, since it's rare for the
> system to be at the latest version.

It means that if your system SQLite is sufficiently recent then you
can use that. The very beginning of the doc says what version is
supported, and the apsw.c file also checks the sqlite header file for
version.

http://rogerbinns.github.io/apsw/

Your two requests are in github issues, making it easier to track:

https://github.com/rogerbinns/apsw/issues/187
https://github.com/rogerbinns/apsw/issues/188

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlUq7NcACgkQmOOfHg372QSEggCeJJGK8X065UnmWfx8E+eBVZLy
R3kAnjAsoUp3IJpKDwXZhhupYTFpBM+Y
=hH6s
-----END PGP SIGNATURE-----