ImportFromJson not working

[bennyvenassi]

Describe the bug I've written an Export (full) and Import Fuction in my application. Exporting works fine and i can save it as text. While importing the function jsonisvalid is sucessful. The Import doesnt work. In the console the app mentions "changes: 0". And nothing is imported.

To Reproduce Code:

async getDatabaseExport(mode: string) {
    const dbName = await this.storage.get(DB_NAME_KEY);
    return this.db.exportToJson(mode);
  }

  async importDatabase(jsonImport) {
    this.dbReady.next(false);
    console.log('JSON IMPORT DATA:');
    console.log(jsonImport);
    try {
      console.log("trying import...");
      const jsonstring = JSON.stringify(jsonImport.export);
      const isValidTry = await CapacitorSQLite.isJsonValid({jsonstring});
      if (isValidTry.result) {
        console.log("dbjson is valid");
        try {
          await this.sqlite.importFromJson(jsonstring);
          this.dbReady.next(true);
          return true;
        } catch (e) {
          console.error(e);
        }
      } else {
        return false;
      }
    } catch (e) {
      console.log(e);
      return false;
    }
  }

Console Log (Simulator / xcode): ⚡️ [log] - JSON IMPORT DATA: ⚡️ To Native -> CapacitorSQLite isJsonValid 123992707 ⚡️ [log] - {"export":{"encrypted":false,"mode":"full","tables":[{"values":[[2,"sdfsdf","<p>Dgfsgdsg</p>","#fcfcfc",1703429694468,1703429694468]],"schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"TEXT NOT NULL","column":"title"},{"value":"TEXT NOT NULL","column":"note"},{"value":"TEXT NOT NULL","column":"color"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"}],"name":"notes"},{"schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"title","value":"TEXT NOT NULL"},{"column":"note","value":"TEXT NOT NULL"},{"column":"color","value":"TEXT NOT NULL"},{"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},{"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}],"name":"trash"},{"values":[[1,"sdfsfd","<p>Dfgdsgdsfg</p>","#fcfcfc",1703429677902,1703429677902]],"schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"title","value":"TEXT NOT NULL"},{"column":"note","value":"TEXT NOT NULL"},{"column":"color","value":"TEXT NOT NULL"},{"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},{"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}],"name":"archive"},{"schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"INTEGER","column":"note_id"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"},{"value":"REFERENCES notes(id) ON DELETE CASCADE","foreignkey":"note_id"}],"name":"notifcations"}],"version":1,"database":"doublenote-db"}} ⚡️ [log] - trying import... ⚡️ TO JS {"result":true} ⚡️ To Native -> CapacitorSQLite importFromJson 123992708 ⚡️ [log] - dbjson is valid ⚡️ TO JS {"changes":{"changes":0}}

Expected behavior Sucessfull import with overwriting actual database

Desktop (please complete the following information):

• OS: iOS 17.2 / MacOS 14.0

Smartphone (please complete the following information):

• Device: iPhone 15 Pro (Simulator)
• OS: iOS 17.2
• Browser: Safari

Thank you in advance!

[jepiqueau]

@bennyvenassi

i test this this morning

      const jsonstring = {
        "export":{
          "encrypted":false,
          "mode":"full",
          "tables":[
            {
              "values":[
              [2,"sdfsdf","<p>Dgfsgdsg</p>","#fcfcfc",1703429694468,1703429694468]
              ],
              "schema":[
                {"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},
                {"value":"TEXT NOT NULL","column":"title"},
                {"value":"TEXT NOT NULL","column":"note"},
                {"value":"TEXT NOT NULL","column":"color"},
                {"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},
                {"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"}
              ],
              "name":"notes"
            },
            {
              "schema":[
                {"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},
                {"column":"title","value":"TEXT NOT NULL"},
                {"column":"note","value":"TEXT NOT NULL"},
                {"column":"color","value":"TEXT NOT NULL"},
                {"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},
                {"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}
              ],
              "name":"trash"
            },
            {
              "values":[
                [1,"sdfsfd","<p>Dfgdsgdsfg</p>","#fcfcfc",1703429677902,1703429677902]
              ],
              "schema":[
                {"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},
                {"column":"title","value":"TEXT NOT NULL"},
                {"column":"note","value":"TEXT NOT NULL"},
                {"column":"color","value":"TEXT NOT NULL"},
                {"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},
                {"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}
              ],
              "name":"archive"
            },
            {
              "schema":[
                {"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},
                {"value":"INTEGER","column":"note_id"},
                {"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},
                {"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"},
                {"value":"REFERENCES notes(id) ON DELETE CASCADE","foreignkey":"note_id"}
              ],
              "name":"notifcations"
            }
          ],
          "version":1,
          "database":"doublenote-db"
        }
      };
      // ************************************************
      // Import Json Object Issue#498
      // ************************************************
      // test Json object validity
      result = await this._sqlite
                            .isJsonValid(JSON.stringify(jsonstring.export));
      console.log("### result.result: ", result.result)
      if(!result.result) {
        return Promise.reject(new Error("IsJson failed"));
      }
      // full import
      result = await this._sqlite
                          .importFromJson(JSON.stringify(jsonstring.export));
      console.log("### result.changes: ", result.changes)

      if(result.changes.changes === -1 ) return Promise.reject(new Error("ImportFromJson 'full' failed"));;

      // create the connection to the database
      const db = await this._sqlite
                        .createConnection("doublenote-db", false,
                                          "no-encryption", 1);
      if(db === null) return Promise.reject(new Error("CreateConnection doublenote-db failed"));
      console.log("### create connection successful: ")

      // open db testNew
      await db.open();
      console.log("### db is opened: ")

      // create synchronization table
/*      result = await db.createSyncTable();
      if (result.changes.changes < 0) return Promise.reject(new Error("CreateSyncTable failed"));

      result = await db.getSyncDate();
      if(result.length === 0) return Promise.reject(new Error("GetSyncDate failed"));

*/
      result = await db.getTableList();
      console.log("### result.values: ", result.values)
      if( result.values.length !== 4) {
        return Promise.reject(new Error("### Number of tables != 4"));
      }

      // close the connection
      await this._sqlite.closeConnection("doublenote-db");

and the result is

[log] - ### jsonstring:  {"export":{"encrypted":false,"mode":"full","tables":[{"values":[[2,"sdfsdf","<p>Dgfsgdsg</p>","#fcfcfc",1703429694468,1703429694468]],"schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"TEXT NOT NULL","column":"title"},{"value":"TEXT NOT NULL","column":"note"},{"value":"TEXT NOT NULL","column":"color"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"}],"name":"notes"},{"schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"title","value":"TEXT NOT NULL"},{"column":"note","value":"TEXT NOT NULL"},{"column":"color","value":"TEXT NOT NULL"},{"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},{"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}],"name":"trash"},{"values":[[1,"sdfsfd","<p>Dfgdsgdsfg</p>","#fcfcfc",1703429677902,1703429677902]],"schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"title","value":"TEXT NOT NULL"},{"column":"note","value":"TEXT NOT NULL"},{"column":"color","value":"TEXT NOT NULL"},{"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},{"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}],"name":"archive"},{"schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"INTEGER","column":"note_id"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"},{"value":"REFERENCES notes(id) ON DELETE CASCADE","foreignkey":"note_id"}],"name":"notifcations"}],"version":1,"database":"doublenote-db"}}
⚡️  To Native ->  CapacitorSQLite isJsonValid 115475932
⚡️  TO JS {"result":true}
⚡️  [log] - ### result.result:  true
⚡️  To Native ->  CapacitorSQLite importFromJson 115475933
database path /Users/queaujeanpierre/Library/Developer/CoreSimulator/Devices/2C75E265-B222-44FA-BB8D-DF66CE3580F4/data/Containers/Data/Application/52543349-DA30-4253-90B2-351A8A03E0DF/Library/CapacitorDatabase/doublenote-dbSQLite.db
>>>> in  getCurrentTimeAsInteger currentTime: 1705655303.653868
⚡️  TO JS {"changes":{"changes":2}}
⚡️  To Native ->  ⚡️  [log] - ### result.changes:  {"changes":2}
CapacitorSQLite createConnection 115475934
database path /Users/queaujeanpierre/Library/Developer/CoreSimulator/Devices/2C75E265-B222-44FA-BB8D-DF66CE3580F4/data/Containers/Data/Application/52543349-DA30-4253-90B2-351A8A03E0DF/Library/CapacitorDatabase/doublenote-dbSQLite.db
⚡️  TO JS undefined
⚡️  To Native ->  ⚡️  [log] - ### create connection successful: 
CapacitorSQLite open 115475935
⚡️  TO JS undefined
⚡️  [log] - ### db is opened: 
⚡️  To Native ->  CapacitorSQLite getTableList 115475936
⚡️  TO JS {"values":["notifcations","archive","trash","notes"]}
⚡️  [log] - ### result.values:  ["notifcations","archive","trash","notes"]
⚡️  To Native ->  CapacitorSQLite closeConnection 115475937
⚡️  TO JS undefined
⚡️  [log] - $$$ runTest was successful

in my case this._sqlite is a sqlite service So for me there is no issue

in your case

await this.sqlite.importFromJson(jsonstring);

i do not know what is this.sqlite and if i look at the call to isJsonValid

const isValidTry = await CapacitorSQLite.isJsonValid({jsonstring});

you do not use this.sqlite to be consistent you must do

await CapacitorSQLite.importFromJson({jsonstring});

Hope this will be helpful

[jepiqueau]

@bennyvenassi Have you had a look?

[bennyvenassi]

Hello @jepiqueau, thank you I gave it a try.

this.sqlite is the SQLiteConnection inside the Service. I changed the code like you suggested. But its still not working. :(

How I initalize the plugin inside the Service:

  async initializePlugin(): Promise<boolean> {
    return new Promise(async (resolve) => {
      const storage = await this.storage.create();
      console.log(storage);
      this._storage = storage;
      console.log(this._storage);
      console.log("starting sqlite initialization...");
      this.platform = Capacitor.getPlatform();
      console.log("*** platform " + this.platform);
      if (this.platform === "ios" || this.platform === "android") {
        this.native = true;
      }
      const sqlitePlugin: any = CapacitorSQLite;
      this.sqlite = new SQLiteConnection(sqlitePlugin);
      this.isService = true;
      console.log("$$$ in service this.isService " + this.isService + " $$$");
      this.setupDatabase();
      resolve(true);
    });
  }

The new code:

  async importDatabase(jsonImport) {
    this.dbReady.next(false);
    console.log('JSON IMPORT DATA:');
    console.log(jsonImport);
    try {
      console.log("trying import...");
      const jsonstring = JSON.stringify(jsonImport.export);
      const isValidTry = await CapacitorSQLite.isJsonValid({jsonstring});
      if (isValidTry.result) {
        console.log("dbjson is valid");
        try {
          await CapacitorSQLite.importFromJson({jsonstring});
          this.dbReady.next(true);
          return true;
        } catch (e) {
          console.error(e);
        }
      } else {
        return false;
      }
    } catch (e) {
      console.log(e);
      return false;
    }
  }

And the Logs from XCode:

 [log] - JSON IMPORT DATA:
⚡️  [log] - {"export":{"version":1,"encrypted":false,"tables":[{"values":[[1,"dsfsdf","<p>Dfdsfsdfdsfsdf</p>","#fcfcfc",1706107098203,1706107098203],[2,"dsfdsfsf","<p>Dsfdsfsdfdsf</p>","#fcfcfc",1706107108779,1706107108779],[3,"sdfsf","<p>Jgfjgfj</p>","#00FA9A",1706107122932,1706107137006]],"schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"TEXT NOT NULL","column":"title"},{"value":"TEXT NOT NULL","column":"note"},{"value":"TEXT NOT NULL","column":"color"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"}],"name":"notes"},{"schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"title","value":"TEXT NOT NULL"},{"column":"note","value":"TEXT NOT NULL"},{"column":"color","value":"TEXT NOT NULL"},{"column":"created","value":"INTEGER DEFAULT (strftime('%s', 'now'))"},{"column":"last_modified","value":"INTEGER DEFAULT (strftime('%s', 'now'))"}],"name":"trash"},{"name":"archive","schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"TEXT NOT NULL","column":"title"},{"value":"TEXT NOT NULL","column":"note"},{"value":"TEXT NOT NULL","column":"color"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"}]},{"schema":[{"value":"INTEGER PRIMARY KEY NOT NULL","column":"id"},{"value":"INTEGER","column":"note_id"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"created"},{"value":"INTEGER DEFAULT (strftime('%s', 'now'))","column":"last_modified"},{"foreignkey":"note_id","value":"REFERENCES notes(id) ON DELETE CASCADE"}],"name":"notifcations"}],"mode":"full","database":"doublenote-db"}}
⚡️  [log] - trying import...
⚡️  TO JS {"result":true}
⚡️  To Native ->  CapacitorSQLite importFromJson ⚡️  [log] - dbjson is valid
⚡️  TO JS {"changes":{"changes":0}}

[jepiqueau]

@bennyvenassi like i told you {"changes":{"changes":0}} is the normal successfull return meaning that the database has been modified. If it failed the changes will be -1. If you create the connection and open the database and do some query you will see that the data have been loaded

[bennyvenassi]

Sorry then I dont get the error. After the "sucessful" import nothing is added. I still have the old data in the db or still nothing if I try the import with an empty db. Even if I close the connection before the import, nothing will be imported...

[jepiqueau]

@bennyvenassi please share your example on github

[bennyvenassi]

storage.service.ts.zip

@jepiqueau Thank you, is the storage.service enough or do you need the entire app?

[jepiqueau]

@bennyvenassi Can you share the whole app on github so i can try it

[jepiqueau]

@bennyvenassi icome back on this, my answer was not correct. you must replace

      const jsonstring = JSON.stringify(jsonImport.export);

with

const jsonObj = jsonImport.export
jsonObj.overwrite = true
const jsonstring = JSON.stringify(jsonObj)

that will delete the database if it exists

[bennyvenassi]

Thank you, that was the solution!!