delete.queries.spec.ts

import { describe, it, beforeEach, expect } from 'bun:test';

import { newDb } from '../db';

import { IMemoryDb } from '../interfaces';

describe('Deletes', () => {

    let db: IMemoryDb;
    let many: (str: string) => any[];
    let none: (str: string) => void;
    beforeEach(() => {
        db = newDb();
        many = db.public.many.bind(db.public);
        none = db.public.none.bind(db.public);
    });

    it('can delete with conditions simple', () => {
        expect(many(`create table test(a text);
            insert into test values ('a'), ('b'), ('c');
            delete from test where a <= 'b';
            select * from test;`))
            .toEqual([{ a: 'c' }])
    });

    it('can delete with conditions within transaction', () => {
        expect(many(`create table test(a text);
            start transaction;
            insert into test values ('a'), ('b'), ('c');
            delete from test where a <= 'b';
            commit;
            select * from test;`))
            .toEqual([{ a: 'c' }])
    });


    it('does not delete if rollback transaction', () => {
        expect(many(`create table test(a text);
            insert into test values ('a'), ('b'), ('c');
            commit;
            start transaction;
            delete from test where a <= 'b';
            rollback;
            select * from test;`))
            .toEqual([{ a: 'a' }, { a: 'b' }, { a: 'c' }])
    });

    it('delete if in same rollbacked transaction', () => {
        expect(many(`create table test(a text);
            commit;
            start transaction;
            insert into test values ('a'), ('b'), ('c');
            delete from test where a <= 'b';
            rollback;
            select * from test;`))
            .toEqual([])
    });

    it('can truncate table', () => {
        expect(many(`create table test(a text);
        insert into test values ('a'), ('b'), ('c');
        truncate test;
        select * from test;`))
            .toEqual([])
    });

    it('does not restart identity by default on truncation', () => {
        expect(many(`create table test_table (id INT GENERATED BY DEFAULT AS identity, val text);
        insert into test_table(val) values ('a'), ('b'), ('c');
        truncate test_table;
        insert into test_table(val) values ('d'), ('e');
        select * from test_table;`))
            .toEqual([{ id: 4, val: 'd' }, { id: 5, val: 'e' }])
    });

    it('can restart identity on truncation', () => {
        expect(many(`create table test_table (id INT GENERATED BY DEFAULT AS identity, val text);
        insert into test_table(val) values ('a'), ('b'), ('c');
        truncate test_table restart identity;
        insert into test_table(val) values ('d'), ('e');
        select * from test_table;`))
            .toEqual([{ id: 1, val: 'd' }, { id: 2, val: 'e' }])
    });

    it('cannot query primary key condition after truncate', () => {
        // this was a bug
        expect(many(`create table test(a text primary key);
        insert into test values ('a'), ('b'), ('c');
        truncate test;
        select * from test where a='a';`))
            .toEqual([])
    });


    it('cannot query index condition after truncate', () => {
        // this was a bug
        expect(many(`create table test(a text);
        create index on test(a);
        insert into test values ('a'), ('b'), ('c');
        truncate test;
        select * from test where a='a';`))
            .toEqual([])
    });
});