Switch unit tests to use temp cohort tables to avoid collisions between runs

[schuemie]

Currently the unit tests use a permanent table called cohorts_of_interest (see here and here, with SQL here). This means that if multiple processes try to create this table simultaneously there could be collisions. In fact, because the unit tests take over 2 hours to complete, it is not unlikely that this will happen!

A solution could be to use a temp table instead. This would require that the connection be kept alive across the various tests (or else the temp table is automatically dropped). Also remember that the temp tables need to be explicitly deleted after finishing the tests, for platforms where we merely emulate temp tables (like Oracle, Spark, and BigQuery)

[anthonysena]

A few notes on this issue:

First steps

• Modify inst/sql/sql_server/cohortsOfInterest.sql to parameterize the target table from:

FeatureExtraction/inst/sql/sql_server/cohortsOfInterest.sql

Lines 8 to 10 in c75e4d9

	SELECT first_use.*
	INTO @resultsDatabaseSchema.cohorts_of_interest
	FROM (

to a temp table that either uses a dynamic name that is unique per session (so different OS unit tests do not collide) or add a suffix to the temp table name per OS. Modify all other dependent code to use the temp table and clean up when the test is complete so they are not lingering on platforms such as Oracle.

Next steps

• There is likely a way to condense the unit tests for FE such that we can load the target RDBMS and use it throughout the test execution so that we don't have duplicate unit tests for each RDBMS platforms. Tagging @azimov as I think he's done this for CohortDiagnostics.

• It appears that the code in https://github.com/OHDSI/FeatureExtraction/blob/develop/tests/testthat/test-query-no-fail.R#L4 and https://github.com/OHDSI/FeatureExtraction/blob/develop/tests/testthat/test-query-no-fail.R#L199 are nearly identical with the only difference being the use of the aggregated = TRUE in the later function. This should be consolidated to a single function with a parameter for aggregated instead.

[anthonysena]

Closed via #185