This is a continuation of my previous article – an introduction to Liquibase SQLcl – in which I’ve shown you how to capture all objects from the DEV schema and move them to UAT (be sure to read that first). Now, we’ll focus on tracking changes in the DEV environment and moving these changes to UAT, too. Like last time, we’ll do this without thinking about what should be moved and in what order – SQLcl Liquibase will do this for us.
First, let’s make some changes to our DEV environment, so we have something to track and export. They’re as follows:
create table locations
(location_id number(4),
street_address varchar2(40),
postal_code varchar2(12),
city varchar2(30)
constraint loc_city_nn not null,
state_province varchar2(25),
country_id char(2)
);
create unique index loc_id_pk
on locations (location_id);
alter table locations
add (constraint loc_id_pk
primary key (location_id),
constraint loc_c_id_fk
foreign key (country_id)
references countries(country_id)
);
create or replace procedure secure_dml
is
begin
if TO_CHAR(SYSDATE, 'HH24:MI') not between '08:00' and '18:00'
or TO_CHAR(SYSDATE, 'DY') in ('SAT', 'SUN')
then
RAISE_APPLICATION_ERROR(-20205,
'This is a change for this blog.You may only make changes during normal office hours');
end if;
end secure_dml;
Now, how to export these changes? There are two ways to do it (I am logged into DEV schema using SQLcl and located in the database folder).
This way of exporting changes is simpler because SQLcl Liquibase will automatically handle all objects and add what is needed to the controller.xml file. However, generating a full schema can take time, especially with bigger databases.
1. First, remove everything from the database folder (all files generated using the generate-schema command from part 1 of this blog series):
2. Generate DEV schema again by executing this command (SQLcl doesn’t replace existing files, that’s why I had to remove them first):
liquibase generate-schema --split
3. If you are tracking your changes with GIT, you will notice that there are 3 of them:
You can also export only new/changed objects. For obvious reasons, this way of exporting is much faster, and it allows you to add only your changes to version control tools like GIT. However, you’ll need to remember to add new lines to the controller.xml file in the correct order.
1. First, go into the /database/procedure/ folder
2. Remove the secure_dml_procedure.xml file
3. Export a new version of the secure_dml procedure
liquibase generate-object --object-type procedure --object-name secure_dml
4. Now, go into /database/table/ and export your newly created table
liquibase generate-object --object-type table --object-name locations
5. Then, manually add a line with a new table to the controller.xml file
🗄️ Need database experts? Pretius has a team of over 200 experienced developers, software architects and other IT specialists. Contact us at hello@pretius.com and tell us your needs.
Since I’ve exported the actual version of the DEV schema, all that remains is moving changes to my UAT.
1. Log into UAT using SQLcl (if you wonder how I set up a connection that way, check this SQLcl’s new feature)
2. Check what changes will be moved from DEV to UAT by executing the update-sql command (this command doesn’t change anything in a database).
liquibase update-sql --changelog-file controller.xml --output-file preview.sql
3. Review generated file. Notice that besides the things that Liquibase logs into %DATABASECHANGELOG% objects, there are two changes:
4. Since everything looks good, we can move these changes to UAT using the update command
liquibase update --changelog-file controller.xml
You can check new rows with changesets in the DATABASECHANGELOG table or the exact SQL that was executed for every changeset.
And that’s it – my DEV and UAT environments are now equal, with the exact same number and versions of objects. If you followed this guide to the letter, you should have similar results. If you have any questions, reach out to me at rgrzegorczyk@pretius.com. Also, check out my other articles on the Pretius blog:
Want to use Liquibase in your software projects? Pretius has extensive knowledge regarding this technology, and world-class expert who know it better than most. Reach out to us at hello@pretius.com – maybe we can help you out!
