why my sqlite files are different?

(1) By anonymous on 2022-07-27 11:17:54 [link] [source]

Hi, I'm new to SQLite3 so probably i made some mistakes.

Instead of having my files like here ( https://www.sqlite.org/fileformat2.html ) described, they are plain text file with all data in clear like this:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test ( id INTEGER PRIMARY KEY, Nome TEXT);
INSERT INTO test VALUES(1,'Flavio');
INSERT INTO test VALUES(2,'Pippo');
COMMIT;



Anyone can help me, please?

(2) By Ryan Smith (cuz) on 2022-07-27 12:54:14 in reply to 1 [source]

What you show is an SQL file, as in a file full of SQL statements, not a "SQLite Database File", which would be a binary format file.

The SQL file you show is sometimes called an "SQL script" and it can be processed by a DB engine that understands that dialect of SQL - and it looks like the dialect is based on the type of SQL (dialect) that specifically SQLite understands.

If the SQLite engine (using an interface such as the sqlite3 command-line-interface) was to read that file it would understand it and create an SQLite database into which it would make the "test" table and store the data as instructed by your SQL statements. This database it creates will also be in the form of a file, but will have a binary format (as with the link you posted) that would look quite weird in a text viewer. It may or may not make a couple of other files alongside to use as journals and the like.

This must be quite new for you so I tried to explain as plainly as possible, but there remains a lot to be said.

Perhaps search the web for "SQLite Tutorial" or "using sqlite3" for more detail.

(3) By anonymous on 2022-07-27 13:14:32 in reply to 2 [link] [source]

Tanks for the reply.

Maybe I didn't explained well.

I obtained that file "testDB.sqlite" with this sequence:

$ sqlite3 testDB.sqlite
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE test ( id INTEGER PRIMARY KEY, Nome TEXT);
sqlite> INSERT INTO test VALUES (NULL, 'Flavio');
sqlite> INSERT INTO test VALUES (NULL, 'Pippo');
sqlite> .tables
test
sqlite> .quit

And the generated file is "not a "SQLite Database File", which would be a binary format file"...

and that's why I'm asking for help :-)

Why my SQLite creates "wrong type" files?



PS: I'm on a Raspberry. I installed using:
sudo apt update
sudo apt install sqlite3

(4) By Stephan Beal (stephan) on 2022-07-27 13:22:07 in reply to 3 [link] [source]

And the generated file is "not a "SQLite Database File", which would be a binary format file"...

Then you're looking at the wrong file. The only possible result of the output you pasted in is a binary database file named testDB.sqlite in your current directory (note that filenames on the Pi are case-sensitive, so make sure you're not looking at a different file). After exiting sqlite3, try:

$ ls -lat | head

and that top-most file in that list should be testDB.sqlite. That file will be a binary database file.

(5) By Stephan Beal (stephan) on 2022-07-27 13:24:00 in reply to 2 [link] [source]

This database it creates will also be in the form of a file, but will have a binary format (as with the link you posted) that would look quite weird in a text viewer.

To add to that because we had a relevant post on that topic recently: if you ever end up opening an sqlite3 database in a text editor do not save it. Saving it in a text editor will irrevocably corrupt it.

(6) By Ryan Smith (cuz) on 2022-07-27 13:45:32 in reply to 3 [link] [source]

Why my SQLite creates "wrong type" files?

That is physically impossible. A bug is possible, a mistake is possible, but to magically gain functionality that never existed before and works correctly is, well, impossible.

I think Stephen's explanation is most likely, that you are simply looking at the wrong file. Another possibility is that you are using a binary cli (sqlite3) made some joker or virus merchant.

Another way to troubleshoot this is to create an empty folder (with needed access), navigate to it, then repeat the steps, or use this minimal set:

$ sqlite3 a.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE t(id);
sqlite> .quit
$ cat a.db

Done correctly, that should output a bunch of binary data, starting with a sequence like "SQLite Format 3(NUL)..." etc.

If not, please paste the entire session here.

(7) By anonymous on 2022-07-27 13:53:45 in reply to 3 [link] [source]

RESOLVED!!

Thanks to all for your patience!

I'm a stupid... sometimes happens.

I use Midnight Commander because command line ... sucks.

And I've NOW discovered that the built in viewer (F3 key) magically shows sqlite file content "decripted" in plain text ... driving me crazy.

If I try to edit (without saving) the file the binary content is there.

Sorry,
Thank you all again.

(8) By Flavio V. Italy (flavio.ventura) on 2022-07-27 15:24:37 in reply to 7 [link] [source]

(I "was" anonymous, now I'm registered 😁)

The keyword was "impossible".

If (it's impossible) than
    the problem is elsewhere;

and with a simple "cat" the magic was shown.

Thanks you all!