Cordova import big files into database using transactions

Question

I have a txt file of about 17 mb that I have to parse, splitting it line by line and then to add it into the database using transactions. If the file is too large and I try to open it, the application will run out of memory, so I tried to read it in pieces and then import each piece into the database. Due to the transactions, the data entered in DB is not correct. There is a part of the used code:

await file_reader.resolveLocalFilesystemUrl(path + file).then(async (file_entry: any) => {

  await file_entry.file(async (file) => {

    let reader = new FileReader();

     reader.onprogress = async (reader_result: any) => {

       let loaded = _.cloneDeep(reader_result.loaded);
       let total = _.cloneDeep(reader_result.total);
       let is_last_element: boolean = _.cloneDeep(loaded == total);
       let i: number = 0;
       let document_length = this.sync_parser.getReaderLength();
       let event_type: number = this.sync_parser.getEventType();

       content = iconv.encode(reader.result, encoding).toString();

       await this.db.db.transaction(async (database: any) => {
         while (document_length >= i) {
           if (event_type == SyncParserIo.START_TAG) {
             this.table = await this.newHeader(this.sync_parser.getName());
           } else if (event_type == SyncParserIo.END_TAG) {
             // this.file_content = null;
           } else if (event_type == SyncParserIo.ROW) {
             // here I execute basic_update_insert function 
           }
           event_type = this.sync_parser.next(i);
           i++;
         }

       }).then(()=>{
        this.logger.info(this.TAG, "End document from transaction");
       }).catch((e)=>{
        //log
       });

       if (is_last_element) {
        resolve(true);
       }

     };

     await reader.readAsBinaryString(file);
  }); 
}).catch((e) => {
  this.logger.error("FileSystem Error", e.message);
  return reject(e);
});


protected basic_update_insert(table, rows_map, where, where_bindings, database?) {
 let db_query = database != null ? database : this.database;
 let update_query_util: any = DbUtil.update(table, rows_map, where, where_bindings);
 let insert_query_util: any = DbUtil.insert(table, rows_map);

 this.import_result = null;

 db_query.executeSql(update_query_util.query, update_query_util.bindings, (tx, res) => {

   if (res.rowsAffected === 0) {
     tx.executeSql(insert_query_util.query, insert_query_util.bindings, (tx2, insert_result) => {
       if (insert_result.insertId != null) {
         this.import_result = ImporterIo.RESULT_OK;
       }
     }, (e) => {
       this.import_result = ImporterIo.ERROR_INSERT_ROW;
     });

   } else if (res.rowsAffected === 1) {
     this.import_result = ImporterIo.RESULT_OK;
   } else if (res.rowsAffected > 1) {
     this.import_result = ImporterIo.RESULT_OK;
   }

 }, (e) => {
   this.logger.error(this.TAG, `error from ${table} update`, e);
   this.import_result = ImporterIo.ERROR_UPDATE_ROW;
 });
}

Answer 1

You may find cordova-sqlite-porter to be useful (there is an Ionic Native Typescript wrapper). It wraps the SQLite DB API, allowing you to pass it a data dump formatted either as SQL statements or as JSON.

With such a large amount of data, it may be in your interests to convert the data to the JSON structure supported by the plugin since it translates the JSON into batched inserts which, in the plugin example project, leads to an observable performance improvement when importing data of 100 times faster.

Alternatively, you may wish to rework your SQL INSERT statements manually to make use of the UNION SELECT optimisation as outlined in this answer.