Make dynamic table creation code more stable

[Nicklas Nordborg]

If the timing is unfortunate between two plug-in jobs, the may both try to create the same dynamic database table. One of them will succeed, the second will get an exception similar to:

net.sf.basedb.core.BaseException: Table 'D13Pos' already exists
at net.sf.basedb.core.HibernateUtil.createVirtualTable(HibernateUtil.java:605)
at net.sf.basedb.core.VirtualDb.createTables(VirtualDb.java:290)
at 
net.sf.basedb.core.PositionBatcher.buildInsertSelectSql(PositionBatcher.java:303)
at net.sf.basedb.core.PositionBatcher.insert(PositionBatcher.java:246)
at 
net.sf.basedb.util.IntensityCalculatorUtil.createRootBioAssaySet(IntensityCalculatorUtil.java:204)
at 
net.sf.basedb.plugins.IntensityCalculatorPlugin.run(IntensityCalculatorPlugin.java:249)
at 
net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:89)
at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:421)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'D13Pos' 
already exists
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1350)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1266)
at 
com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
at net.sf.basedb.core.HibernateUtil.createVirtualTable(HibernateUtil.java:581)

The code does check if the table exists before trying to create it, but this is not an atomic operation. There are a couple of possible solutions:

• Modify the SQL that creates the table to something like: CREATE TABLE IF NOT EXISTS... or whatever the syntax is for the various databases. This has to go into the DbEngine interface unless we can get Hibernate to help us with it. We may need to have a fallback option if the database doesn't supports this kind of checks.

• Try to analyze the error message/exception class. SQL exceptions are tricky not standardized between various databases.

Luckily, all table creation goes through HibernateUtil.createVirtualTable() so there is only a single place to fix.

[Nicklas Nordborg]

(In [4099]) References #888: Make dynamic table creation code more stable

Now works for MySQL. Need to investigate if it is possible to solve for Postgres as well.

[Nicklas Nordborg]

(In [4100]) Fixes #888: Make dynamic table creation code more stable

Removed deboug output. Searching on the net reveals no better solution for Postgres than what is already there (checking and creating the table in two steps).