actual/packages/loot-core/bin/sql-regenerate-views

#!/usr/bin/env actual-cli-runner.js
const { schema } = require('../src/server/aql');
const table = process.argv[3];

if (table == null || table === 'transactions') {
  let fields = Object.keys(schema.transactions).map(fieldName => {
    let desc = schema.transactions[fieldName];
    let field = `t.${desc.name || fieldName}`;

    if (field === 't.description') {
      field = 'pm.targetId';
    } else if (field === 't.category') {
      field = 'cm.transferId';
    }

    return `${field} AS ${fieldName}`;
  });

  console.log(`
DROP VIEW IF EXISTS v_transactions;
CREATE VIEW v_transactions AS
SELECT ${fields.join(', ')} FROM transactions t
  LEFT JOIN category_mapping cm ON cm.id = t.category
  LEFT JOIN payee_mapping pm ON pm.id = t.description
`);
}