pg-mem - npm Package Compare versions

Comparing version 1.0.13 to 1.0.14

btree-index.d.ts

@@ -14,3 +14,3 @@ import { IValue, _IIndex, _ITable, IndexKey, CreateIndexColDef, _Transaction, _Explainer, _IndexExplanation, IndexExpression, IndexOp, Stats } from './interfaces-private';
     compare(_a: any, _b: any): number;
-    private buildKey;
+    buildKey(raw: any, t: _Transaction): any[];
     private bin;
@@ -17,0 +17,0 @@ private setBin;

interfaces-private.d.ts

@@ -203,2 +203,8 @@ import { IMemoryDb, IMemoryTable, DataType, IType, TableEvent, GlobalEvent, ISchema, SchemaField, MemoryDbOptions } from './interfaces';
 }
+export declare type OnConflictHandler = {
+    ignore: 'all' | _IIndex;
+} | {
+    onIndex: _IIndex;
+    update: (item: any, excluded: any) => void;
+};
 export interface _ITable<T = any> extends IMemoryTable {
@@ -210,3 +216,3 @@ readonly hidden: boolean;
     readonly columnDefs: _Column[];
-    insert(t: _Transaction, toInsert: T): T;
+    insert(t: _Transaction, toInsert: T, onConflict?: OnConflictHandler): T;
     delete(t: _Transaction, toDelete: T): void;
@@ -224,2 +230,3 @@ update(t: _Transaction, toUpdate: T): T;
     onChange(columns: string[], check: ChangeHandler<T>): any;
+    getIndex(...forValues: IValue[]): _IIndex;
 }
@@ -298,2 +305,3 @@ export declare type ChangeHandler<T> = (old: T, neu: T, t: _Transaction) => void;
     setOrigin(origin: _ISelection): IValue<TRaw>;
+    clone(): IValue<any>;
     explain(e: _Explainer): _ExprExplanation;
@@ -307,2 +315,3 @@ }
 export interface _IIndex<T = any> {
+    readonly unique?: boolean;
     readonly expressions: IndexExpression[];
@@ -309,0 +318,0 @@ /** Returns a measure of how many items will be returned by this op */

package.json

 {
   "name": "pg-mem",
-  "version": "1.0.13",
+  "version": "1.0.14",
   "description": "A memory version of postgres",
@@ -5,0 +5,0 @@ "main": "index.js",

parser/syntax/ast.d.ts

@@ -36,3 +36,10 @@ import { IType } from '../../interfaces';
     select?: SelectStatement;
+    onConflict?: OnConflictAction;
 }
+export interface OnConflictAction {
+    on?: Expr[];
+    do: 'do nothing' | {
+        sets: SetStatement[];
+    };
+}
 export interface AlterTableStatement {
@@ -39,0 +46,0 @@ type: 'alter table';

src/btree-index.ts

@@ -97,3 +97,3 @@ import { IValue, _IIndex, _ITable, getId, IndexKey, CreateIndexColDef, _Transaction, _Explainer, _IndexExplanation, IndexExpression, IndexOp, Stats } from './interfaces-private';
 
-    private buildKey(raw: any, t: _Transaction) {
+    buildKey(raw: any, t: _Transaction) {
         return this.expressions.map(k => k.get(raw, t));
@@ -100,0 +100,0 @@ }

src/interfaces-private.ts

@@ -241,3 +241,6 @@ import { IMemoryDb, IMemoryTable, DataType, IType, TableEvent, GlobalEvent, ISchema, SchemaField, MemoryDbOptions } from './interfaces';
 }
-
+export type OnConflictHandler = { ignore: 'all' | _IIndex } | {
+    onIndex: _IIndex;
+    update: (item: any, excluded: any) => void;
+}
 export interface _ITable<T = any> extends IMemoryTable {
@@ -250,3 +253,3 @@
     readonly columnDefs: _Column[];
-    insert(t: _Transaction, toInsert: T): T;
+    insert(t: _Transaction, toInsert: T, onConflict?: OnConflictHandler): T;
     delete(t: _Transaction, toDelete: T): void;
@@ -264,2 +267,3 @@ update(t: _Transaction, toUpdate: T): T;
     onChange(columns: string[], check: ChangeHandler<T>);
+    getIndex(...forValues: IValue[]): _IIndex;
 }
@@ -360,2 +364,3 @@
     setOrigin(origin: _ISelection): IValue<TRaw>;
+    clone(): IValue<any>;
 
@@ -371,2 +376,3 @@ explain(e: _Explainer): _ExprExplanation;
 export interface _IIndex<T = any> {
+    readonly unique?: boolean;
     readonly expressions: IndexExpression[];
@@ -373,0 +379,0 @@

src/parser/syntax/ast.ts

@@ -52,4 +52,12 @@ import { IType } from '../../interfaces';
     select?: SelectStatement;
+    onConflict?: OnConflictAction;
 }
 
+export interface OnConflictAction {
+    on?: Expr[];
+    do: 'do nothing' | {
+        sets: SetStatement[];
+    };
+}
+
 export interface AlterTableStatement {
@@ -56,0 +64,0 @@ type: 'alter table';

src/parser/syntax/insert.spec.ts

@@ -30,2 +30,50 @@ import 'mocha';
 
+    checkInsert([`insert into test(a) values (1) on conflict do nothing`], {
+        type: 'insert',
+        into: { table: 'test' },
+        columns: ['a'],
+        values: [[{
+            type: 'integer',
+            value: 1,
+        },]],
+        onConflict: {
+            do: 'do nothing',
+        },
+    });
+
+    checkInsert([`insert into test(a) values (1) on conflict (a, b) do nothing`], {
+        type: 'insert',
+        into: { table: 'test' },
+        columns: ['a'],
+        values: [[{
+            type: 'integer',
+            value: 1,
+        },]],
+        onConflict: {
+            do: 'do nothing',
+            on: [
+                { type: 'ref', name: 'a' }
+                , { type: 'ref', name: 'b' }
+            ]
+        },
+    });
+
+    checkInsert([`insert into test(a) values (1) on conflict do update set a=3`], {
+        type: 'insert',
+        into: { table: 'test' },
+        columns: ['a'],
+        values: [[{
+            type: 'integer',
+            value: 1,
+        },]],
+        onConflict: {
+            do: {
+                sets: [{
+                    column: 'a',
+                    value: { type: 'integer', value: 3 },
+                }]
+            },
+        },
+    });
+
     checkInsert([`insert into test values (1) returning "id";`], {
@@ -127,4 +175,4 @@ type: 'insert',
         }
-        , 'default']]
+            , 'default']]
     });
 });

src/query.ts

 import { ISchema, QueryError, DataType, IType, NotSupported, TableNotFound, Schema, QueryResult, SchemaField } from './interfaces';
-import { _IDb, _ISelection, CreateIndexColDef, _ISchema, _Transaction, _ITable, _SelectExplanation, _Explainer } from './interfaces-private';
+import { _IDb, _ISelection, CreateIndexColDef, _ISchema, _Transaction, _ITable, _SelectExplanation, _Explainer, IValue, _IIndex, OnConflictHandler } from './interfaces-private';
 import { watchUse } from './utils';
@@ -450,39 +450,92 @@ import { buildValue } from './predicate';
             .getTable(p.into.table);
+        const selection = table
+            .selection
+            .setAlias(p.into.alias);
 
-        // get columns to insert into
-        const columns: string[] = p.columns ?? table.selection.columns.map(x => x.id);
 
         const ret = [];
-        const returning = p.returning && buildSelection(new ArrayFilter(table.selection, ret), p.returning);
+        const returning = p.returning && buildSelection(new ArrayFilter(selection, ret), p.returning);
 
-        // get values to insert
+
+        let values = p.values;
+
+        if (p.select) {
+            const selection = this.executeSelect(t, p.select);
+            throw new Error('todo: array-mode iteration');
+        }
+        if (!values) {
+            throw new QueryError('Nothing to insert');
+        }
+        if (!values.length) {
+            return null; // nothing to insert
+        }
+
+        // get columns to insert into
+        const columns: string[] = p.columns ?? table.selection.columns.map(x => x.id).slice(0, values[0].length);
+
+        // build 'on conflict' strategy
+        let ignoreConflicts: OnConflictHandler;
+        if (p.onConflict) {
+            // find the targeted index
+            const on = p.onConflict.on?.map(x => buildValue(table.selection, x));
+            let onIndex: _IIndex;
+            if (on) {
+                onIndex = table.getIndex(...on);
+                if (!onIndex?.unique) {
+                    throw new QueryError(`There is no unique or exclusion constraint matching the ON CONFLICT specification`);
+                }
+            }
+
+            // check if 'do nothing'
+            if (p.onConflict.do === 'do nothing') {
+                ignoreConflicts = { ignore: onIndex ?? 'all' };
+            } else {
+                if (!onIndex) {
+                    throw new QueryError(`ON CONFLICT DO UPDATE requires inference specification or constraint name`);
+                }
+                const subject = new JoinSelection(this
+                    , selection
+                    // fake data... we're only using this to get the multi table column resolution:
+                    , new ArrayFilter(table.selection, []).setAlias('excluded')
+                    , { type: 'boolean', value: false }
+                    , false
+                );
+                const sets = p.onConflict.do.sets.map(x => ({
+                    ...x,
+                    getter: x.value !== 'default' && buildValue(subject, x.value),
+                }));
+                ignoreConflicts = {
+                    onIndex,
+                    update: (item, excluded) => {
+                        const jitem = subject.buildItem(item, excluded);
+                        for (const s of sets) {
+                            item[s.column] = s.getter.get(jitem, t);
+                        }
+                    },
+                }
+            }
+        }
+
+        // insert values
         let rowCount = 0;
-        if (p.values) {
-            const values = p.values;
-            for (const val of values) {
-                rowCount++;
-                if (val.length !== columns.length) {
-                    throw new QueryError('Insert columns / values count mismatch');
+        for (const val of values) {
+            rowCount++;
+            if (val.length !== columns.length) {
+                throw new QueryError('Insert columns / values count mismatch');
+            }
+            const toInsert = {};
+            for (let i = 0; i < val.length; i++) {
+                const v = val[i];
+                const col = table.selection.getColumn(columns[i]);
+                if (v === 'default') {
+                    continue;
                 }
-                const toInsert = {};
-                for (let i = 0; i < val.length; i++) {
-                    const v = val[i];
-                    const col = table.selection.getColumn(columns[i]);
-                    if (v === 'default') {
-                        continue;
-                    }
-                    const notConv = buildValue(null, v);
-                    const converted = notConv.convert(col.type);
-                    if (!converted.isConstant) {
-                        throw new QueryError('Cannot insert non constant expression');
-                    }
-                    toInsert[columns[i]] = converted.get();
+                const notConv = buildValue(null, v);
+                const converted = notConv.convert(col.type);
+                if (!converted.isConstant) {
+                    throw new QueryError('Cannot insert non constant expression');
                 }
-                ret.push(table.insert(t, toInsert));
+                toInsert[columns[i]] = converted.get();
             }
-        } else if (p.select) {
-            const selection = this.executeSelect(t, p.select);
-            throw new Error('todo: array-mode iteration');
-        } else {
-            throw new QueryError('Nothing to insert');
+            ret.push(table.insert(t, toInsert, ignoreConflicts));
         }
@@ -489,0 +542,0 @@

src/table.ts

 import { IMemoryTable, Schema, QueryError, RecordExists, TableEvent, ReadOnlyError, NotSupported, IndexDef, ColumnNotFound, ISubscription } from './interfaces';
-import { _ISelection, IValue, _ITable, setId, getId, CreateIndexDef, CreateIndexColDef, _IDb, _Transaction, _ISchema, _Column, _IType, SchemaField, _IIndex, _Explainer, _SelectExplanation, ChangeHandler, Stats } from './interfaces-private';
+import { _ISelection, IValue, _ITable, setId, getId, CreateIndexDef, CreateIndexColDef, _IDb, _Transaction, _ISchema, _Column, _IType, SchemaField, _IIndex, _Explainer, _SelectExplanation, ChangeHandler, Stats, OnConflictHandler } from './interfaces-private';
 import { buildValue } from './predicate';
@@ -27,5 +27,5 @@ import { BIndex } from './btree-index';
     private readonly: boolean;
-    private serials = new Map<string, number>();
     hidden: boolean;
     private dataId = Symbol();
+    private serialsId: symbol = Symbol();
     private indexByHash = new Map<string, {
@@ -142,3 +142,3 @@ index: BIndex<T>;
         if (column.serial) {
-            this.serials.set(column.name, 0);
+            t.set(this.serialsId, t.getMap(this.serialsId).set(column.name, 0));
         }
@@ -232,3 +232,3 @@
 
-    insert(t: _Transaction, toInsert: T, shouldHaveId?: boolean): T {
+    insert(t: _Transaction, toInsert: T, onConflict?: OnConflictHandler): T {
         if (this.readonly) {
@@ -239,15 +239,8 @@ throw new ReadOnlyError(this.name);
         // get ID of this item
-        let newId: string;
-        if (shouldHaveId) {
-            newId = getId(toInsert);
-            if (!newId) {
-                throw new Error('Unexpeced update error');
-            }
-        } else {
-            newId = this.name + '_' + (this.it++);
-            setId(toInsert, newId);
-        }
+        const newId = this.name + '_' + (this.it++);
+        setId(toInsert, newId);
 
         // serial (auto increments) columns
-        for (const [k, v] of this.serials.entries()) {
+        let serials = t.getMap(this.serialsId);
+        for (const [k, v] of serials.entries()) {
             if (!nullIsh(toInsert[k])) {
@@ -257,4 +250,5 @@ continue;
             toInsert[k] = v + 1;
-            this.serials.set(k, v + 1);
+            serials = serials.set(k, v + 1);
         }
+        t.set(this.serialsId, serials);
 
@@ -271,2 +265,31 @@ // set default values
 
+        // check "on conflict"
+        if (onConflict) {
+            if ('ignore' in onConflict) {
+                if (onConflict.ignore === 'all') {
+                    for (const k of this.indexByHash.values()) {
+                        const found = k.index.eqFirst(k.index.buildKey(toInsert, t), t);
+                        if (found) {
+                            return found; // ignore.
+                        }
+                    }
+                } else {
+                    const index = onConflict.ignore as BIndex;
+                    const found = index.eqFirst(index.buildKey(toInsert, t), t);
+                    if (found) {
+                        return found; // ignore.
+                    }
+                }
+            } else {
+                const index = onConflict.onIndex as BIndex;
+                const key = index.buildKey(toInsert, t);
+                const got = index.eqFirst(key, t);
+                if (got) {
+                    // update !
+                    onConflict.update(got, toInsert);
+                    return this.update(t, got);
+                }
+            }
+        }
+
         // check change handlers (foreign keys)
@@ -279,3 +302,2 @@ for (const h of this.changeHandlers) {
         this.indexElt(t, toInsert);
-
         this.setBin(t, this.bin(t).set(newId, toInsert));
@@ -282,0 +304,0 @@ return toInsert;

src/tests/indexes.spec.ts

@@ -131,3 +131,3 @@ import 'mocha';
 
-    it('can use an index on an aliased selection', () => {
+    it('can use an index on an aliased selection not aliased var', () => {
         preventSeqScan(db);
@@ -144,3 +144,3 @@ const got = many(`create table test(txt text, val integer);
 
-        const explain = db.public.explainSelect(`select * from (select val from test where txt != 'A') x where x.val > 1`);
+        const explain = db.public.explainLastSelect();
         // assert.deepEqual(explain, {} as any);
@@ -147,0 +147,0 @@ assert.deepEqual(explain, {

src/tests/join.spec.ts

@@ -5,6 +5,3 @@ import 'mocha';
 import { expect, assert } from 'chai';
-import { trimNullish } from '../utils';
-import { Types } from '../datatypes';
 import { preventSeqScan, preventCataJoin, watchCataJoins } from './test-utils';
-import { IMemoryDb } from '../interfaces';
 import { _IDb } from '../interfaces-private';
@@ -643,2 +640,58 @@
 
+    it ('can self right join', () => {
+        const got = many(`create table test(usr text, friend text);
+        insert into test values ('me', 'you');
+        insert into test values ('me', 'other1');
+        insert into test values ('you', 'me');
+        insert into test values ('you', 'other2');
+        select a.usr, b.friend from
+            test a
+            right join test b on a.friend = b.usr;`);
+        expect(got)
+            .to.deep.equal([
+                { usr: 'you', friend: 'you' }
+                , { usr: 'you', friend: 'other1' }
+                , { usr: 'me', friend: 'me' }
+                , { usr: 'me', friend: 'other2' }
+            ])
+    })
+
+    it ('can self left join', () => {
+        const got = many(`create table test(usr text, friend text);
+        insert into test values ('me', 'you');
+        insert into test values ('me', 'other1');
+        insert into test values ('you', 'me');
+        insert into test values ('you', 'other2');
+        select a.usr, b.friend from
+            test a
+            left join test b on a.friend = b.usr;`);
+        expect(got)
+            .to.deep.equal([
+                { usr: 'me', friend: 'me' },
+                { usr: 'me', friend: 'other2' },
+                { usr: 'me', friend: null },
+                { usr: 'you', friend: 'you' },
+                { usr: 'you', friend: 'other1' },
+                { usr: 'you', friend: null },
+            ])
+    });
+
+    it ('can self inner join', () => {
+        const got = many(`create table test(usr text, friend text);
+        insert into test values ('me', 'you');
+        insert into test values ('me', 'other1');
+        insert into test values ('you', 'me');
+        insert into test values ('you', 'other2');
+        select a.usr, b.friend from
+            test a
+            join test b on a.friend = b.usr;`);
+        expect(got)
+            .to.deep.equal([
+                { usr: 'me', friend: 'me' },
+                { usr: 'me', friend: 'other2' },
+                { usr: 'you', friend: 'you' },
+                { usr: 'you', friend: 'other1' },
+            ])
+    });
+
     // it ('can full join', () => {
@@ -645,0 +698,0 @@ //     photos();

src/tests/simple-queries.spec.ts

@@ -246,2 +246,8 @@ import 'mocha';
     })
+
+    it ('can create table with compound primary key', () => {
+        none(`create table test(ka text, kb integer, val text,  primary key (ka, kb));
+            insert into test values ('a', 1, 'oldA');`);
+        assert.throws(() => none(`insert into test values ('a', 1, 'oldA');`));
+    })
 });

src/transforms/alias.ts

@@ -17,2 +17,4 @@ import { TransformBase, FilterBase } from './transform-base';
 
+    private oldToThis = new Map<IValue, IValue>();
+    private thisToOld = new Map<IValue, IValue>();
     get debugId() {
@@ -25,6 +27,18 @@ return this.base.debugId;
     }
-
+    private _columns: IValue<any>[];
     get columns(): ReadonlyArray<IValue<any>> {
-        return this.base.columns;
+        this.init();
+        return this._columns;
     }
+    init() {
+        if (this._columns) {
+            return;
+        }
+        this._columns = this.base.columns.map(x => {
+            const ret = x.setOrigin(this);
+            this.oldToThis.set(x, ret);
+            this.thisToOld.set(ret, x);
+            return ret;
+        });
+    }
 
@@ -54,3 +68,12 @@ stats(t: _Transaction): Stats | null {
         }
-        return this.base.getColumn(column, nullIfNotFound);
+        const got = this.base.getColumn(column, nullIfNotFound);
+        if (!got) {
+            return got;
+        }
+        this.init();
+        const ret = this.oldToThis.get(got);
+        if (!ret) {
+            throw new Error('Corrupted alias');
+        }
+        return ret;
     }
@@ -70,5 +93,5 @@
     getIndex(...forValue: IValue[]) {
-        return this.base.getIndex(...forValue);
+        return this.base.getIndex(...forValue.map(v => this.thisToOld.get(v) ?? v));
     }
 
 }

src/valuetypes.ts

@@ -134,3 +134,9 @@ import { IValue, _IIndex, _ISelection, _IType, _Transaction, _Explainer, _ExprExplanation } from './interfaces-private';
     setOrigin(origin: _ISelection): IValue<T> {
-        const ret = new Evaluator<T>(
+        const ret = this.clone();
+        ret.origin = origin;
+        return ret;
+    }
+
+    clone() {
+        return new Evaluator<T>(
             this.type
@@ -144,6 +150,5 @@ , this.id
         );
-        ret.origin = origin;
-        return ret;
     }
 
+
     setWrapper(newOrigin: _ISelection, unwrap: (val: any) => any) {
@@ -150,0 +155,0 @@ if (this.isAny) {

table.d.ts

 import { IMemoryTable, Schema, TableEvent, IndexDef, ISubscription } from './interfaces';
-import { _ISelection, IValue, _ITable, CreateIndexDef, _Transaction, _ISchema, _Column, SchemaField, _IIndex, _Explainer, _SelectExplanation, ChangeHandler, Stats } from './interfaces-private';
+import { _ISelection, IValue, _ITable, CreateIndexDef, _Transaction, _ISchema, _Column, SchemaField, _IIndex, _Explainer, _SelectExplanation, ChangeHandler, Stats, OnConflictHandler } from './interfaces-private';
 import { BIndex } from './btree-index';
@@ -16,5 +16,5 @@ import { Map as ImMap } from 'immutable';
     private readonly;
-    private serials;
     hidden: boolean;
     private dataId;
+    private serialsId;
     private indexByHash;
@@ -45,3 +45,3 @@ private indexByName;
     remapData(t: _Transaction, modify: (newCopy: T) => any): void;
-    insert(t: _Transaction, toInsert: T, shouldHaveId?: boolean): T;
+    insert(t: _Transaction, toInsert: T, onConflict?: OnConflictHandler): T;
     update(t: _Transaction, toUpdate: T): T;
@@ -48,0 +48,0 @@ delete(t: _Transaction, toDelete: T): T;

transforms/alias.d.ts

@@ -6,5 +6,9 @@ import { TransformBase } from './transform-base';
     name: string;
+    private oldToThis;
+    private thisToOld;
     get debugId(): string;
     constructor(sel: _ISelection, name: string);
+    private _columns;
     get columns(): ReadonlyArray<IValue<any>>;
+    init(): void;
     stats(t: _Transaction): Stats | null;
@@ -11,0 +15,0 @@ enumerate(t: _Transaction): Iterable<T>;

valuetypes.d.ts

@@ -25,2 +25,3 @@ import { IValue, _IIndex, _ISelection, _IType, _Transaction, _Explainer, _ExprExplanation } from './interfaces-private';
     setOrigin(origin: _ISelection): IValue<T>;
+    clone(): Evaluator<T>;
     setWrapper(newOrigin: _ISelection, unwrap: (val: any) => any): Evaluator<T>;
@@ -27,0 +28,0 @@ setId(newId: string): IValue;

No alert changes

Improved metrics

Total package byte prevSize

1667713

increased by1.91%

Number of package files

220

increased by0.46%

Lines of code

23496

increased by1.93%

No dependency changes