Hi,

In part two of the XSLT Reports series, I will explain you how to define items that will be displayed in GUI. You will learn how to define default Description, file name, suffix and also how to define parameters and other settings.

See below a new XSL template created for one of our community members.

And this way your definitions will be displayed in Toad Data Modeler.

Let’s continue with the XSLT structure.
In the stylesheet element, you can see definition of standard XSLT namespace plus two additional namespace definitions. The first additional namespace is xmlns:tdm="http://www.quest.com/toad-data-modeler", used for TDM related items, the second additional namespace is xmlns:msxsl="urn:schemas-microsoft-com:xslt", used only when JavaScript function needs to be executed during XSL Transformation.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:tdm="http://www.quest.com/toad-data-modeler"
xmlns:msxsl="urn:schemas-microsoft-com:xslt">

The Stylesheet element is followed by several TDM related elements.

<tdm:description>Output contains Relationship name, parent entity and child entity names and parent and child attribute names. You can change ListSeparator as well as AttributeSeparator parameters. ListSeparator separates CSV values, AttributeSeparator separates attribute names (relationship created using PK of multiple columns.) </tdm:description>
<tdm:caption>Relationships (CSV)</tdm:caption>
<tdm:default-filename>relationships</tdm:default-filename>
<tdm:default-suffix>csv</tdm:default-suffix>
<tdm:db-platforms>
<tdm:db-platform>all</tdm:db-platform>
</tdm:db-platforms>

• tdm:description - use this element to describe your XSLT. The content will appear in GUI, see the screenshot above.
• tdm:caption - allows you to name your XSLT. Whatever you write into this element, it will appear in combo-box.
• tdm:default-filename - gives you the possibility to specify a default name for output.
• tdm:default-suffix - hmmm. It is not very hard to guess what this stands for, isn’t it?
• tdm:default-filename - gives you the possibility to specify a default name for output.
• tdm:db-platforms - here you can see db-related restriction.
  The following definition means that the template is valid for all models created for all target databases.

  <tdm:db-platforms>
  <tdm:db-platform>all</tdm:db-platform>
  </tdm:db-platforms>
  

  This way you can define restriction and say to TDM that the XSLT should be available only for Oracle models.

  <tdm:db-platforms>
  <tdm:db-platform>OR9</tdm:db-platform>
  <tdm:db-platform>OR10</tdm:db-platform>
  <tdm:db-platform>OR11</tdm:db-platform>
  </tdm:db-platforms>
  

  What happens if you create a model for MS SQL Server and open the XSL Transformation dialog? Templates for Oracle model will not be loaded and will not appear in the combo-box.

Now see section 4 in the screenshots. Top-level parameters defined using standard XSLT instructions will appear in GUI. This way you can define parameters before you transform source to ouptut.

<xsl:param name="ListSeparator" select="," />
<xsl:param name="AttributeSeparator" select=";" />

That’s all for now. If you want to use the XSLT template, have a look at the following page:
http://modeling.inside.quest.com/entry.jspa?externalID=2575&categoryID=407

Have a nice day,

Vaclav

Hi,

I am going to write several articles related to generation of customized outputs from Toad Data Modeler. I hope you will find them useful and informative - and maybe you will get some inspiration for making customized templates. In the series you will find out:

• How to generate simplified XML files
• Where to get XSD file that describes structure of simplified XML
• How to create new XSLT file and where to put it
• How to define default file name and default suffix for outputs
• How to define Name and Description of your templates
• How to create parameters that will appear in GUI
• and finally an example of XSLT file that generates XSD from an Oracle model

At the end of this article you will find out that there is an easy way how to e.g. reverse engineer a database system and generate XSD schema for all reversed tables.

Let’s start with basic information and explanations. The current version of Toad Data Modeler allows you to generate detailed report via standard Model | Report… feature. However, there is also another possibility how to generate reports or other outputs. Click Model | Reports / XSL Transformation…. to see the other possibility. A dialog with predefined report types will open.

We can see there that using the XSL transformations we can generate HTML, CSV or XML files. What if we want to generate XSD, SVG or for example XAML code? Is it possible? - Sure! The XSL transformation allows us to work with data stored in our models without the necessity to study internal structure, scripting etc. If you are familiar with XML, xPath and XSLT, then it will be very easy for you to iterate tables, columns and other data and generate output you need to generate.

How it works: for every XSL transformation you need a source (XML file), a template (XSLT file) and a way how to do the transformation. Output type can be specified in the XSLT file.

In Toad Data Modeler, there is GUI that allows you to locate XSLT templates. But..where is the source? Where is XML? Good questions - On the XSL Transformation dialog, click the More button. The dialog will become twice large than it was and you will see there buttons Save XSD files as… and Show XSD File. Click the Show XSD File button. If XSD files are associated with some third party application (e.g. Visual Studio), then the file will open in the associated application. XSD file describes structure of “simplified XML”. In other words, it describes structure of our source. OK, how to generate a real XML file for testing purposes etc.? In the XSL Transformation dialog, select the Complete XML (XML) item from the first combo-box.

Then click Transform. This way you can geneate XML file. We call it “simplified XML”, because, as you probably know, all files created in Toad Data Modeler are in XML format. Save your model as *.TXP file and edit the file in notepad. You will see XML file structure. What’s the difference between simplified XML and standard TXP file? - In the amount and form of information. For example, in simplified XML you will find data types named logically, in TXP the same information is represented by GUID code. Also, simplified XML doesn’t contain information related to workspaces, generators and other data required by Toad Data Modeler application. That’s why you can get oriented in simplified XML much faster.

In next article I will explain how to create a new XSL Template and describe structure of XSL Templates used in Toad Data Modeler. Now I’d like to show you that using one relatively simple XSL template we can generate XSD for Oracle models. Download the “XML Schema (XSD) - Elements” template from the following location:
http://modeling.inside.quest.com/servlet/KbServlet/download/2574-102-4883/xsd-elements.zip

and put it to the same folder where system XSL templates are stored. Open the XSL Transformation dialog and see value in XSL File Location.

Then open any Oracle model or reverse engineer your existing Oracle database structure, click Model | Report / XSL Transformation… item and see a new item in the first combo-box. Click More, define parameters and then click Transform to generate the output and see the result.

The output can be visualized also in Visual Studio (considering column names are unique).

Regards,

Vaclav

Hi,

As you probably know, Toad Data Modeler allows you to convert a model from one database system to another. You can reverse engineer a physically existing Oracle database structure and convert your model to PostgreSQL or MS SQL Server model etc. Tables, relationships, columns and data types will be converted automatically together with logical information. Other database specific items like Triggers, Stored Procedures, Views etc. can be converted too, however, if you select such object types they will be automatically commented out in your newly created model. Why? Because you can write SQL code for Views or Triggers etc. that will work in Oracle, but won’t work in MS SQL Server. And you can face similar problems after converting Oracle 9 model to Oracle 11 model.

How can we easily remove comments (I mean automatically added comments) from the newly created model? - Via scripting!

See below a script that removes comments from newly created Oracle 11g model (converted from Oracle 10g model.)

function main()
{
var app = System.GetInterface('Application');
var Model = app.Models.GetObject(0);
//... parameter in GetObject determines with which model the script should work.
//0 = first model listed in the Application View, 1 = second model listed in the Application View etc.
var i, j, k;
var Entity, Trigger, View, Procedure, Function, CheckConstraint, Attribute, Domain, Default;

Model.Lock();

//Triggers, CheckConstraints
for (i=0; i<Model.Entities.Count; i++)
{
Entity = Model.Entities.GetObject(i);
for (j=0; j<Entity.Triggers.Count; j++)
{
Trigger = Entity.Triggers.GetObject(j);
Trigger.SQL = RemoveCommentChars(Trigger.SQL);
}
for (j=0; j<Entity.CheckConstraints.Count; j++)
{
CheckConstraint = Entity.CheckConstraints.GetObject(j);
CheckConstraint.SQL = RemoveCommentChars(CheckConstraint.SQL);
}
for (j=0; j<Entity.Attributes.Count; j++)
{
Attribute = Entity.Attributes.GetObject(j);
for (k=0; k<Attribute.CheckConstraints.Count; k++)
{
CheckConstraint = Attribute.CheckConstraints.GetObject(k);
CheckConstraint.SQL = RemoveCommentChars(CheckConstraint.SQL);
}
}
}

//Domain CheckConstraints
for (i=0; i<Model.Domains.Count; i++)
{
Domain = Model.Domains.GetObject(i);
for (j=0; j<Domain.CheckConstraints.Count; j++)
{
CheckConstraint = Domain.CheckConstraints.GetObject(j);
CheckConstraint.SQL = RemoveCommentChars(CheckConstraint.SQL);
}
}

//Defaults
for (i=0; i<Model.Defaults.Count; i++)
{
Default = Model.Defaults.GetObject(i);
Default.Value = RemoveCommentChars(Default.Value);
}

//Views
for (i=0; i<Model.Views.Count; i++)
{
View = Model.Views.GetObject(i);
View.SQL = RemoveCommentChars(View.SQL);
}

//Procedures
for (i=0; i<Model.Procedures.Count; i++)
{
Procedure = Model.Procedures.GetObject(i);
Procedure.SQL = RemoveCommentChars(Procedure.SQL);
}

//Functions
for (i=0; i<Model.Functions.Count; i++)
{
Function = Model.Functions.GetObject(i);
Function.SQL = RemoveCommentChars(Function.SQL);
}

Model.UnLock();
}

function RemoveCommentChars( strValue )
{
return strValue.replace(/^--/g,"").replace(/\n--/g,"\n");
}

How to run the script in Toad Data Modeler? Do the following, please:

• Enable Expert Mode - click Settings | Options and in section General, enable the Expert Mode checkbox.
• Click Tools | Scripting Window to open a form from where the script will be executed.
• Copy and paste the script to the main area.
• Click the Execute button (green arrow icon).

Regards,

Vaclav & TDM team