SQLite Forum

Timeline
Login

50 most recent forum posts by user dvdraymond

2021-11-17
13:16 Reply: ROWID primary key works slower than index (artifact: 53a09f7a9f user: dvdraymond)

I can't answer all of of your questions, but in this case the blob size vs the covering index is important. In the main B tree each record has the rowid and the blob for every value in the tree. With a 1K blob then you're only gonna get 3 records per 4k page (or use overflow pages) for the main storage.

Conversely the index on id only stores id, it doesn't store the blob in the index. Since it's only storing one integer for each record then you're gonna store thousands per page.

Since your query doesn't use any of the other fields, it doesn't have to go to the main table to do the query, it can get everything it needs from the nice compact index, which in this specific case means it's fetching a thousand times fewer pages from the database to complete it. If your query involved any of the other fields in the table, then the extra index on ID is going to be slower and not of any use.

2021-10-20
18:39 Reply: I need a little help with UPDATE? (artifact: 1bb7b4bdd9 user: dvdraymond)
Well, you haven't told us what you wanted it to do, or why you think there's a problem. So we have to guess on everything.

For starters as to what's wrong: "s" isn't defined anywhere in there.
2021-10-13
14:59 Reply: binary .dump format (artifact: d287c1fe01 user: dvdraymond)
I'm gonna risk showing my low knowledge and ask something.

For a binary dump of the data, why not just make a straight copy of all the leaf table b-tree pages? (I'm sticking my fingers in my ears and ignoring WITHOUT ROWID tables for now)

Go through each of the tables, and dump out the leaf pages in btree order (not original physical page order). If a leaf has overflow pages, maybe put them in order right after the leaf they overflow from.

We'll be storing sqlite_master, so a loader tool will have the whole schema. All the interior btree pages and all index pages (again, ignoring WITHOUT ROWID for now) are just there for quick search and wouldn't have had any data that needed storing. And when restoring a dump we're just gonna visit each record once and don't need quick searches.

Re-creating the tree part shouldn't be too bad if you have all the leaves, right? You could either decode them one at a time and do a bunch of inserts to the new file, or in a fancier move copy them all at once to the new file, then re-create the interior pages with something akin to create index. The only copied pages you'd have to make updates to would be for new root page numbers in sqlite_master, and overflow page pointers. Right? You'd probably just need a new header to list the start and end pages of each table in the dump.

Just storing the original leaf pages as-is would also let you re-use a bunch of already written code I'd bet.

Admittedly it wouldn't really work for comparing dumps of equivalent data, as how things are distributed between pages and on each page could be different. But as far as a "binary .dump format", why not?

Anyway, let me know how insane I'm talking. And apologies if it really is insane.
2021-09-28
13:36 Reply: Views, Casting vs and converters (artifact: 3a773c65a1 user: dvdraymond)
I believe when you set detect_types to both PARSE_DELCTYPES and PARSE_COLNAMES it prioritizes the column names first, then the declared types.

Since your declared type of my_col is INTEGER, and you only declared a converter for "BOOLEAN"

my_col as "my_col float [FLOAT]"
Because of the column name it looks for a converter for "FLOAT" and doesn't find it.
Since "my_col" is a bare column it checks for a converter for "INTEGER" and doesn't find it.
So it returns what it got from the query, which is 1

CAST(my_col as FLOAT) as [my_col cast float]
The column name "my_col cast float" doesn't conform to the "name [type]" format, so no check is done for a converter on the column name.
CAST(my_col as FLOAT) is not a bare column any more, so there is no declared type for it to look up a converter for.
So it returns what it got from the query, which is my_col cast as a float, or 1.0

my_col as "my_col bol [BOOLEAN]"
The column name of the correct format, so it checks for a converter for "BOOLEAN" and finds it.
It passes what it got from the query (1) to your registered function for "BOOLEAN" and gets back True

CAST(my_col as BOOLEAN) as [my_col cast bol]
The column name "my_col cast bol" doesn't conform to the "name [type]" format, so no check is done for a converter on the column name.
CAST(my_col as BOOLEAN) isn't a bare column any more, so there is no declared type for it to look up a converter for.
So it returns what it got from the query, which is my_col cast to the SQLite type of "BOOLEAN", which following the rules results in numeric affinity. 1 as numeric is still 1, so that's what you get back.


So the important things for you is that for PARSE_DECLTYPES when you do...
cast(my_col as SOMETHING)
...that "SOMETHING" is not carried through as the declared type. PARSE_DECLTYPES only works if it's selecting just a bare column from a table.
And for PARSE_COLNAMES, your column names need to follow the format of "name [type]"
2021-09-01
13:19 Reply: STRICT tables and UNIQUE indices (artifact: 146daf1138 user: dvdraymond)
How about adding in another column used as a boolean that says whether you have an actual value for that?

Where column "b" is your column needing the "non value" value (for this example -1), then how about something like:

create table foo (
    a not null,
    b not null,
    b_is_unknown not null
        check (b_is_unknown in (0, 1)),
    check (case when b_is_unknown then b = -1 end)
);
create unique index foo_idx on foo (a, b, b_is_unknown);


sqlite> insert into foo values (1, 2, 0); -- normal value
sqlite> insert into foo values (1, 2, 0); -- can't dupcliate normal values
Error: UNIQUE constraint failed: foo.a, foo.b, foo.b_is_unknown
sqlite> insert into foo values (1, 2, 1); -- using bad value for unknown
Error: CHECK constraint failed: case when b_is_unknown then b = -1 end
sqlite> insert into foo values (1, -1, 1); -- proper unknown value
sqlite> insert into foo values (1, -1, 0); -- -1 is an actual value here
sqlite> insert into foo values (1, -1, 1); -- can't submit 2 unknown b's with the same a
Error: UNIQUE constraint failed: foo.a, foo.b, foo.b_is_unknown
sqlite> insert into foo values (1, 2, -1); -- bad value for b_is_unknown flag
Error: CHECK constraint failed: b_is_unknown in (0, 1)
2021-08-31
14:54 Reply: Possible bug: Unexpected result using correlated aggregate subquery (artifact: 1d6f05c554 user: dvdraymond)
I mean you have to admit that it does seem a little weird that an aggregate function in the sub-query makes the outer query an aggregate. That is a little unexpected.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (a);
sqlite> create table y (b);
sqlite> insert into x values (1), (2);
sqlite> insert into y values (7), (8), (9);
sqlite> select (select group_concat(y.b) from y) from x; --returns 2 rows
7,8,9
7,8,9
sqlite> select (select group_concat(5) from y) from x; --returns 2 rows
5,5,5
5,5,5
sqlite> select (select group_concat(x.a) from y) from x; --now only returns 1 row
1,2

sqlite> select (select count(*) from (select group_concat(y.b) from y)) from x;
1
1
sqlite> select (select count(*) from (select group_concat(5) from y)) from x;
1
1
sqlite> select (select count(*) from (select group_concat(x.a) from y)) from x;
3
sqlite>
14:21 Reply: Possible bug: Unexpected result using correlated aggregate subquery (artifact: 85a3337362 user: dvdraymond)
> sqlite> CREATE TABLE x AS SELECT 1 a;
> sqlite> CREATE TABLE y AS SELECT 1 b UNION ALL SELECT 1;

Note that the table "y" has no column "a", only column "b"

sqlite> SELECT (SELECT group_concat(a) FROM y) unexpected, -- expected 1,1
...
   ...> FROM x;

The subquery (SELECT group_concat(a) FROM y) here returns 2 rows of 1 column, where the value of that one column is '1' for both rows, because "a" is reffering to the column in x, and not to anything in y. Since this subquery is being used to provide a single column value, only the first of those rows is being used, and the rest discarded.

To get your expected '1,1' you would either have to make it something like
sqlite> select (select group_concat(a) from (select a from y)), ... from x;

if you did indeed mean column "a", or it you mentioned the wrong column and really wanted "b", then

sqlite> select (select group_concat(b) from y), ... from x;
2021-08-24
15:20 Reply: Most performant way to test for having 0, 1 or more rows in a table (artifact: 9d87c828fc user: dvdraymond)
You can see the limiting in the EXPLAIN output, as it's more verbose than the shorter, more readable EXPLAIN QUERY PLAN output.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (k integer primary key not null, data blob);
sqlite> .eqp full
sqlite> select (select count(*) from (select 1 from t limit 2)) = 1;
QUERY PLAN
|--SCAN CONSTANT ROW
`--SCALAR SUBQUERY 2
   |--CO-ROUTINE SUBQUERY 1
   |  `--SCAN t
   `--SCAN SUBQUERY 1
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     28    0                    0   Start at 28
1     Integer        22    3     0                    0   r[3]=22; return address
2     Once           0     22    0                    0
3     Null           0     4     4                    0   r[4..4]=NULL; Init subquery result
4     InitCoroutine  5     13    5                    0   SUBQUERY 1
5     Integer        2     6     0                    0   r[6]=2; LIMIT counter
6     OpenRead       1     2     0     0              0   root=2 iDb=0; t
7     Rewind         1     12    0                    0
8       Integer        1     7     0                    0   r[7]=1
9       Yield          5     0     0                    0
10      DecrJumpZero   6     12    0                    0   if (--r[6])==0 goto 12
11    Next           1     8     0                    1
12    EndCoroutine   5     0     0                    0
13    Integer        1     8     0                    0   r[8]=1; LIMIT counter
14    Null           0     9     9                    0   r[9..9]=NULL
15    InitCoroutine  5     0     5                    0
16      Yield          5     19    0                    0   next row of subquery_1
17      AggStep        0     0     9     count(0)       0   accum=r[9] step(r[0])
18    Goto           0     16    0                    0
19    AggFinal       9     0     0     count(0)       0   accum=r[9] N=0
20    Copy           9     4     0                    0   r[4]=r[9]
21    DecrJumpZero   8     22    0                    0   if (--r[8])==0 goto 22
22    Return         3     0     0                    0
23    Integer        1     1     0                    0   r[1]=1
24    Eq             10    26    4                    64  if r[4]==r[10] goto 26
25    ZeroOrNull     4     1     10                   0   r[1] = 0 OR NULL
26    ResultRow      1     1     0                    0   output=r[1]
27    Halt           0     0     0                    0
28    Transaction    0     0     1     0              1   usesStmtJournal=0
29    Integer        1     10    0                    0   r[10]=1
30    Goto           0     1     0                    0
0
sqlite>
2021-08-16
12:49 Reply: Avoiding recursive CTE materialisation (artifact: 6523441ecc user: dvdraymond)
Perhaps something like this? (Haven't tested so might not work)

with recursive parents (id, parentId, name, found_what_i_want) as (
    select *, false from export where id = 60363923
    union all
    select export.*, export.id > 60363923 as found_what_i_want
    from export inner join parents on export.id = parents.parentId
    where not found_what_i_want
)
select id, parentId, name from parents where found_what_i_want;
2021-08-12
17:05 Reply: LEFT AND RIGHT OUTER JOINS problem (artifact: 9387707b37 user: dvdraymond)

SELECT ..., SUM(o.quantity)*o.price

You've got an aggregate function in there, but no GROUP BY. So it's using all the rows as your group, and treating the rest of the columns are bare columns, which it's taking from one of the rows "at random" (not actually random, but that's another discussion)

You need to either group by something for which you want info on the groups... or get rid of the aggregate functions.

13:40 Reply: SQLite irrespective query (artifact: 99678ffcb5 user: dvdraymond)
HAVING is only for GROUP BY queries, where you're applying the filter to the resulting groups, and not to the rows before they're grouped.

In this case you can just write it like you have the restrictions in English.
the shoppers who joined the company after 01-01-02020 or are a woman
...

FROM shoppers               -- the shoppers
WHERE                       -- who
date_joined >= '2020-01-01' -- joined the company after 01-01-02020
OR                          -- or
gender = 'F'                -- are a woman
...


Also, aliases should not be in single quotes, that's for string literals. Either use no quotes or double quotes to show it's an identifier. SQLite will probably let you do it, but it's being kind.

... AS Age
or
... AS "Age"
but not
... AS 'Age'
2021-07-22
13:20 Reply: problem report: incorrect join behavior (artifact: d730d754d7 user: dvdraymond)

See this optimization

A LEFT JOIN can sometimes be converted into an ordinary JOIN if there are terms in the WHERE clause that guarantee that the two joins will give identical results. In particular, if any column in the right-hand table of the LEFT JOIN must be non-NULL in order for the WHERE clause to be true, then the LEFT JOIN is demoted to an ordinary JOIN.

In your case the ON condition only references the left table, so there's no point in it being an outer join, and thus it treats it as an inner join.

13:01 Reply: How to submit changes (artifact: b7d7244259 user: dvdraymond)

If I understand you correctly, you're saying you tried to commit your own code to the master SQLite repository?

SQLite is open source, but not open-contribute. You can suggest changes here in the forums for example, but only the select few actual members of the team can commit code.

If it's "just" spelling issues (in the code and not the docs) then don't fret if they say no. I remember someone submitted a thing to remove all the trailing white space for example, and the decision was that was going to explode and cause noise in deltas with no actual benefit to the binaries. So someone looking for what changed from version A to B would see 1,000 lines of code changed... but none of those code changes would actually effect how anything worked.

12:44 Reply: operators IS TRUE and IS FALSE missing from diagrams and table plus possible evaluation errors (artifact: a148f53ae6 user: dvdraymond)
> Well it turns out that "is false" and "is not false" do not need to be special

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '' is false;
1
sqlite> select '' is (false);
1
sqlite> select '' is (+false);
0
sqlite> select false;
0
sqlite> select '' is 0;
0
sqlite> select '' is (0);
0
sqlite> select '' is (+0);
0
2021-07-20
13:48 Reply: cycle detection in CHECK constraint with recursive CTE (artifact: a184978699 user: dvdraymond)

Most DBMS's implement immediate Foreign-Keys by default, unlike SQLite.

With deferred FKs, the only mode in SQLite...

No, immediate is the default and you have to specify "deferrable initially deferred" to get deferred FKs.

Deferred Foreign Key Constraints

(Or I'm just misunderstanding what you're saying)

2021-07-19
19:43 Reply: Unexpected return from the NULL column (artifact: 3b70b2ac1d user: dvdraymond)

So we're saying in the original version, that since v3 is calculated as NULL, that the "v3 NOT IN..." should therefore evaluate to NULL, and thus the record should not pass the WHERE clause. But somehow it is.

2021-07-15
18:23 Reply: Should SQLite be able to optimize this query? (artifact: 3344977a85 user: dvdraymond)

My hunch is that you'll find that exact case with group_concat() a lot in the wild because we can't do ordered aggregates.

Adding in the ability to do

group_concat(word, ',' order by word)

would allow people to fix it, but then they'd be fixing it because it broke in the meantime.

12:56 Reply: Feature request: add index info to WITH clause table (artifact: 47e359f7ec user: dvdraymond)

I know SQLite has logic where it can create Automatic Indexes to help with queries. Perhaps if that was combined with AS MATERIALIZED hints to let it know it's ok to consider making an index on the materialization of the with statement?

Or is that already the case?

2021-07-14
19:19 Reply: can i custom index blob data? (artifact: a524364a3a user: dvdraymond)

Remember that you can make Indexes on expressions, so if the way you're pulling out stuff from the blob is something that can be expressed in SQL with deterministic functions, then you can index on it.

2021-07-09
13:23 Reply: How to handle collation version changes (artifact: 9edc83b98c user: dvdraymond)

Compared to PRAGMA integrity_check, does PRAGMA quick_check skip checking the validity of the sort order per the collation? (Is skipping this step one of the things that makes it "quick"?)

Yes. From the docs https://www.sqlite.org/pragma.html#pragma_quick_check

The pragma is like integrity_check except that it does not verify UNIQUE constraints and does not verify that index content matches table content. By skipping UNIQUE and index consistency checks, quick_check is able to run faster. PRAGMA quick_check runs in O(N) time whereas PRAGMA integrity_check requires O(NlogN) time where N is the total number of rows in the database. Otherwise the two pragmas are the same.

I tested it by inserting into a text field all the capitalization variants of 'ABC', then using pragma writable_schema to change it to text collate nocase. After closing and re-opening the database quick_check returned ok, but integrity_check found errors.

2021-07-07
14:36 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: 16d7383a0c user: dvdraymond)

I think the main reason changes() reports is for triggers. That way "on update" triggers will still fire even if all the old and new fields are the same for the record.

14:07 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: a3edf4ec1f user: dvdraymond)

Granted it's not the most thorough of tests, but in Windows, when I run an update query that doesn't end up changing any values, then the Modified and Accessed times of the file are not updated, despite changes() saying something was changed. When I do an update that does change values, then the Modified and Accessed times on the file do get updated. So I'd say yes, there is some optimization there. Whether it depends on the VFS... I haven't a clue.

2021-06-30
18:09 Reply: Duplicated rows (and missing rows) when SELECTing with ORDER BY (artifact: 195d361259 user: dvdraymond)

What does pragma integrity_check; return?

2021-06-10
15:28 Reply: Slow insert with prepare/step (artifact: c6759f6f31 user: dvdraymond)

So I just need to do separate execs for BEGIN and END before and after, is it the right way to do it?

That's my understanding anyway. Give it a whirl and let us know if that does indeed solve your issue.

14:15 Reply: Slow insert with prepare/step (artifact: 1db30fbdd7 user: dvdraymond)

There are two functions below doing the same thing. On my machine Test1 takes microseconds to complete, Test2 ~ 17-20 seconds. Why is Test2 so painfully slow? I expected it to be faster than Test1.

1 transaction in the first vs 1,000 in the second?

2021-06-09
12:14 Reply: Feature request: Stored Procedures (artifact: cd05f203c9 user: dvdraymond)

I'll give you just one scenario to help you see that views and triggers are not always enough.

How are views and triggers not enough for this?

SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table people (pid, name, address);

sqlite> create table phones (pid, phone);

sqlite> create view add_person as select pid, name, address, phone from people inner join phones using (pid);

sqlite> create trigger add_person_trg instead of insert on add_person begin insert into people values (new.pid, new.name, new.address); insert into phones values (new.pid, new.phone); end;

sqlite> insert into add_person values (1, 'First Last', 'Home', '555-1212');

sqlite> select * from people;
pid|name|address
1|First Last|Home

sqlite> select * from phones;
pid|phone
1|555-1212

sqlite>
2021-06-01
15:15 Reply: How to relate a row with more than one topic? See example (artifact: 314bfa4f70 user: dvdraymond)

And which column format does SQLite not support?

Don't know about MYSQL, but in PostgreSQL for example you could have an array field to hold all the topics

create table quotes ( quote_id integer not null primary key, quote text not null, topic_ids integer[] ); insert into quotes values (1, 'A successful man is a fall in love man.', array[1, 2, 3]);

But the only advantage of that would be 1 less table and a lower overall record count, which doesn't really help anything. You couldn't make them official foreign keys, updates would be harder, indexing going from topics to quotes would be harder, etc.

So, as people have recommended, it would be a lot better for the normal

create table quotes_to_topics ( quote_id int not null references quotes, topic_id int not null references topics, primary key (quote_id, topic_id) ) without rowid; create index topics_to_quotes_idx on quotes_to_topics (topic_id, quote_id);

2021-05-27
16:21 Reply: Is there a way to optimize this UPDATE command to run faster? (artifact: 90f7c9ed54 user: dvdraymond)

param_id doesn't seem to be showing up anywhere in your update query. Is that right, or should there also be a bit saying the param_id's are equal? If you've got more than one param_id per object_id then you're potentially messing things up I believe.

2021-05-25
15:01 Reply: How can I merge results from two SELECTs? (artifact: 80c56da25f user: dvdraymond)

If the real schema is as simple as the example you could use SQLite's bare columns in aggregates to get the highest snapshot for each parameter.

Something like... select max(snapshot_id) as snapshot_id, param_id, param_value from SnapshotParameters group by param_id order by param_id;

2021-05-19
15:11 Edit reply: ORDER BY out of order. Is this a bug or expected? (artifact: fe70cc3e9a user: dvdraymond)

I believe order by is supposed to look at the column aliases first, yes. I can't point you to anything specific that says it though.

Don't forget you can order by something that's not in the result columns. So you can explicitly say the column you want to order by.

sqlite> select printf('%8.2f', n) as n from t order by t.n desc;
┌──────────┐
│    n     │
├──────────┤
│   500.00 │
│  -574.41 │
└──────────┘

sqlite>
15:10 Reply: ORDER BY out of order. Is this a bug or expected? (artifact: 713d1aa50d user: dvdraymond)

I believe order by is supposed to look at the column aliases first, yes. I can't point you to anything specific that says it though.

Don't forget you can order by something that's not in the result columns. So you can explicitly say the column you want to order by.

┌──────────┐
│    n     │
├──────────┤
│   500.00 │
│  -574.41 │
└──────────┘

sqlite>
2021-05-11
13:33 Reply: Fast way to insert rows in SQLite (artifact: db7d038eb9 user: dvdraymond)

For the Python library, adding in "isolation_level = None" in the connection call gets rid of all the automatic transaction handling and leaves it in autocommit mode/full manual transaction control.

12:58 Reply: Fast way to insert rows in SQLite (artifact: f832398c19 user: dvdraymond)

Not sure that it would help at all for SQLite, but one example from remote DBMS inserts is using multiple records in your values statement.

So instead of...

INSERT INTO user VALUES (NULL,?,?,?);

...binding 3 values for 1 record per INSERT statement

You might try doing 10 at a time:

INSERT INTO user VALUES (NULL,?,?,?), (NULL,?,?,?), (NULL,?,?,?), (NULL,?,?,?)... x10

...binding 30 values for 10 records per INSERT statement

2021-05-06
14:02 Reply: Table Btree and Index Btree (artifact: 8cc2c69629 user: dvdraymond)

Rowid tables use table b-trees. "without rowid" tables and all indexes use index b-trees.

From the Database File Format page

A b-tree page is either a table b-tree page or an index b-tree page. All pages within each complete b-tree are of the same type: either table or index. There is one table b-trees in the database file for each rowid table in the database schema, including system tables such as sqlite_schema. There is one index b-tree in the database file for each index in the schema, including implied indexes created by uniqueness constraints. There are no b-trees associated with virtual tables. Specific virtual table implementations might make use of shadow tables for storage, but those shadow tables will have separate entries in the database schema. WITHOUT ROWID tables use index b-trees rather than a table b-trees, so there is one index b-tree in the database file for each WITHOUT ROWID table. The b-tree corresponding to the sqlite_schema table is always a table b-tree and always has a root page of 1. The sqlite_schema table contains the root page number for every other table and index in the database file.

13:47 Reply: Why do I have a `SQLITE_AUTOINDEX` index for my primary key? Is the table itself not an index on the PK? (artifact: f16f8ed866 user: dvdraymond)

In the second schema the hash value of base32 is unique instead.

CREATE INDEX hash_base32 ON hash(base32)

There "hash" is the table name and "base32" is the field name.

It's not "hash" as a function name and "base32" as an argument.

CREATE INDEX

2021-04-30
17:29 Reply: Where is the temporary database file stored? (artifact: bfd1f0c044 user: dvdraymond)

Temporary Files Used By SQLite Section 5 has the info I think you're looking for.

Temporary File Storage Locations

The directory or folder in which temporary files are created is determined by the OS-specific VFS.

On unix-like systems, directories are searched in the following order:

    1. The directory set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable
    2. The SQLITE_TMPDIR environment variable
    3. The TMPDIR environment variable
    4. /var/tmp
    5. /usr/tmp
    6. /tmp
    7. The current working directory (".") 

The first of the above that is found to exist and have the write and execute bits set is used. The final "." fallback is important for some applications that use SQLite inside of chroot jails that do not have the standard temporary file locations available.

On Windows systems, folders are searched in the following order:

    1. The folder set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable
    2. The folder returned by the GetTempPath() system interface. 

SQLite itself does not pay any attention to environment variables in this case, though presumably the GetTempPath() system call does. The search algorithm is different for CYGWIN builds. Check the source code for details.
2021-04-19
14:14 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: ff2610b7d0 user: dvdraymond)

How would an index be helpful in processing a "x & ? = 0" WHERE clause term? What algorithm do you propose?

Wouldn't you then only need to execute the test once per each unique value of x?

Go through the index in order, when x changes test the new x. Then you can either say "include every row until x changes" or "ignore every row until x changes"

On a simple test like this then it might not save much, but the more expensive the test, and the fewer distinct values of x then the more useful that would be.

(That or I'm missing something obvious)

2021-04-13
17:58 Reply: how DISTINCT remove the duplicate rows? (artifact: bd2555df20 user: dvdraymond)

In 2.4 of the SELECT documentation there's this:

4 . Removal of duplicate rows (DISTINCT processing).

One of the ALL or DISTINCT keywords may follow the SELECT keyword in a simple SELECT statement. If the simple SELECT is a SELECT ALL, then the entire set of result rows are returned by the SELECT. If neither ALL or DISTINCT are present, then the behavior is as if ALL were specified. If the simple SELECT is a SELECT DISTINCT, then duplicate rows are removed from the set of result rows before it is returned. For the purposes of detecting duplicate rows, two NULL values are considered to be equal. The usual rules apply for selecting a collation sequence to compare text values.

So it looks like it uses =/IS to compare them. And since 1 = 1.0 they're considered the same. Which one you get back is probably which one came first as it was traversing the values.

13:01 Reply: What does, if many threads and/or processes need to write the database at the same instant, mean? (artifact: cc6a7f156e user: dvdraymond)

PRAGMA journal_mode = WAL;

The script gets called in a loop from just two different machines on the network.

Just to make sure I'm understanding this right, each of those two machines is accessing its own separate database. Right? Because WAL mode doesn't work across different machines.

Or is that perhaps part of your problem :)

2021-04-05
17:58 Reply: What indexes help Window Functions (artifact: 543442cd01 user: dvdraymond)

Maybe "one index per table instance in the query" is more accurate?

What we're saying is that if you have a table t with fields a and b, with an index on a and an index on b, then in...

select * from t where a = 5 or b = 7;

Then SQLite with use only one of those two indexes, or do a full table scan. It will not use the A index for "where a = 5", use the B index for "or b = 7" and then de-duplicate the results.

If you have

select * from t as t1 join t as t2 on t1.id1 = t2.id2 where t1.a = 12 or t2.b = 42;

then again for t1 it will use one index or the full table, and for t2 it will use one index or the full table. It can't use an index on t2.id2 to speed up the ON clause and use an index on t2.b to speed up the WHERE and do some cross comparing. It can only pick one.

...or something like that.

2021-03-24
14:09 Reply: Help rewriting a query to match the speed of a 2nd query (artifact: bec274b1de user: dvdraymond)

but is there a way to rewrite the second without the the duplication of IN groups?

If you mean just without having to type/bind the values twice, then how about

with ProjIDs as (values ('PR0000014888'),
                        ('PR0000015204'),
                        ('PR0000016639'),
                        ('PR0000016678'),
                        ('PR0000016724'))
select ...
where b.ProjID in ProjIDs
...
where a.ProjID in ProjIDs
...
2021-03-23
13:28 Reply: When is SELECT ... FROM with no table-or-subquery valid. (artifact: fcb26719db user: dvdraymond)

And remember that at the top of the SELECT page it states:

Note that there are paths through the syntax diagrams that are not allowed in practice. Some examples:

  • A VALUES clause can be the first element in a compound SELECT that uses a WITH clause, but a simple SELECT that consists of just a VALUES clause cannot be preceded by a WITH clause.
  • The WITH clause must occur on the first SELECT of a compound SELECT. It cannot follow a compound-operator.

These and other similar syntax restrictions are described in the text.

2021-03-18
17:52 Reply: Math functions for arguments outside the domain should raise exception according to SQL Standard (artifact: fcc7206112 user: dvdraymond)

How about adding 2 argument versions where the second argument is an onError argument which explicitly states what you want it to do in that case?

That would put it in the hands of the query writer as opposed to being in the hands of the system owner in the environment variable version. Which is better I guess would be debatable.

2021-03-17
18:25 Reply: Serious problems with STORED columns (artifact: 33283001e1 user: dvdraymond)

The idea of a generated column is that you have something which can be determined solely from the other columns in that record. So the value doesn't really count as useful data, because it's completely dependent on other fields.

Example: Say for example you have a country table, and one of the fields, "geometry" is the shape of the country. So you're storing the entire outline of the country for display.

Now let's say in the same table you have a "surface_area" field and a "border_length" field. Well, each of those fields can be completely determined by the geometry field, and a bunch of calculations. So you can define them as calculated fields. If geometry gets changed, the calculated fields will change with it, and you don't need separate update statements, and can't accidentally update one and not the other.

You might used a stored field for something like the surface area. You don't want to go through the entire geometry of Russia and calculate the surface area every time you select that record, because it's an expensive operation. So marking it as stored means that the calculated value will get stored in the table/file. So if the geometry field isn't changing then you don't have to do all those expensive calculations again because it was stored for you to look at. But if the geometry field does get changed, then SQLite will again automatically do the calculations and store the new result for quick reference until geometry gets updated again.

The dump command in the CLI doesn't output those stored calculated fields because they're supposed to be able to be calculated from the other fields in the record. So it's "avoiding wasted storage space" if you will. It means it might be slower on a restore, because all those calculations have to be done again, but it's not "lost data" because it's supposed to be completely deterministic from the other fields.

16:00 Reply: Getting the error (artifact: a59c7ba43f user: dvdraymond)

SELECT * FROM master.leagues;

I am connected to only 1 database (for the time being).

Not sure if this is what you meant, but the database you connect to at the start is "main". So you'd either want "select * from leagues;" or "select * from main.leagues;".

You'd only run the above if you had ATTACH'd a new database under the name of "master"

ATTACH DATABASE

2021-03-16
15:40 Reply: Serious problems with STORED columns (artifact: 6fc4f9b690 user: dvdraymond)

Quick question.

According to the Generated Columns Page section 2.3.3

The expression of a generated column may only reference constant literals and columns within the same row, and may only use scalar deterministic functions. The expression may not use subqueries, aggregate functions, window functions, or table-valued functions.

So my question is: Is datetime() considered deterministic? Or should there have been an error when creating that table which said "deterministic functions only please"?

2021-03-08
14:35 Reply: how to open or create db in sqlite3 (artifact: b1194804d3 user: dvdraymond)
self.hdl = (os.path.join(pa, na),'c')
self.id1 = 0
print("File created")
self.hdl[b'id1'] = str(self.id1)

You're setting self.hdl to a 2 element tuple. Then 3 lines later it looks like you're trying to use it as a dictionary. Looks like there's a similar confusion in the other half of that init

2021-03-03
17:05 Reply: encoding on new DB-File (artifact: 00dca7ba42 user: dvdraymond)

I've using 3.34.0, and am using the CLI.

After way too long of experimenting I think I have it.

You have to vacuum it down to just the first page.

Then you have to close out of the CLI, and open the file again.

Then you have to do something that reads from the database before you set the new encoding.

Then you can create a new table and have it stick.

If you just use the CLI to open the 1 page databse, and immediately run "pragma encoding = 'UTF-16';" for example, then it won't change it. You have to do something like .tables first to get it to read the database, then set the encoding, then make a new table.

C:\Data\Temp\sqlite>sqlite3 DeleteMe.sqlite
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.

sqlite> drop table foo;

sqlite> vacuum;

sqlite> pragma encoding;
encoding
UTF-8

sqlite> .exit

C:\Data\Temp\sqlite>sqlite3 DeleteMe.sqlite
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.

sqlite> pragma encoding = 'UTF-16';

sqlite> create table foo (a);

sqlite> pragma encoding;
encoding
UTF-8

sqlite> drop table foo;

sqlite> vacuum;

sqlite> .exit

C:\Data\Temp\sqlite>sqlite3 DeleteMe.sqlite
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.

sqlite> .tables

sqlite> pragma encoding = 'UTF-16';

sqlite> create table foo (a);

sqlite> pragma encoding;
encoding
UTF-16le

sqlite>
15:58 Reply: encoding on new DB-File (artifact: 18cc31cb81 user: dvdraymond)
That doesn't work. At least not on my machine. Even if you remove everything and vacuum it then you'll still always have the first page with the header and the empty sqlite_master on it.

Running "pragma encoding = <a different option>;" doesn't change it. If you just run a straight "pragma encoding;" afterwards then it will report the new value yeah, but if you close and re-open it, then it's still the original. Setting it and immediately running a vacuum won't change it either like you would do for a page size change.

So any time after that first page is written you're stuck with what it was originally.
2021-02-25
12:37 Reply: sqlite3_prepare_v2 does not seem to work. (artifact: 0d8a78408f user: dvdraymond)

Well... the preview doesn't look very readable; what can I do?

At the top of the text box there's a drop down for what style to use. You can always change it to Plain Text to get rid of any fancy behavior.

If you click on the words "Markup style:" there, and then on "Markdown Formatting Rules" you get a guide of all the Markdown stuff. There's a section on "Literal/Verbatim Text - Code Blocks"

The most basic way to mark a section as without style is to use backticks before and after. Triple backticks seems best when going around a big block.

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main(int argc, const char * argv[])
{
  sqlite3 *db;
  if (sqlite3_open("the_insert.db", &db))
  {
    printf("Could not open the_insert.db\n");
    exit(-1);
  }
  printf("Database %s is open\n", "the_insert.db");
  //
  // Prepare a statement for multiple use:
  // =====================================
  //
  const char *query = "INSERT INTO someTable (second, third) VALUES (?,?)";
  int sqlSize = (int)strlen(query) + 1;
  sqlite3_stmt *stmt;
  
  int err = sqlite3_prepare_v2(db, query, sqlSize, &stmt, NULL);
  printf("err: %d\n", err);
  
  if (sqlite3_prepare_v2(db, query, sqlSize, &stmt, NULL))
  {
    printf("Error executing prepare statement: \n");  //, sqlite3_errstr());
    sqlite3_close(db);
    exit(-1);
  }

  printf("prepared\n"); // I NEVER GET THIS FAR!
  /*
   * The prepared statement executed multiple times
   * ==============================================
   */
  for (...) {
      sqlite3_bind_int (stmt, 1, ...);
      sqlite3_bind_text(stmt, 2, ...);

       if (sqlite3_step(stmt))
       {
          printf("execution failed\n");
          sqlite3_finalize(stmt);         // Only when things go wrong
          return ;
       }
      sqlite3_reset(stmt);
  } 
  //
  // Get rid of the memory allocated for stmt; mandatory!
  sqlite3_finalize(stmt);   
  sqlite3_close(db);  return 0;
}

More ↓