1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
import DatabaseConstructor, { type Database } from "better-sqlite3";
import EVENTS from "./data/events.json" with { type: "json" };
import USERS from "./data/users.json" with { type: "json" };
import RSVPS from "./data/rsvps.json" with { type: "json" };
const db: Database = new DatabaseConstructor("src/sqlite.db", {
verbose: console.log,
});
console.log(`Initializing database: ${db.name} `);
db.pragma("foreign_keys = ON");
db.exec(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
email TEXT
);
`);
db.exec(`
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
image_url TEXT,
date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
host_id INTEGER REFERENCES users NOT NULL
);
CREATE INDEX IF NOT EXISTS eventhosts ON events(host_id);
`);
const upsertUser = db.prepare(`
INSERT INTO users VALUES (@id, @username, @name, @email)
ON CONFLICT(id) DO NOTHING
`);
USERS.map((user) => upsertUser.run(user));
const upsertEvent = db.prepare(`
INSERT INTO events VALUES (@id, @title, @description, @image_url, @date, @host_id)
ON CONFLICT(id) DO NOTHING
`);
EVENTS.map((event) => {
upsertEvent.run(event);
});
db.exec(`
CREATE TABLE IF NOT EXISTS rsvps (
event_id INTEGER REFERENCES events NOT NULL,
name TEXT NOT NULL,
email TEXT NOT NULL,
UNIQUE(event_id, email) ON CONFLICT REPLACE
);
CREATE INDEX IF NOT EXISTS rsvpevents ON rsvps(event_id);
`);
const upsertRSVP = db.prepare(`
INSERT INTO rsvps VALUES (@event_id, @name, @email)
`);
RSVPS.map((rsvp) => {
upsertRSVP.run(rsvp);
});
export default db;
|
