Re: [sqlite] SuperSQLite: a supercharged Python SQLite library

[Keith Medcalf]

On sqlite-users@mailinglists.sqlite.org, Randall Smith 
 wrote:

>Date: Fri, 23 Aug 2019 13:16:18 +0100

>From: Simon Slavin mailto:slav...@bigfraud.org>>

>>I have no connection with this extension to the APSW Python SQLite
>>wrapper, I just saw a pointer to it.  The latest commit is around
>>nine months ago.  Perhaps someone who is familiar with Python
>>libraries might highlight anything this does new or unusually well.

>>

>I have looked at this on and off for Python effort I have been
>working on.  I think one big claim to fame for this wrapper is better
>and more predictable transaction handling.  The "normal" python
>SQLite library is really weird about transactions, and tends to
>introduce its own transaction-related operations behind the scenes,
>so as a user you're never 100% sure what's going on or whether your
>own transaction management is doing what you expect.  I find this
>really annoying since one wants transaction handling to very simple
>and predictable so that the right thing happens under all
>circumstances.

>I, too, would be interested to hear from people who have real
>experience with it.

I took a brief look and I cannot see why one would want to use BOTH the 
standard pysqlite wrapper (which is included with the default distribution of 
Python) AND APSW at the same time, nor can I see anything that this does that 
one could not do by simply using the standard pysqlite and standard APSW, thus 
avoiding all the bother.

As soon as I found out about APSW I stopped using pysqlite for exactly the 
reason that you say -- its transaction handling is completely buggered (though 
you can get around that by always opening a database with the 
isolation_level=None keyword to disable Esmeralda's magic -- Esmeralda of 
course referring to Naomi Hogan's character in the 1964 series Bewitched that 
was always casting spells that didn't come out quite as expected).

Plus, of course, the advantage that APSW is still being actively maintained by 
Roger Binns, who is active here as well; and, that you can build it as an 
extension that includes SQLite3 so there is no symbol pollution and no 
dependency on the vagaries of using the system sqlite3 shared library.

About the only thing that pysqlite has that APSW does not is access to the 
converterss and adapters plugins, and the "Row" object.  These things can, 
however, be fixed quite easily since you can write your own code to implement 
these things (as I have done, far surpassing the ability of pysqlite to do 
these things) using nothing more than the builtin capablilites of APSW (the 
exechook and rowhook) and doing a little class wrapping.  (My Row object 
inherits from the builtin dict but allows access as attributes r.field, 
dictionary r['field'], or tuple r[2] and implements the pickle protocol as 
well).

APSW does not expose the sessions, changeset, or rbu interfaces of SQLite3 to 
Python but then I have never needed those anyway, and if the need came I 
suppose I could always modify APSW to build itself to expose those symbols so 
they could be accessed by ctypes, since a .pyd is nothing more than a shared 
library anyway.

Plus, of course, APSW is very well documented.  I do not think that applies to 
either pysqlite or supersqlite.

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

[sqlite] SuperSQLite: a supercharged Python SQLite library

[Randall Smith]

Date: Fri, 23 Aug 2019 13:16:18 +0100

From: Simon Slavin mailto:slav...@bigfraud.org>>



I have no connection with this extension to the APSW Python SQLite wrapper, I 
just saw a pointer to it.  The latest commit is around nine months ago.  
Perhaps someone who is familiar with Python libraries might highlight anything 
this does new or unusually well.







I have looked at this on and off for Python effort I have been working on.  I 
think one big claim to fame for this wrapper is better and more predictable 
transaction handling.  The "normal" python SQLite library is really weird about 
transactions, and tends to introduce its own transaction-related operations 
behind the scenes, so as a user you're never 100% sure what's going on or 
whether your own transaction management is doing what you expect.  I find this 
really annoying since one wants transaction handling to very simple and 
predictable so that the right thing happens under all circumstances.



I, too, would be interested to hear from people who have real experience with 
it.



Randall Smith


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

Re: [sqlite] Inverted changesets and UNIQUE constraints

[Daniel Kraft]

Hi!

On 26.08.19 14:05, Simon Slavin wrote:
> On 26 Aug 2019, at 12:43pm, Dan Kennedy  wrote:
>> When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change 
>> into a "retry buffer". Then, once it has attempted all changes in the 
>> changeset, it goes back and retries those in the retry buffer. It keeps 
>> retrying like this until no further progress can be made.

Thanks a lot for the explanation, Dan!  That's good to know.

> That's clever.  You'd think that in something as ordered and predictable as 
> SQL it wouldn't be necessary.  I wonder whether there's a changeset it 
> doesn't work for.

Indeed there is.  If you replace my modification with this:

UPDATE `mytable` SET `value` = 50 WHERE `id` = 1;
UPDATE `mytable` SET `value` = 42 WHERE `id` = 2;
UPDATE `mytable` SET `value` = 100 WHERE `id` = 1;

Then it works processing forward, but the changeset does not apply
anymore (because the three UPDATE's get changed into two (one per ID),
and the change is not possible to do at all with only two UPDATE's).

This case is not so relevant for me in practice, but nevertheless could
in theory happen as well.  Is there something I can do to work around
that as well?  For instance, can I record individual changesets for each
distinct change (rather than combining them)?  That will of course hurt
performance, but may be ok in my situation.

E.g., is it possible to use the pre-update hooks that the sessions
module itself uses to start and extract a changeset right around each
change?  Or would that not work (e.g. due to interactions with the hooks
for sessions itself) / be prohibitively expensive?

Thanks!

Yours,
Daniel

-- 
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inverted changesets and UNIQUE constraints

[Simon Slavin]

On 26 Aug 2019, at 12:43pm, Dan Kennedy  wrote:

> When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change 
> into a "retry buffer". Then, once it has attempted all changes in the 
> changeset, it goes back and retries those in the retry buffer. It keeps 
> retrying like this until no further progress can be made.

That's clever.  You'd think that in something as ordered and predictable as SQL 
it wouldn't be necessary.  I wonder whether there's a changeset it doesn't work 
for.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inverted changesets and UNIQUE constraints

[Dan Kennedy]

On 26/8/62 15:12, Daniel Kraft wrote:

Hi!

I'm using the SQLite session extension to create changesets, invert them
and apply them to undo previous changes in the database.  (Essentially
what I need to do is persistent savepoints.)

This works well so far, but I recently wondered about the interaction
with UNIQUE constraints.  In particular, let's say that I record a
changeset of the following modification:  I remove some rows from a
table, and *then* I insert new rows that have the same values in UNIQUE
columns as the previously removed ones.  That obviously works fine
because I delete first and *then* insert.

However, my understanding of how the session extension works (according
to its docs) is that when I invert and apply the changeset, it will
*first* insert the deleted rows, and *then* delete the inserted ones.
(Because it inverts each operation but not the order of them.)

I created a simplified example program that performs exactly this on an
in-memory database.  The code (plus the shell script to build it with
the flags I used) is below and can also be found on Github:

   https://gist.github.com/domob1812/a3b78739772a1ff0c001be6cbc600f17

(Perhaps that is easier to view than in the email.)

When I run that on my system (with SQLite 3.16.2 from Debian Stretch), I
get errors printed from ErrorLogger like this:

E0826 10:08:16.840441 21876 test.cpp:16] SQLite error (code 2067): abort
at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint
failed: mytable.value
E0826 10:08:16.840520 21876 test.cpp:16] SQLite error (code 2067): abort
at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint
failed: mytable.value

This matches what I expect.  However, it seems that applying the
changeset still works fine, and I get the correct "old" state restored.

Is this just "by chance", or can I safely ignore these errors in this
context and rely on the ability to apply inverted changesets even if
they (intermittently) violate UNIQUE constraints?


They can be ignored I think.

When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the 
change into a "retry buffer". Then, once it has attempted all changes in 
the changeset, it goes back and retries those in the retry buffer. It 
keeps retrying like this until no further progress can be made. So in a 
case like yours - where there does exist an order in which the changes 
can be successfully applied without hitting constraints - it eventually 
succeeds in applying the entire changeset. But, as the sessions module 
works through the normal SQL interface, each time it hits an 
intermittent constraint, an error message is emitted on the log.


Dan.






Thanks a lot for any insights!

Yours,
Daniel

=
Build script:

#!/bin/sh -e

PKGS="sqlite3 libglog"
CFLAGS="`pkg-config --cflags ${PKGS}` -std=c++14 -Wall -Werror -pedantic"
CFLAGS="${CFLAGS} -std=c++14 -Wall -Werror -pedantic"
CFLAGS="${CFLAGS} -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK"
LIBS=`pkg-config --libs ${PKGS}`

g++ ${CFLAGS} ${LIBS} test.cpp -o test

=
test.cpp:

/* Test code for UNIQUE keys and inverting SQLite changesets.  */

#include 

#include 

#include 
#include 

namespace
{

void
ErrorLogger (void* arg, const int errCode, const char* msg)
{
   LOG (ERROR) << "SQLite error (code " << errCode << "): " << msg;
}

void
Execute (sqlite3* db, const std::string& sql)
{
   VLOG (1) << "Executing SQL:\n" << sql;
   char* err;
   const int rc = sqlite3_exec (db, sql.c_str (), nullptr, nullptr, );
   if (rc != SQLITE_OK)
 LOG (FATAL) << "SQL error: " << err;
   sqlite3_free (err);
}

void
Print (sqlite3* db)
{
   const std::string sql = R"(
 SELECT `id`, `value`
   FROM `mytable`
   ORDER BY `id` ASC
   )";

   sqlite3_stmt* stmt;
   CHECK_EQ (sqlite3_prepare_v2 (db, sql.c_str (), sql.size (), ,
nullptr),
 SQLITE_OK);

   while (true)
 {
   const int rc = sqlite3_step (stmt);
   if (rc == SQLITE_DONE)
 break;
   CHECK_EQ (rc, SQLITE_ROW);

   LOG (INFO)
   << "  Row: (" << sqlite3_column_int (stmt, 0)
   << ", " << sqlite3_column_int (stmt, 1) << ")";
 }

   CHECK_EQ (sqlite3_finalize (stmt), SQLITE_OK);
}

int
AbortOnConflict (void* ctx, const int conflict, sqlite3_changeset_iter* it)
{
   LOG (ERROR) << "Changeset application has conflict of type " << conflict;
   return SQLITE_CHANGESET_ABORT;
}

} // anonymous namespace

int
main ()
{
   LOG (INFO)
   << "Using SQLite version " << SQLITE_VERSION
   << " (library version: " << sqlite3_libversion () << ")";

   CHECK_EQ (sqlite3_config (SQLITE_CONFIG_LOG, , nullptr),
 SQLITE_OK);

   sqlite3* db;
   CHECK_EQ (sqlite3_open (":memory:", ), SQLITE_OK);
   LOG (INFO) << "Opened in-memory database";

   Execute (db, R"(
 CREATE TABLE `mytable`
   (`id` INTEGER PRIMARY KEY,
`value` INTEGER,
UNIQUE (`value`));
 INSERT INTO 

[sqlite] Inverted changesets and UNIQUE constraints

[Daniel Kraft]

Hi!

I'm using the SQLite session extension to create changesets, invert them
and apply them to undo previous changes in the database.  (Essentially
what I need to do is persistent savepoints.)

This works well so far, but I recently wondered about the interaction
with UNIQUE constraints.  In particular, let's say that I record a
changeset of the following modification:  I remove some rows from a
table, and *then* I insert new rows that have the same values in UNIQUE
columns as the previously removed ones.  That obviously works fine
because I delete first and *then* insert.

However, my understanding of how the session extension works (according
to its docs) is that when I invert and apply the changeset, it will
*first* insert the deleted rows, and *then* delete the inserted ones.
(Because it inverts each operation but not the order of them.)

I created a simplified example program that performs exactly this on an
in-memory database.  The code (plus the shell script to build it with
the flags I used) is below and can also be found on Github:

  https://gist.github.com/domob1812/a3b78739772a1ff0c001be6cbc600f17

(Perhaps that is easier to view than in the email.)

When I run that on my system (with SQLite 3.16.2 from Debian Stretch), I
get errors printed from ErrorLogger like this:

E0826 10:08:16.840441 21876 test.cpp:16] SQLite error (code 2067): abort
at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint
failed: mytable.value
E0826 10:08:16.840520 21876 test.cpp:16] SQLite error (code 2067): abort
at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint
failed: mytable.value

This matches what I expect.  However, it seems that applying the
changeset still works fine, and I get the correct "old" state restored.

Is this just "by chance", or can I safely ignore these errors in this
context and rely on the ability to apply inverted changesets even if
they (intermittently) violate UNIQUE constraints?

Thanks a lot for any insights!

Yours,
Daniel

=
Build script:

#!/bin/sh -e

PKGS="sqlite3 libglog"
CFLAGS="`pkg-config --cflags ${PKGS}` -std=c++14 -Wall -Werror -pedantic"
CFLAGS="${CFLAGS} -std=c++14 -Wall -Werror -pedantic"
CFLAGS="${CFLAGS} -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK"
LIBS=`pkg-config --libs ${PKGS}`

g++ ${CFLAGS} ${LIBS} test.cpp -o test

=
test.cpp:

/* Test code for UNIQUE keys and inverting SQLite changesets.  */

#include 

#include 

#include 
#include 

namespace
{

void
ErrorLogger (void* arg, const int errCode, const char* msg)
{
  LOG (ERROR) << "SQLite error (code " << errCode << "): " << msg;
}

void
Execute (sqlite3* db, const std::string& sql)
{
  VLOG (1) << "Executing SQL:\n" << sql;
  char* err;
  const int rc = sqlite3_exec (db, sql.c_str (), nullptr, nullptr, );
  if (rc != SQLITE_OK)
LOG (FATAL) << "SQL error: " << err;
  sqlite3_free (err);
}

void
Print (sqlite3* db)
{
  const std::string sql = R"(
SELECT `id`, `value`
  FROM `mytable`
  ORDER BY `id` ASC
  )";

  sqlite3_stmt* stmt;
  CHECK_EQ (sqlite3_prepare_v2 (db, sql.c_str (), sql.size (), ,
nullptr),
SQLITE_OK);

  while (true)
{
  const int rc = sqlite3_step (stmt);
  if (rc == SQLITE_DONE)
break;
  CHECK_EQ (rc, SQLITE_ROW);

  LOG (INFO)
  << "  Row: (" << sqlite3_column_int (stmt, 0)
  << ", " << sqlite3_column_int (stmt, 1) << ")";
}

  CHECK_EQ (sqlite3_finalize (stmt), SQLITE_OK);
}

int
AbortOnConflict (void* ctx, const int conflict, sqlite3_changeset_iter* it)
{
  LOG (ERROR) << "Changeset application has conflict of type " << conflict;
  return SQLITE_CHANGESET_ABORT;
}

} // anonymous namespace

int
main ()
{
  LOG (INFO)
  << "Using SQLite version " << SQLITE_VERSION
  << " (library version: " << sqlite3_libversion () << ")";

  CHECK_EQ (sqlite3_config (SQLITE_CONFIG_LOG, , nullptr),
SQLITE_OK);

  sqlite3* db;
  CHECK_EQ (sqlite3_open (":memory:", ), SQLITE_OK);
  LOG (INFO) << "Opened in-memory database";

  Execute (db, R"(
CREATE TABLE `mytable`
  (`id` INTEGER PRIMARY KEY,
   `value` INTEGER,
   UNIQUE (`value`));
INSERT INTO `mytable` (`id`, `value`) VALUES (1, 42), (2, 100);
  )");

  LOG (INFO) << "Initial state:";
  Print (db);

  /* Now we modify the table and record the result in a changeset.  The
 modification is valid with respect to the UNIQUE constraint, but only
 because we delete the existing entries first and insert afterwards.  */
  sqlite3_session* session;
  CHECK_EQ (sqlite3session_create (db, "main", ), SQLITE_OK);
  CHECK_EQ (sqlite3session_attach (session, nullptr), SQLITE_OK);

  Execute (db, R"(
DELETE FROM `mytable`;
INSERT INTO `mytable` (`id`, `value`) VALUES (3, 42), (4, 100);
  )");

  LOG (INFO) << "Modified state:";
  Print (db);

  /* Extract the changeset, invert it and apply the inverted changeset to
 undo the previous 

Re: [sqlite] [EXTERNAL] ORDER BY is ignored during INSERT INTO

[Hick Gunter]

Why would you want to do this?

If you require a SELECT to return rows in a certain order, you need to specify 
ORDER BY on the SELECT statement. And not rely on ascending insert time or any 
other visitation order effect.

Additionally - unless specific precautions are taken - sorted insert results in 
a half empty tree structure.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von André Borchert
Gesendet: Sonntag, 25. August 2019 23:10
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] ORDER BY is ignored during INSERT INTO

Hello,

I try to copy one table into a second identical one. Once the second table is 
created I want to move the content over sorted by ASC.

The issue is that the ORDER BY statement gets ignored during a INSERT INTO:

INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE 
CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC

When the SELECT query is executed alone, the content is sorted fine as expected.

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


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

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

Re: [sqlite] [EXTERNAL] Attached databases and union view.

[Hick Gunter]

I think you are looking for UNION ALL to avoid creating an ephemeral table to 
implement the implied DISTINCT

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peter da Silva
Gesendet: Donnerstag, 22. August 2019 17:28
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Attached databases and union view.

Have an existing application that's pushing the limit on how fast it can read 
data and add it to the database, and thinking of sharding the database file so 
I can have multiple writers writing to shards of the main tables.

ATTACH DATABASE 'shard0.sqlite' as shard0; ATTACH DATABASE 'shard1.sqlite' as 
shard1; ...

CREATE TEMPORARY VIEW sharded_main_table AS
SELECT col,col,col...,all_columns_basically FROM shard0.main_table UNION
SELECT col,col,col...,all_columns_basically FROM shard1.main_table ...;

What's the best way to construct this union view so the query optimizer won't 
be horribly confused? If I run something like "SELECT count(*) FROM 
sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower than 
the same query against the original main_table. Running the query against each 
shardN.main_table it's actually faster (in total time for all queries in 
sequence) than running it against the original table.

Is there a better way to construct the view, or am I going to get best query 
performance by making my code shard-aware?

All the original indexes on main_table have been copied to the shard databases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

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