Program Objects and DayCart

  1. Introduction
  2. Progob Directory
  3. Progob Table
  4. Shell Wrapper
  5. PL/SQL Wrapper
  6. Pipetalk Wrapper
  7. Execution in PL/SQL

  1. Introduction

    2. Program Objects ...

    The PipeTalk Protocol ...

  2. The Progob Directory

    3. Note/Example Command/Code
    Example Program Object scripts and programs are included in the Daylight distribution.   
    <--- Some entries may be omitted --->
% ls ${DY_ROOT}/bin/*talk 
  clogptalk 
  cmrtalk
  dayproptalk
  merlinsmartstalk
% ls ${DY_ROOT}/contrib/src/oracle/*talk*
  clogptalk.sh
  echotalk.sh
% ls ${DY_ROOT}/contrib/src/oracle/mdl/*talk*
  mol2smitalk
  mol2smitalk.sh
  smi2moltalk
  smi2moltalk.sh
% ls ${DY_ROOT}/contrib/src/c/progob
  add_clogp 
  clogpaccess
  echotalk
  fingertalk
  merlinbintalk
  pipetalker
  shelltalk
  stringtalker
  verbosetalk
     
    A Program Object directory should be created at ${ORACLE_BASE}/progob and owned by the Oracle user. 
    % su - oracle
% mkdir ${ORACLE_BASE}/progob
    Program Object scripts may be copied in from the Daylight installation directories.   
    % cp ${DY_ROOT}/contrib/src/oracle/clogptalk.sh ${ORACLE_BASE}/progob
     
    To prevent access to unauthorized Oracle privileges, the program object directory and files should't be group or world writable.   
    % chmod -R 700 ${ORACLE_BASE}/progob
     

  3. The Progob Table

    4. Note/Example Command/Code
    Program Objects are defined in the c$dcischem.progob dictionary table.   
    SQL> desc progob
 Name                       Null?    Type
 -------------------------- -------- -----------------
 NAME                                VARCHAR2(60)
 PATH                                VARCHAR2(4000)
 ARGS                                VARCHAR2(4000)
    A new Program Object is added by defining a symbolic name ('clogp'), an absolute path to the shell wrapper ('/oracle/progob/clogptalk.sh') and the initial (whitespace delimited) arguments (NULL). 
    SQL> insert into progob values ('clogp', 
      '/oracle/progob/clogptalk.sh', NULL); 

SQL> select * from progob;

NAME
---------------------------------------------
PATH
--------------------------------------------------
ARGS
--------------------------------------------------
clogp
/oracle/progob/clogptalk.sh

  4. Shell Wrapper

    5. Note/Example Command/Code
    Since Oracle executes the program object with an empty environment, all required variables must be defined in a shell wrapper before calling the program object. 
    % cat /oracle/progob/clogptalk.sh
  #!/bin/sh

  DY_ROOT=/usr/local/daylight/v481
  export DY_ROOT

  DY_LICENSEDATA=/usr/local/daylight/dy_license.dat
  export DY_LICENSEDATA

  LD_LIBRARY_PATH=/usr/lib:${DY_ROOT}/lib
  export LD_LIBRARY_PATH

  ${DY_ROOT}/bin/clogptalk
    The Program Object setup can be verified by running it from the command line with an empty environment. 
     
% /usr/bin/env -i /oracle/progob/clogptalk.sh
  Qwerty: Over.
  Qwerty: Say VERSION.
  Qwerty: Over.
  4.82
  Qwerty: Over.  
  CCO
  Qwerty: Over.
  CCO -0.235 0 LogPstar: -0.31 
  Qwerty: Over.

  5. PL/SQL Wrapper

    6. Note/Example Command/Code
    Since program objects may return data types which contain several lines of data (VARCHAR2 or CLOB), most require a PL/SQL wrapper function to convert to line-oriented data and to parse returned results.   
    <--- Excerpt from ${DY_ROOT}/contrib/src/oracle/contrib_create.sql --->
SQL> create function fclogp (sosdata in varchar2) return number
   as
    v1 varchar2(4000);
    rc number;
    off1 number;
    off2 number;
   begin
    v1 := c$dcischem.ddpackage.fprogob('clogp', sosdata);
    off1 := instr(v1, ' ', 1, 1);
    off2 := instr(v1, ' ', 1, 2);
    rc := to_number(substr(v1, off1, off2 - off1));
    return rc;
   end;
/

  6. Pipetalk Wrapper

    7. Note/Example Command/Code
    PipeTalk compatibility has been written directly into several supported binary "talk" executables. 
    % ${DY_ROOT}/bin/clogptalk
  Qwerty: Over.
  Qwerty: Say MSGLIST.
  Qwerty: Over.
  Qwerty: Say HELP.
  Qwerty: Say PROGRAM.
  Qwerty: Say VERSION.
  Qwerty: Say MSGLIST.
  Qwerty: Say NOTICE.
  Qwerty: Say ERRORTEXT.
  Qwerty: Say FRAGDB.
  Qwerty: Set TABLEOUTPUT.
  Qwerty: Set TABLE0.
  Qwerty: Set TABLE1.
  Qwerty: Set TABLE2.
  Qwerty: Set TABLE3.
  Qwerty: Set NOTABLEOUTPUT.
  Qwerty: Set TDT.
  Qwerty: Set INTEGERERRORS.
  Qwerty: Set ENGLISHERRORS.
  Qwerty: Over.
    A shell wrapper can parse PipeTalk messages and respond with the appropriate action.   
    <--- Some lines are omitted --->
% cat ${DY_ROOT}/contrib/src/oracle/echotalk.sh
  #!/bin/sh
  echo "echotalk.sh version 1.0"
  echo "Qwerty: Over."
  while { read line;}
    do
    if [ "$line" = "Qwerty: Over and out." ] ; then
      exit
    fi
    while { read junk;}
    do
      if [ "$junk" = "Qwerty: Over." ] ; then
        break
      fi
    done
    case "$line" in
         "Qwerty: Say HELP.") echo "echotalk.sh - echos it's input";;
         "Qwerty: Say PROGRAM.") echo "echotalk.sh";;
         "Qwerty: Say VERSION.") echo "Version 1.0.";;
          *) echo "Args: '$*' Current line is: '$line'";;
     esac
  echo "Qwerty: Over."
  done
  exit
     
    PipeTalk compatibility can be added into C programs as described in the Program Object section of the Toolkit Programmers Guide 
    #include <stdio.h>
#include <string.h>
#include "dt_smiles.h"
#include "dt_progob.h"

int main(int argc, char *argv[])
{
  dt_Handle mol;
  int       lencan;
  char      *out_string, line[1000];
  printf(DX_PT_EOM"\n");
  while (gets(line)) {
    if (0 == memcmp(DX_PT_EOT, line, strlen(line))) {
      return 0; }
    else if (0 == memcmp(DX_PT_HELP, line, strlen(line))) {
      out_string = "Enter only valid SMILES."; }
    else if (0 == memcmp(DX_PT_PROGRAM, line, strlen(line))) {
      out_string = "cansmitalk."; }
    else if (0 == memcmp(DX_PT_VERSION, line, strlen(line))) {
      out_string = "1.0"; }
    else if (0 == memcmp(DX_PT_EOM, line, strlen(line))) {
      printf("%.*s\n", strlen(out_string), out_string);
      printf(DX_PT_EOM"\n");
    }
    else {
      mol = dt_smilin(strlen(line), line);
      out_string = dt_cansmiles(&lencan, mol, 0);
      dt_dealloc(mol);
    }
  }
}
    Contributed libraries can make it easier to implement Program Objects.   
    <--- Comment lines are omitted --->
% cat  more ${DY_ROOT}/contrib/src/c/progob/echotalk.c 

  #include "dt_smiles.h"
  #include "dt_progob.h"
  #include "du_pipeio.h"
  
  static dt_Handle copysos(dt_Handle sosin)
  {
     dt_Handle sob, sosout = dt_alloc_seq();
     dt_reset(sosin);
     while (NULL_OB != (sob = dt_next(sosin)))
        dt_append(sosout, dt_copy(sob));
     dt_reset(sosout);
     return sosout;
  }
  static dt_Handle mytalk(dt_Handle msgin)
  {
     return (NULL_OB == msgin ? NULL_OB : copysos(msgin));
  }
  main()
  {
     du_pipe_loop(NULL_OB, mytalk);
  }

  7. Execution in PL/SQL

    8. Note/Example Command/Code
    Program Objects may be executed using the fprogob function using the symbolic name as the first argument and the message as the second argument.  
    SQL> select ddpackage.fprogob('clogp', 'c1ccccc1') from dual;

  DDPACKAGE.FPROGOB('CLOGP','C1CCCCC1')
  -----------------------------------------------
  c1ccccc1 2.142 0 LogPstar: 2.13
    If a PL/SQL wrapper function has been created, it may be executed with the message as the only argument.   
      SQL> select fclogp('c1ccccc1') from dual;

  FCLOGP('C1CCCCC1')
  ------------------
               2.142
     
    Program Objects can be used in conjunction with triggers to automatically generate derived column values. 
    SQL> create or replace trigger add_logp
  before insert or update
on example_table
for each row
begin
  :new.clogp := fclogp(:new.usmiles);
end;
/