Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

2020-03-13 Thread Simon Slavin
On 13 Mar 2020, at 6:50pm, Thomas Kurz wrote: > Why can't the forum just forward all new postings to this mailing list and > vice versa? Then everyone could chose what to use ;) I think one of the objectives of moving away from email is to prevent email spam.

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

2020-03-12 Thread Simon Slavin
Well, that'll annoy the nabble people. And I can live with that. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

2020-03-12 Thread Simon Slavin
On 12 Mar 2020, at 3:36pm, David Blake wrote: > What stops the > UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ; > from also triggering the AFTER UPDATE ON recursively? > > Perhaps a pragma or inSQLite are triggers non-recursive by default? Bingo.

Re: [sqlite] Is this an SQL parsing / ambiguity bug ?

2020-03-10 Thread Simon Slavin
On 9 Mar 2020, at 8:40pm, Vladimir Vysotsky wrote: > sqlite> insert into dst(id) select id from src on conflict do nothing; > Error: near "do": syntax error SQLite does not understand "DO NOTHING". You probably want "ON CONFLICT IGNORE".

Re: [sqlite] No feedback for executing a mathematical expression

2020-03-10 Thread Simon Slavin
On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera wrote: > Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote... > >> That's going in my list of annoying questions. Thank you. > > Simon, with all due respect, and grateful for all the answers you have

Re: [sqlite] No feedback for executing a mathematical expression

2020-03-10 Thread Simon Slavin
On 10 Mar 2020, at 7:21am, Octopus ZHANG wrote: > sqlite> select 99-(55/(30/57)); > > Should I expect it to return nothing? That's going in my list of annoying questions. Thank you. sqlite> select 99.0-(55.0/(30.0/57.0)); -5.5 Now you can work out what the problem is.

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread Simon Slavin
On 9 Mar 2020, at 2:16pm, David Raymond wrote: > In general I believe the last connection tries to do a complete checkpoint > when it closes, and if it succeeds then it'll delete the -wal and -shm files. > If you have automatic checkpointing turned off (maybe you're doing regular >

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-08 Thread Simon Slavin
[This explanation is much simplified for clarity. Before filling in missing details please consider what the OP wants. Don't just show off your exhaustive knowledge of SQLite.] A database is normally in delete journal mode, as if you'd executed PRAGMA journal_mode=DELETE In this journal

Re: [sqlite] Trigger name missing

2020-03-07 Thread Simon Slavin
On 7 Mar 2020, at 4:49pm, John G wrote: > Would it be possible to create an SQL verification program, which just like > 'sqlite3_analyzer' and 'sqldiff' could be run separately? > It could *warn* about apparently incompletely defined triggers and other > possible pitfalls. The shell tool has

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

2020-03-06 Thread Simon Slavin
On 7 Mar 2020, at 1:52am, Xinyue Chen wrote: > If I change IS NOT FALSE to IS TRUE, the results will be different. I > assume they should perform in the same way? No. Because NULL is not TRUE and is not FALSE. SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. Connected

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Simon Slavin
On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera wrote: > The reason why I know is that if I have 238 INSERTS, but I have a constraint > , there should be 238 INSERTs the first time I run a set of SQL, but if I run > the same SQL again, there should not be any records INSERTED, and thus, the >

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Simon Slavin
On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera wrote: > However, I have found that it does not actually provide the **ACTUAL** > changes, but a count of the possible changes. Hmm. I understand you. Does this do something more like what you need ?

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Simon Slavin
On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera wrote: > Is there a way to know all the changes that may have happened within the full > BEGIN and END? Thanks. Use this function before and after your block, and subtract one from another.

Re: [sqlite] Shell Feature Request: spaces before dot commands

2020-03-04 Thread Simon Slavin
On 4 Mar 2020, at 12:19pm, no...@null.net wrote: >I like to sometimes indent a block of SQL and change > settings or run dot commands within a transaction, e.g.: > >BEGIN >.mode csv >.import ... >COMMIT; Dot commands are not SQL commands, they are instructions to the

Re: [sqlite] Select statement with ORDER BY specified by column value

2020-02-29 Thread Simon Slavin
On 29 Feb 2020, at 8:37am, Marco Bambini wrote: > ORDER BY (prop_tag='ios') LIMIT 1; > > I would like to prioritise results based on the fact that the prop_tag column > is 'ios'. SQLite has a conditional construction: CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END So do SELECT … ORDER BY

Re: [sqlite] Intersecting multiple queries

2020-02-28 Thread Simon Slavin
On 29 Feb 2020, at 12:45am, Keith Medcalf wrote: > select * from data where (...) AND (...) AND (...) ; SELECT uuid FROM Data WHERE filter LIKE ? OR filter LIKE ? OR filter LIKE ? OR filter LIKE ? … would probably be an efficient way to do it if you could construct your

Re: [sqlite] Intersecting multiple queries

2020-02-28 Thread Simon Slavin
On 28 Feb 2020, at 11:02pm, Hamish Allan wrote: > What I'm wondering is if there's a shortcut to avoid having to build the UUID > list in app code I would probably start by building a list of the search patterns then see what I could do with it: (pattern1,pattern2,pattern3) Would a Common

Re: [sqlite] How to prevent sqlite_reset reporting an already known error

2020-02-26 Thread Simon Slavin
On 26 Feb 2020, at 11:58pm, mailing lists wrote: > The issue is that sqlite_reset() reports the same error that already > sqlite3_step() reported. How can I prevent that sqlite_reset() reports the > same error. You can't. It's working as designed. One would normally test the result that

Re: [sqlite] inserting new data only

2020-02-26 Thread Simon Slavin
On 26 Feb 2020, at 5:18pm, Przemek Klosowski wrote: > This 'store only changed values' is > intended for situations like that. > Another suitable candidate for that treatment might be a status, for > instance 'on battery' value for a UPS monitoring system. I can't think of a > scenario where

Re: [sqlite] Trigger name missing

2020-02-26 Thread Simon Slavin
On 26 Feb 2020, at 2:15pm, Dan Kennedy wrote: > A statement like the following creates a "BEFORE" trigger named "AFTER". Does > that explain things? > > CREATE TRIGGER AFTER INSERT ON t1 BEGIN > ... > END; > > I find I fall into this trap about once every 18 months... If only you

Re: [sqlite] inserting new data only

2020-02-25 Thread Simon Slavin
This strikes me as best solved in the programming language. If a single set of data points is being acquired in real time, and you have a programming language (or script) generating the INSERT commands, why not simply keep the most recently inserted temperature in a variable ? On the other

Re: [sqlite] Performance Issue on Large Table

2020-02-23 Thread Simon Slavin
On 23 Feb 2020, at 7:23pm, Richard Damon wrote: > I'm not sure that form of division would be good. One basic rule of database > normalization is that you don't break-up data based on the value of one of > the fields because you can't make the table to lookup data from be taken from > a field

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

2020-02-21 Thread Simon Slavin
On 22 Feb 2020, at 2:28am, Keith Medcalf wrote: > When a database is to be created these commands must be given BEFORE any > command which opens or creates the database: > > pragma auto_vacuum > pragma encoding > pragma page_size > pragma data_store_directory > > The issuance (or preparation)

[sqlite] DRH interview on why/how SQLite succeeded

2020-02-19 Thread Simon Slavin
Podcast / transcription of DRH interview: " We talked to Richard about the history of SQLite, where it came from, why it succeeded as a database, how its development is sustainably funded and also how it’s the most widely deployed database engine in the

Re: [sqlite] Does VACUUM imply REINDEX?

2020-02-19 Thread Simon Slavin
On 19 Feb 2020, at 4:36pm, Olivier Mascia wrote: > having an apparently sane database (pragma integrity_check and pragma > foreign_key_check successful at least), it would make sense to do : REINDEX, > ANALYZE and then VACUUM as part of final preparation step for archive (the DB > would be

Re: [sqlite] Does VACUUM imply REINDEX?

2020-02-19 Thread Simon Slavin
On 19 Feb 2020, at 4:18pm, Richard Hipp wrote: > There is no benefit to running > ANALYZE after running VACUUM if the ANALYZE data (the content of the > sqlite_stat1 table) is still up-to-date. However, if you have a yearly maintenance procedure, it might make sense to include ANALYZE in it.

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

2020-02-16 Thread Simon Slavin
On 16 Feb 2020, at 8:44pm, Stefan Brüns wrote: > On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote: > >>> One use case I am aware of (although this targets places.sqlite, not >>> cookies.sqlite) is reading the history, bookmarks and tags. >> >

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

2020-02-16 Thread Simon Slavin
On 16 Feb 2020, at 8:02pm, Stefan Brüns wrote: > Will this stop anyone from just copying the DB without the -wal file? > Afterwards, the DB can be read, as there is no longer any associated log. The purpose of the locking is to prevent changes being made to the database during a browser

Re: [sqlite] WITHOUT ROWID tables

2020-02-16 Thread Simon Slavin
On 16 Feb 2020, at 6:00pm, Bernardo Ramos wrote: > I've got no rows for both with and without rowid tables: " as of SQLite version 3.30.0 on 2019-10-04 " ___ sqlite-users mailing list

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

2020-02-16 Thread Simon Slavin
On 16 Feb 2020, at 5:15pm, Peng Yu wrote: > Why the database can not be read by another sqlite3 session when the > corresponding -wal file exists? Thanks. This is done on purpose by the developers of Firefox to prevent a security vulnerability which I will not describe in public. One of the

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

2020-02-15 Thread Simon Slavin
On 15 Feb 2020, at 8:12pm, J Decker wrote: > memcmp is harder than strcmp? > and with blob I suppose you don't have 'if UTF8 or UTF16 do > different things' so should compare slightly faster? It's not cut-and-dried, but you can take things into consideration. Once data is in a database it

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread Simon Slavin
On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote: >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA >> index_info('XYZ');". If you get back one or more rows, then XYZ is a >> WITHOUT ROWID table. If you get back no rows, then XYZ is a rowid >> table. >> > Confused...What if

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

2020-02-14 Thread Simon Slavin
On 14 Feb 2020, at 2:59pm, Andy KU7T wrote: > Do you suggest me keeping storing TEXT (and declaring the column as TEXT > instead of GUID) or storing and declaring BLOB (and remove HEX)? TEXT. Makes sorting and searching easier. ___ sqlite-users

Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

2020-02-13 Thread Simon Slavin
On 13 Feb 2020, at 2:01pm, Scott wrote: > Can I search all tables and columns of SQLite database for a specific text > string? No. There's no way to do this, and there's no way to say "all tables" in SQL. In other words it's not easy to write such a thing. I like Thomas Kurz's solution, to

Re: [sqlite] Make sqlite3 database searchable on Mac OS X

2020-02-10 Thread Simon Slavin
On 11 Feb 2020, at 4:10am, Peng Yu wrote: > It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. > Is there a way to make them searchable? Thanks. A long time ago I wrote a SQLite indexer for … I think it was Sherlock back then. It went through all text fields

Re: [sqlite] How to group this?

2020-02-10 Thread Simon Slavin
On 10 Feb 2020, at 10:41pm, Wolfgang Enzinger wrote: > Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin: > >> On 10 Feb 2020, at 1:25am, no...@null.net wrote: >> >> create two VIEWs, [...]. Index both VIEWs on (id, date), > > I don't think that creati

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

2020-02-10 Thread Simon Slavin
Does this problem affect unnumbered indexes too ? In other words if I have (?,?,?,?,?) and bind to the fifth one using the index do I have the same problems as having (?1,?2,?3,?4,?5) and bind to the fifth one using its number ? ___ sqlite-users

Re: [sqlite] notify all processes of database modification

2020-02-09 Thread Simon Slavin
On 10 Feb 2020, at 4:26am, Rowan Worth wrote: > See also PRAGMA data_version when it comes to polling the DB, the return > value of which changes when another process modifies the DB. IIRC the > implementation of this depends on a value in the DB header page, so it may be > sufficient to only

Re: [sqlite] How to group this?

2020-02-09 Thread Simon Slavin
On 10 Feb 2020, at 1:25am, no...@null.net wrote: > Here is one way that appears to generate the correct result. Another way: create two VIEWs, one for systolic, one for diasystolic. Index both VIEWs on (id, date), then JOIN ON id AND date. If you want to, you could use this to make a third

[sqlite] Documentation improvement request

2020-02-08 Thread Simon Slavin
Some pages in the documentation started small but had stuff gradually added to them over the years. For the pages can a "Table Of Contents" be added at the top, like the one on

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

2020-02-08 Thread Simon Slavin
On 8 Feb 2020, at 6:25pm, Andy KU7T wrote: > IF isnull(version) THEN > -- create table, indexes etc. > > ELSE > -- make incremental changes > > END IF You're going to have to give up the idea that you can do all this in one complicated SQL command. You're going to end up doing a lot of 1)

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

2020-02-08 Thread Simon Slavin
On 8 Feb 2020, at 3:10pm, Andy KU7T wrote: > I'm looking for best practices on when and where to create/initialize your > tables when using SqlLite.Net as well as how to properly handle schema > changes from one version to another. I urge you not to use CreateTable, but instead to use SQL

Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request (WIthdrawn)

2020-02-07 Thread Simon Slavin
On 8 Feb 2020, at 4:05am, Keith Medcalf wrote: > filename=FILENAME is the csv filename in quotes. Please mention in documentation whether full or partial paths can be included. > header=BOOL will "parse" the first row to get the column names unless a > SCHEMA is specified. Looks good

Re: [sqlite] Does a foreign key field need a separate index?

2020-02-06 Thread Simon Slavin
On 6 Feb 2020, at 9:23pm, Rael Bauer wrote: > Is a foreign key field automatically indexed, or will it benefit from a > separately created index? No indexes for either lookup are automatically created. You should create indexes which will help lookup in both directions, unless one of the

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

2020-02-04 Thread Simon Slavin
On 4 Feb 2020, at 7:13pm, Deon Brewis wrote: > WHERE x IN (?1,?2,?3,?4...,?1000 ) People really do this ? Sheesh. > The alternate to this is to create a virtual table wrapper over the internal > datasets of the app. Which is of course better, but harder. (We need an STL > for SQLite.

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

2020-02-04 Thread Simon Slavin
On 4 Feb 2020, at 6:27pm, Alex Bronstein wrote: > In such cases, you can easily end > up with more than 999 parameters. I'm curious. Can you show us some place where using 999 parameters is a reasonable way to use SQLite ? > PostgreSQL and some other databases support a 16 bit parameter

Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines

2020-02-04 Thread Simon Slavin
On 4 Feb 2020, at 12:18pm, Robert M. Münch wrote: > - sep=';': field separator character (different from default ',') If you provide this facility, please don't add it to anything called 'csv' since the 'c' stands for 'comma'. For those playing along at home, csv files using semi-colon are a

Re: [sqlite] Unicode quote characters parsing in FTS5 queries

2020-02-03 Thread Simon Slavin
On 3 Feb 2020, at 9:30pm, David Guillen Fandos wrote: > However my queries where choking on some input that contained the U+2018 and > U+2019 characters. It seems like these chars are being treated like a regular > quote character At least one version of SQL respects those characters for

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

2020-02-01 Thread Simon Slavin
On 1 Feb 2020, at 1:30pm, Peng Yu wrote: > https://www.sqlite.org/lang_createtable.html > > "The database in which the new table is created. Tables may be created in the > main database, the temp database, or in any attached database." > > It is not clear to me in the doc. If a table is

Re: [sqlite] Check constrain execution timing change?

2020-01-31 Thread Simon Slavin
On 1 Feb 2020, at 1:28am, Simon Slavin wrote: > How do you feel about that ? Sorry, I didn't read the rest of the list first. I see it's already answered. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org h

Re: [sqlite] Check constrain execution timing change?

2020-01-31 Thread Simon Slavin
On 31 Jan 2020, at 10:51pm, Richard Hipp wrote: > CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text')); > INSERT INTO t1 VALUES('123'); > PRAGMA integrity_check; Short and sweet. That's what I was talking about. How do you feel about that ? Should a programmer be able to create a failure in

Re: [sqlite] Check constrain execution timing change?

2020-01-31 Thread Simon Slavin
On 31 Jan 2020, at 9:27pm, Keith Medcalf wrote: > You are however correct that this is an "application consistency" problem > more than an SQLite problem and it is a lot of change for little actual > benefit. How about this ? A program (possibly part of the SQLite precompiled suite, possibly

[sqlite] "Standard SQL" ?

2020-01-30 Thread Simon Slavin
I would appreciate your help. Reading a technical article today, I came across a casual reference to "Standard SQL" as if it was a well-known thing. This worried me since I've never heard the term and I'm meant to know about such things. It doesn't seem to refer to the official standard for

Re: [sqlite] New word to replace "serverless"

2020-01-28 Thread Simon Slavin
On 28 Jan 2020, at 6:05pm, Roman Fleysher wrote: > I would like to ask, why is it so important to indicate that SQLite, in > reference to threads or client/server, " does not work that way". I think > this might help to find the words to describe it. What a great point. As a (retired)

Re: [sqlite] New word to replace "serverless"

2020-01-27 Thread Simon Slavin
On 27 Jan 2020, at 11:53pm, Donald Shepherd wrote: > In-process? Same concept but defining it by what it is rather than what it > isn't. This comes closest to what I think needs stating. What you're trying to say is that there's no process (on the accessing computer or some other computer

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Simon Slavin
On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera wrote: > CASE >( > SELECT WYear FROM t2 WHERE pid = a.a >) >WHEN c.WYear = 2020 THEN “YES” >ELSE “NO” END That's not the structure of a CASE statement. After CASE comes an expression. After WHEN comes another expression.

Re: [sqlite] 64 bits version missing

2020-01-25 Thread Simon Slavin
On 23 Jan 2020, at 10:22pm, Evert van Dijken wrote: > The 64-bits DLL is missing from the download page, "sqlite-dll-win64-x64-331.zip" works without problems for me. Please try again, or tell us what other thing you're referring to. ___

Re: [sqlite] Row length in SQLITE

2020-01-22 Thread Simon Slavin
On 22 Jan 2020, at 11:44pm, Deon Brewis wrote: > Is there any way to get the length of rows in a table / index in sqlite? Do you mean the count of rows in a table / index ? SELECT count(*) FROM MyTable There's no easy fast way to do this because SQLite doesn't keep that number handy

Re: [sqlite] Find schema of a table in a query

2020-01-20 Thread Simon Slavin
On 20 Jan 2020, at 2:53pm, x wrote: > Is this possible in sql? In SQL schema names and table names are entities. You cannot bind a parameter to a either of them. This is why, instead of keeping data in many different databases, you make one big schema, and use that value as a column.

Re: [sqlite] To edit in sqlite3 CLI multiline SQL statements?

2020-01-18 Thread Simon Slavin
On 18 Jan 2020, at 9:30pm, Csanyi Pal wrote: > can one edit a multiline SQL statement in the sqlite3 CLI? No. But if you make a multiline SQL statement in a text file you can paste it into the CLI all in one operation. ___ sqlite-users mailing list

Re: [sqlite] How can I detect rows with non-ASCII values?

2020-01-18 Thread Simon Slavin
On 18 Jan 2020, at 12:12pm, Rocky Ji wrote: > By question marks, I meant- that some text, like Dutch programmers names, and > address in Nordic locations, have accents and umaults and other such > modifications done to English-alphabets. These get displayed as ? or box SQLite doesn't display

[sqlite] Bad database manager. No biscuit.

2020-01-17 Thread Simon Slavin
tl;dr: CSV is a useful standard which, like most things in computing, has a couple of 'gotchas'. Not all '.CSV' files are CSV files. Can't give details but I just identified a problem which had caused the SQL database owner £100,000 == US$130,000 == €117,000 of damage, mostly because

Re: [sqlite] Not Null Constraint Issue?

2020-01-17 Thread Simon Slavin
On 17 Jan 2020, at 6:39pm, Justin Gielski wrote: > After looking into the issue a bit more, it appears the INTEGER value was > actually saved into the DB as an EMPTY not a NULL, which is currently allowed > by the database constraints. Could you show us the constraint you feel disallows those

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

2020-01-16 Thread Simon Slavin
On 16 Jan 2020, at 4:19pm, Simon Slavin wrote: > The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. 'can' should be 'can't'. Sorry. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org h

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

2020-01-16 Thread Simon Slavin
On 16 Jan 2020, at 3:21pm, Keith Bertram wrote: > Ok. I was hoping I could set the value to a value higher than 10 without > compiling. I'm curious why the limit is set by default to 10. The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. It's worth explaining why you

Re: [sqlite] Not Null Constraint Issue?

2020-01-15 Thread Simon Slavin
On 15 Jan 2020, at 9:44pm, Justin Gielski wrote: > *"database is locked release restore point sqlite"* If there's nothing in your code that caused that to happen, then I would suspect a transient hardware glitch. Does your code use SAVEPOINTs ? > The database locking mode is set to NORMAL

Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

2020-01-14 Thread Simon Slavin
On 14 Jan 2020, at 10:56pm, Alexandre Doubov wrote: > 1) Does the act of bumping this limit up have an effect on memory at all > (assuming that no more than 999 arguments are passed into statements)? Section 9: If I read this correctly, memory is reserved

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Simon Slavin
On 14 Jan 2020, at 4:14pm, Jean-Baptiste Gardette wrote: > The reason i asked this is that i have a query in wich one condition > filtering the recordset involves > an UDF and this UDF needs to be processed after all table filters have been > applied You cannot guarantee this. And even if

[sqlite] More job postings mention SQL than anything else

2020-01-13 Thread Simon Slavin
If you want to justify the hours you spend fiddling with SQLite you should feel a little happier today. Graph from Burning Glass Analytics comparing demand for various buzzwords in job postings. SQL is mentioned >10%

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Simon Slavin
On 13 Jan 2020, at 9:26am, Dominique Devienne wrote: > Which implies length(text_val) is O(N), while > length(blob_val) is O(1), > something I never quite realized. For this reason, and others discussed downthread, some languages which store Unicode strings store the number of graphemes as

Re: [sqlite] how to disable dot commands?

2020-01-12 Thread Simon Slavin
On 13 Jan 2020, at 1:43am, Xingwei Lin wrote: > Is there any way can we disable the dot commands feature in sqlite? SQLite – the library you call from C and other programming languages – does not support the dot commands. It doesn't recognise them. If you try to use them you will get a

Re: [sqlite] Unexplained table bloat

2020-01-12 Thread Simon Slavin
On 12 Jan 2020, at 10:25pm, Tom Browder wrote: > I assume that is just for SQLite, or am I wrong again? You're generally right. SQLite always uses affinities (more or less 'weak typing') rather than strong typing. I don't know of any other SQL implementations which allow this without

Re: [sqlite] Database speed comparison

2020-01-10 Thread Simon Slavin
Are you aware of ? Now all you have to do is write comparative tests for other SQL implementations. However, from what I've seen, realistic speed tests tend to come down to cache sizes. ___

[sqlite] Sharing an SQLite database across containers

2020-01-10 Thread Simon Slavin
" Just exactly how is a file under constant modification safely shared across dozens of containers, all needing concurrent access? And you’re really going to read this file from

Re: [sqlite] Unexplained table bloat

2020-01-10 Thread Simon Slavin
On 10 Jan 2020, at 3:11pm, Ryan Mack wrote: > OK, I think I've got a better sense now. Hex encoding the column shows that > there's actually a huge amount of data stored in there. For some reason > length() isn't revealing it even if the column type is blob. Dumping and > restoring the table

Re: [sqlite] Unexplained table bloat

2020-01-10 Thread Simon Slavin
On 10 Jan 2020, at 2:06pm, David Raymond wrote: > Well something's weird anyway. When I open it with the command line tool it > queries it just fine Did you run an integrity_check on the database ? It looks from your posts as if it's corrupt. ___

Re: [sqlite] how to pass -Dxxx compile option

2020-01-09 Thread Simon Slavin
On 9 Jan 2020, at 6:47am, Xingwei Lin wrote: > How can I pass -Dxxx compile option when I build sqlite? Such as, - > DSQLITE_ENABLE_INTERNAL_FUNCTIONS. Depends on which compiler you're using. See examples for gcc on this page:

Re: [sqlite] Magellan 2.0 Vulnerabilities

2020-01-08 Thread Simon Slavin
On 9 Jan 2020, at 12:18am, Ware, Ryan R wrote: > I see absolutely nothing on sqlite.org or in the mail list archive > specifically about these issues If someone reports a vulnerability here, it gets acknowledged here. But I don't think Tencent posts here. On 8 Jan 2020, at 10:27pm, Ware,

Re: [sqlite] TO

2020-01-08 Thread Simon Slavin
On 8 Jan 2020, at 4:13pm, R Smith wrote: > Anyone have an idea where the word TO is used in SQL in SQLite? I seem to remember that the language used by the SQLite parser works from a table of data. Would questions like this be easy to answer using that table ?

Re: [sqlite] NOTNULL

2020-01-08 Thread Simon Slavin
On 8 Jan 2020, at 11:15am, 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

Re: [sqlite] Best way to store key,value pairs

2020-01-08 Thread Simon Slavin
On 8 Jan 2020, at 3:11am, Jens Alfke wrote: > Consider encoding the headers as JSON and storing them in a single column. > SQLite has a JSON extension that makes it easy to access values from JSON > data in a query. You can even index them. > > Simon’s suggestion (a row per header) is correct

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

2020-01-08 Thread Simon Slavin
I advise you avoid the idea of UPSERT when dealing with SQLite (or better still, all SQL). It is rarely implemented as a single operation, and you can get unexpected results with triggers and foreign key children. Think of your operation as a combinations of INSERT, DELETE and UPDATE and you

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

2020-01-07 Thread Simon Slavin
On 8 Jan 2020, at 12:00am, Michael Kappert wrote: > REPLACE INTO REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume that the command will do either an INSERT or a REPLACE. See the notes about REPLACE on this page: Is the behaviour

Re: [sqlite] Best way to store key,value pairs

2020-01-07 Thread Simon Slavin
On 7 Jan 2020, at 7:13pm, Andy wrote: > But it is variable number this fields and column are strictly defined. Maybe > all in one Sqlite text field or define for example 10 field which often will > empty? Parent Child database. One table has one entry per message. The other table has one

Re: [sqlite] How create database, tables,indices...

2020-01-07 Thread Simon Slavin
On 7 Jan 2020, at 2:11pm, Andy wrote: > Which function are for crating new database, tables, indices, columns, fill > tables. You do this with SQL commands, just like the commands you use to read data out of the database. On 7 Jan 2020,

Re: [sqlite] Size limits

2020-01-07 Thread Simon Slavin
On 7 Jan 2020, at 10:29am, Andy wrote: > What are reasonable limits for size Sqlite3 database file and large blobs? Unfortunately, recommendations (rather than hard limits) are closely tied to your OS and hardware. This is because the things you want to avoid are things like busting your

Re: [sqlite] A hang in Sqlite

2020-01-06 Thread Simon Slavin
On 6 Jan 2020, at 2:40pm, Yongheng Chen wrote: > I am sorry if I was polluting the mail list. As nobody mentioned that before > and reporting bugs to this mail list is what is said in the official website, > I just keep doing this. You are going what we told you to do. It is our fault. You

Re: [sqlite] A hang in Sqlite

2020-01-06 Thread Simon Slavin
On 6 Jan 2020, at 2:27pm, Noel Frankinet wrote: > What is fuzzer sql ? Is it some sort of random sql generator ? Yes. A fuzzer is a program. You feed it some SQL commands which obey syntax and work fine. Then it mixes them and tries small changes to make many other SQL commands. Almost

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

2020-01-05 Thread Simon Slavin
On 5 Jan 2020, at 5:01pm, Amer Neely wrote: > But the question still remains, why the different results? The optimizer gets improved from time to time in SQLite versions. SQLite solved the problem faster by breaking down your query differently: deciding whether to do one scan or use two

Re: [sqlite] Questions about your "Performance Matters" talk re SQLite

2020-01-04 Thread Simon Slavin
On 4 Jan 2020, at 5:49pm, Doug wrote: > for (i=1000; i--; i>0); If you have optimization turned on, your compiler might turn that into "i = 0". Optimization messes with a lot of benchmarks. Checking which optimization setting was used is one aspect of what Howard Chu was talking about:

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-03 Thread Simon Slavin
On 4 Jan 2020, at 7:23am, Mike King wrote: > This is the subject: > > Hex Password with System.Data.Sqlite (.Net Core) I suppose the bot thought you wanted to change your password for accessing the mailing list. ___ sqlite-users mailing list

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-02 Thread Simon Slavin
On 3 Jan 2020, at 12:44am, Mike King wrote: > Ok thanks. As this is a hobby project I don't have any cash for commercial > extensions. So the question becomes whether you actually need encryption in your hobby project, or you were just using encryption because you started off with an

Re: [sqlite] Causal profiling

2019-12-31 Thread Simon Slavin
On 31 Dec 2019, at 3:03pm, Richard Damon wrote: > I will give a short answer, in my opinion, of a few reasons on why C is still > a viable language. 5) SQLite has to work on your set top box. On your Wifi base station. On your Home hub. On the machine that runs the car park. All these

Re: [sqlite] Causal profiling

2019-12-30 Thread Simon Slavin
On 31 Dec 2019, at 2:21am, Alexander Vega wrote: > Could there be some compile time option to force Linux or Windows > statically? One of the problems with this is that there are a lot of utility libraries built around SQLite. These are libraries provided for general-purpose use, often a

Re: [sqlite] SQL help

2019-12-28 Thread Simon Slavin
On 28 Dec 2019, at 10:49pm, Barry Smith wrote: > Why does section 6.4.3.a.i of that linked standard not apply to his sub > select? I was thinking of the matching on (3,3), but perhaps you are right. ___ sqlite-users mailing list

Re: [sqlite] SQL help

2019-12-28 Thread Simon Slavin
On 28 Dec 2019, at 5:19pm, Barry Smith wrote: > Is not identifier resolution (search in the current scope first then step > out) part of the SQL standard? The way the SELECT statements are nested in the question is itself non-standard in any version of SQL I can cite in this message. > Damn

Re: [sqlite] SQL help

2019-12-28 Thread Simon Slavin
On 28 Dec 2019, at 3:46pm, x wrote: > In the embedded select it takes c from the outer table t1 but doesn’t flag > ‘ambiguous column’ for b in the embedded select. Is it standard that tables > at the same level are searched first for the column and tables from the > surrounding levels are

Re: [sqlite] When not using threads: should I switch to single-thread mode

2019-12-27 Thread Simon Slavin
On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: > Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. When > in one of the multithreaded modes, that query utilizes an average of 60% CPU, > compared to 12% when running singlethreaded. So if I understand this right, SQLite

Re: [sqlite] When not using threads: should I switch to single-thread mode

2019-12-27 Thread Simon Slavin
On 27 Dec 2019, at 7:46pm, Keith Medcalf wrote: > Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 minutes. > (Perhaps it disables some of the internal multithreaded sorters -- I don't > know). Can anyone explain this ? (To save you reading all the stuff I snipped, it's one

Re: [sqlite] When not using threads: should I switch to single-thread mode

2019-12-27 Thread Simon Slavin
On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: > My applications only use one thread (for the db stuff). Would it be a good > idea to switch to single-thread mode, or does that not give a real > performance improvement? On a desktop computer, or a mobile phone, the increase in speed is not

  1   2   3   4   5   6   7   8   9   10   >