<commit_before>const Sequelize = require('sequelize');
const { v4: uuidv4 } = require('uuid');

/**
 * @param {import('sequelize').QueryInterface} queryInterface
 * @param {import('../lib/config')} config
 * @param {import('../lib/logger')} appLog
 * @param {object} nedb - collection of nedb objects created in /lib/db.js
 * @param {object} sequelizeDb - sequelize instance
 */
// eslint-disable-next-line no-unused-vars
async function up(queryInterface, config, appLog, nedb, sequelizeDb) {
  /**
   * batches can now take the place of query_history,
   * as it acts as a log of queries that have been run.
   *
   * Existing query_history records are inserted into batches.
   * Then a view is created to translate batches and other tables
   * into a wide table, used for reporting.
   *
   * The query_history table is then dropped,
   * as it is no longer necessary.
   * ========================================================
   */

  let rows = await sequelizeDb.query(
    `
      SELECT 
        connection_id, 
        user_id, 
        start_time, 
        stop_time,
        query_run_time AS duration_ms,
        query_id,
        query_name AS name,
        query_text AS batch_text,
        created_at,
        row_count,
        incomplete
      FROM 
        query_history
    `,
    {
      type: Sequelize.QueryTypes.SELECT,
    }
  );

  const batchRows = [];
  const statementRows = [];

  rows.forEach((row) => {
    const { incomplete, row_count, ...batch } = row;
    const batchId = uuidv4();
    batchRows.push({
      ...batch,
      id: batchId,
      selected_text: row.batch_text,
      status: 'finished',
      updated_at: row.created_at,
    });
    statementRows.push({
      id: uuidv4(),
      sequence: 1,
      batch_id: batchId,
      statement_text: row.batch_text,
      status: 'finished',
      start_time: row.start_time,
      stop_time: row.stop_time,
      duration_ms: row.duration_ms,
      incomplete,
      row_count,
      created_at: row.created_at,
      updated_at: row.created_at,
    });
  });

  if (batchRows.length) {
    await queryInterface.bulkInsert('batches', batchRows);
    await queryInterface.bulkInsert('statements', statementRows);
  }

  await sequelizeDb.query(
    `
      CREATE VIEW vw_query_history AS 
        WITH statement_summary AS (
          SELECT 
            batch_id, 
            SUM(row_count) AS row_count, 
            MAX(CONVERT(INT, incomplete)) AS incomplete
          FROM 
            statements<commit_msg>fixed SQL to be working with MSSQL, MySQL, Postgres and SQLLITE<commit_after>const Sequelize = require('sequelize');
const { v4: uuidv4 } = require('uuid');

/**
 * @param {import('sequelize').QueryInterface} queryInterface
 * @param {import('../lib/config')} config
 * @param {import('../lib/logger')} appLog
 * @param {object} nedb - collection of nedb objects created in /lib/db.js
 * @param {object} sequelizeDb - sequelize instance
 */
// eslint-disable-next-line no-unused-vars
async function up(queryInterface, config, appLog, nedb, sequelizeDb) {
  /**
   * batches can now take the place of query_history,
   * as it acts as a log of queries that have been run.
   *
   * Existing query_history records are inserted into batches.
   * Then a view is created to translate batches and other tables
   * into a wide table, used for reporting.
   *
   * The query_history table is then dropped,
   * as it is no longer necessary.
   * ========================================================
   */

  let rows = await sequelizeDb.query(
    `
      SELECT 
        connection_id, 
        user_id, 
        start_time, 
        stop_time,
        query_run_time AS duration_ms,
        query_id,
        query_name AS name,
        query_text AS batch_text,
        created_at,
        row_count,
        incomplete
      FROM 
        query_history
    `,
    {
      type: Sequelize.QueryTypes.SELECT,
    }
  );

  const batchRows = [];
  const statementRows = [];

  rows.forEach((row) => {
    const { incomplete, row_count, ...batch } = row;
    const batchId = uuidv4();
    batchRows.push({
      ...batch,
      id: batchId,
      selected_text: row.batch_text,
      status: 'finished',
      updated_at: row.created_at,
    });
    statementRows.push({
      id: uuidv4(),
      sequence: 1,
      batch_id: batchId,
      statement_text: row.batch_text,
      status: 'finished',
      start_time: row.start_time,
      stop_time: row.stop_time,
      duration_ms: row.duration_ms,
      incomplete,
      row_count,
      created_at: row.created_at,
      updated_at: row.created_at,
    });
  });

  if (batchRows.length) {
    await queryInterface.bulkInsert('batches', batchRows);
    await queryInterface.bulkInsert('statements', statementRows);
  }

  await sequelizeDb.query(
    `
      CREATE VIEW vw_query_history AS 
        WITH statement_summary AS (
          SELECT 
            batch_id, 
            SUM(row_count) AS row_count, 
            MAX(CAST(incomplete AS INTEGER)) AS incomplete
          FROM 
            statements
{'fraction_non_alphanumeric': 0.12172026040639179, 'fraction_numerical': 0.0019727756954034327, 'mean_word_length': 2.4678522571819426, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6197458', 'n_tokens_mistral': 1581, 'n_tokens_neox': 1511, 'n_words': 404}