Re: [sqlite] SQLite DBaaS

If you're building a small web service, SQLite3 will do fine. If you want to scale big you might be able to use SQLite3 for some pieces of it, but you can't scale up a web service to thousands of servers with tens of cores and one single SQLite3 DB -- that just doesn't work given SQLite3's

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 3:07 PM, Richard Hipp wrote: > The SQLite byte-code engine was being too conservative and was reparsing > the schema in places where it was not strictly necessary.  The fix was to > restrict the places where the schema was reparsed to situations that

Re: [sqlite] Found it

Maybe a view is getting materialized, or a some other temp table's creation under the hood is triggering this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

On Mon, Jan 23, 2012 at 3:02 PM, John Elrick wrote: > I think I can inject something to do some measurements.  I seem to recall, > however, that there was no substantive difference in the number of > times sqlite3RunParser > was called between the two.  I'll check for: >

Re: [sqlite] [shell] utf-8 bug

On Tue, Dec 27, 2011 at 12:44 PM, Jens Frederich wrote: > The sqlite3 command line app doesn't write the string correctly to the > database file. It uses the terminal (cmd) encoding instead the 'PRAGMA > encoding' statement. None of the SQLite3 code converts between

Re: [sqlite] Procedure (Conditional statement) workaround

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

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

On Mon, Dec 19, 2011 at 10:31 PM, romtek wrote: > I've always thought that because SQLite didn't enforce data types, I could > do what I have in the example, and this has worked! So, is this a bug in > more recent versions of SQLite or an intended change that I am unaware of?

Re: [sqlite] Procedure (Conditional statement) workaround

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

Re: [sqlite] Convert data to binary

You can CAST TEXT to BLOB, and you can use x'' for literal BLOBs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free c code for embedded sqlite3

Docs would help people understand what you're up to... Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about a proper forum rather than an e-mail list

On Tue, Nov 15, 2011 at 3:08 PM, Alek Paunov wrote: > Me too. As simple first step - let's load the mail archives to downloadable > sqlite DB. Why are the mail archives for sqlite-users not available for download? As for loading them into a SQLite3 DB... I once wrote a schema

Re: [sqlite] How about a proper forum rather than an e-mail list

You're not yet convincing me (though that probably doesn't matter); repeating my arguments would be obnoxious (or worse: boring!), so I won't. Let's try a different approach: what's the ideal here? Here's my answer: a plethora of interfaces to the same data (posts/threads). I'd like to see: -

Re: [sqlite] I have a stumper

I thought nowadays SQLite3 was smart enough to re-prepare a prepared statement when the schema changes. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserts, performance, file lock...

On Fri, Nov 11, 2011 at 2:38 PM, yqpl wrote: > yes still slows down. Can you characterize it? All index inserts should slow down somewhat as the index grows since lookup and insertion will be O(logN) operations for b-trees, but also as your indexes grow larger than

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On Fri, Nov 11, 2011 at 1:16 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 11 Nov 2011, at 6:09pm, Nico Williams wrote: >> blob = blob should be a binary comparison >> >> blob = string should be a string comparison >> >> blob LIKE pattern s

Re: [sqlite] inserts, performance, file lock...

On Fri, Nov 11, 2011 at 1:39 AM, yqpl <y...@poczta.onet.pl> wrote: > Nico Williams wrote: >> What's your page size? > > i have no access now to those files. but i didnt change any thing - so > default. You really want to set the page size to something decent -- at least th

Re: [sqlite] inserts, performance, file lock...

On Thu, Nov 10, 2011 at 3:19 AM, yqpl wrote: > i did some test do check if indexes make it slow. instead of inserting to > disk database i use ":memory:" database - i have copied tables only - i > assume without indexes and then do inserts - and it works the same. UNIQUE

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On Fri, Nov 11, 2011 at 11:55 AM, Yuriy Kaminskiy wrote: > One way or other, "=", "LIKE" and "GLOB" results should be consistent. > If string is NUL-terminated, "=" should ignore everything after NUL. > If string is length-terminated, "LIKE" should not ignore bytes after NUL.

Re: [sqlite] inserts, performance, file lock...

What's your page size? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INDEX Types

On Wed, Nov 9, 2011 at 3:24 PM, Fabian <fabianpi...@gmail.com> wrote: > 2011/11/9 Nico Williams <n...@cryptonector.com> >> I don't get it.  You're reading practically the whole file in a random >> manner, which is painfully slow, so why can't you read the fil

Re: [sqlite] INDEX Types

On Wed, Nov 9, 2011 at 1:53 PM, Fabian <fabianpi...@gmail.com> wrote: > 2011/11/9 Nico Williams <n...@cryptonector.com> >> What's wrong with reading the whole file into memory at boot time as a >> way to prime the cache?  Rebooting always takes some time, mostly th

Re: [sqlite] INDEX Types

Fabian, What's wrong with reading the whole file into memory at boot time as a way to prime the cache? Rebooting always takes some time, mostly the time to read all sorts of files. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] INDEX Types

On Wed, Nov 9, 2011 at 12:04 PM, Fabian wrote: > 2011/11/9 Simon Slavin >> Didn't someone recently note that entering the first million records was >> fast, but if he then closed and reopened the database, entering the next >> 100,000 records was slow

Re: [sqlite] Slow INDEX

On Thu, Nov 3, 2011 at 12:39 PM, Fabian wrote: > I just tested it, and it made no difference. The root cause of the problem > is most likely not slow writes, because inserting duplicate values (which > are ignored instead of written to disk) are just as slow. If you could

Re: [sqlite] Slow INDEX

On Wed, Nov 2, 2011 at 2:13 PM, Fabian <fabianpi...@gmail.com> wrote: > 2011/11/2 Nico Williams <n...@cryptonector.com> >> But note that this can still fail you when the file is larger than >> available RAM.  In that case such a flag would be very bad.  And >>

Re: [sqlite] Slow INDEX

On Wed, Nov 2, 2011 at 1:20 PM, Fabian wrote: > Linux will not read the whole file in, but Windows eventually does. The > inserts go progressively faster when they are reaching halfway, and Windows > reads very large pages from disk, even if you request only 10 bytes. So in

Re: [sqlite] Slow INDEX

On Wed, Nov 2, 2011 at 11:41 AM, Fabian wrote: > 2011/11/2 Mr. Puneet Kishor >> ahh, so you *are* getting expected behavior, just not what *you* expected. >> Did you have a different number in mind instead of a factor of 300? And, if >> so, why? > > To

Re: [sqlite] How about a proper forum rather than an e-mail

On Thu, Oct 27, 2011 at 12:35 PM, Pete wrote: > The one attraction of a forum to me is that it's searchable so I'd be able > to check for any discussions before posting to the mailing list. Is there an > archive for the mailing list somewhere which could serve the same

Re: [sqlite] How about a proper forum rather than an e-mail list

On Sun, Oct 23, 2011 at 8:42 AM, Yves Goergen wrote: > On 18.10.2011 16:40 CE(S)T, Simon Slavin wrote: >> The way to settle this is easy: leave the mailing list in place. >> Create a web forum.  If people abandon the mailing list and start >> using the web forum

Re: [sqlite] Performance of SELECT: What am I doing wrong?

On Wed, Oct 26, 2011 at 5:01 PM, Jay A. Kreibich wrote: >  The use of IS is causing the query optimizer to use a full table >  scan, essentially turning the query into a O(n) operation.  This has >  to do with how IS differs from = in the handling of NULLs.  Since it >  is

Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

On Wed, Oct 19, 2011 at 4:16 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Wed, Oct 19, 2011 at 03:24:35PM -0500, Nico Williams scratched on the wall: >> Also, regarding NFS, it would be safe to use if SQLite3 were to use >> whole-file byte range locks.  NFS makes conc

Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the wall: >> On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal <sgb...@googlemail.com> wrote: >> > On Tue, Oct 18,

Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > wrote: >> What can I do to make SQLite run safely on CIFS? > > Nothing. Even MS Access cannot (or could not way back when i used it) be >

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

On Tue, Oct 18, 2011 at 7:41 AM, John Drescher wrote: > My biggest reason for wanting a mailing list versus a forum is that I > subscribe to 20+ mailing lists that all go to my gmail account with > gmail rules to organize the content. If these mailing lists all were > forums

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

On Tue, Oct 18, 2011 at 4:35 AM, Frank Missel wrote: > I think that the sqlite-users e-mail list has enough traffic to warrant a > proper forum. > > Has this been considered? I know of no better forum than a mailing list for this sort of thing. Mailing lists have archives that

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Fri, Oct 7, 2011 at 5:52 PM, Simon Slavin wrote: > Okay, I understand why defining an ORDER BY requires the entire result set to > be retrieved.  I had intended to remove ORDER BY when I used COUNT(*), though > I didn't mention that. If the ORDER BY can be satisfied

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Fri, Oct 7, 2011 at 1:17 PM, Roger Binns wrote: > On 07/10/11 09:52, Simon Slavin wrote: >> "Do you really want to see all 50,000 entries that that search would >> return ?".  If this kind of search returns more than 100 records, >> there's no point in doing it at all. >

Re: [sqlite] Help with CASE WHEN

On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor wrote: > The above is not SQL. You can't have a SQL statement begin with CASE. SQL > statements can only begin with either SELECT or UPDATE or CREATE or DELETE or > ALTER, etc. CASE is an expression, and has to be a

Re: [sqlite] How to parse VIEW fields definitions?

On Tue, Jun 14, 2011 at 1:58 PM, Alexey Pechnikov wrote: > As example, we have view: > > create view vtest as select name1 || ' ' || name2 as name from test; > > How to get the definition of "name" field (will be "name1 || ' ' || > name2")? Of cource, the view can be more

Re: [sqlite] Fwd: Possible small memory leak

On Tue, Jun 7, 2011 at 5:31 AM, Ronald Burgman wrote: > Now, I'm not sure if getpwuid actually allocates memory. Some > documentation does not mention anything about it; some mention it is not > possible; some mention that getpwuid can result in an ENOMEM >

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Tue, Jun 7, 2011 at 4:36 AM, Jean-Christophe Deschamps wrote: > Now, please try this: You miss the point. Not every app requires extended precision. But just because you don't require extended precision doesn't mean you can't use FP at all. It depends on the app. Nico

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 6:28 PM, Simon Slavin wrote: > I've rethought my earlier position.  This re-think is the result of the SQL > standard being incompatible with the IEEE standard.  If you want to do IEEE > arithmetic, do it in your own software, and use SQL just for

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 4:58 PM, Jean-Christophe Deschamps wrote: > Look at a FP-intensive product like Spatialite (SQLite-based).  You'd > probably agree it performs much more complex tasks than average, mean > squares and such. > I'd be very surprised if it used NaN

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 4:27 PM, Sidney Cadot wrote: >> If you want bare metal IEEE 754 for your scientific computing >> application, then you might want to rethink doing your math operations >> in a data storage system. > > You are making it sound as if proper support for

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 2:55 PM, Jean-Christophe Deschamps wrote: > Allow me to add a humble bit to what Jay just posted. > > SQLite, as well as most other RDBMS around, allow you to perform FP > calculations in SQL statements.  I assume no-one imagines an extended > FP

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 5:57 AM, Simon Slavin wrote: > No, it's in the standard.  Unfortunately you have to pay to receive the > standards document, but in the draft standard at > > http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt > > see the top of page 7: > >

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011 10:31 AM, "Ian Hardingham" wrote: > > Hey guys, once again thanks for the help. > > Should really every single INSERT/UPDATE section have a begin/end > transaction around it? There's an implied begin/commit if you don't put them there. It's when you can batch

Re: [sqlite] Do I need to migrate to MySQL?

Indexes slow down writes somewhat, true, but it sounds like the OP's issue is with commit latency, the average minimum bound for which is given by the storage hardware's capabilities. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011 10:04 AM, "Ian Hardingham" wrote: > > Thank you Igor, I'll do some more thorough profiling. > > When I run the query: > > UPDATE multiturnTable SET complete=1 WHERE id=-5 > > This takes ~45ms (as reported by SQLite's profile) - is this in the > right ballpark?

Re: [sqlite] Testing for a null string

Try "... WHERE f IS NULL". SQL is a programming language with three-valued logic, meaning it has truth, falsehood, and null. NULL != NULL, strangely enough. Nico -- On Jun 3, 2011 8:54 AM, "Paul Sanderson" wrote: > I am sure tihs is basic but. > > I have a

Re: [sqlite] dangerous allocation?

On Thu, Jun 2, 2011 at 10:53 AM, Richard Hipp wrote: > http://www.sqlite.org/src/ci/efb20b9da6 > > Note, however, that lemon.c is not a deliverable component of SQLite, but > rather a code generator program that generates some of the C code for > SQLite, and lemon always runs on

Re: [sqlite] What is so slow on dropping tables?

On Jun 1, 2011 1:46 PM, "Jan Hudec" <b...@ucw.cz> wrote: > On Tue, May 31, 2011 at 16:44:13 -0500, Nico Williams wrote: > > On Tue, May 31, 2011 at 4:22 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > > Split the DROP into two stages: > > > &g

Re: [sqlite] Bug in sqlite3_bind_parameter_name

You could set a flag nothing that parameters have been optimized out, so the statement author can be warned. Where to encode the flag? Maybe in some opcode's unused parameter? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] What is so slow on dropping tables?

On Tue, May 31, 2011 at 4:22 PM, Simon Slavin wrote: > Split the DROP into two stages: > > DELETE FROM myTable; > DROP TABLE myTable; > > Which one takes all the time ?  If it's the second one, then perhaps just > delete all the records.  Filling the table back up again

Re: [sqlite] What is so slow on dropping tables?

Just a guess: finding all the pages to free requires traversing the internal nodes of the table's b-tree, which requires reading a fair subset of the table's b-tree, which might be a lot of I/O. At 150MB/s it would take almost two minutes to read 15GB of b-tree pages from a single disk, and

Re: [sqlite] using a view for fts

Use AS to ensure that views'columns get useful column names. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB triggers update

Answering myself... It may be feasible to [with little code] catch schema changes that would invalidate DB triggers before committing them. I'm not yet sure how to do that without re-entrance, but that may be OK too (hey, OP_ParseSchema does it). At least I've an idea that might be worth

[sqlite] DB triggers update

I've a patch that has only one bug that I'm aware of left: in autocommit mode, a PRAGMA connect_triggers=1; will fire any connect triggers and any before commit triggers, but not any after begin triggers -- mildly annoying, but tolerable. Writing tests, however, I discovered something subtle and

[sqlite] Bug in test/uri.test?

foreach {tn uri file} { 1 test.db test.db ... 14 file:test%00.db%00extra test 15 test.db?mork=1#boris test.db?mork=1#boris 16 file://localhostPWD/test.db%3Fhello test.db?hello } { if

Re: [sqlite] (no subject)

On Thu, May 19, 2011 at 4:28 PM, Nico Williams <n...@cryptonector.com> wrote: > However, I'm not sure how to write this such that there can be only > one of those constraints of which there should be just one but without > then imposing ordering on those constraints.  IMO there's n

Re: [sqlite] (no subject)

On Thu, May 19, 2011 at 4:22 PM, Pavel Ivanov wrote: >> Is there a rationale for allowing such statements or is that an effect >> of the 'Lite' nature?  (Note: I'm not complaining, just asking.) > > I believe that's an effect of the "typeless" design. As SQLite doesn't > have

Re: [sqlite] Fossil bugs?

On Thu, May 19, 2011 at 12:37 PM, Richard Hipp <d...@sqlite.org> wrote: > On Thu, May 19, 2011 at 1:23 PM, Nico Williams <n...@cryptonector.com>wrote: > Everything is protected by multiple cryptographic hashes, both SHA1 and > MD5.  On-the-wire corruption is not a realistic po

Re: [sqlite] Fossil bugs?

On Thu, May 19, 2011 at 12:01 PM, Richard Hipp wrote: > Anythings possible.  But we've been using Fossil heavily, daily, for 4 years > now without any hints of these kinds of problems.  So fundamental bugs like > what you propose seem improbable.  I'm thinking something else is

[sqlite] Fossil bugs?

I installed Fossil using aptitude, but I'm thinking I should have built and installed it from source.  I'm seeing a few issues with the version I'm using ([15cb835736] 2010-06-17 18:39:10 UTC):  - The Makefile from the SQLite3 docs repository doesn't get checked out -- I thought there wasn't any,

Re: [sqlite] About Fossil usage

I suppose one could use the "shunned artifacts" feature, then rebuild the repository as a way to collapse deltas, but that sounds like a lot of work. I'll just not collapse deltas. Also, I like the git format-patch feature - it's basically a diff with a header slapped on, but still, it's quite

Re: [sqlite] About Fossil usage

On Thu, May 19, 2011 at 3:08 AM, Stephan Beal <sgb...@googlemail.com> wrote: > On Thu, May 19, 2011 at 7:03 AM, Nico Williams <n...@cryptonector.com>wrote: >> How does one remove changesets?  How does one collapse deltas? > > Fossil doesn't allow one to remove change

Re: [sqlite] About Fossil usage

On Wed, May 18, 2011 at 9:50 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, May 18, 2011 at 1:27 PM, Nico Williams <n...@cryptonector.com>wrote: >> Also, I didn't expect commits and branches to go to the repository >> that I cloned mine from.  That was surp

Re: [sqlite] How to build SQLite3 docs?

On Wed, May 18, 2011 at 9:44 PM, Richard Hipp wrote: > You need tclsqlite3.c, not sqlite3.c.  main() is found in tclsqlite3.c.  The > comment in the Makefile is not clear on this point and needs to be fixed Oh, duh. Thanks! Nico --

Re: [sqlite] SQL Statement Help(selecting days).

On Wed, May 18, 2011 at 5:11 PM, Petite Abeille wrote: > Where does the start_gap and end_gap come from? They are only declared in the > select part of the inner select statement, and nowhere in the from part. But > nonetheless, SQLite manages to use these non existing

[sqlite] How to build SQLite3 docs?

The SQLite3 docs Fossil repository doesn't seem to include any instructions for building the docs. I've searched sqlite.org up and down (docs and wiki both) for these instructions. I've also searched the web a bit and found nothing obvious. I have figured out something from the file named

Re: [sqlite] SQL Statement Help(selecting days).

On Wed, May 18, 2011 at 4:10 PM, Petite Abeille wrote: > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote: >> How can I simulate a >> calendar table(maybe using the strftime funtion)? > > Well, you have two broad options: > > (1) materialize the calendar as a table >

Re: [sqlite] DB triggers: Initial patch

On Wed, May 18, 2011 at 4:14 PM, Petite Abeille wrote: > Very nice, thanks for sharing :) > > Wish such a functionality was part of the stock SQLite :)) Thanks for the kind words! I will do my best to make it palatable and hopefully desirable for the core dev team to

[sqlite] About Fossil usage

So I've stumbled twice trying to set up use of Fossil, both times with respect to how to create a private branch. First I did "fossil branch new ..." and didn't realize that the workspace was not switched to be in that branch, so that my subsequent commit didn't go to that branch. My second

Re: [sqlite] Is is it possible to close the random rowid gaps in a sqlite table?

On Tue, May 17, 2011 at 7:37 PM, Frank Chang wrote: >   Hi, I just ran this sqlite command to randomly delete 20% of the rows in a > 15000 row sqlite table. DELETE FROM TABLE > WHERE ROWID IN (SELECT ROWID FROM TABLE ORDER BY RANDOM() LIMIT 3000) >       Now there are

Re: [sqlite] DB triggers: Initial patch

A few more comments: - The patch adds just 8KB to libsqlite3.a and the shell, and this is true regardless of whether the before and after compilations use -g or not, -O0 vs. -O2, and -DSQLITE_DEBUG=1 or not. - I've tried to keep the SQLite3 C style as best I could. - My earlier

Re: [sqlite] Need to be able to rename attributes

On Mon, May 16, 2011 at 1:29 PM, Simon Slavin wrote: > Yet strangely, the ability to obtain the statements used to create the schema > is something I find very useful in quite a few utilities.  If you could > depend on them being in a standard format they'd be even more

Re: [sqlite] Need to be able to rename attributes

On Mon, May 16, 2011 at 1:18 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Mon, May 16, 2011 at 12:49:14PM -0500, Nico Williams scratched on the wall: >> Nit: that's almost certainly the reason that SQLite3 doesn't support >> column rename, > >  I think the bigger

Re: [sqlite] DB triggers: Initial patch

And here's the patch. I should have been using Fossil all this time. I'll switch to Fossil soon. For now the patch is to sqlite\-src\-3070602. A few more notes on the patch besides the ones I posted earlier: - DB triggers are omitted by defining SQLITE_OMIT_TRIGGER - DB triggers are

Re: [sqlite] Need to be able to rename attributes

On May 16, 2011 9:33 AM, "Simon Slavin" wrote: > On 16 May 2011, at 3:44am, romtek wrote: > > > Secondly, if I executed the above SQL code, what would happen to triggers, > > etc. that are associated with the original table? > > I suspect that's a major reason why SQLite

[sqlite] DB triggers: Initial patch

In my next post I'll post a [681 line, 28KB unified diff, or 504 line regular diff] patch implements the following DB triggers: - AFTER DATABASE CONNECT - AFTER TRANSACTION BEGIN - BEFORE TRANSACTION COMMIT These triggers do exactly what I want, and nothing more. If anyone wants to test this

Re: [sqlite] Need to be able to rename attributes

It's perfectly fair to ask for a feature. And ALTER TABLE rename column support is a well justified feature to want, IMO; I don't blame you for wanting it. Whether it will be added, or when, I'd not know. The dev team (whom I do not speak for) has its priorities, and its paying customers to

Re: [sqlite] Need to be able to rename attributes

On Sun, May 15, 2011 at 9:44 PM, romtek wrote: > On Sun, May 15, 2011 at 5:10 PM, Mr. Puneet Kishor wrote: >> > I want to rename date to dateAdded. >> >> sqlite doesn't support changing the name of a table column (and, neither >> you nor your user should be

Re: [sqlite] IN clause in search query to search a single field containing comma delimited values

On May 13, 2011 8:17 PM, "BareFeetWare" wrote: > There is no function built into SQLite to convert a text string into a set (eg convert "1,8,15" into (1, 8, 15)), but such a function is not needed in this case. You need a better design of your database. SQLite is

Re: [sqlite] Transaction triggers?

I believe I've got solutions to the various little problems I've run into. My experiments have helped me shed some light on what the semantics of DB triggers should be, to the point where I think I've reached stable conclusions about those semantics. I'm also ready to characterize performance

Re: [sqlite] Can I dynamically select a table ?

On Wed, May 11, 2011 at 9:03 PM, John wrote: > Yes, I could. But considering that I'm applying tons of logic and not just > selected this would be a real mess. Not even sure I could pull it. > Normalization was something I lacked with regard to previous post. But in > this

Re: [sqlite] Can I dynamically select a table ?

On Wed, May 11, 2011 at 8:47 PM, John wrote: > That would work if I needed to select a single column from a table. But if I > need to select multiple values (c1, c2), then it wouldn't work. Can't have > subquery with more than one column selected, in general, I think. You can

Re: [sqlite] Dynamic SQL for SQLite?

On May 11, 2011 7:14 PM, "John" wrote: > let's say I have a table with columns for each day of the week > > create table seven_days > (monday_value integer, > tueday_value integer, > wednesday_value integer, > ... ); > > I want to select value from whatever day it is

Re: [sqlite] Three questions

On Wed, May 11, 2011 at 12:18 PM, Martin Engelschalk wrote: > This question does not arise with SQLite, because parallel transaction > are not supported, as Igor and Pavel pointed out. > > However, consider this: If you have a unique constraint on a table like > in your

Re: [sqlite] Transaction triggers?

FWIW, I'm making progress. I've got BEGIN and COMMIT triggers firing, but there's issues related to auto-commit which imply that I need to be able to skip -at runtime- the trigger firing that I code with each OP_Transaction and OP_AutoCommit operation, which I think means I need a new op, but

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

On Tue, May 10, 2011 at 12:00 PM, Jay A. Kreibich wrote: >> I guess I am looking for a "round robin queue" here? > >  I'd do something like this.  This keeps a constant number of messages >  in the log.  The "msg_id" provides a message counter, while the >  "msg_seq" is used to

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

Or just a function to return the size of the current DB. Mind you, automatically deleting rows from a log table isn't enough: you may have to periodically VACUUM the DB, or you may have to setup auto_vacuum (and incremental_vacuum). I have code like this in one DB: CREATE TABLE IF NOT EXISTS

Re: [sqlite] Transaction triggers?

I've researched firing of these triggers somewhat. Upshot: firing anything other than a transaction commit trigger is tricky. The semantics of transaction triggers are interesting. Presumably such triggers should execute in the same transaction as the one they are related to -- but this would

Re: [sqlite] Transaction triggers?

The reason to delay connection trigger firing to the first non-pragma statement would be to allow one to enable or disable db triggers. DB triggers should also be disabled by default, and ahould have a separate set of pragmas to enable or disable them. The main utility of connect triggers is to

Re: [sqlite] Transaction triggers?

As for attached DBs, each DB gets its own db triggers. DB connect trigger firing should be about the same (first non-pragma statement affecting the attached db). ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Transaction triggers?

On May 8, 2011 4:14 PM, "Roger Binns" wrote: > C) What about SAVEPOINT? Sounds useful... I should add that too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction triggers?

Indeed, I have been thinking about when database connect fires. My current thought is: on the first non-pragma statement executed (not prepared), not at db open time. I only care about commit, really, but if I can I'll do the others too. ___

Re: [sqlite] Transaction triggers?

FWIW, I have parsing, automatic creation of the crutch views, and creation of the DB triggers working. Next up: firing of DB triggers. The changes so far are fairly trivial, adding very few branches, which means that writing tests for them should be relatively simple too. That said, it's taken

Re: [sqlite] Transaction triggers?

It took just a few minutes to start on that approach. I have the necessary parse.y changes done and know what to do for the rest. Time to subscribe to sqlite-dev... ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Transaction triggers?

BTW, I might implement and contribute something like this. I'm free to contribute any changes to SQLite3 that I make. I'd need some guidance though. And what I cannot contribute is the high degree of testing that SQLite3 is known for. The parsing aspect of database triggers is simple enough.

Re: [sqlite] Transaction triggers?

On Fri, May 6, 2011 at 4:30 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 6 May 2011, at 10:14pm, Nico Williams wrote: >> Here's what I need: >> >> - at transaction time I need a way to record somewhere that the >> transaction did start.  This would

<    1   2   3   4   5   >