Blady-Com / gnoga

gnoga - The GNU Omnificent GUI for Ada
GNU General Public License v3.0
12 stars 4 forks source link

Compatibility between MySQL and SQLite (missing API) #7

Open sowebio opened 1 year ago

sowebio commented 1 year ago

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: