7 Replies Latest reply: Mar 16, 2012 9:30 PM by 882314 RSS

    Custom Transformation Scripts

    21205
      Hi,

      I am very new to data modeler, so excuse me for my ignorant questions..
      These Custom Transformation Scripts functionality is very powerful, I can see that. But writing these things is a .. let's say less enjoyable.
      I know of the /datamodeler/datamodeler/xmlmetadata/doc/index.html documentation, but coming from a database development background - it is not an easy task to get my head around it.

      Anyway... what I want to do is add triggers to my tables. They all have a similar template (fill the ID with the nextval of a sequence and fill the Created_by and Created_date columns).

      How to do this?

      Thanks,
      Alex
        • 1. Re: Custom Transformation Scripts
          Philip Stoyanov-Oracle
          Hi Alex,

          Here are the steps how I'll do it if I have to create something with scripting
          1) to create the object using DM UI and save the design - in this case 1 table and 1 trigger in physical model, set needed properties for the trigger
          2) find the trigger definition into file system - you need actions, body, triggerTime and table properties - well owner also can be set
          3) find description for trigger in HTML documentation - you can see set method for each property
          4) find how to create trigger - physical model (StorageDesign class) is responsible for that - you need StorageDesign for Oracle - looking at collections (OracleTreggers collection) you'll see that method is createTriggerOracle
          5) physical model has to be open in order get the job done - that physical model has to be set as RDBMS site to your relational model
          6) you need tables in physical model - again back in collections of storageDesign - OracleTables and method is getTableProxySet()
          function setBody(tableName,seqName,trigger){
           //set body to trigger depending on tableName and seqName, my example is just illustration, not correct trigger body; you can change parameters for function setBody
           //use \n to put new line in text
           body = "begin\n";
           body = body + tableName+".ID = "+seqName+".NEXTVAL";
           body = body + "\nend";
           trigger.setBody(body);
          }
          //write other help functions if you need them
          
          //get storage design (physical model)
          //model is provided to that script - it's relational model
          sd = model.getStorageDesign();
          tables = sd.getTableProxySet().toArray();
          for (var i = 0; i<tables.length;i++){
           table = tables;
          trigger = sd.createTriggerOracle();
          //set properties, check exact values with sample
          trigger.setTable(table);
          trigger.setName("trig_"+table.getName());
          trigger.setActions("INSERT");
          trigger.setTriggerTime("BEFORE");
          //table getLongName will return owner.table_name
          setBody(table.getLongName(),"seq_"+table.getName(),trigger);
          }
          check the result and save physical model  (design) if everything is correct
          
          Philip                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: Custom Transformation Scripts
            Marc de Oliveira
            Here is an alternative to Philip's suggestion.

            I decided that I did not need the triggers modeled in Data Modeler, so I generate a DDL script to create the necessary triggers.

            This way of using the transformation scripts is very useful, too.

            Regards,
            Marc de Oliveira

            // Create table triggers

            outFile = new java.io.FileWriter("C:/SVN/DIMS/trunk/dims/data/ddl_scripts/DIMSTableTriggers.sql");
            out = new java.io.PrintWriter(outFile);
            out.print("-- -------------------------------------------------");
            out.println();
            out.print("-- Title : Create table triggers");
            out.println();
            out.print("-- Created: " + new Date() );
            out.println();
            out.print("-- -------------------------------------------------");
            out.println();
            out.println();

            tables = model.getTableSet().toArray();

            for (var t = 0; t<tables.length;t++)
            {
            table = tables[t];
            tableName = table.getName();

            if (table.getName().length() > 26)
            {
            trigger_name = table.getName().substring(0,26) + "_"
            }
            else
            {
            trigger_name = table.getName() + "_"
            }

            out.println();
            out.print("-- Create before insert row trigger: " + trigger_name + "bir");
            out.println();
            out.print("create or replace trigger " + trigger_name + "bir");
            out.println();
            out.print(" before insert on " + tableName);
            out.println();
            out.print(" for each row");
            out.println();
            out.print("begin");
            out.println();
            columns = table.getElements();
            idColumn = null;
            fromColumn = null;
            for (var c = 0; c<columns.length;c++)
            {
            column = columns[c];
            if (column.getName() == "id") { idColumn = column}
            }
            if (idColumn!=null)
            {
            out.print(" if :new.id is null");
            out.println();
            out.print(" then");
            out.println();
            out.print(" select " + tableName + "_seq.nextval into :new.id from dual;");
            out.println();
            out.print(" end if;");
            out.println();
            out.print(" ");
            out.println();
            }

            out.print(" :new.created_by:= user;");
            out.println();

            out.print(" :new.created_date:= sysdate;");
            out.println();

            out.println();
            out.print("end;");
            out.println();
            out.print("/");
            out.println();
            out.println();


            out.println();
            out.print("-- Create before update row trigger: " + trigger_name + "bur");
            out.println();
            out.print("create or replace trigger " + trigger_name + "bur");
            out.println();
            out.print(" before update on " + tableName);
            out.println();
            out.print(" for each row");
            out.println();
            out.print("begin");
            out.println();

            out.print(" :new.updated_by:= dims_user.get_dims_username;");
            out.println();

            out.print(" :new.updated_date:= sysdate;");
            out.println();
            out.print("end;");
            out.println();
            out.print("/");
            out.println();
            out.println();

            }

            out.close();
            • 3. Re: Custom Transformation Scripts
              Philip Stoyanov-Oracle
              I decided that I did not need the triggers modeled in Data Modeler, so I generate a DDL script to create the necessary triggers.
              I got the impression Alex needs them in the model. Otherwise you are right - such derivative triggers can stay out of the model and this is the reason we do not create triggers for auto increment columns in physical model.

              Philip
              • 4. Re: Custom Transformation Scripts
                21205
                Thank you both very much, highly appreciated.

                I can see from your responses that there is still a lot I need to learn, thank you for that. Now I can continue on my journey into the world of Data Modeler.
                • 5. Re: Custom Transformation Scripts
                  882314
                  I am trying to remove some columm in the Table_Template, If that columm is exist in other table on my relation model.
                  But I dont see the function RemoveColumm().
                  Please help me!
                  • 6. Re: Custom Transformation Scripts
                    Philip Stoyanov-Oracle
                    you need to find the column you want to remove then use:
                    column.remove();
                    Philip
                    • 7. Re: Custom Transformation Scripts
                      882314
                      Thanks Philip Stoyanov.
                      It works.