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

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

Re: [sqlite] partial index?

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

Re: [sqlite] partial index?

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?

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,

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

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

Re: [sqlite] partial index?

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 >

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

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

Re: [sqlite] playing with triggers

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 >

Re: [sqlite] playing with triggers

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

Re: [sqlite] playing with triggers

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,

Re: [sqlite] partial index?

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.

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

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

Re: [sqlite] partial index?

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

Re: [sqlite] partial index?

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

Re: [sqlite] partial index?

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

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

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

Re: [sqlite] partial index?

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

[sqlite] partial index?

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

[sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

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

Re: [sqlite] playing with triggers

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,

Re: [sqlite] playing with triggers

--- 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

Re: [sqlite] Why the deadlock?

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

Re: [sqlite] playing with triggers

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

Re: [sqlite] Why the deadlock?

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

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

Re: [sqlite] Why the deadlock?

> 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

Re: [sqlite] Sizeof tables

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

Re: [sqlite] Why the deadlock?

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

Re: [sqlite] Sizeof tables

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

Re: [sqlite] Re ferential Integrity

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

Re: [sqlite] Question concerning foreign keys across databases

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

Re: [sqlite] Question concerning foreign keys across databases

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

Re: [sqlite] Re ferential Integrity

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 > >

Re: [sqlite] BINARY type

-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