SQL - Access Database Functions Module


        use strict;
        use Chemistry::SQL;
        use Chemistry::Artificial::SQL;
        my $db_name = $ARGV[0];
        my $file = $ARGV[1];
        my $db1 = Chemistry::SQL::new(db_host=>"",db_user=>"root",db_port=>"3306",db_pwd=>"",
        if ($db1->db_exist) 
        {       $db1->connect_db;
        # Reaction Insertion
        my $qart = Chemistry::Artificial::SQL::new($db1);
        my $qr =$qart->q_reaccion('C=CC=C.C=C>>C1=CCCCC1','smiles');


This package provides the necessary functions to interact with the database. The methods implemented in this module are oriented to give users control of the database without knowing how to use SQL queries.

SQL Attributes

There are some attributes in the Chemistry::SQL object:

        * host: IP Address where the database is located.
        * user: User given to connect_db the database.
        * port: Mysql Port (Default 3306).
        * pwd: User's password to access to the database
        * db_name: Database name that will be used during the application.
        * driver: Driver used while trying to connect_db to database 


The methods of SQL Object are:

SQL->new(name => ...)

Creates a new SQL object with the specified attributes. Example:

    my $db1 = Chemistry::SQL::new(db_host=>"",db_user=>"root",db_port=>"3306",db_pwd=>"",

Database functions

The functions used to acces to the database are:


This function decides if the self::{db_name} exists in the database server. The function returns 1 when the database name exists, else return 0.


Creates a database with $self-{db_name}> name. It firts tests if the database exists; returning 1 when database is been created successfully, else return 0


connect_dbs to the database with $self-{db_name}> name.


Delete Database with $self-{db_name}> name.


Creates all tables necessary to work with the other modules (see Chemistry::Artificial::SQL and Chemistry::Graphics).


* Example of a connect_dbion and creation of a database structure:

        import Chemistry::SQL;
        use strict;
        my $db = Chemistry::SQL::new("","root","3306","","MOL",
        if (!($db1->db_exist)) 
        {       $db1->connect_db;

Before execution of the function:

        | Database |
        | mysql    |
        | test     |

After execution of the function:

        | Database |
        | MOL      |
        | mysql    |
        | test     |

* Example of the components table estructure:

        | Field       | Type         | Null | Key | Default | Extra          |
        | formula     | varchar(250) |      | MUL |         |                |
        | id          | int(11)      |      | PRI | NULL    | auto_increment |
        | smilesform  | blob         |      |     |         |                |
        | description | blob         |      |     |         |                |

Cleans the selected table.


Erases all tables in the database $self-{db_name}>. This function is used to clean a complete database.

* Example of cleaning a database:

        use Chemistry::SQL;
        my $db1 = Chemistry::SQL::new("","root","3306","",
        if ($db1->db_exist) 
        {       $db1->connect_db;

Components functions

These functions are used to work with the components and the database; and they are:


Returns SMILES string of the component.

It is often used to get the SMILES string of the components in the function.


Returns SMILES format string of the component.


Checks if the component already exist in the database.

This function is used in the insertion of components before to inserting them, because it checks if them already exist.

$db->insert_component(component, description)

Inserts component in the components table.


Imports components from smilesformat file.

This function imports a data.smi file into the components table. It is often used in the initialitzation of the database.

$db->recover_comp(formula, smilesform)

This function returns the components.

Options of recover_comp function:

        |formula      |   smilesform  | Result
        |  blank      |   ------      | All components returned 
        |  value set  |   blank       | All components with formula parameter
        | value set   |   value set   | smilesform component is returned 

* Examples:

Returning all components in database:


Reactions functions

Functions to work with the reactions and the database, and they are:

$db->reaction_exist(smilesbefore, smilesafter, formula)

It tests if the reaction described is in the database selecting the formula reaction, and test if smilesbefore and smilesafter are the same that the parameters describe.


Gets the reaction id of the reaction.

        my $reactionID = $db1->react_id($r);

Returns the reaction direction. This function is used during the cha generation to know reaction's direction and how to apply the reaction.

        my $reactionID = $db1->react_id($r);
        my $reactiondir= $db->reacDIR($reactionID);
$db->rection_insert(reaction, description, direction)

Inserts a reaction in the database.

Before inserting the reaction it is tested if the reaction already exists.

The direction is a number between 0 and 2

0=> Forward Reaction.

1=> Reverse Reaction.

2=> Bidirectional Reaction.

* Example of forward reaction insertion :

        $db1->rection_insert($r,"Description of my Reaction",0)

Returns the string of the reaction with a SMILES format.


Recovers all the reactions from the reactions table. This function returns an reference array.

CHA functions

This functions are used to work with artificial chemistry and database. They are:


Querys the database if the cha name exist in the database. This function is used in the module to test if qname exist before inserting a new cha.

        if (!($self->ch_exist($qname)))
$db->new_ch(qname, description)

Inserts cha name and description in the database. When a cha is created, a new table is created in the database.

        $db1->new_ch("cha_name","Description of the cha");
$db->id_artificial(type, qname)

When a i<cha> is created, a new table with <cha> name is created too. In this table are stored components and reactions that will be used in the CHA generation.

This function returns the id's of the selected type.

Type must be 'R' for reactions and 'C' for components.

qname is the name of cha.

* Example to get all the component id from the TESTCHA.

        my $idcomp = $db1->id_artificial("C","TESTCHA");
        foreach $component(@$idcomp)
        { print $db->string_comp($component);}

When a new component or reaction is inserted in a cha table, it is checked before being inserted.

$db->insert_art_react(index, qname)

Inserts reactions in the cha table.


Inserts components in the cha table.


Returns all the chas in the database.

This function is used during the clenaning of the database, because it lists a detail of all the tables that will be dropped.


Next level component is inserted in the lista_after_in. It is used because some cha generations can be bigger than the memory, and then the next level is stored in a table and recovered to be processed when is necesary.


Recovers the next level components in a cha generation.

The variable size defines how many elements can be in the memory. When it recovers the elements of the next level, it returns the number of elements especified in the size variable.


This function inserts components in list_before table.


Recovers the actual level components in a cha generation.

The size variable defines how much elements can be in memory, when it recovers, the elements of the level, the function returns the number of elements defined in size variable.


Checks if the list_before is empty, returning 1 if it is empty.


Moves all after_table data to before_table data setting up the next level to explore

$db->result_before_exists(formula, smilesform, qname)

Test for one component in cha if the result already has been calculated.

Graphic generation functions

These functions are used to work with the sgraph table, and they are:


Generates solution graph from start components.

This function inserts the data into the solution graph table to plot with the graphic module a graphic solution.

startcomp is an array reference with all the components to process.


Generate solution Graph from end components

This function inserts the data into the solution graph table to plot with the graphic module a graphic solution.

endcomp is an array reference with all the components to process.

$db->gsg_levels(qname, initlevel, endlevel)

Generate solution graph levels

Insert the necessary data into graph solution table.

qname: Cha to draw

initlevel: First level

endlevel: Last level to draw


Generate solution graph complete.

Generates a complete cha solution graph, inserting data into solution graph table.

$db->sgraph_exist(formula, smilesbefore, smilesafter, idreact)

Checks if a result is in the solution graph table

$db->sgraph_insert(formula,smilesbefore,smilesafter,idreact,direction, atommap,q_name,is_root)

Solution graph insertion.

Result functions

These functions are used to work with the results generated, and they are:

$db->resultinsert(formula, smilesbefore, smilesafter, idreact, direction, atommap, qname, is_root, level)

Inserts a result in the database.

$db->resultexist(formula, smilesbefore, smilesafter, idreact, qname)

Checks if the result already exists in the database.


Returns information about the components to plot the graph.


Recovers the root components.


Returns all the child components from one component.





The PerlMol website


Bernat Requesens <>.


This program is free software; so it can be redistributed and/or modified under the same terms as Perl itself.