# $Id: merged_schema.tcl,v 1.4 2004/11/05 17:44:24 hume Exp $
#
# Create merged SQL tables that can be used for either host or equip
# with the Hume GEM applications
#
#
# TBD - possibly integrate additional fields used to manage flow of 
#       data and mapping of data between host(s) and equipment
#
# $Log: merged_schema.tcl,v $
# Revision 1.4  2004/11/05 17:44:24  hume
# Max variable name length bumped to 256 from 32.
#
# Revision 1.3  2004/10/18 22:00:11  hume
# Host VariableUpdate, autoInit additions
#
# Revision 1.2  2004/10/13 18:34:21  hume
# Changes to accomodate common use with SecsHost.
#
# Revision 1.1.1.1  2004/09/07 19:03:13  hume
# First checkin.
#
#


proc  merged_schema {} {

   # ALARM tables
   SQL "create table ei_alarm (spname varchar(32),ALID varchar(20), \
 eqname varchar(32), eqALID varchar(20),\
 ID_TSN varchar(8),set_CEID integer, clear_CEID integer, category integer, \
 is_enabled integer, is_set integer, ALTX varchar(40),\
 host_managed integer, host_wants_enabled integer,\
 primary key(spname,ALID))"
    # log table - not used by equipment
    SQL "create table ei_alarm_log (\
 spname varchar(32),\
 clock varchar(16),\
 ALID varchar(20),\
 is_set int,\
 ALTX varchar(40),\
 primary key(spname,clock,ALID))"

    # EVENT tables
   SQL "create table ei_event_report (spname varchar(32),CEID varchar(32),\
 RPTID varchar(32),  primary key(spname,CEID,RPTID))"
    SQL "create table ei_report (spname varchar(32), RPTID varchar(32),\
 VIDs varchar(1000), primary key(spname,RPTID))"

    # merge host/equipment schema
    # new fields to map back to sub-eq
    SQL "create table ei_event (spname varchar(32),CEID varchar(32),\
 eqname varchar(32), eqCEID varchar(32),\
 VFEIname varchar(32), host_managed int, host_wants_enabled int,\
 event_class varchar(10), ID_TSN varchar(4), is_reported integer,\
 description varchar(120), primary key(spname, CEID))"

    # host role use
    SQL "create table ei_event_report_cfg (spname varchar(32),\
 CEID varchar(32), RPTID varchar(32), primary key(spname,CEID,RPTID))"
    SQL "create table ei_report_cfg (spname varchar(32), \
 RPTID varchar(32), VIDs varchar(1000), primary key(spname,RPTID))"
    SQL "create table ei_event_log (\
 spname varchar(32),\
 clock varchar(16),\
 CEID varchar(32),\
 data varchar(2000),\
 primary key(spname,clock,CEID))"


        # VARIABLE table
        SQL "create table ei_variable (\
 spname varchar(32),\
 varID varchar(32),\
 eqname varchar(32),\
 eqvarID varchar(32),\
 ID_TSN varchar(8), \
 varname varchar(256),\
 VFEIname varchar(256), \
 description varchar(200), \
 varclass varchar(10),\
 value_TSN varchar(8), \
 varmethod varchar(2000), \
 varvalue varchar(20000), \
 host_setval varchar(200),\
 host_managed int, \
 t_latest varchar(16), \
 units varchar(10),\
 ECMIN varchar(32), ECMAX varchar(32), ECDEF varchar(32),\
 primary key(spname,varID))"

    # copied from unigem application, then new fields added for .NET SecsHost properties
    # configurable startup of interfaces to sub-equipment and
    #   offering a merged equipment interface to a host
    # - similar to ../host/supervisor.tcl
    # new ei_startup table fields 
    #  parent - for sub-equipment the spname of the cell, blank for the cell spname
    #  EQUIP -  indicates the role to play for the interface that we
    #     instantiate in our logic
    #     set to 0 for the attached equipment since we act as host
    #     set to 1 for the connection we offer to the host
    #  ALIDmap  - expression or procedure call mapping the equipment ALIDs to a U4
    #             ALID passed to the host.  Mapping the other direction is known
    #             by table lookup. Eg.,  'expr $ALID + 10000'
    #  CEIDmap  - expression or procedure call mapping the equipment CEIDs to a U4
    #             CEID passed to the host.  Mapping the other direction is known
    #             by table lookup. Eg.,  'expr $CEID + 10000'
    #  varIDmap - expression or procedure call mapping the equipment varIDs to a U4
    #             varID passed to the host.  Mapping the other direction is known
    #             by table lookup. Eg.,  'expr $varID + 10000'
    #  varNamemap - expression or procedure mapping the equipment varnames to 
    #             varnames passed to the host.  Eg., 'format "handler:%s" $varname'
    #  varNameunmap - expression or procedure mapping the host varnames to 
    #             varnames of the equipment, or blank if varname does not belong to
    #             the equipment. Eg.,  'substr $varname handler: {}'
    #  recipemap, recipeunmap - mapping and unmapping of PPExecNames just like
    #             mapping of varible names.
 
    SQL "create table ei_startup (\
 spname varchar(32),\
 parent varchar(32), \
 EQUIP int, \
 status varchar(60),\
 port varchar(200),\
 auto_init integer,\
 auto_start integer,\
 DEVID integer,\
 ALIDmap varchar(2000),\
 CEIDmap varchar(2000),\
 varIDmap varchar(2000),\
 varNamemap varchar(2000),\
 varNameunmap varchar(2000),\
 recipemap varchar(2000),\
 recipeunmap varchar(2000),\
 hsms_PROTOCOL integer,\
 BAUD integer,\
 CEID_OFFLINE varchar(32),\
 MULT integer,\
 RTY int,\
 SPOOL_INIT varchar(8),\
 T1 int, T2 int, T3 int, T4 int, T5 int, T6 int, T7 int, T8 int\
 tDATAID varchar(6),\
 tDATALENGTH varchar(6),\
 tLENGTH varchar(6),\
 tPPID varchar(6),\
 tREPGSZ varchar(6),\
 tRPTID varchar(6),\
 tTOTSMP varchar(6),\
 tTRID varchar(6),\
 use_s1f3 integer,\
 use_s1f11 integer,\
 use_s1f13 integer,\
 use_s1f17 integer,\
 use_s2f13 integer,\
 use_s2f29 integer,\
 use_s2f31 integer,\
 use_s5f5 integer,\
 use_s5f7 integer,\
 hsms_passive integer,\
 recipe_dir varchar(200),\
 post_startup_code varchar(2000),\
 primary key(spname))"

    }