Adding Additional SQL Files

In many cases you may wish to have the sql:insert task perform additional SQL operations (e.g. add views, stored procedures, triggers, sample data, etc.). Rather than have to run additional SQL statements yourself every time you re-build your object model, you can have the Propel generator do this for you.

1. Create the SQL DDL files

Create any additional SQL files that you want executed against the database (after the base schema.sql file is applied).

For example, if we wanted to add a default value to a column that was unsupported in the schema (e.g. where value is a SQL function):

-- (for postgres)
ALTER TABLE my_table ALTER COLUMN my_column SET DEFAULT CURRENT_TIMESTAMP;

Now we save that as ‘my_column-default.sql’ in the same directory as the generated ‘schema.sql’ file (usually in projectdir/generated-sql).

2. Tell Propel Generator about the new file

In that same directory (where your ‘schema.sql’ is located), there is a ‘sqldb.map’ file which contains a mapping of SQL DDL files to the database that they should be executed against. After running the propel generator, you will probably have a single entry in that file that looks like:

schema.sql=your-db-name

We want to simply add the new file we created to this file (future builds will preserve anything you add to this file). When we’re done, the file will look like this:

schema.sql=your-db-name
my_column-default.sql=your-db-name

Now when you execute the sql:insert Propel generator target, the ‘my_column-default.sql’ file will be executed against the your-db-name database.