Issue importing the exported DB

[samirame]

Hi!

Thank you for the caring support.

I am using version 2.1.0-4 of the plugin and have some problem importing the DB that I have already exported. Here is the steps that I have taken ( the scenario is based on the andular-sqlite-app-starter):

1. create the tables using the following statement (with no data inserts)

      let result:any = await this._SQLiteService.openDB("test-sqlite"); 
      if(result.result) {

        result = await this._SQLiteService.createSyncTable();

        // create tables
        const sqlcmd: string = `
        BEGIN TRANSACTION;
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY NOT NULL,
            email TEXT UNIQUE NOT NULL,
            name TEXT,
            company TEXT,
            size FLOAT,
            age INTEGER
        );
        CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY NOT NULL,
            userid INTEGER,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            FOREIGN KEY (userid) REFERENCES users(id) ON DELETE SET DEFAULT
          );
        CREATE TABLE IF NOT EXISTS images (
          id INTEGER PRIMARY KEY NOT NULL,
          name TEXT UNIQUE NOT NULL,
          type TEXT NOT NULL,
          size INTEGER,
          img BLOB
        );
       CREATE INDEX users_index_name ON users (name);
        PRAGMA user_version = 1;
        COMMIT TRANSACTION;
        `;
         result = await this._SQLiteService.execute(sqlcmd);

2. export the DB in "full" mode:
3. and then import the exported json string

      console.log('****** export ******');
      let result:any = await this._SQLiteService.openDB("test-sqlite"); 
      if(result.result) {
        let ret:boolean = true;
        let result:any = await this._SQLiteService.exportToJson("full");
        console.log('---> result fullexportToJson ',result);
        if (Object.keys(result.export).length === 0)  ret = false;
        const jsObj: string = JSON.stringify(result.export); 
        result = await this._SQLiteService.isJsonValid(jsObj);
        if(!result.result) ret = false;  

        console.log('****** import ******');
        let impresult:any = await this._SQLiteService.importFromJson(jsObj);
        if(impresult.changes.changes === -1 ){ ret = false;}

the exported json:

jsonObject {"database":"test-sqlite","encrypted":false,"mode":"full","tables":[{"name":"users","schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"email","value":"TEXT UNIQUE NOT NULL"},{"column":"name","value":"TEXT"},{"column":"company","value":"TEXT"},{"column":"size","value":"FLOAT"},{"column":"age","value":"INTEGER"}],"indexes":[{"name":"users","column":"users_index_name"}]},{"name":"messages","schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"userid","value":"INTEGER"},{"column":"title","value":"TEXT NOT NULL"},{"column":"body","value":"TEXT NOT NULL"},{"value":"REFERENCES users(id) ON DELETE SET DEFAULT","foreignkey":"userid"}]},{"name":"images","schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"name","value":"TEXT UNIQUE NOT NULL"},{"column":"type","value":"TEXT NOT NULL"},{"column":"size","value":"INTEGER"},{"column":"img","value":"BLOB"}]}]}

and here is the output of importFromJson() method:

changes: {changes: -1}
message: "importFromJson command failed: import JsonObject not successful"

As a side note, I first thought, the definition of indexes in the json object, returned by the exportToJson() might not be correct and this is causing the issue.

which is: "indexes":[{"name":"users","column":"users_index_name"}]

In my opinion it should be (could you please check this also) like this: "indexes":[{"name":"users_index_name","column":"name"}]

Nevertheless, I also tried removing the CREATE INDEX definition from the sql statement, but the issue was still there and I had no success importing what I had just exported. Could you please check this?

I truly appreciate all your support!

[jepiqueau]

@samirane, thanks for the issue, i got the fix but it is more than one hour i tried to publish it without success time out issue so hopefully it will come soon

[jepiqueau]

@samirame the new release capacitor-sqlite@2.1.0-5 is fixing the issue

[samirame]

Thanks for the new release! However, it seems to me there is still some problem. In the generated json, for the indexes, the "column" value is always set to the string "name".

this is what I actually get: "indexes":[{"name":"table1_index_wrd_fk","column":"name"}, {"name":"table1_index_cat_fk","column":"name"},{"name":"table1_index_typ_fk","column":"name"}]}

this is what I expect to get: "indexes":[{"name":"table1_index_wrd_fk","column":"wrd"}, {"name":"table1_index_cat_fk","column":"cat"},{"name":"table1_index_typ_fk","column":"typ"}]}

seems that "name" is the hardcoded value for the column now ;)

[jepiqueau]

@samirame Thanks for your contribution to the debug process. This is hopefully solved in release 2.1.0-6 of the plugin. Do not hesitate to come back if any think else is wrong.

[mpat8121]

@jepiqueau Hi. I'm getting the same error trying to import the export JSON as per the original issue using version 2.1.0-7, however, I don't have any table indexes and the JSON string is passing validation using the isJsonValid method.

The JSON string is 18423 characters long. Is there a limit on the import? Additionally, the table and column names have capitalisation. Would this be likely to cause the error?

Thanks!

[jepiqueau]

@mpat8121 Hi, thanks for using the plugin, the JSON string has for limit the limit for a string which is different on IOS and Android. The problem is coming from capitalization in column names see my answer to issue #25 Android sqlite recognize capitalization letters not ios sqlite. so you should not use capitalization letters in your column names. I may be dealing with that later, but it will slow down the query process of IOS. Is Capitalization letter matter for you?

[mpat8121]

@jepiqueau Thanks for getting back to me so quickly & for the plugin. This issue is occurring on an Android emulator. I have been able to import JSON with capitalisation in both table and column names. It only seems to be after exporting then trying to re-import that it fails. I'll re-write it to be lowercase.

[jepiqueau]

@mpat8121 Going lowercase is better, i will have a look later as i am busy working on the capacitor-video-player

[mpat8121]

@jepiqueau Thanks. I have changed over the SQL schema to be all lowercase but I am still getting the same error. The JSON string is 19553 characters long so I don't believe it should be exceeding the maximum allowed. What other information can I provide you to make this easier to identify?

[jepiqueau]

@mpat8121 Can you provide the schema of the database as well as the number of data in each table. Is it a full export? Can you provide the error and on which line of the code it is happening. Did you had any chance to test it on IOS to see if you get the same error. Please provide me any data that allows me to reproduce. I thought it was due to the Capitalization but it seems not so i need to dig out. Many thanks for participating to the debugging process, i am sorry for this but testing is not an easy task

[mpat8121]

@jepiqueau I've attached the schema object that is being imported via a service during app initialisation (Angular 8+ & Ionic 5) as well as the data import object

. This same data is exported and errors when imported. It is a full import initially and a full export. I am having trouble getting the app to hit breakpoints within the vendor.js to identify which line(s) it is failing on within this plugin. The response object is:

changes: {changes: -1} message: "importFromJson command failed: import JsonObject not successful"

I will spin up the iOS version and let you know if it is any different.

[jepiqueau]

@mpat8121 First in the schema.txt you have to modify { foreignkey: "siteid", value: "REFERENCES Sites(id)" }, as { foreignkey: "siteid", value: "REFERENCES sites(id)" }, you have to do the same for "REFERENCES Defects(id)"as "REFERENCES defects(id)"

Now the issue is that for an import full as of to day you must have for each table the definition of the schema and data in the jsonSQLite Object so if you do this : so you have to split in to import but not like you do by splitting table schemas and table data but the first import with mode "full" having the table areas, elements, issues with their schema and data an do a "partial" import with all the other table schema definition. I understand that the way you did it can be interesting but i will have to revisit the code to take this methodology into account. This will be look at later when i will have completed the work on the capacitor-video-player. Hope that for the time being you can cope with the present solution for a while

[jepiqueau]

@mpat8121 i tested it for both ios and android and it works

[mpat8121]

@jepiqueau Thanks for the hints in the right direction. I think I've figured this out that even though the import of the tables (the second partial import) always returns changes: {changes: -1} message: "importFromJson command failed: import JsonObject not successful" the tables themselves are actually created.

It looks to me like the importFromJson method in SQLiteDatabaseHelper.java (specifically from line 648) always attempts to create data if the table was successfully created but then returns changes: -1 if there are no value arrays to import. This was leading me to think it hadn't worked when it in fact had.

[jepiqueau]

@mpat8121 You right, i will go through the complete process again soon, with a more exhaustive testing including your original case. the success = false was added at a certain stage invalidating the originals tests where you could have schemas without data. Give me a bit of time

[mpat8121]

@jepiqueau There is no hurry. I have it now working with this understanding. Thanks again for the assistance.

[jepiqueau]

@mpat8121 This is now fixed in release 2.1.0. Capitalization characters in field name are also fixed enjoy...

[mpat8121]

@jepiqueau Thanks! Everything seems to be working perfectly. I was not expecting you to re-work this so quickly 😮

[jepiqueau]

@mpat8121 thank you for the feedback you are welcome