Nested Insert

[iphonic]

Hi, Thanks for the library. We have been looking for a solution for nested insertion in multiple tables. We are looking for the correct way of doing it. However, we are able to insert the data but it is insanely slow, though the data is not so big. We are assuming the problem lies in our approach.

This is sample data how it looks

{
    "category": [
      {
          "idcategory": "1031",
          "categoryname": "Something",
          "categorytype": "block",
          "categoryphrase": "Something?",
          "categoryorder": "1",
          "question1": [
              {
                  "idcategory": "1031",
                  "idquestion1": "10045",
                  "questiontext": "Some question ",
                  "question1phrase": "Some question?",
                  "questionorder": "1",
                  "questiontype": "block",
                  "question2": [
                      {
                          "idquestion1": "10045",
                          "idquestion2": "1063",
                          "questiontext": "Some answer",
                          "question2phrase": "###",
                          "questionorder": "1",
                          "question2session": [
                              {
                                  "idquestion2session": "1518",
                                  "idsession": "1203",
                                  "question2sessionorder": "1",
                                  "idquestion2": "1063"
                              },
                              {
                                  "idquestion2session": "1519",
                                  "idsession": "1205",
                                  "question2sessionorder": "2",
                                  "idquestion2": "1063"
                              },
                              {
                                  "idquestion2session": "1520",
                                  "idsession": "1283",
                                  "question2sessionorder": "3",
                                  "idquestion2": "1063"
                              },
                              {
                                  "idquestion2session": "1521",
                                  "idsession": "1284",
                                  "question2sessionorder": "4",
                                  "idquestion2": "1063"
                              }
                          ]
                      },
                      {
                          "idquestion1": "10045",
                          "idquestion2": "1061",
                          "questiontext": "I'm afraid of a feeling",
                          "question2phrase": "###",
                          "questionorder": "2",
                          "question2session": [
                              {
                                  "idquestion2session": "771",
                                  "idsession": "1109",
                                  "question2sessionorder": "1",
                                  "idquestion2": "1061"
                              },
                              {
                                  "idquestion2session": "772",
                                  "idsession": "1112",
                                  "question2sessionorder": "2",
                                  "idquestion2": "1061"
                              },
                              {
                                  "idquestion2session": "773",
                                  "idsession": "1120",
                                  "question2sessionorder": "3",
                                  "idquestion2": "1061"
                              },
                              {
                                  "idquestion2session": "774",
                                  "idsession": "1201",
                                  "question2sessionorder": "4",
                                  "idquestion2": "1061"
                              },
                              {
                                  "idquestion2session": "775",
                                  "idsession": "1241",
                                  "question2sessionorder": "5",
                                  "idquestion2": "1061"
                              }
                          ]
                      }
      ]}

Similarly, we have 13 categories and more or less the same data. Now what we are doing to insert this is as follows

1. First we are looping through all categories and checking if that exists in DB or not, if not Insert else update, on completion of each category insert we are inserting linked questions as shown in the data above in other function, shown next..

function updateDatabaseFromServerResponse(dicResponse,idlanguage,completion){
  var cats=maindata['category']

    cats.map((category)=>{
        db.executeSql(`select count(*) as count from categories where categoryid = "${category.idcategory}"`,[],(tx,result)=>{
            let row = tx.rows.item(0);
            if(row.count == 0){
                db.transaction((tx)=>{
                    tx.executeSql(`insert into categories (categoryid,categoryname,categorytype,categoryphrase,categoryorder,languageId) values("${category.idcategory}","${category.categoryname}","${category.categorytype}","${category.categoryphrase}","${category.categoryorder}","${idlanguage}")`,[],()=>{
                        console.log("Added category ",category.idcategory);
                        addupdateCategoryQuestion1(db,category)
                    },errorCB)
                })
            }else{
                db.transaction((tx)=>{
                    tx.executeSql(`update categories set categoryname="${category.categoryname}",categorytype="${category.categorytype}",categoryphrase="${category.categoryphrase}",categoryorder="${category.categoryorder}",languageId="${idlanguage}" where categoryid = "${category.idcategory}"`,[],()=>{
                        console.log("Updated category ",category.idcategory);
                        addupdateCategoryQuestion1(db,category)
                    },errorCB)
                })
            }
        },errorCB)

    })
}

2. We are doing same checking for to Insert or Update and doing the operation for questions1 here in category.question1 array loop

function addupdateCategoryQuestion1(db,category){
    var arrQuestion1=category.question1

    arrQuestion1.map((question)=>{
        db.executeSql(`select count(*) as count from question1 where questionid = "${question.idquestion1}"`,[],(tx,result)=>{
            let row = tx.rows.item(0);
            if(row.count == 0){
                db.transaction((tx)=>{
                    tx.executeSql(`insert into question1 (questionid,categoryid,questiontext,questiontype,questionphrase,questionorder) values("${question.idquestion1}","${question.idcategory}","${question.questiontext}","${question.questiontype}","${question.question1phrase}","${question.questionorder}")`,[],()=>{
                        console.log("Added question1 ",question.idquestion1);
                        addupdateQuestion2(db,question)
                    },errorCB)
                })
            }else{
                db.transaction((tx)=>{
                    tx.executeSql(`update question1 set questiontext="${question.questiontext}",categoryid="${question.idcategory}",questiontype="${question.questiontype}",questionphrase="${question.question1phrase}",questionorder="${question.questionorder}" where questionid = "${question.idquestion1}"`,[],()=>{
                        console.log("Updated question1 ",question.idquestion1);
                        addupdateQuestion2(db,question)
                    },errorCB)
                })
            }
        },errorCB)
    })
}

3. Now again on each question1 insertion we inserting or updating question2 using the question1.question2 array like shown below

function addupdateQuestion2(db,question){
    var arrQuestion2=question.question2

    arrQuestion2.map((question)=>{
        db.executeSql(`select count(*) as count from question2 where questionid = "${question.idquestion2}"`,[],(tx,result)=>{
            let row = tx.rows.item(0);
            if(row.count == 0){
                db.transaction((tx)=>{
                    tx.executeSql(`insert into question2 (questionid,question1id,questiontext,questionphrase,questionorder) values("${question.idquestion2}","${question.idquestion1}","${question.questiontext}","${question.question2phrase}","${question.questionorder}")`,[],()=>{
                        console.log("Added question2 ",question.idquestion2);
                         --MORE--
                    },errorCB)
                })
            }else{
                db.transaction((tx)=>{
                    tx.executeSql(`update question2 set questiontext="${question.questiontext}",question1id="${question.idquestion1}",questionphrase="${question.question2phrase}",questionorder="${question.questionorder}" where questionid = "${question.idquestion2}"`,[],()=>{
                        console.log("Updated question2 ",question.idquestion2);
                        --MORE--
                    },errorCB)
                })
            }
        },errorCB)
    })
}

So basically it's a loop in the completion of each execution, we are facing two major issues with this approach.

1. It is very slow, it takes more or less 50 secs to insert this much data..
2. We are unable to determine when the whole execution stops.

The above code uses 3 tables (categories, question1, question2)

Please SUGGEST the best way to tackle this.

Thanks.

[iphonic]

The mistake was we were creating a loop of transactions. Whereas the whole insertion should be in a single

db.transaction((tx) => {
  //Put everything inside and use

  tx.executeSql(....)
  tx.executeSql(....)
  tx.executeSql(....)

},
(error)=>{
},
()=>{

})

Hope it helps someone.