In our company we are using LLBLGEN several years against MSSQL. In new version is avalability to specify model and then generate Sql scripts for database. I am very interested in this scenario, because, we had to use another CASE tools for generating Database. So this is the best way to use just one tool. My idea is to use one tool for modelling, then generating database structure and finally generating code scripts for VisualStudio without any interventions directly in MSSQL Management Studio.

I am trying trial version. I designed model with 3 entities with primary keys and 2 normal relations (1:N) between them. Then I mapped them and finally I generated create sql script for database. I have several question about this functionality:

1. I can set primary key, but is there possibility to set it as identity?
2. Is there possibility to order entity fields in final db table? I think it is pretty usefull to have primary key as first column.
3. No primary keys where scripted in create SQL script, may be I missed something?
4. Is there possibility for scripting foreign keys for every relation? With specified name? No one was scripted...
5. I didnt found any possibility to create indexes, stored procedures, triggers... ?
6. Can I set default Collation for varchar and nvarchar columns? Or just let it without collation to use Server/Database default one?

Thanks a lot Jiri Karlovsky as Rebex

Rebex wrote:

In our company we are using LLBLGEN several years against MSSQL. In new version is avalability to specify model and then generate Sql scripts for database. I am very interested in this scenario, because, we had to use another CASE tools for generating Database. So this is the best way to use just one tool. My idea is to use one tool for modelling, then generating database structure and finally generating code scripts for VisualStudio without any interventions directly in MSSQL Management Studio.

I am trying trial version. I designed model with 3 entities with primary keys and 2 normal relations (1:N) between them. Then I mapped them and finally I generated create sql script for database. I have several question about this functionality:

1. I can set primary key, but is there possibility to set it as identity?

When you assign a sequence to the field in its mapping, and the sequence is a system sequence (which is always the case in SQLServer) it will become an identity field. Using auto-mapping, sequences are automatically assigned to int-based pk fields if the project property AutoAssignSequencesToIntegerPks is checked (checked is the default).

Be sure to use validation + adjust relational model data to make sure the relational model data is up to date.

1. Is there possibility to order entity fields in final db table? I think it is pretty usefull to have primary key as first column.

No, there's no possibility to specify the ordinal for the field. the reason is maintenance: changing the ordinal of a field requires migrating the data to another table, drop the table and recreate it, migrate the data back. We decided not to do that.

1. No primary keys where scripted in create SQL script, may be I missed something?

You should use the validate + relational model data adjustment feature. See Project Validation in the documentation

An action on the project isn't always 1 action on the relational model data so this stage is ran separately, when you are ready for it (as it might require several edit action on the relational model to get an FK constraint defined for example)

1. Is there possibility for scripting foreign keys for every relation? With specified name? No one was scripted...

See point 3

1. I didnt found any possibility to create indexes, stored procedures, triggers... ?

Those aren't supported in the designer as they're not part of the model: - indexes are performance tweak tools, and should be applied where needed - procs / triggers are written by hand in SQL and are better written in a tool directly on the RDBMS.

1. Can I set default Collation for varchar and nvarchar columns? Or just let it without collation to use Server/Database default one?

A default collation is generated into the script. The script is to be seen as the starting point of your DDL SQL actions. So if you want a different collation, global search/replace the collation in the script to something else.

Be sure to also check the various project properties which control what the designer chooses for field types by default, e.g. PreferNaturalCharacterTypes (under relational model element construction).

1. Is there possibility to order entity fields in final db table? I think it is pretty usefull to have primary key as first column.

No, there's no possibility to specify the ordinal for the field. the reason is maintenance: changing the ordinal of a field requires migrating the data to another table, drop the table and recreate it, migrate the data back. We decided not to do that.

In my opinion, scenario which can be used is to use ordinal numbering in "Create" scipts, but in "Update" scripts use adding only at the end of the table. So may be next version ;-) . I founded, that ordering columns in create table scripts depend on the sequence of entering fields in designer. So it is OK for me at this moment.

Other questions are resolved or I understand, that main usage is to model Entities and not to model RDBMS. But one more question.

I can save Model view, but no positioning is saved. When I open saved model, positioning is probably computed again. Can I set somewhere to save positioning too?

Thanks Jiri Karlovsky as Rebex

Rebex wrote:

1. Is there possibility to order entity fields in final db table? I think it is pretty usefull to have primary key as first column.

No, there's no possibility to specify the ordinal for the field. the reason is maintenance: changing the ordinal of a field requires migrating the data to another table, drop the table and recreate it, migrate the data back. We decided not to do that.

In my opinion, scenario which can be used is to use ordinal numbering in "Create" scipts, but in "Update" scripts use adding only at the end of the table. So may be next version ;-) . I founded, that ordering columns in create table scripts depend on the sequence of entering fields in designer. So it is OK for me at this moment.

The order is indeed on a 'first come first serve' basis, we might extend this in the future. Other people have also expressed ordering of fields in the entity itself, which then would reflect the order of the mapped table (if there's 1 table of course)

Other questions are resolved or I understand, that main usage is to model Entities and not to model RDBMS. But one more question.

I can save Model view, but no positioning is saved. When I open saved model, positioning is probably computed again. Can I set somewhere to save positioning too?

You have to close the view's editor as at that point the locations of the entities are preserved and if you then save the project, a .layout file is created with the layout of entities on all model views which have coordinates saved. There's otherwise no real point in time when the coordinates can be stored, as the viewer is a separate piece of code (a renderer of a separate library).

Layouts also store the collapsed/expanded state of an entity in a view btw.