[sqlite] how to pass -Dxxx compile option

[Xingwei Lin]

Hi,

How can I pass -Dxxx compile option when I build sqlite? Such as, -
DSQLITE_ENABLE_INTERNAL_FUNCTIONS.

-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Magellan 2.0 Vulnerabilities

[Simon Slavin]

On 9 Jan 2020, at 12:18am, Ware, Ryan R  wrote:

> I see absolutely nothing on sqlite.org or in the mail list archive 
> specifically about these issues

If someone reports a vulnerability here, it gets acknowledged here.  But I 
don't think Tencent posts here.

On 8 Jan 2020, at 10:27pm, Ware, Ryan R  wrote:

> We've been following the Magellan 2.0 
> (https://blade.tencent.com/magellan2/index_en.html) issues found by Tencent.

From the page at that URL:

" If you are using a software that is using SQLite as component (without the 
latest patch, which is 13 Dec 2019), and it supports external SQL queries. Or, 
you are using Chrome that is prior to 79.0.3945.79 with WebSQL enabled, you may 
be affected. "

In other words, the problem reported was patched in SQLite on 2019/12/13, and 
patched in version 79.0.3945.79 of Chrome.

> Does anyone here know if someone is working on updating the CPE info in these 
> 5 CVEs?

You would need to ask someone who works on the CPE database.  That's not us.  
However, from



"Known Affected Software Configurations: Up to (excluding)
79.0.3945.79"

In other words, the problem was fixed in Chrome 79.0.3945.79. That information 
was placed on the page on or before 2019/12/16.  I'm not sure what more you 
expect them to do.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Magellan 2.0 Vulnerabilities

[Ware, Ryan R]

On Wednesday, January 8, 2020 at 3:49:37 PM Richard Hipp said:
> On 1/8/20, Ware, Ryan R  wrote:
> >
> > We've been following the Magellan 2.0
> > (https://blade.tencent.com/magellan2/index_en.html) issues found by Tencent.
> >
> 
> Why, oh why, are you doing this?

Hey Richard.  Thanks for responding.  I'm doing this because while the CVEs 
clearly call out SQLite as the component that needed the fix, I haven't seen 
any statement from the SQLite community on the general applicability of the 
vulnerability.  I can only find Tencent's statement and no feedback from others 
such as yourself who actually work on SQLite.

> If you are a typical user of SQLite, then there are no vulnerabilities
> in SQLite that you need to concern yourself with.

Understood.  Please understand that Intel likely utilizes SQLite in some 
non-typical ways and so we need to have a broad understanding of the issues.

> Now, if you have some application that allows anonymous rogue agents
> on the internet to run arbitrary, unfiltered SQL statements using
> SQLite, and if you enable the legacy "FTS3" extension, then the
> so-called "Magellan 2.0" issues might be of concern to you.  But we
> only know of a single application that fits this description - WebKit.
> - and that application was patched within hours of the hack becoming
> known, which was many months ago.

And the response is great and needs to be celebrated.  I'm simply trying to 
understand since I haven't seen a clear statement in the non-WebKit case of if 
the vulnerabilities are applicable.  Your statement above helps.

> Tencent has a amazing marketing organization that is remarkably
> effective at promoting and amplifying every little trifling bug that
> their hackers find and make it sound like it will bring an end to
> civilization.  I suggest that you not be drawn into the hype.

I understand your concerns here.  I definitively don't want to be drawn into 
the hype which is why I'm coming to the community to find the right 
information.  Lacking a statement from the community on it (and I see 
absolutely nothing on sqlite.org or in the mail list archive specifically about 
these issues), it leaves others outside the community with an inability to draw 
the right conclusions.

> If Intel has some super-sensitive or especially vulnerable application
> using SQLite that we don't know about, then you can take out a
> cost-efficient consulting contract with us and we will work closely
> and confidentially with you to secure your application against past
> and future hacks and ensure that you stay up-to-date with all the
> latest patches.  Otherwise, please just ignore Tencent.  Excessive
> focus on Tencent and their vulnerability marketing organization will
> merely distract you from defending against actual threats.

I am very aware of the motivations of the security researchers.  It's a world I 
live in daily.  In an absence of conflicting information and the 
legitimatization of the issues via inclusion in NVD, there should be little 
surprise at any conclusion made by people external to the SQLite community.  
Given 4 of the 5 CVEs in question call out SQLite in the first sentence of the 
description, it might behoove the community to call out their position clearly.

Ryan

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

Re: [sqlite] Magellan 2.0 Vulnerabilities

[Richard Hipp]

On 1/8/20, Ware, Ryan R  wrote:
>
> We've been following the Magellan 2.0
> (https://blade.tencent.com/magellan2/index_en.html) issues found by Tencent.
>

Why, oh why, are you doing this?

If you are a typical user of SQLite, then there are no vulnerabilities
in SQLite that you need to concern yourself with.

Now, if you have some application that allows anonymous rogue agents
on the internet to run arbitrary, unfiltered SQL statements using
SQLite, and if you enable the legacy "FTS3" extension, then the
so-called "Magellan 2.0" issues might be of concern to you.  But we
only know of a single application that fits this description - WebKit.
- and that application was patched within hours of the hack becoming
known, which was many months ago.

Tencent has a amazing marketing organization that is remarkably
effective at promoting and amplifying every little trifling bug that
their hackers find and make it sound like it will bring an end to
civilization.  I suggest that you not be drawn into the hype.

If Intel has some super-sensitive or especially vulnerable application
using SQLite that we don't know about, then you can take out a
cost-efficient consulting contract with us and we will work closely
and confidentially with you to secure your application against past
and future hacks and ensure that you stay up-to-date with all the
latest patches.  Otherwise, please just ignore Tencent.  Excessive
focus on Tencent and their vulnerability marketing organization will
merely distract you from defending against actual threats.

-- 
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] Magellan 2.0 Vulnerabilities

[Ware, Ryan R]

Hello folks,

First, I want to thank everyone for the great work you do on sqlite.  I’m sure 
it’s no surprise, but sqlite is used heavily at Intel.

We've been following the Magellan 2.0 
(https://blade.tencent.com/magellan2/index_en.html) issues found by Tencent.  
One of the things I've found is that the five CVEs in question only have CPE 
(https://cpe.mitre.org) information for Chrome.  Because of that, there's no 
automated way to query the National Vulnerability Database for sqlite issues 
and have these 5 CVEs come up.  Does anyone here know if someone is working on 
updating the CPE info in these 5 CVEs?

Thanks,

Ryan

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

Re: [sqlite] TO

[Simon Slavin]

On 8 Jan 2020, at 4:13pm, R Smith  wrote:

> Anyone have an idea where the word TO is used in SQL in SQLite?

I seem to remember that the language used by the SQLite parser works from a 
table of data.  Would questions like this be easy to answer using that table ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TO

[R Smith]

On 2020/01/08 6:19 PM, Richard Hipp wrote:

On 1/8/20, R Smith  wrote:

Anyone have an idea where the word TO is used in SQL in SQLite?


alter table t1 rename TO t2;
rollback TO savepoint1;


So obvious...  My brain must be needing a break.


Thank you Richard and Tim!


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

Re: [sqlite] TO

[Tim Streater]

On 08 Jan 2020, at 16:13, R Smith  wrote:

> Hopefully the last of the silly questions...
>
> The word "TO" is given as an SQLite Keyword, but I cannot find any 
> reference to it being used anywhere in the SQL used by SQLite.
>
> The search doesn't help (because the word TO is everywhere in text), so 
> manually looking through CREATE TABLE / TRIGGER / etc. came up with nothing.
>
> Anyone have an idea where the word TO is used in SQL in SQLite?

ALTER TABLE?

See: https://www.sqlite.org/lang_altertable.html


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

Re: [sqlite] TO

[Richard Hipp]

On 1/8/20, R Smith  wrote:
>
> Anyone have an idea where the word TO is used in SQL in SQLite?
>

alter table t1 rename TO t2;
rollback TO savepoint1;

-- 
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] TO

[R Smith]

Hopefully the last of the silly questions...

The word "TO" is given as an SQLite Keyword, but I cannot find any 
reference to it being used anywhere in the SQL used by SQLite.


The search doesn't help (because the word TO is everywhere in text), so 
manually looking through CREATE TABLE / TRIGGER / etc. came up with nothing.


Anyone have an idea where the word TO is used in SQL in SQLite?


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

Re: [sqlite] Implementing xLock/xUnlock VFS methods...

[Dan Kennedy]

On 8/1/63 22:41, J Decker wrote:

On Wed, Jan 8, 2020 at 7:10 AM Dan Kennedy  wrote:


On 8/1/63 20:29, J Decker wrote:

The documentation isn't very clear on what the intent of an xUnlock(
SQLITE_LOCK_NONE ) is intended to do.  Is it unlock everything? Is it the
same as remove a shared lock?

That's right. xUnlock(fd, SQLITE_LOCK_NONE) should completely unlock the
file. xUnlock(fd, SQLITE_LOCK_SHARED) should fall back to a SHARED lock
from RESERVED/PENDING/EXCLUSIVE.

Thank you... the second thing there confuses me...

xLock(SHARED)  xLock(SHARED) xLock(PENDING)
xUnlock( SHARED) - clear pending, and add a shared for total of 3?  or is
xUnlock always remove (this file handle's) locks?


You don't have to count SHARED locks. A connection is either in SHARED 
lock state or it is not. For your sequence:


  xLock(SHARED); // Take SHARED lock
  xLock(SHARED); // No-op, we already have SHARED
  xLock(PENDING); // Upgrade to PENDING lock
  xUnlock(SHARED); // Drop back down to SHARED lock

then

  xUnlock(NONE); // Completely unlock file

If you can't obtain a requested lock, return SQLITE_BUSY. SQLite may 
retry or may abandon the operation, depending on the configured 
busy-handler or busy-timeout.


Dan.







I haven't finished implementing the first part to get a 'real' sequence...
Also; I suppose I should return SQLITE_BUSY instead of waiting on the lock
myself?  expected return values aren't covered well either.

Dan.




The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by
xUnlock(SQLITE_LOCK_NONE)...

sqlite.h.in
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L627
where the constants are defined... says 'one of these are used...'

and describing locking levels...
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L708-L720


https://www.sqlite.org/c3ref/io_methods.html
xLock() increases the lock. xUnlock() decreases the lock. The
xCheckReservedLock() method checks whether any database connection,

either

in this process or in some other process, is holding a RESERVED, PENDING,
or EXCLUSIVE lock on the file. It returns true if such a lock exists and
false otherwise.

https://www.sqlite.org/lockingv3.html
has description of what locking states are intended to be... there's
'UNLOCKED' but not LOCK_NONE

I did find another page that had lock state transitions, and information
about when the busy callback would be called... but that also didn't say
anything about the transition to unlock states...

There was a note around the .C code in the pager unlocking using

LOCK_NONE,

but I cna't find that, it just says, see note above... and that didn't
really say what the intent was.

I searched the nabble mailing list archive for 'SQLITE_LOCK_NONE' and

found

0 hits.
___
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-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] Implementing xLock/xUnlock VFS methods...

[J Decker]

On Wed, Jan 8, 2020 at 7:10 AM Dan Kennedy  wrote:

>
> On 8/1/63 20:29, J Decker wrote:
> > The documentation isn't very clear on what the intent of an xUnlock(
> > SQLITE_LOCK_NONE ) is intended to do.  Is it unlock everything? Is it the
> > same as remove a shared lock?
>
> That's right. xUnlock(fd, SQLITE_LOCK_NONE) should completely unlock the
> file. xUnlock(fd, SQLITE_LOCK_SHARED) should fall back to a SHARED lock
> from RESERVED/PENDING/EXCLUSIVE.
>
> Thank you... the second thing there confuses me...
xLock(SHARED)  xLock(SHARED) xLock(PENDING)
xUnlock( SHARED) - clear pending, and add a shared for total of 3?  or is
xUnlock always remove (this file handle's) locks?
I haven't finished implementing the first part to get a 'real' sequence...
Also; I suppose I should return SQLITE_BUSY instead of waiting on the lock
myself?  expected return values aren't covered well either.

Dan.
>
>
>
> >
> > The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by
> > xUnlock(SQLITE_LOCK_NONE)...
> >
> > sqlite.h.in
> > https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L627
> > where the constants are defined... says 'one of these are used...'
> >
> > and describing locking levels...
> > https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L708-L720
> >
> >
> > https://www.sqlite.org/c3ref/io_methods.html
> > xLock() increases the lock. xUnlock() decreases the lock. The
> > xCheckReservedLock() method checks whether any database connection,
> either
> > in this process or in some other process, is holding a RESERVED, PENDING,
> > or EXCLUSIVE lock on the file. It returns true if such a lock exists and
> > false otherwise.
> >
> > https://www.sqlite.org/lockingv3.html
> > has description of what locking states are intended to be... there's
> > 'UNLOCKED' but not LOCK_NONE
> >
> > I did find another page that had lock state transitions, and information
> > about when the busy callback would be called... but that also didn't say
> > anything about the transition to unlock states...
> >
> > There was a note around the .C code in the pager unlocking using
> LOCK_NONE,
> > but I cna't find that, it just says, see note above... and that didn't
> > really say what the intent was.
> >
> > I searched the nabble mailing list archive for 'SQLITE_LOCK_NONE' and
> found
> > 0 hits.
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing xLock/xUnlock VFS methods...

[Dan Kennedy]

On 8/1/63 20:29, J Decker wrote:

The documentation isn't very clear on what the intent of an xUnlock(
SQLITE_LOCK_NONE ) is intended to do.  Is it unlock everything? Is it the
same as remove a shared lock?


That's right. xUnlock(fd, SQLITE_LOCK_NONE) should completely unlock the 
file. xUnlock(fd, SQLITE_LOCK_SHARED) should fall back to a SHARED lock 
from RESERVED/PENDING/EXCLUSIVE.


Dan.





The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by
xUnlock(SQLITE_LOCK_NONE)...

sqlite.h.in
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L627
where the constants are defined... says 'one of these are used...'

and describing locking levels...
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L708-L720


https://www.sqlite.org/c3ref/io_methods.html
xLock() increases the lock. xUnlock() decreases the lock. The
xCheckReservedLock() method checks whether any database connection, either
in this process or in some other process, is holding a RESERVED, PENDING,
or EXCLUSIVE lock on the file. It returns true if such a lock exists and
false otherwise.

https://www.sqlite.org/lockingv3.html
has description of what locking states are intended to be... there's
'UNLOCKED' but not LOCK_NONE

I did find another page that had lock state transitions, and information
about when the busy callback would be called... but that also didn't say
anything about the transition to unlock states...

There was a note around the .C code in the pager unlocking using LOCK_NONE,
but I cna't find that, it just says, see note above... and that didn't
really say what the intent was.

I searched the nabble mailing list archive for 'SQLITE_LOCK_NONE' and found
0 hits.
___
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] Cascaded delete unexpectedly triggered by upsert

[Julian Dohmen]

This does look very useful - I’ve often thought of the INSERT/EPLACE style but 
the implicit DELETE [when REPLACE occurs] was a barrier - it causes [as I 
understand it] CASCADE DELETE to be in effect.
It seems that this UPSERT style does not cause that effect.

> On Jan 8, 2020, at 5:22 AM, Richard Hipp  wrote:
> 
> On 1/7/20, Michael Kappert  wrote:
>> 
>> If I understand correctly, the upsert should behave like UPDATE in the
>> examples above, but it behaves like a DELETE followed by INSERT instead?
>> 
> 
> REPLACE and UPSERT are different things.  See
> https://www.sqlite.org/lang_conflict.html for information about
> REPLACE and https://www.sqlite.org/lang_UPSERT.html for information
> about UPSERT.
> 
> REPLACE works by first DELETE-ing any rows that would cause a conflict
> and then doing the INSERT.
> 
> UPDATE works by converting the INSERT into an UPDATE.
> -- 
> 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] shell.c: exec_prepared_stmt no return value

[Hannes Mühleisen]

Thanks for your quick response. Turns out while trying to provoke the issue in 
a test case I realised a misunderstanding the SQLite API on my part: errors 
during execution are bubbled up to the shell by sqlite3_finalize. 

My apologies,

Hannes


> On 8 Jan 2020, at 13:30, Richard Hipp  wrote:
> 
> Thank you for the bug report.
> 
> However, you have provided a fix without showing us the malfunction.
> You suggest a change without demonstrating what behavior the change is
> designed to fix.  The problem you are trying to fix is not obvious,
> because when I run test queries that contain errors, I do get an error
> message back, even without your fix.  So I cannot figure out what
> problem your fix is intending to address.
> 
> Can you please provide an example input that gives incorrect results
> before your proposed fix, and that gives the correct result
> afterwards?
> 
> On 1/8/20, Hannes Mühleisen  wrote:
>> Hello SQLite list,
>> 
>> we have noticed that the sqlite shell is unable to report errors that happen
>> within exec_prepared_stmt, because that function has no return value and is
>> thus unable to bubble issues up. For example, if sqlite3_step should fail
>> for some reason, this should be shown to the user, for example in the call
>> to exec_prepared_stmt from shell_exec.
>> 
>> We propose to add a return code to exec_prepared_stmt like so:
>> 
>> static int exec_prepared_stmt(
>> ShellState *pArg,/* Pointer to ShellState
>> */
>> sqlite3_stmt *pStmt  /* Statment to run */
>> ){
>> int rc;
>>  /* ... */
>> return rc;
>> }
>> 
>> then, in shell_exec, we could say something like
>> 
>> rc = exec_prepared_stmt(pArg, pStmt);
>>  if (rc != SQLITE_OK) {
>>  if (pzErrMsg) {
>>  *pzErrMsg = save_err_msg(db);
>>  }
>>  }
>> 
>> This way, if an error occurs during execution, this will be displayed to the
>> user.
>> 
>> Best from Amsterdam,
>> 
>> Hannes
>> 
>> ___
>> 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] Implementing xLock/xUnlock VFS methods...

[J Decker]

The documentation isn't very clear on what the intent of an xUnlock(
SQLITE_LOCK_NONE ) is intended to do.  Is it unlock everything? Is it the
same as remove a shared lock?

The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by
xUnlock(SQLITE_LOCK_NONE)...

sqlite.h.in
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L627
where the constants are defined... says 'one of these are used...'

and describing locking levels...
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L708-L720


https://www.sqlite.org/c3ref/io_methods.html
xLock() increases the lock. xUnlock() decreases the lock. The
xCheckReservedLock() method checks whether any database connection, either
in this process or in some other process, is holding a RESERVED, PENDING,
or EXCLUSIVE lock on the file. It returns true if such a lock exists and
false otherwise.

https://www.sqlite.org/lockingv3.html
has description of what locking states are intended to be... there's
'UNLOCKED' but not LOCK_NONE

I did find another page that had lock state transitions, and information
about when the busy callback would be called... but that also didn't say
anything about the transition to unlock states...

There was a note around the .C code in the pager unlocking using LOCK_NONE,
but I cna't find that, it just says, see note above... and that didn't
really say what the intent was.

I searched the nabble mailing list archive for 'SQLITE_LOCK_NONE' and found
0 hits.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOTNULL

[Richard Hipp]

On 1/8/20, R Smith  wrote:
> I find the keyword NOTNULL listed among known SQLite keywords -
> no. 88 on this page: https://sqlite.org/lang_keywords.html
>
> But cannot find a single mention of it or place to use it in SQLite, nor
> get any hit on the sqlite.org search except in reference to the above list.
>
> 1. What is it for?
>
> 2. Does it have/need any documentation?

NOTNULL is a postgresql-ism.  It is equivalent to IS NOT NULL.

-- 
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] shell.c: exec_prepared_stmt no return value

[Richard Hipp]

Thank you for the bug report.

However, you have provided a fix without showing us the malfunction.
You suggest a change without demonstrating what behavior the change is
designed to fix.  The problem you are trying to fix is not obvious,
because when I run test queries that contain errors, I do get an error
message back, even without your fix.  So I cannot figure out what
problem your fix is intending to address.

Can you please provide an example input that gives incorrect results
before your proposed fix, and that gives the correct result
afterwards?

On 1/8/20, Hannes Mühleisen  wrote:
> Hello SQLite list,
>
> we have noticed that the sqlite shell is unable to report errors that happen
> within exec_prepared_stmt, because that function has no return value and is
> thus unable to bubble issues up. For example, if sqlite3_step should fail
> for some reason, this should be shown to the user, for example in the call
> to exec_prepared_stmt from shell_exec.
>
> We propose to add a return code to exec_prepared_stmt like so:
>
> static int exec_prepared_stmt(
>  ShellState *pArg,/* Pointer to ShellState
> */
>  sqlite3_stmt *pStmt  /* Statment to run */
> ){
>  int rc;
>   /* ... */
> return rc;
> }
>
> then, in shell_exec, we could say something like
>
>  rc = exec_prepared_stmt(pArg, pStmt);
>   if (rc != SQLITE_OK) {
>   if (pzErrMsg) {
>   *pzErrMsg = save_err_msg(db);
>   }
>   }
>
> This way, if an error occurs during execution, this will be displayed to the
> user.
>
> Best from Amsterdam,
>
> Hannes
>
> ___
> 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] Cascaded delete unexpectedly triggered by upsert

[Richard Hipp]

On 1/7/20, Michael Kappert  wrote:
>
> If I understand correctly, the upsert should behave like UPDATE in the
> examples above, but it behaves like a DELETE followed by INSERT instead?
>

REPLACE and UPSERT are different things.  See
https://www.sqlite.org/lang_conflict.html for information about
REPLACE and https://www.sqlite.org/lang_UPSERT.html for information
about UPSERT.

REPLACE works by first DELETE-ing any rows that would cause a conflict
and then doing the INSERT.

UPDATE works by converting the INSERT into an UPDATE.
-- 
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] NOTNULL

[R Smith]

On 2020/01/08 2:03 PM, Keith Medcalf wrote:



1. What is it for?

It is a common misspelling of "IS NOT NULL" and means the same thing.

"ISNULL" is also a reserved word as it is a common misspelling of "IS NULL" and 
means the same thing.

You will note that ISNOTNULL is not a reserved word because apparently the 
common misspellings only omit one or the other of the spaces, butnotboth at the 
sametime.

Also, ISNOT is not a reserved word.  Apparently it is more common for the 
misspelling IS NOTNULL to be made than ISNOT NULL, even though ISNOT is the 
more logical inverse operator to IS.  (Perhaps this is because ISNOT could be 
spelled as iSnot leading to lawsuits from Apple ... :) )



LOL - I'm quoting you in the docs. :)


Thanks for clarifying,
Cheers!

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

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[R Smith]

On 2020/01/08 1:10 PM, Simon Slavin wrote:

I advise you avoid the idea of UPSERT when dealing with SQLite (or better 
still, all SQL).  It is rarely implemented as a single operation, and you can 
get unexpected results with triggers and foreign key children.


I advise you to avoid the idea of driving a Bus to work (or better 
still, at all).  It is rarely easy to navigate narrow lanes and you can 
get unexpected results when trying to park it at the mall.


Rather use a bicycle.

If however your objective is to take 100 other people to work, you'll 
find the bus is a godsend - much like UPSERT.




Simon is quite correct, using a function without proper understanding, 
results can be somewhat unexpected.  Even with proper understanding of 
the SQL, it can still be unexpected, considering that different engines 
implement it differently. A shining example of this is "REPLACE" - 
almost everyone who posted about it here misunderstood what it really 
does[1], often causing the exact problems Simon warns about.


That said, I can't agree with advising against UPSERT.

There is no unknown magic going on with UPSERT (or any other fully 
supported SQL feature in SQLite), if you do need it, do read up properly 
on it (specific to the platform you will use it on) and it will work as 
advertised. (And in the rare case it doesn't, a bug report here usually 
remedies that in about a day).



Cheers,
Ryan

[1] - They often use REPLACE having come from a MySQL background, and 
then they often learn that even in MySQL it doesn't work as they had 
imagined.



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

Re: [sqlite] NOTNULL

[Keith Medcalf]

On Wednesday, 8 January, 2020 04:16, R Smith  wrote:

>I find the keyword NOTNULL listed among known SQLite keywords -
>no. 88 on this page: https://sqlite.org/lang_keywords.html

>But cannot find a single mention of it or place to use it in SQLite, nor
>get any hit on the sqlite.org search except in reference to the above
>list.

>1. What is it for?

It is a common misspelling of "IS NOT NULL" and means the same thing.

"ISNULL" is also a reserved word as it is a common misspelling of "IS NULL" and 
means the same thing.

You will note that ISNOTNULL is not a reserved word because apparently the 
common misspellings only omit one or the other of the spaces, butnotboth at the 
sametime.

Also, ISNOT is not a reserved word.  Apparently it is more common for the 
misspelling IS NOTNULL to be made than ISNOT NULL, even though ISNOT is the 
more logical inverse operator to IS.  (Perhaps this is because ISNOT could be 
spelled as iSnot leading to lawsuits from Apple ... :) )

>2. Does it have/need any documentation?

No.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] NOTNULL

[R Smith]

On 2020/01/08 1:23 PM, Simon Slavin wrote:


You can use NOTNULL as a condition.  It's the opposite of ISNULL.  You see it 
usually as a constraint, to ensure that a field has a value.


Thank you Simon - Do you perhaps have an example of this working in 
SQLite? I am not finding a way to make it work.




However, NOT NULL with a space works too, and that version seems to be more 
popular these days.


Yes, this I'm using in SQLite often.  I'm busy updating the 
documentation functions for the next release of sqlitespeed, needing to 
connect every keyword/function to its proper use in SQLite - which is 
why I've been posting these documentation-related questions.



Thanks for helping,
Ryan

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

[sqlite] shell.c: exec_prepared_stmt no return value

[Hannes Mühleisen]

Hello SQLite list,

we have noticed that the sqlite shell is unable to report errors that happen 
within exec_prepared_stmt, because that function has no return value and is 
thus unable to bubble issues up. For example, if sqlite3_step should fail for 
some reason, this should be shown to the user, for example in the call to 
exec_prepared_stmt from shell_exec. 

We propose to add a return code to exec_prepared_stmt like so:

static int exec_prepared_stmt(
 ShellState *pArg,/* Pointer to ShellState */
 sqlite3_stmt *pStmt  /* Statment to run */
){
 int rc;
/* ... */
return rc;
}

then, in shell_exec, we could say something like

 rc = exec_prepared_stmt(pArg, pStmt);
if (rc != SQLITE_OK) {
if (pzErrMsg) {
*pzErrMsg = save_err_msg(db);
}
}

This way, if an error occurs during execution, this will be displayed to the 
user.

Best from Amsterdam,

Hannes

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

Re: [sqlite] NOTNULL

[Simon Slavin]

On 8 Jan 2020, at 11:15am, R Smith  wrote:

> I find the keyword NOTNULL listed among known SQLite keywords -
> no. 88 on this page: https://sqlite.org/lang_keywords.html
> 
> But cannot find a single mention of it or place to use it in SQLite, nor get 
> any hit on the sqlite.org search except in reference to the above list.
> 
> 1. What is it for?
> 
> 2. Does it have/need any documentation?

You can use NOTNULL as a condition.  It's the opposite of ISNULL.  You see it 
usually as a constraint, to ensure that a field has a value.

However, NOT NULL with a space works too, and that version seems to be more 
popular these days.

CREATE TABLE company (
code TEXT NOT NULL,
name TEXT NOT NULL)

You're right.  I don't think I see NOTNULL mentioned anywhere else in the 
SQLite documentation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOTNULL

[R Smith]

I find the keyword NOTNULL listed among known SQLite keywords -
no. 88 on this page: https://sqlite.org/lang_keywords.html

But cannot find a single mention of it or place to use it in SQLite, nor 
get any hit on the sqlite.org search except in reference to the above list.


1. What is it for?

2. Does it have/need any documentation?


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

Re: [sqlite] Best way to store key,value pairs

[Simon Slavin]

On 8 Jan 2020, at 3:11am, Jens Alfke  wrote:

> Consider encoding the headers as JSON and storing them in a single column. 
> SQLite has a JSON extension that makes it easy to access values from JSON 
> data in a query. You can even index them.
> 
> Simon’s suggestion (a row per header) is correct in theory, but the large 
> numbers of headers you’ll be storing in an NNTP database will make that 
> approach pretty expensive, I think. 

I believe Jens' point is valid, as long as you don't have to search/scan 
headers.  I'm not used to being able to access JSON inside SQLite yet.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[Simon Slavin]

I advise you avoid the idea of UPSERT when dealing with SQLite (or better 
still, all SQL).  It is rarely implemented as a single operation, and you can 
get unexpected results with triggers and foreign key children.

Think of your operation as a combinations of INSERT, DELETE and UPDATE and you 
will be able to correctly predict its effects.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation issues for collating sequences

[Rob Golsteijn]

I encountered a documentation issue at the page that describes how to define 
new collating sequences ( https://www.sqlite.org/c3ref/create_collation.html )

For the 3 variants of sqlite3_create_collation the 5th argument is a function 
called "xCompare", but the text refers to "xCallback" instead of "xCompare":
"The fifth argument, xCallback, is a pointer to the collating function. 
[...]"
Please correct this.

Furthermore the text states:
"The collating function callback is invoked with a copy of the pArg 
application data pointer and with two strings in the encoding specified by the 
eTextRep argument"
But function xCompare is defined as "int(*xCompare)(void*,int,const 
void*,int,const void*)", so having 2 additional integer parameters. These will 
contain the lengths of the two strings but this is not documented. At first I 
guessed they would contain one of the type constants (see 
https://www.sqlite.org/c3ref/c_blob.html) to allow collation implementations 
like the built-in one where integer < text < blob.
But now I see that lengths are necessary, especially with blobs that can 
contain embedded NUL characters. I guess this also means that I cannot assume 
that the strings are nul-terminated.
Please document that the integers arguments contain the lengths of the strings.

Btw. I expected "xCompare" to have signature int(*xCompare)(void*, const 
sqlite3_value * const, const sqlite3_value * const). Can anyone explain why the 
values are passed as strings. E.g. were collating sequences only foreseen for 
text values?

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