SQLite Forum

Timeline
Login

50 events by user kmedcalf occurring around 2021-10-26 00:43:43.

More ↑
2021-11-16
23:29 Reply: Hot backup database in WAL mode by coping artifact: 623a68fb98 user: kmedcalf

No.

All "statements" are executed as presented, one after each, on the connection on which the statements were presented. If you have another connection, it may submit a "statement" for execution at any time including between statements executed on another connection. This is called concurrent access.

23:21 Reply: Hot backup database in WAL mode by coping artifact: a27f866abd user: kmedcalf

My second concern: Does executing PRAGMA wal_checkpoint(TRUNCATE); BEGIN IMMEDIATE; in a single statement make sure that the -wal file is fully committed, restarted and truncated when taking the backup? My concern are threads: While we use the database in Serialized mode, I am not sure if the internals of SQLite guarantee that the above two commands are executed in sequence or if they potentially could be separated (e.g. by another statement from another thread).

Yes. Each statement is independent and executed in the order received.

If you have 1 million connections to the database and on one of those connections you issue one command followed by another command, it is almost guaranteed that one of the other 999,999 connections will issue a command that, by haappenstance, occurs between the two commands issued on the other connection.

If you have 2 connections to the database and on one of those connections you issue one command followed by another command, it is guaranteed that the other connection may execute a command which is processed between the two commands given on the other connection unless you have taken steps to prevent that from happening.

If you need a guarantee that the two statements are executed side by each with no possibility of something happening in betwixt, then you need to ensure that you have guaranteed this.

23:12 Reply: Hot backup database in WAL mode by coping artifact: 173897e539 user: kmedcalf

Which word of not used for any other purpose is causing confusion?

20:01 Reply: Hot backup database in WAL mode by coping artifact: ca61d80c84 user: kmedcalf

Your "historical" method will work just fine, provided that you follow the following rules:

  1. Process 1 is used solely for the purpose stated and nothing else.
  2. Process 2 is used solely for the purpose stated and nothing else.
  3. Process 1 and Process 2 are separate processes (though they may be sibling children of the same parent process which does not access the database/files in any way).
  4. Once process 2 has completed then (and only then) may process 1 relinquish its lock and terminate.
  5. No "frikking about" is undertaken against the database files (which must consist of at least the main database file and any log files) such as deleting the log.
17:27 Reply: Tcl interface : pb with "rollback_hook" method artifact: 8ee9d60f86 user: kmedcalf

Can you point to where in the documentation is contained the statement that the errorcode on the connection has any validity at all during hook processing?

Or are you making assumptions that the procedure you are using will give you the results you desire (which it apparently does not).

2021-11-09
13:05 Reply: ExecuteNonQuery returns wrong number of affected elements artifact: 1461fc6ec4 user: kmedcalf

DELETE ... RETURNING ... works just perfectly (well, as perfectly as such an ill-considered thing can work).

The statement is, however, ill-conceived. You cannot use DELETE ... RETURNING ... in the manner used because it is a delete statement, not a projection statement, and only projection statements are permitted in nested projections.

2021-11-07
20:57 Reply: Delete duplicate rows with an extra character artifact: 9781bdd3cb user: kmedcalf

DELETE is not a valid conflict resolution method, hence two statements are required. (ie, you cannot do an UPDATE OR DELETE)

20:54 Reply: Delete duplicate rows with an extra character artifact: 0cc73c5ec6 user: kmedcalf

There are two statements.

The first is an UPDATE statement which, for all rows where the player_tag startswith '#', removes the '#' character prefix. For any row in which this change causes a conflict (duplicate player_tag after the update) the update is ignored. This means that the only rows remaining where player_tag commences with '#' are duplicates to be deleted.

The second statement deletes all those duplicates.

19:05 Reply: Delete duplicate rows with an extra character artifact: 122885c581 user: kmedcalf

Note that you will need to do pragma case_sensitive_like=1; to use the unique index on player_tag (unless, of course, player_tag is case insensitive) ...

19:00 Reply: Delete duplicate rows with an extra character artifact: 749c1de319 user: kmedcalf

One presumes you mean something like this:

DELETE FROM players o
      WHERE player_tag like '#%'
        AND EXISTS (
                    SELECT 1
                      FROM players
                     WHERE player_tag == substr(o.player_tag, 2)
                    )
;

The original, although it can be made to work, is flawed in that it processes all rows in the table, rather than just the rows that might need to be deleted. If you limit the examiation to only those rows implies that there is always a '#' as the first character so there is no need to search for it.

However, the following statements might be faster:

UPDATE OR IGNORE players
             SET player_tag = substr(player_tag, 2)
           WHERE player_tag like '#%'
;
DELETE FROM players
      WHERE player_tag like '#%'
;
2021-11-04
11:33 Reply: Is the order conserved in a table from a VALUES clause? artifact: 57bb6764ca user: kmedcalf

Yes. Always. It must be. There can be no change nor discussion on this point.

The parent of your so-called VALUES clause originates from the usage in an INSERT statement. It has just been generalized to not require an INSERT INTO prefix.

INSERT INTO x VALUES (1), (2), (3) ...;

is absolutely nothing more than semantic sugar for:

INSERT INTO x VALUES (1);
INSERT INTO x VALUES (2);
INSERT INTO x VALUES (3);
...

Therefore the processing of the list of values sets must be left-to-right (in order specified).

QED

2021-11-03
18:38 Reply: Get SQLITE_READONLY when using UPDATE command artifact: fb695bf2bb user: kmedcalf

Actually that is incorrect. Directory organization (rather than simply files in the root) appeared in DOS 2.0, not DOS 1.1, so the variable path separator was available since DOS 2.1 or perhaps some patch version of DOS between 2.0 and 2.1.

18:22 Reply: Get SQLITE_READONLY when using UPDATE command artifact: ba973d06af user: kmedcalf

Actually, / was accepted as a path separator since DOS 1.1

18:12 Delete reply: Get SQLITE_READONLY when using UPDATE command artifact: e2f211565e user: kmedcalf
Deleted
18:02 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 2d2d7cd87e user: kmedcalf

Now I get a hex E from result.

You mean you get an error:

(15) SQLITE_PROTOCOL

The SQLITE_PROTOCOL result code indicates a problem with the file locking protocol used by SQLite. The SQLITE_PROTOCOL error is currently only returned when using WAL mode and attempting to start a new transaction. There is a race condition that can occur when two separate database connections both try to start a transaction at the same time in WAL mode. The loser of the race backs off and tries again, after a brief delay. If the same connection loses the locking race dozens of times over a span of multiple seconds, it will eventually give up and return SQLITE_PROTOCOL. The SQLITE_PROTOCOL error should appear in practice very, very rarely, and only when there are many separate processes all competing intensely to write to the same database.

Unless of course I interpreting your vague "get a hex E from result" incorrectly.

Try specifying a proper filename in proper filename format and see if that helps (ie, forget about using the URI format, use the CPM/DOS/Windows filename format standard that has existed unchanged since at least 1979 if not earlier.

17:53 Reply: Get SQLITE_READONLY when using UPDATE command artifact: cfd173fc16 user: kmedcalf

The "proper" URL would be "file://Users/David/test.db" which would be the file \Users\David\test.db on the current drive (ie, the current directory location when the program is loaded.

The format specified is FUBAR.

The filename you used, unless URI filenames are enabled, would name the "database" as the alternate data stream called \Users\David\test.db on the file called file in the current direcvtory. This is probablyu not what you want.

You should specify the filename properly. Example: C:\\Users\\David\\test.db or c:/users/david/test.db. THis is standard DOS/Windows since about 1979.

2021-11-02
22:17 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 0ffe91a011 user: kmedcalf

What is the extended error code?

20:35 Reply: sqlite3_threadsafe() return value artifact: 3450eea435 user: kmedcalf

How about simply stating fact:

sqlite3_threadsafe() returns the compile-time threadsafe setting as reflected by the SQLITE_THREADSAFE compile-time define. It does not return the current threadsafe operating mode for either the connection or the library, which may have been overriden at runtime (see ...).

2021-11-01
23:24 Reply: Unable to open the connection artifact: 2da978aa43 user: kmedcalf

File Creation (Write/Change directory), Read, Write, File Deletion (Write/Change directory) each require a whole set of permissions in Windows, so you need to test them all.

Being able to "create" a file only requires read/write access to the directory but does not necessarily imply permission to read or write the actual file (nor to delete it, which is a read/write operation against the directory).

All operations need to be verified since no description of the particulars of the failure have been provided beyond an exeptionally vague "it does not work" without any specification of the error message received or how the behaviour observed differs from the behaviour expected.

2021-10-28
06:09 Reply: Database on android SD CARD artifact: 07b7d0a660 user: kmedcalf

I couldn't get it working.

I tried to paint the house blue. That did not work either!

It might be perspicacous to be more specific.

What exactly was the problem?

The battery was dead? -- buy a new battery will probably fix this.

You got gangrene and your fingers fell off? -- go see a prosthetician and see if they can do anything for you

Unfortunately, the number of possible reason for "did not work" is effectively unlimited. Perhaps a vasectomy will be helpful.

06:03 Reply: database creation getting hung up on a NFS mount path artifact: 2fb76e5907 user: kmedcalf

Now, if I register any other VFS like unix-excl using sqlite3_open_v2 call.. then it works fine.

This would indicate that the POSIX locking used when using the normal VFS is not implemented sufficiently correctly when the database file is on a remote NFS filesystem.

This is documented and expected.

06:00 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: 3a66ea2c0a user: kmedcalf
2021-10-26
04:23 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: 1bf33f0ef4 user: kmedcalf

Perhaps the CLI application can be modified to display the extended error code as well as the text. It is displayed in some places, but not in others.

Instead of printing the error message as:

Error: <message text>

could it perhaps be displayed as:

Error: <message text> (<extended error code>)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

That is instead of the CLI printing the message Error: database is locked in the above circumstance, perhaps the CLI could print the message Error during Step: database is locked (517).

This would make the CLI more useful as a debugging tool.

00:45 Edit reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: a7f2f09ebf user: kmedcalf

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be upgraded -- which case applies is up to the programmer to determine).

** NB: I have MonkeyPatched APSW to have added methods such as .begin, .execute, and .commit to the apsw Connection class **

00:43 Edit reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: cb728a42e1 user: kmedcalf

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done (because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be updated -- which case applies is up to the programmer to determine).

** NB: I have MonkeyPatched APSW to have added methods such as .begin, .execute, and .commit to the apsw Connection class **

00:38 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: f162d6f95b user: kmedcalf

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done (because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be updated -- which case applies is up to the programmer to determine).

2021-10-25
23:06 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: c33881c335 user: kmedcalf

Are you sure that the error code is not SQLITE_BUSY_SNAPSHOT, just being simplificated for display to the user by the application?

2021-10-22
00:14 Edit reply: SELECT STATEMENT FOR OGR2OGR IN GIS artifact: 7f1cf4791a user: kmedcalf

Neither LEFT( or RIGHT( are valid syntax.

LEFT(APN_D, 3) could be SUBSTRING(APN_D, 1, 3)
RIGHT(APN_D, 3) could be SUBSTRING(APN_D, 7, 3) (or perhaps SUBSTRING(APN_D, -3))

Alternatively, create the SQL functions LEFT and RIGHT.

00:08 Edit reply: SELECT STATEMENT FOR OGR2OGR IN GIS artifact: c198f66f99 user: kmedcalf
>sqlite
SQLite version 3.37.0 2021-10-20 17:10:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table apn(apn_d text not null);
sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN LEFT(APN_D, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D end from apn;
Error: near "(": syntax error
sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN SUBSTRING(APN_D, 1, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D end from apn;
Error: near "(": syntax error
sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN SUBSTRING(APN_D, 1, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + SUBSTRING(APN_D,7,3) ELSE APN_D end from apn;
sqlite> 
00:07 Reply: SELECT STATEMENT FOR OGR2OGR IN GIS artifact: d857d0d3cc user: kmedcalf
>sqlite
SQLite version 3.37.0 2021-10-20 17:10:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table apn(apn_d text mot null);
sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN LEFT(APN_D, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D end from apn;
Error: near "(": syntax error
sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN SUBSTRING(APN_D, 1, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D end from apn;
Error: near "(": syntax error
sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN SUBSTRING(APN_D, 1, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + SUBSTRING(APN_D,7,3) ELSE APN_D end from apn;
sqlite> 
2021-10-21
23:32 Reply: SELECT STATEMENT FOR OGR2OGR IN GIS artifact: 8b1051f010 user: kmedcalf

Neither LEFT( or RIGHT( are valid syntax.

LEFT(APN_D, 3) could be SUBSTRING(APN_D, 1, 3)
RIGHT(APN_D, 3) could be SUBSTRING(APN_D, 7, 3)

Alternatively, create the SQL functions LEFT and RIGHT.

02:38 Edit reply: I need a little help with UPDATE? artifact: 35117e44c3 user: kmedcalf

Note that this will replace ALL station names in t1 with whatever they are in "s" for the same lat-long

and any location not found will have the station name set to null.

Either of the following will fix that (assuming that you do not want not found station names set to null.

UPDATE t1
   SET start_station_name = coalesce((SELECT s.start_station_name
                                        FROM s
                                       WHERE start_lat = t1.start_lat
                                         AND start_lng = t1.start_lng
                                     ), start_station_name)
;
-- or --
UPDATE t1
   SET start_station_name = s.start_station_name
  FROM s
 WHERE s.start_lat = t1.start_lat
   AND s.start_lng = t1.start_lng
;
02:32 Reply: I need a little help with UPDATE? artifact: de7f14b20f user: kmedcalf

Note that this will replace ALL station names in t1 with whatever they are in "s" for the same lat-long

and any location not found will have the station name set to null.

2021-10-18
01:32 Reply: Column names and aliases in expressions artifact: 5ed7b20e02 user: kmedcalf

It is still a "secure the building" problem.

00:14 Edit reply: Column names and aliases in expressions artifact: 6b0a817262 user: kmedcalf

Note that <tablename> are not ambiguous due to the fact that there is a search-order for finding tables. For a bare (unqualified) table name, the search order is as follows:

  • firstly, look in the temp schema
  • secondly, look in the main schema
  • thirdly, look in all other accessible schema in the order of attachment

and when the table is found, the (first) one found is used.

THe CLI .databases command will show you what databases (schemas) are attached to the current connection. seq=0 is always the main schema (the database opened when the connection was created), seq=1 is always the temp schema and is reserved for use by the temp schema at all times, even if it has not yet been created. seq=2 and up are "other accessible schema/databases in the order of attachment".

00:12 Reply: Column names and aliases in expressions artifact: 9cb5b81d6b user: kmedcalf

Note that

are not ambiguous edue to the fact that there is a search-order for finding tables. For a bare (unqualified) table name, the search order is as follows:

  • firstly, look in the temp schema
  • secondly, look in the main schema
  • thirdly, look in all other accessible schema in the order of attachment

and when the table is found, the one found is used.

THe CLI .databases command will show you what databases (schemas) are attached to the current connection. seq=0 is always the main schema (the database opened when the connection was created), seq=1 is always the temp schema and is reserved for use by the temp schema at all times, even if it has no yet been created. seq=2 and up are "other accessible schema/databases in the order of attachment".

00:02 Reply: Column names and aliases in expressions artifact: 43b8740461 user: kmedcalf

If you reference a "name" in a context in which that use is ambiguous, you will get a message telling you that the "name" is ambiguous.

As an example, if I have I have a subquery that refers to a column that exists in several tables that are in the parent query including itself, which instance will be used?

None. The computer will inform you that the column reference is ambiguous, which is an error condition that will prevent the query from being able to be prepared/compiled.

How do I refer to the specific one I want?

You use a fully qualified name that is not ambiguous. A referent to a table column has the format [[<schema>.]<table>.]<colname> or [<table-alias>.]<colname>. You must specifically identify the column completely and unambiguously. Once you have done so, the query may be prepared/compiled without raising an ambiguity error.

Computers do not guess (unless they have been programmed to do so). SQLite3 is not an IBM PL/1 Level H compiler. It does not try to "guess what you meant", but will rather "do what you say". If you say something ambiguous then you will be informed of your error. If you say something which is syntactically valid (but semantically a cluster-fuck) then you will get a cluster-fucking.

Mutatis mutandis for any other "name" which may be ambiguous when specified without sufficient qualifiers.

2021-10-16
09:10 Reply: Temp database artifact: 74f3e90063 user: kmedcalf

How do I find the current/default location?

This is Operating System dependent. https://sqlite.org/tempfiles.html Section 5

Why do I never find 1 in the seq column? (first column)

seq 0 is always the main database -- the one opened when the connection was created.
seq 1 is always the temp database -- which may not exist if it has never been used.
seq 2 and after are the currently attached databases in order of attachment.

As with :memory: I can attach as many '' (temp) databases as long as the name/alias is unique. Correct?

Yes. And each so attached database is unique to the connection.

03:16 Reply: Temp database artifact: 9cdef47899 user: kmedcalf

Also note that if you change TEMP_STORE, then the TEMP database will be cleared, however, ':memory:' and '' databases and attachments will not be cleared.

03:04 Reply: Temp database artifact: dd390b9eb7 user: kmedcalf

You will note that when you specify a filename ('filename.db' for example) then that file is opened and used as the database.

When you specify a filename ':memory:' then a database exclusive to that connection is created entirely in memory. No part of it will reside on disk.

If you create a temporary database by specifying a blank filename (ie, '') then the database will be created and treated according to the TEMP_STORE currently set. If TEMP_STORE is currently set to memory only, then there is no difference between a ':memory:' database and a '' database.

In the default case, a '' (temporary) database resides in a temporary disk file, however, since the disk file is temporary, data will not be written to disk until memory is exhausted.

https://sqlite.org/pragma.html#pragma_temp_store

02:41 Reply: Temp database artifact: dcdb9d6a21 user: kmedcalf

''

That is, you can specify a file name, the special name ':memory:', or an empty string.

Specifying a filename opens that file as a database. Specifying the special name ':memory:' opens a database that resides purely in memory (as in Volatile RAM).

Specifying nothing for the filename (an empty text string) causes a "temporary file" to be created and then opened as a database. Once the "temporary file" (database) connection is closed, the "temporary file" is deleted.

A 'named file' database may be accessed simultaneously by multiple connections, however, a ':memory:' or '' (temporary) database may only be exclusively accessed by the connection which created it -- it cannot be accessed by multiple connections.

You will note that shared-cache is a method of emulating multiple connections, however, there is only one single connection to the database.

2021-10-12
03:38 Reply: Compiling sqlite system on Ubuntu artifact: c0c4885b24 user: kmedcalf

a.out is the default name of the output file.

If you want it named something else, then you specify which file to output to with the -o option.

gcc shell.c sqlite3.c -lpthread -ldl -lm -o sqlite3

You may then need to make sure that the file sqlite3 is executable. If it is not, then chmod +x sqlite3 to make it executable.

It would probably be easier to just do a full source download and use the ordinary configure/make process. I would suspect that the generated makefile knows how to do make install (but I do not know for sure)

01:28 Reply: x86_64/amd64 sqlite3 binaries artifact: fc6c19f0b0 user: kmedcalf

Fossil is primarily a binary blob.

SQLite3 is source code.

In one case the product is intended to be used without user modification.

In the other, the product is quite useless without user modification (ie, application program).

Do people make the same complaint for other library code?

I am upset that there is no pre-compiled TOTP liberary available for linkage to my 128-bit machine built from bit slice (Intel 4004) processors.

2021-10-11
10:13 Reply: Question about the WAL checkpoint artifact: c8aa908b1a user: kmedcalf

It would probably be perspicacious to have the checkpoint operations occur on a separate thread with its own connection if you have turned off autocheckpoint in order to prevent checkpoints from running on the other connections/threads at commit time.

06:11 Reply: Question about the WAL checkpoint artifact: 840085999a user: kmedcalf

If a statment finished with sqlite3_step, but not closed so I can reuse it late. In this case that mean a long-running readers block prevent a checkpointer?

If sqlite3_reset is called on the statement either explicitly or implicitly then the statement is no longer executing -- it is completed. If the last call to sqlite3_step returned SQLITE_ROW then the statement is still executing and the checkpoint operation cannot checkpoint changes subsequent to the start of that transaction to the database.

If i want to do checkpoint in other thread and write in main thread, I has to create 2 connection for the same database in single thread mode?

You cannot have more than one thread call into the sqlite3 library (simultaneously) when in single-thread mode.

2021-10-10
04:31 Reply: Understanding memory allocation behavior of SQLite artifact: 27f435d986 user: kmedcalf

Actually, that would be the 1960's or 1970's, when VSAM KSDS arrived. Prior to that ISAM used fixed index (internal) nodes.

VSAM KSDS later became known as "a B-Tree" (Of B- B+ B* B*+ varieties).

04:02 Reply: Understanding memory allocation behavior of SQLite artifact: 7663c481a9 user: kmedcalf

You will also note that the default behaviour of CREATE INDEX is to build the index by in-order insertion. That is why the process

create table
insert data
create indexes

is quicker than

create table
create indexes
insert data
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.

More ↓