> .table  # Show all tables
> .schema # Show table structure
> .read   # Read myscript.sql
> .output  /tmp/output.x    # Redir stdout to file
> .output                   # Switch back to stdout

/Users/aaa/myfile/bitbucket/database/epochtime.sql

-- Save file to /tmp/test.sql
-- source db.sqlite
-- sqlite3 trydb.db < /tmp/test.sql
-- epoachtime

CREATE TABLE tb1 (
entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
create_date TEXT DEFAULT (datetime()),
edit_date TEXT,
addedtime DATETIME DEFAULT CURRENT_TIMESTAMP,
epochtime DATETIME DEFAULT (strftime('%s', 'now')),
entry_text TEXT NOT NULL
);
insert into tb1 (edit_date, entry_text) VALUES('333', 'entry_text')

-- Sun  5 Mar 23:35:34 2023 
-- Sqlite3
-- /Users/aaa/myfile/bitbucket/database/sqlite3/test_table1.sql
--
DROP TABLE test_table1;
CREATE TABLE test_table1(
id INTEGER PRIMARY KEY AUTOINCREMENT, 
name TEXT, 
address TEXT, 
age INTEGER, 
addedtime DATETIME DEFAULT (strftime('%s', 'now')), 
score INTEGER DEFAULT 0
);

INSERT INTO test_table1(name, address, age) VALUES('David', '100 King Street', 18);
INSERT INTO test_table1(name, address, age) VALUES('King', '200 Queen Street', 28);
INSERT INTO test_table1(name, address, age) VALUES('Queen', NULL, 38);
INSERT INTO test_table1(name, address, age) VALUES('Jack', '300 Jack Street', NULL);

-- SELECT * FROM test_table1;
SELECT name, CASE WHEN address IS NULL THEN 'Fake address' ELSE address END, CASE WHEN age IS NULL THEN 100 ELSE age END FROM test_table1;

CREATE TABLE CodeBlock (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
header TEXT, 
codeblock TEXT, 
addedtime DATETIME DEFAULT (stftime('%s', 'now')), 
score INTEGER DEFAULT 0
);


-- 1  |   a:*.sql:c\nine1 line2|a:*.sql:c\nline 22  |   1720133679  |  0
                                                            |
                                                            v
                                                       strftime('%s', 'now')

-- The following DOES NOT WORK 
-- addedtime DATETIME DEFAULT stftime('%s', 'now'),
-- Need brackets  (..)

-- $b/database/epochtime.sql
sqlite3 trdb.db < epochtime.sql
sqlite3 trdb.db
select * from tb1;        

-- We have two tables with different columns: CodeBlock and oldtable
-- and we want to copy data from CodeBlock_copy to CodeBlock
-- CodeBlock table
CREATE TABLE CodeBlock (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
header TEXT, 
codeblock TEXT, 
addedtime DATETIME DEFAULT (strftime('%s', 'now')), 
score INTEGER DEFAULT 0
);

CREATE TABLE CodeBlock_copy (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
header TEXT, 
codeblock TEXT
);

INSERT INTO CodeBlock (header, codeblock) SELECT header, codeblock FROM CodeBlock_copy

        -- id is variable 
let pidInt = fromIntegral pid
execute conn Query{fromQuery = s2Text "DELETE FROM CodeBlock WHERE id = ? "} (Only (pidInt::Int64))        


deleteURLHas::String -> String -> IO () 
deleteURLHas s dbFile = do 
                conn <- open dbFile
                let sql_delete = Query {fromQuery = toSText qStr} 
                execute conn sql_delete ()
                -- execute_ conn sql_delete 
                return ()
        where
          -- qStr = "DELETE FROM urls WHERE id IN (SELECT id FROM urls U WHERE U.id IN (SELECT urlId FROM items X WHERE title LIKE '%Vansky%'));"
          s1 = "DELETE FROM urls WHERE id IN (SELECT id FROM urls U WHERE U.id IN (SELECT urlId FROM items X WHERE title LIKE '%"
          s2 = "%'));"
          qStr = s1 ++ s ++ s2

Monday, 06 March 2023 12:47 PST
Current Sqlite3 Version is 3.32.3 which does not support foreign key contains.
Sqlite3 3.6.19 supports foreign key contains ? I can not intall on my macOS.

      DROP TABLE post;
create table post(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    description TEXT
);

INSERT INTO post (description) values('Awesome Iphone');
INSERT INTO post (description) values('Awesome Samsung');

DROP TABLE image;
create table image(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT,
    post_id INTEGER REFERENCES post(id)
);

INSERT INTO image (path, post_id) values('/tmp/img1.png', 1);
INSERT INTO image (path, post_id) values('/tmp/img2.png', 10);
INSERT INTO image (path, post_id) values('/tmp/img3.png', 10);  

CREATE TABLE urls(
    id INTEGER PRIMARY KEY,
    url TEXT NOT NULL
  );

CREATE TABLE items(
    id INTEGER PRIMARY KEY,
    title TEXT,
    urlId INTEGER REFERENCES urls(id)
                  ON DELETE SET NULL
  );

-- INNER JOIN

SELECT U.id, U.url, I.title FROM urls U INNER JOIN
items I ON I.urlId = U.id WHERE I.title LIKE '%Google%';
                 ↑                        ↑
                 |                        |
                 |                        + → WHERE cause for the new intersected table 
                 |
                 + → INNER JOIN condition 
                 + → Intersection of two tables  with condition I.urlId = U.id

SELECT id, title from moz_bookmarks where title IS NULL;

DROP TABLE test_student;
CREATE TABLE test_student(
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT NOT NULL,
  age  INTEGER NOT NULL
);

INSERT INTO test_student(name, age) VALUES('David',    20);
INSERT INTO test_student(name, age) VALUES('Mike',     30);
INSERT INTO test_student(name, age) VALUES('Smith',    20);
INSERT INTO test_student(name, age) VALUES('John',     40);
INSERT INTO test_student(name, age) VALUES('William', 20);

SELECT count(id) FROM test_student GROUP BY age