import { SchemaType } from './SchemaType.js'
import { schema } from '@vlcn.io/typed-sql'
import { nanoid } from 'nanoid';
import {ID_of} from '@vlcn.io/id';
export const SchemaName = 'schema.sql'
// DB name does not need to be static by any means. See other example apps and the vite-start for dynamic db names.
export const DBName = 'linear';
export function newID<T>(): ID_of<T> {
return nanoid() as ID_of<T>;
}
export const Schema = schema<SchemaType>`
CREATE TABLE IF NOT EXISTS issue (
"id" 'ID_of<Issue>' PRIMARY KEY NOT NULL,
"title" TEXT DEFAULT '' NOT NULL,
"creator" TEXT DEFAULT '' NOT NULL,
"priority" '"none" | "urgent" | "high" | "low" | "medium"' DEFAULT 'none' NOT NULL,
"status" '"backlog" | "todo" | "in_progress" | "done" | "canceled"' DEFAULT 'todo' NOT NULL,
"created" INTEGER NOT NULL,
"modified" INTEGER NOT NULL,
"kanbanorder" NOT NULL
);
SELECT crsql_fract_as_ordered('issue', 'kanbanorder');
CREATE TABLE IF NOT EXISTS "description" (
"id" 'ID_of<Issue>' PRIMARY KEY NOT NULL,
"body" TEXT DEFAULT '' NOT NULL
);
CREATE TABLE IF NOT EXISTS "comment" (
"id" 'ID_of<Comment>' PRIMARY KEY NOT NULL,
"body" TEXT DEFAULT '' NOT NULL,
"creator" TEXT DEFAULT '' NOT NULL,
"issueId" 'ID_of<Issue>' NOT NULL,
"created" INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "filter_state" (
"id" '"singleton"' PRIMARY KEY NOT NULL,
"orderBy" TEXT DEFAULT 'created' NOT NULL,
"orderDirection" TEXT DEFAULT 'asc' NOT NULL,
"status" 'String_of<StatusType[]>',
"priority" 'String_of<PriorityType[]>',
"query" TEXT
);
`import { DbSchema, makeSchema, sql } from '@livestore/livestore'
import { Priority, PriorityType, Status, StatusType } from '../types/issue'
const issue = DbSchema.table('issue', {
id: DbSchema.text({ primaryKey: true }),
title: DbSchema.text({ default: '' }),
creator: DbSchema.text({ default: '' }),
priority: DbSchema.text({ default: Priority.NONE }),
status: DbSchema.text({ default: Status.TODO }),
created: DbSchema.integer(),
modified: DbSchema.integer(),
kanbanorder: DbSchema.text({ nullable: false }),
})
export interface FilterState {
orderBy: string
orderDirection: 'asc' | 'desc'
status?: StatusType[]
priority?: PriorityType[]
query?: string
}
const description = DbSchema.table('description', {
// TODO: id is also a foreign key to issue
id: DbSchema.text({ primaryKey: true }),
body: DbSchema.text({ default: '' }),
})
const comment = DbSchema.table(
'comment',
{
id: DbSchema.text({ primaryKey: true }),
body: DbSchema.text({ default: '' }),
creator: DbSchema.text({ default: '' }),
// TODO: issueId is a foreign key to issue
issueId: DbSchema.text(),
created: DbSchema.integer(),
author: DbSchema.text({ nullable: false }),
},
[
{
name: 'issue_id',
columns: ['issueId'],
},
],
)
// TODO: move filter state into its own table?
const appState = DbSchema.table('app_state', {
key: DbSchema.text({ primaryKey: true }),
value: DbSchema.text(),
})
export type AppState = DbSchema.FromTable.RowDecoded<typeof appState>
export type Issue = DbSchema.FromTable.RowDecoded<typeof issue>
export type Description = DbSchema.FromTable.RowDecoded<typeof description>
export type Comment = DbSchema.FromTable.RowDecoded<typeof comment>
export const schema = makeSchema({
// TODO get rid of `app_state` alias once fixed https://github.com/livestorejs/livestore/issues/25
tables: { issue, description, comment, app_state: appState },
actions: {
createIssue: {
statement: {
sql: sql`INSERT INTO issue ("id", "title", "priority", "status", "created", "modified", "kanbanorder")
VALUES ($id, $title, $priority, $status, $created, $modified, $kanbanorder)`,
writeTables: ['issue'],
},
},
createDescription: {
statement: {
sql: sql`INSERT INTO description ("id", "body") VALUES ($id, $body)`,
writeTables: ['description'],
},
},
createComment: {
statement: {
sql: sql`INSERT INTO comment ("id", "body", "issueId", "created", "author")
VALUES ($id, $body, $issueId, $created, $author)`,
writeTables: ['comment'],
},
},
deleteIssue: {
statement: {
sql: sql`DELETE FROM issue WHERE id = $id`,
writeTables: ['issue'],
},
},
deleteDescriptin: {
statement: {
sql: sql`DELETE FROM description WHERE id = $id`,
writeTables: ['description'],
},
},
deleteComment: {
statement: {
sql: sql`DELETE FROM comment WHERE id = $id`,
writeTables: ['comment'],
},
},
deleteCommentsByIssueId: {
statement: {
sql: sql`DELETE FROM comment WHERE issueId = $issueId`,
writeTables: ['comment'],
},
},
updateIssue: {
statement: {
sql: sql`UPDATE issue SET title = $title, priority = $priority, status = $status, modified = $modified WHERE id = $id`,
writeTables: ['issue'],
},
},
updateIssueStatus: {
statement: {
sql: sql`UPDATE issue SET status = $status, modified = unixepoch() * 1000 WHERE id = $id`,
writeTables: ['issue'],
},
},
updateIssueKanbanOrder: {
statement: {
sql: sql`UPDATE issue SET kanbanorder = $kanbanorder, modified = unixepoch() * 1000 WHERE id = $id`,
writeTables: ['issue'],
},
},
updateIssueTitle: {
statement: {
sql: sql`UPDATE issue SET title = $title, modified = unixepoch() * 1000 WHERE id = $id`,
writeTables: ['issue'],
},
},
moveIssue: {
statement: {
sql: sql`UPDATE issue SET kanbanorder = $kanbanorder, status = $status, modified = unixepoch() * 1000 WHERE id = $id`,
writeTables: ['issue'],
},
},
updateIssuePriority: {
statement: {
sql: sql`UPDATE issue SET priority = $priority, modified = unixepoch() * 1000 WHERE id = $id`,
writeTables: ['issue'],
},
},
updateDescription: {
statement: {
sql: sql`UPDATE description SET body = $body WHERE id = $id`,
writeTables: ['description'],
},
},
upsertAppAtom: {
statement: {
sql: sql`INSERT INTO app_state (key, value) VALUES ($key, $value)
ON CONFLICT (key) DO UPDATE SET value = $value`,
writeTables: ['app_state'],
},
},
},
})import { TXAsync } from "@vlcn.io/xplat-api"
import { Issue, Description, Comment, DecodedFilterState, encodeFilterState, StatusType } from "./SchemaType"
import { ID_of } from "@vlcn.io/id"
function colNames(obj: { [key: string]: unknown }) {
return Object.keys(obj).map(key => `"${key}"`).join(', ');
}
function placeholders(obj: { [key: string]: unknown }) {
return Object.keys(obj).map(() => '?').join(', ');
}
function values(obj: { [key: string]: unknown }) {
return Object.values(obj);
}
function set(obj: { [key: string]: unknown }) {
return Object.keys(obj).map(key => `"${key}" = ?`).join(', ');
}
export const mutations = {
createIssue(tx: TXAsync, issue: Issue) {
return tx.exec(
`INSERT INTO issue (${colNames(issue)}) VALUES (${placeholders(issue)})`,
values(issue)
);
},
createDescription(tx: TXAsync, desc: Description) {
return tx.exec(
`INSERT INTO description (${colNames(desc)}) VALUES (${placeholders(desc)})`,
values(desc)
);
},
createIssueWithDescription(tx: TXAsync, issue: Issue, desc: Description) {
return tx.exec(
`INSERT INTO issue (${colNames(issue)}) VALUES (${placeholders(issue)})`,
values(issue)
).then(() => {
return tx.exec(
`INSERT INTO description (${colNames(desc)}) VALUES (${placeholders(desc)})`,
values(desc)
);
});
},
createComment(tx: TXAsync, comment: Comment) {
return tx.exec(
`INSERT INTO comment (${colNames(comment)}) VALUES (${placeholders(comment)})`,
values(comment)
);
},
putFilterState(tx: TXAsync, filterState: DecodedFilterState) {
const encoded = encodeFilterState(filterState)
return tx.exec(
`INSERT INTO filter_state (${colNames(encoded)}) VALUES (${placeholders(encoded)})
ON CONFLICT DO UPDATE SET ${set(encoded)}`,
[...values(encoded), ...values(encoded)]
);
},
updateIssue(tx: TXAsync, issue: Partial<Issue>) {
if (!issue.modified) {
issue = {
...issue,
modified: Date.now()
}
}
return tx.exec(
`UPDATE issue SET ${set(issue)} WHERE id = ?`,
[...values(issue), issue.id]
);
},
updateDescription(tx: TXAsync, desc: Description) {
return tx.exec(
`UPDATE description SET ${set(desc)} WHERE id = ?`,
[...values(desc), desc.id]
);
},
moveIssue(tx: TXAsync, id: ID_of<Issue>, afterId: ID_of<Issue> | null, newStatus: StatusType) {
return tx.exec(
`UPDATE issue_fractindex SET after_id = ?, status = ? WHERE id = ?`,
[afterId, newStatus, id]
)
},
async deleteIssue(tx: TXAsync, id: ID_of<Issue>) {
await tx.exec(
`DELETE FROM issue WHERE id = ?`,
[id]
);
await tx.exec(
`DELETE FROM description WHERE id = ?`,
[id]
);
await tx.exec(
`DELETE FROM comment WHERE issueId = ?`,
[id]
);
}
};