Hi,

LLBLeGen Pro details:, 4.2 Final, Released on May 5th, 2015.

We are creating tables on posgreSQL and reflect all the changes to LLBLGen. An image(img.jpg) is attached to see how the things are organised.

We go through the process of:

PostgreSQL - Refresh relational model data from db, Under Catalog explorer, PostgreSQL - Reverse engineer Tables to entity definitons Project explorer, Entities - Auto map unmapped entities, Auto map unmapped fields Save project and generate source code.

Once we do that we want to generate update scripts for SQL server.

Project explorer, SQL server - generate database schema update script and that's where the fun starts. We are unable to get valid SQL Server update scripts. In most cases indexes, constraints are not generated meaning that script is partially generated and it is taking a lot of time to check/fix it.

Behaviour above is related to simple scenario when new tables are added. Yesterday I had a case when I updated table on postgreSQL, went to the process mentioned above and generated script had some old fields included(whic are not used anymore).

Regards

Could you please first upgrade to the latest build and see if the problem's still there?

Hi,

yes, I've just tried, and the problem remains. I also discovered new item. I'll create new thread for that.

Regards

Could you please attach a repro proj + db?

Hi,

we can't attach proj or db here, we can only post it in private forum post, do you want us to open another private thread?

Regards

If you perform a validate and adjust relational model data before generating DDL SQL, the pk/fk constraints should be there in SQL Server. You can check in the catalog explorer: if the pk/fk constraints aren't there when you generate DDL SQL, they won't be created during the process.

Fields which aren't used anymore is odd though. However in v4 it will auto-map unmapped fields also to postgresql if they're not mapped anymore. (even if you use it for database first development. In v5 we addressed this, but in 4 it's still all-or-nothing)

this can happen if you e.g. remove a table field in postgresql, then refresh the catalog, and the field is left in the entity, unmapped. If you then automap unmapped fields, it's seen as an unmapped field, so of course it gets a new field in postgresql again.

Please set the setting 'Remove unmapped elements after refresh' to true in preferences, if the project setting with the same name is set to 'default', or set it in the project settings (preferred).

Could you do that for me please and see if that makes things better ?

Hi,

thanks for the help so far.

"Remove unmapped elements after refresh" property is already set to true in preferences. I did a small test now. Added "CoreTest" table on postgreSQL db, (table has PK, FK, unique index and check constraint ).

Followed the process mentioned yesterday above.

Generated SQL update script does not contain unique index and constraint.

Also noticed that some tables in our Catalog explorer (for SQL server) does not have "Foreign keys constraints" node. Please note that current test did have that node and created "New" table and fireign keys script.

You have to do 'validate and adjust relational model data' prior to generating ddl sql, as I explained, otherwise the fk/pk/uc's aren't created in the metadata and thus your script won't have them. It's a 2-step process.

It's a bit cumbersome to do that, so in v5 we made it a single button push for all sync operations, you simply sync one side with the other and all operations take place, auto mapping, adjustment of relational model data etc. So no chance to forget the step to adjust the relational model data.

Hi,

tried with that, but unfortunately without any success. Regards

imarusic wrote:

Hi,

tried with that, but unfortunately without any success. Regards

what do you mean exactly? As what you describe is exactly what happens if 'validate and adjust relational model data' hasn't been run after automapping. there are 2 validate buttons, one with a db icon in it, that's the one you should use. It applies all PK/FK/UCs on the target DBs. If the model is valid of course. You did that and nothing changed, while there are PK's, relationships defined on the entities, mappings are there, but no pk/fk is created in the relational model data?

Hi,

here are the steps:

PostgreSQL - Refresh relational model data from db, Under Catalog explorer, PostgreSQL - Reverse engineer Tables to entity definitions Project explorer, Entities - Auto map unmapped entities, Auto map unmapped fields Save project and generate source code. Project tab - Validate and adjust Relational model data

Project explorer - Generate database schema update.

Tables, primary keys and foreign generated, but constraints and indexes were not.

Model is valid, but there are some warnings regarding Self service which we are not using.

Regarding your last question, image attached.

imarusic wrote:

Hi,

here are the steps:

PostgreSQL - Refresh relational model data from db, Under Catalog explorer, PostgreSQL - Reverse engineer Tables to entity definitions Project explorer, Entities - Auto map unmapped entities, Auto map unmapped fields Save project and generate source code. Project tab - Validate and adjust Relational model data

Project explorer - Generate database schema update.

Tables, primary keys and foreign generated, but constraints and indexes were not.

What do you mean with 'constraints' exactly? Indexes are not created as the designer doesn't support any indexes. It does forward map unique constraints if they're in the entity (or when they're necessary for 1:1 relationships). Are you referring to check constraints and the like? As these aren't part of the model so they're not forward mapped/created in the sql server part.

Ok, thanks for the clarification. I thought that unique indexes and constraints are also generated in SQL scripts.

Did a small test and "Validate and adjust relational model data" generated keys on the Catalog which also caused to generate valid update SQL script.

I'll get back to you if I encounter any issue related to this functionality.

Thanks for the help, Regards.