Support Trino temporary table in shims

[willshen99]

Issue

Trino does not support temporary tables. As a result, the RPresto package throws an error when temporary = TRUE is passed to either dbWriteTable or sql_query_save.
To work around this, we must explicitly set temporary = FALSE when calling compute_new() or copy_to_new() in Argos when running against Trino. This results in all tables being created as permanent. If the user intends for a table to be temporary, they must manually delete it after use.

Proposed Solution

We can introduce a logic similar to the one used for Oracle in the Argos shims:

argos/R/shims.R

Lines 223 to 303 in a74bcaf

	assignInNamespace('sql_query_save.DBIConnection',
	function (con, sql, name, temporary = TRUE, ...) {
	if (temporary) {
	if (any(class(con) == 'OraConnection')) {
	temptbl <- dbplyr::sql(' ')
	self$config('ora_drop_me',
	append(self$config('ora_drop_me'), name))
	}
	else {
	temptbl <- dbplyr::sql('TEMPORARY ')
	}
	}
	build_sql("CREATE ", if (temporary)
	temptbl, "TABLE \n", as.sql(name, con), " AS ",
	sql, con = con)
	}, ns = 'dbplyr')
	assignInNamespace('sql_table_index.DBIConnection',
	function (con, table, columns, name = NULL,
	unique = FALSE, ...)
	{
	assertthat::assert_that(is_string(table) |
	dbplyr:::is.schema(table),
	is.character(columns))
	name <- name %||%
	gsub('[^A-Za-z0-9_]+', '',
	paste0(c(unclass(table), columns), collapse =
	"_"))
	if (any(class(con) == 'OraConnection') &
	nchar(name) > 30) {
	# Generate a likely unique, if not very readable, name
	name <- substr(sprintf('ix%d%d_%s%s',
	Sys.getpid(),
	as.integer(Sys.time()),
	paste0(substr(columns,1,1),
	collapse = ''),
	self$config('results_name_tag')),
	1, 30)
	}
	fields <- dbplyr::escape(ident(columns),
	parens = TRUE, con = con)
	dbplyr::build_sql("CREATE ", if (unique)
	dbplyr::sql("UNIQUE "), "INDEX ",
	dbplyr::as.sql(name, con = con), " ON ",
	dbplyr::as.sql(table, con = con), " ", fields, con = con)
	}, ns = 'dbplyr')
	}
	assignInNamespace('db_write_table.DBIConnection',
	function(con, tabname, types, values, temporary = TRUE,
	overwrite = FALSE, ...) {
	if (any(class(con) == 'OraConnection')) {
	if (temporary) self$config('ora_drop_me',
	append(self$config('ora_drop_me'),
	tabname))
	schema <- NULL
	if (inherits(tabname, c('ident_q', 'dbplyr_schema')) &
	any(grepl('.', tabname, fixed = TRUE))) {
	parts <- gsub('"', '', dbplyr::as.sql(tabname),
	fixed = TRUE)
	parts <- regmatches(parts,
	regexec('(.+)\\.(.+)', parts,
	perl = TRUE))[[1]]
	tabname <- parts[3]
	schema <- parts[2]
	}
	DBI::dbWriteTable(con, name = tabname, schema = schema,
	value = values, field.types = types,
	temporary = temporary,
	overwrite = FALSE, row.names = FALSE,
	...)
	}
	else
	DBI::dbWriteTable(con,
	name = dbplyr::dbi_quote(dbplyr::as.sql(tabname), con),
	value = values, field.types = types,
	temporary = temporary,
	overwrite = FALSE, row.names = FALSE, ...)
	tabname
	}, ns = 'dbplyr')

Specifically, we can override the dbWriteTable and sql_query_save methods for PrestoConnection to:

1. If temporary = TRUE, record the table name in a list.
2. Still create the table as a permanent table.
3. On session cleanup, call env_cleanup() to drop all tables recorded in the list.