Sunday, October 09, 2016

Spring Boot Configuration Properties Localization

Spring Boot allows to externalize application configuration by using properties files or YAML files. Spring Profiles provide a way to segregate parts of your application configuration and make it available in certain environments (development, qa, production, etc.).

When building applications for multiple countries, you might have some configuration properties that might differ for a specific locale. One way to localize these configuration properties, would be creating a new profile per locale where you can override these values. This approach only works if your application is running for that specific locale, and not supporting multiple locales at the same time. This approach would also increase the number of profiles to be managed (number of application properties files).

Here, I am going to show another approach where you can define the localizable properties inside your existing configuration properties class. For example, if have the following set of properties for a single country in your application.yml file:

region:
  hostSuffix: .com
  currencyCode: USD
  patternShortDate: MM-dd-YYYY

Then, you could localize these properties by defining them for specific locales, for example en_CA and en_GB:

region:
  hostSuffix: .com
  currencyCode: USD
  patternShortDate: MM-dd-YYYY
  locales:
    en_CA:
      hostSuffix: .ca
      currencyCode: CAD
      patternShortDate: dd-MM-YYYY
    en_GB:
      hostSuffix: .co.uk
      currencyCode: GBD
      patternShortDate: dd-MM-YYYY

The idea is to be able to get the desired property by locale:

// Getting a property for the default locale.
regionProperties.getHostSuffix(); // returns ".com"

// Getting a property for supported locales.
regionProperties.fromLocale(Locale.US).getHostSuffix(); // returns ".com"
regionProperties.fromLocale(Locale.CANADA).getHostSuffix(); // returns ".ca"
regionProperties.fromLocale(Locale.UK).getHostSuffix(); // returns ".co.uk"

// Getting a property for unsupported locales (returns the default)
regionProperties.fromLocale(Locale.ITALY).getHostSuffix(); // returns ".com" (default)


Mapping locale String to Locale object

The first thing is to enable Spring to read the locale string, e.g. "en_CA", and convert it to a java.util.Locale object while loading the application configuration properties. For that, we will need to define a Converter class from String to Locale by implementing the Converter interface and adding the @ConfigurationPropertiesBinding annotation to tell Spring to use it when biding the properties.


@Component
@ConfigurationPropertiesBinding
public class LocaleConverter implements Converter<String, Locale> {

    private static final Map<String, Locale> LOCALE_MAP = new HashMap<>(Locale.getAvailableLocales().length);

    static {
        for (Locale locale : Locale.getAvailableLocales()) {
            LOCALE_MAP.put(locale.toString(), locale);
        }
    }

    @Override
    public Locale convert(String s) {
        return LOCALE_MAP.get(s);
    }
}

Loading the properties in your Configuration Proper

Once added this converter, all you have to do in your configuration properties class is to add the following property with its public getter/setter and Spring loads the application.yml (or application.properties) files.

    protected Map<String, Locale> locales = new HashMap<>();

Lets define a generic abstract class with this property and with the method to find the setting by locale (fromLocale):

public abstract class LocalizableProperties<T extends LocalizableProperties> {
    protected Locale locale;
    protected Map<String, Locale> locales = new HashMap<>();

    @PostConstruct
    private void postConstruct() {
        // Set the locale of each entry of the map.
        for (Map.Entry<String, Locale> entry : locales.entrySet()) {
            entry.getValue().setLocale(entry.getKey());
        }
    }

    public T fromLocale(Locale locale) {
        return locales.containsKey(locale) ? locales.get(locale) : (T) this;
    }

    public Locale getLocale() {
        return locale;
    }

    public void setLocale(Locale locale) {
        this.locale = locale;
    }

    public Map<String, Locale&gt getLocales() {
        return locales;
    }

    public void setLocales(Map<String, Locale&gt locales) {
        this.locales = locales;
    }
}

Creating Localizable Configuration Properties

Now, lets define our configuration properties class, RegionProperties, that simply extends our abstract class and define some application properties to be localized:

@Component
@ConfigurationProperties(prefix = "region")
public class RegionProperties extends LocalizableProperties<RegionProperties> {
    private String hostSuffix;
    private String currencyCode;
    private String patternShortDate;

    public String getHostSuffix() {
        return hostSuffix;
    }

    public void setHostSuffix(String hostSuffix) {
        this.hostSuffix = hostSuffix;
    }

    public String getCurrencyCode() {
        return currencyCode;
    }

    public void setCurrencyCode(String currencyCode) {
        this.currencyCode = currencyCode;
    }

    public String getPatternShortDate() {
        return patternShortDate;
    }

    public void setPatternShortDate(String patternShortDate) {
        this.patternShortDate = patternShortDate;
    }
}

That is, any configuration property class that need to be localized can be declared to extend the LocaleProperties class so that it can be localizable.

You can find a running code in my github repo:
https://github.com/lerocha/spring-boot-localizable-properties

Sunday, August 23, 2015

Comparing MySQL databases with mysqldbcompare

If  you need to check the differences between two MySQL databases, you can use the mysqldbcompare utility. This utility is part of MySQL utilities and it is very useful when you want to check if databases from different environments (dev, testing, stage, production) are consistent. It compares the objects and data from two databases to find differences between them. The databases can be located on the same host or different hosts. This utility identifies objects having different definitions in the two databases and presents them in a diff-style format.

To use mysqldbcompare, you will need to install MySQL Utilities and MySQL Connector/Python. You can find them here:
I use mysqldbcompare to compare the database objects to identify new tables/columns or anything that has been created or dropped. You can also use it to compare data itself, which would be useful to compare a replica or backup database with its source.

The usage is very simple. I created two identical databases in my localhost using the Chinook Database named Chinook and Chinook2.

The mysqldbcompare syntax is very simple. You use --server1 and server2 to specify the hosts of the databases and then the database names between colon, for example Chinook:Chinook2. You also can use --run-all-tests to avoid stopping at the first object different, and continue to process for all db objects.

mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 Chinook:Chinook2 --run-all-tests

The output is:

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases Chinook on server1 and Chinook2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`
+++ `Chinook2`
@@ -1 +1 @@
-CREATE DATABASE `Chinook` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `Chinook2` /*!40100 DEFAULT CHARACTER SET latin1 */

#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Album                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Artist                                  pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Customer                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Employee                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Genre                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Invoice                                 pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     InvoiceLine                             pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     MediaType                               pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Playlist                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     PlaylistTrack                           pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Track                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass   

# Databases are consistent.
#
# ...done

Note that object definitions, row count and row content match. It shows that table checksum are different, but my guess is that they are not the exactly replica since I created Chinook2 by running the same sql script again.

You can use the option --skip-data-check if you only want to compare objects definition and not data. After I made some changes in Chinook2 (drop and create table and columns), I run mysqldbcompare with --skip-data-check:

mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 Chinook:Chinook2 --run-all-tests --skip-data-check

The output is:


# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases Chinook on server1 and Chinook2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`
+++ `Chinook2`
@@ -1 +1 @@
-CREATE DATABASE `Chinook` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `Chinook2` /*!40100 DEFAULT CHARACTER SET latin1 */

# WARNING: Objects in server1.Chinook but not in server1.Chinook2:
#        TABLE: PlaylistTrack
#
# WARNING: Objects in server1.Chinook2 but not in server1.Chinook:
#        TABLE: Address
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Album                                   pass    pass    SKIP    
# TABLE     Artist                                  pass    pass    SKIP    
# TABLE     Customer                                FAIL    pass    SKIP    
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`.`Customer`
+++ `Chinook2`.`Customer`
@@ -3,16 +3,14 @@
   `FirstName` varchar(40) CHARACTER SET utf8 NOT NULL,
   `LastName` varchar(20) CHARACTER SET utf8 NOT NULL,
   `Company` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
-  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
-  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
   `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Email` varchar(60) CHARACTER SET utf8 NOT NULL,
   `SupportRepId` int(11) DEFAULT NULL,
+  `AddressId` int(11) DEFAULT NULL,
   PRIMARY KEY (`CustomerId`),
   KEY `IFK_CustomerSupportRepId` (`SupportRepId`),
+  KEY `FK_CustomerAddressId` (`AddressId`),
+  CONSTRAINT `FK_CustomerAddressId` FOREIGN KEY (`AddressId`) REFERENCES `Address` (`AddressId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_CustomerSupportRepId` FOREIGN KEY (`SupportRepId`) REFERENCES `Employee` (`EmployeeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1

# TABLE     Employee                                FAIL    pass    SKIP    
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`.`Employee`
+++ `Chinook2`.`Employee`
@@ -6,15 +6,13 @@
   `ReportsTo` int(11) DEFAULT NULL,
   `BirthDate` datetime DEFAULT NULL,
   `HireDate` datetime DEFAULT NULL,
-  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
-  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
   `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Email` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
+  `AddressId` int(11) DEFAULT NULL,
   PRIMARY KEY (`EmployeeId`),
   KEY `IFK_EmployeeReportsTo` (`ReportsTo`),
+  KEY `FK_EmployeeAddressId` (`AddressId`),
+  CONSTRAINT `FK_EmployeeAddressId` FOREIGN KEY (`AddressId`) REFERENCES `Address` (`AddressId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_EmployeeReportsTo` FOREIGN KEY (`ReportsTo`) REFERENCES `Employee` (`EmployeeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

# TABLE     Genre                                   pass    pass    SKIP    
# TABLE     Invoice                                 pass    pass    SKIP    
# TABLE     InvoiceLine                             pass    pass    SKIP    
# TABLE     MediaType                               pass    pass    SKIP    
# TABLE     Playlist                                pass    pass    SKIP    
# TABLE     Track                                   pass    pass    SKIP   

# Database consistency check failed.
#
# ...done


As you can see in the diff output above, I dropped the PlaylistTrack table and created the Address table (see # WARNING). I also dropped the address related columns in Employee and Customer tables (see lines starting with -) and added the addressId foreign key (see lines starting with +).

For more information, see:
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html#option_mysqldbcompare_run-all-tests

Sunday, June 02, 2013

Converting a local Subversion repository to Git

There many articles there about converting a subversion repository to git, but I decided to write my article that describes the conversion of a simple subversion repository (trunk only) stored in the local file system. I have a couple of these repositories that I needed to convert to git and then push them to bitbucket.org.

Note that you will still need svn installed in addition to git. If you are using Mac OSX, svn used to be installed by default but that changed in Mountain Lion (10.8). Since I have XCode already installed, I just went to Xcode > Preferences > Downloads > Command Line Tools > Install. See details here.

Create the Authors Mapping File

The first step is to create a mapping file for the subversion users to the git users. With the help from here, I run the command below to create the initial mapping file. In my case, the local subversion repository is located at //Users/lerocha/svn-repo:

svn log file:///Users/lerocha/svn-repo | sed -ne 's/^r[^|]*| \([^ ]*\) |.*$/\1 = \1 <\1@example.com>/p' | sort -u > ~/authors.txt

Open the generated file (~/authors.txt) in a text editor and change it from this:

lerocha = lerocha <lerocha@example.com>

Into this:

lerocha = Luis Rocha <luis@luisrocha.net>

Convert to a local Git repository

With the help from this stackoverflow answer, run the following commands to initialize the local git repository and import the revisions from subversion:

mkdir repo
cd repo
git svn init file:///Users/lerocha/svn-repo --no-metadata
git config svn.authorsfile ~/authors.txt
git svn fetch

Your local git repository should contain all revisions from your local subversion.

Push to a remote repository

Since we want to push it to bitbucket.org (private repositories are free!), you first create an empty repository in bitbucket.org and then run the following commands below to push to it:

git remote add origin ssh://git@bitbucket.org/youruser/yourrepo.git
git push -u origin --all   # to push up the repo for the first time

Sunday, December 09, 2012

Chinook Database is now available on NuGet

Chinook sample database is now available on NuGet. There are 3 packages available: 
  • Chinook Database SQL Scripts: contains the SQL scripts for all supported databases (SQL Server, SQL Server Compact Edition, SQLite, Oracle, MySQL, PostgreSQL, DB2 and EffiProz. In addition, it also contains batch files to run these scripts with the respective command prompt tools.
  • Chinook Database for SQLite: contains a SQLite file with the Chinook database.
  • Chinook Database for SQL Server Compact 4.0: contains a SQL Compact 4.0 file with the Chinook database.

You can install the Chinook database within Visual Studio using the following instructions and searching for 'Chinook':


For now on, new releases will be available on Codeplex download page and also on Nuget.

Enjoy!

Sunday, December 02, 2012

Chinook Sample Database 1.4 Released

The Chinook Database is a sample database available for multiple database systems. It can be created by running a single SQL script. Chinook database is an alternative to the Northwind database, being ideal for demos and testing ORM tools targeting single and multiple database servers.

The Chinook Database represents a media store, including tables for artists, albums, media tracks, invoices and customers. The media information was generated from my iTunes library file, but anyone can download its source code and use their own iTunes library information. The sales information is auto-generated for a 4 years period. You can see the Chinook data model here.

This new version includes support for DB2 and PostgreSQL. Thanks to @brice_lambson for implementing the PostgreSQL support! Chinook database is available for:

  • DB2 (new)
  • EffiProz
  • MySQL
  • Oracle
  • PostgreSQL (new)
  • SQL Server
  • SQL Server Compact
  • SQLite

You can download the latest release from here. There are available SQL scripts for each database system and also embedded database files for SQL Server Compact and SQLite.

Monday, March 05, 2012

Unit Testing an Entity Framework Data Access Layer

One approach to write unit tests for a data access layer implemented using Entity Framework that targets SQL Server is to use a local test database SQL Server Compact Edition. The test database will have the same schema as the real application database, and it will contain the minimun necessary data for your test cases. 

The only thing to keep in mind is that you will need to restore the previous state of the test database after each unit test run. There are multiple approaches such as to recreate the database before each test or simply restore a backup file. One approach that I like is to run the test under a TransactionScope.  In this approach, you create a TransactionScope object before the test runs and call TransactionScope.Dispose after the test is completed. Since TransactionScope.Complete is not called, the Dispose method will rollback the changes. That is, you SQL Compact database will be back to the state before running the test.

If you are using xUnit, then your unit test class will need to implement the IDisposable interface, create the TransactionScope in the constructor and dispose it in the Dispose method as shown below:

public class MyDataAccessUnitTests : IDisposable
{
  private TransactionScope _transactionScope;

  public MyDataAccessUnitTests()
  {
    _transactionScope = new TransactionScope();
  }

  [Fact]
  void Test1()
  {
    // TODO: implement it.
  }

  [Fact]
  void Test2()
  {
    // TODO: implement it.
  }

  public void Dispose()
  {
    _transactionScope.Dispose();
  }
}


If you are using NUnit or MSTest, then you can create and dispose the TransactionScope object in the following methods:

xUnit NUnit MSTest
new TransactionScope() Constructor [SetUp] [TestInitialize]
TransactionScope.Dispose IDisposable.Dispose [TearDown] [TestCleanup]

I hope this helps!

Tuesday, December 06, 2011

Data Access Performance Comparison in .NET

When writing data access code you are probably using some sort of ORM or data mapper to make your life easier while retrieving and persisting data. We all know that this does not come for free, ORMs allow you to move faster, but there is always a performance hit. I recently started reading about Dapper .NET, an open-source, lightweight, single-file object mapper that was developed for the stackoverflow.com site to execute high performance queries. After seeing its amazing performance results on their project page, I decided to write my owns performance tests to compare it to other ORMs (Entity Framework, NHibernate), and also to plain ADO.NET code.

For these tests, I am using the Chinook Database for SQL Server. I defined a simple repository interface and implemented it using different data access approaches. The interface implemented is:

public interface IRepository
{
 Artist GetArtistById(int id);
 IEnumerable<Song> GetSongsByArtist(string name);
}

The first method executes a simple query in a single table by the primary key. The second method executes a query in multiple tables using joins.

There are multiple implementations of this repository interface including plain ADO.NET code with DataReaders, Entity Framework, NHibernate and Dapper .NET. For the Entity Framework, there are four implementations: Linq To Entities, compiled Linq To Entities, native SQL query and stored procedures. For NHibernate, there are implementations with HQL query, native SQL query, and stored procedure.

All the code is available at https://github.com/lerocha/DotNetDataAccessPerformance.

Single Table Query by Primary Key

The first set of tests is to execute a simple query in a single table to get one record by the primary key. The SQL query is:

SELECT ArtistId, Name FROM Artist WHERE Artist.ArtistId=@id

The following table contains the results per multiple runs:

1 run10 runs100 runs500 runs1000 runs
DataReaderNativeQuery0.02.024.9134.1264.4
DataReaderStoredProcedure0.02.226.3133.8266.4
DapperNativeQuery0.12.528.0141.5285.6
DapperStoredProcedure0.02.428.0137.5276.1
EntityFrameworkCompiledLinqQuery2.222.5207.21041.02099.8
EntityFrameworkLinqToEntities5.525.2267.71263.62522.5
EntityFrameworkNativeQuery0.78.285.2426.0859.5
EntityFrameworkStoredProcedure1.45.258.3275.8552.8
NHibernateHqlQuery0.84.448.1253.6509.5
NHibernateHqlQueryStrongType0.14.147.2251.4508.7
NHibernateNativeQuery1.14.446.1256.9490.1
NHibernateStoredProcedure0.05.257.2294.0589.8

The 1-run test is executed twice, the first execution result is not shown since I am only considering hot start. The following chart shows the results of 1,000 runs. As you can see, using Entity Framework with Linq To Entities (non-compiled) has the worst performance result (2,552.5 ms). It gets a little bit better if you use EF compiled Linq query (2,099.8), then improvements starts when using EF with native query (859.5 ms). EF using stored procedure and all NHibernate approaches are way better, all around 500 ms. And finally, the performance of Dapper is very close to the plain ADO.NET code, providing an level of abstraction with minimal performance impact.


Multiple Table Query with Joins

The second test executes joins in multiple tables and filters by a non-key column. The SQL query is:

 SELECT Album.Title as AlbumName, 
 Track.Name as SongName, Artist.Name as ArtistName
 FROM Artist
 INNER JOIN Album ON Album.ArtistId = Artist.ArtistId
 INNER JOIN Track ON Track.AlbumId = Album.AlbumId
 WHERE Artist.Name=@name

The following table contains the results per multiple runs:

1 run10 runs100 runs500 runs1000 runs
DataReaderNativeQuery0.05.963.0321.8644.2
DataReaderStoredProcedure0.06.265.0332.3658.6
DapperNativeQuery0.46.468.0346.1685.5
DapperStoredProcedure0.16.767.2342.3679.7
EntityFrameworkCompiledLinqQuery1.09.9103.3525.61071.0
EntityFrameworkLinqToEntities5.658.2539.52699.95398.3
EntityFrameworkNativeQuery1.113.1135.0658.81328.3
EntityFrameworkStoredProcedure1.19.799.0489.1983.7
NHibernateHqlQuery3.435.6359.51806.23647.1
NHibernateHqlQueryStrongType6.868.3681.53430.06841.4
NHibernateNativeQuery1.110.6103.2517.7998.7
NHibernateStoredProcedure0.810.1102.0516.21033.8

The following chart shows the results of 1,000 runs. As you can see, the slowest implementation is NHibernate with strong type query, e.g. using NHibernate.IQuery.List(), it took 6,841 ms. EF with LinqToEntities (non-compiled) is a little bit faster (5,398 ms). Using NHibernate without strong type query, e.g. using NHibernate.IQuery.List() is significant faster (3,647 ms) than its strong type equivalent (6,841 ms). Here, we can see the performance improvement when using EF compiled Linq queries (1,071 ms) in comparison with EF non compiled Linq To Entities (5,398 ms). The EF compiled Linq query implementation showed performance equivalent to EF with stored procedures, and NHibernate with native query and stored procedure. And finally, the performance of Dapper (679-685 ms) is again very close to the plain ADO.NET code (644-658 ms), with minimal performance impact.


The performance improvements using Dapper in comparison with Entity Framework and NHibernate is very significant. For database reads that need a high performance execution, you might consider using Dapper to get the best performance with some good abstraction from plain ADO.NET code.

The source code of these tests is available at github and I will continue to work on it to incorporate any feedback and also add additional tests for adding, deleting and updating records. Stay tunned!

Spring Boot Configuration Properties Localization

Spring Boot allows to externalize application configuration by using properties files or YAML files. Spring Profiles  provide a way to segr...