Ionic with SQLite

SQLite

SQLite is basically a software library that implements a self-contained, server less, transactional sql database. It has a higher performance, loading content from SQLite database is faster than loading content out of individual file.

We use

·         ionic-native/sqlite

·         cordova-sqlite-storage

In app.module.ts, we use SQLite as provider

@NgModule({

  declarations: [AppComponent, DetailsComponent,HomePage, TestComponent],

  entryComponents: [],

  imports: [BrowserModule, IonicModule.forRoot(), AppRoutingModule,FormsModule,IonicModule ],

  providers: [

    StatusBar,

    SplashScreen,

    SQLite,

    { provide: RouteReuseStrategy, useClass: IonicRouteStrategy }

  ],

  bootstrap: [AppComponent]

})

We make a database companydetails, the design will be in the following format. We are trying to calculate the total experience in each company and show to the user.

id	[from]	[to]	description	companyName	isCurrent
INTEGER PRIMARY KEY	TEXT	TEXT	TEXT	TEXT	INTEGER

In order to create a database, we use

getData() {

    try {

      this.sqlite.create({

        name: 'ionicdb.db',

        location: 'default'

      }).then((db: SQLiteObject) => {

        db.executeSql('CREATE TABLE IF NOT EXISTS companyDetails(id INTEGER PRIMARY KEY, [from] TEXT , [to] TEXT , description TEXT , companyName TEXT,isCurrent INTEGER )', [])

          .then(res => {

            this.showmsg();

            this.BindData(db);

          })

          .catch(e => this.showmsg(e.message));

      })

    } catch (error) {

      this.showmsg(error.message);

    }

  }

Then we get the data by the following code,

  BindData(db) {

    db.executeSql('SELECT * FROM companyDetails ORDER BY id DESC', [])

      .then(res => {

        this.showmsg()

        this.count = res.rows.length

        this.companyDetailsList = []

        for (var i = 0; i < res.rows.length; i++) {

          var item = res.rows.item(i);

          if (item.isCurrent == 1) {

            item.to = new Date().toISOString().slice(0, 10);

            item.companyName += "(current)"

          }

          res.rows.item(i).isCurrent = parseInt(res.rows.item(i).isCurrent);

          this.companyDetailsList.push({ id: item.id, from: item.from, to: item.to, description: item.description, companyName: item.companyName, datediff: this.calcDate(item.from, item.to), isCurrent: item.isCurrent })

        }

        var tmonth: number = 0;

        var tyear: number = 0;

        var tday: number = 0;

        this.companyDetailsList.forEach(element => {

          tyear += element.datediff.years

          tmonth += element.datediff.months

          tday += element.datediff.days

        });

        this.calulateExperience(tday, tmonth, tyear);

      })

      .catch(e => this.showmsg(e.message));

  }

We calculate the difference of each duration of each experience

calcDate(from, to) {

    var message = "";

    var from1 = from.split('-')

    var to1 = to.split('-')

    var a = moment(to1);

    var b = moment(from1);

    var years = a.diff(b, 'year');

    b.add(years, 'years');

    var months = a.diff(b, 'months');

    b.add(months, 'months');

    var days = a.diff(b, 'days');

    if (years > 0) {

      message = years + ' years ' + months + ' months ' + days + ' days'

    }

    else {

      message = months + ' months ' + days + ' days'

    }

    return { years: years, months: months, days: days, message: message }

  }

In order to add experience

addData(obj) {

    try {

      this.sqlite.create({

        name: 'ionicdb.db',

        location: 'default'

      }).then((db: SQLiteObject) => {

        db.executeSql('INSERT INTO companyDetails VALUES(NULL,?,?,?,?,?)',

          [obj.from, obj.to, obj.description, obj.companyName, 0])

          .then(res => {

            this.BindData(db);

            this.showmsg()

            this.presentToast('Experience added successfully')

          })

          .catch(e => this.showmsg(e.message));

      })

    } catch (error) {

      this.showmsg(error.message);

    }

  }

In order to show a toast message to user

  async presentToast(msg) {

    const toast = await this.toastController.create({

      message: msg,

      duration: 2000

    });

    toast.present();

  }

In order to edit experience detail

editData(obj) {

    try {

      this.sqlite.create({

        name: 'ionicdb.db',

        location: 'default'

      }).then((db: SQLiteObject) => {

        db.executeSql('update companyDetails set [from]=?,[to]=?,description=?,companyName=? where id=?',

          [obj.from, obj.to, obj.description, obj.companyName, this.eId])

          .then(res => {

            this.showmsg()

            this.presentToast('Experience edited successfully')

          })

          .catch(e => this.showmsg(e.message));

      })

    } catch (error) {

      this.showmsg(error.message);

    }

  }