Introduce upsert_row QOL function

[majdisorder]

Is your feature request related to a problem? Please describe.

It would be nice to have an upsert function.

Describe the solution you'd like

Here is how I currently solved it in gdscript.

NOTE: it would make sense to replace the ìd_col parameter by a conditions parameter to be more in line with other QOL functions like select_rows and update_rows

const __upsert_query: String = "SELECT %s FROM %s WHERE %s = ? LIMIT 1;" 

# checks arguments and logs debug msgs
static func __guard(db: SQLite, table: String, id_col: String, row_data: Dictionary) -> bool:
	return Guard.check_null_or_empty(db, "db") \
	and Guard.check_null_or_empty(table, "table") \
	and Guard.check_null_or_empty(id_col, "id_col") \
	and Guard.check_has(row_data, id_col, "row_data")

static func upsert_row(db: SQLite, table: String, id_col: String, row_data: Dictionary) -> void:
	if not __guard(db, table, id_col, row_data): return
	var safe_table = escape_db_token(table) # escapes table name
	var safe_id_col = escape_db_token(id_col) # escapes column name
	var safe_row = escape_column_names(row_data) # escapes all property names in the dictionary
	db.query_with_bindings(
		__upsert_query % [safe_id_col, safe_table, safe_id_col], 
		[row_data[id_col]]
	)
	if db.query_result.is_empty():
		db.insert_row(safe_table, safe_row)
		return
	db.update_rows(
		safe_table,
		"%s = '%s'" % [safe_id_col, row_data[id_col]],
		safe_row
	)
	

EDIT: I just noticed the code above does not account for the fact that id_col may already be escaped or that the corresponding property in row_datamay be escaped. That would be something to keep in mind.

[majdisorder]

Upon further investigation I realized that sqlite actually has syntax for this.
https://www.sqlite.org/lang_upsert.html

INSERT INTO my_table(id, col1, col2) 
VALUES
	('id1','value1', 1),
	('id2','value2', 2),
	('id3','value3', 3)
  ON CONFLICT(id) DO 
  UPDATE SET 
  	col1=excluded.col1,
  	col2=excluded.col2;