On Mon, Feb 6, 2012 at 1:20 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 06/02/12 09:11, Nico Williams wrote:
>> A checksum per-row is certainly a valuable thing at the layer above the
>> RDBMS since it allows for integrity checking ab
On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick wrote:
> Is there no way to force columns added to a table with alter table to be
> added at certain column positions?
>
> Alternatively, if there is some way to save the data in an existing table;
> drop the table; re-create the table with the desire
A checksum per-row is certainly a valuable thing at the layer above
the RDBMS since it allows for integrity checking above the RBDMS, and
in an RBDMS-independent manner. Of course, this approach is easiest
to graft into SQLite3 simply because you'd be adding a hidden column
and the machinery for m
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 archite
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 really
> needed it.
Th
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
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:
>
> which query is being par
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 encodings (other than UTF8<->U
On Tue, Dec 20, 2011 at 5:21 AM, John Gillespie wrote:
> This would make a good entry for an Obfuscated SQL contest.
> Well done
Thanks, I guess :) It was a fun little SQL ditty to write, and only
took a few minutes. (Now I do I a search and see that factorial in
SQL is a fairly common toy prob
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?
SQLite3 doesn't do a
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 itera
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
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
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 for
this, and it's
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:
-
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
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
available memory you'll notice
On Fri, Nov 11, 2011 at 1:16 PM, Simon Slavin 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 should either treat the blob as a stri
On Fri, Nov 11, 2011 at 1:39 AM, yqpl 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
the filesystem
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 constraints on columns im
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.
blob = blob should b
What's your page size?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Wed, Nov 9, 2011 at 3:24 PM, Fabian wrote:
> 2011/11/9 Nico Williams
>> 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 file in one
>> fell swoop (i.e., sequential reads)??
On Wed, Nov 9, 2011 at 1:53 PM, Fabian wrote:
> 2011/11/9 Nico Williams
>> 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.
>>
> I
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
http://sq
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 ?
>>
> Yes, and there is still no real explan
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 use DTrace you could rea
On Wed, Nov 2, 2011 at 2:13 PM, Fabian wrote:
> 2011/11/2 Nico Williams
>> 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
>> SQLite3 can't know how much RAM is available. The OS ca
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
The largest read will
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 read an (un-cached) 150mb file from disk, do
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 prupose?
http://www.google.
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 instead, it worked. If people stay
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 possible bind a NULL to
On Wed, Oct 19, 2011 at 4:16 PM, Jay A. Kreibich 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 concurrent access to byte
>
On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich 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 wrote:
>> > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren
>> > wrote:
>> &g
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
> safely used on SMB/CIFS storage.
Can you elaborate as
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 I would not bother wit
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 can be downloaded
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 entirely by an index, the
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.
>
> You can solve this at
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 replacement for a column.
>
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 complex.
Take Lemon and
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
> (allocation failed) error,
> which clear
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
--
__
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 retrieving the
> numbers
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 representations!
Sure, if you
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 IEEE-754 types would
> op
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 fine-grain support of hundre
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:
>
> "null value (null): A speci
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 lots of INSERT/UPDATE
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
http://s
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? I'm running a fai
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 database with a text column and i
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 a workstation, and
On Jun 1, 2011 1:46 PM, "Jan Hudec" 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
wrote:
> > > Split the DROP into two stages:
> > >
> > > DELETE FROM myTable;
> > >
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
http://sqlite.
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 with new rows
> obviously
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 that's
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
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
pursing
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 o
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 {$tcl_platform(platform)=="w
On Thu, May 19, 2011 at 4:28 PM, Nico Williams 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 no need to
> fix
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 strict type names for
On Thu, May 19, 2011 at 12:37 PM, Richard Hipp wrote:
> On Thu, May 19, 2011 at 1:23 PM, Nico Williams wrote:
> Everything is protected by multiple cryptographic hashes, both SHA1 and
> MD5. On-the-wire corruption is not a realistic possibility.
Excellent.
> Is that normal?
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 going on.
Is TLS u
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,
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 us
On Thu, May 19, 2011 at 3:08 AM, Stephan Beal wrote:
> On Thu, May 19, 2011 at 7:03 AM, Nico Williams wrote:
>> How does one remove changesets? How does one collapse deltas?
>
> Fossil doesn't allow one to remove changesets (and i'm not sure what
> collapsing delt
On Wed, May 18, 2011 at 9:50 PM, Richard Hipp wrote:
> On Wed, May 18, 2011 at 1:27 PM, Nico Williams wrote:
>> Also, I didn't expect commits and branches to go to the repository
>> that I cloned mine from. That was surprising! With git and other
>> similar VCSes su
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
--
___
sqlite-user
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 columns in the where
> cl
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 main.m
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
> (2) virtualize the calendar
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 integrate my patches. I
don
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 attem
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 gaps in the rowid in the mod
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 characterizati
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 useful.
I like the fact
On Mon, May 16, 2011 at 1:18 PM, Jay A. Kreibich 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 issue is t
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 implement
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 doesn't support DROP COLUM
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
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 worry
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 doing this -- there is something strange
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 relational
and you need to make yo
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 and
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 case, I don't think it h
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 do one case for each
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 today. So if today is
> T
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 example, when should the da
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 we'l
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 keep the round-robi
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 Log
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 me
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 l
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
http://sqlite.org:8080/cgi-bin/mailman
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
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.
___
sqlite-users
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 qui
301 - 400 of 448 matches
Mail list logo
