read24 - Server: Deletion Tracking

July 26, 2020

Roger Ngo

Now we will make dateDeleted column to simulate soft deletes. I had debated this for a while in my head whether or not it would be worth having soft deletion, or just get rid of the database row altogether. I think to be safer than sorry, I rather put in some time to add the feature in anyway.‌

First, every database table will get a dateDeleted column which defaults to 0. This will make sure that existing database rows along with future rows being created for tables will now have dateDeleted = 0 by default. This represents an undeleted state.

alter table books add `dateDeleted` bigint not null default 0;
alter table choices add `dateDeleted` bigint not null default 0;
alter table classrooms add `dateDeleted` bigint not null default 0;
alter table questions add `dateDeleted` bigint not null default 0;
alter table quiz_questions add `dateDeleted` bigint not null default 0;
alter table quiz_tokens add `dateDeleted` bigint not null default 0;
alter table ratings add `dateDeleted` bigint not null default 0;
alter table student_answers add `dateDeleted` bigint not null default 0;
alter table students add `dateDeleted` bigint not null default 0;
alter table users add `dateDeleted` bigint not null default 0;

Next, the DataType interface which specifies the most basic resource properties in types.ts should also get an optional dateDeleted property.

export interface DataType {
    id: number;
    dateCreated?: number;
    dateUpdated?: number;
    dateDeleted?: number;
}

To make our code flexible deletion, and selection should receive options to either use the dateDeleted at column within logic.

For example, if the developer wants to explicitly delete rows from the database (dangerous, I guess?), then they can specify the database connector to perform that hard deletion. Now, if the developer in another instance wants to select all rows with the soft-deleted rows included, they should be able to do that too.

So, in connector.ts, I can specify two "option" types:

export interface DeleteOptions {
    hardDelete: boolean;
}

export interface SelectOptions {
    includeDeleted: boolean;
}

in db_connector.ts, the interface method signature for select will change to optionally include the SelectOptions parameter.

public abstract select(tablename: string, whereFunc?: (o: Partial<DataRow>) => boolean, opts?: SelectOptions): Promise<Partial<DataRow>[]>;

The in-memory, and SQL database connectors will now need to be modified to accommodate for this. I will start by modifying the in-memory connector first as it is easier. ‌

In the select implementation, I will just need to check if opts is passed, and if opts.includeDeleted is true. If so, then just return all the rows without filtering. Otherwise, filter out rows, and only include where dateDeleted = 0.

public select(tableName: string, whereFunc?: (o: DataRow) => boolean, opts?: SelectOptions): Promise<DataRow[]> {
    if (!whereFunc)
        return Promise.resolve(db.data[tableName]);

    const filtered: DataRow[] = db.data[tableName].filter(whereFunc);

    if (opts && opts.includeDeleted)
        return Promise.resolve(filtered);

    return Promise.resolve(filtered.filter((o : DataRow) => !o.dateDeleted));
}

Within the SQL connector, the select method can be implemented to by default, only select rows where dateDeleted = 0.

public select(tableName: string, whereFunc?: (o: DataRow) => boolean, opts?: SelectOptions): Promise<DataRow[]> {
    const promise = new Promise<DataRow[]>((resolve, reject) => {
        // By default, do not include rows where deletedAt is set. This is a virtual deletion.
        let queryString = `SELECT * FROM ${TableMapping[tableName]} WHERE dateDeleted = 0`;

        if (opts && opts.includeDeleted)
            queryString = `SELECT * FROM ${TableMapping[tableName]}`;

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

            console.log(query.sql);
            console.log('SELECT results', results);

            const resultRows = results.map(r => Object.assign({}, r));

            return resolve(resultRows.filter(whereFunc) as DataRow[]);
        });
    });


    return promise;
}

While I am at it, I can refactor the find method to reuse the new select method. Since find only returns 1 element matching the id of the entity, it will just be the first item retrieved through select.

public find(tableName: string, id: number): Promise<DataRow> {
    const promise = new Promise<DataRow>((resolve, reject) => {
        const opts: SelectOptions = {
            includeDeleted: false
        };

        return this.select(tableName, (o: DataRow) => o.id === id, opts)
            .then(results => results.length === 0 ? reject(null) : resolve(results[0]))
            .catch(e => reject(e));
    });

    return promise;
}

Now modify base_resource.ts to allow all resources to take advantage of the new soft deletion feature.

First, the BaseResource should have a new property dateDeleted which is initialized to 0.

public dateDeleted: number;

constructor(...) {
    ...
    this.dateDeleted = 0;
}

Then, json method should remove the reference to dateDeleted when being serialized.

delete obj.dateDeleted

The delete method now needs modification in that it should now receive an optional opts parameter of DeleteOptions type for the developer to bypass soft deletion and perform hard deletion.‌

If the opts.hardDelete is turned on, then the regular DatabaseConnector.delete method is called to completely remove the row from the database. Otherwise, the dateDeleted property is set, and an update to the row is performed to simulate the soft deletion.

public async delete(opts?: DeleteOptions) {
    if (this.id === 0)
        return this;

    this.dateDeleted = Date.now();

    const toSave = this.serializeForDb();

    console.log(`Resource delete. opts: ${opts}, id: ${this.id}`);

    if (opts && opts.hardDelete)
        await DatabaseConnector.delete(this._tableName, (o: DataRow) => o.id === this.id);
    else
        await DatabaseConnector.update(this._tableName, toSave);

    return this;
}

Again, while I am at it, load within BaseResource should perform better error handling in case if a resource which has been deleted is attempted to be loaded.

public async load(id: number) {

    try {
        const found = await DatabaseConnector.find(this._tableName, id);

        Object.assign(this, found);

        return Promise.resolve(this);
    } catch (e) {
        return Promise.reject({status: 'not_found', message: `Did not find a resource with ID value: ${id}`});
    }
}

Okay, now I am ready to implement the admin route to delete books and the associated quiz questions, and choices.‌

Since a Book is composed of three entities: the book information itself represented by BookType, the quiz questions represented by QuestionType, and the choices per question represented by ChoiceType. In order to cleanly "delete" this data, I must start by the bottom up.

  1. Delete all choices which depend on the questions tied to the book.
  2. Delete all the questions tied to the book
  3. Delete the book itself.

Here is the implementation:

 app.delete('/admin/quiz/book/:bookId', async (req, res) => {
     const bookId = req.params.bookId;

     try {
        const b = await new Book().load(parseInt(bookId));
        const qs = await Question.listByBookId(b.id);
        for (const q of qs) {
            const cs = await Choice.listByQuestionId(q.id);

            for(const c of cs) {
                await c.delete();
            }

            await q.delete();
        }

        await b.delete();

        return res.status(200).json({deletedAt: new Date(b.dateDeleted)});
     } catch (e) {
        return res.status(errorStatusToHttpCode(e.status)).json(e);
     }
 });

After testing, and calling DELETE /admin/quiz/book/2 I get this response:

{
    "deletedAt": "2020-07-26T14:22:34.657Z"
}

And to query the same book with ID 2, I get a 404:

{
    "status": "not_found",
    "message": "Did not find a resource with ID value: 2"
}

Looks good!