table.convert() method should clean up after itself

[simonw]

It currently works like this:

sqlite-utils/sqlite_utils/db.py

Lines 2177 to 2195 in 77c240d

	def convert_value(v):
	bar.update(1)
	if not v:
	return v
	return fn(v)
	self.db.register_function(convert_value)
	sql = "update [{table}] set {sets}{where};".format(
	table=self.name,
	sets=", ".join(
	[
	"[{output_column}] = convert_value([{column}])".format(
	output_column=output or column, column=column
	)
	for column in columns
	]
	),
	where=" where {}".format(where) if where is not None else "",
	)

It's registering a function called convert_value() and then failing to de-register that function once it has finished.

It might even be possible for two queries running against the same connection to clobber each other's convert_value() functions, leading to incorrect behaviour.

So two fixes: firstly it should register the function with a unique name (maybe add a random suffix). Secondly, it should de-register that function once it has finished.

[simonw]

As far as I can tell the Python sqlite3 module doesn't actually have a mechanism for de-registering a custom SQL function.

This means that if I implement a mechanism whereby each call to .convert() registers a new SQL function with a random suffix (convert_value_23424() for example) those functions will stay registered - and if .convert() is called a large number of times the number of obsolete custom function registrations will grow without bounds.

For that reason, I'm going to wontfix this issue.