Defining property dynamically with query in database

General Discussion
1

Hi again,

is there a possibility to define a property dynamically with a query in the database?

For example I need to query an id to update some tables with . Can I also use the property in the (or ) statement?

It would be okay when I have to use custom sql to get the value for the property.

Thanks,
Markus

 

2

Are you talking about changelog properties ( http://www.liquibase.org/manual/changelog_parameters) ? Those cannot be defined from a query.


To use subqueries in an update “update X set manager_id=(select id from person where name=‘Bob’) where …”, you will have to use


Nathan

3

You can’t do something like:


update table1 set field1 = ‘xyz’ where language_id=1 and key = ‘test’



Nathan

4

I need to query an id and temporarily save it because it's not allowed to use the same table in a subquery of the WHERE-statement.

i.e.:
UPDATE table1 SET field1 = 'xyz' WHERE id = (SELECT id FROM table1 WHERE language_id = 1 AND key = 'test')

So a statement is not the solution :(

5

Hi Nathan,

sorry, I took a bad example.

Sometimes I have to get an ID and have to use it later again. Example (I hope better than the last ;-):

  1. Lookup ID for role "admin"
  2. Set property "admin-role-id" = result from the lookup
  3. Update () table "users" - set role to property "admin-role-id" for user "zierhut"

Yesterday I had the idea of testing it with in the . It didn't test it right now but is it possible to use there a sql query like "(SELECT id FROM roles WHERE title='admin')"?

Thanks for your quick replies!

Markus

6

I think the valueComputed with a select should work fine assuming your database supports that. Otherwise you can use subselects like your select in an block (again, assuming  database supports it)

Nathan

7

I see that this is a really old qustion, and wonder if there is any change to this?

It would be really helpful for us to be able to set a property value dynamically via a query. The case is the following:

  • we have a number of customers using Oracle databases, some are using Enterprise Edition and others are using Standard Edition.
  • For customers using Enterprise edition, we wish to take advantage of this for example by creating indexes with the ONLINE keyword. For customers on standard edition however, that will fail. In other cases using Enterprise edition functionality will not fail, but WILL violate license limitations for those customers.

We wish to define a property IsStandardEdition, and based on the value of this property use or not use that kind of functionality in the liquibase scripts.

The value could be initialized from a query such as:

select count(*)  as IsStandardEdition
    from v$version v
    where v.banner not like '%Enterprise Edition%'
           and v.banner not like '%EE%'
           and v.banner like 'Oracle Database%'

Is this now possible, or is the 11 year old answer still the same?