dbinfo usage

(1) By Trudge on 2022-06-05 16:18:46 [link] [source]

I've read about '.dbinfo' CL but for some reason I get an error when using it.

sqlite kb.db
-- Loading resources from /Users/trudge/.sqliterc
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE authors(id integer primary key autoincrement,author text,authorid integer);
CREATE TABLE keywords(id integer primary key autoincrement,keyword text,authorid integer,titleid integer);
CREATE TABLE IF NOT EXISTS "books" (
	`id`	integer,
	`titleid`	integer,
	`authorid`	integer,
	`title`	text,
	`pubyear`	date,
	`filename`	text,
	`notes`	text, Birthdate text,
	PRIMARY KEY(`id` AUTOINCREMENT)
);
CREATE INDEX `all_titles` ON `books` (
	`title`
);
sqlite> select count(*) from books;
count(*)
--------
356     
sqlite> .dbinfo
error: no such table: sqlite_dbpage

I'm running a Mac M1 with Monterey 12.4. Any help is appreciated.

(2) By Larry Brasfield (larrybr) on 2022-06-05 16:39:11 in reply to 1 [source]

This appears to be a deficiency in the conditional compilation guards involving SQLITE_OMIT_VIRTUALTABLE and SQLITE_ENABLE_DBPAGE_VTAB. Unless the first is not defined and the second defined, the .dbinfo command cannot work. Yet that command is compiled regardless of those preprocessor variables.

This will be fixed soon. Until then, either do not use .dbinfo or build with virtual table support. (I presume the shell build has SQLITE_ENABLE_DBPAGE_VTAB defined.)

(3) By Trudge on 2022-06-05 17:01:57 in reply to 2 [link] [source]

Ah, I had not compiled with that particular option. After re-compiling I get:

sqlite kb.db
-- Loading resources from /Users/trudge/.sqliterc
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> .dbinfo
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 2048
database page count: 112
freelist page count: 28
schema cookie:       21
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3034001
number of tables:    4
number of indexes:   1
number of triggers:  0
number of views:     0
schema size:         473
data version         2

Thank you so much.

(4) By Simon Slavin (slavin) on 2022-06-05 17:19:37 in reply to 1 [link] [source]

I see your problem is being fixed, but I wondered whether you knew about

https://www.sqlite.org/sqlanalyze.html

which gives similar information.

(5) By Trudge on 2022-06-06 15:38:04 in reply to 4 [link] [source]

Thank you for the alternative. I have not used it but good to know. The information provided is quite detailed and will take some time to interpret correctly.