Re: [sqlite] Attach to file in same directory

On 03-02-11 16:18, BareFeetWare wrote: > What SQLite or C library call could I put before that to set the current > directory, that the sqlite3_prepare_v2 function would observe when processing > the attach statement? i'm not a C-programmer but: http://www.delorie.com/gnu/docs/glibc/libc_268.h

Re: [sqlite] Mozilla Sunbird

On 18-02-11 05:04, Andrew Fox wrote: > I would be extremely grateful if someone could tell me how to copy my Mozilla > Sunbird calendar data into a new Mozilla Sunbird calendar. My laptop hard > drive failed and so went my very imporant calendar. I have a complete backup > of my laptop's faile

Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?

On 03-04-2011 14:43, Colin Cuthbert wrote: > First time I've used this (or any!) mailing list, so sorry if I've done > something wrong. > > Pretty sure my question (in the subect) is phrased badly but it's the best I > could do! > > create table People(id integer primary key, name text); > insert

Re: [sqlite] BUG : round(x,y) not consistent

On 29-04-2011 22:09, Jean-Marie CUAZ wrote: > Win XP + SQLite 3.7.4 > > SELECT round(8.875,2)-> 8.88 > SELECT round(16.875,2) -> 16.87 > SELECT round(32.875,2) -> 32.88 > SELECT round(64.875,2) -> 64.87 > > I'm not sure SQLite is the actual culprit (and I'm aware the su

Re: [sqlite] BUG : round(x,y) not consistent

On 30-04-2011 14:04, Luuk wrote: > On 29-04-2011 22:09, Jean-Marie CUAZ wrote: >> Win XP + SQLite 3.7.4 >> >> SELECT round(8.875,2)-> 8.88 >> SELECT round(16.875,2) -> 16.87 >> SELECT round(32.875,2) -> 32.88 >> SELECT round(64

Re: [sqlite] BUG : round(x,y) not consistent

On 30-04-2011 20:48, Simon Slavin wrote: > > On 29 Apr 2011, at 9:09pm, Jean-Marie CUAZ wrote: > >> Win XP + SQLite 3.7.4 >> >> SELECT round(8.875,2)-> 8.88 >> SELECT round(16.875,2) -> 16.87 >> SELECT round(32.875,2) -> 32.88 >> SELECT round(64.875,2) -> 64.87 > > T

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] 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: > > SE

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

y 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 >&g

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 somethin

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 bec

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 >> re

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] 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 if

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

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] 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 syst

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: >> >>> On 14-

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 se

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 w

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 location

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 referen

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 COL

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 documen

[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 missing

[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

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 FILEN

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] 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 #in

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. https://en.wikipedia.org/wiki/SQLite#Programming_la

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

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. Know

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 was that

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 a

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] 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] 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 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 "no

[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 Reg

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

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] 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: sql

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 mail

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] 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 ti

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, b

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 tr

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 = connection.createStatement

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] 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 strftime

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 This i

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 8601

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 mail

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 ISNULL;

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] 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 log

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 &#

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 "" > empt

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 eve

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] 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] 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] 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 SQLite library to produc

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 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? which you might wa

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

>%%i.tsv delete %temp%\dump.tmp >nul On Fri, Dec 8, 2017 at 10:19 AM, Luuk 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 wrote: I'd like to dump all the tables to separate files, one table one file. Each file sh

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 i

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 fro

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, child)

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 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

[sqlite] scanstats

x27; from the list or, give the warning when doing '.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 &quo

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] [column

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 envi

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 differe

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'

turned in 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

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'

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] 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 beli

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.

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

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

on this same recipe out there, 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, "Luu

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] 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 ___ sqlit

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

On 28-4-2018 11:26, J Decker wrote: > On Sat, Apr 28, 2018 at 2:08 AM, Luuk 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 still >> going >&g

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&utm_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 >> the individua

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" files which are act

[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 more

[sqlite] sqlite3 and Unicode

On 4-5-2015 20:54, Richard Hipp wrote: > On 5/4/15, Peter Haworth wrote: >> When using the .dump command with .output to a filename, what encoding does >> sqlite3 for the file? Same as the database encoding? Is it possible to >> change whatever encoding is used? >> >> Similarly, when using the .i

[sqlite] sqlite3 and Unicode

On 5-5-2015 09:22, Luuk wrote: > On 4-5-2015 20:54, Richard Hipp wrote: >> On 5/4/15, Peter Haworth wrote: >>> When using the .dump command with .output to a filename, what >>> encoding does >>> sqlite3 for the file? Same as the database encoding? Is it poss

[sqlite] sqlite3 and Unicode

On 5-5-2015 11:25, Kees Nuyt wrote: > On Tue, 05 May 2015 10:50:00 +0200, Clemens Ladisch > wrote: > >> Luuk wrote: >>> on Windows 7: >>> C:\temp>sqlite3.exe encoding.sqlite >>> sqlite> select * from test; >>> ??n >> >> The

[sqlite] sqlite3 and Unicode

On 5-5-2015 12:25, Clemens Ladisch wrote: > Staffan Tylen wrote: >> I must admit that I'm a bit confused here. If I'm not wrong UTF-8 differs >> from ascii when the value is higher than '7f'x, but storing data in sqlite >> as text with character values beteen 'x80'x and 'ff'x seems to be no >> prob

[sqlite] Windows 10 and UTF8 [Re: sqlite3 and Unicode]

On 5-5-2015 12:40, Luuk wrote: > > now it becomes time that windows will do some things with UTF-8 > (sigh) ;-) > > /me currently watching > "Your PC will restart serveral times. Sit back and relax" > while installing updates for technical preview to Window

[sqlite] Sample SQL code thats beyond me :(

On 23-5-2015 18:41, Keith Medcalf wrote: > > You also lastly mention that the UUID fields are also used in the selection, > so the problem statement is really: > > Return the projection of Users and Perimeter_Notifications using the common > email field as the equijoin key, but return only the re

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

On 23-5-2015 21:14, Darren Duncan wrote: > More like It'll be out in time for Christmas, where the specific year > isn't mentioned. -- Darren Duncan > > On 2015-05-23 11:09 AM, Mikael wrote: >> This sounds like it means we'll have it 2.5-5 years then.. so 2018 maybe, >> ah, that's in the year when

[sqlite] sqlitediff

On 24-5-2015 08:39, Marco Bambini wrote: > Where can I download sqlitediff and its source code? > http://lmgtfy.com/?q=sqlitediff&l=1 or, more correct: http://lmgtfy.com/?q=sqlitediff+source

[sqlite] sqlitediff

On 24-5-2015 09:20, Marco Bambini wrote: > Luuk, that's really really funny thanks. > > Starting from sqlite 3.8.10 sqlitediff.exe has been added to the official > sqlite distribution. > I downloaded both the Amalgamation version and the Alternative Source Code > Fo

[sqlite] Query returns "stack overflow error"

On 30-5-2015 02:53, Etienne Charland wrote: > I'm using SQLiteStudio. > > It's not easy to run it manually because of how the parameters must be > passed, and the function that doesn't exist within the database. > > I tried replacing all parameters by their value, and replacing the function > wit

[sqlite] Simple Math Question

On 23-10-15 16:39, Dominique Devienne wrote: > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A < > Richard.A.Rousselot at centurylink.com> wrote: > >> So I decided to output 1000 digits, because why not? So now I am more >> perplexed with all these digits showing it is working the opposite

  1   2   3   >