[sqlite] sqlite3 .mode columns with UTF-8 breaks on tmux

sciinema to show it off: https://asciinema.org/a/47266 Additional information: desktop:~$ tmux -V tmux 2.2 desktop:~$ uname -a Linux desktop 4.2.0-1-amd64 #1 SMP Debian 4.2.1-1 (2015-09-25) x86_64 GNU/Linux Debian Sid Best regards, Mikael Roos ___ sqlite-u

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

2016-05-15 23:46 GMT+07:00 Dominique Devienne : > On Sunday, May 15, 2016, Mikael wrote: > > > > Would there be any facility whereby after each transaction I do on a > > database or table, I could somehow make a snapshot so that at any future > > point in time,

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

2016-05-15 22:23 GMT+07:00 Mikael : .. > Aha. > > Do you see any way that I could implement *one* layer of branches atop of > this? > > Say 1000 or 10^30 of them. > > Ah, make the selects for the timestamp OR the branchid.

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

2016-05-15 22:14 GMT+07:00 Simon Slavin : > > On 15 May 2016, at 4:02pm, Mikael wrote: > > > Simon, yes using only INSERT and DELETE in total would be fine with me > (and > > perhaps BEGIN and COMMIT if it's OK). > > > > What are you thinking about? &g

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

2016-05-15 21:56 GMT+07:00 Simon Slavin : > > On 15 May 2016, at 3:52pm, Mikael wrote: > > > Would there be any facility whereby after each transaction I do on a > > database or table, I could somehow make a snapshot so that at any future > > point in time, I could ea

[sqlite] Clarify in docs that PRAGMA integrity_check also checks that index content matches table content?

Hi, The documentation for "PRAGMA quick_check" ( http://www.sqlite.org/pragma.html#pragma_quick_check) points out that "PRAGMA integrity_check" will match index content with table content. http://www.sqlite.org/pragma.html#pragma_integrity_check , which is the actual documentation for that

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

Hi! Would there be any facility whereby after each transaction I do on a database or table, I could somehow make a snapshot so that at any future point in time, I could easily do a SELECT to a given version/snaphot? Any solution based purely on SQL would be extremely expensive I guess (e.g.

[sqlite] Make mmap_size dynamic?

On Tuesday, 3 May 2016, Richard Hipp > wrote: > On 5/2/16, Mikael wrote: > > > > If I just force it on (by hacking the build script), as long as mmap_size > > always is 2^63, will Sqlite access the file via memory accesses only, and > > never using fread/fwrite

[sqlite] Make mmap_size dynamic?

: > The existing mmap functionality only maps the actual blocks associated with > the file. So if your file is 16kb and your mmap_size is 1GB, only 16kb is > used. Unless you add data to the file, then the mmap area grows, > obviously. > > -scott > > > On Mon, May 2, 2016 at 2

[sqlite] Make mmap_size dynamic?

ache, such as OpenBSD, where memory mapping is disabled altogether for this reason currently. Looking forward to your response, Thanks, Mikael

[sqlite] Fwd: Usenix paper: Korean researchers invent sqlite WALDIO mode to circumvent redundant journaling by EXT4 on eMMC

aluable so really having both is only good :) ) 2015-07-14 15:59 GMT+02:00 Simon Slavin : > > On 14 Jul 2015, at 2:53pm, Mikael wrote: > > > This is awesome as it's a quite nice data integrity guarantee: this > > guarantees that data is in the right place (so broken sector m

[sqlite] Fwd: Usenix paper: Korean researchers invent sqlite WALDIO mode to circumvent redundant journaling by EXT4 on eMMC

The ZFS filesystem Fletcher-checksums all data - perhaps together with tagging each "page" with its "page number", duno! This is awesome as it's a quite nice data integrity guarantee: this guarantees that data is in the right place (so broken sector mapping tables won't break anything) and is

[sqlite] Ensuring the WAL doesn't grow too big

Hi, Some of this can be read out of the manual but addressing for complete clarity, is slightly related to the fragmentation conversation that just was (+ I saw a GB-size WAL file the other day, perhaps because of someone playing with the wal_autocheckpoint pragma setting): a) How do you ensure

[sqlite] Slow real world performance - Any suggestions please (warning long)

Wait, so if connection 1 is doing VACUUM and connection 2 attempts INSERT/UPDATE/DELETE and connection 3 attempts SELECT all concurrently, what happens and how ACID will the DB be? 2015-07-03 20:07 GMT+07:00 Clemens Ladisch : > Simon Slavin wrote: > > On 3 Jul 2015, at 1:39pm, Rob Willett >

[sqlite] Slow real world performance - Any suggestions please (warning long)

2015-07-03 5:51 GMT+07:00 Simon Slavin : > > On 2 Jul 2015, at 11:16pm, Rob Willett > wrote: > > > We process this XML feed and pull out chunks of the XML, process it and > update our database with it. This database is currently 16GB in size. Our > concern is the time taken to process each file

[sqlite] Worstcase consequence of exotic storage failure, and how guard against it?

2015-06-25 21:06 GMT+07:00 Simon Slavin : > > On 25 Jun 2015, at 11:49am, Mikael wrote: > > > Harddrives can fail in the most remarkable, surprising ways imaginable, > as > > for instance reflected on > > > https://www.usenix.org/legacy/events/fast08

[sqlite] Worstcase consequence of exotic storage failure, and how guard against it?

perhaps? Thanks, Mikael

[sqlite] How mature/stable is SQLite 4 now? ETA?

This sounds like it means we'll have it 2.5-5 years then.. so 2018 maybe, Sounds about correct? :) 2015-05-23 23:06 GMT+05:30 Stephen Chrzanowski : > SQLite4 is a dev "toy". It isn't going to be released any time soon. > > On Sat, May 23, 2015 at 6:09 AM, Mikael wrote:

[sqlite] How mature/stable is SQLite 4 now? ETA?

SQLite4 looks neat! Last code commit was in September, is this because it's so stable or because other priorities took over? (https://sqlite.org/src4/tree?ci=trunk) Thanks! Mikael

[sqlite] Enabling mmap on OpenBSD: Anything really in the way, and how do?

BSD is still a very valid usecase and as per above I guess the tweaks to make it go are very simple, so would very much appreciate your thoughts and guidance on how to make this work!) Thank you very much! Mikael 2015-05-22 14:18 GMT+05:30 Mikael : > What do you say? > > Also I'm curious

[sqlite] (msync()+) If mmap_size is big enough, read() will never be needed, so what about enabling mmap on platforms that "lack of a unified buffer cache"?

GMT+05:30 Mikael : > > > 2015-05-22 13:28 GMT+05:30 Dan Kennedy : > >> On 05/22/2015 02:37 PM, Mikael wrote: >> >>> Hi, >>> >>> If mmap_size is big enough, read() will never be needed, so what about >>> enabling mmap on platforms that

[sqlite] (msync()+) If mmap_size is big enough, read() will never be needed, so what about enabling mmap on platforms that "lack of a unified buffer cache"?

2015-05-22 13:28 GMT+05:30 Dan Kennedy : > On 05/22/2015 02:37 PM, Mikael wrote: > >> Hi, >> >> If mmap_size is big enough, read() will never be needed, so what about >> enabling mmap on platforms that "lack of a unified buffer cache"? >> >> I

[sqlite] (msync()+) If mmap_size is big enough, read() will never be needed, so what about enabling mmap on platforms that "lack of a unified buffer cache"?

Hi, If mmap_size is big enough, read() will never be needed, so what about enabling mmap on platforms that "lack of a unified buffer cache"? I mean, on a 64bit platform, setting mmap_size to say 1TB should be fine - So what about enabling mmap_size on platforms that "lack of a unified buffer

[sqlite] How initialize a database file from unix shell without landing in SQLite command prompt?

I have a file DBDEFINITION.TXT that I want to use to initialize [execute on] the database DB.SQLITE. How do I do something like $ sqlite3 -exec DBDEFINITION.TXT DB.SQLITE $ ? All methods I tried end me up in the SQLite prompt, and that is what I don't want. Like, doing sqlite3 -init

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On the embedded unit write caching disabled? 2014-10-14 23:24 GMT+02:00 Pontus Bergsten : > Hi, > I have the following problem setup: > We use sqlite3 in an embedded signal logger application. The "Main" > in-memory signal signal database consists of some minor signal

Re: [sqlite] About "speed"

big stone, Can you please compile a chart (in text format is ok) that puts your numbers from your last mail in relation with the numbers from your email prior to that, for everyone to get perfectly clear about how the optimizations you applied now do improve beyond the numbers published in the

Re: [sqlite] About "speed"

big stone, What are the same results using RTree? (Also feel free to add -O2.) ? Thanks 2014-03-02 17:25 GMT+01:00 big stone : > Hi again, > > This is what I mean : we should have an updated "speed" page where we could > objectively measure. > > In the mean time, I

Re: [sqlite] to encrypt sqlite db

Wait, can you clarify what you mean by SEE here, and also which library you mean for BW? Thanks 2013/8/31 Etienne > > Those first few bytes are just as well known after they have been run > > through zlib or libbz2 or whatever compression library you are using. >

[sqlite] Suitable datatype for big integers: Introduce an EXACT datatype? (TEXT not optimized for <=64bit integers, and NUMERIC cuts the integer at the 15:th digit :( )

ssible. Introduce a datatype EXACT, that's the same as NUMERIC just that the INTEGER/REAL conversion is done only if *all* digits are preserved? Thanks! Mikael http://www.sqlite.org/datatype3.html : A column with NUMERIC affinity may contain values using all five storage > classes. When

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

into ot (v) values (2),(4),(6),(8),(14),(16),(20); select id, a, b, 1.0 * a / b as c from ( select id, (select sum(v) from ot as ot1 where ot1.v > categories.id) as a, (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b from categories ) order by c; Mikael 2013/7/21 Igor Tandet

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Igor, C is not a present column, but is the result of an expression involving A and B (namely, the expression A / B). Can you please take this into consideration and update the query you proposed? :)) Thanks, Mikael 2013/7/20 Igor Tandetnik <i...@tandetnik.org> > On 7/20/201

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

way to do it whatsoever? Thanks :)) Mikael 2013/7/20 Luuk <luu...@gmail.com> > > SELECT > id, > othertable.thing as a, > othertable2.thing as b, > othertable.thing/othertable2.**thing as c > FROM categories > INNER jOIN othertable ON othertable.something

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

ever to do this? Thanks, Mikael 2013/7/20 Clemens Ladisch <clem...@ladisch.de> > Mikael wrote: > > Is anything like "select 7 as a, 8 as b, a / b as c;" possible? > > Not directy, but you could use a subquery: > > SELECT *, a / b AS c FROM (S

[sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

. Possibly even giving that value a name so I can sort based on it. Is this doable whatsoever? Thanks, Mikael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?

Hi Günter, Great, yes that makes sense! So that solves it. I was thinking about this and thought there must be some way to do this already; thank you so much for pointing this out. This would also open up for picking up several inserted id:s from one query which is nice. Thanks, Mikael 2013

Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?

*every* query however small in a particular binding? If you see any other way to solve this with good performance, please feel free to share, I'm just looking for a general way of getting if the last query inserted an ID and if so which, it can be implemented any way as long as it perf

Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?

2013/5/6 Mikael <mikael.tr...@gmail.com> > Dear Simon, Günter and list, > > The issue goes like this: > > Currently in the absence of a sqlite3_reset_last_insert_rowid(), the > closest you can do to figure out what ID was actually inserted by the most > recent SQLite

Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?

_total_changes > If (Before < After) > ... > (unlock mutex) > > Also note that INSERT OR IGNORE does not change the last inserted rowid. > > I also suspect that INSERT OR REPLACE may very well change the last > inserted rowid (without tallying sqlite3_changes!!! See documentati

[sqlite] How do sqlite3_reset_last_insert_rowid() or functional equivalent?

0 if no insert was done. (mutex for sqlite3 unlock) [starting here sqlite3_last_insert_rowid(sqlite3)'s return value is undefined] Thanks, Mikael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_changes and sqlite3_/extended_/errcode would benefit greatly of being available in stmt-specific variants!?

Dear list, Do you have any feedback on this? I was pretty surprised when I ran into it myself actually ... looks like the kind of thing where lots of complexity for users could be saved by spending some very few CPU cycles in SQLite. Thanks 2012/11/21 Mikael > Dear Dr. Hipp, > >

Re: [sqlite] What is the definition/use/point with the xShm* procedures in sqlite3_io_methods, and what's the benefit of using WAL with those rather than the global file locking option?

by several nodes on a networked filesystem simultaneously. I'd like to be able to understand if this is correct or not - need to understand what the xShm* and foundation for the mapping as per above is for this though. Thanks and kind regards, Mikael 2012/1/29 Roger Binns <rog...@rogerbinns.

[sqlite] What is the definition/use/point with the xShm* procedures in sqlite3_io_methods, and what's the benefit of using WAL with those rather than the global file locking option?

of the file-global locking mode for a WAL DB? Is the main benefit more fine-grained access to the wal/db files' contents and thus higher parallell processing across sqlite instances? Thanks and kind regards, Mikael ___ sqlite-users mailing list sqlite

Re: [sqlite] WAL checkpointing

2011/9/29 Simon Slavin > > On 29 Sep 2011, at 10:38am, Dan Kennedy wrote: > > > On 09/29/2011 03:17 PM, Jaco Breitenbach wrote: > >> In the WAL documentation (http://www.sqlite.org/wal.html) it is hinted > that > >> the checkpoint operation, which is normally in the same

Re: [sqlite] Speed comparison

I believe there is, it would be highly interesting. Please keep me and others posted, and let me and all others know the results of the test/-s! (Disclaimer: I'm in no way a part of the SQLite developer team, solely a ml member.) Kind regards, Mikael 2011/6/8 Cecil Westerhof <cldwes

Re: [sqlite] What stack size is required for SQLite to spin?

2011/4/22 Richard Hipp <d...@sqlite.org> > I do not believe any of these things have any impact on stack depth. But > you should probably double-check by running the experiment. Ok. > Please let us > know if you find me wrong. Yes. Thank you a lot! Kind regards and happ

Re: [sqlite] What stack size is required for SQLite to spin?

to several megabytes, does not imply use of more stack space? Many thanks, Mikael 2011/4/22 Richard Hipp <d...@sqlite.org> The SQL parser and code generator are recursive. So given arbitrarily > complex SQL, one can use arbitrarily much stack space. If you absolutely must limit your s

Re: [sqlite] What stack size is required for SQLite to spin?

any of them at the end of the stack are still there. The key criterion is to somehow prove that it'll always be at least one left. Many thanks, Mikael 2011/3/29 Richard Hipp <d...@sqlite.org> > On Mon, Mar 28, 2011 at 5:25 AM, Mikael <mikael@gmail.com> wrote: > > &

[sqlite] What foolproof strategies for database file non-corruption are there, as regards app and OS crashes (not hardware) when in WAL mode?

this the best way - what strategies/utilities/methods/SQLite parameters and calls are needed to do this. While I didn't find a clear answer to this in the list archives, I'm sure this is a key question for many SQLite users. Many thanks, Mikael ___ sqlite-users

[sqlite] CPU time consumption from sqlite3_* call to VFS (or Application Defined Page Cache) procedure call and back

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

[sqlite] What stack size is required for SQLite to spin?

10% for safety. Looking forward to your response, Kind regards, Mikael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users