homecontrol/javascript/sql.js

/**
 *  Copyright © 2010 Digia Plc
 *  Copyright © 2010 Nokia Corporation
 *
 *  All rights reserved.
 *
 *  Nokia and Nokia Connecting People are registered trademarks of
 *  Nokia Corporation.
 *  Java and all Java-based marks are trademarks or registered
 *  trademarks of
 *  Sun Microsystems, Inc. Other product and company names
 *  mentioned herein may be
 *  trademarks or trade names of their respective owners.
 *
 *
 *  Subject to the conditions below, you may, without charge:
 *
 *  ·  Use, copy, modify and/or merge copies of this software and
 *     associated documentation files (the "Software")
 *
 *  ·  Publish, distribute, sub-licence and/or sell new software
 *     derived from or incorporating the Software.
 *
 *
 *  This file, unmodified, shall be included with all copies or
 *  substantial portions
 *  of the Software that are distributed in source code form.
 *
 *  The Software cannot constitute the primary value of any new
 *  software derived
 *  from or incorporating the Software.
 *
 *  Any person dealing with the Software shall not misrepresent
 *  the source of the Software.
 *
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY
 *  KIND, EXPRESS OR IMPLIED,
 *  INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 *  MERCHANTABILITY, FITNESS FOR A
 *  PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 *  AUTHORS OR COPYRIGHT
 *  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 *  WHETHER IN AN ACTION
 *  OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
 *  CONNECTION WITH THE
 *  SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */

/*
 *  HOME CONTROL SYSTEM - SQL FUNCTIONS
 *      This file contains all the necessary functions
 *      for Home Control Systems database connectivity.
 */

// Important variables
var theRoom = new Array();
var roomx = new Array();
var cur_roomCode = "";
var cur_info = Array();
var theme = 0;
var lockStat = false;


/**
 * Holds information for a Room object
 *
 * @param roomCode Rooms unique ID
 * @param roomName Rooms name
 * @param temp Room temperature
 * @param light Room lights level
 * @param volume Room audio volume
 * @param mute Room audio mute
 * @param fireAlarm True if there's a fire in the room
 * @param burglarAlarm True if burglar alarm is triggered in this room
 * @return Nothing
 */
function Room(roomCode, roomName, temp, light, volume, mute, fireAlarm, burglarAlarm) {
    this.light = light;
    this.volume = volume;
    this.mute = mute;
    this.temperature = temp;
    this.fireAlarm = fireAlarm;
    this.burglarAlarm = burglarAlarm;
    this.name = roomName;
    this.code = roomCode;

    this.lightMin = 0;
    this.lightMax = 100;
    this.volumeMin = 0;
    this.volumeMax = 100;
    this.temperatureMin = 18;
    this.temperatureMax = 30;
}

/**
 * Resets demo application info
 *
 * @return Nothing
 */
function setDefaults() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            // Create the database if it doesn't already exist
            tx.executeSql('CREATE TABLE IF NOT EXISTS RoomTable(roomCode TEXT, roomName TEXT, temp INT, tempMin INT, tempMax INT, light INT, lightMin INT, lightMax INT, volume INT, volumeMin INT, volumeMax INT, mute BOOL, fireAlarm BOOL, burglarAlarm BOOL)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS Themes(style INT)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS Sauna(saunaOn BOOLEAN default false, temperature DECIMAL default 0)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS Security(alarmOn BOOLEAN default false, doorsLocked BOOLEAN default false)');

            // Delete all existing info
            var deleteResult = tx.executeSql('DELETE FROM RoomTable');
            deleteResult = tx.executeSql('DELETE FROM Themes');
            deleteResult = tx.executeSql('DELETE FROM Sauna');
            deleteResult = tx.executeSql('DELETE FROM Security');
            // Add all the rooms with default values
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'room1', 'Bedroom 1', 18, 18, 30, 80, 0, 100, 10, 0, 100, true, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'room2', 'Bedroom 2', 19, 18, 30, 70, 0, 100, 20, 0, 100, false, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'room3', 'Master bedroom', 20, 18, 30, 60, 0, 100, 30, 0, 100, true, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'kitchen', 'Kitchen', 21, 18, 40, 50, 0, 100, 40, 0, 100, true, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'livingroom', 'Living room', 22, 18, 30, 40, 0, 100, 50, 0, 100, true, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'khh', 'Utility room', 23, 18, 30, 30, 0, 100, 60, 0, 100, true, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'kph', 'Bathroom', 24, 18, 30, 20, 0, 100, 70, 0, 100, false, false, false ]);
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ 'sauna', 'Sauna', 25, 18, 30, 10, 0, 100, 80, 0, 100, true, false, false ]);
            tx.executeSql('INSERT INTO Themes VALUES(?)', [ 1 ]);
            tx.executeSql('INSERT INTO Sauna VALUES(?,?)', [false,0]);
            tx.executeSql('INSERT INTO Security VALUES(?,?)', [false,false]);

        }
    )
    return;
}

/**
 * Saves changes to a room
 *
 * @param roomCode Unique room ID
 * @param temp Room temperature
 * @param Room lights level
 * @param Room audio volume
 * @param Room audio mute
 * @return Nothing
 */
function saveRoomInfo(roomCode, temp, light, volume, mute) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            // Show all rooms
            var RoomTable = tx.executeSql("SELECT fireAlarm, burglarAlarm, roomName FROM RoomTable WHERE roomCode='"+roomCode+"' LIMIT 1");
            var returnThis = new Array(RoomTable.rows.item(0).fireAlarm, RoomTable.rows.item(0).burglarAlarm, RoomTable.rows.item(0).roomName);
            // Delete existing room info
            var deleteResult = tx.executeSql("DELETE FROM RoomTable WHERE roomCode='"+roomCode+"'");

            // Save new info
            tx.executeSql('INSERT INTO RoomTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [ roomCode, returnThis[2], temp, 18, 30, light, 0, 100, volume, 0, 100, mute, returnThis[0], returnThis[1] ]);
        }
    )
}



/**
 * Creates a new room object with all the infor of that room
 *
 * @param roomCode Unique room ID
 * @return Nothing
 */
function getTheRoom(roomCode) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            // Get all rooms
            var RoomTable = tx.executeSql("SELECT * FROM RoomTable WHERE roomCode='"+roomCode+"'");
            theRoom = new Room(RoomTable.rows.item(0).roomCode, RoomTable.rows.item(0).roomName, RoomTable.rows.item(0).temp, RoomTable.rows.item(0).light, RoomTable.rows.item(0).volume, RoomTable.rows.item(0).mute, RoomTable.rows.item(0).fireAlarm, RoomTable.rows.item(0).burglarAlarm);
        }
    )
}

/**
 * Gets some info from a specific room and stores it in an array.
 *
 * @param roomCode Unique room ID
 * @return Array
 */
function getRoom(roomCode) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    return db.transaction(
        function(tx) {
            // Show all rooms
            var RoomTable = tx.executeSql("SELECT light, volume, mute, fireAlarm, burglarAlarm FROM RoomTable WHERE roomCode='"+roomCode+"' LIMIT 1");
            var returnThis = new Array(RoomTable.rows.item(0).light, RoomTable.rows.item(0).volume, RoomTable.rows.item(0).mute, RoomTable.rows.item(0).fireAlarm, RoomTable.rows.item(0).burglarAlarm);
            roomx = returnThis;
        }
    )
    return roomx;
}

/**
 * Checks door locks status
 *
 * @return boolean
 */
function getLockStatus() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    return db.transaction(
        function(tx) {
            var RoomTable = tx.executeSql("SELECT doorsLocked FROM Security LIMIT 1");
            lockStat = RoomTable.rows.item(0).doorsLocked;
        }
    )
    return lockStat;
}

/**
 * Sets door lock status
 *
 * @param newLocks New status for door locks
 * @return Nothing
 */
function setLocks( newLocks ) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    return db.transaction(
        function(tx) {
            tx.executeSql('UPDATE Security SET doorsLocked="'+newLocks+'"');
        }
    )
}

/**
 * Sets HCS theme
 *
 * @param style Name of the new theme
 * @return Nothing
 */
function setTheme(style) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {

            var themes = tx.executeSql("SELECT style FROM Themes");
            var returnThis = themes.rows.item(0).style;
            // Delete existing style info
            var deleteResult = tx.executeSql("DELETE FROM Themes");

            // Save new info
            tx.executeSql('INSERT INTO Themes VALUES(?)', [style]);
        }
    )
}

/**
 * Gets HCS current theme
 *
 * @return string
 */
function getTheme() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    return db.transaction(
        function(tx) {
            // fetch theme
            var themes = tx.executeSql("SELECT style FROM Themes");
            theme = themes.rows.item(0).style;
        }
    ) 
    return theme;
}


/**
 * Sets sauna on
 *
 * @param saunaOn
 * @return Nothing
 */
function setSaunaOn(saunaOn) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            //Updating information
            tx.executeSql('UPDATE Sauna SET saunaOn="'+saunaOn+'"');
        }
    )
}

/**
 * Gets sauna status
 *
 * @return boolean
 */
function isSaunaOn() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    var saunaOn = false;
    db.transaction(
        function(tx) {
            //Updating information
            saunaOn = tx.executeSql('SELECT saunaOn FROM Sauna').rows.item(0).saunaOn;
        }
    )
    if(saunaOn == "true") return true
    if(saunaOn == "false") return false
}

/**
 * Sets sauna temperature
 *
 * @param temperature New sauna temperature
 * @return Nothing
 */
function setSaunaTemperature(temperature) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            //Updating information
            tx.executeSql('UPDATE Sauna SET temperature='+temperature);
        }
    )
}

/**
 * Gets sauna temperature
 *
 * @return int
 */
function getSaunaTemperature() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    var temperature = -1
    db.transaction(
        function(tx) {
            //Updating information
            temperature = tx.executeSql('SELECT temperature FROM Sauna').rows.item(0).temperature;
        }
    )
    return temperature;
}


/**
 * Changes door locks status
 *
 * @param doors Locks status
 * @return Nothing
 */
function setDoorsLocked(doors) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            //Updating information
            tx.executeSql('UPDATE Security SET doorsLocked="'+doors+'"');
        }
    )
}

/**
 * Checks whether the doors are locked
 *
 * @return boolean
 */
function isDoorsLocked() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    var doorsLocked = false;
    db.transaction(
        function(tx) {
            //Updating information
            doorsLocked = tx.executeSql('SELECT doorsLocked FROM Security').rows.item(0).doorsLocked;
        }
    )
    if(doorsLocked == "true") return true
    if(doorsLocked == "false") return false
}

/**
 * Sets alarm system mode
 *
 * @param alarm New status for the alarm system
 * @return Nothing
 */
function setAlarmSystemOn(alarm) {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    db.transaction(
        function(tx) {
            //Updating information
            tx.executeSql('UPDATE Security SET alarmOn="'+alarm+'"');
        }
    )
}

/**
 * Checks whether the alarm system is on
 *
 * @return Nothing
 */
function isAlarmSystemOn() {
    var db = openDatabaseSync("HomeControl", "1.0", "CoDe Qt Quick application", 1000000);
    var alarmOn = false;
    db.transaction(
        function(tx) {
            //Updating information
            alarmOn = tx.executeSql('SELECT alarmOn FROM Security').rows.item(0).alarmOn;
        }
    )
    if(alarmOn == "true") return true
    if(alarmOn == "false") return false
}