Compatibility between MySQL and SQLite (missing API)

[sowebio]

Problem description

Number_Of_Rows is not implemented in SQLite: this is acceptable because it is clearly expressed in the doc and it can be implemented with the reservations below.

Affected_Rows is not implemented correctly for SQLite: this is annoying because it's not expressed at all as Gnoga doc says: executes a SQL query and returns the number of rows affected, but affected_Rows calls sqlite3_total_changes() which only handles modifications (i.e. only INSERT, UPDATE or DELETE) and not the result of the query. A simple SELECT * table will return 0 even if the table is full to bursting. 2) Implementation has an impact on performance (see below).

Execute_Update is not implemented correctly for SQLite: since, after calling Execute_Query, we call Affected_Rows, we end up with the same problem.
8.2.2 Suggested corrections
Correcting these inconsistencies is not difficult, but the result will range from transparent to very slow (depending on the type of query and/or the volume of data returned, since the entire recordset have to be iterated).

Number_Of_Rows: As the name don't suggests, returns the number of rows in the recordset, not in the table. Can be implemented with the above reservations by iterating through the entire recordset.

Affected_Rows: you'll need to remember the last query performed by Execute_Update (in the connection record, next to UTF8_String). If this query contains INSERT, UPDATE or DELETE, we return the result unchanged; if it contains a SELECT |Column_Name, we transform it into a SELECT COUNT(|Column_Name). If the query contains anything else, we execute a standard Query with the above reservations, since we have to iterate through the entire recordset.

Suggested implementations

Two options:

• Either I implement it at Gnoga level, but Pascal has to agree. It's an imperfect hack, but it'll be better than this unusable existing one. This is the preferred option to my taste.

• Or I can implement this in v22 and ban the use of Execute_Query, Execute_Update, Number_Of_Rows in the docs in favor of functions with identical names but prefixed v22.Sql, which is pretty dirty and wouldn't solve the problem of these rogue functions in Gnoga.Server.Database.