Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Max Vlasov]

Whether SQLITE_FCNTL_CHUNKS_SIZE is useful is a different discussion.
> I myself would love to see features exposed via pragmas whenever
> possible, for the simple reason that I don't use the C API and can't
> make use of the features otherwise.  I would assume that since the
> SQLite developers added the feature to the C API, there must be a use
> for it or they wouldn't have bothered.
>
>
You have your reason for wanting pragma more, but for a general developer
using Pragma instead of api calls is worse in the long run since Pragma
generates no errors and the docs says there's no guarantee the syntax will
not change and the results will be the same  (The recent WAL-related changes
to journal_mode is a good example). In case of api calls, we at least have
compiler's errors and warnings.

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

Re: [sqlite] partial index?

[Igor Tandetnik]

Tim Romano  wrote:
> How would you find a row whose column X contained value Y if the "partial"
> index on column X specified that rows containing value Y in column X should
> never be returned?

No one suggests partial index should be capable of hiding anything. The idea is 
that, when the query can be proven to only involve rows covered by the partial 
index, the index can be used to speed up the query. Otherwise, it simply won't 
be used.
-- 
Igor Tandetnik


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

Re: [sqlite] partial index?

[Tim Romano]

Typo:
"... more performant than partial query" should read "more performant than a
partial index".
Tim Romano


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

Re: [sqlite] partial index?

[Tim Romano]

Eric,
How would you find a row whose column X contained value Y if the "partial"
index on column X specified that rows containing value Y in column X should
never be returned?  If the index hides the row, how do you cause the row to
become visible to a query? You have to drop the index.

However, I would be willing to accept an index on a *virtual* column whose
set of discrete possible values was a subset of the values in the actual
underlying table, or some translated form of those values, for example a
column that was the result of a function that converted a date to 'Q1',
'Q2', 'Q3', or 'Q4'.

Compare: http://www.oracle-base.com/articles/11g/VirtualColumns_11gR1.php

If your goal is performance, moving rows out of the table when they cease to
meet your business rule's definition of relevance will be more performant
than partial query: not only will the index  contain just as few nodes, but
the table itself will contain fewer rows than the table when using a partial
index.

And programming would not be more difficult: you'd simply substitute a
trigger for the partial index declaration. Moreover, this technique would be
highly portable. Partial indexes, not.

Regards
Tim Romano
Swarthmore PA



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

[sqlite] errors running test suite (couldn't execute "testfixture")

[Paweł Hajdan , Jr .]

I updated to latest fossil version, ran make distclean, ./configure, make,
make test and got this:

avtrans-9.19.4-5116... Ok
avtrans-9.19.5-5116... Ok
avtrans-9.20.1-5640... Ok
avtrans-9.20.2-5640... Ok
avtrans-10.1... Ok
avtrans.test-closeallfiles... Ok
avtrans.test-sharedcachesetting... Ok
Time: avtrans.test 6915 ms
Memory used:  now 16  max2372872  max-size 530991
Allocation count: now  1  max   3346
Page-cache used:  now  0  max  0  max-size   1272
Page-cache overflow:  now  0  max 759264
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max   8384  max-size   8384
./testfixture: couldn't execute "testfixture": no such file or directory
while executing
"open "|$prg tf_main.tcl" r+"
(procedure "launch_testfixture" line 5)
invoked from within
"launch_testfixture $binary"
(procedure "get_version" line 2)
invoked from within
"get_version $bin"
("foreach" body line 2)
invoked from within
"foreach bin $binaries {
  puts "Testing against $bin - version [get_version $bin]"
}"
(file "./test/backcompat.test" line 55)
invoked from within
"source ./test/backcompat.test"
invoked from within
"interp eval tinterp $script"
(procedure "slave_test_script" line 24)
invoked from within
"slave_test_script [list source $zFile] "
invoked from within
"time { slave_test_script [list source $zFile] }"
(procedure "slave_test_file" line 14)
invoked from within
"slave_test_file $::testdir/$file"
(procedure "run_tests" line 12)
invoked from within
"run_tests veryquick -presql {} -files {shared3.test bigfile.test
where9.test tkt3419.test sync.test fts1o.test fts2f.test misc2.test
tkt3541.test type..."
("uplevel" body line 1)
invoked from within
"uplevel run_tests $name $::testspec($name)"
(procedure "run_test_suite" line 5)
invoked from within
"run_test_suite veryquick"
(file "./test/veryquick.test" line 16)
make: *** [test] Error 1

What should I do to make it pass?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partial index?

[Stephen Oberholtzer]

On Thu, Aug 19, 2010 at 5:53 PM, Kees Nuyt  wrote:
> On Thu, 19 Aug 2010 17:39:14 -0400, Eric Smith
>  wrote:
>
>>Am I missing something?
>
> You could add a "deleted" column with value range (0,1) and
> create an index on it if benchmarks show that makes it
> faster. As a bonus it is easier to code and maintain than a
> separate table with references and triggers.
>
> Alternatively, you can create an composite index with the
> "deleted" column as one of the components.
>
> From a theoretical view, if you care about the visibility of
> a row, you should express it as an attribute of the entity.
> The solutions above comply with that notion.
> --
>  (  Kees Nuyt

I think you've missed the point. I believe what he's getting at is this:

>> CREATE INDEX foo ON bar (to_be_deleted) <<

Imagine if he had 100 million rows in his table, and 100 of them were
marked "to_be_deleted".
His index will have 100 million rows, probably 500MB or 900MB (not
sure if rowid is 32- or 64-bit), consisting of 99,999,900 "0"s and 100
"1"s.

If he could create what MSSQL calls a "filtered index", using a syntax
like this:

>> CREATE INDEX foo_filtered ON bar (to_be_deleted) WHERE to_be_deleted = 1 <<

he could speed up the statement

>> DELETE FROM bar WHERE to_be_deleted = 1 <<

using that index, just like he could with the unfiltered "foo" index.
The only difference is that where foo has 100 million rows,
foo_filtered only contains 100 rows, taking up only 500-900 bytes
(thus actually having like 300% overhead due to page sizes!)


Now, in order to implement this, the following changes would have to be made:

1. Conditional logic would have to be generated inside the VDBE
programs for INSERT statements.  This is pretty straightforward.

2. Conditional logic would have to be generated inside the VDBE
programs for UPDATE statements. Care must be taken to make sure that
the index is updated properly when the column(s) referenced in the
WHERE clause are updated, but other than that, it's probably pretty
straightforward.

3. Depending on how the IdxDelete operator handles "key not found in
index" errors, the VDBE code generated for DELETE statements may also
need to be updated.

4. The statement parser needs to be modified to parse this syntax.

5. The schema parser needs to be modified to decode this syntax.

6. The optimizer needs to flatten and check that every possible branch
of the WHERE clause on a SELECT/DML statement is compatible with the
WHERE clause of the index, before it can use that index.

Now, I personally could do #1-3, because they're pretty easy.
I could probably even manage #4 and #5 if I spent a week familiarizing
myself with the code.
But #6, as far as I can tell, is a LOT harder.  Consider the following examples:

create index ix1 on Bar (baz) where quux between 30 and 95;

select * from baz where quux = 35; -- Index is viable
select * from baz where quux between 31 and 94; -- Index is viable
select * from baz where quux = 38 or quux between 80 and 90; -- Index is viable
select * from baz where quux in (40,50,60,70); -- again, index is viable
select * from baz where quux between 25 and 35; -- index is NOT viable
select * from baz where quux = 38 or baz = 5; -- index is NOT viable



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Jim Wilcoxson]

On 8/19/10, Simon Slavin  wrote:
>
> On 19 Aug 2010, at 9:27pm, Taras Glek wrote:
>
>> I really appreciate that sqlite got this feature to reduce
>> fragmentation, but why not expose this as a pragma?
>
> Do you have figures which suggest that reducing fragmentation leads to any
> improvement in performance ?

Whether SQLITE_FCNTL_CHUNKS_SIZE is useful is a different discussion.
I myself would love to see features exposed via pragmas whenever
possible, for the simple reason that I don't use the C API and can't
make use of the features otherwise.  I would assume that since the
SQLite developers added the feature to the C API, there must be a use
for it or they wouldn't have bothered.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] playing with triggers

[Simon Slavin]

On 19 Aug 2010, at 11:22pm, Igor Tandetnik wrote:

> No, cascading triggers (one trigger causing another to fire) have "always" 
> worked ("always" meaning long enough that I can't recall when this was *not* 
> the case). Recursive triggers (a trigger causing itself to fire, directly or 
> indirectly) are relatively new (a couple years old) and have to be explicitly 
> enabled, for backward compatibilty with existing schemas.

Thanks.

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

Re: [sqlite] playing with triggers

[Igor Tandetnik]

Simon Slavin  wrote:
> If that worries you then you should be aware that the same problem applies 
> when one TRIGGER triggers another:
> 
> http://www.sqlite.org/pragma.html#pragma_recursive_triggers
> 
> You have to remember to turn it on in your application.  My understanding of 
> the term 'recursive triggers' is that it refers not
> just to when a trigger triggers itself (what 'recursive' means to me), but 
> also when any trigger triggers another. However I may
> be wrong about this.  

No, cascading triggers (one trigger causing another to fire) have "always" 
worked ("always" meaning long enough that I can't recall when this was *not* 
the case). Recursive triggers (a trigger causing itself to fire, directly or 
indirectly) are relatively new (a couple years old) and have to be explicitly 
enabled, for backward compatibilty with existing schemas.
-- 
Igor Tandetnik


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

Re: [sqlite] playing with triggers

[Simon Slavin]

On 19 Aug 2010, at 9:00pm, David Bicking wrote:

> I haven't tried RAISE(ROLLBACK... as that seems to severe. 
> RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
> RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
> the first three there, which I can't let happen. It is all or nothing for 
> data changes below Table1.
> 
> Which leads me to believe I can't do what I want without application code 
> supervising the changes.

With requirements that complicated you either need to work each TRIGGER 
differently or, as you say, implement the logic in your application.

>> with special regard to those two constructions and see
>> 
>> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
> 
> I do have foreign keys declared. Mind you in my tests they don't work if I 
> forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it 
> seems like I really can't trust foreign keys and it is best to never enable 
> them. 

If that worries you then you should be aware that the same problem applies when 
one TRIGGER triggers another:

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

You have to remember to turn it on in your application.  My understanding of 
the term 'recursive triggers' is that it refers not just to when a trigger 
triggers itself (what 'recursive' means to me), but also when any trigger 
triggers another. However I may be wrong about this.

> Or is there a way to force them to be enabled at all times? (i.e. not trust 
> me to remember to have any and all applications that talk to the data file to 
> remember to issue the pragma statement.)

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

says in part

"As of SQLite version 3.6.19, the default setting for foreign key enforcement 
is OFF. However, that might change in a future release of SQLite. To minimize 
future problems, applications should set the foreign key enforcement flag as 
required by the application and not depend on the default setting."

So the answer is not yet, but maybe in a future release.  However, there's a 
similar note about triggers !

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

Re: [sqlite] partial index?

[Simon Slavin]

On 19 Aug 2010, at 10:39pm, Eric Smith wrote:

> I want an index that only can be used to find rows with a particular 
> value or set of values.

Take a look at VIEWs:

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

This is the SQL standard way to reduce your view of a table to just certain 
rows.  If I understand your request, this feature should provide exactly what 
you want.  Appropriate indexes will be used when consulting any VIEW you've 
defined.

> Since SQLite doesn't support partial indices directly, I'm 
> thinking about making my own index as a separate table and 
> populating/depopulating it using triggers on the main table.  I only 
> need it for fast lookups during deletion of the relevant rows, so I'll 
> hijack the app logic that wants to delete those rows and instead use 
> the secondary table to get the row ids, and delete those directly.  
> 
> Something like DELETE FROM records WHERE __recno IN (SELECT __recno 
> FROM idxTable), where __recno is the INTEGER PRIMARY KEY on records.

I don't understand what you're looking up here.  If you have some method of 
recognising which rows of a table should be deleted just use the appropriate

DELETE FROM ... WHERE ...

command.  No need for any sub-SELECT clause.

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

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Simon Slavin]

On 19 Aug 2010, at 9:27pm, Taras Glek wrote:

> I really appreciate that sqlite got this feature to reduce 
> fragmentation, but why not expose this as a pragma?

Do you have figures which suggest that reducing fragmentation leads to any 
improvement in performance ?

It might be worth noting that fragmentation is normally seen as an issue only 
under Windows which is very sensitive to it however.  Other operating systems 
use different ways of handling disk access, however, real figures from 
real-world examples may disprove this classic view.  Also, many installations 
of SQLite are on solid state devices where, of course, fragmentation has no 
effect at all.

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

Re: [sqlite] partial index?

[Kees Nuyt]

On Thu, 19 Aug 2010 17:39:14 -0400, Eric Smith
 wrote:

>Am I missing something?

You could add a "deleted" column with value range (0,1) and
create an index on it if benchmarks show that makes it
faster. As a bonus it is easier to code and maintain than a
separate table with references and triggers.

Alternatively, you can create an composite index with the
"deleted" column as one of the components.

>From a theoretical view, if you care about the visibility of
a row, you should express it as an attribute of the entity.
The solutions above comply with that notion.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partial index?

[Eric Smith]

Tim Romano wrote: 

> The partial index is one very messy thing, fraught with ambiguities, 
> something to avoid.  

I want an index that only can be used to find rows with a particular 
value or set of values.  In what way is that ambiguous?  Other databases 
(e.g. postgres) seem to support this kind of thing.  

> I can imagine other business rules being really 
> bollixed up by the sudden reappearance of zombie rows.  

This isn't a 'business rule', this is an optimization.  No high 
level logic will change.  Just like when we use other sql indices.  

> Under the partial index method, how would 
> you ever find a row again once it has become invisible, unless you were 
> perhaps to change or suspend the partial index rule, and cause the missing 
> rows to reappear?  

"Become invisible", meaning it no longer contains data that I care 
about?  I don't need to find it quickly because it no longer contains 
data that I care about.  

So, I'm not sure I understand your concerns.  

Since SQLite doesn't support partial indices directly, I'm 
thinking about making my own index as a separate table and 
populating/depopulating it using triggers on the main table.  I only 
need it for fast lookups during deletion of the relevant rows, so I'll 
hijack the app logic that wants to delete those rows and instead use 
the secondary table to get the row ids, and delete those directly.  

Something like DELETE FROM records WHERE __recno IN (SELECT __recno 
FROM idxTable), where __recno is the INTEGER PRIMARY KEY on records.

Am I missing something?

Eric 

-- 
Eric A. Smith

Furbling, v.:
Having to wander through a maze of ropes at an airport or 
bank even when you are the only person in line.
-- Rich Hall, "Sniglets"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partial index?

[Kees Nuyt]

On Thu, 19 Aug 2010 17:15:40 -0400, Tim Romano
 wrote:

>Ah, an opportunity for another purist tirade presents itself.
>
>I don't have a hack for SQLite but something I consider to be a much better
>practice that accomplishes the same goal. If your business rules would
>declare that rows with value X in column Y no longer belong to the set, the
>most straightforward way to implement such a rule is to move those rows to
>another table where they do belong. Use an after update/insert trigger to do
>this.

[...]

>The partial index is one very messy thing, fraught with ambiguities,
>something to avoid.  I can imagine other business rules being really
>bollixed up by the sudden reappearance of zombie rows.

+1

My 2 cents:
Pure SQL doesn't mind about indexes, in RDBMS
implementations they are just an optimization feature and a
way to implement unique constraints. Anything more is a can
of worms indeed.

Optional indexes are a codasyl hierarchical or network
database feature, where indexes are exposed to the DML.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Max Vlasov]

On Fri, Aug 20, 2010 at 12:27 AM, Taras Glek  wrote:

>  Hi,
> I really appreciate that sqlite got this feature to reduce
> fragmentation, but why not expose this as a pragma?
>

Taras, I think that you're overestimating the feature. On the OS level it
won't matter how far the file pointer will go, a larger block still can be
fragmented if the OS don't find a continuous block. On the sqlite pager
level it's the same, for identical appends your data will probably get the
same pages in the file regardless of the chunk size. I suppose it has some
benefits in some specific cases and if it gets its own pragma it will just
add confusion.

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

Re: [sqlite] partial index?

[Tim Romano]

Ah, an opportunity for another purist tirade presents itself.

I don't have a hack for SQLite but something I consider to be a much better
practice that accomplishes the same goal. If your business rules would
declare that rows with value X in column Y no longer belong to the set, the
most straightforward way to implement such a rule is to move those rows to
another table where they do belong. Use an after update/insert trigger to do
this

Splitting the rows into separate tables In that manner, you could move an
inactive|invisible row back into active|visible status if the need should
ever arise, simply by changing the column value and moving the row back into
the active table. Under the partial index method, how would you ever find a
row again once it has become invisible, unless you were perhaps to change or
suspend the partial index rule, and cause the missing rows to reappear?

The partial index is one very messy thing, fraught with ambiguities,
something to avoid.  I can imagine other business rules being really
bollixed up by the sudden reappearance of zombie rows.

Regards
Tim Romano
Swarthmore PA





   on the Gender column.

On Thu, Aug 19, 2010 at 4:30 PM, Eric Smith  wrote:

> Afaict sqlite doesn't support indices on subsets of rows in a table, Ю
> la http://en.wikipedia.org/wiki/Partial_index -- right?
>
> Any plans to implement that?
>
> Are there any known hacks to implement something similar?
>
> --
> Eric A. Smith
>
> Keeping Young #3:
> Keep the juices flowing by janglin round gently as you move.
>-- Satchel Paige
> ___
> 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] partial index?

[Eric Smith]

Afaict sqlite doesn't support indices on subsets of rows in a table, Ю
la http://en.wikipedia.org/wiki/Partial_index -- right?

Any plans to implement that?

Are there any known hacks to implement something similar?

--
Eric A. Smith

Keeping Young #3:
Keep the juices flowing by janglin round gently as you move.
-- Satchel Paige
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Taras Glek]

  Hi,
I really appreciate that sqlite got this feature to reduce 
fragmentation, but why not expose this as a pragma? In many cases it is 
not feasible to pass the chunk size via a C API. For example with a 
pragma I could do fragmentation testing via an sqlite shell, now this 
option is out without modifying the shell source. I presume that a 
similar problem exists for users of scripting languages until every 
single scripting host provides support for this.

Perhaps this should even persist similar to how page_size persists 
across connections.

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

Re: [sqlite] playing with triggers

[Doug Currie]

On Aug 19, 2010, at 4:00 PM, David Bicking wrote:

> I haven't tried RAISE(ROLLBACK... as that seems to severe. 
> RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
> RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
> the first three there, which I can't let happen. It is all or nothing for 
> data changes below Table1.
> 
> Which leads me to believe I can't do what I want without application code 
> supervising the changes.

Would SAVEPOINTs help you here? http://www.sqlite.org/lang_savepoint.html

I've never tried using ROLLBACK TO in a trigger.

e

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

Re: [sqlite] playing with triggers

[David Bicking]

--- On Thu, 8/19/10, Simon Slavin  wrote:

> 
> On 19 Aug 2010, at 8:10pm, David Bicking wrote:
> 
> > The way it is set up, if any of the updates/inserts
> done by the triggers fail, everything rolls back, including
> the original data that caused the triggers. What I want to
> happen is that while everything else gets rolled back,
> Table1 still has its data, along with the error messages
> returned by the triggers.
> > 
> > Nearest I can tell you can't do that with triggers,
> but I really don't understand how they work, so maybe I am
> wrong.
> 
> You may be able to do this with ON CONFLICT and
> RAISE.  See
> 
> http://www.sqlite.org/lang_createtrigger.html
> 

I did read that, and think I mostly understand it, With it, and help from this 
list, I have gotten as far as I have. 

I haven't tried RAISE(ROLLBACK... as that seems to severe. 
RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
the first three there, which I can't let happen. It is all or nothing for data 
changes below Table1.

Which leads me to believe I can't do what I want without application code 
supervising the changes.

> with special regard to those two constructions and see
> 
> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
> 

I do have foreign keys declared. Mind you in my tests they don't work if I 
forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it seems 
like I really can't trust foreign keys and it is best to never enable them. 

Or is there a way to force them to be enabled at all times? (i.e. not trust me 
to remember to have any and all applications that talk to the data file to 
remember to issue the pragma statement.)

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

Re: [sqlite] Why the deadlock?

[Jay A. Kreibich]

On Thu, Aug 19, 2010 at 07:54:19PM +0100, Simon Slavin scratched on the wall:

>  I don't know what you mean by 'cursor'.  SQLite has commands.  You
> execute one command at a time.  Even a command like a SELECT that
> gathers lots of data gathers the data all in one go, then finishes.  

  None of this is true for the API.  You are more than able to have
  multiple statements "in flight" at the same time (I don't mean
  "prepared", I mean actually executing).  SELECT does not give the data
  in one go, but returns it one row at a time.  You can have calls to
  sqlite3_step() that reference multiple statements inter-mixed with
  each other.

> SQLite does not mark its place with one command, then return to that
> place again with another command.  

  Actually, that is *exactly* what prepared statements do each time you
  call sqlite3_step().

> SQLite never locks individual
> records, it only every locks the entire database file in various ways.

  OK, that's almost true.

> > - Is there a way to prevent SQLite from keeping the SHARED lock
> >   while waiting for an EXCLUSIVE lock if doing so would result in a
> >   deadlock (because another connection holding a SHARED lock needs to
> >   get an EXCLUSIVE lock before it can release the SHARED lock)?
> 
> SQLite will never know about an impending deadlock,

  You can't jump directly to the EXCLUSIVE lock, there are steps.

  But, yes, SQLite auto-detects this dead-lock situation and has the
  connection that does not already have the write lock return an
  SQLITE_BUSY, even if there is an busy-handler in place.  It is up to
  the application to do the right thing, however.

  http://sqlite.org/c3ref/busy_handler.html

 The presence of a busy handler does not guarantee that it will be
 invoked when there is lock contention. If SQLite determines that
 invoking the busy handler could result in a deadlock, it will go
 ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of
 invoking the busy handler. Consider a scenario where one process
 is holding a read lock that it is trying to promote to a reserved
 lock and a second process is holding a reserved lock that it is
 trying to promote to an exclusive lock. The first process cannot
 proceed because it is blocked by the second and the second process
 cannot proceed because it is blocked by the first. If both
 processes invoke the busy handlers, neither will make any
 progress. Therefore, SQLite returns SQLITE_BUSY for the first
 process, hoping that this will induce the first process to release
 its read lock and allow the second process to proceed.

  In short, you always need to deal with SQLITE_BUSY errors by, sooner
  or later, backing off and rolling back the current transaction (which
  should also release all the locks).  SQLite puts this responsibility
  in the hands of the application, however...  SQLite will let the
  application know there is a problem, but it is up to the application
  to fix it.  If you do not respond to a SQLITE_BUSY error, the 
  connections can remain dead-locked.

  This should never happen between statements using the same
  connection, however... the locks belong to the connection, not the
  statements, so two statements using the same connection can never
  deadlock.

   -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] playing with triggers

[Simon Slavin]

On 19 Aug 2010, at 8:10pm, David Bicking wrote:

> The way it is set up, if any of the updates/inserts done by the triggers 
> fail, everything rolls back, including the original data that caused the 
> triggers. What I want to happen is that while everything else gets rolled 
> back, Table1 still has its data, along with the error messages returned by 
> the triggers.
> 
> Nearest I can tell you can't do that with triggers, but I really don't 
> understand how they work, so maybe I am wrong.

You may be able to do this with ON CONFLICT and RAISE.  See

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

with special regard to those two constructions and see

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

for some examples.  However, a lot of work that's done with triggers is really 
about foreign keys, and SQLite now implements foreign keys so you don't have to 
simulate them in such a bulky way.  Read

http://www.hwaci.com/sw/sqlite/foreignkeys.html

before you get too into triggers.

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

Re: [sqlite] Why the deadlock?

[Simon Slavin]

On 19 Aug 2010, at 8:06pm, Pavel Ivanov wrote:

> Simon, read the whole thread please. Here is an example of 'cursor' in
> SQLite which Nikolaus talks about:

Thanks.  I didn't know about the SQLite internals involved.  Thanks for posting 
the detailed information.

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

[sqlite] playing with triggers

[David Bicking]

I am more or less playing with triggers trying to learn what they can do.

I have a setup where I write data to Table1. An after insert trigger looks up 
the newly written data codes in Table1, and writes 1 to 4 records to Table2. An 
after insert trigger on Table2 looks at the new data and updates a handful of 
other table, creating records if need be.

It all works. It is kind of neat to insert one record, and see changes across a 
half dozen other tables.

But... I want it to do more. 

The way it is set up, if any of the updates/inserts done by the triggers fail, 
everything rolls back, including the original data that caused the triggers. 
What I want to happen is that while everything else gets rolled back, Table1 
still has its data, along with the error messages returned by the triggers.

Nearest I can tell you can't do that with triggers, but I really don't 
understand how they work, so maybe I am wrong.

So, is there a way to do the following:

CREATE TRIGGER table1_insert AFTER INSERT ON TABLE1
BEGIN
   -- write to other tables (which can fire triggers or return
   --   constraint failures)
   -- if writes were not successful (constraint failures, etc.)
   --update table1 with message in Errors column
   -- else
   --write 'ok' to Errors column.
END;

Thanks,
David


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

Re: [sqlite] Why the deadlock?

[Pavel Ivanov]

> I don't know what you mean by 'cursor'.  SQLite has commands.  You execute 
> one command at a time.  Even a command like a SELECT that gathers lots of 
> data gathers the data all in one go, then finishes.  SQLite does not mark its 
> place with one command, then return to that place again with another command. 
>  SQLite never locks individual records, it only every locks the entire 
> database file in various ways.

Simon, read the whole thread please. Here is an example of 'cursor' in
SQLite which Nikolaus talks about:

stmt1 = sqlite3_prepare_v2(db, "select ...");
rc = sqlite3_step(stmt1);
assert(rc == SQLITE_ROW);
stmt2 = sqlite3_prepare_v2(db, "update ...");
rc = sqlite3_step(stmt2);
assert(rc == SQLITE_DONE);

You see, stmt2 was executed when 'cursor' stmt1 wasn't closed yet. And
AFAIK, 'cursor' is exact internal name for this select query in
SQLite's sources.


Pavel

On Thu, Aug 19, 2010 at 2:54 PM, Simon Slavin  wrote:
>
> On 18 Aug 2010, at 6:33pm, Nikolaus Rath wrote:
>
>> Still no one able to clarify the issues raised in this thread?
>>
>> Let me try to summarize what I still don't understand:
>>
>> - Will SQLite acquire and release an EXCLUSIVE lock while keeping a
>>   SHARED lock if one executes a UPDATE query with one cursor while a
>>   different cursor is in the middle of a SELECT query,
>>
>>   -or-
>>
>>   will the EXCLUSIVE lock be held until the SELECT query finishes?
>
> I don't know what you mean by 'cursor'.  SQLite has commands.  You execute 
> one command at a time.  Even a command like a SELECT that gathers lots of 
> data gathers the data all in one go, then finishes.  SQLite does not mark its 
> place with one command, then return to that place again with another command. 
>  SQLite never locks individual records, it only every locks the entire 
> database file in various ways.
>
>> - Is there a way to prevent SQLite from keeping the SHARED lock
>>   while waiting for an EXCLUSIVE lock if doing so would result in a
>>   deadlock (because another connection holding a SHARED lock needs to
>>   get an EXCLUSIVE lock before it can release the SHARED lock)?
>
> SQLite will never know about an impending deadlock, because there's no way to 
> make that information available to SQLite.  If your own application knows 
> this information you should be able to take care of it in your own 
> application.  Taking care of this situation correctly for arbitrary SQLite 
> requires you to pay attention to the various error conditions like 
> SQLITE_BUSY.
>
> Simon.
> ___
> 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] Sizeof tables

[Richard Hipp]

Download the sqlite3_analyzer.exe utility from the website and run it on
your database file.

On Tue, Aug 17, 2010 at 8:28 AM, Lukas Haase  wrote:

> Hi,
>
> My sqlite database is about 65 MB. The data is split into serval tables.
>
> Is there a way to enumerate the space requirements for each table so
> that I can see which tables are the memory consumers?
>
> Regards,
>  Luke
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Why the deadlock?

[Simon Slavin]

On 18 Aug 2010, at 6:33pm, Nikolaus Rath wrote:

> Still no one able to clarify the issues raised in this thread?
> 
> Let me try to summarize what I still don't understand:
> 
> - Will SQLite acquire and release an EXCLUSIVE lock while keeping a
>   SHARED lock if one executes a UPDATE query with one cursor while a
>   different cursor is in the middle of a SELECT query,
> 
>   -or-
> 
>   will the EXCLUSIVE lock be held until the SELECT query finishes?

I don't know what you mean by 'cursor'.  SQLite has commands.  You execute one 
command at a time.  Even a command like a SELECT that gathers lots of data 
gathers the data all in one go, then finishes.  SQLite does not mark its place 
with one command, then return to that place again with another command.  SQLite 
never locks individual records, it only every locks the entire database file in 
various ways.

> - Is there a way to prevent SQLite from keeping the SHARED lock
>   while waiting for an EXCLUSIVE lock if doing so would result in a
>   deadlock (because another connection holding a SHARED lock needs to
>   get an EXCLUSIVE lock before it can release the SHARED lock)?

SQLite will never know about an impending deadlock, because there's no way to 
make that information available to SQLite.  If your own application knows this 
information you should be able to take care of it in your own application.  
Taking care of this situation correctly for arbitrary SQLite requires you to 
pay attention to the various error conditions like SQLITE_BUSY.

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

Re: [sqlite] Sizeof tables

[Simon Slavin]

On 17 Aug 2010, at 1:28pm, Lukas Haase wrote:

> My sqlite database is about 65 MB. The data is split into serval tables.
> 
> Is there a way to enumerate the space requirements for each table so 
> that I can see which tables are the memory consumers?

Do you see the space taken by indexes as being part of the space taken by the 
table ?  If not, then I would just count the number of records of each table.

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

Re: [sqlite] Re ferential Integrity

[Simon Slavin]

On 19 Aug 2010, at 12:19pm, gher wrote:

> thanks for your reply, do you known some sqlite gui administrator to create
> "referential integrity" 

Your question does not make sense.  Everything done using the proper SQLite 
calls results in a database with referential integrity.  If you want 
referential integrity, just use SQLite the way the documentation says you 
should.

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

Re: [sqlite] Question concerning foreign keys across databases

[Simon Slavin]

On 18 Aug 2010, at 9:54pm, Stephan Wehner wrote:

> So attaching the file1 before creating the table in file2
> is going to fail? (Then sqlite would know about the {texts} table)

The ATTACH command is a bit of a misnomer: it doesn't do anything to the 
database files.  It affects a particular connection that you opened with 
sqlite3_open(), and allows your application to address both files on disk via 
the same connection.  It does not attach the two database files for any later 
connections to them, and the two files still don't know anything about 
one-another.

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

Re: [sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hi Pavel,

I think I guess that I understand what your point is.

On 2010-08-18 21:18, Pavel Ivanov wrote:
> This is exactly the reason why it's not logical action: SQLite will
> check constraint only in those places where it knows that something is
> changed and constraint can be violated. And it shouldn't re-check it
> in million other places where nothing seems to be changed...
> To catch up with reason of "why" consider these scenarios:
> 1) You connected to main, attached texts, made foreign constraint and
> inserted some records. Then you created other connection to texts
> directly and deleted all referenced records. How should SQLite know
> that they cannot be deleted?
> 2) You made another connection to main and connected another database
> as "texts". How foreign keys should be enforced in this situation?
I see, so the guarantees made by constraints are somehow just don't fit
with the feature of using more than one database at a time.


Thanks for pointing it out,

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

Re: [sqlite] Re ferential Integrity

[gher]

thanks for your reply, do you known some sqlite gui administrator to create
"referential integrity" 

Gher

Igor Tandetnik wrote:
> 
> gher  wrote:
>> Hello everybody, does support "referential integrity" SQLITE database..?
> 
> Yes it does.
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Referential-Integrity-tp29477414p29480803.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] BINARY type

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/19/2010 07:31 AM, Benjamin Peterson wrote:
> I was curious if there's a reason why BINARY as a column type doesn't produce 
> a
> column without a type affinity like BLOB. This would be one less special case
> between SQLite and other RDMS.

The mappings are defined in http://www.sqlite.org/datatype3.html

You are correct that there are potentially several more synonyms for the
various types.  However unless you can show massive problems the current
set can't realistically be changed since that would alter behaviour of
existing databases.  SQLite takes forward and backwards compatibility
very seriously.

But in any event this should not matter.  If you insert a blob into a
column with any other affinity then it will remain a blob.

sqlite> create table x(y binary);
sqlite> insert into x values(x'31');
sqlite> insert into x values(x'3100');

0x31 is the char '1'.

sqlite> .dump
CREATE TABLE x(y binary);
INSERT INTO x VALUES(X'31');
INSERT INTO x VALUES(X'3100');

The affinity will only affect you if you declare the column as type
'binary' (which will give integer affinity) and then insert a string
consisting of digits.  But if you expect the column to be binary, why
are you not supplying blobs?

If you are using Python for all this then I suggest taking it up on the
python-sqlite group since there are further "issues" behind what
pysqlite does.

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

iEYEARECAAYFAkxs6lUACgkQmOOfHg372QTCuQCg1bbJlQPWOslrnhH6XagB+U3s
Gq0AoOXlX7LfH/17usyqMuQqDlt+ufnI
=1nRf
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users