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
68
69
70
71
72
|
import 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 = new Database('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;
|
