Created
November 24, 2021 00:42
-
-
Save shurane/0f0dfaf53f1d3a3e8c8ce9809f16e83e to your computer and use it in GitHub Desktop.
SQLite Book Page Assignments
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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" | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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