ModeShape/modeshape — pull request #751 by rhauch (2013-03-19 21:49:23+00:00)
Title: MODE-1861 Database binary store is more adaptable to different DBMSes

Changed how the DatabaseBinaryStore configures and uses the database, so that the various SQL statements (both DDL and DML) are read from property files rather than be dynamically created. This means that it is far easier to see and understand exactly the kinds of statements that are issued for a given DBMS. It also allows the database table and statements to be altered slightly for specific DBMSes.

Several DBMS-specific property files (containing the SQL statements) were added to accommodate the variations in the previously-generated statements. There is a fair amount of duplication, but each file is self-contained, is very readable, and can be customized as needed (including by the user, since additional property files can be created to override those ModeShape provides out-of-the-box). IMO, these benefits outweigh the negative aspects of the duplication.

Since several DBMSes (including MySQL, PostgreSQL, Sybase and SQLServer) consider 'usage' to be a reserved keyword, the 'usage' column was renamed to 'usage_flag' in the create-table and other statements for all of the DBMSes. (This shouldn't be a problem, since the use of reserved words should have prevented creation of the table in the first place). The statements for the other DBMSes still use 'usage', but these DBMSes don't consider 'usage' to be a reserved keyword.

This change also reduced the amount of "general-purpose" JDBC-oriented code, such as eliminating the SQLBuilder code.

Finally, the Database class now creates PreparedStatement objects during initialization, and these are reused throughout the lifetime of the DatabaseBinaryStore. This will increase performance compared with creating a PreparedStatement every time one is needed.