Substitute variables

[GoogleCodeExporter]

Some databases allow the developer to declare variables in their sql
scripts. For example, in PostgreSQL a variable name is preceded by a colon.

grant select on app_user to :readonly_role;

The value of the variable can be provided in different ways depending on
how the script is run (command line parameters, prompt dialogs etc).

It would be great if DbDeploy were able to substitute these variables when
it generates its delta script. Ideally, this would work without requiring
any DbDeploy-specific syntax in the sql scripts. i.e. The scripts should
still work with other tools.

Original issue reported on code.google.com by kevin.st...@gmail.com on 17 Oct 2009 at 8:39

[GoogleCodeExporter]

Is it not possible to do the variable substitution in the normal way by running 
the
script using the database vendor's tool?

Can you give me some example use cases for this?

(Why? Because although I think I can see where you're coming from, I think this 
will
be quite hard to implement, especially in a way that works ok for both
direct-db-apply mode and script-creation mode, and works ok with all the 
different
data types including date time. On all databases.)

Original comment by gtack...@googlemail.com on 18 Oct 2009 at 6:36

[GoogleCodeExporter]

Original comment by gtack...@googlemail.com on 18 Oct 2009 at 6:36

• Added labels: Component-Core, Type-Enhancement

[GoogleCodeExporter]

I'll describe the use case first.

We have multiple environments each with their own database. E.G. Prod, UAT1, 
UAT2,
one per developer. Each environment database has an owner account that creates 
and
owns the database objects but the application connects using a different user 
account
that has been granted read and write privileges. The app user accounts have 
different
usernames and passwords in each environment. So we use variables in the SQL 
scripts
to enable us to run the same script on each database and provide the 
appropriate user
details when the script is run.

Yep, its possible to generate the script using DbDeploy and then run it using 
some
other tool, but I would like to be able to run the script as part of the Ant 
build.
I've tried invoking psql (Postgres command line tool) using the Ant exec task 
but it
doesn't work due to some issues with incorrect quoting of command line 
parameters. So
instead I have to write a shell script that runs the SQL and invoke that from 
Ant. I
would prefer to avoid this approach because it adds an extra layer of 
indirection,
requires developers to have the psql client installed and requires 
corresponding .bat
scripts for portability. 

With regards to implementation, I think it will involve a parsing step when the
contents are being read from the file but it shouldn't be too difficult. The
ChangeScript.getFileContents method looks like the right place. However, it 
would
require a parser for each supported dbms.

I think from your comment about data types that your understanding is a little
different from mine. I'm not sure if I'm correct or not but as far as I'm aware,
there is no intelligence involved with regards to data type conversion. I think
variables are just used as placeholders for text in the SQL file. At least, 
that's
the only way I've ever used them. 

This link gives some info about how they can be used in Postgres:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html#APP-PSQL-VARIABLES

Original comment by kevin.st...@gmail.com on 18 Oct 2009 at 8:01