@minatojs/sql-utils - npm Package Compare versions

Comparing version 4.2.0 to 4.2.1

lib/index.d.ts

@@ -18,3 +18,3 @@ import { Dict } from 'cosmokit';
 }
-type SQLType = 'raw' | 'json' | 'list';
+type SQLType = 'raw' | 'json' | 'list' | 'date' | 'time' | 'timestamp';
 interface State {
@@ -37,2 +37,3 @@ sqlType?: SQLType;
     protected modifiedTable?: string;
+    private readonly _timezone;
     constructor(tables?: Dict<Model<any>> | undefined);
@@ -39,0 +40,0 @@ protected unescapeId(value: string): string;

lib/index.js

@@ -93,2 +93,12 @@ "use strict";
       $divide: this.binary("/"),
+      $modulo: this.binary("%"),
+      // mathemetic
+      $abs: (arg) => `abs(${this.parseEval(arg)})`,
+      $floor: (arg) => `floor(${this.parseEval(arg)})`,
+      $ceil: (arg) => `ceil(${this.parseEval(arg)})`,
+      $round: (arg) => `round(${this.parseEval(arg)})`,
+      $exp: (arg) => `exp(${this.parseEval(arg)})`,
+      $log: (args) => `log(${args.filter((x) => !(0, import_cosmokit.isNullable)(x)).map((arg) => this.parseEval(arg)).reverse().join(", ")})`,
+      $power: (args) => `power(${args.map((arg) => this.parseEval(arg)).join(", ")})`,
+      $random: () => `rand()`,
       // string
@@ -110,7 +120,14 @@ $concat: (args) => `concat(${args.map((arg) => this.parseEval(arg)).join(", ")})`,
       $nin: ([key, value]) => this.createMemberQuery(this.parseEval(key), value, " NOT"),
+      // typecast
+      $number: (arg) => {
+        const value = this.parseEval(arg);
+        const res = this.state.sqlType === "raw" ? `(0+${value})` : this.state.sqlType === "time" ? `unix_timestamp(convert_tz(addtime('1970-01-01 00:00:00', ${value}), '${this._timezone}', '+0:00'))` : `unix_timestamp(convert_tz(${value}, '${this._timezone}', '+0:00'))`;
+        this.state.sqlType = "raw";
+        return `ifnull(${res}, 0)`;
+      },
       // aggregation
       $sum: (expr) => this.createAggr(expr, (value) => `ifnull(sum(${value}), 0)`),
       $avg: (expr) => this.createAggr(expr, (value) => `avg(${value})`),
-      $min: (expr) => this.createAggr(expr, (value) => `(0+min(${value}))`),
-      $max: (expr) => this.createAggr(expr, (value) => `(0+max(${value}))`),
+      $min: (expr) => this.createAggr(expr, (value) => `min(${value})`),
+      $max: (expr) => this.createAggr(expr, (value) => `max(${value})`),
       $count: (expr) => this.createAggr(expr, (value) => `count(distinct ${value})`),
@@ -140,2 +157,3 @@ $length: (expr) => this.createAggr(expr, (value) => `count(${value})`, (value) => {
   modifiedTable;
+  _timezone = `+${(/* @__PURE__ */ new Date()).getTimezoneOffset() / -60}:00`.replace("+-", "-");
   unescapeId(value) {
@@ -205,5 +223,7 @@ return value.slice(1, value.length - 1);
       return `json_unquote(${value})`;
-    const res = this.state.sqlType === "json" ? `json_unquote(${value})` : value;
-    this.state.sqlType = "raw";
-    return res;
+    if (this.state.sqlType === "json") {
+      this.state.sqlType = "raw";
+      return `json_unquote(${value})`;
+    }
+    return value;
   }
@@ -213,5 +233,7 @@ jsonQuote(value, pure = false) {
       return `cast(${value} as json)`;
-    const res = this.state.sqlType === "raw" ? `cast(${value} as json)` : value;
-    this.state.sqlType = "json";
-    return res;
+    if (this.state.sqlType !== "json") {
+      this.state.sqlType = "json";
+      return `cast(${value} as json)`;
+    }
+    return value;
   }
@@ -223,3 +245,2 @@ createAggr(expr, aggr, nonaggr) {
       this.state.group = true;
-      this.state.sqlType = "raw";
       return value;
@@ -229,3 +250,2 @@ } else {
       const res = nonaggr ? nonaggr(value) : `(select ${aggr(`json_unquote(${this.escapeId("value")})`)} from json_table(${value}, '$[*]' columns (value json path '$')) ${(0, import_core.randomId)()})`;
-      this.state.sqlType = "raw";
       return res;
@@ -364,3 +384,10 @@ }
       this.state.sqlTypes = Object.fromEntries(Object.entries(model.fields).map(([key, field]) => {
-        return [key, field.type === "json" ? "json" : field.type === "list" ? "list" : "raw"];
+        let sqlType = "raw";
+        if (field.type === "json")
+          sqlType = "json";
+        else if (field.type === "list")
+          sqlType = "list";
+        else if (import_core.Field.date.includes(field.type))
+          sqlType = field.type;
+        return [key, sqlType];
       }));
@@ -367,0 +394,0 @@ } else if (table instanceof import_core.Selection) {

package.json

 {
   "name": "@minatojs/sql-utils",
-  "version": "4.2.0",
+  "version": "4.2.1",
   "description": "SQL Utilities for Minato",
@@ -33,3 +33,3 @@ "main": "lib/index.js",
   "peerDependencies": {
-    "@minatojs/core": "^2.6.0"
+    "@minatojs/core": "^2.8.0"
   },
@@ -36,0 +36,0 @@ "dependencies": {

src/index.ts

@@ -24,3 +24,3 @@ import { Dict, isNullable } from 'cosmokit'
 
-type SQLType = 'raw' | 'json' | 'list'
+type SQLType = 'raw' | 'json' | 'list' | 'date' | 'time' | 'timestamp'
 
@@ -45,2 +45,4 @@ interface State {
 
+  private readonly _timezone = `+${(new Date()).getTimezoneOffset() / -60}:00`.replace('+-', '-')
+
   constructor(public tables?: Dict<Model>) {
@@ -109,3 +111,14 @@ this.queryOperators = {
       $divide: this.binary('/'),
+      $modulo: this.binary('%'),
 
+      // mathemetic
+      $abs: (arg) => `abs(${this.parseEval(arg)})`,
+      $floor: (arg) => `floor(${this.parseEval(arg)})`,
+      $ceil: (arg) => `ceil(${this.parseEval(arg)})`,
+      $round: (arg) => `round(${this.parseEval(arg)})`,
+      $exp: (arg) => `exp(${this.parseEval(arg)})`,
+      $log: (args) => `log(${args.filter(x => !isNullable(x)).map(arg => this.parseEval(arg)).reverse().join(', ')})`,
+      $power: (args) => `power(${args.map(arg => this.parseEval(arg)).join(', ')})`,
+      $random: () => `rand()`,
+
       // string
@@ -131,7 +144,17 @@ $concat: (args) => `concat(${args.map(arg => this.parseEval(arg)).join(', ')})`,
 
+      // typecast
+      $number: (arg) => {
+        const value = this.parseEval(arg)
+        const res = this.state.sqlType === 'raw' ? `(0+${value})`
+          : this.state.sqlType === 'time' ? `unix_timestamp(convert_tz(addtime('1970-01-01 00:00:00', ${value}), '${this._timezone}', '+0:00'))`
+            : `unix_timestamp(convert_tz(${value}, '${this._timezone}', '+0:00'))`
+        this.state.sqlType = 'raw'
+        return `ifnull(${res}, 0)`
+      },
+
       // aggregation
       $sum: (expr) => this.createAggr(expr, value => `ifnull(sum(${value}), 0)`),
       $avg: (expr) => this.createAggr(expr, value => `avg(${value})`),
-      $min: (expr) => this.createAggr(expr, value => `(0+min(${value}))`),
-      $max: (expr) => this.createAggr(expr, value => `(0+max(${value}))`),
+      $min: (expr) => this.createAggr(expr, value => `min(${value})`),
+      $max: (expr) => this.createAggr(expr, value => `max(${value})`),
       $count: (expr) => this.createAggr(expr, value => `count(distinct ${value})`),
@@ -222,5 +245,7 @@ $length: (expr) => this.createAggr(expr, value => `count(${value})`, value => {
     if (pure) return `json_unquote(${value})`
-    const res = this.state.sqlType === 'json' ? `json_unquote(${value})` : value
-    this.state.sqlType = 'raw'
-    return res
+    if (this.state.sqlType === 'json') {
+      this.state.sqlType = 'raw'
+      return `json_unquote(${value})`
+    }
+    return value
   }
@@ -230,5 +255,7 @@
     if (pure) return `cast(${value} as json)`
-    const res = this.state.sqlType === 'raw' ? `cast(${value} as json)` : value
-    this.state.sqlType = 'json'
-    return res
+    if (this.state.sqlType !== 'json') {
+      this.state.sqlType = 'json'
+      return `cast(${value} as json)`
+    }
+    return value
   }
@@ -241,3 +268,4 @@
       this.state.group = true
-      this.state.sqlType = 'raw'
+      // pass through sqlType of elements for variant types
+      // ok to pass json on raw since mysql can treat them properly
       return value
@@ -248,3 +276,2 @@ } else {
         : `(select ${aggr(`json_unquote(${this.escapeId('value')})`)} from json_table(${value}, '$[*]' columns (value json path '$')) ${randomId()})`
-      this.state.sqlType = 'raw'
       return res
@@ -397,3 +424,7 @@ }
       this.state.sqlTypes = Object.fromEntries(Object.entries(model.fields).map(([key, field]) => {
-        return [key, field!.type === 'json' ? 'json' : field!.type === 'list' ? 'list' : 'raw']
+        let sqlType: SQLType = 'raw'
+        if (field!.type === 'json') sqlType = 'json'
+        else if (field!.type === 'list') sqlType = 'list'
+        else if (Field.date.includes(field!.type)) sqlType = field!.type as SQLType
+        return [key, sqlType]
       }))
@@ -400,0 +431,0 @@ } else if (table instanceof Selection) {

No alert changes

Improved metrics

Total package byte prevSize

59181

increased by6.54%

Lines of code

1056

increased by5.6%

No dependency changes