Author

Executing a CQL wildcard search in CMDBuild’s REST API

For our internal search engine I am currently developing a simple microservice to make our CMDBuild instance searchable. The microservice provides a fairly simple JSON API which itself queries the REST API of CMDBuild. Because of the insufficient documentation of CMDBuild I had to dig into the the source how to write a wildcard search query. CMDBuild has its own query language called CQL (CMDBuild Query Language). The CQL statements are converted into SQL which can be executed natively by PostgreSQL. CQL does also allow to include native SQL statements into the CQL queries. Native SQL statements are masked with (/( … )/). Between us, the combination CQL and SQL produces a absolute messy code, but this another story.

One problem is, that the REST search API of CMDBuild is exposed through HTTP GET. Accessing the HTTP endpoint with a filter like

GET https://cmdbuild/services/rest/v2/cql?filter={CQL: "from Department where Description (/(LIKE 'Develop%')/)"}

does unfortunately confuse the Apache CXF interceptor which struggles upon the percent sign. Encoding the percent does not help and a POST request is not allowed.

To fix this problem I took a look into the source of CMDBuild. Luckily for me the CQL parser is automatically generated with help of ANTLR. The grammer file is much better than any incomplete example from the official forum. So I discovered that CQL natively provides the following operators: CONTAINS, BEGIN, END, BETWEEN, NULL.
In the end it worked as I had expected:

GET https://cmdbuild/services/rest/v2/cql?filter={CQL: "from Department where Description CONTAINS 'Develop'"}

Executing Liquibase database migrations from command line and as a shared Maven JAR

I am currently working on the migration of our time tracking system from Microsoft SQL Server/.NET to Java. Most of the logic resides in Stored Procedures and Stored Functions inside the database schema. Because of some reasons (testability, maintainability, migration from MSSQL to PostgreSQL in a far future) the whole logic must be converted to Java. As the system has a high criticality all of the end user applications must be running parallel. There are 4 tools in total, written in different languages: an old PHP web application, a bridge from JIRA to our time tracking system, another JIRA-to-time-tracker converter and the original C#/.NET fat client. All systems will be migrated bit by bit to the new Spring Boot web application.

Using Liquibase for database versioning

After collecting information about the current application environment I noticed that there were no database versioning system in use. The installation of the MSSQL schema was a pain: there were a lot of plain SQL files which had to be executed by hand. Since Java was the target programming language I decided to use Liquibase and moved the whole SQL scripts into a new Git repository, added the pom.xml and wrote a self-explaining Readme.md how the .NET developers had to use Liquibase.

Running Liquibase migration with the command line Maven plug-in

I decided to describe only the pure Maven approach and not the Liquibase installation. The execution of the Liquibase migrations are trivial and no dependencies had to be installed by hand. The pom.xml contained the following definitions:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>my.package</groupId>
	<artifactId>database-schema</artifactId>
	<version>1.0.0</version>
	<packaging>jar</packaging>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>
		<dependency>
			<!--  from internal Artifactory; Microsoft does not make the sqljdbc4.jar available in official repositories -->
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>sqljdbc4</artifactId>
			<version>4.0</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-resources-plugin</artifactId>
				<version>2.7</version>
				<configuration>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.liquibase</groupId>
				<artifactId>liquibase-maven-plugin</artifactId>
				<version>3.0.5</version>
				<configuration>
					<propertyFile>src/main/resources/liquibase/liquibase.properties</propertyFile>
				</configuration>
				<executions>
					<execution>
						<goals>
							<goal>update</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

The liquibase.properties file did only contain default values and is not important. With the configuration above I were able migrate the MS SQL schema with

mvn -Dliquibase.url=jdbc:sqlserver://$HOST:1433;databaseName=$DATABASE-Dliquibase.username=$USERNAME -Dliquibase.password=$PASSWORD liquibase:update

At this point I could update the database schema by hand. This was necessary when someone had to develop inside a .NET environment or we had to migrate a staging or production database.

Making the schema available for developer environments

I realized quickly that the approach did not work really well for my Java development environment. A lot of database migrations had to be developed and the integration test environments should be automatically in sync with the defined migrations. My idea was to let Jenkins push the Liquibase defined schema as a Maven JAR into our internal Artifactory. I should be able to include the JAR as a normal Maven dependency and let Spring’s Liquibase integration to execute the latest migrations.

Reference a db-changelog.xml inside a JAR in your application.properties

I took a look in LiquibaseProperties and saw that the changeLog attribute supports the resource syntax. All I had to do was defining the db-changelog.xml by adding the following line to the application.properties:

liquibase.change-log=classpath:/liquibase/db-changelog.my-app.xml

Please note that I changed the filename from db-changelog.xml to db-changelog.my-app.xml. This should prevent ordering issues if there is already another XML file present with the same file name. The classpath prefix is used by Spring to scan all JARs in the classpath for the requested path.

Do not use the full path name of included SQL files

As I mentioned above all SQL statements resided in their corresponding SQL files. I used the following definition in the db-changelog.my-app.xml to include the SQL files:

	<changeSet id="5" author="ckl">
		<comment>Bla</comment>
		<sqlFile dbms="mssql" encoding="utf8"
			path="install/20151126-005_sp_calculate_worklogs.sql"
			relativeToChangelogFile="true"
			splitStatements="true" />
	</changeSet>

This worked if Liquibase was either executed only through the Maven command line or as a Maven JAR dependency, but not both.

How Liquibase calculate database migration differentials

Liquibase iterates through all changesets defined in your db-changelog.xml. The XML attributes id, author and path are used at first to check whether this migration already exists in the database table DATABASECHANGELOG. If a row with the given parameters does exist, the checksum of the SQL file is calculated by normalizing the SQL file content (replacing new lines and so on). After that a MD5 checksum is generated by using the header and the content of the file.

The content of the “path” attribute differs

When executing mvn … liquibase:update inside the Git repository, the column path is filled with src/main/resources/liqiuibase/install/20151126-005_sp_calculate_worklogs.sql. Executing the migrations during the Spring startup process will result in a value classpath:/liquibase/install/20151126-005_sp_calculate_worklogs.sql for the path columns.
This means that every migration will be executed again, resulting in DDL errors.

Ignoring the path attribute

The easiest way was to use the attribute logicalFilePath in my databaseChangeLog tag. This forces all rows to have the same value of the path column:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd"
	logicalFilePath="path-ignored">
....
</databaseChangeLog>

In the DATABASECHANGELOG table the column path is filled with path-ignored.

Do not mix up different Liquibase versions

After I had fixed the previous error Liquibase showed that the calculated checksum of the files differed. At first I thought I had some encoding issues and forced everything to UTF-8 but the error was still there. It took a while until I noticed that the Maven dependency liquibase-core in the Spring Boot app and the Maven Liquibase plugin for command line execution had different versions (3.3.2 versus 3.0.5). Both versions calculates the MD5 checksum in different ways. The checksum inside the DATABASECHANGELOG table differed with the newly calculated checksum. All I had to do was changing the Liquibase Maven plug-in to use the same version:

	<build>
		<plugins>
			<plugin>
				<groupId>org.liquibase</groupId>
				<artifactId>liquibase-maven-plugin</artifactId>
				<!-- same version -->
				<version>3.3.2</version>
				<configuration>
					<propertyFile>src/main/resources/liquibase/liquibase.properties</propertyFile>
				</configuration>
				<executions>
					<execution>
						<goals>
							<goal>update</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>

TL:DR

I moved the definition of a Microsoft SQL Server database schema into its own repository, made the schema migratable with help of Liquibase and made it executable in standalone/command line mode and as a Maven JAR dependency.

ExceptionHandler of @ControllerAdvice is not executed

It happened again: after writing about some issues caused by different JVM class-loader order a similar problem occured on Friday. One of my collagues (Dev-A) asked me to look into a problem the team had. Because of unknown reasons the Spring Boot based application did not return a serialized JSON error object after a @Valid annotated controller method parameter had been validated.

@Controller
public class MyController {
	// Validator for MyDto (MyDtoValidator) got called
	@RequestMapping("/validate")
	public @ResponseBody MyData myMethod(@Valid MyDto myDto) {
		return new MyData()
	}
}

An @ControllerAdvice annotated class transformed any validation error into a new exception. This has been done to unify the validation errors when using Spring Data REST and Spring MVC validation.

@ControllerAdvice
public class ValidationErrorHandlerAdvice {

	private MessageSourceAccessor messageSourceAccessor;

	@Autowired
	public ValidationErrorHandlerAdvice(MessageSourceAccessor messageSourceAccessor) {
		Assert.notNull(messageSourceAccessor, "messageSourceAccessor must not be null");

		this.messageSourceAccessor = messageSourceAccessor;
	}

	@ExceptionHandler({ MethodArgumentNotValidException.class })
	@ResponseStatus(HttpStatus.BAD_REQUEST)
	@ResponseBody
	public RepositoryConstraintViolationExceptionMessage handleValidationErrors(Locale locale,
			MethodArgumentNotValidException exception) {
		// this method should be called if the validation of MyController.myMethod had failed
		return produceException(exception.getBindingResult());
	}

	@ExceptionHandler({ BindException.class })
	@ResponseStatus(HttpStatus.BAD_REQUEST)
	@ResponseBody
	public RepositoryConstraintViolationExceptionMessage handleValidationErrors(Locale locale,
			BindException exception) {
		return produceException(exception.getBindingResult());
	}

	private RepositoryConstraintViolationExceptionMessage produceException(BindingResult bindingResult) {
		return new RepositoryConstraintViolationExceptionMessage(
				new RepositoryConstraintViolationException(bindingResult), messageSourceAccessor);
	}
}

All in all, the controller advice itself looked fine to me, especially as the code is easy to understand and has been used in other projects too without any problems.

Empty HTTP response body

Nevertheless the behavior was mysterious:

• When calling /validated in the browser, the custom validator for MyDto so the controller method got definitely hit. Nevertheless none of the exception handlers in the ValidationErrorHandlerAdvice got called. To make it more mysterious the HTTP response Spring generated did only consist of the HTTP status code 400 (Bad Request) without any character in the HTTP response body. The response body was completely clear.
• Another developer (Dev-B) uses Linux as operating system. On his machine the code above worked without any problems and returned the expected HTTP status code 400 with the serialized JSON validation error object.

Dev-A has a Windows based machine. When he had called the “/validated” endpoint on Dev-Bs host the repsonse body contained the serialized validation error. In return, when Dev-B (Linux) had called “/validated” on Dev-As machine (Windows) the response body was empty.
I checked the HTTP request headers of both browsers but they were more or less the same and did not have any influence on any HTTP pre-filters Spring had registered. Both environments uses the Oracle JDK with different update releases (u43 vs. u63). Patching both JDKs to the same level I wanted to try at last as it seemed unlikely to be the reason.

Debugging session

I started to debug through the Spring Framework and realized that the order in which the registered exception handlers got checked for their responsibility of the current occured exception was completely different. On Dev-Bs machine the ValidationErrorHandlerAdvice were the first in the list, on Dev-A the first responsible exception handler was located in ResponseEntityExceptionHandler.
After stepping further through ResponseEntityExceptionHandler it made absolutely sense that the response body was empty on Dev-As machine. But it does not made any sense that the ResponseEntityExceptionHandler got loaded in the first place.

After searching for more @ControllerAdvice annotated classes in the project I found this piece of code:

@ControllerAdvice
public class CustomErrorController extends ResponseEntityExceptionHandler {
	@ExceptionHandler()
	public ModelAndView notFound(HttpServletRequest req, Exception exception) {
		LOG.info(exception.getMessage());
		ModelAndView mav = new ModelAndView();
		// ... not so important ...
		return mav;
	}
}

Okay, at least the exception handler of ResponseEntityExceptionHandler was introduced without any Spring magic.

Fixing the problem

During debugging the initialization phase of Spring I saw that the order of the detected controller advices was different between both systems: CustomErrorController got registered before ValidationErrorHandlerAdvice on Dev-A and vice versa on Dev-B. As the wrong behavior only occured on Windows machines I assume that the underlying component scan is responsible for the different order.

In the end the fix for this solution was easy. I annotated both controllers with @Order and gave the ValidationErrorHandlerAdvice a higher precedence than CustomErrorController.