[MYSQL] Full text search (match against)

[corbanvilla]

Added full text search (select <> from <> where match(<cols>) against (<query>)) (as specified here).

Changes include:

• added inNaturalLanguage expression
• added .against call
• added match for use with where
• added fulltext indexes (types only)
• added test cases

I only implemented the fulltext index types for indexes but wasn't sure which direction to go for anything further. Also not super familiar with the codebase so feedback is welcome.

[dankochetov]

I've looked through the MySQL docs, and if I understood correctly, match and against are parts of a single expression: MATCH (col1,col2,...) AGAINST (expr [search_modifier]). So I feel like it should all be implemented as an operator function (maybe also using a builder syntax), something like this:

.where(
  match(articles.title, articles.body)
    .against('database')
    .inNaturalSelectionMode()
)

[corbanvilla]

Sorry for the delay on this, hoping to get to this by this weekend. Appreciate the feedback, definitely makes sense!

[maoosi]

@corbanvilla does your implementation supports adding multiple columns as fulltext index (like in the below example)?

fulltextIndex('title_content_idx').on([Post.title, Post.content])

CREATE FULLTEXT INDEX `title_content_idx` ON `Post` (`title`, `content`);

[mlecoq]

Hi, @corbanvilla it seems that you have not taken into account of @dankochetov about match and against. What is the status of this PR ?

Thanks !

[mlecoq]

I had to modify drizzle-kit for it to create my full text index, here is the patch

index 4508891..0e97fd2 100755
--- a/node_modules/drizzle-kit/index.js
+++ b/node_modules/drizzle-kit/index.js
@@ -6286,6 +6286,7 @@ var init_mysqlSchema = __esm({
       name: stringType(),
       columns: stringType().array(),
       isUnique: booleanType(),
+      isFulltext: booleanType().optional(),
       using: enumType(["btree", "hash"]).optional(),
       algorithm: enumType(["default", "inplace", "copy"]).optional(),
       lock: enumType(["default", "none", "shared", "exclusive"]).optional()
@@ -6394,14 +6395,16 @@ var init_mysqlSchema = __esm({
       squashIdx: (idx) => {
         var _a, _b, _c;
         index.parse(idx);
-        return `${idx.name};${idx.columns.join(",")};${idx.isUnique};${(_a = idx.using) != null ? _a : ""};${(_b = idx.algorithm) != null ? _b : ""};${(_c = idx.lock) != null ? _c : ""}`;
+    
+        return `${idx.name};${idx.columns.join(",")};${idx.isUnique};${idx.isFulltext};${(_a = idx.using) != null ? _a : ""};${(_b = idx.algorithm) != null ? _b : ""};${(_c = idx.lock) != null ? _c : ""}`;
       },
       unsquashIdx: (input) => {
-        const [name, columnsString, isUnique, using, algorithm, lock] = input.split(";");
+        const [name, columnsString, isUnique, isFulltext, using, algorithm, lock] = input.split(";");
         const destructed = {
           name,
           columns: columnsString.split(","),
           isUnique: isUnique === "true",
+          isFulltext: isFulltext === "true",
           using: using ? using : void 0,
           algorithm: algorithm ? algorithm : void 0,
           lock: lock ? lock : void 0
@@ -21419,6 +21422,7 @@ var init_mysqlSerializer = __esm({
             name,
             columns: indexColumns,
             isUnique: (_a = value.config.unique) != null ? _a : false,
+            isFulltext: (_a = value.config.fulltext) === true ? _a : undefined,
             using: value.config.using,
             algorithm: value.config.algorythm,
             lock: value.config.lock
@@ -21615,6 +21619,7 @@ var init_mysqlSerializer = __esm({
         const constraintName = idxRow["INDEX_NAME"];
         const columnName = idxRow["COLUMN_NAME"];
         const isUnique = idxRow["NON_UNIQUE"] === 0;
+        const isFulltext = idxRow["INDEX_TYPE"] === "FULLTEXT" ? true : undefined;
         const tableInResult = result[tableName];
         if (typeof tableInResult === "undefined")
           continue;
@@ -21630,7 +21635,8 @@ var init_mysqlSerializer = __esm({
           tableInResult.indexes[constraintName] = {
             name: constraintName,
             columns: [columnName],
-            isUnique
+            isUnique,
+            isFulltext
           };
         }
       }
@@ -24905,7 +24911,6 @@ ${BREAKPOINT}ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT ${statement.newCo
       }
       convert(statement) {
         const { tableName, columnName } = statement;
-        console.log(statement);
         const tableNameWithSchema = statement.schema ? `"${statement.schema}"."${statement.tableName}"` : `"${statement.tableName}"`;
         return `ALTER TABLE ${tableNameWithSchema} ADD PRIMARY KEY ("${columnName}");`;
       }
@@ -25140,10 +25145,10 @@ ${BREAKPOINT}ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT ${statement.newCo
         return statement.type === "create_index" && dialect6 === "mysql";
       }
       convert(statement) {
-        const { name, columns, isUnique } = MySqlSquasher.unsquashIdx(
+        const { name, columns, isUnique, isFulltext } = MySqlSquasher.unsquashIdx(
           statement.data
         );
-        const indexPart = isUnique ? "UNIQUE INDEX" : "INDEX";
+        const indexPart = isFulltext ? "FULLTEXT INDEX":  isUnique ? "UNIQUE INDEX" : "INDEX";
         const value = columns.map((it) => `\`${it}\``).join(",");
         return `CREATE ${indexPart} \`${name}\` ON \`${statement.tableName}\` (${value});`;
       }

[svyslf]

Any update on this feature? @corbanvilla

[Angelelz]

This is a very interesting feature that would benefit an app I'm working on. Is there any update on this? @corbanvilla

[Angelelz]

Closing in favor of #1030