Sequelize/packages/core/test/integration/dialects/postgres/query-interface.test.js

'use strict';

const uniq = require('lodash/uniq');

const chai = require('chai');

const expect = chai.expect;
const Support = require('../../support');

const dialect = Support.getTestDialect();
const { DataTypes } = require('@sequelize/core');

if (dialect.startsWith('postgres')) {
  describe('[POSTGRES Specific] QueryInterface', () => {
    beforeEach(function () {
      this.queryInterface = this.sequelize.queryInterface;
    });

    describe('fetchDatabaseVersion', () => {
      it('reports version', async function () {
        const res = await this.queryInterface.fetchDatabaseVersion();
        // check that result matches the expected version number format. example 9.5.4
        expect(res).to.match(/\d\.\d/);
      });
    });

    describe('renameFunction', () => {
      beforeEach(async function () {
        // ensure the function names we'll use don't exist before we start.
        // then setup our function to rename
        await this.queryInterface.dropFunction('rftest1', []).catch(() => {});

        await this.queryInterface.dropFunction('rftest2', []).catch(() => {});

        await this.queryInterface.createFunction(
          'rftest1',
          [],
          'varchar',
          'plpgsql',
          "return 'testreturn';",
          {},
        );
      });

      it('renames a function', async function () {
        await this.queryInterface.renameFunction('rftest1', [], 'rftest2');
        const res = await this.sequelize.query('select rftest2();', {
          type: this.sequelize.QueryTypes.SELECT,
        });
        expect(res[0].rftest2).to.be.eql('testreturn');
      });
    });

    describe('createFunction', () => {
      beforeEach(async function () {
        // make sure we don't have a pre-existing function called create_job
        // this is needed to cover the edge case of afterEach not getting called because of an unexpected issue or stopage with the
        // test suite causing a failure of afterEach's cleanup to be called.
        await this.queryInterface
          .dropFunction('create_job', [{ type: 'varchar', name: 'test' }])
          // suppress errors here. if create_job doesn't exist thats ok.
          .catch(() => {});
      });

      after(async function () {
        // cleanup
        await this.queryInterface
          .dropFunction('create_job', [{ type: 'varchar', name: 'test' }])
          // suppress errors here. if create_job doesn't exist thats ok.
          .catch(() => {});
      });

      it('creates a stored procedure', async function () {
        const body = 'return test;';
        const options = {};

        // make our call to create a function
        await this.queryInterface.createFunction(
          'create_job',
          [{ type: 'varchar', name: 'test' }],
          'varchar',
          'plpgsql',
          body,
          options,
        );
        // validate
        const res = await this.sequelize.query("select create_job('test');", {
          type: this.sequelize.QueryTypes.SELECT,
        });
        expect(res[0].create_job).to.be.eql('test');
      });

      it('treats options as optional', async function () {
        const body = 'return test;';

        // run with null options parameter
        await this.queryInterface.createFunction(
          'create_job',
          [{ type: 'varchar', name: 'test' }],
          'varchar',
          'plpgsql',
          body,
          null,
        );
        // validate
        const res = await this.sequelize.query("select create_job('test');", {
          type: this.sequelize.QueryTypes.SELECT,
        });
        expect(res[0].create_job).to.be.eql('test');
      });

      it('produces an error when missing expected parameters', async function () {
        const body = 'return 1;';
        const options = {};

        await Promise.all([
          // requires functionName
          expect(
            this.queryInterface.createFunction(
              null,
              [{ name: 'test' }],
              'integer',
              'plpgsql',
              body,
              options,
            ),
          ).to.be.rejectedWith(
            /createFunction missing some parameters. Did you pass functionName, returnType, language and body/,
          ),

          // requires Parameters array
          expect(
            this.queryInterface.createFunction(
              'create_job',
              null,
              'integer',
              'plpgsql',
              body,
              options,
            ),
          ).to.be.rejectedWith(/function parameters array required/),

          // requires returnType
          expect(
            this.queryInterface.createFunction(
              'create_job',
              [{ type: 'varchar', name: 'test' }],
              null,
              'plpgsql',
              body,
              options,
            ),
          ).to.be.rejectedWith(
            /createFunction missing some parameters. Did you pass functionName, returnType, language and body/,
          ),

          // requires type in parameter array
          expect(
            this.queryInterface.createFunction(
              'create_job',
              [{ name: 'test' }],
              'integer',
              'plpgsql',
              body,
              options,
            ),
          ).to.be.rejectedWith(/function or trigger used with a parameter without any type/),

          // requires language
          expect(
            this.queryInterface.createFunction(
              'create_job',
              [{ type: 'varchar', name: 'test' }],
              'varchar',
              null,
              body,
              options,
            ),
          ).to.be.rejectedWith(
            /createFunction missing some parameters. Did you pass functionName, returnType, language and body/,
          ),

          // requires body
          expect(
            this.queryInterface.createFunction(
              'create_job',
              [{ type: 'varchar', name: 'test' }],
              'varchar',
              'plpgsql',
              null,
              options,
            ),
          ).to.be.rejectedWith(
            /createFunction missing some parameters. Did you pass functionName, returnType, language and body/,
          ),
        ]);
      });

      it('overrides a function', async function () {
        const first_body = "return 'first';";
        const second_body = "return 'second';";

        // create function
        await this.queryInterface.createFunction(
          'create_job',
          [{ type: 'varchar', name: 'test' }],
          'varchar',
          'plpgsql',
          first_body,
          null,
        );
        // override
        await this.queryInterface.createFunction(
          'create_job',
          [{ type: 'varchar', name: 'test' }],
          'varchar',
          'plpgsql',
          second_body,
          null,
          { force: true },
        );
        // validate
        const res = await this.sequelize.query("select create_job('abc');", {
          type: this.sequelize.QueryTypes.SELECT,
        });
        expect(res[0].create_job).to.be.eql('second');
      });

      it('produces an error when options.variables is missing expected parameters', function () {
        const body = 'return 1;';
        expect(
          this.queryInterface.createFunction('test_func', [], 'integer', 'plpgsql', body, [], {
            variables: 100,
          }),
        ).to.be.rejectedWith(/expandFunctionVariableList: function variables must be an array/);

        expect(
          this.queryInterface.createFunction('test_func', [], 'integer', 'plpgsql', body, [], {
            variables: [{ name: 'myVar' }],
          }),
        ).to.be.rejectedWith(/function variable must have a name and type/);

        expect(
          this.queryInterface.createFunction('test_func', [], 'integer', 'plpgsql', body, [], {
            variables: [{ type: 'integer' }],
          }),
        ).to.be.rejectedWith(/function variable must have a name and type/);
      });

      it('uses declared variables', async function () {
        await this.sequelize.query('DROP FUNCTION IF EXISTS add_one;');

        const body = 'RETURN myVar + 1;';
        const options = { variables: [{ type: 'integer', name: 'myVar', default: 100 }] };
        await this.queryInterface.createFunction(
          'add_one',
          [],
          'integer',
          'plpgsql',
          body,
          [],
          options,
        );
        const res = await this.sequelize.query('select add_one();', {
          type: this.sequelize.QueryTypes.SELECT,
        });
        expect(res[0].add_one).to.be.eql(101);
      });
    });

    describe('dropFunction', () => {
      beforeEach(async function () {
        const body = 'return test;';
        const options = {};

        // make sure we have a droptest function in place.
        await this.queryInterface
          .createFunction(
            'droptest',
            [{ type: 'varchar', name: 'test' }],
            'varchar',
            'plpgsql',
            body,
            options,
          )
          // suppress errors.. this could fail if the function is already there.. thats ok.
          .catch(() => {});
      });

      it('can drop a function', async function () {
        // call drop function
        await this.queryInterface.dropFunction('droptest', [{ type: 'varchar', name: 'test' }]);
        await expect(
          // now call the function we attempted to drop.. if dropFunction worked as expect it should produce an error.
          this.sequelize.query("select droptest('test');", {
            type: this.sequelize.QueryTypes.SELECT,
          }),
          // test that we did get the expected error indicating that droptest was properly removed.
        ).to.be.rejectedWith(/.*function droptest.* does not exist/);
      });

      it('produces an error when missing expected parameters', async function () {
        await Promise.all([
          expect(this.queryInterface.dropFunction()).to.be.rejectedWith(/.*requires functionName/),

          expect(this.queryInterface.dropFunction('droptest')).to.be.rejectedWith(
            /.*function parameters array required/,
          ),

          expect(
            this.queryInterface.dropFunction('droptest', [{ name: 'test' }]),
          ).to.be.rejectedWith(/.*function or trigger used with a parameter without any type/),
        ]);
      });
    });

    describe('indexes', () => {
      beforeEach(async function () {
        await this.queryInterface.dropTable('Group');

        await this.queryInterface.createTable('Group', {
          username: DataTypes.STRING,
          isAdmin: DataTypes.BOOLEAN,
          from: DataTypes.STRING,
        });
      });

      it('supports newlines', async function () {
        await this.queryInterface.addIndex(
          'Group',
          [
            this.sequelize.literal(`(
            CASE "username"
              WHEN 'foo' THEN 'bar'
              ELSE 'baz'
            END
          )`),
          ],
          { name: 'group_username_case' },
        );

        const indexes = await this.queryInterface.showIndex('Group');
        const indexColumns = uniq(indexes.map(index => index.name));

        expect(indexColumns).to.include('group_username_case');
      });

      it('adds, reads and removes a named functional index to the table', async function () {
        await this.queryInterface.addIndex(
          'Group',
          [this.sequelize.fn('lower', this.sequelize.col('username'))],
          {
            name: 'group_username_lower',
          },
        );

        const indexes0 = await this.queryInterface.showIndex('Group');
        const indexColumns0 = uniq(indexes0.map(index => index.name));

        expect(indexColumns0).to.include('group_username_lower');
        await this.queryInterface.removeIndex('Group', 'group_username_lower');
        const indexes = await this.queryInterface.showIndex('Group');
        const indexColumns = uniq(indexes.map(index => index.name));
        expect(indexColumns).to.be.empty;
      });
    });
  });
}