PostgreSQL support seems a bit weak

[chopper]

I came to Toad Modeler from years with its predecessor, Charon’s Case Studio2. I loved CS but, alas, it was purchased by Quest and support stopped at PG 8.2

I’ve run into several problems with Toad which I’m hoping is just a matter of my unfamiliarity with it.

First, in the Entity Properties editor, the Data Type pulldown is only like 9 characters wide so half the datatypes are truncated. For instance, I have four "Timestar"s in the pulldown, which are actually four different types of PG “timestamp” types. And because the columns in the editor aren’t expandable the only way I can tell if I’ve selected the correct data type is to save the data type semi-blindly and do a SQL Preview to make sure I got the right one.

SQL Preview exposed another problem with FK relationships. Like Case Studio, Toad supports Identifying and Non-Identifying relationships but Toad is generating some bizarre SQL with the latter. Here’s what I see when I create a Non-Identifying relationship on “book_id”:

– Table social_referral

CREATE TABLE “social_referral”(
“id” Serial NOT NULL,
“href” Varchar NOT NULL,
“created_at” Timestamp with time zone DEFAULT NOW(),
“site_type” Varchar NOT NULL,
“book_id” Integer DEFAULT nextval(‘book_id_seq’::regclass) NOT NULL,
CONSTRAINT “social_referral__site_type_check” CHECK (site_type::text = ANY (ARRAY[‘facebook’::text, ‘twitter’::text, ‘linkedin’::text, ‘blog’::text, ‘other’::text]))
)
WITH (OIDS=FALSE)
;

– Add keys for table social_referral

ALTER TABLE “social_referral” ADD CONSTRAINT “Key4” PRIMARY KEY (“id”,“book_id”)
;

Not only don’t I see the constraint code for foreign key creation, why is it defaulting book_id with nextval(‘book_id_seq’::regclass)? That’s just going to cycle the sequence table on ‘book’, which is definitely incorrect.

Also, is there anyway to move the knots/ties on the relationship lines? Toad seems to want to place them where it wants to, not where I want them.

[Vaclav_Frolik]

Hi,

Toad Data Modeler supports PostgreSQL to version 9.2 and support for version 9.3 is planned.

Re attributes: in both the Entity Properties and Attribute Properties form the data type combo box length is sufficient or can be enlarged manually.

Re default value: please send or attach sample model.

Re relationship lines and anchor points: That's what we plan for next commercial release. You can download version 5.2 BETA and try the new system of relationship lines management. In the BETA version you can move anchor points and change positions of lines much more comfortably.

Regards,

Vaclav

[chopper]

Sorry, I meant that Case Studio ended its support at PG 8.2. That’s why I purchased TDM.

A few minutes after I posted that message I found the column divider bars on Entity Properties so I could expand them. They’re very, very faint against a white background and a monitor with less-than-optimal contrast.

On the FK issue though, this seems to be something unique to refactoring a reverse engineered database. Here’s what I think the issue is.

‘Serial’ isn’t a real datatype in PG, just a notational shortcut for PG to expand that column to something like this when the table is created:

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename (
colname INTEGER DEFAULT NEXTVAL(‘tablename_colname_seq’) NOT NULL
);

When you reverse engineer a table created with a Serial column, that’s what you get back for that column: an integer with a DEFAULT pulling its value from a sequence table: colname INTEGER DEFAULT NEXTVAL(‘tablename_colname_seq’) NOT NULL

In Case Studio, when you created a Non-Identifying FK from such a table you got an FK that’s just the parent’s datatype. But in Toad when I do it to an (as originally defined) Serial column in a parent table I get back an integer in the child table AND it inherits the parent column’s default, or DEFAULT nextval(‘PARENT_id_seq’::regclass). To be honest, I haven’t exported this to PG to determine what would happen if I created a new row in that child table where the key wasn’t found in the parent table. But the DDL suggests that if the key wasn’t found it would grab the next value from the parent table’s sequence table (thereby incrementing it as well) rather than throw an exception on the NOT NULL constraint.

Here’s a set up.

Parent table:

CREATE TABLE “public”.“book”(
“book_id” Integer DEFAULT nextval(‘book_book_id_seq’::regclass) NOT NULL,
“isbn” Character varying(20),
…
)
WITH (OIDS=FALSE);

Child table:

CREATE TABLE “social_referral”(
“id” Serial NOT NULL,
“href” Varchar NOT NULL,
“created_at” Timestamp with time zone DEFAULT NOW() NOT NULL,
…
)
WITH (OIDS=FALSE);

If you create a Non-Identifying relationship from parent to child you get this (at least as determined by SQL Preview):

CREATE TABLE “social_referral”(
“id” Serial NOT NULL,
“href” Varchar NOT NULL,
“created_at” Timestamp with time zone DEFAULT NOW() NOT NULL,
“book_id” Integer DEFAULT nextval(‘book_book_id_seq’::regclass) NOT NULL
…
)
WITH (OIDS=FALSE);

What I would suggest for FK relationships in TDM is to not inherit the DEFAULT from the parent. Since the parent “owns” the data in that shared column any defaults it sets for itself should only apply to rows inserted in its table. The child table shouldn’t inherit that default too.

�

[Vaclav_Frolik]

Hi,

if you draw new relationship and do not want the Default value to be added to child attribute, change settings this way:

Regards,

Vaclav

[chopper]

Great! Thanks, Vaclav.