Re: [sqlite] fsync on -wal still happening

On 29 Dec 2017, at 7:07pm, Ian Freeman wrote: > Thanks, Simon. Indeed I did see that option. But I'm concerned about > maintaining integrity in the middle of an power-interrupted commit, This is not a problem for either WAL mode or the older journal modes. If you avoid

Re: [sqlite] fsync on -wal still happening

On 29 Dec 2017, at 4:34pm, Ian Freeman wrote: > I see, then what I'm seeing is just normal behavior of the writes being > flushed to disk. I read what I wanted to hear about synchronous=NORMAL > delaying writes to the -wal file. Instead I'm going to see if I can > move -wal

Re: [sqlite] Btree page corruption

On 29 Dec 2017, at 4:10am, Rowan Worth wrote: > do any of your processes open the database file, for any > reason, without going through sqlite's API? Just to note that a major offender in this respect is anti-virus software. So don’t think just of things that might want to

Re: [sqlite] Is WAL mode more robust against corruption?

On 28 Dec 2017, at 8:10pm, Chris Brody wrote: > I am considering whether or not to recommend the WAL mode for users in the > PhoneGap sqlite plugin that I maintain. The negative I see is the delays > that may result at certain points from the need for database checkpoints.

Re: [sqlite] Btree page corruption

On 27 Dec 2017, at 6:55pm, Simon Slavin <slav...@bigfraud.org> wrote: > An alternative might be to run "integrity_check" on backup copies which don’t > show up anything on "quick_check". This could be done without blocking the > production system. If yo

Re: [sqlite] Dan: zipfile.c FYI data column quirk

On 27 Dec 2017, at 8:20pm, petern wrote: > --Added explicit newline to end of row1.txt How. What did you do ? Was it a NL or a CR ? Or done as Unicode ? Simon. ___ sqlite-users mailing list

Re: [sqlite] Move to Github!!?

On 27 Dec 2017, at 8:10pm, Warren Young wrote: > It is almost certainly the case that some of the files in x and y are > identical so that those files could be cloned from those in the other at the > filesystem level by making one of these OS-specific API calls, but it

Re: [sqlite] Move to Github!!?

On 27 Dec 2017, at 6:49pm, Peter Da Silva <peter.dasi...@flightaware.com> wrote: > On 12/27/17, 12:14 PM, "sqlite-users on behalf of Simon Slavin" > <sqlite-users-boun...@mailinglists.sqlite.org on behalf of > slav...@bigfraud.org> wrote: > >> Would

Re: [sqlite] Btree page corruption

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" on every startup at init, > bails

Re: [sqlite] Move to Github!!?

On 27 Dec 2017, at 4:05pm, Warren Young wrote: > Fossil has that problem, too. Most DVCSes do, because by their very nature, > they want to clone the entire history of the whole project to every machine, > then make a second copy of the tip of each working branch you

Re: [sqlite] Move to Github!!?

On 27 Dec 2017, at 1:51pm, Fredrik Gustafsson wrote: > - What are our demands for a ticket system, would fossil or gitlab (for > example) suit us better? Here’s something from experience. You can have three types of ticket: A) Internal tickets. These are raised by employees

Re: [sqlite] Move to Github!!?

On 26 Dec 2017, at 7:07pm, Bob Friesenhahn wrote: > On Tue, 26 Dec 2017, J Decker wrote: > >>> Why aren't you moving all of your GitHub projects over to Fossil! >> >> Because Pull Requests, and a larger variety of tools to deal with Git >> repositories. > > It is

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

On 22 Dec 2017, at 4:50pm, Radovan Antloga wrote: > select d from (select c AS d from (select a AS c from test)); > > I get d as column name. If I create table with > create table as I get a as column name. As I wrote, you did not specify AS for d, so you cannot

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

On 22 Dec 2017, at 3:04pm, Tim Streater wrote: > 2) I don't see this issue mentioned when I read the PHP documentation about > their SQLite interface, nor do I see it in the Xojo docs about *their* > interface either. I assume their interfaces are not rewriting SELECT

Re: [sqlite] Btree page corruption

On 21 Dec 2017, at 9:47pm, Nikhil Deshpande wrote: > 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 different VMs). > > [snip] > > There were no power-off or reboots in

Re: [sqlite] generic advice for insert performance in-memory database optimization

On 21 Dec 2017, at 7:06pm, Nelson, Erik - 2 wrote: > I'm running on linux with the OS page size configured to 4096 and ~380 GB of > ram (much more than required for the table so I think I'm not swapping) and > haven't altered the sqlite page size. I am

Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

On 21 Dec 2017, at 3:46pm, David Raymond wrote: > The only potential problem with "insert or ignore into" is that it will > ignore any constraint violation for that record insert Not true. sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

On 21 Dec 2017, at 5:58pm, Igor Tandetnik wrote: > Isn't that precisely what happened in your example? Inserting 6 failed > silently. What again seems to be the problem? I’m sorry. You and Scott are quite right. I have no idea what I was thinking. Simon.

[sqlite] INSERT OR IGNORE ignores constraints. Bug ?

On 21 Dec 2017, at 3:46pm, David Raymond wrote: > The only potential problem with "insert or ignore into" is that it will > ignore any constraint violation for that record insert Wait. What ? SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints.

Re: [sqlite] What happens if an in memory database runs out of memory

On 21 Dec 2017, at 4:34pm, curmudgeon wrote: > Put it this way, if I create n temp tables will there be n_+ 1 page caches > or just the main cache containing a further n temporary caches? Try it and see. But unless you intentionally make us strange numbers the problem

Re: [sqlite] Can a SELECT statement be used within a trigger?

On 21 Dec 2017, at 3:11pm, Shane Dev wrote: > Here we see the INSERT statement was triggered but not the SELECT. Have I > misunderstood the syntax diagram? It’s possible that the SELECT is being processed. However, since the INSERT command returns no data it still can

Re: [sqlite] What happens if an in memory database runs out of memory

On 21 Dec 2017, at 2:15pm, curmudgeon wrote: > Can someone tell me, if I create a temporary table does is its 'separate > cache' created within the cache_size cache or is it completely separate from > that? According to the documentation you quoted, "Each temporary

Re: [sqlite] create index implies analyze?

On 20 Dec 2017, at 5:19pm, David Raymond wrote: > I have often thought that a "create analyzed index" statement would be nice > addition though to do the two at the same time. Perhaps not as useful as you might think, because most people create indexes while their

Re: [sqlite] performance impact of index creation order

On 20 Dec 2017, at 3:38pm, Nelson, Erik - 2 wrote: > Assuming that table 'data' is completely constructed, does index creation > order have any performance ramifications? > > For example, would it be reasonable to assume that the order of these two >

Re: [sqlite] SELECT query #first run# is very slow VOL2

On 19 Dec 2017, at 8:37pm, zakari wrote: > pasting some logs, Im declaring again this happening only the first time, > afterwards working without problem. > 2017-12-17 15:16:23 - execute > 2017-12-17 15:17:20 - executed > > 2017-12-19 14:53:35 - execute > 2017-12-19

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

On 20 Dec 2017, at 6:30am, Shane Dev wrote: > Let's say we have nodes and edges tables - > > sqlite> .sch nodes > CREATE TABLE nodes(id integer primary key, description text); > sqlite> .sch edges > CREATE TABLE edges(parent references nodes, child references nodes); >

Re: [sqlite] What happens if an in memory database runs out of memory

On 19 Dec 2017, at 9:22pm, curmudgeon wrote: > Thanks Keith. So sqlite does look to increase the temp table's cache size if > it's not big enough? No. Memory allocated to that cache is memory which can’t be used by anything else. There’s probably a far better use for

Re: [sqlite] What happens if an in memory database runs out of memory

On 19 Dec 2017, at 6:01pm, curmudgeon wrote: > 1) I read in those links that each temp table is given (by default) its own > page cache of 500 pages. Is this a separately created page cache or is it > 500 pages from THEE page chache? If it's the latter that will explain

Re: [sqlite] Atomic DELETE index optimisation?

On 19 Dec 2017, at 4:15pm, Dinu wrote: > 3) "Deleted" bit field - presumably the "soft delete" as you call it; If you do try this, the 'bit' column should be declared as INTEGER and the values stored should be 0 and 1. SQLite is extremely efficient at storing/sorting

Re: [sqlite] same code produces a different database file on different computers

On 19 Dec 2017, at 1:30pm, Karl Forner wrote: > and the dumps usign the .dump sqlite3.exe command are also identical. Paul has a better chance of understanding the hex dump than I do, but what I think I’m seeing is trivial differences in file organisation. In other

Re: [sqlite] same code produces a different database file on different computers

On 19 Dec 2017, at 12:43pm, Karl Forner wrote: > All the software in the docker container. so it is exactly the same version > of all software and libraries, except the linux kernel. Moreover the > differences are not in the header, rather interspersed with what looks

Re: [sqlite] What happens if an in memory database runs out of memory

On 19 Dec 2017, at 11:40am, Simon Slavin <slav...@bigfraud.org> wrote: > See section 3 of > > <https://sqlite.org/tempfiles.html> Sorry. See section 4 too. Simon. ___ sqlite-users mailing list sqlite-users@mailing

Re: [sqlite] What happens if an in memory database runs out of memory

On 19 Dec 2017, at 9:33am, curmudgeon wrote: > Running the same tests again using > an actual table (TEMP keyword omitted) shows the in memory INSERT (and > DELETE) to be twice as fast on the database loaded into memory compared to > when it's accessed from the SSD.The

Re: [sqlite] Macro expansion of B0 on Linux PPC system

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 >> configure/make output if

Re: [sqlite] What happens if an in memory database runs out of memory

On 18 Dec 2017, at 2:48pm, curmudgeon wrote: > You're definitely right about me wasting my time Simon. I loaded my entire > database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache > size (using win64). I then ran my test (inserting the results of a

Re: [sqlite] Atomic DELETE index optimisation?

On 18 Dec 2017, at 12:28pm, Dinu wrote: > Actually I realized that the DB page size is 1K. Is this bad? I tried to run > the pragma query with 1M pages, to amount to the same 1G; there seems to be > a dramatic improvement in throughput at the beginning of the query, The

Re: [sqlite] Atomic DELETE index optimisation?

On 18 Dec 2017, at 10:03am, Dinu wrote: > I honestly don't see how in any DB system the client process would not crash > if the index it's running a curson on were to be removed. SQLite doesn’t run cursors. There are no cursor commands in the SQLite API. SQLite does

Re: [sqlite] Atomic DELETE index optimisation?

On 17 Dec 2017, at 10:57pm, Dinu wrote: > Na, they are not a single-time use rows, otherwise I'd have used a FIFO :) > Every now and then, a large portion of the table becomes obsolete by > external factors. Are you issuing one DELETE command with a WHERE clause which

Re: [sqlite] Atomic DELETE index optimisation?

On 17 Dec 2017, at 10:18pm, Dinu wrote: > For the table swap operation, that is out of the question, the DB is running > on a live system that has multiple clients. Foreign keys or not, any > structure change crashes all clients (DB structure has changed). Too high a >

Re: [sqlite] Atomic DELETE index optimisation?

On 17 Dec 2017, at 10:01pm, Dinu wrote: > The CPU is capped up badly, the HDD > is at idle level so this also hints to the same issue. Apparently your program/OS is trying to do everything in cache. This may be self-defeating for this operation. Your description of

Re: [sqlite] Atomic DELETE index optimisation?

On 17 Dec 2017, at 8:53pm, Dinu Marina wrote: > It became apparent to me from performance measurements that the DELETE > operation is very slow, when operating on large recordsets with indexes > involved. My deduction is that SQLite updates the indexes for every deleted

Re: [sqlite] Odd question

On 16 Dec 2017, at 9:43pm, Nelson, Erik - 2 wrote: > I'm using an application that I can't change. I can give it multiple queries > to run but the application assumes that each query will produce at least one > row and causes an error if that's not the

Re: [sqlite] Missing data table

On 16 Dec 2017, at 1:51pm, Chris B wrote: > Twice now in the last two months, the "Aircraft" table has vanished from my > dad's database effectively showing no data in the data tab on the main > window, but the file size has remained the same, suggesting the data is

Re: [sqlite] What happens if an in memory database runs out of memory

On 15 Dec 2017, at 8:36pm, x wrote: > I’ll have to look into how to increase the in-memory pager cache. Before you do anything like that, ask yourself two questions: a) Is my program actually fast enough without any of these weird picky measures ? Or am I spending

Re: [sqlite] What happens if an in memory database runs out of memory

On 15 Dec 2017, at 4:30pm, x wrote: > Suppose I execute “attach :memory: as mem” and then create a table in mem > that requires more space than the available RAM can hold what will happen? The problem would not happen when you create the table. SQLite reserves only a

Re: [sqlite] Is this a bug with expression evaluation?

On 14 Dec 2017, at 5:03pm, Tony Papadimitriou wrote: > SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has > no affinity. " > It seems that 'no affinity' gets translated to integer affinity, then. Just to remind you that if something is not documented

Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?

What you see is not a bug, it’s an annoying heritage of C syntax. Might even precede C. Here’s the problem: select column1*(24/100); And here’s what you’re meant to do for 24%: select column1*(24.0/100.0); Alternatively, the value in column1 should be real. That should also

Re: [sqlite] How to index data based on custom comparisons?

On 13 Dec 2017, at 8:34pm, Lifepillar wrote: > But, (correct me if > I am wrong), if I index the blob column directly, comparisons are > based on memcpy(), which in my case is not what I want. Is it > possible to create an index that somehow uses a custom comparison >

Re: [sqlite] DateTime kind stored as undefined

On 13 Dec 2017, at 11:51am, Michał Niegrzybowski wrote: > I have a table which has a column of type DateTime in my code I insert > there an actual UTC Date (which is not the same as my local time). When I > want to gather previously added record, my record

Re: [sqlite] Enabling MMAP in Android

On 12 Dec 2017, at 6:27pm, Jens Alfke <j...@mooseyard.com> wrote: > On Dec 12, 2017, at 5:46 AM, Simon Slavin <slav...@bigfraud.org> wrote: >> Before you answer that question, you should know that both Windows and macOS >> have been proved to have serious bugs i

[sqlite] Seasonal syntax

Some SQL terminology: Selection Clause: WHERE Sort Clause: ORDER BY Sublist Clause: LIMIT OFFSET Subsort Clause: GROUP BY HAVING Santa Clause: SELECT name,hobbies,address FROM people WHERE behaviour='nice’ Season’s greetings and best wishes to all subscribers.

Re: [sqlite] Enabling MMAP in Android

On 12 Dec 2017, at 1:32pm, advancenOO wrote: > Um, stray pointer or array overrun in upper applications will be treated as > BUG in my system. And I believe all these bugs could be fixed before using > sqlite. > So can I safely enable SQLITE_MMAP_READWRITE directly

Re: [sqlite] SQLite3 on WinCE platform

On 11 Dec 2017, at 8:59am, Ertan Küçükoğlu wrote: > Error I am getting when application runs is: EInOutError: Can not load > SQLite client library "sqlite3ce.dll". Check your installation. Given this page

Re: [sqlite] Unable to store 500MB size of row data even after define macro SQLITE_MAX_LENGTH

On 9 Dec 2017, at 6:29am, Dianne Dunn wrote: > How,do I get off this email list?? What is it for?? Use the. link on the last line of every post, including this one. Discussing SQLite. Why and how did you join it if you didn’t know ? Simon.

Re: [sqlite] How to dump all the tables into separate files?

On 8 Dec 2017, at 1:58pm, Peng Yu wrote: > Could you provide the working code for bash (on Mac OS X or linux)? Thanks. Something like this … sqlite3 myDatabase.sqlite > myTable.tsv << EOS .mode tabs select * from myTable; EOS The first EOS must be at the very

Re: [sqlite] Timing issue with min, max and count

On 8 Dec 2017, at 1:17pm, Simon Slavin <slav...@bigfraud.org> wrote: > helps you (and us, if you want to post it) to understand what’s happening. Please ignore my post. Dr H explain your situation exactly. Simon. ___ sqlite-users mai

Re: [sqlite] Timing issue with min, max and count

On 8 Dec 2017, at 12:20pm, x wrote: > I have a table with 2.4 million records. It’s a without rowid table (I don’t > know if that’s significant) with an integer primary key (ID) and several > secondary indexes of the form (OtherCol, ID). If I run > > select min(ID),

Re: [sqlite] How to dump all the tables into separate files?

On 8 Dec 2017, at 7:02am, Peng Yu wrote: > I'd like to dump all the tables to separate files, one table one file. > Each file should be in TSV format. > > Is there a convenient way to do so in sqlite3? There’s no direct output from the SQLite library to produce TSV

Re: [sqlite] sqlite3_analyzer for Debian

On 7 Dec 2017, at 11:22pm, wrote: > be careful with "under any Linux" as the name of the zip says the > binarys are for X86 only: sqlite-tools-linux-x86-321.zip Point taken. Does "Lintel" mean anything apart from a horizontal bracing strut ?

Re: [sqlite] sqlite3_analyzer for Debian

On 7 Dec 2017, at 6:37pm, Cecil Westerhof wrote: > I was looking at: >Tcl'2017 - SQLite's use of Tcl (Richard Hipp) > > There is talk about the sqlite3_analyzer. But I do not have this on my > Debian system. Is that only for Windows? It’s not part of the standard

Re: [sqlite] Emulate right-join

On 6 Dec 2017, at 6:07pm, R Smith wrote: > You mean make SQLite less Lite, but with Zero computational advantage, by > simply adding syntactic sugar bloat? - I'm going to have to vote No on that. > (Luckily my vote counts extremely little.) I would normally vote with you,

Re: [sqlite] How to store as integer

On 6 Dec 2017, at 1:19pm, Cecil Westerhof wrote: >message NOT NULL Given thqt you want the "message" stored as REAL, you should be defining this column as REAL. This is necessary, though not sufficient. Simon.

Re: [sqlite] Emulate right-join

On 6 Dec 2017, at 9:36am, Jean-Luc Hainaut wrote: > Actually, the left outer join is sufficient to execute all the outer join > operators: > > - right outer join: just swap the "from" arguments > > - full outer joins: union of left and right outer joins I never

Re: [sqlite] Error code 14 for the Journal file

On 4 Dec 2017, at 9:31am, Tilak Vijayeta wrote: > PID:02FB0036 TID:06B60006 SQLite error (14): os_win.c:36317: (31) > winOpen(\--\TTDB.db3-journal) - A device attached to the system is not > functioning. You appear to have a hardware or operating system problem

[sqlite] Why Unicode is difficult

Every so often someone asks on this list for Unicode to be handled properly. I did it myself. Then other people have to explain how hard this is. So here’s an article which, after introductory material, discusses the hard questions in Unicode:

Re: [sqlite] Automatic index, despite existing index?

On 1 Dec 2017, at 1:50pm, Olivier Mascia wrote: > Could it be that it might need a DESC index? SQLite should not be doing that. It understands that an index can be used "backwards" if it needs to reverse the sort order. > Could the "automatic index on ..." in the error

Re: [sqlite] Foreign key lint has issues with without rowid

On 30 Nov 2017, at 7:02pm, David Raymond wrote: > I’m using the CLI’s .lint fkey-indexes command, and it appears to be having > issues with without rowid tables. A low priority thing to look into when > someone's bored. Please tell us which version of SQLite you’re

Re: [sqlite] Article on AUTOINC vs. UUIDs

On 30 Nov 2017, at 3:52pm, Stephen Chrzanowski wrote: > As one of the security guys here at work say, "Security does not help > convenience". In the debug world, yeah, I agree, looking for 4310 is much > easier than 8af7* but, that should stick to a debug environment.

Re: [sqlite] PLEASE UNSUBSCRIBE

On 30 Nov 2017, at 3:55pm, oɹɹoɯɐɥɔ ǝƃɹoɾ wrote: > I've tried a zillion times via the above link but it does not seem to work. Clicking on that link just worked fine for me. It’s not even HTTPS. You might want to try a different browser, try your smartphone, then

[sqlite] Article on AUTOINC vs. UUIDs

Thought some of you might enjoy seeing this article. I make no comment on what I think of the reasoning therein. It’s set in the PostgreSQL world, but you could make an external function for SQLite which generates UUIDs.

Re: [sqlite] Sqlite and docker performance question

OP wrote: > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER, > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER); Is this the first content of a new file ? If so, SQLite has to create the file and write some structure information as well as writing the table. I

Re: [sqlite] Datetime / Transactions / CLI

On 28 Nov 2017, at 5:34pm, Keith Medcalf wrote: > This would indicate that "now" has statement-stability and not > transaction-stability, which matches with my observations. You’re right, I was wrong. Thanks for the correction. Simon.

Re: [sqlite] Datetime / Transactions / CLI

On 28 Nov 2017, at 3:50pm, no...@null.net wrote: > Can someone point me to the documentation for behaviour of date/time > functions inside transactions? In my code it appears time is frozen. Correct. The value of 'now' is frozen at the time a transaction begins. This is to ensure that if

Re: [sqlite] Foreign key help

On 28 Nov 2017, at 3:26pm, x wrote: > If I have foreign keys in place but always have foreign_keys = OFF then one > day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly > check all foreign keys and report / delete violations or does it leave >

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

On 28 Nov 2017, at 12:38am, Peter Halasz wrote: > Please can SQLite developers make the ROW ID status of a field visible in > future versions? If it’s cone, the obvious place would be to add a column to Can you not

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

On 27 Nov 2017, at 4:51pm, Joe Mistachkin wrote: > Judging from the description so far, I think that both SQLite and > System.Data.SQLite are working correctly. > > It is true that System.Data.SQLite has an internal retry mechanism > that will wait 150ms between retries.

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

On 27 Nov 2017, at 12:41pm, Jiří Matějka wrote: > We have no busy timeout set because we use System.Data.SQlite library which > has its own waiting mechanism (repeating the sqlite3_reset/sqlite3_step > within 30 secons). I’m hoping that Joe Mistachkin, or someone else

Re: [sqlite] Simple read-only program very slow

On 26 Nov 2017, at 3:13am, Kevin O'Gorman wrote: > > I've got a database of some 100 million records, and a file of just over > 300 thousand that I want represented in it. I wanted to check how much > difference it was going to make, so I wrote a super > simple

Re: [sqlite] How do I combine these to 2 views ...

On 26 Nov 2017, at 8:02am, Shane Dev wrote: > Any ideas to achieve this? Use the UNION keyword to combine the results of the two SELECT commands: Simon. ___ sqlite-users mailing

Re: [sqlite] More timeline changes.

On 25 Nov 2017, at 1:41pm, Richard Hipp wrote: > Web developers - help me with this: For item (3) above, how can I > make the ellipsis or icon to "show more detail" configurable using > CSS? Declare two different CSS classes: one is the "hidden" one which does not show the

Re: [sqlite] Getting an advance list of RowIDs for a query result set

On 25 Nov 2017, at 1:15pm, curmudgeon wrote: > Given a select where a 'base table' is attached to lookup tables > how can I determine which of the lookup tables can be removed from the table > such that > > select BaseTbl.RowID from ... where ... order by ... > > will

Re: [sqlite] Web issue

On 23 Nov 2017, at 9:14pm, R Smith wrote: > In searching the sqlite.org pages for a previous post, I tried to search with > this computed url: > > http://www.sqlite.org/search?q=CTE > > To which there was about 8 seconds wait time and then this response returned: Seems

Re: [sqlite] Exit value of "PRAGMA integrity_check" in command line

On 23 Nov 2017, at 3:11am, 林自均 wrote: > It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and > write new logs into a new sqlite file. Does that affect the way I check it? I was interested why you were checking for corruption, so your answer just

Re: [sqlite] Exit value of "PRAGMA integrity_check" in command line

On 23 Nov 2017, at 3:00am, 林自均 wrote: > In other word, to check if a sqlite file is corrupted, I have to check the > stdout instead of the exit value. Am I right? Why are you checking for corrupted databases ? What will you do if the database is corrupt ? Simon.

Re: [sqlite] Bug

On 23 Nov 2017, at 12:40am, Ivan De La Cruz wrote: > SQLiteDataAdapater is dropping characters after a space in the field when > filling a datatable (c# winforms). > > I.e. > Field : 100 > ml

Re: [sqlite] Can I recursively concatenate strings?

On 22 Nov 2017, at 9:56pm, Shane Dev wrote: > P.S I know that substr('x', 1, stringlengths.length) would work in > this particular case but then I must know maximum value of > stringlengths.length at the point of time when I construct the query. Is > there a more

Re: [sqlite] how into insert row into middle of table with integer primary key

On 22 Nov 2017, at 8:30pm, Shane Dev wrote: > Imagine I have a GUI element with a drop down list of fruit. The source of > the list is my fruit table and it may have many entries. It might more > convenient to list the popular fruit near the top. In that case the >

Re: [sqlite] how into insert row into middle of table with integer primary key

On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: >> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >> >> That assumes you are not starting from an integer part (like 4000) and >> hitting the exact same relative insert spot every time, which /can/ happen,

Re: [sqlite] Many ML emails going to GMail's SPAM

On 21 Nov 2017, at 2:30pm, Richard Hipp wrote: > I really need to come up with an alternative to the mailing list. > Perhaps some kind of forum system. Suggestions are welcomed. If we’re to end up with a chat-based system then I’d prefer Discord. I can’t recommend a

Re: [sqlite] [OT] Updating sqlite in PHP windows

On 21 Nov 2017, at 10:30am, Eduardo wrote: > Thanks Simon. It seems that php 5.6 it's EOL and all updates (except > security) are on 7.1 and 7.2 branchs. > > See the answers I get from php staff, 7.2 will have 3.21 version plus FTS and > JSON extensions on

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

On 20 Nov 2017, at 3:41pm, Kniep Stefan (CM/ESN3) wrote: > P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is > called) to delete/truncate the WAL when journal_size_limit is reached. I had > to change only two lines, but of course I am

Re: [sqlite] Energy consumption of SQLite queries

On 20 Nov 2017, at 10:54pm, Ali Dorri wrote: > I am doing a research on the energy consumed by a query in SQLite. I have a > program which fills a database with blocks of data. Then, it attempts to > remove some data from the database. I don't know how to measure the

Re: [sqlite] WAL mode with readers and writers

On 20 Nov 2017, at 7:37pm, Jim Dossey wrote: > sqlite3_prepare("SELECT * FROM table;"); > while (sqlite3_step() == SQLITE_ROW) { > x = current_rowid(); > sqlite3_exec("UPDATE table SET value=1 WHERE rowid=x;"); > sleep(1); > } In SQLite, as in other SQL engines, all

Re: [sqlite] how into insert row into middle of table with integer primary key

On 20 Nov 2017, at 9:31pm, Shane Dev wrote: > I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? As others have

Re: [sqlite] "PRAGMA data_version" documentation wrong

On 20 Nov 2017, at 6:20pm, Jim Dossey wrote: > I think the documentation for "PRAGMA data_version" at > https://www.sqlite.org/pragma.html is incorrect. I've been testing this > pragma and I've found that it only returns '1' for a standard database or '2' > if the database is

Re: [sqlite] how into insert row into middle of table with integer primary key

On 20 Nov 2017, at 2:57pm, Clemens Ladisch <clem...@ladisch.de> wrote: > Simon Slavin wrote: >> UPDATE fruit SET id = id+1 WHERE id >=2; > > This is unlikely to work because some ID values can conflict in the > middle of the execution. Which in fact violates f

Re: [sqlite] how into insert row into middle of table with integer primary key

On 19 Nov 2017, at 8:37pm, Shane Dev wrote: > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? > > desired result - > >

Re: [sqlite] [OT] Updating sqlite in PHP windows

On 20 Nov 2017, at 11:06am, Eduardo wrote: > Or better, a recipe that works to compile sqlite3 on php5.6.x? This is the best-looking page I’ve found, but I have never tried it on Windows.

Re: [sqlite] Help with left joins

On 20 Nov 2017, at 11:09am, x wrote: > explain > select ColA from TblA > left join TblB using (ColB) > left join TblC using (ColC) > left join TblD using (ColD) > where ColBX=?; > > there will be no trace of TblC or TblD as they're redundant. Although you have not

Re: [sqlite] Does wal-file support MMAP？

On 19 Nov 2017, at 6:15am, Howard Kapustein wrote: > On 10 Nov 2017, at 8:49am, advancenOO wrote: > >> hAve you optimised your column orders ? > What is optimal? SQLite reads only up to the last column it needs to execute the

<    6   7   8   9   10   11   12   13   14   15   >