Liquibase Update Command doesn't drop elements (tables, functions, procedure…) from my database despite SQL script absent from my solution

0
votes

I use liquibase tool to manage a postgres database. I work as the following :

I have a solution composed of different folders containing SQL scripts responsible for schema creation, tables creations, types creation, procedures creation, etc... Then, I have a ChangeLog file in xml format, containing the following informations :

includeAll path="#{Server.WorkingDirectory}#/02 - Schema" relativeToChangelogFile="false"

includeAll path="#{Server.WorkingDirectory}#/03 - Types" relativeToChangelogFile="false

includeAll path="#{Server.WorkingDirectory}#/04 - Tables" relativeToChangelogFile="false"

includeAll path="#{Server.WorkingDirectory}#/05 - Fonctions" relativeToChangelogFile="false"

includeAll path="#{Server.WorkingDirectory}#/06 - Stored Procedures" relativeToChangelogFile="false"

I run liquibase via command line :

liquibase --changeLogFile=$(Changelog.File.Name) --driver=$(Driver.Name) --classpath=$(Driver.Classpath) --url=$(BDD.URL) --username=$(BDD.Login) --password=$(BDD.Password) update

This enable Liquibase to take all the SQL scripts in the different folders listed in the changelogFile, compare it with the current database at url $(BDD.URL), and generate a delta script containing all the SQL queries to be executed to have a database corresponding to my solution.

This works well when I add new scripts (new tables or procedures) or modify existing scripts, my database is correctly updated by the command line, as expected. BUT it does not do anything when I delete a script from my solution. To be more factual, here is what I want :

  1. I have a SQL file containing the query "CREATE TABLE my_table" located in the folder "04 - Tables".

  2. I execute the update command above, and it creates the table "my_table" in my database.

  3. I finally do not want this table in my database any more. Thus I would like to simply remove the corresponding SQL script from my solution, and then run again the "update" command to simply remove my table in my database, generating automatically a "DROP TABLE my_table" by the liquibase "update" command. But this is not working as Liquibase doesn't record any change when I remove a sql file (whereas it does when I add or modify a file).

Does anyone know a solution to this ? Is there a specific command to drop an element when there is no "CREATE" query for this element, in a SQL solution ?

Many thanks in advance for you help :)

1
liquibase
think of liquibase like append only changes. Something like git or svn is doing. So if you want to remove something you need to write a script for that. – bilak

1 Answers

0
votes

You will need to explicitly write a script to drop the table. Other option is to rollback the change IF YOU HAVE Specified the Rollback SQL as part of your original SQL script.

There is a Pro Version option to rollback a single update , with free / community version, you can rollback last few changes in sequence

ex; I did "liquibase rollbackCount 5" will rollback the last 5 changes that were applied ONLY IF I HAD Coded the rollback sql needed as part of my script.

My Sql script sample that included the code to rollback is

--rollback drop TABLE test.user1 ; drop table test.cd_activity;

CREATE TABLE test.user1 ( user_type_id int NOT NULL ); CREATE TABLE test.cd_activity ( activity_id Integer NOT NULL userid int);