[Bug]: Unable to open databases with encoding UTF-16le

[stefanofraccaro]

What did you do?

I opened the database with version 3.13.0-rc1 but an error appear:
executeSQL: "database table is locked (SELECT type,name,sql,tbl_name FROM main.sqlite_master;)"
"non posso ottenere la listra degli oggetti db: database table is locked"
The same database file works well with version 3.12.2 (integrity check results OK, foreign_key results OK)

What did you expect to see?

I expected to see tables, indexes and views. All groups are empty ( 0 ).

What did you see instead?

executeSQL: "database table is locked (SELECT type,name,sql,tbl_name FROM main.sqlite_master;)"
"non posso ottenere la listra degli oggetti db: database table is locked"

DB4S Version

3.13.0-rc1

What OS are you seeing the problem on?

Windows

OS version

Windows 10 22H2

Relevant log output

No response

Prevention against duplicate issues

• I have searched for similar issues

[stefanofraccaro]

db2.zip

[FriedrichFroebel]

Do you have the file open with different applications at the same time or do you see any other files with a similar name inside the directory? The message seems to indicate so.

[stefanofraccaro]

I tried on another computer with Windows 11 but the result is the same. I don't have any other file in folder. I don't have any other application running. This database (db2) has 1 table and 1 index. If you try to open it with 3.13.0-rc1 with the "Open Database" button, you don't have the alarm but tables and indexes are zero (0) instead if one (1).
If you open the database by double clicking on it, you see the alarm and have zero (as before) on table and indexes. The database is not empty.

[chrisjlocke]

I can reproduce this using your database file.
I've never seen this error before.

Trying to attach it to a known-working database also generates an error.

Works OK in the command-line sqlite3 though. No data - just one 'users' table, is that right?

[stefanofraccaro]

Yes, it is right. The database belongs to a commercial application that use SQLite. I removed all tables and indexes to be sure that issue was not related to database data or foreign checks. I added 1 table 'users' only for test with no data. I had never seen the text encoding error: maybe the root cause is a database setting. I'm able to read the database data from C# with system.data.sqlite library (like sqlite3 command-line)

[stefanofraccaro]

You can reproduce a similar error following these steps:

• open DB Browser 3.13.0-rc1
• create new database
• run sql " PRAGMA encoding='UTF-16le'; "
• create a table
• close DB Browser
• double click the database just created
  

The problem seems to be the schema saved in UTF16. Please note that I can create database in UTF16 (le/be) with DB Browser but I can't read it if I close/reopen the file. If the database is created with another tool, I can open UTF-16le databases with DB Browser 3.12.2 but not with 3.13.0-rc1

[ducvietcao]

DB4S can open UTF-16le (and UTF16-be) just fine, but it fails to create a new database with a non-default encoding.

Reproduction

• Create database using sqlite3 CLI:
  • Run sqlite3 cli.db "PRAGMA encoding='UTF-16le'; CREATE TABLE t ( i INTEGER );"
• Create database using DB4s
  • New database: db4s.db
  • Close the table wizard
  • Execute SQL: PRAGMA encoding='UTF-16le'; CREATE TABLE t ( i INTEGER );
  • Close database
• Both sqlite3 and DB4S can open cli.db (created by sqlite3)
• Neither sqlite3 nor DB4S can open db4s.db (created by DB4S). sqlite3 says error: malformed database schema (t)

If you compare the contents of the two databases (using a hex editor) and lookup the byte offsets in the SQLite Docs, you will see that they are almost equal, except for these headers fields

	cli.db	db4s.db
The database text encoding.	2 (utf-16le)	1 (utf-8)
File change counter.	1	3
The schema cookie.	1	3
The version-valid-for number.	1	3

Both files contain UTF-16-encoded text, but the broken one claims that it is UTF-8. The increased file change counter indicates that DB4S has performed some extra operations on the file.

Cause

In DB4S, the DBBrowserDB::create() function does not simply create a fresh database, but instead

1. creates a new database
2. creates a new table (CREATE TABLE notempty (id integer primary key);)
3. drops that table (DROP TABLE notempty;)
4. closes the database (so the file is actually written)
5. open the database file again

BUT: Once you have created something in the SQLite database, you cannot change its encoding anymore.

Fix

Remove the hidden SQL operations from the creation function:

diff --git a/src/sqlitedb.cpp b/src/sqlitedb.cpp
index 37f1e6fe..6d297cc2 100644
--- a/src/sqlitedb.cpp
+++ b/src/sqlitedb.cpp
@@ -678,9 +677,0 @@ bool DBBrowserDB::create ( const QString & db)
-        // force sqlite3 do write proper file header
-        // if we don't create and drop the table we might end up
-        // with a 0 byte file, if the user cancels the create table dialog
-        {
-            NoStructureUpdateChecks nup(*this);
-            executeSQL("CREATE TABLE notempty (id integer primary key);", false, false);
-            executeSQL("DROP TABLE notempty;", false, false);
-        }
-

After that, DB4S will create exactly the same database as the sqlite3 command, allowing you to change the encoding, if you close the table creation dialog.

Obviously, this would re-introduce the issue of 0-byte files being created.