Re: Re: [sqlite] Need help on build query.

[Artem Yankovskiy]

Thanks
--- [EMAIL PROTECTED] wrote:

> "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> > Artem Yankovskiy
>

> > wrote:
> > > I know about DROP TABLE.
> > > I have not knew when compatibility DROP TABLE
> and
> > > SELECT...
> > >
> > > DROP TABLE (select name from sqlite_master
> where...)
> > > did not work.
> > > Can I build resembling query?
> > 
> > No. You will have to run the select, store table
> names in memory, then 
> > build and run a separate DROP TABLE query for each
> table name.
> > 
> 
> Igor is correct - you cannot do that now.  But I
> have sometimes
> wondered if it would be nice to add an exec()
> function to SQLite
> that would recursively invoke its argument as an SQL
> script.
> For example:
> 
>SELECT exec('DELETE FROM ' || quote(name))
>  FROM sqlite_master
> WHERE ;
> 
> There would be security implications here, of
> course.  Clearly
> one would need to make sure that user-entered SQL
> (such as can
> be seen at http://www.sqlite.org/cvstrac/new) does
> not have access
> to the eval() function, just as it does not have
> access to the
> the load_extension() function.
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 


Best regards,
Artem Yankovskiy







Вы уже с Yahoo!? 
Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[Wesley W. Terpstra]

For anyone who has the same questions I had, I've found most of the  
answers.
PS. Excellent internal documentation of the VDBE (though a bit out-of- 
date)! It made it really easy to navigate the source.


On Feb 12, 2007, at 1:51 PM, Wesley W. Terpstra wrote:

What is the defined behaviour of interleaved inserts and selects?


Any open select statements must have executed a 'Callback' opcode. An  
insert at this point will complete in one step (unless there's an  
error). Therefore, when the select resumes, any open cursors will  
simply walk on to the next value (possibly the new one) with no  
problems.


Drop table fails with SQLITE_LOCKED if your try to use it with an in- 
progress query.



Will an UPDATE ... WHERE ... also *always* complete in a single step?


Yes.

 there some sort of 'open transaction counter'? ie: if I  
begin&commit within the scope of an 'automatic' transaction, it  
will not commit until the automatic transaction completes?


There is no counter. Instead, each query locks as it needs to and  
releases on Halt. Begin/Commit simply toggle the autocommit flag to  
off and on. If running statements have not completed, they still have  
their locks. So a begin statement makes the locks retained until the  
commit/rollback unset the flag.


Suppose I ran: begin, select *, step1, commit, step2. Does the  
commit fail because there is still a nested, active query?


Yes. The commit will fail with cannot commit/rollback transaction -  
SQL statement in progress and SQLITE_ERROR.


if I am running a "select * from giant_table;" and find on row  
10/800 the data I was looking for, I might want to just finalize  
the select statement. My question is what happens to an explicitly  
opened transaction that included this select statement? The  
documentation implies that the transaction will be aborted, which  
is not very desirable.


Stopping the query will not affect the autocommit flag. So the  
containing transaction is not aborted. It is impossible to stop an  
update/insert as they never invoke Callback. Therefore, there is no  
problem. Stopping a query is harmless to the forward progress of a  
transaction.


Now that I'm working on wrapping bind_text/bind_text16, I have  
another question: what is the native on-disk format of the strings?  
The documentation suggests that open/open16 determine the encoding.


db_enc determines the encoding of the values used on the stack.  
Conversion does happen, so just use UTF-8 everywhere and there will  
be none.


It's ackward that there is no separation between a compiled VM and  
a compiled VM's state. ie: If I want to run the same precompiled  
query twice in parallel (since this is apparently well defined),  
then I would need two separate instances of the state.


There is this separation within sqlite3. It's just not exposed to the  
user. However, nearly all of a Vdbe structure has to do with state  
information. While it would be possible to cache the  parsed query,  
the cost of creating a Vdbe structure is probably high enough that  
the savings are not worthwhile.


TBH, it would be more helpful if there was an intermediate form  
between prepare and the stmt.


This looks quite easy to implement. However, it's probably a red  
herring.


It seems the right solution to my problem is to keep a pool of  
prepared queries in the 'factory'. When all of them are in use,  
create a new one from the query string, and add it to the pool. This  
allows re-entrant use of the same query, and both can iterate  
correctly and independently.


Sometimes silence is the right answer. Thanks drh!

The well thought out concurrency makes me feel warm and fuzzy inside.  
sqlite3 will look after my bits. :-)



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Performance Question

[Slater, Chad]

I overly simplified my example. I'm actually selecting columns from
table B and C which is why I had this in the where clause:

AND ( JoinAToB.B_id = B.id ) 
AND ( JoinAToB.A_id = A.id )
...

Converting those to explicit JOIN clauses fixed the problem.

Thanks for your help!


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 12, 2007 4:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Performance Question

Slater, Chad wrote:
> Hello,
>
> I'm having trouble with the performance of one of my queries and my
"sql
> kung fu" is limited. Any help with this problem would be greatly
> appreciated
>
> Here's a stripped down version of the tables I'm dealing with:
>
> CREATE TABLE A ( 
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT
> )
>
> CREATE TABLE B ( 
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT
> )
>
> CREATE TABLE C ( 
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT
> )
>
> CREATE TABLE JoinAToB ( 
> A_id INTEGER NOT NULL REFERENCES A ( id ), 
> B_id INTEGER NOT NULL  REFERENCES B ( id ), 
> UNIQUE( A_id, B_id )  
> )
>
> CREATE TABLE JoinAToC ( 
> A_id INTEGER NOT NULL  REFERENCES A ( id ), 
> C_id INTEGER NOT NULL  REFERENCES C ( id ), 
> UNIQUE( A_id, C_id )  
> )
>
> The following query takes so long I end up killing the app before the
> query returns:
>
> SELECT DISTINCT A.id
>   FROM A,  
>C,
>B,
>JoinAToB,
>JoinAToC
>  WHERE 
>( ( ( JoinAToB.B_id IN ( 1 ) ) 
>AND ( JoinAToB.B_id = B.id ) 
>AND ( JoinAToB.A_id = A.id ) )  
>
> OR ( ( JoinAToC.C_id IN  ( 1 ) ) 
>AND ( JoinAToC.C_id = C.id ) 
>AND ( JoinAToC.A_id = A.id ) ) ) ;
>
>
> Table A has approx 13,000 rows
> Table B has 15 rows
> Table C has 5 row
> JoinTableAToB has 11 rows
> JoinTableAToC has approx 450 rows
>
> If I execute either of these queries separately they are very fast:
>
> SELECT DISTINCT A.id
>   FROM A, B,
>JoinAToB
>  WHERE 
>JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND
JoinAToB.A_id
> = A.id ;
>
>
> SELECT DISTINCT A.id
>   FROM A,  
>C,
>JoinAToC
>  WHERE 
>   JoinAToC.C_id IN  ( 1 ) AND JoinAToC.C_id = C.id AND
JoinAToC.A_id
> = A.id ;
>
>
> Adding the OR clause to combine the results seems to be the culprit
but
> I don't know why...
>
Chad,

You seem to be overly complicating the matter. Your query

SELECT DISTINCT A.id
  FROM A, B,
   JoinAToB
 WHERE 
   JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id
= A.id ;


is the same as

SELECT A_id
  FROM JoinAToB
 WHERE JoinAToB.B_id = 1;


Your table JoinAToB relates some A ids to some B ids. You don't need to 
join this to the tables A and B to do a query on the ids in that table. 
Similarly arguments apply to your table JoinAToC.

It looks like you are trying to get all the A ids that are referenced by

these two tables where the B id is 1 or the C id is 1. In SQL this is:

SELECT A_id
FROM JoinAToB
WHERE JoinAToB.B_id = 1
UNION
SELECT A_id
FROM JOINAToC
WHERE JoinAToC.C_id = 1

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance Question

[Dennis Cote]

Slater, Chad wrote:

Hello,

I'm having trouble with the performance of one of my queries and my "sql
kung fu" is limited. Any help with this problem would be greatly
appreciated

Here's a stripped down version of the tables I'm dealing with:

CREATE TABLE A ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT
)

CREATE TABLE B ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT
)

CREATE TABLE C ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT
)

CREATE TABLE JoinAToB ( 
A_id INTEGER NOT NULL REFERENCES A ( id ), 
B_id INTEGER NOT NULL  REFERENCES B ( id ), 
UNIQUE( A_id, B_id )  
)


CREATE TABLE JoinAToC ( 
A_id INTEGER NOT NULL  REFERENCES A ( id ), 
C_id INTEGER NOT NULL  REFERENCES C ( id ), 
UNIQUE( A_id, C_id )  
)


The following query takes so long I end up killing the app before the
query returns:

SELECT DISTINCT A.id
  FROM A,  
   C,

   B,
   JoinAToB,
   JoinAToC
 WHERE 
   ( ( ( JoinAToB.B_id IN ( 1 ) ) 
   AND ( JoinAToB.B_id = B.id ) 
   AND ( JoinAToB.A_id = A.id ) )  

OR ( ( JoinAToC.C_id IN  ( 1 ) ) 
   AND ( JoinAToC.C_id = C.id ) 
   AND ( JoinAToC.A_id = A.id ) ) ) ;



Table A has approx 13,000 rows
Table B has 15 rows
Table C has 5 row
JoinTableAToB has 11 rows
JoinTableAToC has approx 450 rows

If I execute either of these queries separately they are very fast:

SELECT DISTINCT A.id
  FROM A, B,
   JoinAToB
 WHERE 
   JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id

= A.id ;


SELECT DISTINCT A.id
  FROM A,  
   C,

   JoinAToC
 WHERE 
  JoinAToC.C_id IN  ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id

= A.id ;


Adding the OR clause to combine the results seems to be the culprit but
I don't know why...


Chad,

You seem to be overly complicating the matter. Your query

SELECT DISTINCT A.id
 FROM A, B,
  JoinAToB
WHERE 
  JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id

= A.id ;


is the same as

SELECT A_id
 FROM JoinAToB
WHERE JoinAToB.B_id = 1;


Your table JoinAToB relates some A ids to some B ids. You don't need to 
join this to the tables A and B to do a query on the ids in that table. 
Similarly arguments apply to your table JoinAToC.


It looks like you are trying to get all the A ids that are referenced by 
these two tables where the B id is 1 or the C id is 1. In SQL this is:


SELECT A_id
FROM JoinAToB
WHERE JoinAToB.B_id = 1
UNION
SELECT A_id
FROM JOINAToC
WHERE JoinAToC.C_id = 1

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Atomically creating a database and bootstrapping its tables

[Dennis Cote]

Ron Stevens wrote:


In general, does SQLite protect against the database being opened
while it's being created so other processes don't open it assuming
everything is in a good state?


Ron,

No, you have to do that in your application.

In pseudo code your create function could do this:

   open the database file
   begin an exclusive transaction (since you may need to write to 
initialize the tables)

   read the user_version
   if the user version is not zero
  create the tables (read SQL script into a string and pass the 
string to sqlite3_exec)

  set the user version to a non zero value
   endif
   commit the transaction

Only one process will succeed in getting the write lock while opening 
the transaction. That process will create the tables and set the 
user_version so no other process will try to reinitialize the tables latter.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Performance Question

[Slater, Chad]

Hello,

I'm having trouble with the performance of one of my queries and my "sql
kung fu" is limited. Any help with this problem would be greatly
appreciated

Here's a stripped down version of the tables I'm dealing with:

CREATE TABLE A ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)

CREATE TABLE B ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)

CREATE TABLE C ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)

CREATE TABLE JoinAToB ( 
A_id INTEGER NOT NULL REFERENCES A ( id ), 
B_id INTEGER NOT NULL  REFERENCES B ( id ), 
UNIQUE( A_id, B_id )  
)

CREATE TABLE JoinAToC ( 
A_id INTEGER NOT NULL  REFERENCES A ( id ), 
C_id INTEGER NOT NULL  REFERENCES C ( id ), 
UNIQUE( A_id, C_id )  
)

The following query takes so long I end up killing the app before the
query returns:

SELECT DISTINCT A.id
  FROM A,  
   C,
   B,
   JoinAToB,
   JoinAToC
 WHERE 
   ( ( ( JoinAToB.B_id IN ( 1 ) ) 
   AND ( JoinAToB.B_id = B.id ) 
   AND ( JoinAToB.A_id = A.id ) )  

OR ( ( JoinAToC.C_id IN  ( 1 ) ) 
   AND ( JoinAToC.C_id = C.id ) 
   AND ( JoinAToC.A_id = A.id ) ) ) ;


Table A has approx 13,000 rows
Table B has 15 rows
Table C has 5 row
JoinTableAToB has 11 rows
JoinTableAToC has approx 450 rows

If I execute either of these queries separately they are very fast:

SELECT DISTINCT A.id
  FROM A, B,
   JoinAToB
 WHERE 
   JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id
= A.id ;


SELECT DISTINCT A.id
  FROM A,  
   C,
   JoinAToC
 WHERE 
  JoinAToC.C_id IN  ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id
= A.id ;


Adding the OR clause to combine the results seems to be the culprit but
I don't know why...


Regards,


Chad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[John Stanton]

The basis of our business has been writing compilers and run time 
packages to integrate legacy and newer software.  We discovered that 
basic computer science provides the answers and looking beyond the 
paradigm of the legacy language or system is essential if a complexity 
chain reaction is to be avoided.


Good luck with your project.

Wesley W. Terpstra wrote:

On Feb 12, 2007, at 7:32 PM, John Stanton wrote:

I suggest that you also look carefully at the manifest typing  
implemented in Sqlite.  If your language is strongly typed you will  
have some design issues to address.



I am aware of this issue and already have a solution. It's part of  why 
I wanted precompiled queries to be distinct from their execution  state:


val AgeQuery = query db "select name, age from users where  age="iI";" 
oS oI $


will bind AgeQuery to a query factory that takes an integer as input  
(the iI) and outputs a (string, integer) pair (from the oS oI). The  '$' 
is a terminating function.


This already works fine. Using a technique called functional  unparsing, 
the above line constructs a function (hidden behind the  query type) 
that converts+binds arguments and fetchs+converts result  columms. 
However, for some compiler implementations (not MLton), this  dynamic 
creation of a function is costly, so it should be done once  per 'query 
template'.


This binding should be good for more than just my favourite of the  many 
SML compilers.


sqlite3_exec ... implements callbacks and that you implement a  
callback to handle each row.  Then the logic of your interface is  
elegant.



I already can do the functional equivalents. There is no real need  for 
a 'callback function' in a functional language. Functions can be  passed 
around like any other argument.


In the following code snippet, the function f takes the (string,  
integer) pairs output by the query and creates a new string. The map  
function takes the query result set and feeds it through this  function. 
The number '6' is the parameter which is bound to the  query's input.



fun f (s & i) = "User " ^ s ^ " has age: " ^ Int.toString i
val results = SQL.map f AgeQuery 6


The value results will now contain a 'string vector' with entries  like 
"User Tom has age: 6", "User Joe has age: 6", ...


The problem I have stems from sqlite3_stmt containing both the parsed  
query and the query state. In a binding like the above, these would  
ideally happen at two distinct points.

1. query parsing happens in the 'val AgeQuery = query ...' line
2. query state is created in the 'SQL.map f AgeQuery' line
Then the user function 'f' can quite happily re-use AgeQuery to  create 
a recursive use of the query, but with different state  (arguments and 
stack).


Without this separation, I am forced to choose to do both at step 1  or 
both at step 2:
1. both query state creation and parsing happen at 'val AgeQuery =  
query ...'. This means that an attempt by 'f' to reuse AgeQuery must  
result in an exception, as it would require two instances of the  
AgeQuery state.
2. both query state creation and parsing happen at 'SQL.map f  
AgeQuery'. This would allow 'f' to reuse AgeQuery, but it would mean  
that every execution reparsed the query string. The documentation  
implies this is unacceptably slow in some situations. As MLton- compiled 
executables are about as fast as C [1], too slow for C is  too slow for 
SML.


If there were a separation between the execution state of a query,  and 
the prepared form of a query, then this would be a perfect fit.  At the 
moment, I take option #1. Now that I know SQLite can handle  subqueries, 
I would like to support this. It would be a shame to  prohibit recursive 
use of the same query.


I could also try hacking a hybrid approach. In this case, I mark the  
AgeQuery as 'in-use' during the SQL.map. If a nested use of the query  
occurs, I could clone the query, and use this clone for the nested  
execution. From looking at the SQLite3 source code, it seems a clone  
method would be relatively easy to add. I could also implement the  
clone functionality in SML by re-executing prepare_v2 with a saved  copy 
of the query string.



You will find on reflection that the Sqlite API is simple and elegant



I didn't argue that it's inelegant. I *do* think it's a tad too  simple. 
The state of a query and the bytecode are combined into one  concept. It 
might be convenient most of the time, but it is a  confusion of two 
distinct things. You can run the same program more  than once in Unix. 
You can call the same method twice (even  recursively) in every 
programming language. I can't think of many  places where code must be 
one-to-one with data.


and will let you craft your interface provided you design it to  suit 
Sqlite, not have a preconceived notion and then be unable to  make the 
connection.



That's a valid point. I do have a preconceived notion of how this  
should work. However, it comes from the standard idioms used in

Re: [sqlite] Atomically creating a database and bootstrapping its tables

[Ken]

To bootstrap my db's I create a database template. Then make a physical copy of 
that. Locking and access is done via flock. So the first process to gain the 
lock wins and is respoonsible for making the copy, the other just waits until 
the lock is released and then  connects.
 
 I make lots of databases and found that creating a template then copying is 
much faster than using the sqlite api to create the db, then to create the 
individual tables.
 
 

Ron Stevens <[EMAIL PROTECTED]> wrote: I have two processes trying to access a 
database for the first time at
roughly the same time. I'm wondering if it's possible to atomically
create a database and bootstrap it with some tables from one process
so that the other process doesn't open the database either before
SQLite has finished writing the system tables or the first process has
finished the application specific bootstrapping.

In general, does SQLite protect against the database being opened
while it's being created so other processes don't open it assuming
everything is in a good state?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Atomically creating a database and bootstrapping its tables

[Ron Stevens]

I have two processes trying to access a database for the first time at
roughly the same time. I'm wondering if it's possible to atomically
create a database and bootstrap it with some tables from one process
so that the other process doesn't open the database either before
SQLite has finished writing the system tables or the first process has
finished the application specific bootstrapping.

In general, does SQLite protect against the database being opened
while it's being created so other processes don't open it assuming
everything is in a good state?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[Wesley W. Terpstra]

On Feb 12, 2007, at 7:32 PM, John Stanton wrote:
I suggest that you also look carefully at the manifest typing  
implemented in Sqlite.  If your language is strongly typed you will  
have some design issues to address.


I am aware of this issue and already have a solution. It's part of  
why I wanted precompiled queries to be distinct from their execution  
state:
val AgeQuery = query db "select name, age from users where  
age="iI";" oS oI $
will bind AgeQuery to a query factory that takes an integer as input  
(the iI) and outputs a (string, integer) pair (from the oS oI). The  
'$' is a terminating function.


This already works fine. Using a technique called functional  
unparsing, the above line constructs a function (hidden behind the  
query type) that converts+binds arguments and fetchs+converts result  
columms. However, for some compiler implementations (not MLton), this  
dynamic creation of a function is costly, so it should be done once  
per 'query template'.


This binding should be good for more than just my favourite of the  
many SML compilers.


sqlite3_exec ... implements callbacks and that you implement a  
callback to handle each row.  Then the logic of your interface is  
elegant.


I already can do the functional equivalents. There is no real need  
for a 'callback function' in a functional language. Functions can be  
passed around like any other argument.


In the following code snippet, the function f takes the (string,  
integer) pairs output by the query and creates a new string. The map  
function takes the query result set and feeds it through this  
function. The number '6' is the parameter which is bound to the  
query's input.

fun f (s & i) = "User " ^ s ^ " has age: " ^ Int.toString i
val results = SQL.map f AgeQuery 6
The value results will now contain a 'string vector' with entries  
like "User Tom has age: 6", "User Joe has age: 6", ...


The problem I have stems from sqlite3_stmt containing both the parsed  
query and the query state. In a binding like the above, these would  
ideally happen at two distinct points.

1. query parsing happens in the 'val AgeQuery = query ...' line
2. query state is created in the 'SQL.map f AgeQuery' line
Then the user function 'f' can quite happily re-use AgeQuery to  
create a recursive use of the query, but with different state  
(arguments and stack).


Without this separation, I am forced to choose to do both at step 1  
or both at step 2:
1. both query state creation and parsing happen at 'val AgeQuery =  
query ...'. This means that an attempt by 'f' to reuse AgeQuery must  
result in an exception, as it would require two instances of the  
AgeQuery state.
2. both query state creation and parsing happen at 'SQL.map f  
AgeQuery'. This would allow 'f' to reuse AgeQuery, but it would mean  
that every execution reparsed the query string. The documentation  
implies this is unacceptably slow in some situations. As MLton- 
compiled executables are about as fast as C [1], too slow for C is  
too slow for SML.


If there were a separation between the execution state of a query,  
and the prepared form of a query, then this would be a perfect fit.  
At the moment, I take option #1. Now that I know SQLite can handle  
subqueries, I would like to support this. It would be a shame to  
prohibit recursive use of the same query.


I could also try hacking a hybrid approach. In this case, I mark the  
AgeQuery as 'in-use' during the SQL.map. If a nested use of the query  
occurs, I could clone the query, and use this clone for the nested  
execution. From looking at the SQLite3 source code, it seems a clone  
method would be relatively easy to add. I could also implement the  
clone functionality in SML by re-executing prepare_v2 with a saved  
copy of the query string.



You will find on reflection that the Sqlite API is simple and elegant


I didn't argue that it's inelegant. I *do* think it's a tad too  
simple. The state of a query and the bytecode are combined into one  
concept. It might be convenient most of the time, but it is a  
confusion of two distinct things. You can run the same program more  
than once in Unix. You can call the same method twice (even  
recursively) in every programming language. I can't think of many  
places where code must be one-to-one with data.


and will let you craft your interface provided you design it to  
suit Sqlite, not have a preconceived notion and then be unable to  
make the connection.


That's a valid point. I do have a preconceived notion of how this  
should work. However, it comes from the standard idioms used in this  
language, which (if at all possible) i would like to preserve.


[1] http://shootout.alioth.debian.org/



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[John Stanton]

I wasn't suggesting that you use sqlite3_exec.  On the contrary I would 
counsel you not to.  I just suggested that you look to see how it 
implements callbacks and that you implement a callback to handle each 
row.  Then the logic of your interface is elegant.


If you are creating a language interface, you have assumed 
responsibility to make it work seamlessly and flawlessly.


You will find on reflection that the Sqlite API is simple and elegant 
and will let you craft your interface provided you design it to suit 
Sqlite, not have a preconceived notion and then be unable to make the 
connection.


I understood quite well what you are trying to achieve and suggested 
that you rethink your strategy.


I suggest that you also look carefully at the manifest typing 
implemented in Sqlite.  If your language is strongly typed you will have 
some design issues to address.


Wesley W. Terpstra wrote:

On Feb 12, 2007, at 3:22 PM, John Stanton wrote:

Look at how Sqlite implements callbacks in sqlite3_exec to discover  a 
way to implement your language.


I had already looked at it. I will be using the prepare_v2/bind/step/ 
reset/.../finalize interface instead. The exec interface is too  
simplified. Incidentally, SML is not my language. It has been  
standardized since 1997.


Doing that will ensure that the transactions are atomic and that  
database integrity is maintained.


How are ACID properties in any way my responsibility? They are SQLite's.

No offence, but I think you've completely missed my point.

My questions had to do with how the SQLite interface behaves in cases  
that are undocumented in the C API (which I have read completely,  
several times). I need to know exactly which sequence of method calls  
are allowed, in order to bind the library in such a way that the type  
checker in SML will ensure only correct use. SML type-safety is much  
stronger than in C. If a  program compiles, it is guaranteed not to  
segfault/etc. Therefore, binding a C library must be done carefully  to 
preserve this guarantee.


The problem I was talking about comes from (what I see as) a  deficiency 
in the SQLite3 API. That is, the compiled query and it's  state are 
associated with the same opaque data structure. Switching  to using exec 
will not solve this.


It looks like I'll have to do a SQLite3 source dive to see how hard  it 
would be to separate state from statements. If I can't do this,  then I 
will have to settle for a less elegant binding, without  precompiled 
queries.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[Dennis Cote]

Wesley W. Terpstra wrote:


val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" &  3)
val Iterator2 = SQL.execute Query2 4

case Iterator1a () of
   NONE => print "End of this table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^ 
"\n")


case Iterator1b () of
   NONE => print "End of the table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^ 
"\n")


case Iterator1a () of
   NONE => print "End of this table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^ 
"\n")


The point is that here I use the same prepared query twice, with two 
different associated states and bindings. It seems this sort of 
interface is impossible at the moment without reparsing the SQL 
statement again. Even if I choose not to expose the 'step' method as 
an iterator, during the processing of a query's results there's 
nothing I can do to prevent a user from executing  the query again.

Wesley,

You will have to explain how SML works.

In your example, what exactly does this statement Do?

   val Iterator1a = SQL.execute Query1 ("parameter1" & 2)

Does it execute the query to completion, does it only bind the 
parameters, or does it also take the first step? If the first, then you 
are simply iterating through the results and your queries are in fact 
not executing at the same time. If the second or third are true it seem 
to me you have a mismatch between your execute semantics and sqlite's 
prepare, bind, and step semantics.


I am assuming that the Iterator1a() type "calls" are stepping the query 
and returning the next result row using the sqlite_column_* API functions.


Dennis Cote





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance problem with simple queries

[John Stanton]

You are almost certainly encountering disk cacheing effects.

Makavy, Erez (Erez) wrote:

Problem summery:
---
Simple queries sometimes take ~400 ms
 
Analysis:

---
- A php script runs the same SQL query several times in different places
(in different transactions).
Some of the queries take around 400ms while the others (identical
queries) takes only 4-20ms.
(The timings are consistent for the same php script.)
 
- Testing showed that when adding multiple identical queries after a

"problematic" query run very fast, I.e.: ...
 
- time measurement is done with explode(' ', microtime());
 
Test script:

---
A Test script shows that the first query take long time, but the others
are quick.
It happens also not in the first query, but we don't have a simple test
script that reproduces that yet.
 
Script output:

DB is sqlite:/usr/local/web/443/lbaProvDBoneOpenTest:
 0.66058707 SELECT * FROM MediaGateway
 0.00106287 SELECT * FROM MediaGateway
 0.00017715 SELECT * FROM MediaGateway
 0.12085819 SELECT * FROM GeneralPlatformProperties
 0.00016093 SELECT * FROM GeneralPlatformProperties WHERE 1
 0.0649929 SELECT * FROM ClassOfRestriction WHERE id IS NULL
 0.05895591 SELECT * FROM ClassOfRestriction
 0.05869102 SELECT * From BriefHuntGroupList ORDER BY huntGroupName
COLLATE NOCASE
Second run:
 0.04814911 SELECT * FROM MediaGateway
 0.00015306 SELECT * FROM MediaGateway
 0.00019789 SELECT * FROM MediaGateway
 0.00516605 SELECT * FROM GeneralPlatformProperties
 0.000175 SELECT * FROM GeneralPlatformProperties WHERE 1
 0.02837396 SELECT * FROM ClassOfRestriction WHERE id IS NULL
 0.00037098 SELECT * FROM ClassOfRestriction
 0.05728984 SELECT * From BriefHuntGroupList ORDER BY huntGroupName
COLLATE NOCASE

Questrions:
---
 
What can explain the long procces time for the first query?

What can explain log proccessing time for queries whic are not the first
?
 
Thanks,

Erez.
 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Performance problem with simple queries

[Makavy, Erez \(Erez\)]

Problem summery:
---
Simple queries sometimes take ~400 ms
 
Analysis:
---
- A php script runs the same SQL query several times in different places
(in different transactions).
Some of the queries take around 400ms while the others (identical
queries) takes only 4-20ms.
(The timings are consistent for the same php script.)
 
- Testing showed that when adding multiple identical queries after a
"problematic" query run very fast, I.e.: ...
 
- time measurement is done with explode(' ', microtime());
 
Test script:
---
A Test script shows that the first query take long time, but the others
are quick.
It happens also not in the first query, but we don't have a simple test
script that reproduces that yet.
 
Script output:
DB is sqlite:/usr/local/web/443/lbaProvDBoneOpenTest:
 0.66058707 SELECT * FROM MediaGateway
 0.00106287 SELECT * FROM MediaGateway
 0.00017715 SELECT * FROM MediaGateway
 0.12085819 SELECT * FROM GeneralPlatformProperties
 0.00016093 SELECT * FROM GeneralPlatformProperties WHERE 1
 0.0649929 SELECT * FROM ClassOfRestriction WHERE id IS NULL
 0.05895591 SELECT * FROM ClassOfRestriction
 0.05869102 SELECT * From BriefHuntGroupList ORDER BY huntGroupName
COLLATE NOCASE
Second run:
 0.04814911 SELECT * FROM MediaGateway
 0.00015306 SELECT * FROM MediaGateway
 0.00019789 SELECT * FROM MediaGateway
 0.00516605 SELECT * FROM GeneralPlatformProperties
 0.000175 SELECT * FROM GeneralPlatformProperties WHERE 1
 0.02837396 SELECT * FROM ClassOfRestriction WHERE id IS NULL
 0.00037098 SELECT * FROM ClassOfRestriction
 0.05728984 SELECT * From BriefHuntGroupList ORDER BY huntGroupName
COLLATE NOCASE

Questrions:
---
 
What can explain the long procces time for the first query?
What can explain log proccessing time for queries whic are not the first
?
 
Thanks,
Erez.
 

Re: [sqlite] Multiple prepared queries

[Wesley W. Terpstra]

On Feb 12, 2007, at 3:22 PM, John Stanton wrote:
Look at how Sqlite implements callbacks in sqlite3_exec to discover  
a way to implement your language.
I had already looked at it. I will be using the prepare_v2/bind/step/ 
reset/.../finalize interface instead. The exec interface is too  
simplified. Incidentally, SML is not my language. It has been  
standardized since 1997.


Doing that will ensure that the transactions are atomic and that  
database integrity is maintained.

How are ACID properties in any way my responsibility? They are SQLite's.

No offence, but I think you've completely missed my point.

My questions had to do with how the SQLite interface behaves in cases  
that are undocumented in the C API (which I have read completely,  
several times). I need to know exactly which sequence of method calls  
are allowed, in order to bind the library in such a way that the type  
checker in SML will ensure only correct use. SML type-safety is much  
stronger than in C. If a  program compiles, it is guaranteed not to  
segfault/etc. Therefore, binding a C library must be done carefully  
to preserve this guarantee.


The problem I was talking about comes from (what I see as) a  
deficiency in the SQLite3 API. That is, the compiled query and it's  
state are associated with the same opaque data structure. Switching  
to using exec will not solve this.


It looks like I'll have to do a SQLite3 source dive to see how hard  
it would be to separate state from statements. If I can't do this,  
then I will have to settle for a less elegant binding, without  
precompiled queries.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] INTEGER PRIMARY KEY

[Tom Shaw]

Here's a question on INTEGER PRIMARY KEY. I would like use IP 
addresses (converted to an unsigned number to man them monotonically 
increasing) for INTEGER PRIMARY KEY however I can't determine from 
the online docs whether if I supply an unsigned integer in PHP 5:


$uip = sprintf("%u", ip2long($ip));

to sqlite 3. Is this possible or do I have to either use text (yuk) 
or split the ips (yuk)


TIA,

Tom

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Appropriate uses for SQLite

[Mike Owens]

Hey, sorry I'm a little late on this one (as usual).

On 2/3/07, David M X Green <[EMAIL PROTECTED]> wrote:

I am new to this but are these issues those of trying to get it to do what sqlite 
it > is not designed for. I quote the book

The Definitive Guide to SQLite - Chapter 1 --- Networking
"  Again, most of these limitations are intentional—they are a result of 
SQLite's
design. Supporting high write concurrency, for example, brings with it great
deal of complexity and this runs counter to SQLite's simplicity in design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis].  This should come as no surprise.
In short, what SQLite can't do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use embedded
relational database whose code base is within the reach of the programmers
using it. And in many respects it can do what many other databases cannot, such
as run in embedded environments where actual power consumption is a limiting
factor. "


Dennis Cote and Scott Hess have hit the nail on the head, in my
opinion. As you quoted, chapter 1 sites networking support as a big
disclaimer, for the simple reason that I want to make it clear to the
reader what SQLite does not do, so that ideally he/she can make a
decision right up front whether or not if might be useful for their
purposes.

Having said that, p 196 in chapter 5 includes a sidebar on network
file systems and this whole issue. The issue is in theory quite
simple: SQLite supports the requisite locking semantics such that if
the underlying network file system supports locking in its network
files system in the same way it does its local file system (which it
should), then then SQLite will work correctly. Period. That is to say,
SQLite does locking the same way over network file systems as it does
local file systems. Since this locking code works correctly on local
file systems, and network file systems use exactly the same locking
semantics, then one may logically conclude that SQLite works correctly
over network file systems. End theory.

In practice, however, it's a whole other question: How do you know
that a particular network file system implements every part of this
locking protocol accurately under all conditions? Plus, you are
introducing many other variables -- namely the network, both logical
(TCP stack bugs, etc.) and physical (bad NICs, switches, cables,
punchdowns, etc.). Everything becomes much more complex, even if the
NFS implementation is flawless.

But let's consider just the network file system. With respect to it,
the short answer is, no one can tell you definitively that it's not
without bugs. And a single bug is all it takes to cause proper locking
to fail, no fault to SQLite. It comes down to the logical conundrum
that you can't prove a negative. Even the people who implemented a
particular network file system cannot be 100% sure it is not without a
bug somewhere that may adversely affect locking in some particular
scenario (client crash, or network problem).

So the official answer from SQLite, wisely, is that they cannot
certify that a particular network file system is without bugs, and
therefore whether or not SQLite works over a particular NFS
implementation for your particular application. There are simply too
many variables to account for, but more importantly, *all* of these
variables are external to SQLite. SQLite uses standard system calls
for locking. If the NFS implements those locking calls correctly, then
SQLite will work correctly.

So that's the disclaimer. It's not that SQLite lacks special code to
make it work over network file systems. Much to the contrary, it has
everything required for it to work. The disclaimer simply states that
you cannot expect SQLite's developers to speak for code or systems
they did not write. They can tell you for sure that SQLite follows
POSIX locking semantics on UNIX and the equivalent on Windows. The
locking semantics used on local file systems are the same used for
network file systems (that is, they should be transparent --- no
additional coding is required to use them). What SQLite's developers
cannot tell you that Samba is without bugs, or that Linux NFS is
without bugs, which may be connecting to a OS X NFS server, which is
also without bugs, etc. They didn't write the code. Theoretically,
SQLite should work perfectly in all cases, but one small bug in a
single NFS implementation on either client or server, and either a
lock may exist too long, leaving other clients locked out, or may not
happen at all, letting multiple clients write to the same database at
the same time, resulting in almost assured corruption.

So, what can you do? Well, things may still work out fine for you, you
just have to determine empirically whether or not your particular
system's network file system has any serious locking bugs that come
into play for your particular application.


Is it rea

Re: [sqlite] Multiple prepared queries

[John Stanton]

Wesley W. Terpstra wrote:

On Feb 12, 2007, at 1:21 AM, John Stanton wrote:

Prepared queries are actually the query compiled into the  
metalanguage, VDBE.  You can have many statements in one  transaction, 
and each of those statements can be reusing a VDBE  virtual machine 
instance with a current set of data bound to it.


It is a good idea to store and resuse compiled SQL because you  avoid 
the tedious and unecessary process of recompiling the same  SQL over 
and over during the life of an application invocation.



Yes, I've understood this.
Unfortunately, an sqlite3_stmt is not just the VDBE, but also the  
current execution state and bindings of that VDBE.


|deally, what I'd like is something where a user can prepare a bunch  of 
SQL statements, creating query objects, like this:


local
  open SQL.Template
in
  val Query1 = query db "select * from table where x="iS" and  y="iI";" 
oS oI $

  val Query2 = query db "select * from table2 where x="iS";" oS oI $
end

Here the iS and iI refer to input string and input integer  
respectively. The oS and oI refer to output string and integer.

Then, later the user might do the following:

val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" &  3)
val Iterator2 = SQL.execute Query2 4

case Iterator1a () of
   NONE => print "End of this table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^  
"\n")


case Iterator1b () of
   NONE => print "End of the table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^  
"\n")


case Iterator1a () of
   NONE => print "End of this table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^  
"\n")


The point is that here I use the same prepared query twice, with two  
different associated states and bindings. It seems this sort of  
interface is impossible at the moment without reparsing the SQL  
statement again. Even if I choose not to expose the 'step' method as  an 
iterator, during the processing of a query's results there's  nothing I 
can do to prevent a user from executing  the query again.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 




Look at how Sqlite implements callbacks in sqlite3_exec to discover
a way to implement your language.  It looks like it requires a little
extra in your language compiler to resolve the forward references.
Doing that will ensure that the transactions are atomic and that
database integrity is maintained.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Need help on build query.

[drh]

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 2/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> > Igor is correct - you cannot do that now.  But I have sometimes
> > wondered if it would be nice to add an exec() function to SQLite
> > that would recursively invoke its argument as an SQL script.
> > For example:
> >
> >SELECT exec('DELETE FROM ' || quote(name))
> >  FROM sqlite_master
> > WHERE ;
> >
> > There would be security implications here, of course.  Clearly
> > one would need to make sure that user-entered SQL (such as can
> > be seen at http://www.sqlite.org/cvstrac/new) does not have access
> > to the eval() function, just as it does not have access to the
> > the load_extension() function.
> >
> 
> 
> Perhaps if the row in sqlite_master were deleted that could invoke the drop
> table functionality?

That would certainly make the table vanish from view.  But
it wouldn't free the associated disk space.  And if you failed
to delete associated indices, that also could give trouble.

Notice also that you cannot easily edit the SQLITE_MASTER table - 
the library won't let you unless you use a magic, undocumented
pragma first.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[Wesley W. Terpstra]

On Feb 12, 2007, at 1:21 AM, John Stanton wrote:
Prepared queries are actually the query compiled into the  
metalanguage, VDBE.  You can have many statements in one  
transaction, and each of those statements can be reusing a VDBE  
virtual machine instance with a current set of data bound to it.


It is a good idea to store and resuse compiled SQL because you  
avoid the tedious and unecessary process of recompiling the same  
SQL over and over during the life of an application invocation.


Yes, I've understood this.
Unfortunately, an sqlite3_stmt is not just the VDBE, but also the  
current execution state and bindings of that VDBE.


|deally, what I'd like is something where a user can prepare a bunch  
of SQL statements, creating query objects, like this:


local
  open SQL.Template
in
  val Query1 = query db "select * from table where x="iS" and  
y="iI";" oS oI $

  val Query2 = query db "select * from table2 where x="iS";" oS oI $
end

Here the iS and iI refer to input string and input integer  
respectively. The oS and oI refer to output string and integer.

Then, later the user might do the following:

val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" &  3)
val Iterator2 = SQL.execute Query2 4

case Iterator1a () of
   NONE => print "End of this table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^  
"\n")


case Iterator1b () of
   NONE => print "End of the table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^  
"\n")


case Iterator1a () of
   NONE => print "End of this table"
|  SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^  
"\n")


The point is that here I use the same prepared query twice, with two  
different associated states and bindings. It seems this sort of  
interface is impossible at the moment without reparsing the SQL  
statement again. Even if I choose not to expose the 'step' method as  
an iterator, during the processing of a query's results there's  
nothing I can do to prevent a user from executing  the query again.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Need help on build query.

[Jay Sprenkle]

On 2/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



Igor is correct - you cannot do that now.  But I have sometimes
wondered if it would be nice to add an exec() function to SQLite
that would recursively invoke its argument as an SQL script.
For example:

   SELECT exec('DELETE FROM ' || quote(name))
 FROM sqlite_master
WHERE ;

There would be security implications here, of course.  Clearly
one would need to make sure that user-entered SQL (such as can
be seen at http://www.sqlite.org/cvstrac/new) does not have access
to the eval() function, just as it does not have access to the
the load_extension() function.




Perhaps if the row in sqlite_master were deleted that could invoke the drop
table functionality?

Re: [sqlite] Multiple prepared queries

[John Stanton]

Prepared queries are actually the query compiled into the metalanguage, 
VDBE.  You can have many statements in one transaction, and each of 
those statements can be reusing a VDBE virtual machine instance with a 
current set of data bound to it.


When you reset the compiled statement you make it ready for a new set of 
bindings and when you finalize it you destroy it.


As far as I can see there is no simple way in Sqlite to use cursors and 
have multiple transactions current.  I am sure that with ingenuity you 
could implement some cursor-like functionality.


It is a good idea to store and resuse compiled SQL because you avoid the 
tedious and unecessary process of recompiling the same SQL over and over 
during the life of an application invocation.


Wesley W. Terpstra wrote:

Good evening.

I've been working on an SQLite3 binding for Standard ML (SML)
targeting the MLton compiler. I've read all the material on the C API
I've found, but I have some questions about the VM prepare/step
methods. Specifically, I am confused about the lifetime of non-
finalized queries. It seems that there can be several prepared
queries at once, and the documentation implies that this represents a
savings: the queries need not be reparsed. However, it also means
it's possible to interleave steps to prepared queries.

Here are the things I believe to be true, but I would like
confirmation / denial, as it affects the interface I expose in SML:

Suppose you have two prepared queries: q1 and q2. I currently believe
that it is an error to execute: step q1, step q2, step q1.

In fact, I believe that after step q1, you must either call finalize
or reset on q1 before you can call step q2.

If I'm wrong with the above, what happens if I step q1, start a
transaction, then step q1 again?

What happens to an open transaction if you reset a query? I imagine
that a user might be running a select statement, have found the row
they were interested in, and then called reset rather than read the
rest of the result set. In this case, I would like to be able to
continue running new statements within the query. Similarly, I might
cancel the query with finalize. The documentation seems to imply that
this will abort a transaction?

I ask all this, because I think it would be convenient to allow uses
to create query objects that they can call / reuse at a later point.
However, it's not clear to me if I should expose the 'step' interface
to a user. If interleaving steps is bad, it is possible to design the
interface to allow stopping a query prematurely, without exposing
'step'. If this premature cancelation breaks an open transaction,
then perhaps I should forbid it as well.

Thanks for any clarification on these points.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple prepared queries

[Wesley W. Terpstra]

Thanks for your answers!

On Feb 12, 2007, at 1:58 AM, [EMAIL PROTECTED] wrote:

"Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote:

Suppose you have two prepared queries: q1 and q2. I currently believe
that it is an error to execute: step q1, step q2, step q1.


No.  Queries can be interleaved this way.


Ok.

What is the defined behaviour of interleaved inserts and selects? I  
gather since an insert completes in a single step there is no  
problem? Active queries now return the result if they have not been  
stepped past this point? Will an UPDATE ... WHERE ... also *always*  
complete in a single step?



For that matter SQLite is reentrant, and this fact is used within
the implementation.


So, I can create subqueries within user-code in response to some of  
the rows I've retrieved? That's pretty cool



An implicit transaction was already started when you started
stepping q1.  That implicit transaction will not end until all
active queries have finished.

If you run the BEGIN command, all that does is set a flag that
tells SQLite not to commit once all queries have finished but
rather to keep the transaction open.


Is there some sort of 'open transaction counter'? ie: if I  
begin&commit within the scope of an 'automatic' transaction, it will  
not commit until the automatic transaction completes?


Suppose I ran: begin, select *, step1, commit, step2. Does the commit  
fail because there is still a nested, active query? If yes, then  
great. If not, then what is the behaviour of step2?



What happens to an open transaction if you reset a query?


If you have not run BEGIN, then the transaction will end once *all*  
active queries have completed.


While most helpful in understanding the mechanism (is this documented  
somewhere?), that didn't actually answer this question:


What I meant is that if I am running a "select * from giant_table;"  
and find on row 10/800 the data I was looking for, I might want to  
just finalize the select statement. My question is what happens to an  
explicitly opened transaction that included this select statement?  
The documentation implies that the transaction will be aborted, which  
is not very desirable.


Here's a concrete example: I need to compute some statistic and write  
it into a table. I start a transaction and then begin processing some  
accounting records. Part-way through reading those records, I realize  
I have enough information, so I stop the select with finalize/reset,  
and then run an update, then commit. Is this ok?


Now that I'm working on wrapping bind_text/bind_text16, I have  
another question: what is the native on-disk format of the strings?  
The documentation suggests that open/open16 determine the encoding.  
Does this mean that if I created the database with open, then a  
future bind_text16 will do an automatic charset conversion? ie:  
should I pick one encoding and stick with it, or allow users to mix  
them? (SML has its own Unicode character classes, so I have to  
convert to UTF-8/16 anyway. It would be a shame to do it twice.)


It's ackward that there is no separation between a compiled VM and a  
compiled VM's state. ie: If I want to run the same precompiled query  
twice in parallel (since this is apparently well defined), then I  
would need two separate instances of the state.


How hard would it be to create a method that cloned only the byte- 
code: stmt* sqlite3_clone(stmt*)? If I wrote this, would it be  
accepted? TBH, it would be more helpful if there was an intermediate  
form between prepare and the stmt.


Again, thanks for your answers. I just want to be sure that I don't  
allow breaking SQLite's invariants.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Backing up a SQlite database

[Brandon, Nicholas \(UK\)]

>Derrell,

>Just to clarify, you don't need to use an exclusive transaction. That
will acquire a write lock and unnecessarily block 
>all other readers as well. You only need to hold a read lock to prevent
any other process from acquiring a write lock.

>Dennis Cote

I asked a similar question last year...
http://article.gmane.org/gmane.comp.db.sqlite.general/17946




This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Need help on build query.

[drh]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> Artem Yankovskiy 
> wrote:
> > I know about DROP TABLE.
> > I have not knew when compatibility DROP TABLE and
> > SELECT...
> >
> > DROP TABLE (select name from sqlite_master where...)
> > did not work.
> > Can I build resembling query?
> 
> No. You will have to run the select, store table names in memory, then 
> build and run a separate DROP TABLE query for each table name.
> 

Igor is correct - you cannot do that now.  But I have sometimes
wondered if it would be nice to add an exec() function to SQLite
that would recursively invoke its argument as an SQL script.
For example:

   SELECT exec('DELETE FROM ' || quote(name))
 FROM sqlite_master
WHERE ;

There would be security implications here, of course.  Clearly
one would need to make sure that user-entered SQL (such as can
be seen at http://www.sqlite.org/cvstrac/new) does not have access
to the eval() function, just as it does not have access to the
the load_extension() function.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-