1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071 |
- 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)
- }
- export function getComment (uuid) {
- const q = db.prepare('SELECT * from chan WHERE uuid = ?')
- const comment = q.get(uuid)
- return comment
- }
|