http://www.symfony-project.org/forum/index.php?t=msg&goto=5521

I am not sure how much of this is trouble with symfony and how much is propel, but below is the list of steps I had to take to port a symfony schema to MS SQLServer. I thought I would post it in case there are bugs that can be fixed and also to help anyone else who may need to do the same task. I would also be most happy to learn if the source of my problems was in front of the screen and if so what I did wrong.

I started with a schema.xml file that works wonderfully in MySQL. Instead of editing the databases.yml I had to edit the propel.ini file to use propel.database = mssql. I discovered this from some related tickets although supposedly this bug was already fixed (I am using 0.7.1218). Once I made this change, I was able to get the symfony propel-build-sql command to generate SQLServer syntax instead of MySQL syntax.

Unfortunately, I also had to make a couple of other changes. I now have 2 separate versions of my schema.xml file. The MySQL version uses

onDelete="RESTRICT" onUpdate="RESTRICT"

in the foreign key attributes and the SQLServer one replaces these with

onDelete="NO ACTION" onUpdate="NO ACTION"

In the resulting schema.sql file I then had to replace all occurrences of

CONSTRAINT [<table_name>]_PK PRIMARY KEY ([id])

with

CONSTRAINT [<table_name>_PK] PRIMARY KEY ([id])

I assume this is a bug in propel that puts the ] in the wrong place.

I also had to move all of the foreign key statements to the bottom of the file since SQLServer doesn't appear to have an equivalent to the MySQL set foreign_key_checks = 0 statement.

Lastly, I had to edit any foreign keys referencing the user table to use [user] instead. Propel does a pretty good job of putting the [] around all table and column names but it appears they missed this spot.

The good news is that once I made all of these changes the script runs just fine.

I am not looking forward to repeating these steps every time my model changes.

documented on: 26 April 2006, jrogoff