chapter 12

More On Client and Web Interfaces To RDBMS

Most of the emphasis in this book has been on ways of using SQL and RDBMS. Using functions written in SQL and other languages greatly extends the capabilities of existing RDBMS for handling molecular structures. Because the RDBMS is run as a server, a client program is necessary to interact with the RDBMS. Chapter 5 introduced several common client programs to do this. When developing a more complex system, existing applications may not satisfy the needs of the project. In that case, it becomes necessary to develop new client programs to interact with the RDBMS.

This chapter discusses ways in which more complex client applications can be written. These programs use SQL to select, insert, delete or update table in the database. [view wordle] [hide wordle]

Page  
141 
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host", $username, $password); 
while (($id, $ic50, $ed50) = &get_data()) { 
  my $sql = "Insert Into test_assay (id, ic50, ed50) Values ($id, $ic50, $ed50)"; 
  my $sth = $dbh->prepare($sql); 
  my $rv = $sth->execute; 
}

my $sql = "Insert Into test_assay (id, ic50, ed50) Values (?,?,?)";          
my $sth = $dbh->prepare($sql); 
while (($id, $ic50, $ed50) = &get_data()) { 
  last if ($id < 1);
  my $rv = $sth->execute($id, $ic50, $ed50); 
}
142 
PreparedStatement st = con.prepareStatement("Insert Into test_assay (id,ic50,ed50) Values (?,?,?)"); 
while ( true ) {
  data_values = get_data(); 
  if ( data_values[0] < 1 ) break;
  st.setInt (1, (int) data_values[0]); 
  st.setDouble(2, data_values[1]); 
  st.setDouble(3, data_values[2]); 
  st.executeUpdate(); 
}

$sql = "Insert Into test_assay (id,ic50,ed50) Values ($1,$2,$3)";        
$stmt = pg_prepare($dbconn, "test_assay_insert", $sql); 
while ( $data_values = get_data() ) { 
  if ( $data_value[0] < 1 ) break;
  $rv = pg_execute($dbconn, "test_assay_insert", $data_values);        
}
143 
my $sql = "Select smiles,cas from nci.structure where gnova.matches(smiles,'c1ccccc1C(=O)NC') limit 20"; 
my $sth = $dbh->prepare($sql); 
my $rv = $sth->execute; 
while (my @row = $sth->fetchrow_array()) { 
   print join "\t",@row; 
   print "\n"; 
} 

my $sth = $dbh->prepare($sql); 
my $rv = $sth->execute; 
my $data = $sth->fetchall_arrayref(); 
while ( my $row = shift(@$data) ) { 
  print join "\t",@$row; 
  print "\n"; 
} 

my $sth = $dbh->prepare($sql); 
my $rv = $sth->execute; 
$sth->bind_columns(\$smiles, \$cas); 
while ($sth->fetchrow_array()) { 
  print "$smiles\t$cas\n"; 
}
144 
Create Or Replace Function marvin_sketch() Returns Text As $EOSQL$ 
Select ' 
  <script LANGUAGE="JavaScript1.1"> 
    msketch_name = "MSketch"; 
    msketch_begin("/marvin", 200, 200); 
    msketch_param("background", "#EEFFDD"); 
    msketch_param("menubar", "false"); 
    msketch_param("molFormat", "smiles"); 
    msketch_param("importConv", "-a"); 
    msketch_param("detach", "hide"); 
    msketch_end(); 
    function get_smiles() { 
      smi = MSketch.getMol("smiles:a"); 
      return smi; 
    } 
  </script> 
'::Text; 
$EOSQL$ Language SQL; 

<HTML> 
<HEAD> 
<TITLE>Sample PHP web app example</TITLE> 
<SCRIPT SRC="http://www.chemaxon.com/marvin/marvin.js"></SCRIPT>        
</HEAD> 
<BODY> 
<?php print marvin_sketch(); ?> 
</BODY> 
</HTML> 
<?php 
function marvin_sketch() { 
  $dbconn = pg_connect("host=localhost dbname=book user=reader”);        
  $res = pg_exec($dbconn, "select marvin_sketch()"); 
  $row = pg_fetch_array($res); 
  pg_close($dbconn); 
  return $row["marvin_sketch"]; 
} 
?>
145 
Create Or Replace Function get_fields(text,text) Returns Setof Record As $EOSQL$
  Declare
    col_types Record;
  Begin
    For col_types In
      SELECT attname::text, typname::text
       FROM  pg_attribute
        Join pg_class On attrelid = pg_class.oid
        Join pg_namespace On relnamespace = pg_namespace.oid
        Join pg_type On atttypid = pg_type.oid
       Where attnum > 0 And nspname = $1 And relname = $2
     Loop
       Return Next col_types;
     End Loop;
  End;
$EOSQL$ Language plpgsql;
<HTML> 
<HEAD> 
<TITLE>Sample PHP web app example</TITLE> 
<SCRIPT SRC="http://www.chemaxon.com/marvin/marvin.js"></SCRIPT>
</HEAD> 
<BODY> 
<FORM> 
<?php 
print marvin_sketch(); 
print "<p><input type=submit value=search>"; 
print "pubchem.nci_h23"; 
foreach (get_columns('pubchem', 'nci_h23') as $key => $value) { 
  print "<BR><input type=checkbox name=${key}>${key}\n";          
} 
?> 
</FORM> 
</BODY> 
</HTML> 
<?php 
function get_columns($schema,$table) { 
  $dbconn = pg_connect("host=localhost dbname=book user=reader");          
  $sql = "select * from get_fields('${schema}', '${table}') as (colnam text, typnam text)"; 
  $result = pg_query($dbconn, $sql); 
  $columns = array(); 
  while ( $row = pg_fetch_array($result) ) { 
    $colnam = $row['colnam']; 
    $columns[${colnam}] = $row['typnam']; 
  } 
  pg_close($dbconn); 
  return $columns; 
} 
?>
146 
Create Or Replace Function marvin_view(text, text) Returns Text As $EOSQL$
Select '
   <script LANGUAGE="JavaScript1.1">
    msketch_name = "MView";
    mview_begin("/marvin", 200, 200);
    mview_param("colorScheme", "atomset");
    mview_param("mol", "' || $1 || '");
    mview_param("AtomSet0.1", "' ||
    array_to_string(list_matches($1, $2),',') ||'");
    mview_end();
   </script>
'::Text;
$EOSQL$ Language SQL;
147 
require("RODBC");
channel = odbcConnect("PostgreSQL30", uid="reader", case="postgresql");
sql = "Select 
  Case When a.id < b.id Then tanimoto(a.gfp,b.gfp)
    Else null
  End
  from xlogp.test_set as a, xlogp.test_set as b";
tani = sqlQuery(channel, sql, max=0);
n = sqrt(length(tani[,1]))
tanimoto = as.dist(matrix(tani[,1], nrow=n, ncol=n));
fit = hclust(1.0-tanimoto, method="ward");
plot(fit);
fit = hclust(1.0-tanimoto, method="single");
plot(fit);
close(channel);
148 
require("RODBC");
channel = odbcConnect("PostgreSQL30", uid="reader",case="postgresql");

# get experimental logp from training_set
sql = "Select logp from xlogp.training_set order by id";
logpval = sqlQuery(channel, sql, max=0);
ntrain = length(logpval$logp);# get smarts
sql = "select smarts,train_freq from xlogp.simplex where train_freq > 1 order by train_freq desc";
smarts = sqlQuery(channel, sql, max=0);

# match each smiles in the training_set to each fragment smarts
sql = "Select count_matches(smiles,smarts) as matches from
  (select smarts, train_freq from xlogp.simplex where train_freq > 1) as smarts,
  (select smiles, id from xlogp.training_set order by id) as train
     order by train_freq desc, smarts, id";
count = sqlQuery(channel,sql,max=0);
m = matrix(count$matches, nrow=ntrain);

# fit the experimental logP values to the matched fragment counts
logpfit = lm(data.frame(logpval, m));
summary(logpfit);
plot(fitted(logpfit), logpval[[1]], main="simplex smarts", ylab='experimental value', xlab='predicted value');
# create data frame of smarts and coefficients and store in a table
dt = data.frame(c(NA,as.vector(smarts$smarts)), coef(logpfit), summary(logpfit)$coefficients[,2]);
names(dt) = c('smarts', 'contribution', 'error');
sqlSave(channel,dt,table='simplex_coefficients');close(channel);
149  
150  
151 
Select sum(contribution*count_matches('c1ccccc1C(=O)NC',smarts)) - 0.262592 as glogp from simplex_coefficients
Create Function glogp(text) Returns Numeric As $EOSQL$
  Select (sum(contribution*gnova.count_matches($1,smarts))-0.262592)::numeric(5,3) From simplex_coefficients; 
$EOSQL$ Language SQL;
152 
Select glogp('c1ccccc1C(=O)NC');
Select smiles, glogp(smiles) from structure;