SQLite Forum

Timeline
Login

50 events by user kmedcalf occurring on or before 2021-10-10 03:58:43.

More ↑
2021-10-10
03:58 Reply: Understanding memory allocation behavior of SQLite artifact: 380c7b4e09 user: kmedcalf

in-order loading will not eliminate splits, only rebalancing.

See https://en.wikipedia.org/wiki/B%2B_tree particularly the section on bulk-loading.

Insertion in sorted (index B-Tree) order has been used since the 1940's to "speed up" table load operations (it is "old technology").

03:46 Reply: Understanding memory allocation behavior of SQLite artifact: 4145a5b3aa user: kmedcalf

Note that this does not answer your question(s) directly. However, minimizing the amount of "work" being done also reduces the number of calls to malloc/free, and addressing the "root cause" will be more efficient in the long run (that is, if it takes you 20 minutes to shovel the snow out of the driveway, then having the shovel "empty faster" is not likely to make much difference. However, moving from Edmonton to Miami will speed up the process.)

03:36 Reply: Understanding memory allocation behavior of SQLite artifact: 191a960f3f user: kmedcalf

I generated a CSV file containing a bunch of random data using the following Python code:

import random

csv = open('x.csv', 'w')
print('col1,col2,col3,col4,col5', file=csv)
r = 0
a = [x for x in range(ord('A'), ord('Z')+1)]
u = 2**62
while r < 77000000:
    r += 1
    random.shuffle(a)
    print(random.randint(1,u), end=',', file=csv)
    print(random.randint(1,u), end=',', file=csv)
    print(random.randint(1,u), end=',', file=csv)
    print(random.randint(1,u), end=',', file=csv)
    print(''.join(map(chr,a)), file=csv)
csv.close()

Creating the WITHOUT ROWID table and extra index, and then loading the random data results as follows:

create virtual table s using vsv (filename = x.csv, header, affinity=integer);
Run Time: real 0.001 user 0.000000 sys 0.000000
create table x (col1 integer not null, col2 integer not null, col3 integer not null, col4 integer not null, col5 text not null, primary key(col1, col2, col3)) without rowid;
Run Time: real 0.000 user 0.000000 sys 0.000000
create index xa on x (col4, col5);
Run Time: real 0.000 user 0.000000 sys 0.000000
insert into x select * from s;
Run Time: real 512.051 user 503.484375 sys 8.156250

TimeThis :  Elapsed Time :  00:08:51.023

Doing the same thing using a standard table (not a WITHOUT ROWID table) looks like this:

create virtual table s using vsv (filename = x.csv, header, affinity=integer);
Run Time: real 0.000 user 0.000000 sys 0.000000
create table x (col1 integer not null, col2 integer not null, col3 integer not null, col4 integer not null, col5 text not null, primary key(col1, col2, col3));
Run Time: real 0.000 user 0.000000 sys 0.000000
create index xa on x (col4, col5);
Run Time: real 0.000 user 0.000000 sys 0.000000
insert into x select * from s;
Run Time: real 487.248 user 478.390625 sys 8.484375

TimeThis :  Elapsed Time :  00:08:29.620

Loading the same data directly into a standard table (not a WITHOUT ROWID table) and then creating the indexes results as follows:

create virtual table s using vsv (filename = x.csv, header, affinity=integer);
Run Time: real 0.001 user 0.000000 sys 0.000000
create table x (col1 integer not null, col2 integer not null, col3 integer not null, col4 integer not null, col5 text not null);
Run Time: real 0.000 user 0.000000 sys 0.000000
insert into x select * from s;
Run Time: real 114.776 user 110.484375 sys 4.156250
create unique index xp on x (col1, col2, col3);
Run Time: real 48.642 user 146.171875 sys 24.843750
create index xa on x (col4, col5);
Run Time: real 47.736 user 150.078125 sys 36.671875

TimeThis :  Elapsed Time :  00:03:34.656

Loading the data in sorted order and then creating the non-primary index looks thus for a WITHOUT ROWID table:

create virtual table s using vsv (filename = x.csv, header, affinity=integer);
Run Time: real 0.001 user 0.000000 sys 0.000000
create table x (col1 integer not null, col2 integer not null, col3 integer not null, col4 integer not null, col5 text not null, primary key(col1, col2, col3)) without rowid;
Run Time: real 0.001 user 0.000000 sys 0.000000
insert into x select * from s order by col1, col2, col3;
Run Time: real 187.497 user 253.875000 sys 48.234375
create index xa on x (col4, col5);
Run Time: real 52.749 user 150.750000 sys 50.046875

TimeThis :  Elapsed Time :  00:04:03.667

Creating the WITHOUT ROWID table and its secondary index, and then loading the data in-order of the primary key has the following result (as expected, since we are inserting in-order to one B-Tree and in random order to the other):

create virtual table s using vsv (filename = x.csv, header, affinity=integer);
Run Time: real 0.001 user 0.000000 sys 0.000000
create table x (col1 integer not null, col2 integer not null, col3 integer not null, col4 integer not null, col5 text not null, primary key(col1, col2, col3)) without rowid;
Run Time: real 0.000 user 0.000000 sys 0.000000
create index xa on x (col4, col5);
Run Time: real 0.000 user 0.000000 sys 0.000000
insert into x select * from s order by col1, col2, col3;
Run Time: real 392.314 user 454.437500 sys 51.906250

TimeThis :  Elapsed Time :  00:06:51.696

The conclusion is that the most efficient way to load the data is to insert it into a standard (ROWID) table and then create the indexes. Alternatively, loading the data in sorted order and then creating the additional index (after the data is loaded) also works and is the only alternative for a WITHOUT ROWID table to avoid B-Tree rebalance operations.

2021-10-09
22:55 Edit reply: Understanding memory allocation behavior of SQLite artifact: 68f0bb3574 user: kmedcalf

You will also note that if your schema is thus:

CREATE TABLE table_name 
(
   col1 INTEGER, 
   col2 INTEGER, 
   col3 INTEGER, 
   col4 INTEGER, 
   col5 STRING, 
   PRIMARY KEY(col1, col2, col3)
) WITHOUT ROWID
;
CREATE INDEX index_name ON table_name (col4, col5)
;

then in addition to all the fiddle-faddle trying to "convert" col5 from however you present it into a REAL or INTEGER (since you have indicated a preference for storage as an INTEGER, then if that does not work, as REAL, and if that does not work, then "as presented") you are also storing the contents of EVERY ROW TWICE.

Once in the WITHOUT ROWID B-Tree and then yet again in the index_name B-Tree. The only difference between the two B-Tree's being the key and the payload (all columns are required in both B-Tree's but the ordering is different), but all records are duplicated completely.

Since it is not possible to insert in-order, then no matter what you do you will be requiring TWO B-Tree rebalance operations FOR EACH ROW (batch) inserted.

Perhaps you need to re-visit your "design".

22:53 Reply: Understanding memory allocation behavior of SQLite artifact: a713acf701 user: kmedcalf

You will also not that if your chema is thus:

CREATE TABLE table_name 
(
   col1 INTEGER, 
   col2 INTEGER, 
   col3 INTEGER, 
   col4 INTEGER, 
   col5 STRING, 
   PRIMARY KEY(col1, col2, col3)
) WITHOUT ROWID
;
CREATE INDEX index_name ON table_name (col4, col5)
;

then in addition to all the fiddle-faddle trying to "convert" col5 from however you present it into a REAL or INTEGER (since you have indicated a preference for storage as an INTEGER, then if that does not work, as REAL, and if that does not work, then "as presented") you are also storing the contents of EVERY ROW TWICE.

Once in the WITHOUT ROWID B-Tree and then yet again in the index_name B-Tree. The only difference between the two B-Tree's being the key and the payload, but all records are duplicated completely.

Since it is not possible to insert in-order, then no matter what you do you will be requiring TWO B-Tree rebalance operations FOR EACH ROW (batch) inserted.

Perhaps you need to re-visit your "design".

22:36 Reply: Understanding memory allocation behavior of SQLite artifact: 6c19417c4c user: kmedcalf

In the following table definition taken from your post (with all your bad quotes fixed):

CREATE TABLE table_name 
(
   col1 INTEGER, 
   col2 INTEGER, 
   col3 INTEGER, 
   col4 INTEGER, 
   col5 STRING, 
   PRIMARY KEY(col1, col2, col3)
) WITHOUT ROWID;

Firstly, the index is on (col1, col2, col3). You would need a CREATE INDEX statement to create an additional index.

Secondly, STRING is not a known datatype. It is an alias for NUMERIC. If you actually intend to store "UNICODE TEXT" in this column then it may be perspicacious to declare the type as TEXT in order to avoid forcing the exercize of the conversion path (and the multiple malloc/free calls associated therewith) which will never be applicable (and cause a massive usage of CPU and memory for no useful purpose).

Thirdly, are you loading the table in-order?

05:39 Reply: Updating in-process connections on write artifact: 2098136c48 user: kmedcalf

There is. PRAGMA data_version' https://sqlite.org/pragma.html#pragma_data_version

However, the data_version will not change if the request is not made when no transaction is in progress on that connection, even if a change was previously committed on another connection.

05:32 Edit reply: Updating in-process connections on write artifact: d4840bf9e8 user: kmedcalf

The problem I'm having is that sometimes, after completing a write transaction, the RO connection doesn't "see" the change.

That is correct. WHen using the WAL journal mode, a connection will not "see" any changes made to the database on a different connection until the specific sequence (1) commits, either implicitly or explicitly, the changes; and then (2) the original connection (the one that you want to see the changes) must BEGIN A TRANSACTION, either explicitly or implicitly, in order to see the changes to the database made on the other connection.

This means that, for a connection, if the following holds:

Then a SELECT issued against that connection will "see" all changes committed to the database BEFORE the above condition was tested.

((Actually, that would be all changes committed before the statement which starts the transaction, not from the time the condition was tested.))

05:30 Reply: Updating in-process connections on write artifact: bf7e0d8f1d user: kmedcalf

The problem I'm having is that sometimes, after completing a write transaction, the RO connection doesn't "see" the change.

That is correct. WHen using the WAL journal mode, a connection will not "see" any changes made to the database on a different connection until the specific sequence (1) commits, either implicitly or explicitly, the changes; and then (2) the original connection (the one that you want to see the changes) must BEGIN A TRANSACTION, either explicitly or implicitly, in order to see the changes to the database made on the other connection.

This means that, for a connection, if the following holds:

Then a SELECT issued against that connection will "see" all changes committed to the database BEFORE the above condition was tested.

03:38 Reply: Updating in-process connections on write artifact: 28ff21d6ac user: kmedcalf

What do you mean by a read-only connection?

Do you mean a connection on which you have issued PRAGMA QUERY_ONLY=1; https://sqlite.org/pragma.html#pragma_query_only; the SQLITE_OPEN_READONLY flag was passed to sqlite3_open_v2 https://sqlite.org/c3ref/open.html; or, something else?

2021-10-05
21:33 Edit reply: Insert within CTE artifact: 98fb8943a2 user: kmedcalf

Do you mean something like:

create table t(x);
WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
INSERT INTO t SELECT x FROM cnt;

?

Or perhaps,

create table x as
 WITH RECURSIVE cnt(x) 
   AS (
                 SELECT 1
       UNION ALL
                 SELECT x+1 
                   FROM cnt
           LIMIT 1000000
      )
      SELECT x FROM cnt;
21:29 Reply: Insert within CTE artifact: 9a1011513e user: kmedcalf

Do you mean something like:

create table t(x);
WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
INSERT INTO t SELECT x FROM cnt;

?

2021-10-02
21:35 Reply: How to read Images from sqlite3 and show via opencv, PIL or Matplot lib ? artifact: c4cb567022 user: kmedcalf

The error messages are perfectly explicit about the error YOU are committing.

In the first case "Can't convert object of type 'bytes' to 'str' for 'filename'" is saying that the parameter you passed which is supposed to contain the 'filename' in text (unicode string) does contain data in the format required. The parameter you passed has type 'bytes' and it must be type 'str', and the 'bytes' that you provided cannot be 'magically converted' into a valid 'str'.

In the second case, "ValueError: embedded null byte", means that you passed an argument which must be a string 'str' and therefore cannot contain a null byte (0). However the value you passed was not a valid 'str' because it contained a null (0) byte.

THe likely cause is that the API you are calling requires you to present to it "Purple Giraffes" and you have instead presented "Fushia Tigers", although the internal conversion mechanism cannot convert "Fushia Tigers" into "Purple Giraffes".

THe proper course of action would be for you to read the documentation and find the API which accepts "Fushcia Tigers" and, if that API does what you want, then use it. If there is no API that accepts "Fushia Tigers" and all you have are "Fushia Tigers" then you either need to figure out how to do the conversion yourself, on just give up trying and go have a nice bottle of wine.

Your presentation of an incorrect data type is not an SQLite3 issue

2021-09-29
01:16 Reply: User-defined functions in the CLI? artifact: 17b16efcf2 user: kmedcalf

which is very much not possible currently

That is actually untrue. You can export symbols from an executable (such as the CLI). You do not actually need to name the "shared object" with a .dll or .so extension if you do not want to do so. You can call it "HumpingGiraff.Eaters" and STILL have it be the CLI application and STILL export symbols for external use.

I suspect that what the OP is asking is whether the CLI has a "CREATE FUNCTION" command and a compiler. The answer is no. However, the CLI does "contain" sqlite3_create_function and calls it quite fine for defining functions such as date(), time(), datetime(), strftime(), sin(), cos(), etc, etc, as specified in the documentation.

Although you can type "sqlite3_create_function" at the CLI user interface, it will have no different effect than if you type that phrase into a word processor.

01:04 Reply: Reset database artifact: a6ae99e7e8 user: kmedcalf

It would perhaps be perspicacious to close (as in sqlite3_close) the database before deleting it. Just sayin.

2021-09-28
19:56 Reply: Can I detect if the disk supports WAL mode? artifact: 292a68c5e4 user: kmedcalf

What is a VM/docker?

They are different things.

A VM (Virtuasl Machine) allows one to run "native code" on emulated or virtualized hardware. When working properly there is NO WAY for the software running on "virtualized hardware" to know that it is not running on "real hardware", and if it is possible to detect the difference, then the VM is not a real VM, it is merely a toy.

Docker, on the other hand, is an APPLICATION PROGRAM. It has nothing whatsoever to do with any "Virtual Machine". As a separate application in its own right, it has its own set of constraints -- one of which is that IT DOES NOT PROVIDE A SUFFICIENTLY WORKING FILESYSTEM FOR CONCURRENT ACCESS.

WHat you have asked does not really make any sense whatsoever.

There is nothing that can be done to make the "docker" application work properly, other than to re-write the docker application so that it works properly. It is a hopeless abortion for use by little kiddies to solve a problem that does not exist.

If you insist on using "Docker" then you must also live with the limitations of it.

As far a Virtual Machine, if you run an Operating System that works properly on BARE NAKED HARDWARE, then it will also work EXACTLY the same on a PROPERLY WORKING Virtual Machine.

No matter what you dop, "Docker" is knows to be broken and NOTHING whatsoever will fix it (running it on a VM, running it on bare metal, running it on Gode's Computer) will not help.

Docker is like a dory (a wee little row boat) that has a hole in the bottom. It matters not whether you put your dory on the Ocean or a Lake, or even in the toilet. Water will still perculate up through the holes and drown you.

2021-09-23
22:06 Reply: sqlite3_exec artifact: bdf03c6b29 user: kmedcalf

sqlite3_exec is a crutch that uses the SQLite3 prepare/bind/step APIs to provide a simplistic interface for use when it is adequate for the purpose.

It sounds like it is not adequate for your purpose.

Therefore, you should use the prepare/bind/step APIs directly.

And no, the callback function is used to handle the text results of executing the statement.

21:59 Reply: cannot start a transaction within a transaction using sqlite shell artifact: c6bd81ce51 user: kmedcalf

The root cause is likely that WSL does not work correctly and is insufficient for reliable use and should only be used for "entertainment purposes".

The Great Unwashed seem to commonly believe that these "entertainment quality only" systems are designed for "general purpose production use" when this has been shown, repeatedly, not to be the case.

I do not think there is anything that can be done other than to advise patients "Do not do that then".

2021-09-22
20:46 Reply: cannot start a transaction within a transaction using sqlite shell artifact: 8f7faacc3d user: kmedcalf

This is because of a failure to comprehend what is happening, and a failure to check the return code of each statement within a batch.

You are sending "commands" of the form: BEGIN; <do something>; COMMIT;

This ENTIRE bufferload consisting of THREE STATEMENTS is sent to the CLI for execution. However, the execution had an ABORT at the second statement in the batch; thus, aborting the batch. The COMMIT; statement was never executed.

Thereafter, for each subsequent duplicate bufferload of three statements batched together, the first statement in the batch, BEGIN;, is executed when a transaction is in progress. An error message that you cannot start a transaction within a transaction is issued and the entire batch is ABORTED -- the remaining three statements in the batch are not executed.

Lather, Rinse, Repeat.

As for the difference with DELETE journal vs WAL probably is the result of an attempt to upgrade a stale snapshot.

18:34 Reply: cannot start a transaction within a transaction using sqlite shell artifact: dbb5e7c5d7 user: kmedcalf

Have you checked the return code from each command?

2021-09-21
19:36 Edit reply: How to find out whether a table is STRICT? artifact: bc3f2aa846 user: kmedcalf

Unfortunately you have to modify the SQLite3 source code to add/change pragma's. It is very easy to do, but I do not think there is a way to dynamically add in pragma extensions.

I modified table_[x]info to add aff, coll, rowid, autoinc and the cid=-1 internal rowid column (affinity, collation, whether a rowid or not, and if a rowid, whether autoincrement is in effect).

I added database_info(schema, type, name), trigger_list(seq, name, tr_tm, op).

table_list shows schema, type, name, ncols, ro, eph, rid, pk, ai, named, strict, shad for each table. (respectively the schema/name/type (table, etable, vtable, view), number of columns, whether read only, whether ephemeral, whether has a rowid, whether has a primary key, whether the rowid uses autoincrement, whether the rowid is "named", whether the table is strict, and whether it is a shadow table or not.

Patches to mkpragmatab.tcl.patch and pragma.c.patch are on http://www.dessus.com/files/mkpragmatab.tcl.patch and http://www.dessus.com/files/pragma.c.patch respectively.

I believe the patches are up-to-date. I regenerate them from time to time as the SQLite3 code changes.

19:35 Edit reply: How to find out whether a table is STRICT? artifact: 09995f663d user: kmedcalf

Unfortunately you have to modify the SQLite3 source code to add/change pragma's. It is very easy to do, but I do not think there is a way to dynamically add in pragma extensions.

I modified table_[x]info to add aff, coll, rowid, autoinc and the cid=-1 internal rowid column (affinity, collation, whether a rowid or not, and if a rowid, whether autoincrement is in effect).

I added database_info(shema, type, name), trigger_list(seq, name, tr_tm, op).

table_list shows schema, type, name, ncols, ro, eph, rid, pk, ai, named, strict, shad for each table. (respectively the schema/name/type (table, etable, vtable, view), number of columns, whether read only, whether ephemeral, whether has a rowid, whether has a primary key, whether the rowid uses autoincrement, whether the rowid is "named", whether the table is strict, and whether it is a shadow table or not.

Patches to mkpragmatab.tcl.patch and pragma.c.patch are on http://www.dessus.com/files/mkpragmatab.tcl.patch and http://www.dessus.com/files/pragma.c.patch respectively.

I believe the patches are up-to-date. I regenerate them from time to time as the SQLite3 code changes.

19:34 Reply: How to find out whether a table is STRICT? artifact: 306b1c9009 user: kmedcalf

Unfortunately you have to modify the SQLite3 source code to add/change pragma's. It is very easy to do, but I do not think there is a way to dynamically add in pragma extensions.

I modified table_[x]info to add aff, coll, rowid, autoinc and the cid=-1 internal rowid column (affinity, collation, whether a rowid table or not, and if a rowid table, whether autoincrement is in effect).

I added database_info(shema, type, name), trigger_list(seq, name, tr_tm, op).

table_list shows schema, type, name, ncols, ro, eph, rid, pk, ai, named, strict, shad for each table. (respectively the schema/name/type (table, etable, vtable, view), number of columns, whether read only, whether ephemeral, whether has a rowid, whether has a primary key, whether the rowid uses autoincrement, whether the rowid is "named", whether the table is strict, and whether it is a shadow table or not.

Patches to mkpragmatab.tcl.patch and pragma.c.patch are on http://www.dessus.com/files/mkpragmatab.tcl.patch and http://www.dessus.com/files/pragma.c.patch respectively.

I believe the patches are up-to-date. I regenerate them from time to time as the SQLite3 code changes.

19:12 Reply: create taable with syntax error works ? artifact: ed960841e2 user: kmedcalf

This is a SEMANTIC error, not a SYNTAX error.

Their is know way for the computer to no that you elected to be shooting up the wrong words.

2021-09-20
21:06 Reply: How to find out whether a table is STRICT? artifact: 755549daff user: kmedcalf

Note that the as-distributed version SQLite3 does not have a table_list pragma to report the list of tables and table information stored in the internal schema, however, this does not mean that there is not quite a lot of useful information stored in the internal data dictionary for each table.

21:02 Reply: How to find out whether a table is STRICT? artifact: 501b42cc24 user: kmedcalf

The table_info (and table_xinfo) return information about the columns in the table. This is the wrong place to store information about the table itself. STRICT is an attribute of the table, not a column, and therefore is already stored where it belongs, with the table attribute data.

sqlite> create table x(x);
sqlite> create table y(y integer) strict;
sqlite> pragma table_xinfo(x);
┌─────┬──────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │ type │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │      │      │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ x    │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴──────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_xinfo(y);
┌─────┬──────┬─────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │  type   │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────┼─────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │      │         │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ y    │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴──────┴─────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_list;
┌────────┬────────┬────────────────────┬───────┬────┬─────┬─────┬────┬────┬───────┬────────┬──────┐
│ schema │  type  │        name        │ ncols │ ro │ eph │ rid │ pk │ ai │ named │ strict │ shad │
├────────┼────────┼────────────────────┼───────┼────┼─────┼─────┼────┼────┼───────┼────────┼──────┤
│ main   │ table  │ y                  │ 1     │ 0  │ 0   │ 1   │ 0  │ 0  │ 0     │ 1      │ 0    │
│ main   │ table  │ x                  │ 1     │ 0  │ 0   │ 1   │ 0  │ 0  │ 0     │ 0      │ 0    │
│ main   │ table  │ sqlite_master      │ 5     │ 1  │ 0   │ 1   │ 0  │ 0  │ 0     │ 0      │ 0    │
└────────┴────────┴────────────────────┴───────┴────┴─────┴─────┴────┴────┴───────┴────────┴──────┘
20:52 Reply: adding record if it doesn't already exist artifact: 2cca664331 user: kmedcalf

Well, that is a design flaw (failure to store needed information) and has nothing whatsoever to do with the question asked. Unless the inserted record contains a timestamp of when it was inserted, there is no way to tell when the record was inserted. Playing with pseudokeys will not be helpful in this regard. It also does not really matter what some excrement-head thinks "after the fact" -- they can either accept the answer "I don't know" (which by the way is an incorrect answer -- the correct answer would be "that information was not recorded").

And going back to the post to which you replied, it only appears to the uneducated that there is a need to know the pseudokey for the row. It is actually not needed for anything at all.

create table names
(
 name_id integer primary key,
 name text not null collate nocase unique
);
create table houses
(
 house_id integer primary key,
 address text not null collate nocase unique
);
create table peasants
(
 id integer primary key,
 name_id integer references names,
 house_id integer references houses
);
create view persons 
as select peasants.id, 
          name, 
          address 
     from peasants, names, houses
    where (peasants.name_id is null or peasants.name_id == names.name_id)
      and (peasants.house_id is null or peasants.house_id == houses.house_id);
create trigger ins_person instead of insert into persons
begin
  select raise(ABORT, 'Name cannot be NULL') where new.name is null;
  insert or ignore into names (name) values (new.name);
  insert or ignore into houses (address) select new.address where new.address is not null;
  insert into peasants (name_id, house_id)
  select (select name_id from names where name == new.name),
         (select house_id from houses where address == new.address);
end;

Seems to me that there is no need to EVER deal with the pseudokeys. So I can see why someone might think they need to know the pseudokey, but they are incorrect.

08:52 Reply: adding record if it doesn't already exist artifact: 8e1549f8c1 user: kmedcalf

Why ever would you care?

If the record did not exist before the insert, then it was inserted; and if it already existed before the insert, then it was not inserted -- the net result in any case, where no error is raised to the application when the statement is executed, is that after execution the record will exist.

What ever else could you possibly need and for what conceivable reason?

03:09 Reply: adding record if it doesn't already exist artifact: 39a3149d9e user: kmedcalf

Assuming that you have a unique index on table1.email, then why not use this more simple command:

INSERT OR IGNORE INTO table1(email) VALUES ('test@domain.com');

Of course, if you either have no useable index then you can use a longer more inefficient method such as:

INSERT INTO table1 (email) 
     SELECT email 
       FROM (
             SELECT 'test@domain.com' AS email
            ) AS o
      WHERE NOT EXISTS (
                        SELECT *
                          FROM table1
                         WHERE email == o.email
                       );

The former is much shorter and staightforward while the latter is much longer and slower, but is standard SQL that should work unchanged on all SQL platforms since about 1985.

02:30 Reply: Suggestion to support gzipped text in vsv extension artifact: 3b6330655d user: kmedcalf

This change allows the vsv.c extension to read EITHER a plain-text file, OR a gzipped plain-text file as the filename= parameter automagically.

If you have a file called THEFILE.CSV then you can gzip that file using the command gzip THEFILE.CSV which will create a file called THEFILE.CSV.gz

See https://en.wikipedia.org/wiki/Gzip

For a file that is about 32 MB, the GZipped stream is about 5 MB.

02:02 Reply: Suggestion to support gzipped text in vsv extension artifact: faf17605aa user: kmedcalf

Added the patch and it appears to work correctly both when "in-line" and when used as a loadable extension. When building as a loadable extension you have to define SQLITE_HAVE_ZLIB and add the zlib library when linking.

The latest code is here: http://www.dessus.com/files/vsv.c
and the whole bunch are: http://www.dessus.com/files/sqlite3extensions.zip

2021-09-19
20:57 Reply: Compiling FILEIO.C artifact: dd31edc609 user: kmedcalf

Include the directory containing the needed .h files on the command line. For example, if the "sexyturd.h" file is required and it can be found in directory "D:/Toilet" then add that include directory to your command:

-ID:/Toilet

Lather rinse and repeat adding each directory which contains an include file that the compiler needs to locate.

2021-09-18
03:11 Reply: about "strict" mode artifact: 82d3c34bef user: kmedcalf

even if you don't provide a value in the insert, there are explicit rules to auto-generate the value

No. If you do not specify a value for the rowid then IMPLICIT rules are used to calculate an IMPLICIT value. You cannot specify how the rowid is computed if you do not specify it explicitly.

You may either explicitly specify a value, or if one is not specified then one will be implicitly computed for you.

Notwithstanding whether the value of the rowid is given explicitly or calculated implicitly, the resulting value is only part of the "stable row data" if the column which is to contain the rowid is declared explicitly. If the column which is to hold the rowid value is not explicitly declared, then the value is ephemeral and is only guaranteed to identify a particular table row during the execution of the query returning the rowid. It is not a stable attribute of the row and may change (as viewed by a connection) any time that connection does not hold a lock on the database.

00:28 Reply: about "strict" mode artifact: 456eddc2b5 user: kmedcalf

No, implicitly generated rowids are NOT stable.

You are incorrect. Implicitly generated rowids are stable.

create table x
(
  rowid integer primary key,
  y
);
insert into x (y) values (1);

The rowid is implicitly generated but is stable.

You are confusing the rowid itself (which may be specified either explicitly or computed implicitly) with whether or not the rowid is being stored in an explicitly designated column and is part of the row data.

create table x
(
 y
);
insert into x (rowid, y) values (1,1);

Although the rowid is explicitly generated, it is stored in an implicitly defined column are therefor does not constitute part of the row data.

2021-09-17
18:09 Reply: about "strict" mode artifact: dc62c25813 user: kmedcalf

RowIds are not stable if the are generated by the DB.

What? Of course they are. It sounds to me like you are talking about a user error in allocation of the pseudo-key range amongst non-contiguous databases. (Meaning that you have the same database being created/updated in two (or more) places and you have not adequately managed the keyspace.)

This is a long solved problem (since at least 1940).

Although other methods have existed since the 1940's, you could use the "Microsoft Method" and simply assign a subrange to each instance.

2021-09-16
17:51 Reply: Javascript enforcement artifact: 90cca2cbd5 user: kmedcalf

It leads to a litterling of the world with useless Javascript.

The correct way to achieve the objective is to use plain links and a robots.txt file in the root. If some crawler disregards the robots.txt file, you nuke that fucker from orbit.

Javascript is evil. Javascript is the root of all evil. Badly written javascript (and there has never existed goodly-written-javascript in the entire history of the universe) is responsible for 99.999% of all safety and security issues since it was barfed-up by a moron.

2021-09-15
01:19 Reply: about "strict" mode artifact: 76bf2f315c user: kmedcalf

There already is a column type ROWID. You spell it INTEGER PRIMARY KEY in a rowid table (that is, a table that is not WITHOUT ROWID).

2021-09-13
18:07 Reply: Retrieve a record and delete it artifact: 09f3750fab user: kmedcalf

Yes. It is called a transaction.

BEGIN IMMEDIATE;
SELECT whopee FROM dickiedee WHERE yummy=fruitcake;
DELETE FROM dickiedee WHERE yummy=fruitcake;
COMMIT;

Changes made by a database connection within a transaction cannot be seen by any other connection. Also, when a connection has an "intent" lock on the database, no other connection may modify the database.

This is called ACID (not d-lysergic acid diethylamide by the way) and is a property of some database systems including SQLite.

https://sqlite.org/transactional.html

00:55 Reply: Android and desktop Java library artifact: 7cab869fb1 user: kmedcalf

A an aside, I have "samsung phone" and also "laptop". However in my case "samsung phone" I/O is slower than "laptop" by a factor of almost 1,000,000.

00:44 Reply: Android and desktop Java library artifact: d5502fbbf5 user: kmedcalf

The fastest way to perform I/O is to not do it.

It would appear that "Samsung phone" does I/O 10 times slower than "laptop".

00:42 Reply: .expert via api artifact: f48117d53a user: kmedcalf

This has been a longstanding issue. For some reason there is a great love of adding things to the Diagnostic Shell Tool (CLI) rather than the actual SQLite product.

I cannot fathom why one would do this, as it is fraught with all sorts of peril.

However, it is a rather trivial matter to remove all the "crappola" from the Diagnostic Shell Tool (CLI) and add it to the SQLite3 library where it belongs -- I have to do this every single time yet more dreck that belongs not in the CLI is added there instead of to the core.

00:32 Reply: Wishing CLI could be usefully embedded artifact: 2b94815cbd user: kmedcalf

think a good test of the interface would be that all the Windows-related cruft in shell.c could be removed and replaced with a Windows application that calls the embedded CLI.

I agree, and the same goes for the *Nix-related cruft.

I, for one, do not follow. The CLI does nothing at all except send SQL text to the SQLite3 library for processing.

What on earth would anyone want to "embed" it in another application for?

Why not just write your other application to interface with the SQLite3 library and use the API to execute SQL commands? (which is all the CLI does).

2021-09-10
17:04 Reply: where does the amalgamation preprocessed file go when unzipped artifact: 0366cf7746 user: kmedcalf

Third star from the left.

2021-09-08
22:38 Reply: SQLite3.exe fails to handle huge multi-line-comment artifact: 634aed769e user: kmedcalf

Well, actually, no, I have not enjoyed using the wrong comment indicators for any purpose because I do not do so.

The purpose of the /* this is an inline comment */ is to insert comments in-line.

The purpose of the -- the remainder of the line is useless to the computer and should be ignored.

So for example, if I want to comment AN ENTIRE LINE OR THE REMAINDER OF AN ENTIRE LINE, then I use the comment indicator designed for that use, namely "--". If I wish to comment out something in-line then I use the inline comment designators "\* comment *\".

The only placed that mixed use of comment indicators does not matter is when a pre-processor removes all the "crud and crappola" before passing the input to the processor (as in with a C compiler, for example). Otherwise, discarding of "crud and crappola" takes a long time. Choosing the correct comment introducer "this is a comment IN LINE with a valid command that should be processed" vs "the remainder of the line can be discarded because it is meaningless". Making the wrong choice should have the same deleterious effect on the chooser as any other ill-conceived choice made.

22:14 Reply: SQLite3.exe fails to handle huge multi-line-comment artifact: 417b8a4513 user: kmedcalf

This works but it should be noted that it is still 33% slower (at best) then using the correct comment introducer. Using the "in-line comment" capabilities for whole-line or block comments is ill-concieved from the get-go.

21:53 Reply: SQLite3.exe fails to handle huge multi-line-comment artifact: 17592ab3ce user: kmedcalf

The answer to the question "Doctor, Doctor, it hurts when I do this" is "Do not do that".

Have you considered that the SQL comment introducer is --?

Does the problem "go away" if you use proper SQL style comments (that is, do not do that anymore)?

Try using a good text editor to replicate the three characters -- in front of your commentary and see if that solves your problem?

21:37 Reply: WAL/SHM files do not get deleted with ReadOnly flag artifact: 0a3191bb1f user: kmedcalf

That would be a mistaken impression. It would be perspicacious to keep track of whether a connection is open or not directly rather than by relying on magical signals from the gods.

Sometimes when a connection is closed it is sunny out, sometimes it is not. Whether or not the sun is shining (like testing whether or not the WAL file exists) is not a recommended way to tell if a connection is open.

2021-09-04
17:32 Reply: about "strict" mode artifact: f5dbee109a user: kmedcalf

The STRICT keyword prevents the storage in a cell of a value that does not conform to the declared type affinity for the column in which the cell value is stored.

THe STRICT keyword prevents the use of gibberish type declarations in which heuristics are used to guess the column affinity. Rather, the type declaration of a column must be a specific datatype.

The STRICT keyword prohibits NULL in components of a PRIMARY KEY and requires that all components of a PRIMARY KEY be declared NOT NULL.

Without the STRICT keyword, any value of any type can be stored in any cell (intersection of column and row) notwithstanding the declared type/affinity.

Without the STRICT keyword, any gibberish may be used as a declared type name and heuristic rules are used to interpolate the datatype affinity meant. You can declare a column to be of type "Elongated Giraffe's with Big Boners" if you want, as compared to STRICT mode where you must use the proper datatype name NUMERIC for that case.

Without the STRICT keyword, a PRIMARY KEY declaration is merely syntactic sugar for a UNIQUE contraint.

It is not about performance -- it is about being STRICT.

Mayhaps the use of the STRICT keyword is more efficient (when storing rows) than simply declaring appropriate constraints which achieve the same ends without using the STRICT keyword, but the STRICT keyword is less typing and additional constraints do not need to be run at insert time.

17:14 Reply: The characters to be escaped in Sqlite3 to prevent Sql Injection artifact: b41758f3b4 user: kmedcalf

Escaping characters in SQL statements will not prevent so-called SQL Injection attacks.

They (what those of limited intellect call SQL Injection) are a common class of problem where the unwashed confuse CODE and DATA and attempt to execute DATA. It is extremely common in anything that Microsoft has touched, designed, or has anything whatsoever to do with. Everything from Microsoft must be examined carefully for this common defect because Micrsofties Live to Destroy absolutely everything, and everything Microsofties touch is ill-conceived.

Keep your CODE separate from your DATA. Stop executing DATA.

There is absolutely no need whatsoever for any type of quoting or escaping to avoid executing DATA. Use one of the mechnanisms, most invented more than a century ago, to keep your CODE and DATA separate, and do not execute DATA.

There is actually nothing more simple in the multiverse.

2021-09-02
20:30 Reply: What happens if I define LONGDOUBLE_TYPE as double? artifact: f8d5005aea user: kmedcalf

"Extended Precision" is a part of the specification. The "extended precision" requiremnent for a given type requires that the "extended precision" type be AT LEAST as precise as the base type, up to the precision of the next larger type.

base-2 floating point typically has "standard" types for 32-bit, 64-bit, 128-bit, 256-bit, 512-bit, and 1024-bit representations.

The "extended" 64-bit format must have at least the same precision as the standard 64-bit float and not more than the precision of a 128-bit float, however, you are correct that the representation of a base-2 extended precision floating point number is implementation defined.

Typically, a "long double" is ten bytes (80-bits) simply because the Intel 8087 math co-processor used that format internally -- it is how the IEEE requirement to "compute exactly then round half-even" was implemented (which implementation was really "compute with guard bits then round").

Some compilers treat "long double" as a 96-bit "storage space" (12 bytes) but what is stored there can be anything ranging from a IEEE 64-bit double up to some extended precision encoding.

Some compilers treat "long double" as a 128-bit space (one paragraph or 16 bytes) and may store in that space anything from a single IEEE 64-bit float (with padding), some internal representation that is implementation specific but more precise than a IEEE 64-bit float and less precise than a IEEE 128-bit float, or maybe even a IEEE 128-bit float.

More ↓