SQLite Forum

Timeline
Login

50 most recent forum posts by user kmedcalf

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.

18:19 Reply: What happens if I define LONGDOUBLE_TYPE as double? (artifact: da7cbb5453 user: kmedcalf)

That is what happens when using Microsoft compilers (for example Microsoft VIsual Studie on x64) because then the declaration long double is ignored (the long part) and it just becomes double. Microsoft compilers no longer generate code that is IEEE compliant.

You can define LONGDOUBLE_TYPE to be whatever you want the LONGDOUBLE_TYPE spelling to be -- it defaults to the spelling long double only if no other definition is given.

THe idea is that it gives the spelling of the "extended precision" declaration type for your particular compiler and platform.

It depends what you mean by "broken". Do you mean "broken" as in the Microsoft way, where the compiler still accepts the long double as the spelling for the double precision extended type but does not actually implement the type (it ignores the long qualifier).

You can always define LONGTYPE_DOUBLE as double.

Myself, I declare LONGTYPE_DOUBLE as __float128 so that high precision intermediates use 128-bit IEEE-754 floats.

2021-09-01
18:09 Reply: Lua db:close deletes WAL file (artifact: 8e121a3145 user: kmedcalf)

This is not particular nor peculiar to SQLite3 but applies to ALL libraries when abused in this manner.

18:02 Reply: Why WAL file (-wal) may disappear? (artifact: 868b497a4f user: kmedcalf)

How do you mean "connection is still open". What proof do you have that this is the case? In other words, how do you know the connection is open?

17:59 Reply: Finding overlap of boxed coordinates (artifact: 6534fd3497 user: kmedcalf)

There is an R-Tree extension specifically for this purpose when working with fixed co-ordinate systems and multi-dimensional rectangles. https://sqlite.org/rtree.html

There is a Geopoly extension specifically for this purpose when working with fixed co-ordinate systems and 2-dimensional polygons, which extends R-Tree by converting the N-sided polygons to 2-dimensional "bounding boxes" to quickly locate candidates. https://sqlite.org/geopoly.html

As for Python and Panda dataframe, have reference to the documentation for those whacks of code, neither of which are SQLite3.

2021-08-30
18:44 Reply: A thought on LIKE/GLOB optimization (artifact: 48076b81fa user: kmedcalf)

Mutatis Mutandis generally means "making the same/necessary change".

00:00 Edit reply: A thought on LIKE/GLOB optimization (artifact: e893ded226 user: kmedcalf)

That is untrue.

'A' sorts before 'a' in BINARY.
'A' sorts equal 'a' in NOCASE.
Mutatis mutandis all the single-byte latin alphabetic characters [A-Z] and [a-z].

While it is technically true that 'a' collate nocase between 'A' and 'B' is indeed true, so is 'A' collate nocase between 'a' and 'b'.

For further clarity and to avoid boundary conditions (because I am sure that someone somewhere will claim that 'oh, thats because they are equal') it should be noted that both of the following are also true:

'M' collate nocase between 'a' and 'z'
'm' collate nocase between 'A' and 'Z'

2021-08-29
23:53 Reply: A thought on LIKE/GLOB optimization (artifact: 4bff081882 user: kmedcalf)

That is untrue.

'A' sorts before 'a' in BINARY.
'A' sorts equal 'a' in NOCASE.
Mutatis mutandis all the single-byte latin alphabetic characters [A-Z] and [a-z].

While it is technically true that 'a' collate nocase between 'A' and 'B' is indeed true, so is 'A' collate nocase between 'a' and 'b'.

2021-08-26
20:31 Reply: Recursively Import 10 GB sized csv file to sqlite fast without blocking reads (artifact: a48a944442 user: kmedcalf)

DO the following:

PRAGMA journal_mode=WAL;
BEGIN;
DROP INDEX IF EXISTS idx;
DELETE FROM foo;
CREATE TABLE IF NOT EXISTS foo(ip TEXT, field1 TEXT, field2 TEXT, field3 TEXT, field4  TEXT, field5 TEXT, num1 REAL, num2 REAL, field6 TEXT);
VACUUM;
.mode csv
.import sample.csv foo
create index idx on foo(ip);
COMMIT;

Secondly, make sure you set a sufficient cache_size.

2021-08-24
22:04 Reply: SQLite.Interop.dll is not loading due to its dependency binaries are missing in windows server core OS (artifact: d9c9e88dec user: kmedcalf)

Use the dependency walker to find out what DLL you are missing and install it.

22:01 Reply: When is "IS" used in SQLite? (artifact: ab51b4d4bb user: kmedcalf)

The tests IS TRUE and IS FALSE test the ZERO flag.

If the ZERO flag is set in the CPU register when the value is loaded, then the value IS FALSE. If the ZERO flag is not set when the value is loaded into the accumulator, then the value IS TRUE.

In other words, any value that is 0 IS FALSE and any non-zero value IS TRUE.

This has been the state of affairs for about a decade shy of a century.

How people do not know this I cannot fathom.

NULL is neither True nor False (it is NULL). As a conditional, a NULL value evaluates as False.

This issue can probably be attributed to a lack of education and understanding of how digital computers work.

Also note that cast(something as boolean) does nothing because boolean is not an understood type (it means NUMERIC). Therefore a NULL or INTEGER value is unchanged, so cast(5 as boolean) is nothing more than a CPU and RAM intensive way to say 5.

However, in platforms that DO have a boolean type (SQLite3 not being one of them), then a cast to boolean becomes either 0 or 1 depending on whether the ZERO flag is set or not.

Claiming that cast(x as boolean) when x is already either integer or float does anything at all other than waist CPU and MEMORY resources is demonstrating their lack of understanding.

2021-08-23
07:35 Reply: Random crash doing SELECT (artifact: 7d76de2dd9 user: kmedcalf)

My conclusion from my experience is that SQLITE_OPEN_NOMUTEX does not support this, because this scenario means both T1 and T2 would access C, although there is no concurrent accessing to C because application protects it with M.

My conclusion is that your code is broken.

If you use SQLITE_OPEN_FULLMUTEX and the crashes go away, then the problem is isolated to your re-implementation of SQLITE_OPEN_FULLMUTEX not being sufficient and/or not working as you think it does.

If you use SQLITE_OPEN_FULLMUTEX and the crashes do not go away, that would indicate that you either found a bug (which needs to be fixed) or that your code is tromping on shit it owns not and causing the sewers to back up.

00:21 Reply: Coverting 305GB CSV to sqlite database (artifact: 28b351f272 user: kmedcalf)

See if the limiting factor is CPU, DASD (I/O), or RAM. Once you have identified the bottleneck, then go to the bank and get some money so you can go to the store and buy a bottle (CPU/DASD/memory) that has a bigger neck to replace the bottle with the small neck.

You have provided no information which could be used to generate any other process improvement.

2021-08-22
02:06 Reply: about "strict" mode (artifact: 5741068d63 user: kmedcalf)

There is no DATE/TIME type is SQLite3.

You can store an INTEGER epoch date/time in an INTEGER field, and INTEGER or REAL epoch date/time in a REAL field, and a TEXT format ISO-8601 subset datetime in a TEXT field.

A STRICT table merely requires that the datatype be an actual supported datatype, and changes the semantics of the "value container" from being the "cell" or "value" itself to encompassing the entire column and also making the PRIMARY KEY definition, if it is not an INTEGER PRIMARY KEY of a rowid-table, be a PRIMARY KEY constraint (cannot contain nulls) rather than merely syntactic sugar for a UNIQUE constraint.

This makes a table declared as STRICT follow the standard. It does not make SQLite3 more restrictive than the standard, it merely brings the table up to standard.

The other "missing" stuff can already be activated by setting the DQS, TRUSTED_SCHEMA and FOREIGN_KEYs defines when building the library.

Of course, it has always been possible to coerce SQLite3 to operate in strict compliance with the standard if one wanted to do so. Adding the STRICT keyword to the table definition is sufficient. If you wish to activate other features (DQS, Foreign Keys, Recursive Triggers, etc) you do that the same way you always have.

2021-08-20
03:25 Reply: How to estimate a database disk file size or in-memory db memory usage ? ? (artifact: f4c37307a8 user: kmedcalf)

https://sqlite.org/fileformat.html

You can do a "very rough" calculation as follows:

one byte for the count of fields
one byte for each field to indicate the type
additional bytes for text/blob fields for the max length
(that is, if it is a text field of length 3000 then that will take 3 byes, lets say)
and then for each field
+ 8 bytes for a integer or float
+ N bytes for each text/blob field (max)
four bytes for the length of all the above

this is the "record size" (more or less)

Compute the number of "records" that will fit on a page
Divide the number of records by the number records per page

This will give you the "fully packed" size (min data size)

The size of the table will be somewhere between the above size and the size yielded if each record occupies one page (assuming that the record size is less than the page size). If you keep updating the table, eventually it will reach a "stable size" that is somewhere between the two aforestated extremes.

2021-08-19
22:31 Edit reply: "database or disk is full" with in-memory database. Suggested next steps? (artifact: befa34108e user: kmedcalf)

THe memdb.c code returns that result code (SQLITE_FULL) when the database is full (no more memory is available or the number of pages is exceeded).

The solution is to buy more ram and/or to make sure that, once you have more ram, it can be accessed by the process.

If the process is using 32-bit addresses then the maximum memdb size is 1 GB.
If the process is using 64-bit addresses then the maximum memdb size is 1,048,576 TB.
If the process is using 48bit addresses then the maximum memdb size is 32 TB.

Presently, current computer technology uses 48-bits or less.

Limits on ram usage per process can also be set in the Operating System.

In this case it sounds like the memdb is limited to 1 GB either because (1) it ram out virtual memory (2) it is only using 32-bit address space, or (3) the Operating System has been configured to not permit the process access to more memory.

Note also that the memdb max size is configurable

22:28 Reply: "database or disk is full" with in-memory database. Suggested next steps? (artifact: 7cedf2c0cb user: kmedcalf)

These sessions use the default (1 GB) and a modified (4 GB) SQLITE_CONFIG_MEMDB_MAXSIZE

SQLite version 3.37.0 2021-08-18 18:05:52
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open "file:///x?vfs=memdb"
sqlite> create table x(x);
sqlite> insert into x select value from wholenumber where value between 1 and 100000000;
Error: database or disk is full
sqlite> ^Z


SQLite version 3.37.0 2021-08-19 19:52:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open "file:///x?vfs=memdb"
sqlite> create table x(x);
sqlite> insert into x select value from wholenumber where value between 1 and 100000000;
sqlite>

Note that table x will take about 2 GB of space ...

More ↓