SQLite Forum

Timeline
Login

50 most recent forum posts by user TripeHound

2021-05-13
09:12 Reply: How to convert am/pm time to 24hrs time format (artifact: b4ce3bfcec user: TripeHound)

Scott says:

...it seems intuitive that 12:00:01 am is one second after midnight, and 12:00:01 pm is one second after noon. By extension, it would be silly to consider 12:00:00 am as being offset by more than one second from 12:00:01 am.

Someone else might say:

...it seems intuitive that 11:59:59 am is one second before noon, and 11:59:59 pm is one second before midnight. By extension, it would be silly to consider 12:00:00 am as being offset by more than one second from 11:59:59 am.

I'm not claiming one view is more intuitive than the other: just highlighting that there's an "intuitive" view for both positions. And note in passing that the ambiguity is such that in 2008 the United States Government Printing Office reversed their designations: before 2008 they had "12 a.m. = noon" and "12 p.m. = midnight" (matching the second "intuition") and from then switched designations (to match Scott's intuition).

2021-05-05
08:53 Reply: Why do I have a `SQLITE_AUTOINDEX` index for my primary key? Is the table itself not an index on the PK? (artifact: 56aa9f593f user: TripeHound)

That index is to enforce the UNIQUE constraint on base32.

2021-04-10
14:11 Reply: useless constant term lead to incorrect output (artifact: 173c42f489 user: TripeHound)

Which version are you on? It works as you expect with 3.32.2:

sqlite> CREATE TABLE t0(c0 PRIMARY KEY);
sqlite> INSERT INTO t0(c0) VALUES (0);
sqlite> SELECT * FROM t0 WHERE (c0 NOT NULL) OR (c0 == NULL);
0
sqlite> SELECT * FROM t0 WHERE ((c0 NOT NULL) AND 1) OR (c0 == NULL);
0
2021-03-30
19:41 Reply: Atomically initialize database (artifact: 9b1f89b3fd user: TripeHound)

Initialize files under a different file name (but in the same directory), and rename them to foo.bar when successfully initialized.

Another possibility: you could have an already-initialised file as part of your "application package" which you just copy to the target file (foo.bar) when required. Probably little different than programmatically creating the file under a different name and then renaming, but just throwing the idea out in case it helps.

2021-03-29
09:20 Reply: Query Contraint with an if clause? (artifact: 6b3616c189 user: TripeHound)

Have you tried wrapping those UPDATEs in a transaction? I don't know Python, but I suspect executemany cannot be doing much different than:

BEGIN TRANSACTION
INSERT OR IGNORE...
INSERT OR IGNORE...
...
INSERT OR IGNORE...
COMMIT

so you should be aiming for:

BEGIN TRANSACTION
UPDATE ...
UPDATE ...
...
UPDATE ...
COMMIT

How you engineer a wrapping-transaction in Python I don't know, but I'd be very surprised if it isn't possible.

2021-03-27
14:49 Reply: Novice question about running UTF-8 on CLI from file. (artifact: ad6d5f92c7 user: TripeHound)

In my version (Windows 10 Pro, 64 bit if it matters) the "Save As" dialog has an "Encoding" drop-down which includes "UTF-8" and "UTF-8 with BOM". I'm making a (perhaps unwarranted) assumption that the former does not include a BOM. I would try first with a new, empty file: it's conceivable that it won't remove a BOM that's already there, although I suspect it might.

2021-03-25
22:36 Edit reply: json_tree documentation improvement: change type "string" to "text" (artifact: e56a7597e7 user: TripeHound)

For objects and arrays it does return the "JSON types": "object" and "array":

select * from json_tree('{"a1":[1],"a2":"foo"}');
key         value                  type        atom        id          parent      fullkey     path
----------  ---------------------  ----------  ----------  ----------  ----------  ----------  ----------
            {"a1":[1],"a2":"foo"}  object                  0                       $           $
a1          [1]                    array                   2           0           $.a1        $
0           1                      integer     1           3           2           $.a1[0]     $.a1
a2          foo                    text        foo         5           0           $.a2        $

so it's only "text" that it gets wrong. That probably cannot be changed for compatibility, but the docs should probably mention it. Perhaps:

 type TEXT,           -- 'object','array','text' (for a string),'integer', etc.
22:35 Reply: json_tree documentation improvement: change type "string" to "text" (artifact: 3e86be3c6d user: TripeHound)

For objects and arrays it does return the "JSON types": "object" and "array":

select * from json_tree('{"a1":[1],"a2":"foo"}');
key         value                  type        atom        id          parent      fullkey     path
----------  ---------------------  ----------  ----------  ----------  ----------  ----------  ----------
            {"a1":[1],"a2":"foo"}  object                  0                       $           $
a1          [1]                    array                   2           0           $.a1        $
0           1                      integer     1           3           2           $.a1[0]     $.a1
a2          foo                    text        foo         5           0           $.a2        $

so it's only "text" that it gets wrong. That probably cannot be changed for compatibility, but the docs should probably mention it. Perhaps:

 type TEXT,           -- 'object','array','text'( for a string),'integer', etc.
2021-03-23
11:07 Reply: Strange CLI .exit issue (artifact: f1ecabfa8a user: TripeHound)

A possibly pertinent question is why you sometimes have 120 lines or more in your script file after the .exit command?

On the face of it, if you have a script to some program, and that program stops consuming the script, then "something has gone wrong", and it doesn't seem unreasonable to indicate an error. In your case, it seems you have "a script + something else" (the stuff after the .exit)... my first instinct (without knowing more, hence the question) is to say the error is in the script for having (seemingly) superfluous lines in it.

2021-03-19
14:13 Edit reply: error changing data - attempt to write a readonly database (artifact: 4e47e93711 user: TripeHound)

Given that it works in one location, and not in another, including what those locations are in a problem report would be (a) helpful, and (b) (I would have hoped) obvious :-)

Best guess is the original location is somewhere that Windows (now) regards as "protected" in some measure (e.g. "C:\Program Files") and the recent update has imposed stricter permissions on that area (or, perhaps, has removed looser permissions you might have given it in the past).

Also, it should be noted that SQLiteBrowser is a 3rd-party product. If your investigations into "protected locations" doesn't lead anywhere, try accessing the database using the SQLite CLI command-line program to eliminate anything SQLiteBrowser might be doing.

14:11 Reply: error changing data - attempt to write a readonly database (artifact: 02e8989aea user: TripeHound)

Given that it works in one location, and not in another, including what those locations are in a problem report would be (a) helpful, and (b) (I would have hoped) obvious :-)

Best guess is the original location is somewhere that Windows (now) regards as "protected" in some measure (e.g. "C:\Program Files") and the recent update has imposed stricter permissions on that area (or, perhaps, has removed looser permissions you might have given it in the past).

2021-03-17
23:48 Reply: Math functions for arguments outside the domain should raise exception according to SQL Standard (artifact: 07c3936a79 user: TripeHound)

Or:

 LN(-1)
-------- = PI
SQRT(-1)

2021-03-12
16:48 Reply: Unable to load dll 'SQLite.interop.dll' after installing my app. The dll is present in the application folder (artifact: c8237a0d39 user: TripeHound)

Have a look at the System.Data.SQLite Downloads page... there (usually) is a dependency on the Microsoft Visual C++ Runtime Library. Installing Visual Studio will get these (as you found), but there are downloads from Microsoft of just the library, which you'd need to include in the MSI or deploy manually. There also appear to be (from skimming that page) "static" versions of the packages that don't have the dependency on the runtime libraries (but will, therefore, be bigger, I assume).

2021-02-28
16:38 Post: Ultra-minor typo in documentation (artifact: af2c9cf0f1 user: TripeHound)

On 4.3. ON DELETE and ON UPDATE Actions, the section:

  • SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.

should read "the column's default value" (with an apostrophe).

15:07 Reply: SQLite-related problem with the Signal app (artifact: 5624f24c61 user: TripeHound)

Possibly of interest old SQLite mailing-list thread: Feb 06, 2018; 4:57pm Header corruption. One of the first "hypotheses" was that "the rest of the program" might be closing a file, but still writing using the file-descriptor... and in the meantime SQLite had opened a file using that descriptor.

In that particular case (see this post) the problem was similar, but involved sockets (which are close-enough to file-descriptors to trigger/suffer-from the same problem).

2021-02-21
16:35 Reply: Update database item (artifact: af41e956e5 user: TripeHound)

Should any descendants of clade Amniotes1 actually want to store the week-by-week rankings over time, they probably should be in a different table that looks something like

WeekNo  Ranking   ArtistID
   1       1      23 (-> Justin Bieber in an "Artists" table)
   1       2      32 (-> Ed Sheeran)
  ...     ...     ...
   1      40      ??
   2       1      32 (Ed Sheeran knowns Justin Bieber of the top spot, pop-pickers)
etc.

1 According to A Bird’s-Eye View of Human Language and Evolution, the last common ancestor of birds and mammals (the clade Amniotes) lived about 310 – 330 million years ago.

2021-02-01
21:02 Reply: Approximate COUNT(*) using sqlite_stat4 (artifact: 267eb3dcba user: TripeHound)

There's this thread which ends with a post from Richard stating that (at the time of writing, 2017) ANALYZE does an exact count, but he has some code on a branch that does an approximate count, which might make it to trunk (no idea whether it did).

There's also an older thread (2014) that talks about counting rows in large tables, and includes this post from Richard that discusses a potential (but file-format-breaking) technique to do count(*) in logarithmic time by being able to store/compute a "rank" for entries in the b-tree table.

2021-01-28
10:50 Reply: Compiling amalgamation with Visual Studio 2019 (artifact: 720df5a754 user: TripeHound)

The reference to "linux" is (IMHO) slightly misleading: they are part of the "standard C library headers", and are/should-be present on both Linux and Windows (at least when suitable dev-tools, such as Visual Studio, are installed).

Out of the box (in my experience), Visual Studio SHOULD know where these are, and SHOULD know to use them: you would normally have to take deliberate steps for it not to find them.

For instance, using the "Community" edition of Visual Studio 2019, I just downloaded that zip file and -- with the Visual Studio environment enabled (i.e. having run VsDevCmd.bat) -- I can just type:

cl shell.c sqlite3.c

and it creates shell.exe with no problems.

So: How are you trying to compile? Through the IDE or from the command-line? Are you trying to compile the shell, or link the SQLite code into an existing project? Have you changed any of the default options in the IDE?

2021-01-16
07:29 Reply: "Hello, world" CGI with althttpd (artifact: 151a543486 user: TripeHound)

I'd be tempted to try either text/plain for the content-type, or make the message part something like <html><body><p>Hello, world!</p></body></html>.

2021-01-14
14:06 Reply: Using android AAR with modified sqlite_open_v2() - attach gives problems (artifact: 0f29669bf8 user: TripeHound)

I've virtually no experience of working with SQLite at the source level, and none with using it on Android, but with a "generic debugger's hat" on, it seems to me that one of the most likely causes (or, at least, the one to eliminate first before digging deeper) is that your custom libsqliteX.so has not been "built properly" in some manner.

Therefore, assuming you've not already done so, I would suggest building libsqliteX.so with no modifications to the source. If your app hangs/crashes, then it suggests the build process (or whatever is involved in getting your app to use your custom version of SQLite) is at fault.

If/when an unmodified custom-build works while doing "normal SQLite things", I would then make a small change to the code to "prove beyond doubt" that it is your custom version of SQLite that's being used: perhaps write something to a known file or logcat; make one of the interface-level functions return an easy-to-see-it's-different value. It probably doesn't matter at this stage if it makes SQLite unusable: you're just proving that you can alter the code and see the effects.

Once you know you can customise the normal SQLite code and successfully build and use it from your app the focus switches to whether you're "breaking" SQLite and/or Android in what you're trying to do... I assume this post by omen_wand relates to the same problem (if not, the two of you should probably collaborate!).

While I don't know much about the internals of SQLite's code, a phrase from that post "returns a handle with two attached database connections" feels like the sort of thing that would be easy to "get wrong": thinking purely off the top of my head, I can imagine that there might be things that happen later in the official sqlite3_open_v2() code that mean trying to do the equivalent of ATTACH at the point you're doing so means it's never going to work (internal structures haven't been initialised properly, that sort of thing).

Again thinking in a generic-debugger manner, I'd try working though things in stages. Start with a small change to sqlite3_open_v2(): perhaps instead of opening the passed-in database file, always open "test.db", or open "mydb-test.db" when "mydb.db" was passed-in. If that works, try something a little more involved.

If you get to this stage, you probably need someone who does know the innards of SQLite and you probably need to show examples of the changes you're trying to make...

2021-01-11
10:17 Reply: Possible Bug (artifact: ec313fa2cd user: TripeHound)

I've no idea how relevant it is to whether there really is a bug in SQLite (as opposed to SQLite being misused), but in skimming your "wall of SQL"1, I noticed:

...
BEGIN EXCLUSIVE TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;
INSERT INTO vt1(vt1) VALUES('integrity-check');
BEGIN  TRANSACTION;
...

However, according to SQLite's Transactions documentation, you cannot nest BEGIN statements (attempting to do so returns an error). You start your post with "The query below", but in reality you have a sequence of queries: I don't know how you are executing them, but are you correctly checking the return-codes of each query to ensure they are not in error?


1 I don't know whether this is a "real" query or a "constructed test case" (I strongly suspect the latter), but on StackOverflow, such a wall of almost impenetrable SQL would likely be met with a request for an MCVE – a minimal, complete, verifiable example. In other words, for you to cut your query down to the absolute minimum capable of demonstrating the problem.

2021-01-08
09:56 Reply: Additional Delimiter Support for the CSV Virtual Table Module (artifact: e2cea10f26 user: TripeHound)

But you have to decide on, or specify, the way of quoting or escaping an embedded delimiter. And suppose the user wants to use the character normally used as the "escape" character as their delimiter... nothing that can't be ironed-out, but not as simple as "just" changing the delimiter character.

2021-01-05
09:51 Reply: Make a time-range query faster when "GROUP BY day" (artifact: ef0d0d63b9 user: TripeHound)

The main question I had here is: why is the GROUP BY using a temporary B-tree since strftime(...) AS DAY is already sorted?

One thing I think you're missing is that while it may be "obvious" to a human that if the inputs to strftime are sorted, then its outputs will be sorted, that fact is (almost certainly) not known to the query-planner (QP).

SQLite functions can be marked deterministic, meaning that if you give them the same input(s) they will generate the same output, and the query-planner may use this knowledge to its advantage.

However, as far as I know1, there is nothing like (what might be called) a monotonic marker, that would assert that if a >= b then f(a) >= f(b) and therefore if a collection of inputs is sorted, the collection of outputs of a function so-marked will remain sorted.

Without that knowledge, the QP cannot assume anything about the outputs from strftime() (=DAY) and must therefore create a temporary b-tree to sort or group them.


1 I've not actually dug into the sources to check, but in the four or so years I've been following the SQLite mailing list, I've never heard mention of any ability of the QP to "know" that sorted inputs to a function will give sorted outputs. I'm sure someone will correct me if I'm wrong.

2020-12-03
09:54 Reply: Request: Conform to XDG Base Directory Specification for sqliterc configuration file (artifact: 4c5c832cf5 user: TripeHound)

Related post on the Fossil forum from earlier this year where something similar was done for Fossil: New Configuration-File Location Algorithm.

2020-11-30
15:58 Edit reply: FTS5 Handling of multi-token phrases (artifact: 17a2e0e7f3 user: TripeHound)

I believe you're reading things that aren't there.

if the document contains at least one sub-sequence of tokens

To me means split each document into tokens (e.g. "aaa bbb ccc ddd eee"). Look for all sub-sequences (e.g. "aaa bbb ccc", "bbb ccc", "bbb ccc ddd" etc.) and see if any of those match

the sequence of tokens that make up the phrase.

Here, there's no sub-sequencing mentioned; certainly no "degrading into subsets". If the phrase is "xxx yyy zzz" then only that sequence (somewhere in the source document) will match. A document containing just "xxx yyy aaa" won't match; neither will "bbb yyy zzz".

15:57 Reply: FTS5 Handling of multi-token phrases (artifact: b14faac1df user: TripeHound)

I believe you're reading things that aren't there.

if the document contains at least one sub-sequence of tokens

To me means split each document into tokens (e.g. "aaa bbb ccc ddd eee"). Look for all sub-sequences (e.g. "aaa bbb ccc", "bbb ccc", "bbb ccc ddd" etc.) and see if any of those match

the sequence of tokens that make up the phrase.

Here, there's no sub-sequencing mentioned; certainly no "degrading into subsets". If the phrase is "xxx yyy zzz" then only that sequence (somewhere in the source document) will match. "xxx yyy aaa" won't match; neither will "bbb yyy zzz".

2020-11-24
21:22 Reply: SQLite ODBC (artifact: 3097d06505 user: TripeHound)

Why not simply address the issue raised or simply ignore it

This is a "generic" reply, not specific to the original poster, their question, or Larry's response.

I can think of two reasons for not ignoring certain requests for help, while not directly addressing the question asked:

  • Just as programming is a skill/art that needs to be learnt, so too is asking a useful question. In both cases people can acquire skills themselves; in both cases, a "nudge" from someone to point them in the right direction can be helpful. One common problem is not providing enough detail (descriptions of what was attempted, instead of precise steps; "it didn't work" instead of actual/expected output); another is an X-Y Problem, where the "real" problem gets hidden behind a perceived problem.

  • Different message boards, forums, websites have different goals and focuses: some questions (and/or the answers they might spawn) are outside those goals/focus ("off topic"). Sometimes, directing a questioner to better places to ask such questions is the right thing to do.

2020-11-17
09:37 Reply: Serious Sqlite command line Problems after Windows 10 update (artifact: 3fd467df57 user: TripeHound)

Note, the utility (on my Windows 10 machine, at least) is rstrui.exe – with a second "r". I've no idea whether it used to be rstui, or whether that's just a common misspelling across t'internet.

2020-10-31
22:15 Edit reply: SQLite: -tabs option is missing (artifact: 36d4c152d8 user: TripeHound)

Either:

sqlite3 V1LS.sql3db -cmd ".mode tab" "select * from members"

or:

echo select * from members | sqlite3 V1LS.sql3db -cmd ".mode tab"

seems to work OK in Windows.

18:38 Reply: SQLite: -tabs option is missing (artifact: dea24851de user: TripeHound)

Either:

sqlite3 V1LS.sql3db -cmd ".mode tab" "select * from members"

or:

echo select * from members | 3 V1LS.sql3db -cmd ".mode tab"

seems to work OK in Windows.

2020-09-29
09:17 Reply: DISTINCT does not work in CTE (artifact: 25520cb153 user: TripeHound)

I don't the what either the SQL spec(s), SQLite, or other database engines say on the matter, but it strikes me that aliasing an expression involving column b as b – and then adding a WHERE clause involving b – is likely to lead to the equivalent of C's "undefined behaviour"... is the constraint meant to act on the original value of the column, b, or the aliased version, b+1? Even if it is defined how this should work, it feels like something "you should not do", at least for clarity's sake.

From a quick experiment, it looks like SQLite is using the original column b in the where clause, whereas your other SQL engine is using the alias. If you change the penultimate line to:

SELECT a, b+1 c FROM r1 WHERE c < 3

then SQLite gives the same as your other database.

07:06 Reply: Dump and online backupjexclude tables (artifact: 7bc0b0ff43 user: TripeHound)

As such, what is the downside to the .dump {relevant-tables} approach?

My assumption (without casting a vote for or against) is that the OP wants something like .dump --except <table-name> so that it will continue working "as is" if new tables (that they do want dumping) are added. Currently (as I understand it), if they add a table, they'll have to remember to add it to the "dump list". Of course, with their proposed enhancement, if they add a new FTS table, they'd have to remember to add it to the exclusion list.

2020-09-28
19:53 Edit reply: compiling extensions for Windows x64 with mingw-w64 (artifact: 788361b937 user: TripeHound)

<offtopic>

Keith, given your seemingly-encyclopaedic knowledge of all things to do with dates, time-zones and the like, I'm surprised at you confusing a "solstice" ("when the Sun appears to reach its most northerly or southerly excursion relative to the celestial equator") and the "solidus" (the Unicode name for the "slash" and, when reversed, the "backslash"). :-)

</offtopic>

(Scott's reply wasn't there when I started this...)

19:51 Reply: compiling extensions for Windows x64 with mingw-w64 (artifact: cdd3c114bc user: TripeHound)

<offtopic>

Keith, given your seemingly-encyclopaedic knowledge of all things to do with dates, time-zones and the like, I'm surprised at you confusing a "solstice" ("when the Sun appears to reach its most northerly or southerly excursion relative to the celestial equator") and the "solidus" (the Unicode name for the "slash" and, when reversed, the "backslash"). :-)

</offtopic>

2020-09-26
14:16 Reply: FTS5 rows not searchable with match (artifact: 01f5122a69 user: TripeHound)

Is:

CREATE TRIGGER __item_search_ai INSTEAD OF INSERT ON item_view BEGIN INSERT INTO item_view(...

correct, or should it – to match the other triggers – be ...INSERT INTO item_search(...``?

2020-09-14
15:35 Reply: Connect to sample Database (artifact: c507bd204f user: TripeHound)

First, to the best of my knowledge, that tutorial site has no official connection to "core" SQLite and this forum... any difficulties with the tutorial are probably best addressed to that site.

Having said that, your problem is confusing a CMD command-line with an SQLite command-line (I've not looked at the tutorial, so I don't know if it's their fault for not making it clear enough :-))

To address the "cannot get out of"... any SQL statements given to the SQLite shell needs to be terminated with a semi-colon. These commands can span multiple lines (hence the ...> prompt), so you are being prompted to enter the rest of the command, together with the terminating semi-colon.

To get out, type ; and press ENTER (you'll get an error message of some kind: ignore it) then type .quit (or just .q) and press ENTER. This will return you to the command-prompt.

(In passing, any command starting with a full-stop are "internal" commands to the SQLite shell, and are not SQL statements. As such, they do not need to be terminated with a semi-colon).

Your main problem is that sqlite3 c:\sqlite\db\chinook.db is a command-line command: start the SQLite shell and open the database c:\sqlite\db\chinook.db. If you give this now (instead of just sqlite3) things should get one step further.

You can use the SQLite shell's .open command to open a database after you've launched the shell, but you have to "fight" with backslashes (either doubling them, or using forward-slashes instead).

2020-09-13
21:08 Reply: Placeholder for table name when inserting data? (artifact: 5e858702a7 user: TripeHound)

I'm by no means a database expert, but having a hundred tables is very often not the best way of doing things. If your 100 tables are essentially storing the same (or very similar) types of data, it is often better to put all records in the same table, with an additional field to indicate which "table" it came from.

For instance, in a manual system you might keep a list of books on Sci-Fi, a list of books on history and another list of books on cooking. In a database, it is usually better to have one "Books" list, with a field indicating which type of book it is (often as a "type code": 1=SciFi, 2=History etc.).

Similarly, if the original tables are split by time (e.g. a different table for each month), then it is usually better to use one database table with a date or month-number field.

With a (semi) manual system, it's can be easier to have separate lists, so you're not hunting for the entry you want among lots of irrelevant entries. With a database, it's (usually) just a matter of adding WHERE BOOKTYPE=2 to the query.

Example benefits of reduced numbers of tables (apart from the "don't repeat yourself" practice you mention) include: it is easier to make cross-subject queries (how many books in total were published before 2000); it is easier to change how the data is stored (say you suddenly decide you want to record the number of pages in each book). With 100 tables, you'd need to make 100 queries and combine the results, or make alterations to 100 table structures. With one table, things are much easier.

2020-09-12
13:09 Reply: INNER JOIN error (artifact: f2044f5648 user: TripeHound)

Whenever you use a third-party tool/wrapper (such as SQLite Studio or some (unspecified) Python wrapper), be aware that they will (almost always) not be using your CLI instance of SQLite.

The docs state that the UPDATE ... FROM variant was only introduced in version 3.33.0 of SQLite, so run:

select sqlite_version() ;

from within SQLite Studio and/or through Python to see what version of SQLite they are using.

2020-09-10
13:42 Reply: SQLITE_ENABLE_UPDATE_DELETE_LIMIT (artifact: 06d1c392fb user: TripeHound)

The above check-in ensures the necessary define (SQLITE_UDL_CAPABLE_PARSER) is "baked-in" to the amalgamation if SQLITE_ENABLE_UPDATE_DELETE_LIMIT is defined when creating the amalgamation...

...on a slightly related matter, there was a recent thread where someone was trying to compile the amalgamation with some of the several SQLITE_OMIT_xxx options that only work when building from the canonical sources. Given this crops up every now and again (and despite it being documented at Options To Omit Features), would it be sensible/possible to include in amalgamations assembled without such options something like:

#ifdef SQLITE_OMIT_PRAGMA
#error SQLITE_OMIT_PRAGMA must be defined when building the amalgamation
#endif

In light of (my emphasis):

Important Note: The SQLITE_OMIT_* options may not work with the amalgamation. SQLITE_OMIT_* compile-time options usually work correctly only when SQLite is built from canonical source files.

from the above page, you may only want to do this for "omit" options that are known not to work properly with the amalgamation, or – if it's (justifiably) felt trying to keep track of which ones won't work / might work / will work is too much trouble, you could decide to "ban" all the "omit" options unless applied to the canonical sources.

(If something like this has already been considered and rejected, then please ignore this post!)

2020-09-04
14:47 Reply: Join Question (artifact: 9921c0eae5 user: TripeHound)

One method, (there may be others) is to use sub-selects:

select
    Support.main as MainKey,
    ( select Name from Names where Names.Key = Support.Main  ) as MainName, 
    Support.Backup as BackKey,
    ( select Name from Names where Names.Key = Support.Backup) as BackName
from Support ;

MainKey     MainName    BackKey     BackName
----------  ----------  ----------  ----------
10          Bob         72          Sally
106         Amanda      73          Jose

For symmetry, I've used sub-select for both names; you could leave the JOIN in place for one of the names and use a sub-select for the other.

2020-09-01
21:37 Reply: Which one - System.Data.SQLite or Microsoft.Data.SQLite (artifact: e11e2327a4 user: TripeHound)

You might also try running pragma compile_options; (see docs) to see how each has been built.

2020-08-28
21:52 Reply: Recursive CTE (artifact: e4375f99ca user: TripeHound)

Italy has 1 'exteriorring' (obviously)

What about Sardinia, Sicily etc.? Should Italy not have additional 'exteriorring' polygons for them?

2020-08-27
14:19 Reply: CHECK violation during table definition ignored, normal or bug? (artifact: e51c2a83a7 user: TripeHound)

returning just the table name is rather redundant as an INSERT or UPDATE can only work on a single table, so there is no ambiguity

Unless triggers are involved, in which case the table with a failing CHECK clause may not be the one mentioned in the INSERT or UPDATE command:

sqlite> create table x (a check ( a < 10 ) ) ;
sqlite> create table y (b check ( b < 20 ) ) ;
sqlite> create trigger yx after insert on y begin insert into x values ( new.b ) ; end ;
sqlite> insert into y values ( 24 ) ;
Error: CHECK constraint failed: y
sqlite> insert into y values ( 14 ) ;
Error: CHECK constraint failed: x
2020-08-22
21:25 Reply: Weird warning on the Xcode/OSX (artifact: edad96bcb8 user: TripeHound)

What version of sqlite3.c are you using? Can you check if it has guards around the definitions of MIN and MAX? At line 14247 in my copy (v3.33.0), there's:

#ifndef MIN
# define MIN(A,B) ((A)<(B)?(A):(B))
#endif
#ifndef MAX
# define MAX(A,B) ((A)>(B)?(A):(B))
#endif

That came from sqliteInt.h. There's a similar, guarded definition at line 166006 (that seems to come from fts3Int.h) and another at 187836 (that comes from rtree.c). The latter both use x and y instead of A and B. Finally, at line 207899 (originating from fts5.c), there is what at first glance appears to be an unguarded pair of definitions:

#define MIN(x,y) (((x) < (y)) ? (x) : (y))
#define MAX(x,y) (((x) > (y)) ? (x) : (y))

but there's a #ifndef SQLITE_AMALGAMATION wrapping the section they're in, which I ass.u.me means they'll be excluded when compiling sqlite3.c (but does this mean they could clash with a prior definition if fts5.c is compiled outside the amalgamation?)

2020-07-30
09:48 Reply: Database Growing by itself to huge sizes (artifact: 49dada7744 user: TripeHound)

I don't know if it's relevant to your problem, but there was an email-chain from 10 Jan 2020, titled "Unexplained table bloat" (which doesn't appear to be in the forum) with a possibly-similar sort of problem: the database size was much larger than the apparent size of the content. That discussion drifted into many side topics, but the gist seems to be that what was being stored was essentially (large) strings/blocks-of-bytes with embedded NUL characters (0x00 bytes) in them. The apparent size only showed the length up to the "terminating NUL byte", but the database was storing the full collection of bytes.

I don't think the code that caused the problem in that case was shown, but the following (incomplete and untested) snippet should demonstrate a possible cause:

char bigBuffer[10000];
strcpy( bigBuffer, "A short string" );
sqlite3_bind_text( stmt, 1, bigBuffer, sizeof bigBuffer, NULL );

As (I think) I understand it, if the fourth parameter of sqlite3_bind_text has a positive value, then that many bytes will be stored in the database (10,000 in this case), even if the block of memory given by the third parameter contains a zero-byte. From that page:

If any NUL characters occurs at byte offsets less than the value of the fourth parameter then the resulting string value will contain embedded NULs. The result of expressions involving strings with embedded NULs is undefined.

Examining select * ... after the above would show only the string "A short string", but it would be consuming 10,000 bytes of space within the database. The SQL length(...) function would – I believe – only show 14, but the C API function sqlite3_column_bytes should show 10,000 according to a reply Richard made in the aforementioned email chain:

There's no way at all, to know the length of a text column with embedded NULLs?

You can find the true length of a string in bytes from C-code using the sqlite3_column_bytes() interface. But I cannot, off-hand, think of a way to do that from SQL.

It might be worth reviewing your code to see if anything somewhat-similar might be occurring.

2020-07-23
19:49 Reply: Possible bug in .read when a directory is specified? (artifact: 01d12ae640 user: TripeHound)

For info, the bottom of this post on the Fossil forum is possibly the "same sort of thing"... reading a directory as though it were a normal file.

2020-07-17
21:14 Reply: SELECT using variables (artifact: 8b7074d0d0 user: TripeHound)

It's also been (poorly) asked on StackOverflow, including the ubiquitous "but it didn't work" with no hint of how it didn't work nor any error messages that might have been produced.

2020-07-15
08:45 Reply: The SQLITE_DETERMINISTIC flag isn't work on 3.32.3 release (artifact: eca0e5f1d1 user: TripeHound)

There was another similar discussion at [bc6ed1f746]. As Richard says, DETERMINISTIC doesn't mean SQLite will only call a function once, just that it is allowed to assume that every call (with the same parameters) will return the same value (which may result in a reduced number of calls).

2020-07-13
08:40 Reply: Setting non-binary columns to null in a table with a binary column populated with data creates free space and a larger file (artifact: 2cc03ad895 user: TripeHound)

The schema in the above reads

CREATE TABLE [Data1](
  [Data] BLOB, 
  [Items_id] INTEGER, 
  [Key] TEXT);
2020-07-11
14:05 Reply: .selecttrace unknown and strange errors (artifact: 7289c873d3 user: TripeHound)

I believe the problem is the line:

A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED

which doesn't prefix C1_QTY_PRODUCED with NEW. or OLD..

Please don't take this the wrong way – this comes from many years of experience from the days when the best diagnostic you could hope for was "HOW?", "WHAT?" or "SORRY" – but from the few threads you've posted ostensibly about problems with .selecttrace, I think you need to try and develop your "inner debugger".

I've done very little with triggers, but after cutting-and-pasting your code, and confirming that I get the same error, I simply searched for C1_QTY_PRODUCED within the block of code. First check is that it does appear, as typed, in the CREATE TABLE statement (so: not a simple typo).

Next, in trying to discover which reference SQLite was complaining about, it was fairly obvious (to me, after much general experience) that all other references both to it and other columns in C1 were preceded by NEW. or OLD.. Coupled with my basic knowledge of triggers, I tried adding NEW. to the above line and it "worked" (as the trigger threw up some messages).

I haven't followed the logic of the trigger to know whether it should be OLD. instead, but I'm fairly certain that it should be one or the other.

More ↓