LDAP KIM Integration Documentation

I have added documentation in a permanent location that will be updated and maintained for KIM LDAP Integration

Testing with lb-maven-plugin

Overview

These are some instructions for developers using the lb-maven-plugin on how to test your changelogs to verify they work on varying database platforms before committing changes to your VCS.

Setup

I'm going to assume you have already installed the lb-maven-plugin. Here are some preliminary setups we need to do before actual testing:

• Add lb-maven-plugin to the pom.xml
• Create connection information for each database to test.
• Create an actual changelog

Setup the lb-maven-plugin

This is what I added to my pom.xml. This ensures my changelogs are run whenever I do any kind of testing. Since I am testing both mysql and oracle databases, it requires the drivers during testing.

  <plugin>
    <groupId>com.rsmart.kuali.tools</groupId>
    <artifactId>lb-maven-plugin</artifactId>
    <version>0.0.1</version>
    <configuration>
      <changeLogTagUrl>https://svn.rsmart.com/svn/kuali/contribution/community/travel_module/tags/</changeLogTagUrl>
    </configuration>
    <executions>
      <execution>
        <id>test-liquibase-changelogs</id>
        <phase>test</phase>
        <goals>
          <goal>test</goal>
        </goals>
      </execution>
    </executions>
    <dependencies>
      <dependency>
        <groupId>com.rsmart.kuali.tools.liquibase</groupId>
        <artifactId>rsmart-lb-extensions</artifactId>
        <version>1.0.0</version>
        <scope>runtime</scope>
      </dependency>
      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.version}</version>
        <scope>runtime</scope>
      </dependency>
      <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc14</artifactId>
        <version>${oracle.version}</version>
        <scope>runtime</scope>
      </dependency>
    </dependencies>
  </plugin>

This may seem a little weird, but I also modified the resources plugin

  <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-resources-plugin</artifactId>
    <version>2.5</version>
    <executions>
      <execution>
        <id>copy-test-changelogs</id>
        <!-- here the phase you need -->
        <phase>validate</phase>
        <goals>
          <goal>copy-resources</goal>
        </goals>
        <configuration>
          <outputDirectory>${basedir}/target/changelogs/update</outputDirectory>
          <resources>          
            <resource>
              <directory>src/main/changelogs/update</directory>
            </resource>
          </resources>              
        </configuration>            
      </execution>
    </executions>
  </plugin>

The reason is because the lb-maven-plugin assumes by convention changelogs are located in src/main/changelogs and your updates are within the update directory. Further, the target location of the changelogs is in target/changelogs/update. The resources plugin needs to be modified to recognize this.

Setup the Liquibase Properties Files

The lb-maven-plugin regards any properties files in target/test-classes/liquibase to be considered a liquibase properties file. It will iterate over each one and run the changelog in target/changelogs/update for each one. In order to get properties files into target/test-classes/liquibase, I created a directory called src/test/resources/liquibase. These are already moved to the appropriate location. All I need to do is create the files:

driver: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@localhost:1521:KFS
username: TEM
password: TEMPORARY

driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/TEM
username: TEM
password: TEMPORARY

Create a Changelog

Next, I just need to make a change to test out. I'm just going to do something simple:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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-2.0.xsd">	
  <changeSet author="kuali (generated)" id="CM-156-1">
    <comment>Adding System Parameter for testing</comment>
    <sql><![CDATA[
insert into KRNS_PARM_T (NMSPC_CD, PARM_DTL_TYP_CD, PARM_NM, OBJ_ID, VER_NBR, PARM_TYP_CD, TXT, PARM_DESC_TXT, CONS_CD, APPL_NMSPC_CD) 
values ('KFS-TEM','TravelReimbursement','TEST_PARAMETER',sys_guid(),1,'CONFG','Y','System parameter to test update and rollback','A','KFS');
    
    ]]></sql>
    <rollback><![CDATA[
    delete from KRNS_PARM_T where PARM_DTL_TYP_CD = 'TravelReimbursement' AND PARM_NM = 'TEST_PARAMETER';
    ]]></rollback>
  </changeSet>
</databaseChangeLog>

I'm just adding your basic system parameter. Testing requires that I rollback the change when I'm done, so I'm forced into providing a rollback. Imagine that. Testing forcing good practices on developers. That's the way it should be.

Prepare for Testing

Now that I have created these files, I simply run the following:

mvn validate testResources

. You may recall that the changelogs are copied during the validate goal. I run testResources to copy my properties files to the appropriate locations. After doing that, I should see this in target/changelogs/update/

leo@behemoth~/.workspace/kfs/release-4-0-overlay
(21:38:19) [540] ls target/changelogs/update
CM-156.xml

I see the changelog I created. Good. I should also see my properties in target/test-classes/liquibase

leo@behemoth~/.workspace/kfs/release-4-0-overlay
(22:02:22) [541] ls target/test-classes/liquibase/
TEM.properties			TEMNIGHTLY.properties		liquibase.properties.template

There you have it. Now we're ready to test.

leo@behemoth~/.workspace/kfs/release-4-0-overlay
(21:29:10) [537] mvn validate lb:test
[INFO] Scanning for projects...
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building kfs 4.0M2
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-resources-plugin:2.5:copy-resources (copy-test-changelogs) @ kfs ---
[debug] execute contextualize
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] 
[INFO] --- lb-maven-plugin:0.0.1:test (default-cli) @ kfs ---
[WARNING] Artifact with no actual file, 'org.kuali.kfs:kfs'
[WARNING] Artifact with no actual file, 'commons-lang:commons-lang'
[WARNING] Artifact with no actual file, 'com.lowagie:itext'
[WARNING] Artifact with no actual file, 'jasperreports:jasperreports'
[WARNING] Artifact with no actual file, 'org.kuali.kfs:kfs'
[WARNING] Artifact with no actual file, 'mysql:mysql-connector-java'
[WARNING] Artifact with no actual file, 'junit:junit'
[WARNING] Artifact with no actual file, 'javax.servlet:servlet-api'
[WARNING] Artifact with no actual file, 'javax.servlet:jstl'
[WARNING] Artifact with no actual file, 'taglibs:standard'
[WARNING] Artifact with no actual file, 'javax.servlet:jsp-api'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-deploy'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-jsp-2.1'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-server'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-webapp'
[WARNING] Artifact with no actual file, 'org.hamcrest:hamcrest-library'
[WARNING] Artifact with no actual file, 'org.springframework:spring-beans'
[WARNING] Artifact with no actual file, 'org.springframework:spring-context'
[WARNING] Artifact with no actual file, 'org.springframework:spring-context-support'
[WARNING] Artifact with no actual file, 'org.springframework:spring-core'
[WARNING] Artifact with no actual file, 'org.springframework:spring-jdbc'
[WARNING] Artifact with no actual file, 'org.springframework:spring-tx'
[WARNING] Artifact with no actual file, 'org.springmodules:spring-modules-ojb'
[INFO] Parsing Liquibase Properties File
[INFO]   File: /Users/leo/.workspace/kfs/release-4-0-overlay/target/test-classes/liquibase/TEM.properties
[INFO] ------------------------------------------------------------------------
[INFO] ------------------------------------------------------------------------
[WARNING] Artifact with no actual file, 'org.kuali.kfs:kfs'
[WARNING] Artifact with no actual file, 'commons-lang:commons-lang'
[WARNING] Artifact with no actual file, 'com.lowagie:itext'
[WARNING] Artifact with no actual file, 'jasperreports:jasperreports'
[WARNING] Artifact with no actual file, 'org.kuali.kfs:kfs'
[WARNING] Artifact with no actual file, 'mysql:mysql-connector-java'
[WARNING] Artifact with no actual file, 'junit:junit'
[WARNING] Artifact with no actual file, 'javax.servlet:servlet-api'
[WARNING] Artifact with no actual file, 'javax.servlet:jstl'
[WARNING] Artifact with no actual file, 'taglibs:standard'
[WARNING] Artifact with no actual file, 'javax.servlet:jsp-api'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-deploy'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-jsp-2.1'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-server'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-webapp'
[WARNING] Artifact with no actual file, 'org.hamcrest:hamcrest-library'
[WARNING] Artifact with no actual file, 'org.springframework:spring-beans'
[WARNING] Artifact with no actual file, 'org.springframework:spring-context'
[WARNING] Artifact with no actual file, 'org.springframework:spring-context-support'
[WARNING] Artifact with no actual file, 'org.springframework:spring-core'
[WARNING] Artifact with no actual file, 'org.springframework:spring-jdbc'
[WARNING] Artifact with no actual file, 'org.springframework:spring-tx'
[WARNING] Artifact with no actual file, 'org.springmodules:spring-modules-ojb'
[INFO] Parsing Liquibase Properties File
[INFO]   File: /Users/leo/.workspace/kfs/release-4-0-overlay/target/test-classes/liquibase/TEM.properties
[INFO] ------------------------------------------------------------------------
[INFO] Executing on Database: jdbc:mysql://localhost:3306/TEM
[INFO] Tagging the database
INFO 9/21/11 9:29 PM:liquibase: Successfully acquired change log lock
INFO 9/21/11 9:29 PM:liquibase: Reading from `DATABASECHANGELOG`
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
[INFO] Doing update
INFO 9/21/11 9:29 PM:liquibase: Successfully acquired change log lock
INFO 9/21/11 9:29 PM:liquibase: Reading from `DATABASECHANGELOG`
INFO 9/21/11 9:29 PM:liquibase: ChangeSet /Users/leo/.workspace/kfs/release-4-0-overlay/target/changelogs/update/CM-156.xml::CM-156-1::kuali (generated) ran successfully in 37ms
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
[INFO] Doing rollback
INFO 9/21/11 9:29 PM:liquibase: Successfully acquired change log lock
INFO 9/21/11 9:29 PM:liquibase: Rolling Back Changeset:/Users/leo/.workspace/kfs/release-4-0-overlay/target/changelogs/update/CM-156.xml::CM-156-1::kuali (generated)::(Checksum: 3:85a5e658332342fba8b60df3a29fc393)
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] Parsing Liquibase Properties File
[INFO]   File: /Users/leo/.workspace/kfs/release-4-0-overlay/target/test-classes/liquibase/TEMNIGHTLY.properties
[INFO] ------------------------------------------------------------------------
[INFO] ------------------------------------------------------------------------
[WARNING] Artifact with no actual file, 'org.kuali.kfs:kfs'
[WARNING] Artifact with no actual file, 'commons-lang:commons-lang'
[WARNING] Artifact with no actual file, 'com.lowagie:itext'
[WARNING] Artifact with no actual file, 'jasperreports:jasperreports'
[WARNING] Artifact with no actual file, 'org.kuali.kfs:kfs'
[WARNING] Artifact with no actual file, 'mysql:mysql-connector-java'
[WARNING] Artifact with no actual file, 'junit:junit'
[WARNING] Artifact with no actual file, 'javax.servlet:servlet-api'
[WARNING] Artifact with no actual file, 'javax.servlet:jstl'
[WARNING] Artifact with no actual file, 'taglibs:standard'
[WARNING] Artifact with no actual file, 'javax.servlet:jsp-api'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-deploy'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-jsp-2.1'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-server'
[WARNING] Artifact with no actual file, 'org.eclipse.jetty:jetty-webapp'
[WARNING] Artifact with no actual file, 'org.hamcrest:hamcrest-library'
[WARNING] Artifact with no actual file, 'org.springframework:spring-beans'
[WARNING] Artifact with no actual file, 'org.springframework:spring-context'
[WARNING] Artifact with no actual file, 'org.springframework:spring-context-support'
[WARNING] Artifact with no actual file, 'org.springframework:spring-core'
[WARNING] Artifact with no actual file, 'org.springframework:spring-jdbc'
[WARNING] Artifact with no actual file, 'org.springframework:spring-tx'
[WARNING] Artifact with no actual file, 'org.springmodules:spring-modules-ojb'
[INFO] Parsing Liquibase Properties File
[INFO]   File: /Users/leo/.workspace/kfs/release-4-0-overlay/target/test-classes/liquibase/TEMNIGHTLY.properties
[INFO] ------------------------------------------------------------------------
[INFO] Executing on Database: jdbc:oracle:thin:@heisenberg.rsmart.com:1521:KFS
[INFO] Tagging the database
INFO 9/21/11 9:29 PM:liquibase: Successfully acquired change log lock
INFO 9/21/11 9:29 PM:liquibase: Reading from DATABASECHANGELOG
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
[INFO] Doing update
INFO 9/21/11 9:29 PM:liquibase: Successfully acquired change log lock
INFO 9/21/11 9:29 PM:liquibase: Reading from DATABASECHANGELOG
INFO 9/21/11 9:29 PM:liquibase: ChangeSet /Users/leo/.workspace/kfs/release-4-0-overlay/target/changelogs/update/CM-156.xml::CM-156-1::kuali (generated) ran successfully in 205ms
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
[INFO] Doing rollback
INFO 9/21/11 9:29 PM:liquibase: Successfully acquired change log lock
INFO 9/21/11 9:29 PM:liquibase: Rolling Back Changeset:/Users/leo/.workspace/kfs/release-4-0-overlay/target/changelogs/update/CM-156.xml::CM-156-1::kuali (generated)::(Checksum: 3:85a5e658332342fba8b60df3a29fc393)
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
INFO 9/21/11 9:29 PM:liquibase: Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 28.920s
[INFO] Finished at: Wed Sep 21 21:29:49 MST 2011
[INFO] Final Memory: 11M/262M
[INFO] ------------------------------------------------------------------------
leo@behemoth~/.workspace/kfs/release-4-0-overlay
(21:29:49) [538]

Conclusion

You can see that it ran against two separate databases relatively quickly. It's a good way to test your changes and make sure they work everywhere you need them to.

lb-maven-plugin: Why Another Liquibase Maven Plugin?

Overview

I've started a project at github for an extension of the liquibase maven plugin. It's true that liquibase already has a maven plugin, but liquibase is so configuration intensive. I believe there needs to be a more standardized approach to developing with liquibase. Maven is a paragon for the methodology of "convention over configuration". I sincerely believe this methodology is a simpler way to go as far as configuration is concerned. With that in mind, my goal is to make development with liquibase more constrained and simpler for developers. This is especially the case in Kuali.

The Problem

You may recall from a previous blog post the structure and style of Liquibase implementation I recommend. To summarize, I recommend only publishing updates to your VCS. This makes it easier on developers and also eliminates confusion when making essentially the same change to multiple files. This approach does have its complications though. For example, it raises the question, "How do you create a database to start with?" Also, "What if an environment or a developer gets several revisions behind? How do we bring them back up to the current version?" The maven plugin basically answers those questions. Especially, for developers.

lb-maven-plugin

This plugin is an extension of the liquibase-maven-plugin, so it has all the same configuration and goals. In addition, it has 2 more goals: migrate and test.

migrate

You may recall that there used to be a migrate task in the liquibase-maven-plugin. I think they renamed it to be consistent with the CLI and Ant. I have brought it back to be more consistent with RoR migrations. The objective here is to not just update the database, but to bring the database up to the current version. Right now, liquibase update just runs whatever changelogs you want to update with. They might not be the ones that will get you to the right version. Liquibase doesn't know what changelogs to run to get you to the right version. This goal takes into consideration the structure outlined in a previous blog post, and retrieves from the SCM the relevant changelogs to update your database.

test

There's already an updateAndRollback goal. Unfortunately, this tests the update and rollback, but does another update. This is great if you want to test your script while updating your database. In development, this isn't as realistic though. Sometimes, you want to group your changes. Developers are likely to continually add to a change log, then commit it to the VCS. This is especially the case if the changes are related to the same Jira issue. If you update, you can't really keep updating. You could get around this by being really hacky with your changelogs, but it would just be better if the tool did what you expected: run the test, and then rollback to a state prior to do more testing. That's exactly what this goal does. It basically tests your changelogs out. You can define multiple databases to run it against. If you are concerned about changelog compatibility, you can run the changes against as many databases as you want (local or remote). Continue to follow this blog, and I'll post shortly an example on testing your changelogs with this tool.

Liquibase: Semi-Database Agnostic Changelogs

Overview

Changelogs in Liquibase are typically RDBMS specific. For a database change management system that advertises being database agnostic, it is pretty weird to still be writing changelogs that are database specific. For example, check this out:

Above is a changelog specific to MySQL. You can tell because of the VARCHAR type used. If it were Oracle, that would probably be a VARCHAR2

This makes it a little difficult to reuse changelogs across database platforms. End up locked into one database platform which is a bit troublesome for projects that use more than one (or support more than one like Kuali).

Semi-Database Agnostic Changelogs

Not all RDBMS-specific issues can be corrected. Liquibase suggests using contexts in these kinds of scenarios. However, it is possible to directly address the field type issue. Liquibase supports generic SQL Types defined in the java.sql.Types class. Here's an example:

You start seeing java.sql.Types.VARCHAR(30) where the 30 is the length of the field. This will work in Oracle as well. The java.sql.Types.VARCHAR refers to the field in the Java API. During processing, Liquibase will substitute the native Oracle type for the java.sql type.

Generating Semi-Database Agnostic Changelogs

You may be asking right away, "Why do you want to generate changelogs? Why not just have developers maintain them?" I've tried this method before, and I found it distracting and cumbersome for developers to duplicate work on changelogs. Whenever a database is modified, then the install changelog and the update changelogs need to be modified. The install changelog is to recreate the database, and the update change log is for updating existing databases. Indeed, this does eliminate the "Master database" antipattern, but it forces some extra effort and testing. I've found this to be unnecessary. I much rather prefer and recommend sticking with the "Master database" antipattern. Just apply update scripts to your "Master" and run generate changelogs against it. Since liquibase is database agnostic, we should get the same changelog from any database and be able to apply it to any other database; therefore, it doesn't really matter which database is used as the master.

The Problem

Of course, anyone can start doing their changelogs differently to support the new types, but what about when you generate your changelogs? Well, Liquibase will just use the standard RDBMS targeted changelogs. You could just do post-processing on your changelogs and cleanup after generating.

The Solution

I have put together some Liquibase extensions to get around this. They are in a Git Hub repository. Anyone can download or check them out. You simply put them into your classpath when you run Liquibase, and it will load the extensions automagically. Among the extensions are numerous fixes for generalizing changelogs. One very prominent one is the lack of sequence support in MySQL databases. Even though MySQL does not require sequences, Kuali does use sequences. This causes conflict. It is especially the case when generating changelogs.

For examples on how to use the extensions, you can look at my other github project lbcopy. it's a database copy tool based on Liquibase. It can export/import databases as well as directly migrate from one to another.

Screencast: Database Export with Liquibase

Screencast

This screencast is of me demoing a tool called lbcopy. It is a tool built with liquibase and a few liquibase extensions. The idea is a fast and simple database migration. One of the greatest advantages of this tool is its database agnostic personality. You can export from MySQL and import the schema and content directly into an Oracle database without any modification. The same goes for just about any database.

Another distinguishing concept between this tool and others is that data is exported in its native environment. Not SQL, but a database. When exporting data, an H2 or HSQLDB jar file read-only database is produced. The data can then be observed and queried using a normal database query tool like Aqua Data Studio. This gives users a much better visualization of the data vs. observing raw SQL or a CSV.

That's what I demo here. I demo how to export a database and gain access to the exported data.

Screencast: Database Migration with Liquibase

Screencast

This screencast is of me demoing a tool called lbcopy. It is a tool built with liquibase and a few liquibase extensions. The idea is a fast and simple database migration. One of the greatest advantages of this tool is its database agnostic personality. You can export from MySQL and import the schema and content directly into an Oracle database without any modification. The same goes for just about any database.

Another distinguishing concept between this tool and others is that data is exported in its native environment. Not SQL, but a database. When exporting data, an H2 or HSQLDB jar file read-only database is produced. The data can then be observed and queried using a normal database query tool like Aqua Data Studio. This gives users a much better visualization of the data vs. observing raw SQL or a CSV.

There are 3 primary uses for this tool: Export, Import, and Migrate. This demo is on Migrate. Migrate is direct copy of the database from one location to another without export or import. It is just one step.