SQLite Forum

The insert should be done in-order for maximum efficiency. That means you should execute that as:

CREATE TABLE new_test(
    name text PRIMARY KEY, 
    value blob
) WITHOUT ROWID;
INSERT INTO new_test SELECT * from test1 ORDER BY name;

select * from test1 is free to return results in any order unless you specify a specific order and there is no guarantee that unless the order has been specified that the select will not produce rows in the most pathologically inefficient order.

Note that even if you are inserting into a rowid table, you should also make sure that the insert order is optimized since that will be used to build the index as rows are inserted.

Theoretically (assuming sufficient page cache) the two following sets of statements will take more or less the same overall elapsed wall time to execute:

create table new_test
(
  name text primary key,
  value blob
);
insert into new_test select * from test1 order by name;

and

create table new_test
(
  name text,
  value blob
);
insert into test select * from test1;
create index new_test_name on new_test1 (name);

The collation attribute (and affinity) travel (are attributes of) the column. Expression results do not have any affinity nor do they have a collation.

Use the following:

create view b as select substr(s, 1, 3) collate nocase s from t;

which will "attach" the collation attribute NOCASE to the result of the expression substr(s, 1, 3).

You need to do similarly as this:

SELECT s FROM t WHERE substr(s,1,3) = 'aaa' collate nocase;

or

SELECT s FROM t WHERE substr(s,1,3) collate nocase = 'aaa';

The former attaches the collate nocase to the equality operator (that is, the standard manner). The latter attaches the collate nocase attribute to the result of the expression substr(s, 1, 3) which is then used by the equality operator to perform a comparison using the NOCASE collation.

That error message would indicate that the version of SQLite3 library being used is prior to the version at which the UPDATE ... FROM ... syntax was recognized.

What exactly are you trying to accomplish and why?

RAM that has been purchased but is unused was a waste of money.

Another difference is that when a child table references a parent-table, but that reference does not contain column name data, then the reference is assumed to be to the PRIMARY KEY of the parent. This saves having to type the column names in the parent key over again. This only works for columns in the parent declared as PRIMARY KEY, not for those declared UNIQUE; although, a UNIQUE index on the parent column is all that is required (though then you have to specify the columns) in the references clause.

There is a draft version of the website that contains all the proposed updates in progress (I do not know what the regular update schedule is, but it is re-built from source periodically).

You can find the head page here: https://sqlite.org/draft/ so the page which corresponds to the datetime funcions documentation page is: https://sqlite.org/draft/lang_datefunc.html

The strftime(...) also accepts date-time strings with submilliseconds and returns them faithfully (it just needs to repeat the input):

sqlite> SELECT strftime('2022-01-27 07:34:42.1239');
2022-01-27 07:34:42.1239

In the same vein, strftime accepts are returns as a result what was given as the format string, if it contains no valid "formatting specifiers" -- therefore it would appear that it also accepts dates in "descriptive format" and returns them unmolested ...

select strftime('yapapalooza sex party');

All builtin date/time handling in SQLite3 is limited to millisecond precision (though the accuracy may be considerably more or considerably less -- that is an Operating System restriction and not due to SQLite3) because datetime values are stored and computed internally using an internally maintained "Offset from the Epoch" which is stored as an integer number of milliseconds since the Julian Epoch. It can be manipulated and presented in a multiplicity of formats however it can never express more accuracy than one millisecond in whatever format it is presented.

What Operating System and drive settings?

I can see your point. While it is indeed true that CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, date(...), time(...) and datetime(...) are merely aliases for the strftime function specifying a pre-bound "format string" and returning text, the julianday(...) function is not an overload of strftime(...) -- although there is a format code that can be used in a strftime format string to return a textual representation of the julian epoch day offset.

The julianday(...) function takes the same parameters (without a format string) as the strftime function -- however it returns a dirrectly calculated floating point value.

Conceptually, the '%J' format string to strftime returns the same number, just in ASCII TEXT (since that is what strftime does). The difference is, of course, that conversion between base-10 ASCII representation and base-2 binary floating point representation introduces "conversion error" and should be expected.

In other words, the Julian Epoch Offset is retrieved by the julianday(...) function. This same value can be "converted to text" by formatting the floating point result using whatever method the heart desires, including providing the appropriate formating codes to the strftime function, however, as everyone should be aware, conversion to and from ASCII base-10 print format and double precision base-2 floating point are not exact.

It would be a rational expectation that each conversion would introduce additional non-stochastic error.

It other words, I agree that the first paragraph in the documentation is misstated.

julianday(...) does not return the same value as strftime('%J', ...); nor is juliandate an overload of strftime (as are the other functions). It instead returns the accurately computed (within the bounds of hardware capability) Julian Epoch offset.

The same caveats apply to the unixepoch(...) function, which also returns a directly computed integer value and is not an overload of strftime -- although there is a format code for strftime that will produce similar text output.

Those three results represent -1, 0 and +1 ULP of precision and represents the conversion discrepancy between the actual value (as returned by the julianday function as a double precision floating point) and the ASCII representation of the same value after conversion to and fro double precision floating point.

The current epsilon of juliandate('now') expressed as a double precision floating point number is 4.65661287307739e-10 days (0.0402331352233887 milliseconds).

The maximum difference is therefore about 40 microseconds and is really due to the conversion to and from ASCII TEXT base 10 representation.

It should be pointed out that iJD (the internal timestamp used by SQLite3) is only precise to the millisecond, the difference in value is entirely an aftifact of conversion and does not represent any actual difference in the source value.

Yes.

  select location,
         min(time) as mintime,
         report
    from weather_reports
group by location
;

Would be the "standard" way of phrasing the query. Note that many other RDBMS have removed the capability to retrieve bare columns as part of a group-by query, though SQLite3 has not -- it is this removal of a "standard feature" from the 1960's that has resulted in the addition of other vendor-proprietary methods of achieving the same result.

See https://sqlite.org/lang_select.html and in particular section 2.5 thereof.

This would mean that there is no column accounting period in the table B.

You can try putting identifier quotes around it, rather than using quotation marks. Unless you have DQS=0 then handling of quotes is slippery.

Try running:

select `accounting period` from B;

and see what error message you receive.

Neither a SELECT nor a VIEW change the database.

As such, there is nothing to record in a changeset (that is, a changeset is a collection of changes, and neither a SELECT nor VIEW causes a change to the database, hence there are no changes to record).

You can also find the statement in the output of select * from sqlite_stmt which will return a bunch of data to you about each prepared statement on the connection including whether it is currently busy or not, and whether it has been run and how many times.

The result obtained depends on how the Operating System deals with a filename of "file:stk.db?mode=memory&cache=shared".

Microsoft Windows NTFS will treat that as a reference to a datastream called "stk.db?mode=memory&cache=shared" of the file "file" in the current directory.

Your mileage will vary for other filesystems and operating systems according to their documented file naming behaviour.

In order to have Sqlite3 subparse the filename field as a URI one must have enabled Sqlite3 to do so. SQLITE_USE_URI=1 is one way to do so.

This is true, because you are conflating the concept of doneness with the concept of being active. There is no API call to check that a statement has completed execution, only whether it is active or not.

How is it that you do not know if a statement has begun execution since it is only you that can make it so.

You could even have your trigger check that the date is valid:

create trigger balderdash before insert on calendar
begin
   select raise(ABORT, 'date format is invalid')
    where datetime(new.date) IS NOT new.date;
   select raise(ABORT, 'date out of sequence')
    where new.date <= (select max(date) from calendar)
       or new.date > datetime('now');
end;

So that not only can you not insert out of sequence but you cannot insert garbage either ...

You cannot do this with a check constraint. A check constraint can only see the current row and is intended to express record/row/tuple invariants.

How about:

sqlite> CREATE TABLE calendar(date TEXT PRIMARY KEY ASC NOT NULL);
sqlite> create trigger balderdash before insert on calendar
   ...> begin
   ...>   select raise(ABORT, 'date out of sequence')
   ...>     from calendar
   ...>    where new.date <= (select max(date) from calendar)
   ...>       or date > datetime('now');
   ...> end;
sqlite> insert into calendar values (datetime());
sqlite> insert into calendar values (datetime());
sqlite> insert into calendar values (datetime('now', '-1 day'));
Error: stepping, date out of sequence (19)
sqlite>

You could accomplish this as follows, but notice that it is more inefficient than simply expressing what you want directly.

sqlite> select item_sum from (select sum(item_count) as item_sum from itemlist group by user_id) where item_sum > 50;
QUERY PLAN
|--CO-ROUTINE SUBQUERY 1
|  `--SCAN itemlist USING COVERING INDEX idx (~1048576 rows)
`--SCAN SUBQUERY 1 (~88 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     53    0                    0   Start at 53
1     InitCoroutine  1     40    2                    0   SUBQUERY 1
2     Noop           2     2     0                    0
3     Integer        0     5     0                    0   r[5]=0; clear abort flag
4     Null           0     8     8                    0   r[8..8]=NULL
5     Gosub          7     36    0                    0
6     OpenRead       3     3     0     k(3,,,)        0   root=3 iDb=0; idx
7     ColumnsUsed    3     0     0     3              0
8     Explain        8     0     0     SCAN itemlist USING COVERING INDEX idx (~1048576 rows)  0
9     Noop           0     0     0                    0   Begin WHERE-loop0: itemlist
10    Rewind         3     24    10    0              0
11      Noop           0     0     0                    0   Begin WHERE-core
12      Column         3     0     9                    0   r[9]=itemlist.user_id
13      Compare        8     9     1     k(1,B)         0   r[8] <-> r[9]
14      Jump           15    19    15                   0
15      Move           9     8     1                    0   r[8]=r[9]
16      Gosub          6     29    0                    0   output one row
17      IfPos          5     39    0                    0   if r[5]>0 then r[5]-=0, goto 39; check abort flag
18      Gosub          7     36    0                    0   reset accumulator
19      Column         3     1     10                   0   r[10]=itemlist.item_count
20      AggStep        0     10    2     sum(1)         1   accum=r[2] step(r[10])
21      Integer        1     4     0                    0   r[4]=1; indicate data in accumulator
22      Noop           0     0     0                    0   End WHERE-core
23    Next           3     11    0                    1
24    Noop           0     0     0                    0   End WHERE-loop0: itemlist
25    Gosub          6     29    0                    0   output final row
26    Goto           0     39    0                    0
27    Integer        1     5     0                    0   r[5]=1; set abort flag
28    Return         6     0     0                    0
29    IfPos          4     31    0                    0   if r[4]>0 then r[4]-=0, goto 31; Groupby result generator entry point
30    Return         6     0     0                    0
31    AggFinal       2     1     0     sum(1)         0   accum=r[2] N=1
32    Le             11    30    2     BINARY-8       80  if r[2]<=r[11] goto 30
33    Copy           2     12    0                    0   r[12]=r[2]
34    Yield          1     0     0                    0
35    Return         6     0     0                    0   end groupby result generator
36    Null           0     2     3                    0   r[2..3]=NULL
37    Integer        0     4     0                    0   r[4]=0; indicate accumulator empty
38    Return         7     0     0                    0
39    EndCoroutine   1     0     0                    0
40    Explain        40    0     0     SCAN SUBQUERY 1 (~88 rows)  0
41    Noop           0     0     0                    0   Begin WHERE-loop0: subquery_1
42    InitCoroutine  1     0     2                    0
43      Yield          1     51    0                    0   next row of subquery_1
44      Copy           12    13    0                    0   r[13]=r[12]; subquery_1.item_sum
45      Le             11    50    13    BINARY-8       80  if r[13]<=r[11] goto 50
46      Noop           0     0     0                    0   Begin WHERE-core
47      Copy           12    14    0                    0   r[14]=r[12]; subquery_1.item_sum
48      ResultRow      14    1     0                    0   output=r[14]
49      Noop           0     0     0                    0   End WHERE-core
50    Goto           0     43    0                    0
51    Noop           0     0     0                    0   End WHERE-loop0: subquery_1
52    Halt           0     0     0                    0
53    Transaction    0     0     2     0              1   usesStmtJournal=0
54    Integer        50    11    0                    0   r[11]=50
55    Goto           0     1     0                    0
sqlite>

This is exactly the same except that it requires more typing ...

This is untrue. Yopu have to type the aggregate function twice, but it is only calculated once:

SQLite version 3.38.0 2022-01-22 22:55:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table itemlist (user_id, item_count);
sqlite> .eqp full
sqlite> SELECT SUM(item_count) AS totalitems FROM itemlist GROUP BY user_id HAVING SUM(item_count) > 50;
QUERY PLAN
|--SCAN itemlist (~1048576 rows)
`--USE TEMP B-TREE FOR GROUP BY
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     47    0                    0   Start at 47
1     SorterOpen     1     2     0     k(1,B)         0
2     Integer        0     4     0                    0   r[4]=0; clear abort flag
3     Null           0     7     7                    0   r[7..7]=NULL
4     Gosub          6     43    0                    0
5     OpenRead       0     2     0     2              0   root=2 iDb=0; itemlist
6     ColumnsUsed    0     0     0     3              0
7     Explain        7     0     0     SCAN itemlist (~1048576 rows)  0
8     Noop           0     0     0                    0   Begin WHERE-loop0: itemlist
9     Rewind         0     17    0                    0
10      Noop           0     0     0                    0   Begin WHERE-core
11      Column         0     0     9                    0   r[9]=itemlist.user_id
12      Column         0     1     10                   0   r[10]=itemlist.item_count
13      MakeRecord     9     2     11                   0   r[11]=mkrec(r[9..10])
14      SorterInsert   1     11    0                    0   key=r[11]
15      Noop           0     0     0                    0   End WHERE-core
16    Next           0     10    0                    1
17    Noop           0     0     0                    0   End WHERE-loop0: itemlist
18    OpenPseudo     2     11    2                    0   2 columns in r[11]
19    SorterSort     1     46    0                    0   GROUP BY sort
20      SorterData     1     11    2                    0   r[11]=data
21      Column         2     0     8                    0   r[8]=
22      Compare        7     8     1     k(1,B)         0   r[7] <-> r[8]
23      Jump           24    28    24                   0
24      Move           8     7     1                    0   r[7]=r[8]
25      Gosub          5     36    0                    0   output one row
26      IfPos          4     46    0                    0   if r[4]>0 then r[4]-=0, goto 46; check abort flag
27      Gosub          6     43    0                    0   reset accumulator
28      Column         2     1     12                   0   r[12]=itemlist.item_count
29      AggStep        0     12    1     sum(1)         1   accum=r[1] step(r[12])
30      Integer        1     3     0                    0   r[3]=1; indicate data in accumulator
31    SorterNext     1     20    0                    0
32    Gosub          5     36    0                    0   output final row
33    Goto           0     46    0                    0
34    Integer        1     4     0                    0   r[4]=1; set abort flag
35    Return         5     0     0                    0
36    IfPos          3     38    0                    0   if r[3]>0 then r[3]-=0, goto 38; Groupby result generator entry point
37    Return         5     0     0                    0
38    AggFinal       1     1     0     sum(1)         0   accum=r[1] N=1
39    Le             13    37    1                    80  if r[1]<=r[13] goto 37
40    Copy           1     14    0                    0   r[14]=r[1]
41    ResultRow      14    1     0                    0   output=r[14]
42    Return         5     0     0                    0   end groupby result generator
43    Null           0     1     2                    0   r[1..2]=NULL
44    Integer        0     3     0                    0   r[3]=0; indicate accumulator empty
45    Return         6     0     0                    0
46    Halt           0     0     0                    0
47    Transaction    0     0     1     0              1   usesStmtJournal=0
48    Integer        50    13    0                    0   r[13]=50
49    Goto           0     1     0                    0
sqlite>

If you define an appropriate overloaded index then this becomes clearer:

sqlite> create index idx on itemlist(user_id, item_count);
sqlite> SELECT SUM(item_count) AS totalitems FROM itemlist GROUP BY user_id HAVING SUM(item_count) > 50;
QUERY PLAN
`--SCAN itemlist USING COVERING INDEX idx (~1048576 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     39    0                    0   Start at 39
1     Noop           1     2     0                    0
2     Integer        0     4     0                    0   r[4]=0; clear abort flag
3     Null           0     7     7                    0   r[7..7]=NULL
4     Gosub          6     35    0                    0
5     OpenRead       2     3     0     k(3,,,)        0   root=3 iDb=0; idx
6     ColumnsUsed    2     0     0     3              0
7     Explain        7     0     0     SCAN itemlist USING COVERING INDEX idx (~1048576 rows)  0
8     Noop           0     0     0                    0   Begin WHERE-loop0: itemlist
9     Rewind         2     23    9     0              0
10      Noop           0     0     0                    0   Begin WHERE-core
11      Column         2     0     8                    0   r[8]=itemlist.user_id
12      Compare        7     8     1     k(1,B)         0   r[7] <-> r[8]
13      Jump           14    18    14                   0
14      Move           8     7     1                    0   r[7]=r[8]
15      Gosub          5     28    0                    0   output one row
16      IfPos          4     38    0                    0   if r[4]>0 then r[4]-=0, goto 38; check abort flag
17      Gosub          6     35    0                    0   reset accumulator
18      Column         2     1     9                    0   r[9]=itemlist.item_count
19      AggStep        0     9     1     sum(1)         1   accum=r[1] step(r[9])
20      Integer        1     3     0                    0   r[3]=1; indicate data in accumulator
21      Noop           0     0     0                    0   End WHERE-core
22    Next           2     10    0                    1
23    Noop           0     0     0                    0   End WHERE-loop0: itemlist
24    Gosub          5     28    0                    0   output final row
25    Goto           0     38    0                    0
26    Integer        1     4     0                    0   r[4]=1; set abort flag
27    Return         5     0     0                    0
28    IfPos          3     30    0                    0   if r[3]>0 then r[3]-=0, goto 30; Groupby result generator entry point
29    Return         5     0     0                    0
30    AggFinal       1     1     0     sum(1)         0   accum=r[1] N=1
31    Le             10    29    1                    80  if r[1]<=r[10] goto 29
32    Copy           1     11    0                    0   r[11]=r[1]
33    ResultRow      11    1     0                    0   output=r[11]
34    Return         5     0     0                    0   end groupby result generator
35    Null           0     1     2                    0   r[1..2]=NULL
36    Integer        0     3     0                    0   r[3]=0; indicate accumulator empty
37    Return         6     0     0                    0
38    Halt           0     0     0                    0
39    Transaction    0     0     2     0              1   usesStmtJournal=0
40    Integer        50    10    0                    0   r[10]=50
41    Goto           0     1     0                    0
sqlite>

Do you mean sqlite3_bind_value? https://sqlite.org/c3ref/bind_blob.html

Like every other statement, if the statement is executed outside an explicit transaction, then a transaction appropriate to the statement being executed is commenced as the first operation of statement execution, and (assuming no errors occurred) the transaction is committed as the last operation of statement execution.

Statements which only read data from the database occur inside an automatic read transaction. Statements which write/update data to the database occur inside a write (immediate) transaction.

Transaction states may be "upgraded" when required. For example, if a "read" transaction has been started already (whether explicitly or implicitly) and the statement being processed requires a write (immediate) trasaction then an attempt is made to upgrade the transaction level (with all the nasty implications that process entails).

If a transaction is in process when a statement commences execution, then the transaction state is not terminated at the end of that statement. The transaction state persists until it is terminated by your command. The transaction state will be the maximum level (read/immediate) that was required to process all the statements executed within the transaction context.

Note that this should get the same results. The index will need to contain the noted colums as the first three columns in the index (in any order).

The original query executes the correlated subquery for each row of the outer table and generates a plustitude of output, most of which is being uselessly generated and then checked using the IN operator -- this process would be highly inefficient.

Also of note is that 'Microsoft Access' is not a database system. It is a hooey-gui interface builder that speaks to a database engine. One can conclude by your reference to 'Microsoft Access' that you likely mean JET, which is one of the backend datastores that can be utilized by 'Microsoft Access'.

Unless I am misunderstanding your query (which is entirely possible), the following should work better (ie, contains less useless convolution).

SELECT grouper_name,
       synonym_id,
       code,
       grouper_id,
       country_id,
       synonym_name,
       code_name,
       version
  FROM groups as o
 WHERE (
        select sum(cnt)
          from (
                select 1 as cnt
                  from groups
                 where grouper_name == o.grouper_name
                   and synonym_id == o.synonym_id
                   and code == o.code
                 limit 2
                )
       ) > 1
;

You will need an index on groups (grouper_name, synonym_id, code).

Note that if you want to ensure that you get a row back containing either true (non-zero) or false (zero) then you can execute the following SQL, which will always return precisely one row containing exactly one integer result.

select exists (
               select *
                 from main.sqlite_master
                where type == 'table'
                  and name == ? collate nocase
               )
;

The easiest way to test for the existance of a table called theTable in schema main is to execute the following SQL:

select 1 
  from main.sqlite_master
 where type == 'table'
   and name == 'theTable' collate nocase
;

It also contradicts the code for the function.

The code does exactly what the label on the tin says: it reports non-zero (true) if the referenced VDBE statement is "executing" (that is, the state is RUNNING and the PC (program counter) is non-zero. If the VDBE statement is not executing, then it returns zero (false).

A VDBE program does not enter the RUNNING state until it is stepped the first time. It stays in RUNNING state until it either completes (signals SQLITE_DONE) or it is de-rugged (has its execution context pulled out from underneath it by performing an sqlite3_reset on the statement, thus causing the statement to no longer have a "RUNNING" context -- similar to sending, for example, SIG_ABORT/SIG_KILL to an executing program).

Like any other executable, compiling the source code (sqlite3_prepare) does not cause the output executable to be executed. You have to cause the execution to begin (by, for example typing the name of the file containing the compiler output on the command line which is merely syntactic sugar for passing the executable blob to the loader for loading and execution). You must use the sqlite3_step against the VDBE statement (output of the sqlite3_prepare compiler) to put the "blob of executable code" into an executing state.

Memory Mapped I/O does not increase I/O speed. The only wy to "speed up" I/O is to not do it at all. This has been the case for more than a century and is unlikely to change in the future unless the underlying limiting factor (called Physics) changes. Unfortunately, these underlying limiting factors have been stable and unchanging for BILLIONS of years and are unlikely to change in your lifetime.

Memory-mapped I/O merely eliminates a buffer copy operation and might also eliminate a transition between user and supervisor mode.

This leads to a race condition if multiple processes might be creating the table.

This is true only if the creation of a race condition was a design goal. If one was not hell-bent on creating a race condition then avoiding one is trivial.

Actually, I do have a "running" Windows 1.0 machine. I also have a running "Microsoft Bob" machine. I have a wee Zilog (Z-80) box running CP/M as well.

It works this way in Windows 1.0 and OS/2 as well and has for about 40 years...

This result is entirely expected (or should be).

select ?1, ?01;

Allocates a parameter array with length one, the parameter being named "?1". "?01" is a reference to parameter 1. (The 0 is silent).

select ?01, ?1;

Allocates a parameter array with length one, the parameter being named "?01". "?1" is reference to parameter 1 which was named "?01".

The "?" is intended for positional (numbered) parameters. Other characters "@", "$", and ":" are introducers for "named" parameters.

Note that mixing of positional and named parameters works as expected:

.param init
.param set :ijit "'ijit'"
.param set ?1 "'?1'"
select :ijit, ?1;

This allocates one parameter (number 1) with name ":ijit". The parameter "?1" is a numbered parameter that refers to the first (number 1) parameter.

If you are going to use the virtual table syntax, then you can do so:

select * from pragma_table_info('test') where schema == 'store';
select * from pragma_table_info where arg == 'test' and schema == 'arch';

Note that if you do pragma table_info('pragma_table_info') you will be presented with information regarding the pragma_table_info table.

sqlite> pragma table_xinfo('pragma_table_xinfo');
┌─────┬────────────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │    name    │ type │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼────────────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │            │      │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ cid        │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ name       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ type       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ aff        │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ coll       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ notnull    │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ dflt_value │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ pk         │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ rowid      │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ autoinc    │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ hidden     │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 11  │ arg        │      │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
│ 12  │ schema     │      │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
└─────┴────────────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘

NOTE: My implementation of the table_[x]info pragma returns additional information from the internal data dictionary that is not displayed in the as-distributed pragma.

NOTE ALSO: You have to use table_xinfo to get information on hidden columns.

sqlite3_db_handle() takes a pointer to a prepared (successfully compiled) statement as an argument and returns a pointer to the owning connection.

The sqlite3_prepare*() returned something other than SQLITE_OK, indicating a failure to prepare (compile) the statement source. As such there is no "prepared statement" from which the owning connection can be retrieved.

This is conceptually simillar to attempting to read byte 407f of the executable produced by a failed compliation. The compilation failure resulted in no executable output, so attempting to find the value of byte 407f of the executable will fail (there is no executable produced).

This may be helpful.

https://www.google.ca/search?q=principles+of+cache+design

There exists a great volume of data regarding cache design and efficiency dating from the early 1940's to today.

THe current tip of trunk displays the behaviour shown.

Why are you pissing around directly with ctime.c and not updating the mkctimec.tcl script which is supposed to generate ctime.c?

If mkctimec.tcl is no longer used to generate ctime.c and it is now maintained by direct edit, the tcl script should be deleted as it is useless.

I believe you labour under the misaprehension that the "subtype" is stored in the database. It is not. SQLite3 only recognizes five storage types: NULL, BLOB, TEXT, REAL, INTEGER and something called NUMERIC which is a made-up combination of REAL and INTEGER that does not actually exist as a type.

The "subtype" is something that gets "tagged onto a retrieved value" by "a function execution against the value".

Subtype is not persistent. It must be recalculated every time. Nor is the subtype associated with anything in particular related to the data value itself.

It is merely a way of tagging a retrieved value. If you were to take a TEXT item containing JSON and pass it to the Jimbo functions, then it would get a subtype of Jimbo. Subtype is not an inherent characteristic of a value but is a convenient way to "remember" something about the value after it has been processed by a function.

The first person to do something gets to name it. The procoess of turning C source code into an executable is called "compiling". If someone else had invented the process, then it might be called "frobnicating" instead.

Similarly, IBM decided back in the 1960's that the process of turning SQL "source code" into executable code was to be called "preparing" of the statement for execution.

THere is no reason that it could not be called "compiling" or "frobnicating" -- the result is the same. The hooman readable source code is turned into executable code.

There is no way to execute SQL (in SQLite3 or most other SQL-speaking storage systems) without first "preparing" (or compiling or frobnicating) the SQL into machine executable code.