Toad World® Forums

Customization generator SQL


#1

Hello,

I’m using Toad version 5.1 with a customized generator SQL File for Oracle 10g.

Moving on to the new version 5.3 I can not bring changes to the generator.

I should customize the generation of the sequence and the trigger of the sequence.

Example

/* Sequence for table MAIN_CONTEXTS linked to attribute MAIN_CONTEXT_ID */
CREATE SEQUENCE “MAIN_CONTEXTS_SEQ”
INCREMENT BY 1 START WITH 1 MAXVALUE 2147483647 MINVALUE 1 CYCLE NOCACHE ORDER

/

– Trigger for sequence MAIN_CONTEXTS_SEQ for column MAIN_CONTEXT_ID in table MAIN_CONTEXTS ---------

CREATE OR REPLACE TRIGGER “T_MAIN_CONTEXTS_SEQ” BEFORE INSERT
ON “MAIN_CONTEXTS” FOR EACH ROW
BEGIN
SELECT MAIN_CONTEXTS_SEQ.nextval INTO :new.“MAIN_CONTEXT_ID” FROM DUAL;
END;
/

Now

Sequence not generator because is set to not generate in the model.

– Trigger for sequence MAIN_CONTEXTS_SEQ for column MAIN_CONTEXT_ID in table MAIN_CONTEXTS ---------

CREATE OR REPLACE TRIGGER “ts_MAIN_CONTEXTS_MAIN_CONTEX_0” BEFORE INSERT
ON “MAIN_CONTEXTS” FOR EACH ROW
BEGIN
SELECT “MAIN_CONTEXTS_SEQ”.nextval INTO :new.“MAIN_CONTEXT_ID” FROM DUAL;
END;
/

thanks


#2

Hello there,

Some functions in TDM 5.3 have been rewritten, which may be the reason your customization no longer works.

Could you send us the package which contains your customized function? We can take a look and see what we can do.

Regards,

Lukas


#3

Hi,

I had modify GenerationsOR.txg, that now is not possible.

I attached the function modified.

I ask how create the same results without change a system file.
CreateAttrTrigSequences.zip (1.3 KB)


#4

Oh you’ve sent us the script itself, not the package. Nevermind that, one of our devs has created a package for you. Just download it and put it in the user packages folder, which should be located in

C:\Users<USER>\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}

After you move the package there, open TDM and try the Oracle 10g generation now. The script from the package should be used. You can edit it in the future and it will still be used instead of default TDM 5.3 generation method.

Let us know how it worked out for you!
GenerationsOR_user.zip (2.15 KB)


#5

Fantastic, works great.

I looked at the package but what you have done is not documented anywhere :frowning:

How can I maintain the same order of generation indices foreign key.

Before the index was created just before the foreign key while now just after the creation of the table.

How find a FKIndex from FK in macro script?

Thank you so much


#6

First, let me elaborate on why it isn’t possible to customize some of the system functions anymore (for example the Oracle 10g generation). We are currently in the process of rewriting many of TDM functions in Delphi. Rewritten functions are not customizable any more in in the way you are used to, but they are significantly faster. The Oracle generation functions are rewritten already in 5.3, that’s why you weren’t able to find them in Package/Script Explorer.

To answer your questions:

I looked at the package but what you have done is not documented anywhere

It’s a process that isn’t fully documented because most TDM users won’t probably ever need this and there also exists the danger of doing something wrong and blocking some of the TDM functions, if not the whole application. But basically in the package we used Metamodel where we configured your customized function (CreateAttrTrigSequences()) to be used instead of the default TDM function.

There is some information on Metamodels in TDM Help (Projects and Models > Scripting and Customization > Metamodel) but it doesn’t really go deep. There is also an interesting article on the Wiki which is related to the topic and describes how TDM allows you to add your own functionality by using your own packages - that is the process we used in your case.

How can I maintain the same order of generation indices foreign key. Before the index was created just before the foreign key while now just after the creation of the table.

This needs some explanation as well. In TDM 5.1, the foreign key indices were created just before the foreign keys themselves, but only during the generation of DDL script (the indices didn’t exist in the model). This caused some trouble in the long run so starting with TDM 5.2 the FK indexes are by default created after creating a table they belong to, just like other indexes (they are now part of the model).

In Model Menu > Order of Generated Objects > Extension Tab you can set if the indexes will be created after each table, or all at once after all tables are created. If you’d like to create them just before FKs, feel free to submit the idea to the Idea Pond and we will consider implementing it.

How find a FKIndex from FK in macro script?

You can test a relationship and see, if it there are any FK indices associated with it by doing the following:

FKIndex = Relation.ForeignIndex // If the relation has no FK index associated with it, FKInxed will be NULL

Or, if you want to know if an index is a FK index, try this:

Relation = Index.RelationSource;
if (Relation != null)
Log.Information("Index is FKIndex for relationship: " + Relation.Name);

Hopefully this wall of text will help you :slight_smile:

Regards,

Lukas

**