Between updates, automatic maintenance, registry churn, event logs, and
background "optimisations" I reckon windows could give 400G/day a run for
its money :P
-Rowan
On 19 June 2018 at 12:37, Keith Medcalf wrote:
>
> The new "consumer" SSDs from Samsung carry a 1200 TBW/8 year warranty on a
> 4
On 2 July 2018 at 23:32, Charles Samuels wrote:
> I have found that when my process has a lot of threads each of which opens
> a
> DIFFERENT database, they each block on eachother while opening each
> database.
>
> This is at least on conflict with the documentation, as the documentation
> sugg
On 26 July 2018 at 05:56, Rune Torgersen wrote:
> The databases have been opened with two connections (one for reads, one
> for writes), and use the following options:
> sqlite3_busy_timeout(mDbConn, 500);
> sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0);
>
Surely thi
On 28 July 2018 at 05:41, Rune Torgersen wrote:
> > Rowan Worth Thu, 26 Jul 2018 22:02:50 -0700
> >
> > On 26 July 2018 at 05:56, Rune Torgersen wrote:
> >
> > > The databases have been opened with two connections (one for reads, one
> > >
On 30 July 2018 at 17:25, Dominique Devienne wrote:
> On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin
> wrote:
>
> > On 30 Jul 2018, at 9:32am, Eric Grange wrote:
> >
> > > As these are cryptographic GUIDs, the first few bytes of a values are
> in
> > > practice unique, so in theory I can index j
On 30 July 2018 at 17:53, Eric Grange wrote:
> @Rowan Worth
> > What if you could create a "lite" index, which stores just the rowids in
> a particular order and
> > refers back to the table for the rest of the column data?
>
> As I have millions of rows, and d
On 30 July 2018 at 18:10, Eric Grange wrote:
> @Rowan Worth
> > Doesn't that problem already exist with the current index? Except worse
> > because it's storing the cryptographic hash *and* the rowid.
>
> No, because SQLite is using a B-Tree (and with cryptographi
On 6 August 2018 at 22:20, R Smith wrote:
> Think of paragraphs in English as large records delimited by 2 or more
> Line-break characters (#10+#13 or perhaps only #10 if on a *nix platform)
> between texts.
>
> Each paragraph record could be comprised of one or more sentences (in
> English) as r
On 7 August 2018 at 21:25, David Raymond wrote:
> Correct.
>
> In rollback journal mode when one connection says "I'm ready to write now"
> it blocks any new transactions from being made, but it can't do anything
> about existing read transactions. It has to wait for them to finish their
> reads
On 12 August 2018 at 00:51, Lars Frederiksen wrote:
> Is this an error of my windows 10 cmd prompt or is it a general problem
> that
> the sqlite3 console is not able to show unicode in the cmd-window.??
>
> No problem writing to the database and show (greek) unicode characters in
> the cmd promp
FWIW in the building I work in we have 20-30 users hitting around a dozen
SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs. The
number of corruptions I've seen in the last 5 years which nfs *might* be
responsible for is *very low*. The number of corruptions where nfs was
*defini
On 15 August 2018 at 13:57, Wout Mertens wrote:
> For the interested:
>
> In NixOS (http://nixos.org), a very interesting Linux distribution, the
> entire OS (libraries, binaries, shared files, up to and including
> configuration files) is composed out of "build products" that are addressed
> by
On 15 August 2018 at 14:12, Wout Mertens wrote:
> On Tue, Aug 14, 2018 at 6:28 PM Rowan Worth wrote:
>
> > FWIW in the building I work in we have 20-30 users hitting around a dozen
> > SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs.
>
>
> Mult
sqlite is pretty loose about types. The column definitions don't constrain
what is stored in the rows at all:
sqlite> CREATE TABLE a(c INTEGER);
sqlite> INSERT INTO a VALUES ("fourty-two");
sqlite> SELECT * FROM a;
fourty-two
So "UNSIGNED" seems kind of pointless as it's implies a further constra
>
> The above select shows the issues.
>
>
> On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth wrote:
> > sqlite is pretty loose about types. The column definitions don't
> constrain
> > what is stored in the rows at all:
> >
> > sqlite> CREATE TA
On 10 September 2018 at 22:28, Joshua Watt wrote:
> BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT;
> Normally, this query takes no more than 1-3 seconds to complete,
> however, on rare occasion this will take an order of magnitude more
> (20-30 seconds).
>
> pragma synchronous = norm
You can also filter out specific messages at the shell level:
sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the
rest with NULL' >&2)
But note that the >() syntax is not a POSIX sh feature, and will not work
in a script using a shebang of #!/bin/sh. You need to change it to
On Sat, 13 Oct 2018 at 00:21, Chris Locke wrote:
> > Database is locked
>
> Close your application. Is there a xxx-journal file in the same directory
> as the database? (where xxx is the name of the database)
> Try deleting this file.
>
For the record, "delete the journal file" is terrible advi
On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen wrote:
> I type info into some labeledits and by pressing "Append to DB" button
> this code is executed (fdqGoser2 is a FDQuery)
>
> fdqGloser2.Open;
> fdqGloser2.Append;
> fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
> ...
> fd
On Wed, 10 Oct 2018 at 05:18, Warren Young wrote:
> That event was the immediate spur to start this Fossil forum project, but
> if you search the archives, there are multiple threads. Here’s one from
> about a year ago:
>
>
> http://sqlite.1065341.n5.nabble.com/Many-ML-emails-going-to-GMail-s-SP
On Fri, 19 Oct 2018 at 19:52, Mantas Gridinas wrote:
> I found code of conduct in documentation and I was wondering if it were
> true. Checking the version history it appears to have been added on
> 2018-02-22.
>
> 23. Do not nurse a grudge.
::sigh::
DROP TABLE grudges;
I was amassing such a g
"The problem will affect you only if you have multiple reads/writes
happening at the same time."
ie. The problem will only manifest if the user is doing anything at all
with their computer? :P
Interesting analysis - thanks for sharing.
-Rowan
On Tue, 30 Oct 2018 at 10:13, Simon Slavin wrote:
On Sun, 16 Dec 2018 at 05:00, Pierre Tempel wrote:
> > “... programs which rely on [the O_CREAT and O_EXCL flags of
> > open(2) to work on filesystems accessed via NFS version 2] for
> > performing locking tasks will contain a race condition. The solution
> > for performing atomic file locking us
On Sat, 15 Dec 2018 at 15:10, Frank Millman wrote:
> On Dec 15, 2018, at 08.58, Jay Kreibich wrote:
>
> > > On Dec 15, 2018, at 12:49 AM, Frank Millman
> wrote:
> > >
> > > I know that floating point is not precise and not suitable for
> financial uses. Even so, I am curious about the following
There seems to be a few misconceptions in the article regarding the
difference between DEFERRED/IMMEDIATE/EXCLUSIVE, and the fine details of
how different lock states interact.
Specifically, your diagrams suggest that once a writer obtains a RESERVED
lock (as happens when an IMMEDIATE transaction
On Tue, 15 Jan 2019 at 02:54, Simon Slavin wrote:
> The "just-in-time" idea mentioned in your question doesn't work in real
> life, since constantly checking mutex status keeps one core completely
> busy, using lots of power and generating lots of heat.
>
Technically "just-in-time" could be impl
On Mon, 21 Jan 2019 at 15:46, wrote:
> For the moment, the solution that is working for me is to disable syncing
> with PRAGMA synchronous = OFF. This is acceptable in this particular
> application because a power failure or OS crash will necessitate restarting
> the data gathering process anywa
On Mon, 21 Jan 2019 at 07:21, Keith Medcalf wrote:
> In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction
> in progress blocks a WRITE transaction and a WRITE transaction in progress
> blocks all other attempts to commence a transaction of any type on any
> other connection.
>
On Tue, 22 Jan 2019 at 12:51, Rahul Jayaraman
wrote:
> > I think "single writer, multiple readers" is the simplest way to describe
> sqlite's approach to isolation
>
> I’m not sure if this summarization paints enough of a picture about how
> SQLite restricts interleaving of read & write operation
On Tue, 22 Jan 2019 at 17:24, Rahul Jayaraman
wrote:
> From an operational perspective, which describes algorithms used and
> implementation details. I think it’s useful to understand algorithms
> because different algorithms give rise to different `busy` scenarios, and
> having a better mental m
On Fri, 25 Jan 2019 at 13:21, Rahul Jayaraman
wrote:
> > 1. "In the above case, since all transactions are started with
> IMMEDIATE,” -- the diagram is actually using EXCLUSIVE transactions not
> IMMEDIATE
> > 2. "they behave as writers, and concurrent transactions are blocked" —
> this implies t
On Tue, 5 Feb 2019 at 16:06, Simon Slavin wrote:
> On 5 Feb 2019, at 8:00am, Gerlando Falauto
> wrote:
>
> > Thank you for your explanations guys. All this makes perfect sense.
> > I still can't find a solution to my problem though -- write a query that
> is guaranteed to return sorted results,
On Tue, 5 Feb 2019 at 22:46, Simon Slavin wrote:
> On 5 Feb 2019, at 8:59am, Rowan Worth wrote:
>
> > SELECT source1, source2, ts, value
> > FROM rolling
> > WHERE source1 = 'aaa'
> > AND ts > 1 AND ts < 1
> > ORDER BY source1, sourc
On Fri, 8 Feb 2019 at 20:03, Theodore Dubois wrote:
> I'd like to essentially commit changes to disk in the middle of the
> transaction, resulting in a transaction that is atomic with respect to
> other database connections but is two atomic transactions with respect to
> the filesystem.
>
"atom
Huh, fascinating stuff. I'm not an sqlite developer but I can shed light on
some of your questions.
On Tue, 12 Feb 2019 at 09:54, Edwin Török wrote:
> A very conservative interpretation of various fsync bugs in various OS
> kernels [2][5] would suggest that:
>
> #1. the list of known OS issues [
On Tue, 12 Feb 2019 at 15:07, Rowan Worth wrote:
> Huh, fascinating stuff. I'm not an sqlite developer but I can shed light
> on some of your questions.
>
> On Tue, 12 Feb 2019 at 09:54, Edwin Török wrote:
>
>> A very conservative interpretation of various fsync bugs i
On Fri, 15 Feb 2019 at 16:13, Wout Mertens wrote:
> sqlite> create index b on t(b) where b is not null;
> sqlite> explain query plan select b from t where b is not null;
> QUERY PLAN
> `--SCAN TABLE t USING COVERING INDEX b
> sqlite> explain query plan select b from t where (b is not null)=1;
> Q
On Sun, 24 Feb 2019 at 01:55, Tim Streater wrote:
> (sorry for the duplicate - vibrating finger).
>
> I have a hosted web site using the SQLite functions from PHP. The page
> where PHP is used was failing, and on investigation this is because an
> SQLite function called from within PHP is now ret
On Fri, 1 Mar 2019 at 18:26, Lloyd wrote:
> I have two database files. One in Rollback mode and the other in WAL mode.
> I am able to serialize, deserialize and prepare a SQL query against the
> rollback database. When I do the same against the WAL database file, the
> 'prepare' statement fails w
On Sun, 3 Mar 2019 at 20:53, Keith Medcalf wrote:
> My observation (on the current tip version 3.28.0) of Schrodingers
> Transactions is that if there is (for example) a transaction in progress
> and that is COMMIT or ROLLBACK, then the changes are either committed or
> rolled back and the explic
On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte
wrote:
> Hello Gert !
>
> I normally do this (be aware that if there is a power outage the
> database is screwed):
>
> ===
>
> PRAGMA synchronous = OFF;
> begin;
>
> --processing here
>
> commit;
> PRAGMA synchronous = ON;
>
You can probably le
Richard Hipp wrote (quoting from several emails):
> The problem is that Git now thinks that 9b888fcc is the HEAD of master
> and that the true continuation of master (check-in 4f35b3b7 and
> beyond) are disconnected check-ins
>
Because from the git perspective it _is_ still the HEAD -- there's be
On Mon, 29 Apr 2019 at 01:22, Lullaby Dayal
wrote:
>
> Considering all this, I have written a test application running on Linux
> with sqlite3 library in serialized mode. My test application has 200
> parallel threads in which 100 threads are executing SELECT * operation from
> a table and 100 ar
On Wed, 1 May 2019 at 19:30, Frank Kemmer wrote:
>
> https://github.com/xerial/sqlite-jdbc/blob/14839bae0ceedff805f9cda35f5e52db8c4eea88/src/main/java/org/sqlite/core/CoreResultSet.java#L86
>
> Here we see, that colsMeta == null results in throwing the seen exception.
>
> But how can colsMeta be
On Fri, 3 May 2019 at 16:03, Dominique Devienne wrote:
> On Mon, Apr 29, 2019 at 9:49 PM Russ Cox wrote:
>
> > On Mon, Apr 29, 2019 at 3:28 PM Richard Hipp wrote:
> > For what it's worth, it was not clear to me until just now that the
> article
> > existed to push back on a general "asserts con
On Tue, 7 May 2019 at 16:00, Eric Grange wrote:
> Is there are way to get more information about an SQL syntax error message
> ?
> For example on a largish SQL query I got the following error message
>
> near "on": syntax error
>
> but as the query is basically a long list of joins, this
On Sat, 18 May 2019 at 00:34, Tony Papadimitriou wrote:
> It’s quite often (for me, at least) the case I need to do something like
> this from the command line:
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
> copied from some other app’)
>
> The problem is the multi
On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera
wrote:
> Ok, I think it happens even before the casting. This should be,
> 3.2598, and yet, it's 3.26.
>
> sqlite> SELECT 0.005 + 3.2548;
> 3.26
>
Note that no arithmetic is required to see these symptoms:
sqlite> SELECT
On 21 July 2017 at 17:50, Tim Streater wrote:
>$sql = "UPDATE Movies SET name = '$newname' where id=$newid";
>
> Personally I don't like forcing PHP to scan strings so I tend to use
> concatentation, rewriting the last of these as:
>
>$sql = 'UPDATE Movies SET name = \'' . $newname . '\'
On 8 August 2017 at 18:32, x wrote:
> Why does it always turn out to be my own fault ☹
>
Welcome to programming ;)
It's very rare in practice to find an actual bug in a library or language,
especially one as widely deployed and tested as sqlite. Glad you're sorted,
I just wanted to have a quick
Jump to the byte offset specified by the "start of the cell content"
header, which comes just after the number of pages (ie. offset 0x0f90 in
your pasted example).
Cross reference the data at that offset against section "2.1 Record Format"
of the Database File Format page. By decoding the record h
On 14 August 2017 at 17:11, J Decker wrote:
> I monitored the process with ProcMon (I'm on windows 7 BTW)
>
> 12:55:34.7416316 AM node.exe 9012 WriteFile
> \Device\HarddiskVolume17\javascript\gun.db\gun.db-wal SUCCESS Offset:
> 188,416, Length: 20,480, I/O Flags: Non-cached, Paging I/O, Synchrono
On 21 August 2017 at 17:25, Clemens Ladisch wrote:
> sanhua.zh wrote:
> > I find that `PRAGMA table_info(tableName)` will not check the expired
> schema which is modified by other sqlite connections.
> >
> > Here is the sample code:
>
> That code is incomplete and buggy. (Heed the compiler warni
https://sqlite.org/src/artifact/0c6bc6f55191b690
(it was linked recently by Richard Hipp in another thread, to pre-empt
questions of "how did you find that" :) )
-Rowan
On 22 August 2017 at 10:22, Gelin Yan wrote:
> Hi All
>
>I noticed there is a tag called server-process-edition in the tim
On 24 August 2017 at 13:58, J Decker wrote:
> Even though the process will always be single threaded access even if there
> are multiple connections, it would be more helpful to have a
> locking_mode=NONE.
>
>
>
> PRAGMA locking_mode = EXCLUSIVE gives me good speed, at the cost of only 1
> conn
On 2 September 2017 at 22:19, Simon Slavin wrote:
>
> And please note that you never /need/ to do any kind of VACUUM in a
> production setting. The only situation where it helps is if your database
> is significantly shrinking and will not grow again soon, and that’s quite
> rare for a database.
On 4 September 2017 at 15:37, Bart Smissaert
wrote:
> Trying this out, so I edited the source and indeed now there is no crash.
> I get however a message:
>
> malformed JSON
>
> This is the dump of that testing table, dataset:
>
> doc
> {"field1": [{"value": "val1"}, {"value": "val2"}]
> {"field1
On 18 September 2017 at 16:52, David Wellman
wrote:
> To run a select statement the application code has to:
> sqlite3_prepare: parse the sql, make sure it's valid, build the plan
> (using "nifty heuristics" :-) )
> sqlite3_step - this one runs the sql, builds an answer set and then
> returns the
The reason "select count(*) from t" is super fast is the special "count"
> opcode that does the "running" in just one go, instead of calling "Column",
> "AggStep" and "Next" in a loop.
>
> -Ursprüngliche Nachricht-
> V
I don't have sqlite-src-3200100.zip on hand but it seems to be there in
tool/sqldiff.c for sqlite-src-3081002 (and in the fossil repo).
-Rowan
On 19 September 2017 at 15:58, Rob Willett
wrote:
> Hi,
>
> I'm trying to find the source to sqldiff and am struggling to locate it.
>
> I've looked on
On 6 October 2017 at 15:42, wrote:
> On Fri Oct 06, 2017 at 09:28:08AM +0200, Clemens Ladisch wrote:
> >
> > For boolean values, "a XOR b" = "a <> b".
>
> Is the <> operator documented somewhere? I can't find it in either of
> these places:
>
<> is SQL for "not equal to" (shout out to all the BA
In that case you would be well advised to use a monotonic clock source,
rather than a "date-generating" clock. In linux this is the difference
between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
to clock_gettime().
But any API you might use to set a trigger for 2 seconds into
On 24 November 2017 at 10:27, J. King wrote:
> The rollback journal is used to return the database to its previous state
> in the case of a partial write, not complete writes which are interrupted.
> As you didn't commit the transaction, no write occurred, never mind a
> partial one, so the datab
On 24 November 2017 at 16:44, Jiří Matějka wrote:
> We need to lock our SQLite datafiles at the filesystem level, because our
> application frequently crashes with "database is locked" error, which is
> probably due to other processes opening our files. At least we identified
> Windows Search ind
There is one degenerate case, which has been discussed a few times on this
list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that
marks a transaction being committed is the deletion of the rollback
journal. Deleting a file is a directory level operation, which means there
are t
On 14 December 2017 at 01:19, Warren Young wrote:
> On Dec 12, 2017, at 10:24 AM, Simon Slavin wrote:
> >
> > Santa Clause: SELECT name,hobbies,address FROM people WHERE
> behaviour=‘nice’
>
> I think you mean
>
> SELECT name,address
> CASE behaviour
> WHEN ‘nice' THEN
>
On 18 December 2017 at 06:59, Dinu wrote:
> Richard Hipp-3 wrote
> > Can you try this:
>
> (1) BEGIN TRANSACTION
> > (2) DROP all indexes from the table
> > (3) Do the DELETE operation
> > (4) CREATE all indexes again
> > (5) COMMIT
> Thanks Richard, as mentioned earlier, any structure change is
On 18 December 2017 at 18:03, Dinu wrote:
> Rowan Worth-2 wrote
> > I'm not sure what you're worried about? Dropping and recreating identical
> > indices within a transaction won't cause a visible structure change to
> > concurrent readers -- that's t
On 19 December 2017 at 11:23, Simon Slavin wrote:
>
>
> On 19 Dec 2017, at 2:55am, Richard Hipp wrote:
>
> > On 12/18/17, Lee, Greg wrote:
> >> I am still seeing the problem on Power 8 and others report the problem
> >> persists on Power 9. Please see the spack github issue. I also attached
> a
On 16 December 2017 at 11:21, Richard Hipp wrote:
> On 12/15/17, Lee, Greg wrote:
> > I never got a reply on this issue and someone else tripped up on it:
> >
> > https://github.com/spack/spack/issues/6698
> >
> > Any help or even acknowledgement of this message would be appreciated.
> >
>
> I b
Seems to be working as advertised. Unless you specify --disable-tcl, the
configure script defaults to building an sqlite extension for TCL.
The extension can't go in $PREFIX, since tcl wouldn't be able to find it.
So your options for a non-root install are:
1) --disable-tcl
2) set the environment
Does either process take backups of the DB? If so, how is that implemented?
-Rowan
On 22 December 2017 at 05:47, Nikhil Deshpande
wrote:
> Hi,
>
> We have an application that in a Linux VM that's running into
> SQLite DB corruption (after weeks and months of running,
> 4 such instances yet in di
On 22 December 2017 at 23:57, Michael Tiernan
wrote:
>
> > It just doesn’t install to a directory it can’t write to, because you
> > told it to install system-level things.
>
> Not going to hash it out here but I didn't tell it to install system-level
> things, I told it to compile and install e
On 23 December 2017 at 00:17, curmudgeon wrote:
> >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
>
> Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
> with a minus sign before that directive I get a compile error "macro names
> must be identifiers
On 28 December 2017 at 02:55, Simon Slavin wrote:
> On 27 Dec 2017, at 6:10pm, Nikhil Deshpande wrote:
>
> >> Can you include a "pragma integrity_check" at startup ?
> >> Can you include a "pragma integrity_check" executed at regular
> intervals ?
> > The writer process does "pragma quick_check"
nything to you? If it doesn’t I’ll make a post on the c++
> builder forum.
>
>
> From: Rowan Worth<mailto:row...@dug.com>
> Sent: 29 December 2017 03:13
> To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] What happens if a
Firstly this code is extremely dangerous. What would happen if
acInputString contained this string?
';DROP TABLE AUDIO; SELECT '
It's best practice to use bound parameters to prevent this kind of problem,
ie.
sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, &stmt,
NULL);
sprin
On 12 January 2018 at 07:21, wei1.z wrote:
> What is the meaning of this line?
>
> 01-11 14:40:59.733 10011 2864 2877 E SQLiteLog: (14) os_unix.c:36136: (2)
> open() -
>
> db file cannot be found, or permission issue ?
>
To decipher this in future, the first number in parens is the sqlite erro
https://www.sqlite.org/withoutrowid.html
"NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
table."
It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
KEY columns of _every_ table according to the SQL standard, but an early
version of sqlite include
On 17 February 2018 at 08:34, Deon Brewis wrote:
> Anybody have a recommendation for a tool that can read/show/interpret a
> SQLITE file at the BTREE level?
>
> Want to be able to decode the links between pages, figure out what all the
> data mean etc. And should be able to work on a corrupted fi
What is your expected answer for:
select length(printf ('%4s', 'です'))
-Rowan
On 18 February 2018 at 01:39, Ralf Junker wrote:
> Example SQL:
>
> select
> length(printf ('%4s', 'abc')),
> length(printf ('%4s', 'äöü')),
> length(printf ('%-4s', 'abc')),
> length(printf ('%-4s', 'äöü'))
>
On 2 March 2018 at 03:43, Shevek wrote:
> We use HikariCP, so a connection is in use by one thread at a time with
> JMM-safe handoff, and they all share the mmap region.
>
Shevek also wrote:
> What I think is happening is that either a pthread mutex or a database
lock is serializing the accesse
On 16 March 2018 at 18:24, Robert M. Münch
wrote:
> Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since
> it doesn’t has an UPSERT?
>
> So, if I have a table with 30 columns and my code updates sub-sets out of
> these columns, I don’t want to write queries that manually retri
On 16 March 2018 at 21:44, Paul wrote:
> A few years back I've been asking the same question. To be honest, there's
> no more
> efficient alternative, than the one that can be implemented within library
> itself.
> Both performance-wise and productivity-wise.
>
> Doing hacks with INSERT + UPDATE
5/10
1/11
5/10
Always in conjunction with INTEGER PRIMARY KEY fwiw.
Also the following command is perhaps more portable:
sqlite3 yourfile.db .schema | grep -ic autoincrement
The sqlite3 shell on my system is too old to understand .schema --indent
and doesn't output anything so there's alwa
On 23 March 2018 at 05:24, Jonathan Moules
wrote:
> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
> (preview)) despite looking through hundreds of thousands of records in each
> table, and it returns 86 records in all. This is great!
>
> But when I stick a
On 20 March 2018 at 22:33, Deon Brewis wrote:
> How do you actually get a SQLITE_CANTOPEN_ISDIR error?
>
> In order to get an extended result code, we need to pass a sqlite3*
> connection, but you don't have that if the file can't be opened in the
> first place.
>
I understand why you'd think th
On 23 March 2018 at 08:54, Deon Brewis wrote:
> Most of the time when the database gets corrupted, we don't crash, it
> corrupts midway through valid SQL (no pattern to it - completely unrelated
> SQL). I was thinking if the expression functions have bugs in them it could
> cause corruption, but
Amusing -- but without the leading single-quote it would take intentional
effort for a programmer to detonate this payload.
Its omission is interesting though. Does it indicate an incompetent
attacker, or is companieshouse.gov.uk using some bespoke approach like
"delete all single quotes" instead
On 7 May 2018 at 15:13, Scott Robison wrote:
> On Sun, May 6, 2018 at 11:34 PM, Rowan Worth wrote:
> > Its omission is interesting though. Does it indicate an incompetent
> > attacker, or is companieshouse.gov.uk using some bespoke approach like
> > "delete all
On 8 May 2018 at 17:22, R Smith wrote:
> On 2018/05/08 9:37 AM, Donald Shepherd wrote:
>
>> I've long assumed that when using the online backup API on a SQLite
>> database, other processes will not be able to write to the source database
>> for the duration of the sqlite3_backup_step call. Howev
On 9 May 2018 at 08:56, Richard Hipp wrote:
> But with
> SQLite, there is no round-trip latency. A "round-trip" to and
> database is just a function call, and is very very cheap.
>
I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the
latency of _communication_ between t
On 14 May 2018 at 01:08, Richard Damon wrote:
> On 5/13/18 12:55 PM, Rowan Worth wrote:
> > On 9 May 2018 at 08:56, Richard Hipp wrote:
> >
> >> But with
> >> SQLite, there is no round-trip latency. A "round-trip" to and
> >> data
On 28 May 2018 at 17:29, x wrote:
> I’ve just discovered the thread in the original app decreases the
> available memory by around 4 GB. Are they really that expensive?
A thread itself is not expensive in terms of memory.
> It has very little data of its own
Either this statement is wrong,
On 3 June 2018 at 07:28, Scott Robison wrote:
> I've encountered a feature that I think would be awesome:
> https://www.postgresql.org/docs/9.3/static/dml-returning.html
>
> Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING
> id;
>
> my thoughts are just that this could
On 6 June 2018 at 07:14, Richard Hipp wrote:
> On 6/5/18, Stéphane Aulery wrote:
> > Hello,
> >
> > The changelog of the last release [1] say at the point 13 :
> >
> > UPDATE avoids writing database pages that do not actually change. For
> > example, "UPDATE t1 SET x=25 WHERE y=?" becomes a no-o
Hi Bruce,
I had a go at post-processing the sqlite3 shell's output to apply thousand
separators. I don't recommend looking too hard at the sed for the sake of
your sanity, but the gist is it repeatedly prepends a comma to trailing
groups of three digits, and then repeatedly removes commas which ap
Hi Bruce,
On 27 November 2015 at 10:59, Bruce Hohl wrote:
> Thanks to suggestions on this list I explored the sed post processing
> avenue and found a solution. There are quite a few sed docs at
> http://sed.sourceforge.net For my needs I adapted an example from
> http://www-rohan.sdsu.edu/doc
Suspect you are running into more VBA<->C issues. The db path, journal path
and wal path are stored sequentially in memory, so if you were to skip the
NUL terminators you'd see all three paths.
But I'm not exactly sure how that might happen without resulting in a
segfault, so I could be missing so
On 18 October 2015 at 04:15, wrote:
> I just tried one more time with the -lm switch and this time it worked.
> Hmm...
>
> Problem solved. Thanks.
You might find that specifying -lm *after* the .c files works but -lm
before the .c files doesn't. The linker processes libraries/object files in
t
1 - 100 of 237 matches
Mail list logo
