Skip to content

Latest commit

 

History

History
77 lines (57 loc) · 1.04 KB

SETUP.md

File metadata and controls

77 lines (57 loc) · 1.04 KB

Setup

SQLite

To experiment with SQLite,

docker run -it --rm -v "$(pwd)":/data keinos/sqlite3 sqlite3 /data/test-database.db

Basic commands

To list all the databases

.databases

To list all the table

.tables

To create a table

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

DB design

CREATE TABLE undo_stack (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL
);
CREATE TABLE redo_stack (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL
);

Push operation

INSERT INTO undo_stack (path) VALUES ('directory path');

Pop operation

BEGIN TRANSACTION;

-- Retrieve the item with the highest id
WITH item_to_pop AS (
    SELECT id, value
    FROM stack
    ORDER BY id DESC
    LIMIT 1
)
-- Select the value of the item to pop (before deleting it)
SELECT value FROM item_to_pop;

-- Delete the item
DELETE FROM stack
WHERE id = (SELECT id FROM item_to_pop);

COMMIT;