README.md

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)