SQLite Forum


50 most recent forum posts by user RichardDamon

19:09 Reply: Sqlite high IO load makes usage impossable (artifact: 66c86d9fb6 user: RichardDamon)

You can group them into the same EXPLICIT transaction to avoid the repeated setting of the checkpoint limit, and in fact, if you are building queries based on the results of queries, you WANT them all in a single transaction so the data can't change on you while you are building the selects.

This can help things some.

19:47 Reply: Sir Richard (artifact: 4b019c2f4d user: RichardDamon)

'expr IS NULL' is just a special case of 'expr IS expr', since 'expr' includes as an option a 'literal-value', and one of the options for 'literal-value' is 'NULL'

The fact that there is also a different option of ISNULL is immaterial. That couldn't be decomposed into a more general case.

01:41 Reply: Proposed slightly-incompatible change to date-time functions (artifact: 30bae14a48 user: RichardDamon)

That shouldn't break any reasonable existing code, it may want a note to explain the 'unusual' conditions.

As Kieth mentioned, negative values would likely be safe, as the Epoch base was intentionally chosen so negative Julian Day values are very unlikely as it goes into 'pre-history' so any shuch date would be very artificial.

Thinking a bit, it might be useful if there was some way to force a database that is using this new behavior to force older version that don't understand this to reject the database as unfit for that version.

13:22 Reply: Proposed slightly-incompatible change to date-time functions (artifact: 7b7010e25a user: RichardDamon)

Unix time stamps are basically worthless for real HISTORICAL data (more than about a century old).

It really depends on what sort of data you are processing.

12:40 Reply: Proposed slightly-incompatible change to date-time functions (artifact: e6c46742b4 user: RichardDamon)

I know I am currently working on one that does, and I find it hard to imagine that others would not.

The key is that Julian date (or minor modifications on it) are a very good compact method to store historical dates.

If you are only concerned about DATE, and not the time, then the Julian Day Number as an integer is an ideal storage format, no need to waste 8 bytes when less will do.

And if dealing with historical times Julian Dates as floats have problems that there represent the time in GMT, not local, and it is unlikely that you have the time specified as GMT, and if you want to actually DO anything with those values you need to know your local time offset (not just 'time zone, as those didn't exist yet).

13:49 Reply: Proposed slightly-incompatible change to date-time functions (artifact: b5b3fb365a user: RichardDamon)

If the affinity is INTEGER, then you can insert 22 or 22.0 and you will always get back 22. If the affinity is REAL, you can insert 22 or 22.0 and you will always get back 22.0. But if the affinity is NUMERIC or BLOB then you always get back what you inserted. If you insert 22 you get back 22, and if you insert 22.0 you get back 22.0.

And the issue is that when looked in the lens of backward compatibility, a column declared 'DATE' will be NUMERIC, and if the application ONLY dealt with DATE (and not DATE-TIME) values it would have been very reasonable to work with integer Julian Day values, as they are much smaller in the database (and in the program) than REALS.

So, changing the default to make this data be seen as Unix Timestamps would be a major breaking change to that class of applications.

SQLite has a history of refusing to make some more obvious fixes of removing clearly incorrect behavior just because some existing applications depended on that incorrect behavior, and it seems out of place to break existing applications that actually have fully intended behavior.

Adding a modifier to make it do that sort of behavior makes sense. Even adding a pragme to change the behavior so that modifier is the default (which might imply the need for a 'jday' or 'julian' modifier to get back to the current default) makes sense, as this doesn't break existing programs

Maybe even adding a way to make a persistent change in the default for a given database (which if used might make the database unusable by earlier versions that don't understand the persistent default change) could make sense.

02:49 Reply: Proposed slightly-incompatible change to date-time functions (artifact: 975405dad8 user: RichardDamon)

Indeed, columns that are intended to store date/time values could hold a mixture of TEXT, REAL, and INT values, and queries using the date/time functions would always be able to interpret the values correctly.

SQLite could take times that just happen to be exactly at noon, and thus equal to an integer, and store it in the database as an integer as a form of compression

SQLite does indeed do this. But it also converts the integer back into a floating point number as it reads it back off the disk, so by the time it gets to the date/time SQL functions, it is a REAL again.

The point I am making is that because it does this, you can't tell if the original was inserted as an integer or as a real if the affinity of the column allows this transform. Thus what seems like a small change in the schema for a table could drastically change the meaning of data that is sent to it.

This is not saying that the idea is inherently bad, but it is worth some though, and careful documentation that includes comments about how the affinity of a column used with these functions might affect the interpretation of that data.(some affinities will NEVER have their values be interpreted as Unix Timestamps because they will be converted to REAL when read.

13:31 Reply: Proposed slightly-incompatible change to date-time functions (artifact: 8f60de0027 user: RichardDamon)

One comment is that having Julian Day numbers as just an integer might not be as rare as you think, as if something is only storing the DATE something happened, and not a full Date/Time then that makes a lot of sense, and for historical data, that often makes sense as you may not really have information of the precision to add a time to the event.

Also, I think SQLite could take times that just happen to be exactly at noon, and thus equal to an integer (and of reasonable value) and store it in the database as an integer as a form of compression, so just because the column value happens to be stored as an integer, doesn't mean that an integer value was sent to the database, it could have been a real value that just happened to have no fractional bits present. I think this may be a function of the affinity of the column, but we don't know the affinity of existing legacy date columns

Maybe adding an 'Auto' modifier that makes the function decide format based on the type of the input would avoid the backwards compatibility break.

14:46 Reply: binary .dump format (artifact: b6eae8e5c9 user: RichardDamon)

The full answer might be to pull a copy like they are currently seeming to do, and the rather than backup that copy, do the diff and back that up. The other side of the backup then might need to apply the diff, to its copy.

12:07 Reply: binary .dump format (artifact: 46fee948f9 user: RichardDamon)

One thought, the original request seemed to be based on wanting minimal differences in file contents for 'small' changes in the database, so an incremental backup could see just small parts of the file changed.

Any conversion of the database into 'dump' records would cause ALL of the file after any record that changes length or is added/removed to move and thus be a new difference.

My guess is that unless you spend a LOT of effort to make a dump that works to minimize this sort of difference, the raw database may well be a near minimal difference set, as it naturally wants to minimize how much of the file it changes to record differences as disk I/O is the most expensive part of the database access.

16:53 Reply: create taable with syntax error works ? (artifact: 009de11f14 user: RichardDamon)

But the syntax IS valid, it just has a very funny, but legal for SQLite, type for the second (and last) column.

00:47 Reply: about "strict" mode (artifact: dffb7d6d20 user: RichardDamon)

IF you declare the column, then the column is explicit and stable, yes, even if you don't provide a value in the insert, there are explicit rules to auto-generate the value.

The implicit rowid that people are talking about is the rowid column that ISN'T defined in the schema, but are able to be read back using several implicitly defined names.

something like

create table x ( name text primary key; );

insert into x ('Foo');

select rowid, name from x;

THAT rowid which was never defined in the schema is not stable and can change if you vacuum the table.

Note your case was a given value was 'implicitly generated', and that is stable (the database doesn't kee track of which values were auto-generated, and which were explicitly provided..

In this case, the whole COLUMN was 'implicitly generated', and is not stable.

19:49 Reply: about "strict" mode (artifact: 43ac141b1e user: RichardDamon)

No, implicitly generated rowids are NOT stable. In particular, it has been mentioned that when you vacuum a table, IMPLICIT rowids might change, while explicitly generated columns of type PRIMARY INTEGER will be stable.

Not sure if there is an explicit promise of exactly how long an implicit key is promised to remain, and changing its value does require updating all the indexes for the table, so won't be don't lightly (so vacuuming is a good time to reset them to squeeze out spaces in the numbers since indexes are being regenerated anyway).

14:48 Reply: Automatic indexing (idle question) (artifact: 2aa071ead8 user: RichardDamon)

The problem with indexing every column is that this is actually an anti-pattern in most cases.

Indexes are an optimization tool, and almost always have a trade-off. They make some operation, a lookup, faster at the cost of space (which itself can slow some things down), and the need to update the index for every database update.

Update frequency is a very important factor for deciding on indexes. If you rarely lookup by the value of a column, but the table is updated a lot, it may be faster to just do the table scan to do that lookup, especially if the table size is somewhat limited because most of the updates are changing values rather than adding new values (or we are doing a lot of deletes also).

On the other hand, a very static table that is updated rarely, but looked up a lot can benefit a lot from a good set of indexes, and good table statistics for the planner.

So, not only do you need to run all the select queries that you will use, but you also need a good representation of the updates you will be doing so you can get an estimate of the full cost for each index.

15:02 Reply: about "strict" mode (artifact: c086ba326d user: RichardDamon)

The answer for older code opening the database is to have it make a change that prevents older versions that don't understand the option from opening the database.

This has happened with some other options.

It does say that for a while, until the newer versions are commonly deployed, using the option will be a significant decision about data portability.

04:33 Reply: sqlite3_carray_bind and constness (artifact: c0fc170bdd user: RichardDamon)

For 'Desktop' like systems, I don't think so, the last was probably 16 bit x86 (which some memory models had this characteristic), but there are embedded processors with Harvard Architectures with different size Function and Object pointers. I don't know if SQLite supports any of them.

Note, there ARE still machines out there running 32-bit windows which will still run 16-bit programs, so it can be a bit of a question of how you define 'platform in common use.

23:12 Reply: sqlite3_carray_bind and constness (artifact: 28ef386120 user: RichardDamon)

Pointers to functions and pointers to objects can be different, as can pointers to different types of objects, but const/non-const pointers to the same type of object have to be the same.

21:55 Reply: sqlite3_carray_bind and constness (artifact: 51bc56bb9f user: RichardDamon)

It would be non-conforming for a const pointer to use a different representation than a non-const pointer.

6.2.5 p28 sentence 2 says "Similarly, pointers to qualified or unqualified versions of compatible types shall have the same representation and alignment requirements."

I could imagine a system that wants to do this, like a Harvard architecture to be able to specify code/data memories for const pointers, but the standard also allows ANY const pointer to be converted to a non-const pointer, and be fully usable as long as you don't actually write through it, so those platforms really need other solutions.

20:07 Reply: Changing from MySQL to SQlite (artifact: 66604ac366 user: RichardDamon)

I am not an expert on this, but the BIG question is which interface were you using to MySQL. If you used a MySQL-specific one, then you are going to need to change EVERY call to a different library that was designed to use SQLite (directly or generically).

If you used a generic library that supports both MySQL and SQLite, then you might just need to change your connection call. A bit more likely there will be some calls that behave enough different between the two back-ends that you need to make other changes, but this path would be much simpler.

As was else mentioned, this is really a topic for a PHP database group, but come here if specific operations in SQLite seem to be a problem.

13:42 Reply: How much would you trust a page-level checksum ? (artifact: c5aaab86fa user: RichardDamon)

I am fairly sure that it is quicker to compare the two blocks rather than computing the checksum, at least if the checksum is stored as a piece of the block on disk, so the old data needed to be read in.

18:06 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: f75f984325 user: RichardDamon)

Some folder are just protected because of where they are. What is the path that you are trying to access.

20:03 Reply: New version of app, more columns for same table (artifact: b1b6f06e58 user: RichardDamon)

What you seem to be missing is that when you read the row that physically has fewer columns written in it than the current table schema defines, that SQLite will add in those columns with the default values. In fact, my understanding is that for most purposes, unless you low-level read the raw database and go around SQLite itself, you can't tell by reading it that the values aren't there.

I think the one exception is that you can detect that the data isn't there by reading the record, changing the default with an alter table, and read the record again. Columns with no value and just getting the default will change here, while once the record actually get re-written, the value will stick to the default value written when the record was written.

18:47 Reply: Feature request, PRAGMA for setting default transaction mode (artifact: 856f63cca7 user: RichardDamon)

Unless you really do have an unusual application that almost all transactions will promote themselves to a write transaction, I know of few better ways to kill your throughput then to make all transactions BEGIN IMMEDIATE;

This basically says that no transactions will be able to overlap as the BEGIN IMMEDIATE; will wait for any previous BEGIN IMMEDIATE; transaction to finish and will prevent the next one from starting until it is done.

You will basically get ZERO concurrencies.

01:16 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: c815d2658e user: RichardDamon)

The key answer to how to handle races is how databases normally handle races, using a Transaction.

If the program places the check and the creation of the tables and such in a transaction, you won't have the race problem.

First, you don't first check if the DB exists, you just open it and see if the needed tables/data does (inside a transaction).

If you use a simple BEGIN, then the second one might see the tables not there when it checks but will get a BUSY return when it goes to create the tables, and that tells it it needs to back off and start over.

If you use a BEGIN IMMEDIATE, then the second one will delay doing its test due to the busy wait until the first finishes. This makes the logic simpler, but if the database in use might have longish write transactions might get some delays on start that wouldn't be otherwise needed.

00:46 Reply: Is there a way to optimize this UPDATE command to run faster? (artifact: 135edad33d user: RichardDamon)

I don't think you need the second two indexes, as the first index can do everything that the others can. Maybe they make lookups a bit faster as they are smaller, but need to be updated for every new insertion slowing that down.

12:25 Reply: long runtime for sqlite3_prepare_v2 (artifact: 0a19840561 user: RichardDamon)

One thing to remember when your SQLite, is that a lot of the 'conventional wisdom' for using a database is just wrong. With standard client/server databases, it is important to minimize the number of statements, as each statement might involve a network transaction, so is slow. For SQLite, this doesn't hold, SQLite is just part of your program. Instead, it is better to make the statements simpler so it can parse them faster, and reuse the statement.

Maximizing the number in a TRANSACTION might make sense, but rather than make the transaction a single statement, make it an explicit transaction with a lot of statements. That way SQLite works with simpler smaller chunks and all goes well.

13:18 Reply: Statement that fires trigger is not executed when the trigger causes an error (artifact: c3b325a61d user: RichardDamon)

I think the issue you are missing is that the statement is executed as part of a session (implied if you don't create one), and a result of the error is to roll back the session so it is as if it never happened.

20:32 Reply: Is CSV parsing too liberal? (artifact: 7704b12758 user: RichardDamon)

As you say, it is sort of a system convention, but not universal. In one sense, stripping out the BOM when reading a known to be text file is a fairly safe operation, as there really is no reason for one to be at the beginning of a file except to indicate it is UTF-8 encoded, even if by the Unicode Standard it isn't supposed to be used that way.

The one big problem with that convention is that it says that utilities that were designed to work with plain ASCII files, and didn't need to care about encodings (like tail) suddenly are now broken as they don't know to do this.

The advantage of this convention (and somewhat why it happens a lot in Windows) is that for programs that DO need to worry about encoding, it provides a big clue of UTF-8 vs local default 8-bit code page.

Windows has a bigger problem with this as it is older, especially in the 'business world where this was more of an issue.

Linux was late enough to be able to just assume UTF-8 unless it was told otherwise, and could live with those issues.

16:19 Reply: Is CSV parsing too liberal? (artifact: a1cc30167d user: RichardDamon)

Arguably, the error is either in tail, if BOMs are supposed to be ignored at the beginning of files, since it move it, or in the program that added them if they aren't supposed to be ignored at the beginning of files.

Maybe you just need a BOM stripper that you add to the pipe that just removes a leading (or maybe even embedded) BOM mark if programs disagree on the rules for BOMs.

15:10 Reply: classic save possibility (artifact: 7203fa7f23 user: RichardDamon)

Be sure to think through the ramifications of what you are trying to do, and if there is any possibility of multiple actors working on the same database.

Depending on the requirements, saving a. mirror of the old as a 'backup' that you can roll back to, or working with the mirror might be better. It somewhat depends on how often/likely you are to want to do that rollback.

Saving the backup and working with the original will avoid needing to worry about merging conflicts from other accesses to the file unless you actually want to do a rollback, but this won't seem like the Document case, but in the document case, normally no one else is allowed to access the document while you are editing, or maybe only in a read-only mode.

If you want to recreate that, make the copy and use it, but start a BEGIN IMMEDIATE transaction on the original so no one else can modify it. Then rolling in the new database won't need the check for conflicts.

The fundamental issue is a database really isn't just like a simple document, so a flow that treats it as one will have problems.

13:28 Reply: classic save possibility (artifact: 2c6825b418 user: RichardDamon)

The issue is that once you commit the transaction and it gets written to the WAL file, the ONLY way that I know of to 'undo' that transaction is to delete the WAL file, and the behavior of that is technically undefined.

So yes, you can keep your main database from being updated, but every usage to read the data from it will see the data in the WAL file, so you haven't established a way to roll back to that last 'save' command. That would require doing something undocumented.

13:00 Reply: Is CSV parsing too liberal? (artifact: fc22fba706 user: RichardDamon)

My guess this behavior is based on the principle of being liberal as to what you accept, but be strict as to what you generate. There are enough 'broken' CSV generators, that being too strict as to what you accept will cause issues with not being able to import from some sources.

If you really want to be strict, write your own program to do the import, then you can check for all the errors you want, including things that might be valid per the RFC, but are inconsistent data for your application.

The SQLite Shell is really just a convenience tool and isn't supposed to be all things for all users.

11:58 Reply: classic save possibility (artifact: cb8048210f user: RichardDamon)

A basic comment, if by "Save", you mean functionality like with a normal word processing document or a spread-sheet where you can save the document, then edit the document for a while, and have the option to revert back to that previous save point if you want, then you need to understand that databases just don't work that way.

Every Transaction basically is that 'SAVE' command.

Either your application needs to keep a change history to allow it to perform an 'undo' function, or you need to 'backup' your database to give you the save file you could roll back to.

-WAL mode with manual checkpoints could work sort of like that, where the -WAL file becomes a record of the changes that have been done, but you are working outside the documentation (I think) if you do the roll-back by just deleting the WAL file. That becomes your risk, you would be depending on an operation that is not documented to work.

15:51 Reply: New type of transaction (or a new behavior for DEFERRED transactions) (artifact: bbd5f225e8 user: RichardDamon)

The problem with not giving an Error on the detected deadlock is that inconsistent results could happen. If transaction A reads some values, computes some changes based on those values and then goes to write them, but finds that someone else is doing a write, then because that write might change the values that A has read, the values A computed might not be right after B finishes its write, thus A does need to redo its work.

In a more complicated world, SQLite could keep track of every record that a read transaction has read, and only give the busy if a write transaction changes some of that data, but that goes away from 'lite', but is what some Database systems will do.

Note, with the rules SQLite uses, it is impossible for an application to read 'stale' data and write back results based on it as long as the full operation is part of a single Transaction, as any write operation that is in progress or finishes after the read transaction started (and thus might have changed values) makes SQLite return the error for trying to update the read to a write transaction. And not making it a single transaction is basically saying you don't care about the stale data problem.

Making IMMEDIATE transactions the default would reduce the ability for a read-only transaction to be able to start if there is a write transaction in progress. You of course always have the ability to just make your own application just issue BEGIN IMMEDIATE for every transaction, or every transaction that just might want to write so you don't need to worry about getting the deadlock BUSY answer.

02:00 Reply: "Office Space"... I'm losing all those pennies (artifact: 1bd298c4b5 user: RichardDamon)

Why would you expect differently from a Database being told to use floating-point numbers to be different than a C program using floating-point numbers.

SQLite numeric is still floating-point, not some fixed point decimal notation.

23:28 Reply: zero width space (artifact: ae4ddfaf5d user: RichardDamon)

One other thing to watch out for if you are comparing strings is you may want to convert them to a 'canonical form, as some letters (with 'accent' marks) can be represented with different sequences of code points, either composed as a single code point or separate code points for the base character and the accent.

12:58 Reply: zero width space (artifact: c54625465f user: RichardDamon)

Uincode Codepoint U+200D is defined as a Zero-Width Joiner. It should take no space on the screen and tells the glyph system that the characters on either side of it rather than being 'independent' as they normally would be, are really together. (This seems mostly used with Emoji).

I don't know Indian coding to know what it is supposed to mean in that context, and maybe the issue is that your data source is doing something wrong and there shouldn't be a joiner there.

The question becomes, why do you think you need to change it?

If it is an encoding error from your source, then you just need to either keep the error everywhere or remove it everywhere,

SQLite itself won't care about it.

23:22 Reply: The NUMERIC data type? (artifact: 758fca79f4 user: RichardDamon)

I think that some SQL systems might make DECIMAL work with decimal arithmetic rather than binary floating-point. SQLite isn't one of them.

22:52 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: c97b1d3336 user: RichardDamon)

It night use the index to find name, but the only way to find values in bitfield that match, with just an index on the value, is to scan through all the values. It can't assume that it only wants to find the value 0x0040, as that isn't the condition. Yes, if the cycles were added to ALL index searches, it could possibly do some proofs and determine the lowest value it could be would be 0x0040 and skip over to find that. and when it gets to 0x007F skip to 0x00C0, but that is a lot of logic for a very special case.

Add a partial index on "B(name) where bitfield & 0x0040 != 0" might be good enough to get it to use the index. Of course, you will need to make a partial index for every bit combination you want to test.

Making it a partial index cuts down the work to keep them, but does say you can't look for the converse condition of WHERE B.name=? AND B.bitfield & 0x0040 == 0

For that, you would need to make an index on B(name, bitfield&0x0040), and for all other bits you need, which gets to be a lot of index data.

20:22 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: 70a135b29f user: RichardDamon)

As I said, I wouldn't expect indexes to help if the mask is done with a bound parameter, as the planner doesn't know which index (if any) would be of help. In effect, you have created a run time 'column select' which if it were legal to do directly would give the planner fits too.

That does become one question, if you can make the bit-mask a fixed value in the SQL Statement, the planner could choose a partial index to use (if one exists) and you could get a speedup.

19:20 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: 8dc774f745 user: RichardDamon)

Yes, there are reasons to use less efficient methods to get results. You just have to realize that doing so may cause performance issues.

Making the query have an ORDER BY clause might get the planner to use the index, and maybe at some point the scanner will be smart enough to think of skipping.

Adding functional indexes to the table (for the values of x & n that you use a lot) but this might not help if the n is provided as a binding to ? as that is too later for the planner to act on it.

(This assumes that adding indexes doesn't trigger as much of an issue as other Schema changes.) Adding indexes also will add overhead to every change to that table.

If efficiency isn't significant, don't worry about it.

15:11 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: 3ad68c3b96 user: RichardDamon)

I don't know of any great algorithm to scan an index and get to the next unique value that is that much faster than a scan. Maybe if you could assume that the values have somewhat low cardinality and a very small percentage match the pattern (most patterns having low bit count). You only get real savings if you can skip reading a number of pages because you read pages before and after it that have the same value of x, and x doesn't match the pattern.

Then, for all the matches, you need to still go to the main table and find that entry unless the index is a covering index supplying all the data needed for the query. This need may easily make the table scan faster. Maybe adding an ORDER BY x clause would increase the chance of it using the index.

13:48 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: 80eacfe984 user: RichardDamon)

The big issue is that since you used a ?, the processing can't assume it is something big, so can't search part way down. If the ? was replaced with a 0 or a 1, then the full scan would definitely be the fastest. Remember, if you use an index, then you need to do a second lookup for every hit you find, unless the index itself has all the information you need, so if you are going to need to actually scan the index, you might as well scan the original table.

Yes, perhaps you, knowing a lot about the structure of the table, can decide that some other method would be faster in this case, the planner has to work with what it knows, and wants to make the decision with somewhat simple logic to avoid slowing down the planning of simpler cases. Slightly sub-optimal decisions are not really bugs, particularly if it is a corner case.

In many cases, using bit encoding to try and squeeze a bit of efficiency is the wrong tactic, better to have a set of binary fields that you can build indexes on if you really want to be able to select on things like this efficiently. If you don't need efficiency, you shouldn't worry about it.

23:23 Reply: SQL query help for between times and grouping times (artifact: 8a3fb5cb1e user: RichardDamon)

My guess is that the code isn't handling that 12 comes before 1, so should really be changed to 0.

21:48 Reply: What does, if many threads and/or processes need to write the database at the same instant, mean? (artifact: 1bada19fe8 user: RichardDamon)

But I think this pragma is still just for the current connection. The question is how to set it so that connections default to a longer timeout than 0 by default without each connection needing to set its own timeout.

15:49 Reply: Atomically initialize database (artifact: 73791d194d user: RichardDamon)

You need to test for 1 before even trying to open the file, as SQLite will create it if it doesn't exist as soon as you do anything with the file.

Assuming you don't have two versions that need different initializations, I would just do each transaction worth as a chunk, start the transaction with a BEGIN IMMEDIATE so you know that no other version can be competing with you for initializing, and test if you need to do that phase. For the 'CREATE TABLE' phase, either just use CREATE TABLE IF NOT EXISTS or check for the tables you need if that might take too long (unlikely). If you need to populate the tables with data, again BEGIN IMMEDIATE, check if the data is there, and if not insert.

Note, setting JPURNAL_MODE to WAL doesn't really need isolation unless you are flipping modes during initialization

00:52 Reply: null character sorts greater than 0x7f and less than 0x80 (artifact: 8a72053f51 user: RichardDamon)

In my case I discovered it was converting "mathematical fraktur capital a" into "ed a0 b5 ed b4 84" instead of "F0 9D 94 84" and PostgreSQL said "ERROR: invalid byte sequence for encoding "UTF8": 0xed 0xa0 0xb5". Passing through "Tcl_UtfToExternalDString(utf8encoding..." fixed it right up.

That looks like someone naively converted UTF-16 to UTF-8 and didn't handle the Surrogate pairs properly. The Unicode Standard requires that Surrogate pairs be processed by building the actual character and encoding that, not just the characters of the pairs individually. Many programs don't do the test right, and the need to test is somewhat what sort of security model you are trying to enforce, but PostgreSQL is right in rejecting it.

11:06 Reply: Serious problems with STORED columns (artifact: 52675c5fa8 user: RichardDamon)

You specify what value they are to have by the expression that generates them. The intent is that it will ALWAYS have that value. STORED means err on the side of saving the computed value, and use that saved value if you can. Omitting the STORED says always recompute.

The error was that you used a function for the value that keeps changing, but SQLite didn't realize that. Thus it would give you back wrong values because it didn't realize it needed to recompute the value.

Storing a cached value so that you don't need to recompute every time is different than a 'normal' persistent value which absolutely won't change until you explicitly write a new value to it.

02:02 Reply: Math functions for arguments outside the domain should raise exception according to SQL Standard (artifact: 3ae169eeb3 user: RichardDamon)

Actually, the question is which definition of 'the reals' is it using. For the true mathematical reals, sqrt(-1) is a domain error, so an error message would be appropriate.

Only when you extend the reals to add the Not-a-Number value, can you include that in the possible results. Since SQL predates the IEEE-754 definition for floating-point representations, which is as far as I know the origin of the NAN value, at least as far as a general definition. it isn't surprising that it adopted some conventions that disagree with the later standard.

00:53 Reply: Serious problems with STORED columns (artifact: 7c47c8fd97 user: RichardDamon)

Is there a requirement that the default be a deterministic value?

Yes, where it was used in a computed column, it seems that it would be wrong by the rules, (and this case sort of shows why), but it should be allowed in the default clause.

Backward compatibility is always an issue when thinking of fixing this sort of bug.

More ↓