Big Initial Insert of Data: Are there any things one would need to watch out for?

[folsze]

Just a quick question, I am not sure how this plugin handles such a scenario, maybe there is some functionaltiy that automatically handles this?

I am making a big app release next week and I am not sure whether I need to consider special things for a big initial data insert.
I mean the user could just close the app during the insert. and then ends up with an unsuable app? Maybe I need to clear the database if only half the data gets

I am doing a big initial insert of data at the start of my app. It takes around 1-3 seconds.
I think one of the example app has an initial insert too not sure anymore.
Are there any things one has to consider for this?

I am using WAL:

if (this.platform !== 'android') {
  await db.execute(`PRAGMA journal_mode=WAL;`,false);
}

and this:

const transaction = ['ios', 'android'].includes(this.platform);
return this.mDb.run(stmt, values, transaction))

But I am not explicitly using any transactions or anything. Should I? Or do they get done automatically? Can things go wrong, maybe someone knows sth about this?

[jepiqueau]

@folsze There is 3 ways of using transaction

• default

  const testTransactionDefault = async (db: SQLiteDBConnection) => {
    setLog(prevLog => prevLog + '### Start Transaction Default ###\n');

    if (db !== null) {
      // Delete all data if any
      await db.execute('DELETE FROM DemoTable');

      // run command
      let insertQuery = 'INSERT INTO DemoTable (name, score) VALUES (?, ?);';
      let bindValues = ["Sue", 102];
      let ret = await db.run(insertQuery, bindValues);
      console.log(`>>> run ret 1: ${JSON.stringify(ret)}`)
      // execute command
      const statements = `
        INSERT INTO DemoTable (name, score) VALUES ('Andrew',415);
        INSERT INTO DemoTable (name, score) VALUES ('Josh',310);
        INSERT INTO DemoTable (name, score) VALUES ('Billy',253);
      `;
      ret = await db.execute(statements);
      console.log(`>>> execute ret 1: ${JSON.stringify(ret)}`)

      // executeSet command
      ret = await db.executeSet(setScores);
      console.log(`>>> executeSet 1 ret: ${JSON.stringify(ret)}`)

      let selectQuery = "SELECT * /* all columns */ FROM Demotable;";
      const retQuery = await db.query(selectQuery);
      console.log(`>>> query All retQuery: ${JSON.stringify(retQuery)}`)

    }     
    setLog(prevLog => prevLog + '### End Test Transaction Default ###\n');

  }

here each individual command implement automatically the transaction mode

• manage by yourself

  const testTransactionManage = async (db: SQLiteDBConnection) => {
    setLog(prevLog => prevLog + '### Start Transaction Manage ###\n');
    if (db !== null) {
      await db.beginTransaction();
      const isTransAct = await db.isTransactionActive();
      if(!isTransAct) {
        throw new Error('db Transaction not Active');
      }
      try {
        // run command
        let insertQuery = 'INSERT INTO DemoTable (name, score) VALUES (?, ?);';
        let bindValues = ["Betty", 152];
        let ret = await db.run(insertQuery, bindValues, false);
        console.log(`>>> run ret 2: ${JSON.stringify(ret)}`)
        // execute command
        const statements = `
          INSERT INTO DemoTable (name, score) VALUES ('Aimie',115);
          INSERT INTO DemoTable (name, score) VALUES ('Test1',330);
          INSERT INTO DemoTable (name, score) VALUES ('George',223);
        `;
        ret = await db.execute(statements,false);
        console.log(`>>> execute ret 2: ${JSON.stringify(ret)}`)

        // executeSet command
        ret = await db.executeSet(setScores1,false);
        console.log(`>>> executeSet 2 ret: ${JSON.stringify(ret)}`)


        // Commit Transaction
        await db.commitTransaction()
        if (platform === 'web') {
          await sqliteService.saveToStore(dbName);
        }
        setLog(prevLog => prevLog + '### Commit Test Transaction Manage ###\n');
      } catch (err) {
        console.log(`in catch : ${err}`)
        // Rollback Transaction
        await db.rollbackTransaction()
        setLog(prevLog => prevLog + '### RollBack Test Transaction Manage ###\n');

      } finally {
        let selectQuery = "SELECT * /* all columns */ FROM Demotable;";
        const retQuery = await db.query(selectQuery);
        console.log(`>>> query All retQuery2: ${JSON.stringify(retQuery)}`)
        setLog(prevLog => prevLog + '### End Test Transaction Manage ###\n');
      }
    }
  }

here it is obvious that you manage yourself the transaction process.

• executeTransaction

  const testExecuteTransaction = async (db: SQLiteDBConnection) => {
    setLog(prevLog => prevLog + '### Start Execute Transaction ###\n');
    if (db !== null) {
      const txn: any[] = [];
      txn.push({statement:'DELETE FROM DemoTable;'});
      const stmt = 'INSERT INTO DemoTable (name, score) VALUES (?, ?);';
      for (let i=0; i<100; i++ ) {
        const values = [`test${i}`, getRandomNumber(1, 1000)];
        txn.push({statement: stmt, values: values});
      }
      txn.push({statement: "DELETE FROM DemoTable WHERE name='test50';"});
      txn.push({statement:"UPDATE DemoTable SET score = ? WHERE name = ?",
      values:[152,"test10"]});
      try {
        const ret = await db.executeTransaction(txn);
        console.log(`testExecuteTransaction ret: ${JSON.stringify(ret)}`);
        setLog(prevLog => prevLog + '### Test ExecuteTransaction successfull###\n');

      } catch(err:any) {
        const msg = err.message ? err.message : err;
        console.log(`testExecuteTransaction msg: ${msg}`)
        setLog(prevLog => prevLog + `### Test ExecuteTransaction failed : ${msg} ###\n`);

      } finally {
        let selectQuery = "SELECT * /* all columns */ FROM Demotable;";
        const retQuery = await db.query(selectQuery);
        console.log(`>>> query All retQuery3: ${JSON.stringify(retQuery)}`)
        setLog(prevLog => prevLog + '### End Test ExecuteTransaction ###\n');
      }

    }   
  }

it is like the second one but the transaction process i set automatically for the transaction task given as parameter of the command.

so for big insert you must use process 2 or 3 at your convenience
Hope my answer is clear enough