CODEWITHSUNDEEP
sqlsqlite
Keng
Keng

Reputation: 53101

How can I find out how much physical space a record (BLOB) in a sqlite database uses?

I'm using Ditto as my clipboard history manager and the database is now at about 5.5 gig. that's probably too big ;o) After deleting a bunch of screenshots and doing a compact and repair on the database, it is still too big.

I'm looking at the table definition below for the main data table.

CREATE TABLE Data(lID INTEGER PRIMARY KEY AUTOINCREMENT, lParentID INTEGER, strClipBoardFormat TEXT, ooData BLOB)

is there a way in sqlite (version 3.7.13) to find out how much space a record is using so I can delete it/them?

Upvotes: 2

Views: 309

Answers (1)

Tobias Roland
Tobias Roland

Reputation: 1223

length(ooData) should do the trick.

From the documentation:

length(X): For a string value X, the length(X) function returns the number of characters (not bytes) in X prior to the first NUL character. Since SQLite strings do not normally contain NUL characters, the length(X) function will usually return the total number of characters in the string X. For a blob value X, length(X) returns the number of bytes in the blob. If X is NULL then length(X) is NULL. If X is numeric then length(X) returns the length of a string representation of X.

So your query would look something like this, I believe:

SELECT id_field, length(blobData) AS size, blobdata FROM your_table 
WHERE blobData IS NOT NULL

Since you're only really interested in the "biggest" files in the database, you'll probably want to only choose the biggest of the files, so you'll want to add a constraint like this

AND size > 1000000;

Upvotes: 3

Related Questions