Ionic3 - fastest way to syncronize big JSON file data with Sqlite

Viewed 92

Good afternoon, I am creating an application in ionic using angular where currently it handles two connection modes: connected if the user has internet access and disconnected if they do not have it

Currently I have a feature and it is that the user has the possibility to sync data and apply in SQLite

like this JSON (to syncronize)

{
   countries: [/* 200+ records */],
   vaccine: [/* 3000+ records */],
   ... other 20 keys
}

and the SQLite structure(mobile) has a table per key like this

CREATE TABLE IF NOT EXISTS countries(
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL,
    name TEXT NULL,
    --- other columns
);

CREATE TABLE IF NOT EXISTS vaccine(
    id INTEGER PRIMARY KEY,
    name TEXT NULL,
    --- other columns
);

How can I execute the synchronization process without executing sql per cycle? but validating that if the row exists, update it

public descargar(clave): void {
  this.descargarParametricas().subscribe(
    parametros => {
      // make process by key
      Object.keys(parametros).forEach(
        index => this.descargarParametrica(index, parametros[index]) : void(0)
      );
    }
  );
}

public descargarParametrica( key: string, parametros: any[] ){
  parametros.forEach( item => {
    const headers = Object.keys(item);
    const rows = Object.values(item).map( item => item === '' ? 'NULL' : item );
    const sqlQuery = `INSERT INTO ${key}(${headers.join()}) VALUES (${rows.join()});`;
    // execute query per loop is too slow, only countries data(200 records) affect performance and stop
    this.datalayer.execute(sqlQuery);
  })
}

this.descargarParametricas() return a JSON 40kb size, the problem is that the insertion process must be done by the key of the json and update if the row exist, actually have a performance issues because the insertion/update process execute row per row

thanks for all

0 Answers
Related