Add a column to all tables containing a specified sufifix in the name

Liquibase Development
#1

I want to add a migration to my Spring-Boot project. In this migration I intend to add a column to all the tables which name contains some suffix. I will leave an example below:

In my database I have this two tables saft_2020_1_111_nc_generalledgerentriestotals and saft_2017_2_112_nc_generalledgerentriestotals and their names both end in generalledgerentriestotals .

Is there any way I can make my migration add the column to both the tables?

I am using liquibase to manage the migrations and my database is MySQL.

I tried this way but an exception was thrown because there is no table with that name

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <changeSet author="fabio" id="changelog-4.0">
        <addColumn tableName="*_generalledgerentriestotals">
            <column name="multiple_added" type="varchar(255)"/>
        </addColumn>
        <rollback>
            <dropColumn tableName="*_generalledgerentriestotals">
                <column name="multiple_added" type="varchar(255)"/>
            </dropColumn>
        </rollback>
    </changeSet>
 
</databaseChangeLog>
1 Like
#2

Hi @fabiopires10 ,

I don’t think Liquibase has a native way to accomplish this special task at “migration time”, but you could be creative and generate the changelog from MySQL workbench (or what IDE you use) and manually add it to the deploy package, by copy/pasting the output into a new .xml file (or even use MySQL into file functionality if you have proper priviledges).
Also consider the ordering - add it after the create table changelos(s) of course.
The below 3 union-ed select statements should “assemble” your desired changelog (formatting may be further needed as pasting here changed the indentation a little).

select '<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">' as databaseChangeLog
union all
select concat('<changeSet author="fabio" id="changelog-',t.table_name,'-4.0">
<addColumn tableName="',t.table_name,'">
	<column name="multiple_added" type="varchar(255)"/>
</addColumn>
<rollback>
	<dropColumn tableName="',t.table_name,'">
		<column name="multiple_added" type="varchar(255)"/>
	</dropColumn>
</rollback>
</changeSet>'
)  as databaseChangeLog
from information_schema.tables t
where t.table_name like '%_generalledgerentriestotals'
union all
select '</databaseChangeLog>' as  databaseChangeLog

Hope this helps,
Eduard

#3

That was my approach , but the objective is to make it full automatic

1 Like
#4

Do you think I would be able to do it using modifySql tag?
https://docs.liquibase.com/workflows/liquibase-community/modify-sql.html