formula results from the CPAN

formula

Chemistry-SQL

view release on metacpan or search on metacpan

	| MOL      |
	| mysql    |
	| test     |
	+----------+

I<* 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         |      |     |         |                |
	+-------------+--------------+------+-----+---------+----------------+

=cut

sub create_tables_mysql
{	my $self=shift;
	$self->{sql}="CREATE TABLE `listafter` (

lib/Chemistry/SQL.pm view on Meta::CPAN

	$self->{sql}="CREATE TABLE `listbefore` (
	`id` int(11) NOT NULL auto_increment,
	`smilesform` blob NOT NULL,
	PRIMARY KEY(`id`)
	)  ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute ;

	# Creating components Table
	$self->{sql}="CREATE TABLE `components` (
	`formula` varchar(250) NOT NULL default '',
	`id` int(11) NOT NULL auto_increment,
	`smilesform` blob NOT NULL,
	`description` blob NOT NULL,
	PRIMARY KEY  (`id`),
	KEY `formula` (`formula`)
	)  ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute ;

	# Creating Reactions Table	
	$self->{sql}="CREATE TABLE `reactions` (
 	`formula` varchar(255) NOT NULL default '',
	`smilesbefore` blob NOT NULL,
	`smilesafter` blob NOT NULL,
	`atommap_forward` blob NOT NULL,
	`atommap_reverse` blob NOT NULL,
	`id` int(11) NOT NULL auto_increment,
	`direction` tinyint(1) NOT NULL default '0',
	`description` blob NOT NULL,
	PRIMARY KEY  (`id`),
	KEY `formula` (`formula`)
	) ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql}) ;
	$self->{sth}->execute ;

	# Creating Results Table	
	$self->{sql}="CREATE TABLE `results` (
	`id` int(11) NOT NULL auto_increment,
	`q_name` varchar(255) NOT NULL default '',
	`formula` varchar(255) NOT NULL default '',
	`smilesbefore` blob NOT NULL,
	`smilesafter` blob NOT NULL,
	`atommap` blob NOT NULL,
	`idreact` int(11) NOT NULL default '0',
	`direction` tinyint(1) NOT NULL default '0',
	`is_root` tinyint(1) NOT NULL default '0',
	`level` int(11) NOT NULL default '0',
	PRIMARY KEY  (`id`),
	KEY `busquedakey` (`formula`,`idreact`),
	KEY `qnamekey` (`q_name`),
	KEY `levelkey` (`level`)
	)  ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql}) ;
	$self->{sth}->execute;	

	# Creating Solution Graph	
	$self->{sql}="CREATE TABLE `sgraph` (
	`id` int(11) NOT NULL auto_increment,
	`q_name` varchar(255) NOT NULL default '',
	`formula` varchar(255) NOT NULL default '',
	`smilesbefore` blob NOT NULL,
	`smilesafter` blob NOT NULL,
	`atommap` blob NOT NULL,
	`idreact` int(11) NOT NULL default '0',
	`direction` tinyint(1) NOT NULL default '0',
	`is_root` tinyint(1) NOT NULL default '0',
	`reaction_smiles` blob NOT NULL default '',
	`painted` tinyint(1) NOT NULL default '0',
	PRIMARY KEY  (`id`),
	KEY `busqueda` (`formula`,`idreact`),
	KEY `q_name` (`q_name`)
	)  ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql}) ;
	$self->{sth}->execute;	
	
	# Creating  quimics	
	$self->{sql}="CREATE TABLE `quimics` (
	`q_name` varchar(250) NOT NULL default '',
	`descripcio` blob NOT NULL,
	PRIMARY KEY  (`q_name`)

lib/Chemistry/SQL.pm view on Meta::CPAN


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

=cut

sub component_exist 	
{	my $self = shift;
	my ($component) = @_;
	my $result = 0;
	my $formula = $component->sprintf("%f");
	my $smilesform = $self->smiles_string($component);
	$self->{sql} = "SELECT smilesform,id FROM components where formula =
			'$formula'";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	while ((my @row = $self->{sth}->fetchrow_array)&&($result==0))
	{	if ($row[0] eq $smilesform) {$result=$row[1];}
	}
	$self->{sth}->finish;
	return $result;
}

=item $db->insert_component(component, description)

Inserts component in the components table.

=cut

sub insert_component 	
{	my $self=shift;
	my ($component,$description) = @_;
	my $formula = $component->sprintf("%f");
	my $smilesform = $self->smiles_string($component);
	if (($self->component_exist($component))==0)
	{	
		$self->{sql} = "INSERT INTO components
		(formula,smilesform,description)
		VALUES('$formula','$smilesform','$description')";
		$self->{sth}= $self->{dbh}->prepare($self->{sql});
		$self->{sth}->execute;
	}
	$self->{sth}->finish;
}

=item $db->inscomp_from_file(file)

Imports components from I<smilesformat> file.

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

=cut

sub inscomp_from_file
{	my $self=shift;
	my($file)=@_;
	my @mols = Chemistry::Mol->read($file, format => 'smiles');
	my $smilesform="";
	my $formula = "";
	my $repeat=0;
	my $ending_value = scalar(@mols) ;
	for(my $counter=0 ; $counter < $ending_value ; $counter++)
	{	$self->insert_component($mols[$counter],"");
	}		
	return 1;
}

=item $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 
	-------------------------------

* I<Examples:>

Returning all components in database:

	$db1->recover_comp("","") 

=cut

sub recover_comp
{	my $self=shift;
	my ($formula,$smilesform)=@_;
	my @list=();
	if ($formula eq "")
	{$self->{sql} = "SELECT smilesform FROM components";}
	else
	{$self->{sql} = "SELECT smilesform FROM components where formula=
			'$formula'";}
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	if ($smilesform eq "")
	{while (my $row = $self->{sth}->fetchrow)
	 {	push @list, Chemistry::Mol->parse($row, format => 'smiles');
	 }
	}
	else
	{while (my $row = $self->{sth}->fetchrow)
	 {	if ($row eq $smilesform)

lib/Chemistry/SQL.pm view on Meta::CPAN

=back

=head2 Reactions functions

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

=cut

=over 4

=item $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.

=cut


sub reaction_exist 	
{	my $self=shift;
	my ($smilesbefore,$smilesafter,$formula) = @_;
	my $result = 0;
	$self->{sql} = "SELECT smilesbefore,smilesafter,id FROM reactions 
	where formula = '$formula' ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	while (my @row = $self->{sth}->fetchrow_array)
	{	if (($row[0] eq $smilesbefore) && ($row[1] eq $smilesafter)) 
		{$result=$row[2];}
	}
	return $result;
}

=item $db->react_id(reaction)

lib/Chemistry/SQL.pm view on Meta::CPAN


=cut


sub react_id	
{	my $self=shift;
	my ($reaction) = @_;
	my $id;
	my $substrate = $reaction->substrate;
	my $product = $reaction->product;
	my $formula = $substrate->sprintf("%f");
	my $smilesbefore = $substrate->sprintf("%s");
	my $smilesafter = $product->sprintf("%s");
	$self->{sql} = "SELECT id,smilesbefore,smilesafter FROM reactions 
			where formula = '$formula'";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	while (my @row = $self->{sth}->fetchrow_array)
	{	if (($row[1] eq $smilesbefore) && ($row[2] eq $smilesafter))
		 {$id = $row[0];}
	}
	$self->{sth}->finish;
	return $id;
}

lib/Chemistry/SQL.pm view on Meta::CPAN

* Example of forward reaction insertion :

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

=cut

sub reaction_insert 	
{	my $self=shift;
	my ($reaction,$description,$direction) = @_;
	my $substrate = $reaction->substrate;
	my $formula = $substrate->sprintf("%f");
	my $product = $reaction->product;
	my $smilesbefore = $substrate->sprintf("%s");
	my $smilesafter = $product->sprintf("%s");
	my $atommapf;
	my $atommapr;
	my @map = $substrate->atom_map;
	$atommapf=split(//,@map);
	my @map = $product->atom_map;
	$atommapr=split(//,@map);
	if (($self->reaction_exist($smilesbefore,$smilesafter,$formula))==0)
	{	 $self->{sql} = "INSERT INTO reactions
		(formula,smilesbefore,smilesafter,atommap_forward,
		atommap_reverse,description,direction)
		VALUES('$formula','$smilesbefore','$smilesafter','$atommapf'
		,'$atommapr','$description','$direction')";
		$self->{sth} = $self->{dbh}->prepare($self->{sql});
		$self->{sth}->execute;	
	}
}

=item $db->string_react(id)

Returns the string of the reaction with a SMILES format.

lib/Chemistry/SQL.pm view on Meta::CPAN

			$sth->execute;
			$sth->finish;
		}
	}
	$self->{sql}= "delete from listafter";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	$self->{sth}->finish;
}

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

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

=cut

sub result_before_exists
{	my $self=shift;
	my ($formula,$smilesform,$qname)=@_;
	my $result = 0;
	$self->{sql} = "SELECT smilesbefore FROM results where formula = 
			'$formula' AND q_name='$qname'";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	while (my $rows=$self->{sth}->fetchrow)
	{	if ($rows eq $smilesform)
		{$result=1;}	
	}
	return $result;
}

=back

lib/Chemistry/SQL.pm view on Meta::CPAN


=cut

sub gsg_fsc
{	my $self=shift;
	my ($startcomp)=@_;
	my $smilesform;
	my $component;
	my @child;my $rcomponent;
	foreach $component(@$startcomp)
	{	my $formula=$component->sprintf("%f");
		$smilesform=$self->smiles_string($component);
		my $sql = "SELECT smilesbefore,smilesafter,idreact,direction,
			atommap FROM results where formula = '$formula'";
		my $sth = $self->{dbh}->prepare($sql);
		$sth->execute;
		# Create the root Graph Nodes
		my $counter=0;
		while (my @row = $sth->fetchrow_array)
		{	# creem el component
			 if ($row[0] eq $smilesform)
			 {	
			 	push @child,$row[1];
				$self->sgraph_insert($formula,$row[0],$row[1],
					$row[2],$row[3],$row[4],"","1");
			 }
		}
		# Get all the others
		# Firts Get the Child Components, 
		# when detect a new child it is added in the list
		foreach $rcomponent(@child)
		{	my $component = Chemistry::Mol->parse($rcomponent, 
					format => 'smiles');
			my $formula=$component->sprintf("%f");
			my $smilestring=$self->smiles_string($component);
			my $sql  = "select smilesbefore,smilesafter,idreact,
				   direction,atommap from results where 
				   formula = '$formula' ";
			my $sth = $self->{dbh}->prepare($sql);
			$sth->execute;
			while (my @row = $sth->fetchrow_array)
			{	if ($row[0] eq $smilestring)
				{	if (!($self->sgraph_exist($formula,
						$row[0],$row[1],$row[2])))	
					{	push @child,$row[1];
						$self->sgraph_insert($formula,
						$row[0],$row[1],$row[2],
						$row[3],$row[4],"","0");
					}
				}
			}
		}
	}
}

=item $db->gsg_fec(endcomp)

lib/Chemistry/SQL.pm view on Meta::CPAN

=cut

sub gsg_fec
{	my $self=shift;
	my ($endcomp)=@_;
	my $smilesform;
	my $component;
	my @fathers;my $rcomponent;
	my $invdir;#inverted direction
	foreach $component(@$endcomp)
	{	my $formula=$component->sprintf("%f");
		$smilesform=$self->smiles_string($component);
		my $sql = "SELECT smilesbefore,smilesafter,idreact,direction,
			atommap FROM results where formula = '$formula'";
		my $sth = $self->{dbh}->prepare($sql);
		$sth->execute;
		# Create the root Graph Nodes
		my $counter=0;
		while (my @row = $sth->fetchrow_array)
		{
			 if ($row[1] eq $smilesform)
			 {	
			        if ($row[3]==1) 
			 	{$invdir=0;}
				else
				{ if ($row[3]==0)
				  {$invdir=1;}
				}
			 	push @fathers,$row[0];
				$self->sgraph_insert($formula,$row[1],$row[0],
				$row[2],$invdir,$row[4],"","1");
			 }
		}
		
		# Get all the others
		foreach $rcomponent(@fathers)
		{	my $component = Chemistry::Mol->parse($rcomponent, 
					format => 'smiles');
			my $formula=$component->sprintf("%f");
			my $smilestring=$self->smiles_string($component);
			my $sql  = "select smilesbefore,smilesafter,idreact,
					direction,atommap from results where 
					formula = '$formula' ";
			my $sth = $self->{dbh}->prepare($sql);
			$sth->execute;
			while (my @row = $sth->fetchrow_array)
			{	if ($row[1] eq $smilestring)
				{	 if ($row[3]==1) 
			 		 {$invdir=0;}
					 else
					 { if ($row[3]==0)
				  	   {$invdir=1;}
					 }
					if (!($self->sgraph_exist($formula,
						$row[1],$row[0],$row[2])))
					{	push @fathers,$row[0];
						$self->sgraph_insert($formula,
						$row[1],$row[0],$row[2],
						$invdir,$row[4],"","0");
					}
				}
			}
		}
	}
}

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

lib/Chemistry/SQL.pm view on Meta::CPAN

I<initlevel>: First level 

I<endlevel>: Last level to draw

=cut


sub gsg_levels
{	my $self=shift;
	my ($qname,$initlevel,$endlevel)=@_;
	my $sql = "SELECT level,formula,smilesbefore,smilesafter,idreact,
		direction,atommap FROM results where q_name='$qname' 
		AND level >= '$initlevel' AND level<='$endlevel'";
	my $sth = $self->{dbh}->prepare($sql);
	$sth->execute;
	while (my @row = $sth->fetchrow_array)
	{	if($row[0]==$initlevel)
		{$self->sgraph_insert($row[1],$row[2],$row[3],$row[4],$row[5],
					$row[6],$qname,"1");	
		}
		else

lib/Chemistry/SQL.pm view on Meta::CPAN


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

=cut


sub gsg_complete
{	my $self=shift;
	my ($qname)=@_;
	my $sql = "SELECT formula,smilesbefore,smilesafter,idreact,
	direction,atommap,is_root FROM results where q_name='$qname'";
	my $sth = $self->{dbh}->prepare($sql);
	$sth->execute;
	while (my @row = $sth->fetchrow_array)
	{	$self->sgraph_insert($row[0],$row[1],$row[2],$row[3],$row[4],
					$row[5],$qname,$row[6]);	}
	$self->{sth}->finish;
}

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

Checks if a result is in the solution graph table

=cut


sub sgraph_exist
{	my $self=shift;
	my ($formula,$smilesbefore,$smilesafter,$idreact) = @_;
	my $result=0;
	$self->{sql} = "SELECT smilesbefore, smilesafter FROM sgraph where 
			formula = '$formula'  AND idreact = '$idreact' ";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	#Finding for a equal smilesbefore and smilesafter strings
	while (my @row = $self->{sth}->fetchrow_array)
	{	if (($row[0] eq $smilesbefore) && ($row[1] eq $smilesafter)) 
		{$result=1;}
	}
	# 0 if not exist
	$self->{sth}->finish;
	return $result;
}

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

Solution graph insertion.

=cut


sub sgraph_insert
{	my $self=shift;
	my ($formula,$smilesbefore,$smilesafter,$idreact,$direction,$atommap,
	$qname,$is_root) = @_;
	if (!($self->sgraph_exist($formula,$smilesbefore,$smilesafter,$idreact)))
	{	# Get the Reaction Properties and Insert into the 
		# Result Graph Table
		my $sql = "Select smilesbefore,smilesafter from reactions  
				where id = $idreact";
		my $sth =  $self->{dbh}->prepare($sql);
		$sth->execute;
		my $smilesreaction;
		if (my @row=$sth->fetchrow_array)
		{	$smilesreaction = $row[0].">>".$row[1];
		}
		$sth->finish;
		$self->{sql} = "INSERT INTO sgraph
		(formula,smilesbefore,smilesafter,idreact,direction,atommap,
		q_name,is_root,reaction_smiles)
		VALUES('$formula','$smilesbefore','$smilesafter','$idreact',
		'$direction','$atommap','$qname','$is_root','$smilesreaction')";
		$self->{sth} = $self->{dbh}->prepare($self->{sql});
		$self->{sth}->execute;
		$self->{sth}->finish;
		return 1;
	}
	else 
	{return 0;}
}

=back

=head2 Result functions

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

=cut

=over 4

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

Inserts a result in the database.

=cut

sub result_insert
{	my $self=shift;
	my ($formula,$smilesbefore,$smilesafter,$idreact,$direction,$atommap,
	$qname,$is_root,$level) = @_;
	if ($smilesbefore eq $smilesafter) {return 0;}
	
	if (!($self->results_exist($formula,$smilesbefore,$smilesafter,
	$idreact,$qname)))
	{	$self->{sql} = "INSERT INTO results
		(formula,smilesbefore,smilesafter,idreact,direction,atommap,
		q_name,is_root,level)
		VALUES('$formula','$smilesbefore','$smilesafter','$idreact',
		'$direction','$atommap','$qname','$is_root','$level')";
		$self->{sth} = $self->{dbh}->prepare($self->{sql});
		$self->{sth}->execute;
		$self->{sth}->finish;
		return 1;
	}
	else 
	{return 0;}
}

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

Checks if the result already exists in the database.

=cut

sub results_exist
{	my $self=shift;
	my ($formula,$smilesbefore,$smilesafter,$idreact,$qname) = @_;
	my $resultado=0;
	$self->{sql} = "SELECT smilesbefore, smilesafter FROM results where
			formula = '$formula'  AND idreact = '$idreact' 
			AND q_name='$qname'";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	while (my @row = $self->{sth}->fetchrow_array)
	{	if ((@row[0] eq $smilesbefore) && (@row[1] eq $smilesafter)) 
		{$resultado=1;}
	}
	$self->{sth}->finish;
	return $resultado;
}

lib/Chemistry/SQL.pm view on Meta::CPAN


=cut


sub graphic_information	
{	my $self = shift;
	# @_ is the quimics name
	my ($id,$component) = @_;
	my @info;
	my $smilesform = $self->smiles_string($component);
	my $formula = $component->sprintf("%f");
	$self->{sql} = "select smilesbefore,direction,atommap,reaction_smiles 
			from sgraph where id='$id'";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	if (my @row = $self->{sth}->fetchrow_array)
	{		push @info, $smilesform;
			push @info, $formula;
			push @info, $row[0];
			push @info, $row[1];
			push @info, $row[2];
			push @info, $row[3];	
	}
	return \@info;
}

=item $db->rec_root()

lib/Chemistry/SQL.pm view on Meta::CPAN


=item $db->rec_child(component)

Returns all the child components from one component.

=cut

sub rec_child 
{	my $self = shift;
	my ($component)=@_;
	my $formula = $component->sprintf("%f");
	my $smilesform = $self->smiles_string($component);
	$self->{sql} = "select id,smilesbefore,smilesafter from sgraph 
			where formula = '$formula' and painted=0";
	$self->{sth} = $self->{dbh}->prepare($self->{sql});
	$self->{sth}->execute;
	my @result;my @ids;
	while (my @row = $self->{sth}->fetchrow_array)
	{	# creem el component
		 if ($row[1] eq $smilesform)
		 {push @result, Chemistry::Mol->parse($row[2], 
		 		format => 'smiles');
		  push @ids,$row[0];
		  push @result,$row[0]; }

( run in 0.928 second using v1.01-cache-2.11-cpan-3cd7ad12f66 )