Microsoft SQL Server Producer

The Microsoft SQL Server Producer is a persistence producer which will translate your SoftFluent CodeModeler model into a Microsoft SQL Server database.

Designing your model, you can define business concepts (e.g. entities, properties, methods, views), relations from a concept to another as well as the logic in which they all interact with one another. From this platform independent model, a meta-model is built which will be translated by producers to generate platform specific code.

From this meta-model, the Microsoft SQL Server Producer generates a set of T-SQL scripts which create data objects; translating platform independent concepts (e.g. a Customer entity) into platform specific code (e.g. a Customer table). On the same principles:

• Properties become columns,

• Methods become stored procedures,

• Instances become lines of data,

• Entity keys become primary keys,

• Entity relation properties become foreign keys,

• Views in your model become actual SQL views,

• etc.

Note: Indices declared in the model are not generated as indices in a SQL Server database. Index creation is quite complex and specific to the database, so it’s a design choice that nothing automatic is generated in that area.

All those data objects are created by the generated scripts. By default, those generated scripts are executed against the development database automatically when building your model.

Furthermore, the Microsoft SQL Server Producer has a differential engine, meaning that between generations the database isn't dropped and created over but altered (columns and procedures added, types changed). This feature is a key feature, since it allows to generate continuously, driving developments from the model.

SQL Azure target

The SQL Server producer can translate your model into SQL Azure compliant T-SQL scripts to generate the equivalent SQL Azure database. Moreover, the generated scripts can be run automatically on the SQL Azure database.

Note: Unlike the SQL Server Producer, this producer doesn't create the SQL Azure database. On SQL Azure, billing is based, in part, on your database edition and size. Therefore, we're letting developers create their database as desired, and the tool focuses on generating the database schema (schemas, tables, views, stored procedures, constraints, data, etc.).

You can also work locally, by generating on a local SQL Server database. In this scenario, the SQL Server producer will generate 100% compliant code on both platforms. Furthermore, when generating on a SQL Server instance, the producer detects it and will use a diff engine which will update the database through generations rather than dropping and creating it over.

This diff engine is in fact a key feature since it allows developers to generate continuously, without ever losing data. Once the application is ready to go live, the developer can configure the producer to run the scripts on the online SQL Azure database.

How to: Execute custom T-SQL scripts with the Microsoft SQL Server producer

The Microsoft SQL Server Producer can execute hand-made scripts after or before running a standard generated script.

Generated scripts are executed in a specific order, and you can plug-in your own hand-made ones by using the script name prefixed with the desired adjective (before or after). Here's a table listing possibilities:

Step	Description	Custom T-SQL script / PreExecution Step	Generated T-SQL script / Execution Step	Custom T-SQL script / PostExecution Step
1	Create Runtime functions	before_[DefaultNamespace]_runtime.sql	[DefaultNamespace]_runtime.sql	after_[DefaultNamespace]_runtime.sql
2	Remove Constraints	before_[DefaultNamespace]_relations_drop.sql	[DefaultNamespace]_relations_drop.sql	after_[DefaultNamespace]_relations_drop.sql
3	Update Schemas	before_[DefaultNamespace]_schemas.sql	[DefaultNamespace]_schemas.sql	after_[DefaultNamespace]_schemas.sql
4	Update Tables	before_[DefaultNamespace]_table_diffs.sql	[DefaultNamespace]_table_diffs.sql	after_[DefaultNamespace]_table_diffs.sql
5	Update Views	before_[DefaultNamespace]_views.sql	[DefaultNamespace]_views.sql	after_[DefaultNamespace]_views.sql
6	Add Constraints	before_[DefaultNamespace]_relations_add.sql	[DefaultNamespace]_relations_add.sql	after_[DefaultNamespace]_relations_add.sql
7	Update Stored procedures	before_[DefaultNamespace]_procedures.sql	[DefaultNamespace]_procedures.sql	after_[DefaultNamespace]_procedures.sql
8	Update Instances	before_[DefaultNamespace]_instances.sql	[DefaultNamespace]_instances.sql	after_[DefaultNamespace]_instances.sql