aliens/scuttle/src/SemanticScuttle/db/sqlite.php, branch oni

Support reentrant calls to ‘sql_db->sql_transaction’ for SQLite

2024-06-11T21:45:50Z

SQLite transactions don't appear to be reentrant. So calling ‘BEGIN’ and then again ‘BEGIN’ is an error if there is no ‘COMMIT’ or ‘ROLLBACK’ in between. In order to pretend like this works (it doesn't) I keep track of the level of the number of transactions that have been created and only call the actual ‘BEGIN’ on the first call and ‘COMMIT’ and ‘ROLLBACK’ on the final one. This does mean that if any part of the code forgets to do a commit or rollback for any reason it'll maintain the transaction indefinitely. I want to change it to explicitly fail if this is attempted, but MySQL/MariaDB doesn't seem to have any problem with calling ‘BEGIN’ and ‘COMMIT’/‘ROLLBACK’ as many times as one wants.

Use ‘exec’ instead of ‘query’ for transaction commands in sqlite

2024-06-11T07:00:38Z

The ‘query’ method will try and return a ‘SQLite3Result’ object, which will be meaningless because it won't contain any fetched data. The ‘exec’ method instead returns a boolean indicating the success or failure of the query.

Fix using SQLite as a database engine

2024-06-11T06:38:09Z

- The ‘sqlite_*’ functions don't exist anymore in PHP 8. They have been replaced with several ‘SQLite3*’ classes. - There are some differences between MySQL and SQLite queries that showed up while doing this work. These differences have been pushed into a QueryBuilder class so that the other database engines don't have to be modified. This is done in an ad-hoc basis for now, just to get things working. - SQLite doesn't support the ‘!’ negation operator used as ‘!ISNULL(...)’, instead I use ‘IIF(...)’. - SQLite doesn't support a ‘LEFT(...)’ function, instead I use ‘SUBSTRING(..., 0, ...)’. - The SQLite3 module doesn't provide a connection identifier, instead you use an object that represents the connection. - SQLite doesn't support the ‘ALL’ keyword (or at least doesn't support it in the same way MySQL does). Instead of ‘<> ALL ’ I use ‘NOT IN ’. - The ‘SQLite3*’ classes don't provide any way to determine how many rows have been returned without iterating through all of them, so any place that tries to figure out the rows beforehand just doesn't anymore. - All the database engine classes require a ‘QueryBuilder’ instance. The sqlite one uses a specialized one. I can't test most of these database engines, so I'm focusing on SQLite and MySQL/MariaDB for now.

PHP 8 modifications

2023-12-21T00:40:53Z

modifications to avoid "Deprecated" warnings in sql_connect function

move files to new locations

2009-10-03T14:00:33Z

git-svn-id: https://semanticscuttle.svn.sourceforge.net/svnroot/semanticscuttle/trunk@386 b3834d28-1941-0410-a4f8-b48e95affb8f