Full list of tables when generate SQL schema code - Forum - OpenEdge Pro2 - Progress Community

Full list of tables when generate SQL schema code

 Forum

Full list of tables when generate SQL schema code

This question is answered

Hello,

I will use the right of the first message in this group ;-)

I am preparing for the test Pro2SQL (Pro2v4.6.4) environment  for one of my clients and I saved up several questions which I plan to ask here, one question - one post.

And it is the first question.

The client has application with more than 700 tables. When for the first time we created the replication map we generated the SQL schema for all 700 tables through Tools -> Generate Code -> SQL Schema (at the beginning the client wanted to replicate all tables)  and it is Ok. But then it was decided to replicate only 100 tables. I deleted from the replication schema something about 600 tables - how I did it and what problems I faced I will write separately ;-)

The problem is that after this I tried to create SQL Schema again (Tools -> Generate Code -> SQL Schema).

I expected that SQL Schema will be created based on already created replication map, but it generate for me SQL script for all 700 tables based on source db schema :-(
- it is not convenient for deploy in the production environment.

In this regard, I have a question to Pro2 developers: it is designed specifically or is it a bug?

Regards,
Valeriy

Verified Answer
  • Valeriy,

    The SQL generation is working as designed. Keep in mind that typically SQL generation is done prior to any table mapping , hence the generator always reads the entire contents of the source database, and in your case created the DDL to fully build the source schema on the target.  When you run fully synchronized schemas, Pro2 can you assist in keeping them synchronized, even when applying changes to the source db (SQL Diff Tool).

    However, for sure you are not required to run synchronized schemas, so doing a drop on the 600 tables is certainly at the discretion of the customer.  Be aware the SQL Diff will no longer be of value as it will always want to add back the schema for the missing 600 tables.

    You do bring up an interesting idea.  In your case the full schema generation was loaded, you then mapped tables and wanted to regenerate the SQL.  Should Pro2 only generate DDL for tables in which mapping exists?   I'll take that as an enhancement request and explore how it might work.  It could get tricky within the SQL diff in regards to detection of new tables to source, versus tables that are being ignored.  So for sure it requires some thought.

    Thank your for your question and hope this helps.

    Terry

    Progress Engineering Group

All Replies
  • Valeriy,

    The SQL generation is working as designed. Keep in mind that typically SQL generation is done prior to any table mapping , hence the generator always reads the entire contents of the source database, and in your case created the DDL to fully build the source schema on the target.  When you run fully synchronized schemas, Pro2 can you assist in keeping them synchronized, even when applying changes to the source db (SQL Diff Tool).

    However, for sure you are not required to run synchronized schemas, so doing a drop on the 600 tables is certainly at the discretion of the customer.  Be aware the SQL Diff will no longer be of value as it will always want to add back the schema for the missing 600 tables.

    You do bring up an interesting idea.  In your case the full schema generation was loaded, you then mapped tables and wanted to regenerate the SQL.  Should Pro2 only generate DDL for tables in which mapping exists?   I'll take that as an enhancement request and explore how it might work.  It could get tricky within the SQL diff in regards to detection of new tables to source, versus tables that are being ignored.  So for sure it requires some thought.

    Thank your for your question and hope this helps.

    Terry

    Progress Engineering Group

  • Hello Terry,

    Thank you for your answer!

    >>Should Pro2 only generate DDL for tables in which mapping exists?

    For example, to make question for user with a choice on what basis generate DLL.