feat: Add support for FULLTEXT indexes

nwoltman · May 9, 2019 · 673fad7 · 673fad7
1 parent 8cab7ce
commit 673fad7
Show file tree

Hide file tree

Showing 9 changed files with 176 additions and 3 deletions.
diff --git a/README.md b/README.md
@@ -1162,11 +1162,13 @@ Keys can be defined with the `keys` property, which is an array of [`KeyTypes`](
     accountID: pool.ColTypes.int().unsigned().notNull(),
     email: pool.ColTypes.varchar(255).notNull(),
     location: pool.ColTypes.point().notNull(),
+    description: pool.ColTypes.text(),
   },
   keys: [
     pool.KeyTypes.index('accountID'),
     pool.KeyTypes.uniqueIndex('email'),
     pool.KeyTypes.spatialIndex('location'),
+    pool.KeyTypes.fulltextIndex('description'),
     pool.KeyTypes.foreignKey('accountID').references('account', 'id'),
   ]
 }
@@ -1305,6 +1307,7 @@ Methods:
 
 + `charset(value)` - Sets the column's character set
 + `collate(value)` - Sets the column's collation
++ `fulltextIndex()` - Creates a [fulltext index](https://dev.mysql.com/doc/en/innodb-fulltext-index.html) for the column (excluding `enum` and `set` columns)
 
 Compatible types:
 
@@ -1375,7 +1378,7 @@ Normally if the `DEFAULT` is unspecified, MySQL uses `CURRENT_TIMESTAMP` as the
 
 Methods:
 
-+ `spatialIndex()` - Creates a spatial index for the column
++ `spatialIndex()` - Creates a [spatial index](https://dev.mysql.com/doc/en/create-index.html#create-index-spatial) for the column
 
 Compatible types:
 
@@ -1395,6 +1398,7 @@ Compatible types:
 + `index(columnName [, ...otherColumns])` - Creates a regular [index](https://dev.mysql.com/doc/en/create-index.html)
 + `uniqueIndex(columnName [, ...otherColumns])` - Creates a [unique index](https://dev.mysql.com/doc/en/create-index.html#create-index-unique)
 + `spatialIndex(columnName)` - Creates a [spatial index](https://dev.mysql.com/doc/en/create-index.html#create-index-spatial)
++ `fulltextIndex(columnName)` - Creates a [fulltext index](https://dev.mysql.com/doc/en/innodb-fulltext-index.html)
 + `foreignKey(columnName [, ...otherColumns])` - Creates a [foreign key constraint](https://dev.mysql.com/doc/en/create-table-foreign-keys.html)
 
 **Example:**
@@ -1404,6 +1408,7 @@ Compatible types:
     pool.KeyTypes.index('accountID'),
     pool.KeyTypes.uniqueIndex('email'),
     pool.KeyTypes.spatialIndex('location'),
+    pool.KeyTypes.fulltextIndex('description'),
     pool.KeyTypes.foreignKey('accountID').references('account', 'id'),
 
     // Multi-column keys
@@ -1421,6 +1426,7 @@ All key types have a `name` method that can be used to customize the key’s nam
     pool.KeyTypes.index('accountID').name('account_key'),
     pool.KeyTypes.uniqueIndex('email').name('email_key'),
     pool.KeyTypes.spatialIndex('location').name('location_key'),
+    pool.KeyTypes.fulltextIndex('description').name('description_key'),
     pool.KeyTypes.foreignKey('accountID').references('account', 'id').name('account_foreign_key'),
   ]
 }

diff --git a/jsdoc2md/README.hbs b/jsdoc2md/README.hbs
@@ -203,11 +203,13 @@ Keys can be defined with the `keys` property, which is an array of [`KeyTypes`](
     accountID: pool.ColTypes.int().unsigned().notNull(),
     email: pool.ColTypes.varchar(255).notNull(),
     location: pool.ColTypes.point().notNull(),
+    description: pool.ColTypes.text(),
   },
   keys: [
     pool.KeyTypes.index('accountID'),
     pool.KeyTypes.uniqueIndex('email'),
     pool.KeyTypes.spatialIndex('location'),
+    pool.KeyTypes.fulltextIndex('description'),
     pool.KeyTypes.foreignKey('accountID').references('account', 'id'),
   ]
 }
@@ -346,6 +348,7 @@ Methods:
 
 + `charset(value)` - Sets the column's character set
 + `collate(value)` - Sets the column's collation
++ `fulltextIndex()` - Creates a [fulltext index](https://dev.mysql.com/doc/en/innodb-fulltext-index.html) for the column (excluding `enum` and `set` columns)
 
 Compatible types:
 
@@ -416,7 +419,7 @@ Normally if the `DEFAULT` is unspecified, MySQL uses `CURRENT_TIMESTAMP` as the
 
 Methods:
 
-+ `spatialIndex()` - Creates a spatial index for the column
++ `spatialIndex()` - Creates a [spatial index](https://dev.mysql.com/doc/en/create-index.html#create-index-spatial) for the column
 
 Compatible types:
 
@@ -436,6 +439,7 @@ Compatible types:
 + `index(columnName [, ...otherColumns])` - Creates a regular [index](https://dev.mysql.com/doc/en/create-index.html)
 + `uniqueIndex(columnName [, ...otherColumns])` - Creates a [unique index](https://dev.mysql.com/doc/en/create-index.html#create-index-unique)
 + `spatialIndex(columnName)` - Creates a [spatial index](https://dev.mysql.com/doc/en/create-index.html#create-index-spatial)
++ `fulltextIndex(columnName)` - Creates a [fulltext index](https://dev.mysql.com/doc/en/innodb-fulltext-index.html)
 + `foreignKey(columnName [, ...otherColumns])` - Creates a [foreign key constraint](https://dev.mysql.com/doc/en/create-table-foreign-keys.html)
 
 **Example:**
@@ -445,6 +449,7 @@ Compatible types:
     pool.KeyTypes.index('accountID'),
     pool.KeyTypes.uniqueIndex('email'),
     pool.KeyTypes.spatialIndex('location'),
+    pool.KeyTypes.fulltextIndex('description'),
     pool.KeyTypes.foreignKey('accountID').references('account', 'id'),
 
     // Multi-column keys
@@ -462,6 +467,7 @@ All key types have a `name` method that can be used to customize the key’s nam
     pool.KeyTypes.index('accountID').name('account_key'),
     pool.KeyTypes.uniqueIndex('email').name('email_key'),
     pool.KeyTypes.spatialIndex('location').name('location_key'),
+    pool.KeyTypes.fulltextIndex('description').name('description_key'),
     pool.KeyTypes.foreignKey('accountID').references('account', 'id').name('account_foreign_key'),
   ]
 }

diff --git a/lib/ColumnDefinitions/TextColumnDefinition.js b/lib/ColumnDefinitions/TextColumnDefinition.js
@@ -5,6 +5,7 @@ const ColumnDefinition = require('./ColumnDefinition');
 class TextColumnDefinition extends ColumnDefinition {
   constructor(type, m) {
     super(type, m);
+    this.$fulltextIndex = false;
     this._charset = null;
     this._collate = null;
   }
@@ -19,6 +20,11 @@ class TextColumnDefinition extends ColumnDefinition {
     return this;
   }
 
+  fulltextIndex() {
+    this.$fulltextIndex = true;
+    return this;
+  }
+
   $equals(columnDefinition, columnTableSchema) {
     if (!super.$equals(columnDefinition)) {
       return false;

diff --git a/lib/KeyDefinitions/index.js b/lib/KeyDefinitions/index.js
@@ -25,6 +25,10 @@ const KeyDefinitions = {
     throwIfNoColumns(columns);
     return new IndexKeyDefinition('SPATIAL INDEX', 'sptl', columns);
   },
+  fulltextIndex(...columns) {
+    throwIfNoColumns(columns);
+    return new IndexKeyDefinition('FULLTEXT INDEX', 'fltxt', columns);
+  },
   foreignKey(...columns) {
     throwIfNoColumns(columns);
     return new ForeignKeyDefinition(columns);

diff --git a/lib/TableDefinition.js b/lib/TableDefinition.js
@@ -411,6 +411,10 @@ function createInternalSchema(schema, tableName) {
       const key = KeyDefinitions.spatialIndex(columnName);
       indexKeys[key.$name] = key;
     }
+    if (column.$fulltextIndex) {
+      const key = KeyDefinitions.fulltextIndex(columnName);
+      indexKeys[key.$name] = key;
+    }
   }
 
   if (schema.primaryKey) {

diff --git a/lib/sqlToSchema.js b/lib/sqlToSchema.js
@@ -163,6 +163,7 @@ function generatePrimaryKeySchema(keySQL) {
 const rgxUniqueKey = /^\s*UNIQUE KEY `(\w+)` \((.*?)\)/;
 const rgxIndexKey = /^\s*KEY `(\w+)` \((.*?)\)/;
 const rgxSpatialKey = /^\s*SPATIAL KEY `(\w+)` \((.*?)\)/;
+const rgxFulltextKey = /^\s*FULLTEXT KEY `(\w+)` \((.*?)\)/;
 const rgxForeignKey =
   /\s*CONSTRAINT `(\w+)` FOREIGN KEY \(`(.*?)`\) REFERENCES `(\w+)` \(`(.*?)`\)(?: ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?(?: ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?/;
 
@@ -181,6 +182,10 @@ function generateKeySchema(keySQL) {
     return KeyDefinitions.spatialIndex(...columnsSQLToSchema(keyMatch[2])).name(keyMatch[1]);
   }
 
+  if ((keyMatch = rgxFulltextKey.exec(keySQL)) !== null) {
+    return KeyDefinitions.fulltextIndex(...columnsSQLToSchema(keyMatch[2])).name(keyMatch[1]);
+  }
+
   if ((keyMatch = rgxForeignKey.exec(keySQL)) !== null) {
     const foreignKey = KeyDefinitions
       .foreignKey(...columnsSQLToSchema(keyMatch[2]))

diff --git a/test/integration/MySQLPlus.test.js b/test/integration/MySQLPlus.test.js
@@ -29,6 +29,7 @@ describe('MySQLPlus', function() {
       jdoc: ColTypes.json(),
       location: ColTypes.point().notNull().spatialIndex(),
       line: ColTypes.linestring(),
+      description: ColTypes.text().fulltextIndex(),
     },
     keys: [
       KeyTypes.uniqueIndex('name', 'letter'),
@@ -52,12 +53,14 @@ describe('MySQLPlus', function() {
     '  `jdoc` json DEFAULT NULL,\n' +
     '  `location` point NOT NULL,\n' +
     '  `line` linestring DEFAULT NULL,\n' +
+    '  `description` text,\n' +
     '  PRIMARY KEY (`id`),\n' +
     '  UNIQUE KEY `uniq_email` (`email`),\n' +
     '  UNIQUE KEY `uniq_name_letter` (`name`,`letter`),\n' +
     '  UNIQUE KEY `uniq_created` (`created`),\n' +
     '  KEY `idx_letter` (`letter`),\n' +
-    '  SPATIAL KEY `sptl_location` (`location`)\n' +
+    '  SPATIAL KEY `sptl_location` (`location`),\n' +
+    '  FULLTEXT KEY `fltxt_description` (`description`)\n' +
     ') ENGINE=InnoDB DEFAULT CHARSET=utf8';
 
   const autoIncTableName = 'auto_inc_table';
@@ -345,6 +348,47 @@ describe('MySQLPlus', function() {
     '  SPATIAL KEY `sptl_c` (`c`)\n' +
     ') ENGINE=InnoDB DEFAULT CHARSET=utf8';
 
+  const fulltextIndexesTableName = 'fulltext_table';
+  const fulltextIndexesTableSchema = {
+    columns: {
+      a: ColTypes.char(32).notNull(),
+      b: ColTypes.varchar(255).notNull(),
+      c: ColTypes.text().notNull(),
+    },
+    keys: [
+      KeyTypes.fulltextIndex('a'),
+      KeyTypes.fulltextIndex('b'),
+    ],
+  };
+  const fulltextIndexesTableExpectedSQL =
+    'CREATE TABLE `fulltext_table` (\n' +
+    '  `a` char(32) NOT NULL,\n' +
+    '  `b` varchar(255) NOT NULL,\n' +
+    '  `c` text NOT NULL,\n' +
+    '  FULLTEXT KEY `fltxt_a` (`a`),\n' +
+    '  FULLTEXT KEY `fltxt_b` (`b`)\n' +
+    ') ENGINE=InnoDB DEFAULT CHARSET=utf8';
+
+  const fulltextIndexesTableMigragedSchema = {
+    columns: {
+      a: ColTypes.char(32).notNull(),
+      b: ColTypes.varchar(255).notNull(),
+      c: ColTypes.text().notNull(),
+    },
+    keys: [
+      KeyTypes.fulltextIndex('a'),
+      KeyTypes.fulltextIndex('c'),
+    ],
+  };
+  const fulltextIndexesTableMigratedExpectedSQL =
+    'CREATE TABLE `fulltext_table` (\n' +
+    '  `a` char(32) NOT NULL,\n' +
+    '  `b` varchar(255) NOT NULL,\n' +
+    '  `c` text NOT NULL,\n' +
+    '  FULLTEXT KEY `fltxt_a` (`a`),\n' +
+    '  FULLTEXT KEY `fltxt_c` (`c`)\n' +
+    ') ENGINE=InnoDB DEFAULT CHARSET=utf8';
+
   const foreignKeysTableName = 'fk_table';
   const foreignKeysTableSchema = {
     columns: {
@@ -567,6 +611,7 @@ describe('MySQLPlus', function() {
       pool.defineTable(uniqueKeysTableName, uniqueKeysTableSchema);
       pool.defineTable(indexesTableName, indexesTableSchema);
       pool.defineTable(spatialIndexesTableName, spatialIndexesTableSchema);
+      pool.defineTable(fulltextIndexesTableName, fulltextIndexesTableSchema);
       pool.defineTable(foreignKeysTableName, foreignKeysTableSchema);
       pool.defineTable(optionsTableName, optionsTableSchema);
       pool.defineTable(textTableName, textTableSchema);
@@ -638,6 +683,13 @@ describe('MySQLPlus', function() {
         cbSpatial();
       });
 
+      const cbFulltext = cbManager.registerCallback();
+      pool.query(`SHOW CREATE TABLE \`${fulltextIndexesTableName}\``, (err, result) => {
+        if (err) throw err;
+        result[0]['Create Table'].should.equal(fulltextIndexesTableExpectedSQL);
+        cbFulltext();
+      });
+
       const cb8 = cbManager.registerCallback();
       pool.query(`SHOW CREATE TABLE \`${foreignKeysTableName}\``, (err, result) => {
         if (err) throw err;
@@ -691,6 +743,7 @@ describe('MySQLPlus', function() {
       pool.defineTable(uniqueKeysTableName, uniqueKeysTableSchema);
       pool.defineTable(indexesTableName, indexesTableSchema);
       pool.defineTable(spatialIndexesTableName, spatialIndexesTableSchema);
+      pool.defineTable(fulltextIndexesTableName, fulltextIndexesTableSchema);
       pool.defineTable(foreignKeysTableName, foreignKeysTableSchema);
       pool.defineTable(optionsTableName, optionsTableSchema);
       pool.defineTable(textTableName, textTableSchema);
@@ -767,6 +820,13 @@ describe('MySQLPlus', function() {
         cbSpatial();
       });
 
+      const cbFulltext = cbManager.registerCallback();
+      pool.query(`SHOW CREATE TABLE \`${fulltextIndexesTableName}\``, (err, result) => {
+        if (err) throw err;
+        result[0]['Create Table'].should.equal(fulltextIndexesTableExpectedSQL);
+        cbFulltext();
+      });
+
       const cb8 = cbManager.registerCallback();
       pool.query(`SHOW CREATE TABLE \`${foreignKeysTableName}\``, (err, result) => {
         if (err) throw err;
@@ -820,6 +880,7 @@ describe('MySQLPlus', function() {
       pool.defineTable(uniqueKeysTableName, uniqueKeysTableSchema);
       pool.defineTable(indexesTableName, indexesTableSchema);
       pool.defineTable(spatialIndexesTableName, spatialIndexesTableSchema);
+      pool.defineTable(fulltextIndexesTableName, fulltextIndexesTableSchema);
       pool.defineTable(foreignKeysTableName, foreignKeysTableSchema);
       pool.defineTable(optionsTableName, optionsTableSchema);
       pool.defineTable(textTableName, textTableSchema);
@@ -891,6 +952,13 @@ describe('MySQLPlus', function() {
         cbSpatial();
       });
 
+      const cbFulltext = cbManager.registerCallback();
+      pool.query(`SHOW CREATE TABLE \`${fulltextIndexesTableName}\``, (err, result) => {
+        if (err) throw err;
+        result[0]['Create Table'].should.equal(fulltextIndexesTableExpectedSQL);
+        cbFulltext();
+      });
+
       const cb8 = cbManager.registerCallback();
       pool.query(`SHOW CREATE TABLE \`${foreignKeysTableName}\``, (err, result) => {
         if (err) throw err;
@@ -943,6 +1011,7 @@ describe('MySQLPlus', function() {
       pool.defineTable(uniqueKeysTableName, uniqueKeysTableMigragedSchema);
       pool.defineTable(indexesTableName, indexesTableMigragedSchema);
       pool.defineTable(spatialIndexesTableName, spatialIndexesTableMigragedSchema);
+      pool.defineTable(fulltextIndexesTableName, fulltextIndexesTableMigragedSchema);
       pool.defineTable(foreignKeysTableName, foreignKeysTableMigratedSchema);
       pool.defineTable(optionsTableName, optionsTableMigratedSchema);
 
@@ -1020,6 +1089,13 @@ describe('MySQLPlus', function() {
         cbSpatial();
       });
 
+      const cbFulltext = cbManager.registerCallback();
+      pool.query(`SHOW CREATE TABLE \`${fulltextIndexesTableName}\``, (err, result) => {
+        if (err) throw err;
+        result[0]['Create Table'].should.equal(fulltextIndexesTableMigratedExpectedSQL);
+        cbFulltext();
+      });
+
       const cb8 = cbManager.registerCallback();
       pool.query(`SHOW CREATE TABLE \`${foreignKeysTableName}\``, (err, result) => {
         if (err) throw err;

diff --git a/test/unit/ColumnDefinitions.test.js b/test/unit/ColumnDefinitions.test.js
@@ -429,6 +429,12 @@ describe('ColumnDefinitions', () => {
 
       ColumnDefinitions.point().notNull().index().spatialIndex()
         .$toSQL().should.equal('point NOT NULL');
+
+      ColumnDefinitions.text().fulltextIndex()
+        .$toSQL().should.equal('text');
+
+      ColumnDefinitions.longtext().notNull().index().fulltextIndex()
+        .$toSQL().should.equal('longtext NOT NULL');
     });
 
     it('should allow the columns to be defined as primary keys, but not change the SQL, except for forcing the column to be NOT NULL', () => {
@@ -500,6 +506,26 @@ describe('ColumnDefinitions', () => {
         .$toSQL().should.equal('char CHARACTER SET utf8 COLLATE utf8_general_ci');
     });
 
+    it('should be able to create fulltext indexes', () => {
+      ColumnDefinitions.char(32).fulltextIndex()
+        .$toSQL().should.equal('char(32)');
+
+      ColumnDefinitions.varchar(255).fulltextIndex()
+        .$toSQL().should.equal('varchar(255)');
+
+      ColumnDefinitions.text().fulltextIndex()
+        .$toSQL().should.equal('text');
+
+      ColumnDefinitions.tinytext().fulltextIndex()
+        .$toSQL().should.equal('tinytext');
+
+      ColumnDefinitions.mediumtext().fulltextIndex()
+        .$toSQL().should.equal('mediumtext');
+
+      ColumnDefinitions.longtext().fulltextIndex()
+        .$toSQL().should.equal('longtext');
+    });
+
   });