[sqlite] Feature request

[Simon Slavin]

I welcome discussion of a feature request I intend to make.  Please  
suggest better function names, more considerations, or anything else  
interesting.



int sqlite3_change_count(sqlite3*)

Returns an integer which is incremented whenn a change is made to any  
table in the database.  May be the value stored in bytes 24..27 of  
file header or something else if that's not what I really want.

Considerations:

I don't mind whether 'a change' means the number of 'COMMIT's, the  
number of change commands that have been committed, the number of rows  
changed, or the number of fields changed.

I'm not too worried if it changes when no detectable change has been  
made (e.g. writing a value to a field that's already set to that  
value), but it should not stay the same when something really does  
change.

The value should decrease iff wrap-around value has been reached.   
However, normal programming practice would check to see whether the  
value has changed, rather than whether the value has increased.

If it's undesirable to introduce yet another function to the library,  
alternatives would be to introduce a read-only PRAGMA value, or  
something like 'now', whatever that is.



This is related to a thread started by Ben Harper on May the 4th.  I  
happen to want the same thing he did for two reasons: so I can tell if  
another app is messing with my data behind my back, and so I can  
compare two copies of a database for synchronisation or crash- 
recovery.  I'm aware I can read the header manually, or check the  
file's modification timestamp, but I would like the function for  
neatness' sake.

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

Re: [sqlite] about insert into select

[Dan]

On May 21, 2009, at 8:43 AM, Wenton Thomas wrote:

> What's the execution sequence about  " insert ino  A select   
> from B "?
> I  means,which is correct prescription in the following:
> (1) select  all  rows from B at first, then  insert all  the result  
> into table A;
> (2) select a row from B ,then insert the row into table A  
> immediately, repeat the precess.

Usually strategy 2. Falls back to 1 if A and B are the same table, or
if there are triggers that imply inserting rows into B may modify A.

Dan.


>
>
>
>
>
> ___
> 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] Question about reading all database data into memory

[rajyalakshmi bommaraju]

HI,
 
.  If I have to read all the database data into memory, I needed to execute 
sqlite3_prepare_v2 statement,and then sqlite3_step to access the rows, and 
processing each row to get the rows content.  This does not look efficient. Is 
there any other way to do this?
 
Thanks
Raji


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

Re: [sqlite] Importing Data into a SQLite database from a MySQL SQL Dump File

[Simon Slavin]

On 21 May 2009, at 4:43am, Debjit Saha wrote:

> I have a SQL Dump created from a database in MySQL.

This is a text file that looks like a lot of SQL commands, right ?   
And I assume this is a one-off thing you need to do, not something  
you're going to need to do repeatedly.

> Now I want to import
> that dump into an SqLite database. Is thatpossible? If yes, please  
> tell me
> how.


Use the 'sqlite3' command.

Create the tables manually, because the syntax isn't quite the same  
and you're going to want to use slightly different column types.

Copy one INSERT command out of the dump and test it by pasting it into  
sqlite3 and checking to see that it works as intended.  You may need  
to make minor changes such as globally replacing one kind of quote  
character with another.

Once you have a format that works, put all the appropriate INSERT  
commands into a text file and use the '.read' command from 'sqlite3'  
to execute all the commands.

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

[sqlite] Importing Data into a SQLite database from a MySQL SQL Dump File

[Debjit Saha]

I have a SQL Dump created from a database in MySQL. Now I want to import
that dump into an SqLite database. Is thatpossible? If yes, please tell me
how.

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

Re: [sqlite] about insert into select

[John Machin]

On 21/05/2009 11:43 AM, Wenton Thomas wrote:
> What's the execution sequence about  " insert ino  A select  from B "?
> I  means,which is correct prescription in the following:
> (1) select  all  rows from B at first,

meanwhile this product (frequently chosen for use on devices with 
minimal memory and slow pseudo-disk storage) would be buffering up the 
selected rows where?

> then  insert all  the result into table A;
> (2) select a row from B ,then insert the row into table A immediately, repeat 
> the precess.

So the least implausible possibility is ... ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about insert into select

[Dennis Cote]

Wenton Thomas wrote:
> What's the execution sequence about  " insert ino  A select  from B "?
> I  means,which is correct prescription in the following:
> (1) select  all  rows from B at first, then  insert all  the result into 
> table A;
> (2) select a row from B ,then insert the row into table A immediately, repeat 
> the precess.
>
>
When in doubt ask sqlite. :-)

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .explain on
sqlite> create table t1(a,b);
sqlite> create table t2(c,d);
sqlite> explain insert into t2 select * from t1;
addr  opcode p1p2p3p4 p5  comment 
  -        -  --  -
0 Trace  0 0 000  
1 Goto   0 13000  
2 Noop   0 0 000  
3 OpenWrite  1 3 0 2  00  
4 OpenRead   0 2 0 2  00  
5 Rewind 0 10000  
6 NewRowid   1 2 000  
7 RowData0 1 000  
8 Insert 1 1 2 t2 0b  
9 Next   0 6 000  
10Close  0 0 000  
11Close  1 0 000  
12Halt   0 0 000  
13Transaction0 1 000  
14VerifyCookie   0 2 000  
15TableLock  0 3 1 t2 00  
16TableLock  0 2 0 t1 00  
17Goto   0 2 000  
sqlite>


 From this it's easy (relatively) to see that inserts each row as it 
find them while executing the select.

HTH
Dennis Cote

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

[sqlite] about insert into select

[Wenton Thomas]

What's the execution sequence about  " insert ino  A select  from B "?
I  means,which is correct prescription in the following:
(1) select  all  rows from B at first, then  insert all  the result into table 
A;
(2) select a row from B ,then insert the row into table A immediately, repeat 
the precess.



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

Re: [sqlite] most efficient way to get 1st row

[Shane Harrelson]

We're not talking about transaction control - we're talking about limiting
the size of the result set.   And LIMIT/OFFSET clauses are not supported by
the SQL-92 standard.   LIMIT is listed as a reserved word, but that's it.

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt




On Wed, May 20, 2009 at 2:47 PM, John Stanton  wrote:

> What  makes you think that transaction control is not part of SQL-92?
>
> If a database engine does not support transaction control it is not a
> full implementation.   Transaction control is at the very heart of
> effective data management.
>
> Shane Harrelson wrote:
> > Additionally, it's important to note that the LIMIT/OFFSET clause is not
> > standard SQL, and although it is supported by SQLite, and many other SQL
> > engines, there are some that do NOT support it, most notably Microsoft
> SQL
> > Server.
> >
> > HTH.
> > -Shane
> >
> >
> > On Tue, May 19, 2009 at 2:23 PM, Sam Carleton 
> wrote:
> >
> >
> >> Kees Nuyt wrote:
> >>
> >>
> >>> Imagine a SELECT with an ORDER BY which makes SQLite sort
> >>> the resultset before it can return the first row in the
> >>> resultset. Need I say more?
> >>>
> >>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> >>>
> >>>
> >>>
> >> Thank you for the link, it is a VERY useful read, VERY useful!
> >>
> >> Sam
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] most efficient way to get 1st row

[Igor Tandetnik]

John Stanton  wrote:
> Shane Harrelson wrote:
>> Additionally, it's important to note that the LIMIT/OFFSET clause is
>> not standard SQL
>
> What  makes you think that transaction control is not part of SQL-92?

What does LIMIT clause have to do with transaction control, in your 
opinion?

Igor Tandetnik



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

Re: [sqlite] most efficient way to get 1st row

[John Stanton]

What  makes you think that transaction control is not part of SQL-92?

If a database engine does not support transaction control it is not a 
full implementation.   Transaction control is at the very heart of 
effective data management.

Shane Harrelson wrote:
> Additionally, it's important to note that the LIMIT/OFFSET clause is not
> standard SQL, and although it is supported by SQLite, and many other SQL
> engines, there are some that do NOT support it, most notably Microsoft SQL
> Server.
>
> HTH.
> -Shane
>
>
> On Tue, May 19, 2009 at 2:23 PM, Sam Carleton  wrote:
>
>   
>> Kees Nuyt wrote:
>>
>> 
>>> Imagine a SELECT with an ORDER BY which makes SQLite sort
>>> the resultset before it can return the first row in the
>>> resultset. Need I say more?
>>>
>>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
>>>
>>>
>>>   
>> Thank you for the link, it is a VERY useful read, VERY useful!
>>
>> Sam
>>
>> ___
>> 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

[sqlite] multiple connections by multiple threads

[Igor Trindade Oliveira]

Hello,

I like to know if is recommended to do multiple connections from multiple 
threads in Sqlite or the best approach is create just one connection to 
multiple threads.
Each thread has a hight concurrence level. 
I already try to open one connection per thread with that arguments:
sqlite3_open_v2(db.toUtf8().data(), >access, SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE|SQLITE_OPEN_FULLMUTEX , 0)
and with SQLITE_OPEN_NOMUTEX too.
and with sqlite_busy_timeout to 5 seconds, but look likes that i still have 
deadlocks.

Igor


  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

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

Re: [sqlite] most efficient way to get 1st row

[Sam Carleton]

I just learned today that Interbase 7.5 does not support it either.

Shane Harrelson wrote:

Additionally, it's important to note that the LIMIT/OFFSET clause is not
standard SQL, and although it is supported by SQLite, and many other SQL
engines, there are some that do NOT support it, most notably Microsoft SQL
Server.

HTH.
-Shane


On Tue, May 19, 2009 at 2:23 PM, Sam Carleton  wrote:

  

Kees Nuyt wrote:



Imagine a SELECT with an ORDER BY which makes SQLite sort
the resultset before it can return the first row in the
resultset. Need I say more?

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor


  

Thank you for the link, it is a VERY useful read, VERY useful!

Sam

___
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] most efficient way to get 1st row

[Shane Harrelson]

Additionally, it's important to note that the LIMIT/OFFSET clause is not
standard SQL, and although it is supported by SQLite, and many other SQL
engines, there are some that do NOT support it, most notably Microsoft SQL
Server.

HTH.
-Shane


On Tue, May 19, 2009 at 2:23 PM, Sam Carleton  wrote:

> Kees Nuyt wrote:
>
>> Imagine a SELECT with an ORDER BY which makes SQLite sort
>> the resultset before it can return the first row in the
>> resultset. Need I say more?
>>
>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
>>
>>
> Thank you for the link, it is a VERY useful read, VERY useful!
>
> Sam
>
> ___
> 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] Accessing sqlite using javascript

[Simon Slavin]

On 20 May 2009, at 5:14am, Saurabh Pawar wrote:

> This is the best link i have found out till now which will give a  
> very clear view.
>
> https://developer.mozilla.org/en/Storage

Actually, for my own use I wrote a WebKit Plugin which provides an  
interface to some of the calls to sqlite3 -- enough to execute SQL  
commands and find out a few status things.  It's only useful for  
Apple's Safari, or any other browser that uses WebKit, though.

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

Re: [sqlite] SQLite version 3.6.14.1

[MikeW]

D. Richard Hipp  writes:

> 
> SQLite version 3.6.14.1 is now available on the sqlite website: 
http://www.sqlite.org/
> 
...snip...
> 
> As always, please let me know if you encounter any difficulties.
> 
> D. Richard Hipp
> d...@...

Version 3.6.14 ... "*Countless* minor bug fixes" ?!

Regards,
MikeW

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