5 Replies Latest reply on Oct 2, 2019 5:19 AM by hschlote

    ctf: how to access physical models and set properties?

    hschlote

      custom transformation scripts (ctf)

       

      I adapted "create index on FK" to create explicit indexes on PKs.

      Now I want to set "Using Index" to "By index name" and Using Index / Existing index to the appropriate index.

      How can I access physical model(s)?

      Is there any documentation available on how to develop custom transformation scripts?

      And btw: is there a proper way to develop these scripts, e.g. support for indenting in emacs or any other editor and so on?

       

      Thx in advance

        Hans

       

      script so far:

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

      for (var t = 0; t < tables.length; t++){

        table = tables[t];

        indexes = table.getKeys();

        for (var i = 0; i < indexes.length; i++) {

          index = indexes[i];

          if(index.isPK()){

            columns = index.getColumns();

            newIndex = getIndex(table,columns);

          if(newIndex==null){

              newIndex = table.createIndex();

              table.setDirty(true);

              for (var k = 0; k < columns.length; k++){

                newIndex.add(columns[k]);

              }

            }  

          }

        }

      }

        • 1. Re: ctf: how to access physical models and set properties?
          jflack

          Have a look at this blog entry from Dave Schleis:

          https://daveschleis.wordpress.com/2018/01/30/lets-get-physical/

          That should answer your first question.  And browse around Dave's blog - he has a few articles on scripting Data Modeler, some intended for people getting started with this powerful capability.

           

          As for editor - any good JavaScript editor will do, if you are writing your script in JavaScript.  I am perfectly happy using Notepad++ for this, but most IDEs have good support for this.  I think it might be interesting to write a script in TypeScript someday - since TypeScript compiles to JavaScript.

          • 2. Re: ctf: how to access physical models and set properties?
            Philip Stoyanov-Oracle

            Hi Hans,

             

            you can read SQLDeveloperDataModelerScripting.docx located in datamodeler\datamodeler\xmlmetadata\doc directory of your DM installation.

            It's written there - model.getStorageDesign() will return the physical model for that relational model - it could be closed.

            Each physical model is also RDBMSSite and there is RDBSSiteSet at design level - visible in "Tools>RDBS Site administration". So to get physical model for specific RDBMSSite (not the current one set to relational model) you need fist to get the site:

            site = model.getDesign().getRDBMSSiteSet().getByName("site name as it's set");

            //get storage design (physical model) now

            sd = model.getStorageDesign(site);

             

            to set the index you need to get presentation for that index in physical model

             

            Philip

            • 3. Re: ctf: how to access physical models and set properties?
              hschlote

              Hi Philip,

               

              Thx a lot - again ;-) Actually I did find the documentation and tried to get along with it.

              What I did not find was that site = model.getDesign().getRDBMSSiteSet().getByName("site name as it's set"); part.

              And this is what did puzzle me cause you may have more then one phys model and I wondered how DM might by magic chose the "right" one.

              So this most probably was the missing hint.

              Anyway, my script is working now and I did learn a lot.

               

              Kind regards and thanks a lot again, Hans

               

              BTW: When you debug such a script the cursor position is off by some lines. So when you click a branch actually the one 2 or 3 lines above is opened.

              • 4. Re: ctf: how to access physical models and set properties?
                hschlote

                Hi John,

                 

                I did take a look at Dave's blog.

                But I struggled with Groovy and didn't get it working in time before Philip added his comment and saved Nashorn.

                And actually while I find that Groovy approach pretty neat - for me the Nashorn approach using arrays and indexes is easier to understand.

                And Nashorn can be debugged so you can step through your code and look up what methods may be called.

                In my humble opinion that's much easier than consuming that XML Metadata documentation.

                As editor I used emacs in JavaScript-mode and indentation for Groovy was pretty fast getting me to the outer right margin.

                 

                Kind regards, Hans

                • 5. Re: ctf: how to access physical models and set properties?
                  hschlote

                  But thanks anyway for your response! It made me peek into Groovy.