491 行
16 KiB
Scheme
491 行
16 KiB
Scheme
(define-module (juniority database)
|
|
#:use-module (ice-9 exceptions)
|
|
#:use-module (ice-9 format)
|
|
#:use-module (sqlite3)
|
|
#:export (
|
|
assoc-idol-tag-stmt
|
|
assoc-volume-idol-stmt
|
|
assoc-volume-studio-stmt
|
|
assoc-volume-tag-stmt
|
|
count-idols-stmt
|
|
count-notes-stmt
|
|
count-studios-stmt
|
|
count-tags-stmt
|
|
count-volumes-stmt
|
|
create-tables-sql
|
|
deassoc-idol-tag-stmt
|
|
deassoc-volume-idol-stmt
|
|
deassoc-volume-tag-stmt
|
|
insert-idol-stmt
|
|
insert-studio-stmt
|
|
insert-tag-stmt
|
|
insert-volume-stmt
|
|
migrate-db
|
|
open-db
|
|
param-query
|
|
param-query-all
|
|
param-query-map
|
|
param-query-one
|
|
rate-idol-stmt
|
|
rate-studio-stmt
|
|
rate-volume-stmt
|
|
search-idols-stmt
|
|
search-volumes-stmt
|
|
select-all-idols-stmt
|
|
select-all-studios-stmt
|
|
select-all-tags-stmt
|
|
select-all-volumes-stmt
|
|
select-idol-notes-stmt
|
|
select-idol-stmt
|
|
select-idol-tag-ids-stmt
|
|
select-idol-tags-stmt
|
|
select-idol-volumes-stmt
|
|
select-studio-notes-stmt
|
|
select-studio-stmt
|
|
select-studio-tags-stmt
|
|
select-studio-volumes-stmt
|
|
select-tag-stmt
|
|
select-volume-idol-ids-stmt
|
|
select-volume-idols-stmt
|
|
select-volume-notes-stmt
|
|
select-volume-stmt
|
|
select-volume-tag-ids-stmt
|
|
select-volume-tags-stmt
|
|
update-idol-dob-stmt
|
|
update-volume-pub-stmt
|
|
))
|
|
|
|
;;;;;;;;;;;;;;;;;;;;;;;;
|
|
;; DATABAS ABASEDA
|
|
;; ATABASEDA BASEDATAB
|
|
;; TAB ATA ASE ABA
|
|
;; ABA AB SED AS
|
|
;; BAS BA EDA ASE
|
|
;; ASE AS DATABASE
|
|
;; SED SE ATABASEDA
|
|
;; EDA ED TAB ATA
|
|
;; DAT EDA ABA TAB
|
|
;; ATABASEDA BASEDATAB
|
|
;; TABASED ASEDATAB
|
|
;;;;;;;;;;;;;;;;;;;;;;;;
|
|
(define (param-query db stmt . args)
|
|
;; https://www.sqlite.org/c3ref/stmt.html
|
|
;; use `sqlite-prepare' to prepare a statement
|
|
(let ((p-stmt (sqlite-prepare db stmt)))
|
|
;; use `sqlite-bind-arguments' to bind args to a param query
|
|
(apply sqlite-bind-arguments (cons p-stmt args))
|
|
;; use `sqlite-step' to run a prepared statement
|
|
;; use `sqlite-reset' to commit the implicit transaction
|
|
p-stmt))
|
|
|
|
(define (param-query-all db stmt . rest)
|
|
(let ((results (list))
|
|
(query (apply param-query (cons* db stmt rest)))
|
|
)
|
|
|
|
(do ((result (sqlite-step query) (sqlite-step query)))
|
|
((eq? result #f))
|
|
(set! results (cons result results)))
|
|
(sqlite-reset query)
|
|
(reverse results)))
|
|
|
|
(define (param-query-map db fn stmt . rest)
|
|
(let ((results (list))
|
|
(query (apply param-query (cons* db stmt rest)))
|
|
)
|
|
|
|
(do ((result (sqlite-step query) (sqlite-step query))
|
|
(count 0 (+ 1 count)))
|
|
((eq? result #f))
|
|
(set! results (cons (fn count result) results)))
|
|
(sqlite-reset query)
|
|
(reverse results)))
|
|
|
|
(define (param-query-one db stmt . args)
|
|
;; https://www.sqlite.org/c3ref/stmt.html
|
|
;; use `sqlite-prepare' to prepare a statement
|
|
(let ((p-stmt (sqlite-prepare db stmt))
|
|
(result #nil))
|
|
;; use `sqlite-bind-arguments' to bind args to a param query
|
|
(apply sqlite-bind-arguments (cons p-stmt args))
|
|
;; use `sqlite-step' to run a prepared statement
|
|
(set! result (sqlite-step p-stmt))
|
|
;; use `sqlite-reset' to commit the implicit transaction
|
|
(sqlite-reset p-stmt)
|
|
result))
|
|
|
|
(define (open-db db-path)
|
|
(let ((db (sqlite-open db-path
|
|
(logior
|
|
SQLITE_OPEN_CREATE
|
|
SQLITE_OPEN_READWRITE
|
|
))))
|
|
(unless (sqlite-db? db)
|
|
(raise-exception
|
|
(make-exception-with-message
|
|
(format #t "sqlite: not a database: ~a"
|
|
db-path))))
|
|
db))
|
|
(define (migrate-db db)
|
|
(sqlite-exec db create-tables-sql))
|
|
|
|
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
|
|
;; RIES QUER UER
|
|
;; ERIESQUE SQUERIES ERI
|
|
;; ERI RI SQU SQU RIE
|
|
;; RI QU UE IES
|
|
;; IESQ UE ER ESQ
|
|
;; SQUERIES ER RI SQU
|
|
;; RIESQU RI IE QUE
|
|
;; UE IE ER ES UER
|
|
;; UE UER ESQ IESQ ERI
|
|
;; RIESQUER QUERIESQ RIESQUERIE
|
|
;; SQUE RIES UE IESQUERIES
|
|
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
|
|
(define assoc-idol-tag-stmt
|
|
"INSERT INTO idol_tag (idol_id, tag_id) VALUES (?, ?)")
|
|
(define assoc-volume-idol-stmt
|
|
"INSERT INTO volume_idol (volume_id, idol_id) VALUES (?, ?)")
|
|
(define assoc-volume-studio-stmt
|
|
"INSERT INTO volume_studio (volume_id, studio_id) VALUES (?, ?)")
|
|
(define assoc-volume-tag-stmt
|
|
"INSERT INTO volume_tag (volume_id, tag_id) VALUES (?, ?)")
|
|
|
|
(define count-idols-stmt "SELECT COUNT(*) FROM idol")
|
|
(define count-notes-stmt "SELECT COUNT(*) FROM note")
|
|
(define count-studios-stmt "SELECT COUNT(*) FROM studio")
|
|
(define count-tags-stmt "SELECT COUNT(*) FROM tag")
|
|
(define count-volumes-stmt "SELECT COUNT(*) FROM volume")
|
|
|
|
(define deassoc-idol-tag-stmt
|
|
"DELETE FROM idol_tag WHERE idol_id = ? AND tag_id = ?")
|
|
(define deassoc-volume-idol-stmt
|
|
"DELETE FROM volume_idol WHERE volume_id = ? AND idol_id = ?")
|
|
(define deassoc-volume-tag-stmt
|
|
"DELETE FROM volume_tag WHERE volume_id = ? AND tag_id = ?")
|
|
|
|
(define insert-idol-stmt
|
|
"INSERT INTO idol (jp_name, birth_date) VALUES (?, ?) RETURNING id")
|
|
(define insert-studio-stmt
|
|
"INSERT INTO studio (call_sign) VALUES (?) RETURNING id")
|
|
(define insert-tag-stmt
|
|
"INSERT INTO tag (name) VALUES (?) RETURNING id")
|
|
(define insert-volume-stmt
|
|
"INSERT INTO volume (identifier, published) VALUES (?, ?) RETURNING id")
|
|
|
|
(define rate-idol-stmt
|
|
"UPDATE idol SET rating = ?
|
|
WHERE id = ?")
|
|
(define rate-studio-stmt
|
|
"UPDATE studio SET rating = ?
|
|
WHERE id = ?")
|
|
(define rate-volume-stmt
|
|
"UPDATE volume SET rating = ?
|
|
WHERE id = ?")
|
|
|
|
(define search-idols-stmt
|
|
"SELECT idol.jp_name, idol.birth_date, idol.rating
|
|
FROM idol
|
|
LEFT OUTER JOIN idol_tag ON idol_tag.idol_id = idol.id
|
|
LEFT OUTER JOIN tag ON idol_tag.tag_id = tag.id
|
|
WHERE tag.name LIKE '%' || ? || '%'
|
|
OR idol.jp_name LIKE '%' || ? || '%'
|
|
ORDER BY idol.rating DESC NULLS LAST, idol_tag.id DESC")
|
|
|
|
(define search-volumes-stmt
|
|
"SELECT volume.identifier, volume.published, volume.rating
|
|
FROM volume
|
|
LEFT OUTER JOIN volume_tag ON volume_tag.volume_id = volume.id
|
|
LEFT OUTER JOIN tag ON volume_tag.tag_id = tag.id
|
|
WHERE tag.name LIKE '%' || ? || '%'
|
|
OR volume.identifier LIKE '%' || ? || '%'
|
|
ORDER BY volume.rating DESC NULLS LAST, volume_tag.id DESC")
|
|
|
|
(define select-all-idols-stmt
|
|
"SELECT jp_name, birth_date, rating FROM idol
|
|
ORDER BY rating DESC NULLS LAST, birth_date DESC NULLS LAST")
|
|
(define select-all-studios-stmt
|
|
"SELECT call_sign, full_name, rating FROM studio
|
|
ORDER BY rating DESC NULLS LAST, call_sign ASC")
|
|
(define select-all-tags-stmt
|
|
"SELECT name FROM tag
|
|
ORDER BY name ASC")
|
|
(define select-all-volumes-stmt
|
|
"SELECT identifier, published, rating FROM volume
|
|
ORDER BY rating DESC NULLS LAST, published DESC NULLS LAST, identifier ASC")
|
|
|
|
(define select-idol-stmt
|
|
"SELECT id, birth_date, rating, jp_name FROM idol WHERE jp_name = ?")
|
|
(define select-idol-notes-stmt
|
|
"SELECT note.text, note.updated
|
|
FROM idol_note JOIN note ON idol_note.note_id = note.id
|
|
WHERE idol_note.idol_id = ?
|
|
ORDER BY note.created ASC")
|
|
(define select-idol-tags-stmt
|
|
"SELECT tag.name
|
|
FROM idol_tag INNER JOIN tag ON idol_tag.tag_id = tag.id
|
|
WHERE idol_tag.idol_id = ?
|
|
ORDER BY tag.name ASC")
|
|
(define select-idol-tag-ids-stmt
|
|
"SELECT tag.id
|
|
FROM idol_tag INNER JOIN tag ON idol_tag.tag_id = tag.id
|
|
WHERE idol_tag.idol_id = ?
|
|
ORDER BY tag.id ASC")
|
|
(define select-idol-volumes-stmt
|
|
"SELECT volume.identifier, volume.published, volume.rating, volume.title
|
|
FROM volume_idol JOIN volume ON volume_idol.volume_id = volume.id
|
|
WHERE volume_idol.idol_id = ?
|
|
ORDER BY volume.identifier ASC")
|
|
|
|
(define select-studio-stmt
|
|
"SELECT id, call_sign, full_name, rating FROM studio WHERE call_sign = ?")
|
|
(define select-studio-notes-stmt
|
|
"SELECT note.text, note.updated
|
|
FROM studio_note JOIN note ON studio_note.note_id = note.id
|
|
WHERE studio_note.studio_id = ?
|
|
ORDER BY note.created ASC")
|
|
(define select-studio-tags-stmt
|
|
"SELECT tag.name
|
|
FROM studio_tag JOIN tag ON studio_tag.tag_id = tag.id
|
|
WHERE studio_tag.studio_id = ?
|
|
ORDER BY tag.name ASC")
|
|
(define select-studio-volumes-stmt
|
|
"SELECT volume.identifier, volume.published, volume.rating, volume.title
|
|
FROM volume_studio JOIN volume ON volume_studio.volume_id = volume.id
|
|
WHERE volume_studio.studio_id = ?
|
|
ORDER BY volume.identifier ASC")
|
|
|
|
(define select-tag-stmt
|
|
"SELECT id FROM tag WHERE name = ?")
|
|
|
|
(define select-volume-stmt
|
|
"SELECT id, identifier, title, rating, published FROM volume
|
|
WHERE identifier = ?")
|
|
(define select-volume-notes-stmt
|
|
"SELECT note.text, note.updated
|
|
FROM volume_note JOIN note ON volume_note.note_id = note.id
|
|
WHERE volume_note.volume_id = ?
|
|
ORDER BY note.created ASC")
|
|
(define select-volume-tags-stmt
|
|
"SELECT tag.name
|
|
FROM volume_tag JOIN tag ON volume_tag.tag_id = tag.id
|
|
WHERE volume_tag.volume_id = ?
|
|
ORDER BY tag.name ASC")
|
|
(define select-volume-tag-ids-stmt
|
|
"SELECT tag.id
|
|
FROM volume_tag JOIN tag ON volume_tag.tag_id = tag.id
|
|
WHERE volume_tag.volume_id = ?
|
|
ORDER BY tag.name ASC")
|
|
(define select-volume-idols-stmt
|
|
"SELECT idol.jp_name, idol.birth_date, idol.rating
|
|
FROM volume_idol JOIN idol ON volume_idol.idol_id = idol.id
|
|
WHERE volume_idol.volume_id = ?
|
|
ORDER BY idol.birth_date ASC NULLS LAST, rating DESC NULLS LAST")
|
|
(define select-volume-idol-ids-stmt
|
|
"SELECT idol.id
|
|
FROM volume_idol JOIN idol ON volume_idol.idol_id = idol.id
|
|
WHERE volume_idol.volume_id = ?
|
|
ORDER BY idol.id ASC")
|
|
|
|
(define update-idol-dob-stmt
|
|
"UPDATE idol SET birth_date = ? WHERE id = ?")
|
|
(define update-volume-pub-stmt
|
|
"UPDATE volume SET published = ? WHERE id = ?")
|
|
|
|
(define create-tables-sql
|
|
"
|
|
CREATE TABLE IF NOT EXISTS tag (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(255) NOT NULL
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS tag_name_unique ON tag (name);
|
|
|
|
CREATE TABLE IF NOT EXISTS note (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
text TEXT NOT NULL,
|
|
created INTEGER NOT NULL, -- epoch
|
|
updated INTEGER NOT NULL -- epoch
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS studio (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
call_sign VARCHAR(255) NOT NULL,
|
|
full_name VARCHAR(255),
|
|
rating SMALLINT
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS studio_call_sign_unique ON studio (call_sign);
|
|
CREATE INDEX IF NOT EXISTS studio_full_name ON studio (full_name);
|
|
|
|
CREATE TABLE IF NOT EXISTS idol (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
jp_name VARCHAR(255),
|
|
birth_date VARCHAR(10),
|
|
rating SMALLINT
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idol_jp_name_unique ON idol (jp_name);
|
|
|
|
CREATE TABLE IF NOT EXISTS volume (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
identifier VARCHAR(255) NOT NULL,
|
|
title VARCHAR(255),
|
|
rating SMALLINT,
|
|
published VARCHAR(10) -- YYYY-MM-DD
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS volume_identifier_unique ON volume (identifier);
|
|
CREATE INDEX IF NOT EXISTS volume_title ON volume (title);
|
|
CREATE INDEX IF NOT EXISTS volume_published ON volume (published);
|
|
|
|
CREATE TABLE IF NOT EXISTS volume_idol (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
volume_id INTEGER,
|
|
idol_id INTEGER,
|
|
CONSTRAINT fk_volume_idol_volume
|
|
FOREIGN KEY (volume_id) REFERENCES volume (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_volume_idol_idol
|
|
FOREIGN KEY (idol_id) REFERENCES idol (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS volume_idol_volume ON volume_idol (volume_id);
|
|
CREATE INDEX IF NOT EXISTS volume_idol_idol ON volume_idol (idol_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS volume_idol_unique ON volume_idol (volume_id, idol_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS volume_studio (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
volume_id INTEGER,
|
|
studio_id INTEGER,
|
|
CONSTRAINT fk_volume_studio_volume
|
|
FOREIGN KEY (volume_id) REFERENCES volume (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_volume_studio_studio
|
|
FOREIGN KEY (studio_id) REFERENCES studio (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS volume_studio_volume ON volume_studio (volume_id);
|
|
CREATE INDEX IF NOT EXISTS volume_studio_studio ON volume_studio (studio_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS volume_studio_unique ON volume_studio (volume_id, studio_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS volume_tag (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
volume_id INTEGER,
|
|
tag_id INTEGER,
|
|
CONSTRAINT fk_volume_tag_volume
|
|
FOREIGN KEY (volume_id) REFERENCES volume (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_volume_tag_tag
|
|
FOREIGN KEY (tag_id) REFERENCES tag (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS volume_tag_volume ON volume_tag (volume_id);
|
|
CREATE INDEX IF NOT EXISTS volume_tag_tag ON volume_tag (tag_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS volume_tag_unique ON volume_tag (volume_id, tag_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS idol_tag (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
idol_id INTEGER,
|
|
tag_id INTEGER,
|
|
CONSTRAINT fk_idol_tag_idol
|
|
FOREIGN KEY (idol_id) REFERENCES idol (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_idol_tag_tag
|
|
FOREIGN KEY (tag_id) REFERENCES tag (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idol_tag_tag ON idol_tag (tag_id);
|
|
CREATE INDEX IF NOT EXISTS idol_tag_idol ON idol_tag (idol_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idol_tag_unique ON idol_tag (idol_id, tag_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS studio_tag (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
studio_id INTEGER,
|
|
tag_id INTEGER,
|
|
CONSTRAINT fk_studio_tag_studio
|
|
FOREIGN KEY (studio_id) REFERENCES studio (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_studio_tag_tag
|
|
FOREIGN KEY (tag_id) REFERENCES tag (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS studio_tag_tag ON studio_tag (tag_id);
|
|
CREATE INDEX IF NOT EXISTS studio_tag_studio ON studio_tag (studio_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS studio_tag_unique ON studio_tag (studio_id, tag_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS volume_note (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
volume_id INTEGER,
|
|
note_id INTEGER,
|
|
CONSTRAINT fk_volume_note_volume
|
|
FOREIGN KEY (volume_id) REFERENCES volume (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_volume_note_note
|
|
FOREIGN KEY (note_id) REFERENCES note (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS volume_note_note ON volume_note (note_id);
|
|
CREATE INDEX IF NOT EXISTS volume_note_volume ON volume_note (volume_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS volume_note_unique ON volume_note (volume_id, note_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS idol_note (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
idol_id INTEGER,
|
|
note_id INTEGER,
|
|
CONSTRAINT fk_idol_note_idol
|
|
FOREIGN KEY (idol_id) REFERENCES idol (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_idol_note_note
|
|
FOREIGN KEY (note_id) REFERENCES note (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idol_note_note ON idol_note (note_id);
|
|
CREATE INDEX IF NOT EXISTS idol_note_idol ON idol_note (idol_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idol_note_unique ON idol_note (idol_id, note_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS studio_note (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
studio_id INTEGER,
|
|
note_id INTEGER,
|
|
CONSTRAINT fk_studio_note_studio
|
|
FOREIGN KEY (studio_id) REFERENCES studio (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_studio_note_note
|
|
FOREIGN KEY (note_id) REFERENCES note (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS studio_note_note ON studio_note (note_id);
|
|
CREATE INDEX IF NOT EXISTS studio_note_studio ON studio_note (studio_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS studio_note_unique ON studio_note (studio_id, note_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS tag_note (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
tag_id INTEGER,
|
|
note_id INTEGER,
|
|
CONSTRAINT fk_tag_note_tag
|
|
FOREIGN KEY (tag_id) REFERENCES tag (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_tag_note_note
|
|
FOREIGN KEY (note_id) REFERENCES note (id)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE RESTRICT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS tag_note_note ON tag_note (note_id);
|
|
CREATE INDEX IF NOT EXISTS tag_note_tag ON tag_note (tag_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS tag_note_unique ON tag_note (tag_id, note_id);
|
|
")
|