20240115234601_initial_schema.js

/**
 * @param { import('knex').Knex } knex
 * @returns { Promise<void> }
 */
export async function up(knex) {
  await knex.schema.createTable('citation', t => {
    t.increments();
    t.string('name', 255).notNullable().unique();
    t.text('description').nullable()
    t.timestamps();
  });

  await knex.schema.createTable('citation_citation', t => {
    t.integer('citation_from').notNullable().references('id').inTable('citation');
    t.integer('citation_to').notNullable().references('id').inTable('citation');
    t.timestamps();
    t.unique(['citation_from', 'citation_to']);
  });
}

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
export async function down(knex) {
  await knex.schema.dropTable('citation_citation');
  await knex.schema.dropTable('citation');
}

router.js

import express from "express";
import lodash from "lodash";
import debug from 'debug';

let router = express.Router();
let logger = debug('citation-management:api:citations');

router.use((req, res, next) => {
  logger('hello');
  next();
});

/* GET home page. */
router.get('/api/citations', async function (req, res) {
  /** @type import('knex').Knex */ let db = res.app.locals.db;
  console.log(db);
  let rows =
    await db('citation')
      .limit(parseInt(req.query.limit || '10', 10))
      .offset(parseInt(req.query.offset || '0', 10))
  res.send(rows);
});

router.post('/api/citations', async function (req, res) {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let now = new Date();
  let data = {
    ...lodash.pick(req.body, ['name']),
    created_at: now,
    updated_at: now,
  };
  let [id] = await db('citation').insert(data);
  res.status(201).send({ ...data, id });
});

router.get('/api/citations/:id', async function (req, res) {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  res.send(await db('citation').where({ id: req.params.id }).first());
});

router.put('/api/citations/:id', async function (req, res) {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let now = new Date();
  let data = {
    ...lodash.omit(req.body, ['id']),
    updated_at: now,
  };
  if (!(await db('citation').where({ id: req.params.id }).update(data)))
    return res.status(404).end();
  res.send(await db('citation').where({ id: req.params.id }).first());
});

router.delete('/api/citations/:id', async function (req, res) {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let value;
  await db.transaction(async trx => {
    value = await trx('citation').where({ id: req.params.id }).first();
    if (!value) return res.status(404);
    await trx('citation').where({ id: req.params.id }).del();
  });
  res.send(value);
});

router.get('/api/citations/:id/cites', async (req, res) => {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let rows = await db('citation_citation')
    .select('citation.*')
    .leftJoin('citation', 'citation_to', 'id')
    .where({ citation_from: req.params.id })
    .limit(parseInt(req.query.limit || '10', 10))
    .offset(parseInt(req.query.offset || '0', 10))
  res.send(rows);
});

/**
 * PUT into the collection of citations where id cites toId
 */
router.put('/api/citations/:id/cites/:toId', async (req, res) => {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let data = {
    citation_from: req.params.id,
    citation_to: req.params.toId,
    created_at: new Date(),
  };
  await db('citation_citation').insert(data);
  res.send(data);
})

router.get('/api/citations/:id/cited', async (req, res) => {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let rows = await db('citation_citation')
    .select('citation.*')
    .leftJoin('citation', 'citation_from', 'id')
    .where({ citation_to: req.params.id })
    .limit(parseInt(req.query.limit || '10', 10))
    .offset(parseInt(req.query.offset || '0', 10))
  res.send(rows);
});

/**
 * PUT into the collection of citations where id is cited by fromId
 */
router.put('/api/citations/:id/cited/:fromId', async (req, res) => {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let data = {
    citation_from: req.params.fromId,
    citation_to: req.params.id,
    created_at: new Date(),
  };
  await db('citation_citation').insert(data);
  res.send(data);
});

router.get('/api/latest-citations', async (req, res) => {
  /** @type import('knex').Knex */
  let db = res.app.locals.db;
  let rows = await db({ cc: 'citation_citation' })
    .select(['from.*', 'to.*'])
    .leftJoin('citation', 'citation_from', 'id')
    .where({ citation_to: req.params.id })
    .limit(parseInt(req.query.limit || '10', 10))
    .offset(parseInt(req.query.offset || '0', 10))
  res.send(rows);
})

export default router;