SQL Preview dialog suggestion.

Toad Data Modeler
1

When previewing the SQL for a table create statement it is nicely formatted with carriage returns and indention

CREATE TABLE [dbo].[e5]
(
[e5_id] Uniqueidentifier NOT NULL,
[e6_id] Uniqueidentifier NOT NULL,
[e1_id] Uniqueidentifier NOT NULL,
[e9_id] Uniqueidentifier NOT NULL
)

When you view the DDL for a relationship, you just get one long line of text.

ALTER TABLE [dbo].[e5] ADD CONSTRAINT [e6_to_e5] FOREIGN KEY ([e6_id]) REFERENCES [dbo].[e6] ([e6_id]) ON DELETE CASCADE

I see that you have a gray vertical line down the right hand side of the preview dialog at 80 characters. It would be nice if you could automatically wrap text to that maybe with some extra spaces on the next line… The above statement becomes

ALTER TABLE [dbo].[e5] ADD CONSTRAINT [e6_to_e5] FOREIGN KEY ([e6_id]) REFERENCES
[dbo].[e6] ([e6_id]) ON DELETE CASCADE

It would be even nicer if you could format the statement a little better. Something like

ALTER TABLE [dbo].[e5] ADD CONSTRAINT [e6_to_e5]
FOREIGN KEY ([e6_id]) REFERENCES [dbo].[e6] ([e6_id])
ON DELETE CASCADE

Of course, we would want that wrapped text to carry through to the SQL that gets generated to the script, but that should be easy enough. I’m sure there will be those people who don’t want the text to wrap. They can un-check the new box labelled “Wrap text” that you add beside the magnifying glass on the SQL Preview dialog.

I suppose there is a way to modify the SQL genrator that I haven’t found and played with yet such that I can format the SQL that is generated to meet my internal coding standards.

2

Hello Bruce,

Thank you for your suggestion. We will consider it. CR # 39 538.

I suppose there is a way to modify the SQL generator that I haven't found and played with yet such that I can format the SQL that is generated to meet my internal coding standards.

I'll get back to you with this on Monday. Thanks for your patience!

Regards,

Vladka

3

I guess since you didn’t specify which Monday you would respond on, that explains why you haven’t gotten back to me on this.

While we are talking about scripting and customization, I asked some time earlier about being able to modify the output of the HTML and RTF reports. It would be nice to specify landscape vs portrait for some of the report tables and some other types of customization that I haven’t really thought about yet. Do you have any thoughts or suggestions in that area? I’m getting ready to generate some documentation and it would be nice if I could get the output close to the standards that I have to adhere to without having to make too many modifications. Are there any updates to that thread?

4

Oops! Sorry, Bruce. I simply forgot! Please accept my apology.
(Mmmm…wondering what Monday I could mean… :wink: )

Concerning the customization itself, the truth is that I need to discuss it with our developers. They are still making changes here, improving, rearranging, developing… TDM3 is being changed significantly, still in progress. As soon as it’s clear, it will be documented properly. I promise!
What is available now: Please see the Manual - there is the Customization chapter with an exact example (see e.g. the Modifying HTML Reports topic). Also, there’s a movie on this issue at:
http://modeling.inside.quest.com/entry.jspa?externalID=1140&categoryID=158

Back to your questions: Anyway, I’ve already asked my colleagues for advice. As soon as I have some helpful information for you, I’ll write. This time, I will not forget. I make a note of it in red. :wink:

Regards,

Vladka

5

Hello Bruce,

I suppose there is a way to modify the SQL generator that I haven't found and played with yet such that I can format the SQL that is generated to meet my internal coding standards.

Bruce, we'd like to ask you for some examples on what exactly you'd like to modify. We'll make an example (a movie) showing how, where etc. Please tell us what you'd like to see. We will focus on it.

Thanks in advance for the details.

Regards,

Vladka + TDM Team

6

The first, and simplest, option that comes to mind is the number of characters used for indentation. Some coding standards call for two, some three, others four characters.

Some folks prefer to have the comma to follow a column in a table definition, others prefer to have the comma starting the column definition. For example

create table (
column1 datatype,
column2 datatype,
column3 datatype);
…

create table (
column1 datatype
,column2 datatype
,column3 datatype);

For maximum flexibility, maybe you can look at the Toad Code Formatter that is part of Toad for Oracle and Toad for SQL Server. It has several options for indentation, comma seperation and wrapping. It deals primarily with DML code, but maybe it can give you some ideas for DDL as well.

You are probably aware that you aren’t going to please everybody , but with some flexibility, maybe the community can tailor their coding standards to more closely reflect your output so we can meet in the middle.

My first suggestion was just to wrap the ‘create constraint’ statement to somewhere around 80 characters so that I could see it all without having to scroll WAAAAAY out to the right hand edge of the the screen. My best example of that would be instead of:

ALTER TABLE “dbo”.“e5” ADD CONSTRAINT “e6_to_e5” FOREIGN KEY (“e6_id”) REFERENCES “dbo”.“e6” (“e6_id”) ON DELETE CASCADE
/

I would have something like
ALTER TABLE “dbo”.“e5” ADD CONSTRAINT “e6_to_e5”
FOREIGN KEY (“e6_id”) REFERENCES “dbo”.“e6” (“e6_id”)
ON DELETE CASCADE
/

In this example, you have the alter table command, the table name and the action all on one line. The next line specifies the foreign key name and the parent table refreence. The third line has any constraint action. That is just the way I would break it out. I’m sure there are as many opinions as there are community members. Let everyone chime in with what they want.

7

Hi Bruce,

Thanks for your suggestions.
We will have a look at it and will try to make an instructional movie showing some basic steps that are necessary to take to make the modifications.
At the moment, I can’t say when the movie is available. In any case, it will not be this week. So, I need to ask you for patience. Thanks very much.

Regards,

Vladka + TDM Team

8

I’m creating a view so I’m working a little more with the SQL preview/edit dialog. Another thing to add to the list of potential modifications is word case. Some people prefer SQL reserve word (create table) to be lower case and others prefer CREATE TABLE. The same for database object names. I guess you are generally going to use one case for reserve words and the other case for database objects.

I noticed when editing the text in the SQL tab for a view that it doesn’t quite behave as I would expect. When I get to the end of the line, I can use the right arrow and just continue scrolling out to infinity. I would have expected it go to the next line in the buffer if there is one. If there isn’t it should chirp or make some other obnoxious noise to indicate that it can’t do what I’m trying to do.

New/old ER. There really needs to be a better way to define a view rather than just typing in all of the SQL text. I can create a view and link tables to it. I should be able to select the fields from the tables that I want in the view. I wound up previewing the SQL for the table that I want, copy that text out to Notepad because the editor works better, re-format it, and copy that back into the SQL tab on the view dialog and then repeat those steps for each table that I want in my view.

9

Hello Bruce,

Thanks very much for your suggestions.

  1. Word case - SQL Preview behaves like generator. It will be possible to modify it.
  2. Right arrow in the SQL tab - We will have a look at it and will see if it’s possible to change the behaviour. CR # 43 195.
  3. Define view - Thanks for this suggestion. Again, we need to analyze it and find out the possibilities (if there are any). CR # 43 196.

Thanks for your co-operation and patience.

Regards,

Vladka + TDM Team

10

For #3, I’m thinking of a dialog similar to the one where you define the attributes for a key or index. Entity Properties | Keys tab | Key Attribute Dialog | Attributes tab or Entity Properties | Indexes tab | Index Properties Dialog | Items tab. From there, you get the list of attributes in all tables selected for the view and you can select them and move them to the right for inclusion in the view. There would need to be another tab on the dialog where you enter the where clause. So here’s is what you have. When you double click on view, you get a dialog. One tab would be where you select the tables for inclusion. The next tab would contain all of the fields for all of the tables that are selectable for inclusion into the view. The third tab would be where you put the where clause. If you add another line style for view definition (similar to the notes line), then the first tab would contain the tables where you drew lines for into the view. Let me know if you would like more explanation.

I’m basing this interface on another modeling tool that I’ve used in past. I won’t name names. Think old product, really expensive, with next to no customer support. Bert Scalzo knows who I’m talking about.

11

Hi Bruce,

Your further explanation has helped us a lot. Now we better understand.
I’ve added your notes to the case details.

Thanks a lot!

Vladka

12

Hi Bruce,

Here I’m with an update concerning the promised movie. Unfortunately, we won’t manage to make it till the holidays. (It was just yesterday we released Beta.) And during the holiday season, all our team will be out of offices. I’m sorry.
We’ll start working on this then (so, next year… :wink: ).
Bruce, we hope you don’t mind (however, not sure how urgent it is for you…).

Thanks very much for your patience!

Regards,

Vladka

13

My note was just that - a suggestion for future improvement of TDM. I’m not in a hurry for the finished product. The fact that it will show up one day is an improvement over most software development tools.

My office will also shut down for the holidays so Friday will be my last day here until the new year. Upon return, I’ll have an extended medical leave and a transfer back to an old project (I’m on loan) which will probably mean that I will disappear like a wisp of smoke. Feel free to remember me as just a bad dream. Perhaps I’ll drop in and check on you while I’m on leave.

Happy holidays to all in TDM land.

14

Hi Bruce,

Thanks very much for all your ideas, suggestions and recommendations you have shared with us. Your help and support (also to other community members) have been really great!

Feel free to remember me as just a bad dream.

Thanks also for this suggestion. :wink: Nevertheless, we will definitely remember you as one of the best TDM3 user and Modeling community contributor and external member of our TDM team.

Perhaps I'll drop in and check on you while I'm on leave.
If you can, please do drop by, we'd be happy.

Bruce, let us wish you Merry Christmas, great holiday season spent with your family and good friends. Quick recovery and good health, happiness and success in the new year.

We do hope to hear from you soon and are already looking forward to it. :slight_smile:

Take care,

Vladka + TDM Team