Massively updating the attributes data type to chosen domain

Toad Data Modeler
1

Hi all,

I have an issue with Toad Data Modeler.

We are modeling a normalized layer for our data warehouse.

For homogeneity reason we wish to make sure that each field use a standard Domain, defined in TOAD.

But changing the attributes one by one take forever and I am looking for a way to massively change them, I tried to use the Export/import excel function but it doesn’t work. My understanding is that this functionality is not provided by this import/export.

Is there any way you can recommend me to do so ?

Or there are no way to your knowledge to do such operation ?

Thank you in advance for your help !

2

Hello Clement,

As you guessed, Excel Import/Export isn’t really meant for this purpose. In your case you might want to use scripting to change multiple attribute data types. Enable Expert Mode in Options | General and go to Expert Mode Menu | Scripting Window. This is the place where you write your own scripts in TDM.

There are some resources available on scripting, see Help | Projects and Models | Scripting and Customization. If you like, you may try the following script, which changes ALL attributes to “Domain1” including PKs, FKs (you can change the name in script).

function main()
{
var e, a, Entity, Attribute;
var Domain = Model.Domains.GetObjectByName(‘Domain1’); //Change name of the Domain here

if (Domain == null)
{
   Log.Information('Domain "Domain1" does not exist.')
   return;
}

Model.Lock();

for (e=0; e<Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);
Entity.Lock();

  for (a=0; a<Entity.Attributes.Count; a++)
  {
      Attribute = Entity.Attributes.GetObject(a);
      if (Attribute.DataType!=null)
      {
          Attribute.SetLinkedObject('DataType', null);
          Attribute.ChangeDataType(Domain);
      }
  }
  Entity.UnLock();

}
Model.UnLock();
}

Regards,

Lukas

3

Hi, you might find some useful information in this blog article:

www.toadworld.com/…/assign-existing-domain-to-selected-columns.aspx

It shows how to assign Domain to selected attributes.

4

Hi

Thanks for you answers, both of you.

Vaclav, this package is useful but not precisely in that case, since I wish to apply the domain to certain type of fields, depending on their data type, and the object explorer doesn’t show data type.

Lukas, I reused your script, and after few tweak I made it work.

Here is the script I used : (WARNING IT GENERATE LOTS OF LOG, i still need to clean this)

function main()

{

var e, a, Entity, Attribute, aqString, aSubString, Res;

// var Domain = Model.Domains.GetObjectByName(‘Domain1’); //Change name of the Domain here

/* if (Domain == null)

{

Log.Information(‘Domain ‘+Domain+’ does not exist.’)

return;

}*/

Model.Lock();

for (e=0; e<Model.Entities.Count; e++)

{

Entity = Model.Entities.GetObject(e);

Entity.Lock();

for (a=0; a<Entity.Attributes.Count; a++)

{

Log.Information('Check the table '+Entity.Name);

Attribute = Entity.Attributes.GetObject(a);

if (Attribute.DataType!=null)

{

var data_type = Attribute.DataType.Name;

var attribute_name = Attribute.Name;

Log.Information('Check the ‘+attribute_name+’ field of type '+data_type);

var not_sys_table = 0

var Domain = Model.Domains.GetObjectByName(‘Enculer’);

switch (attribute_name) {

case ‘sys_sourcename’ :

var Domain = Model.Domains.GetObjectByName(‘sys_large’);

var text = ‘sys_large’;

break;

default :

var text_att = attribute_name;

var not_sys_table = 1;

Log.Information(‘Have not found ‘+text_att +’ in the system attributes’ ) ;

}

if(not_sys_table == 1)

{

Log.Information('We check the other type of fields ’ +data_type ) ;

switch(data_type) {

case ‘Character varying’:

if(Attribute.DataTypeParam1 <180){

var Domain = Model.Domains.GetObjectByName(‘small text’);

var text = ‘small_text’;

}

else if(Attribute.DataTypeParam1 >1800){

var Domain = Model.Domains.GetObjectByName(‘large text’);

var text = ‘large_text’; }

else {

var Domain = Model.Domains.GetObjectByName(‘medium text’);

var text = ‘medium_text’;

}

break;

case ‘Character varying(%p1)’:

if(Attribute.DataTypeParam1 <180){

var Domain = Model.Domains.GetObjectByName(‘small text’);

var text = ‘small_text’;

}

else if(Attribute.DataTypeParam1 >1800){

var Domain = Model.Domains.GetObjectByName(‘large text’);

var text = ‘large_text’; }

else {

var Domain = Model.Domains.GetObjectByName(‘medium text’);

var text = ‘medium_text’;

}

break;

case ‘Timestamp without time zone’:

var Domain = Model.Domains.GetObjectByName(‘date and time’);

var text = ‘date and time’;

break;

default :

var Domain = Model.Domains.GetObjectByName(‘none existing domain’);

var text_att = Attribute.Name;

var not_sys_table = 1

Log.Information('Have not found in other '+text_att+ 'with type '+Attribute.DataType ) ;

}

}

if (Domain == null)

{

Log.Information(‘Domain ‘+text+’ does not exist.’)

return;

}

Attribute.SetLinkedObject(‘DataType’, null);

Attribute.ChangeDataType(Domain);

}

}

Entity.UnLock();

}

Model.UnLock();

}

It take a little bit of time, and you may wish to change the rules to apply the different domain but it works.

One issue, if there is a attribute he can’t change, the function will stop and will leave your model and entities locked, you may need to use a othe piece of code to unlock them. (since I don’t know how to do it manually)