Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[gwenn]

Yes, you are right.
Thanks for the investigation.


On Sun, Feb 9, 2014 at 11:54 PM, Richard Hipp  wrote:
> This behavior change is in response to ticket
> http://www.sqlite.org/src/info/406d3b2ef9 - a diff across several check-ins
> that makes this change can be seen here:
>
>
> http://www.sqlite.org/src/vdiff?from=b1b0de29fdf7de83=62465ecba7431e1d=1=25
>
> Note that the behavior changes brings the implementation into agreement
> with the historical documentation.  The document was clarified and enhanced
> as part of this change.  But the key statements in the old documentation
> where:
>
> "If [the sqlite3_set_auxdata destructor] is not NULL, SQLite will invoke
> the destructor function given by the 4th parameter to sqlite3_set_auxdata()
> on the metadata when the corresponding function parameter changes or when
> the SQL statement completes, whichever comes first. SQLite is free to call
> the destructor and drop metadata on any parameter of any function at any
> time. The only guarantee is that the destructor will be called before the
> metadata is dropped."
>
> The corresponding text in the revised documentation is similar:
>
> "SQLite is free to discard the metadata at any time, including:
>   *  when the corresponding function parameter changes, or
>   * when [sqlite3_reset()] or [sqlite3_finalize()] is called for the  SQL
> statement, or
>   * when sqlite3_set_auxdata() is invoked again on the same parameter, or
>   * during the original sqlite3_set_auxdata() call when a memory
> allocation error occurs. "
>
> The revised documentation is on the website here:
> http://www.sqlite.org/c3ref/get_auxdata.html
>
> So as far as I can tell, the current implementation is doing what it is
> suppose to do. Or did I misunderstand the complaint?
>
>
>
>
>
> On Sun, Feb 9, 2014 at 10:50 AM, gwenn  wrote:
>
>> Hello,
>> I am not sure but it seems there is a regression between versions
>> 3.7.17 and 3.8.0.
>> It's impacting custom/user declared function and auxiliary data.
>>
>> sqlite-amalgamation-3071700 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3071700 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) reusing...
>> z
>> (0) reusing...
>> y
>>
>> sqlite-amalgamation-3080300 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3080300 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>>
>> The auxiliary data is reused in the second loop with SQLite 3.7.17 but
>> not with SQLite 3.8.0.
>> What is the expected/correct behaviour?
>>
>> Regards
>>
>> Here is the content of auxdata.c:
>> #include 
>> #include 
>> #include 
>> #include "sqlite3.h"
>>
>> static void log(void *pArg, int iErrCode, const char *zMsg) {
>> printf("(%d) %s\n", iErrCode, zMsg);
>> }
>>
>> static void glibRegexpDelete(void *p){
>>   GRegex *pRegex = (GRegex *)p;
>>   g_regex_unref(pRegex);
>> }
>>
>> static void glibReplaceAllFunc(
>>   sqlite3_context *ctx,
>>   int argc,
>>   sqlite3_value **argv
>> ){
>> GError *err = NULL;
>> GRegex *p;
>> gchar *result = NULL;
>>
>> (void)argc;  /* Unused parameter */
>>
>> const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
>> if (!str) {
>> return;
>> }
>>
>> const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
>> if (!replacement) {
>> sqlite3_result_error(ctx, "no replacement string", -1);
>> return;
>> }
>>
>> p = sqlite3_get_auxdata(ctx, 0);
>> if( !p ){
>> const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
>> if( !re ){
>> //sqlite3_result_error(ctx, "no regexp", -1);
>> return;
>> }
>> p = g_regex_new(re, 0, 0, );
>>
>> if( p ){
>> sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
>> }else{
>> char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
>> sqlite3_result_error(ctx, e2, -1);
>> sqlite3_free(e2);
>> g_error_free(err);
>> return;
>> }
>> sqlite3_log(0, "compiling...");
>> } else {
>> sqlite3_log(0, "reusing...");
>> }
>>
>> result = g_regex_replace(p, str, -1, 0, replacement, 0, );
>> if (err) {
>> sqlite3_result_error(ctx, err->message, -1);
>> g_error_free(err);
>> return;
>> }
>> sqlite3_result_text(ctx, result, -1, g_free);
>> }
>>
>> int main(int argc, char **argv) {
>> sqlite3_config(SQLITE_CONFIG_LOG, log, NULL);
>> sqlite3 *db = NULL;
>> sqlite3_stmt *stmt = NULL;
>> char *zErrMsg = NULL;
>> const char *z;
>> int rc = 0;
>> rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
>> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
>> if (db == NULL || SQLITE_OK != rc) {
>> fprintf(stderr, "Error: 

Re: [sqlite] Free Page Data usage

[Raheel Gupta]

Hi,

I tried the same database I had and used a 2KB page size.
It works much faster and also the pages are reused immediattly to the
extent of 95%.

If only the number of pages could be increased somehow. Does anyone think
its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned
Integer.

I do understand that VACUUM is not a good option for me.



On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin  wrote:

>
> On 9 Feb 2014, at 10:45am, RSmith  wrote:
>
> > On 2014/02/09 12:06, Raheel Gupta wrote:
> >> Sir, I have only one auto increment primary key.
> >> Since the new rows will always have a higher number will the pages
> >> which have some free space by deleting rows with lower numbered keys
> never
> >> be reused ? e.g. If row with ROWID "1" was deleted and freed, will it
> not
> >> be used to store the NEW row which will be assigned ROWID 10001 ?
> >
> > Yes. That is the point of AutoIncrement, every new Key will always be
> higher than any previous key ever used, and always exactly one higher than
> the highest ever previously used key. As such, it cannot be re-used within
> pages that are half filled from deletion (except maybe the last page), and
> I believe pages that go completely empty may be re-used without the need to
> vacuum etc. (need someone to confirm this).
>
> You are correct, depending on this PRAGMA:
>
> 
>
> auto_vacuum = NONE
>
> A page which has all its data deleted is added to the 'free pages' list
> and eventually reused.
>
> auto_vacuum = FULL
>
> A page which has all its data deleted is replaced by the last page of the
> file.  The file is then truncated to release the space of the last page for
> use in other files.
>
> auto_vacuum = INCREMENTAL
>
> A page which has all its data deleted is replaced by the last used page of
> the file.  When you issue "PRAGMA incremental_vacuum(N)" the file is
> truncated to release unused pages at the end for use in other files.
>
> As in previous discussion, all this is about reclaiming space at the page
> level: releasing entire pages of space.  It has nothing to do with
> reclaiming space within a page.  And also as in previous discussion, the
> fastest of these is "auto_vacuum = NONE".  Copying one page to another,
> releasing filespace and claiming it back again are slow and require much
> reading and writing.
>
> Simon.
> ___
> 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] FW: Need Help with Golf Handicap Calculation

[Keith Medcalf]

>I thought LIMIT did the same thing as TOP.

>With both LIMIT (or TOP) and ORDER BY, my assumption (and my
>experience) is that the result is sorted, and then the first NN are
>returned.

Correct.  SELECT TOP  ... is equivalent to SELECT ... LIMIT 

The  rows returned are the first  rows of the completed, grouped, 
computed, and ordered result-set ... that is

SELECT TOP  ...
is the same as
SELECT TOP  * FROM (SELECT ...)

and

SELECT ... LIMIT 
is the same as
SELECT * FROM (SELECT ...) LIMIT 

>
>   tenholde
>
>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of James K. Lowden
>Sent: Sunday, February 09, 2014 6:04 PM
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] FW: Need Help with Golf Handicap Calculation
>
>On Sat, 8 Feb 2014 19:47:44 +
>Ed Tenholder  wrote:
>
>> Query:  SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96
>> FROM (SELECT * FROM  (SELECT * FROM (SELECT
>> ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith"
>> ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20)
>> ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10)
>
>That's hard to read, so I'm relying on your description,
>
>> Logic:
>>
>> ? Select the oldest N scores (3 in the example above)
>> ? From that, select the 20 newest scores
>> ? From that, select the 10 lowest handicap-indexes:
>> (Score-Rating)*(113/Slope) ? Return the lowest ScoreDate and the
>> average of the handicap-indexes multiplied by .96
>
>If you're learning SQL, you'll want to know that in the SQL
>standard LIMIT does not exist and ORDER BY is valid only to order the
>final output.
>
>I describe ranking rows in a general way at
>http://www.schemamania.org/sql/#rank.rows.
>
>HTH.
>
>--jkl
>___
>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] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

[Richard Hipp]

On Sun, Feb 9, 2014 at 7:19 PM, Drake Wilson  wrote:

> Quoth "James K. Lowden" , on 2014-02-09
> 17:49:15 -0500:
> > That's true, although it's not quite fair to say the filesystem cache
> > isn't "coherent".  It's just not the Linux implementation.
>
> FYI, the term "coherent" I interpret as being used in the specific
> technical sense involved (data is reflected automatically between
> mmap and read/write), by relation to "cache coherent".  So in that
> sense it is both correct and not a disparagement of the OpenBSD
> behavior.
>
>
Correct.  I should have been clearer in my original statement.

-- 
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] FW: Need Help with Golf Handicap Calculation

[Ed Tenholder]

I thought LIMIT did the same thing as TOP.

With both LIMIT   (or TOP) and ORDER BY, my assumption (and my experience) is 
that the result is sorted, and then the first NN are returned.

   tenholde

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Sunday, February 09, 2014 6:04 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FW: Need Help with Golf Handicap Calculation

On Sat, 8 Feb 2014 19:47:44 +
Ed Tenholder  wrote:

> Query:  SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96
> FROM (SELECT * FROM  (SELECT * FROM (SELECT
> ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith"
> ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20)
> ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10)

That's hard to read, so I'm relying on your description, 

> Logic:
> 
> ? Select the oldest N scores (3 in the example above)
> ? From that, select the 20 newest scores
> ? From that, select the 10 lowest handicap-indexes:
> (Score-Rating)*(113/Slope) ? Return the lowest ScoreDate and the
> average of the handicap-indexes multiplied by .96   

If you're learning SQL, you'll want to know that in the SQL
standard LIMIT does not exist and ORDER BY is valid only to order the
final output.  

I describe ranking rows in a general way at
http://www.schemamania.org/sql/#rank.rows.  

HTH.  

--jkl
___
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] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

[Drake Wilson]

Quoth "James K. Lowden" , on 2014-02-09 17:49:15 
-0500:
> That's true, although it's not quite fair to say the filesystem cache
> isn't "coherent".  It's just not the Linux implementation. 

FYI, the term "coherent" I interpret as being used in the specific
technical sense involved (data is reflected automatically between
mmap and read/write), by relation to "cache coherent".  So in that
sense it is both correct and not a disparagement of the OpenBSD
behavior.

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

Re: [sqlite] FW: Need Help with Golf Handicap Calculation

[James K. Lowden]

On Sat, 8 Feb 2014 19:47:44 +
Ed Tenholder  wrote:

> Query:  SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96
> FROM (SELECT * FROM  (SELECT * FROM (SELECT
> ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith"
> ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20)
> ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10)

That's hard to read, so I'm relying on your description, 

> Logic:
> 
> ? Select the oldest N scores (3 in the example above)
> ? From that, select the 20 newest scores
> ? From that, select the 10 lowest handicap-indexes:
> (Score-Rating)*(113/Slope) ? Return the lowest ScoreDate and the
> average of the handicap-indexes multiplied by .96   

If you're learning SQL, you'll want to know that in the SQL
standard LIMIT does not exist and ORDER BY is valid only to order the
final output.  

I describe ranking rows in a general way at
http://www.schemamania.org/sql/#rank.rows.  

HTH.  

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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[E.Pasma]

Op 9 feb 2014, om 20:50 heeft big stone het volgende geschreven:


Hi,

You're right,' recommands' is not in the code, but was not run anymore
either.
The "x" table cte definition should be removed, as the final  
request  use

only "q".

Anyway, after all this algorithmic surgery, Postgresql was slower  
with the

'optimization' algorithm than with the brute force.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Postgresql is out of competition then:
http://www.sqlite.org/lang_with.html#sudoku runs about 10% slower  
there (after s/instr/strpos/).
Bigstone's https://www.mail-archive.com/sqlite-users@sqlite.org/msg81630.html 
 takes three times longer in Psql.


(psql version 8.4.19)

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

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

[Richard Hipp]

On Sun, Feb 9, 2014 at 5:49 PM, James K. Lowden wrote:

>
> I know portability is important to the SQLite project.  Is this
> particular issue a matter of manpower, know-how, or policy?
>
>
The mmap functionality in SQLite is completely portable to OpenBSD now,
since as currently implemented, requests to activate mmap are silently
ignored in OpenBSD.  So you always get the correct answer.

I suspect that adding msync() calls would wipe out any speed advantage for
using memory-mapped I/O.  And since speed is the only advantage to memory
mapped I/O and because there are many disadvantages, I don't see a use-case
for trying to make mmap work on OpenBSD.

-- 
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] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[Richard Hipp]

This behavior change is in response to ticket
http://www.sqlite.org/src/info/406d3b2ef9 - a diff across several check-ins
that makes this change can be seen here:


http://www.sqlite.org/src/vdiff?from=b1b0de29fdf7de83=62465ecba7431e1d=1=25

Note that the behavior changes brings the implementation into agreement
with the historical documentation.  The document was clarified and enhanced
as part of this change.  But the key statements in the old documentation
where:

"If [the sqlite3_set_auxdata destructor] is not NULL, SQLite will invoke
the destructor function given by the 4th parameter to sqlite3_set_auxdata()
on the metadata when the corresponding function parameter changes or when
the SQL statement completes, whichever comes first. SQLite is free to call
the destructor and drop metadata on any parameter of any function at any
time. The only guarantee is that the destructor will be called before the
metadata is dropped."

The corresponding text in the revised documentation is similar:

"SQLite is free to discard the metadata at any time, including:
  *  when the corresponding function parameter changes, or
  * when [sqlite3_reset()] or [sqlite3_finalize()] is called for the  SQL
statement, or
  * when sqlite3_set_auxdata() is invoked again on the same parameter, or
  * during the original sqlite3_set_auxdata() call when a memory
allocation error occurs. "

The revised documentation is on the website here:
http://www.sqlite.org/c3ref/get_auxdata.html

So as far as I can tell, the current implementation is doing what it is
suppose to do. Or did I misunderstand the complaint?





On Sun, Feb 9, 2014 at 10:50 AM, gwenn  wrote:

> Hello,
> I am not sure but it seems there is a regression between versions
> 3.7.17 and 3.8.0.
> It's impacting custom/user declared function and auxiliary data.
>
> sqlite-amalgamation-3071700 gwen$ gcc
> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
> sqlite-amalgamation-3071700 gwen$ ./auxdata
> loop 1
> (0) compiling...
> z
> (0) reusing...
> y
> loop 2
> (0) reusing...
> z
> (0) reusing...
> y
>
> sqlite-amalgamation-3080300 gwen$ gcc
> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
> sqlite-amalgamation-3080300 gwen$ ./auxdata
> loop 1
> (0) compiling...
> z
> (0) reusing...
> y
> loop 2
> (0) compiling...
> z
> (0) reusing...
> y
>
> The auxiliary data is reused in the second loop with SQLite 3.7.17 but
> not with SQLite 3.8.0.
> What is the expected/correct behaviour?
>
> Regards
>
> Here is the content of auxdata.c:
> #include 
> #include 
> #include 
> #include "sqlite3.h"
>
> static void log(void *pArg, int iErrCode, const char *zMsg) {
> printf("(%d) %s\n", iErrCode, zMsg);
> }
>
> static void glibRegexpDelete(void *p){
>   GRegex *pRegex = (GRegex *)p;
>   g_regex_unref(pRegex);
> }
>
> static void glibReplaceAllFunc(
>   sqlite3_context *ctx,
>   int argc,
>   sqlite3_value **argv
> ){
> GError *err = NULL;
> GRegex *p;
> gchar *result = NULL;
>
> (void)argc;  /* Unused parameter */
>
> const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
> if (!str) {
> return;
> }
>
> const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
> if (!replacement) {
> sqlite3_result_error(ctx, "no replacement string", -1);
> return;
> }
>
> p = sqlite3_get_auxdata(ctx, 0);
> if( !p ){
> const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
> if( !re ){
> //sqlite3_result_error(ctx, "no regexp", -1);
> return;
> }
> p = g_regex_new(re, 0, 0, );
>
> if( p ){
> sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
> }else{
> char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
> sqlite3_result_error(ctx, e2, -1);
> sqlite3_free(e2);
> g_error_free(err);
> return;
> }
> sqlite3_log(0, "compiling...");
> } else {
> sqlite3_log(0, "reusing...");
> }
>
> result = g_regex_replace(p, str, -1, 0, replacement, 0, );
> if (err) {
> sqlite3_result_error(ctx, err->message, -1);
> g_error_free(err);
> return;
> }
> sqlite3_result_text(ctx, result, -1, g_free);
> }
>
> int main(int argc, char **argv) {
> sqlite3_config(SQLITE_CONFIG_LOG, log, NULL);
> sqlite3 *db = NULL;
> sqlite3_stmt *stmt = NULL;
> char *zErrMsg = NULL;
> const char *z;
> int rc = 0;
> rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
> if (db == NULL || SQLITE_OK != rc) {
> fprintf(stderr, "Error: unable to open database: %s\n",
> sqlite3_errmsg(db));
> exit(1);
> }
> sqlite3_create_function_v2(db, "regex_replace", 3, SQLITE_UTF8, 0,
> glibReplaceAllFunc, NULL, NULL, NULL);
>   rc = sqlite3_prepare_v2(db, "select regex_replace('.', 'abcde', r)
> from (select 'z' as r union all select 'y')", -1, , NULL);
>   if 

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

[James K. Lowden]

On Sat, 8 Feb 2014 08:26:43 -0500
Richard Hipp  wrote:

> > > The memory-mapped I/O is only enabled for windows, linux, mac
> > > OS-X, and solaris.  We have found that it does not work on
> > > OpenBSD, for reasons we have not yet been able to uncove; but as
> > > a precaution, memory mapped I/O
> > is
> > > disabled by default on all of the *BSDs until we understand the
> > > problem.
> >
> > Was the problem ever identified?
> >
> 
> OpenBSD lacks a coherent filesystem cache.  That is to say, changes
> to a file made using write() are not necessarily reflected in mmap-ed
> memory right away.  And change to a mmap-ed segment are not
> necessarily reflected in subsequent read() operations.

That's true, although it's not quite fair to say the filesystem cache
isn't "coherent".  It's just not the Linux implementation. 

It looks like a sprinkling of msync(2) would do the trick.  My copy of
the Linux Programming Interface (chapter 49) says, 

"However, a unified virtual memory system is not required by
SUSv3 and is not employed on all UNIX implementations. On such systems,
a call to msync() is required to make changes to the contents of a
mapping visible to other processes that read() the file, and the
MS_INVALIDATE flag is required to perform the converse action of making
writes to the file by another process visible in the mapped region."

That appears to be the situation you describe.  It continues, 

"Multiprocess applications that employ both mmap() and I/O
system calls to operate on the same file should be designed to make
appropriate use of msync() if they are to be portable to systems that
don?t have a unified virtual memory system."

I know portability is important to the SQLite project.  Is this
particular issue a matter of manpower, know-how, or policy?  

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

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[gwenn]

Here you are:

#include 
#include 
#include "sqlite3.h"

static void reuseAuxDataCountFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
int *reuseAuxDataCount;
int value;
(void)argc;  /* Unused parameter */

reuseAuxDataCount = (int*)sqlite3_get_auxdata(ctx, 0);
if (reuseAuxDataCount == NULL) {
reuseAuxDataCount = (int *)malloc(sizeof(int));
if (reuseAuxDataCount == NULL) {
sqlite3_result_error_nomem(ctx);
return;
}
*reuseAuxDataCount = 0;
sqlite3_set_auxdata(ctx, 0, reuseAuxDataCount, free);
} else {
(*reuseAuxDataCount)++;
}
sqlite3_result_int(ctx, *reuseAuxDataCount);
}

int main(int argc, char **argv) {
sqlite3 *db = NULL;
sqlite3_stmt *stmt = NULL;
char *zErrMsg = NULL;
const char *z;
int rc = 0;
rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (db == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: unable to open database: %s\n", sqlite3_errmsg(db));
exit(1);
}
sqlite3_create_function_v2(db, "reuseAuxDataCountFunc", 1,
SQLITE_UTF8, 0, reuseAuxDataCountFunc, NULL, NULL, NULL);
// at least, one constant must be passed to make SQLite reuse auxiliary data...
  rc = sqlite3_prepare_v2(db, "select reuseAuxDataCountFunc('test')
from (select 1 union all select 2)", -1, , NULL);
  if (stmt == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
exit(1);
  }
  for (int i = 1; i <= 2; i++) {
  printf("loop %d\n", i);
 rc = sqlite3_step(stmt);
 while (rc == SQLITE_ROW) {
  z = (const char*)sqlite3_column_text(stmt, 0);
  printf("%s\n", z);
  rc = sqlite3_step(stmt);
 }
 if (SQLITE_OK != rc && SQLITE_DONE != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
 rc = sqlite3_reset(stmt);
 if (SQLITE_OK != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
}
  sqlite3_finalize(stmt);
sqlite3_close(db);
}

sqlite-amalgamation-3071700 gwen$ ./auxdata
loop 1
0
1
loop 2
2
3

sqlite-amalgamation-3080300 gwen$ ./auxdata
loop 1
0
1
loop 2
0
1

But it appears that SQLite is behaving as specified in:
http://sqlite.org/c3ref/get_auxdata.html
"SQLite is free to discard the metadata at any time, including:
...
when sqlite3_reset() or sqlite3_finalize() is called for the SQL statement, or
...
"

Sorry for the false alarm.
I will try to find another strategy to keep the compiled regexp...
Regards.

On Sun, Feb 9, 2014 at 7:34 PM, Richard Hipp  wrote:
> Can you provide an example program that omits the glib.h dependency?
>
>
> On Sun, Feb 9, 2014 at 10:50 AM, gwenn  wrote:
>
>> Hello,
>> I am not sure but it seems there is a regression between versions
>> 3.7.17 and 3.8.0.
>> It's impacting custom/user declared function and auxiliary data.
>>
>> sqlite-amalgamation-3071700 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3071700 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) reusing...
>> z
>> (0) reusing...
>> y
>>
>> sqlite-amalgamation-3080300 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3080300 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>>
>> The auxiliary data is reused in the second loop with SQLite 3.7.17 but
>> not with SQLite 3.8.0.
>> What is the expected/correct behaviour?
>>
>> Regards
>>
>> Here is the content of auxdata.c:
>> #include 
>> #include 
>> #include 
>> #include "sqlite3.h"
>>
>> static void log(void *pArg, int iErrCode, const char *zMsg) {
>> printf("(%d) %s\n", iErrCode, zMsg);
>> }
>>
>> static void glibRegexpDelete(void *p){
>>   GRegex *pRegex = (GRegex *)p;
>>   g_regex_unref(pRegex);
>> }
>>
>> static void glibReplaceAllFunc(
>>   sqlite3_context *ctx,
>>   int argc,
>>   sqlite3_value **argv
>> ){
>> GError *err = NULL;
>> GRegex *p;
>> gchar *result = NULL;
>>
>> (void)argc;  /* Unused parameter */
>>
>> const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
>> if (!str) {
>> return;
>> }
>>
>> const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
>> if (!replacement) {
>> sqlite3_result_error(ctx, "no replacement string", -1);
>> return;
>> }
>>
>> p = sqlite3_get_auxdata(ctx, 0);
>> if( !p ){
>> const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
>> if( !re ){
>> //sqlite3_result_error(ctx, "no regexp", -1);
>> return;
>> }
>> p = g_regex_new(re, 0, 0, );
>>
>> if( p ){
>> sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
>> }else{
>> char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
>> sqlite3_result_error(ctx, e2, -1);
>> sqlite3_free(e2);
>> 

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi,

You're right,' recommands' is not in the code, but was not run anymore
either.
The "x" table cte definition should be removed, as the final request  use
only "q".

Anyway, after all this algorithmic surgery, Postgresql was slower with the
'optimization' algorithm than with the brute force.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[Richard Hipp]

Can you provide an example program that omits the glib.h dependency?


On Sun, Feb 9, 2014 at 10:50 AM, gwenn  wrote:

> Hello,
> I am not sure but it seems there is a regression between versions
> 3.7.17 and 3.8.0.
> It's impacting custom/user declared function and auxiliary data.
>
> sqlite-amalgamation-3071700 gwen$ gcc
> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
> sqlite-amalgamation-3071700 gwen$ ./auxdata
> loop 1
> (0) compiling...
> z
> (0) reusing...
> y
> loop 2
> (0) reusing...
> z
> (0) reusing...
> y
>
> sqlite-amalgamation-3080300 gwen$ gcc
> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
> sqlite-amalgamation-3080300 gwen$ ./auxdata
> loop 1
> (0) compiling...
> z
> (0) reusing...
> y
> loop 2
> (0) compiling...
> z
> (0) reusing...
> y
>
> The auxiliary data is reused in the second loop with SQLite 3.7.17 but
> not with SQLite 3.8.0.
> What is the expected/correct behaviour?
>
> Regards
>
> Here is the content of auxdata.c:
> #include 
> #include 
> #include 
> #include "sqlite3.h"
>
> static void log(void *pArg, int iErrCode, const char *zMsg) {
> printf("(%d) %s\n", iErrCode, zMsg);
> }
>
> static void glibRegexpDelete(void *p){
>   GRegex *pRegex = (GRegex *)p;
>   g_regex_unref(pRegex);
> }
>
> static void glibReplaceAllFunc(
>   sqlite3_context *ctx,
>   int argc,
>   sqlite3_value **argv
> ){
> GError *err = NULL;
> GRegex *p;
> gchar *result = NULL;
>
> (void)argc;  /* Unused parameter */
>
> const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
> if (!str) {
> return;
> }
>
> const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
> if (!replacement) {
> sqlite3_result_error(ctx, "no replacement string", -1);
> return;
> }
>
> p = sqlite3_get_auxdata(ctx, 0);
> if( !p ){
> const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
> if( !re ){
> //sqlite3_result_error(ctx, "no regexp", -1);
> return;
> }
> p = g_regex_new(re, 0, 0, );
>
> if( p ){
> sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
> }else{
> char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
> sqlite3_result_error(ctx, e2, -1);
> sqlite3_free(e2);
> g_error_free(err);
> return;
> }
> sqlite3_log(0, "compiling...");
> } else {
> sqlite3_log(0, "reusing...");
> }
>
> result = g_regex_replace(p, str, -1, 0, replacement, 0, );
> if (err) {
> sqlite3_result_error(ctx, err->message, -1);
> g_error_free(err);
> return;
> }
> sqlite3_result_text(ctx, result, -1, g_free);
> }
>
> int main(int argc, char **argv) {
> sqlite3_config(SQLITE_CONFIG_LOG, log, NULL);
> sqlite3 *db = NULL;
> sqlite3_stmt *stmt = NULL;
> char *zErrMsg = NULL;
> const char *z;
> int rc = 0;
> rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
> if (db == NULL || SQLITE_OK != rc) {
> fprintf(stderr, "Error: unable to open database: %s\n",
> sqlite3_errmsg(db));
> exit(1);
> }
> sqlite3_create_function_v2(db, "regex_replace", 3, SQLITE_UTF8, 0,
> glibReplaceAllFunc, NULL, NULL, NULL);
>   rc = sqlite3_prepare_v2(db, "select regex_replace('.', 'abcde', r)
> from (select 'z' as r union all select 'y')", -1, , NULL);
>   if (stmt == NULL || SQLITE_OK != rc) {
> fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
> exit(1);
>   }
>   for (int i = 1; i <= 2; i++) {
>   printf("loop %d\n", i);
>  rc = sqlite3_step(stmt);
>  while (rc == SQLITE_ROW) {
>   z = (const char*)sqlite3_column_text(stmt, 0);
>   printf("%s\n", z);
>   rc = sqlite3_step(stmt);
>  }
>  if (SQLITE_OK != rc && SQLITE_DONE != rc) {
> fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
> exit(1);
> }
>  rc = sqlite3_reset(stmt);
>  if (SQLITE_OK != rc) {
> fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
> exit(1);
> }
> }
>   sqlite3_finalize(stmt);
> sqlite3_close(db);
> }
> ___
> 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] LIKE operator and collations

[Simon Slavin]

On 9 Feb 2014, at 6:05pm, Constantine Yannakopoulos  
wrote:

> - You are assuming that 'z' is the higher order character that can appear
> in a value. This is not the case; for instance greek characters have higher
> order than 'z'. This can be fixed (only for latin/greek) by using the
> highest order greek character 'ώ' (accented omega) instead of 'z'; but I
> would prefer a very high-order non-printable one instead.

By all means replace the '' I typed with a string of 200 accented 
lower-case omegas.  Or with (char)0x7F or some other equivalent.  Since (if I 
remember my Greek) it's impossible for a word to contain three omegas in a row, 
I doubt you'll come across any occurrences of it from anyone who is using your 
program realistically.

Hmm.  I assume that some internal part of SQLite would actually be putting this 
string through your collation function.  It would presumably happen 
automatically as part of what SQLite does.

I know it's a hack.  But it's an elegant efficient hack that takes advantage of 
the things SQLite does well.  As long as that's the only way you were using 
LIKE.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

Thank you all for your
replies,

@RSmith:

My question is about the _specific_ case:

SELECT * FROM ATable WHERE AColumn LIKE
'FixedSearchPart' || 'SomeWildCard'

where the right side of the LIKE operator is a constant that has a fixed
part at the beginning and a wildcard after that. The optimization is that
the index is used to produce a rowset in which AColumn starts with
'FixedSearchPart' and then rows are filtered according to 'SomeWildCard'.
For instance, in

SELECT * FROM ATable WHERE AColumn LIKE
'Constantine%Yann%'

As I understand it, the index scan will use the string 'Constantine' as
argument and then the full string 'Constantine%Yann%' will be used to
further filter the rowset. Of course any other case that has no fixed part
at the start of the right-side string will have to fall back to a full scan
and filter.

Maybe the parameter notation was a little confusing but from the
description you can deduce that it will not contain a wildcard.
Essentially, the user will be asking for the rows where AColumn BEGINS WITH
a string.

@Jean-Christophe:

Thank you for the extension. I will certainly have a look at it, but I
already have the comparison algorithm (specific to latin/greek) from
another case. So the only thing I have to do is to tailor it inside a
custom collation.

@Yuriy:

Yes, the current interface for custom functions does not provide any
information on what the arguments are, so it is impossible to deduce the
collation of arguments inside the function body. That's why this has to be
implemented by the SqLite query optimizer itself. I was thinking about
something like:

- The query optimizer understands that the left-side of the LIKE argument
is a column with an index and a collation, and the right side is a literal
with a fixed left part (after parameter substitution).
- It takes the fixed part, appends a very high-order (preferably
non-printable) character to it and use the index to materialize this clause:

  AColumn BETWEEN 'FixedPart' AND 'FixedPart' || .

If we were using ANSI I would suggest something like:

  AColumn BETWEEN 'FixedPart' AND 'FixedPart' || (char)0xFF

but for UTF-8 I am not sure what to suggest. It would have to be a UTF8
character value that is guaranteed to be greater than any other usable
character in any code page. I am not sure whether there actually is one.

-Then it applies the whole right-side argument to this rowset using LIKE
pattern matching to produce the correct result.

@Simon:

Your proposal is very clever but it has two weaknesses:
- You are assuming that 'z' is the higher order character that can appear
in a value. This is not the case; for instance greek characters have higher
order than 'z'. This can be fixed (only for latin/greek) by using the
highest order greek character 'ώ' (accented omega) instead of 'z'; but I
would prefer a very high-order non-printable one instead.
- It assumes that the column has a maximum width. Since SQLite does not
have maximum widths for columns of TEXT affinity (although a max width can
be implied and imposed by the app itself), improbable as it may be, there
could be a row with the value :SearchString||''||'a' which will
erroneously not make it into the result set. Again, the very high-order
non-printable character would solve this.
So it can be a solution for a specific case with a given max value and a
known set of code pages but it cannot be a general solution.

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

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 9 Feb 2014, at 4:21pm, Yuriy Kaminskiy  wrote:

> Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in
> ICU extension), sqlite won't use index for optimization.

However, this particular use of LIKE ...

> SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

is really just a call to BETWEEN:

SELECT * FROM ATable WHERE AColumn BETWEEN :SearchString AND 
:SearchString||''

And BETWEEN translates into >= and <=.  And those /will/ use the index.

So implementing a 'greek soundex' function as a collation would be useful, if 
this is the only use you need to make of LIKE.

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

Re: [sqlite] LIKE operator and collations

[Yuriy Kaminskiy]

Constantine Yannakopoulos wrote:
> I have a case where the user needs to perform a search in a text column of
> a table with many rows. Typically the user enters the first n matching
> characters as a search string and the application issues a SELECT statement
> that uses the LIKE operator with the search string:
> 
> SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'
> 
> According to the LIKE optimization this statement will use an index so it
> will be fast.

FWIW, sqlite only use index if pattern is string constant or placeholder, it
won't try to use index optimization with more complex constant expression.
I.e.
   SELECT * FROM ATable WHERE AColumn LIKE :SearchString
and append % to your search string outside of sql.
(Well, it does not matter, as I think your following question does not have
acceptable solution).

> The application is used by Greek users. The greek alphabet has some letters
> that are visually identical to corresponding latin letters when in
> capitals, (e.g. A, E, I, O, B, H, K, etc), some that are different but
> match deterministically to a single latin letter (e.g. Σ = S, Φ = F, Ω = O
> etc.) and some that don't have a deterministic match (e.g. Γ = Gh or Yi).

You may want to look at ICU extension, but then sqlite won't use index.

Another option may be fts extension (likely, with custom tokenizer function).

> The table contains strings that consist of words that can be written in
> either latin or greek characters; sometimes even mixed (the user changed
> input locale midword before typing the first non-common letter). I have a
> request that the search should match strings that are written with either
> latin or greek or mixed letters, e.g. "MIS" should match "MISKO" (all
> latin), "ΜΙΣΚΟ" (all greek) or "MIΣΚΟ" (first two letters latin, rest
> greek). I thought of using a custom collation that does this type of
> comparison, have the column use that collation and create an index on that
> column to speed up the search but I discovered that the LIKE operator
> either will not use collations other than BINARY and NOCASE (pragma
> case_sensitive_like) or (if overloaded to perform custom matching) will not

I think "will not use index" is a bug that was fixed in latest sqlite version;
however, "ignoring collation" is intended behavior, and not easy to change :-(.

> use an index, and, worse yet, its behaviour will be the same to all string
> comparisons regardless of collation. So, a full table scan seems inevitable.
> I was wondering whether it is realistic to ask for the LIKE operator to use
> by default the assigned collation of a column. I assume that an index on

From first look, it won't be easy. Probably impossible without changing current
sqlite interface for user-defined collation/function/etc. Besides, it will break
compatibility with existing code, so would require some new PRAGMA to enable.

> that column is using by default the specified collation of the column for
> comparisons, so a LIKE clause like the aforementioned can use the index and
> perform a fast search while using the "mixed" comparison I need. This would
> transparently solve my problem and make the case_sensitive_like pragma
> redundant, but for backward compatibility this behaviour could be activated
> by a new pragma.
> 
> Are there any details I am missing that prevent this from being implemented?
> 
> Thanks in advance.
> 
> --Constantine.


RSmith wrote:
[...]
> It will be a matter of finding the most acceptable deficit... Whether it
> be size, time waste, upgrade cost etc.  By the way, I don't think
> upgrading the table schemata need to be a real hard thing... some
> scripts can take care of that in minimum amount of time. (Test them
> thoroughly though). Also, another poster here had developed a full set
> of international collations and comparison mechanisms as a loadable
> extension to SQLite - Nunicode by Aleksey Tulinov I think... link here:
>
> https://bitbucket.org/alekseyt/nunicode

Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in
ICU extension), sqlite won't use index for optimization.

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

[sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[gwenn]

Hello,
I am not sure but it seems there is a regression between versions
3.7.17 and 3.8.0.
It's impacting custom/user declared function and auxiliary data.

sqlite-amalgamation-3071700 gwen$ gcc
-I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
-I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
sqlite-amalgamation-3071700 gwen$ ./auxdata
loop 1
(0) compiling...
z
(0) reusing...
y
loop 2
(0) reusing...
z
(0) reusing...
y

sqlite-amalgamation-3080300 gwen$ gcc
-I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
-I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
sqlite-amalgamation-3080300 gwen$ ./auxdata
loop 1
(0) compiling...
z
(0) reusing...
y
loop 2
(0) compiling...
z
(0) reusing...
y

The auxiliary data is reused in the second loop with SQLite 3.7.17 but
not with SQLite 3.8.0.
What is the expected/correct behaviour?

Regards

Here is the content of auxdata.c:
#include 
#include 
#include 
#include "sqlite3.h"

static void log(void *pArg, int iErrCode, const char *zMsg) {
printf("(%d) %s\n", iErrCode, zMsg);
}

static void glibRegexpDelete(void *p){
  GRegex *pRegex = (GRegex *)p;
  g_regex_unref(pRegex);
}

static void glibReplaceAllFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
GError *err = NULL;
GRegex *p;
gchar *result = NULL;

(void)argc;  /* Unused parameter */

const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
if (!str) {
return;
}

const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
if (!replacement) {
sqlite3_result_error(ctx, "no replacement string", -1);
return;
}

p = sqlite3_get_auxdata(ctx, 0);
if( !p ){
const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
if( !re ){
//sqlite3_result_error(ctx, "no regexp", -1);
return;
}
p = g_regex_new(re, 0, 0, );

if( p ){
sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
}else{
char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
sqlite3_result_error(ctx, e2, -1);
sqlite3_free(e2);
g_error_free(err);
return;
}
sqlite3_log(0, "compiling...");
} else {
sqlite3_log(0, "reusing...");
}

result = g_regex_replace(p, str, -1, 0, replacement, 0, );
if (err) {
sqlite3_result_error(ctx, err->message, -1);
g_error_free(err);
return;
}
sqlite3_result_text(ctx, result, -1, g_free);
}

int main(int argc, char **argv) {
sqlite3_config(SQLITE_CONFIG_LOG, log, NULL);
sqlite3 *db = NULL;
sqlite3_stmt *stmt = NULL;
char *zErrMsg = NULL;
const char *z;
int rc = 0;
rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (db == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: unable to open database: %s\n", sqlite3_errmsg(db));
exit(1);
}
sqlite3_create_function_v2(db, "regex_replace", 3, SQLITE_UTF8, 0,
glibReplaceAllFunc, NULL, NULL, NULL);
  rc = sqlite3_prepare_v2(db, "select regex_replace('.', 'abcde', r)
from (select 'z' as r union all select 'y')", -1, , NULL);
  if (stmt == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
exit(1);
  }
  for (int i = 1; i <= 2; i++) {
  printf("loop %d\n", i);
 rc = sqlite3_step(stmt);
 while (rc == SQLITE_ROW) {
  z = (const char*)sqlite3_column_text(stmt, 0);
  printf("%s\n", z);
  rc = sqlite3_step(stmt);
 }
 if (SQLITE_OK != rc && SQLITE_DONE != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
 rc = sqlite3_reset(stmt);
 if (SQLITE_OK != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
}
  sqlite3_finalize(stmt);
sqlite3_close(db);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[E.Pasma]

Op 9 feb 2014, om 13:19 heeft big stone het volgende geschreven:


Congratulations, *E.Pasma* !

You indeed won the "brute force sudoku speed" competition for sqlite  
3.8.3

by a factor of  x2.5 on my PC !

D. Richard Hipp is still tuning the CTE implementation, so  
competition may

be less slow with 3.8.4.

To get clother to python speed (http://norvig.com/sudoku.html), we  
would

need to implement :
- an efficient Peter Norvig's method,
- or an efficient Dancing Links method.

==> See below link for "slower than brute force" dancing links
implementation in pgSQL.

http://www.postgresql.org/message-id/caj-6ajqpxxex8ftljksngksvbjrn0h3hydw9xpsvu837gsn...@mail.gmail.com

regards,



I see that there is much competition. The version at http://norvig.com/sudoku.html 
 solves our example in 0.04 seconds. At least that is what it  
reports. A timer within Python made it 0.1 seconds. Still that is at  
least 10 times faster than my SQLite version. On my machine I have the  
following timings now:


http://www.sqlite.org/lang_with.html#sudoku - 13 seconds
Bigstone's https://www.mail-archive.com/sqlite-users@sqlite.org/msg81630.html 
 - 4 sceonds

My bitmapped version - 1.5 seconds
Peter Norvig's http://norvig.com/sudoku.html - 0.1 second

By the way I could not run your postgresql version. Psql reports that  
function recommands does not exist.



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

Re: [sqlite] LIKE operator and collations

[RSmith]

On 2014/02/09 13:18, Constantine Yannakopoulos wrote:

Hello all,

I have a case where the user needs to perform a search in a text column of
a table with many rows. Typically the user enters the first n matching
characters as a search string and the application issues a SELECT statement
that uses the LIKE operator with the search string:

SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

According to the LIKE optimization this statement will use an index so it
will be fast.


This can't be true, there is no way a LIKE or GLOB operator can always use an Index (I mean, it can "use" the Indexed column, but it 
cannot always reduce the number of iterations via Indexing).


To explain more what I mean, consider a simple binary search, let's say we have an ordered list of names that we use the alphabet as 
an indexer:


0:Abraham
1:Ben
2:Constantine
3:Igor
4:James
5:John
6:Ryan
7:Simon

A binary search would start by hitting the middle item typically ( i = Length div 2 ) which is 4 in this case, then comparing it to 
the searched item, let's say it is "JOH" in this case.  It sees that Idx 4 is James, which is smaller than "JOH" (no-case collation 
enabled), then looks to divide the remainder of items larger than James (Idx 5, 6 and 7) in two (3 div 2 = 1 ) and adds it to the 
bottom of them (5) so it checks Index 6, which is now Ryan and is higher than "JOH", it then divides into below Ryan and above James 
and obviously gets "John" which is a match.


A binary tree works similar with the difference it does not have to divide anything, the tree node children are already divisive so 
it just follows down the node closest to the target match until a definite match or matches is/are found (depending on search criteria).


The list above does however demonstrate why a LIKE operator cannot always use an Index, let's say I'm using a search for LIKE '%n', 
how on Earth would you be able to look for that by binary jumping through the list? ANY Indexed item might end on an n, indeed 4 of 
those above do, there is no way to tell and a full-table scan is inevitable.


Of course some clever DB systems, of which SQLite is one, can detect when you use LIKE "Jam%" and knows this is index-searchable and 
still use the Index, but it all depends on what you type and where those % signs are - something which is again negated if the 
search collation does not match the column collation, but is rather easy when standard text or binary collations are used.




store two text columns in the table.  The first is the text as entered.
  The second is your text reduced to its simplified searchable form,
probably all LATIN characters, perhaps using some sort of soundex.  Search
on the second column but return the text in the first.

This allows you to write your conversion routine in the language you're
using to do the rest of your programming, instead of having to express it
as a SQLite function.


Thanks for your reply,


Yes. I thought of that as well. I even have the greek soundex() function
from a previous implementation. Problem is it will bloat the database
considerably, keeping in mind that the users will typically demand that 
ALL
searchable text columns in the application work that way, and who can 
blame
them? And the project manager will not be very keen on accepting both 
this
database size increase and the time needed to calculate the extra 
soundex
column for every database row. It will be much easier to convince this
person to accept the time-costly database upgrade needed in both cases
(tables need to be recreated to change collation) but not both upgrade 
and
bloat.


And I am not happy to accept the fact that I cannot fly, but the laws of the 
Universe demands I adhere to the deficit, and when I simply have to fly, employ 
the help of a very large costly winged tube with jet engines attached to it!

It will be a matter of finding the most acceptable deficit... Whether it be 
size, time waste, upgrade cost etc.  By the way, I don't think upgrading the 
table schemata need to be a real hard thing... some scripts can take care of 
that in minimum amount of time. (Test them thoroughly though). Also, another 
poster here had developed a full set of international collations and comparison 
mechanisms as a loadable extension to SQLite - Nunicode by Aleksey Tulinov I 
think... link here:

https://bitbucket.org/alekseyt/nunicode





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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Congratulations, *E.Pasma* !

You indeed won the "brute force sudoku speed" competition for sqlite 3.8.3
by a factor of  x2.5 on my PC !

D. Richard Hipp is still tuning the CTE implementation, so competition may
be less slow with 3.8.4.

To get clother to python speed (http://norvig.com/sudoku.html), we would
need to implement :
- an efficient Peter Norvig's method,
- or an efficient Dancing Links method.

==> See below link for "slower than brute force" dancing links
implementation in pgSQL.

http://www.postgresql.org/message-id/caj-6ajqpxxex8ftljksngksvbjrn0h3hydw9xpsvu837gsn...@mail.gmail.com

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Sun, Feb 9, 2014 at 1:25 PM, Simon Slavin  wrote:

> store two text columns in the table.  The first is the text as entered.
>  The second is your text reduced to its simplified searchable form,
> probably all LATIN characters, perhaps using some sort of soundex.  Search
> on the second column but return the text in the first.
>
> This allows you to write your conversion routine in the language you're
> using to do the rest of your programming, instead of having to express it
> as a SQLite function.
>

Thanks for your reply,


Yes. I thought of that as well. I even have the greek soundex() function
from a previous implementation. Problem is it will bloat the database
considerably, keeping in mind that the users will typically demand that ALL
searchable text columns in the application work that way, and who can blame
them? And the project manager will not be very keen on accepting both this
database size increase and the time needed to calculate the extra soundex
column for every database row. It will be much easier to convince this
person to accept the time-costly database upgrade needed in both cases
(tables need to be recreated to change collation) but not both upgrade and
bloat.

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

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 9 Feb 2014, at 11:18am, Constantine Yannakopoulos  
wrote:

> So, a full table scan seems inevitable.

I can't answer the specific question you asked, but I have a suggestion for 
your program: store two text columns in the table.  The first is the text as 
entered.  The second is your text reduced to its simplified searchable form, 
probably all LATIN characters, perhaps using some sort of soundex.  Search on 
the second column but return the text in the first.

This allows you to write your conversion routine in the language you're using 
to do the rest of your programming, instead of having to express it as a SQLite 
function.

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

[sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

Hello all,

I have a case where the user needs to perform a search in a text column of
a table with many rows. Typically the user enters the first n matching
characters as a search string and the application issues a SELECT statement
that uses the LIKE operator with the search string:

SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

According to the LIKE optimization this statement will use an index so it
will be fast.

The application is used by Greek users. The greek alphabet has some letters
that are visually identical to corresponding latin letters when in
capitals, (e.g. A, E, I, O, B, H, K, etc), some that are different but
match deterministically to a single latin letter (e.g. Σ = S, Φ = F, Ω = O
etc.) and some that don't have a deterministic match (e.g. Γ = Gh or Yi).

The table contains strings that consist of words that can be written in
either latin or greek characters; sometimes even mixed (the user changed
input locale midword before typing the first non-common letter). I have a
request that the search should match strings that are written with either
latin or greek or mixed letters, e.g. "MIS" should match "MISKO" (all
latin), "ΜΙΣΚΟ" (all greek) or "MIΣΚΟ" (first two letters latin, rest
greek). I thought of using a custom collation that does this type of
comparison, have the column use that collation and create an index on that
column to speed up the search but I discovered that the LIKE operator
either will not use collations other than BINARY and NOCASE (pragma
case_sensitive_like) or (if overloaded to perform custom matching) will not
use an index, and, worse yet, its behaviour will be the same to all string
comparisons regardless of collation. So, a full table scan seems inevitable.

I was wondering whether it is realistic to ask for the LIKE operator to use
by default the assigned collation of a column. I assume that an index on
that column is using by default the specified collation of the column for
comparisons, so a LIKE clause like the aforementioned can use the index and
perform a fast search while using the "mixed" comparison I need. This would
transparently solve my problem and make the case_sensitive_like pragma
redundant, but for backward compatibility this behaviour could be activated
by a new pragma.

Are there any details I am missing that prevent this from being implemented?

Thanks in advance.

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

Re: [sqlite] Free Page Data usage

[Simon Slavin]

On 9 Feb 2014, at 10:45am, RSmith  wrote:

> On 2014/02/09 12:06, Raheel Gupta wrote:
>> Sir, I have only one auto increment primary key.
>> Since the new rows will always have a higher number will the pages
>> which have some free space by deleting rows with lower numbered keys never
>> be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
>> be used to store the NEW row which will be assigned ROWID 10001 ?
> 
> Yes. That is the point of AutoIncrement, every new Key will always be higher 
> than any previous key ever used, and always exactly one higher than the 
> highest ever previously used key. As such, it cannot be re-used within pages 
> that are half filled from deletion (except maybe the last page), and I 
> believe pages that go completely empty may be re-used without the need to 
> vacuum etc. (need someone to confirm this).

You are correct, depending on this PRAGMA:



auto_vacuum = NONE

A page which has all its data deleted is added to the 'free pages' list and 
eventually reused.

auto_vacuum = FULL

A page which has all its data deleted is replaced by the last page of the file. 
 The file is then truncated to release the space of the last page for use in 
other files.

auto_vacuum = INCREMENTAL

A page which has all its data deleted is replaced by the last used page of the 
file.  When you issue "PRAGMA incremental_vacuum(N)" the file is truncated to 
release unused pages at the end for use in other files.

As in previous discussion, all this is about reclaiming space at the page 
level: releasing entire pages of space.  It has nothing to do with reclaiming 
space within a page.  And also as in previous discussion, the fastest of these 
is "auto_vacuum = NONE".  Copying one page to another, releasing filespace and 
claiming it back again are slow and require much reading and writing.

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

Re: [sqlite] Free Page Data usage

[RSmith]

On 2014/02/09 12:06, Raheel Gupta wrote:

Hi,

Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
be used to store the NEW row which will be assigned ROWID 10001 ?


Yes. That is the point of AutoIncrement, every new Key will always be higher than any previous key ever used, and always exactly one 
higher than the highest ever previously used key. As such, it cannot be re-used within pages that are half filled from deletion 
(except maybe the last page), and I believe pages that go completely empty may be re-used without the need to vacuum etc. (need 
someone to confirm this).


I think if you have 2 columns, one with Autoinc Integer and another one with rowid alias as primary key (but not auto-incremented) 
you can get page re-using... but it will still be subject to many factors and never really mimic a full re-using system as you 
really want.


You could of course make the keys yourself, no need to leave it up to the DB to autoincrement them, which might be a cheap solution 
to the problem, but I would still much more favour separate file(s) with byte data alongside an SQLite indexing DB - it would be the 
least work with highest guarantee of working flawlessly.



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

Re: [sqlite] Free Page Data usage

[Raheel Gupta]

Hi,

Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
be used to store the NEW row which will be assigned ROWID 10001 ?


On Sat, Feb 8, 2014 at 11:38 PM, Richard Hipp  wrote:

> On Fri, Feb 7, 2014 at 7:39 AM, Raheel Gupta  wrote:
>
> > Hi,
> >
> > My Page size is 64KB and I store around 4KB of row data in one row.
> > I store around 1 rows in one table and the database size reaches
> 42MB.
> >
> > Now, I am facing a peculiar problem. When I delete just 2-3 rows, that
> page
> > is not reused for the new data which will be inserted in the future.
> >
>
> That space will be reused if your new data has the same (or similar) key as
> the rows that were deleted.
>
> In order to achieve fast lookup, content must be logically ordered by key.
> That means that all of the rows on a single page must have keys that are
> close to one another.  If you have space on a page, and you insert a new
> row with a nearby key, that space will be (re)used.  But if you insert a
> new row with a very different key, that new row must be placed on a page
> close to other rows with similar keys, and cannot appear on the same page
> with rows of very dissimilar keys.
>
>
>
> >
> > The pragma freelist_count shows 0 if I delete the 1st 10 rows (approx
> 40KB)
> > Only if I delete more than 20 rows does the freelist_count reflect 1 page
> > as free.
> >
> > How should I get SQLIte to use the free space within a partially used
> page
> > when rows from that page have been deleted.
> >
> > This causes a lot of space wastage when I store more rows.
> > ___
> > 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