[sqlite] Using sqlite3_backup* while the destination connection has "busy" read-only statements.

2014-11-12 Thread Shaun Seckman (Firaxis)
Greetings all,
I'm running into a situation in where our application is crashing during a call 
to sqlite_backup_finish inside of btreeParseCellPtr because some of the 
structure is corrupted.

Both the source and destination database are using the same page sizes and I'm 
running everything from within a single-thread.

I'd like to get clarification on one possible issue in order to determine 
whether this is the culprit or at least rule it out.  This destination 
connection (which is an in-memory database) has several prepared statements, 
some of which are marked as busy (from sqlite3_stmt_busy) due to having step 
called but not reset.  All of these statements are read-only (simple select 
statements).

My question is, is it safe to have these busy statements on the destination 
connection while the backup happens (again, this is all single threaded so 
nothing happens DURING the backup process)?

If not,  then should sqlite3_backup_init check for this (at least in debug) and 
return NULL if any statements are busy?

-Shaun


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


Re: [sqlite] cnt(x) what do the brackets signify

2014-11-12 Thread Paul Sanderson
Thanks
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 12 November 2014 18:00, Igor Tandetnik  wrote:
> On 11/12/2014 12:52 PM, Paul Sanderson wrote:
>>
>> I have googled but can't see what cnt(x) actually signifies - cnt is
>> not a function
>
>
> The same thing it signifies in "CREATE TABLE cnt(x);"
>
> "cnt" is an "ephemeral" table with a single column named "x".
> --
> 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] Triggers and CTE's

2014-11-12 Thread James K. Lowden
On Tue, 11 Nov 2014 17:15:53 -0600
Ben Newberg  wrote:

> CREATE TRIGGER t_populate_zweeks
> AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1
> BEGIN
> DELETE FROM zWeeks;
> WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from
> Weeks limit 10)
> INSERT INTO zWeeks (Week) select wk from Weeks;
> END;
> 
> When I run this, I get an error message from sqlite3_errmsg() saying
> syntax error near "INSERT". However, when I run the DELETE, WITH and
> INSERT statements above separately without the Create Trigger DDL,
> the query runs successfully and populates my zWeeks table with values
> 1 through 10.
> 
> Do triggers not support this behavior, or is my syntax incorrect?

You really don't want to use triggers this way.  I know you think you
do.  :-)  Everyone faces the temptation sooner or later.  But it is a
misuse of triggers, and you'll have the devil to pay in due time.  

Rather than lifting everything back up into the application, I would
suggest moving the SQL into a prepared statement inside a transaction.
It is an example of why I'd like to see SQLite support user-defined
functions (not "stored procedures") for DML purposes.  Before I did
that, though, I would make sure I actually need to persist weeks.  I'm
pretty sure weeks can be computed in a view, and it's not obvious that
performance would be worse.  

If persistence is required, consider maintaining a single offset that
can be added to weeks and incremented separately, instead incrementing
each week.  For example, you could have the first row be week 0, and
each successive row as max(week) - 1, keeping e.g. 0, -1, -2 (where
zero is oldest) and offset of 2, making 2+0, 2+-1, 2+-2, i.e. 2, 1, 0.  

Best advice is to restrict the use of triggers to enforcement of
referential integrity.  For the most part they are obsolete now that
we have DRI (e.g. declared foreign keys) support.  

Why only that?  Triggers fire regardless of application, regardless of
business rule.  They can maintain the internal consistency of the
database.  If you put business rules in triggers, you cannot change
them without affecting all applications and future uses.  Business
rules are less logical and more volatile than RI rules, too, and thus
more prone to change.  

In your case you have some incrementation of week if Procedure = 6 AND
new.Flag = 1.  The database would not be *inconsistent* if that rule
were violated; it would only be inaccurate insofar as it failed to
reflect the world it models.  A different interpretation of world and
model could be used without changing the database or requiring that
particular row relationship. 

HTH.  

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


Re: [sqlite] cnt(x) what do the brackets signify

2014-11-12 Thread Igor Tandetnik

On 11/12/2014 12:52 PM, Paul Sanderson wrote:

I have googled but can't see what cnt(x) actually signifies - cnt is
not a function


The same thing it signifies in "CREATE TABLE cnt(x);"

"cnt" is an "ephemeral" table with a single column named "x".
--
Igor Tandetnik

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


Re: [sqlite] cnt(x) what do the brackets signify

2014-11-12 Thread Stephan Beal
On Wed, Nov 12, 2014 at 6:52 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I have googled but can't see what cnt(x) actually signifies - cnt is
> not a function - could someone point me to a resource so I can
> understand this construct
>

The diagram at the top of this page demonstrates what it is:

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


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] cnt(x) what do the brackets signify

2014-11-12 Thread Paul Sanderson
I am looking at recursive cte and saw this example

I have googled but can't see what cnt(x) actually signifies - cnt is
not a function - could someone point me to a resource so I can
understand this construct

Thanks

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<100)
SELECT x FROM cnt;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] sliteonline or sqlite in js

2014-11-12 Thread Kirill
Good day,

Full line manager to work with sqlite directly
from the browser on any platform:
http://sqliteonline.com/

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