bug: exportToJson returns SQL containing CR/LF characters

[GilMarques]

Plugin version:
v7.0.1

Platform(s):
Android
Web

Current behavior:
Calling exportToJson produces JSON that contains raw SQL definitions with embedded CR/LF characters (\r\n, tabs, and excessive whitespace).

 {
  "export": {
    "database": "myData",
    "version": 1,
    "encrypted": false,
    "mode": "full",
    "tables": [
      {
        "name": "weight",
        "schema": [
          {
            "column": "\"id\"\tINTEGER",
            "value": "NOT NULL"
          },
          {
            "column": "\"weight\"\tREAL",
            "value": "NOT NULL"
          },
          {
            "column": "",
            "value": "\"body_fat\"\tREAL"
          },
          {
            "column": "\"timestamp\"\tTEXT",
            "value": "DEFAULT (datetime('now'))"
          },
          {
            "column": "last_modified",
            "value": "INTEGER DEFAULT 0"
          },
          {
            "column": "sql_deleted",
            "value": "BOOLEAN DEFAULT 0 CHECK (sql_deleted IN (0,1))"
          },
          {
            "constraint": "CPK_\"id\" AUTOINCREMENT",
            "value": "PRIMARY KEY(\"id\" AUTOINCREMENT)"
          }
        ],
        "triggers": [
          {
            "name": "weight_trigger_last_modified",
            "logic": "BEGIN\r\n      UPDATE weight SET last_modified = (strftime('%s', 'now')) WHERE id=NEW.id;\r\n  END",
            "condition": "FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified",
            "timeevent": "AFTER UPDATE ON"
          }
        ],
         "values":  [...]
      },
       
    ],
    "views": [
      {
        "name": "history_full_view",
        "value": "history_id,\r\n    wh.name AS workout_name,\r\n    wh.duration,\r\n    wh.note,\r\n    wh.timestamp,\r\n\r\n    whe.id AS history_exercise_id,\r\n    whe.exercise_id,\r\n    whe.position AS exercise_position,\r\n    whe.superset_group,\r\n\r\n    ex.name AS exercise_name,\r\n    ex.path AS exercise_path,\r\n    ex.archived AS exercise_archived,\r\n    ex.is_custom AS exercise_is_custom,\r\n\tex.body_part AS exercise_bodypart,\r\n\tex.category AS exercise_category,\r\n\r\n    whes.id AS set_id,\r\n    whes.previous_weight,\r\n    whes.previous_reps,\r\n    whes.position AS set_position\r\n\r\n\r\nFROM workout_history AS wh\r\nLEFT JOIN workout_history_exercises AS whe\r\n    ON whe.history_id = wh.id\r\nLEFT JOIN exercises AS ex\r\n    ON ex.id = whe.exercise_id\r\nLEFT JOIN workout_history_exercise_sets AS whes\r\n    ON whes.history_exercise_id = whe.id\r\n\r\nORDER BY \r\n    wh.timestamp DESC,\r\n    whe.position ASC,\r\n    whes.position ASC"
      },
      {
        "name": "template_full_view",
        "value": "template_id,\r\n    t.name AS template_name,\r\n    t.last_used,\r\n    t.archived,\r\n\tt.position,\r\n\r\n    e.id AS template_exercise_id,\r\n    e.exercise_id,\r\n    e.rest_time_seconds,\r\n    e.note AS exercise_note,\r\n    e.superset_group,\r\n    e.position AS exercise_position,\r\n\r\n    s.id AS set_id,\r\n    s.weight,\r\n    s.reps,\r\n    s.position AS set_position\r\n\t\r\n\r\nFROM templates t\r\nLEFT JOIN template_exercises e ON e.template_id = t.id\r\nLEFT JOIN template_exercise_sets s ON s.template_exercise_id = e.id"
      }
    ]
  }
}

Expected behavior:
The exported JSON should contain normalized SQL string
Expected output example (manually corrected):

{
  database: 'original',
  version: 1,
  encrypted: false,
  mode: 'full',
  tables: [
    {
      name: 'weight',
      schema: [
        {
          column: 'id',
          value: 'INTEGER NOT NULL',
        },
        {
          column: 'weight',
          value: 'REAL NOT NULL',
        },
        {
          column: 'body_fat',
          value: 'REAL',
        },
        {
          column: 'timestamp',
          value: "TEXT DEFAULT (datetime('now'))",
        },
        {
          column: 'last_modified',
          value: 'INTEGER DEFAULT 0',
        },
        {
          column: 'sql_deleted',
          value: 'BOOLEAN DEFAULT 0 CHECK (sql_deleted IN (0,1))',
        },
        {
          constraint: 'CPK_id',
          value: 'PRIMARY KEY(id AUTOINCREMENT)',
        },
      ],
      triggers: [
        {
          name: 'weight_trigger_last_modified',
          logic:
            "BEGIN  UPDATE weight SET last_modified = (strftime('%s', 'now')) WHERE id=NEW.id;   END",
          condition: 'FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified',
          timeevent: 'AFTER UPDATE ON',
        },
      ],
    },
...
 views: [
    {
      name: 'history_full_view',
      value:
        'SELECT wh.id AS history_id, wh.name AS workout_name, wh.duration, wh.note, wh.timestamp, whe.id AS history_exercise_id, whe.exercise_id, whe.position AS exercise_position, whe.superset_group, ex.name AS exercise_name, ex.path AS exercise_path, ex.archived AS exercise_archived, ex.is_custom AS exercise_is_custom, ex.body_part AS exercise_bodypart, ex.category AS exercise_category, whes.id AS set_id, whes.previous_weight, whes.previous_reps, whes.position AS set_position FROM workout_history AS wh LEFT JOIN workout_history_exercises AS whe ON whe.history_id = wh.id LEFT JOIN exercises AS ex ON ex.id = whe.exercise_id LEFT JOIN workout_history_exercise_sets AS whes ON whes.history_exercise_id = whe.id ORDER BY wh.timestamp DESC, whe.position ASC, whes.position ASC',
    },
    {
      name: 'template_full_view',
      value:
        'SELECT t.id AS template_id, t.name AS template_name, t.last_used, t.archived, t.position, e.id AS template_exercise_id, e.exercise_id, e.rest_time_seconds, e.note AS exercise_note, e.superset_group, e.position AS exercise_position, s.id AS set_id, s.weight, s.reps, s.position AS set_position FROM templates t LEFT JOIN template_exercises e ON e.template_id = t.id LEFT JOIN template_exercise_sets s ON s.template_exercise_id = e.id',
    },

Steps to reproduce:
const exportResult = await db.exportToJson('partial');

Related code:
The issue appears to originate from how SQL is extracted in getJsonObject.

For example in createExportObject,

public JsonSQLite createExportObject(Database db, JsonSQLite sqlObj) throws Exception {
...
              JSArray resViews = db.selectSQL(stmtV, new ArrayList<>());
            if (resViews.length() > 0) {
                for (int i = 0; i < resViews.length(); i++) {
                    JSONObject oView = resViews.getJSONObject(i);
                    JsonView v = new JsonView();
                    String val = (String) oView.get("sql");
                    val = val.substring(val.indexOf("AS ") + 3);
                    v.setName((String) oView.get("name"));
                    v.setValue(val);
                    views.add(v);
                }
            }
...

Other information:
The issue occurs with both exportToJson('partial') and exportToJson('full')
As a workaround, I normalized whitespace with
val = val.replace("\r", " ").replace("\n", " ").replaceAll("\\s+", " ").trim();
But this could interfere with user data if applied incorrectly.

Capacitor doctor:

   Capacitor Doctor

Latest Dependencies:

  @capacitor/cli: 8.0.0
  @capacitor/core: 8.0.0
  @capacitor/android: 8.0.0
  @capacitor/ios: 8.0.0

Installed Dependencies:

  @capacitor/ios: not installed
  @capacitor/cli: 8.0.0
  @capacitor/android: 8.0.0
  @capacitor/core: 8.0.0

[success] Android looking great! 👌

[robingenz]

Please test the behavior with the latest plugin version.

[GilMarques]

Same behavior with "@capacitor-community/sqlite": "^7.0.3"

[robingenz]

Alright, thanks for testing. Feel free to create a PR and I will take a look at it.

[GilMarques]

These characters come from the command
SELECT name,sql FROM sqlite_master WHERE type = 'view' AND name NOT LIKE 'sqlite_%';

And I think its because I used DB Browser do create most of my tables, and it saves those characters.
If I get the Create Statement for a table i get this for example:

CREATE TABLE "weight" (
	"id"	INTEGER NOT NULL,
	"weight"	REAL NOT NULL,
	"body_fat"	REAL,
	"timestamp"	TEXT DEFAULT (datetime('now')), last_modified INTEGER DEFAULT 0, sql_deleted BOOLEAN DEFAULT 0 CHECK (sql_deleted IN (0,1)),
	PRIMARY KEY("id" AUTOINCREMENT)
)

The sql_deleted column was added using a command so it has no quotes.
I think I need to recreate all tables using clean statements.

[GilMarques]

The sql_deleted column was added using a command so it has no quotes. I think I need to recreate all tables using clean statements.

I recreated my tables using single line commands, and now it works as intended.

[robingenz]

In this case I'm closing this issue. Thanks!