Format Generated DDL

[former_member462146]

In PowerDesigner 16.5, how do I format my generated SQL from a table so I can get from this:

with

(

     appendonly = true,

     compresslevel = 1,

     orientation = column,
     compresstype = quicklz,

     blocksize = 65536

)

to this:

with ( appendonly=true, compresslevel=1, orientation=column, compresstype=quicklz, blocksize=65536 )

I assume I need to edit the DBMS somewhere, but I don't know where.

Marty

[marc_ledier]

Hi Marty,

Unfortunately, the OPTIONS item cannot be formatted.

You may try changing it by creating a computed extended attribute that will mimic the object physical options.

For instance:

* the %Get% would retrieve the obj.PhysicalOptions string and apply any formatting (in VBS) that you want

* the %Set% would simply set the physical option to the value variable ("obj.PhysicalOptions = value")

Marc

[pascalmenoud]

I guess that would be Script > Objects > Table > Options.

[former_member462146]

I started there and it appears that modifying Script > Objects > Table > Options changes the way the Items tab looks (I was able to add and remove entries in the options tree, and change their order.  However, I'm not sure how to change the generated SQL options. Is there something I'm missing here?

Also, are you aware of any documentation on the syntax for the Options screen?  I've been able to infer many things, but having documentation would be very helpful.

[c_baker]

Have a look at the documentation under 'Customizing and Extending PowerDesigner > DBMS Definition Files > Physical Options (DBMS)'.

It has been a while since I looked at this, but how you define the options (e.g. simple, complex, etc) has a bearing on whether they are exclusive, inclusive, etc.  You may have to play around for them to work correctly.

On another note, why are you needing to format the DDL?  Is this to allow comparison of the line in a CVS?  In that case, I would suggest using the PD repository instead.of a text-based version control system. The repository properly compares the models at the object level and does not worry about such issues as generation options (e.g. creating constraints inside vs. outside a table) that will send a text-based CVS into fits from comparing DDL.

Chris.

[former_member462146]

My intent with the formatting was to reduce the number of lines of DDL being generated.  The DDL generated by 59 tables is 19,187 lines.  All I'm trying to do is collapse the number of lines.  For example, combining the "with" keyword from 8 lines  (1 for the "with", 2 for the "(" and ")" and 5 for the values)  down to a single line.  This would reduce the number of lines for the "with" from 472 to 59.  This would also be done for the distributed by, and the partitions.

We are using the repository, and if you have any documentation or suggestions on how to use the repository as you indicated I would greatly appreciate it.

[c_baker]

You can compare models in the repository or between the repository and workspace using the 'Repository/Compare...' menu or within the workspace using 'Tools/Compare Models' menu.

This is documented in the 'Core Features Guide > Modeling with PowerDesigner > Comparing and Merging Models > Comparing Models' and  'Core Features Guide > Storing, Sharing, and Reporting on Models > The Repository > Comparing Models in the Repository'.

You can easliy compare between versions and even generate a modification script ('Database/Apply Model Changes to Database...') when altering a database.

The Merge/Compare facility is quite powerful.

Comparisons can be performed between models/repository regardless of how you end up formatting your eventual DDL output.

Chris