Re: [sqlite] database locked on select

On 27 May 2018, at 11:49pm, Deon Brewis wrote: > By one connection doing SELECT and UPDATE, do you mean multi-threaded mode > and using the connection from 2 threads? A connection cannot lock the database against itself. If you are doing two operations with one connection, there can be no SQL

Re: [sqlite] database locked on select

On 27 May 2018, at 7:30pm, Torsten Curdt wrote: > I am doing a select, then iterate through the resultset and on each row > call update on that row. > I am using the golang driver and ran into the issue that on the update the > database is still locked from the select. Are you usihg one connecti

Re: [sqlite] ALTER TABLE

Just to explain to everyone why these commands are harder than they appear at first, consider ALTER TABLE DROP COLUMN . The problem is not in "deleting" the column of data . All you have to do for that is to rename the column something that can't be typed, and remove any constraints built into

Re: [sqlite] After update from 3.20 to 3.23 alter table throws error for boolean with default value FALSE

On 17 May 2018, at 8:17pm, Warren Young wrote: > On May 17, 2018, at 12:54 PM, Simon Slavin wrote: > >> [snip] doing it properly might be how SQLite 4 would start [snip] > > That sort reasoning gave us Python 3, which forked the Python community for > about a decade. I

Re: [sqlite] After update from 3.20 to 3.23 alter table throws error for boolean with default value FALSE

On 17 May 2018, at 7:13pm, Dominique Devienne wrote: > I think I'm not alone in wishing there was a way to disable all legacy > backward compatibility "warts". The testing involved is horrendous. You have to test everything with the setting enabled, then test it again with it disabled. If the

Re: [sqlite] After update from 3.20 to 3.23 alter table throws error for boolean with default value FALSE

On 17 May 2018, at 4:32pm, heribert wrote: > 'alter table Inbox add column WasSend boolean default FALSE' > [...] > > So i looked into the SQLite keyword list... but i didn't find neither FALSE > nor TRUE. So, why did the FALSE work with 3.20? It wasn't doing what you thought it was doing. SQ

Re: [sqlite] unique constraint

On 16 May 2018, at 12:25am, Mark Wagner wrote: > I'm wondering if > someone can explain why this simple test of unique column constraints > doesn't work. At least it doesn't work as I expected it would (i.e. that > the second insert would yield a unique constraint violation). > > create table t

Re: [sqlite] Weird query plan when sorting by a joined column

On 15 May 2018, at 11:12pm, Torsten Landschoff wrote: > when doing a join like > > select * from base b join derived d using (id) order by d.id > > the query plan is abysmal slower than when using > > select * from base b join derived d using (id) order by b.id > > I would have expected that

Re: [sqlite] question about DB

On 14 May 2018, at 3:03pm, sebastian bermudez wrote: > the question is, there are some order of penalty in attach (2+) databases vs > one big DB ? Three databases of 2.4Gb size ? SQLite is designed to expect this. Slow down for attaching compared with one big database is very very small. N

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

On 13 May 2018, at 4:57pm, Kevin O'Gorman wrote: > More importantly, it uses DDR4 memory which I think means there > are 4 channels to memory which can be used in parallel -- perhaps not on > exactly the same address but the memory is spread among 16 DIMMs. Suppose your different threads are rea

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

On 13 May 2018, at 11:50am, Techno Magos wrote: > So, memory sqlite is not really usable with multiple threads (readers). > While one might expect that multiple readers of *memory *content could > scale even better than with file content. > > Can this restriction be lifted? It's not a pointles

Re: [sqlite] Is there ever a perf advantage to LIMIT outside of a subquery?

On 11 May 2018, at 9:50pm, Deon Brewis wrote: > e.g. If you do: > > SELECT c1 from t1 ORDER BY c2 LIMIT 5; > > vs. just running the query without the "LIMIT" clause and taking the top 5 > rows programmatically? The LIMIT clause just tells sqlite3_step() to return SQLITE_DONE after the fifth

Re: [sqlite] This list is getting spammed again

On 11 May 2018, at 10:26am, Paul Sanderson wrote: > Would it be possible for an admin to run a script that sent an individual > email (e.g. different number in subject) to each user on the list and see > who is sending the spam based on the replies? My guess is that the spammer harvests our addr

Re: [sqlite] This list is getting spammed again

On 9 May 2018, at 9:37pm, Cecil Westerhof wrote: > ​I am bitten by it also now. I posted a question and within two minutes I > got a spam message​ I got three or four of these, each one soon after I'd posted a message. Then I got no more. I didn't do anything to stop them and I have checked

Re: [sqlite] "missing" sqlite3 invocation option?

On 9 May 2018, at 7:32pm, John McKown wrote: > The sqlite3 command has a input command to ".read" a file which "contains > SQL in FILENAME". I am wondering why there isn't an equivalent command line > argument to do this. That is, have something like: > > sqlite3 -f FILENAME database.db3 > > wh

Re: [sqlite] Database locking with online backup API

On 8 May 2018, at 1:12pm, Simon Slavin wrote: > You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN IMMEDIATE > depending on which you want. Then do all the backup stuff, then COMMIT or > ROLLBACK without having changed anything. On 9 May 2018, at 1:50am, Donald

Re: [sqlite] About storage of large amounts of image data

On 8 May 2018, at 2:08pm, Mike Clark wrote: > Since the prospective users will probably be > dealing with hundreds of gigabytes in their use of the project, I am > wondering if this is an effective or efficient use of SQLite -- or safe, > because of the risk of data corruption. I used to have a

Re: [sqlite] Getting statement column data types on Android

On 8 May 2018, at 5:37pm, Bart Smissaert wrote: >> SQLite does not have column types. It has column affinities instead. > > OK, so I would like to see that declared column affinity as that will > determine how to process the data. Ah. You don't care about the data, you want the column declara

Re: [sqlite] Getting statement column data types on Android

On 8 May 2018, at 4:19pm, Bart Smissaert wrote: > Just tested that (TypeOf) on the Android phone and it doesn't do what I > wanted. > I tested on a column declared Real but with text values in it as well. > It will give both real and text and what I wanted was to produce only real > as that > is

Re: [sqlite] Database locking with online backup API

On 8 May 2018, at 10:56am, R Smith wrote: > Thank you for clarifying - but it is still my understanding that the DB is > not locked (if only in WAL mode), so the backup API, even with -1, either > must ignore changes, or restart. My proposed flag is to lock rather than > restart or ignore. Yo

Re: [sqlite] Getting statement column data types on Android

On 7 May 2018, at 10:49pm, Bart Smissaert wrote: > Using B4A for a SQLite database app on an Android phone. > B4A doesn't have functions like sqlite3_column_decltype and > sqlite3_column_type > and this is causing some difficulty getting the column datatypes of a row > producing statement. See

[sqlite] Always call a value-quoting routine

This is a genuine company registered under the UK Companies Act: The name of company is ; DROP TABLE "COMPANIES";-- LTD (Note: For legal reasons a UK company name must end in 'LTD' or 'plc', depending on the type of company it is.) Simon.

Re: [sqlite] How to Handle BigInt

On 2 May 2018, at 6:08pm, Thomas Kurz wrote: > Are there any plans for supporting a true BigInt/HugeInt data type (i.e. > without any length restriction) in the near future? The largest integer storable as an integer is current 2^63-1, which is the value as signed BigInt in many libraries. I

Re: [sqlite] How to Handle BigInt

On 2 May 2018, at 5:22pm, dmp wrote: > Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL > with getString(), handles all, then using Integer.parseInt(stringValue) for > BigInts in storing to SQLite. > > There lies the problem since BigInt values were exceeding the rang

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 1 May 2018, at 6:43pm, Peter Da Silva wrote: > CSV is an interchange format, it's for software to communicate with other > software, so the syntax needs to be independent of the locale since you don't > know if the sender and recipient are in the same locale. Field separator is > syntax, so

Re: [sqlite] probably recursive?

On 1 May 2018, at 6:28pm, Simon Slavin wrote: > I just realised that That was intended to be personal email. Apologies, everyone. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-

Re: [sqlite] probably recursive?

On 1 May 2018, at 1:45am, Roman Fleysher wrote: > If x=10 has less than nX dots, all dots with x=10 are deleted. Because of > deletion, y=3 which previously had more than nY dots no longer passes the > threshold and thus y=3 must be deleted too. This could cause deletion of some > other x, etc

Re: [sqlite] probably recursive?

On 1 May 2018, at 5:34pm, Roman Fleysher wrote: > With recursive route, I am thinking I need to build deleteList(x,y). Rather than actually delete rows, if you can, insert a new column in the table of all points. It starts with every row set to TRUE. When you decide a row doesn't count the v

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 1 May 2018, at 3:01pm, Olivier Mascia wrote: > My question was more generic, even though it didn't look that way: the > well-known and (maybe too) much-used software tool named Excel tend to > encourage people to export "CSV" files which are actually "SCSV" files > (semi-colon separated val

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 1 May 2018, at 11:11am, Olivier Mascia wrote: > CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); > > Is there any way to teach the csv extension to use ';' instead of ',' as the > column delimiter, getting away from the strict RFC4180 definition? The source code for the csv e

Re: [sqlite] probably recursive?

On 1 May 2018, at 1:45am, Roman Fleysher wrote: > If x=10 has less than nX dots, all dots with x=10 are deleted. Because of > deletion, y=3 which previously had more than nY dots no longer passes the > threshold and thus y=3 must be deleted too. This could cause deletion of some > other x, etc

[sqlite] Joel Spolsky on what makes an acceptable software question

_Stack Overflow_ is a little like this list, and Joel Spolsky, CEO of _Stack Overflow_, considers what makes an acceptable question for _Stack Overflow_ here: Just for fun I'm listing ten common types of question I see h

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

On 19 Apr 2018, at 1:27pm, MARCHAND Loïc wrote: > CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID)) > > I read a file, and for each line I add a row on the table. Each line can have > binary data at end. It's not a problem for many chars, but \0 char make a > problem. > I

Re: [sqlite] Are you getting spam when you post to sqlite-users ?

On 18 Apr 2018, at 11:19pm, dave wrote: > Wouldn't it be as simple as subscribing to the mailing list and harvesting > the emails directly from the inbound content? No need to subscribe. This mailing list is gated to web fora. Just scrape some web pages. I don't think there's a solution shor

Re: [sqlite] crash dropping table

On 18 Apr 2018, at 5:59pm, Mark Wagner wrote: > I will try to produce a repro case that I can share out (the database I was > testing on has lots of personal data that I will first try to delete). Before you do anything else to it, make a copy and use the SQLite shell to do PRAGMA integri

Re: [sqlite] crash dropping table

On 18 Apr 2018, at 4:47pm, Mark Wagner wrote: > I have a simple test case wherein I delete from a number of tables and then > drop one of those tables. This crashes sqlite3. > > So something like this: > > begin; > delete from x; > delete from y; > delete from z; > drop x; << crashes here >

Re: [sqlite] Are you getting spam when you post to sqlite-users?

On 17 Apr 2018, at 11:11pm, Rich Shepard wrote: > FWIW, I haven't seen any spam from this mail list. I do run my own MTA > which aggressively rejects known spam. I won't give details in public but I will say that one or more characteristics of the spam messages appear in the URIBL blacklist. I

Re: [sqlite] Are you getting spam when you post to sqlite-users ?

On 17 Apr 2018, at 11:00pm, Warren Young wrote: > Yes. My recent reply to the header corruption thread triggered one. I got a couple of direct email replies too, with enough information for me to isolate the offending mail server. Okay, that's enough. Thanks for the help, everyone. Can the

[sqlite] Are you getting spam when you post to sqlite-users ?

Dear list-posters, Are you getting a new dating-spam each time you post to this list ? If you are, please post a brief follow-up to this message. Please do /not/ include any details about the spam, its headers, or the person it's apparently from. Just a "me too" until I say I have seen enoug

Re: [sqlite] Header corruption

On 17 Apr 2018, at 9:13pm, Peter Da Silva wrote: > Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already > closed still have a risk of unexpected behavior? Under Windows an attempting to shutdown a connection which is already shutdown returns SOCKET_ERROR . You can then ma

Re: [sqlite] kooky thought: a vm-only build (for embedded). feasible?

On 15 Apr 2018, at 7:54pm, dave wrote: > I wonder if I can really strip it down > further by eliminating parsing, query planning, etc, altogether, and only > support the virtual machine. I wonder what you would find if you looked through the data structure of sqlite3_stmt. Presumably the com

Re: [sqlite] Resetting autoincrement

> On 13 Apr 2018, at 11:16 pm, Mike Clark wrote: > > DELETE FROM your_table;DELETE FROM sqlite_sequence WHERE name = > 'your_table'; > > > But when I try to run it I get "table not found". Has this been superseded? No. What is the primary key on that table ? If you declared is in the CRE

Re: [sqlite] feature request: MIN() and MAX() of set of row values

On 13 Apr 2018, at 8:40am, Mark Brand wrote: > It also occurs to me that COUNT() should work (but doesn't) over sets of row > values: > > sqlite> select count((1,2)); > Error: row value misused > > I would expect it to return the number of non-NULL row values in the set. What should t

Re: [sqlite] memory leak?

On 12 Apr 2018, at 8:00pm, Warren Young wrote: > Also: http://valgrind.org/docs/manual/faq.html#faq.pronounce I didn't know that. So they want it to be pronounced like 'lint'. Interesting. Simon. ___ sqlite-users mailing list sqlite-users@mailinglis

Re: [sqlite] To use or not to use single quotes with integers

On 12 Apr 2018, at 7:49pm, Thomas Kurz wrote: > [Simon Slavin wrote] > >> A similar thing happens when you specify that a column has affinity of REAL. >> In both cases, SQLite considers that the CREATE command knows better than >> whatever specifies the value,

Re: [sqlite] SELECT with CASE

On 12 Apr 2018, at 5:16pm, R Smith wrote: > SELECT MIN(TheDate) -- get the smallest date > FROM Orak -- from the table with School-days > WHERE TheDate >= date('now') -- where the school-day is later or equal to > today. > ; This reflects exactly the right s

Re: [sqlite] SELECT with CASE

On 12 Apr 2018, at 1:25pm, Csányi Pál wrote: > SELECT CASE TheDate = date('now') WHEN TheDate ... I don't think that's what you wanted. Perhaps SELECT CASE TheDate WHEN date('now') ... But you should test the output of "date('now')" to make sure it is in the format you want. Simon.

Re: [sqlite] Constraints must be defined last?

On 11 Apr 2018, at 6:41pm, J Decker wrote: > Is there something about SQL that requires constraints to follow all column > definitions? I don't know if it applies to SQL in general, but it is in SQLite: The table constraints come after the col

Re: [sqlite] UPPER function depends on Locale?

On 10 Apr 2018, at 7:44am, Dr. Mucibirahman İLBUĞA wrote: > I my opinion, there should be well documented, step by step information about > how to add ICU support in SQLite. I think that the reason this doesn't exist is that different people want to use different compilers. But it would be n

Re: [sqlite] UPPER function depends on Locale?

On 9 Apr 2018, at 8:49pm, sandu wrote: > I am also curious about how can someone define the locale parameter for the > LOWER/UPPER functions? See section 1.1 of Simon. ___ s

Re: [sqlite] In memory only WAL file

On 9 Apr 2018, at 8:41am, Pavel Cernohorsky wrote: > if you use bigger pro-level SSD, you get on a bit less that 1 year.. still > not good enough. Of course, big unknown in this is the constant representing > the ability of the SSD controller to merge requests and minimize block > re-writes.

Re: [sqlite] Documentation update

On 9 Apr 2018, at 11:02am, R Smith wrote: > If you prefer "different from" to "different to", it is simply likely that > you are American rather than English, in which language the preference (I > should say "habit" really) is "different to" rather than "different from", > but I gather from

Re: [sqlite] Documentation update

On 9 Apr 2018, at 1:08am, Don V Nielsen wrote: > "For the purposes of unique indices, all NULL values are considered [to] > different from all other NULL values and are thus unique." > > I think it should read "...NULL value are considered different..." > > https://www.sqlite.org/lang_createind

Re: [sqlite] UPPER function depends on Locale?

On 8 Apr 2018, at 10:51pm, mucip.ilb...@gmail.com wrote: > I develop project on QT/C++ and compile both Linux and Windows platforms. > In this case, should I recompile SQLite both of these platforms?! Should I > make some modifications on QT too?! I think you will need to do all these things. I

Re: [sqlite] UPPER function depends on Locale?

On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA wrote: > As you see above table it returns incorrect. Only i>>I is working like in > English language?! When I use this code in PostgreSQL it works correct. But I > got unexpected result in SQLite unfortunatelly. Thank you for your examples, w

Re: [sqlite] UPPER function depends on Locale?

On 8 Apr 2018, at 12:50pm, Dr. Mucibirahman İLBUĞA wrote: > The UPPER function is not working properly in Turkish language?! How can I > get this? > > For example small "i" is equal to big "I" in Turkish language. But it's not > working properly?! Can you paste into a reply to this message

Re: [sqlite] To use or not to use single quotes with integers

On 8 Apr 2018, at 11:54am, Markos wrote: > CREATE TABLE foods( > id integer PRIMARY KEY, > type_id integer, > name text ); > > I can insert type_id without single quote: > > INSERT INTO foods (name, type_id) VALUES ('Rice', 16); > > And also with single quote: > > INSERT INTO foods (name,

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

On 6 Apr 2018, at 8:58pm, R Smith wrote: > my guess is Mr. Thomas inherited it from someone else who did not fully know > what they wanted to achieve, then googled a solution and found a hit on an > old stackoverflow question that was sort-of like what they wanted, but not > exactly. [snip]

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

On 5 Apr 2018, at 11:41am, DThomas wrote: > Select DISTINCT Sites.Customer, Sites.Digit, > Count(TblContractTasks.TaskNumber) > OVER (PARTITION BY Sites.Digit) As TaskCount > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On > Sites.Digit = TblContractDetails.SiteDigit)

Re: [sqlite] In memory only WAL file

On 4 Apr 2018, at 3:01pm, Pavel Cernohorsky wrote: > Hello, does anybody know if there is some possibility to not have WAL file as > a normal file on the disk, but only in memory? I understand that all the > modifications to the database would get lost in case of the application / OS > crash,

Re: [sqlite] sqlite3_column_decltype and affinity

On 1 Apr 2018, at 8:19am, Eric Grange wrote: > The decl_type allows to differentiate a datetime, or a "wide" text from a > single char text column The reason descl_type does not work on expressions is the reason I illustrated in my previous post: the expression can have a different type for dif

Re: [sqlite] Select query does not give new added records

On 31 Mar 2018, at 2:04pm, Koen Amant wrote: > there is a service running in the background who adds records > to the database (POS system) I can't stop this service and all the new > records that are added I can't see in my query result. It's like the > database is locked for other users and so

Re: [sqlite] MIN() and MAX() of set of row values

On 30 Mar 2018, at 6:04pm, Peter Da Silva wrote: > On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" > slav...@bigfraud.org> wrote: >> can think of to do it is to devise a metric to turn a pair (a, b) into one >> number. > > Problem is you

Re: [sqlite] MIN() and MAX() of set of row values

On 30 Mar 2018, at 5:55pm, Igor Tandetnik wrote: > I don't think that's what the OP had in mind. They didn't want the smaller of > a and b for each row, but rather the lexicographically smallest (a, b) pair > among all rows. > > Row values support less-than comparison, so it kind of makes sens

Re: [sqlite] MIN() and MAX() of set of row values

On 30 Mar 2018, at 3:48pm, Mark Brand wrote: > SELECT MIN((a,b)) = (1, 2) ok FROM T; > --Error: near line 4: row value misused > > SELECT MIN((SELECT a, b)) = (1, 2) ok FROM T; > --Error: near line 7: row value misused > > SELECT (1, 2) = MIN(SELECT a, b FROM T); > --Error: near line 10: near

Re: [sqlite] sqlite3_column_decltype and affinity

On 30 Mar 2018, at 11:22am, Eric Grange wrote: > Is there a way to have sqlite3_column_decltype return the affinity for an > expression ? You may be referring to sqlite3_column_type() which can be applied to columns returned by a query even if that column is an expression. But if you wan

Re: [sqlite] .dump command and user_version

On 29 Mar 2018, at 3:10pm, Scott Robison wrote: > Yet as he said, user_version is data. It can be queried and can be > checked to make decisions about what to do with the data. It seems a > reasonable to suggestion to add it. On consideration, I see the point. Would you want to dump PRAGMA s

Re: [sqlite] .dump command and user_version

On 29 Mar 2018, at 1:47pm, Wout Mertens wrote: > I noticed that `.dump` does not output the user_version pragma. It seems to > me that that is part of the database data? > > I don't actually use it, but it might be interesting to add this for > completeness? .dump is for things which affect d

Re: [sqlite] what is server-process-edition?

On 26 Mar 2018, at 9:26am, Olivier Mascia wrote: > Simon, if this discussion is really around the branch > 'server-process-edition', it was my (possibly wrong) understanding that this > is not really true. This branch does apply page-level locking, or I got it > all wrong. Whoops. Thanks for

Re: [sqlite] what is server-process-edition?

On 26 Mar 2018, at 8:57am, Marco Bambini wrote: > So it has nothing to do with which table/row the transaction is modifying? Correct. SQLite does not have table-locking or row-locking. Any locks in a SQLite database lock the entire database. This is a fundamental aspect of SQLite and one of

Re: [sqlite] what is server-process-edition?

On 26 Mar 2018, at 8:09am, Marco Bambini wrote: > Is there a better formal description about the "transactions may not overlap" > sentence? > Is there any example about overlapping transactions? Overlapping transactions occur when a second connection does a BEGIN before the first connection do

Re: [sqlite] Determine sort order of query

On 24 Mar 2018, at 6:00pm, x wrote: > Thanks for the reply Richard. I don’t suppose it matters to me that it may > change from one day to the next or one connection to another as long as I can > determine the order it will be returned in milliseconds before the first > sqlite3_step. Can't be

Re: [sqlite] The upcoming 3.23.0 release

On 23 Mar 2018, at 3:09pm, R Smith wrote: > select blue, one from foo where one is blue or blue is true; > -- blue | one > -- | > -- 1 | 1 > > Is the blue one true? or the true one blue? or is this the one true blue in > the foo? Yo

Re: [sqlite] Index on expression goes corrupt from valid SQL

On 23 Mar 2018, at 3:02am, Deon Brewis wrote: > PS: I did send an example corrupted file to Richard - I can send it to you as > well if you like? Richard will find anything there is to find. I do hope the problem gets solved since you seem to have put so much work into it and done everything

Re: [sqlite] Index on expression goes corrupt from valid SQL

On 23 Mar 2018, at 12:54am, Deon Brewis wrote: > However, what we see doesn't generally exhibit like the bug describes. The > bug as reported gives errors like this: > "row 1 missing from index idx1" > > Where we instead see things like: > "database disk image is malformed" > > Can it still be

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

On 22 Mar 2018, at 10:16pm, Mark Wagner wrote: > Curious about the suggestion of adding + to the order by first term. This stops SQLite from realising it can use an existing index. If you do CREATE INDEX m_s ON members (score) SELECT * FROM members ORDER BY score DESC SQLite cleverly

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

On 22 Mar 2018, at 10:09pm, Jonathan Moules wrote: > Sure; I didn't include them because the only difference is the last line, and > that just seems to be the standard "ordering" line. I figured the explain was > more useful as a lot has changed in that. I find EXPLAIN QUERY PLANs easier to r

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

On 22 Mar 2018, at 9:24pm, Jonathan Moules wrote: > But when I stick an "ORDER BY" on the end (either ASC or DESC), the > processing time shoots up to 0.15s. The EXPLAIN between the two is > considerably different so it seems the ORDER BY is getting it to use a > sub-optimal query plan. Hi, J

Re: [sqlite] The upcoming 3.23.0 release

On 22 Mar 2018, at 7:09pm, Richard Hipp wrote: > For a summary of changes see > https://sqlite.org/draft/releaselog/3_23_0.html "causes the database connection D to disconnection from database S"

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 10:58pm, Jonathan Moules wrote: > I know SQLite has a CLI tool, but I'm happier with front-ends You can use the CLI tool (which does still work under Windows 7) to open the database you prepared in your preferred environment and execute just the statement you're interested

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 6:17pm, Jonathan Moules wrote: > Simon - I suspected the ORDER BY thing was wrong but wanted to check first > rather than simply come out with "SQLite is broken!". This may be related to > the 3.22 regression I brought up a couple of days ago (and why I'm using > 3.15) - pro

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 5:39pm, Jonathan Moules wrote: > After a couple of hours of investigation, it's only slow when there is either > no ORDER BY, or if I use DESC (which is what I need). If I use ORDER BY u.url > ASC - it's near instantaneous. You have a query which gets faster when you add an

Re: [sqlite] How to optimise a somewhat-recursive query?

On 18 Mar 2018, at 11:13pm, Jonathan Moules wrote: > Given there's such a small amount of data at this point, I suspect the issue > is more related to the recursion. I've tried creating these two indexes to > facilicate that Nice idea but I can see why it's not working. You have an underlyin

Re: [sqlite] possible bug: select clause column alias shadowing

On 18 Mar 2018, at 9:31am, Moritz Bruder wrote: > I'm not exactly sure what the SQL standard says As best I can find, SQL92 does not specify what happens when you choose an AS clause giving a value name the same as a column. It doesn't go into much detail at all about applying "AS" to a valu

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 When needed I use a declared INTEGER PRIMARY KEY. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

On 16 Mar 2018, at 12:43pm, Robert M. Münch wrote: > On 16 Mar 2018, at 11:35, Simon Slavin wrote: > >> You can use INSERT OR IGNORE for this. First, do an INSERT OR IGNORE >> command which will add a dummy entry, with the right key, if one doesn't >> alread

Re: [sqlite] UPSERT

On 16 Mar 2018, at 10:24am, Robert M. Münch wrote: > for every column I don’t want to change I need to add a sub-select statement. > If I need to build this statement dynamically, IMO it would be better to > handle this code directly in code: > > if(record-exists?){ > UPDATE … > } else

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

On 16 Mar 2018, at 8:38am, Wout Mertens wrote: > It seems to me that the only real option is to maintain a derived table, > the table could even be R*TREE to allow range queries. You can always do range queries on tables in SQLite. Not sure what you mean by the above. Simon. _

[sqlite] Issues about SQLite archive files

One important security issue and several unimportant ones. Security issue: The built-in path functions of some operating systems interpret file paths beginning with a path separator as relative to the root of the drive rather than the current subdirect

Re: [sqlite] Boolean casting

On 15 Mar 2018, at 7:04pm, Jens Alfke wrote: > it would be nice if boolean could be elevated to a data type Agreed. And I think it could be done without breaking backward compatibility [1], just by implementing the string 'BOOLEAN' as a type. The value stored can be encoded the same as integ

Re: [sqlite] Boolean casting

On 15 Mar 2018, at 4:43pm, Pavan Paolo wrote: > I use in a view the following snip of code to force the results to be > considered as Boolean: SQLite does not have a BOOLEAN type. If you use the word BOOLEAN where it expects to see a type name it will use an affinity of NUMBER. See sections

Re: [sqlite] Function design question

On 15 Mar 2018, at 11:40am, Toby Dickenson wrote: > But I also want to update these tables from the command line, when > these functions wont be available. Is there a way to construct the > trigger so that I get NULLs instead of errors when a custom function > doesnt exist? Or a better way to p

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

On 11 Mar 2018, at 12:05pm, Csányi Pál wrote: > Columns are: id,date,D,lb,g,c,lp,ld,re > > where D is a Day name in Week, > lb is the number of the Lesson Block hour, > g is the grade of a school class, > c is the name of the school class, > lp is LessonPlan, > ld is LessonDiary, > re is Reminde

Re: [sqlite] Is it a bug or "as designed"?

On 10 Mar 2018, at 7:15am, John Found wrote: > Simon Slavin wrote: > >> On 9 Mar 2018, at 7:49pm, John Found wrote: >> >>> In the current implementation "insert or replace" behave as the foreign >>> constraint is deferred. >>> But

Re: [sqlite] Is it a bug or "as designed"?

On 9 Mar 2018, at 7:49pm, John Found wrote: > In the current implementation "insert or replace" behave as the foreign > constraint is deferred. > But according to documentation, all foreign constraints in SQLite are > immediate by default. John, The documentation suggests that in SQLite forei

Re: [sqlite] Is it a bug or "as designed"?

On 9 Mar 2018, at 7:11pm, John Found wrote: > "insert or replace" succeed without deleting the old rows from B. "replace" means "delete the original row, then insert a new one". In your code, figure out whether you need INSERT or UPDATE, and do the appropriate one. Simon.

Re: [sqlite] Help getting started

On 9 Mar 2018, at 4:42pm, Richard Hipp wrote: > On 3/9/18, Larry Mullings wrote: >> I have a SQLite Bible database. It has >> Bible verses with Strong's numbers and Hebrew. > > Are you willing to share your database? Can you send me a copy via > private email attachment? Anyone interested in

Re: [sqlite] Help getting started

On 9 Mar 2018, at 3:48pm, Larry Mullings wrote: > I'm in need of some serious help. I'm a first timer to anything database. I > have a SQLite Bible database. It has > Bible verses with Strong's numbers and Hebrew. I'd like to add some fields > to the database. I downloaded > sqlite-amalgam

Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

On 9 Mar 2018, at 4:09am, sanhua.zh wrote: > Here is the sample SQL I tried: > ``` > CREATE TABLE t(i INTEGER); > INSERT INTO t VALUES(1); > WITH cte AS(SELECT i FROM t) > UPDATE cte SET i = 0; // Failed with "Error: no such table: cte" > ``` > > > Note that the sample WITH CLAUSE make no sense

Re: [sqlite] String Too Long

On 1 Mar 2018, at 7:42pm, Matías Badin wrote: > The string is over 55000 characters and i'm using SQLiteCommand in C# Since that's less than a million, whatever limit you're hitting isn't part of SQLite, it's part of one of 1) Operating System 2) SQLite shim library 3) Programming language

<    4   5   6   7   8   9   10   11   12   13   >