Re: [sqlite] SQLite 3.22.0 coming soon

[Gwendal Roué]

No more glitch with sqlite3_column_name :-)

Thanks Richard,
Gwendal

> Le 10 janv. 2018 à 02:06, Richard Hipp  a écrit :
> 
> All of the minor issues mentioned by prior emails in this thread
> should now be fixed.  Thanks to everybody for proof-reading and
> testing!
> 
> Fresh source code is now available on trunk
> (https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot"
> link on the https://sqlite.org/download.html page if you would like to
> continue testing.
> 
> We are not yet at "pencils down".  More enhancements may yet go into
> this release before we start our final release testing.  Nevertheless,
> the community testing you have conducted so far has found several real
> issues, and is greatly appreciated.  Please continue the good work!
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

[Edwards, Mark C.]

Release mode/x86 Visual Studio 2015 Prono problems with the new snapshot
___
Mark C Edwards
Chief Scientist, C2 Systems Engineering & Integration
779 Monika Ct
Chubbuck, ID 83202
mark.c.edwa...@leidos.com
Mobile: 208-241-7982



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, January 09, 2018 6:07 PM
To: SQLite mailing list 
Subject: EXTERNAL: [sqlite] SQLite 3.22.0 coming soon

All of the minor issues mentioned by prior emails in this thread should now be 
fixed.  Thanks to everybody for proof-reading and testing!

Fresh source code is now available on trunk
(https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot"
link on the https://sqlite.org/download.html page if you would like to continue 
testing.

We are not yet at "pencils down".  More enhancements may yet go into this 
release before we start our final release testing.  Nevertheless, the community 
testing you have conducted so far has found several real issues, and is greatly 
appreciated.  Please continue the good work!
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 3.22.0 coming soon

[Richard Hipp]

All of the minor issues mentioned by prior emails in this thread
should now be fixed.  Thanks to everybody for proof-reading and
testing!

Fresh source code is now available on trunk
(https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot"
link on the https://sqlite.org/download.html page if you would like to
continue testing.

We are not yet at "pencils down".  More enhancements may yet go into
this release before we start our final release testing.  Nevertheless,
the community testing you have conducted so far has found several real
issues, and is greatly appreciated.  Please continue the good work!
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[Richard Hipp]

On 1/9/18, E.Pasma  wrote:
> Dear SQLite developers and eventual PowerPC users,
> using a likely outdated compiler:
>   powerpc-apple-darwin9-gcc-4.0.1
> a compile error occors:
>   shell.c:10062: error: conflicting types for ‘integerValue’
>   shell.c:9169: error: previous implicit declaration of ‘integerValue’

This seems like a compiler bug.  But it is easy enough to work around,
and that is what I have done.  Please verify that the latest trunk
code (or the latest "prerelease-snapshot") is now working for you on
your old PowerPC system.  Thanks!

This is the kind of thing is very important.  We SQLite developers can
test as much as we want to, but we will never find strange
interactions with old compilers that we don't own.  This is exactly
the kind of thing we need your help with.  Thanks for all your
testing!

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[E.Pasma]

Dear SQLite developers and eventual PowerPC users,
using a likely outdated compiler:
powerpc-apple-darwin9-gcc-4.0.1
a compile error occors:
shell.c:10062: error: conflicting types for ‘integerValue’
	shell.c:9169: error: previous implicit declaration of ‘integerValue’  
was here
it is still to overcome by an explicit declaration of IntegerValue  
before expertDotCommand.
This must be be the only place where this order of reference and  
definition occurs. Is that alright?

Thanks, E. Pasma

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

Re: [sqlite] SQLite 3.22.0 coming soon

[J Decker]

On Tue, Jan 9, 2018 at 1:19 PM, Gwendal Roué  wrote:

>
> > Le 9 janv. 2018 à 21:47, Richard Hipp  a écrit :
> >
> > On 1/9/18, Gwendal Roué  wrote:
> >> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT
> :a,
> >> :b`, the sqlite3_column_name used to return the parameter name ("?" or
> ":a"
> >> etc.) for all parameters, the last one included. The latest version
> returns
> >> an empty string for the last parameter.
> >
> > I have not been able to reproduce this problem using the command-line
> > shell.  I tried the following script:
> >
> >  .header on
> >  SELECT ?, ?;
> >  SELECT :a, :b;
> >
> > It shows me the correct column names for all columns.  Do you have a
> > reproducible test case that I can work from?
>
> I have Swift code which reliably reproduces the bug :
>
> var conn: OpaquePointer? = nil
> sqlite3_open_v2("", , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE,
> nil)
> var stmt: OpaquePointer? = nil
> sqlite3_prepare_v3(conn, "SELECT :a, :b", -1, 0, , nil)
> sqlite3_column_name(stmt, 0) // ":a"
> sqlite3_column_name(stmt, 1) // ""
>
> A manual translation into C gives:
>
> void *conn, *stmt;
> sqlite3_open_v2("", , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE,
> NULL);
> sqlite3_prepare_v3(conn, "SELECT :a, :b", -1, 0, , NULL);
> sqlite3_column_name(stmt, 0); // ":a"
> sqlite3_column_name(stmt, 1); // ""
>
>
was also able reproduce it with regular C.


> If this is a side effect of the Swift C, we have found another interesting
> bug, but not in SQLite :-)
>
> Gwendal
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[Gwendal Roué]

> Le 9 janv. 2018 à 21:47, Richard Hipp  a écrit :
> 
> On 1/9/18, Gwendal Roué  wrote:
>> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a,
>> :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a"
>> etc.) for all parameters, the last one included. The latest version returns
>> an empty string for the last parameter.
> 
> I have not been able to reproduce this problem using the command-line
> shell.  I tried the following script:
> 
>  .header on
>  SELECT ?, ?;
>  SELECT :a, :b;
> 
> It shows me the correct column names for all columns.  Do you have a
> reproducible test case that I can work from?

I have Swift code which reliably reproduces the bug :

var conn: OpaquePointer? = nil
sqlite3_open_v2("", , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, nil)
var stmt: OpaquePointer? = nil
sqlite3_prepare_v3(conn, "SELECT :a, :b", -1, 0, , nil)
sqlite3_column_name(stmt, 0) // ":a"
sqlite3_column_name(stmt, 1) // ""

A manual translation into C gives:

void *conn, *stmt;
sqlite3_open_v2("", , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, 
NULL);
sqlite3_prepare_v3(conn, "SELECT :a, :b", -1, 0, , NULL);
sqlite3_column_name(stmt, 0); // ":a"
sqlite3_column_name(stmt, 1); // ""

If this is a side effect of the Swift C, we have found another interesting bug, 
but not in SQLite :-)

Gwendal

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

Re: [sqlite] SQLite 3.22.0 coming soon

[Richard Hipp]

On 1/9/18, Gwendal Roué  wrote:
> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a,
> :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a"
> etc.) for all parameters, the last one included. The latest version returns
> an empty string for the last parameter.

I have not been able to reproduce this problem using the command-line
shell.  I tried the following script:

  .header on
  SELECT ?, ?;
  SELECT :a, :b;

It shows me the correct column names for all columns.  Do you have a
reproducible test case that I can work from?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[J Decker]

swarmvtab3 test is still failing... (windows 10, msvc 2017)  10.0.16299.0

swarmvtab3-1.2.3...
Error: inconsistent ::dbcache and disk
swarmvtab3-1.3.3...
Error: inconsistent ::dbcache and disk


On Tue, Jan 9, 2018 at 11:46 AM, Simon Slavin  wrote:

> On 9 Jan 2018, at 6:46pm, Richard Hipp  wrote:
>
> > The latest change summary can be seen at
> > https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
>
> In  …
>
> For "clobbering" use "overwriting", if only for the sake of international
> readers.
>
> "If this option is pass a non-zero argument" should probably be "passed".
>
> I think you can come up with a more appropriate name than "expert".  How
> about "cleverindex" or "indexwizard" or something.
>
> If this is easy to do, then for the .expert command and, by extension, the
> external function …
>
> Please allow an option "—ignoreexisting" for the .expert command.  This
> option makes SQLite ignore any existing indexes except for the ones which
> implement primary keys.
>
> For extra points, with this option selected, in the output section, it
> includes DROP INDEX commands for the indexes it ignored before the CREATE
> INDEX command it prefers.
>
> For an additional bonus point, with this option selected, the command
> should recognise when the new index it recommended is the same as one of
> the existing indexes it ignored, and do the "no new indexes" thing.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[Gwendal Roué]

Hello,

I could spot two behavior changes in f6355970 while testing GRDB.swift.

1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a, :b`, 
the sqlite3_column_name used to return the parameter name ("?" or ":a" etc.) 
for all parameters, the last one included. The latest version returns an empty 
string for the last parameter.

2. '^foo' used to be an invalid FTS5 pattern. It is valid in the latest version 
(but I did not check its behavior).

Cheers,
Gwendal Roué

> Le 9 janv. 2018 à 19:46, Richard Hipp  a écrit :
> 
> Version 3.22.0 will probably be released soon.  Your help in
> beta-testing is appreciated.  Please download the latest "trunk"
> sources (from https://sqlite.org/src/info/trunk) or a pre-release
> snapshot (the top-most link at https://sqlite.org/download.html) and
> verify that you are able to build and use the latest code in your
> applications.  Report any issues to this mailing list, or directly to
> me.
> 
> The latest change summary can be seen at
> https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
> 3.22.0 webpage is at https://www.sqlite.org/draft
> 
> Detailed log of all check-ins since the last release:
> https://www.sqlite.org/src/timeline?from=release=trunk
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

[Edwards, Mark C.]

The Release build/X86 on Visual Studio 2015 Pro worked. The prior problem with 
"possible uninitialized variables" has been resolved. 3.22.0 looks like a 
viable candidate
for incorporation in our source base. We use only the amalgamated sqlite3.h, 
sqlite3ext.h and sqlite3.c files.

Thanks!

___
Mark C Edwards
Chief Scientist, C2 Systems Engineering & Integration
779 Monika Ct
Chubbuck, ID 83202
mark.c.edwa...@leidos.com
Mobile: 208-241-7982



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, January 09, 2018 11:46 AM
To: General Discussion of SQLite Database 
Subject: EXTERNAL: [sqlite] SQLite 3.22.0 coming soon

Version 3.22.0 will probably be released soon.  Your help in beta-testing is 
appreciated.  Please download the latest "trunk"
sources (from https://sqlite.org/src/info/trunk) or a pre-release snapshot (the 
top-most link at https://sqlite.org/download.html) and verify that you are able 
to build and use the latest code in your applications.  Report any issues to 
this mailing list, or directly to me.

The latest change summary can be seen at 
https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
3.22.0 webpage is at https://www.sqlite.org/draft

Detailed log of all check-ins since the last release:
https://www.sqlite.org/src/timeline?from=release=trunk

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[Simon Slavin]

On 9 Jan 2018, at 6:46pm, Richard Hipp  wrote:

> The latest change summary can be seen at
> https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft

In  …

For "clobbering" use "overwriting", if only for the sake of international 
readers.

"If this option is pass a non-zero argument" should probably be "passed".

I think you can come up with a more appropriate name than "expert".  How about 
"cleverindex" or "indexwizard" or something.

If this is easy to do, then for the .expert command and, by extension, the 
external function …

Please allow an option "—ignoreexisting" for the .expert command.  This option 
makes SQLite ignore any existing indexes except for the ones which implement 
primary keys.

For extra points, with this option selected, in the output section, it includes 
DROP INDEX commands for the indexes it ignored before the CREATE INDEX command 
it prefers.

For an additional bonus point, with this option selected, the command should 
recognise when the new index it recommended is the same as one of the existing 
indexes it ignored, and do the "no new indexes" thing.

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

Re: [sqlite] SQLite 3.22.0 coming soon

[David Raymond]

Oh sweet, zipfile extension.


Small typo fixes:

In https://www.sqlite.org/draft/rtree.html#rtreecheck
"on the rtree table named R containing in database S."
should be
"on the rtree table named R contained in database S."

https://www.sqlite.org/draft/cli.html#expert
"If this option is pass a non-zero argument"
should be
"If this option is passed a non-zero argument"


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, January 09, 2018 1:46 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite 3.22.0 coming soon

Version 3.22.0 will probably be released soon.  Your help in
beta-testing is appreciated.  Please download the latest "trunk"
sources (from https://sqlite.org/src/info/trunk) or a pre-release
snapshot (the top-most link at https://sqlite.org/download.html) and
verify that you are able to build and use the latest code in your
applications.  Report any issues to this mailing list, or directly to
me.

The latest change summary can be seen at
https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
3.22.0 webpage is at https://www.sqlite.org/draft

Detailed log of all check-ins since the last release:
https://www.sqlite.org/src/timeline?from=release=trunk

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[Donald Griggs]

In an attempt to be helpful, two possible typos are:

https://www.sqlite.org/draft/cli.html#expert

 If this option is pass a non-zero argument  [change pass -> passed]



https://www.sqlite.org/draft/releaselog/3_22_0.html

 A complex result set bias the decision

 Maybe " A complex result biases the decision"

 Or " A complex result set biases the decision"



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

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

[Edwards, Mark C.]

Thanks Richard. I'll build the amalgamated version in Release mode/X86 in VS 
2015 Pro to see if it completes without complaint.
___
Mark C Edwards
Chief Scientist, C2 Systems Engineering & Integration
779 Monika Ct
Chubbuck, ID 83202
mark.c.edwa...@leidos.com
Mobile: 208-241-7982


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, January 09, 2018 11:46 AM
To: General Discussion of SQLite Database 
Subject: EXTERNAL: [sqlite] SQLite 3.22.0 coming soon

Version 3.22.0 will probably be released soon.  Your help in beta-testing is 
appreciated.  Please download the latest "trunk"
sources (from https://sqlite.org/src/info/trunk) or a pre-release snapshot (the 
top-most link at https://sqlite.org/download.html) and verify that you are able 
to build and use the latest code in your applications.  Report any issues to 
this mailing list, or directly to me.

The latest change summary can be seen at 
https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
3.22.0 webpage is at https://www.sqlite.org/draft

Detailed log of all check-ins since the last release:
https://www.sqlite.org/src/timeline?from=release=trunk

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 3.22.0 coming soon

[Richard Hipp]

Version 3.22.0 will probably be released soon.  Your help in
beta-testing is appreciated.  Please download the latest "trunk"
sources (from https://sqlite.org/src/info/trunk) or a pre-release
snapshot (the top-most link at https://sqlite.org/download.html) and
verify that you are able to build and use the latest code in your
applications.  Report any issues to this mailing list, or directly to
me.

The latest change summary can be seen at
https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
3.22.0 webpage is at https://www.sqlite.org/draft

Detailed log of all check-ins since the last release:
https://www.sqlite.org/src/timeline?from=release=trunk

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Simon Slavin]

On 9 Jan 2018, at 11:35am, Eric Grange  wrote:

> In both cases, since things are constantly in flux, the absolute rank and
> neighbor do not really matter
> (outside the top ranks), but changes in rank are being looked at.
> i.e. having rank 155k or 154k is not really meaningful in itself, but on
> the other hand
> gaining 1000 "ranks" by going from 155k to 154k is what users are after.

Okay, I see what you mean.  You have a special, unusual, case where the ranks 
change frequently and you have a genuine need to do things like "give me all 
the ranks from 154k to 155k".

That’s a very difficult thing to do quickly.  The fastest way to do it would be 
different depending on which you do more: change ranks or query ranks.

The cannonical SQL way would be that every time a figure changes you would 
change the ranks of all the rows between the two positions.  This would take 
only two UPDATE commands each time.  The data would be up-to-date all the time 
and therefore queries would be fast.  But if you have million rows this could 
involve a lot of number-shuffling and I can see that it might not work out in 
the real world.

>> But then, if your range queries are based on a rank derived from value, why
>> not index value directly? You'd still get fast range queries based on 
>> values, no?
> 
> You get fast value range queries, but rank range queries become slower and
> slower the farther away you get from the top rank.

This should not be true.  You should not be using OFFSET.  Your queries should 
be something like

SELECT * FROM ranked WHERE rank BETWEEN 154000 AND !55000 ORDER BY rank

which should be lightning fast because you have an index on "rank".

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

Re: [sqlite] [EXTERNAL] bind blob lifetime

[Hick Gunter]

A bound blob or string is destroyed "after SQLite has finished with it". This 
should be the case when sqlite3_clear_bindings() is called. Are you sure it is 
not deleted then? Code reading suggests it should be.

Other times are when the parameter is re-bound, or the statement finalized.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dave Milter
Gesendet: Dienstag, 09. Jänner 2018 16:36
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] bind blob lifetime

I have cycle like this:

```c
const char sql[] = "INSERT INTO test (pk, geom) VALUES (?, ?)"; sqlite3_stmt 
*stmt;
sqlite3_prepare_v2 (handle, sql, strlen (sql), , NULL);

for (...) {
sqlite3_reset (stmt);
sqlite3_clear_bindings (stmt);
int blob_size = ..;
unsigned char *blob = malloc(blob_size);
sqlite3_bind_int64 (stmt, 1, pk);
sqlite3_bind_blob (stmt, 2, blob, blob_size, free);
sqlite3_step (stmt);
}
//sqlite3_finalize
```

I wonder is it necessary to allocate memory on every cycle?
I know that I can pass SQLITE_TRANSIENT, but in this case code would be exactly 
the same, just allocation on every cycle happens inside sqlite.

According to documentation  it is not clear when sqlite call destructor of blob 
(in our case  "free"), is it happens after:
sqlite3_reset
sqlite3_clear_bindings
sqlite3_bind_blob
step

sqlite3_reset
sqlite3_clear_bindings
sqlite3_bind_blob
<< here previous memory was freed???
step

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bind blob lifetime

[Dave Milter]

I have cycle like this:

```c
const char sql[] = "INSERT INTO test (pk, geom) VALUES (?, ?)";
sqlite3_stmt *stmt;
sqlite3_prepare_v2 (handle, sql, strlen (sql), , NULL);

for (...) {
sqlite3_reset (stmt);
sqlite3_clear_bindings (stmt);
int blob_size = ..;
unsigned char *blob = malloc(blob_size);
sqlite3_bind_int64 (stmt, 1, pk);
sqlite3_bind_blob (stmt, 2, blob, blob_size, free);
sqlite3_step (stmt);
}
//sqlite3_finalize
```

I wonder is it necessary to allocate memory on every cycle?
I know that I can pass SQLITE_TRANSIENT, but in this case code would
be exactly the same, just allocation on every cycle happens inside
sqlite.

According to documentation  it is not clear when sqlite call
destructor of blob (in our case  "free"), is it happens after:
sqlite3_reset
sqlite3_clear_bindings
sqlite3_bind_blob
step

sqlite3_reset
sqlite3_clear_bindings
sqlite3_bind_blob
<< here previous memory was freed???
step

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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Wade, William]

If you were going to do this entirely in memory (perhaps in C, or some similar 
language), you would likely use some tree structure where each node keeps track 
of the number of descendants (direct and indirect) of that node. That allows 
the operations you describe to occur in O(log(N)) time. Single-record 
insert/delete/update has the same time complexity.

It is likely that for your tree, every node would have the same structure 
(struct in C), or else every internal node would have one structure, and every 
leaf node would have another structure.

Now given a bunch of objects with the same structure, you can easily store them 
in a relational database, rather than in memory, and perform similar operations 
on them. A collection of struct instances turns into a table, and a C pointer 
turns into a row-id (or similar). This isn't entirely free, of course. In C we 
think of a pointer dereference as occurring in constant time, while in a 
database, a key lookup is typically log(N) time, but still, your log(N) 
in-memory solution becomes a log-squared(N) database solution, and that is 
usually fast enough.

Of course you lose some of the database "convenience. You're essentially 
implementing trees which are close to those that already "free" in sqlite. 
Likewise, some simple SQL queries turn into something more complex (since you 
need to maintain your tree). At least you still get the ACID benefits.

If I google "counting tree in sqlite" I see some hits that, perhaps, already do 
this kind of thing.

Regards,
Bill

-Original Message-
From: Eric Grange [mailto:zar...@gmail.com]
Sent: Tuesday, January 9, 2018 3:51
To: General Discussion of SQLite Database 
Subject: [sqlite] Efficient ways to maintaining order rank / row_number() in a 
rather large set ?

Hi,

I have a problem where I have a large set of (key, value) which I want to sort 
by value, and then store in a table having (rank, key, value) fields, so that 
for a given key I can quickly find the rank, or for a given rank range, I can 
quickly list the keys & values.

Since there is no ROW_NUMBER() function, but there is an autoincrement feature, 
and the rank are numbered 1, 2, 3 etc. the strategy I have been using is to 
create ranked table like

   CREATE RANKED (
  RANK INTEGER PRIMARY KEY AUTOINCREMENT,
  KEY INTEGER,
  VALUE FLOAT
   )

(+ an index for the key)

and then I fill that table with something like

   INSERT INTO RANKED
  SELECT key, value
  FROM ...something rather complex and big...
  ORDER BY value desc

This works well enough, but as the amount of values to be ranked increases, 
this feels wasteful to delete everything and then re-insert everything just to 
adjust the RANK column, also I am running into memory issues as the ORDER BY 
requires a temporary b-tree which runs into the gigabyte range in some 
instances.

I have ways to maintain the KEY and VALUES individually and incrementally, but 
approaches I have tried to maintain the RANK with UPDATE queries ran much 
slower than deleting and recreating everything, though this could just be bad 
implementations from my part.

Are there any other strategies I could use that could update just the RANK 
field and mitigate the temporary B-tree size?

Eric


**
This e-mail and any attachments thereto may contain confidential information 
and/or information protected by intellectual property rights for the exclusive 
attention of the intended addressees named above. If you have received this 
transmission in error, please immediately notify the sender by return e-mail 
and delete this message and its attachments. Unauthorized use, copying or 
further full or partial distribution of this e-mail or its contents is 
prohibited.
**
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Dinu]

Analogous to the percentile solution (it's actually the same thing), you can
use a checkpointing table. This has roughly the complexity of SQRT(n) for
both read and write.

I.E. say you expect to have 1M records and define order based on value then
id.

You then make a checkpoint table (first_rank,value,rec_id) holding every
1000th record in sorted order. So row 1 of checkpoint table coresponds to
the 1000th sorted record.

When you insert/delete a row, you only need to update checkpoints that come
after said row.

When you are searching for row 4521, you do something like:

SELECT 
FROM 
table 
JOIN
checkpoint
WHERE 
(
(table.value=checkpoint.value AND table.id>=checkpoint.id) OR 
table.value>checkpoint.value
) AND
checkpoint.first_rank=4500
ORDER BY
table.value ASC,table.id ASC
LIMIT 21,1



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Dinu]

The only way to efficiently do this would be to have counting (range) index
b-trees. Since you don't, you're stuck with a O(n) implementation, either on
reading or writing. So your solution is as good as it gets, save maybe some
implementation particularities.

However, you may consider a shift in perspective: with this kind of data,
statisticians use percentiles.
That is, instead of querying for ranks 21,000-22,000, you query for "top
1%", "6-8%" etc, based on either value or rank; this way, you can maintain a
percentile rank table as granular as you like (i.e. every 1% results in a
table with 100 lines). Each line would have count, value min, value max.
Such a table is much faster to update and then if you need to retrieve the
actual records, you use by range (value BETWEEN min AND max) joined with the
percentile table.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Dominique Devienne]

On Tue, Jan 9, 2018 at 12:35 PM, Eric Grange  wrote:

> > But then, if your range queries are based on a rank derived from value,
> why
> > not index value directly? You'd still get fast range queries based on
> values, no?
>
> You get fast value range queries, but rank range queries become slower and
> slower the farther away you get from the top rank.
>

As I wrote, that can be (greatly IMHO) mitigated by having a partial
mapping from values to ranks,
across the value range, which thus allows to restrict your query to a
value-range that's larger than
the exact rank-range you want, but still narrow enough to be fast.

And another thought is that you may be able to derive than partial mapping
from the stats ANALYZE [1]
extracts and stores in [2] or [3], if you don't want to do it in your app.
(stats on the value column of your
original table, not the ranked "derived" one).

Also, I can't find the name right now, and thus no link sorry, but SQLite
has a way to "mount" an index
as a table, if I recall correctly (mostly for troubleshooting if I
remember), not sure if that's super useful
since you'd probably need access to low-level b-tree info I think estimate
ranks from that I think, the
stat(1|3|4) table info is a better avenue I think. --DD

[1] https://www.sqlite.org/lang_analyze.html
[2] https://www.sqlite.org/fileformat2.html#stat3tab
[3] https://www.sqlite.org/fileformat2.html#stat4tab
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Eric Grange]

> Do you actually have a need to find the 4512nd rank ?

Yes, this is is used to display and check on "neighbors", ie. keys with
similar rank.
The other very common query is to show the rank for a given key.

In both cases, since things are constantly in flux, the absolute rank and
neighbor do not really matter
(outside the top ranks), but changes in rank are being looked at.
i.e. having rank 155k or 154k is not really meaningful in itself, but on
the other hand
gaining 1000 "ranks" by going from 155k to 154k is what users are after.


> 3) In your programming language, number the rows by doing
>
>SELECT KEY FROM RANKED ORDER BY VALUE
>
>and for each row returned doing an UPDATE for the RANK value.
>It’ll be slower, but it won’t take up the huge chunk of memory needed to
keep that index in memory.

Yes, that's what I tried, but it is indeed much slower than deleting the
whole table an inserting everything all at once,
because as soon as one key moves from top tier to bottom (or vice versa),
you have to touch basically all records,
and doing so with many updates wrecks the performance completely.

> But then, if your range queries are based on a rank derived from value,
why
> not index value directly? You'd still get fast range queries based on
values, no?

You get fast value range queries, but rank range queries become slower and
slower the farther away you get from the top rank.

Also while most end-user queries are for the top 100 / top 1000, those
results are cached and only infrequently
hit the db (so even if finding out the top 1000 was slow and inefficient,
it would not really matter).
In practice, the queries that really hit on the db are for "random" keys
far from the top 1000.

Eric


On Tue, Jan 9, 2018 at 11:44 AM, Dominique Devienne 
wrote:

> On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange  wrote:
>
> > So the order by is used to control the insertion order, so that the RANK
> > autoinc primary key ends up with natural rank order
>
>
> But then, if your range queries are based on a rank derived from value, why
> not index value directly?
> You'd still get fast range queries based on values, no? That probably
> forces you app to maintain a
> mapping from "rank" to values, but you don't need to know all ranks, just
> the few necessary to know
> where to "page" from, no? (assuming I'm guess what you use rank for
> correctly).
>
> SQLite then maintain that index automatically, no? I'm probably missing
> something though. Sounds too simple... --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Dominique Devienne]

On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange  wrote:

> So the order by is used to control the insertion order, so that the RANK
> autoinc primary key ends up with natural rank order


But then, if your range queries are based on a rank derived from value, why
not index value directly?
You'd still get fast range queries based on values, no? That probably
forces you app to maintain a
mapping from "rank" to values, but you don't need to know all ranks, just
the few necessary to know
where to "page" from, no? (assuming I'm guess what you use rank for
correctly).

SQLite then maintain that index automatically, no? I'm probably missing
something though. Sounds too simple... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Simon Slavin]

On 9 Jan 2018, at 10:26am, Eric Grange  wrote:

> You mean using limit / offset instead ?
> 
> Even with an index on the VALUE column, queries like
> 
>select * from ranked
>order by value
>limit 10 offset xxx
> 
> become very slow when xxx is great

Yeah, to do OFFSET SQLite has to do the whole SELECT, it just throws away the 
first few entries without reporting them to you.

Do you actually have a need to find the 4512nd rank ?  I was expecting you to 
just need to need to list the rows in rank order, meaning you would never need 
OFFSET.

Okay, if you do need to do things like find the 4512nd rank, and you want to be 
able to insert the values without taking the great amount of memory needed for 
the ORDER BY,

1) declare an index on the VALUE column (fastest to do the INSERT first then 
create the index, but might not matter for your data)
2) do the INSERT without ORDER BY
3) In your programming language, number the rows by doing

SELECT KEY FROM RANKED ORDER BY VALUE

and for each row returned doing an UPDATE for the RANK value.

It’ll be slower, but it won’t take up the huge chunk of memory needed to keep 
that index in memory.

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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Andy Ling]

Isn't this really a repeat of this thread...

http://sqlite.1065341.n5.nabble.com/how-into-insert-row-into-middle-of-table-with-integer-primary-key-td98629.html

The result of which was, don't try and use the table row order to sort your 
data. Add a column that defines your sort order
and do the sorting on output, not input.

I rather liked Jens solution to use a string to define the sort order. (top of 
second page of thread)

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Eric Grange
Sent: Tue 09 January 2018 10:26
To: SQLite mailing list
Subject: [External] Re: [sqlite] Efficient ways to maintaining order rank / 
row_number() in a rather large set ?

You mean using limit / offset instead ?

Even with an index on the VALUE column, queries like

select * from ranked
order by value
limit 10 offset xxx

become very slow when xxx is great, while

select * from ranked
order by rank
where rank between xxx and xxx+9

are fast regardless of the value of xxx

Similarly finding the rank of a key becomes sluggish for keys that are not
in the top without

So the order by is used to control the insertion order, so that the RANK
autoinc primary key ends up with natural rank order



On Tue, Jan 9, 2018 at 10:59 AM, Simon Slavin  wrote:

> On 9 Jan 2018, at 9:50am, Eric Grange  wrote:
>
> > then I fill that table with something like
> >
> >   INSERT INTO RANKED
> >  SELECT key, value
> >  FROM ...something rather complex and big...
> >  ORDER BY value desc
> >
> > This works well enough, but as the amount of values to be ranked
> increases,
> > this feels wasteful to delete everything and then re-insert
> > everything just to adjust the RANK column, also I am running into memory
> > issues as the ORDER BY requires a temporary b-tree
> > which runs into the gigabyte range in some instances.
>
> The ORDER BY clause serves no useful value here.  Leave it out.  Do your
> sorting when you query the table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Eric Grange]

You mean using limit / offset instead ?

Even with an index on the VALUE column, queries like

select * from ranked
order by value
limit 10 offset xxx

become very slow when xxx is great, while

select * from ranked
order by rank
where rank between xxx and xxx+9

are fast regardless of the value of xxx

Similarly finding the rank of a key becomes sluggish for keys that are not
in the top without

So the order by is used to control the insertion order, so that the RANK
autoinc primary key ends up with natural rank order



On Tue, Jan 9, 2018 at 10:59 AM, Simon Slavin  wrote:

> On 9 Jan 2018, at 9:50am, Eric Grange  wrote:
>
> > then I fill that table with something like
> >
> >   INSERT INTO RANKED
> >  SELECT key, value
> >  FROM ...something rather complex and big...
> >  ORDER BY value desc
> >
> > This works well enough, but as the amount of values to be ranked
> increases,
> > this feels wasteful to delete everything and then re-insert
> > everything just to adjust the RANK column, also I am running into memory
> > issues as the ORDER BY requires a temporary b-tree
> > which runs into the gigabyte range in some instances.
>
> The ORDER BY clause serves no useful value here.  Leave it out.  Do your
> sorting when you query the table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Simon Slavin]

On 9 Jan 2018, at 9:50am, Eric Grange  wrote:

> then I fill that table with something like
> 
>   INSERT INTO RANKED
>  SELECT key, value
>  FROM ...something rather complex and big...
>  ORDER BY value desc
> 
> This works well enough, but as the amount of values to be ranked increases,
> this feels wasteful to delete everything and then re-insert
> everything just to adjust the RANK column, also I am running into memory
> issues as the ORDER BY requires a temporary b-tree
> which runs into the gigabyte range in some instances.

The ORDER BY clause serves no useful value here.  Leave it out.  Do your 
sorting when you query the table.

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

[sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Eric Grange]

Hi,

I have a problem where I have a large set of (key, value) which I want to
sort by value, and then store in a table having (rank, key, value) fields,
so that for a given key I can quickly find the rank, or for a given rank
range, I can quickly list the keys & values.

Since there is no ROW_NUMBER() function, but there is an autoincrement
feature, and the rank are numbered 1, 2, 3 etc. the strategy I have
been using is to create ranked table like

   CREATE RANKED (
  RANK INTEGER PRIMARY KEY AUTOINCREMENT,
  KEY INTEGER,
  VALUE FLOAT
   )

(+ an index for the key)

and then I fill that table with something like

   INSERT INTO RANKED
  SELECT key, value
  FROM ...something rather complex and big...
  ORDER BY value desc

This works well enough, but as the amount of values to be ranked increases,
this feels wasteful to delete everything and then re-insert
everything just to adjust the RANK column, also I am running into memory
issues as the ORDER BY requires a temporary b-tree
which runs into the gigabyte range in some instances.

I have ways to maintain the KEY and VALUES individually and incrementally,
but approaches I have tried to maintain the RANK
with UPDATE queries ran much slower than deleting and recreating
everything, though this could just be bad implementations
from my part.

Are there any other strategies I could use that could update just the RANK
field and mitigate the temporary B-tree size?

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