sqlite3.js

'use strict';

const { expect } = require('chai');

let tableSql;

const sinon = require('sinon');
const SQLite3_Client = require('../../../lib/dialects/sqlite3');
const client = new SQLite3_Client({ client: 'sqlite3' });
const {
  parseCreateTable,
  parseCreateIndex,
} = require('../../../lib/dialects/sqlite3/schema/internal/parser');
const {
  compileCreateTable,
  compileCreateIndex,
} = require('../../../lib/dialects/sqlite3/schema/internal/compiler');

const _ = require('lodash');
const { equal, deepEqual } = require('assert');
const knex = require('../../../knex');

describe('SQLite SchemaBuilder', function () {
  it('basic create table', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.increments('id');
        table.string('email');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`id` integer not null primary key autoincrement, `email` varchar(255))'
    );
  });

  it('basic create table like', function () {
    tableSql = client
      .schemaBuilder()
      .createTableLike('users', 'users_like')
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` as select * from `users_like` where 0=1'
    );
  });

  it('test create table like with additionnal columns', function () {
    tableSql = client
      .schemaBuilder()
      .createTableLike('users_like', 'users', function (table) {
        table.text('add_col');
        table.integer('add_num_col');
      });

    expect(tableSql.toSQL().length).to.equal(3);
    expect(tableSql.toSQL()[0].sql).to.equal(
      'create table `users_like` as select * from `users` where 0=1'
    );
    expect(tableSql.toSQL()[1].sql).to.equal(
      'alter table `users_like` add column `add_col` text'
    );
    expect(tableSql.toSQL()[2].sql).to.equal(
      'alter table `users_like` add column `add_num_col` integer'
    );
  });

  describe('views', function () {
    let knexSqlite3;

    before(function () {
      knexSqlite3 = knex({
        client: 'sqlite3',
        connection: {
          filename: '',
        },
      });
    });

    it('basic create view', async function () {
      const viewSql = client
        .schemaBuilder()
        .createView('adults', function (view) {
          view.columns(['name']);
          view.as(knexSqlite3('users').select('name').where('age', '>', '18'));
        })
        .toSQL();
      equal(1, viewSql.length);
      expect(viewSql[0].sql).to.equal(
        "create view `adults` (`name`) as select `name` from `users` where `age` > '18'"
      );
    });

    it('basic create view without columns', async function () {
      const viewSql = client
        .schemaBuilder()
        .createView('adults', function (view) {
          view.as(knexSqlite3('users').select('name').where('age', '>', '18'));
        })
        .toSQL();
      equal(1, viewSql.length);
      expect(viewSql[0].sql).to.equal(
        "create view `adults` as select `name` from `users` where `age` > '18'"
      );
    });

    it('create view or replace', async function () {
      const viewSql = client
        .schemaBuilder()
        .createViewOrReplace('adults', function (view) {
          view.columns(['name']);
          view.as(knexSqlite3('users').select('name').where('age', '>', '18'));
        })
        .toSQL();
      expect(viewSql.length).to.equal(2);
      expect(viewSql[0].sql).to.equal('drop view if exists `adults`');
      expect(viewSql[1].sql).to.equal(
        "create view `adults` (`name`) as select `name` from `users` where `age` > '18'"
      );
    });

    it('create view or replace without columns', async function () {
      const viewSql = client
        .schemaBuilder()
        .createViewOrReplace('adults', function (view) {
          view.as(knexSqlite3('users').select('name').where('age', '>', '18'));
        })
        .toSQL();
      expect(viewSql.length).to.equal(2);
      expect(viewSql[0].sql).to.equal('drop view if exists `adults`');
      expect(viewSql[1].sql).to.equal(
        "create view `adults` as select `name` from `users` where `age` > '18'"
      );
    });

    it('create view with check options', async function () {
      expect(() => {
        client
          .schemaBuilder()
          .createView('adults', function (view) {
            view.columns(['name']);
            view.as(
              knexSqlite3('users').select('name').where('age', '>', '18')
            );
            view.localCheckOption();
          })
          .toSQL();
      }).to.throw('check option definition is not supported by this dialect.');
    });

    it('drop view', function () {
      tableSql = client.schemaBuilder().dropView('users').toSQL();
      equal(1, tableSql.length);
      expect(tableSql[0].sql).to.equal('drop view `users`');
    });

    it('drop view with schema', function () {
      tableSql = client
        .schemaBuilder()
        .withSchema('myschema')
        .dropView('users')
        .toSQL();
      equal(1, tableSql.length);
      expect(tableSql[0].sql).to.equal('drop view `myschema`.`users`');
    });

    it('rename and change default of column of view', function () {
      expect(() => {
        tableSql = client
          .schemaBuilder()
          .view('users', function (view) {
            view.column('oldName').rename('newName').defaultTo('10');
          })
          .toSQL();
      }).to.throw('rename column of views is not supported by this dialect.');
    });

    it('rename view', function () {
      expect(() => {
        tableSql = client
          .schemaBuilder()
          .renameView('old_view', 'new_view')
          .toSQL();
      }).to.throw(
        'rename view is not supported by this dialect (instead drop then create another view).'
      );
    });

    it('create materialized view', function () {
      expect(() => {
        tableSql = client
          .schemaBuilder()
          .createMaterializedView('mat_view', function (view) {
            view.columns(['name']);
            view.as(
              knexSqlite3('users').select('name').where('age', '>', '18')
            );
          })
          .toSQL();
      }).to.throw('materialized views are not supported by this dialect.');
    });

    it('refresh view', function () {
      expect(() => {
        tableSql = client
          .schemaBuilder()
          .refreshMaterializedView('view_to_refresh')
          .toSQL();
      }).to.throw('materialized views are not supported by this dialect.');
    });
  });

  it('create json table', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('user', function (table) {
        table.json('preferences');
      })
      .table('user', function (t) {})
      .toSQL();
    expect(tableSql[0].sql).to.equal(
      'create table `user` (`preferences` json)'
    );
  });

  it('basic create table without primary key', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        // This make nothing in SQLite, autoincrement without primary key is a syntax error.
        table.increments('id', { primaryKey: false });
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`id` integer not null primary key autoincrement)'
    );
  });

  it('basic alter table', function () {
    tableSql = client
      .schemaBuilder()
      .alterTable('users', function (table) {
        table.increments('id');
        table.string('email');
      })
      .toSQL();

    equal(2, tableSql.length);
    const expected = [
      'alter table `users` add column `id` integer not null primary key autoincrement',
      'alter table `users` add column `email` varchar(255)',
    ];
    expect(expected).to.eql(_.map(tableSql, 'sql'));
  });

  it('drop table', function () {
    tableSql = client.schemaBuilder().dropTable('users').toSQL();
    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'drop table `users`');
  });

  it('drop table if exists', function () {
    tableSql = client.schemaBuilder().dropTableIfExists('users').toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'drop table if exists `users`');
  });

  it('drop unique', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.dropUnique('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'drop index `users_foo_unique`');
  });

  it('drop unique, custom', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.dropUnique(null, 'foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'drop index `foo`');
  });

  it('drop index', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.dropIndex('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'drop index `users_foo_index`');
  });

  it('drop index, custom', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.dropIndex(null, 'foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'drop index `foo`');
  });

  it('rename table', function () {
    tableSql = client.schemaBuilder().renameTable('users', 'foo').toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` rename to `foo`');
  });

  it('adding primary key', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo');
        table.primary('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), primary key (`foo`))'
    );
  });

  it('adding primary key with specific identifier', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo');
        table.primary('foo', 'pk-users');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), constraint `pk-users` primary key (`foo`))'
    );
  });

  it('adding composite primary key', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo');
        table.string('order_id');
        table.primary(['foo', 'order_id']);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), `order_id` varchar(255), primary key (`foo`, `order_id`))'
    );
  });

  it('adding composite primary key with specific identifier', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo');
        table.string('order_id');
        table.primary(['foo', 'order_id'], 'pk-users');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), `order_id` varchar(255), constraint `pk-users` primary key (`foo`, `order_id`))'
    );
  });

  it('adding primary key fluently', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo').primary();
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), primary key (`foo`))'
    );
  });

  it('adding primary key fluently with specific identifier', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo').primary('pk-users');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), constraint `pk-users` primary key (`foo`))'
    );
  });

  it('adding foreign key', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo').primary();
        table.string('order_id');
        table.foreign('order_id').references('id').on('orders');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), `order_id` varchar(255), foreign key(`order_id`) references `orders`(`id`), primary key (`foo`))'
    );
  });

  it('adding foreign key with specific identifier', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo').primary();
        table.string('order_id');
        table
          .foreign('order_id', 'fk-users-orders')
          .references('id')
          .on('orders');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), `order_id` varchar(255), constraint `fk-users-orders` foreign key(`order_id`) references `orders`(`id`), primary key (`foo`))'
    );
  });

  it('adding foreign key fluently', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table.string('foo').primary();
        table.string('order_id').references('id').on('orders');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`foo` varchar(255), `order_id` varchar(255), foreign key(`order_id`) references `orders`(`id`), primary key (`foo`))'
    );
  });

  it('adds a unique key with autogenerated name', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.unique('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'create unique index `users_foo_unique` on `users` (`foo`)'
    );
  });

  it('adding unique key with specific name', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.unique('foo', 'bar');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'create unique index `bar` on `users` (`foo`)');
  });

  it('adding index', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.index(['foo', 'bar'], 'baz');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'create index `baz` on `users` (`foo`, `bar`)');
  });

  it('adding index with a predicate', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.index(['foo', 'bar'], 'baz', {
          predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
        });
      })
      .toSQL();
    equal(1, tableSql.length);
    expect(tableSql[0].sql).to.equal(
      'create index `baz` on `users` (`foo`, `bar`) where email = "foo@bar"'
    );
  });

  it('adding index with a where not null predicate', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.index(['foo', 'bar'], 'baz', {
          predicate: client.queryBuilder().whereNotNull('email'),
        });
      })
      .toSQL();
    equal(1, tableSql.length);
    expect(tableSql[0].sql).to.equal(
      'create index `baz` on `users` (`foo`, `bar`) where `email` is not null'
    );
  });

  it('adding unique index with a predicate', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.unique(['foo', 'bar'], {
          indexName: 'baz',
          predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
        });
      })
      .toSQL();
    equal(1, tableSql.length);
    expect(tableSql[0].sql).to.equal(
      'create unique index `baz` on `users` (`foo`, `bar`) where email = "foo@bar"'
    );
  });

  it('adding unique index with a where not null predicate', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.unique(['foo', 'bar'], {
          indexName: 'baz',
          predicate: client.queryBuilder().whereNotNull('email'),
        });
      })
      .toSQL();
    equal(1, tableSql.length);
    expect(tableSql[0].sql).to.equal(
      'create unique index `baz` on `users` (`foo`, `bar`) where `email` is not null'
    );
  });

  it('adding incrementing id', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.increments('id');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'alter table `users` add column `id` integer not null primary key autoincrement'
    );
  });

  it('adding big incrementing id', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.bigIncrements('id');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'alter table `users` add column `id` integer not null primary key autoincrement'
    );
  });

  it('adding big incrementing id without primary key', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        // This make nothing in SQLite, autoincrement without primary key is a syntax error.
        table.bigIncrements('id', { primaryKey: false });
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'alter table `users` add column `id` integer not null primary key autoincrement'
    );
  });

  it('adding string', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.string('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` varchar(255)');
  });

  it('allows setting a value in the string length, although unused by sqlite3', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.string('foo', 100);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` varchar(100)');
  });

  it('correctly interprets defaultTo(null)', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.string('foo').defaultTo(null);
      })
      .toSQL();

    equal(
      tableSql[0].sql,
      'alter table `users` add column `foo` varchar(255) default null'
    );
  });

  it('correctly escape singleQuotes passed to defaultTo()', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.string('foo').defaultTo("single 'quoted' value");
      })
      .toSQL();

    equal(
      tableSql[0].sql,
      "alter table `users` add column `foo` varchar(255) default 'single ''quoted'' value'"
    );
  });

  it('chains notNull and defaultTo', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.string('foo', 100).notNull().defaultTo('bar');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      "alter table `users` add column `foo` varchar(100) not null default 'bar'"
    );
  });

  it('adding text', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.text('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` text');
  });

  it('adding big integer', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.bigInteger('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` bigint');
  });

  it('bigincrements works the same as increments for sqlite3', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.bigIncrements('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'alter table `users` add column `foo` integer not null primary key autoincrement'
    );
  });

  it('adding integer', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.integer('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` integer');
  });

  it('adding autoincrements', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.increments('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      'alter table `users` add column `foo` integer not null primary key autoincrement'
    );
  });

  it('adding medium integer', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.mediumint('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` integer');
  });

  it('adding tiny integer', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.tinyint('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` tinyint');
  });

  it('adding small integer', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.smallint('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` integer');
  });

  it('adding float', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.float('foo', 5, 2);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` float');
  });

  it('adding double', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.double('foo', 15, 8);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` float');
  });

  it('adding decimal', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.decimal('foo', 5, 2);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` float');
  });

  it('test adding decimal, no precision', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.decimal('foo', null);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` float');
  });

  it('adding boolean', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.boolean('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` boolean');
  });

  it('adding enum', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.enum('foo', ['bar', 'baz']);
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(
      tableSql[0].sql,
      "alter table `users` add column `foo` text check (`foo` in ('bar', 'baz'))"
    );
  });

  it('adding date', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.date('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` date');
  });

  it('adding date time', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.dateTime('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` datetime');
  });

  it('adding time', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.time('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` time');
  });

  it('adding time stamp', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.timestamp('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` datetime');
  });

  it('adding time stamps', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.timestamps();
      })
      .toSQL();

    equal(2, tableSql.length);
    const expected = [
      'alter table `users` add column `created_at` datetime',
      'alter table `users` add column `updated_at` datetime',
    ];
    deepEqual(expected, _.map(tableSql, 'sql'));
  });

  it('adding binary', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.binary('foo');
      })
      .toSQL();

    equal(1, tableSql.length);
    equal(tableSql[0].sql, 'alter table `users` add column `foo` blob');
  });

  it('adding uuid', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.uuid('foo');
      })
      .toSQL();

    expect(tableSql.length).to.equal(1);
    expect(tableSql[0].sql).to.equal(
      'alter table `users` add column `foo` char(36)'
    );
  });

  it('adding binary uuid', function () {
    tableSql = client
      .schemaBuilder()
      .table('users', function (table) {
        table.uuid('foo', { useBinaryUuid: true });
      })
      .toSQL();

    expect(tableSql.length).to.equal(1);
    expect(tableSql[0].sql).to.equal(
      'alter table `users` add column `foo` binary(16)'
    );
  });

  it('allows for on delete cascade with foreign keys, #166', function () {
    tableSql = client
      .schemaBuilder()
      .createTable('users', function (table) {
        table
          .string('user_id', 36)
          .index()
          .references('id')
          .inTable('user')
          .onDelete('CASCADE');
      })
      .toSQL();

    equal(2, tableSql.length);
    equal(
      tableSql[0].sql,
      'create table `users` (`user_id` varchar(36), foreign key(`user_id`) references `user`(`id`) on delete CASCADE)'
    );
  });

  describe('queryContext', function () {
    let spy;
    let originalWrapIdentifier;

    before(function () {
      spy = sinon.spy();
      originalWrapIdentifier = client.config.wrapIdentifier;
      client.config.wrapIdentifier = function (value, wrap, queryContext) {
        spy(value, queryContext);
        return wrap(value);
      };
    });

    beforeEach(function () {
      spy.resetHistory();
    });

    after(function () {
      client.config.wrapIdentifier = originalWrapIdentifier;
    });

    it('SchemaCompiler passes queryContext to wrapIdentifier via TableCompiler', function () {
      client
        .schemaBuilder()
        .queryContext('table context')
        .createTable('users', function (table) {
          table.increments('id');
          table.string('email');
        })
        .toSQL();

      expect(spy.callCount).to.equal(3);
      expect(spy.firstCall.args).to.deep.equal(['id', 'table context']);
      expect(spy.secondCall.args).to.deep.equal(['email', 'table context']);
      expect(spy.thirdCall.args).to.deep.equal(['users', 'table context']);
    });

    it('TableCompiler passes queryContext to wrapIdentifier', function () {
      client
        .schemaBuilder()
        .createTable('users', function (table) {
          table.increments('id').queryContext('id context');
          table.string('email').queryContext('email context');
        })
        .toSQL();

      expect(spy.callCount).to.equal(3);
      expect(spy.firstCall.args).to.deep.equal(['id', 'id context']);
      expect(spy.secondCall.args).to.deep.equal(['email', 'email context']);
      expect(spy.thirdCall.args).to.deep.equal(['users', undefined]);
    });

    it('TableCompiler allows overwriting queryContext from SchemaCompiler', function () {
      client
        .schemaBuilder()
        .queryContext('schema context')
        .createTable('users', function (table) {
          table.queryContext('table context');
          table.increments('id');
          table.string('email');
        })
        .toSQL();

      expect(spy.callCount).to.equal(3);
      expect(spy.firstCall.args).to.deep.equal(['id', 'table context']);
      expect(spy.secondCall.args).to.deep.equal(['email', 'table context']);
      expect(spy.thirdCall.args).to.deep.equal(['users', 'table context']);
    });

    it('ColumnCompiler allows overwriting queryContext from TableCompiler', function () {
      client
        .schemaBuilder()
        .queryContext('schema context')
        .createTable('users', function (table) {
          table.queryContext('table context');
          table.increments('id').queryContext('id context');
          table.string('email').queryContext('email context');
        })
        .toSQL();

      expect(spy.callCount).to.equal(3);
      expect(spy.firstCall.args).to.deep.equal(['id', 'id context']);
      expect(spy.secondCall.args).to.deep.equal(['email', 'email context']);
      expect(spy.thirdCall.args).to.deep.equal(['users', 'table context']);
    });
  });
});

describe('SQLite parser and compiler', function () {
  const wrap = (v) => `"${v}"`;

  describe('create table', function () {
    it('basic', function () {
      const sql = 'CREATE TABLE "users" ("foo")';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('temporary', function () {
      const sql = 'CREATE TEMP TABLE "users" ("foo")';
      const ast = {
        temporary: true,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('if not exists', function () {
      const sql = 'CREATE TABLE IF NOT EXISTS "users" ("foo")';
      const ast = {
        temporary: false,
        exists: true,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('schema', function () {
      const sql = 'CREATE TABLE "schema"."users" ("foo")';
      const ast = {
        temporary: false,
        exists: false,
        schema: 'schema',
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('rowid', function () {
      const sql = 'CREATE TABLE "users" ("foo") WITHOUT ROWID';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: true,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition type', function () {
      const sql = 'CREATE TABLE "users" ("foo" INTEGER)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: 'INTEGER',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition type one parameter', function () {
      const sql = 'CREATE TABLE "users" ("foo" VARYING CHARACTER(255))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: 'VARYING CHARACTER(255)',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition type two parameters', function () {
      const sql = 'CREATE TABLE "users" ("foo" DECIMAL(+10, -5))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: 'DECIMAL(+10, -5)',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition multiple type', function () {
      const sql = 'CREATE TABLE "users" ("foo" FLOAT, "bar" DECIMAL(4, 2))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: 'FLOAT',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'bar',
            type: 'DECIMAL(4, 2)',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition primary key', function () {
      const sql = 'CREATE TABLE "users" ("foo" PRIMARY KEY)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: {
                name: null,
                order: null,
                conflict: null,
                autoincrement: false,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition primary key order', function () {
      const sql = 'CREATE TABLE "users" ("foo" PRIMARY KEY ASC)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: {
                name: null,
                order: 'ASC',
                conflict: null,
                autoincrement: false,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition primary key conflict', function () {
      const sql = 'CREATE TABLE "users" ("foo" PRIMARY KEY ON CONFLICT ABORT)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: {
                name: null,
                order: null,
                conflict: 'ABORT',
                autoincrement: false,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition primary key autoincrement', function () {
      const sql = 'CREATE TABLE "users" ("foo" PRIMARY KEY AUTOINCREMENT)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: {
                name: null,
                order: null,
                conflict: null,
                autoincrement: true,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition primary key all', function () {
      const sql =
        'CREATE TABLE "users" ("foo" PRIMARY KEY DESC ON CONFLICT FAIL AUTOINCREMENT)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: {
                name: null,
                order: 'DESC',
                conflict: 'FAIL',
                autoincrement: true,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition not null', function () {
      const sql = 'CREATE TABLE "users" ("foo" NOT NULL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: {
                name: null,
                conflict: null,
              },
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition not null conflict', function () {
      const sql = 'CREATE TABLE "users" ("foo" NOT NULL ON CONFLICT IGNORE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: {
                name: null,
                conflict: 'IGNORE',
              },
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition null', function () {
      const sql = 'CREATE TABLE "users" ("foo" NULL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: {
                name: null,
                conflict: null,
              },
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition null conflict', function () {
      const sql = 'CREATE TABLE "users" ("foo" NULL ON CONFLICT ABORT)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: {
                name: null,
                conflict: 'ABORT',
              },
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition unique', function () {
      const sql = 'CREATE TABLE "users" ("foo" UNIQUE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: {
                name: null,
                conflict: null,
              },
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition unique conflict', function () {
      const sql = 'CREATE TABLE "users" ("foo" UNIQUE ON CONFLICT REPLACE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: {
                name: null,
                conflict: 'REPLACE',
              },
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition check', function () {
      const sql = 'CREATE TABLE "users" ("foo" CHECK ("foo" != 42))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: {
                name: null,
                expression: ['"foo"', '!=', '42'],
              },
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition default', function () {
      const sql = `CREATE TABLE "users" ("foo" DEFAULT 'bar')`;
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: {
                name: null,
                value: `'bar'`,
                expression: false,
              },
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition default signed number', function () {
      const sql = 'CREATE TABLE "users" ("foo" DEFAULT -42)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: {
                name: null,
                value: '-42',
                expression: false,
              },
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition default expression', function () {
      const sql = 'CREATE TABLE "users" ("foo" DEFAULT (random()))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: {
                name: null,
                value: ['random', []],
                expression: true,
              },
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition collate', function () {
      const sql = 'CREATE TABLE "users" ("foo" COLLATE RTRIM)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: {
                name: null,
                collation: 'RTRIM',
              },
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references', function () {
      const sql = 'CREATE TABLE "users" ("foo" REFERENCES "other")';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: [],
                delete: null,
                update: null,
                match: null,
                deferrable: null,
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references referenced column', function () {
      const sql = 'CREATE TABLE "users" ("foo" REFERENCES "other" ("bar"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: ['bar'],
                delete: null,
                update: null,
                match: null,
                deferrable: null,
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references multiple columns', function () {
      const sql =
        'CREATE TABLE "users" ("foo" REFERENCES "other" ("bar", "lar"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: ['bar', 'lar'],
                delete: null,
                update: null,
                match: null,
                deferrable: null,
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references on delete', function () {
      const sql =
        'CREATE TABLE "users" ("foo" REFERENCES "other" ON DELETE SET NULL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: [],
                delete: 'SET NULL',
                update: null,
                match: null,
                deferrable: null,
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references on update', function () {
      const sql =
        'CREATE TABLE "users" ("foo" REFERENCES "other" ON UPDATE CASCADE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: [],
                delete: null,
                update: 'CASCADE',
                match: null,
                deferrable: null,
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references match', function () {
      const sql = 'CREATE TABLE "users" ("foo" REFERENCES "other" MATCH FULL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: [],
                delete: null,
                update: null,
                match: 'FULL',
                deferrable: null,
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references deferrable', function () {
      const sql =
        'CREATE TABLE "users" ("foo" REFERENCES "other" NOT DEFERRABLE INITIALLY DEFERRED)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: [],
                delete: null,
                update: null,
                match: null,
                deferrable: {
                  not: true,
                  initially: 'DEFERRED',
                },
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition references all', function () {
      const sql =
        'CREATE TABLE "users" ("foo" REFERENCES "other" ("bar") ON DELETE RESTRICT ON UPDATE NO ACTION MATCH PARTIAL DEFERRABLE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: ['bar'],
                delete: 'RESTRICT',
                update: 'NO ACTION',
                match: 'PARTIAL',
                deferrable: {
                  not: false,
                  initially: null,
                },
              },
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition as', function () {
      const sql = 'CREATE TABLE "users" ("foo" AS (count(*)))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: {
                name: null,
                generated: false,
                expression: ['count', ['*']],
                mode: null,
              },
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition as generated always', function () {
      const sql =
        'CREATE TABLE "users" ("foo" GENERATED ALWAYS AS (length(foo)))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: {
                name: null,
                generated: true,
                expression: ['length', ['foo']],
                mode: null,
              },
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition as mode', function () {
      const sql = 'CREATE TABLE "users" ("foo" AS (length(foo)) VIRTUAL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: {
                name: null,
                generated: false,
                expression: ['length', ['foo']],
                mode: 'VIRTUAL',
              },
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition all', function () {
      const sql =
        'CREATE TABLE "users" ("foo" TEXT PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL ON CONFLICT FAIL NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT REPLACE CHECK ("foo" != 42) DEFAULT NULL COLLATE BINARY REFERENCES "other" ("baz", "bar", "lar") ON UPDATE SET NULL MATCH SIMPLE NOT DEFERRABLE GENERATED ALWAYS AS (count(*)) STORED)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: 'TEXT',
            constraints: {
              primary: {
                name: null,
                order: 'DESC',
                conflict: 'ROLLBACK',
                autoincrement: true,
              },
              notnull: {
                name: null,
                conflict: 'FAIL',
              },
              null: {
                name: null,
                conflict: 'IGNORE',
              },
              unique: {
                name: null,
                conflict: 'REPLACE',
              },
              check: {
                name: null,
                expression: ['"foo"', '!=', '42'],
              },
              default: {
                name: null,
                value: 'NULL',
                expression: false,
              },
              collate: {
                name: null,
                collation: 'BINARY',
              },
              references: {
                name: null,
                table: 'other',
                columns: ['baz', 'bar', 'lar'],
                delete: null,
                update: 'SET NULL',
                match: 'SIMPLE',
                deferrable: {
                  not: true,
                  initially: null,
                },
              },
              as: {
                name: null,
                generated: true,
                expression: ['count', ['*']],
                mode: 'STORED',
              },
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition named', function () {
      const sql =
        'CREATE TABLE "users" ("foo" UNSIGNED BIG INT CONSTRAINT "primary_constraint" PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT CONSTRAINT "notnull_constraint" NOT NULL ON CONFLICT FAIL CONSTRAINT "null_constraint" NULL ON CONFLICT IGNORE CONSTRAINT "unique_constraint" UNIQUE ON CONFLICT REPLACE CONSTRAINT "check_constraint" CHECK ("foo" != 42) CONSTRAINT "default_constraint" DEFAULT NULL CONSTRAINT "collate_constraint" COLLATE BINARY CONSTRAINT "references_constraint" REFERENCES "other" ("baz", "bar", "lar") ON UPDATE SET NULL MATCH SIMPLE NOT DEFERRABLE CONSTRAINT "as_constraint" GENERATED ALWAYS AS (count(*)) STORED)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: 'UNSIGNED BIG INT',
            constraints: {
              primary: {
                name: 'primary_constraint',
                order: 'DESC',
                conflict: 'ROLLBACK',
                autoincrement: true,
              },
              notnull: {
                name: 'notnull_constraint',
                conflict: 'FAIL',
              },
              null: {
                name: 'null_constraint',
                conflict: 'IGNORE',
              },
              unique: {
                name: 'unique_constraint',
                conflict: 'REPLACE',
              },
              check: {
                name: 'check_constraint',
                expression: ['"foo"', '!=', '42'],
              },
              default: {
                name: 'default_constraint',
                value: 'NULL',
                expression: false,
              },
              collate: {
                name: 'collate_constraint',
                collation: 'BINARY',
              },
              references: {
                name: 'references_constraint',
                table: 'other',
                columns: ['baz', 'bar', 'lar'],
                delete: null,
                update: 'SET NULL',
                match: 'SIMPLE',
                deferrable: {
                  not: true,
                  initially: null,
                },
              },
              as: {
                name: 'as_constraint',
                generated: true,
                expression: ['count', ['*']],
                mode: 'STORED',
              },
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition multiple', function () {
      const sql =
        'CREATE TABLE "users" ("primary_column" BLOB CONSTRAINT "primary_constraint" PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT, "notnull_column" DOUBLE PRECISION NOT NULL ON CONFLICT FAIL, "null_column" NATIVE CHARACTER(70) NULL ON CONFLICT IGNORE, "unique_column" CONSTRAINT "unique_constraint" UNIQUE ON CONFLICT REPLACE, "check_column" CHECK ("foo" != 42), "default_column" INT8 DEFAULT NULL, "collate_column" CONSTRAINT "collate_constraint" COLLATE BINARY, "references_column" NUMERIC REFERENCES "other" ("baz", "bar", "lar") ON UPDATE SET NULL MATCH SIMPLE NOT DEFERRABLE, "as_column" CONSTRAINT "as_constraint" GENERATED ALWAYS AS (count(*)) STORED)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'primary_column',
            type: 'BLOB',
            constraints: {
              primary: {
                name: 'primary_constraint',
                order: 'DESC',
                conflict: 'ROLLBACK',
                autoincrement: true,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'notnull_column',
            type: 'DOUBLE PRECISION',
            constraints: {
              primary: null,
              notnull: {
                name: null,
                conflict: 'FAIL',
              },
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'null_column',
            type: 'NATIVE CHARACTER(70)',
            constraints: {
              primary: null,
              notnull: null,
              null: {
                name: null,
                conflict: 'IGNORE',
              },
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'unique_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: {
                name: 'unique_constraint',
                conflict: 'REPLACE',
              },
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'check_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: {
                name: null,
                expression: ['"foo"', '!=', '42'],
              },
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'default_column',
            type: 'INT8',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: {
                name: null,
                value: 'NULL',
                expression: false,
              },
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'collate_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: {
                name: 'collate_constraint',
                collation: 'BINARY',
              },
              references: null,
              as: null,
            },
          },
          {
            name: 'references_column',
            type: 'NUMERIC',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: ['baz', 'bar', 'lar'],
                delete: null,
                update: 'SET NULL',
                match: 'SIMPLE',
                deferrable: {
                  not: true,
                  initially: null,
                },
              },
              as: null,
            },
          },
          {
            name: 'as_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: {
                name: 'as_constraint',
                generated: true,
                expression: ['count', ['*']],
                mode: 'STORED',
              },
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint primary key', function () {
      const sql = 'CREATE TABLE "users" ("foo", PRIMARY KEY ("foo"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: null,
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
            ],
            conflict: null,
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint primary key conflict', function () {
      const sql =
        'CREATE TABLE "users" ("foo", PRIMARY KEY ("foo") ON CONFLICT ROLLBACK)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: null,
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
            ],
            conflict: 'ROLLBACK',
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint primary key column collation and order', function () {
      const sql =
        'CREATE TABLE "users" ("foo", PRIMARY KEY ("foo", "baz" COLLATE BINARY, "bar" ASC, "lar" COLLATE NOCASE DESC))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: null,
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
              {
                name: 'baz',
                expression: false,
                collation: 'BINARY',
                order: null,
              },
              {
                name: 'bar',
                expression: false,
                collation: null,
                order: 'ASC',
              },
              {
                name: 'lar',
                expression: false,
                collation: 'NOCASE',
                order: 'DESC',
              },
            ],
            conflict: null,
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint primary key column expression', function () {
      const sql =
        'CREATE TABLE "users" ("foo", PRIMARY KEY (abs(foo), random() COLLATE RTRIM, baz + bar ASC))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: null,
            columns: [
              {
                name: ['abs', ['foo']],
                expression: true,
                collation: null,
                order: null,
              },
              {
                name: ['random', []],
                expression: true,
                collation: 'RTRIM',
                order: null,
              },
              {
                name: ['baz', '+', 'bar'],
                expression: true,
                collation: null,
                order: 'ASC',
              },
            ],
            conflict: null,
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint unique', function () {
      const sql = 'CREATE TABLE "users" ("foo", UNIQUE ("foo"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'UNIQUE',
            name: null,
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
            ],
            conflict: null,
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint check', function () {
      const sql = 'CREATE TABLE "users" ("foo", CHECK ("foo" IS NULL))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'CHECK',
            name: null,
            expression: ['"foo"', 'IS', 'NULL'],
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other")';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: [],
              delete: null,
              update: null,
              match: null,
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key referenced column', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other" ("bar"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: ['bar'],
              delete: null,
              update: null,
              match: null,
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key multiple columns', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo", "baz") REFERENCES "other" ("bar", "lar"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo', 'baz'],
            references: {
              table: 'other',
              columns: ['bar', 'lar'],
              delete: null,
              update: null,
              match: null,
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key on delete', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other" ON DELETE SET NULL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: [],
              delete: 'SET NULL',
              update: null,
              match: null,
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key on update', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other" ON UPDATE CASCADE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: [],
              delete: null,
              update: 'CASCADE',
              match: null,
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key match', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other" MATCH FULL)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: [],
              delete: null,
              update: null,
              match: 'FULL',
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key deferrable', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other" NOT DEFERRABLE INITIALLY DEFERRED)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: [],
              delete: null,
              update: null,
              match: null,
              deferrable: {
                not: true,
                initially: 'DEFERRED',
              },
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint foreign key all', function () {
      const sql =
        'CREATE TABLE "users" ("foo", FOREIGN KEY ("foo") REFERENCES "other" ("bar") ON DELETE RESTRICT ON UPDATE NO ACTION MATCH PARTIAL DEFERRABLE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['foo'],
            references: {
              table: 'other',
              columns: ['bar'],
              delete: 'RESTRICT',
              update: 'NO ACTION',
              match: 'PARTIAL',
              deferrable: {
                not: false,
                initially: null,
              },
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint named', function () {
      const sql =
        'CREATE TABLE "users" ("foo", CONSTRAINT "primary_constraint" PRIMARY KEY ("foo"))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: 'primary_constraint',
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
            ],
            conflict: null,
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('table constraint multiple', function () {
      const sql =
        'CREATE TABLE "users" ("foo", PRIMARY KEY ("foo", "baz" DESC) ON CONFLICT IGNORE, CHECK ("foo" IS NULL), CONSTRAINT "check_42" CHECK (count(foo) < 42 AND bar = 42), CONSTRAINT "unique_bar" UNIQUE ("bar"), FOREIGN KEY ("bar") REFERENCES "other" ("foo") ON DELETE SET DEFAULT ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "foreign_other_multiple" FOREIGN KEY ("bar", "lar") REFERENCES "other" MATCH SIMPLE NOT DEFERRABLE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: null,
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
              {
                name: 'baz',
                expression: false,
                collation: null,
                order: 'DESC',
              },
            ],
            conflict: 'IGNORE',
          },
          {
            type: 'CHECK',
            name: null,
            expression: ['"foo"', 'IS', 'NULL'],
          },
          {
            type: 'CHECK',
            name: 'check_42',
            expression: ['count', ['foo'], '<', '42', 'AND', 'bar', '=', '42'],
          },
          {
            type: 'UNIQUE',
            name: 'unique_bar',
            columns: [
              {
                name: 'bar',
                expression: false,
                collation: null,
                order: null,
              },
            ],
            conflict: null,
          },
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['bar'],
            references: {
              table: 'other',
              columns: ['foo'],
              delete: 'SET DEFAULT',
              update: 'SET DEFAULT',
              match: null,
              deferrable: {
                not: false,
                initially: 'IMMEDIATE',
              },
            },
          },
          {
            type: 'FOREIGN KEY',
            name: 'foreign_other_multiple',
            columns: ['bar', 'lar'],
            references: {
              table: 'other',
              columns: [],
              delete: null,
              update: null,
              match: 'SIMPLE',
              deferrable: {
                not: true,
                initially: null,
              },
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column definition multiple table constraint multiple', function () {
      const sql =
        'CREATE TABLE "users" ("primary_column" BLOB CONSTRAINT "primary_constraint" PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT, "notnull_column" DOUBLE PRECISION NOT NULL ON CONFLICT FAIL, "null_column" NATIVE CHARACTER(70) NULL ON CONFLICT IGNORE, "unique_column" CONSTRAINT "unique_constraint" UNIQUE ON CONFLICT REPLACE, "check_column" CHECK ("foo" != 42), "default_column" TEXT DEFAULT NULL, "collate_column" CONSTRAINT "collate_constraint" COLLATE BINARY, "references_column" NUMERIC REFERENCES "other" ("baz", "bar", "lar") ON UPDATE SET NULL MATCH SIMPLE NOT DEFERRABLE, "as_column" CONSTRAINT "as_constraint" GENERATED ALWAYS AS (count(*)) STORED, PRIMARY KEY ("foo", "baz" DESC) ON CONFLICT IGNORE, CHECK ("foo" IS NULL), CONSTRAINT "check_42" CHECK (count(foo) < 42 AND bar = 42), CONSTRAINT "unique_bar" UNIQUE ("bar"), FOREIGN KEY ("bar") REFERENCES "other" ("foo") ON DELETE SET DEFAULT ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "foreign_other_multiple" FOREIGN KEY ("bar", "lar") REFERENCES "other" MATCH SIMPLE NOT DEFERRABLE)';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'primary_column',
            type: 'BLOB',
            constraints: {
              primary: {
                name: 'primary_constraint',
                order: 'DESC',
                conflict: 'ROLLBACK',
                autoincrement: true,
              },
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'notnull_column',
            type: 'DOUBLE PRECISION',
            constraints: {
              primary: null,
              notnull: {
                name: null,
                conflict: 'FAIL',
              },
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'null_column',
            type: 'NATIVE CHARACTER(70)',
            constraints: {
              primary: null,
              notnull: null,
              null: {
                name: null,
                conflict: 'IGNORE',
              },
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'unique_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: {
                name: 'unique_constraint',
                conflict: 'REPLACE',
              },
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'check_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: {
                name: null,
                expression: ['"foo"', '!=', '42'],
              },
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'default_column',
            type: 'TEXT',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: {
                name: null,
                value: 'NULL',
                expression: false,
              },
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'collate_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: {
                name: 'collate_constraint',
                collation: 'BINARY',
              },
              references: null,
              as: null,
            },
          },
          {
            name: 'references_column',
            type: 'NUMERIC',
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: {
                name: null,
                table: 'other',
                columns: ['baz', 'bar', 'lar'],
                delete: null,
                update: 'SET NULL',
                match: 'SIMPLE',
                deferrable: {
                  not: true,
                  initially: null,
                },
              },
              as: null,
            },
          },
          {
            name: 'as_column',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: {
                name: 'as_constraint',
                generated: true,
                expression: ['count', ['*']],
                mode: 'STORED',
              },
            },
          },
        ],
        constraints: [
          {
            type: 'PRIMARY KEY',
            name: null,
            columns: [
              {
                name: 'foo',
                expression: false,
                collation: null,
                order: null,
              },
              {
                name: 'baz',
                expression: false,
                collation: null,
                order: 'DESC',
              },
            ],
            conflict: 'IGNORE',
          },
          {
            type: 'CHECK',
            name: null,
            expression: ['"foo"', 'IS', 'NULL'],
          },
          {
            type: 'CHECK',
            name: 'check_42',
            expression: ['count', ['foo'], '<', '42', 'AND', 'bar', '=', '42'],
          },
          {
            type: 'UNIQUE',
            name: 'unique_bar',
            columns: [
              {
                name: 'bar',
                expression: false,
                collation: null,
                order: null,
              },
            ],
            conflict: null,
          },
          {
            type: 'FOREIGN KEY',
            name: null,
            columns: ['bar'],
            references: {
              table: 'other',
              columns: ['foo'],
              delete: 'SET DEFAULT',
              update: 'SET DEFAULT',
              match: null,
              deferrable: {
                not: false,
                initially: 'IMMEDIATE',
              },
            },
          },
          {
            type: 'FOREIGN KEY',
            name: 'foreign_other_multiple',
            columns: ['bar', 'lar'],
            references: {
              table: 'other',
              columns: [],
              delete: null,
              update: null,
              match: 'SIMPLE',
              deferrable: {
                not: true,
                initially: null,
              },
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('special character identifier', function () {
      const sql =
        'CREATE TABLE "$chem@"."users  table" (" ( ", "[`""foo""`]", " INTEGER ", " PRIMARY KEY ", " ) WITHOUT ROWID")';
      const ast = {
        temporary: false,
        exists: false,
        schema: '$chem@',
        table: 'users  table',
        columns: [
          {
            name: ' ( ',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: '[`""foo""`]',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: ' INTEGER ',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: ' PRIMARY KEY ',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: ' ) WITHOUT ROWID',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('no wrap', function () {
      const sql =
        'CREATE TABLE users (note, foo, CONSTRAINT foreign_constraint FOREIGN KEY (note, foo) REFERENCES other (baz, bar))';
      const ast = {
        temporary: false,
        exists: false,
        schema: null,
        table: 'users',
        columns: [
          {
            name: 'note',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
          {
            name: 'foo',
            type: null,
            constraints: {
              primary: null,
              notnull: null,
              null: null,
              unique: null,
              check: null,
              default: null,
              collate: null,
              references: null,
              as: null,
            },
          },
        ],
        constraints: [
          {
            type: 'FOREIGN KEY',
            name: 'foreign_constraint',
            columns: ['note', 'foo'],
            references: {
              table: 'other',
              columns: ['baz', 'bar'],
              delete: null,
              update: null,
              match: null,
              deferrable: null,
            },
          },
        ],
        rowid: false,
      };

      const parsed = parseCreateTable(sql);
      const compiled = compileCreateTable(ast);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('ordering', function () {
      const sql =
        'CREATE TABLE "users" ("foo" TEXT REFERENCES "other" ("baz", "bar", "lar") MATCH PARTIAL ON UPDATE NO ACTION ON DELETE RESTRICT DEFERRABLE CHECK ("foo" != 42) GENERATED ALWAYS AS (count(*)) STORED UNIQUE ON CONFLICT REPLACE NOT NULL ON CONFLICT FAIL PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT DEFAULT NULL COLLATE BINARY NULL ON CONFLICT IGNORE)';
      const newSql =
        'CREATE TABLE "users" ("foo" TEXT PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL ON CONFLICT FAIL NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT REPLACE CHECK ("foo" != 42) DEFAULT NULL COLLATE BINARY REFERENCES "other" ("baz", "bar", "lar") ON DELETE RESTRICT ON UPDATE NO ACTION MATCH PARTIAL DEFERRABLE GENERATED ALWAYS AS (count(*)) STORED)';

      const parsedSql = compileCreateTable(parseCreateTable(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });

    it('lowercase', function () {
      const sql =
        'create table "users" ("primary_column" blob constraint "primary_constraint" primary key desc on conflict rollback autoincrement, "notnull_column" double precision not null on conflict fail, "null_column" native character(70) null on conflict ignore, "unique_column" constraint "unique_constraint" unique on conflict replace, "check_column" check ("foo" != 42), "default_column" text default null, "collate_column" constraint "collate_constraint" collate binary, "references_column" numeric references "other" ("baz", "bar", "lar") on update set null match simple not deferrable, "as_column" constraint "as_constraint" generated always as (count(*)) stored, primary key ("foo", "baz" desc) on conflict ignore, check ("foo" is null), constraint "check_42" check (count(foo) < 42 AND bar = 42), constraint "unique_bar" unique ("bar"), foreign key ("bar") references "other" ("foo") on delete set default on update set default deferrable initially immediate, constraint "foreign_other_multiple" foreign key ("bar", "lar") references "other" match simple not deferrable)';
      const newSql =
        'CREATE TABLE "users" ("primary_column" blob CONSTRAINT "primary_constraint" PRIMARY KEY DESC ON CONFLICT ROLLBACK AUTOINCREMENT, "notnull_column" double precision NOT NULL ON CONFLICT FAIL, "null_column" native character(70) NULL ON CONFLICT IGNORE, "unique_column" CONSTRAINT "unique_constraint" UNIQUE ON CONFLICT REPLACE, "check_column" CHECK ("foo" != 42), "default_column" text DEFAULT null, "collate_column" CONSTRAINT "collate_constraint" COLLATE binary, "references_column" numeric REFERENCES "other" ("baz", "bar", "lar") ON UPDATE SET NULL MATCH simple NOT DEFERRABLE, "as_column" CONSTRAINT "as_constraint" GENERATED ALWAYS AS (count(*)) STORED, PRIMARY KEY ("foo", "baz" DESC) ON CONFLICT IGNORE, CHECK ("foo" is null), CONSTRAINT "check_42" CHECK (count(foo) < 42 AND bar = 42), CONSTRAINT "unique_bar" UNIQUE ("bar"), FOREIGN KEY ("bar") REFERENCES "other" ("foo") ON DELETE SET DEFAULT ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "foreign_other_multiple" FOREIGN KEY ("bar", "lar") REFERENCES "other" MATCH simple NOT DEFERRABLE)';

      const parsedSql = compileCreateTable(parseCreateTable(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });

    it('whitespaces', function () {
      const sql =
        'CREATE  TABLE   "users  table"("foo",\t"bar"CHECK("foo"\n!=\t42),    CONSTRAINT\r\n"foreign_constraint" \t FOREIGN \n KEY("foo","bar")REFERENCES \r\n "other"("baz","bar"))';
      const newSql =
        'CREATE TABLE "users  table" ("foo", "bar" CHECK ("foo" != 42), CONSTRAINT "foreign_constraint" FOREIGN KEY ("foo", "bar") REFERENCES "other" ("baz", "bar"))';

      const parsedSql = compileCreateTable(parseCreateTable(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });

    it('wrap', function () {
      const sql =
        'CREATE TABLE "schema".users (`foo`, [bar], \'baz\' DEFAULT "lar", CONSTRAINT [foreign_constraint] FOREIGN KEY (foo, `bar`) REFERENCES other ([baz], "bar"))';
      const newSql =
        'CREATE TABLE "schema"."users" ("foo", "bar", "baz" DEFAULT "lar", CONSTRAINT "foreign_constraint" FOREIGN KEY ("foo", "bar") REFERENCES "other" ("baz", "bar"))';

      const parsedSql = compileCreateTable(parseCreateTable(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });
  });

  describe('create index', function () {
    it('basic', function () {
      const sql = 'CREATE INDEX "users_index" on "users" ("foo")';
      const ast = {
        unique: false,
        exists: false,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          { name: 'foo', expression: false, collation: null, order: null },
        ],
        where: null,
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('unique', function () {
      const sql = 'CREATE UNIQUE INDEX "users_index" on "users" ("foo")';
      const ast = {
        unique: true,
        exists: false,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          { name: 'foo', expression: false, collation: null, order: null },
        ],
        where: null,
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('if not exists', function () {
      const sql = 'CREATE INDEX IF NOT EXISTS "users_index" on "users" ("foo")';
      const ast = {
        unique: false,
        exists: true,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          { name: 'foo', expression: false, collation: null, order: null },
        ],
        where: null,
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('schema', function () {
      const sql = 'CREATE INDEX "schema"."users_index" on "users" ("foo")';
      const ast = {
        unique: false,
        exists: false,
        schema: 'schema',
        index: 'users_index',
        table: 'users',
        columns: [
          { name: 'foo', expression: false, collation: null, order: null },
        ],
        where: null,
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('where', function () {
      const sql =
        'CREATE INDEX "users_index" on "users" ("foo") where foo IS NOT NULL AND bar = 42';
      const ast = {
        unique: false,
        exists: false,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          { name: 'foo', expression: false, collation: null, order: null },
        ],
        where: ['foo', 'IS', 'NOT', 'NULL', 'AND', 'bar', '=', '42'],
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column collation and order', function () {
      const sql =
        'CREATE INDEX "users_index" on "users" ("foo", "baz" COLLATE BINARY, "bar" ASC, "lar" COLLATE NOCASE DESC)';
      const ast = {
        unique: false,
        exists: false,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          {
            name: 'foo',
            expression: false,
            collation: null,
            order: null,
          },
          {
            name: 'baz',
            expression: false,
            collation: 'BINARY',
            order: null,
          },
          {
            name: 'bar',
            expression: false,
            collation: null,
            order: 'ASC',
          },
          {
            name: 'lar',
            expression: false,
            collation: 'NOCASE',
            order: 'DESC',
          },
        ],
        where: null,
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('column expression', function () {
      const sql =
        'CREATE INDEX "users_index" on "users" (abs(foo), random() COLLATE RTRIM, baz + bar ASC)';
      const ast = {
        unique: false,
        exists: false,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          {
            name: ['abs', ['foo']],
            expression: true,
            collation: null,
            order: null,
          },
          {
            name: ['random', []],
            expression: true,
            collation: 'RTRIM',
            order: null,
          },
          {
            name: ['baz', '+', 'bar'],
            expression: true,
            collation: null,
            order: 'ASC',
          },
        ],
        where: null,
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('special character identifier', function () {
      const sql =
        'CREATE INDEX "$chema"."users  index" on "use-rs" (" ( ", " COLLATE ", " ""foo"" " ASC, "``b a z``" DESC, "[[``""bar""``]]") where foo IS NOT NULL';
      const ast = {
        unique: false,
        exists: false,
        schema: '$chema',
        index: 'users  index',
        table: 'use-rs',
        columns: [
          {
            name: ' ( ',
            expression: false,
            collation: null,
            order: null,
          },
          {
            name: ' COLLATE ',
            expression: false,
            collation: null,
            order: null,
          },
          {
            name: ' ""foo"" ',
            expression: false,
            collation: null,
            order: 'ASC',
          },
          {
            name: '``b a z``',
            expression: false,
            collation: null,
            order: 'DESC',
          },
          {
            name: '[[``""bar""``]]',
            expression: false,
            collation: null,
            order: null,
          },
        ],
        where: ['foo', 'IS', 'NOT', 'NULL'],
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast, wrap);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('no wrap', function () {
      const sql =
        'CREATE INDEX users_index on users (note COLLATE BINARY ASC) where foo IS NOT NULL';
      const ast = {
        unique: false,
        exists: false,
        schema: null,
        index: 'users_index',
        table: 'users',
        columns: [
          {
            name: 'note',
            expression: false,
            collation: 'BINARY',
            order: 'ASC',
          },
        ],
        where: ['foo', 'IS', 'NOT', 'NULL'],
      };

      const parsed = parseCreateIndex(sql);
      const compiled = compileCreateIndex(ast);

      expect(parsed).to.deep.equal(ast);
      expect(compiled).to.equal(sql);
    });

    it('lowercase', function () {
      const sql =
        'create index "users_index" ON "users" ("foo" collate BINARY asc) WHERE "foo" is not null';
      const newSql =
        'CREATE INDEX "users_index" on "users" ("foo" COLLATE BINARY ASC) where "foo" is not null';

      const parsedSql = compileCreateIndex(parseCreateIndex(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });

    it('whitespaces', function () {
      const sql =
        'CREATE  INDEX   "users  index"\non\t"users"("foo"    COLLATE\tBINARY\nASC)\r\nwhere     "foo"\n  IS\tNOT\r\nNULL';
      const newSql =
        'CREATE INDEX "users  index" on "users" ("foo" COLLATE BINARY ASC) where "foo" IS NOT NULL';

      const parsedSql = compileCreateIndex(parseCreateIndex(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });

    it('wrap', function () {
      const sql =
        'CREATE INDEX "schema".[users index] on `users` ("foo " ASC, [b a z] DESC, ` bar`, \'lar\')';
      const newSql =
        'CREATE INDEX "schema"."users index" on "users" ("foo " ASC, "b a z" DESC, " bar", "lar")';

      const parsedSql = compileCreateIndex(parseCreateIndex(sql), wrap);

      expect(parsedSql).to.equal(newSql);
    });

    describe('Checks tests', function () {
      it('allows adding checks positive', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.integer('price').checkPositive();
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          'alter table `user` add column `price` integer check (`price` > 0)'
        );
      });

      it('allows adding checks negative', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.integer('price').checkNegative();
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          'alter table `user` add column `price` integer check (`price` < 0)'
        );
      });

      it('allows adding checks in', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.string('animal').checkIn(['cat', 'dog']);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          "alter table `user` add column `animal` varchar(255) check (`animal` in ('cat','dog'))"
        );
      });

      it('allows adding checks not in', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.string('animal').checkNotIn(['cat', 'dog']);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          "alter table `user` add column `animal` varchar(255) check (`animal` not in ('cat','dog'))"
        );
      });

      it('allows adding checks between', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.integer('price').checkBetween([10, 15]);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          'alter table `user` add column `price` integer check (`price` between 10 and 15)'
        );
      });

      it('allows adding checks between with multiple intervals', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.integer('price').checkBetween([
              [10, 15],
              [20, 25],
            ]);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          'alter table `user` add column `price` integer check (`price` between 10 and 15 or `price` between 20 and 25)'
        );
      });

      it('allows adding checks between strings', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.integer('price').checkBetween(['banana', 'orange']);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          "alter table `user` add column `price` integer check (`price` between 'banana' and 'orange')"
        );
      });

      it('allows length equals', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.varchar('phone').checkLength('=', 8);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          'alter table `user` add column `phone` varchar(255) check (length(`phone`) = 8)'
        );
      });

      it('check regexp', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.varchar('phone').checkRegex('[0-9]{8}');
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          "alter table `user` add column `phone` varchar(255) check (`phone` REGEXP '[0-9]{8}')"
        );
      });

      it('drop checks', function () {
        tableSql = client
          .schemaBuilder()
          .table('user', function (t) {
            t.dropChecks(['check_constraint1', 'check_constraint2']);
          })
          .toSQL();
        expect(tableSql[0].sql).to.equal(
          'alter table `user` drop constraint check_constraint1, drop constraint check_constraint2'
        );
      });
    });
  });
});