Include "if not exists" in table creation scripts [BATCH-1729]

[spring-projects-issues]

Matthew T. Adams opened BATCH-1729 and commented

Adding conditional table creation to the creation scripts would be nice: CREATE TABLE IF NOT EXISTS ...

That way, the scripts could be run without having to have them bomb out because a table already exists. Using <jdbc:initialize-database ignore-failures="ALL" ... /> doesn't really help when you want to ensure that the schema either already exists or needs to be created.

---

No further details from BATCH-1729

[spring-projects-issues]

Dave Syer commented

"IF NOT EXISTS" is not supported by many (most?) of the platforms that people use. Which databases do you use? If you know it works for a particular platform or platforms we can maybe change it there.

[spring-projects-issues]

Matthew T. Adams commented

MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

MS SQL Server: (there may also be a system stored procedure that'll do this -- seems smarter to me to do it that way)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[tablename] ( columns specification );

I'm sure there are ways to do this in all of the other major databases.

[spring-projects-issues]

Dave Syer commented

OK, that's a start. I don't understand the syntax for MS SQL, so someone is going to have to submit a patch or pull request and verify that it works. If we get a few more contributions we can start to apply them. All contributions gratefully accepted. I'll post on the forum and see if anyone responds.