@balena/abstract-sql-compiler - npm Package Compare versions

Comparing version 9.0.4-build-renovate-lint-staged-15-x-7e2725130693ea2d82ed5a9b1f2c29478fd167c9-1 to 9.1.0-build-partial-unique-index-12e3a0c5b8ab677e073e7ab524d980a431a558d6-1

CHANGELOG.md

@@ -7,5 +7,6 @@ # Change Log
 
-## 9.0.4 - 2023-10-17
+## 9.1.0 - 2023-11-26
 
-* Update dependency lint-staged to v15 [Self-hosted Renovate Bot]
+* Add support for unique indexes with NOT DISTINCT NULLS [Thodoris Greasidis]
+* Add support for partial unique indexes [Thodoris Greasidis]
 
@@ -12,0 +13,0 @@ ## 9.0.3 - 2023-08-09

out/AbstractSQLCompiler.d.ts

@@ -270,2 +270,10 @@ export declare const enum Engines {
 }
+export interface Index {
+    type: string;
+    fields: string[];
+    name?: string;
+    description?: string;
+    nulls?: string;
+    predicate?: BooleanTypeNodes;
+}
 export interface Check {
@@ -288,6 +296,3 @@ description?: string;
     fields: AbstractSqlField[];
-    indexes: Array<{
-        type: string;
-        fields: string[];
-    }>;
+    indexes: Index[];
     primitive: false | string;
@@ -294,0 +299,0 @@ triggers?: Trigger[];

out/AbstractSQLCompiler.js

@@ -177,2 +177,3 @@ "use strict";
         const createSqlElements = [];
+        const createIndexes = [];
         for (const field of table.fields) {
@@ -226,3 +227,19 @@ const { fieldName, references, dataType, computed } = field;
         for (const index of table.indexes) {
-            createSqlElements.push(index.type + '("' + index.fields.join('", "') + '")');
+            const nullsSql = index.nulls != null ? ` NULLS ${index.nulls}` : '';
+            if (index.predicate == null) {
+                createSqlElements.push(index.type + nullsSql + '("' + index.fields.join('", "') + '")');
+                continue;
+            }
+            if (index.name == null) {
+                throw new Error('No name provided for partial index');
+            }
+            const comment = index.description
+                ? `-- ${index.description.split(/\r?\n/).join('\n-- ')}\n`
+                : '';
+            const whereSql = compileRule(index.predicate, engine, true);
+            createIndexes.push(`\
+${comment}\
+CREATE ${index.type} INDEX IF NOT EXISTS "${index.name}"
+ON "${table.name}" ("${index.fields.join('", "')}")${nullsSql}
+WHERE (${whereSql});`);
         }
@@ -276,2 +293,3 @@ if (table.checks) {
 );`,
+                ...createIndexes,
                 ...createTriggers,
@@ -278,0 +296,0 @@ ],

package.json

 {
   "name": "@balena/abstract-sql-compiler",
-  "version": "9.0.4-build-renovate-lint-staged-15-x-7e2725130693ea2d82ed5a9b1f2c29478fd167c9-1",
+  "version": "9.1.0-build-partial-unique-index-12e3a0c5b8ab677e073e7ab524d980a431a558d6-1",
   "description": "A translator for abstract sql into sql.",
@@ -36,3 +36,3 @@ "main": "out/AbstractSQLCompiler.js",
     "husky": "^8.0.3",
-    "lint-staged": "^15.0.0",
+    "lint-staged": "^13.2.1",
     "mocha": "^10.2.0",
@@ -65,4 +65,4 @@ "ts-node": "^10.9.1",
   "versionist": {
-    "publishedAt": "2023-10-17T18:44:54.473Z"
+    "publishedAt": "2023-11-26T14:25:25.911Z"
   }
 }

src/AbstractSQLCompiler.ts

@@ -451,2 +451,10 @@ export const enum Engines {
 }
+export interface Index {
+	type: string;
+	fields: string[];
+	name?: string;
+	description?: string;
+	nulls?: string;
+	predicate?: BooleanTypeNodes;
+}
 export interface Check {
@@ -469,6 +477,3 @@ description?: string;
 	fields: AbstractSqlField[];
-	indexes: Array<{
-		type: string;
-		fields: string[];
-	}>;
+	indexes: Index[];
 	primitive: false | string;
@@ -816,2 +821,3 @@ triggers?: Trigger[];
 		const createSqlElements: string[] = [];
+		const createIndexes: string[] = [];
 
@@ -875,5 +881,26 @@ for (const field of table.fields) {
 		for (const index of table.indexes) {
-			createSqlElements.push(
-				index.type + '("' + index.fields.join('", "') + '")',
+			const nullsSql = index.nulls != null ? ` NULLS ${index.nulls}` : '';
+			// Non-partial indexes are added directly to the CREATE TABLE statement
+			if (index.predicate == null) {
+				createSqlElements.push(
+					index.type + nullsSql + '("' + index.fields.join('", "') + '")',
+				);
+				continue;
+			}
+			if (index.name == null) {
+				throw new Error('No name provided for partial index');
+			}
+			const comment = index.description
+				? `-- ${index.description.split(/\r?\n/).join('\n-- ')}\n`
+				: '';
+			const whereSql = compileRule(
+				index.predicate as AbstractSqlQuery,
+				engine,
+				true,
 			);
+			createIndexes.push(`\
+${comment}\
+CREATE ${index.type} INDEX IF NOT EXISTS "${index.name}"
+ON "${table.name}" ("${index.fields.join('", "')}")${nullsSql}
+WHERE (${whereSql});`);
 		}
@@ -935,2 +962,3 @@
 );`,
+				...createIndexes,
 				...createTriggers,
@@ -937,0 +965,0 @@ ],

No alert changes

Improved metrics

Total package byte prevSize

837411

0.83%

Lines of code

17791

0.29%

No dependency changes