Sounds like a good idea. Anyone willing to provide a patch for this?

I'd be interested too. An Actual DBMS is a bit overkill for that purpose.
Is mantis running PDO ? If no database specific functions are used it should be fairly simple to enable a PDO driver.

At the moment our API relies on ADOdb for DB abstraction.

There was a plan to switch to a new DB API based on PDO, but the developer who led that effort has left the team, so at this time I don't know if and when that switch will actually happen.

That being said, ADOdb does offer SQLite drivers, so in theory it should be feasible to implement that.

Should you decide to implement it, feel free to submit a pull request on Github.

Thanks for the info, I'll probably do that !

Great, looking forward to your contribution !

Note, if and when you get around to it, please make sure to base your changes on our 'master' branch (i.e. not 1.2.x).

You may want to see the patch submitted at 0019350 to add Sqlite support for 1.2.x and 1.3.x.

To support modern SQLite 3, you need to re-enable it in the SQLite ADOdb driver itself. This is about DROP and RENAME COLUMN, which are required for a successful multi-step application of the MantisBT database schema during installation.
I have added the corresponding PR to the ADOdb project: https://github.com/ADOdb/ADOdb/pull/1053
Let's push it through.

Another necessary change (it affects only MantisBT) is to use the mode of fetching data from the table as an array with named keys, i.e. to use the ADODB_FETCH_ASSOC mode. Previously this value was set through a global variable, but in new versions of ADODb it is necessary to set it for database objects individually by calling the function: $g_db->SetFetchMode( ADODB_FETCH_ASSOC ). I suspect there is some bug in ADODb that causes the global constant to not work as intended.

The last step is to add a little database schema processing because SQLite uses only very basic types internally. To do this we need to remove meaningless SQL statements such as UNSIGNED and AUTOINCREMENT, and replace the entire VARCHAR(x) set with a simple TEXT. Also, SQLite does not support the auto-increment of columns that include dual primary keys (in the "mantis_tag_table").

First attempt works well: PR 2055.

For small systems it would be nice to have a database

In addition, I think that it will make it easier to run automated tests.

I have added the corresponding PR to the ADOdb project: https://github.com/ADOdb/ADOdb/pull/1053
Let's push it through.

This will be part of ADOdb 5.23.0, whenever that comes out.

use the ADODB_FETCH_ASSOC mode. Previously this value was set through a global variable, but in new versions of ADODb it is necessary to set it for database objects individually

The global variable should still work, what makes you say it needs to be set individually ?

For the record, this is how it's currently done in database_api.php, I never noticed any issues.
https://github.com/mantisbt/mantisbt/blob/6eff1a4e467ff7e5407c7c35720d3e4f93817617/core/database_api.php#L58-L62

That being said, calling ADOConnection::setFetchMode() should be fine too.

SQLite uses only very basic types internally. To do this we need to remove meaningless SQL statements such as UNSIGNED and AUTOINCREMENT, and replace the entire VARCHAR(x) set with a simple TEXT

Is that a theoretical question, or did you face actual errors due to this ?

We define the schema using ADOdb meta types, so I would expect the sqlite3 driver to abstract that for us automatically, without the need to change our schema. Moreover, if I'm not mistaken SQLite will interpret specific data type it receives, and will use its own internal type (e.g. VARCHAR should automatically be considered as TEXT). https://www.sqlite.org/datatype3.html

SQLite does not support the auto-increment of columns that include dual primary keys (in the "mantis_tag_table").

Composite primary keys are normally only used for intersection tables (where there are no AUTOINCREMENT columns). I never realised that we had one in the tag table... It does not make any sense to me, as tag_id should be unique already since it's an AUTOINCREMENT.

Looking at the schema, I wonder if it is not a mistake in the table definition, maybe the name column was meant to be UNIQUE instead of PRIMARY.

A little more and I'll start to understand how the database works in MantisBT even though I don't want to. �

Is that a theoretical question, or did you face actual errors due to this ?

I found what SQLite absolutely does not like in a column with AUTOINCREMENT - is the absence of PRIMARY KEY keywords and the presence of any other type other than INTEGER. For example, SMALLINT will not work, even though it is basically just an INTEGER inside. ADODb rebuilds the query by moving the PRIMARY KEY to a separate definition from AUTOINCREMENT and this doesn't work for SQLite. So I redid the PR to restore the correct keyword sequence before executing the query.

maybe the name column was meant to be UNIQUE instead of PRIMARY.

It makes sense. I also decided to modify the schema.php file to fix the lack of the AlterColumnSQL function for SQLite. These changes work fine for new databases as well as for updating existing ones.

I also found why functions like db_is_oracle() didn't work in this file and fixed it. No one has ever complained about Oracle not working correctly? �

After these changes, the process of installing MantisBT in SQLite makes the database schema identical to any other database type.

decided to modify the schema.php file to fix the lack of the AlterColumnSQL function for SQLite. These changes work fine for new databases as well as for updating existing ones.

This kind of changes are very sensitive and require double-extra caution and testing, to ensure that all install and upgrade scenarios for all RDBMS types are not introducing any variations in the final schema, otherwise we risk introducing nasty bugs that will be both difficult to diagnose AND to fix as this would require additional schema updates.

I also found why functions like db_is_oracle() didn't work in this file and fixed it. No one has ever complained about Oracle not working correctly? �

Well, I'm sure it did work at some point since I used to run Mantis on Oracle at my previous company, but that was about 10 years ago and I see that the db_get_type() has been added after that. I don't think Oracle sees much use nowadays.

This kind of changes are very sensitive and require double-extra caution and testing

I'll definitely test on MySQL and PostgreSQL at least, and I was thinking about adding checks to admin/checks (including SQLite 'PRAGMA integrity_check' for example), unfortunately I haven't used PHPUnit before.