· 8 years ago · Dec 11, 2017, 11:46 AM
1/**
2 * Database class.
3 * Usage :
4 *
5 * var db = new database("projectdatabase"); // Makes or connects database 'projectdatabase'
6 *
7 * var table = db.table('profiles', ["name", "picture", "email"]); // Makes or opens table 'profiles', with given fields.
8 *
9 * table.insert( {name: "Bart", picture: "nonte", email: "bartjakobs@gmail.com"} ); // inserts stuff
10 *
11 * table.getWhere( {name: "Bart"},
12 * function(rows) { console.log(rows }
13 * ); // Receives data using callback function
14 *
15 * @author Bart
16 */
17var database = (function(){
18
19 /**
20 * Create new database or connect to existing with this name.
21 * @param {String} name Database name
22 * @return {Object} Database object.
23 */
24 function database(name){
25 this.db = openDatabase(name, '1.0', 'Database', 2 * 1024 * 1024);
26 this.name = name;
27 }
28
29 /**
30 * Invisible table constructor
31 * @param {String} name Table name
32 * @param {Object} dbConnection database object
33 * @param {Object} columns Columns needed to insert if the table does not exist.
34 * @return {Object} Table object
35 */
36 function table(name, dbConnection, columns){
37 this.name = name;
38 this.dbobj = dbConnection;
39 this.db = dbConnection.db;
40 this.columns = columns
41 }
42
43 /**
44 * Open a table in this database.
45 * @param {String} name table name
46 * @param {Object} obj Columns needed to create this table
47 * @return {Object} Table object.
48 */
49 database.prototype.table = function(name, obj) {
50 var sql = 'CREATE TABLE IF NOT EXISTS '+name;
51 var fields = obj.join(', ');
52 sql = sql + '('+fields+')';
53
54 this.db.transaction(function (tx) {
55 tx.executeSql(sql);
56 });
57 return new table(name, this, obj);
58 };
59
60 /**
61 * Insert a row into this table
62 * @param {Object} obj key-value pairs to insert
63 */
64 table.prototype.insert = function(obj){
65 var columns = Object.keys(obj);
66
67 var safeValues = [];
68 var values = [];
69 for (var i = 0; i<columns.length; i++) {
70 safeValues.push('?');
71 values.push(obj[columns[i]]);
72 };
73
74 columns = columns.join(', ');
75 safeValues = safeValues.join(', ');
76 var sql = 'INSERT INTO '+this.name+' ('+columns+') VALUES ('+safeValues+')';
77 this.db.transaction(function (tx) {
78 tx.executeSql(sql, values);
79 });
80 };
81
82 /**
83 * Get all rows from this table
84 * @param {Function} callback callback function, will receive the rows as an array.
85 */
86 table.prototype.getAll = function(callback) {
87 var sql = 'SELECT * FROM '+this.name;
88 this.db.transaction(function (tx) {
89 tx.executeSql(sql, [] ,
90 function(transaction, results){
91 var rows = results.rows;
92 var rowsArray = [];
93 for (var i=0; i<rows.length; i++){
94 rowsArray.push(rows.item(i));
95 }
96 callback(rowsArray);
97 }
98 );
99 });
100 };
101
102 /**
103 * Get from this table using a WHERE statement
104 * @param {Object} where key-value pairs to find
105 * @param {Function} callback Callbackfunction, will receive found rows as an array.
106 */
107 table.prototype.getWhere = function(where, callback) {
108 where = setWhere(where);
109 var sql = 'SELECT * FROM '+this.name+' WHERE '+where.str;
110
111 this.db.transaction(function (tx) {
112 tx.executeSql(sql, where.values,
113 function(transaction, results){
114 var rows = results.rows;
115 var rowsArray = [];
116 for (var i=0; i<rows.length; i++){
117 rowsArray.push(rows.item(i));
118 }
119 callback(rowsArray);
120 }
121 );
122 });
123 };
124
125 /**
126 * Remove a row from the table.
127 * @param {Object} where {column : value}
128 */
129 table.prototype.removeWhere = function(where){
130 where = setWhere(where);
131 var sql = 'DELETE FROM '+this.name+' WHERE '+where.str;
132 this.db.transaction(function (tx) {
133 tx.executeSql(sql, where.values);
134 });
135 };
136
137
138
139 // ooooo ooooo oooo .o88o. . o8o
140 // `888' `888' `888 888 `" .o8 `"'
141 // 888 888 .ooooo. 888 oo.ooooo. .ooooo. oooo d8b o888oo oooo oooo ooo. .oo. .ooooo. .o888oo oooo .ooooo. ooo. .oo. .oooo.o
142 // 888ooooo888 d88' `88b 888 888' `88b d88' `88b `888""8P 888 `888 `888 `888P"Y88b d88' `"Y8 888 `888 d88' `88b `888P"Y88b d88( "8
143 // 888 888 888ooo888 888 888 888 888ooo888 888 888 888 888 888 888 888 888 888 888 888 888 888 `"Y88b.
144 // 888 888 888 .o 888 888 888 888 .o 888 888 888 888 888 888 888 .o8 888 . 888 888 888 888 888 o. )88b
145 // o888o o888o `Y8bod8P' o888o 888bod8P' `Y8bod8P' d888b o888o `V88V"V8P' o888o o888o `Y8bod8P' "888" o888o `Y8bod8P' o888o o888o 8""888P'
146 // 888
147 // o888o
148
149
150 /**
151 * Parse a Where object and make a string and a values array.
152 * @param {Object} where {str: 'KEY1 = ? & KEY2 = ? etc', values: ['value1', 'value2']}
153 */
154 function setWhere(where){
155 var columns = Object.keys(where);
156 var whereStr = columns.join(' = ? AND ')+' = ?';
157 var values = [];
158 for (var i = 0; i<columns.length; i++) {
159 values.push(where[columns[i]]);
160 };
161 return {str: whereStr, values: values};
162 }
163
164 return database;
165})();