VRS/selftest_db.js

var st_db;

function getDatabase(){
    if(!st_db)
        st_db = LocalStorage.openDatabaseSync("selftest_db", "1.0", "selftest history records", 2048*1000);

    return st_db
}

function initDatabase() {
    st_db = LocalStorage.openDatabaseSync("selftest_db", "1.0", "selftest history records", 2048*1000);
    try {
        st_db.transaction( function(tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table(\
                                    dt TEXT primary key, \
                                    time TEXT, \
                                    target REAL, \
                                    interval INTEGER, \
                                    threshold REAL, \
                                    begin REAL, \
                                    end REAL, \
                                    delta REAL, \
                                    passed INTEGER )');
        })
    } catch (err) {
           console.log("Error creating table in database: " + err)
    };
}

function readData(tablename_str) {
    var res;
    //let sql_str = 'select * from ${tablename_str}'
    if(!st_db) { return; }

    try{
        st_db.transaction( function(tx) {
            res = tx.executeSql('select * from '+ tablename_str);
        })
    }catch(err){
        console.log("Error table:"+tablename_str+" not exist in database: " + err)
        return;
    }

    return res;
}

function readRecord(date_str) {
    var res;
    //let sql_str = 'select * from ${tablename_str}'
    if(!st_db) { return; }

    try{
        st_db.transaction( function(tx) {
            res = tx.executeSql('SELECT * FROM test_table WHERE dt = '+ date_str);
        })
    }catch(err){
        console.log("Error Record:"+date_str+" not exist in test_table: " + err)
        return;
    }

    return res;
}

function insertRecord(record) {
    var res = "Error";

    if(!st_db) { return res }

    var today = new Date();

    //日期
    var DD = String(today.getDate()).padStart(2, '0'); // 获取日
    var MM = String(today.getMonth() + 1).padStart(2, '0'); //获取月份，1 月为 0
    var yyyy = today.getFullYear(); // 获取年

    // 时间
    var hh =  String(today.getHours()).padStart(2, '0');       //获取当前小时数(0-23)
    var mm = String(today.getMinutes()).padStart(2, '0');     //获取当前分钟数(0-59)
    var ss = String(today.getSeconds()).padStart(2, '0');     //获取当前秒数(0-59)

    let date = yyyy + '-' + MM + '-' + DD;
    let time = hh + ':' + mm + ':' + ss;

    try{
        st_db.transaction( function(tx) {
            var result = tx.executeSql('INSERT OR REPLACE INTO test_table VALUES (?,?,?,?,?,?,?,?,?)',
                                       [date, time, record.target, record.interval, record.threshold, record.begin, record.end, record.delta, record.passed]);
            if (result.rowsAffected > 0) {
              res = "OK";
            } else {
              res = "Error";
            }
        })
    }catch(err){
        console.log("Error insert record in test_table: " + err)
        res = "Error";
    }
    return res
}


function deleteRecord(date_str) {
    var res = "Error";

    var today = new Date(date_str);
    var DD = String(today.getDate()).padStart(2, '0'); // 获取日
    var MM = String(today.getMonth()+1).padStart(2, '0'); //获取月份，1 月为 0
    var yyyy = today.getFullYear(); // 获取年

    let date = yyyy + MM  + DD;
    let table_name = 'data_'+date

    console.log("DeleteRecord in date_str: " + date_str)
    console.log("DeleteRecord in table_name: " + table_name)

    if(!st_db) { return res }

    try{
        st_db.transaction( function(tx) {
            tx.executeSql('DELETE FROM test_table WHERE dt =\"'+date_str+'\"');

            var result = tx.executeSql('SELECT * FROM sqlite_master WHERE type="table" AND name=\"'+table_name+'\"')

            if(result.rows.length > 0){
                tx.executeSql('DROP TABLE '+table_name);
            }

        })
    }catch(err){
        console.log("Error insert record in test_table: " + err)
        return res
    }

    res = "OK";

    return res
}

function insertData(data_arry) {
    var res = "Error";
    if(!st_db) { return res }

    var today = new Date();
    var DD = String(today.getDate()).padStart(2, '0'); // 获取日
    var MM = String(today.getMonth() + 1).padStart(2, '0'); //获取月份，1 月为 0
    var yyyy = today.getFullYear(); // 获取年

    let date = yyyy + MM  + DD;
    let table_name = 'data_'+date

        console.log("insertData tablename: " + table_name)

    try {
        st_db.transaction( function(tx) {
            var result = tx.executeSql('SELECT * FROM sqlite_master WHERE type="table" AND name=\"'+table_name+'\"')

            console.log("insertData 1111 result.rows.length: " + result.rows.length)


            var table_exist = false
            if(result.rows.length > 0){
                table_exist=true
            }

            if(true == table_exist){
                console.log("insertData 1111 true table_exist: " + table_exist)
                st_db.transaction( function(tx) {
                    tx.executeSql('DELETE  FROM '+ table_name);
                    tx.executeSql('UPDATE sqlite_sequence SET seq = 0 WHERE name =\"'+table_name+'\"');
                })

            }else{
                console.log("insertData 1111 false table_exist: " + table_exist)
                st_db.transaction( function(tx) {
                    tx.executeSql('CREATE TABLE IF NOT EXISTS '+ table_name +'(\
                                            ID INTEGER primary key AUTOINCREMENT, \
                                            step INTEGER, \
                                            pressure REAL)');
                })
            }


            console.log("insertData 2222 tablename: " + table_name)

            st_db.transaction( function(tx) {
                //var i;
                for(var i=0; i< data_arry.length; i++){
                    tx.executeSql('INSERT INTO '+ table_name +' (step,pressure) VALUES ('+data_arry[i].step +','+ data_arry[i].pressure+')');
                }
            })

        })
    } catch (error) {
        console.log("Error SELECT table in sqlite_master: " + error)
        return res;
    };


    /*
    if(table_exist){
        try {
            st_db.transaction( function(tx) {
                tx.executeSql('DELETE * FROM '+ table_name);
            })
        } catch (error1) {
            console.log("Error1 DELETE * FROM table insertData : " + error1)
            return res;
        };
    }else{
        try {
            st_db.transaction( function(tx) {
                tx.executeSql('CREATE TABLE IF NOT EXISTS '+ table_name +'(\
                                        id INTEGER primary key AUTOINCREMENT, \
                                        step INTEGER, \
                                        pressure REAL)');
            })
        } catch (error2) {
               console.log("Error creating table insertData in database: " + error2)
            return res;
        };
    }

    try {
        st_db.transaction( function(tx) {
            for(i=0; i< data_arry.length; i++){
                tx.executeSql('INSERT INTO '+ table_name +'(?,?)', [NULL, data_arry[i].step, data_arry[i].pressure]);
            }
        })
    } catch (error3) {
           console.log("Error INSERT INTO "+ table_name +"(?,?):"  + error3)
        return res
    };

*/
    res = "OK";

    return res
}

/*
function deleteData(date_str){
    let table_name = date_str+'_data'

}
*/