Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

for a couple of years, and has worked well. See the second link above for more information. -- D. Richard Hipp d...@sqlite.org ___ -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Luuk

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

On 24-3-2020 09:04, Luuk wrote: "The mailing list is deprecated. You need to go to https://sqlite.org/forum/ for the sqlite forum." Can anyone give the source of this? No, i do NOT mean the source of the forum, but the source for "The list

[sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

"The mailing list is deprecated. You need to go to https://sqlite.org/forum/ for the sqlite forum." Can anyone give the source of this? On 23-3-2020 14:35, Hick Gunter wrote: The mailing list is deprecated. You need to go tohttps://sqlite.org/forum/ for the sqlite forum.

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

script: #!/bin/bash if [ ! -f test.db ] ; then sqlite3 test.db "CREATE VIRTUAL TABLE tab USING fts5(x)"; fi sqlite3 test.db ".import wikipedia tab" a=$(sqlite3 test.db "SELECT count(*) FROM tab_data") echo "# records after import: $a" sqlite3 test.db "DELETE FROM tab" a=$(sqlite3 test.db

Re: [sqlite] Performance Issue on Large Table

of unchanged data, which is inserted later on (only because `Comments` did change?) -- Luuk ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOTNULL

On 8-1-2020 12:15, R Smith wrote: I find the keyword NOTNULL listed among known SQLite keywords - no. 88 on this page: https://sqlite.org/lang_keywords.html But cannot find a single mention of it or place to use it in SQLite, nor get any hit on the sqlite.org search except in reference to the

Re: [sqlite] AVG Function HowTo

On 28-12-2019 13:07, Csanyi Pal wrote: Hi, I have attached here the exported sql file of my little sqlite database. I am a newbe in the sqlite language so I ask here only an advice in which direction to go, eg. which functions of sqlite language to use to achieve my goal? SQList is able

Re: [sqlite] OT!!!!! Understanding the WITH clause

On 20-6-2019 08:11, Michael Falconer wrote: SELECT peace FROM disaster WHERE disaster.cause = 'Windows'; Returns: *nix The query should be SELECT peace FROM disaster WHERE (disaster.cause LIKE '%Windows%' OR disaster.cause LIKE '%nix%' OR disaster.cause LIKE '%incompetence%'

Re: [sqlite] Understanding the WITH clause

On 16-6-2019 19:32, E.Pasma wrote: Message to Luuk: thanks for explaining Pax vobiscum. E. Pasma Thanks for the recursive CTE. Luuk ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman

Re: [sqlite] Understanding the WITH clause

On 15-6-2019 18:24, Sam Carleton wrote: Pax vobiscum, Sam Carleton I had to pull up a dictionary to know what Pax (=Peace) you are talking about.. (https://en.wikipedia.org/wiki/Pax_(liturgy)) "In Christian liturgy

Re: [sqlite] How to insert the BLOB in database?

On 10-6-2019 05:08, Mark Halegua wrote: On Monday, June 10, 2019 03:46:02 AM Simon Slavin wrote: On 10 Jun 2019, at 3:44am, Mark Halegua wrote: I probably should figure this out, but in a GUI, how do I recover a graphic from the database? Programming. SQLite can't do it since it doesn't

Re: [sqlite] Failing SQLite .import returns exit status 0

On 8-6-2019 15:26, Simon Slavin wrote: Use .bail ON If the shell bails out because of an error, it should be setting the exit code to indicate an error. unfortunatly still 0 is returned  (sqlite3 version 3.28.0) ~/temp> cat empty.sh #!/bin/bash rm -f empty.db empty.csv echo "" >

Re: [sqlite] How to set access permissions to protect a database file?

or write to the file books.db (only the program reading_room.tcl) The standard Unix permissions/ACLs architecture doesn't support this use case directly. Can you give some more information on this, because it seems to work as i excpect it to: Database is 'owned' by user 'luuk', trying

Re: [sqlite] How to set access permissions to protect a database file?

On 26-5-2019 01:49, Markos wrote: Hi, I made a program (reading_room.tcl), with Sqlite running on Debian 9, to control the books of a reading room. I implemented an authentication system for common users and administrator users in the reading_room.tcl program. Now I want that any user

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

On 13-5-2019 20:06, David Raymond wrote: What version are you using? Because it seems to be working fine for me on 3.28.0 Windows CLI. D:\TEMP>sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

On 13-5-2019 19:36, Manuel Rigger wrote: Hi everyone, Consider the following test case: CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID; INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5); REINDEX; SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1

Re: [sqlite] unsubscribe

should that no be below?  On 13-5-2019 11:21, Tim Streater wrote: On 13 May 2019, at 09:19, Christof Arnosti wrote: http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Do that yourself at the link above. ___ sqlite-users

Re: [sqlite] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601

On 9-5-2019 18:20, Nißl Reinhard wrote: Hi, it would be nice, if sqlite3's strftime() would support the following formatting codes: %g The last 2 digits of the ISO 8601 week-based year as a decimal number (00 - 99) %G The ISO 8601 week-based year as a decimal number %V ISO

Re: [sqlite] Getting the week of the month from strftime or date functions

On 4-5-2019 15:21, Petite Abeille wrote: On May 4, 2019, at 12:47, Luuk wrote: As others have noted, it's a question of definition, and which definition do you follow? What about just sticking with the ISO week definition? https://en.wikipedia.org/wiki/ISO_week_date

Re: [sqlite] Getting the week of the month from strftime or date functions

On 2-5-2019 22:17, Jose Isaias Cabrera wrote: I found this very interesting, 15:52:46.71>sqlite3 SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT

Re: [sqlite] Row values with IN

On 23-4-2019 18:08, Simon Slavin wrote: On 23 Apr 2019, at 4:14pm, Charles Leifer wrote: SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1)); Perhaps the row-value doc could clarify the behavior of IN with row values? This ((2019, 1, 1), (2019, 2, 1)) This works:

Re: [sqlite] SQLite error while fetching the data from a table

On 22-4-2019 14:03, Ananta Jena wrote: Hi All, Need quick help to resolve one issue i am getting now. I am a new user of SQLite. my code: connection = DriverManager.getConnection("jdbc:sqlite:C:\\sqllite\\sqlite-tools-win32-x86-328\\Stories.db"); Statement st =

Re: [sqlite] Table names starting with sqlite (not sqlite_)

On 19-4-2019 20:53, Semih Hazar wrote: The bug was introduced in version 3.25.0. It was due to a LIKE pattern attempting to match against "sqlite_%%" except "_" means "match any single character to to LIKE operator, so we had to go back in and escape that character. The problem is fixed on

Re: [sqlite] Table names starting with sqlite (not sqlite_)

On 19-4-2019 16:19, Semih Hazar wrote: Hi, With 3.28.0 I'm getting an inconsistent behavior if I try to rename a table if its name starts with "sqlite". It's not "sqlite3_" by the way, which just gives an error since it's a reserved keyword. After renaming, .table command shows the new name,

Re: [sqlite] Inserting the same column multiple times

On 14-4-2019 09:36, Shawn Wagner wrote: Discovered this tonight answering a question on stack overflow: sqlite> create table foo(a, b); sqlite> insert into foo(a,b,a,b) values(1,2,3,4); sqlite> select * from foo; a b -- -- 1 2 Inserting a column multiple

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

On 10-4-2019 10:28, Kees Nuyt wrote: On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: I have not used extensions before. I understand that some are included in the amalgamation source file and that some of these are enabled by default. So, which ones are built-in and which of those are enabled

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

On 10-4-2019 05:17, John McMahon wrote: which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI Some possibility tho show this like (i.e.) '.extensions' would be very nice!? ___ sqlite-users

Re: [sqlite] Help with INDEXing a query

On 3-4-2019 19:34, Jose Isaias Cabrera wrote: Never mind, guys. I was missing the INDEX for the table for the first left join: CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID); Everything is nice, now. Thanks. From: Jose Isaias Cabrera Sent: Wednesday, April 3, 2019 01:02 PM To:

Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)

NSERT INTO test VALUES(1);" ~$ ~$ touch /tmp/test.init ~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 ~$ ~$ ~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null ~$ ls -l /tmp total 16 -rw-r--r-- 1 luuk luuk 8192 Mar 30 17:

Re: [sqlite] Clear sqlite3 terminal enhancement

On 28-3-2019 14:42, Dominique Devienne wrote: On Thu, Mar 28, 2019 at 1:16 PM Clemens Ladisch wrote: Jeffrey Walton wrote: When working in the Linux terminal we can clear the scrollback with the 'clear' command; and we can delete all history and scrollback with the 'reset' command. I am not

[sqlite] where did my data go ? Re: Import data into a temporary table

On 7-3-2019 22:45, Eric Tsau wrote: Hi, Is it possible to add the option of importing data into a temporary table? Currently you have to create a temporary table first before importing to it, or having to drop the table afterwards. .import dump.csv temp.table or .import dump.csv attach.table

Re: [sqlite] Handling ROLLBACK

On 3-3-2019 16:27, Jesse Rittner wrote: It didn't work correctly because the sequence of commands you sent doesn't make sense. BEGIN; ... RELEASE point1; ... ROLLBACK; ... END; First you began an explicit transaction. Then you tried to release a savepoint that you never created. (Hence the

Re: [sqlite] Handling ROLLBACK

On 3-3-2019 15:33, Simon Slavin wrote: On 3 Mar 2019, at 2:29pm, Luuk wrote: Conclusion: RESTORE does not end TRANSACTION ? Your statement is correct. However, RESTORE is a partner of SAVEPOINT. My question does not consider SAVEPOINTs. Simon

Re: [sqlite] Handling ROLLBACK

On 3-3-2019 15:01, Richard Damon wrote: On Mar 3, 2019, at 8:32 AM, Simon Slavin wrote: To summarize, the list feels that this is an incorrect model BEGIN; ... first set of commands ROLLBACK; ... second set of commands END; whereas this is how things are meant to work:

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 18-2-2019 14:51, Simon Slavin wrote: On 18 Feb 2019, at 1:33pm, Rocky Ji wrote: Here's the new thing: https://pastebin.com/raw/pSqjvJdZ Again, can we get rid of them sub-query? The "sum()" with the "group by" in the subquery make it difficult. There's nothing wrong with that query just

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 17-2-2019 17:46, Rocky Ji wrote: Hello everyone, How can I prevent group by clause from reducing the number of rows without affecting accuracy of what aggregate functions provide? Scenario: My club has-many coaches. Each coach trains a team of players. Of course, a player has-many matches

Re: [sqlite] Min/Max and skip-scan optimizations

On 4-2-2019 14:55, Gerlando Falauto wrote: Thank you Luuk, I understand your point. However, the query plan already takes advantage of the index and should be retrieving data in that order. Reading the docs https://www.sqlite.org/optoverview.html#order_by_optimizations my understanding

Re: [sqlite] Min/Max and skip-scan optimizations

On 3-2-2019 23:29, Gerlando Falauto wrote: IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should ideally yield the exact same query plan. In the end adding an ORDER BY clause on the exact same columns of the index used to traverse the table, should be easily recognizable.

Re: [sqlite] Variable Declaration

On 19-1-2019 14:49, Luuk wrote: This question is not about: 'parameter binding'! It's about 'variable decalaration'... oops 'variable declaration' ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org

Re: [sqlite] Variable Declaration

On 19-1-2019 14:23, Jesse Rittner wrote: What language do you want to use? Parameter binding is a feature of the SQLite C API. So pretty much any language with a C FFI will suffice. There's a list on Wikipedia, not sure how authoritative it is.

Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

On 6-1-2019 14:11, Daniel Kraft wrote: Since it seems that the mailing list swallowed my attached example code, I've put it on Github as well: https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1 The example code (from github.com), so no-one has to go-to github: #include

Re: [sqlite] i Know i should use 'AS', but ....

On 26-12-2018 08:20, Clemens Ladisch wrote: Luuk wrote: sqlite> .mode column sqlite> .headers on sqlite> select 1 as X,date() as d union all select 2,date() union all select 3,datetime(); X   d --  -- 1   2018-12-25 2   2018-12-25 3   2

Re: [sqlite] [OT] Re: i Know i should use 'AS', but ....

On 24-12-2018 21:25, Shawn Wagner wrote: Using '.mode column" in conjunction with ".headers on" you're already using makes it a lot more obvious. like this: SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open

[sqlite] [OT] Re: i Know i should use 'AS', but ....

On 24-12-2018 19:21, Peter Johnson wrote: The headers are present in all three queries you pasted. The first result shows two rows, the top row is the header. The other two results show 4 rows each, the top row of each is the header row. -P On Tue, 25 Dec 2018, 3:42 AM Luuk sqlite> .vers

[sqlite] i Know i should use 'AS', but ....

sqlite> .version SQLite 3.26.0 sqlite> .headers on sqlite> select 1 as X,date(); X|date() 1|2018-12-24 sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x union all select 2 union all select 3); x|row_number() over (order by 1 desc) 3|1 2|2 1|3 Why are the headers

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 14-12-2018 16:54, Keith Medcalf wrote: You get the message about missing collating sequences because the database is using collating sequences that you don't have (ie, are missing) in your version of SQLite3. Extensioon in SQLite, thats somewhere on my TODO list ;) Is there any

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 14-12-2018 11:22, Simon Slavin wrote: On 14 Dec 2018, at 10:15am, Luuk wrote: why do i get 'no collation sequence' with this statement: sqlite> select Folder_Path from Folder; Error: no such collation sequence: NoCaseUnicode There's a UNIQUE index for Folder_Path COLLATE NoCaseUnic

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 14-12-2018 11:15, Luuk wrote: On 13-12-2018 18:26, Simon Slavin wrote: On 13 Dec 2018, at 2:57pm, Carlo capaldo wrote: UPDATE folder     SET Folder_Path = 'E:\Photos' would change the directory references in all existing 16 rows currently containing Folder_Path references

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 13-12-2018 18:26, Simon Slavin wrote: On 13 Dec 2018, at 2:57pm, Carlo capaldo wrote: UPDATE folder SET Folder_Path = 'E:\Photos' would change the directory references in all existing 16 rows currently containing Folder_Path references to the wrong locations to the correct

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

On 11-12-2018 10:09, Wout Mertens wrote: Hi Luuk, Not sure if you realize this, but your email comes over as very aggressive, and if there's one person on this mailing list that doesn't deserve that, it's dr Hipp. In particular, the quotes around forgot seem to imply that it was forgotten

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

On 8-12-2018 23:47, Richard Hipp wrote: On 12/8/18, Deon Brewis wrote: I'm curious how that test that you added works? i.e. What causes the test to fail if the results are wrong? The particular test case you are referring to is written in in the TCL language. The TCL tests are the oldest

Re: [sqlite] Fill empty space with random

On 14-10-2018 17:07, J Decker wrote: > (sorry for the math err s/16/32/g and s/512/256/ - I double and halfed > the wrong directions.) > > On Sun, Oct 14, 2018 at 7:57 AM J Decker wrote: > >> >> On Sun, Oct 14, 2018 at 7:24 AM Luuk wrote: >> >>&g

Re: [sqlite] Fill empty space with random

On 14-10-2018 16:17, Simon Slavin wrote: > On 14 Oct 2018, at 12:56pm, J Decker wrote: > >> Is there maybe a compile option for sqlite to fill empty space in a db with >> random data rather than 0 ? > There is not. But > > (A) It may be an easy change to the source code > (B) Your operating

Re: [sqlite] SQLite Windows GUI alternative to Excel?

On 7-10-2018 01:18, Warren Young wrote: > On Oct 6, 2018, at 2:21 PM, Simon Slavin wrote: >> >> Excel ate the financial business world because companies use Excel to solve >> a simple problem, then add a feature, then add another feature, and keep >> going until they have some crawling creeping

Re: [sqlite] SQLite Windows GUI alternative to Excel?

On 6-10-2018 18:23, Warren Young wrote: > On Oct 6, 2018, at 9:46 AM, Roger Schlueter wrote: >> In addition to the freebies mentioned by Luuk, WordPerfect Office X9 is a >> commercial product that includes all of Excel's features including >> import/export of Excel da

Re: [sqlite] SQLite Windows GUI alternative to Excel?

On 6-10-2018 13:40, Winfried wrote: > Hello, > > After reading this article… > > "In the workplace, spreadsheet experts face a constant barrage of help > requests" > https://www.wsj.com/articles/the-first-rule-of-microsoft-exceldont-tell-anyone-youre-good-at-it-1538754380 > > … I'd like to check

Re: [sqlite] Full text serch - Matching all except chosen

On 31-7-2018 10:52, Luuk wrote: > From: http://www.sqlitetutorial.net/sqlite-full-text-search/ > For example, to get the documents that match the |learn| phrase but > doesn’t match the |FTS5| phrase, you use the |NOT| operator as follows: > > LECT * +SE > FROM posts > WHE

Re: [sqlite] Full text serch - Matching all except chosen

On 31-7-2018 10:52, Luuk wrote: > On 31-7-2018 07:25, paul tracy wrote: >> Forgive me if this is the wrong way to do this but I'm a newbie. >> I am using version 3.24.0 with FTS5 >> Is there a way to perform a full text search that returns every row except >> records

Re: [sqlite] Full text serch - Matching all except chosen

On 31-7-2018 07:25, paul tracy wrote: > Forgive me if this is the wrong way to do this but I'm a newbie. > I am using version 3.24.0 with FTS5 > Is there a way to perform a full text search that returns every row except > records matching a specified query string? > The following does not work

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

On 30-6-2018 15:45, Luuk wrote: > > In SQLite3 you are allowed to do this: > SELECT a,b,c > FROM t1 > GROUP BY a > > The values of 'b' and 'c' will be taken from a 'random' row... > > But if we rewrite this in SQL, i am getting something like this: > SELECT >

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

oup by a; > ? It still does not quarantee that the valuse show for b and c are comming from the same row... > > 2018-06-30 15:12 GMT+02:00, Luuk : >> On 30-6-2018 14:55, Keith Medcalf wrote: >>> Note that this is SQLite3 specific (and specific to Sybase of the era &

[sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

On 30-6-2018 14:55, Keith Medcalf wrote: > Note that this is SQLite3 specific (and specific to Sybase of the era where > Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft > re-writes of SQL Server up to about 2000). Technically you cannot do a query > of the form: > >

Re: [sqlite] Determine collation associated with sort

On 30-6-2018 14:05, x wrote: > Suppose I have a select such as > > ‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’ > > I want to know the collation associated with the sort. I know a COLLATE > condition could be attached to the order by BUT suppose there’s none. How

[sqlite] ROWID....

In the docs (https://www.sqlite.org/autoinc.html) it says: In SQLite, table rows normally have a 64-bit signed integer ROWID Question: Why it this a signed integer, and not an unsigned integer? Simply by choice? of is there something

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

On 1-5-2018 16:20, Simon Slavin wrote: > 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"

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

On 28-4-2018 12:05, J Decker wrote: > https://productforums.google.com/forum/?utm_medium=email_source=footer#!msg/gmail/vaG8BpMPov0/JqRR4wk2CQAJ > > On Sat, Apr 28, 2018 at 3:05 AM, J Decker wrote: > >> Yes, but then that spams the whole thread, and counts against sqlite, not

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

On 28-4-2018 11:26, J Decker wrote: > On Sat, Apr 28, 2018 at 2:08 AM, Luuk <luu...@gmail.com> wrote: > >> On 28-4-2018 07:36, Luuk wrote: >>> On 27-4-2018 20:57, Denis Burke wrote: >>>> I know steps were taken to reduce it, but just confirming it is

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

On 28-4-2018 07:36, Luuk wrote: > On 27-4-2018 20:57, Denis Burke wrote: >> I know steps were taken to reduce it, but just confirming it is still going >> on today. >> ___ >> sqlite-users mailing list >> sqlite-user

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

rg/cgi-bin/mailman/listinfo/sqlite-users I do not see spam in this list. The main reason is, i only read messages that contain the text '[sqlite]' in the subject. It's one of the features of gmail to filter on such a thing. -- Luuk ___ sqlite-users mailing

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

0 C:\Users\Luuk\AppData\Roaming\Thunderbird\Profiles\.default>sqlite3 global-messages-db.sqlite ".schema --indent" | findstr /i "autoincrement" C:\Users\Luuk\AppData\Roaming\Thunderbird\Profiles\.default> On 16-3-2018 16:37, Richard Hipp

Re: [sqlite] Feature request for the Shell Tool: .mode json

on StackExchange, etc. > > As with any such localized solution, once you get it working, you can use > it seamlessly as a function or an aliased call. > > Regards. > > Brian P Curley > > > > > On Jan 21, 2018 10:15 AM, "Luuk" <luu...@gmail.com> wrote:

Re: [sqlite] sqlite command line tool fails to dump data

On 21-01-18 16:16, J Decker wrote: > create table test (a,b) > insert into test (a,b) values ( ?,? ) > bind 'hello\0world.' 'te\0st' luuk@opensuse:~/tmp> sqlite3 SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. Connected to a transient in-memor

Re: [sqlite] Feature request for the Shell Tool: .mode json

On 21-01-18 16:05, Brian Curley wrote: > Is there even a need to embed it into sqlite itself? Since you're on the > shell, and in keeping with the whole 'do one thing well' mandate: pipe it > through jq instead. > > Beautiful creature that jq... > > Regards. > > Brian

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

On 11-01-18 18:01, Matthew Towler wrote: > Hi > > I believe I have found a bug in sqlite, which my tests show was introduced > between versions 3.7.17 and 3.8.0 and remains present in all versions up to > and including 3.21.0 (I have also tested 3.8.11, 3.9.3, 3.12.2 all of which > hang. I

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 07-01-18 19:09, x wrote: >> Because reading the whole record (all 3 fields) is more expensive than >> just reading the index which has all the info you need to give a correct >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' > Yes, but the covering index has 2 fields (X & ID). The pk has only

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 07-01-18 18:49, x wrote: > Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it > clear that Tbl had numerous secondary indexes attached to it. The table has > 2.4 million records and 13 columns. There is a non-unique index on one of the > other column

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

case of not ``NOT NULL'' field. > > > Yeah, I would get that result as well if I had no secondary indexes on Tbl. > If either you or Luuk add a secondary index XXX to your versions of Tbl > you’ll get the same result I’m getting and maybe then we’ll be on the same > page rega

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

42 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 06-01-18 10:44, x wrote: > Thanks for the replies. > > While I’m now on board as to what a NULL is I’m still a bit puzzled by the > query planner. > > Explain query plan select ID from Tbl where ID is null order by ID; > > returns > > SCAN TABLE Tbl USING COVERING INDEX ... > > I do see

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 06-01-18 00:49, Simon Slavin wrote: > > To expand on this, in SQL NULL has a special meaning. This should read: NULL has a special meaning. and not: in SQL NULL has a special meaning. Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not more special than in any other

Re: [sqlite] pragma table_info return column delimiters?

On 03-01-18 11:15, Bart Smissaert wrote: > Is there a way with pragma table_info or otherwise (other than parsing the > table create statement from SQLite_master) to get the column names > including the column delimiters, eg double quotes or square brackets? So I > would get eg: [column1]

[sqlite] scanstats

'.scanstats' about it not being available. luuk@opensuse:~/tmp> sqlite-autoconf-321/sqlite3 SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. s

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

On 01-01-18 16:52, E.Pasma wrote: > Clemens Ladisch wrote: > >> Luuk wrote: >>> On 01-01-18 03:14, Shane Dev wrote: >>>> select * from nodes where not exists (select * from edges where >>>> child=nodes.id); >>> >>> Changing this to: &g

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

On 01-01-18 12:18, Luuk wrote: > On 01-01-18 03:14, Shane Dev wrote: >> Hello, >> >> I have a directed acyclic graph defined as follows - >> >> sqlite> .sch >> CREATE TABLE nodes(id integer primary key, description text); >> CREATE TABLE edges(

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

On 01-01-18 03:14, Shane Dev wrote: > Hello, > > I have a directed acyclic graph defined as follows - > > sqlite> .sch > CREATE TABLE nodes(id integer primary key, description text); > CREATE TABLE edges(parent not null references nodes, child not null > references nodes, primary key(parent,

Re: [sqlite] Importing Text to Create a Table

Can you give a (short) example of some lines, and how you import them? Creating a table with 1 field text 255 seems not the right way to do this if you are only interested in emailaddresses. On 22-12-17 01:22, Lawrence Murphy wrote: > I am supporting a website which aims to protect a forest

Re: [sqlite] random value get re-generated too often in SQLite

On 08-12-17 23:34, Scott Doctor wrote: > > Is it possible that the first call to random is cached and the cached > value is being returned in subsequent calls? > > - > Scott Doctor > sc...@scottdoctor.com > - > The easiest way to get this behaviour

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

lete %temp%\dump.tmp >nul On Fri, Dec 8, 2017 at 10:19 AM, Luuk <luu...@gmail.com> wrote: On 08-12-17 14:52, Luuk wrote: On 08-12-17 14:13, Simon Slavin wrote: On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> wrote: I'd like to dump all the tables to separate

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

On 08-12-17 14:52, Luuk wrote: On 08-12-17 14:13, Simon Slavin wrote: On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> 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 s

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

On 08-12-17 14:13, Simon Slavin wrote: 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

Re: [sqlite] Out of date online documents

On 31-12-16 20:52, Richard Hipp wrote: On 12/31/16, Paul Lambert wrote: While exploring the sqlite.org website for specific sqlite details I found the document at the URL below which is labelled as "draft" and appears to be from version 3.6.11. The latest release is 3.15

Re: [sqlite] Why takes the second SELECT three times as much time?

On 16-10-16 12:00, Cecil Westerhof wrote: I have defined the following table: CREATE TABLE messages ( dateTEXT NOT NULL DEFAULT CURRENT_DATE, timeTEXT NOT NULL DEFAULT CURRENT_TIME, typeTEXT NOT NULL, messageTEXT NOT NULL,

Re: [sqlite] Complicated join

On 19-09-16 21:28, David Bicking wrote: This is what I want: SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr; A 1 1 A 2 1 A 3 1 A 4 1 A 5 5 -- matches the (A,5) record in the M table. A 6 1 A 7 1 A 8 1 A 9 1 B 1 NULL -- no match found for CombinedKeyfield in M Did this

Re: [sqlite] Complicated join

On 19-09-16 19:33, David Bicking wrote: select E.CombinedKeyField, E.EvtNbr, M.EvtNbr from E left join M on E.CombinedKeyField = M.CombinedKeyField and (E.EvtNbr = M.EvtNbr or M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1 WHERE M1.CombinedKeyField = E.CombinedKeyField

Re: [sqlite] Complicated join

On 19-09-16 19:33, David Bicking wrote: INSERT INTO M (CombinedKeyField, EvtNbr) VALUES ('A', 1), ('A', 5); INSERT INTO E (CombineKeyField, EvtNbr) VALUES ('A', 1) , ('A', 2) , ('A', 3) , ('A', 4) , ('A', 5) , ('A', 6) , ('A', 7) , ('A', 8) , ('A', 9) , ('B', 1); What is the name of this

Re: [sqlite] where is the old version of sqlite3，anyone could tell me？

On 03-09-16 04:59, 陈伟 wrote: 发自我的 iPhone ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users define 'old' see:

[sqlite] You may add sha256 checksum for files in the download page?

On 02-04-16 19:13, Simon Slavin wrote: > On 2 Apr 2016, at 11:35am, Pavel Volkov wrote: > >> You may add sha256 checksum for files in the download page? > SHA1 is so easy to crack now it might make sense to replace the SHA1 > checksums with SHA256. Does anyone have a good reason why SHA1 is

[sqlite] Efficient relational SELECT

On 04-02-16 19:32, Simon Slavin wrote: > Simplified explanation. Here's the setup: > > Two tables: > > rooms: each room has an id (think the standard SQLite rowid/id) and some > other columns > bookings: includes room id, date, time, and some other stuff > > Date/time is encoded as a long

[sqlite] Can SQLite know from the statement string if it is row producing or not?

On 08-01-16 21:27, Bart Smissaert wrote: > I am interested to know from the statement string if the statement is > invalid, row producing (could produce rows) or non row producing. I know > sqlite3_prepare16_v2 can see if the statement is valid or not but how about > the other 2? > I can do this

[sqlite] Apparent sqlite bug

On 03-01-16 00:11, richard parkins wrote: > An INSERT statement which fails with no explicit conflict clause appears to > throw away a pending SAVEPOINT. > The following sequence demonstrates this behaviour > SAVEPOINT demonstration; > CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT", >

  1   2   3   >