123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 |
- import Database from 'better-sqlite3'
- import { v4 } from 'uuid'
- const db = new Database('./cosette.db')
- 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 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)')
- }
- // TODO: da gestire in qualche modo all'avvio
- // load()
- export function add (cosetta) {
- const q = db.prepare('INSERT INTO cosette (uuid, name, description, tags, images) VALUES(:uuid, :name, :description, :tags, :imgs)')
- cosetta.uuid = v4()
- cosetta.tags = JSON.stringify(cosetta.tags.split(',').map(t => t.toLowerCase().trim()))
- cosetta.imgs = JSON.stringify(cosetta.imgs)
- 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)
- }
|