I think that the other plans are either a ) too slow, meaning people wont use them when they should or b ) too hard to implement, meaning people wont implement them when they should...
I think concurrent use is an ok thing to lose in this case

The portfolio code needs to test the integration between the caller (eg assignment) writing files (eg submission) and the portfolio plugin picking it up and doing something with it...

We make a subclass of $DB (call it test_db) that:

i) In insert_record, keeps track, for each database table, of the id of each row inserted using this object - do this either in memory, in a file, or on disc. There is a speed/reliability issue here. This list of added rows is local to the current unit test run - so it is a bit like the backupids table, effectively there will be a table with three columns testrunid, tablename and rowid.
ii) For simple calls to update_record and delete record, we check that the row to be updated/deleted is one of the ones in our list of rows inserted during the test, and if not do an asser_fail - there is nothing we can do about the few places we do complicated SQL deletes and updates, but there are not many of those.
iii) For simple get_record(s) calls, where we are selecting from a single table, we check that we are only returning rows that were inserted as part of the test.
iv) There is a clean-up method (Use the destructor for this?) called at the end of the test run, which deletes all the test rows we have inserted.

This based on the assumption that test methods should only update or delete data that they themselves have inserted. If this assumption is true, then my proposed test class would increase the power of the unit tests to check that code does not modify the wrong data. If that assumption is false, then this whole proposal is rubbish.

So typical test code would then look like:

// before a block of unit tests - perhaps in each test class constructor.
setup_test_db(); // This would replace the global $DB with an instance of the test_db class.
// create test data.

// All the test methods, no change required.

// After all the tests.
cleanup_test_db();

This will be slower than 3, but a bit safer in the case that one person is running unit tests while other people are doing things with the site, or where you yourself are doing things with the site while some slow unit tests run.

Here are some possible extra features that could be added to the basic four above, but they would not be needed in a first release:

v) Possibly, have it so that when a test run starts, if the place where test data ids are stored contains traced of a previous test run that crashed, it automatically cleans up the rows from the old test run? Another thing to consider if it is a good idea.

vi) to get round the problem of complex inserts and updates in ii). Provide some assert methods on the test DB class that can be called after some complex SQL, for example assert_record_exists, or assert_record_deleted. These could be used both to check that the complicated SQL did what it was supposed to do in the database, but also be used to update the list of rowids containing test data.

Thinking about where to log ids, my preference would be to store them in arrays in memory, so they can be checked quickly, but also write them to a simple plain text file, to enable v).

We can also think about how often we call setup_test_db/cleanup_test_db. We could do it once for the whole test run, and that would work, or we could do it more frequently. That would be a bit slower, but would mean that bad side-effect of tests methods are identified more quickly, and are easier to localise. Actually, we can probably do the setup_test_db/cleanup_test_db in the simpletest driver methods, so the test code does not have to think about them at all.

1. DB tables with alternate prefix are required before Unit tests are available.
2. These tables are not built during install
3. The tables are built on-demand using a new function which refactors much of the functionality of admin/index.php
4. During each DB upgrade, the test tables are NOT upgraded.
5. The discrepancy in version numbers following a DB upgrade will make unit tests disabled until the test DB is also upgraded (done on-demand like the install)
6. An option remains to entirely drop and rebuild the test tables, once again using the new function described in 3.

Regarding the cleaning up of data between each tests:

1. Implement a lock so that unit tests can only be performed by one user concurrently
2. In the test code, write a subclass of the DB object, overriding basic insert, update and delete functions so that a record is kept of the max ids of these tables the first time the function is called.
3. In the test's tearDown() method, delete all records whose id is higher than that of the ids recorded in 2.

1. Once at the beginning of the tests, save a flat text file with the highest PKs of all tables.
2. Once all the tests are finished, delete the file
3. If, when the tests are run next, the file is still there, it means the tests didn't complete properly the last time. The file is then used to truncate all records whose PKs are higher than the ones recorded. The file is kept.

I tried very hard to prevent access to other tables with different prefixes in DML and this is totally against it. If you need to access other tables, please create a new database object with separate DB connection.

Thanks

of course that does mean we can't test it.

We will have to undo this and find some other solution I am afraid. This must be solved NOW, if you do not come with some suitable solution I will revert the code myself.

• renames MoodleUnitTestCase to SeparateDBUnitTestCase - it must be clear that the real db emulation is not intended for normal users
• review all unittests and make sure they use SeparateDBUnitTestCase really only when needed === revert many tests to simple UnitTestCase
• remove creation of $CFG->unittestprefix, only manually created in config.php for developers
• execute normal tests only if prefix not found
• refactor db installation and remove test db upgrade; instead of upgrade add new cfg var which would indicate that main moodle or any plugin version changed - force reinstallation of all tables

I can imagine one function that being "safe" today (because it doesn't uses DB) can become "dangerous" tomorrow, because we change its internals (so should go to separate unit tests). I guess there is a 99% chance of forgetting to move that test from normal to separate, so there is a 99% chance of people executing it in the wrong (production) environment.

I really think tests are a dangerous thing by definition and only should be allowed (all them) if:

• The server isn't a production server ($CFG->productionserver=0).
• The unittest prefix has been set (CFG->unittest) and isn't a substring of $DB->prefix (possible confilcts). But can be $DB->prefix (I think).
• debugging mode is set to developer

Summarizing, we must work to make unittest (all) useful for developers, allowing them to be re-executed safely and so on. But, at the same level, we must enforce the rule of not running them in production servers at all. Simply that. And, if that becomes true... then I cannot imagine any reason for having "normal" and "separate" unit tests. Difficult to maintain IMO.

Ciao