ddl/achilles endpoint is missing the tempSchema parameter

[katy-sadowski]

Expected behavior

Similar to the ddl/results endpoint, it is possible to pass a tempSchema parameter to this endpoint, for databases which require a temp schema to be specified.

Actual behavior

It does not have this parameter:

Steps to reproduce behavior

Generate and run the ddl/achilles queries on a database which requires specification of a temp schema (this is the case for our Snowflake instance). An error will be raised for the DROP IF EXISTS clauses for the temp tables due to their lack of a schema. However, we're unable to pass in a temp schema parameter to prevent this issue from happening.

We worked around it by pulling down the raw untranslated queries, then translating with SqlRender / tempEmulationSchema. These queries ran without error.

[chrisknoll]

Ok, we should fix, but will need to understand the necessary change: I think we run sqlRender() when we are passed in a dialect to the endpoint, so I think the change is the endpint needs to accept that param so that it's passed into sqlRender...does that sound like the necessary change?

[katy-sadowski]

Yeah, I think it should work just like ddl/results:

WebAPI/src/main/java/org/ohdsi/webapi/service/DDLService.java

Lines 132 to 154 in f258186

	* @param tempSchema
	* @return SQL to create tables in results schema
	*/
	@GET
	@Path("results")
	@Produces("text/plain")
	public String generateResultSQL(
	@QueryParam("dialect") String dialect,
	@DefaultValue("vocab") @QueryParam("vocabSchema") String vocabSchema,
	@DefaultValue("results") @QueryParam("schema") String resultSchema,
	@DefaultValue("true") @QueryParam("initConceptHierarchy") Boolean initConceptHierarchy,
	@QueryParam("tempSchema") String tempSchema) {
	Collection<String> resultDDLFilePaths = new ArrayList<>(RESULT_DDL_FILE_PATHS);
	if (initConceptHierarchy) {
	resultDDLFilePaths.addAll(INIT_CONCEPT_HIERARCHY_FILE_PATHS);
	}
	String oracleTempSchema = ObjectUtils.firstNonNull(tempSchema, resultSchema);
	Map<String, String> params = new HashMap<String, String>() {{
	put(VOCAB_SCHEMA, vocabSchema);
	put(RESULTS_SCHEMA, resultSchema);
	put(TEMP_SCHEMA, oracleTempSchema);

One note (out of scope) - oracleTempSchema is actually deprecated, in favor of tempEmulationSchema. But it still works.