Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Jay A. Kreibich
On Tue, Jun 15, 2010 at 08:58:22PM -0700, Roger Binns scratched on the wall:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 06/15/2010 07:59 PM, Simon Slavin wrote:
> > The standard assumption about SQLite is that it's faster to do your INSERTs 
> > first, then create the indices.
> 
> If the index is created first then the data in the table and the pages
> making up the index will be interleaved.  That is likely to lead to more and
> further seeks on accessing the index.  Creating the index afterwards will
> result in a contiguous sequence of pages (assuming no existing free pages).

  Contiguous, yes, but the pages may not be in any logical order.
  The internal node pages will get shuffled as the tree is built,
  meaning you might still have a significant number of seeks.

   -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] create index before or after many inserts?

2010-06-15 Thread Eric Smith
Simon Slavin wrote: 

> The standard assumption about SQLite is that it's faster to do your 
> INSERTs first, then create the indices.  How much of a difference this 
> makes depends on a lot of things.  

On what things does it depend?

-- 
Eric A. Smith

Sendmail may be safely run set-user-id to root.
-- Eric Allman, "Sendmail Installation Guide"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 07:59 PM, Simon Slavin wrote:
> The standard assumption about SQLite is that it's faster to do your INSERTs 
> first, then create the indices.

If the index is created first then the data in the table and the pages
making up the index will be interleaved.  That is likely to lead to more and
further seeks on accessing the index.  Creating the index afterwards will
result in a contiguous sequence of pages (assuming no existing free pages).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwYS94ACgkQmOOfHg372QQqngCeJ2itOBTZmY2gGVzk4CKCQWPc
ZMwAn0fZ1fO+ID4K8Ak65RZBrtV03Te3
=a+mh
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] notify all processes of database modification

2010-06-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 08:02 PM, Igor Tandetnik wrote:
> That's just polling, really. SQLite's busy handler tries to access the 
> database, sleeps a little, tries again, sleeps again, and so on. It's not 
> "woken up" the way, say, a thread waiting on a mutex would be.

True, However it does correctly deal with things like a process going away
abruptly which is nice logic to not have to reimplement and test.  I did use
the word "crappy" :-)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwYSw8ACgkQmOOfHg372QQFYwCeLyq8dYG8S97yuOl7eImgLNzT
ZG8An1a/JS3KXEDkCEDuk0rGDtpydnGe
=CoTo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] notify all processes of database modification

2010-06-15 Thread Jay A. Kreibich
On Tue, Jun 15, 2010 at 09:47:23PM -0500, Jay A. Kreibich scratched on the wall:
> On Tue, Jun 15, 2010 at 09:48:53PM -0400, Igor Tandetnik scratched on the 
> wall:
> > Rich Rattanni  wrote:
> > > The creator of SQLite actually gave a talk about using an SQLite
> > > database as a means for IPC (it was available on youtube, maybe you
> > > can find it).  If you want an 'sqlit-ish' way, why not use that trick?
> > 
> > Well, what trick? There is no way I know of for another process to
> > get notified of changes to the database. That is, other than polling,
> > or a side channel independent of SQLite.
> 
>   Depends on the type of RPC.  SQLite makes a pretty good message queue

Err... make that IPC.  We all know SQLite for RPC doesn't work that well.

>   system, since clients can read/write/disconnect/reconnect at will,
>   and the transaction system makes it all safe and sound.
> 
> 
>   If polling produces acceptable speed, where one applicatoin can react
>   to changes made by the other in a 10 to 15 secnod window, the best
>   bet is likely to be PRAGMA user_version.
> 
>   http://www.sqlite.org/pragma.html#pragma_schema_version
> 
>-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

-- 
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] notify all processes of database modification

2010-06-15 Thread Igor Tandetnik
Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 06/15/2010 06:48 PM, Igor Tandetnik wrote:
>> Well, what trick? There is no way I know of for another process to get 
>> notified of changes to the database. That is, other than
>> polling, or a side channel independent of SQLite. 
> 
> One way I have done it in the past is to have a second database that is
> exclusively locked by whoever does writing.  Waiters then also try a begin
> immediate with infinite timeouts.  After the writer updates the main
> database they release the lock on the second one which wakes up all the
> waiters.  Rinse and repeat.

That's just polling, really. SQLite's busy handler tries to access the 
database, sleeps a little, tries again, sleeps again, and so on. It's not 
"woken up" the way, say, a thread waiting on a mutex would be.
-- 
Igor Tandetnik

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


Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Simon Slavin

On 16 Jun 2010, at 3:55am, Eric Smith wrote:

> Let's say my app has (only) inserts followed by (only) reads.  
> 
> The reads are best served by some indices.  So I can create the indices 
> before the INSERTs, or after them.  
> 
> In general, should I expect a run time perf difference between these two 
> options?  

The standard assumption about SQLite is that it's faster to do your INSERTs 
first, then create the indices.  How much of a difference this makes depends on 
a lot of things.

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


[sqlite] create index before or after many inserts?

2010-06-15 Thread Eric Smith
Let's say my app has (only) inserts followed by (only) reads.  

The reads are best served by some indices.  So I can create the indices 
before the INSERTs, or after them.  

In general, should I expect a run time perf difference between these two 
options?  

Eric 

-- 
Eric A. Smith

Louis Pasteur's theory of germs is ridiculous fiction.
-- Pierre Pachet, Professor of Physiology at Toulouse, 1872
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] notify all processes of database modification

2010-06-15 Thread Jay A. Kreibich
On Tue, Jun 15, 2010 at 09:48:53PM -0400, Igor Tandetnik scratched on the wall:
> Rich Rattanni  wrote:
> > The creator of SQLite actually gave a talk about using an SQLite
> > database as a means for IPC (it was available on youtube, maybe you
> > can find it).  If you want an 'sqlit-ish' way, why not use that trick?
> 
> Well, what trick? There is no way I know of for another process to
> get notified of changes to the database. That is, other than polling,
> or a side channel independent of SQLite.

  Depends on the type of RPC.  SQLite makes a pretty good message queue
  system, since clients can read/write/disconnect/reconnect at will,
  and the transaction system makes it all safe and sound.


  If polling produces acceptable speed, where one applicatoin can react
  to changes made by the other in a 10 to 15 secnod window, the best
  bet is likely to be PRAGMA user_version.

  http://www.sqlite.org/pragma.html#pragma_schema_version

   -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] notify all processes of database modification

2010-06-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 06:48 PM, Igor Tandetnik wrote:
> Well, what trick? There is no way I know of for another process to get 
> notified of changes to the database. That is, other than polling, or a side 
> channel independent of SQLite.

One way I have done it in the past is to have a second database that is
exclusively locked by whoever does writing.  Waiters then also try a begin
immediate with infinite timeouts.  After the writer updates the main
database they release the lock on the second one which wakes up all the
waiters.  Rinse and repeat.

This is an extremely crappy signaling mechanism, but does work if the use
case is fully understood.  My reason for using it was because it works on
all platforms and saved me from having to come up with and test various
platform specific mechanisms.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwYNHAACgkQmOOfHg372QQcggCcDcPa2k0LCaQ8pMumMPT0dXdQ
ytEAn3TMFVGTfKn6EJxEoerRifV34tgT
=Eoem
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] notify all processes of database modification

2010-06-15 Thread Igor Tandetnik
Rich Rattanni  wrote:
> The creator of SQLite actually gave a talk about using an SQLite
> database as a means for IPC (it was available on youtube, maybe you
> can find it).  If you want an 'sqlit-ish' way, why not use that trick?

Well, what trick? There is no way I know of for another process to get notified 
of changes to the database. That is, other than polling, or a side channel 
independent of SQLite.
-- 
Igor Tandetnik

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


Re: [sqlite] How do you switch to daily digest mode for the sqlliteemail

2010-06-15 Thread Igor Tandetnik
Lake Lake  wrote:
> I tried doing what the page says, but it acted like i was trying to 
> reregister.

Click  "Unsubscribe or edit options" button at the very bottom.
-- 
Igor Tandetnik

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


[sqlite] How do you switch to daily digest mode for the sqllite email

2010-06-15 Thread Lake Lake
I tried doing what the page says, but it acted like i was trying to reregister.

Sry noob question
Mark


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


Re: [sqlite] notify all processes of database modification

2010-06-15 Thread Rich Rattanni
The creator of SQLite actually gave a talk about using an SQLite
database as a means for IPC (it was available on youtube, maybe you
can find it).  If you want an 'sqlit-ish' way, why not use that trick?

One advantage of using SQLite is that debugging / backtracing becomes
easier.  Since your messages are passed through a database, you can
easily backtrace IPC calls (by perhaps setting a 'complete' field
instead of deleting a IPC message) and you can inject IPC messages
easily by using the SQLite CLI.


On Tue, Jun 15, 2010 at 5:01 PM, Igor Tandetnik  wrote:
> arno  wrote:
>> I have two processes connecting to a database. Both can modify database. When
>> some process modify database, I want the other to be notified (so, it can
>> reload data).
>> Currently, I use unix sockets, so a process can notify all listener process
>> that something has changed. But I wonder if there's a better way to achieve
>> that.
>> I've tried to a create a custom function (with sqlite3_create_function), and
>> use triggers. But unfortunately, my trigger was executed for the modifying
>> process.
>
> SQLite is not an interprocess communication mechanism. Any notification of 
> the sort you envision must be done outside of SQLite.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] is there any way for create a sqlite blob field with python?

2010-06-15 Thread Simon Slavin

On 15 Jun 2010, at 10:26pm, Roger Binns wrote:

> On 06/15/2010 02:16 PM, Simon Slavin wrote:
>> An image file is just a file.  If you really want to store a file in a 
>> database, open the file, read the contents, and store what you read in a 
>> BLOB field.
> 
> That is the general correct answer but not useful in this case.  Just like
> SQLite Python also does dynamic/manifest typing.  That means you have to
> supply the file contents in the appropriate type.  Python 2 makes no
> distinction between regular strings and sequences of bytes so just providing
> the contents of a file would cause it to be regarded as a string.  Python 3
> does make the distinction so this is no longer an issue.

Thank you.  I continue to learn.

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


Re: [sqlite] is there any way for create a sqlite blob field with python?

2010-06-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 02:16 PM, Simon Slavin wrote:
> An image file is just a file.  If you really want to store a file in a 
> database, open the file, read the contents, and store what you read in a BLOB 
> field.

That is the general correct answer but not useful in this case.  Just like
SQLite Python also does dynamic/manifest typing.  That means you have to
supply the file contents in the appropriate type.  Python 2 makes no
distinction between regular strings and sequences of bytes so just providing
the contents of a file would cause it to be regarded as a string.  Python 3
does make the distinction so this is no longer an issue.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwX8A0ACgkQmOOfHg372QRgZgCgnP+U0tEHAPFoNxhHxuwNMTYn
7wgAoLiRJnn3RdiSWPPScM3p3jmTJA8E
=oMVD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] is there any way for create a sqlite blob field with python?

2010-06-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 01:46 PM, Fabio Spadaro wrote:
> I am developing an interface to Python 's SQLite and would it be
> you can store files in the sqlite database. To do this
> obviously need to create a blob field but how to store image files?

If you are using Python 2 then use the buffer type.  If using Python 3 then
use bytes.

Py2:

  cursor.execute("insert into foo(image) values(?)",
   (buffer(open(".../foo.jpg", "rb").read()), ))

Py3:

  cursor.execute("insert into foo(image) values(?)",
   (open(".../foo.jpg", "rb").read(), ))


If you use APSW then you can also use the incremental blob API:

  http://apsw.googlecode.com/svn/publish/blob.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwX7yAACgkQmOOfHg372QSIkQCfej/1Zw7htLxZtVFGsIsxb91m
YnkAniPp7aScNJITD3xYOmH4MC9e4Asx
=M6pK
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] is there any way for create a sqlite blob field with python?

2010-06-15 Thread Simon Slavin

On 15 Jun 2010, at 9:46pm, Fabio Spadaro wrote:

> I am developing an interface to Python 's SQLite and would it be
> you can store files in the sqlite database. To do this
> obviously need to create a blob field but how to store image files?

An image file is just a file.  If you really want to store a file in a 
database, open the file, read the contents, and store what you read in a BLOB 
field.

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


Re: [sqlite] notify all processes of database modification

2010-06-15 Thread Igor Tandetnik
arno  wrote:
> I have two processes connecting to a database. Both can modify database. When
> some process modify database, I want the other to be notified (so, it can
> reload data).
> Currently, I use unix sockets, so a process can notify all listener process
> that something has changed. But I wonder if there's a better way to achieve
> that.
> I've tried to a create a custom function (with sqlite3_create_function), and
> use triggers. But unfortunately, my trigger was executed for the modifying
> process.

SQLite is not an interprocess communication mechanism. Any notification of the 
sort you envision must be done outside of SQLite.
-- 
Igor Tandetnik


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


[sqlite] notify all processes of database modification

2010-06-15 Thread arno
Hi,
I have two processes connecting to a database. Both can modify database. When
some process modify database, I want the other to be notified (so, it can
reload data).
Currently, I use unix sockets, so a process can notify all listener process
that something has changed. But I wonder if there's a better way to achieve
that.
I've tried to a create a custom function (with sqlite3_create_function), and
use triggers. But unfortunately, my trigger was executed for the modifying
process.
So, is there a sqlitish way to have process notifications, or should I stick
to using unix sockets to tell other processes something has changed in the
database.
I'm using C++ language (C api for sqlite)

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


[sqlite] is there any way for create a sqlite blob field with python?

2010-06-15 Thread Fabio Spadaro
Hi all.
I am developing an interface to Python 's SQLite and would it be
you can store files in the sqlite database. To do this
obviously need to create a blob field but how to store image files?

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Avoiding Database Is Locked Error

2010-06-15 Thread sub sk79
Hi Shawn,

>  demonstrate SQLite best
>  practices, including how to correctly handle error conditions, such as
>  the database is locked condition?

>  If you pre-select and then modify, you have to be aware enough to
>  realize you MUST wrap the whole process in a manual transaction,
>  and you still need to know how to deal with all the locking and
>  busy issues that come with that.

One way to assure use of best-practices in dealing with SQLite
nitty-gritties of transactions, locking and busy errors would be to
use a tool like StepSqlite PL/SQL compiler
(https://www.metatranz.com/stepsqlite).
It automates SQLite best-practices (as recommended by Jay, Pavel and
Simon in this thread)  by wrapping things in transactions,  handling
BUSY errors and retrying after delay and others like preparing all SQL
statements only once etc.

SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
writing your code instead of having to learn to deal with SQLite
idiosyncrasies right at the beginning.

Full disclosure: I am the creator of StepSqlite.

-Swapnil Kashikar
support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Avoiding Database Is Locked Error

2010-06-15 Thread Jay A. Kreibich
On Tue, Jun 15, 2010 at 04:36:15PM +0100, Simon Slavin scratched on the wall:
> 
> On 15 Jun 2010, at 4:23pm, Robert Latest wrote:
> 
> > Instinctively I'd rather first SELECT, store the
> > results, finalize the SELECT statement and then get to work on its
> > result using the stored data. It's just that without intermediate
> > storage it's a bit easier (no need to do any ressource management),
> > and I've found nothing in the docs that says I shouldn't do that.
> 
> On a standard desktop system where RAM is cheap, your principle
> of storing all the SELECT data before beginning your changes is sound. 

  Depends on the database, but in general I suppose that's true.

> Technically there's no reason not to interleave the _step() with
> changing the data, but it requires a detailed understanding of how
> SQLite works,

  Pre-selecting the data doesn't really simplify the situation.  You
  still need to deal with transactions and locking issues.  In
  specific, you need to wrap the whole process-- the read, store, and
  modify steps-- into a single transaction or you risk having the
  database change between the read and modify steps, possibly
  invalidating the modifications you're trying to make.

  Once you go that far, interleaving the select and modify commands is
  more or less the same thing.

> especially if the changes you make would have changed the result of
> the SELECT command. [1]

  OK, yeah, that's an issue.  There isn't any way to do this in a 
  deterministic way when using interleaved select/modify statements.
  
  Most people don't do that, however... If you need to modify the
  table you're scanning, you can usually roll the whole thing up
  into one very large and complex update command.  

> Storing all the SELECT data before making your changes means you
> don't have to understand these technicalities,

  I disagree with this.  Doing the pre-select doesn't get rid of any of
  the transaction issues.  In fact, it makes it worse.  If you
  interleave the commands, the select's autocommit transaction protects
  the whole process.  If the developer never issues or thinks about a
  transaction, it all works correctly and safely.
  
  If you pre-select and then modify, you have to be aware enough to
  realize you MUST wrap the whole process in a manual transaction,
  and you still need to know how to deal with all the locking and
  busy issues that come with that.

   -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] Avoiding Database Is Locked Error

2010-06-15 Thread Simon Slavin

On 15 Jun 2010, at 4:23pm, Robert Latest wrote:

> Instinctively I'd rather first SELECT, store the
> results, finalize the SELECT statement and then get to work on its
> result using the stored data. It's just that without intermediate
> storage it's a bit easier (no need to do any ressource management),
> and I've found nothing in the docs that says I shouldn't do that.

On a standard desktop system where RAM is cheap, your principle of storing all 
the SELECT data before beginning your changes is sound.  Technically there's no 
reason not to interleave the _step() with changing the data, but it requires a 
detailed understanding of how SQLite works, especially if the changes you make 
would have changed the result of the SELECT command. [1] Storing all the SELECT 
data before making your changes means you don't have to understand these 
technicalities, and it ensures that your code is engine-agnostic in case you 
ever have to convert to using a different SQL engine.

[1] I did a quick google for documentation on this but didn't find anything.

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


Re: [sqlite] INSERT is not committing from c#

2010-06-15 Thread Simon Slavin

On 15 Jun 2010, at 3:07pm, nmartin wrote:

> I have a C# from which I am trying to perform a COMMIT. The INSERT does not
> throw an error but no data is inserted.

What makes you think that no data is inserted ?  If you quit your program after 
those instructions and examine the database with the command-line tool, does it 
show the newly-inserted row ?

>{
>SQLiteTransaction liteTransaction =
> Connection.BeginTransaction();
>SQLiteCommand command = new
> SQLiteCommand(insert.BuildInsert(tableName, keyValuePairs), Connection);
>command.Transaction = liteTransaction;
>command.ExecuteNonQuery();
>liteTransaction.Commit();
>}
>catch (SQLiteException e)
>{
>//error
>connection.Close();
>}

That depends on how your library works.  I have no idea what, for example 
'.BuildInsert()' does because it's not part of SQLite, nor can I tell if you're 
using .Commit() properly.  You might want to ask about the above in a list 
where they understand whatever library 'SQLiteTransaction' is from.

> And Just and Explicit Commit:
> 
> INSERT INTO TestData
> (deepbreathing,usertechnique,guidedimagery,yoga,exercise,meditation,prayer,somethingelse,howoften,numtechniques,userreturn,chosentechnique,chosentechnique)VALUES
> ("true","I will try to use deep
> breathing.","false","true","false","false","true","true","When I'm
> stressed","1","true","deepbreathing","deepbreathing"); COMMIT;

Strings in SQLite are surrounded with single quotes, not double quotes.  And be 
careful of your apostrophe.  For testing purposes leave it out until you're 
sure your software works generally.

Also, you show a minor misunderstanding of how transactions work.  Some 
programmers omit all commands to do with transactions.  If SQLite finds it's 
doing a data-changing command without you having started a transaction first, 
it will handle that command as if it's in its own transaction.  In other words 
it will do

BEGIN; ; COMMIT

for you.  You would not need the extra COMMIT command because SQLite already 
took care of it.  On the other hand, had you explicitly declared a transaction 
by putting a BEGIN before your INSERT, then nothing would be COMMITted until 
your own explicit COMMIT command.

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


Re: [sqlite] Avoiding Database Is Locked Error

2010-06-15 Thread Robert Latest
Hello all,

> Oh, I completely forgot that people can do that. So, Robert, you case
> is exactly the case I was talking about. As Simon said your SELECT
> opens read-only transaction and then as you issue your first UPDATE
> this transaction have to be converted to writing one. This is a call
> for problems. So you better to issue "BEGIN IMMEDIATE" before you
> execute your SELECT statement.
>
> BTW, I hope you don't change the table you selected in this scenario?

I'm not sure right now, but I think I've done that every now and then.
With a bad feeling. Instinctively I'd rather first SELECT, store the
results, finalize the SELECT statement and then get to work on its
result using the stored data. It's just that without intermediate
storage it's a bit easier (no need to do any ressource management),
and I've found nothing in the docs that says I shouldn't do that.

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


[sqlite] INSERT is not committing from c#

2010-06-15 Thread nmartin

I have a C# from which I am trying to perform a COMMIT. The INSERT does not
throw an error but no data is inserted. I have tried a couple ways of
COMMITTING:

try
{
SQLiteTransaction liteTransaction =
Connection.BeginTransaction();
SQLiteCommand command = new
SQLiteCommand(insert.BuildInsert(tableName, keyValuePairs), Connection);
command.Transaction = liteTransaction;
command.ExecuteNonQuery();
liteTransaction.Commit();
}
catch (SQLiteException e)
{
//error
connection.Close();
}

And Just and Explicit Commit:

INSERT INTO TestData
(deepbreathing,usertechnique,guidedimagery,yoga,exercise,meditation,prayer,somethingelse,howoften,numtechniques,userreturn,chosentechnique,chosentechnique)VALUES
("true","I will try to use deep
breathing.","false","true","false","false","true","true","When I'm
stressed","1","true","deepbreathing","deepbreathing"); COMMIT;


Can anyone helps?
-- 
View this message in context: 
http://old.nabble.com/INSERT-is-not-committing-from-c--tp28891789p28891789.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


[sqlite] Yet another SQLite wrapper for Java

2010-06-15 Thread Igor Sereda

Hello.

I have just posted our Java wrapper for SQLite as an open-source project:
http://code.google.com/p/sqlite4java

It's a thin JNI-based wrapper (no JDBC) with performance and stability being
the key concerns. The library is targeted for desktop Java apps, but may be
used in other Java environments (binaries are compiled for Windows, Linux,
Mac OS X). 

The reasons for building our own library were:
  * We wouldn't use JDBC because we needed a really tight integration. We
could possibly have a need to use any function from the SQLite C API, and we
needed additional functions implemented in C for the sake of performance.
  * Among the existing non-JDBC wrappers, we found none that wouldn't be
outdated, have satisfactory interface or implementation, or be applicable to
cross-platform Java GUI apps.

After our library got successfully deployed with our commercial
applications, we made it open-source. Granted that we pursued our own goals
first, it's not always the best solution, yet I believe it is generic enough
to be published.

So, there it is. I would appreciate feedback from the community, especially
from fellow Java developers who are using other SQLite wrappers. Feel free
to suggest improvements or send patches.

Igor

PS. It's a good occasion to say: Big thanks and kudos to D. Richard Hipp and
the team for SQLite, an excellent product!
-- 
View this message in context: 
http://old.nabble.com/Yet-another-SQLite-wrapper-for-Java-tp28890371p28890371.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] SQLite and Qt

2010-06-15 Thread Sylvain Pointeau
Hi,

What is the error?
did you make a sample project that you could share?

Best regards,
Sylvain

On Tue, Jun 15, 2010 at 1:11 AM, Sam Carleton wrote:

> Sylvain,
>
> I have given it a try and it isn't working.  First a quick questions:
> Is qsqlite.dll statically linked to the sqlite code or dynamically
> linked?  The sqlite3.lib I am trying to link to is the for dynamically
> linking, aka requiring the sqlite3.dll.  I am guessing that is the
> issue, but since it will take me a while to spin up a statically
> linked version of the lib, I thought I might ask before running down
> that rat whole;)
>
> Sam
>
> On Wed, Jun 9, 2010 at 4:20 AM, Sylvain Pointeau
>  wrote:
> >
> > build your qt sqlite as a plugin
> > then recompile this plugin with the version you want.
> >
> >
> http://doc.trolltech.com/4.6/sql-driver.html#qsqlite-for-sqlite-version-3-and-above
> >
> > best regards,
> > Sylvain
> ___
> 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