sqlite3: I won’t begin to try and explain

I have sqlite3 on my list of applications, and I know you can access sqlite database files with it, in console mode.

2014-05-07-6m47421-sqlite3

But I’ll be dead honest: I haven’t a clue what to do next. The .help command ticks off a huge list of commands, and I am hopelessly lost as to what any one of them — except perhaps for .exit — does. 😯

So I mention this only out of a sense of obligation, but I’m just crossing it off my list with this post.

And because I know if I don’t mention it, somewhere down the line, I’ll get that e-mail that starts off with, “Hey, what about sqlite3? Why didn’t you didn’t mention that one?! I use that every day in my job as. …” 🙄

So there it is, for that unnamed person in the future. Enjoy. 😀

4 thoughts on “sqlite3: I won’t begin to try and explain”

  1. Silvio

    you can create and examine sqlite3-based databases. If you use firefox you have several sqlite databases on your filesystem. try “find ~/.mozilla -iname “#.sqlite”.

    Example open the formhistory.sqlite of my firefox: “sqlite3 .mozilla/firefox/y1zixjcr.default/formhistory.sqlite”
    and show the tables with “.tables”
    moz_deleted_formhistory moz_formhistory

    Now you can examine the tables with the “.show” command

    sqlite> .schema moz_formhistory
    CREATE TABLE moz_formhistory (id INTEGER PRIMARY KEY, fieldname TEXT NOT NULL, value TEXT NOT NULL, timesUsed INTEGER, firstUsed INTEGER, lastUsed INTEGER, guid TEXT);
    CREATE INDEX moz_formhistory_index ON moz_formhistory(fieldname);
    CREATE INDEX moz_formhistory_lastused_index ON moz_formhistory(lastUsed);
    CREATE INDEX moz_formhistory_guid_index ON moz_formhistory(guid);

    With this you can sql-select statements to view the content… (you need the “;” at end of the line)

    select * from moz_formhistory;
    […]
    3577|q|xcomp|1|1399461969786000|1399461969786000|vV3sPNKNSY+XXXX
    3578|q|xcompr|1|1399461977330000|1399461977330000|9KSzIWq1RxeXXXX
    3579|q|xcompmgr|1|1399461984748000|1399461984748000|iTwCd7DUT8asXXXX
    3580|q|com manager|1|1399461996446000|1399461996446000|WHIEPMjvTdeXXXX
    […]

  2. GDR!

    If you have an sqlite file produced by some other program, you can start sqlite:

    sqlite3 programsdatabase.sqlite3

    View the tables in that file:

    .tables

    Show data from the table:

    SELECT * FROM tablename;

    It’s useful if you want to tweak some settings, if you use a program which uses SQLite database at the backend.

    I used a lot of the word “use” 🙂

  3. Pingback: Links 9/5/2014: LXQt Introduced, New Debian Release | Techrights

  4. Pingback: Bonus: S is for squashed | Inconsolation

Comments are closed.