view release on metacpan or search on metacpan
lib/Chemistry/SQL.pm view on Meta::CPAN
203204205206207208209210211212213214215216217218219220221222223
| 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
231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311$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
450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560This 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
568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602=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
607608609610611612613614615616617618619620621622623624625626627628629630=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
668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701* 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
11101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141
$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
11591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214=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
1223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289=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
129712981299130013011302130313041305130613071308130913101311131213131314131513161317I<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
132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469Generates 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
147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500=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
152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545=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]; }