I have enjoyed using TDM for modeling PostgreSQL databases for a couple of years now, but I keep missing better support for inheritance (and the associated caveats). Many very helpful features and improvements have been added, these issues are just the last bits that still make me do an occational google search for TDM alternatives:
While it’s a great help to be able to name inherited tables, (and have them generated in the right order) I’d really like a way to see this relationship visually in the physical model.
Also, only attributes themselves are inherited. Contraints need to be explicitly added for each child table…TDM could be a real lifesaver by supporting this somehow.
Currently I’m trying to figure out if I can add these through a custom script during the DDL generation.
Oh and the C-style comments in the beginning of the generated DDL has always forced me to write shellscripts scripts to handle the import
Tip: Please check out encoding of your script. In TDM, you can select it - Settings | Options | Physical Model | SQL Script area | Encoding…
Possibly, this information will help.
We look forward to hearing from you.
Regards,
Vladka + Mario
Hello,
Thank you for your message.
support for inheritance in PG models - This request has already been raised and is registered under CR # 36 941. Unfotunately, it is not planned in the near future.
I’m not sure if you know about the Inherited Tables box in the Entity Properties form. Reading your first sentence again, I think that you know about it. Anyway, I’ve mentioned the information again.
“Constraints need to be explicitly added for each child table.”
Unfortunately, we are not sure what you mean. Do you want TDM to inherit constraints automatically via the inheritance?
Please write us details on what you require. Then we will be able to write/modify a script for you.
“Oh and the C-style comments in the beginning of the generated DDL has always forced me to write shellscripts scripts to handle the import.”
Again, we are not sure what you mean, sorry.
Anyway, it is possible to use this:
I’m happy to hear that you are aware of the issue. It is of course unfortunate that it’s not a priority, but then again I didn’t expect that. I just thought I’d mention it. Is there any way that I can see the actual ticket? I do have some further stuff, but I don’t want to bother you with it if it’s already in there
I do know about the box, and it is something i use regularly - it might very well be the main reason I bought TDM the first time
I’m not sure if you mean to say that TDM supports both styles, or that PG does. I just checked the PG manual, and it does indeed say both styles are supported. I must admit that I haven’t tested this extensively - all I know is that doing something like ‘psql mydatabase < Generated.SQL’ in a Linux shell has always given me errors about the C-style comments in the beginning of the file. I always end up having to pipe it through sed to remove the first few lines.
I may be doing something wrong, or the PG client might not strip comments from stdin or something. I dunno. It’s not the character set though.
But it’s really a minor issue, I just thought I’d mention it.
About the inherited constraints…what I have been looking into is basically what you suggest: inheriting all constraints without having to manually copy/paste dozens of copies of SQL into the after script for each table.
So a script that loops through tables at generation time, checks if the table has a parent, and then copies the constraints from the parent to the child. Originally I was considering also combining the primary keys from the parent and the child, but I don’t think that’s really necesary
3.Yes, PG supports both types of comments. TDM supports only the second one - /* commentary */
I mentioned the tip as standardly DDL script is generated in UTF, which means that there are three characters at the beginning of the script. These characters might cause a problem. So, you can try to select another encoding - ANSI - and try it out in shell. Please let us know if it helped.
Please find attached a script that might help you a little. The script will go through all entities (of the first model listed in the Application View) and to the After Script section of child entity it will insert SQL command for generation of a primary key created by a primary key of parent entity.
Run the script in Scripting Window (Tools menu, Expert mode must be turned on.)
Other details:
The script has been written for PG 8.3. If you want to use it for another PG version, you need to modify function CreatePrimaryKey (e.g. version 8.2 does not have FILLFACTOR).
If you want to run the script for another model (not the first one listed in the Application View), you need to change the number on line 4.
Name of primary key that will be created in After Script will be in this format: “ChildEntityName_PKey”. If you want it to be in another format, you can change it on line 31.
If you have any questions, please do not hesitate to write us back. Thanks!
I had the same inheritance question as wugs. What I think he wants, and what I was hoping for, is a script in LER to PER(PG) conversion that does something like [psuedocode]
for each inheritance in LER
add parent table to inherited tables list of child in PER
end for
There is lots of CS/TDM script code that iterates this way (e.g., creating the attribute list), so I was wondering about creating a script myself, but I haven’t found adequate documentation about how to access the Collection of all inheritances in the logical model.
With a script like this, it doesn’t matter if the attributes are copied from the parent to the children, because PG is smart enough to merge them into one child table column when the DDL is processed.
As far as the comments, that must be an encoding issue; I have never had it.
Off topic, but it IS the UTF characters at the beginning of the file that make psql (Postgres interface) choke. I’m not sure if this can be fixed by recompiling psql with another encoding or with doing a
SET client_encoding = ‘UTF8’;
before reading in the DDL file. My easy work around is to put a blank line with semicolon in the before script, and the errors get attached to the empty line. And of course a comment explanation.
