bug: Plugin fails to parse modern SQL syntax including CTEs and ON CONFLICT clauses.

[xEverth]

Plugin version: 7.0.2

Platform(s): Android, Web

Current behavior:

1. When an INSERT or UPSERT statement contains an ON CONFLICT clause with its own parenthesized expressions (e.g. conflict targets or column-name-lists), the plugin incorrectly splits the statement when detecting RETURNING. For example a valid statement like:

INSERT INTO table (col1, col2) VALUES (1,2), (3,4)
ON CONFLICT (col1) DO UPDATE SET col2 = 0
RETURNING *;

is transformed into the invalid

INSERT INTO table (col1, col2) VALUES (1,2), (3,4) 
ON CONFLICT (col1);

2. The Common Table Expression used for "UPDATE" or "DELETE" is ignored by the getUpdDelReturnedValues and the resulting statement may be invalid. An update statement with a CTE like WITH new_data (col1, col2) AS ( VALUES (1,2),(3,4),(5,6) ) SELECT * FROM table WHERE col1 IN (SELECT col1 FROM new_data); would become SELECT (col1, col2) FROM table WHERE col1 IN (SELECT col1 FROM new_data); with the CTE definition missing.

Expected behavior: Since the returning clause is always last in the grammar for the statements INSERT, UPDATE and DELETE, the parsing should just remove the associated part. I understand that the altering of the statement is performed in order to maintain retro-compatibility with older devices running older versions of SQLite, although it was not immediately clear to me while debugging the issue that this was happening behind the scenes. Resources:

• https://sqlite.org/syntax/insert-stmt.html
• https://sqlite.org/syntax/upsert-clause.html
• https://sqlite.org/syntax/update-stmt.html
• https://sqlite.org/lang_delete.html

Steps to reproduce: Run a query like the above using the plugin methods "execute" or "executeSet", observe the error in response.

Related code: The methods responsible for this behaviour are in the Java implementation: android\src\main\java\com\getcapacitor\community\database\sqlite\SQLite\Database.java and android\src\main\java\com\getcapacitor\community\database\sqlite\SQLite\UtilsSQLStatement.java

Other information: The issue has been isolated, verified and debugged in Android Studio. An attempt for a resolution will be shown in an associated draft PR.

Capacitor doctor:

   Capacitor Doctor   

Latest Dependencies:

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

Installed Dependencies:

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

[success] Android looking great! 👌
[error] Xcode is not installed