Get sqitch deploy calling a specific command to load data.

[baugereau]

Hello,

is there a way to add a change that would be performed by a dedicated database client? Depending on SQL INSERT INTO/DELETE FROM /TRUNCATE to (un)load data is an important constraint in case of large objects (e.g. loading XML document) and geospatial objects (gdal/ogr tools). Is there a possibility to indicate to the sqitch deploy command to execute a specific command? Note, for such operation there could be a different client for the deploy (dedicated command), and revert and verify (sql scripts).

Thanks !

[theory]

No, there is not. The pattern that has emerged to try to address the need for custom clients is to add something like a shell script to your repository that knows what things it must do, and then set the engine.$name.client command to point to it. For example:

sqitch engine alter pg --client ./bin/psql_wrapper.sh

Then teach your script to do the right thing based on some criteria, like parsing the first few lines of each change script, or perhaps knowing to do something for changes with a particular name. Note that the client will be called with Sqitch with the arguments required for your CLI. For Postgres, for example, it assumes you're using psql, so passes these options based on the target configuration:

sqitch/lib/App/Sqitch/Engine/pg.pm

Lines 63 to 68 in aace1e8

	[ user => $self->username ],
	[ dbname => $uri->dbname ],
	[ host => $uri->host ],
	[ port => $uri->_port ],
	map { [ $_ => $query_params{$_} ] }
	sort keys %query_params,

As well as these options for every invocation:

sqitch/lib/App/Sqitch/Engine/pg.pm

Lines 91 to 96 in aace1e8

	'--quiet',
	'--no-psqlrc',
	'--no-align',
	'--tuples-only',
	'--set' => 'ON_ERROR_STOP=1',
	'--set' => 'registry=' . $self->registry,

And then to run a change script, it passes --file:

sqitch/lib/App/Sqitch/Engine/pg.pm

Line 329 in aace1e8

$self->_run('--file' => $file);

So you could write a shell script to parse out the --file option and determine if it should call something else.

[baugereau]

Thanks for your guidance. I keep the idea and I will try it whenever I will have a more refine data management plan in place and experimented a bit further this question.

[baugereau]

thanks for the support !