[sqlite] Work toward making the schema parsing logic simplier

[Zsbán Ambrus]

On 1/15/16, Domingo Alvarez Duarte  wrote:
> Now that you are refactoring on the schema parsing could be nice if somehow
> sqlite3 expose the schema/sql parser for developers.

Note that sqlite3 already exposes most of the schema in a form
readable to an application.  The SQLITE_MASTER and SQLITE_TEMP_MASTER
tables give you the list of tables and views.  The PRAGMA table_info
statement gives information about a table or view; PRAGMA index_list
and PRAGMA index_info give information about indexes.

-- Ambrus

[sqlite] out of the void: xDlSym

[Richard Damon]

A reason for a function pointer like void (*f)(void) is that C 
guarantees that all function pointers are 'compatible' in the sense that 
you can cast a function pointer to a different type of function pointer 
and then back again and get a working pointer. This is the same 
guarantee that void* has for object pointers, but void* also adds 
implicit conversions for which no function pointer type has.

The xDlSym function uses that particular signature, because as you say, 
it is somewhat unlikely to be used in normal programming (it actually IS 
a useful type of function, it just needs to interact through global 
variables). It is expected that you will case the result to the proper 
type of function pointer before calling the function.

On many machines (and I believer required for POSIX) the conversion of a 
function pointer to a void* and back to the proper type of function 
pointer would work, giving you a working function pointer. It was never 
promised by the standard to work in general, and there are machines 
where it doesn't work (you just need a larger program space than data 
space).

-- 
Richard Damon

[sqlite] out of the void: xDlSym

[Richard Hipp]

On 1/15/16, James K. Lowden  wrote:
> I spent a fair number of hours scrutinizing xDlSym today, and I'd just
> like to confirm my understanding.  Despite having worked with C on and
> off since the Reagan administration, I was unprepared for
>
>void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void);

Yes, you decoded the declaration correctly.

The rule in C is that you start with the symbol name ("xDlSym" in this
case) and start working your way out, preferring stuff on the right
over stuff on the left unless there are parentheses to force the
left-hand stuff to be taken first.

So in the example above we start with

"xDlSym is..."

There is stuff to the right of the base symbol, but we have to go to
the left due to the inner-most parentheses...

"... a pointer to"

Having worked out of the inner-most parentheses, we go back to the
right again...

"... a function taking parameters sqlite3_vfs*, void*, and const
char* and returning..."

Now there is another set of parentheses which forces us to stop moving
right to go to the left again...

"... a pointer to..."

Now back to the right...

"... a function taking no parameters and returning..."

And finally once more back to the left:

"... void."

>
> IIUC xDlSym is a pointer to a function taking 3 arguments, returning a
> pointer to a "a function", as it says in src/os_unix.c.  That function
> has a peculiar signature,
>
>   void f(void);
>
> You may imagine my resistance.  That's one function I'm sure I've never
> needed, nor ever will!  :-)
>
> The comments also indicate that this definition was created to satisfy
> gcc under C90 in pedantic mode.  That suggests that once upon a time
> xDlSym would have been defined more conventionally as
>
>void * (*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol);
>
> but for the never-a-function-pointer-a-void*-shall-be rule of C?
>
> BTW, cdecl.org offers its own interpretation:
>
>   "declare xDlSym as pointer to function (pointer to void, pointer
> to void, pointer to const char) returning pointer to function (void)
> returning void"
>
> which, for me at least, is one of those answers that makes sense only
> after you know the answer.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] out of the void: xDlSym

[James K. Lowden]

I spent a fair number of hours scrutinizing xDlSym today, and I'd just
like to confirm my understanding.  Despite having worked with C on and
off since the Reagan administration, I was unprepared for 

   void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void);

IIUC xDlSym is a pointer to a function taking 3 arguments, returning a
pointer to a "a function", as it says in src/os_unix.c.  That function
has a peculiar signature, 

void f(void);

You may imagine my resistance.  That's one function I'm sure I've never
needed, nor ever will!  :-)  

The comments also indicate that this definition was created to satisfy
gcc under C90 in pedantic mode.  That suggests that once upon a time
xDlSym would have been defined more conventionally as

   void * (*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol);

but for the never-a-function-pointer-a-void*-shall-be rule of C?

BTW, cdecl.org offers its own interpretation:

"declare xDlSym as pointer to function (pointer to void, pointer
to void, pointer to const char) returning pointer to function (void)
returning void"   

which, for me at least, is one of those answers that makes sense only
after you know the answer.  

--jkl

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Olivier Mascia]

> Le 15 janv. 2016 ? 19:13, James K. Lowden  a 
> ?crit :
> 
>> Let a transaction (started with BEGIN TRANSACTION) which did only
>> reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK
>> TRANSACTION, or is it completely insignificant?
> 
> Why not do the right thing and remove the begin & commit transaction
> statements?
> 
> An explicit transaction implies a unit of work, two or more changes to
> the database that must cohere for the database be consistent.
> Read-only operations (by which I suppose you mean SELECT) don't modify
> the database and don't belong in a transaction.


James, I appreciate and thank you for your answer, though I completely disagree.

a) There is always a transaction: either implicit or explicit.

b) I have many situations where running multiple statements within a single 
transaction, even though these statements are read-only (selects) is 
specifically wanted. When using WAL such readers do not block writers and write 
transactions committed after the read transaction began, are not seen by that 
read transaction. Allowing it to have a stable view on the whole database.  
Which is *very* important for some kind of work which can not easily expressed 
in a single select statement, however complex it is.

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160115/1962271c/attachment.pgp>

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Dominique Devienne]

On Fri, Jan 15, 2016 at 7:25 PM, Keith Medcalf  wrote:

> While it is true that without further action each statement is carried out
> in a separate transaction, it may very well be that the OP wants to have
> what is called Repeatable-Read.  The only way to obtain Repeatable-Read
> between statements is to put the SELECTs in the *same* transaction,
> otherwise they will see different views of the database if there was an
> intervening update.


Did you consider the new http://sqlite.org/c3ref/snapshot_get.html when you
wrote the above?

Richard, what's the use case(s) for the new experimental snapshot APIs?
Thanks, --DD

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[James K. Lowden]

On Fri, 15 Jan 2016 20:39:15 +0100
Olivier Mascia  wrote:

> write transactions committed after the read transaction began, are
> not seen by that read transaction. Allowing it to have a stable view
> on the whole database.  

As Keith correctly surmised, you need repeatable read.  Never having
had the need, I never explored that aspect of transactions.  (I've
changed isolation levels on other products.  I don't remember the
circumstances well enough to know whether a transaction would have
accomplished the same end.)  

Live & learn.  Thanks for the explanation.  

--jkl

[sqlite] Work toward making the schema parsing logic simplier

[Domingo Alvarez Duarte]

Hello Richard !  

Now that you are refactoring on the schema parsing could be nice if somehow
sqlite3 expose the schema/sql parser for developers.  

A kind of sax style parser, with callbacks given option to do some
rewrite/extend syntax ? (ideas welcome)  

An interesting project can be seem here :  

https://github.com/codeschool/sqlite-parser  

demo : https://codeschool.github.io/sqlite-parser/demo/  

Cheers !  

?  

?

[sqlite] Setting SQLITE_OMIT_FLOATING_POINT has surprisingundocumented consequences

[Domingo Alvarez Duarte]

Hello !  

When we declare SQLITE_OMIT_FLOATING_POINT what really happens is that this
is activated (mainly #define double sqlite_int64):

/*
** If compiling for a processor that lacks floating point support,
** substitute integer for floating-point
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# define double sqlite_int64
# define float sqlite_int64
# define LONGDOUBLE_TYPE sqlite_int64
# ifndef SQLITE_BIG_DBL
#?? define SQLITE_BIG_DBL (((sqlite3_int64)1)<<50)
# endif
# define SQLITE_OMIT_DATETIME_FUNCS 1
# define SQLITE_OMIT_TRACE 1
# undef SQLITE_MIXED_ENDIAN_64BIT_FLOAT
# undef SQLITE_HAVE_ISNAN
#endif
#ifndef SQLITE_BIG_DBL
# define SQLITE_BIG_DBL (1e99)
#endif  

And with some more macros we could also have sqlite using _Descimal64 instead
of double:  

/*
** If compiling for a processor that lacks floating point support,
** substitute integer for floating-point
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# define sqlite_double sqlite_int64
# define TO_SQLITE_DOUBLE(x)? (x)
# define LITDBL(n) n
# define float sqlite_int64
# define LONGDOUBLE_TYPE sqlite_int64
# ifndef SQLITE_BIG_DBL
#?? define SQLITE_BIG_DBL (((sqlite3_int64)1)<<50)
# endif
# define SQLITE_OMIT_DATETIME_FUNCS 1
# define SQLITE_OMIT_TRACE 1
# undef SQLITE_MIXED_ENDIAN_64BIT_FLOAT
# undef SQLITE_HAVE_ISNAN
#else
# ifdef SQLITE_USE_DECIMAL
# define sqlite_double? _Decimal64
# define TO_SQLITE_DOUBLE(x)? __bid_extenddfdd(x)
# define LITDBL(n) n##dd
#? define LONGDOUBLE_TYPE _Decimal128
# else
#? define sqlite_double? double
# define TO_SQLITE_DOUBLE(x)? (x)
#? define LITDBL(n) n
#? define LONGDOUBLE_TYPE long double
# endif
#endif
#ifndef SQLITE_BIG_DBL
# define SQLITE_BIG_DBL (LITDBL(1e99))
#endif  

#ifndef DOUBLE_SIGNIFICANT_DIGITS
# define DOUBLE_SIGNIFICANT_DIGITS 16
#endif  

Cheers !  

?  ?  

?

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Simon Slavin]

On 15 Jan 2016, at 4:20pm, Olivier Mascia  wrote:

> I'm only left with the case of a row returning query which the programmer 
> would not want to step() up to the end. In this case, it is very reasonable 
> to have the programmer think of "freeing" the query in some way through 
> either the Statement class going out of scope or the call of some "reset()" 
> method.

This is a common problem when you're writing a library.

Assuming that you can provide a _dispose() method which is automatically called 
when your object slips out of scope, you're fine: just call _finalize() in it.

If you cannot depend on this then you are going to have to provide a disposal 
method and rely on your programmer calling it.

Simon.

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Olivier Mascia]

Richard, Simon,

> Le 15 janv. 2016 ? 16:30, Richard Hipp  a ?crit :
> 
>> But the finalize of a
>> statement (using the C++ wrapper we build for our use over C SQLite API) is
>> currently differed until another prepare is done using the same Statement
>> object, or until the Statement object goes out of scope (destructor).
> 
> That's a problem.  Until a prepared statement is either finalized or
> reset, SQLite must assume that you might call sqlite3_step() on it
> again.  And so it has to continue holding various locks and other
> state that will be needed by that subsequent sqlite3_step() call.

I think this is well understood now.   Thanks Richard.

> A better approach would be to invoke sqlite3_reset() as soon as you
> know that the prepared statement will not be stepped again.  That will
> release locks (and other resources) and help other operations to
> proceed unimpeded.

> Le 15 janv. 2016 ? 16:31, Simon Slavin  a ?crit :
> 
> Immediately after doing your first _step() you should be thinking to do your 
> _finalize() or _reset() as soon as practical.  It should weigh on your mind 
> like an open door in a high-crime area.  Having done either of those, you can 
> keep the statement around as long as you like: all it will use up is a little 
> memory.

Thanks Richard and Simon.

We indeed need to design the Statement class differently to sqlite3_reset() it 
as soon as possible. And I now realize that I overlooked one detail, which will 
help a lot.  For select-kind queries I can capitalize on sqlite3_step() having 
to be called until it returns SQLITE_DONE (so one more time than there are 
rows).  When that happens, I can sqlite3_reset() immediately. The programmer 
has been done with the previous (last row) since before the last call to 
step().  Same thing for SQLITE_OK, which would be returned for successful 
non-row returning queries, it can call reset() right away.

I'm only left with the case of a row returning query which the programmer would 
not want to step() up to the end. In this case, it is very reasonable to have 
the programmer think of "freeing" the query in some way through either the 
Statement class going out of scope or the call of some "reset()" method.  You 
see, the whole purpose of using a thin set of classes over the C API is to make 
it as easy as possible to merge SQL statements within the C++ code of the 
application.  I have done this exercise for some other databases, but I'm new 
at SQLite.

Again thank you very much for the very valuable input this mailing list 
provides. When we will be code-complete and able to decide for SQLite in our 
next releases, we will happily contract one of the paid professional support 
options HWACI offers.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Olivier Mascia]

> Le 15 janv. 2016 ? 13:08, Hick Gunter  a ?crit :
> 
> I think you may mean "database connection" instead of "statement handle".
> 
> When you are finished with processing a statement, you should either reset 
> (if you intead to use it again later) or finalize it. Otherwise SQLite must 
> assume that you want to continue later and needs to keep around whatever 
> locks it acquired during processing. Which is also the cause for 
> sqlite3_wal_checkpoint returning SQLITE_LOCKED.
> 
> You should not be reusing a statement handle before calling finalize on it, 
> as this would cause a memory leak.

Thanks Hick,

Be sure I'm not reusing any statement handle without finalizing it.  No leaks, 
we have a checker in place to detect.  But the finalize of a statement (using 
the C++ wrapper we build for our use over C SQLite API) is currently differed 
until another prepare is done using the same Statement object, or until the 
Statement object goes out of scope (destructor).  We might have to add a method 
to 'clear' our Statement objects as soon as not needed anymore.  Though that 
probably is not very important in our real programming, the configuration I had 
in the test which returned LOCKED on the wal_checkpoint is 'synthetic' and not 
really representative. A Statement is generally re-used for successive 
different queries while a method is doing whatever its job is, then the local 
Statement goes out of scope (so its last instance gets finalized).

What remains in a multiple consumer set of processes or threads is that at any 
point in time while there exist a single not-yet-finalized statement, 
wal_checkpoint is bound to return LOCKED very easily, drastically reducing the 
windows of time where it could be called successfully.

Would it make sense (or even be valid) to use the sqlite3_unlock_notify() 
mechanism on attempts to wal_checkpoint as we do it successfully on prepare or 
step?

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

[sqlite] WAL: difference between IMMEDIATE and DEFERRED transaction

[Olivier Vidal]

Than you Mr Hipp!

So DEFERRED advantage is that less time locked (if there are SELECTS 
between BEGIN and UPDATE). But its drawback is that other threads can 
write between BEGIN and the first UPDATE / DELETE .. ?

> Richard Hipp 
> vendredi 15 janvier 2016 15:19
>
> BEGIN IMMEDIATE claims a write lock on the database file immediately,
> so that no other process or thread can come along and start a second
> write transaction until after the one you just started finishes.
>
> BEGIN DEFERRED (the default) waits for the first actual change to the
> database (a subsequent DELETE, INSERT, or UPDATE) before acquiring the
> write lock. That means that even though the "BEGIN" was successful,
> the next write operation might fail with an SQLITE_BUSY error if
> another thread or process jumps in line ahead of you and acquires the
> write lock.
>
> Olivier Vidal 
> vendredi 15 janvier 2016 15:09
>
> Hello all,
>
> I would like to be sure I understand the difference between an 
> IMMEDIATE transaction and a DEFERRED transaction, in WAL mode.
> Sorry for my bad english.
>
> Here is what I understand:
>
> Example of an IMMEDIATE transaction:
>
> - BEGIN IMMEDIATE TRANSACTION
> - SELECT
> - UPDATE
> - SELECT
> - UPDATE
> - INSERT
> - SELECT
> - COMMIT
>
> When the transaction starts, it tries to put a RESERVED lock on the 
> database. If the database is busy (another thread that writes at the 
> same time), the engine retries for the duration of the TIMEOUT. If 
> this is not possible at the end of the timeout, a BUSY error is 
> generated.
>
> If BEGIN IMMEDIATE obtains the lock, it is sure that the database 
> cannot be modified between BEGIN and COMMIT by others threads. Also, 
> it is sure that the others threads that read the database at the same 
> time will get a non-altered view of the database, so the view before 
> the BEGIN.
>
> For the DEFERRED transaction:
>
> - BEGIN DEFERRED TRANSACTION
> - SELECT
> - UPDATE
> - SELECT
> - UPDATE
> - INSERT
> - SELECT
> - COMMIT
>
> The lock is requested at the first UPDATE (and there have no 
> TIMEOUT?). The database cannot be changed by others threads between 
> the FIRST UPDATE and COMMIT. But others threads may write between 
> BEGIN and the first UPDATE. Other threads that read at the same time 
> will see the State of the database before the first UPDATE?
>
> is that right?
>
> Thank you
> olivier
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Simon Slavin]

On 15 Jan 2016, at 3:18pm, Olivier Mascia  wrote:

> Be sure I'm not reusing any statement handle without finalizing it.  No 
> leaks, we have a checker in place to detect.  But the finalize of a statement 
> (using the C++ wrapper we build for our use over C SQLite API) is currently 
> differed until another prepare is done using the same Statement object, or 
> until the Statement object goes out of scope (destructor).  We might have to 
> add a method to 'clear' our Statement objects as soon as not needed anymore.  
> Though that probably is not very important in our real programming, the 
> configuration I had in the test which returned LOCKED on the wal_checkpoint 
> is 'synthetic' and not really representative. A Statement is generally 
> re-used for successive different queries while a method is doing whatever its 
> job is, then the local Statement goes out of scope (so its last instance gets 
> finalized).

Please don't do this.  Until you have done your _finalize() or _reset() that 
statement can be taking up a lot of memory and/or have temporary files created 
and/or have locks on files.  You want to release all this stuff as soon as 
possible to prevent your program from being a memory/disk/resource/handle hog.

Remember that the only reason _prepare, _step, _finalize/_reset are not all one 
command is that not all operating systems allow SQLite to callback your program 
to tell it that the next row is ready.

Immediately after doing your first _step() you should be thinking to do your 
_finalize() or _reset() as soon as practical.  It should weigh on your mind 
like an open door in a high-crime area.  Having done either of those, you can 
keep the statement around as long as you like: all it will use up is a little 
memory.

Simon.

[sqlite] WAL: difference between IMMEDIATE and DEFERRED transaction

[Olivier Vidal]

Hello all,

I would like to be sure I understand the difference between an IMMEDIATE 
transaction and a DEFERRED transaction, in WAL mode.
Sorry for my bad english.

Here is what I understand:

Example of an IMMEDIATE transaction:

- BEGIN IMMEDIATE TRANSACTION
- SELECT
- UPDATE
- SELECT
- UPDATE
- INSERT
- SELECT
- COMMIT

When the transaction starts, it tries to put a RESERVED lock on the 
database. If the database is busy (another thread that writes at the 
same time), the engine retries for the duration of the TIMEOUT. If this 
is not possible at the end of the timeout, a BUSY error is generated.

If BEGIN IMMEDIATE obtains the lock, it is sure that the database cannot 
be modified between BEGIN and COMMIT by others threads. Also, it is sure 
that the others threads that read the database at the same time will get 
a non-altered view of the database, so the view before the BEGIN.

For the DEFERRED transaction:

- BEGIN DEFERRED TRANSACTION
- SELECT
- UPDATE
- SELECT
- UPDATE
- INSERT
- SELECT
- COMMIT

The lock is requested at the first UPDATE (and there have no TIMEOUT?). 
The database cannot be changed by others threads between the FIRST 
UPDATE and COMMIT. But others threads may write between BEGIN and the 
first UPDATE. Other threads that read at the same time will see the 
State of the database before the first UPDATE?

is that right?

Thank you
olivier

[sqlite] Using sqlite3.exe as a subprocess

[Matthew Allen]

It seems that sqlite3.exe (console) doesn't work as a subprocess with pipes.

I've tried it with both C++ code calling the CreateProcessW win32 API and
with python and both resulted in the same behaviour. Which is the
sub-process doesn't return anything when I try and read it's output (just
hangs). I'm expecting the signon and prompt messages to be printed via
stdout and readable by the parent process. Both C++ + python work fine with
other binaries (cmd.exe for instance). So I very much doubt it's my code or
environment (Win7).

Here is my python code:

import os
import sys
import subprocess

if 0:
p = subprocess.Popen(["C:\\Windows\\system32\\cmd.exe"],
stdout=subprocess.PIPE)
else:
p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"],
stdout=subprocess.PIPE)

if p is None:
print "Error creating process."
else:
while p.poll() == None:
resp = p.communicate()
print len(resp[0]), resp[0]



I expect there is something funny going on with sqlite3.exe's stdout/stdin.
But I'm not immediately clear what that is looking at it's code. Why
doesn't it work like other console processes?

The problem I'm trying to solve is:
When my application that uses an sqlite3 database gets the "database disk
image is malformed" I need to be able to give the user a "repair" option
which dumps the datrabase to an .sql file and reimport it all. I'm assuming
the best way is to do that via the shell rather than try and copy all the
dump code into my own application.

Based on
http://froebe.net/blog/2015/05/27/error-sqlite-database-is-malformed-solved/

Regards
Matthew

[sqlite] Using sqlite3.exe as a subprocess

[Dominique Devienne]

On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen  wrote:

> It seems that sqlite3.exe (console) doesn't work as a subprocess with
> pipes.
> [...] I expect there is something funny going on with sqlite3.exe's
> stdout/stdin.


Sorry to highjack your thread Matthew, but I have what I consider a related
use case.

I'd like to embed the SQLite3 shell into another program, both a console
program and a gui one,
and because I'd like it to access in-memory databases, this cannot be done
via forking and pipes.

Basically I'd like the shell to have a "Virtual Console Interface" (VCI),
to be able to reuse all the shell's
goodness, in client apps, w/o having to hack and duplicate the shell's
code. With some way to access
in-memory databases in the same process as well (a special form of attach
or an API?).

I realize the shell is not meant and designed to be embedded right now,
only the library is,
but I'd really like it to be, basically. My own 2016 wishful-thinking
feature request :). --DD

[sqlite] Database is locked

[Werner Kleiner]

Hello Richard,
I am not a SQLITE expert.
As I understand WAL it is especially made for transactions?
But our application do not have transactions, just normal SQL queries like
Select, Insert and Updates.

So does WAL makes sense for that?



2016-01-14 14:20 GMT+01:00 Richard Hipp :

> On 1/14/16, Werner Kleiner  wrote:
> > Hello
> > I have written a small Wep application with PHP (PDO Apache,). This web
> app
> > uses a sqlite db3 database. Also there is a Windows application which
> uses
> > the same database file.
> > Now we heard one time that there war a problem and in a log file we saw
> the
> > error code
> > "Database is locked"
> > My question now is:
> > Could this be a problem when Apache or PHP uses same database and also a
> > windows application?
> > For example if PHP writes to table "users" and windows application reads
> > from "users" at same time?
>
> Set WAL-mode to work around that.  https://www.sqlite.org/wal.html
>
> Also set a busy timeout using "PRAGMA busy_timeout=1;" or similar
> (https://www.sqlite.org/pragma.html#pragma_busy_timeout).
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[James K. Lowden]

On Thu, 14 Jan 2016 16:54:04 +0100
Olivier Mascia  wrote:

> Let a transaction (started with BEGIN TRANSACTION) which did only
> reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK
> TRANSACTION, or is it completely insignificant?

Why not do the right thing and remove the begin & commit transaction
statements?  

An explicit transaction implies a unit of work, two or more changes to
the database that must cohere for the database be consistent.
Read-only operations (by which I suppose you mean SELECT) don't modify
the database and don't belong in a transaction.  

--jkl

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Olivier Mascia]

> Le 15 janv. 2016 ? 11:54, Olivier Mascia  a ?crit :
> 
> What are the circumstances leading to:
> 
> int status = sqlite3_wal_checkpoint_v2(conn, "main", 
> SQLITE_CHECKPOINT_PASSIVE, 0, 0);
> 
> returning SQLITE_LOCKED immediately?
> 
> 
> It looks like that the simple fact of having a select statement prepared, 
> then ran, but not yet finalized, on that same connection, induces the 
> SQLITE_LOCKED return. I can get it to go away by simply finalizing the last 
> statement before trying checkpoint. I just did not expected LOCKED to be 
> returned. Reading https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I 
> expected to get SQLITE_BUSY. My misunderstanding probably.

I probably should have been more descriptive about the conditions:

For the purpose of the test, there are some (at least one) transactions 
committed waiting to be checkpointed and those transactions did inserts on a 
table, which is the one selected (outside of any explicit transaction).

In pseudo code, this means:

With one statement handle:
begin transaction
insert into T(C) values(xyz)
commit transaction

With the same statement handle:
begin transaction
select ... from T
commit transaction

Again with the same statement handle:
select ... from T

(*)

And finally:
sqlite3_wal_checkpoint_v2 as above, returning SQLITE_LOCKED.

Merely finalizing the statement at point (*) right after the (out of explicit 
transaction) select and the wal_checkpoint returns SQLITE_OK.

Is this perfectly expected?
In a somehow more complex configuration where multiple threads or processes 
(all using distinct connections and private cache) could be running quite any 
query at any time, it could become quite hard to find an appropriate slot of 
time to call sqlite3_wal_chekcpoint not returning LOCKED.
Should it be valuable to use the sqlite3_unlock_notify mechanism on attempts to 
wal_checkpoint returning SQLITE_LOCKED? (I understand that in this simple test, 
if the mere un-finalized statement is the reason of the LOCKED, it won't help, 
but I'm thinking more general in a multi-consumer processes or threads model.)

It might very well be much easier then to leave it to the automated 
checkpointing mode of SQLite, eventually changing its default of 1000 pages 
before trigger, if it better fit my needs.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

[sqlite] Using sqlite3.exe as a subprocess

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 14/01/16 19:53, Matthew Allen wrote:
> It seems that sqlite3.exe (console) doesn't work as a subprocess
> with pipes.

There is a bit of a problem with using apps via pipes.  Generally when
stdout is a terminal, output will be line buffered (ie you get each
line from printf as \n is encountered).  However when output is not a
terminal then other buffering kicks in.  For example it may be in
blocks of 4kb, so you'll only see something every time that much has
been generated.

The Windows standard library is even a bit stranger when not connected
to a terminal.  For the first 512 bytes of output it will send them
immediately, and then switch to block buffers.

There are solutions available to try and "trick" the apps to believing
they are outputting to a terminal, when it is in fact a pipe.  However
you won't need them (but shoutout to Expect - a populariser of TCL).

> while p.poll() == None: resp = p.communicate() print len(resp[0]),
> resp[0]

That code doesn't make sense.  communicate waits until the process
terminates.  The SQLite shell won't terminate unless it gets a quit
command, or EOF on stdin.

> The problem I'm trying to solve is: When my application that uses
> an sqlite3 database gets the "database disk image is malformed" I
> need to be able to give the user a "repair" option which dumps the
> datrabase to an .sql file and reimport it all. I'm assuming the
> best way is to do that via the shell rather than try and copy all
> the dump code into my own application.

Good news - here is a shell in Python I already made for you:

  https://rogerbinns.github.io/apsw/shell.html#shell-class
  https://github.com/rogerbinns/apsw/blob/master/tools/shell.py

It does require APSW for the database access, as the standard sqlite3
module lacks various functionality.

  https://rogerbinns.github.io/apsw/pysqlite.html

You can add your own repair command based on the existing dump
command.  This shell aborts on error. The way the standard SQLite
shell handles errors (IIRC) is to scan a table forwards (rowid order),
and then on encountering the error scans backwards.  This is a best
effort, but doesn't mean you won't lose lots of data!

However I'd recommend you use the backup api and make periodic copies
of the database that way.  You can then offer going back to a previous
snapshot.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlaZU68ACgkQmOOfHg372QTvegCgpF/pck6KCjdOqDKhxl5XEyuA
cFYAoMdJwpDo5Pwg2uRr/RbNYmEhtdz1
=AR0i
-END PGP SIGNATURE-

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Hick Gunter]

I think you may mean "database connection" instead of "statement handle".

When you are finished with processing a statement, you should either reset (if 
you intead to use it again later) or finalize it. Otherwise SQLite must assume 
that you want to continue later and needs to keep around whatever locks it 
acquired during processing. Which is also the cause for sqlite3_wal_checkpoint 
returning SQLITE_LOCKED.

You should not be reusing a statement handle before calling finalize on it, as 
this would cause a memory leak.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Olivier 
Mascia
Gesendet: Freitag, 15. J?nner 2016 12:18
An: SQLite mailing list
Betreff: Re: [sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in 
what circumstances?

> Le 15 janv. 2016 ? 11:54, Olivier Mascia  a ?crit :
>
> What are the circumstances leading to:
>
> int status = sqlite3_wal_checkpoint_v2(conn, "main",
> SQLITE_CHECKPOINT_PASSIVE, 0, 0);
>
> returning SQLITE_LOCKED immediately?
>
>
> It looks like that the simple fact of having a select statement prepared, 
> then ran, but not yet finalized, on that same connection, induces the 
> SQLITE_LOCKED return. I can get it to go away by simply finalizing the last 
> statement before trying checkpoint. I just did not expected LOCKED to be 
> returned. Reading https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I 
> expected to get SQLITE_BUSY. My misunderstanding probably.

I probably should have been more descriptive about the conditions:

For the purpose of the test, there are some (at least one) transactions 
committed waiting to be checkpointed and those transactions did inserts on a 
table, which is the one selected (outside of any explicit transaction).

In pseudo code, this means:

With one statement handle:
begin transaction
insert into T(C) values(xyz)
commit transaction

With the same statement handle:
begin transaction
select ... from T
commit transaction

Again with the same statement handle:
select ... from T

(*)

And finally:
sqlite3_wal_checkpoint_v2 as above, returning SQLITE_LOCKED.

Merely finalizing the statement at point (*) right after the (out of explicit 
transaction) select and the wal_checkpoint returns SQLITE_OK.

Is this perfectly expected?
In a somehow more complex configuration where multiple threads or processes 
(all using distinct connections and private cache) could be running quite any 
query at any time, it could become quite hard to find an appropriate slot of 
time to call sqlite3_wal_chekcpoint not returning LOCKED.
Should it be valuable to use the sqlite3_unlock_notify mechanism on attempts to 
wal_checkpoint returning SQLITE_LOCKED? (I understand that in this simple test, 
if the mere un-finalized statement is the reason of the LOCKED, it won't help, 
but I'm thinking more general in a multi-consumer processes or threads model.)

It might very well be much easier then to leave it to the automated 
checkpointing mode of SQLite, eventually changing its default of 1000 pages 
before trigger, if it better fit my needs.

--
Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, 
integral.be/om



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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Olivier Mascia]

Dear all,

What are the circumstances leading to:

int status = sqlite3_wal_checkpoint_v2(conn, "main", SQLITE_CHECKPOINT_PASSIVE, 
0, 0);

returning SQLITE_LOCKED immediately?


It looks like that the simple fact of having a select statement prepared, then 
ran, but not yet finalized, on that same connection, induces the SQLITE_LOCKED 
return. I can get it to go away by simply finalizing the last statement before 
trying checkpoint. I just did not expected LOCKED to be returned. Reading 
https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I expected to get 
SQLITE_BUSY. My misunderstanding probably.

I have no problem with that, I'm merely gathering knowledge of how to best use 
it.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/o=m

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Keith Medcalf]

On Friday, 15 January, 2016 11:14, James K. Lowden ,

> On Thu, 14 Jan 2016 16:54:04 +0100 Olivier Mascia  wrote:

> > Let a transaction (started with BEGIN TRANSACTION) which did only
> > reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK
> > TRANSACTION, or is it completely insignificant?

> Why not do the right thing and remove the begin & commit transaction
> statements?

> An explicit transaction implies a unit of work, two or more changes to
> the database that must cohere for the database be consistent.
> Read-only operations (by which I suppose you mean SELECT) don't modify
> the database and don't belong in a transaction.

While it is true that without further action each statement is carried out in a 
separate transaction, it may very well be that the OP wants to have what is 
called Repeatable-Read.  The only way to obtain Repeatable-Read between 
statements is to put the SELECTs in the *same* transaction, otherwise they will 
see different views of the database if there was an intervening update.  Once 
you put multiple SELECTs in the same transaction, they will ALL see the same 
database, unchanging, notwithstanding concurrent updates.

Of course, in SQLite the READ transaction will block all writers -- thus 
achieving stability.  If WAL is used then updates can run concurrently and the 
view of the database inside the BEGIN ... COMMIT containing only SELECTs will 
not see the updates committed in a different transaction until the after the 
commit.

[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

[Richard Hipp]

On 1/15/16, Olivier Mascia  wrote:
> But the finalize of a
> statement (using the C++ wrapper we build for our use over C SQLite API) is
> currently differed until another prepare is done using the same Statement
> object, or until the Statement object goes out of scope (destructor).

That's a problem.  Until a prepared statement is either finalized or
reset, SQLite must assume that you might call sqlite3_step() on it
again.  And so it has to continue holding various locks and other
state that will be needed by that subsequent sqlite3_step() call.

A better approach would be to invoke sqlite3_reset() as soon as you
know that the prepared statement will not be stepped again.  That will
release locks (and other resources) and help other operations to
proceed unimpeded.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Setting SQLITE_OMIT_FLOATING_POINT has surprising undocumented consequences

[Simon Davies]

On 14 January 2016 at 22:31, Warren Young  wrote:
> For no especially good reason, I decided to turn off all SQLite features I?m 
> not using now and which I have no plans to use in the future.
>
> My current DB doesn?t use any FP columns, so I rebuild SQLite with 
> SQLITE_OMIT_FLOATING_POINT and ran ran into a bunch of breakage:
>
> 1. The (double) cast on line 66942 of the current amalgamation in 
> valueFromExpr() produces a warning.  Most of that block probably should go 
> away if FP is disabled.

http://www.sqlite.org/compile.html#omitfeatures

SQLITE_OMIT_xxx options "may only be used when the library is built
from canonical source, not from the amalgamation"

Regards,
Simon

[sqlite] Database is locked

[Simon Slavin]

On 15 Jan 2016, at 7:51am, Werner Kleiner  wrote:

> Takes your suggestion with PDO setAttribute(PDO:: ATTR_TIMEOUT,  the same
> effect as Richards with PRAGMA busy_timeout?
> For example I do a:
> 
> $dbConnection =$db->query('PRAGMA busy_timeout=6') ;
> 
> instead of
> 
> $dbConnection->setAttribute(PDO:: ATTR_TIMEOUT, 60);
> 
> is this the same?

I haven't tested it (I use the sqlite3 library in PHP rather than PDO) but 
that's how I'd have implemented it had I written in.

I gave you the PDO-style solution because I guessed that if you've using PDO 
you probably want code which is useful in many databases, not just SQLite.

Simon.

[sqlite] Using sqlite3.exe as a subprocess

[Warren Young]

On Jan 14, 2016, at 8:53 PM, Matthew Allen  wrote:
> 
>p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"],
> stdout=subprocess.PIPE)

It looks like you?re trying to use both stdin and stdout, but you really only 
need stdout here, since sqlite3.exe will accept SQL or sqlite3 shell commands 
on its command line after the database argument.  You don?t need to feed it the 
commands over stdin.

In fact, you don?t even need to use stdout, if you?re willing to leave it all 
to the command shell:

   cmd.exe /c sqlite3 Database.sqlite .dump | sqlite3 NewDatabase.sqlite

Then replace Database.sqlite with NewDatabase.sqlite, optionally moving the 
former to a backup location first.

[sqlite] Using sqlite3.exe as a subprocess

[Richard Hipp]

On 1/14/16, Matthew Allen  wrote:
> It seems that sqlite3.exe (console) doesn't work as a subprocess with
> pipes.
>

Yeah it does.  The test suite does this, in the shellN.test test
scripts (N=1..5, ex:
https://www.sqlite.org/src/artifact/ce5e744870387164)  Those these are
written in TCL, not in Python, and TCL does go through a lot of
gyrations to make sure pipes work in a civilized manner.  So I'm not
saying that this is easy to pull off.  But it does work.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Setting SQLITE_OMIT_FLOATING_POINT has surprising undocumented consequences

[Warren Young]

On Jan 15, 2016, at 3:11 AM, Simon Davies  
wrote:
> 
> On 14 January 2016 at 22:31, Warren Young  wrote:
>> 
>> I rebuild SQLite with SQLITE_OMIT_FLOATING_POINT and ran ran into a bunch of 
>> breakage:
> 
> http://www.sqlite.org/compile.html#omitfeatures
> 
> SQLITE_OMIT_xxx options "may only be used when the library is built
> from canonical source, not from the amalgamation?

Fine:

fossil clone http://www.sqlite.org/cgi/src ~/museum/sqlite.fossil
mkdir -p ~/src/sqlite/head
cd ~/src/sqlite/head
fossil open ~/museum/sqlite.fossil
cp Makefile.linux-gcc Makefile
# edit TOP: the default should be ., not ../sqlite!
# edit OPTS: add -DSQLITE_OMIT_FLOATING_POINT
rm -f sqlite3.c# just to be sure
make

?and the thrust of what I wrote remains true: there?s a bunch of code in SQLite 
that tries to use double even though this option is enabled.

It actually dies trying to build the shell:

./src/shell.c: In function ?timeOfDay?:
./src/shell.c:172:5: warning: passing argument 2 of ?clockVfs->xCurrentTime? 
from incompatible pointer type [enabled by default]
 clockVfs->xCurrentTime(clockVfs, );
 ^
./src/shell.c:172:5: note: expected ?sqlite3_int64 *? but argument is of type 
?double *?

Realize that I?m reporting this mainly as a heads-up to the developers, not 
because I particularly want this fixed.  Every FPU-less platform I?ve ever 
developed for was too small to run SQLite in the first place.  I was just 
trying to remove code I don?t use.

Given that, I see several options:

1. Ignore the known breakage.

2. Declare that SQLite now requires FP, and remove the option.

3. Rework it like I proposed in the previous post: one option for completely 
FP-free builds, and a separate one to just remove FP features from the 
supported SQL language: no REAL columns, no likelihood(), no round(), etc.

It?s the last option I actually wanted, because in my app, an FP literal must 
be a bug, so I?d prefer that SQLite refused to accept it.

It?s hard to imagine another SQLite data type that someone would want to treat 
that way, except possibly BLOB.  But FP?  There are *many* applications where 
FP is not only not used, but also not *wanted*.

[sqlite] Using sqlite3.exe as a subprocess

[Adam Devita]

Good day,

Assuming you don't want to alter the code of the shell tool to take a
named pipe (this isn't that difficult to do, unfortunately due to the
business logic I can't go into, it was not allowed):

Have you tried to create a command prompt shell, begin the sqlite
shell tool in that and direct IO to the shell?  There is an occasion
(the reasoning for which I will not go into) that we do this in c#.
Yours should be able to pull the same (or similar) trick in c++.
(You should get the gist from this)

 System.Diagnostics.Process pIOSql ;


  pIOSql = new System.Diagnostics.Process();
  pIOSql.StartInfo.CreateNoWindow = true;
  pIOSql.StartInfo.UseShellExecute = false;

 pIOSql.StartInfo.FileName = PathToDbDirectory + "sqlite3.exe";
 pIOSql.StartInfo.Arguments = "\""+PathToDbDirectory + "my.db\"";

 pIOSql.StartInfo.RedirectStandardError = true;
 pIOSql.StartInfo.RedirectStandardInput = true;
 pIOSql.StartInfo.RedirectStandardOutput = true;
 pIOSql.Start();
 pIOSql.StandardInput.WriteLine("select count(1) from
someTable;\n");
  }
.

  pIOSql.StandardOutput.DiscardBufferedData();
  StreamWriter sCmd = pIOSql.StandardInput;
  String sqlcmd = Command;
  sCmd.WriteLine(sqlcmd);

etc.

One has to do a bit of work to handle timing.  If you aren't worried
(at all) about security then you could even create a temp file, and
stick your queries into it, so you can redirect your output to another
file and funnel everything through .read
Be careful about empty set results!

regards,
Adam DeVita


On Fri, Jan 15, 2016 at 8:32 AM, Dominique Devienne  
wrote:
> On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen  wrote:
>
>> It seems that sqlite3.exe (console) doesn't work as a subprocess with
>> pipes.
>> [...] I expect there is something funny going on with sqlite3.exe's
>> stdout/stdin.
>
>
> Sorry to highjack your thread Matthew, but I have what I consider a related
> use case.
>
> I'd like to embed the SQLite3 shell into another program, both a console
> program and a gui one,
> and because I'd like it to access in-memory databases, this cannot be done
> via forking and pipes.
>
> Basically I'd like the shell to have a "Virtual Console Interface" (VCI),
> to be able to reuse all the shell's
> goodness, in client apps, w/o having to hack and duplicate the shell's
> code. With some way to access
> in-memory databases in the same process as well (a special form of attach
> or an API?).
>
> I realize the shell is not meant and designed to be embedded right now,
> only the library is,
> but I'd really like it to be, basically. My own 2016 wishful-thinking
> feature request :). --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] WAL: difference between IMMEDIATE and DEFERRED transaction

[Richard Hipp]

On 1/15/16, Olivier Vidal  wrote:
>
> Hello all,
>
> I would like to be sure I understand the difference between an IMMEDIATE
> transaction and a DEFERRED transaction, in WAL mode.

BEGIN IMMEDIATE claims a write lock on the database file immediately,
so that no other process or thread can come along and start a second
write transaction until after the one you just started finishes.

BEGIN DEFERRED (the default) waits for the first actual change to the
database (a subsequent DELETE, INSERT, or UPDATE) before acquiring the
write lock.  That means that even though the "BEGIN" was successful,
the next write operation might fail with an SQLITE_BUSY error if
another thread or process jumps in line ahead of you and acquires the
write lock.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Database is locked

[Werner Kleiner]

Thanks a lot Simon. Now I understand a little bit better.
Last question: (hopefully :-) )

Takes your suggestion with PDO setAttribute(PDO:: ATTR_TIMEOUT,  the same
effect as Richards with PRAGMA busy_timeout?
For example I do a:

$dbConnection =$db->query('PRAGMA busy_timeout=6') ;

instead of

$dbConnection->setAttribute(PDO:: ATTR_TIMEOUT, 60);

is this the same?

Werner



2016-01-14 16:00 GMT+01:00 Simon Slavin :

>
> On 14 Jan 2016, at 1:42pm, Werner Kleiner  wrote:
>
> > The windows application is written in C# and uses the
> sqlite.systemData.dll.
>
> I'm sure someone here can tell you how to set a timeout in that.
>
> > What does the timeout mean in detail for sqlite ?
> > Is this time (in your example 5 minutes) for each SQL query which is
> > executed?
>
> SQLite contains its own backoff-and-retry procedure for use when the
> database is locked.  By default the timeout value is 0 which means SQLite
> never gets to use it.  But instead you can set a timout value.
>
> Then if SQLite tries the command and finds that the database is locked it
> will sleep a while, try again, sleep a little longer, try again, sleep even
> longer, try again ...  and it will keep doing this until the timeout value
> has been reached.  Only if it is still failing at that time will SQLite
> return SQLITE_BUSY or SQLITE_LOCKED.  At that point the error is final, and
> there's no need to implement your own system for backoff-and-retry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked

[Richard Hipp]

On 1/15/16, Werner Kleiner  wrote:
> Hello Richard,
> I am not a SQLITE expert.
> As I understand WAL it is especially made for transactions?
> But our application do not have transactions, just normal SQL queries like
> Select, Insert and Updates.

Every "normal SQL query" is a transaction unto itself, unless you take
explicit steps to group two or more such queries together into a
single transaction.

So, if you are using SQLite, then you are using transactions.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] 答复: Best way to terminate a dead-transaction: commit or rollback?

[Quan Yong Zhai]

I prefer to COMMIT TRANSACTION, because
1. As a rule, all successfully executed transactions should be commit, 
otherwise should  be rollback.
2. To rollback a transaction with no  errors  occurred will  misguide  some 
developers.
3. if someone puts a writing statement or function in the " read only" 
transaction incidentally, there will be a difference, "ROLLBACK TRANSACTION"  
always return a  misguided SQLITE_OK, but "COMMIT" will inform you 
SQLITE_SNAPSHOT_BUSY or SQLITE_LOCK or something else additionally.

My 2 cents.

???: Olivier Mascia
: ?2016/?1/?14 23:54
???: SQLite mailing list
??: [sqlite] Best way to terminate a dead-transaction: commit or rollback?

Hello,

Let a transaction (started with BEGIN TRANSACTION) which did only reads.
Is it any better to end it by COMMIT TRANSACTION or ROLLBACK TRANSACTION, or is 
it completely insignificant?

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

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