table.js

const Query = require('./query')
const Parameters = require('./parameters')
const Cache = require('./cache')
/**
 * Represents a table.
 *
 * A table will be initialized after first crud operation.
 * This can be an existing one or a new one.
 *
 * Missing fields specified in select / where clause are atomatically created.
 * Missing indexes specified in where clause are automatically created.
 *
 * @property {string[]} column column names
 * @property {string[]} indexes index names
 * @property {string} name table name
 * @property {Object} db sqlite-async connection instance
 * @property {bool} initialized is true if table exists, columns are loaded and indexes are loaded
 */
class Table {
    constructor(db, name) {
        this.db = db
        this.name = name
        this.indexes = []
        this.columns = []
        this.initialized = false
        this.cache = new Cache(2)
    }

    /**
     * Creates new record. Supported field types:
     *  - string
     *  - integer
     *  - boolean will be converted to int
     *  - date will be converted to string
     *
     * @example
     * console.info('Created pk', await this.insert({'stringColumn': 'value', 'numberColumn': 5}})
     *
     * @param {Object} {'columnName':'value'}
     * @returns {number} primary key of created record
     */
    insert(data) {
        const query = new Query.InsertQuery(this, data)
        return query.execute().then((primaryKey) => {
            this.cache.flush()
            return primaryKey
        })
    }

    /**
     * Find multiple records
     *
     * @example
     * await this.find({
     *      'id__gte': 5,
     *      'name__like': '%John',
     *      '_orderBy': '-id',
     *      '_limit': 50,
     *      '_offset': 10
     * })
     * @param {Object} [where=null] - E.g: {'id__like': '%a%'}
     * @param {Array} [columnNames=null] - E.g: ['id', 'name']
     * @returns {Object[]} Array of found records
     */
    find(where, columnNames) {
        return this.cache.get(['find', columnNames, where], () => {
            const query = new Query.SelectQuery(this, columnNames, where)
            return query.execute()
        })
    }

    /**
     * Find one record
     *
     * @example
     * await this.findOne({
     *      'name': 'John'
     * }))
     *
     * @param {Object} [where={}] - E.g: {'id': 1}
     * @param {string[]} [columnNames=null]
     * @returns {Object} Record
     * @returns {null} if no matches
     */
    findOne(where, columnNames) {
        const whereCriteria = where || {}
        whereCriteria._limit = 1
        return this.cache.get(['findOne', columnNames, whereCriteria], () => {
            const query = new Query.SelectQuery(this, columnNames, where)
            return query.execute().then(rows => {
                return rows[0]
            })
        })
    }

    /**
     *
     * @example <caption>Update with column name array as where clause</caption>
     * const changedRecordCount = await this.update({
     *      'id': 1
     *      'name': 'John'
     * },['id'])
     *
     * @example <caption>Update with string as where clause</caption>
     * const changedRecordCount = await this.update({
     *      'id': 1
     *      'name': 'John'
     * },'id')
     *
     * @example <caption>Update with object as where clause</caption>
     * const changedRecordCount = await this.update({
     *      'id': 1
     *      'name': 'John'
     * },{'id': 1})
     *
     * @example <caption>Update without where clause</caption>
     * // Update with no where clause
     * const changedRecordCount = await this.update({
     *      'id': 1
     *      'name': 'John'
     * })
     *
     * @param {Object} data record
     * @param {Object} where values that should match
     * @param {string[]} where field names of values that should match
     */
    update(data, where) {
        // returns update count
        const query = new Query.UpdateQuery(this, data, where)
        return query.execute().then((updatedCount) => {
            this.cache.flush()
            return updatedCount
        })
    }

    delete(where) {
        // returns deleted count
        const query = new Query.DeleteQuery(this, null, where)
        return query.execute().then((deletedCount) => {
            this.cache.flush()
            return deletedCount
        })
    }

    upsert(data, where) {
        // returns true if updated, else new primary key
        return this.update(data, where).then(changeCount => {
            return changeCount ? true : this.insert(data)
        }).then((result) => {
            this.cache.flush()
            return result
        })
    }

    count(where) {
        return this.cache.get(['count', where], () => {
            const query = new Query.CountQuery(this, null, where)
            return query.execute()
        })
    }

    ensureIndex(names) {
        return new Promise((resolve, reject) => {
            if (names.length < 2) {
                return resolve(false)
            }
            names.sort()
            const indexName = 'idx_' + names.join('_')
            if (this.indexes.indexOf(indexName) !== -1) {
                return resolve(false)
            }
            const nameString = names.join(',')

            const query = [
                'CREATE INDEX',
                indexName,
                'ON',
                `"${this.name}"`,
                `(${nameString})`
            ].join(' ')
            this.indexes.push(indexName)
            this.db.run(query, []).then(resolve)
        })
    }

    async ensure(obj) {
        if (!this.initialized) {
            this.initialized = true
            await this.createTable()
            this.columns = await this.getColumns()
            this.indexes = await this.getIndexes()
        }
        obj = obj || {}
        const params = new Parameters(obj)
        const promises = []
        params.keys.filter(
            key => this.columns.indexOf(key.name) === -1
        ).map((key) => {
            promises.push(this.addColumn(key.name))
        })

        return Promise.all(promises)
    }

    createTable() {
        return this.db.run(`CREATE TABLE IF NOT EXISTS "${this.name}" (id INTEGER PRIMARY KEY)`)
    }

    async getColumns() {
        const result = await this.db.all('SELECT name FROM PRAGMA_TABLE_INFO(?)', [this.name])
        const names = []
        result.forEach(row => {
            names.push(row.name)
        })
        return names
    }

    getIndexes() {
        return this.db.all('select name FROM sqlite_master WHERE tbl_name = ? AND type="index"', [this.name]).then(indexes => {
            return indexes.map((row) => {
                return row.name
            })
        })
    }

    async addColumn(name) {
        const query = [
            'ALTER TABLE',
            `"${this.name}"`,
            'ADD COLUMN ',
            `"${name}";`
        ].join(' ')
        this.columns.push(name)
        return this.db.run(query, []).then(() => {
            this.cache.flush()
        })
    }
}

module.exports = Table