@minatojs/driver-sqlite - npm Package Compare versions

Comparing version 3.6.3 to 3.7.0

lib/index.d.ts

@@ -1,3 +0,1 @@
-/// <reference types="node" />
-/// <reference types="node" />
 import { Database, Driver, Eval, Selection } from '@minatojs/core';
@@ -24,9 +22,6 @@ import { Builder } from '@minatojs/sql-utils';
     sql: Builder;
-    writeTask?: NodeJS.Timeout;
-    sqlite: init.SqlJsStatic;
+    beforeUnload?: () => void;
     constructor(database: Database, config: SQLiteDriver.Config);
     /** synchronize table schema */
     prepare(table: string, dropKeys?: string[]): Promise<void>;
-    init(buffer: ArrayLike<number> | null): void;
-    load(): Promise<Buffer | null>;
     start(): Promise<void>;
@@ -33,0 +28,0 @@ stop(): Promise<void>;

lib/index.js

@@ -93,2 +93,12 @@ "use strict";
     this.evalOperators.$if = (args) => `iif(${args.map((arg) => this.parseEval(arg)).join(", ")})`;
+    this.evalOperators.$concat = (args) => `(${args.map((arg) => this.parseEval(arg)).join("||")})`;
+    this.evalOperators.$length = (expr) => this.createAggr(expr, (value) => `count(${value})`, (value) => {
+      if (this.state.sqlType === "json") {
+        this.state.sqlType = "raw";
+        return `${this.jsonLength(value)}`;
+      } else {
+        this.state.sqlType = "raw";
+        return `iif(${value}, LENGTH(${value}) - LENGTH(REPLACE(${value}, ${this.escape(",")}, ${this.escape("")})) + 1, 0)`;
+      }
+    });
     this.define({
@@ -121,4 +131,35 @@ types: ["boolean"],
   createElementQuery(key, value) {
-    return `(',' || ${key} || ',') LIKE ${this.escape("%," + value + ",%")}`;
+    var _a;
+    if (((_a = this.state.sqlTypes) == null ? void 0 : _a[this.unescapeId(key)]) === "json") {
+      return this.jsonContains(key, this.quote(JSON.stringify(value)));
+    } else {
+      return `(',' || ${key} || ',') LIKE ${this.escape("%," + value + ",%")}`;
+    }
   }
+  jsonLength(value) {
+    return `json_array_length(${value})`;
+  }
+  jsonContains(obj, value) {
+    return `json_array_contains(${obj}, ${value})`;
+  }
+  jsonUnquote(value, pure = false) {
+    return value;
+  }
+  createAggr(expr, aggr, nonaggr) {
+    if (!this.state.group && !nonaggr) {
+      const value = this.parseEval(expr, false);
+      return `(select ${aggr((0, import_sql_utils.escapeId)("value"))} from json_each(${value}) ${(0, import_core.randomId)()})`;
+    } else {
+      return super.createAggr(expr, aggr, nonaggr);
+    }
+  }
+  groupArray(value) {
+    const res = this.state.sqlType === "json" ? `('[' || group_concat(${value}) || ']')` : `('[' || group_concat(json_quote(${value})) || ']')`;
+    this.state.sqlType = "json";
+    return `ifnull(${res}, json_array())`;
+  }
+  transformJsonField(obj, path) {
+    this.state.sqlType = "raw";
+    return `json_extract(${obj}, '$${path}')`;
+  }
 };
@@ -142,4 +183,3 @@ __name(_SQLiteBuilder, "SQLiteBuilder");
     __publicField(this, "sql");
-    __publicField(this, "writeTask");
-    __publicField(this, "sqlite");
+    __publicField(this, "beforeUnload");
     this.sql = new SQLiteBuilder();
@@ -243,21 +283,21 @@ }
   }
-  init(buffer) {
-    this.db = new this.sqlite.Database(buffer);
+  async start() {
+    const isBrowser = process.env.KOISHI_ENV === "browser";
+    const sqlite = await (0, import_sql.default)({
+      locateFile: (file) => process.env.KOISHI_BASE ? process.env.KOISHI_BASE + "/" + file : isBrowser ? "/modules/@koishijs/plugin-database-sqlite/" + file : require.resolve("@minatojs/sql.js/dist/" + file)
+    });
+    if (!isBrowser || this.config.path === ":memory:") {
+      this.db = new sqlite.Database(this.config.path);
+    } else {
+      const buffer = await import_fs.promises.readFile(this.config.path).catch(() => null);
+      this.db = new sqlite.Database(this.config.path, buffer);
+      if (isBrowser) {
+        window.addEventListener("beforeunload", this.beforeUnload = () => {
+          __privateMethod(this, _export, export_fn).call(this);
+        });
+      }
+    }
     this.db.create_function("regexp", (pattern, str) => +new RegExp(pattern).test(str));
+    this.db.create_function("json_array_contains", (array, value) => +JSON.parse(array).includes(JSON.parse(value)));
   }
-  async load() {
-    if (this.config.path === ":memory:")
-      return null;
-    return import_fs.promises.readFile(this.config.path).catch(() => null);
-  }
-  async start() {
-    const [sqlite, buffer] = await Promise.all([
-      (0, import_sql.default)({
-        locateFile: (file) => process.env.KOISHI_BASE ? process.env.KOISHI_BASE + "/" + file : process.env.KOISHI_ENV === "browser" ? "/modules/@koishijs/plugin-database-sqlite/" + file : require.resolve("@minatojs/sql.js/dist/" + file)
-      }),
-      this.load()
-    ]);
-    this.sqlite = sqlite;
-    this.init(buffer);
-  }
   async stop() {
@@ -267,2 +307,6 @@ var _a;
     (_a = this.db) == null ? void 0 : _a.close();
+    if (this.beforeUnload) {
+      this.beforeUnload();
+      window.removeEventListener("beforeunload", this.beforeUnload);
+    }
   }
@@ -278,5 +322,3 @@ async drop(table) {
   async stats() {
-    const data = this.db.export();
-    this.init(data);
-    const stats = { size: data.byteLength, tables: {} };
+    const stats = { size: this.db.size(), tables: {} };
     const tableNames = __privateMethod(this, _all, all_fn).call(this, 'SELECT name FROM sqlite_master WHERE type="table" ORDER BY name;');
@@ -308,6 +350,6 @@ const dbstats = __privateMethod(this, _all, all_fn).call(this, 'SELECT name, pgsize as size FROM "dbstat" WHERE aggregate=TRUE;');
     const builder = new SQLiteBuilder(sel.tables);
-    const output = builder.parseEval(expr);
-    const inner = builder.get(sel.table, true);
+    const inner = builder.get(sel.table, true, true);
+    const output = builder.parseEval(expr, false);
     const { value } = __privateMethod(this, _get, get_fn).call(this, `SELECT ${output} AS value FROM ${inner}`);
-    return value;
+    return builder.load(value);
   }
@@ -398,3 +440,2 @@ async set(sel, update) {
   import_fs.promises.writeFile(this.config.path, data);
-  this.init(data);
 }, "#export");
@@ -405,6 +446,2 @@ _run = new WeakSet();
   const result = callback == null ? void 0 : callback();
-  if (this.config.path) {
-    clearTimeout(this.writeTask);
-    this.writeTask = setTimeout(() => __privateMethod(this, _export, export_fn).call(this), 0);
-  }
   return result;
@@ -411,0 +448,0 @@ }, "#run");

package.json

 {
   "name": "@minatojs/driver-sqlite",
-  "version": "3.6.3",
+  "version": "3.7.0",
   "description": "SQLite Driver for Minato",
@@ -29,13 +29,13 @@ "main": "lib/index.js",
   "peerDependencies": {
-    "@minatojs/core": "^2.4.3"
+    "@minatojs/core": "^2.5.0"
   },
   "devDependencies": {
-    "@minatojs/tests": "^1.6.1"
+    "@minatojs/tests": "^1.7.0"
   },
   "dependencies": {
-    "@minatojs/sql-utils": "^4.0.7",
-    "@minatojs/sql.js": "^2.0.1",
+    "@minatojs/sql-utils": "^4.1.0",
+    "@minatojs/sql.js": "^3.1.0",
     "cosmokit": "^1.5.1",
-    "reggol": "^1.6.2"
+    "reggol": "^1.6.3"
   }
-}
+}

src/index.ts

 import { deepEqual, Dict, difference, isNullable, makeArray } from 'cosmokit'
-import { Database, Driver, Eval, executeUpdate, Field, Model, Selection } from '@minatojs/core'
+import { Database, Driver, Eval, executeUpdate, Field, Model, randomId, Selection } from '@minatojs/core'
 import { Builder, escapeId } from '@minatojs/sql-utils'
@@ -54,2 +54,12 @@ import { promises as fs } from 'fs'
     this.evalOperators.$if = (args) => `iif(${args.map(arg => this.parseEval(arg)).join(', ')})`
+    this.evalOperators.$concat = (args) => `(${args.map(arg => this.parseEval(arg)).join('||')})`
+    this.evalOperators.$length = (expr) => this.createAggr(expr, value => `count(${value})`, value => {
+      if (this.state.sqlType === 'json') {
+        this.state.sqlType = 'raw'
+        return `${this.jsonLength(value)}`
+      } else {
+        this.state.sqlType = 'raw'
+        return `iif(${value}, LENGTH(${value}) - LENGTH(REPLACE(${value}, ${this.escape(',')}, ${this.escape('')})) + 1, 0)`
+      }
+    })
 
@@ -87,4 +97,40 @@ this.define<boolean, number>({
   protected createElementQuery(key: string, value: any) {
-    return `(',' || ${key} || ',') LIKE ${this.escape('%,' + value + ',%')}`
+    if (this.state.sqlTypes?.[this.unescapeId(key)] === 'json') {
+      return this.jsonContains(key, this.quote(JSON.stringify(value)))
+    } else {
+      return `(',' || ${key} || ',') LIKE ${this.escape('%,' + value + ',%')}`
+    }
   }
+
+  protected jsonLength(value: string) {
+    return `json_array_length(${value})`
+  }
+
+  protected jsonContains(obj: string, value: string) {
+    return `json_array_contains(${obj}, ${value})`
+  }
+
+  protected jsonUnquote(value: string, pure: boolean = false) {
+    return value
+  }
+
+  protected createAggr(expr: any, aggr: (value: string) => string, nonaggr?: (value: string) => string) {
+    if (!this.state.group && !nonaggr) {
+      const value = this.parseEval(expr, false)
+      return `(select ${aggr(escapeId('value'))} from json_each(${value}) ${randomId()})`
+    } else {
+      return super.createAggr(expr, aggr, nonaggr)
+    }
+  }
+
+  protected groupArray(value: string) {
+    const res = this.state.sqlType === 'json' ? `('[' || group_concat(${value}) || ']')` : `('[' || group_concat(json_quote(${value})) || ']')`
+    this.state.sqlType = 'json'
+    return `ifnull(${res}, json_array())`
+  }
+
+  protected transformJsonField(obj: string, path: string) {
+    this.state.sqlType = 'raw'
+    return `json_extract(${obj}, '$${path}')`
+  }
 }
@@ -95,4 +141,3 @@
   sql: Builder
-  writeTask?: NodeJS.Timeout
-  sqlite!: init.SqlJsStatic
+  beforeUnload?: () => void
 
@@ -205,27 +250,26 @@ constructor(database: Database, public config: SQLiteDriver.Config) {
 
-  init(buffer: ArrayLike<number> | null) {
-    this.db = new this.sqlite.Database(buffer)
+  async start() {
+    const isBrowser = process.env.KOISHI_ENV === 'browser'
+    const sqlite = await init({
+      locateFile: (file: string) => process.env.KOISHI_BASE
+        ? process.env.KOISHI_BASE + '/' + file
+        : isBrowser
+          ? '/modules/@koishijs/plugin-database-sqlite/' + file
+          : require.resolve('@minatojs/sql.js/dist/' + file),
+    })
+    if (!isBrowser || this.config.path === ':memory:') {
+      this.db = new sqlite.Database(this.config.path)
+    } else {
+      const buffer = await fs.readFile(this.config.path).catch(() => null)
+      this.db = new sqlite.Database(this.config.path, buffer)
+      if (isBrowser) {
+        window.addEventListener('beforeunload', this.beforeUnload = () => {
+          this.#export()
+        })
+      }
+    }
     this.db.create_function('regexp', (pattern, str) => +new RegExp(pattern).test(str))
+    this.db.create_function('json_array_contains', (array, value) => +(JSON.parse(array) as any[]).includes(JSON.parse(value)))
   }
 
-  async load() {
-    if (this.config.path === ':memory:') return null
-    return fs.readFile(this.config.path).catch(() => null)
-  }
-
-  async start() {
-    const [sqlite, buffer] = await Promise.all([
-      init({
-        locateFile: (file: string) => process.env.KOISHI_BASE
-          ? process.env.KOISHI_BASE + '/' + file
-          : process.env.KOISHI_ENV === 'browser'
-            ? '/modules/@koishijs/plugin-database-sqlite/' + file
-            : require.resolve('@minatojs/sql.js/dist/' + file),
-      }),
-      this.load(),
-    ])
-    this.sqlite = sqlite
-    this.init(buffer)
-  }
-
   #joinKeys(keys?: string[]) {
@@ -238,2 +282,6 @@ return keys?.length ? keys.map(key => `\`${key}\``).join(', ') : '*'
     this.db?.close()
+    if (this.beforeUnload) {
+      this.beforeUnload()
+      window.removeEventListener('beforeunload', this.beforeUnload)
+    }
   }
@@ -272,3 +320,2 @@
     fs.writeFile(this.config.path, data)
-    this.init(data)
   }
@@ -279,6 +326,2 @@
     const result = callback?.()
-    if (this.config.path) {
-      clearTimeout(this.writeTask)
-      this.writeTask = setTimeout(() => this.#export(), 0)
-    }
     return result
@@ -296,5 +339,3 @@ }
   async stats() {
-    const data = this.db.export()
-    this.init(data)
-    const stats: Driver.Stats = { size: data.byteLength, tables: {} }
+    const stats: Driver.Stats = { size: this.db.size(), tables: {} }
     const tableNames: { name: string }[] = this.#all('SELECT name FROM sqlite_master WHERE type="table" ORDER BY name;')
@@ -327,6 +368,6 @@ const dbstats: { name: string; size: number }[] = this.#all('SELECT name, pgsize as size FROM "dbstat" WHERE aggregate=TRUE;')
     const builder = new SQLiteBuilder(sel.tables)
-    const output = builder.parseEval(expr)
-    const inner = builder.get(sel.table as Selection, true)
+    const inner = builder.get(sel.table as Selection, true, true)
+    const output = builder.parseEval(expr, false)
     const { value } = this.#get(`SELECT ${output} AS value FROM ${inner}`)
-    return value
+    return builder.load(value)
   }
@@ -333,0 +374,0 @@

No alert changes

Improved metrics

Total package byte prevSize

48402

increased by10.98%

Lines of code

886

increased by8.45%

Dependency changes

• + Added@minatojs/sql.js@3.1.0(transitive)

• - Removed@minatojs/sql.js@2.0.1(transitive)

• Updated@minatojs/sql-utils@^4.1.0

• Updated@minatojs/sql.js@^3.1.0

• Updatedreggol@^1.6.3