Mathematical operation on fields to create new field

Hi,





Is it possible (or could it be) to create a new field in a table that calculates the average value of another field, based on a matching index value? E.g., you've got say IC50 values for 1000 compounds that have been repeated on two test occasions and you want to store the average in a new table/field, along with the number of test occasions for that compound.





Cheers,





A.

This is not currently possible, but its definitely on our TODO list.


We plan to allow creation of these calculated fields, probably both at the view level (e.g.a widget on a form that just calculates the result) and at the database level (e.g. created in the database by means of a trigger).





The advantage of the first would be that you would not need rights to modify the database to use these, whereas the advantage of the second would be that these are true fields and so could be sorted and queried.





In the mean time if you are in a hurry you could potentially create an empty field for the data in IJC, and then go into the database and populate the values using SQL.





Tim

My question is similar - we're evaluating IJChem and thinking about how to have, for example, automatic generation of an ID number for new compounds, starting at a certain value, incrementing automatically, and combining some alphabetic characters with the number.





Seems like a MySQL trigger is required? Could we try the same strategy of creating an empty column in IJChem and then going into the DB back-end to set up the rest? Will this then appear correctly?

We will be adding this to IJC, hopefully in the 2.3 version (early next year).


We will do this by providing the ability to create a "calculated field" that generates its values using a database triger.





A a temporary solution you could as you suggest create an empty field in IJC and then go into the DB and define a trigger that updates the values. This will work, but the one downside is that that value will still be editable inside IJC (assuming the user has edit rights).





Tim

Looking at the info on triggers here:





http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html





it seems like you need to create a separate table with a 1-to-1 relationship to make this work?

Code:

"CREATE TABLE `id_generation` (   `cd_id` int(11) NOT NULL,   `corp_id` varchar(100) default NULL, ); CREATE TRIGGER testref AFTER INSERT ON mytable   FOR EACH ROW BEGIN     INSERT INTO id_generation SET  cd_id = NEW.cd_id, corp_id = CONCAT('XYZ-', NEW.cd_id + 1100);   END;

or alternately, maybe you can use a sequences table for the trigger like:

Code:

CREATE TABLE `seq` (   `name` varchar(100) NOT NULL,   `last_id` int(11) NOT NULL default '1',   PRIMARY KEY  (`name`) ); CREATE TRIGGER testref BEFORE INSERT ON mytable   FOR EACH ROW BEGIN     SET NEW.corp_id = CONCAT('XYZ-', (SELECT seq.last_id FROM seq WHERE seq.name = 'mytable'));     UPDATE seq SET seq.last_id = seq.last_id +1 WHERE seq.name = 'mytable';   END;

You will need some mechanism to generate the ID you need. The most typical approach would be to create a sequence to generate the IDs, but sequences are not supported on MySQL as autoincrement is usally sufficient.





Either of the approaches you describe should be OK.


Look here for a suggestion


http://forge.mysql.com/snippets/view.php?id=7


but its remarkably similar to second of your approaches.





Tim

I've been scratching my head to figure out how to do the same thing with Derby.





Here's what I've got - you need an SQL client. Two options are:





SquirrelSQL - http://www.squirrelsql.org


ij (part of the Derby package - note IJChem seems to be using Derby 10.2) - http://db.apache.org/derby/derby_downloads.html





If you use SquirrelSQL, the basic setup instruction are here:


http://db.apache.org/derby/integrate/SQuirreL_Derby.html





All you need is to set up in SquirrelSQL the "embedded Derby" driver. To make this easier (at least on Mac OSX), you need to copy the derby.jar file to somewhere you can find it via the file browser. I copied it from:

Code:

/Applications/ChemAxon/InstantJChem/InstantJChem.app/Contents/Resources/app/instantjchem/modules/ext

to:

Code:

/Applications/ChemAxon

Once you get either SquirrelSQL or ij setup, you can connect to the localdb in an IJC project with a connection string like:

Code:

jdbc:derby:/Users/myname/IJCProjects/myproject/.config/localdb/db

where you use your username in place of "myname" and the project directory name in place of "myproject"





So, as above, create a sequences table like:

Code:

CREATE TABLE seq (   seqid int NOT NULL GENERATED ALWAYS AS IDENTITY,   name varchar(100) NOT NULL,   next_id int NOT NULL default 1,   PRIMARY KEY (seqid),   UNIQUE (name) );

We insert something into that table to define the name and starting value of the sequence:

Code:

INSERT INTO seq (name, next_id) VALUES ('XYZID', 1000);

Let's assume we have a table "MYTABLE" with a chemicial id (CD_ID) field as is usual, and a VARCHAR(100) field "CORP_ID" as above.





Then since Derby can only have one statement per trigger, create 2 triggers in this order:

Code:

CREATE TRIGGER MYTABLE_TRIG1 AFTER INSERT ON MYTABLE REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL UPDATE MYTABLE SET MYTABLE.CORP_ID = 'XYZ-' || (SELECT CAST(seq.next_id AS CHAR(10)) FROM seq WHERE seq.name = 'XYZID') WHERE MYTABLE.CD_ID = NEW.CD_ID;

Code:

CREATE TRIGGER MYTABLE_TRIG2 AFTER INSERT ON MYTABLE FOR EACH ROW MODE DB2SQL UPDATE seq SET seq.next_id = seq.next_id + 1 WHERE seq.name = 'XYZID';

Note that the real advantage of this is that we can have multiple tables with compounds (e.g. for different projects, or differnt subparts of one project) and by creating this pair of triggers on each table (using the same "name" in the seq table for all triggers) we get a set of IDs that increments by one any time a compound is added to any of the tables.

Thanks for this information. I expect this might be of interest to other users.





A few things to bear in mind:





1. When using Derby in embedded mode only a single application can connect to the database at a time. e.g Squirrel or IJC, but not both. Shut down one before you start the other.





2. IJC contains a low level database client (the Netbeans database explorer) that can be used instead of Squirrel. Open the Services window to see it (but the same thing about connecting with only IJC or the database explorer still applies).





3. We are currently implementing trigger based fields in IJC, which should make these sorts of operations simpler in future.








Tim