LRDMxhaX

1class Websql {
2    static get db() {
3        return this.prepareDatabase();
4    }
5
6    // Check if this browser supports Web SQL
7    static getOpenDatabase() {
8        try {
9            if( !! window.openDatabase ) return window.openDatabase;
10            else return undefined;
11        } catch(e) {
12            return undefined;
13        }
14    }
15
16    // Open the Web SQL database
17    static prepareDatabase() {
18        let odb = this.getOpenDatabase();
19        if(!odb) {
20            alert('Web SQL Not Supported');
21            return undefined;
22        } else {
23            return odb( this.dbName, '1.0', 'A Test Database', 10 * 1024 * 1024 );
24        }
25    }
26
27     // create tables
28     // createSQL type array
29     static migrate() {
30        const createSQL = this.createSQL;
31        this.db.transaction( t => {
32            createSQL.forEach( sql => {
33                t.executeSql( sql, [],
34                    (t, r) => console.log('tables created'),
35                    (t, e) => alert('create table: ' + e.message)
36                );
37            });
38        });
39     }
40
41    // fields type string
42    static insert(table, fields, ...data) {
43        let placeholder = '';
44        for(let str in data) placeholder += '?, ';
45        placeholder = placeholder.substring(0, placeholder.length - 2);
46
47        this.db.transaction( t =>
48            t.executeSql(` INSERT INTO ${table} ( ${fields} ) VALUES ( ${placeholder} ) `, data),
49            (t, e) => alert('Insert row: ' + e.message),
50            () => console.log('insert success')
51        );
52    }
53
54    // fields type array
55    static update(table, fields, ...data){
56        let placeholder = '';
57        for(let str of fields) placeholder += `${str} = ?, `;
58        placeholder = placeholder.substring(0, placeholder.length - 2);
59
60        this.db.transaction( t =>
61            t.executeSql(` UPDATE ${table} SET ${placeholder} WHERE id = ?`, data),
62            (t, e) => alert('Update row: ' + e.message),
63            () => console.log('update success')
64        );
65     }
66
67    static delete(table, id) {
68        if(confirm(`Delete ID: ${id} ?`)) {
69            this. db.transaction( t => t.executeSql(`DELETE FROM ${table} WHERE id = ?`, [id]));
70        }
71    }
72
73    static clearDB(table) {
74        if(confirm('Clear the entire table?')) {
75            this.db.transaction( t => t.executeSql(`DELETE FROM ${table}`));
76        }
77    }
78}
79
80class Person extends Websql{
81    static get dbName() {
82        return 'testDb2';
83    }
84
85    static get createSQL() {
86        return ['CREATE TABLE IF NOT EXISTS table2 (id INTEGER PRIMARY KEY, name TEXT)',
87                'CREATE TABLE IF NOT EXISTS table3 (id INTEGER PRIMARY KEY, name TEXT, address TEXT)',
88                'CREATE TABLE IF NOT EXISTS table4 (id INTEGER PRIMARY KEY, name TEXT, address TEXT)'];
89    }
90
91    static dispResults(table, orderBy = 'name') {
92        if(this.db) {
93            this.db.readTransaction( t => { // readTransaction sets the database to read-only
94                t.executeSql(`SELECT * FROM ${table} ORDER BY LOWER(${orderBy})`, [],
95                    (t, r) => {
96                        const row = [];
97                        for( var i = 0; i < r.rows.length; i++ ) {
98                            let res = r.rows.item(i);
99                            row.push(res);
100                            //console.log(res.name);
101                        }
102                        console.log(row);
103                    }
104                );
105            });
106        }
107    }
108
109    static countRows(table) {
110        if(!this.db) return;
111        this.db.readTransaction( t => {
112            t.executeSql(`SELECT COUNT(*) AS c FROM ${table}`, [],
113            (t, r) => {
114                let res = r.rows.item(0).c;
115                // display here the result
116                console.log(res);
117            },
118            (t, e) => alert('countRows: ' + e.message));
119        });
120    }
121}