[sqlite] SQLite 3.20.0 postponed

[Richard Hipp]

The 3.20.0 release will be delayed.  Some concerns came up over the
new sqlite3_value_pointer() interface.  Interface chagnes were made
over the weekend.  But there are still concerns.  So the decision has
been made to back off and give the current design a few weeks to soak
before trying to press forward with a release which will commit us to
a particular design.

The draft website is still up at https://sqlite.org/draft - note that
the change log at https://sqlite.org/draft/releaselog/3_20_0.html now
identifies three (obscure) backwards compatibility breaks.  Your input
on these changes is requested.

The "prelease snapshot" on https://sqlite.org/download.html is
up-to-date for people who want to try out the new code.  Please do so.
Report any concerns here or via direct email to me.

I will try provide a write-up on the motivations, use, and limitations
of sqlite3_value_pointer() soon.

All of the changes that where queued for release on branch-3.20 are on
trunk.  Development will continue on trunk until we restart the
release cycle.

The check-list (https://www.sqlite.org/checklists/320/index) has
been reset to its initial state.  It will start over again in a week
or two.

-- 
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] Draft docs typo

[Dominique Devienne]

On Mon, Jul 17, 2017 at 5:43 PM, petern  wrote:

> Speaking of type string lifetime, what about pointer lifetime management?
>
> I think you've overlooked the pointer lifetime problem for
> sqlite3_result_pointer(C,P,T).   This form, unlike the blob form, lacks the
> destructor callback for cleanup when SQLite determines the scope of the
> last statement using a copy of the pointer value has exited. Your use case
> appears to presume that a virtual table is managing the pointer object.
> Either that or this mechanism is only otherwise used for global pointer
> variables.   Is there another way for the host application to be notified
> that the last statement using the pointer value has exited scope?


FWIW, I had exactly the same thought. W/o a destructor, this new feature
seems like
it's missing an essential piece, although I'm not sure I understand this
new feature correctly.

It's just that when I see raw pointers being passed around, with no
"lifetime attached",
reflexes kick in and make me worry about leaks, accesses to "dead"
pointers, etc... --DD

PS: That document would be mighty useful Richard :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Ron]

This seems to be exactly what I'm looking for. Thanks!

--Ron

Op ma 17 jul. 2017 om 20:23 schreef Jens Alfke 

>
> > On Jul 17, 2017, at 11:08 AM, Ron  wrote:
> >
> > I am looking into the possibility of putting the changes in SQLite
> database
> > into kafka, similar to this:
> >
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> <
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> >
>
> I suggest looking at the source code of the Sessions extension, which
> “provide[s] a mechanism for recording changes to some or all of the rowid
> tables in an SQLite database, and packaging those changes into a
> "changeset" or "patchset" file that can later be used to apply the same set
> of changes to another database with the same schema and compatible starting
> data.”
>
> It looks as though the changesets produced by the extension aren’t
> intended to be readable, but its implementation must be doing something
> similar to what you want to do, i.e. discovering all the changes to tables
> made since some prior time. So you should be able to modify the extension
> to take those changes and send them to Kafka instead of writing them into a
> blob.
>
> https://www.sqlite.org/sessionintro.html
>
> —Jens
> ___
> 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] (no subject)

[Jens Alfke]

> On Jul 17, 2017, at 11:08 AM, Ron  wrote:
> 
> I am looking into the possibility of putting the changes in SQLite database
> into kafka, similar to this:
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
>  
> 

I suggest looking at the source code of the Sessions extension, which 
“provide[s] a mechanism for recording changes to some or all of the rowid 
tables in an SQLite database, and packaging those changes into a "changeset" or 
"patchset" file that can later be used to apply the same set of changes to 
another database with the same schema and compatible starting data.”

It looks as though the changesets produced by the extension aren’t intended to 
be readable, but its implementation must be doing something similar to what you 
want to do, i.e. discovering all the changes to tables made since some prior 
time. So you should be able to modify the extension to take those changes and 
send them to Kafka instead of writing them into a blob.

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

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

Re: [sqlite] logical decoding?

[Ron]

First, apologies for sending without a subject line, pressed send too soon.

I would need all changes in all tables, but not transactions that did not
execute. How would I fill about setting this up?

Op ma 17 jul. 2017 om 20:14 schreef Jay Kreibich 

>
> I’d look at creating a virtual table that can be setup to “shadow” any
> existing table.  Basically pass-through any read or write operations,
> possibly shunting off writes.   This has the advantage of not requiring any
> kind of patching or modifications to the core library.  Sounds a lot
> simpler too.
>
>  -j
>
>
>
>
>
> On Jul 17, 2017, at 1:08 PM, Ron  wrote:
>
> > Hi,
> >
> > Before I dive into the SQLite internals, I'd like to ask the mailing list
> > first.
> >
> > I am looking into the possibility of putting the changes in SQLite
> database
> > into kafka, similar to this:
> >
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> > .
> >
> > Would it be hard to get this info out of SQLite?
> > PostgreSQL calls it 'logical decoding'. I may not need the full feature,
> > but I would need all changed records.
> >
> > Thanks for any insights.
> > Ron Arts
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
> ___
> 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] (no subject)

[Jay Kreibich]

I’d look at creating a virtual table that can be setup to “shadow” any existing 
table.  Basically pass-through any read or write operations, possibly shunting 
off writes.   This has the advantage of not requiring any kind of patching or 
modifications to the core library.  Sounds a lot simpler too.

 -j





On Jul 17, 2017, at 1:08 PM, Ron  wrote:

> Hi,
> 
> Before I dive into the SQLite internals, I'd like to ask the mailing list
> first.
> 
> I am looking into the possibility of putting the changes in SQLite database
> into kafka, similar to this:
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> .
> 
> Would it be hard to get this info out of SQLite?
> PostgreSQL calls it 'logical decoding'. I may not need the full feature,
> but I would need all changed records.
> 
> Thanks for any insights.
> Ron Arts
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



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

[sqlite] (no subject)

[Ron]

Hi,

Before I dive into the SQLite internals, I'd like to ask the mailing list
first.

I am looking into the possibility of putting the changes in SQLite database
into kafka, similar to this:
https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
.

Would it be hard to get this info out of SQLite?
PostgreSQL calls it 'logical decoding'. I may not need the full feature,
but I would need all changed records.

Thanks for any insights.
Ron Arts
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Draft docs typo

[Richard Hipp]

I'm trying to write up a document on the whole sqlite3_bind_pointer()
thing now, that describes its motivation and its limitations.
Hopefully that will clear up the confusion.  I will announce when the
new document goes up and request clearance from petern and kmedcalf
prior to the release.

On 7/17/17, Keith Medcalf  wrote:
>
> On Monday, 17 July, 2017 08:00, Richard Hipp  wrote:
>> On 7/17/17, Keith Medcalf  wrote:
>
>> > Also, a question.  sqlite3_bind_pointer(C, P, T) and
>> > sqlite3_value_pointer(C, P, T) indicates that T should be a "static
>> > string".
>
>> > Does this mean of type SQLITE_STATIC and that it cannot be a string
>> > located on the stack (ie, an SQLITE_TRANSIENT), or is a copy made of
>> > the string value (ala SQLITE_TRANSIENT) for future use?
>
>> No copy is made of the string.  SQLite merely keeps a pointer.  If the
>> memory that holds the string is deallocated or reused for some other
>> purpose, bad things will happen.
>
> Ok, understand.  I presume therefore that this is NOT a database type but is
> for passing around value types and contexts only.  Therefore using a
> quoted-string will point to something in the global string table built by
> the compiler and therefore be static.  Makes sense.
>
> So if the sqlite3_bind_pointer is used to bind into a database column, that
> value will be null when stored in the database?  I suppose I could just try
> this, but I thought I would ask you just to be sure ...
>
> Example:
>
> prepare("insert into table (col1, col2) values (?,?)")
> bind_int64(...,1,intval)
> bind_pointer(...,2,, "carray")
> step()
>
> that col1 gets the integer and col2 gets a NULL?  and that the bind_pointer
> actual pointer value and type string is only accessible when the bound
> column is accessed by something that uses the used the sqlite3_value type
> interfaces (that does not actually originate or terminate as a database row
> -- and the pointer type is not preserved in the database itself but would be
> persisted as if NULL?)
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Draft docs typo

[Keith Medcalf]

On Monday, 17 July, 2017 08:00, Richard Hipp  wrote:
> On 7/17/17, Keith Medcalf  wrote:

> > Also, a question.  sqlite3_bind_pointer(C, P, T) and
> > sqlite3_value_pointer(C, P, T) indicates that T should be a "static
> > string".

> > Does this mean of type SQLITE_STATIC and that it cannot be a string
> > located on the stack (ie, an SQLITE_TRANSIENT), or is a copy made of
> > the string value (ala SQLITE_TRANSIENT) for future use?
 
> No copy is made of the string.  SQLite merely keeps a pointer.  If the
> memory that holds the string is deallocated or reused for some other
> purpose, bad things will happen.

Ok, understand.  I presume therefore that this is NOT a database type but is 
for passing around value types and contexts only.  Therefore using a 
quoted-string will point to something in the global string table built by the 
compiler and therefore be static.  Makes sense.

So if the sqlite3_bind_pointer is used to bind into a database column, that 
value will be null when stored in the database?  I suppose I could just try 
this, but I thought I would ask you just to be sure ...

Example:

prepare("insert into table (col1, col2) values (?,?)")
bind_int64(...,1,intval)
bind_pointer(...,2,, "carray")
step()

that col1 gets the integer and col2 gets a NULL?  and that the bind_pointer 
actual pointer value and type string is only accessible when the bound column 
is accessed by something that uses the used the sqlite3_value type interfaces 
(that does not actually originate or terminate as a database row -- and the 
pointer type is not preserved in the database itself but would be persisted as 
if NULL?)




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

Re: [sqlite] Draft docs typo

[petern]

Speaking of type string lifetime, what about pointer lifetime management?

I think you've overlooked the pointer lifetime problem for
sqlite3_result_pointer(C,P,T).   This form, unlike the blob form, lacks the
destructor callback for cleanup when SQLite determines the scope of the
last statement using a copy of the pointer value has exited. Your use case
appears to presume that a virtual table is managing the pointer object.
Either that or this mechanism is only otherwise used for global pointer
variables.   Is there another way for the host application to be notified
that the last statement using the pointer value has exited scope?



On Mon, Jul 17, 2017 at 6:59 AM, Richard Hipp  wrote:

> On 7/17/17, Keith Medcalf  wrote:
> >
> > Also, a question.  sqlite3_bind_pointer(C, P, T) and
> > sqlite3_value_pointer(C, P, T) indicates that T should be a "static
> string".
> >  Does this mean of type SQLITE_STATIC and that it cannot be a string
> located
> > on the stack (ie, an SQLITE_TRANSIENT), or is a copy made of the string
> > value (ala SQLITE_TRANSIENT) for future use?
>
> No copy is made of the string.  SQLite merely keeps a pointer.  If the
> memory that holds the string is deallocated or reused for some other
> purpose, bad things will happen.
>
> >
> > ---
> > Life should not be a journey to the grave with the intention of arriving
> > safely in a pretty and well preserved body, but rather to skid in
> broadside
> > in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> > proclaiming "Wow! What a Ride!"
> >  -- Hunter S. Thompson
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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] Error message for nonsensical flags.

[Richard Hipp]

On 7/17/17, Dan Ackroyd  wrote:
> Hi Rowan,
>
> Thanks for the feedback. fyi The problem seems to be with the version
> of SQLite shipped with PHP.
>
> Compiling with the version that PHP uses and running a little test, I get:
>
> versionString: "3.15.1"
> Error message: out of memory
>
> But compiling SQLite from source and then using that gives the
> expected error message:
>
> versionString: "3.20.0"
> Error message: bad parameter or other API misuse
>

The error message processing for the failure case in question was in
3.20.0 in response to your original bug report.
-- 
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] Draft docs typo

[Richard Hipp]

On 7/17/17, Keith Medcalf  wrote:
>
> Also, a question.  sqlite3_bind_pointer(C, P, T) and
> sqlite3_value_pointer(C, P, T) indicates that T should be a "static string".
>  Does this mean of type SQLITE_STATIC and that it cannot be a string located
> on the stack (ie, an SQLITE_TRANSIENT), or is a copy made of the string
> value (ala SQLITE_TRANSIENT) for future use?

No copy is made of the string.  SQLite merely keeps a pointer.  If the
memory that holds the string is deallocated or reused for some other
purpose, bad things will happen.

>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Draft docs typo

[Keith Medcalf]

https://sqlite.org/draft/c3ref/result_blob.html

host-language pointer P or type T

or should be of

Also, a question.  sqlite3_bind_pointer(C, P, T) and sqlite3_value_pointer(C, 
P, T) indicates that T should be a "static string".  Does this mean of type 
SQLITE_STATIC and that it cannot be a string located on the stack (ie, an 
SQLITE_TRANSIENT), or is a copy made of the string value (ala SQLITE_TRANSIENT) 
for future use?

---
Life should not be a journey to the grave with the intention of arriving safely 
in a pretty and well preserved body, but rather to skid in broadside in a cloud 
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! 
What a Ride!"
 -- Hunter S. Thompson




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

Re: [sqlite] Error message for nonsensical flags.

[Dan Ackroyd]

Hi Rowan,

Thanks for the feedback. fyi The problem seems to be with the version
of SQLite shipped with PHP.

Compiling with the version that PHP uses and running a little test, I get:

versionString: "3.15.1"
Error message: out of memory

But compiling SQLite from source and then using that gives the
expected error message:

versionString: "3.20.0"
Error message: bad parameter or other API misuse

So the PHP maintainers probably just need to use the latest version of SQLite.

Thanks for pointing me in the right direction.

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

Re: [sqlite] Version 3.20.0 coming soon...

[Richard Hipp]

On 7/17/17, petern  wrote:
> Missing documentation or wrong extension source?

The documentation and code you are looking are from different branches.

>
> https://www.sqlite.org/src/file/ext/misc/remember.c line 51:
> -> ptr = sqlite3_value_pointer(argv[1], "carray");
>
> This signature, 'void* sqlite3_value_pointer(V,T)' , is not documented here:
>
> https://sqlite.org/draft/c3ref/value_blob.html
>
> Nor is there any mention about the additional parameter T when setting the
> result here:
>
> https://sqlite.org/draft/c3ref/result_blob.html
>
> Nor here:
>
> https://sqlite.org/draft/c3ref/bind_blob.html
>
> I suppose that's consistent but it would be nice to read about the new API.
>
> The only clue about how it works is a brief statement in change header of
> remember.c:
>
> "The pointer type is a string that must compare equal using strcmp() or
> else the pointer comes through as a NULL."
>
> On Thu, Jul 13, 2017 at 11:49 AM, Richard Hipp  wrote:
>
>> The 3.20.0 release of SQLite is expected in about a week.  Please
>> report any issues that you have with the beta as soon as possible.
>>
>> Code is available in the "prerelease snapshot" at
>> https://sqlite.org/download.html
>>
>> An overview of changes is at https://sqlite.org/draft/
>> releaselog/3_20_0.html
>>
>> You can send issues to this email list, or directly to me.
>>
>> --
>> 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
>


-- 
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] Disable trigger?

[petern]

If runtime disablement of one or more whole triggers is desired, their
execution can be gated by adding a conditioned WHEN clause (or logical
conjuction with existing WHEN clause) to the offending trigger declaration.

https://sqlite.org/syntax/create-trigger-stmt.html

For example:

CREATE TRIGGER this_one ...etc...
WHEN NOT exists(SELECT name FROM disabled_triggers WHERE name='this_one')
BEGIN ...trigger body... END;

This example presumes a table called disabled_triggers which one manages at
runtime to name the trigger(s) to be temporarily disabled.



On Mon, Jul 17, 2017 at 1:12 AM, Clemens Ladisch  wrote:

> Thomas Flemming wrote:
> > Is there a way in SQLite, to temporary disable and enable a trigger
> without dropping and recreating ?
>
> You could disable recursive triggers, and then make your triggers
> recursive by executing all your SQL statements through a temporary
> trigger on some temporary table.  But that would restrict what you could
> execute, and be more cumbersome than removing the actual triggers.
>
> What is the actual problem you're trying to solve?  If you have
> temporarily inconsistent data, it might be a better idea to deal with it
> in a temporary table.
>
>
> Regards,
> Clemens
> ___
> 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] Website Typo

[Donald Heskett]

Your https://www.sqlite.org/whentouse.html page uses "emphasis" where it needs 
"emphasize".
It's in the second paragraph: "They emphasis scalability...".

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

Re: [sqlite] Disable trigger?

[Clemens Ladisch]

Thomas Flemming wrote:
> Is there a way in SQLite, to temporary disable and enable a trigger without 
> dropping and recreating ?

You could disable recursive triggers, and then make your triggers
recursive by executing all your SQL statements through a temporary
trigger on some temporary table.  But that would restrict what you could
execute, and be more cumbersome than removing the actual triggers.

What is the actual problem you're trying to solve?  If you have
temporarily inconsistent data, it might be a better idea to deal with it
in a temporary table.


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