Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 2:44 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams <n...@cryptonector.com> wrote: > Right. Virtual Tables are very flexible, but the syntax is indeed not > practical, and it also forces you to

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden wrote: > Right you are, except it's not a question of efficient but of > possible. A UDF works one of two ways: > > 1. Per row. The engine calls the function for each row. The function > transforms the input into the

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

On Wed, Feb 27, 2013 at 7:53 AM, James K. Lowden wrote: > begin transaction; > update tgt > set name = (select name from src where tgt.key = src.key) > where exists ( > select 1 from src > where src.key = tgt.key > ); > -- check for error Yes, this is

Re: [sqlite] light weight write barriers

On Mon, Nov 26, 2012 at 6:05 PM, Larry Brasfield <larry_brasfi...@iinet.com> wrote: > Nico Williams emitted: > >> You keep saying that programmers don't understand "barriers". You've >> provided no evidence of this. Meanwhile memory barriers are generally >&g

Re: [sqlite] light weight write barriers

Vlad, You keep saying that programmers don't understand "barriers". You've provided no evidence of this. Meanwhile memory barriers are generally well understood, and every programmer I know understands that a "barrier" is a synchronization primitive that says that all operations of a certain

Re: [sqlite] light weight write barriers

On Tue, Nov 13, 2012 at 11:40 AM, Alan Cox wrote: >> > Barriers are pretty much universal as you need them for power off ! >> >> I'm afraid, no storage (drives, if you like this term more) at the moment >> supports >> barriers and, as far as I know the storage history,

Re: [sqlite] "natrual" join (not "natural")

On Thu, Nov 1, 2012 at 5:18 PM, Mark Brand wrote: > Why should the "missing" AS even be a warning in lint mode? SQL-92 does not > mandate it. One of the most important implementations does not even accept > it. Do you know of any SQL implementation that requires an AS here?

Re: [sqlite] light weight write barriers

Hmm, so sorry I didn't notice the cc'ing of the linux-kernel list, resulting in so much additional traffic to sqlite-users, which I'll drop in my replies to the linux-kernel list. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Union all writting on /var/tmp

Also, as far as I'm concerned, if the choice is "more optimizations in SQLite3" or "more work on SQLite4", then put me down for the latter. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Union all writting on /var/tmp

On Mon, Oct 29, 2012 at 8:08 PM, Richard Hipp wrote: > The reason for using a temp table for UNION ALL in a subquery is because > that is the path of least resistance. [...] For what it's worth, UNION ALL is generally understood to be a major optimization over UNION because no

Re: [sqlite] light weight write barriers

On Wed, Oct 24, 2012 at 8:04 PM, <da...@lang.hm> wrote: > On Wed, 24 Oct 2012, Nico Williams wrote: >> COW is "copy on write", which is actually a bit of a misnomer -- all >> COW means is that blocks aren't over-written, instead new blocks are >> written.

Re: [sqlite] light weight write barriers

On Wed, Oct 24, 2012 at 7:17 PM, Simon Slavin wrote: > A) fsync() doesn't work the way it's meant to on the majority of user > platforms. It effectively does nothing. Here are typical notes for Windows > Server and FreeBSD: Many systems lie, that's true. For example:

Re: [sqlite] light weight write barriers

On Wed, Oct 24, 2012 at 5:03 PM, wrote: > I'm doing some work with rsyslog and it's disk-baded queues and there is a > similar issue there. The good news is that we can have a version that is > linux specific (rsyslog is used on other OSs, but there is an existing queue >

Re: [sqlite] light weight write barriers

On Tue, Oct 23, 2012 at 2:53 PM, Vladislav Bolkhovitin <...@gmail.com> wrote: >> As most of the time the order we need do not involve too many blocks >> (certainly a lot less than all the cached blocks in the system or in >> the disk's cache), that topological order isn't likely to be very >>

Re: [sqlite] Why can't SQLite support ALTER TABLE commands that rewrite the table?

On Fri, Oct 19, 2012 at 4:14 PM, Yves Goergen wrote: > Since my last question was not answered but instead another statement > was made which I don't understand, I feel the need to reformulate my > question to this: Why is it that SQLite can only support ALTER TABLE >

Re: [sqlite] light weight write barriers

On Fri, Oct 12, 2012 at 5:14 PM, Simon Slavin wrote: > I think I understand what you're asking for, but I see no point in being > informed about D, because I can't see anything useful a program can do if the > transaction gets marked 'complete' but D doesn't succeed.

Re: [sqlite] light weight write barriers

On Fri, Oct 12, 2012 at 4:08 PM, Simon Slavin wrote: > If all you're doing is showing something on a display that's fine. But if > that's what you're doing I see no point in distinguishing between 'success' > and 'durable'. As far as I can see your program has nothing to

Re: [sqlite] light weight write barriers

On Fri, Oct 12, 2012 at 3:53 PM, Simon Slavin wrote: > That's an interesting idea. I have a question. Suppose your program > received the 'success' result for a transaction and carried on to do other > transactions. Later you test to see whether the transaction is

Re: [sqlite] light weight write barriers

On Fri, Oct 12, 2012 at 2:58 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 10/11/2012 11:38 PM, Nico Williams wrote: >> There is something you can do: [...] > > SQLite WAL mode comes close to that if you run your checkpoints > in the background. [...] Right. WAL m

Re: [sqlite] light weight write barriers

Lying hardware is a different problem. Richards was asking for something else. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

On Thu, Oct 11, 2012 at 11:59 AM, Simon Slavin <slav...@bigfraud.org> wrote: > On 11 Oct 2012, at 5:38pm, Nico Williams <n...@cryptonector.com> wrote: >> There is something you can do: use a combination of COW on-disk >> formats in such a way that it's possible to

Re: [sqlite] light weight write barriers

To expand a bit, the on-disk format needs to allow the roots of N of the last transactions to be/remain reachable at all times. At open time you look for the latest transaction, verify that it has been written[0] completely, then use it, else look for the preceding transaction, verify it, and so

Re: [sqlite] light weight write barriers

On Wed, Oct 10, 2012 at 12:48 PM, Richard Hipp wrote: >> Could you list the requirements of such a light weight barrier? >> i.e. what would it need to do minimally, what's different from >> fsync/fdatasync ? > > For SQLite, the write barrier needs to involve two separate inodes.

Re: [sqlite] Is it possible to get the amalgation as individual files

On Fri, Sep 21, 2012 at 2:55 PM, Jeff Archer wrote: > Igor Tandetnik Fri Sep 21 15:26:16 EDT 2012 >> ..."Canonical Source Code" section > > I knew about this but unfortunately I am Windows only. So the "Note that a > unix-like development environment, including a

Re: [sqlite] How to build sqlite4? Which branches are expected to build?

Also, what is the status of SQLite4? I know it's in development, but I'm wondering how far along it is. I'm guessing from the errors I saw that some of the extensions are not ready, for example. But looking at vdbe.c (particularly the comment block for OP_OpenRead) and explain output it seems

Re: [sqlite] How to build sqlite4? Which branches are expected to build?

On Wed, Aug 8, 2012 at 10:37 AM, Stephan Beal wrote: > The current trunk builds for me as-is with the GNUmakefile.linux: > > make compiler=gcc > make compiler=clang > make compiler=tcc > > all work fine here (and tcc is FAST!) That works, though it fails with tcc: tcc -g

Re: [sqlite] How to build sqlite4? Which branches are expected to build?

On Wed, Aug 8, 2012 at 5:27 AM, Richard Hipp wrote: > Copy Makefile.linux-gcc into Makefile. Edit to suite your system. Type > "make" And what branches are expected to build? Last night I had errors building trunk (FTS3 referred to an undefined sqlite4_blob type), and when I

[sqlite] How to build sqlite4? Which branches are expected to build?

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

Re: [sqlite] Windows I/O (was: Initial read speed greater than subsequent)

On Wed, Aug 1, 2012 at 2:43 PM, Black, Michael (IS) wrote: > You may be interested in this article: > http://www.drdobbs.com/parallel/multithreaded-file-io/220300055?pgno=2 > > Mutli-threaded reading of multiple files (which is basically what you're > talking about by

Re: [sqlite] C++ - Evaluating a statement

On Thu, Jul 26, 2012 at 4:32 PM, Arbol One wrote: > Reading the documentation, it says that 'sqlite3_step(sqlite3_stmt*) > > ' must be called at least once to evaluate the statement. However, what does > it evaluate? The prepared statement. Prepared statements are really a

Re: [sqlite] MIN() for a timedelta?

On Thu, Jul 26, 2012 at 4:32 PM, C M wrote: > I could zero pad these strings myself, so that '9:00:00.00' > becomes '09:00:00.00', but that would break other uses of these > values in my code and was wondering if there were a way in SQlite to > "see" these values as

Re: [sqlite] SQLite4 key encoding -- Is it ever decoded?

On Tue, Jul 24, 2012 at 8:29 AM, Steven E. Harris wrote: > Richard Hipp writes: >> Many keys can be decoded, but for TEXT keys with application-defined >> collating sequences, the encoding is not reversible. Many applications >> will

Re: [sqlite] Solaris SQLITE_IOERR_DELETE while sqlite3_backup_step

On Wed, Jul 11, 2012 at 3:36 PM, Артем Зуйков wrote: > As I understand it could not set errno in unlink() called from this block: > > if( unlink(zPath)==(-1) && errno!=ENOENT ){ > return unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath); > } That's not setting errno.

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Wed, Jul 11, 2012 at 12:36 PM, Valentin Davydov wrote: > Individual IP addresses are nicely supported in the form of unsigned > integers, and prefixes/ranges - as contiguous ranges of such integers. > For example, to determine whether given IP address belongs to a

Re: [sqlite] SQLite Provenance

What the OP is interested in, most likely, is that SQLite3 is not from Cuba, Iran, North Korea, ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite4 nit/question

In the key encoding page you talk about encoding E as a varint, and in some cases -E as a varint, but the page on varint says they are unsigned. I assume that "-E" == ~E + 1 (i.e., two's complement of E), and that ~E == one's complement of E. Nico --

Re: [sqlite] Bug in sqlite3_step

On Wed, Jul 4, 2012 at 8:06 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Nico Williams <n...@cryptonector.com> wrote: >> SQLite3 also needs to know the identifiers of schema elements at >> statement prep time. It might be nice to have a variant of >> sqlite3_

Re: [sqlite] Bug in sqlite3_step

SQLite3 also needs to know the identifiers of schema elements at statement prep time. It might be nice to have a variant of sqlite3_prepare_v2() that takes a varargs list of parameters which must be identifiers, and then have a syntax for referring to identifier parameters as opposed to value

Re: [sqlite] Can I rely on this being ordered?

In SQL you cannot rely on the result set being in any order unless you use ORDER BY. This is true in SQLite3 as well. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Mon, Jul 2, 2012 at 1:14 PM, Simon Slavin wrote: > Your argument is for SQLite to allow users to implement their own affinities > (datatypes ?) must like SQLite3 allows users to implement collation > algorithms and functions. But I don't think you're going to get

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Mon, Jul 2, 2012 at 12:11 PM, Niall O'Reilly <niall.orei...@ucd.ie> wrote: > On 2 Jul 2012, at 17:52, Nico Williams wrote: >> So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81' >> and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32 >&

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81' and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32 encoded as x'0A025D8000' (that's 5 bytes). That is, IPv4 addresses would require one more byte than usual. I'm not sure that we can justify the extra complexity

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

Ah, if you encode any bit string as a BLOB such that it ends in 3 bits that encode the length of the string mod 8, and with 7 - length of string mod 8 preceding zero-valued bits then you get a result that should sort [lexicographically] correctly, no? So bit string would be a trivial extension of

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

The key is to come up with a bit string encoding in bytes that is suitable for use in table keys -- they have to sort correctly when sorted lexicographically. The encoding should be reasonably efficient; one byte per-bit, for example, would be too inefficient (though in a pinch much better than

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

On Mon, Jul 2, 2012 at 4:29 AM, Niall O'Reilly wrote: > > On 29 Jun 2012, at 23:58, Richard Hipp wrote: > >> But you know: How often do people use BLOBs as keys? What other SQL >> engines other than SQLite even allow BLOBs as keys? Are we trying to >> optimize something

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

OK, I give :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

On Fri, Jun 29, 2012 at 5:24 PM, Richard Hipp wrote: > varint+value does not sort BLOBs in lexicographical order. > > Not having a distinct terminator for the BLOB means that two BLOBs where > one is a prefix of the other might not compare correctly. Would 31-bit encoding help?

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

On Fri, Jun 29, 2012 at 4:39 PM, Cory Nelson wrote: > On Fri, Jun 29, 2012 at 2:48 PM, Richard Hipp wrote: > What is the rationale for the 7-bit BINARY encoding? The performance impact > will surely outweigh any convenience of being able to treat blobs as >

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

On Fri, Jun 29, 2012 at 2:48 PM, Richard Hipp <d...@sqlite.org> wrote: > On Fri, Jun 29, 2012 at 3:40 PM, Nico Williams <n...@cryptonector.com>wrote: >> So, if I understand section 3.2 of the SQLite4 design page then it >> will often be the case that lookup keys wil

[sqlite] Consequences of lexicographic sorting of keys in SQLite4?

So, if I understand section 3.2 of the SQLite4 design page then it will often be the case that lookup keys will not be stored in an order that will be useful for optimizing common ORDER BY expressions. Is this correct? If so, is this worth the trade-off for the single key/value storage

Re: [sqlite] symbol collisions between sqlite{3,4}.h

On Fri, Jun 29, 2012 at 12:30 PM, Stephan Beal <sgb...@googlemail.com> wrote: > On Fri, Jun 29, 2012 at 7:24 PM, Nico Williams <n...@cryptonector.com>wrote: > >> I'd be more concerned about linker symbol colliosions than about C >> pre-processor symbol collisions.

Re: [sqlite] symbol collisions between sqlite{3,4}.h

On Fri, Jun 29, 2012 at 9:27 AM, Stephan Beal wrote: > On Fri, Jun 29, 2012 at 4:25 PM, Richard Hipp wrote: > >> Bummer.  These are going to be hard to fix.  :-( >> > > i guessed that would be the case for some of the long-standing symbols like >

Re: [sqlite] how to build sqlite4 (four)?

On Thu, Jun 28, 2012 at 4:32 PM, Simon Slavin wrote: > I think SQLite4 will need to support AUTOINCREMENT for INTEGERs because so > many SQL users will assume that it's supported. Sure. At least for UNIQUE and PRIMARY KEY fields it's possible with reasonable semantics.

Re: [sqlite] how to build sqlite4 (four)?

On Thu, Jun 28, 2012 at 2:48 PM, Richard Hipp wrote: > Note that SQLite4 does not have a rowid (at last not always) so the whole > concept of autoincrement will need to be revisited.  I don't think the old > SQLite3 way of doing autoincrement makes sense any more.  Not good sense

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Thu, May 31, 2012 at 12:11 PM, Marc L. Allen wrote: > True, but an optimizer could only intelligently exclude OUTER JOINS in most > cases. Right, and LEFT OUTER JOINs at that (since SQLite3 doesn't support RIGHT joins). > For instance, if I have a convenience

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman wrote: >> Actually, anytime you have VIEWs that join table sources you >> can benefit from this optimization. > > No, there is only a benefit if the optimization actually finds a table that > can be removed from the join. When

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Tue, May 29, 2012 at 7:33 PM, Igor Tandetnik wrote: > On 5/29/2012 8:21 PM, Charles Samuels wrote: >> Is this a planned feature? > > > Well, it's not a feature, it's a lack thereof. The query planner is not > specifically looking for this particular optimization

Re: [sqlite] Improving performance of GROUP BY

On Fri, May 25, 2012 at 1:38 PM, Udi Karni wrote: > I am running the following query - > > CREATE TABLE XYZ AS > SELECT ID, MIN (DATE) > FROM SOURCE-TABLE > WHERE CRITERIA > GROUP BY ID ; > > SOURCE-TABLE has 600 million rows, 2 million meet the WHERE criteria, and > get grouped

Re: [sqlite] Details on New Features

On Fri, May 4, 2012 at 4:04 PM, Richard Hipp wrote: > Correction:  The one that it encounters first, since subsequent rows of the > same value will not trigger a new copy of values into the output registers, > since only a new min/max does that. But surely that's

Re: [sqlite] Details on New Features

On Fri, May 4, 2012 at 9:20 AM, Richard Hipp wrote: >>         Queries of the form: "SELECT max(x), y FROM table" returns the >> value of y on the same row that contains the maximum x value. >> >> Is that standard SQL behavior?  I'd have expected that to return one row >> for

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

On Thu, Apr 19, 2012 at 3:51 PM, Black, Michael (IS) wrote: > You are correct that non-zero will be treated as true. > > That's more an accident then anything else as everybody is checking "!= 0" > for true. No, C treats any non-zero value as true, as in "if (condition)

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 2:45 PM, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams <n...@cryptonector.com>wrote: >> Note that you'll lose any fractional second information when you do >> this.  On the other hand, fractional secon

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones wrote: > I think I'll spend the time going back and storing the dates as integer > time (since the epoch) as Nico suggested and just use strftime to convert > them as and when required. Note that you'll lose any fractional

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 10:45 AM, Mark Jones wrote: > Afternoon all, > > I have the following schema: > > CREATE TABLE day > ("id" INTEGER PRIMARY KEY, > "timestamp" DATETIME, > "value" REAL); > > And the following sample data: > > INSERT INTO day VALUES (NULL,

Re: [sqlite] auto-incrementing integer in composite primary key

On Mon, Apr 16, 2012 at 8:00 PM, Simon Slavin wrote: > On 17 Apr 2012, at 12:33am, Petite Abeille wrote: >> On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote: >>> Now, consider what it takes if you're logging value changes instead of >>> commands

Re: [sqlite] auto-incrementing integer in composite primary key

On Mon, Apr 16, 2012 at 5:04 PM, Simon Slavin wrote: > On 16 Apr 2012, at 10:31pm, Peter Aronson wrote: >> You might want to look at the book Temporal Data and the Relational Model by >> Date, Darwin and Lorentzos, which goes into the subject in fairly

Re: [sqlite] auto-incrementing integer in composite primary key

On Mon, Apr 16, 2012 at 3:30 PM, Petite Abeille wrote: > > On Apr 16, 2012, at 9:09 PM, Kit wrote: > >> SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id >>      WHERE doc.id=id_xx AND created_on<=time_xx >>      ORDER BY created_on DESC LIMIT 1; > > - how

Re: [sqlite] auto-incrementing integer in composite primary key

On Mon, Apr 16, 2012 at 12:58 PM, Puneet Kishor wrote: > I am experimenting with a home-grown versioning system where every > "significant" modification to row would be performed on a copy of the row, > the original being preserved. So, if I have There are several ways to

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Tue, Apr 3, 2012 at 8:27 PM, Webdude wrote: > But if data was added exactly in the same way/order shouldn't the counters > all count to the same end result if the process was repeated at a later time > on a another machine? Well, why not... try it? :)

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Mon, Apr 2, 2012 at 11:39 PM, Webdude wrote: > I'm sure everyone thinks I'm mad, but I still haven't seen proof of "Can't > be done". The question is not "can this be done" but "should it be done this way". Relying on a sequence of SQL statements yielding exactly the

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Mon, Apr 2, 2012 at 8:37 PM, Webdude wrote: > It's not important that the 2 db files are exactly the same all the time > that people are editing them, but only when they 'finalise' a 'package'. > So what if some code in the 'packaging' process performed a sequence of >

Re: [sqlite] Hashing 2 SQLite db files with the same data

Also, if you were to use the running XOR of hashes method you'd also have to not make use of auto-allocated row IDs or any INTEGER PRIMARY KEYs, or AUTOINCREMENTed columns, or to not include any of those in the hashes, which probably also means not using any of those in FOREIGN KEYs. That's...

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Mon, Apr 2, 2012 at 6:27 PM, Webdude wrote: > I am building a file comparison tool that is free as in beer and speech. > The program allows people to put certain things in an SQLite database file, > then an MD5 or SHA hash is run on the resulting file for identity of

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

On Mon, Apr 2, 2012 at 5:46 PM, Simon Slavin wrote: > Replace part of that routine with something which specifies the locale rather > than fetching it from OS settings.  And store the locale used with the index, > as a COLLATE setting.  Thus leaving it up to whoever writes

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp wrote: > On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin wrote: >> I think ... a higher priority than that would be handling Unicode >> correctly.  And having Unicode support would be useful in writing the code >>

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

On Mon, Apr 2, 2012 at 4:24 PM, Simon Slavin wrote: > On 2 Apr 2012, at 9:58pm, Alexey Pechnikov wrote: >> Description: Unicode string library for C >> The 'libunistring' library implements Unicode strings (in the UTF-8, >> UTF-16, and UTF-32

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

On Mon, Apr 2, 2012 at 3:58 PM, Alexey Pechnikov wrote: > And see too: > > Homepage: http://www.gnu.org/software/libunistring/ Thanks! That looks like a dream come true (even if LGPL). It's much more complete than the OpenSolaris u8_textprep stuff, which would be

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp wrote: > On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin wrote: >> I think ... a higher priority than that would be handling Unicode >> correctly.  And having Unicode support would be useful in writing the code >>

Re: [sqlite] NaN in, 0.0 out?

On Mon, Mar 26, 2012 at 9:42 PM, Jay A. Kreibich wrote: > On Tue, Mar 27, 2012 at 03:30:03AM +0100, Simon Slavin scratched on the wall: >  Not do drag things out, but how would you "handle" a NaN?  If someone >  writes a signaling-NaN into the database, the DB will start to throw

Re: [sqlite] Core Data

On Fri, Mar 23, 2012 at 11:26 AM, Kristof Van Landschoot wrote: > Not sure if this question belongs here, let me know if it doesn't. It > can also be answered here >

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On Mon, Mar 19, 2012 at 10:02 AM, TAUZIN Mathieu wrote: > Thanks, > > This syntax works but it is not documented... it looks like a short hand for > a subquery, interesting !. Join sources are like sub-queries. Look at the syntax. A sub-select specified in the join-source

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

Use one connection. Attach the other DBs to this one connection, then use INSERT OR REPLACE INTO table SELECT ...;. See the recent thread about INSERT OR REPLACE. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] INSERT OR REPLACE

On Mon, Mar 12, 2012 at 9:02 AM, Christian Smith wrote: > On Fri, Mar 09, 2012 at 09:49:22AM +1100, BareFeetWare wrote: >> I suggest only using "insert or replace" if you genuinely want to delete and >> replace with a new row. This is generally not hat you want

Re: [sqlite] INSERT OR REPLACE

On Fri, Mar 9, 2012 at 6:05 AM, Ryan Johnson wrote: > Don't forget stored procedures. I didn't need to pollute the language with procedural extensions in order to make my case, which is why I didn't mention stored procedures :) I figured using stored procs in my

Re: [sqlite] INSERT OR REPLACE

On Thu, Mar 8, 2012 at 4:37 PM, Simon Slavin wrote: > That's why you don't make a DBMS (SQL) do the job of a programming language.   > Use your programming language to to retrieve the values you need to make your calculations.  Then use your programming language to figure

Re: [sqlite] INSERT OR REPLACE

On Thu, Mar 8, 2012 at 1:52 PM, Pavel Ivanov wrote: >> So, please.. add INSERT OR UPDATE.  I'd ask that INSERT OR REPLACE be >> modified to do an UPDATE to the record if the UNIQUE Constraint is violated >> instead of the delete, but that would break existing applications.

Re: [sqlite] How efficient is this?

On Thu, Mar 8, 2012 at 12:00 PM, Marc L. Allen wrote: >> This particular query need not be very inefficient if the pages needed >> to do the second sub-query are left in the cache from the first sub- >> query... > > No.  That's true.  But this is just a simple

Re: [sqlite] How efficient is this?

On Thu, Mar 8, 2012 at 11:56 AM, Nico Williams <n...@cryptonector.com> wrote: > On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen > <mlal...@outsitenetworks.com> wrote: >> But this is extremely inefficient as well.  Since each record is, in fact, >> an update, you'r

Re: [sqlite] How efficient is this?

On Thu, Mar 8, 2012 at 9:53 AM, Marc L. Allen wrote: > I'm trying to do a multi-column update, adding values from another table.   > Something like: > > UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id), >                              b += (SELECT b FROM t2

Re: [sqlite] How efficient is this?

On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen wrote: > But this is extremely inefficient as well.  Since each record is, in fact, an > update, you're actually performing a delete/insert for each record, > activating any recursive triggers you have as well.  On top

Re: [sqlite] How efficient is this?

On Thu, Mar 8, 2012 at 11:22 AM, Nico Williams <n...@cryptonector.com> wrote: > I use this all the time as it's the only way in SQLite3 to do UPDATEs > with JOINs.  I use it with an INTEGER PRIMARY KEY column.  SQLite3 > tables always have a rowid somehow that's suitable for

Re: [sqlite] How efficient is this?

On Thu, Mar 8, 2012 at 10:43 AM, Igor Tandetnik wrote: > Unfortunately, there are no good alternatives for this statement. I wish > SQLite supported UPDATE ... FROM syntax (non-standard, used by some other > DBMS). Here's one not-so-good alternative: > > insert or replace

Re: [sqlite] Efficient random sampling in a large table using builtinfunctions.

On Thu, Mar 8, 2012 at 8:02 AM, Igor Tandetnik wrote: > You can implement this algorithm in your application's code, if you are so > inclined. Just prepare a query lile > > select * from the_table where rowid=?; > > then run it N times, binding different random numbers to

Re: [sqlite] About new ticket "Per page/region checksums"

On Tue, Feb 7, 2012 at 11:12 AM, Michael Stephenson wrote: > It's almost trivial to add per-page checksums at the page level.  Here are > basic steps: This is not enough, though it's a lot better than nothing. You need to be able to store the checksums where the page

Re: [sqlite] About new ticket "Per page/region checksums"

On Mon, Feb 6, 2012 at 4:38 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 6 Feb 2012, at 9:49pm, Nico Williams wrote: >> Encryption is not enough.  You really need block pointers to carry the >> block checksum/hash/MAC/integrity tag. > > File systems (FAT, NTF

Re: [sqlite] About new ticket "Per page/region checksums"

On Mon, Feb 6, 2012 at 2:27 PM, Roger Binns <rog...@rogerbinns.com> wrote: > On 06/02/12 11:35, Nico Williams wrote: >> Indeed, but if you'd do integrity protection at the application layer >> then surely you'd have have a backup/restore strategy to deal with >> lower

Re: [sqlite] ALTER TABLE

I'm pretty sure that the user_version pragma is considered stable. That said, if your application is in full control of the DB then you could just check the exact create statements logged in sqlite_master (this is probably less stable, ironically enough).

Re: [sqlite] About new ticket "Per page/region checksums"

On Mon, Feb 6, 2012 at 1:20 PM, Roger Binns <rog...@rogerbinns.com> 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 allow

Re: [sqlite] ALTER TABLE

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

Re: [sqlite] About new ticket "Per page/region checksums"

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

<    1   2   3   4   5   >