Ionic-native sqlite issue

Ionic Framework ionic-v3
18

Hello @niravparsana94
Thank you for saving me time !

Because I’ll use many tables, so I refactor your code to use with many tables with CRUD queries generation.
Share it with you :slight_smile:

db.ts

import {Injectable} from "@angular/core";
import "rxjs/add/operator/map";
import {Platform} from "ionic-angular";

/*
 Generated class for the DbProvider provider.

 See https://angular.io/docs/ts/latest/guide/dependency-injection.html
 for more info on providers and Angular 2 DI.
 */

const DB_NAME: string = 'FeroUser';
const win: any = window;
export enum TABLES{Product, User}
;
@Injectable()
export class DbProvider {
  private _dbPromise: Promise<any>;

  constructor(public platform: Platform) {
    this._dbPromise = new Promise((resolve, reject) => {
      try {
        let _db: any;
        this.platform.ready().then(() => {
          if (this.platform.is('cordova') && win.sqlitePlugin) {
            //FOR MOBILE DEVICE
            _db = win.sqlitePlugin.openDatabase({
              name: DB_NAME,
              location: 'default'
            });
          } else {
            //FOR WEBSQL
            console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
            _db = win.openDatabase(DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
          }
          resolve(_db);
        });
      } catch (err) {
        reject({err: err});
      }
    });
    this._tryInit();
  }

  // Initialize the DB with our required tables
  _tryInit(drop = false) {
    if (drop) {
      this.dropTable(TABLES.User);
      this.dropTable(TABLES.Product);
    }
    this.createProductTable();
    this.createUserTable();
  }

  private dropTable(table: TABLES) {
    this.query("DROP TABLE " + TABLES[table]
    ).catch(err => {
      console.error('Storage: Unable to create initial storage User table', err.tx, err.err);
    });
  }

  private createProductTable() {
    this.query(`
      CREATE TABLE IF NOT EXISTS ` + TABLES[TABLES.Product] + ` (
                         id	INTEGER PRIMARY KEY AUTOINCREMENT,
                         name	TEXT NOT NULL UNIQUE
                     )
    `).catch(err => {
      console.error('Storage: Unable to create initial storage PRODUCT tables', err.tx, err.err);
    });
  }

  private createUserTable() {
    this.query(`
      CREATE TABLE IF NOT EXISTS ` + TABLES[TABLES.User] + ` (
                         id	INTEGER PRIMARY KEY AUTOINCREMENT,
                         name	DATE NOT NULL UNIQUE
                         birthday	DATE NOT NULL
                     )
    `).catch(err => {
      console.error('Storage: Unable to create initial storage User table', err.tx, err.err);
    });
  }

  list(table: TABLES): Promise<any> {
    return this.query('SELECT * FROM ' + TABLES[table]).then(data => {
      if (data.res.rows.length > 0) {
        console.log('Rows found.');
        if (this.platform.is('cordova') && win.sqlitePlugin) {
          let result = [];

          for (let i = 0; i < data.res.rows.length; i++) {
            var row = data.res.rows.item(i);
            result.push(row);
          }
          return result;
        }
        else {
          return data.res.rows;
        }
      }
    });
  }

  insert(newObject, table: TABLES): Promise<any> {
    return this.query('INSERT INTO ' + TABLES[table] + ' (' + this.getFieldNamesStr(newObject)
      + ') VALUES (' + this.getFieldValuesStr(newObject) + ")", []);
  }

  private getFieldNamesStr(newObject) {
    let fields = '';
    for (let f in newObject) {
      if (f !== "id") fields += f + ',';
    }
    fields = fields.substr(0, fields.length - 1);
    return fields;
  }

  private getFieldValuesStr(object) {
    let fields = '';
    for (let f in object) {
      if (f !== "id") fields += '\"' + object[f] + '\",';
    }
    fields = fields.substr(0, fields.length - 1);
    return fields;
  }

  update(object, table: TABLES): Promise<any> {
    return this.query('UPDATE ' + TABLES[table] + ' SET ' + this.getFieldSetNamesStr(object) + ' WHERE id=?',
      this.getFieldValuesArray(object));
  }

  private getFieldSetNamesStr(object) {
    let fields = '';
    for (let f in object) {
      if (f !== "id") fields += f + "=? ,";
    }
    fields = fields.substr(0, fields.length - 1);
    return fields;
  }

  private getFieldValuesArray(object) {
    let fields = [];
    for (let f in object) {
      if (f !== "id") fields.push(object[f]);
    }
    fields.push(object.id);
    return fields;
  }

  delete(table: TABLES, object): Promise<any> {
    let query = "DELETE FROM " + TABLES[table] + " WHERE id=?";
    return this.query(query, object.id);
  }

  query(query: string, params: any[] = []): Promise<any> {
    return new Promise((resolve, reject) => {
      try {
        this._dbPromise.then(db => {
          db.transaction((tx: any) => {
              tx.executeSql(query, params,
                (tx: any, res: any) => resolve({tx: tx, res: res}),
                (tx: any, err: any) => reject({tx: tx, err: err}));
            },
            (err: any) => reject({err: err}));
        });
      } catch (err) {
        reject({err: err});
      }
    });
  }
}

To use any of CRUD method (list, insert, update , delete), just pass the object when needed, and the name of table from the enum TABLES{product, user, …}.

In this way, when add a new table, just add it to the enum TABLES :wink:

Thank you again.

1 Like