Wrong size calculation

[mstuefer]

SQLite uses a general dynamic type system, instead of a static, rigid typing as the most SQL databases. This means that the datatype of a value is associated with the value itself, not with its container.

For example:

-- First we create a table like the following 
CREATE TABLE myTestTable (id int, text varchar(5));

-- Then we insert regular stuff
INSERT INTO myTestTable VALUES (1, 'abcde');

-- Now we insert a string which is longer than the specified varchar(5), works in sqlite
INSERT INTO myTestTable VALUES (2, 'abcdeFGHIJ');

-- We are even able to insert a string instead of an int for the id column
INSERT INTO myTestTable VALUES ('text?', 123);

-- And some NULLs
INSERT INTO myTestTable VALUES (NULL, NULL);

If now check the length of both fields, (where the length shows the total number of characters in case of a string, the number of bytes in case of a blob, the length of a string representation of the value in case of int), as well as the type and the respective value we can see that not every id-field is still an integer.
For example row number three contains text and is therefore of type text, while the nulls are of type null. Furthermore we can see that the varchar(5) in row 2 has a length of 10 etc.

SELECT length(id), length(text), typeof(id), typeof(text), id, text FROM myTestTable;

length(id)  length(text)  typeof(id)  typeof(text)  id          text
----------  ------------  ----------  ------------  ----------  ----------
1           5             integer     text          1           abcde
1           10            integer     text          2           abcdeFGHIJ
5           3             text        text          text?       123
                          null        null

Sqlite has 5 storage classes (NULL, INTEGER, REAL, TEXT, BLOB) where the integer can be stored in 1,2,3,4,6 or 8 bytes, the real is a floating point value stored in 8 bytes, text is either in UTF-8, UTF-16BE or UTF-16LE and the blob is stored exactly as it is.

To get the text encoding you can invoke the following:

pragma encoding;

Looks like there is no easy way to get the size of each table, like invoking a dynamic management view as we can do in other databases, or a useful pragma statement. Maybe the SQLite C Interface (sqlite3_column_bytes() etc.) could be a better choice to get the size in bytes of every column per row which you could sum to get the size per table or so.. you could try to investigate in that direction.

Some useful links:
https://www.sqlite.org/datatype3.html
https://www.sqlite.org/c3ref/column_blob.html