Re: [sqlite] Error/Result code documentation

[Dominique Devienne]

On Wed, Feb 26, 2020 at 11:09 AM Dominique Devienne  wrote:
> Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
> are the $n*Code variables supposed to be expanded with a numeric
> value, and there's a little issue in the doc generation? Thanks, --DD

Thanks for the fix Richard. --DD

To anyone else, here's the fix:
https://www.sqlite.org/docsrc/info/d35032eb7ff2018b
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to prevent sqlite_reset reporting an already known error

[Simon Slavin]

On 26 Feb 2020, at 11:58pm, mailing lists  wrote:

> The issue is that sqlite_reset() reports the same error that already 
> sqlite3_step() reported. How can I prevent that sqlite_reset() reports the 
> same error.

You can't.  It's working as designed.

One would normally test the result that sqlite_reset() returns, making sure it 
is either SQLITE_OK or the error you got from sqlite3_step(). However I see 
you're using a wrapper that makes it difficult.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to prevent sqlite_reset reporting an already known error

[mailing lists]

I am executing a prepared statement S with a couple of different bindings. The 
execution sequence is similar to this

while (moreBindings)
{
 bind_parameters_to_prepared_statement;
sqlite3_step();

if (error)
{
…
}
sqlite_reset();
if (error)
{
}

}

The issue is that sqlite_reset() reports the same error that already 
sqlite3_step() reported. How can I prevent that sqlite_reset() reports the same 
error. I also tried to clear the bindings before the reset statement but this 
did not help either.

PS: Actually, I am using not directly the SQLite statement but a wrapper around 
it that makes it a bit more complicated to filter out the multiple reported 
same error.

Regards,
Hardy

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

[sqlite] VFS xFullPathname cannot access URI parameters

[Chris Warner]

In 3.31.1, attempting to access URI Parameters via the sqlite3_uri_* functions 
inside a VFS xFullPathname function returns junk.  This was not an issue in 
3.30.1 or 3.31.0.

The internals of those functions use the new databaseName() function, which 
attempts to find the database name from the path passed info sqlite3_open_v2(). 
 According to the function documentation:

> This only works if the filename passed in was obtained from the Pager.

However, the xFullPathname function is called before the Pager has opened the 
file, the parameter passed to sqlite3_uri_* functions aren't prefixed by the 4 
null bytes, thus the databaseName() call steps backward too far.

Minimal reproducer:

--

#include 
#include 

int test_full_path_name(sqlite3_vfs* vfs, const char* zName, int nOut, char* 
zOut)
{
  auto test_param = sqlite3_uri_parameter(zName, "test_param");
  if (!test_param)
  {
    return SQLITE_ERROR;
  }

  return SQLITE_OK;
}

int main()
{
  static sqlite3_vfs uzip_vfs = {
      3,
      0,
      sqlite3_vfs_find(nullptr)->mxPathname,
      nullptr,
      "test-vfs",
      nullptr,
      sqlite3_vfs_find(nullptr)->xOpen,
      sqlite3_vfs_find(nullptr)->xDelete,
      sqlite3_vfs_find(nullptr)->xAccess,
      test_full_path_name,
      sqlite3_vfs_find(nullptr)->xDlOpen,
      sqlite3_vfs_find(nullptr)->xDlError,
      sqlite3_vfs_find(nullptr)->xDlSym,
      sqlite3_vfs_find(nullptr)->xDlClose,
      sqlite3_vfs_find(nullptr)->xRandomness,
      sqlite3_vfs_find(nullptr)->xSleep,
      sqlite3_vfs_find(nullptr)->xCurrentTime,
      sqlite3_vfs_find(nullptr)->xGetLastError,
      sqlite3_vfs_find(nullptr)->xCurrentTimeInt64,
      sqlite3_vfs_find(nullptr)->xSetSystemCall,
      sqlite3_vfs_find(nullptr)->xGetSystemCall,
      sqlite3_vfs_find(nullptr)->xNextSystemCall};

  int rc = sqlite3_vfs_register(_vfs, 0);
  if (rc != SQLITE_OK)
  {
    std::cout << "ERROR (" << rc << "): vfs registration failed." << std::endl;
  }

  std::string open_path{"file:E:/Temp/test.sqlite?test_param=hello"};

  sqlite3* db{nullptr};
  rc = sqlite3_open_v2(open_path.c_str(), , SQLITE_OPEN_READONLY | 
SQLITE_OPEN_URI, "test-vfs");
  if (rc != SQLITE_OK)
  {
    std::cout << "ERROR (" << rc << "): opening the database failed." << 
std::endl;
  }

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

[sqlite] Issues with sqlite3session_attach(conn, NULL) w/ duplicate table in temp

[Adam Levy]

Hi all

When I have a database connection with a temp table of the same name
as a table in main, I am getting what I feel is unexpected behavior
from the session extension.

Most succinctly, I start a session on a connection on the main db with
a table with an INTEGER PRIMARY KEY, attach it to all tables by
passing NULL, make a change such as a single INSERT in aforementioned
table, and then record a changeset. Then I invert this changeset, and
apply that inverse to the same connection, to reverse the changes just
made.

Normally this works flawlessly. However, if a table by the same name
exists in the temp database on that connection, I run into odd
behavrior.

If the temp table has different rows than the main table, a very odd
changeset conflict occurs. The conflict type is SQLITE_CHANGESET_DATA
but when I examine the changeset iter the values for the columns, they
match the conflicting values. The inverse of an INSERT is a DELETE so
this conflict type indicates that one of the non-primary key column
values doesn't match, but in every examination of the database and the
changeset I have performed they do match. The primary key exists and
matches as well.

If the temp table has the same rows at the main table (what the
changeset expects), the changeset_apply does not raise a conflict, but
it takes no affect on the main table.

This can all be avoided by being explicit about the database when
calling sqlite3session_attach.
For example passing "*" or "main.*" or "main." avoids the
issue. But it occurs if NULL is passed. This is not obvious and
arguably not expected given that the documentation about the Session
extension makes it seem like it should be already scoped to a single
database on a connection. After all, sqlite3session_create accepts the
database as an argument. Also, sqlite3_changeset_apply appears to be
specific to the "main" database. So it shouldn't care about whats in
the temp database at all.

From all of the docs I have read on the Session extension this seems
like misbehavior at the level of sqlite3session_attach being passed
NULL for the zTab argument. At the minimum perhaps a note in the
documentation of sqlite3session_attach would be appropriate.

Below is a very simple example that can reproduce this behavior. It
has a number of lines commented out that describe how the bug can be
avoided to caused.

Its written in Golang, but the library it uses is just a very thin
wrapper around the C interface. It is using the latest 3.31.1
amalgamation. I help maintain the SQLite library that it uses and can
confirm that everything being called here is a very thin wrapper
around the C interface. I could write a C example but it would take me
more work.

The library it calls to print the SQL of the changeset is a utility I
wrote that is just using a thin wrapper around the changeset_iter to
parse the changeset into human readable SQL. It of course is not aware
of database names so it doesn't print "main" but this SQL is just for
displaying to the user. The values returned by
sqlite3changeset_conflict are formatted into the comments of that
printed SQL.

The code can be more easily downloaded here:
https://github.com/AdamSLevy/sqlite-session-bug

// main.go

package main

import (
"bytes"
"fmt"
"io"
"log"

"crawshaw.io/sqlite"
"crawshaw.io/sqlite/sqlitex"
"github.com/AdamSLevy/sqlitechangeset"
)

func run() error {
conn, err := sqlite.OpenConn(":memory:", 0)
if err != nil {
return fmt.Errorf("sqlite open: %w", err)
}
defer conn.Close()

fmt.Println("Creating tables...")
err = sqlitex.ExecScript(conn, `
CREATE TABLE main.t(id INTEGER PRIMARY KEY, a,b,c);
CREATE TABLE temp.t(id INTEGER PRIMARY KEY, a,b,c) --- remove this
line or rename the table to avoid issue;
`)
if err != nil {
return err
}
fmt.Println("Starting session on main...")
sess, err := conn.CreateSession("main")
if err != nil {
return err
}
defer sess.Delete()
// An empty string will pass NULL to sqlite3session_attach and allow
// the bug.
var attach string
// Any of these prevent the bug.
//attach = "*"
//attach = "main.*"
//attach = "main.t"
fmt.Printf("Attaching to %s ...\n", attach)
if err := sess.Attach(attach); err != nil {
return err
}

fmt.Println("Inserting into main.t ...")
commit := sqlitex.Save(conn)
err = sqlitex.ExecScript(conn, `
INSERT INTO main.t(a,b,c) VALUES (1,2,3);
INSERT INTO temp.t(a,b,c) VALUES (1,2,3) --- This line avoids the
conflict, but the changeset doesn't apply to main.t like expected;
`)
if err != nil {
return err
}
commit()

sql, err := sqlitechangeset.SessionToSQL(conn, sess)
if err != nil {
return err
}
fmt.Println("changeset:", sql)

chgset := bytes.NewBuffer(nil)
if err := sess.Changeset(chgset); err != nil {
return err
}
invrt := bytes.NewBuffer(nil)
invrtCp := 

Re: [sqlite] Trigger name missing

[Jean-Luc Hainaut]

On 26/02/2020 12:18, Richard Hipp wrote:

On 2/26/20, Jean-Luc Hainaut  wrote:

Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in
which the name is missing. When fired, this trigger doesn't crashes but
exhibits a strange behaviour. In particular, while expression
"new." in an "insert" trigger returns the correct value, the
equivalent expression "select  from T where Id = new.Id" always
returns null (column "Id" is the PK of table "T"). Similarly, "update T
set  =   where Id = new.Id" (silently) fails.


What is the text of your trigger?


This trigger belongs to a small experimental application I'm writting to 
study the extent to what application code (initially in Java, Python, 
etc.) can be integrated into SQL, notably through triggers. In short, 
can one convert a standard 3-tier business application into just a GUI + 
an active database, without the standard application program between them?
The following trigger controls the registration of a customer order 
[insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when 
the available quantity (Qavail) of the requested item is sufficient.

If the name 'CORD_INS1' is missing, this trigger (among others):
  - updates the ITEM table. [successfully]
  - completes the customer order (Price and State in CUSTORDER). [fails]
  - creates an invoice (in CUSTINVOICE) and prints it in a text file. 
[successfully]


After reading all your explanations and comments, my interpretation is 
as follows:
1. The SQLite syntax tells me that the "before/after/instead of" keyword 
can be missing, in which case (I guess) "before" is assumed.

2. So, my "name-less" trigger is valid and must be read:
   create trigger "after" before insert on CUSTORDER ...
3. In a "before" trigger, the current row cannot be updated, since it 
doesn't exist yet (though several RDBMS have a specific syntax for that).

4. This explains why SQLite legitimely ignores the second update.
Am I right?
If I am, this behaviour is "not a bug but a feature". It could be useful 
to precise these facts in the documentation.


Thanks to all

Jean-Luc Hainaut

create table CUSTOMER (CustID,Name,Address,City,Account,...);
create table ITEM 
(ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);

create table CUSTORDER (OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
create table CUSTINVOICE 
(InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);

create table SUPPLIER (SuppID,Name,City,...);
create table OFFER (SuppID,ItemID,Price,Delay,...);
create table SUPPORDER (OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);

create trigger CORD_INS1
after insert on CUSTORDER
for each row
when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
begin
   --
   -- Subtract Qty from Qavail:
   update ITEM
   set   Qavail = Qavail - new.Qty
   where ItemID = new.ItemID;
   --
   --...
   -- Set CUSTORDER.State to 'invoiced' or 'pending'
   update CUSTORDER
   set   Price = (select Price from ITEM where ItemID = new.ItemID),
 State = case when new.Qty <= (select QonHand from ITEM where 
ItemID = new.ItemID)

  then 'invoiced'
  else 'pending'
 end
   where OrdID = new.OrdID;
   --
   -- Create an invoice and print it:
   insert into CUSTINVOICE(...);
   --
end;

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

[sqlite] [feature request] Quirk-fixing compile-time options

[Joshua Wise]

Hi, I was very happy to see the addition of the SQLITE_DQS compile-time option, 
which fixes one of the legacy bugs/quirks of SQLite3.

I was wondering if additional compile-time options could be added to fix two 
other legacy quirks:
- Primary Keys containing NULLs 

- Aggregate queries containing non-aggregate result columns 


It would be great for SQLite3 users to opt out of these behaviors, as it would 
help prevent many difficult-to-find bugs.

Any feedback on this suggestion by a SQLite3 dev would be much appreciated!

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

Re: [sqlite] Fwd: inserting new data only

[Jose Isaias Cabrera]

James K. Lowden, on Wednesday, February 26, 2020 11:55 AM, wrote...
>
> On Tue, 25 Feb 2020 12:02:24 -0500
> Przemek Klosowski  wrote:
>
> > and I want to avoid storing repetitive data, so that the database
> > should contain
> > 10:32  12
> > 10:35  15
> > 10:39  13
> > 10:46  18
> > where only the earliest time with the unchanging value is stored.
>
> Be careful what you wish for.  Usually "avoid storing" is a proxy for
> some kind of intended use.  Unless it's infeasible, it's usually better
> to store everything, verbatim, as it arrives.  Then you can present it
> however you like, with nothing lost.

Fresh real-life-work experience...
I was tasked to take data from a system and report on it, so, as I looked
over the data I saw multiple entries, so I asked the client do you want to
know if a set of data is repeated? "NO," said the client, "I only care for
what new and fresh."  Are you sure, I can see that there are repeated
entries, and I can see different places where they are coming from, and
how often, and... "NO!,  I only care for what new and fresh."
When I was going to show the reporting tool, she said, "By the way, this
has how many times repeated records are coming in, and from where,
correct?" I knew this was going to happened, so I had left a way to
quickly make a few changes, re-imported the data, and was able to save
the world.  So, as James said, "I would keep everything and just show
what you need."  Maybe someday you want to know how many
repeated entries happen in an hour, day, etc.  Thanks.

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

Re: [sqlite] inserting new data only

[Simon Slavin]

On 26 Feb 2020, at 5:18pm, Przemek Klosowski 
 wrote:

> This 'store only changed values' is
> intended for situations like that.
> Another suitable candidate for that treatment might be a status, for
> instance 'on battery' value for a UPS monitoring system. I can't think of a 
> scenario where storing it every time would be better.

Suppose some horrible disaster happens to your chemical plant at 4pm.  Your log 
for a particular sensor shows an acceptable value, but the timestamp on that 
reading is 3:15pm.  But only changed readings are logged.

Perhaps a fault occurred in the sampling circuit at 3:16pm, so no further 
readings were taken, so an alarm was not raised.  Had every reading been logged 
you could have proved that another reading was taken at 3:58pm still showing 
the same acceptable value.

So it depends why you're doing what you're doing.  Different circumstances 
suggest different tactics.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: inserting new data only

[Przemek Klosowski]

On Wed, Feb 26, 2020 at 11:56 AM James K. Lowden
 wrote:
> > and I want to avoid storing repetitive data, so that the database
> > should contain
> > [...]
> > only the earliest time with the unchanging value is stored.
>
> Be careful what you wish for.  Usually "avoid storing" is a proxy for
> some kind of intended use.  Unless it's infeasible, it's usually better
> to store everything, verbatim, as it arrives.  Then you can present it
> however you like, with nothing lost.

A lot of process measurement instrumentation returns some nominal
values, for instance firmware version. Do we store it or not? Storing
it every second is silly, but if we drop it, we wouldn't be able to
for instance audit if the statistics changed in some subtle way
because of a firmware bug. This 'store only changed values' is
intended for situations like that.
Another suitable candidate for that treatment might be a status, for
instance 'on battery' value for a UPS monitoring system. I can't think
of a scenario where storing it every time would be better.
The only disadvantage I can think of would be incidental, like
monitoring the monitoring system itself: a long period of 'no data'
could be caused by a monitoring failure as well as by the value not
changing; but if that is a concern, I think it would be better to
store a single heartbeat rather than possibly multiple unchanging
values.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: inserting new data only

[James K. Lowden]

On Tue, 25 Feb 2020 12:02:24 -0500
Przemek Klosowski  wrote:

> and I want to avoid storing repetitive data, so that the database
> should contain 
> 10:32  12
> 10:35  15
> 10:39  13
> 10:46  18
> where only the earliest time with the unchanging value is stored.

Be careful what you wish for.  Usually "avoid storing" is a proxy for
some kind of intended use.  Unless it's infeasible, it's usually better
to store everything, verbatim, as it arrives.  Then you can present it
however you like, with nothing lost.  

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

Re: [sqlite] Trigger name missing

[Jose Isaias Cabrera]

SQLite is even better than I thought...


From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Wednesday, February 26, 2020 11:44 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Trigger name missing

On 2/26/20, Simon Slavin  wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
--
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] Trigger name missing

[Richard Hipp]

On 2/26/20, Simon Slavin  wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
-- 
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] Trigger name missing

[Simon Slavin]

On 26 Feb 2020, at 2:15pm, Dan Kennedy  wrote:

> A statement like the following creates a "BEFORE" trigger named "AFTER". Does 
> that explain things?
> 
>   CREATE TRIGGER AFTER INSERT ON t1 BEGIN
> ...
>   END;
> 
> I find I fall into this trap about once every 18 months...

If only you knew someone with the power to make this generate SQLITE_MISUSE so 
that you, and other programmers, don't have this problem.  Oh well.

(Backward compatibility ?  Do you think anyone who used the word AFTER really 
wants a BEFORE trigger ?  More likely to be a bug they should know about.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] more efficient JSON encoding: idle musing

[Paul van Helden]

>
>
> I experimented with a number of similar ideas for storing JSON when I
> was first designing the JSON components for SQLite.  I was never able
> to find anything that was as fast or as compact as just storing the
> original JSON text.
>

 I've also done a lot of experiments and was surprised at how little a
binary encoding saves in space. Also tried with lookups for keys, but the
lookup values quickly become close to the size of the keys (if not larger)
if keys are mostly shortish.

I'd be happy with a JSON5-like ability to have the quotes on keys optional
if they contain no spaces and no special characters. Seems to reduce the
data size quite significantly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger name missing

[Dan Kennedy]

On 26/2/63 16:31, Jean-Luc Hainaut wrote:

Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in 
which the name is missing. When fired, this trigger doesn't crashes 
but exhibits a strange behaviour. In particular, while expression 
"new." in an "insert" trigger returns the correct value, the 
equivalent expression "select  from T where Id = new.Id" 
always returns null (column "Id" is the PK of table "T"). Similarly, 
"update T set  =   where Id = new.Id" 
(silently) fails.



A statement like the following creates a "BEFORE" trigger named "AFTER". 
Does that explain things?


  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    ...
  END;

I find I fall into this trap about once every 18 months...

Dan.





Not critical but annoying if you are, like me, absent minded when 
typing code!


Regards

Jean-Luc Hainaut

___
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] sqliteDefaultBusyCallback and HAVE_USLEEP

[Peter Kolbus]

> On Feb 26, 2020, at 5:53 AM, Graham Holden  wrote:
> 
> Wednesday, February 26, 2020, 11:15:14 AM, Richard Hipp  
> wrote:
> 
>>> On 2/25/20, Peter Kolbus  wrote:
>>> I noticed that sqliteDefaultBusyCallback() seems to depend directly on the
>>> OS (behave differently based on SQLITE_OS_WIN||HAVE_USLEEP).  Since the
>>> underlying primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and
>>> unixSleep() also has a roundup to whole seconds when HAVE_USLEEP is not
>>> defined, any time resolution limitations are already handled there. And when
>>> a custom VFS is configured, that VFS may well be able to sleep in milli or
>>> microseconds using an RTOS-specific function that is not usleep() — for
>>> example FreeRTOS has osDelay().
>>> 
>>> Is there a reason sqliteDefaultBusyCallback() has this dual implementation,
>>> and defining HAVE_USLEEP is correct to get better performance on platforms
>>> that don’t have usleep()? Or could it be simplified?
>>> 
> 
>> I don't think I understand the question.  It sounds like you are
>> asking why SQLite does not try to sleep for sub-second intervals on
>> systems that do not support usleep()?
> 
> I've not looked at the source, so I don't know whether what I think
> Peter is saying is correct or not, but what I THINK Peter is implying
> there's POSSIBLY some decision (based on HAVE_USLEEP) in the "core"
> SQLite code about what sort of timeout to ask for, before the request
> gets handed over to the VFS to implement.

Thanks Graham, this is exactly what I’m pointing out.

> 
> Presumably, in the default VFS implementation(S) this also uses
> HAVE_USLEEP to decide whether sub-seconds times are possible or not.

The UNIX VFS implementation does test HAVE_USLEEP in unixSleep(). The Windows 
VFS looks like it supports subsecond times as well but doesn’t use this flag.

> 
> However, a custom VFS may have its own way of implementing sub-second
> delays (but does not implement usleep() itself)... it could therefore
> honour a request for sub-second delay if asked. However, to be asked,
> it has to "lie" about supporting usleep() and define HAVE_USLEEP.
> 
> It may simply be a concern over semantics: i.e. whether HAVE_USLEEP
> means ("implements the function usleep()" vs. "can do short delays
> somehow") or it might be a deeper problem in that if you define
> HAVE_USLEEP (to allow a custom VFS to be asked to sleep for short
> amounts) it also causes other parts of the SQLite code to try and use
> usleep() when it isn't implemented).

My primary concern is indeed semantics, especially as not setting HAVE_USLEEP 
with a custom VFS leads to worse performance when multiple threads are 
contending for a lock. (I’m prepared to go through the exercise of defining 
this for my company’s products, but wanted to raise the question first since 
other SQLite users could well be missing out on performance).

Every other use of a HAVE_XXX define that I’ve ever seen, indicates a function 
or header file is available. The second option Graham suggests (can do short 
delays) doesn’t seem to be consistent with the Winflows VFS, as 
sqliteDefaultBusyCallback() also tests SQLITE_OS_WIN.

The second concern is probably not a concern for SQLite if the project defines 
SQLITE_OS_OTHER, but there could be problems for other software components that 
take HAVE_USLEEP to mean that usleep() is available.

> 
> 
> Graham
> 
> 
> ___
> 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] sqliteDefaultBusyCallback and HAVE_USLEEP

[Graham Holden]

Wednesday, February 26, 2020, 11:15:14 AM, Richard Hipp  wrote:

> On 2/25/20, Peter Kolbus  wrote:
>> I noticed that sqliteDefaultBusyCallback() seems to depend directly on the
>> OS (behave differently based on SQLITE_OS_WIN||HAVE_USLEEP).  Since the
>> underlying primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and
>> unixSleep() also has a roundup to whole seconds when HAVE_USLEEP is not
>> defined, any time resolution limitations are already handled there. And when
>> a custom VFS is configured, that VFS may well be able to sleep in milli or
>> microseconds using an RTOS-specific function that is not usleep() — for
>> example FreeRTOS has osDelay().
>>
>> Is there a reason sqliteDefaultBusyCallback() has this dual implementation,
>> and defining HAVE_USLEEP is correct to get better performance on platforms
>> that don’t have usleep()? Or could it be simplified?
>>

> I don't think I understand the question.  It sounds like you are
> asking why SQLite does not try to sleep for sub-second intervals on
> systems that do not support usleep()?

I've not looked at the source, so I don't know whether what I think
Peter is saying is correct or not, but what I THINK Peter is implying
there's POSSIBLY some decision (based on HAVE_USLEEP) in the "core"
SQLite code about what sort of timeout to ask for, before the request
gets handed over to the VFS to implement.

Presumably, in the default VFS implementation(S) this also uses
HAVE_USLEEP to decide whether sub-seconds times are possible or not.

However, a custom VFS may have its own way of implementing sub-second
delays (but does not implement usleep() itself)... it could therefore
honour a request for sub-second delay if asked. However, to be asked,
it has to "lie" about supporting usleep() and define HAVE_USLEEP.

It may simply be a concern over semantics: i.e. whether HAVE_USLEEP
means ("implements the function usleep()" vs. "can do short delays
somehow") or it might be a deeper problem in that if you define
HAVE_USLEEP (to allow a custom VFS to be asked to sleep for short
amounts) it also causes other parts of the SQLite code to try and use
usleep() when it isn't implemented).


Graham


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

Re: [sqlite] Trigger name missing

[Richard Hipp]

On 2/26/20, Jean-Luc Hainaut  wrote:
> Hi all,
>
> It seems that SQLite (version 31.1) accepts a trigger declaration in
> which the name is missing. When fired, this trigger doesn't crashes but
> exhibits a strange behaviour. In particular, while expression
> "new." in an "insert" trigger returns the correct value, the
> equivalent expression "select  from T where Id = new.Id" always
> returns null (column "Id" is the PK of table "T"). Similarly, "update T
> set  =   where Id = new.Id" (silently) fails.
>

What is the text of your trigger?
-- 
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] sqliteDefaultBusyCallback and HAVE_USLEEP

[Richard Hipp]

On 2/25/20, Peter Kolbus  wrote:
> I noticed that sqliteDefaultBusyCallback() seems to depend directly on the
> OS (behave differently based on SQLITE_OS_WIN||HAVE_USLEEP).  Since the
> underlying primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and
> unixSleep() also has a roundup to whole seconds when HAVE_USLEEP is not
> defined, any time resolution limitations are already handled there. And when
> a custom VFS is configured, that VFS may well be able to sleep in milli or
> microseconds using an RTOS-specific function that is not usleep() — for
> example FreeRTOS has osDelay().
>
> Is there a reason sqliteDefaultBusyCallback() has this dual implementation,
> and defining HAVE_USLEEP is correct to get better performance on platforms
> that don’t have usleep()? Or could it be simplified?
>

I don't think I understand the question.  It sounds like you are
asking why SQLite does not try to sleep for sub-second intervals on
systems that do not support usleep()?
-- 
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] unexpected sqlite_busy behaviour within transactions

[Software]

Dear Igor, Andy, Keith

Thank you for your patience to explain. Now very clear to me why it is not 
worth for the second process to honor the sqlite_busy handler, and instead 
returns immediately. In retrospect re-reading with your explanations in mind, I 
also understand the official documentation.

In my case I will use BEGIN IMMEDIATE TRANSACTION to circumvent the issue.

Best regards

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

[sqlite] Error/Result code documentation

[Dominique Devienne]

Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
are the $n*Code variables supposed to be expanded with a numeric
value, and there's a little issue in the doc generation? Thanks, --DD

4. Primary Result Code List
The $nPrimCode result codes ...

5. Extended Result Code List
The $nExtCode extended result codes ...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger name missing

[Jean-Luc Hainaut]

Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in 
which the name is missing. When fired, this trigger doesn't crashes but 
exhibits a strange behaviour. In particular, while expression 
"new." in an "insert" trigger returns the correct value, the 
equivalent expression "select  from T where Id = new.Id" always 
returns null (column "Id" is the PK of table "T"). Similarly, "update T 
set  =   where Id = new.Id" (silently) fails.


Not critical but annoying if you are, like me, absent minded when typing 
code!


Regards

Jean-Luc Hainaut

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

Re: [sqlite] Compiling on AIX OS

[Domingo Alvarez Duarte]

Hello and thank you for reply !

As I said it's an exercise to know the peculiarities of compiling on 
this platform and I'm exposing this experience here in hope it can help 
others and improve sqlite build system.


If what you mention is correct about requiring tcl 8.5 or newer then the 
configure procedure probably should check this and report the problem.


Cheers !

On 26/2/20 10:27, Shawn Wagner wrote:

I'm pretty sure the full build scripts require tcl 8.5 or newer. Maybe try
the -amalgamation or -autoconf versions?

On Wed, Feb 26, 2020 at 1:21 AM Domingo Alvarez Duarte 
wrote:


Hello !



$ uname -a
AIX minimal 1 7 00C63E504B00
$ tclsh
% puts $tcl_patchLevel
8.4.7
%


On 26/2/20 10:19, Shawn Wagner wrote:

puts $tcl_patchLevel

___
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] Compiling on AIX OS

[Shawn Wagner]

I'm pretty sure the full build scripts require tcl 8.5 or newer. Maybe try
the -amalgamation or -autoconf versions?

On Wed, Feb 26, 2020 at 1:21 AM Domingo Alvarez Duarte 
wrote:

> Hello !
>
> 
>
> $ uname -a
> AIX minimal 1 7 00C63E504B00
> $ tclsh
> % puts $tcl_patchLevel
> 8.4.7
> %
> 
>
> On 26/2/20 10:19, Shawn Wagner wrote:
> > puts $tcl_patchLevel
> ___
> 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] Compiling on AIX OS

[Domingo Alvarez Duarte]

Hello !



$ uname -a
AIX minimal 1 7 00C63E504B00
$ tclsh
% puts $tcl_patchLevel
8.4.7
%


On 26/2/20 10:19, Shawn Wagner wrote:

puts $tcl_patchLevel

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

Re: [sqlite] Compiling on AIX OS

[Shawn Wagner]

What version of tcl do you have installed?

(At the tclsh prompt, do a `puts $tcl_patchLevel`)

On Wed, Feb 26, 2020 at 1:05 AM Domingo Alvarez Duarte 
wrote:

> Hello !
>
> Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on AIX
> 7.1 operating system for exercise and to know the peculiarities of
> building software on this OS and I'm getting this error, somehow tclsh
> that is installed on this OS do not wrap/hide OS incompatibilities.
>
> 
>
> tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
> illegal access mode "rb"
>  while executing
> "open $topdir/src/shell.c.in rb"
>  invoked from within
> "set in [open $topdir/src/shell.c.in rb]"
>  (file "/home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl" line 32)
> gmake: *** [Makefile:1079: shell.c] Error 1
>
> 
>
> Cheers !
>
> ___
> 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] Compiling on AIX OS

[Domingo Alvarez Duarte]

Going forward after fixing the access mode from "rb" to "r" we get this 
error (tclsh8.4):




tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
cp /home/mingo/dev/sqlite-src-3310100/ext/fts5/fts5parse.y .
rm -f fts5parse.h
./lemon  -S fts5parse.y
tclsh /home/mingo/dev/sqlite-src-3310100/ext/fts5/tool/mkfts5c.tcl
bad index "19-1": must be integer or end?-integer?
    while executing
"string range $date 0 [string last . $date]-1"
    (procedure "fts5_source_id" line 7)
    invoked from within
"fts5_source_id $::srcdir"
    (procedure "fts5c_printfile" line 7)
    invoked from within
"fts5c_printfile $f "
    ("foreach" body line 1)
    invoked from within
"foreach f $G(src) { fts5c_printfile $f }"
    (file 
"/home/mingo/dev/sqlite-src-3310100/ext/fts5/tool/mkfts5c.tcl" line 112)

gmake: *** [Makefile:1188: fts5.c] Error 1


On 26/2/20 10:05, Domingo Alvarez Duarte wrote:

Hello !

Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on 
AIX 7.1 operating system for exercise and to know the peculiarities of 
building software on this OS and I'm getting this error, somehow tclsh 
that is installed on this OS do not wrap/hide OS incompatibilities.




tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
illegal access mode "rb"
    while executing
"open $topdir/src/shell.c.in rb"
    invoked from within
"set in [open $topdir/src/shell.c.in rb]"
    (file "/home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl" line 32)
gmake: *** [Makefile:1079: shell.c] Error 1



Cheers !


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

[sqlite] Compiling on AIX OS

[Domingo Alvarez Duarte]

Hello !

Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on AIX 
7.1 operating system for exercise and to know the peculiarities of 
building software on this OS and I'm getting this error, somehow tclsh 
that is installed on this OS do not wrap/hide OS incompatibilities.




tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
illegal access mode "rb"
    while executing
"open $topdir/src/shell.c.in rb"
    invoked from within
"set in [open $topdir/src/shell.c.in rb]"
    (file "/home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl" line 32)
gmake: *** [Makefile:1079: shell.c] Error 1



Cheers !

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