72 lines
2.5 KiB
JavaScript
72 lines
2.5 KiB
JavaScript
import Database from 'better-sqlite3'
|
|
import { v4 } from 'uuid'
|
|
|
|
const db = new Database('./cosette.db')
|
|
|
|
|
|
export function load() {
|
|
db.pragma('journal_mode = WAL')
|
|
db.exec('CREATE TABLE IF NOT EXISTS cosette (uuid TEXT PRIMARY KEY, name TEXT NOT NULL, description TEXT, tags TEXT, images TEXT, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP)')
|
|
db.exec('CREATE INDEX IF NOT EXISTS cosette_updated_at_index ON cosette (updatedAt)')
|
|
db.exec('CREATE TABLE IF NOT EXISTS chan (uuid TEXT PRIMARY KEY, cosetta_uuid REFERENCES cosette(uuid), message TEXT, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP)')
|
|
}
|
|
|
|
export function add(cosetta) {
|
|
const q = db.prepare('INSERT INTO cosette (uuid, name, description, tags, images) VALUES(:uuid, :name, :description, :tags, :images)')
|
|
cosetta.uuid = v4()
|
|
cosetta.tags = JSON.stringify(
|
|
cosetta.tags.split(',')
|
|
.map(t => t.toLowerCase().trim())
|
|
.filter(t => t)) // tolgo i vuoti
|
|
cosetta.images = JSON.stringify(cosetta.images)
|
|
q.run(cosetta)
|
|
return cosetta
|
|
}
|
|
|
|
export function get(uuid) {
|
|
let q = db.prepare('SELECT * from cosette WHERE uuid = ?')
|
|
const cosetta = q.get(uuid)
|
|
cosetta.tags = cosetta.tags ? JSON.parse(cosetta.tags) : []
|
|
cosetta.images = cosetta.images ? JSON.parse(cosetta.images) : []
|
|
|
|
q = db.prepare('SELECT message, updatedAt FROM chan WHERE cosetta_uuid = ? ORDER BY updatedAt desc')
|
|
const comments = q.all(uuid)
|
|
|
|
return { cosetta, comments }
|
|
}
|
|
|
|
export function getAll({ page }) {
|
|
page = Number(page || 0)
|
|
const perPage = 3
|
|
let query = 'SELECT * FROM cosette ORDER BY updatedAt DESC LIMIT ? OFFSET ?'
|
|
const q = db.prepare(query)
|
|
const ret = q.all(perPage, perPage * page)
|
|
const cosette = ret.map(r => {
|
|
r.tags = r.tags ? JSON.parse(r.tags) : []
|
|
r.images = r.images ? JSON.parse(r.images) : []
|
|
return r
|
|
})
|
|
|
|
const count = db.prepare('SELECT COUNT(*) count from cosette').get().count
|
|
|
|
return { cosette, details: { page, count, prev: page !== 0, next: count > perPage * (page + 1), n_page: Math.ceil(count / perPage) } }
|
|
}
|
|
|
|
|
|
export function addComment(comment) {
|
|
const q = db.prepare('INSERT INTO chan (uuid, cosetta_uuid, message) VALUES(:uuid, :cosetta_uuid, :message)')
|
|
comment.uuid = v4()
|
|
q.run(comment)
|
|
return comment
|
|
}
|
|
|
|
export function getComments(cosetta_uuid) {
|
|
const q = db.prepare('SELECT * from chan WHERE cosetta_uuid = ?')
|
|
return q.all(cosetta_uuid)
|
|
}
|
|
|
|
export function getComment(uuid) {
|
|
const q = db.prepare('SELECT * from chan WHERE uuid = ?')
|
|
const comment = q.get(uuid)
|
|
return comment
|
|
}
|