SingleRecordSQL() - Generate all SQL for inserting or upserting a flat array

Post your working scripts, libraries and tools
Qriist
Posts: 32
Joined: 11 Sep 2016, 04:02

SingleRecordSQL() - Generate all SQL for inserting or upserting a flat array

21 Mar 2020, 15:48

Hello! This simple-to-use SQLite function is meant to ease iterating over a large array of data, especially data from APIs that may not return a consistent set of keys between different calls.

Code: Select all

SingleRecordSQL(table,array,upsertkey := "",upsertdiscardkeys := ""){
	/*
		[table]						a string containing the name of the table to insert into. If working with multiple databases prepend the schema name here as you normally would.
		[array]						the array of data you want to insert. The function will safely ignore nested arrays; pass those in seperate calls.
		[upsertkey]		[optional]	pass a string numeric array with column name *values* that reflects the ON CONFLICT(key)
		[upsertdiscardeys]	[optional]	pass a comma-delimited string of column names, or a numeric array with column name *values* to ignore on an upsert.
		_								*KNOWN LIMITATION*	keynames with commas are not currently discarded; probably shouldn't use commas in your column names anyways
	*/
	If (upsertkey != "") {	;only process upserts if there's an upsertkey
		outup := "ON CONFLICT(" sqlGlue(upsertkey,1) ") DO UPDATE SET`n"
		for k, v in array
			if !IsObject(v)	;do not go into a nested array.
			{
				If !IfIn(sqlQuote(k),sqlGlue(upsertdiscardkeys))	;does not currently handle key names with commas
				&& !IfIn(k,sqlGlue(upsertkey,-1))			;does not currently handle key names with commas
					outup .= (final := a_tab sqlQuote(k,1) "=" "excluded." sqlQuote(k,1)) ",`n" ;"`t`t-- " sqlQuote(v) "`n"
			}
	}
	values := SubStr(values, 2)
	
	return Format("INSERT OR IGNORE INTO {}({}) VALUES{}", sqlQuote(table,1), sqlGlue(array,1,1), "(" sqlGlue(array) ")") 
	.	(upsertkey=""?"":"`n" RTrim(outup,"`n,") "`n") ;conditionally pass the upsert language
	.	";`n"
}
	

sqlQuote(value,wrap := 0) { ;escapes the strings and corrects NULL values
	/*
		Defaults based on SQLite
		'keyword'		A keyword in single quotes is a string literal.
		"keyword"		A keyword in double-quotes is an identifier.
		[keyword]		A keyword enclosed in square brackets is an identifier. This is not standard SQL. 
		_				This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
		`keyword`		A keyword enclosed in grave accents (ASCII code 96) is an identifier. 
		_				This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.
	*/
	
	static w := {-1:"",0:"'",1:"""",2:"[",3:"``"}
	return (value != "")? w[wrap] StrReplace(value, "'", "''") (wrap!=2?w[wrap]:"]")
		: "NULL"
}

sqlGlue(array,wrap := 0,RetCols := 0,delim := ",")
{
	/*
		Generates an escaped fragment of SQL for use as a list of columns or values
		wrap accepts 0-3 as in sqlQuote
		RetCols returns key names instead of key values if true
	*/
	for k,v in array
		new.=sqlQuote((RetCols=0?v:k),wrap) delim
	return (IsObject(array)=1?trim(new, delim):array)	;return the newly built string if 'array' is an object, or pass the unaltered string
}

;You can remove this if you use Functions.ahk
IfIn(ByRef var, MatchList) {
	If var in %MatchList%
		Return, true
}
So let's look at some examples, eh?

Code: Select all

;Sample array
dummy := []
dummy["a"] := "yes"
dummy["b"] := "no"
dummy["c"] := "maybe"
dummy["d"] := "I don't know"
dummy["e"] := "can you repeat the question?"
dummy["🔪"] := "you're not the boss of me now"	;note that this has a single-quote in "you're"
dummy["a","x"] := "nested array that nobody likes"
The easiest insertion mechanism is to pass the table and array.
Note the escaped quote in column 🔪

Code: Select all

;SingleRecordSQL("test",dummy)
INSERT OR IGNORE INTO "test"("a","b","c","d","e","☺") INSERT OR IGNORE INTO "test"("a","b","c","d","e","🔪") 
VALUES('yes','no','maybe','I don''t know','can you repeat the question?','you''re not the boss of me now');
But let's assume we want to dump data in while updating other data, conforming or database to the source data. 🔪 will be our PRIMARY or UNIQUE key. Well shoot, we passed it as a string and the function simply passed it to the ON CONFLICT clause. this might work or it might not, depending on the engine and text supplied.

Code: Select all

;SingleRecordSQL("test",dummy,"🔪") 
INSERT OR IGNORE INTO "test"("a","b","c","d","e","🔪") 
VALUES('yes','no','maybe','I don''t know','can you repeat the question?','you''re not the boss of me now')
ON CONFLICT(🔪) DO UPDATE SET
	"a"=excluded."a",
	"b"=excluded."b",
	"c"=excluded."c",
	"d"=excluded."d",
	"e"=excluded."e"
;
On the other hand, if we pass it as the sole item in an array, then the function knows to wrap it.

Code: Select all

;SingleRecordSQL("test",dummy,["🔪"]) 
INSERT OR IGNORE INTO "test"("a","b","c","d","e","🔪") 
VALUES('yes','no','maybe','I don''t know','can you repeat the question?','you''re not the boss of me now')
ON CONFLICT("🔪") DO UPDATE SET
	"a"=excluded."a",
	"b"=excluded."b",
	"c"=excluded."c",
	"d"=excluded."d",
	"e"=excluded."e"
;
Lastly, we've had some database changes. The company has decided we will no longer retroactively update "🔪" or "d" and instead use c as our primary key. If it's initially supplied we'll use it, but these keys are now considered immutable for whatever reason.

Code: Select all

;ignoreArr := []
;ignoreArr.push("🔪")
;ignoreArr.push("d")
;SingleRecordSQL("test",dummy,["c"],ignoreArr) 
INSERT OR IGNORE INTO "test"("a","b","c","d","e","🔪") 
VALUES('yes','no','maybe','I don''t know','can you repeat the question?','you''re not the boss of me now')
ON CONFLICT("c") DO UPDATE SET
	"a"=excluded."a",
	"b"=excluded."b",
	"e"=excluded."e"
;
Thanks to @GeekDude and @tidbit for various aspects of this code as a launching point (even if almost nothing of the original code exists anymore...)
Last edited by Qriist on 23 Mar 2020, 23:30, edited 1 time in total.
burque505
Posts: 1312
Joined: 22 Jan 2017, 19:37

Re: SingleRecordSQL() - Generate all SQL for inserting or upserting a flat array

22 Mar 2020, 10:01

@Qriist, thanks for this. Very useful.

BTW, at line 52 of your post there's an extraneous 's' at the beginning of the line. It's not like people won't find it right away, I know. :)

Regards,
burque505
Qriist
Posts: 32
Joined: 11 Sep 2016, 04:02

Re: SingleRecordSQL() - Generate all SQL for inserting or upserting a flat array

23 Mar 2020, 23:31

burque505 wrote:
22 Mar 2020, 10:01
s
Huh, how'd that get there? lol
Fixed.


Glad you like it! :D

Return to “Scripts and Functions”

Who is online

Users browsing this forum: hoppfrosch, pgeugene and 25 guests