data graber - db interactions for iMessage analyzer app

node-main.js

// node-main.js
const sqlite3 = require('sqlite3')
const util = require('util')
const exec = require('child_process').exec

class DataGrabber {
    constructor() {
        this.OSX_EPOCH = 978307200
        this.messages = []
        const { exec } = require('child_process');
        exec('whoami', (err, stdout, stderr) => {
            if (err) {
                return;
            }
            console.log(stdout)
            var path = '/Users/' + stdout.replace(/\n|\r/g, "") + '/Library/Messages/chat.db'
            this.dbPath = path
            console.log(path)
            this.conn = new sqlite3.Database(path, (err) => {
                if (err) {
                    console.error(err.message);
                }
                console.log('Connected to the iMessage SQlite database.');
            })
        });
    }
    getTableNames() {
        this.conn.each(`SELECT name FROM sqlite_master WHERE type='table';`, (err, row) => {
            if (err) {
                console.error(err.message);
            }
            console.log(row.id + "\t" + row.name);
        });
    }
    buildMessage(message) {
        var fro = (message['is_from_me'] == 1) ? "ME" : "THEM"
        var val = (message['date'] + this.OSX_EPOCH) * 1000
        // console.log( val)
        var utcSeconds = val
        return {
            uid: message['guid'],
            isgroup: message['type'],
            group: message['cache_roomnames'],
            message: message['text'],
            "time": new Date(utcSeconds),
            from: fro
        }
    }
    getMessages(handle_id) {
        var self = this
        return new Promise(function(resolve, reject) {
            var responseObj;
            var db = new sqlite3.Database(self.dbPath, sqlite3.OPEN_READONLY);
            var SQL = 'SELECT * FROM `message` WHERE handle_id=' + handle_id.toString() + ' ORDER BY date DESC LIMIT 99999'
            db.all(SQL, function cb(err, rows) {
                if (err) {
                    responseObj = {
                        'error': err
                    };
                    reject(responseObj);
                } else {
                    var payload = []
                    for (let msg of rows) {
                        payload.push(self.buildMessage(msg))
                    }
                    responseObj = {
                        statement: this,
                        rows: payload
                    };
                    resolve(responseObj);
                }
                db.close();
            });
        });
    }
    buildLastMessage(message) {
        var self = this
        return new Promise(function(resolve, reject) {
            var fro = (message['is_from_me'] == 1) ? "ME" : "THEM"
            self.getNumbersFromHandle(message['handle_id'].toString()).then(function(number) {
                var numTo = ''
                if (number.rows.length > 0) {
                    numTo = number.rows[0].id
                }
                var val = (message['date'] + self.OSX_EPOCH) * 1000
                var utcSeconds = val
                resolve({
                    "handle": numTo,
                    "isgroup": message['type'],
                    "group": (message['cache_roomnames'] == null) ? "" : message['cache_roomnames'],
                    "uid": message['guid'],
                    "message": message['text'],
                    "time": new Date(utcSeconds),
                    "from": fro
                })
            }, function(error) {
                reject(error)
            })
        });
    }
    getLastMessages() {
        var self = this
        return new Promise(function(resolve, reject) {
            var responseObj;
            var db = new sqlite3.Database(self.dbPath, sqlite3.OPEN_READONLY);
            var SQL = "SELECT * FROM `message` ORDER BY date DESC LIMIT 1000"

            db.all(SQL, function cb(err, rows) {
                if (err) {
                    responseObj = {
                        'error': err
                    };
                    reject(responseObj);
                } else {
                    var payload = []
                    for (let msg of rows) {
                        payload.push(self.buildLastMessage(msg))
                    }
                    responseObj = {
                        statement: this,
                        rows: payload.reverse()
                    };
                    resolve(responseObj);
                }
                db.close();
            });
        });
    }
    getHandlesLike(search) {
        var self = this
        return new Promise(function(resolve, reject) {
            var responseObj;
            var db = new sqlite3.Database(self.dbPath, sqlite3.OPEN_READONLY);
            var SQL = "SELECT * FROM `handle` WHERE id LIKE '%" + search + "%'"

            db.all(SQL, function cb(err, rows) {
                if (err) {
                    responseObj = {
                        'error': err
                    };
                    reject(responseObj);
                } else {
                    responseObj = {
                        statement: this,
                        rows: rows
                    };
                    resolve(responseObj);
                }
                db.close();
            });
        });
    }
    getNumbersFromHandle(handle) {
        var self = this
        return new Promise(function(resolve, reject) {
            var responseObj;
            var db = new sqlite3.Database(self.dbPath, sqlite3.OPEN_READONLY);
            var SQL = "SELECT * FROM `handle` WHERE ROWID LIKE '" + handle + "'"

            db.all(SQL, function cb(err, rows) {
                if (err) {
                    responseObj = {
                        'error': err
                    };
                    reject(responseObj);
                } else {
                    responseObj = {
                        statement: this,
                        rows: rows
                    };
                    resolve(responseObj);
                }
                db.close();
            });
        });
    }
    getAllConversations(handle_id) {
        var self = this
        return new Promise(function(resolve, reject) {
            var responseObj;
            var db = new sqlite3.Database(self.dbPath, sqlite3.OPEN_READONLY);
            var SQL = "SELECT * FROM `chat_handle_join` WHERE handle_id=" + handle_id.toString()

            db.all(SQL, function cb(err, rows) {
                if (err) {
                    responseObj = {
                        'error': err
                    };
                    reject(responseObj);
                } else {
                    responseObj = {
                        statement: this,
                        rows: rows
                    };
                    resolve(responseObj);
                }
                db.close();
            });
        });
    }
    findGetMessages(search) {
        var self = this
        return new Promise(function(resolve, reject) {

            self.getHandlesLike(search).then(
                function(messages) {
                    var contactNumbers = []
                    for (let handle of messages.rows) {
                        contactNumbers.push(self.getMessages(handle.ROWID))
                    }
                    resolve(contactNumbers)
                },
                function(error) {
                    responseObj = {
                        'error': error
                    };
                    reject(responseObj);
                })
        });
    }
}

module.exports.DataGrabber = DataGrabber