SQLite Forum

Timeline
Login

50 most recent forum posts by user cladisch

2021-06-07
11:28 Reply: how to open a SQLite3 database as read only (artifact: 7f15b663f3 user: cladisch)

Chrome opens the database exclusive mode.

I guess that tool just copies the raw files (which will result in corrupted data if writes have not yet been flushed).

2021-06-02
19:02 Reply: memory vs mmap (artifact: c814109b19 user: cladisch)

it is a problem with NFS3

Correct. Delegations, which allow caching, were introduced in NFS4.

08:09 Reply: memory vs mmap (artifact: c2ffb39009 user: cladisch)

fileserver: "hey just fyi another client modified this file you're interested in"
linux client: "roger that, I'll invalidate my cache"

Windows (SMB/CIFS) calls this opportunistic locks (oplocks), NFS calls this delegations. (The server tells the caching client to drop/flush its cache before allowing another client to access the file.)

If the network connection is temporarily broken and the client uses the stale cache later, you get data corruption. This was a somewhat common problem with SMB; I guess recent protocol versions protect against this.

2021-05-03
14:27 Reply: Feature Request make SQLite support asynchronous io (artifact: 714062396f user: cladisch)

But RWF_SYNC applies only to individual write operations.

Cached writes already are handled asynchronously by the kernel. SQLite does not care about individual writes, only that all of them have finished, and fsync() is the correct API for that.

Using an asynchronous API like libaio makes sense only when you have multiple parallel (read) requests, and when you can handle that the results become available in arbitrary order. This is not how the SQLite VFS works.

14:13 Reply: Setting temp_store_directory on Windows in future proof way (artifact: 885efe8e01 user: cladisch)

But getenv() might use a cached copy of the environment variables. So you must use _putenv() (which is not a standard function).

14:09 Reply: Bug report: inconsistent error message across different CONSTRAINT types (artifact: 2aa2d29c51 user: cladisch)

Why not?

It is likely to break in future versions. The exact syntax of error messages is not part of the API.

The only practical way of giving the calling code semantic information about the error is through additional error fields, such as the standard SQLSTATE, or Postgres' PG_DIAG_STATEMENT_POSITION. SQLite does not have those fields, so there is no good solution.

2021-05-02
15:06 Reply: Bug report: inconsistent error message across different CONSTRAINT types (artifact: d85ad6c8fa user: cladisch)

You are not supposed to try to parse error messages in code.

NOT NULL and UNIQUE messages do not show the constraint name because the constraint is already identified by its columns.

15:04 Reply: Feature Request make SQLite support asynchronous io (artifact: 5c19012abf user: cladisch)

I do not understand why fsync would be faster when issued through libaio, or with what you would replace it. Can you show how you would implement this in a VFS?

2021-03-21
16:24 Reply: FILE_SHARE_DELETE Flag (artifact: 3188bf3420 user: cladisch)

When you write the DB into a file, the data appears twice in the file cache (executable and temp file). A better solution for your problem would be to use a VFS that allows to read DB pages directly from the executable file: https://www.sqlite.org/src/file/ext/misc/appendvfs.c

2021-02-28
20:15 Reply: Is there a way to detect if committing a transaction will modify the database? (artifact: 75833ed4f8 user: cladisch)

The database can also be modified by ALTER, ANALYZE, CREATE, DROP, PRAGMA, or REINDEX.

Anyway, the sqlite3_txn_state() function determines the read/write transaction state.

2021-02-24
10:46 Reply: Improve sqlite3_column_name() return value documentation (artifact: 51f8700e48 user: cladisch)
  1. I used AS in my SQL code.
  2. I used SELECT *

I don't want programs depending on column names for anything else.

Then the SQL standard is not your friend. SQL-92 says in 7.9 9:

a) If the i-th <derived column> in the <select list> specifies an <as clause> that contains a <column name> C, then the <column name> of the i-th column of the result is C.

b) If the i-th <derived column> in the <select list> does not specify an <as clause> and the <value expression> of that <derived column> is a single <column reference>, then the <column name> of the i-th column of the result is C.

c) Otherwise, the <column name> of the i-th column of the <query specification> is implementation-dependent and different from the <column name> of any column, other than itself, of a table referenced by any <table reference> contained in the SQL-statement.

(SELECT * generates <column reference>s as in case b.)

Every SQL database must give predictable column names for direct column references.

And if it were necessary to write SELECT x AS x, y AS y FROM ..., such a database would not find many users.

10:07 Reply: Is there anything one could wish for? (artifact: 6c8fae79d0 user: cladisch)

You would use something like SELECT err('some message: ' || 'xxx');, without the built-in RAISE function.

(When I try this in Python, I get just "sqlite3.OperationalError: user-defined function raised exception", without the message. So it's not always useful.)

08:11 Reply: Is there anything one could wish for? (artifact: 1ca9c3d63f user: cladisch)

The RAISE function does not take a string expression but a name (an identifier, which is also allowed be written as a string literal).

In most cases, you could use a user-defined function that raises a 'normal' error.

2021-02-10
18:03 Reply: Unicode and CLI for Mac (artifact: a4c97eb922 user: cladisch)

I guess your console does not use UTF-8.

What is the output of SELECT x'c3a1', hex('รก');

2021-01-01
10:43 Reply: Checking database took about 350 more time as normal (artifact: 5ce5423edb user: cladisch)

Possible reasons:

  • the database was unusually large
  • the database was unusually fragmented
  • something else ate up the I/O bandwidth
  • disk errors
2020-12-27
10:08 Reply: Is using prepared statements without a FROM to employ built-in methods bad practice? (artifact: f88951d6b5 user: cladisch)

Reusing a prepared statement in a loop is certainly OK.

Keeping the statement longer does not hurt because it does not need much memory. But re-parsing the SQL would not need much timer either. It does not really matter either way.

The only misuse is using NULL as the last parameter of sqlite3_bind_text().

2020-11-29
20:50 Reply: SELECT How to include percentage? (artifact: 8f39a233b7 user: cladisch)

Division by zero is not possible because the GROUP BY would not generate any groups in that case.

To avoid integer trunaction, use:

COUNT(*) * 100.0 / (SELECT COUNT(*) FROM census)

20:13 Reply: SELECT How to include percentage? (artifact: 15136e9abd user: cladisch)

Percentage of what?

If TOTAL(census) means the number of all rows in the census table, then you can write:

SELECT COUNT(*), (COUNT(*) / (SELECT COUNT(*) FROM census)) * 100, ...
2020-11-10
07:46 Reply: Empty journal files left on disk after connection was gracefully closed (artifact: 41e5e8ab7d user: cladisch)

Can I configure sqlite to delete empty journal files?

In theory, the WAL files should be automatically deleted when the backup DB connection is closed: https://www.sqlite.org/wal.html#the_wal_file

You can also switch that DB out of WAL mode: PRAGMA journal_mode = delete;

Can I configure sqlite not to use shared-memory files?

Only when you have a single connection: https://www.sqlite.org/wal.html#use_of_wal_without_shared_memory

(SQLite does not differentiate between connections from multiple processes, and multiple connections from threads in a single process.)

2020-11-09
09:19 Reply: Support custom indexes, and indexes on virtual tables (artifact: a6b5ec976d user: cladisch)

The interface between the VDBE code and the built-in index implementations assumes that the indexes behave like B-tree indexes. So a custom index would require replacing all the code that accesses the table, and that is what virtual tables are for.

2020-11-04
08:07 Reply: Spanish characters in SQLITE (artifact: 9fba0c5ace user: cladisch)

If you need the string as a char*, then use sqlite3_column_text() to begin with.

2020-11-03
16:16 Reply: Spanish characters in SQLITE (artifact: c81fc5f7f5 user: cladisch)

SQLite does not change the contents of strings (unless you tell it to do so).

How are you typing and display characters? The Windows console window does not really support UTF-8.

2020-10-07
14:28 Reply: Documentation for where query optimizations & relation algebra occurs? (artifact: b91c020e81 user: cladisch)

The output of the EXPLAIN command gives you the opcodes.

These internals can (and do) change in any new SQLite version, so this is probably not very useful.

2020-09-28
13:02 Reply: RTree on ESP32 SQLite Version (artifact: 7097cd74b2 user: cladisch)

The R-tree extension uses incremental blob I/O functions. Please check that pragma compile_options; does not mention OMIT_INCRBLOB. If it does, you have to recompile the SQLite library without that setting.

06:34 Reply: RTree on ESP32 SQLite Version (artifact: 18b90a49ce user: cladisch)

No problem when I create the virtual table using rtree

How?

nor when I add line to this table

How?

What is the result of SELECT length(data) FROM AIRSPACERTREE_node WHERE nodeno = 1; after both steps?

2020-08-25
07:13 Reply: SQLite - ODBC - Concatenation (artifact: b31ee522f9 user: cladisch)

Works for me.

It might be useful to be more specific than "it fails".

07:05 Reply: Can not open SQLite-DB with full path (artifact: 418788941a user: cladisch)

The documentation says:

URI filename interpretation is enabled if the SQLITE_OPEN_URI flag is set in the third argument to sqlite3_open_v2(), or if it has been enabled globally using the SQLITE_CONFIG_URI option with the sqlite3_config() method or by the SQLITE_USE_URI compile-time option. URI filename interpretation is turned off by default

2020-08-12
11:54 Reply: history keeping (artifact: c514828e2e user: cladisch)

You have multiple rows/versions of the same record in a table, so it is not possible to use the autoincrementing rowid.

And you want to be able to do queries on old versions of a record, so the actual primary key will be something like (ID, timestamp). So the ID values themselves do not change.

2020-07-28
07:28 Reply: EntityFramework database is locked (artifact: c3a69b7711 user: cladisch)

Some other connection has an active transaction on the database.

To allow waiting for other transactions to finish, you should set the busy timeout on each connection. If this is not possible in the connection string, then the correct place is the connection's StateChange event:

Connection.StateChange += ConnectionStateChange;

void ConnectionStateChange(object sender, StateChangeEventArgs e)
{
    if (e.CurrentState == ConnectionState.Open)
         db.ExecuteStoreCommand("PRAGMA busy_timeout = 12345");
}

But if that other code has a bug an never ends its transaction, then no amount of waiting will help.

2020-07-14
21:07 Reply: When using sqlite3_step(stmt) inside a transaction my busy-handler is not being called (artifact: da1f3cfda7 user: cladisch)

my condition is a simple stand-alone application that does not share the database with other applications

What SQLite cares about is that there are multiple connections.

Is there a way to configure sqlite to always call my busy-handler callback?

And what would your busy handler do? In case of a deadlock, waiting would not help.

I found that when using IMMEDIATE transaction my callback is being called and all seems to work, but I am not sure on how solid this solution is

A deadlock happens when two read-only transactions want to upgrade to read/write. BEGIN IMMEDIATE starts the transaction as read/write.

2020-06-18
13:10 Reply: How to speed up this query? (artifact: 6c8ae1b15f user: cladisch)

If I had to guess, I'd suspect that the parameter/value comparison cannot be done with the index because the columns in the meta table use a different collation.

2020-06-12
13:47 Reply: closure table, prevent identical childrens names per parent (artifact: 4bb0c2b2d7 user: cladisch)
CREATE TRIGGER no_duplicate_childname_per_parent
BEFORE INSERT ON asset_node
BEGIN
    SELECT RAISE(FAIL, 'A parent cannot have two children with the same name')
    FROM asset_node AS sibling_node
    JOIN asset_tree AS sibling_tree ON sibling_node.node_id = sibling_tree.id
    JOIN asset_tree AS parent_tree ON sibling_tree.parent_id = parent_tree.id
    JOIN asset_tree AS new_tree ON new_tree.parent_id = parent_tree.id
    WHERE new_tree.id = NEW.node_id
      AND sibling_node.node = NEW.node;
END;
2020-06-09
12:39 Reply: union vs left join for select (artifact: ff5c3a49e1 user: cladisch)

UNION actually removes duplicates.

Can you show with some example data what you (not) want?

12:36 Reply: Constraint Name (artifact: e1a6a29e2a user: cladisch)

For CHECK constraints, SQLite shows the name in error messages. For other constraints, the SQL standard allows them.

I guess treating non-constraint column properties as constraints makes the SQLite grammar simpler, although it is not allowed by the standard:

<column definition> ::=
		<column name> [ <data type> | <domain name> ] [ <reference scope check> ]
		[ <default clause> | <identity column specification> | <generation clause> ]
		[ <column constraint definition>... ] [ <collate clause> ]

12:16 Reply: DB operation inside commit hook / enforce explicit INTEGER PRIMARY KEY column (artifact: 17c0c65b13 user: cladisch)

Neither the authorizer hook nor the commit hook are allowed to execute the SELECT. And sqlite3_table_column_metadata() does not work if you do not know the column name.

As far as I can see, you cannot prevent the CREATE TABLE statement immediately, and have to do the check later, probably when you're trying to use the table.

11:32 Reply: union vs left join for select (artifact: 4bbed0bc6f user: cladisch)

These queries are different, so you should use the one that returns the results you want.

What is the actual problem you're trying to solve?

2020-06-04
06:59 Reply: SELECT * Column Sequence / Order (not ORDER BY) (artifact: 8a7f276c0b user: cladisch)

Exactly the same text is still in SQL:2003 and SQL:2011.

2020-06-03
12:51 Reply: SELECT * Column Sequence / Order (not ORDER BY) (artifact: 22c06f45ce user: cladisch)

The SQL standard dictates nothing to that effect

Actually, the SQL-92 specification says that

the <select list> "*" is equivalent to a <value expression> sequence in which each <value expression> is a <column reference> that references a column of T and each column of T is referenced exactly once. The columns are referenced in the ascending sequence of their ordinal position within T.

The problem with * is not that the database might reorder the columns, but that humans will tell the database to alter them in the table.

2020-05-30
11:59 Reply: Database Schema Changed Error (artifact: 696e08e297 user: cladisch)

This is not an error but a warning. The first execution of the statement will fail (this is why it says "error"), but SQLite will then automatically re-prepare the statement with the new schema and re-execute it.

2020-05-23
15:04 Reply: how to save results of (artifact: 39ef9bed24 user: cladisch)

The easiest way to use sqlite3_exec() is to replace it with sqlite3_prepare_v2()/sqlite3_step()/sqlite3_column_*()/sqlite3_finalize() calls so that you can read the data in the same place where you actually need to handle it:

sqlite3_stmt *stmt;
const char *sql = "SELECT FrmNo,CapTime,CamTime from radar_1 WHERE FrmNo = ?";
int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
    print("error: ", sqlite3_errmsg(db));
    return;
}
sqlite3_bind_int(stmt, 1, 1);
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int frm_no           = sqlite3_column_int (stmt, 0);
    const char *cap_time = sqlite3_column_text(stmt, 1);
    // ...
}
if (rc != SQLITE_DONE) {
    print("error: ", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);

2020-05-16
07:26 Reply: Join columns, separate items with with \n, and export as geojson? (artifact: aac6dc1ab8 user: cladisch)

Join some columns so that I only get a single column through SELECT, where each original item should have a \n added so they're still separate

This is what the group_concat function does, as long as you convert the columns to rows.

To create JSON-encoded text, use the JSON functions:

SELECT json_object(
    'geometry', json_object(
        'coordinates', json_array(longitude, latitude),
        'type', 'Point'),
    'properties', json_object(
        'description', (SELECT group_concat(p, char(10))
                        FROM (SELECT 'email=' || email AS p
                              UNION ALL
                              SELECT 'www='   || www
                              UNION ALL
                              SELECT 'phone=' || phone)),
        'name', name),
    'type', 'Feature')
FROM camping;
2020-05-10
07:22 Reply: When will/were recent "sqlite3 new security issues CVEs" be addressed? (artifact: 7864f011c0 user: cladisch)

The worst outcome is denial-of-service.

Not necessarily.

A use-after-free bug could be exploited if the attacker can influence the program to allocate memory with interesting data at the same address as the just-freed data. An uninitialized-pointer bug could be exploited if the attacker can influence the value of the pointer, or if he can predict the value of the pointer and influence the program to place interesting data at that address.

This is strongly dependent on architecture, configuration, and other circumstances, usually requires other bugs, and takes much more effort than running SQLite through a fuzzer. But it cannot be ruled out completely.

2020-04-29
16:47 Edit reply: DATA RACE: Found in sqlite3.c (artifact: deb773506c user: cladisch)

My fuzzer finds that these 2 accesses can be executed concurrently

The fuzzer is wrong. Have a look at the first assert() in lockBtree(), which checks the that sqlite3BtreeEnter() did lock correctly.

they are protected by different locks

Do you have any evidence for this assertion? A BtShared object has a single Pager, which has a single Wal, which has a single file, which has a single unixShmNode.

07:09 Reply: DATA RACE: Found in sqlite3.c (artifact: 13543db99a user: cladisch)

My fuzzer finds that these 2 accesses can be executed concurrently

The fuzzer is wrong. Have a look at the first assert() in lockBtree().

they are protected by different locks

Do you have any evidence for this assertion?

2020-04-14
19:23 Reply: Return boolean if item EXISTS in database (artifact: fbb6969012 user: cladisch)

SQLite does not have a boolean type, and returns 0 or 1.

Compare against 0 or 1, or use if ($EntryExistsBoolean) {...}.

2020-04-01
12:10 Reply: How to add a column to a table using a query? (artifact: 13afbaba4b user: cladisch)

Works for me:

sqlite> create table characters([...]);
sqlite> ALTER TABLE CHARACTERS ADD BLOOD FLOAT DEFAULT 0.0;
sqlite> .schema
CREATE TABLE characters([...], BLOOD FLOAT DEFAULT 0.0);
I suspect that phpliteadmin is not able to access the file, or that the database is locked by another program.

As for the error message, you have to complain to the phpliteadmin authors.

2020-03-30
20:03 Reply: How can I maintain a list in an arbitrary order? (artifact: 294711bbde user: cladisch)

Indeed:

WITH RECURSIVE ordered AS (
  SELECT *
  FROM list
  WHERE prev = 0

  UNION ALL

  SELECT list.*
  FROM list
  JOIN ordered ON list.prev = ordered.row_id
)
SELECT * FROM ordered;

This should have an index on prev.

2020-03-29
08:27 Reply: Colons in column names? (artifact: cb610cf4af user: cladisch)
The SQL-92 standard says:

   6.4  <column reference>

   <column reference> ::= [ <qualifier> <period> ] <column name>

   7.9  <query specification>

   <query specification> ::=
        SELECT [ <set quantifier> ] <select list> <table expression>

   <select list> ::=
          <asterisk>
        | <select sublist> [ { <comma> <select sublist> }... ]

   <select sublist> ::=
          <derived column>
        | <qualifier> <period> <asterisk>

   <derived column> ::= <value expression> [ <as clause> ]

   <as clause> ::= [ AS ] <column name>

   Syntax Rules

   9) Case:

      a) If the i-th <derived column> in the <select list> specifies
        an <as clause> that contains a <column name> C, then the
        <column name> of the i-th column of the result is C.

      b) If the i-th <derived column> in the <select list> does not
        specify an <as clause> and the <value expression> of that
        <derived column> is a single <column reference>, then the
        <column name> of the i-th column of the result is C.

      c) Otherwise, the <column name> of the i-th column of the <query
        specification> is implementation-dependent and different
        from the <column name> of any column, other than itself, of
        a table referenced by any <table reference> contained in the
        SQL-statement.


So when you write `SELECT tab.col FROM ...`, the output column name is
guaranteed even without an AS. But with the subquery, SQLite is forced
to invent a new name.
07:53 Reply: Natural sort order (artifact: cbad577d80 user: cladisch)

It is definitely more "natural" to ignore leading zeros.

https://natsort.readthedocs.io/en/master/howitworks.html talks about the design decisions made in the Python module. While I doubt that you'd want to support all those features, at least ignoring case would be useful.

2020-03-26
10:55 Reply: Query to make timestamp to readable date (artifact: 831e473be2 user: cladisch)

The documentation actually says that

if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.

There is no restriction that the primary key constraint must be a column constraint.

More ↓