SQLite Forum

Timeline
Login

50 events by user slavin occurring on or before 2021-09-15 11:42:17.

More ↑
2021-09-15
11:42 Reply: Select query returning: no such table error artifact: ca3b8865ac user: slavin

We've seen this sort of thing before. It often turns out that something is stomping on the memory SQLite uses to keep track of data or file handles, causing it to look in a corrupted list of tables, or in a non-existent file. I'm not saying that this is the cause in your case, just that it's something to look for.

Do you have anything else accessing the same file ? It could be the SQLite API being called from another thread, process or app. But it could also be an automatic backup routine which locks the file while it backs it up. And in a non-Linux context I would also be suspicious of a virus-checker.

2021-09-14
14:48 Post: DELETE FROM … RETURNING artifact: 9ceaa4a1e0 user: slavin

The page https://sqlite.org/lang_delete.html includes the following:

The ORDER BY clause on a DELETE statement is used only to determine which rows fall within the LIMIT. The order in which rows are deleted is arbitrary and is not influenced by the ORDER BY clause. This means that if there is a RETURNING clause, the rows returned by the statement probably will not be in the order specified by the ORDER BY clause.

(The same thing happens with UPDATE). Someone told me, without even thinking about it, that that was a bug. I wanted to hear opinions.

I understand why SQLite does it. I don't need someone to explain the programming. Just whether an intelligent programmer would consider this a bug or not.

2021-09-12
14:09 Reply: Android and desktop Java library artifact: 725a45707a user: slavin

I assume you mean that all 10,000 rows are part of the same transaction. It might go faster if you try 1,000 rows and use ten transactions. Or it might not.

SQLite's processing is extremely fast. Almost all the time taken by SQLite is about storage access. Don't think about cores and GHz, think about memory bus speed, and time taken to read and write a sector.

Your Samsung phone is writing to Flash memory. Your laptop is passing the data to a storage subsystem, which stores it in a RAM cache then says "job done" (also in the background writes the cache to the real storage). Flash memory is slower than RAM.

2021-09-11
16:39 Reply: Android and desktop Java library artifact: 495997e71e user: slavin

Android:

https://devtut.github.io/android/sqlite.html

https://developer.android.com/jetpack/androidx/releases/sqlite

https://github.com/requery/sqlite-android

https://github.com/ArturVasilov/SQLite

There is no clear agreement that one of these is definitely the best option for all SQLite development. You will need to do some reading and make some choices.

I don't know enough about Java to answer that part of your question.

2021-09-10
06:10 Reply: Method to temporarily disable Not Null constraint? artifact: f58551be36 user: slavin

Option A: remove the constraints

Option B: make a table which is a copy of the entity table, but lacks the constraints. Load your data into that. Once the data is all there (which I think means there will be no NULLs left) copy that table to the real table using the INSERT … SELECT command which is optimized for speed. Then DELETE FROM your temporary table.

Comment on your post:

given that hundreds of tables are involved

This is always a red flag in a SQL database. If two tables have the same definition, or your table names include numbers or dates, it's probably a sign that they should be the same table, with one extra column. If you're right now at a stage where you are designing the schema, think hard about this because it will save you a lot of trouble in the long run.

02:15 Edit reply: NEXT VALUE FOR in SQLite artifact: 9d90f945e7 user: slavin

In your situation I might keep the counter in another table of the same database. Make a Config table, and in one column of one row, keep your counter.

UPDATE Config
    SET counter TO counter + 5
    WHERE configItem='customers'
    RETURNING counter

This does the update in one operation and returns a value which lets you figure out the ids for the 5 rows you want to insert. it is ACID, and threadsaf, given that you are correctly using separate connections.

02:15 Reply: NEXT VALUE FOR in SQLite artifact: f1524c8da4 user: slavin

In your situation I might keep the counter in another table of the same database. Make a Config table, and in one column of one row, keep your counter.

UPDATE Config
    SET counter TO counter + 5
    WHERE configItem='customers'
    RETURNING counter

This does the update in one operation and returns a value which lets you figure out the ids for the 5 rows you want to insert. it is ACID, and threadsafe (given that you are correctly using separate connections.

2021-09-09
13:47 Reply: How to insert duplicate rows? artifact: 335930bfb9 user: slavin

Thanks for the headsup. I saw a comment about the formatting above but didn't understand it. You explained it.

Okay, so the question isn't what I thought it was. Please ignore my previous post. Here's what I should have responded.

There's no reason why duplicate INSERTs shouldn't work. What happens when you try it ?

13:43 Edit reply: NEXT VALUE FOR in SQLite artifact: a04e556795 user: slavin

https://sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

But what are you going to do with it ? You don't need to know it for the next INSERT. Will you be using it for a FOREIGN KEY ?

Also, assuming that your application really is multi-threading and not just multi-processing, are your multiple threads all using the same connection to the database ? That's not considered safe unless you understand exactly how they interact. They should be using different connections.

13:37 Reply: NEXT VALUE FOR in SQLite artifact: 78b66a3c9b user: slavin

https://sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

But what are you going to do with it ? You don't need to know it for the next INSERT. Will you be using it as a FOREIGN KEY ?

07:59 Reply: How to insert duplicate rows? artifact: f70dc8384e user: slavin

Your table definition has incorrect syntax. Doing it that way gives you a column called id TEXT which has no affinity. The double quotes should not be there at all:

id TEXT

If you must use double quotes for your column names then they should be around the column name, not the affinity:

"id" TEXT

and you should use them everywhere you refer to a column name.

2021-09-08
18:13 Reply: solved disk I/O error on F2FS artifact: 63a651757f user: slavin

Most likely that your hard disk is corrupt, and SQLite is running into it when trying to make temporary files. Especially since you report problems with FireFox and Discord too.

In your situation I'd be looking for things like fsck and ntfsfix. Not to mention backing up all my files on another drive.

18:11 Reply: WAL/SHM files do not get deleted with ReadOnly flag artifact: 3ac8cfca59 user: slavin

The 'ReadOnly' status tells SQLite that it should not make any changes.

12:49 Reply: Segmentation fault in function isLikeOrGlob() artifact: 39ccb68762 user: slavin

Just wanted to praise this report.

  1. Test the right version of the source code: source tree, not amalgamation, current version.
  2. Find a genuine crash.
  3. Find how to reproduce the crash with 100% reliability.
  4. (optional) find suspicious lines of source code
  5. (optional) bisect previous versions to find which version introduced bug
  6. Send a polite and informative message to the correct forum including the above.

I've been receiving and submitting bug reports for the last 40 years and wish they were all this good. Apologies for interrupting your scheduled technical discussions.

2021-09-07
15:24 Reply: Automatic indexing (idle question) artifact: 1912e63e6b user: slavin

Not in this context, because the order of the columns matters.

I don't understand why you mentioned it in the first place.

2021-09-06
18:58 Reply: Automatic indexing (idle question) artifact: 594bed7b7c user: slavin

I was envisioning SQLite keeping a set of statistics, like the ones prepared by ANALYZE. In its simplest state, for each set of search/sort conditions (combinations of WHERE and ORDER BY), keep stats of how often it is used. Every so often analyze this data to figure out which indexes would be most useful. Then decide whether to make the changes right now or not.

Yes, there are a ton of considerations not included in the above, but that was my top-level understanding of how it would work.

And as stated upthread, making an index on each column is not a solution to any reasonable problem. When I find databases where people have done this they usually betray other misunderstandings of SQL.

2021-09-05
21:53 Post: Automatic indexing (idle question) artifact: d6677078fd user: slavin

I'm just pondering this. I don't have a specific project or use in mind.

Various parts of SQLite look like they're part of a move towards automatic indexing. I'm wondering how far we are away from this. The average programmer would not bother using CREATE INDEX … at all, it might be left to programmers with unusually good understanding of SQL. I'm thinking of SQLite not only creating its own indexes, but also deleting indexes which go unused, or should be subsumed by a longer index.

Presumably the programmer would somewhere set a number which changes the balance between filespace used and time saved. And another number which says whether actual index creation can be done by everyday API calls, or whether it should be left for runs of a special PRAGMA.

Is this a solved problem ? Or perhaps a problem that someone proved cannot be usefully solved ?

2021-09-04
14:51 Reply: The characters to be escaped in Sqlite3 to prevent Sql Injection artifact: eca1100f5f user: slavin

Tim's reply is the key one here. Prepared statements have no dangerous characters, including 0x00. But to your point, the apostrophe character is the only character you need to worry about, and you've already found what to do about it.

You might want to escape other characters including the percent sign if you're using user-sourced strings for searching, but that's a detail of how your program works, and a dangerous thing to do anyway. If you want to do it I suggest you read about the ESCAPE clause, as described in

https://sqlite.org/lang_expr.html#the_like_glob_regexp_and_match_operators

2021-09-03
13:52 Reply: What happens if I define LONGDOUBLE_TYPE as double? artifact: 506dd87ea5 user: slavin

That's one use for the test suite(s). Try it, run the entire test suite, and see what fails.

2021-09-01
12:32 Reply: Finding overlap of boxed coordinates artifact: e9408c7224 user: slavin

Can be done by SQLite using multiple CASE constructions, or nested IF statements. However, It's not going to be fast, compact, neat, or easy to debug. IMO it's a task which should be performed in a programming language rather than trying to make a complicated SQLite command.

Given two isometric (same axes) rectangles A and B, with boundaries l, r, b, t, intersection rectangle C is as follows:

C <-- A
IF B.l > C.l, C.b <-- B.l
IF B.b > C.b, C.b <-- B.b
IF B.r < C.r, C.r <-- B.r
IF B.t < C.t, C.t <-- B.t

If C.l >= C.r, there is no overlap
If C.b >= C.t, there is no overlap
Otherwise overlap is C.

Edge cases (e.g. left line of A is exactly the same as right line of B, or wrap around at -180/+180) must be considered within the context of your requirements.

Suppose you have the coordinates of a rectangle A, and your left longitude column of the database is indexed. You can quickly dismiss a swathe of rectangles B which won't intersect with A using

SELECT … WHERE B.l < A.r

2021-08-26
21:01 Reply: Recursively Import 10 GB sized csv file to sqlite fast without blocking reads artifact: ed5f8061f2 user: slavin

If blocking a database is your major problem, do the above CSV import to a different database file, then write your own code to copy rows from that database to the one being read. That way you can use whatever transaction sizes you want. Or perhaps the single optimised command INSERT INTO table SELECT ... will be good for you.

The ATTACH command is used to open two databases on the same connection.

2021-08-25
01:36 Reply: Why WAL file (-wal) may disappear? artifact: fef23cf2ff user: slavin

Something just occurred to me. SQLite does automatically delete the WAL file if there are no open connections to it. Is it possible that you're checking while all connections are closed ? Perhaps whatever SQLite library you're using closes connections automatically if your program is not in the foreground, or something.

2021-08-23
02:42 Reply: about "strict" mode artifact: 2ef5e46b63 user: slavin

There's an argument for not having STRICT TABLEs at all. Just have a LAX keyword, or whatever a better word would be.

To make the whole database STRICT use the PRAGMA. If you have some legacy table data you can't get rid of which violates STRICT ,declare the table as LAX before you use the PRAGMA. This automatically makes appropriate indexes and views LAX too.

00:37 Reply: Coverting 305GB CSV to sqlite database artifact: 64e5159ff7 user: slavin

I would test it with the top 100 lines first. Apart from that, you seem to know what you're doing.

2021-08-22
16:26 Post: New Hipp podcast artifact: d768ddc945 user: slavin

https://changelog.com/podcast/454

" This week, Richard Hipp returns to catch us up on all things SQLite, his single file webserver written in C called Althttpd, and Fossil – the source code manager he wrote and uses to manage SQLite development instead of Git. "

No transcript yet, but that channel generally adds them to the page later.

15:56 Post: About STRICT tables artifact: ac9d8f7248 user: slavin

A column about strict tables has appeared in the draft site:

https://www.sqlite.org/draft/stricttables.html

Possibly a good idea to raise a discussion on it. I'll open with a few things:

  1. The syntax to mark a table as strict puts the word STRICT at the end of the CREATE line. Is there a good reason why the word doesn't go after CREATE ? I'm likely to 'lose' a word at the end of the line: my eyes could pass over it and not see it.
  2. Could correct use of single and double quotes be enforced for the strict CREATE commands ? Or is that better associated with the strict PRAGMA ?
  3. Could the strict mode make all tables STRICT, whether declared STRICT or not ?

For reference, here's the page about strict mode:

https://sqlite.org/src/wiki?name=StrictMode

06:55 Reply: about "strict" mode artifact: 7456cd275b user: slavin

SQL engines which do support DATE, TIME, or DATETIME don't store in the database the thing you put in the SQL command. They convert it to an integer, and store that. For the sake of argument, you could say that all DATEs are stored as days since 1 January 1970, and all TIMEs are stored as milliseconds after midnight. When you ask for a value from a DATE or TIME field it gets produced as a string in one specific format.

So SQLite could introduce a DATE datatype by accepting only a string in one specific format. A later version of SQLite could accept dates in another format too. It just won't convert into the second format when you read a DATE value from a database. It won't break compatibility, either backward or forward.

2021-08-21
13:39 Reply: about "strict" mode artifact: 99472fab32 user: slavin

I'm not against your suggestion, but both the web page and you missed out a vital consideration, so I'm going to bring it up here.

There's a nasty question of whether the strict setting should be stored in each database file. Or has to be set as each connection is opened like the connection timeout. But that's beyond the scope of my post.

Consider an existing database which was created without strict mode. It has all the sorts of faults listed in https://sqlite.org/src/wiki?name=StrictMode: missing keys, table definitions and TRIGGER with the wrong quotes, NULL primary keys values, all that badness, and more that people haven't thought of yet. A new programmer takes over the project and uses their own code (not a custom utility like the CLI) to turn on strict mode. What happens ?

Should turning on strict mode involve a lengthy check for all these things ?

  1. Running the checks when switching to strict mode: Could take minutes or, rarely, hours.
  2. Not running the checks when switching to strict mode: All operations throughout all programs which access the database now have to deal with formerly unanticipated error reports from a lot of API calls.
  3. A strict setting is stored in each database file, and is set like PRAGMA page_size. To convert an existing database to strict mode you must VACUUM it. The extra checks take place during the VACUUM. VACUUM is changed so that it can return syntax error and other error results. VACUUM is changed so that it will check the integrity of foreign keys if strict mode has just been turned on.

Given that the check for strict-compliance could already be lengthy, and relies on the integrity of the database, should it start off with an integrity_check ?

2021-08-20
13:04 Reply: Trying to "Open Database" Safari History.db file and get Error: "could not open safari database file reason: unable to open.. artifact: fcc0327ba1 user: slavin

trying to use this software

The SQLite team made only one piece of software a non-programmer would use. It's very technical, and it's unlikely you used it. On a Mac you would have to be running Terminal.app to get at it.

I'm guessing you used SQLite Browser or something like that. Those programs are written by other people and the SQLite team can't support them. SQLite itself does not have error messages like 'unable to open database file'.

Can I ask why you're not using Safari to look at your Safari history ? That would seem the obvious way to do it. If I know why that isn't working I might be able to help.

Also, do you have the pages you need bookmarked, or just in your history ?

2021-08-18
14:50 Reply: random DB locks artifact: cc4f5da458 user: slavin

Bingo.

Virtual servers have a poor reputation for supporting file locking. Yours might be simulating full XFS support, but the XFS disk is not a real disk, it's part of the simulated hardware. And the communication between the virtual computer and the virtual storage may not provide full support for locking. Consequently you may be running into

https://www.sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations

In your case, you don't have two competing processes causing corruption. But you're getting lock failure anyway.

I am guessing that identical software to yours won't have this problem if it is run on a real computer with a real XFS drive inside it. But I have no way to prove it, and it's possible you have a different problem none of us have thought of. Do you have any way to test this ?

01:22 Reply: ADO.NET INSERT/UPDATE RETURNING statement question artifact: a05aa153bb user: slavin
sqlite> .mode table
sqlite> INSERT INTO "test"("name") VALUES ('johnny2') RETURNING id,name;
+----+---------+
| id |  name   |
+----+---------+
| 4  | johnny2 |
+----+---------+
sqlite> INSERT INTO "test"("name") VALUES ('johnny3') RETURNING "id","name";
+----+---------+
| id |  name   |
+----+---------+
| 6  | johnny3 |
+----+---------+ 

I suspect this bug, if it is a bug, is in System.Data.SQLite . You have posted useful version numbers, so someone familiar with it should be able to help.

2021-08-17
23:37 Reply: random DB locks artifact: a94f483471 user: slavin

Please run an integrity check on the database.

How are you doing the COMMIT ?

Is the database stored on the same computer that is doing the processing ? If not, how is the storage accessed ?

12:12 Reply: Is it possible to put the journal file to non-volatile RAM artifact: 74be7f0953 user: slavin

You can't do that, but there's probably a way to get the overall result you want.

What is the point in setting SQLite to store a journal file in memory, but then moving that memory to non-volatile memory ? Doesn't that have the same result as just leaving the journal file in the same folder as the database, which is the default ?

What result as you searching for ? And what kind of hardware are you using ? A normal desktop computer, a smartphone, or an embedded processor ?

2021-08-15
13:10 Reply: wal checkpointing very slow artifact: 94c40d994a user: slavin

[the following is oversimplified to make it easy to understand]

In WAL mode, until you reach a checkpoint, SQLite doesn't update the database. All changes are logged in the WAL file. When a checkpoint is processed, SQLite has to go through the log of changes, figure out which ones haven't been obsoleted by later changes, and make appropriate changes to the database file. Then it can start again with a new blank WAL file.

That's why the checkpoints are the bottleneck: really SQLite doesn't do much writing between checkpoints.

In your situation of many small unpredictable changes, it may be that WAL mode is not the best mode to use. You might be better off with

PRAGMA journal_mode = TRUNCATE

Perhaps you could try it.

02:32 Reply: pragma integrity_check; Error: disk I/O error artifact: 52886c2567 user: slavin

Yeah, but after taking the copy do an integrity check immediately to make sure it's not already corrupt.

We had a thread here from someone who insisted that their database kept getting corrupted. But it turned out that after the database file failed the integrity check they continued working with it, apparently thinking that SQLite automatically fixed all the problems integrity_check reported.

2021-08-14
21:05 Reply: Converting from sqlite to sqlite3 artifact: 95a8f14560 user: slavin

As DRH wrote, you're using an API that has nothing to do with SQLite. I think you're using SQL Server API calls. You could not pass a setup string when you opened a SQLite1 database. And SQLite doesn't have API calls for transactions, you just use normal calls and pass them things like "BEGIN" and "COMMIT".

So we can't tell if you mean you're converting SQL Server API calls to SQLite calls, or something else. Perhaps delete this thread and make another which tells us what you're doing more precisely.

13:02 Reply: pragma integrity_check; Error: disk I/O error artifact: 229e2b5d93 user: slavin

Page 35: unable to get the page. error code=266

and/or sometimes

Error: near line 1: disk I/O error

You appear to get different, random, errors each time you try an integrity check. Is there any chance that you have a real hardware failure here ? Your reports would all be explained if your storage device was developing a fault. You would start out getting occasional errors, and get more and more as the fault developed.

Can you run something irrelevant to SQLite that tests the partition's integrity ? 'chkdsk' or something ?

2021-08-13
13:06 Reply: pragma integrity_check; Error: disk I/O error artifact: aba3e16947 user: slavin

While checking that, also check that Mozilla isn't actually running while you're doing the integrity check. SQLite is meant to take care of that problem, but there's a chance that Mozilla assumes that it has sole access to that database file.

2021-08-12
15:57 Reply: Test ACID properties SQLITE artifact: c09b3b60b6 user: slavin

If you have a smartphone, a car, a SatNav device, a TV, a PVR, a security camera, a power smartmeter, a smartwatch, a games console, you have nine copies of SQLite. If you have a Windows/Apple computer too, you have another four or five copies.

Some of the less expected devices I have found SQLite in:

  • the machines in carparks (parking lots ?) which record entry/exit/payments
  • the hand-held devices some parking inspectors use to print tickets
  • a lathe which you can program with curve profiles
  • a hand-held gas detector used to spot leaks
  • the smartmeter connected to my power supply
15:37 Reply: Documentation: list of addons and extras artifact: 9b1f6db6d6 user: slavin

I think extensions are covered well, but they are a minority of what the people I am picturing want. And those people won't be looking at source code at that point, because they'd have to search the whole codebase, because they don't know what they're looking for yet.

My imagined users have an idea in their head of 'copy an active database' or 'quick word search' or 'interface with System.Data' or 'difference between two databases'. They don't know the name SQLite uses for it, or whether they're looking for API calls, a loadable library, a utility program, or a VFS. Just what they want done.

The page I'm thinking ofl would be "Here are links to 40 supported features you wouldn't stumble on when figuring out basic write/read/find.". And it would links to all of these

  • what unicode support is there ?
  • fast text search
  • System.Data and Android bindings
  • the backup APi
  • the checksum shim
  • comparing two databases
  • carray
  • SQLite Database Analyzer
  • json support

even though they are different kinds of things. Because the person looking for features doesn't know enough to guess whether something is a VFS or a utility program. Someone who has just got into SQLite runs their eye down that page of links once. They might seize on something immediately, or a year later come up with a new requirement and remember that SQLite does it because they read it somewhere.

2021-08-11
17:10 Post: Documentation: list of addons and extras artifact: 30bd112cfd user: slavin

Twice in the last month I've looked for something in SQLite which is not mentioned in the API documentation, because it is an extra program, or a rarely used addon. Having read this forum (or the email list) for years, I know roughly what the feature is called, or a word that is probably mentioned in the documentation for it. I can use the website's search features to find that word.

But many newer users of SQLite may be looking for useful or interesting extensions/addons and not have that advantage. So I went looking for a web page which mentions all the extras. And I couldn't find one. A good source is to use

https://sqlite.org/docs.html

and reveal both 'extensions' and 'tools'. This gets me json1, the archiver, Android bindings, and FTS5. But not the Checksum shim. And the System.Data.SQLite bindings are listed in Programming Interfaces. It also doesn't mention the backup API, because that's part of the C API, yet people can miss it if they don't happen to be looking through the complete list of API function calls. And why would anyone do that when looking for what feels like an addon, not a basic SQLite feature ?

I'm thinking there could be useful things developed and supported by the SQLite team, which users would find if there was a list all on one page. Am I missing that such a list exists ? Or just being silly ?

2021-08-10
15:15 Reply: SQLite Binary Log support! artifact: a0079eb66b user: slavin

That is far easier for me to understand. Thank you. I hope other readers will now understand your questions and be able to answer them. I also understand why you cannot use the session extension.

I do have one caution for you. You can test several configurations by making a lot of changes as fast as possible. And you will get a different speed from each one and find out which is fastest. Good. However, this reflects only the situation where you are making a lot of changes as fast as possible. If you test a situation where the changes are happening less often, a different configuration can be faster. So your result may be correct, but not useful for any real situation.

Some reasons are that caching happens at several levels in your computer and storage subsystem, and some systems wait for inactivity, or for the cache to be full, before writing their cache. And your computer tries to write the cache at the same time as running operations in other processes. Things happen with different interlacing, in a different order.

So if your real application is not going to make a lot of changes as fast as possible, this may not be such a useful test.

00:32 Reply: SQLite Binary Log support! artifact: e7e18e601d user: slavin

Your post refers to a ton of things that SQLite doesn't need or use: Raft, Kafka, clusters, binary logs. They are tools which allow you to do something. But we don't know what that something is, which means that we don't know how to help you.

Can I ask what you're trying to do that SQLite doesn't do ? And can you explain it without referring to a tool or technique that works with another DBMS ? You've found the session extension. You know what it does. What doesn't it do that you want ?

2021-08-09
14:35 Reply: Why WAL file (-wal) may disappear? artifact: eafdda128a user: slavin

One test I can think of is to have your App close the SQLite database at some point in the procedure and reopen it again. Does this make the WAL file magically appear ? This is just for diagnostic purposes, to investigate the problem. You should not need to do this in production code.

Another test would be to run your test code on a desktop computer, rather than the Android hardware. Do you get the same behaviour ?

A recent question to this forum about the Android platform leads me to believe that Android does some sort of internal caching, and doesn't show files on disk until they get big (and the cache busts) or they are closed.

The person who started that thread was unable to reproduce their problem on their computer: on computer the WAL and shm files showed up when expected. On Android they were sometimes visible and sometimes not visible.

The App running SQLite ran correctly until the App was killed, accessing the data correctly. It's just that a directory listing didn't show any actual file. It caused a problem only when the SQLite App was terminated by lost power, or by being killed by the operating system. If the Android App was allowed to quit normally, the WAL file appeared at that time.

Since you are using a smart terminal and not a mobile phone, perhaps you won't have a problem with this. Your users are less likely to run out of power.

2021-08-08
12:41 Reply: Old Nabble SQLite mailing list site down? artifact: 748e339cdb user: slavin

There is a good argument for preserving all messages from the email forum intact, as text files (or possibly MBOX format), probably ZIPped. Those interested could download the whole thing, expand it, and search it on their equipment.

My problem was making the messages searchable via standard websearch toolls, for people who don't check the dates on the results shown, and don't know that SQLite gets substantial changes very frequently. Though I like the suggestion upthread of including a highly visible messages like "WARNING: This is an old answer and applies to an old version of SQLite. It may not apply to the version you're using.".

12:33 Reply: Not understanding a subquery - Some guidance? artifact: 13b06bae21 user: slavin

The LIMIT clause goes at the end. After DESC.

The way around you put it, SQLite would do the limit first, making it choose one random row of the results first, and only then apply DESC to the one row it has, which would do nothing.

2021-08-07
23:26 Reply: Not understanding a subquery - Some guidance? artifact: 6dcc9cd5fc user: slavin

Did you test the sub-select ? Does that give you the result you expected/wanted ?

13:30 Edit reply: Questions about data types and overflow bounds artifact: 91d6da55c1 user: slavin

If so, did SQLite forget to apply the strategy to PRIMARY KEY?

Not 'forget' as much as 'not care'. Values for INTEGER PRIMARY KEY fields are processed to make them useful for primary keys without the programmer having to deal with special cases. SQLite can do anything up to and including picking a random integer, as long as it keeps the column suitable for use as a primary key.

https://sqlite.org/faq.html#q1

If you want fine-detail control over the values in an INTEGER key column, don't define it as a INTEGER PRIMARY KEY, define it as INTEGER UNIQUE and set the values you want.

2021-08-06
15:32 Reply: Questions about data types and overflow bounds artifact: b980380c87 user: slavin

If so, did SQLite forget to apply the strategy to PRIMARY KEY?

Not 'forget' as much as 'not care'. Values for INTEGER PRIMARY KEY fields are processed to make them useful for primary keys without the programmer having to deal with special cases. SQLite can do anything up to and including picking a random integer, as long as it keeps the column suitable for use as a primary key.

https://sqlite.org/faq.html#q1

If you want fine-detail control over the values in an INTEGER column, don't define it as a primary key, define it as INTEGER UNIQUE and set the values you want.

15:30 Reply: Fail to calculate long expression artifact: eec4141a1b user: slavin

I haven't seen any such evidence. Different languages have differences in parsing numbers (how to handle '11' '11.' and '11.0' when looking at text) and differences in rules for division and modulus arithmetic. Every language is free to implement its own rules.

The original question includes at least one operation of division or modulus by 0. Division by zero is undefined in SQLite. SQLite returns NUL. Not a bug. Nothing to see here.

More ↓