summaryrefslogtreecommitdiff
path: root/app/lib/db.server.ts
diff options
context:
space:
mode:
Diffstat (limited to 'app/lib/db.server.ts')
-rw-r--r--app/lib/db.server.ts175
1 files changed, 107 insertions, 68 deletions
diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index e35bba6..cb51028 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -47,6 +47,7 @@ function initSchema(db: Database.Database) {
order_index INTEGER NOT NULL DEFAULT 0
);
+ -- legacy table kept for migration only
CREATE TABLE IF NOT EXISTS band_artists (
band_id TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
@@ -55,6 +56,17 @@ function initSchema(db: Database.Database) {
PRIMARY KEY (band_id, artist_id)
);
+ CREATE TABLE IF NOT EXISTS members (
+ id TEXT PRIMARY KEY,
+ band_id TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
+ artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
+ role TEXT,
+ since TEXT NOT NULL DEFAULT '',
+ until TEXT NOT NULL DEFAULT '',
+ note TEXT NOT NULL DEFAULT '',
+ order_index INTEGER NOT NULL DEFAULT 0
+ );
+
CREATE TABLE IF NOT EXISTS band_revisions (
id TEXT PRIMARY KEY,
band_id TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
@@ -79,16 +91,35 @@ function initSchema(db: Database.Database) {
// migrations
try { db.exec("ALTER TABLE bands ADD COLUMN description TEXT"); } catch { /* already exists */ }
try { db.exec("ALTER TABLE bands ADD COLUMN status TEXT NOT NULL DEFAULT 'active'"); } catch { /* already exists */ }
+ try { db.exec("ALTER TABLE band_artists ADD COLUMN periods TEXT"); } catch { /* already exists */ }
+
+ // one-time data migration: band_artists → members
+ try {
+ const mc = (db.prepare("SELECT COUNT(*) as c FROM members").get() as { c: number }).c;
+ if (mc === 0) {
+ const rows = db.prepare("SELECT * FROM band_artists").all() as {
+ band_id: string; artist_id: string; role: string | null; order_index: number;
+ }[];
+ const insert = db.prepare(
+ "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, '', '', '', ?)"
+ );
+ for (const r of rows) {
+ insert.run(crypto.randomUUID(), r.band_id, r.artist_id, r.role, r.order_index);
+ }
+ }
+ } catch { /* band_artists might not have data */ }
db.exec(`
CREATE INDEX IF NOT EXISTS idx_artist_links_artist_id ON artist_links(artist_id);
- CREATE INDEX IF NOT EXISTS idx_band_artists_band_id ON band_artists(band_id);
- CREATE INDEX IF NOT EXISTS idx_band_artists_artist_id ON band_artists(artist_id);
+ CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id);
+ CREATE INDEX IF NOT EXISTS idx_members_artist_id ON members(artist_id);
CREATE INDEX IF NOT EXISTS idx_band_revisions_band_id ON band_revisions(band_id);
CREATE INDEX IF NOT EXISTS idx_artist_revisions_artist_id ON artist_revisions(artist_id);
`);
}
+// ── Interfaces ────────────────────────────────────────────────────────────────
+
export interface Band {
id: string;
slug: string;
@@ -122,23 +153,35 @@ export interface ArtistLink {
order_index: number;
}
-export interface BandArtistRow {
+export interface MemberRaw {
+ id: string;
band_id: string;
artist_id: string;
role: string | null;
+ since: string;
+ until: string;
+ note: string;
order_index: number;
+}
+
+export interface BandMemberRow extends MemberRaw {
artist_name: string;
artist_slug: string;
}
-export interface ArtistBandRow {
- band_id: string;
- artist_id: string;
- role: string | null;
+export interface ArtistMemberRow extends MemberRaw {
band_name: string;
band_slug: string;
}
+export interface MemberInput {
+ artist_id: string;
+ role: string | null;
+ since: string;
+ until: string;
+ note: string;
+}
+
export interface BandRevision {
id: string;
band_id: string;
@@ -194,16 +237,16 @@ export function getBandLinks(bandId: string): BandLink[] {
.all(bandId) as BandLink[];
}
-export function getBandArtists(bandId: string): BandArtistRow[] {
+export function getBandMembers(bandId: string): BandMemberRow[] {
return getDb()
.prepare(
- `SELECT ba.*, a.name AS artist_name, a.slug AS artist_slug
- FROM band_artists ba
- JOIN artists a ON a.id = ba.artist_id
- WHERE ba.band_id = ?
- ORDER BY ba.order_index`
+ `SELECT m.*, a.name AS artist_name, a.slug AS artist_slug
+ FROM members m
+ JOIN artists a ON a.id = m.artist_id
+ WHERE m.band_id = ?
+ ORDER BY m.order_index`
)
- .all(bandId) as BandArtistRow[];
+ .all(bandId) as BandMemberRow[];
}
export function getBandRevisions(bandId: string): BandRevision[] {
@@ -220,7 +263,7 @@ export interface CreateBandInput {
description: string | null;
status: string;
links: { label: string; url: string }[];
- artists: { id: string; role: string | null }[];
+ members: MemberInput[];
message: string;
ip_address: string;
}
@@ -236,25 +279,17 @@ export function createBand(input: CreateBandInput): Band {
"INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
).run(crypto.randomUUID(), input.id, link.label, link.url, i);
});
- input.artists.forEach((artist, i) => {
+ input.members.forEach((m, i) => {
db.prepare(
- "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
- ).run(input.id, artist.id, artist.role, i);
+ "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
+ ).run(crypto.randomUUID(), input.id, m.artist_id, m.role, m.since, m.until, m.note, i);
});
const band = getBandById(input.id)!;
const links = getBandLinks(input.id);
- const artists = getBandArtists(input.id);
- const snapshot = JSON.stringify({
- name: band.name,
- area: band.area,
- description: band.description,
- status: band.status,
- links: links.map((l) => ({ label: l.label, url: l.url })),
- artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })),
- });
+ const members = getBandMembers(input.id);
db.prepare(
"INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
- ).run(crypto.randomUUID(), input.id, snapshot, input.message, input.ip_address);
+ ).run(crypto.randomUUID(), input.id, buildSnapshot(band, links, members), input.message, input.ip_address);
return band;
})() as Band;
}
@@ -266,7 +301,7 @@ export interface UpdateBandInput {
description: string | null;
status: string;
links: { label: string; url: string }[];
- artists: { id: string; role: string | null }[];
+ members: MemberInput[];
message: string;
ip_address: string;
}
@@ -283,29 +318,39 @@ export function updateBand(id: string, input: UpdateBandInput): void {
"INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
).run(crypto.randomUUID(), id, link.label, link.url, i);
});
- db.prepare("DELETE FROM band_artists WHERE band_id = ?").run(id);
- input.artists.forEach((artist, i) => {
+ db.prepare("DELETE FROM members WHERE band_id = ?").run(id);
+ input.members.forEach((m, i) => {
db.prepare(
- "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
- ).run(id, artist.id, artist.role, i);
+ "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
+ ).run(crypto.randomUUID(), id, m.artist_id, m.role, m.since, m.until, m.note, i);
});
const band = getBandById(id)!;
const links = getBandLinks(id);
- const artists = getBandArtists(id);
- const snapshot = JSON.stringify({
- name: band.name,
- area: band.area,
- description: band.description,
- status: band.status,
- links: links.map((l) => ({ label: l.label, url: l.url })),
- artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })),
- });
+ const members = getBandMembers(id);
db.prepare(
"INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
- ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address);
+ ).run(crypto.randomUUID(), id, buildSnapshot(band, links, members), input.message, input.ip_address);
})();
}
+function buildSnapshot(band: Band, links: BandLink[], members: BandMemberRow[]): string {
+ return JSON.stringify({
+ name: band.name,
+ area: band.area,
+ description: band.description,
+ status: band.status,
+ links: links.map((l) => ({ label: l.label, url: l.url })),
+ members: members.map((m) => ({
+ artist_id: m.artist_id,
+ artist_name: m.artist_name,
+ role: m.role,
+ since: m.since,
+ until: m.until,
+ note: m.note,
+ })),
+ });
+}
+
// ── Artist queries ────────────────────────────────────────────────────────────
export function listArtists(): Artist[] {
@@ -326,16 +371,16 @@ export function getArtistLinks(artistId: string): ArtistLink[] {
.all(artistId) as ArtistLink[];
}
-export function getArtistBands(artistId: string): ArtistBandRow[] {
+export function getArtistMembers(artistId: string): ArtistMemberRow[] {
return getDb()
.prepare(
- `SELECT ba.*, b.name AS band_name, b.slug AS band_slug
- FROM band_artists ba
- JOIN bands b ON b.id = ba.band_id
- WHERE ba.artist_id = ?
- ORDER BY b.name`
+ `SELECT m.*, b.name AS band_name, b.slug AS band_slug
+ FROM members m
+ JOIN bands b ON b.id = m.band_id
+ WHERE m.artist_id = ?
+ ORDER BY b.name, m.order_index`
)
- .all(artistId) as ArtistBandRow[];
+ .all(artistId) as ArtistMemberRow[];
}
export function getArtistRevisions(artistId: string): ArtistRevision[] {
@@ -411,21 +456,14 @@ export function updateArtist(id: string, input: UpdateArtistInput): void {
// ── Export / Import ───────────────────────────────────────────────────────────
-interface BandArtistRaw {
- band_id: string;
- artist_id: string;
- role: string | null;
- order_index: number;
-}
-
export interface DbExport {
- version: 1;
+ version: 3;
exported_at: string;
bands: Band[];
band_links: BandLink[];
artists: Artist[];
artist_links: ArtistLink[];
- band_artists: BandArtistRaw[];
+ members: MemberRaw[];
band_revisions: BandRevision[];
artist_revisions: ArtistRevision[];
}
@@ -433,13 +471,13 @@ export interface DbExport {
export function exportDb(): DbExport {
const db = getDb();
return {
- version: 1,
+ version: 3,
exported_at: new Date().toISOString(),
bands: db.prepare("SELECT * FROM bands").all() as Band[],
band_links: db.prepare("SELECT * FROM band_links ORDER BY band_id, order_index").all() as BandLink[],
artists: db.prepare("SELECT * FROM artists").all() as Artist[],
artist_links: db.prepare("SELECT * FROM artist_links ORDER BY artist_id, order_index").all() as ArtistLink[],
- band_artists: db.prepare("SELECT * FROM band_artists ORDER BY band_id, order_index").all() as BandArtistRaw[],
+ members: db.prepare("SELECT * FROM members ORDER BY band_id, order_index").all() as MemberRaw[],
band_revisions: db.prepare("SELECT * FROM band_revisions ORDER BY created_at").all() as BandRevision[],
artist_revisions: db.prepare("SELECT * FROM artist_revisions ORDER BY created_at").all() as ArtistRevision[],
};
@@ -450,15 +488,16 @@ export interface ImportResult {
artists: number;
band_links: number;
artist_links: number;
- band_artists: number;
+ members: number;
band_revisions: number;
artist_revisions: number;
}
export function importDb(data: DbExport): ImportResult {
- if (data.version !== 1) throw new Error("Unsupported export version");
+ if (data.version !== 3) throw new Error("Unsupported export version");
const db = getDb();
return db.transaction(() => {
+ db.prepare("DELETE FROM members").run();
db.prepare("DELETE FROM band_artists").run();
db.prepare("DELETE FROM band_revisions").run();
db.prepare("DELETE FROM artist_revisions").run();
@@ -495,11 +534,11 @@ export function importDb(data: DbExport): ImportResult {
insertArtistLink.run(l.id, l.artist_id, l.label, l.url, l.order_index);
}
- const insertBandArtist = db.prepare(
- "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
+ const insertMember = db.prepare(
+ "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
);
- for (const ba of data.band_artists) {
- insertBandArtist.run(ba.band_id, ba.artist_id, ba.role, ba.order_index);
+ for (const m of data.members) {
+ insertMember.run(m.id, m.band_id, m.artist_id, m.role, m.since, m.until, m.note, m.order_index);
}
const insertBandRev = db.prepare(
@@ -521,7 +560,7 @@ export function importDb(data: DbExport): ImportResult {
artists: data.artists.length,
band_links: data.band_links.length,
artist_links: data.artist_links.length,
- band_artists: data.band_artists.length,
+ members: data.members.length,
band_revisions: data.band_revisions.length,
artist_revisions: data.artist_revisions.length,
};
generated by cgit v1.2.3 (git 2.25.1) at 2026-05-17 13:19:37 +0000