Re: [sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Nico Williams 
On Wed, Oct 26, 2011 at 5:01 PM, Jay A. Kreibich  wrote:
>  The use of IS is causing the query optimizer to use a full table
>  scan, essentially turning the query into a O(n) operation.  This has
>  to do with how IS differs from = in the handling of NULLs.  Since it
>  is possible bind a NULL to the query parameter (and one tends to only
>  use IS when looking for NULLs, so this is a valid assumption), the
>  optimizer likely assumes the condition in question may return "hit"
>  for a significant percentage of the table rows.  This, and some more
>  subtle interactions, makes a table scan more appropriate unless you
>  know the size and diversity of the column in question.  Further, the
>  optimizer cannot replace the IS with a = internally, despite the fact
>  MyKey is known to be an I.P.K. (other any other column with a NOT
>  NULL constraint).

Right.  You could write

SELECT * FROM mytable WHERE MyKey = ? AND MyKey IS NOT NULL;

if what you're looking for is non-null keys.  If you're looking to
include NULL keys then you can do:

SELECT * FROM mytable WHERE MyKey IS NULL;

Finally, you can always do:


SELECT * FROM mytable WHERE  :a IS NOT NULL MyKey = :a
UNION ALL
SELECT * FROM mytable WHERE :a IS NULL AND  MyKey IS NULL;

This should give you exactly the same results as your original query,
but with a fast query plan if there is a suitable index.  (It does for
me.)

The optimizer could re-write your original query as above, but that's
probably a fairly hairy optimization to program.  That said, it'd be a
very useful optimization to program for when you really want IS
instead of =...  It's nice to be able to write clean SQL and not pay a
price in performance for it.  But then, SQLite3 is "lite".

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

Re: [sqlite] Remove from mailing list please.

2011-10-26 Thread Igor Tandetnik 

On 10/26/2011 9:49 PM, G. J. Weinberger wrote:

Please remove me from mailing list. One kind person helped me; as for the rest, 
the issues discussed are way over my head.


Every message you get from the list displays a link at the bottom where 
you can go to edit your membership. Here it is again:


http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--
Igor Tandetnik

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

[sqlite] Remove from mailing list please.

2011-10-26 Thread G. J. Weinberger 
Please remove me from mailing list. One kind person helped me; as for the rest, 
the issues discussed are way over my head.

Cordially,
GJW
 
G. J. Weinberger
9 Mulberry Lane
Farmington, CT 06032
USA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error messages from user-defined functions calling sqlite3_result_error() requires sqlite3_finalize?

2011-10-26 Thread Peter Aronson 
Oh, that *is* funny.

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jean-Christophe Deschamps
> Sent: Wednesday, October 26, 2011 6:12 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Error messages from user-defined functions calling
> sqlite3_result_error() requires sqlite3_finalize?
> 
> Funny coincidence in time, I just posted on the same subject while your
> post was being received!
> 
> ___
> 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] Error messages from user-defined functions calling sqlite3_result_error() requires sqlite3_finalize?

2011-10-26 Thread Jean-Christophe Deschamps 
Funny coincidence in time, I just posted on the same subject while your 
post was being received!


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

[sqlite] sqlite3_errcode() and sqlite3_errmsg()

2011-10-26 Thread Jean-Christophe Deschamps 

Dear list,


It seems that after sqlite3_prepare_v2() and sqlite3_step() we have 
that sqlite3_errcode() returns meaningful result right away when some 
function invokes sqlite3_result_error() during sqlite3_step().  OK so far.


The issue is with sqlite3_errmsg().  If a function post an ad-hoc 
message using sqlite3_result_error(), then this message is only 
available after sqlite3_finalize() or sqlite3_reset().  In the meantime 
(that is after sqlite3_step and before sqlite3_reset or _finalize) only 
the standard message hardcoded in SQLite code is available using 
sqlite3_errmsg().


E.g. suppose you have loaded an extension which has a sqrt() function 
returning a custom error message in case the argument passed is 
negative.  Then this is the scenario:


sqlite3_prepare_v2("select sqrt(-1);")
sqlite3_step()  // error returned by sqrt here
sqlite3_finalize()
sqlite3_errmsg()// check the error message

We get the expected custom message "sqrt domain error". But if I execute

sqlite3_prepare_v2("select sqrt(-1);")
sqlite3_step()  // error returned by sqrt here
sqlite3_errmsg()// check the error message

We get "SQL logic error or missing database".
Looks like the custom error message is not reachable unless the 
statement is finalized or reset.


If this a design "feature" that future releases aren't going to fix 
anytime soon, at least the docs should explain the situation 
clearly.  Lack of sync between errcode and errmsg is highly unexpected.


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

[sqlite] Error messages from user-defined functions calling sqlite3_result_error() requires sqlite3_finalize?

2011-10-26 Thread Peter Aronson 
This may be a known thing, but I can't find anything on it on-line.  I finally 
figured out a solution by examining the code to shell.c.

OK, I have a user-defined function in an extension that calls 
sqlite3_result_error() when an out-of-bounds argument is passed in.  I called 
sqlite3_prepare16_v2() on the SQL, which worked, then sqlite3_step(), which 
failed.  Then I called sqlite3_errmsg16(), and got instead of my message, this 
message: "SQL logic error or missing database".  Only once I called 
sqlite3_finalize() before sqlite3_errmsg16 did I get the error message from my 
function (I was calling sqlite3_finalize() after calling sqlite3_errmsg() 
before 
that).

Now the on-line doc notes that you need to call sqlite3_finalize() to get the 
proper error code when using the legacy interface.  But I'm not using the 
legacy 
interface and it says nothing about the error message functions anyway.

So, is this a software error, a documentation error, or did I simply miss 
something somewhere?

Best regards,

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

Re: [sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Jay A. Kreibich 
On Wed, Oct 26, 2011 at 11:35:33PM +0200, Tobias Mohrl?der scratched on the 
wall:
> On Wednesday, Oct 26, 2011 at 02:26PM Jay A. Kreibich wrote:
> 
> > On Tue, Oct 25, 2011 at 11:51:46PM +0200, Tobias Mohrl?der scratched on the 
> > wall:
> >> Hello everybody,
> >> 
> >> I strongly believe that my setup is somehow wrong, I just can't figure
> >> out how.
> > 
> > 
> >> sqlite3_prepare_v2(db, "SELECT * FROM mytable WHERE MyKey IS ?", -1, 
> >> , 0);
> > 
> >  Don't use "IS", use "=".  The two operations are quite different.
> > 
> >   -j
> 
> That's it, thanks!
> 
> I'm still wondering why there is such a huge performance hit. Judging from the
> documentation I would have expected some if-statements worth of overhead,
> nothing serious.

  The use of IS is causing the query optimizer to use a full table
  scan, essentially turning the query into a O(n) operation.  This has
  to do with how IS differs from = in the handling of NULLs.  Since it
  is possible bind a NULL to the query parameter (and one tends to only
  use IS when looking for NULLs, so this is a valid assumption), the
  optimizer likely assumes the condition in question may return "hit"
  for a significant percentage of the table rows.  This, and some more
  subtle interactions, makes a table scan more appropriate unless you
  know the size and diversity of the column in question.  Further, the
  optimizer cannot replace the IS with a = internally, despite the fact
  MyKey is known to be an I.P.K. (other any other column with a NOT
  NULL constraint).

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Tobias Mohrlüder 
On Wednesday, Oct 26, 2011 at 02:26PM Jay A. Kreibich wrote:

> On Tue, Oct 25, 2011 at 11:51:46PM +0200, Tobias Mohrlüder scratched on the 
> wall:
>> Hello everybody,
>> 
>> I strongly believe that my setup is somehow wrong, I just can't figure
>> out how.
> 
> 
>> sqlite3_prepare_v2(db, "SELECT * FROM mytable WHERE MyKey IS ?", -1, , 
>> 0);
> 
>  Don't use "IS", use "=".  The two operations are quite different.
> 
>   -j

That's it, thanks!

I'm still wondering why there is such a huge performance hit. Judging from the
documentation I would have expected some if-statements worth of overhead,
nothing serious.

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

Re: [sqlite] Bug in using overloaded functions for virtual tables?

2011-10-26 Thread nobre 

Here are the opcodes generated for each operation using the EXPLAIN command

sqlite> explain select offsets(pasta_text), DataPasta from pasta_text where
past
a_text match 'andamento*';
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 14000
2 VOpen  0 0 0 vtab:3E6EE8:46A920  00
3 String80 3 0 andamento* 00
4 Integer371 000
5 Integer1 2 000
6 VFilter0 12100
7 VColumn0 35100
8 Function   0 1 4 offsets(1) 01
9 VColumn0 4 500
10ResultRow  4 2 000
11VNext  0 7 000
12Close  0 0 000
13Halt   0 0 000
14Transaction0 0 000
15VerifyCookie   0 297   000
16Goto   0 2 000
sqlite> explain select offsets(pasta_text), DataPasta from pasta_text where
past
a_text match 'andamento*' group by DataPasta;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 OpenEphemeral  1 3 0 keyinfo(1,BINARY)  00
2 Integer0 4 000
3 Integer0 3 000
4 Gosub  6 41000
5 Goto   0 45000
6 VOpen  0 0 0 vtab:3E6EE8:46A920  00
7 String80 110 andamento* 00
8 Integer379 000
9 Integer1 10000
10VFilter0 17900
11VColumn0 4 900
12Sequence   1 10000
13VColumn0 3511   00
14MakeRecord 9 3 12   00
15IdxInsert  1 12000
16VNext  0 11000
17Close  0 0 000
18Sort   1 44000
19Column 1 0 800
20Compare7 8 1 keyinfo(1,BINARY)  00
21Jump   222622   00
22Move   8 7 100
23Gosub  5 34000
24IfPos  4 44000
25Gosub  6 41000
26Column 1 2 100
27Column 1 0 200
28Integer1 3 000
29Next   1 19000
30Gosub  5 34000
31Goto   0 44000
32Integer1 4 000
33Return 5 0 000
34IfPos  3 36000
35Return 5 0 000
36SCopy  1 9 000
37Function   0 9 13offsets(1) 01
38SCopy  2 14000
39ResultRow  132 000
40Return 5 0 000
41Null   0 1 000
42Null   0 2 000
43Return 6 0 000
44Halt   0 0 000
45Transaction0 0 000
46VerifyCookie   0 297   000
47Goto   0 6 000
sqlite>
-- 
View this message in context: 
http://old.nabble.com/Bug-in-using-overloaded-functions-for-virtual-tables--tp32364864p32726667.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Corrupt format in sqlite file

2011-10-26 Thread Simon Slavin 

On 26 Oct 2011, at 7:05pm, G. J. Weinberger wrote:

> Can anyone out there help me?

The instructions you quoted are fairly clear.  Do you understand what

~/Library/Application Support/GarageSale/garagesale.sqlite

means ?  '~' means your home folder.  Then inside that folder, look for a 
folder called 'Library', etc..  See if you can follow the instructions you 
posted.  If not, you should probably contact the GarageSale people rather than 
us.

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

[sqlite] Corrupt format in sqlite file

2011-10-26 Thread G. J. Weinberger 
Hello.
I use GarageSale as my ebay listing tool.
Suddenly, I am unable to post, duplicate templates etc., getting this message 
(screen shot):

file://localhost/Users/bill/Desktop/Screen%20shot%202011-10-26%20at%2010.01.20%20AM.pngG.
 J. Weinberger
Garage sale has only sent me this message re some files that I can't/don't know 
how to access:
 
 Seems like your GS database has corrupted.

Please quit GS and move this file to the desktop: ~/Library/Application 
Support/GarageSale/garagesale.sqlite Then navigate here: ~/Library/Application 
Support/GarageSale/Backups/ And move the most recent file from this folder to 
here: ~/Library/Application Support/GarageSale Now start GS and see what 
happens. If this does not take care of the problem, repeat the process with 
another backup file, until you find one that works.Can anyone out there help 
me? Thanks.
 GJW


G. J. Weinberger 
9 Mulberry Lane
Farmington, CT 06032
USA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite on Windows 64bit

2011-10-26 Thread Taleeb Anwar 
Not sure what is causing the problem..and thus my suggestion(s) can be
labelled as trial and error ---
1. Check the target platform (try with x64 if it is 'any cpu').

Thanks & Regards
Taleeb Anwar

*Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*




On Wed, Oct 26, 2011 at 11:09 PM, J Trahair  wrote:

> HI.
>
> There's no SQLite.Interop.dll, anywhere.
>
> I downloaded sqlite-netFx40-setup-bundle-x64-2010-1.0.76.0.exe which gave
> me System.Data.SQLite.dll.
>
> Works on my development 64bit machine, not on the customer's 64bit.
>
> How do I get the interop?
>
> Thank you.
>
> Jonathan Trahair
>
>  - Original Message -
>  From: Joe Mistachkin
>  To: 'General Discussion of SQLite Database'
>  Sent: Wednesday, October 26, 2011 3:20 PM
>  Subject: Re: [sqlite] Using SQLite on Windows 64bit
>
>
>
>  J Trahair wrote:
>  >
>  > However, I have installed the same installation setup.exe on a separate
>  Windows
>  > 64bit computer, and it comes up with the error message 'Could not load
>  file or
>  > assembly System.Data.SQLite.dll or one of its dependencies, the
> specified
>  module
>  > could not be found.' However, the System.Data.SQLite.dll IS there
>  (v.1.0.76).
>  > What dependencies does it need (64bit)?
>  >
>
>  Is the SQLite.Interop.dll file in the same directory as the
>  System.Data.SQLite.dll
>  (or somewhere along the system PATH)?  Alternatively, you could use the
>  mixed-mode
>  assembly, which contains both the native and managed code required for
> using
>  SQLite
>  (i.e. it is more suitable for GAC'ing without having to alter the system
>  PATH, etc).
>
>  --
>  Joe Mistachkin 
>
>  ___
>  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] Using SQLite on Windows 64bit

2011-10-26 Thread Joe Mistachkin 

J Trahair wrote:
>
> Works on my development 64bit machine, not on the customer's 64bit.
>

The most likely cause for it working on your development machine and not on
your
customer's machine is that you have the Microsoft Visual C++ 2010 Runtime
Library
installed and your customer does not.

>
> How do I get the interop?
>

With the "bundle" packages, you do not need it because the native SQLite
code is
compiled into the System.Data.SQLite.dll assembly itself.

--
Joe Mistachkin

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

Re: [sqlite] Using SQLite on Windows 64bit

2011-10-26 Thread J Trahair 
HI.

There's no SQLite.Interop.dll, anywhere.

I downloaded sqlite-netFx40-setup-bundle-x64-2010-1.0.76.0.exe which gave me 
System.Data.SQLite.dll.

Works on my development 64bit machine, not on the customer's 64bit.

How do I get the interop?

Thank you.

Jonathan Trahair

  - Original Message - 
  From: Joe Mistachkin 
  To: 'General Discussion of SQLite Database' 
  Sent: Wednesday, October 26, 2011 3:20 PM
  Subject: Re: [sqlite] Using SQLite on Windows 64bit



  J Trahair wrote:
  > 
  > However, I have installed the same installation setup.exe on a separate
  Windows
  > 64bit computer, and it comes up with the error message 'Could not load
  file or
  > assembly System.Data.SQLite.dll or one of its dependencies, the specified
  module
  > could not be found.' However, the System.Data.SQLite.dll IS there
  (v.1.0.76).
  > What dependencies does it need (64bit)? 
  > 

  Is the SQLite.Interop.dll file in the same directory as the
  System.Data.SQLite.dll
  (or somewhere along the system PATH)?  Alternatively, you could use the
  mixed-mode
  assembly, which contains both the native and managed code required for using
  SQLite
  (i.e. it is more suitable for GAC'ing without having to alter the system
  PATH, etc).

  --
  Joe Mistachkin 

  ___
  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] Using SQLite on Windows 64bit

2011-10-26 Thread Joe Mistachkin 

J Trahair wrote:
> 
> However, I have installed the same installation setup.exe on a separate
Windows
> 64bit computer, and it comes up with the error message 'Could not load
file or
> assembly System.Data.SQLite.dll or one of its dependencies, the specified
module
> could not be found.' However, the System.Data.SQLite.dll IS there
(v.1.0.76).
> What dependencies does it need (64bit)? 
> 

Is the SQLite.Interop.dll file in the same directory as the
System.Data.SQLite.dll
(or somewhere along the system PATH)?  Alternatively, you could use the
mixed-mode
assembly, which contains both the native and managed code required for using
SQLite
(i.e. it is more suitable for GAC'ing without having to alter the system
PATH, etc).

--
Joe Mistachkin 

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

Re: [sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Jay A. Kreibich 
On Tue, Oct 25, 2011 at 11:51:46PM +0200, Tobias Mohrlüder scratched on the 
wall:
> Hello everybody,
> 
> I strongly believe that my setup is somehow wrong, I just can't figure
> out how.


> sqlite3_prepare_v2(db, "SELECT * FROM mytable WHERE MyKey IS ?", -1, , 
> 0);

  Don't use "IS", use "=".  The two operations are quite different.

   -j



-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using SQLite on Windows 64bit

2011-10-26 Thread J Trahair 
Hi.

I want to install a VB.Net application onto a customer's Windows 64bit computer.

I have my own Windows 64bit laptop with Visual Studio 2010 installed. I have a 
VB.Net application which uses SQLite successfully, both in the development 
runtime, and as an 'installed' program in a separate folder on the same laptop.

However, I have installed the same installation setup.exe on a separate Windows 
64bit computer, and it comes up with the error message
'Could not load file or assembly System.Data.SQLite.dll or one of its 
dependencies, the specified module could not be found.' However, the 
System.Data.SQLite.dll IS there (v.1.0.76). What dependencies does it need 
(64bit)?

Thanks in advance.

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

[sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Tobias Mohrlüder 
Hello everybody,

I'm currently experimenting with SQLite (3.7.8) and am a complete newbie in the 
field of SQL databases.

Running performance comparisons between a C++ std::map< int, std::string > and 
an in-memory SQLite database, I found SQLite to be astonishingly slow when 
selecting single rows by their ROWID. More than that, the performance 
degradation when increasing the total number of rows was far from being O(log 
n) - a jump from 1000 rows to 1 rows resulted in a factor of 100 in 
execution time.

I strongly believe that my setup is somehow wrong, I just can't figure out how.

Below is my test, I left out checking the return values to make it more 
readable:


static const int TOTAL_ROW_COUNT = 1;

sqlite3* db = 0;

sqlite3_open(":memory:", );

static const uint BUFSIZE = 2048;
char buf[BUFSIZE];

sqlite3_exec(db, "CREATE TABLE mytable (MyKey INTEGER PRIMARY KEY)", 0, 0, 0);

for (int key=0; key 1ms on a 2.4GHz Core2Duo and a table 
with 1 rows */
sqlite3_step(stmt);

sqlite3_reset(stmt);
}


Enclosing the last loop in "BEGIN TRANSACTION" and "COMMIT TRANSACTION" 
statements did not help, neither did fiddling with PRAGMAs (journal_mode = OFF, 
synchronous = OFF, temp_store = MEMORY).

I ran this test under Windows XP/VS2008 (Release build) and OS X Lion/XCode 
(Debug build, I believe.. I'm new to this IDE), the results were nearly 
identical (slight differences because of the hardware).

I'm completely puzzled by this. It is actually faster to dump the whole table 
into a std::map and then access it one-by-one:

- execute "SELECT * FROM mytable"
- sqlite3_step() through the whole result set
  - for each row: std::map.insert(sqlite3_column_int(stmt, 0), "sometext") 
(pseudo-code)
- for (int i=0; i

Re: [sqlite] Multiple databases in one DB connection. How to let BEGIN TRANSACTION..COMMIT apply to only one of them?

2011-10-26 Thread Frank Missel 
Well, if the statements within your BEGIN TRANSACTION ... COMMIT only really
affects one database (hereafter called the main database) only that database
should be locked.
If you experience otherwise, it could be because:

1. The locking_mode for the databases has been set to EXCLUSIVE (see
http://sqlite.org/pragma.html#pragma_locking_mode)

2.  The statements executed against the main database may read from other
databases as part of the execution thus holding SHARED locks on the other
databases which will prevent writing to them. See an explanation of the
locking system in SQLite here (http://www.sqlite.org/lockingv3.html).
However, you should still be able to read from the other databases while the
transaction is being committed.

3. Inside the transaction against the main database you have overlooked some
statement that does in fact write to the other databases.

Perhaps you can supply some more details about what takes place inside the
transaction against the main database and what you are trying to do with the
other databases, if the above explanation doesn't solve your issue.


/Frank

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon L
> Sent: 26 October 2011 14:08
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Multiple databases in one DB connection. How to let
BEGIN
> TRANSACTION..COMMIT apply to only one of them?
> 
> While  one of the databases is under a lengthy writing process,  I don't
want
> other databases to be locked by the BEGIN TRANSACTION...COMMIT lock.
> Is this possible? Thanks.
> ___
> 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] triggers : NEW keyword with multiple tables

2011-10-26 Thread Sébastien Escudier 
> You haven't given the view explicit column names

How ?

> try this instead:
>
> CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type
> as table2_type FROM

Yes this works on latest version. But I wonder why the other syntax is
not accepted anymore. Because I'll have to rewrite my database views
when I upgrade sqlite.




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

Re: [sqlite] triggers : NEW keyword with multiple tables

2011-10-26 Thread Sébastien Escudier 
> Try NEW."table1.type" and NEW."table2.type"

This works with my old 3.6 version but not on 3.7.8
I still get : Error: no such column: NEW.table1.type


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

[sqlite] Multiple databases in one DB connection. How to let BEGIN TRANSACTION..COMMIT apply to only one of them?

2011-10-26 Thread Simon L 
While  one of the databases is under a lengthy writing process,  I
don't want other databases to be locked by the BEGIN
TRANSACTION...COMMIT lock.
Is this possible? Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users