Skip to content

Instantly share code, notes, and snippets.

@trauber
Created February 3, 2020 21:20
Embed
What would you like to do?

Notes for Sqlite Blog

  • Create a sqlite timestring with date:
date +'%Y-%m-%d %H:%M'
  • Read text file into a blob:
sqlite> CREATE TABLE files(name TEXT, content TEXT);
sqlite> INSERT INTO files(name,content) VALUES('a.txt',readfile('a.txt'));
  • This will prevent duplicate file names:
sqlite> create table files(name TEXT unique, content TEXT);
  • replacing a text blog using a WHERE statement:
sqlite> update files set content=readfile("a.txt") where name ="b.txt"
  • sort by descending timestamp
sqlite> SELECT * FROM Table ORDER BY dateColumn DESC;
  • reindex a table; for use after deleting rows
DELETE FROM 'table_name' WHERE col='value';
REINDEX 'table_name';
  • to add timestamp to row automatically via schema:
CREATE TABLE users(
    id INTEGER PRIMARY KEY,
    username TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Questions:

  • How many tables will I need? Just one?

    Maybe just one: (timestamp, layout, name, title, content)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment