Skip to content

Instantly share code, notes, and snippets.

@shurane
Created November 24, 2021 00:42
Show Gist options
  • Save shurane/0f0dfaf53f1d3a3e8c8ce9809f16e83e to your computer and use it in GitHub Desktop.
Save shurane/0f0dfaf53f1d3a3e8c8ce9809f16e83e to your computer and use it in GitHub Desktop.
SQLite Book Page Assignments
{
"name": "2021.11.23.sqlite.book.page.assignments",
"version": "0.0.1",
"description": "",
"main": "index.js",
"scripts": { "test": "echo \"Error: no test specified\" && exit 1" },
"author": "",
"license": "ISC",
"dependencies": {
"better-sqlite3": "^7.4.5"
}
}
// https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/api.md
const db = require('better-sqlite3')(':memory:'); //, { verbose: console.log });
// https://sqlite.org/lang_createtable.html
db.prepare(`CREATE TABLE books(
id INTEGER PRIMARY KEY ASC NOT NULL,
isbn TEXT NOT NULL,
title TEXT NOT NULL,
author TEXT NOT NULL,
year_published INT
)`).run();
db.prepare(`CREATE TABLE teachers(
id INTEGER PRIMARY KEY ASC NOT NULL,
name TEXT NOT NULL
)`).run();
// https://www.sqlite.org/foreignkeys.html
db.prepare(`CREATE TABLE contents(
id INTEGER PRIMARY KEY ASC NOT NULL,
book_id INTEGER,
teacher_id INTEGER,
start INTEGER NOT NULL,
end INTEGER NOT NULL,
FOREIGN KEY(book_id) REFERENCES books(id),
FOREIGN KEY(teacher_id) REFERENCES teachers(id)
)`).run();
const insertBook = db.prepare(`
INSERT INTO books (isbn, title, author, year_published)
VALUES (@isbn, @title, @author, @year_published)`);
const insertManyBooks = db.transaction((books) => {
for (const book of books) {
insertBook.run(book);
}
});
const getBooks = db.prepare(`SELECT * FROM books`);
const insertTeacher = db.prepare(`INSERT INTO teachers (name) VALUES (?)`);
const insertContent = db.prepare(`INSERT INTO contents (book_id, start, end, teacher_id) VALUES (?, ?, ?, ?)`);
const getContents = db.prepare(`
SELECT title, start, end, T.name AS teacher_name
FROM contents C
INNER JOIN books B on C.book_id = B.id
LEFT JOIN teachers T on C.teacher_id = T.id`);
// https://www.goodreads.com/list/show/1.Best_Books_Ever
const books = [
{ title: "The Hunger Games", author: "Suzanne Collins", year_published: 2008, isbn: "978-0439023481" },
{ title: "To Kill a Mockingbird", author: "Harper Lee", year_published: 1960, isbn: "978-0446310789" },
{ title: "Pride and Prejudice", author: "Jane Austen", year_published: 1813, isbn: "978-1593083243" },
{ title: "Animal Farm", author: "George Orwell", year_published: 1945, isbn: "978-0451526342" },
{ title: "The Fault in Our Stars", author: "John Green", year_published: 2012, isbn: "978-0142424179" },
{ title: "The Giving Tree", author: "Shel Silverstein", year_published: 1964, isbn: "978-0060284510" },
{ title: "Les Misérables", author: "Victor Hugo", year_published: 1862, isbn: "978-0449300022" },
];
insertManyBooks(books);
insertTeacher.run("John Keating");
insertTeacher.run("Dewey Finn");
insertTeacher.run("Albus Dumbledore");
insertTeacher.run("Jim McAllister");
insertTeacher.run("Professor Xavier");
insertContent.run(1, 1, 20, null);
insertContent.run(1, 21, 40, null);
insertContent.run(1, 41, 60, null);
insertContent.run(1, 61, 80, null);
insertContent.run(1, 81, 100, null);
insertContent.run(2, 1, 25, null);
insertContent.run(2, 26, 50, null);
insertContent.run(2, 51, 75, null);
insertContent.run(2, 76, 100, null);
insertContent.run(1, 1, 60, 1);
insertContent.run(1, 61, 120, 1);
insertContent.run(2, 1, 50, 2);
insertContent.run(2, 51, 100, 2);
console.log(getBooks.all());
console.log(getContents.all());
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment