read24 - Server - More Database Improvements

August 11, 2020

Roger Ngo

I’m preparing to actually create some automated tests for the code I have written so far on the server. One of the higher priority items to get the system in a state where it can be tested is to create a proper in-memory database that can stay in parity with the SQL connector.‌

An in-memory database will be used for creating automated tests. I have to ensure that the in-memory database connector this time will be able to keep up with the changes made to the SQL connector.

First, I’ll move the TableMapping variable to its own file called tables.ts. The future in-memory database will use the information here as keys to values.‌

Next, db_connector.ts, needs to remove the concept of whereFunc. I think at this point, there are enough options available within SelectOptions, and DeleteOptions, that it can replace this parameter to filter out results before returning back to the caller. Besides, it’s better to be more restrictive with having configurable options rather than a user-passed in function.

So select, and delete will have the new definitions:

public abstract select(tablename: string, opts?: SelectOptions): Promise<Partial<DataRow>[]>;
public abstract delete(tableName: string, opts: DeleteOptions): Promise<number>;

Now, SelectOptions will have a new option called in which will allow the database connectors to build the equivalent of an IN-clause.

export interface SelectOptions {
    includeDeleted?: boolean;
    orderBy?: OrderByOptions;
    offset?: number;
    limit?: number;
    columns?: string[];
    filters?: FilterOption[];
    fullTextMatch?: FullTextMatchOption[];
    in?: InOption;
}

export interface InOption {
    column: string;
    value: string[]|number[];
}

InOption takes in a column, and a list of values to build the following:

WHERE [column] IN (value[0], value[1], ..., value[n])

DeleteOptions also needs to be updated to include FilterOption[] and InOption to perform controlled/filtered deletes.

export interface DeleteOptions {
    hardDelete: boolean;
    filters?: FilterOption[];
    in?: InOption;
}

sql.ts needs some significant updating with all these changes. Aside from the obvious change in removing whereFunc in select, and delete, I need to update the functions to use the new SelectOptions, and DeleteOptions as appropriate.

Let’s take a look at delete first.

public delete(tableName: string, opts: DeleteOptions): Promise<number> {
    const promise = new Promise<number>(async (resolve, reject) => {
        // There needs to be some filtering
        if (opts.filters.length === 0 && !opts.in && !isValue(opts.in.column) && !isValue(opts.in.value))
            return resolve(0);

        let queryString = `DELETE FROM ${TableMapping[tableName]} WHERE `;
        for(const f of opts.filters)
            queryString += ` ${f.column} = ${connection.escape(f.value)}`;
        
        if (opts.in) {
            const list = [];
            for(const l of opts.in.value)
                list.push(connection.escape(l));

            queryString += `${opts.in.column} IN (${list.join(',')})`;
        }


        const query = connection.query(queryString, (err, results) => {
            if (err)
                return reject(err);

            console.log(query.sql);

            return resolve(results.affectedRows);
        });
    });

    return promise;
}

First, in order to stay safe, a DELETE should never, ever be performed in an unfiltered manner. This is why opts.filters, an opts.in are checked to see if there are any options available.

Once that condition passes, then opts.filter is checked to see if there are any filter options available. If so, a generic string of column = value connected by AND conditionals is generated.

Finally, an IN-clause is built if opts.in is available.

I follow the same approach in select to now adjust for not having a whereFunc.

After all these changes, all the resource classes which use select, and delete explicitly must be updated to remove the whereFunc, and be substitute with the analogous FilterOption.

For example, in choice.ts to list by question ID, the listByQuestionid method is updated to be:

public static async listByQuestionId(questionId: number): Promise<Choice[]> {
    const choiceIds = (await DatabaseConnector.select('choices', {
        filters: [
            {
                column: 'questionId',
                value: questionId
            }
        ]
    })).map((c: ChoiceType) => c.id);

    const choices = [];
    for(const id of choiceIds) {
        choices.push(await new Choice().load(id));
    }

    return choices;
}

Now, let’s get started on an in-memory database.

First let’s stub something… I’ll create a new file: memory.ts within the db folder. It will implement the db_connector interface.

import { DbConnector } from "./db_connector";
import { DataRow } from "./types";
import { SelectOptions, DeleteOptions } from "./connector";

export class _MemoryDb extends DbConnector {
    public insert(tableName: string, data: Partial<DataRow>): Promise<number> {
        return Promise.resolve(0);
    }

    public update(tableName: string, data: Partial<DataRow>): Promise<number> {
        return Promise.resolve(0);
    }

    public find(tableName: string, id: number): Promise<Partial<DataRow>> {
        return Promise.resolve({});
    }

    public select(tableName: string, opts?: SelectOptions): Promise<Partial<DataRow>[]> {
        return Promise.resolve([]);
    }

    public delete(tableName: string, opts: DeleteOptions): Promise<number> {
        return Promise.resolve(0);
    }
}

Next time, I will implement the functionality in this connector.