Tag: sqlite3

How can one see the structure of a table in SQLite?

Invoke the sqlite3 utility on the database file, and use its special dot commands:
  • .tables will list tables
  • .schema [tablename] will show the CREATE statement(s) for a table or tables

There are many other useful built-in dot commands — see the documentation at http://www.sqlite.org/sqlite.html, section Special commands to sqlite3.

Example:

sqlite> entropy:~/Library/Mail>sqlite3 Envelope\ Index
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
addresses              ews_folders            subjects
alarms                 feeds                  threads
associations           mailboxes              todo_notes
attachments            messages               todos
calendars              properties             todos_deleted_log
events                 recipients             todos_server_snapshot
sqlite> .schema alarms
CREATE TABLE alarms (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, alarm_id,
                     todo INTEGER, flags INTEGER, offset_days INTEGER,
                     reminder_date INTEGER, time INTEGER, argument,
                     unrecognized_data BLOB);
CREATE INDEX alarm_id_index ON alarms(alarm_id);
CREATE INDEX alarm_todo_index ON alarms(todo);

Note also that SQLite saves the schema and all information about tables in the database itself, in a magic table named sqlite_master, and it’s also possible to execute normal SQL queries against that table. For example, the documentation link above shows how to derive the behavior of the .schemaand .tables commands, using normal SQL commands (see section: Querying the database schema).

How to retrieve ALL data from an sqlite database?

A couple days ago I’ve been working with an sqlite3 database and noticed that after deleting a lot of data (an entire table (DROP) to be exact) the file size did not change (the table used 5MB) at all. Not the database I’m asking about, but maybe this is relevant to my question.

I’m having a bunch of databases that I was looking through using grep -a for a specific message, I successfully found the database in question having 50MB in size, seems perfect! However, opening the database in an sqlite3 database viewer (or the sqlite3 command line tool) shows only a little over 800 entries in the messages table. The current auto increment index is above 18.000, which is the amount of entries the table should have. Going through the .db file with grep, everything I want seems to be there, but I’m guessing it’s somehow “hidden”(?).

How can I retrieve ALL the data from an sqlite database? Note that .dump <table> does also not include the messages I’m looking for. For a quick explanation on this and why data doesn’t actually get deleted/filesize won’t shrink when I delete a 5MB table I’d be thankful too.

sqlite> .dbinfo
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 366
database page count: 11405
freelist page count: 10372
schema cookie:       2
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        7
application id:      0
software version:    3008005
number of tables:    3
number of indexes:   1
number of triggers:  0
number of views:     0
schema size:         737

(Note that I did not create nor ever edited this database, it’s Instagram’s direct messages database.)

Solution:

Deleted data is not immediately removed from the database file; see change sqlite file size after “DELETE FROM table”.

As shown by the freelist page count, that file is mostly empty, and running VACUUM is likely to throw away the remains of the data you’re searching for.

There is no easy way to recover deleted data (because some important management information is missing). If you are interested in only a small amount of data, try a hex editor.