12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455 |
- 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 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 (limit=0, offset=0, tags=[], search='') {
- const q = db.prepare('SELECT * FROM cosette')
- const ret = q.all()
- return ret.map(r => {
- r.tags = r.tags ? JSON.parse(r.tags) : []
- r.images = r.images ? JSON.parse(r.images) : []
- return r
- })
- }
- 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)
- }
|