custom cd_id while importing structures to table

Hi,





I wanted to know whether we can implement a naming scheme for cdid instead of just having it autoincremented. for eg. the cdid for the first compound should be table001 ,second should be table002 etc (where table is the table name). In this way each compound will be uniquely identified among the compounds in other tables. Where would I have to modify in order for it to implement this scheme? Thanks.








Bhargav

Is it atleast possible to implement the above naming scheme when exporting it into a file?


I mean that the tables will have the cd_id in autoincremented format but when we export it to a file we will add the name of the table to it , so the field for cd_id in the exported file reads


tablename001, tablename002 etc.





Thanks


bhargav

Hi,





The cd_id column is numeric, so it can only store numbers.


(The range is between 1 and Integer.MAXVALUE).





It is certainly possible to create identifiers during the export, however it needs a relatively low-level usage of our API.


I can give further advice on that if needed, but until then here is a simpler solution (the examples assume that you use Oracle):





1. Add a text column to store the ID to your table:

Code:

ALTER TABLE SCOTT.MYTABLE ADD ("ID" VARCHAR2(100));

2. Use a trigger to automatically to fill the ID column with the appropriate value on insert.


A BEFORE trigger is needed for this that is executed for each row.


Currently there is a BEFORE trigger for JChem tables (<table_name>_TR). This trigger affects the cd_id value, so it's best to modify this trigger. Simply insert the following line at the end of the "IF INSERTING" block:

Code:

...            END IF;            :NEW.id := 'SCOTT.MYTABLE_' || :NEW.CD.ID; /*the new line*/        END IF;        IF UPDATING THEN ....

Please note that I have also included the schema into the identifier, since in an other schema you may have the same table name.





3. If there were rows in the table before the trigger modification, you should also update the values for pre-existing rows:

Code:

UPDATE SCOTT.MYTABLE SET ID='SCOTT.MYTABLE_' || CD_ID WHERE ID IS NULL;

Now you can use and export this field as any other data field.





Best regards,





Szilard

Hi,





I assume that the TRIGGER, MYTABLE_TR is created when the table MYTABLE is being created. In order to add an extra column to the table, we are modifiying the trigger manually for each table after it is created. Is there any way that the statements


...


END IF;


:NEW.id := 'SCOTT.MYTABLE_' || :NEW.CD.ID; /*the new line*/


END IF;


IF UPDATING THEN


....


can be incorporated/added directly into the trigger MYTABLE_TR when it is created instead of manually changing it afterwards? Because every day we have around 10 -15 tables being created and it is not possible to add the lines to the trigger for each table manually.





Bhargav

Hi,

Quote:

I assume that the TRIGGER, MYTABLE_TR is created when the table MYTABLE is being created.

Yes.

Quote:

Is there any way that the statements [...] can be incorporated/added directly into the trigger MYTABLE_TR when it is created instead of manually changing it afterwards?

There is only one new line / statement:

Code:

:NEW.id := 'SCOTT.MYTABLE_' || :NEW.CD.ID; /*the new line*/

I quoted the rest of the lines to help to find its place more easily.





There is no way to specify to JChem to add this line to the trigger.





To automatize the changing of the trigger, you have to issue a "CREATE OR REPLACE TRIGGER" statement with the full source of the new trigger. Only specific parts change due to the change in table name, these can be substituted fairly easily, so a script or a small application can do the job.





An alternative to trigger manipulation may be to ensure that the mentioned UPDATE statement will be run after each import to calculate the missing values for the newly added rows:

Code:

UPDATE SCOTT.MYTABLE SET ID='SCOTT.MYTABLE_' || CD_ID WHERE ID IS NULL;

Best regards,





Szilard





PS: On the long run we plan to allow JChem users to specify some columns with automatically calculated values.


In general these will be Chemical Terms expressions, but we might find a way to squeeze in a similar naming schema as yours.


Presently I cannot give you any deadline on this project or guarantee the implementation of the naming schema within its scope, so I suggest using one of the described methods until then.