· 9 years ago · Dec 07, 2016, 08:36 AM
1import {Injectable} from "@angular/core";
2import {SQLite} from "ionic-native";
3
4const DB_NAME: string = '__rimsapp';
5const win: any = window;
6
7@Injectable()
8export class Sql {
9 private _db: any;
10
11 constructor() {
12 if (win.sqlitePlugin) {
13 let db = new SQLite();
14 this._db = db.openDatabase({
15 name: DB_NAME,
16 location: 'default'
17 });
18 } else {
19 console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
20 this._db = win.openDatabase(DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
21 }
22 this._tryInit();
23 }
24
25 // Initialize the DB with our required tables
26 _tryInit() {
27 this.query('CREATE TABLE IF NOT EXISTS kv (key text primary key, value text)').catch(err => {
28 console.error('Storage: Unable to create initial storage tables', err.tx, err.err);
29 });
30 }
31
32 /**
33 * Perform an arbitrary SQL operation on the database. Use this method
34 * to have full control over the underlying database through SQL operations
35 * like SELECT, INSERT, and UPDATE.
36 *
37 * @param {string} query the query to run
38 * @param {array} params the additional params to use for query placeholders
39 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
40 */
41 query(query: string, params: any[] = []): Promise<any> {
42 return new Promise((resolve, reject) => {
43 try {
44 this._db.transaction((tx: any) => {
45 tx.executeSql(query, params,
46 (tx: any, res: any) => resolve({tx: tx, res: res}),
47 (tx: any, err: any) => reject({tx: tx, err: err}));
48 },
49 (err: any) => reject({err: err}));
50 } catch (err) {
51 reject({err: err});
52 }
53 });
54 }
55
56 /**
57 * Get the value in the database identified by the given key.
58 * @param {string} key the key
59 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
60 */
61 get(key: string): Promise<any> {
62 return this.query('select key, value from kv where key = ? limit 1', [key]).then(data => {
63 if (data.res.rows.length > 0) {
64 return data.res.rows.item(0).value;
65private dbConnections: {[dbName:string]:any} = {};
66
67 private hasSQLite: boolean = false;
68
69 constructor(private storage: Storage) {
70 //noinspection TypeScriptUnresolvedVariable
71 if (!!win.sqlitePlugin)
72 this.hasSQLite = true;
73
74 this.initDb();
75 }
76
77 private initDb(db: string = 'firebaseDataStore') {
78 if (this.hasSQLite) {
79 let dbInit = new SQLite();
80 this.dbConnections[db] = dbInit.openDatabase({
81 name: db,
82 location: 'default'
83 });
84 } else {
85 this.dbConnections[db] = win.openDatabase(db, '1.0', 'database', 5 * 1024 * 1024);
86 }
87 this.query(db, 'CREATE TABLE IF NOT EXISTS kv (key text primary key, value text)').catch(err => {
88 console.error('Storage: Unable to create initial storage tables', err.tx, err.err);
89 });
90 }
91
92 public getJSON(key: string, db:string = 'firebaseDataStore'): Promise<any> {
93 return this.get(key, db).then(value => {
94 try {
95 return JSON.parse(value);
96 } catch (e) {
97 console.warn('Storage getJson(): unable to parse value for key', key, ' as JSON');
98 throw e; // rethrowing exception so it can be handled with .catch()
99 }
100 });
101 }
102
103 public setJSON(key: string, data: any, db: string = 'firebaseDataStore') {
104 try {
105 return this.set(key, JSON.stringify(data), db);
106 } catch (e) {
107 return Promise.reject(e);
108 }
109 }
110
111 /**
112 * Get the value in the database identified by the given key.
113 * @param {string} key the key
114 * @param {string} db the database to target
115 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
116 */
117 public get(key: string, db: string = 'firebaseDataStore'): Promise<any> {
118 return this.query(db, 'select key, value from kv where key = ? limit 1', [key]).then(data => {
119 if (data.res.rows.length > 0) {
120 return data.res.rows.item(0).value;
121 }
122 });
123 }
124
125 /**
126 * Set the value in the database for the given key. Existing values will be overwritten.
127 * @param {string} key the key
128 * @param {string} value The value (as a string)
129 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
130 */
131 set(key: string, value: string, db:string = 'firebaseDataStore'): Promise<any> {
132 return this.query(db, 'insert or replace into kv(key, value) values (?, ?)', [key, value]);
133 }
134
135 // public getJSON(key: string, db: string = 'firebaseDataStore'): Promise<any> {
136 // return new Promise<any>((resolve, reject) => {
137 // this.storage.get(key).then(val => resolve(JSON.parse(val)), err => reject(err));
138 // });
139 // }
140
141 public getDataSet(keyPrefix: string, db: string = 'firebaseDataStore'): Promise<any[]> {
142 return new Promise<any[]>((resolve, reject) => {
143 resolve(null);
144 this.query(db, "SELECT * FROM kv WHERE key LIKE '" + keyPrefix + "%';").then(resultSet => {
145 resolve(resultSet);
146 }, err => reject(err));
147 });
148 }
149
150 /**
151 * Perform an arbitrary SQL operation on the database. Use this method
152 * to have full control over the underlying database through SQL operations
153 * like SELECT, INSERT, and UPDATE.
154 *
155 * @param {string} [db] the database to target
156 * @param {string} query the query to run
157 * @param {array} params the additional params to use for query placeholders
158 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
159 */
160 query(db: string = 'firebaseDataStore', query: string, params: any[] = []): Promise<any> {
161 return new Promise((resolve, reject) => {
162 try {
163 if(!this.dbConnections[db])
164 this.initDb(db);
165
166 this.dbConnections[db].transaction((tx: any) => {
167 tx.executeSql(query, params,
168 (tx: any, res: any) => resolve({tx: tx, res: res}),
169 (tx: any, err: any) => reject({tx: tx, err: err}));
170 },
171 (err: any) => reject({err: err}));
172 } catch (err) {
173 reject({err: err});
174 }
175 });
176 }
177
178 /**
179 * Remove the value in the database for the given key.
180 * @param {string} key the key
181 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
182 */
183 remove(key: string, db:string = 'firebaseDataStore'): Promise<any> {
184 return this.query(db, 'delete from kv where key = ?', [key]);
185 }
186
187 /**
188 * Clear all keys/values of your database.
189 * @return {Promise} that resolves or rejects with an object of the form { tx: Transaction, res: Result (or err)}
190 */
191 clear(db:string = 'firebaseDataStore'): Promise<any> {
192 return this.query(db, 'delete from kv');
193 }
194}