Re: [sqlite] Strange behavior for timeouts in transactions

[romtek]

On Mon, Dec 19, 2011 at 1:34 PM, Pavel Ivanov  wrote:
>
> Yes, this is expected behavior. In this case transaction won't be able
> to ever proceed because it can proceed only when writing transaction
> in session 1 is committed but it cannot be committed until all reading
> transactions are finished, including transaction in session 2. So it's
> a deadlock and SQLite knows about that, thus it returns "database
> locked" immediately.


Does anyone know if those who use SQLite with PHP need to be concerned
with this issue (i.e. to program around this)? I've assumed that a PDO
driver for SQLite or another layer will take care of waiting,
retrying, etc. when needed. Is that a correct assumption?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Procedure (Conditional statement) workaround

[John Gillespie]

This would make a good entry for an Obfuscated SQL contest.
Well done

John


On 19 December 2011 21:43, Nico Williams  wrote:

> You can do conditionals via WHERE clauses, as others have pointed out.
>  You can also use WHEN clauses on triggers.
>
> Think of it as  IF .  And remember that the
> WHERE clause can refer to all sorts of things, including parameters
> from the application (something like WHERE @foo = 1).
>
> You can do iteration via INSERT/UPDATE/DELETE if you have tables with
> rows to iterate over.
>
> You can make sub-routines (that return no values but have
> side-effects) out of INSERTs on VIEWs or TABLEs with triggers that
> will use the new row(s) as arguments, and but leave the VIEW/TABLE
> itself unaltered.  In the case of a TABLE you'd use a BEFORE INSERT
> trigger that does a SELECT RAISE(IGNORE) at the end to make sure the
> TABLE is left unaltered.
>
> You can use recursive triggers to implement recursion.
>
> This is really an abuse of SQL, of course, but it can be done :)  And,
> of course, you have SQLite3's various limits to keep in mind (e.g., on
> recursion depth).
>
> I've done this sort of thing as a proof of concept, for the fun of it.
>
> Factorial() might be implemented as follows:
>
> PRAGMA recursive_triggers = 1;
> CREATE TABLE factorials (n INTEGER PRIMARY KEY, n_factorial INTEGER);
> CREATE VIEW factorial AS SELECT n, n_factorial FROM factorials;
> CREATE TRIGGER factorial_ins INSTEAD OF INSERT ON factorial BEGIN
>SELECT RAISE(FAIL, "n must be non-negative") WHERE NEW.n < 0;
>SELECT RAISE(IGNORE) WHERE EXISTS (SELECT f.n FROM factorials f
> WHERE f.n = NEW.n);
>INSERT INTO factorials SELECT NEW.n, 1 WHERE NEW.n < 2 AND NOT
> EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n);
>INSERT INTO factorial SELECT NEW.n - 1, NULL WHERE NEW.n > 0 AND
> NOT EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1);
>INSERT INTO factorials SELECT NEW.n, NEW.n * (SELECT f.n_factorial
> FROM factorials f WHERE f.n = NEW.n - 1) WHERE NEW.n > 0 AND EXISTS
> (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1) AND NOT EXISTS
> (SELECT f.n FROM factorials f WHERE f.n = NEW.n);
> END;
>
> sqlite> select * from factorial;
> sqlite> insert into factorial select 5, null;
> sqlite> select * from factorial;
> 0|1
> 1|1
> 2|2
> 3|6
> 4|24
> 5|120
> sqlite>
>
> (With 64-bit signed integers you can compute up to 20!, but no further.)
>
> Here we have a trigger acting as a sub-routine that doesn't return any
> results, but it does ensure that the results are available for a
> subsequent query.  Another way to think of it is as a method of
> building recursive queries in the absence of recursive query support
> in SQLite3.
>
> So there you have it: procedural programming in SQL.  My advice:
> follow the advice that you've been given, which is to say: do your
> procedural programming in C or whatever host language you're using,
> and use SQL as it was intended to be used.
>
> Cheers,
>
> Nico
> --
> ___
> 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] Strange behavior for timeouts in transactions

[Simon Slavin]

On 20 Dec 2011, at 9:03am, romtek wrote:

> On Mon, Dec 19, 2011 at 1:34 PM, Pavel Ivanov  wrote:
>> 
>> Yes, this is expected behavior. In this case transaction won't be able
>> to ever proceed because it can proceed only when writing transaction
>> in session 1 is committed but it cannot be committed until all reading
>> transactions are finished, including transaction in session 2. So it's
>> a deadlock and SQLite knows about that, thus it returns "database
>> locked" immediately.
> 
> Does anyone know if those who use SQLite with PHP need to be concerned
> with this issue (i.e. to program around this)? I've assumed that a PDO
> driver for SQLite or another layer will take care of waiting,
> retrying, etc. when needed. Is that a correct assumption?

First, there's more than one way of using SQLite3 from PHP.  There's also the 
interface SQLite3:: which is a much thinner wrapper around the basic SQLite C 
library.  I have no way of knowing what proportion of programmers use one 
library or the other.

As long as you set an acceptable timeout, which you do with one of these calls 
depending which library you're using





your web service probably isn't going to run into problems.  What might be 
worrying you right now is the result of the default timeout being zero, 
something which is arguably weird.  In other words, unless you specify a 
timeout yourself any report of a lock is instantly treated like an error.  Set 
your timeout to one second or five seconds and the behaviour will be more 
reasonable.

Second, most transactions and locks in SQLite from PHP are fleeting.  Generally 
you want your web page to list some records or do one update.  You wouldn't 
have a process keep a lock active for a long time because this would correspond 
to your server taking a long time to show a web page, and nobody wants that.  
So a lock by one process probably isn't going to last very long -- probably a 
fraction of a second.  Access for a web page is unlikely to involve deadlock 
because of the nature of web pages: they generally don't interact with the user 
while a transaction is open.

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

[sqlite] datetime

[Stephen Chrzanowski]

I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should be
13:06Z.  However, according to this:

select datetime('now','localtime'),datetime('now','utc');
datetime('now','localtime')datetime('now','utc')
2011-12-20 08:05:242011-12-20 18:05:24

Its 18:05Z.  I started out putting blame on both my XP VM and SQLite Expert
Pro at work, but then coming home and running the same on my Win 7 machine,
both in SEP and sqlite3.exe (version 3.5 of the EXE) I get the same results.

When I set the time zone on the XP VM to GMT+0 the times match, UTC is
right on the money.  If I set GMT-1, I end up being two hours off.  The
local time is one hour closer to my time local time, but the UTC time
shouldn't change!

One thing I JUST tried now is the following:

select datetime('now','localtime'),datetime('now','localtime','utc');
datetime('now','localtime')datetime('now','localtime','utc')
2011-12-20 08:10:432011-12-20 13:10:43

But this doesn't make sense.  I want the localtime and UTC time in one
call, and I get the right time??  If anything localtime and UTC time
shouldn't be the same since localtime should calculate based on UTC time.

Can someone shed some logic on this for me please?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime

[Kit]

2011/12/20 Stephen Chrzanowski :
> I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should be
> 13:06Z.  However, according to this:
>
> select datetime('now','localtime'),datetime('now','utc');
> datetime('now','localtime')    datetime('now','utc')
> 2011-12-20 08:05:24            2011-12-20 18:05:24

Try
select datetime('now');
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime

[Stephen Chrzanowski]

That returned the expected results.  So in other words, even though 'UTC'
is a valid option, it shouldn't be used?

On Tue, Dec 20, 2011 at 8:41 AM, Kit  wrote:

> 2011/12/20 Stephen Chrzanowski :
> > I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should
> be
> > 13:06Z.  However, according to this:
> >
> > select datetime('now','localtime'),datetime('now','utc');
> > datetime('now','localtime')datetime('now','utc')
> > 2011-12-20 08:05:242011-12-20 18:05:24
>
> Try
> select datetime('now');
> --
> Kit
> ___
> 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] datetime

[Igor Tandetnik]

Stephen Chrzanowski  wrote:
> I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should be
> 13:06Z.  However, according to this:
> 
> select datetime('now','localtime'),datetime('now','utc');
> datetime('now','localtime')datetime('now','utc')
> 2011-12-20 08:05:242011-12-20 18:05:24

You misunderstand how modifiers work. datetime('now'), with no modifier, 
produces a string reflecting UTC time. 'localtime' modifier assumes that the 
string to the left of it represents time in UTC, and converts it to local time 
- so far so good. 'utc' does the reverse - it assumes that the string to the 
left is in local time, and converts it to UTC. But since 'now' is already in 
UTC, you effectively apply the time zone bias twice - that's how you end up 
with 10 hours difference.

> One thing I JUST tried now is the following:
> 
> select datetime('now','localtime'),datetime('now','localtime','utc');
> datetime('now','localtime')datetime('now','localtime','utc')
> 2011-12-20 08:10:432011-12-20 13:10:43

Naturally, since 'localtime' and 'utc' do the exact opposite adjustments, 
datetime('now','localtime','utc') is equivalent to datetime('now'). It's like 
being surprised that -(-1) == 1
-- 
Igor Tandetnik

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

Re: [sqlite] datetime

[Stephen Chrzanowski]

That would make sense.  Thank you for clearing that up.

On Tue, Dec 20, 2011 at 8:50 AM, Igor Tandetnik  wrote:

> Stephen Chrzanowski  wrote:
> > I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should
> be
> > 13:06Z.  However, according to this:
> >
> > select datetime('now','localtime'),datetime('now','utc');
> > datetime('now','localtime')datetime('now','utc')
> > 2011-12-20 08:05:242011-12-20 18:05:24
>
> You misunderstand how modifiers work. datetime('now'), with no modifier,
> produces a string reflecting UTC time. 'localtime' modifier assumes that
> the string to the left of it represents time in UTC, and converts it to
> local time - so far so good. 'utc' does the reverse - it assumes that the
> string to the left is in local time, and converts it to UTC. But since
> 'now' is already in UTC, you effectively apply the time zone bias twice -
> that's how you end up with 10 hours difference.
>
> > One thing I JUST tried now is the following:
> >
> > select datetime('now','localtime'),datetime('now','localtime','utc');
> > datetime('now','localtime')datetime('now','localtime','utc')
> > 2011-12-20 08:10:432011-12-20 13:10:43
>
> Naturally, since 'localtime' and 'utc' do the exact opposite adjustments,
> datetime('now','localtime','utc') is equivalent to datetime('now'). It's
> like being surprised that -(-1) == 1
> --
> 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] Strange behavior for timeouts in transactions

[romtek]

On Tue, Dec 20, 2011 at 6:08 AM, Simon Slavin  wrote:
>
> First, there's more than one way of using SQLite3 from PHP.  There's also the 
> interface SQLite3:: which is a much thinner wrapper around the basic SQLite C 
> library.  I have no way of knowing what proportion of programmers use one 
> library or the other.

I started using SQLite with PHP in version 5.2.x, when there wasn't
SQLite3:: interface, so I chose to use PDO. Moreover, I don't want to
lock myself into SQLite, PDO provides a somewhat more flexible
approach.


> As long as you set an acceptable timeout, which you do with one of these 
> calls depending which library you're using
>
> 
>
> 
>
> your web service probably isn't going to run into problems.  What might be 
> worrying you right now is the result of the default timeout being zero, 
> something which is arguably weird.  In other words, unless you specify a 
> timeout yourself any report of a lock is instantly treated like an error.  
> Set your timeout to one second or five seconds and the behaviour will be more 
> reasonable.

I'd done some research into this since I asked my question and learned
some things. According to http://bugs.php.net/bug.php?id=38182 and
http://www.serverphorums.com/read.php?7,118071, PDO_SQLITE defaults to
a 60 second busy timeout. This should be enough.

> Second, most transactions and locks in SQLite from PHP are fleeting.  
> Generally you want your web page to list some records or do one update.  You 
> wouldn't have a process keep a lock active for a long time because this would 
> correspond to your server taking a long time to show a web page, and nobody 
> wants that.  So a lock by one process probably isn't going to last very long 
> -- probably a fraction of a second.  Access for a web page is unlikely to 
> involve deadlock because of the nature of web pages: they generally don't 
> interact with the user while a transaction is open.


One would think so :) But Drupal people have run into the opposite
(http://drupal.org/node/1120020 and
http://stackoverflow.com/questions/6108602/avoiding-locked-sqlite-database-timeouts-with-php-pdo).
This seems to happen for them mostly when they are indexing their data
a visitor to a site requests a page. Apparently, their indexing tasks
is long running and write intensive. I think they should rework this
tasks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about the way put a database of SQLite 3 beside web folder?

[romtek]

On Fri, Dec 16, 2011 at 8:31 AM, Simon Slavin  wrote:
> I would not advise putting it in a folder that you are serving to the web 
> since this would allow anyone browsing yourweb site to inspect the full 
> contents of the file.


One could easily configure the server to *not* serve database files
(or contents of folders) to a site's visitor. Therefore, it could be
located anywhere.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange behavior for timeouts in transactions

[Simon Slavin]

On 20 Dec 2011, at 5:21pm, romtek wrote:

> I'd done some research into this since I asked my question and learned
> some things. According to http://bugs.php.net/bug.php?id=38182 and
> http://www.serverphorums.com/read.php?7,118071, PDO_SQLITE defaults to
> a 60 second busy timeout. This should be enough.

Agreed.  Certainly an unexpected pause of more than 60 seconds.  The default of 
60 contradicts something I was told, but that might have been for a different 
version of PHP.

>> Second, most transactions and locks in SQLite from PHP are fleeting.  
>> Generally you want your web page to list some records or do one update.  You 
>> wouldn't have a process keep a lock active for a long time because this 
>> would correspond to your server taking a long time to show a web page, and 
>> nobody wants that.  So a lock by one process probably isn't going to last 
>> very long -- probably a fraction of a second.  Access for a web page is 
>> unlikely to involve deadlock because of the nature of web pages: they 
>> generally don't interact with the user while a transaction is open.
> 
> 
> One would think so :) But Drupal people have run into the opposite
> (http://drupal.org/node/1120020 and
> http://stackoverflow.com/questions/6108602/avoiding-locked-sqlite-database-timeouts-with-php-pdo).
> This seems to happen for them mostly when they are indexing their data
> a visitor to a site requests a page. Apparently, their indexing tasks
> is long running and write intensive. I think they should rework this
> tasks.

Not sure why someone would start a long-running blocking process for an 
application which purports to be running.  When I do this I give the 
application a status so it just knows not to try accessing the data file until 
the blocking process is finished.

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

[sqlite] insert error

[jim-on-linux]

Can someone let me know why the following 
keeps giving invalid syntax err msg.

 con = sqlite3.connect(sqldb)
 cursor = con.cursor()
 cursor.execute insert into default (rowname) values ( '1' ) ;
 con.commit()
 con.close()

I get invalid syntax pointing to the t in insert.

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

Re: [sqlite] insert error

[Petite Abeille]

On Dec 20, 2011, at 8:34 PM, jim-on-linux wrote:

> cursor.execute insert into default (rowname) values ( '1' ) ;

'default' is a keyword:

http://www.sqlite.org/lang_keywords.html

If you insist on that name, double quote it.

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

Re: [sqlite] insert error

[epankoke]

Besides that, shouldn't it be 

cursor.execute "insert into ""default"" (rowname) values('1');" 

I would expect that the execute command is expecting a string containing the 
SQL to execute. 




- Original Message -
From: "Petite Abeille"  
To: "General Discussion of SQLite Database"  
Sent: Tuesday, December 20, 2011 2:40:50 PM 
Subject: Re: [sqlite] insert error 


On Dec 20, 2011, at 8:34 PM, jim-on-linux wrote: 

> cursor.execute insert into default (rowname) values ( '1' ) ; 

'default' is a keyword: 

http://www.sqlite.org/lang_keywords.html 

If you insist on that name, double quote it. 

___ 
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] Procedure (Conditional statement) workaround

[Nico Williams]

On Tue, Dec 20, 2011 at 5:21 AM, John Gillespie  wrote:
> This would make a good entry for an Obfuscated SQL contest.
> Well done

Thanks, I guess :)  It was a fun little SQL ditty to write, and only
took a few minutes.  (Now I do I a search and see that factorial in
SQL is a fairly common toy problem, and here you can see a recursive
query version of factorial:
http://www.sqlservercentral.com/Forums/Topic164151-213-2.aspx (on page
2, today, search for "with fact").

It'd be nice to have recursive queries (with tail-call optimization).
Then a lot of things get easier.  Of course, recursive algorithms (and
thus recursive queries) do tend to look obfuscated to some, but a
combination of C/Python/... and SQL can also look obfuscated, so it's
really a question of what you're trading off for what, and the best
thing to do is to go for maintainability (i.e., readability), because
someone will have to support whatever code you write.  Taking
advantage of SQL expressibility requires writing lengthy block
comments for the benefit of the next person to read that code.

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

Re: [sqlite] Procedure (Conditional statement) workaround

[Petite Abeille]

On Dec 20, 2011, at 9:52 PM, Nico Williams wrote:

> It'd be nice to have recursive queries (with tail-call optimization).

Yes for recursive with clauses!

http://gennick.com/with.html

> Then a lot of things get easier.

Like solving that damn sodoku puzzle:

Solving a Sudoku using Recursive Subquery Factoring
http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring

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

[sqlite] run a script in Visual Basic.NET

[Esteban Cervetto]

How can I run an SQL script written in a txt file in Visual Basic .NET

I am using *System.Data.SQLite.dll*  1.0.66.0   library

I want to avoid write the SQL code in the visual basic routine.

Any Ideas?

Regards

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

Re: [sqlite] run a script in Visual Basic.NET

[Simon Slavin]

On 21 Dec 2011, at 2:27am, Esteban Cervetto wrote:

> How can I run an SQL script written in a txt file in Visual Basic .NET
> 
> I am using *System.Data.SQLite.dll*  1.0.66.0   library
> 
> I want to avoid write the SQL code in the visual basic routine.

Either write some Visual BASIC code to read in the contents of the file and 
call _exec() on it, or issue an OS call to tell sqlite3.exe to execute the 
contents of that file.

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