Validation and Output

[enric.ballo]

Hi,

In the old sql script we had a validation ( just check if there is any invalid object in the database ), and after the validation if there is any invalid object we were printing the result with the ones that are invalid. This was really useful when you are running the script.

I partially fixed the problem using a precondition, and if there is any invalid object i just finish the execution of the script and i print the error message. But I would like to list the invalid objects also. Any Idea or suggestion ??

Current code:

1. <preConditions onFailMessage=“There are invalid objects in the database!!” 

   onFail=“HALT” >

   <![CDATA[

   SELECT count(1)

   FROM all_objects alo

   WHERE status = ‘INVALID’

   AND alo.owner in

   (‘CIN’,‘NOPA’,‘ORC’,‘PRV’,‘SEC’,‘TIPI_OWNER’,‘TT’,‘ZON’);

   ]]>

   <![CDATA[

   SELECT alo.owner

   , alo.object_name

   , alo.status

   , alo.last_ddl_time

   FROM all_objects alo

   WHERE status = ‘INVALID’

   AND alo.owner in

   (‘CIN’,‘NOPA’,‘ORC’,‘PRV’,‘SEC’,‘TIPI_OWNER’,‘TT’,‘ZON’);

   ]]>

Thanks in advance

[nvoxland]

To get the problem object in the output message, you’ll probably need to write a custom precondition class. There is documentation on it at http://www.liquibase.org/documentation/preconditions.html 

Within a custom precondition, you have whatever flexibility you need both in what you are checking and the output.

Nathan

[un1378880814576r10id]

Thanks for your reply Nathan!

I’m trying to make it work, and looks good so far, but I would like to enable the logger, then will be easy to see what i’m doing. But I don’t know why I can’t see the log in debug level.

This is my code: 

3. <preConditions onFailMessage="There are invalid objects in the database!!"

4. onFail=“HALT”>

5. <customPrecondition

6. className=“nl.tele2.bos.liquibase.custom.preconditions.CustomBuildCheck”>

7. <param name=“sql”
8. value="SELECT count(1)
9. FROM all_objects alo
10. WHERE status = ‘INVALID’
11. AND alo.owner in

12. (‘BOS_RELEASE’,‘BIL’,‘BLIS’,‘CBSMIG’,‘COJO’,‘CMD_AGENT’,‘CIN’,‘NOPA’,‘ORC’,‘PRV’,‘SEC’,‘TIPI_OWNER’,‘TT’,‘ZON’)

13. AND alo.OBJECT_NAME not like ‘TST%’
14. AND alo.OBJECT_NAME not like ‘TEST%’
15. AND ALO.OBJECT_NAME <> ‘ZON_ORBIT_LOG_PCK’
16. AND ALO.OBJECT_NAME <> ‘ZON_TESTING_PCK’
17. AND ALO.OBJECT_NAME <> ‘ZON_MONITORING_PCK’;"/>
18. <param name=“sqlNames” value="SELECT alo.owner
19. , alo.object_name
20. , alo.status

21. ,

22. alo.last_ddl_time

23. FROM all_objects alo
24. WHERE status = ‘INVALID’

25. AND

26. alo.owner in

27. (‘BOS_RELEASE’,‘BIL’,‘BLIS’,‘CBSMIG’,‘COJO’,‘CMD_AGENT’,‘CIN’,‘NOPA’,‘ORC’,‘PRV’,‘SEC’,‘TIPI_OWNER’,‘TT’,‘ZON’)

28. AND alo.OBJECT_NAME not like ‘TST%’
29. AND alo.OBJECT_NAME not like

30. ‘TEST%’

31. AND ALO.OBJECT_NAME <> ‘ZON_ORBIT_LOG_PCK’

32. AND

33. ALO.OBJECT_NAME <> ‘ZON_TESTING_PCK’
34. AND ALO.OBJECT_NAME <>

35. ‘ZON_MONITORING_PCK’

36. ORDER BY alo.owner, alo.object_name;"/>

Java class:

2. import liquibase.database.Database;
3. import liquibase.database.DatabaseConnection;
4. import liquibase.exception.CustomPreconditionErrorException;
5. import liquibase.exception.CustomPreconditionFailedException;
6. import liquibase.exception.DatabaseException;
7. import liquibase.executor.ExecutorService;
8. import liquibase.logging.LogFactory;
9. import liquibase.logging.Logger;
10. import liquibase.precondition.CustomPrecondition;
11. import liquibase.statement.core.RawSqlStatement;
12. import lombok.Getter;
13. import lombok.Setter;
15. /**
16.  * CustomBuildCheck Precondition
17.  *
18.  *
19.  *
20.  *
21.  *
22.  * 
23.  * @author Enric Ballo 
24.  * @since Oct 24, 2014
25.  */
26. public class CustomBuildCheck implements CustomPrecondition {
27.     
28.     private final Logger logger = LogFactory.getInstance().getLog("CustomBuildCheck");
30.     @Getter
31.     @Setter
32.     private String expectedResult;
34.     @Getter
35.     @Setter
36.     private String sql;
38.     @Getter
39.     @Setter
40.     private String sqlNames;
42.     @Override
43.     public void check(final Database database) throws CustomPreconditionFailedException, CustomPreconditionErrorException {
45.         final DatabaseConnection connection = database.getConnection();
46.         try {
47.             logger.debug("CustomBuildCheck is executed...");
48.             
49.             final String result =
50.                     ExecutorService.getInstance().getExecutor(database).queryForObject(
51.                             new RawSqlStatement(getSql().replaceFirst(";$", "")), String.class);
53.             if (result == null) {
54.                 throw new CustomPreconditionFailedException("No rows returned from SQL Precondition");
55.             }
56.             
57.             logger.debug("result is :"+result);
58.             
59.             if (!expectedResult.equals(result)) {
60.                 
61.                 logger.debug("We have some errors! ");
63.                 // final String resultNames = ExecutorService.getInstance().getExecutor(database).queryForObject(new RawSqlStatement(getSqlNames().replaceFirst(";$", "")), String.class);
65.                 throw new CustomPreconditionFailedException("SQL Precondition failed.  Expected '" + expectedResult + "' got '" + result
66.                     + "'");
67.             }
69.         } catch (final DatabaseException e) {
70.             throw new CustomPreconditionFailedException(e.getMessage());
71.         }
72.     }
74. }

Result in the console:

2. DEBUG 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-changelog.xml: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Computed checksum for  as d41d8cd98f00b204e9800998ecf8427e
3. DEBUG 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-changelog.xml: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Running Changeset:liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo
4. DEBUG 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-changelog.xml: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Changeset liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo
5. DEBUG 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-changelog.xml: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Executing QUERY database command: SELECT count(1)     FROM all_objects alo     WHERE status = 'INVALID'     AND alo.owner in     ('BOS_RELEASE','BIL','BLIS','CBSMIG','COJO','CMD_AGENT','CIN','NOPA','ORC','PRV','SEC','TIPI_OWNER','TT','ZON')     AND alo.OBJECT_NAME not like 'TST%'     AND alo.OBJECT_NAME not like 'TEST%'     AND ALO.OBJECT_NAME <> 'ZON_ORBIT_LOG_PCK'     AND ALO.OBJECT_NAME <> 'ZON_TESTING_PCK'     AND ALO.OBJECT_NAME <> 'ZON_MONITORING_PCK'
6. SEVERE 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-changelog.xml: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Change Set liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo failed.  Error: Migration failed for change set liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo:
7.      Reason:
8.           liquibase/changelogs/bos-incremental-changelog.xml : There are invalid objects in the database!!
10. liquibase.exception.MigrationFailedException: Migration failed for change set liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo:
11.      Reason:
12.           liquibase/changelogs/bos-incremental-changelog.xml : There are invalid objects in the database!!
14.         at liquibase.changelog.ChangeSet.execute(ChangeSet.java:463)
15.         at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:43)
16.         at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:70)
17.         at liquibase.Liquibase.update(Liquibase.java:195)
18.         at liquibase.Liquibase.update(Liquibase.java:174)
19.         at liquibase.integration.commandline.Main.doMigration(Main.java:997)
20.         at liquibase.integration.commandline.Main.run(Main.java:170)
21.         at liquibase.integration.commandline.Main.main(Main.java:89)
22. Caused by: liquibase.exception.PreconditionFailedException: Preconditions Failed
23.         at liquibase.precondition.core.PreconditionContainer.check(PreconditionContainer.java:220)
24.         at liquibase.changelog.ChangeSet.execute(ChangeSet.java:449)
25.         ... 7 more
26. DEBUG 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Release Database Lock
27. DEBUG 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Executing UPDATE database command: UPDATE BOS_RELEASE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
28. INFO 10/27/14 3:27 PM: liquibase: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Successfully released change log lock
29. Unexpected error running Liquibase: Preconditions Failed

How shall i use the Logger ? do am I doing something wrong ?

Thanks in advance

[un1378880814576r10id]

Ok, I just fixed

Just if someone want to know the LogFactory should be like this:

1.     private final Logger logger = LogFactory.getInstance().getLog("liquibase");

[nvoxland]

Glad you got it figured out. Thanks for letting us know.

Nathan

[nvoxland]

The easiest way with JdbcExecutor to query is to use a queryForList() method. If you want to specify the exact SQL, use executor.queryForList(new RawSqlStatement(YOUR_SQL) which will return a List of Maps containing the data from the result set.

Nathan

[nvoxland]

Great, thanks for sharing.

Nathan

[un1378880814576r10id]

I’m almost done, but i would like to know if there is a way to execute a query and get a list of results.

I was looking into JdbcExecutor class and i don’t have a clear idea how i can do that.

I would like to do this query when I have errors, and then display in the logs as a sever the list of all the object that are not working.

2. SELECT    alo.owner
3. , alo.object_name
4. , alo.status
5. , alo.last_ddl_time
6. FROM all_objects alo
7. WHERE status = 'INVALID'
8. AND alo.owner in
9. ('BOS_RELEASE','BIL','BLIS','CBSMIG','COJO','CMD_AGENT','CIN','NOPA','ORC','PRV','SEC','TIPI_OWNER','TT','ZON')
10. AND alo.OBJECT_NAME not like 'TST%'
11. AND alo.OBJECT_NAME not like 'TEST%'
12. AND ALO.OBJECT_NAME <> 'ZON_ORBIT_LOG_PCK'
13. AND ALO.OBJECT_NAME <> 'ZON_TESTING_PCK'
14. AND ALO.OBJECT_NAME <> 'ZON_MONITORING_PCK'
15. ORDER BY alo.owner, alo.object_name;

Thanks in advance,

[un1378880814576r10id]

I already have a solution for that I would like to share with you guys, maybe is useful for someone.

Java Class

2. /**
3.  * CustomBuildCheck Precondition
4.  *
5.  *
6.  *
7.  *
8.  * 
9.  * @author Enric Ballo (enric.ballo@tele2.com)
10.  * @since Oct 24, 2014
11.  */
12. @Getter
13. @Setter
14. public class CustomBuildCheck implements CustomPrecondition {
16.     private static final String LAST_DDL_TIME = "LAST_DDL_TIME";
18.     /** Liquibase logger */
19.     private final Logger logger = LogFactory.getInstance().getLog("liquibase");
21.     /** the sql to execute */
22.     private String sql;
24.     /** the expected result */
25.     private String expectedResult;
27.     @SuppressWarnings("rawtypes")
28.     @Override
29.     public void check(final Database database) throws CustomPreconditionFailedException, CustomPreconditionErrorException {
31.         try {
32.             
33.             logger.debug("CustomBuildCheck is executed...");
35.             final List<Map> resultQuery =
36.                     ExecutorService.getInstance().getExecutor(database).queryForList(
37.                             new RawSqlStatement(getSql().replaceFirst(";$", "")));
39.             if (resultQuery == null) {
40.                 throw new CustomPreconditionFailedException("No rows returned from SQL Precondition");
41.             }
43.             final int size = resultQuery.size();
44.             
45.             logger.debug("result is :" + size);
46.             logger.debug("expected result is :" + expectedResult);
47.             
48.             final Integer resultInteger = new Integer(size);
49.             final Integer expectIntegerResult = new Integer(expectedResult);
51.             if (!expectIntegerResult.equals(resultInteger)) {
53.                 logger.debug("We have some errors! displaying the result");
55.                 final StringBuilder invalidObject = new StringBuilder();
56.                 
57.                 int counter = 1;
58.                 for (final Map map : resultQuery) {
60.                     invalidObject.append("  " + counter + " )  ");
62.                     for (final Map.Entry entry : map.entrySet()) {
63.                         invalidObject.append(entry.getValue());
64.                         
65.                         //Avoid the last -
66.                         if (!entry.getKey().equals(LAST_DDL_TIME)) {
67.                             invalidObject.append(" - ");
68.                         }
70.                     }
72.                     invalidObject.append("\n");
73.                     counter++;
74.                 }
76.                 final StringBuilder message = new StringBuilder();
77.                 message.append("\n");
78.                 message.append("\n");
79.                 message.append("\n");
80.                 message.append("--------------------------------------------------------------------------------------------- \n");
81.                 message.append("               ERROR - There are " + size + " invalid Objects in the Database \n");
82.                 message.append("--------------------------------------------------------------------------------------------- \n");
83.                 message.append("      OWNER - OBJECT NAME - STATUS - LAST DDL TIME \n");
84.                 message.append("\n");
85.                 message.append(invalidObject.toString());
86.                 message.append("\n");
87.                 message.append("--------------------------------------------------------------------------------------------- \n");
88.                 message.append("\n");
89.                 message.append("\n");
91.                 throw new CustomPreconditionFailedException(message.toString());
92.             }
94.         } catch (final DatabaseException e) {
95.             throw new CustomPreconditionFailedException(e.getMessage());
96.         }
97.     }
99. }

ChangeLog file

4. <customPrecondition

5. className=“nl.tele2.bos.liquibase.custom.preconditions.CustomBuildCheck”>

6. <param name=“sql” value="SELECT alo.owner

7.                                   ,alo.object_name

8.                                   ,alo.status

9.                                   ,alo.last_ddl_time

10.                              FROM all_objects alo

11.                             WHERE status = ‘INVALID’

12.                               AND alo.owner in (‘BOS_RELEASE’,‘BIL’,‘BLIS’,‘CBSMIG’,‘COJO’,‘CMD_AGENT’,‘CIN’,‘NOPA’,‘ORC’,‘PRV’,‘SEC’,‘TIPI_OWNER’,‘TT’,‘ZON’)

13.                               AND alo.OBJECT_NAME not like ‘TST%’

14.                               AND alo.OBJECT_NAME not like ‘TEST%’

15.                               AND ALO.OBJECT_NAME <> ‘ZON_ORBIT_LOG_PCK’

16.                               AND ALO.OBJECT_NAME <> ‘ZON_TESTING_PCK’

17.                               AND ALO.OBJECT_NAME <> ‘ZON_MONITORING_PCK’

18.                          ORDER BY alo.owner, alo.object_name;"/>

Sample output:

SEVERE 10/30/14 9:52 AM: liquibase: liquibase/changelogs/bos-incremental-changelog.xml: liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo: Change Set liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo failed.  Error: Migration failed for change set liquibase/changelogs/bos-incremental-databaseCheck-changelog.xml::dataBaseCheck_v2::e-ballo:

     Reason:

          liquibase/changelogs/bos-incremental-changelog.xml : Custom Precondition Failed:

---------------------------------------------------------------------------------------------

               ERROR - There are 1 invalid Objects in the Database

---------------------------------------------------------------------------------------------

      OWNER - OBJECT NAME - STATUS - LAST DDL TIME

  1 )  ZON - T2_ORDER_ENTRY_PCK - INVALID - 2014-10-30 09:52:36.0

---------------------------------------------------------------------------------------------