SQLite 3.15.0 scheduled for 2016-10-14

classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

SQLite 3.15.0 scheduled for 2016-10-14

Richard Hipp-3
Our current schedule for the next SQLite release (3.15.0) is for 2016-10-14.

Your beta-tests are appreciated.  You can get a pre-release snapshot
from the download page (https://www.sqlite.org/download.html) and you
can review the change log
(https://www.sqlite.org/draft/releaselog/3_15_0.html).

The trunk (https://www.sqlite.org/src/timeline?r=trunk) is stable and
is being used by the SQLite developers for mission-critical processes.

Thank you for your attention.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Petite Abeille-2

> On Sep 22, 2016, at 9:04 PM, Richard Hipp <[hidden email]> wrote:
>
> (https://www.sqlite.org/draft/releaselog/3_15_0.html).

Ohhhhh! Row Values! Nice! :)

https://www.sqlite.org/draft/rowvalue.html



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Darren Duncan
On 2016-09-22 12:16 PM, Petite Abeille wrote:
>
>> On Sep 22, 2016, at 9:04 PM, Richard Hipp <[hidden email]> wrote:
>>
>> (https://www.sqlite.org/draft/releaselog/3_15_0.html).
>
> Ohhhhh! Row Values! Nice! :)
>
> https://www.sqlite.org/draft/rowvalue.html

I second that, its a valuable feature to have.

The purist in me thinks it should also be possible to have rows with exactly 1
or zero elements also, not just 2+ as described above.

Syntactically, () could be a zero-element row, and distinguishing a
single-element row could be done with say a trailing comma; eg "(42,)" is a
single-element row while "(42)" is simply the value 42 since parens are also
used for forcing evaluation precedence of expressions.

-- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

James K. Lowden
On Thu, 22 Sep 2016 12:43:29 -0700
Darren Duncan <[hidden email]> wrote:

> single-element row could be done with say a trailing comma; eg
> "(42,)"

All hail the Python tuple!  

        "Tuples of two or more items are formed by comma-separated
lists of expressions. A tuple of one item (a ?singleton?) can be formed
by affixing a comma to an expression (an expression by itself does not
create a tuple, since parentheses must be usable for grouping of
expressions). An empty tuple can be formed by an empty pair of
parentheses."

https://docs.python.org/3/reference/datamodel.html#the-standard-type-hierarchy

--jkl


 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Dominique Devienne
In reply to this post by Richard Hipp-3
On Thu, Sep 22, 2016 at 9:04 PM, Richard Hipp <[hidden email]> wrote:

> Our current schedule for the next SQLite release (3.15.0) is for
> 2016-10-14.
> [...] you can review the change log
> (https://www.sqlite.org/draft/releaselog/3_15_0.html).



> SQLite Release 3.15.0 (Pending)
>
> Added support for row values.
>

https://www.sqlite.org/draft/rowvalue.html

When do we get to bind row values?

For a long time, I've wanted to bind the RHS of a IN operator of arbitrary
size,
instead of
1) having to hard-code the cardinality, e.g. IN (:1, :2);
2) or use a tmp table to insert the values and use a subquery;
3) somehow use an eponymous vtable as the RHS.

None of which is very clean or satisfying.
Any chance this could be finally cleaned up by binding a row value instead?
--DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Clemens Ladisch
In reply to this post by Petite Abeille-2
Petite Abeille wrote:
> Ohhhhh! Row Values! Nice! :)

<https://www.sqlite.org/draft/rowvalue.html#row_values_in_update_statements>
This description exchanges "LHS" and "RHS".


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

R Smith
In reply to this post by Richard Hipp-3


On 2016/09/22 9:04 PM, Richard Hipp wrote:
> Our current schedule for the next SQLite release (3.15.0) is for 2016-10-14.


    2.2. Row Values In UPDATE Statements

Row values can also be used in the SET clause of an UPDATE
<https://www.sqlite.org/draft/lang_update.html> statement. The RHS must
be a list of column names. The LHS can be any row value. For example:

UPDATE tab3
    SET (a,b,c) = (SELECT x,y,z
                     FROM tab4
                    WHERE tab4.w=tab3.d);
  WHERE tab3.e BETWEEN 55 AND 66;


---- That's an answered need if ever I saw one - Much appreciation!!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Richard Hipp-3
In reply to this post by Dominique Devienne
On 9/23/16, Dominique Devienne <[hidden email]> wrote:
>
> For a long time, I've wanted to bind the RHS of a IN operator of arbitrary
> size,

See the carray() table-valued function extension:
https://www.sqlite.org/draft/carray.html

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Dominique Devienne
On Fri, Sep 23, 2016 at 1:39 PM, Richard Hipp <[hidden email]> wrote:

> On 9/23/16, Dominique Devienne <[hidden email]> wrote:
> >
> > For a long time, I've wanted to bind the RHS of a IN operator of
> arbitrary size,
>
> See the carray() table-valued function extension:
> https://www.sqlite.org/draft/carray.html


Thanks for the reminder.

My RHS is a list of guids in the form of blob(16) values, so carray doesn't
fit.
I can of course make my own carray-like eponymous vtable, but honestly
passing
a pointer to an array who's lifetime is not managed by SQLite is a hack
IMHO.

This is what I wish for:

sqlite3_bind_row_value( // or sqlite3_bind_array, either way
  sqlite3_stmt*,
  int, // bind index
  void *pApp, // access via sqlite3_user_data(), and passed in to callbacks
below
  int (*xSize)(sqlite3_context*, void* pApp), // optional? but if provided,
used by query planner
  int (*xItem)(sqlite3_context*, void* pApp), // returns SQLITE_OK or
SQLITE_DONE (unless xSize required)
  void(*xDestroy)(void* pApp)
);

This is like an "anonymous" "eponymous" "temporary" vtable, whose lifetime
is managed
by SQLite properly. It's a cross between a sqlite3_bind_* and
sqlite3_register_*, and the
sqlite3_context* allows to return one value at a time, with an optional
cardinality method
to allow the query planner to use the best plan, but w/o the
sqlite3_value** argument
since binding does not depend on runtime arguments.

And by reusing the machinery of https://www.sqlite.org/c3ref/context.html,
we can even leverage the recent
https://www.sqlite.org/c3ref/result_subtype.html too.

AFAIK, this is much easier than eponymous vtables, but more importantly
it is safe, clean, with proper (temporary) lifetime semantics, and
anonymous too.

My $0.02. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Keith Medcalf
In reply to this post by Dominique Devienne

> For a long time, I've wanted to bind the RHS of a IN operator of arbitrary
> size,
> instead of
> 1) having to hard-code the cardinality, e.g. IN (:1, :2);
> 2) or use a tmp table to insert the values and use a subquery;
> 3) somehow use an eponymous vtable as the RHS.
>
> None of which is very clean or satisfying.
> Any chance this could be finally cleaned up by binding a row value
> instead?
> --DD

A carray won't work for you?




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Dominique Devienne
On Fri, Sep 23, 2016 at 5:04 PM, Keith Medcalf <[hidden email]> wrote:

> [...] bind the RHS of a IN operator of arbitrary size [...]
>
> A carray won't work for you?


No. carray doesn't support blobs.

The semantic of carray is fundamentally flawed for variable-sized values.
And the lifetime management, or lack thereof, is also fundamentally flawed.

Binding is about providing explicit values to a compiled statement.

Binding a surrogate (a raw pointer...) to those values, to a table-valued
function,
which further assumes a given representation in memory, which for char*
implies
an indirection to some other memory slots which must be null-terminated,
that's
all very "icky" IMHO, and most unlike SQLite's usual clean designs.

My proposed sqlite3_bind_row_value() is I think much closer to SQLite's
norm. FWIW. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Quan Yong Zhai
http://sqlite.org/draft/rowvalue.html

Quote<<

A "row value" is an ordered list of two or more scalar values. In other words, a "row value" is a vector.>>



some word in the above sentence not strictly correct. A “row value” is a tuple, not a vector. When your using a tuple, you know how many items in it, and the type of each item of it.



Like “SELECT * FROM CUSTOMER WHERE (first_name, last_name, birthday) = ( :1, :2, :3)”

Obviously, LHS and RHS is a tuple, sqlite3_bind_values() is not needed for above SQL statements.



A vector is an arbitrary length of elements of same type.

Like “SELECT * FROM CUSTOMER WHERE CUST_ID  IN (?, ?, ?, ? ….)”



What you need is sqlite3_bind_vector(), And that’s carray extension work for, simple modify a few lines, it will support blobs or your custom type. I don’t think a dedicated sqlite3_bind_values() or sqlite3_bind_vector() worth the effort.



Zhai



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



From: Dominique Devienne<mailto:[hidden email]>
Sent: 2016年9月23日 23:29
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14



On Fri, Sep 23, 2016 at 5:04 PM, Keith Medcalf <[hidden email]> wrote:

> [...] bind the RHS of a IN operator of arbitrary size [...]
>
> A carray won't work for you?


No. carray doesn't support blobs.

The semantic of carray is fundamentally flawed for variable-sized values.
And the lifetime management, or lack thereof, is also fundamentally flawed.

Binding is about providing explicit values to a compiled statement.

Binding a surrogate (a raw pointer...) to those values, to a table-valued
function,
which further assumes a given representation in memory, which for char*
implies
an indirection to some other memory slots which must be null-terminated,
that's
all very "icky" IMHO, and most unlike SQLite's usual clean designs.

My proposed sqlite3_bind_row_value() is I think much closer to SQLite's
norm. FWIW. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Gerry Snyder-4
In reply to this post by Richard Hipp-3
Just curious--will Tcl lists be usable as row values?

Gerry Snyder

On Thu, Sep 22, 2016 at 12:04 PM, Richard Hipp <[hidden email]> wrote:

> Our current schedule for the next SQLite release (3.15.0) is for
> 2016-10-14.
>
> Your beta-tests are appreciated.  You can get a pre-release snapshot
> from the download page (https://www.sqlite.org/download.html) and you
> can review the change log
> (https://www.sqlite.org/draft/releaselog/3_15_0.html).
>
> The trunk (https://www.sqlite.org/src/timeline?r=trunk) is stable and
> is being used by the SQLite developers for mission-critical processes.
>
> Thank you for your attention.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

David Raymond
In reply to this post by Richard Hipp-3
-Add the ".testcase" and ".check" dot-commands.

Is there documention on what these are? I don't see anything on them in the linked CLI page draft.
https://www.sqlite.org/draft/cli.html

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Thursday, September 22, 2016 3:04 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

Our current schedule for the next SQLite release (3.15.0) is for 2016-10-14.

Your beta-tests are appreciated.  You can get a pre-release snapshot
from the download page (https://www.sqlite.org/download.html) and you
can review the change log
(https://www.sqlite.org/draft/releaselog/3_15_0.html).

The trunk (https://www.sqlite.org/src/timeline?r=trunk) is stable and
is being used by the SQLite developers for mission-critical processes.

Thank you for your attention.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Cecil Westerhof-5
In reply to this post by Richard Hipp-3
2016-09-22 21:04 GMT+02:00 Richard Hipp <[hidden email]>:

> Our current schedule for the next SQLite release (3.15.0) is for
> 2016-10-14.
>
> Your beta-tests are appreciated.


​What can I do to beta test?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Warren Young-2
On Sep 25, 2016, at 4:50 AM, Cecil Westerhof <[hidden email]> wrote:
>
> 2016-09-22 21:04 GMT+02:00 Richard Hipp <[hidden email]>:
>
>> Our current schedule for the next SQLite release (3.15.0) is for
>> 2016-10-14.
>>
>> Your beta-tests are appreciated.
>
> ​What can I do to beta test?

First, just try it against your existing application, to see if there are any regressions.

Second, go through the changelog and see if there are any new features or fixes that interest you:

  https://www.sqlite.org/draft/releaselog/current.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Simon Slavin-3

> On 26 Sep 2016, at 7:48pm, Warren Young <[hidden email]> wrote:
>
>> ​What can I do to beta test?

The other part of the question is "How do I download the beta-test version in order to beta-test it ?".

For technical reasons, the download page in the /draft/ version of the web site doesn't actually include download links.  The pre-release version can be found in the yellow portion at the top of this page:

<https://www.sqlite.org/download.html>

While I'm here, I note that

<https://www.sqlite.org/draft/cli.html#dotcmd>

does not include documentation for the two newly-introduced dot commands.  I think someone else noted this too but I can't find the post at the moment.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

David Raymond
In reply to this post by Richard Hipp-3
So the .help on .testcase says:
.testcase NAME         Begin redirecting output to 'testcase-out.txt'

And .check says:
.check GLOB            Fail if output since .testcase does not match

So it's a way to check actual TEXT output vs expected TEXT output, right? Since I'm not sure of the normal reason for this I just played around with it real quick. Here're some tests where I don't know if it's working as intended or not. (For below copy/pastes headers default on)


Windows 7 command prompt

sqlite_source_id()
2016-09-26 12:38:22 fe89225eab777c2c9cb1cbc31092b9e39f516842

------------------------------
.check on its own kills the session and quits back to the command prompt



D:\Temp>sqlite3
SQLite version 3.15.0 2016-09-26 12:38:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .check
Usage: .check GLOB-PATTERN

D:\Temp>


------------------------------
Also kills the session if it fails the test.



D:\Temp>sqlite3
SQLite version 3.15.0 2016-09-26 12:38:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table tbl (a int, b text, c real);

sqlite> insert into tbl values (1, 'one', 1.0), (2, 'two', 2.0), (3, 'three', 3.0);

sqlite> .testcase a

sqlite> select * from tbl where a = 1;

sqlite> .check 1|one|1.0
testcase-a FAILED
 Expected: [1|one|1.0]
      Got: [a|b|c
1|one|1.0
]

D:\Temp>


------------------------------
If testcase-out.txt exists from a previous session then .check looks at it even if you didn't do a .testcase on that session:



D:\Temp>sqlite3
SQLite version 3.15.0 2016-09-26 12:38:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table tbl (a int, b text, c real);

sqlite> insert into tbl values (1, 'one', 1.0), (2, 'two', 2.0), (3, 'three', 3.0);

sqlite> .check a|b|c*
testcase- ok

sqlite>


------------------------------
Doing a .testcase immediately followed by a .check gives an error and quits to command prompt.



sqlite> .testcase b

sqlite> .check *
Error: cannot read 'testcase-out.txt'

D:\Temp>



------------------------------
Same thing happens if you do run queries but don't have any returned rows



D:\Temp>sqlite3
SQLite version 3.15.0 2016-09-26 12:38:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table tbl (a int, b text, c real);

sqlite> insert into tbl values (1, 'one', 1.0), (2, 'two', 2.0), (3, 'three', 3.0);

sqlite> .testcase a

sqlite> select * from tbl where a = 4;

sqlite> .check *
Error: cannot read 'testcase-out.txt'

D:\Temp>



-----Original Message-----
From: David Raymond
Sent: Friday, September 23, 2016 12:57 PM
To: 'SQLite mailing list'
Subject: RE: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

-Add the ".testcase" and ".check" dot-commands.

Is there documention on what these are? I don't see anything on them in the linked CLI page draft.
https://www.sqlite.org/draft/cli.html

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Thursday, September 22, 2016 3:04 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

Our current schedule for the next SQLite release (3.15.0) is for 2016-10-14.

Your beta-tests are appreciated.  You can get a pre-release snapshot
from the download page (https://www.sqlite.org/download.html) and you
can review the change log
(https://www.sqlite.org/draft/releaselog/3_15_0.html).

The trunk (https://www.sqlite.org/src/timeline?r=trunk) is stable and
is being used by the SQLite developers for mission-critical processes.

Thank you for your attention.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Richard Hipp-3
On 9/26/16, David Raymond <[hidden email]> wrote:
> So the .help on .testcase says:
> .testcase NAME         Begin redirecting output to 'testcase-out.txt'
>
> And .check says:
> .check GLOB            Fail if output since .testcase does not match

These dot-commands help in writing scripts that test various SQL
language features in SQLite.

For example, I could test various row-value comparisons using a script
like this:

.testcase 100
SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1);
.check 1||1


Running that script using:

     ./sqlite3 <script.txt

Generates output: "testcase-100 ok".  Or, it generates an error
message if the expected result does not appear.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.15.0 scheduled for 2016-10-14

Simon Slavin-3

On 26 Sep 2016, at 9:42pm, Richard Hipp <[hidden email]> wrote:

> Generates output: "testcase-100 ok".  Or, it generates an error
> message if the expected result does not appear.

Does it also change the exit code of the application ?  If it does then you can test the output in shell scripts and more easily use the CLI as part of a test suite.

Or maybe it would be better to implement another dot command which immediately quit the CLI and returned a code indicating whether the previous output matched a GLOB value.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12