Re: [sqlite] Prepared statement invariants

[James K. Lowden]

On Thu, 13 Jun 2013 21:27:33 -0400
Igor Tandetnik  wrote:

> On 6/13/2013 9:15 PM, Maxim Khitrov wrote:
> > This works and also triggers SQLITE_SCHEMA with v1 interface. I did
> > a few more tests and it looks like the schema changes are ignored
> > if the statement is in the middle of iteration. As you said, only
> > the first step after a prepare/reset causes the values to change.
> > Is that a safe assumption to make?
> 
> Well, that's how it works with the current implementation. Whether it 
> will work this way forever, I don't know.

Well, forever is a long time.  But at the very least I would hope we
can count on the number of columns being constant thoughout the life of
the transaction.  

SELECT is atomic.  Once executed (not merely prepared, but executed) it
must return consistent results until the last row is fetched or the
operation is otherwise completed.  

It's not that the schema changes are "ignored", exactly, but
"managed".  The DBMS is isolating the process from changes made by other
processes.   Well done, and may it be ever thus.  

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

Re: [sqlite] float to string conversion problem

[Kevin Benson]

On Fri, Jun 14, 2013 at 6:34 PM, Filipe Madureira <
filipe.madure...@sysdevsolutions.com> wrote:

> Hi,
>
> Thanks for the great help.
> I tracked the problem to et_getdigit()
> More precisely to:
> digit = (int)*val;
>
> So, SQLite is assuming that "double" to "int" cast will truncate the
> value. This should be true.
> But in this device this is not happening, so if "val" is 2.55 I get 3 in
> "digit".
>
> Anyone knows why this could happen?
>
> I found something that makes it work correctly.
> If I call (Visual Studio C++):
> _controlfp(_RC_CHOP , _MCW_RC);
>
> it works correctly. But I am a little bit out of my league here.
> Wont this possibly cause other problems somewhere else?
> Isn't the default setting with _RC_NEAR?
> (Note: This call is thread dependent)
>
>
Possibly a bug in Win CE or ARM ?
I came across the post [1] linked below from a QT-related  forum.
Interesting to note is that each of the values (greater than 1.0) whose
digits to the right of the decimal point (fractional part) were greater
than .5 have been returned as the next larger decimal (integer part):

14.5879 has become 15.
1.6189 has become 2.
64.8874 has become 65.

However, values (less than 1.0) whose digits to the right of the decimal
point (fractional part) were greater than .5 have been returned properly:

0.7997 has become .800

[1] http://qt-project.org/forums/viewthread/25060

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to interrupt a long running update without roll back?

[Simon Slavin]

On 14 Jun 2013, at 11:29pm, DL  wrote:

> UPDATE T set C1 = calculation(C2) where C1 is NULL
> If table is large, this update may take many seconds or even minutes.
> During this time all other queries on this table fail with database is 
> locked
> after connection timeout expires (currently my timeout is 5 seconds).

Do you have an index on T(C1) ?  That should dramatically reduce the search 
time.

> I would like to stop this update query after, say, 3 seconds,
> then restart it. Hopefully, after several restarts entire table will be 
> updated.
> Another option is to stop this update query before making any other request
> (this will require inter-process cooperation, but it may be doable).

Well, you could increase your timeout:




which will fix the problem one way.  Another way would be to use the LIMIT 
clause on UPDATE, if it’s available to you:



Do an UPDATE LIMIT 1, then check the total_changes() function



and if it’s not zero pause a while for your other processes to do their thing, 
then do another UPDATE LIMIT 1.

If compiling your own SQLite is a problem for you, step through your big table 
a section at a time:

UPDATE T SET C1 = calculation(C2) WHERE C1 IS NULL AND rowid < 1

then pause for a while, then do it again but this time up to row 2, etc..

Simon.


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

Re: [sqlite] float to string conversion problem

[Filipe Madureira]

Hi,

Thanks for the great help.
I tracked the problem to et_getdigit()
More precisely to:
digit = (int)*val;

So, SQLite is assuming that "double" to "int" cast will truncate the 
value. This should be true.
But in this device this is not happening, so if "val" is 2.55 I get 3 in 
"digit".


Anyone knows why this could happen?

I found something that makes it work correctly.
If I call (Visual Studio C++):
_controlfp(_RC_CHOP , _MCW_RC);

it works correctly. But I am a little bit out of my league here.
Wont this possibly cause other problems somewhere else?
Isn't the default setting with _RC_NEAR?
(Note: This call is thread dependent)


Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
-

>Floating point to ASCII conversion happens here:
>
>   http://www.sqlite.org/src/artifact/bff529ed476?ln=406-553
>
>
>
>--
>D. Richard Hipp
>drh at sqlite.org


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

[sqlite] How to interrupt a long running update without roll back?

[DL]

Hi,
I have a long running multirow update such as:
UPDATE T set C1 = calculation(C2) where C1 is NULL
If table is large, this update may take many seconds or even minutes.
During this time all other queries on this table fail with database is 
locked
after connection timeout expires (currently my timeout is 5 seconds).

I would like to stop this update query after, say, 3 seconds,
then restart it. Hopefully, after several restarts entire table will be updated.
Another option is to stop this update query before making any other request
(this will require inter-process cooperation, but it may be doable).

But I cannot find a way to stop update query without rolling back
all previously updated records.
I tried calling interrupt and returning non-0 from progress_handler.
Both these approaches abort the update command
and roll back all the changes ( I know, this behavior is documented).

If interrupt and progress_handler cannot help me, what else I can do?

I also tried UPDATE with LIMIT and also WHERE custom_condition(C1).
These approaches do allow me to terminate update earlier,
but they are significantly slower than regular update
and they cannot terminate the query at specific time
(before another connection timeout expires).

Any other ideas?
Thank you
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: add support for COMMENT statement

[Roger]

Would this not work for you?


http://stackoverflow.com/questions/7426205/sqlite-adding-comments-to-tables-and-columns



.schema
On 13-06-14 11:00 AM, Dave Wellman wrote:

Thanks Clemens, that is probably a workable option (at least for me).

As someone else noted, the PRAGMA user_version will not work for us as it is
one value per db file and we want to set this per table.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: 14 June 2013 15:21
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Feature request: add support for COMMENT statement

Alexey Pechnikov wrote:

It's very important to have place to store table metainformation. In
all common DBMSs we can use TABLE/COLUMN COMMENT as meta description
of table but SQLite doesn't support it.

SQLite saves comments in table/view/index/trigger definitions:

sqlite> create table t(x /* :-) */);
sqlite> .schema
CREATE TABLE t(x /* :-) */);


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

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



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

Re: [sqlite] Prepared statement invariants

[Nico Williams]

IMO you should provide a function to invalidate the cache and also one
to check the schema version number, then leave it to the application
to decide when or if to bother with this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statement invariants

[Maxim Khitrov]

On Fri, Jun 14, 2013 at 12:56 PM, Maxim Khitrov  wrote:
> On Thu, Jun 13, 2013 at 9:27 PM, Igor Tandetnik  wrote:
>> On 6/13/2013 9:15 PM, Maxim Khitrov wrote:
>>>
>>> This works and also triggers SQLITE_SCHEMA with v1 interface. I did a
>>> few more tests and it looks like the schema changes are ignored if the
>>> statement is in the middle of iteration. As you said, only the first
>>> step after a prepare/reset causes the values to change. Is that a safe
>>> assumption to make?
>>
>>
>> Well, that's how it works with the current implementation. Whether it will
>> work this way forever, I don't know.
>
> Understood, thanks! I was just looking through the SQLite source and
> it looks like sqlite3_expired would give me the information that I
> need, but it's marked as deprecated. Is it unsafe to use or just no
> longer necessary due to automatic recompilation?

Never mind. I dug deeper into code and realized that sqlite3_expired
only tells me about database changes from the same process. The
statement could be invalidated after comparing the in-memory schema
with what's on disk. I have no way of learning about that before the
first step call and don't get any indication that recompilation
happened after.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statement invariants

[Maxim Khitrov]

On Fri, Jun 14, 2013 at 1:16 PM, Simon Slavin  wrote:
>
>
> On 14 Jun 2013, at 5:56pm, Maxim Khitrov  wrote:
>
>> Unconditionally invalidating the cache after each initial call to step
>> will add a bit of overhead when repopulating the values.
>
> Do you think that every single user of SQLite does this ?  Have you ever seen 
> it included in the source code of any application that uses SQLite ?
>
> I don’t know quite what you’re worried about.  Do you actually expect your 
> schema to be changed when you’re in the middle of a SELECT ?  And don’t you 
> think that if the schema did change it would be better for your application 
> to show the user an error code so your user would realise that the database 
> they were consulting was now holding different kinds of data ?

There is no application. I'm writing SQLite bindings for Go [1].
Calling into C is expensive, as is the conversion from C to Go
strings. I don't load any column metadata initially, but if the user
asks for it, then I'd rather cache what I can and return it quickly
the second time around. The current implementation assumes that column
counts, names, and declarations never change, which I realized was
incorrect.

I'm trying to find the optimum way of invalidating the cache precisely
because it is rare for the schema to change. After the initial load,
the cache will likely stay valid for the lifetime of the statement.
But if recompilation does happen, I need a cheap way to detect this.

[1] http://code.google.com/p/go-sqlite/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statement invariants

[Simon Slavin]

On 14 Jun 2013, at 5:56pm, Maxim Khitrov  wrote:

> Unconditionally invalidating the cache after each initial call to step
> will add a bit of overhead when repopulating the values.

Do you think that every single user of SQLite does this ?  Have you ever seen 
it included in the source code of any application that uses SQLite ?

I don’t know quite what you’re worried about.  Do you actually expect your 
schema to be changed when you’re in the middle of a SELECT ?  And don’t you 
think that if the schema did change it would be better for your application to 
show the user an error code so your user would realise that the database they 
were consulting was now holding different kinds of data ?

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

Re: [sqlite] Pager bugs(?)

[Dan Kennedy]

On 06/14/2013 10:55 PM, Jan Slodicka wrote:

(Applies to SQLITE 3.7.15.2 (but v3.7.17.0 uses the same code))

Pager bug(?):

Following code excerpt from sqlite3.c should crash if pPage==NULL &&
createFlag==0:

static sqlite3_pcache_page *pcache1Fetch(
   sqlite3_pcache *p,
   unsigned int iKey,
   int createFlag
){
{
   ...
   if( pPage || createFlag==0 ){
 pcache1PinPage(pPage);
 goto fetch_out;
   }
   ...

fetch_out:
   ...
   return >page;
}

Note:
The funny thing is that when I debugged (VS 2010) the return statement for
pPage=NULL, there was no crash and the routine returned NULL value to the
caller.
In other words a correct return instead of crash. (Probably due to page
structure definition, where the member page is located at the offset 0.)


I don't think it would actually matter. The expression in the return
statement is only doing pointer arithmetic, not actually dereferencing
any pointers. Similar to:

 return (sqlite4_pcache_page *)((char *)pPage + offsetof(PgHdr1, page));

My guess is that whoever wrote that line thought it slightly less
error prone than the equivalent:

 return (sqlite4_pcache_page *)pPage;

Dan.




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

Re: [sqlite] Feature request: add support for COMMENT statement

[Igor Tandetnik]

On 6/14/2013 10:17 AM, Finn Wilcox wrote:

Yes but it is defined once-per-file instead of once-per-table.


"we've found that the use of a single place to store our version number 
makes the checking much easier."

--
Igor Tandetnik

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

Re: [sqlite] Prepared statement invariants

[Maxim Khitrov]

On Thu, Jun 13, 2013 at 9:27 PM, Igor Tandetnik  wrote:
> On 6/13/2013 9:15 PM, Maxim Khitrov wrote:
>>
>> This works and also triggers SQLITE_SCHEMA with v1 interface. I did a
>> few more tests and it looks like the schema changes are ignored if the
>> statement is in the middle of iteration. As you said, only the first
>> step after a prepare/reset causes the values to change. Is that a safe
>> assumption to make?
>
>
> Well, that's how it works with the current implementation. Whether it will
> work this way forever, I don't know.

Understood, thanks! I was just looking through the SQLite source and
it looks like sqlite3_expired would give me the information that I
need, but it's marked as deprecated. Is it unsafe to use or just no
longer necessary due to automatic recompilation?

Unconditionally invalidating the cache after each initial call to step
will add a bit of overhead when repopulating the values. If I could do
a simple if (sqlite3_expired(stmt)) {...} check instead, that would be
better.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite security

[Igor Tandetnik]

On 6/14/2013 11:42 AM, Keith Medcalf wrote:

You do not need to overwrite system DLLs (which would be detectable).  You 
merely need to inject/change code on a loaded shared code segment.  Granted, 
you still need a privilege escalation exploit to be able to do so initially.


Yes, once you got root or Administrator, you own the system and can mess 
with it in all kinds of creative ways. It's that little insignificant 
detail of "privilege escalation exploit to be able to do so initially" 
that we are discussing. You make it sound like those are trivial to come by.

--
Igor Tandetnik

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

[sqlite] Pager bugs(?)

[Jan Slodicka]

(Applies to SQLITE 3.7.15.2 (but v3.7.17.0 uses the same code))

Pager bug(?):

Following code excerpt from sqlite3.c should crash if pPage==NULL &&
createFlag==0:

static sqlite3_pcache_page *pcache1Fetch(
  sqlite3_pcache *p,
  unsigned int iKey,
  int createFlag
){
{
  ...
  if( pPage || createFlag==0 ){
pcache1PinPage(pPage);
goto fetch_out;
  }
  ...

fetch_out:
  ...
  return >page;
}

Note:
The funny thing is that when I debugged (VS 2010) the return statement for
pPage=NULL, there was no crash and the routine returned NULL value to the
caller.
In other words a correct return instead of crash. (Probably due to page
structure definition, where the member page is located at the offset 0.)

---

2nd problem:
#ifdef SQLITE_LOG_CACHE_SPILL
  // this code refers sqlite3GlobalConfig.pcache
  // instead of sqlite3GlobalConfig.pcache2

#endif


---

A suggestion:

sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW...) ... reported by sqlite3
shell as "Number of Pcache Overflow Bytes".

I find the term "overflow" highly confusing in this context as this number
normally refers to the standard memory allocated by the page cache. (2000
pages by default.)

Please, consider using less confusing wording at least in sqlite shell.

Best regards,
Jan Slodicka


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

Re: [sqlite] sqlite security

[Keith Medcalf]

You do not need to overwrite system DLLs (which would be detectable).  You 
merely need to inject/change code on a loaded shared code segment.  Granted, 
you still need a privilege escalation exploit to be able to do so initially.  
This is not a common exploit because, while not particularly difficult 
(assuming you have a valid initial escalation method), it is not needed by the 
majority of miscreants who are merely trying to sell more Viagra or perform 
other trivial exploits such as blast spam or steal banking credentials -- the 
sort of people who write malicious code that anti-virus and IDS are designed to 
protect against and is very quickly upon arrival on a system because it is 
usually pretty badly written, obnoxious, noisy and creates -- as you so 
colourfully describe -- a shambles.

Using terminology such as "security barrier" indicates a deeper interest in the 
security of the system than merely protecting against such pests.

People who write such exploits are not in the business of creating "shambles", 
and they are usually not detected for years or decades.  Lately the detection 
response of these sorts of hackers has become one of scorched-earth, leaving 
the victim with nothing against which to perform forensic analysis and 
completely preventing the mounting of any sort of future defense, or discovery 
of what was stolen or modified.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, 14 June, 2013 07:56
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite security
> 
> On 6/14/2013 9:44 AM, Keith Medcalf wrote:
> > Some Operating Systems (such as any version of Microsoft Windows) cannot
> be protected from these sorts of attacks, so if you are running Windows,
> then you probability of compromise is 100%, and the estimated lifetime of
> your "security barrier" is zero.
> 
> Citation needed. A low-privilege process cannot overwrite system DLLs on
> Windows. How come microsoft.com and other Microsoft's web properties are
> not lying in shambles? Windows is not nearly as bad as you make it out
> to be.
> --
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Feature request: add support for COMMENT statement

[Dave Wellman]

Thanks Clemens, that is probably a workable option (at least for me).

As someone else noted, the PRAGMA user_version will not work for us as it is
one value per db file and we want to set this per table.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: 14 June 2013 15:21
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Feature request: add support for COMMENT statement

Alexey Pechnikov wrote:
> It's very important to have place to store table metainformation. In 
> all common DBMSs we can use TABLE/COLUMN COMMENT as meta description 
> of table but SQLite doesn't support it.

SQLite saves comments in table/view/index/trigger definitions:

sqlite> create table t(x /* :-) */);
sqlite> .schema
CREATE TABLE t(x /* :-) */);


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

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

Re: [sqlite] Feature request: add support for COMMENT statement

[Clemens Ladisch]

Alexey Pechnikov wrote:
> It's very important to have place to store table metainformation. In all
> common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table
> but SQLite doesn't support it.

SQLite saves comments in table/view/index/trigger definitions:

sqlite> create table t(x /* :-) */);
sqlite> .schema
CREATE TABLE t(x /* :-) */);


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

Re: [sqlite] Feature request: add support for COMMENT statement

[Finn Wilcox]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 14/06/2013 15:03, Igor Tandetnik wrote:
> On 6/14/2013 9:59 AM, Dave Wellman wrote:
>> We use a COMMENT to store information about the version of our
>> tables that are in place on the customer system.
> 
> PRAGMA user_version is intended for this very purpose.
Yes but it is defined once-per-file instead of once-per-table.
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.18 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRuyYWAAoJEHp3pOlF38H/nQIH/1mfVa/3AMGhYbXhBLaGtxX3
ElWz8lRdPAygQNB3XvhU7Xh9z6u5ZqnMv9ovsI18b/l6w7XzlezP7EoMuwQETzgY
ynBiGO2i4bb5Z1Ew9SMJIMYBfFmoCDF01achQllwnhBDDnjb0Q4pAyqPX4bMKqxf
9qLmymCFeXzghyw/LCYCfkxY+oo3AeY7dzDPGhFy6s7q6viV9sDis4JdI2AxAOf1
UTrta1Z5GvtKefD/iX0u0bYMylMd1LkQWuXqHJpSv0QS1yQaV5yNkq29ssV5ea6u
RAse/bEOAj+5+/joYm6Aa5v2w1Z6rP3wb1FeV407hVUjMtcx/44j+/SEQrC5GJQ=
=8CoS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: add support for COMMENT statement

[Igor Tandetnik]

On 6/14/2013 9:59 AM, Dave Wellman wrote:

We use a COMMENT to store information about the version of our tables that
are in place on the customer system.


PRAGMA user_version is intended for this very purpose.
--
Igor Tandetnik

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

Re: [sqlite] Feature request: add support for COMMENT statement

[Dave Wellman]

I'd just like to add my penny's worth to this discussion.

We use a COMMENT to store information about the version of our tables that
are in place on the customer system.

Assume that we start with 'v1' of our tables that have 3 columns.

For a variety of reasons we might add a 4th column in 'v2'. Most of the time
we do not want to force our customers to upgrade their table definitions to
'v2' just so they can run the new version of our products. This means that
our new code has to cater for both 'v1' and 'v2' table definitions. A simple
way of doing this is to return the COMMENT which contain our 'version
number'.

Yes, there are other ways of doing this. But with a myriad of changes
possible (indexes, column definitions, triggers etc) we've found that the
use of a single place to store our version number makes the checking much
easier.

Regards,
Dave



Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: 14 June 2013 14:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Feature request: add support for COMMENT statement

On 6/13/2013 10:23 AM, Alexey Pechnikov wrote:
> It's very important to have place to store table metainformation.

You are the first person in years who asked for it, so it's probably not
*that* important.

> In all
> common DBMSs we can use TABLE/COLUMN COMMENT as meta description of 
> table but SQLite doesn't support it.

Suppose you have it. What would you do with it? What's the use case?
--
Igor Tandetnik

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

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

Re: [sqlite] sqlite security

[Igor Tandetnik]

On 6/14/2013 9:44 AM, Keith Medcalf wrote:

Some Operating Systems (such as any version of Microsoft Windows) cannot be protected 
from these sorts of attacks, so if you are running Windows, then you probability of 
compromise is 100%, and the estimated lifetime of your "security barrier" is 
zero.


Citation needed. A low-privilege process cannot overwrite system DLLs on 
Windows. How come microsoft.com and other Microsoft's web properties are 
not lying in shambles? Windows is not nearly as bad as you make it out 
to be.

--
Igor Tandetnik

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

Re: [sqlite] sqlite security

[Keith Medcalf]

You have far more security vulnerabilities inherent in the (quite often 
mis-)design of the operating system, development tools and libraries; and 
idiotic decisions made by application designers themselves.  You don't want to 
put the cart in front of the horse.  Compromise in your (briefly stated) 
scenario is most likely via the vector of code injection into shared load 
library text (code) segments, including injection directly in the Operating 
System discontiguous saved segment itself.  Some Operating Systems (such as any 
version of Microsoft Windows) cannot be protected from these sorts of attacks, 
so if you are running Windows, then you probability of compromise is 100%, and 
the estimated lifetime of your "security barrier" is zero.  Your only 
mitigation measures are external.

Other Operating Systems have similar vulnerabilities that are varyingly more 
difficult to exploit.

Your question has nothing to do with SQLite but rather requires addressing the 
inherent design of your chosen Operating System, your Application, and the 
toolchains you choose to use to convert the human-readable expressions thereof 
into code executable in silicon.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Toby Dickenson
> Sent: Friday, 14 June, 2013 04:18
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite security
> 
> Hi all,
> 
> I have a question about security considerations for using sqlite.
> 
> Suppose I have two processes which communicate via a shared database.
> One process is internet-facing, and therefore carries a risk of being
> compromised. The second process is running under a different uid, and
> has access to other files which should be kept private. The database
> is a trust boundary.
> 
> To what extent is this IPC mechanism a risk of privilege escalation,
> whereby any malicious code injected into the first process might be
> able to use the shared database to attack the second process.
> Obviously there is a need for both applications to handle the data
> retrieved from that database in a secure manner, but are there other
> risks/considerations from the sqlite library itself?
> 
> There are some obvious and maybe unavoidable denial-of-service risks:
> the first process might fill up the disk, or (Im guessing here) hold
> onto locks for too long. Any other considerations?
> 
> Thanks in advance,
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Feature request: add support for COMMENT statement

[Igor Tandetnik]

On 6/13/2013 10:23 AM, Alexey Pechnikov wrote:

It's very important to have place to store table metainformation.


You are the first person in years who asked for it, so it's probably not 
*that* important.



In all
common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table
but SQLite doesn't support it.


Suppose you have it. What would you do with it? What's the use case?
--
Igor Tandetnik

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

Re: [sqlite] sqlite security

[Igor Tandetnik]

On 6/14/2013 7:39 AM, Simon Slavin wrote:


On 14 Jun 2013, at 11:18am, Toby Dickenson  wrote:

To what extent is this IPC mechanism a risk of privilege escalation,
whereby any malicious code injected into the first process might be
able to use the shared database to attack the second process.


The SQLite library does not store low-level executable code in any database 
file, nor does it execute any code found in a database file.  Putting 
executable code in a SQLite database (presumably as a BLOB) does nothing unless 
you have specially written your program to retrieve a BLOB, put it into memory 
and execute it.  Don’t do that.  Unless you have a good reason to.


I'd be thinking of a different attack. Imagine there's a bug in SQLite 
library itself - say, a buffer overrun - that manifests when handling a 
malformed database file. The compromised process wouldn't use SQLite to 
manipulate the database (create triggers or what not) but would write to 
the file directly to craft a malformed database file specifically 
designed to exploit that bug. When the second process opens the 
database, it would trigger the exploit, whose effects would range from 
denial of service at best (the second process simply crashes) to 
arbitrary code execution/privilege escalation at worst.


And of course, the compromised process could simply wipe the database 
(data loss, denial of service), write wrong data to the database (data 
poisoning), and/or send it to some third party (information disclosure).

--
Igor Tandetnik

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

Re: [sqlite] Multiprocess accessing SQLite connection

[Richard Hipp]

On Fri, Jun 14, 2013 at 9:10 AM, Vijay Khurdiya <
vijay.khurd...@securetogether.com> wrote:

> Please confirm below statement is TRUE when Sqlit3 configure in thread
> safe mode. (I am checking for Serialized)
>
> "Multiple processes can access same database connection"?
>

False.

A "database connection" is an in-memory object that communicates with an
SQLite database file.  The operating system prevents multiple processes
from accessing the same memory, so it is not possible for multiple
processes to access the same database connection.

Perhaps you meant to ask if multiple processes could access the same SQLite
database file at the same time.  The answer to that question is "yes".

An SQLite "database connection" is analogous to the FILE* handle of
fopen().  You can fopen() the same file at the same time from multiple
processes.  Each process has its own private FILE* handle, but all FILE*
handles point to the same file on disk.  In the same say, each process
using sqlite3_open() will have its own database connection, but all the
database connections will be accessing the same database file.

Don't take this analogy too far, however.  When multiple processes open the
same file using fopen(), they can overwrite one another and cause all kinds
of mischief.  But SQLite employs file locking to prevent writes by one
process from interfering with the other processes and to insure that the
database stays consistent, even if some of the connecting processes
misbehave or crash.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiprocess accessing SQLite connection

[Vijay Khurdiya]

Please confirm below statement is TRUE when Sqlit3 configure in thread safe 
mode. (I am checking for Serialized)

"Multiple processes can access same database connection"?
Bye -
This e-mail and any files transmitted with it are for the sole use of the 
intended recipient(s) and may contain confidential and privileged information. 
If you are not the intended recipient, please contact the sender by reply 
e-mail and destroy all copies and the original message. Any unauthorized 
review, use, disclosure, dissemination, forwarding, printing or copying of this 
email or any action taken in reliance on this e-mail is strictly prohibited and 
may be unlawful. The recipient acknowledges that Secure Meters Limited or its 
subsidiaries and associated companies(collectively "Secure Meters Limited"),are 
unable to exercise control or ensure or guarantee the integrity of/over the 
contents of the information contained in e-mail transmissions and further 
acknowledges that any views expressed in this message are those of the 
individual sender and no binding nature of the message shall be implied or 
assumed unless the sender does so expressly with due authority of Secure Meters 
Limi
 ted. Before opening any attachments please check them for viruses and defects. 
In case you have any problem or issue with the E-mails from Secure Meters 
Limited Please do lets us know on netad...@securetogether.com Secure Meters 
Limited- Udaipur- Rajasthan -313001
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float to string conversion problem

[Arjen Markus]

Hi Richard,

On Fri, 14 Jun 2013 08:26:19 -0400
 Richard Hipp  wrote:



SQLite has its own printf() implementation.  It has to. 
If it used system
printf(), than certain LOCALE settings would turn "." 
into "," and

introduce syntax errors.

The build-in printf() of SQLite also introduces a number 
of useful new
formatting options, such as %q, %Q, %w, and %z.  Those 
extensions (and
others) are widely used internally by SQLite, so at this 
point it would be

a VERY big task to convert to system printf().



Yes, I saw your message, just after I pressed the send 
button.

So, this has nothing to do with some quirk of the system's
printf() family.

Regards,

Arjen



DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




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

Re: [sqlite] float to string conversion problem

[Richard Hipp]

On Fri, Jun 14, 2013 at 8:19 AM, Arjen Markus wrote:

> I have no solution to offer and you probably thought of it
> yourseld too, but the + might be an attempt (rather
> superfluous and annoying) to indicate upward rounding
> took place.
>
> Does this happen with an ordinary C program too? The culprit
> would be the printf() family as implemented on the platform.
>

SQLite has its own printf() implementation.  It has to.  If it used system
printf(), than certain LOCALE settings would turn "." into "," and
introduce syntax errors.

The build-in printf() of SQLite also introduces a number of useful new
formatting options, such as %q, %Q, %w, and %z.  Those extensions (and
others) are widely used internally by SQLite, so at this point it would be
a VERY big task to convert to system printf().


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float to string conversion problem

[Noel Frankinet]

Hello,

The pda probably has no floating point support, you should check how sqlite
as been compiled.
Best wishes
Noël


On 14 June 2013 14:19, Arjen Markus  wrote:

> Hi Filipe,
>
>
> On Thu, 13 Jun 2013 15:59:35 +0100
>  Filipe Madureira 
> >
> wrote:
>
>> Hi,
>>
>> I have a problem executing a query on a WinCE6 ARM device.
>> I use SQlite for years and tested on all types of devices including
>> WinCE6 ARM and never had a problem.
>>
>> But I have one problem on particular device that has a Texas Instruments
>> ARM CPU with WinCE6.
>> A very simple way to reproduce my problem is:
>> Create Table t1(c1 DECIMAL(12,3))
>> Insert Into t1(c1) values(2.55)
>> Select c1 From t1
>>
>> I run this "Select" with sqlite3_get_table()
>> The value I get is "3.,+"
>>
>> It makes no sense, it is not even a number, and only happens if the
>> decimal values have a number higher than 5.
>> This seems to be a problem with this CPU/WinCE version.
>>
>> I don't know SQLite source code, and it is huge, so can someone tell me
>> where in the source code the float (or double, I don't know) value of 2.55
>> is being converted to a string to be outputed by sqlite3_get_table()?
>> Or any hint on how to track the problem?
>>
>> Note: I copied the database file to my PC and the value stored there is
>> 2.55, so the problem is retrieving the value on the device.
>>
>>
> I have no solution to offer and you probably thought of it
> yourseld too, but the + might be an attempt (rather
> superfluous and annoying) to indicate upward rounding
> took place.
>
> Does this happen with an ordinary C program too? The culprit
> would be the printf() family as implemented on the platform.
> Can scanf() read this type of numbers?
>
> Regards,
>
> Arjen
>
>
>
> DISCLAIMER: This message is intended exclusively for the addressee(s) and
> may contain confidential and privileged information. If you are not the
> intended recipient please notify the sender immediately and destroy this
> message. Unauthorized use, disclosure or copying of this message is
> strictly prohibited.
> The foundation 'Stichting Deltares', which has its seat at Delft, The
> Netherlands, Commercial Registration Number 41146461, is not liable in any
> way whatsoever for consequences and/or damages resulting from the improper,
> incomplete and untimely dispatch, receipt and/or content of this e-mail.
>
>
>
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float to string conversion problem

[Richard Hipp]

On Thu, Jun 13, 2013 at 10:59 AM, Filipe Madureira <
filipe.madure...@sysdevsolutions.com> wrote:

>  can someone tell me where in the source code the float (or double, I
> don't know) value of 2.55 is being converted to a string to be outputed
>

Floating point to ASCII conversion happens here:

http://www.sqlite.org/src/artifact/bff529ed476?ln=406-553



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float to string conversion problem

[Arjen Markus]

Hi Filipe,

On Thu, 13 Jun 2013 15:59:35 +0100
 Filipe Madureira  
wrote:

Hi,

I have a problem executing a query on a WinCE6 ARM 
device.
I use SQlite for years and tested on all types of 
devices including WinCE6 ARM and never had a problem.


But I have one problem on particular device that has a 
Texas Instruments ARM CPU with WinCE6.

A very simple way to reproduce my problem is:
Create Table t1(c1 DECIMAL(12,3))
Insert Into t1(c1) values(2.55)
Select c1 From t1

I run this "Select" with sqlite3_get_table()
The value I get is "3.,+"

It makes no sense, it is not even a number, and only 
happens if the decimal values have a number higher than 
5.

This seems to be a problem with this CPU/WinCE version.

I don't know SQLite source code, and it is huge, so can 
someone tell me where in the source code the float (or 
double, I don't know) value of 2.55 is being converted to 
a string to be outputed by sqlite3_get_table()?

Or any hint on how to track the problem?

Note: I copied the database file to my PC and the value 
stored there is 2.55, so the problem is retrieving the 
value on the device.




I have no solution to offer and you probably thought of it
yourseld too, but the + might be an attempt (rather
superfluous and annoying) to indicate upward rounding
took place.

Does this happen with an ordinary C program too? The 
culprit
would be the printf() family as implemented on the 
platform.

Can scanf() read this type of numbers?

Regards,

Arjen



DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




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

[sqlite] float to string conversion problem

[Filipe Madureira]

Hi,

I have a problem executing a query on a WinCE6 ARM device.
I use SQlite for years and tested on all types of devices including 
WinCE6 ARM and never had a problem.


But I have one problem on particular device that has a Texas Instruments 
ARM CPU with WinCE6.

A very simple way to reproduce my problem is:
Create Table t1(c1 DECIMAL(12,3))
Insert Into t1(c1) values(2.55)
Select c1 From t1

I run this "Select" with sqlite3_get_table()
The value I get is "3.,+"

It makes no sense, it is not even a number, and only happens if the 
decimal values have a number higher than 5.

This seems to be a problem with this CPU/WinCE version.

I don't know SQLite source code, and it is huge, so can someone tell me 
where in the source code the float (or double, I don't know) value of 
2.55 is being converted to a string to be outputed by sqlite3_get_table()?

Or any hint on how to track the problem?

Note: I copied the database file to my PC and the value stored there is 
2.55, so the problem is retrieving the value on the device.


--

Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
-


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

[sqlite] Feature request: add support for COMMENT statement

[Alexey Pechnikov]

Hello!

It's very important to have place to store table metainformation. In all
common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table
but SQLite doesn't support it.

COMMENTs are supported from very old to new PostgreSQL:
http://www.postgresql.org/docs/7.1/static/sql-comment.html
http://www.postgresql.org/docs/9.2/static/sql-comment.html

MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

ORACLE:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4009.htm

IBM DB2:
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_addandretrievecommentsfromcatalog.htm

Sybase:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X315695.htm


P.S.  Field type is bad storage for additional information because there
are a lot of conversion problems from some bindings (including official
tclsqlite) when column types are non-standard.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite security

[Simon Slavin]

On 14 Jun 2013, at 11:18am, Toby Dickenson  wrote:

> Suppose I have two processes which communicate via a shared database.
> One process is internet-facing, and therefore carries a risk of being
> compromised. The second process is running under a different uid, and
> has access to other files which should be kept private. The database
> is a trust boundary.
> 
> To what extent is this IPC mechanism a risk of privilege escalation,
> whereby any malicious code injected into the first process might be
> able to use the shared database to attack the second process.
> Obviously there is a need for both applications to handle the data
> retrieved from that database in a secure manner, but are there other
> risks/considerations from the sqlite library itself?

The SQLite library does not store low-level executable code in any database 
file, nor does it execute any code found in a database file.  Putting 
executable code in a SQLite database (presumably as a BLOB) does nothing unless 
you have specially written your program to retrieve a BLOB, put it into memory 
and execute it.  Don’t do that.  Unless you have a good reason to.

The SQLite library can store certain types of 'code' in a database at the SQL 
level.  If someone manages to compromise your web-facing interface they might 
be able to, for example, create a TRIGGER in your database that caused the 
execution of one SQL command to execute other SQL commands.  A nasty TRIGGER 
might be, for example, one which noticed a new row in the member table with the 
name 'Bobby Tables' and responded by deleting all rows in the member table.  
This would affect all applications which used that database even if the new 
member was added by a different application from the one which inserted the 
TRIGGER.

Worth noting here that all this TRIGGER did was delete data (or otherwise mess 
up your database file).  It didn’t inject executable code into your 
application.  It might make your application run parts of itself unexpectedly 
(for example, your application might react to a particular SQLite error code by 
running certain lines of code) but it couldn’t do much more than that.

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

Re: [sqlite] SQLite3 Database access

[Simon Slavin]

On 14 Jun 2013, at 4:55am, Vijay Khurdiya  
wrote:

> I am quite new with SQLite3, tried sample application to create, read & write 
> database using SQLite3.
> 
> Now I am trying to write test where multiple process are trying to access 
> database using SQLite3. To get best result w.r.t Speed & Concurrency what is 
> best mode & mechanism to be used.

Each of the modes and mechanisms supplied by SQLite are best under some 
combination of database sizes, frequencies of reads and writes, and types of 
hardware.  If any mode or mechanism was better than the others in all cases, 
SQLite would offer only that one.

Do not concern yourself with 'best speed'.  Instead write your software in the 
simplest, fastest and most elegant way possible, then test it out.  If it 
performs acceptably ship it.  If not, /then/ you can start investigating ways 
to improve things and you will have data ready for testing.

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

[sqlite] sqlite security

[Toby Dickenson]

Hi all,

I have a question about security considerations for using sqlite.

Suppose I have two processes which communicate via a shared database.
One process is internet-facing, and therefore carries a risk of being
compromised. The second process is running under a different uid, and
has access to other files which should be kept private. The database
is a trust boundary.

To what extent is this IPC mechanism a risk of privilege escalation,
whereby any malicious code injected into the first process might be
able to use the shared database to attack the second process.
Obviously there is a need for both applications to handle the data
retrieved from that database in a secure manner, but are there other
risks/considerations from the sqlite library itself?

There are some obvious and maybe unavoidable denial-of-service risks:
the first process might fill up the disk, or (Im guessing here) hold
onto locks for too long. Any other considerations?

Thanks in advance,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning SQLite performance with WAL mode

[Runcy Oommen]

Thanks you Simon for your insightful reply, appreciate it.
 
I will make the respective changes and get back you with the results shortly.

From: Simon Slavin 
To: Runcy Oommen ; General Discussion of SQLite Database 
 
Sent: Thursday, June 13, 2013 4:06 PM
Subject: Re: [sqlite] Fine tuning SQLite performance with WAL mode



On 13 Jun 2013, at 7:34am, Runcy Oommen  wrote:

> PRAGMA journal_mode = wal;

WAL is the new modern way to do things and is generally better in lots of ways. 
 Use it unless it causes problems for you.

> PRAGMA wal_autocheckpoint = 10;
> 
> Now I know that the default wal_autocheckpoint is 1000, does it affect either 
> positive or negatively with my current value of 10?

Don’t do this unless you have a great reason for it.  It makes SQLite update 
the files on disk extremely often, which means that anything that makes changes 
to your database will take a very long time to execute.

I recommend you leave the value at its default.  If you have a particular point 
in your application at which the database on file absolutely must be up-to-date 
(I’m not just talking normal transactions here, but more like a time when you 
expect power loss or USB drive disconnection) then at that point execute this 
command:

PRAGMA wal_checkpoint

Apart from that, it’s just things like ...

> About 190+ SELECT, INSERT, DELETE statements executed every 10 seconds  
> (whether in idle or use)

If any of these things go together, execute them all in a transaction 
(including the SELECT statements).  This means (simplified) that SQLite only 
has to do one lot of locking for the whole transaction rather than locking for 
each statement.

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