# $Id: tasks.md,v 1.154 2008-05-31 02:19:17 jhoblitt Exp $

# this table records all exposure ID ever seen from the summit
# exp_name == fileset

# note that dec is a MySQL reserved word
# note that use is a MySQL reserved word
# note that exp is a MySQL reserved word

# for use with this stored procedure to generate exp_ids under Mysql 5+
#
#CREATE FUNCTION genTag (exp_name varchar(64)) RETURNS VARCHAR(64)
#BEGIN
#    UPDATE expTagCounter SET counter = LAST_INSERT_ID(counter + 1);
#    RETURN CONCAT(exp_name, '.', LAST_INSERT_ID());
#END
#
#expTagCounter METADATA
#   counter     U64         0 
#END

pzDataStore METADATA
    camera      STR         64      # Primary Key
    telescope   STR         64      # Primary Key
    uri         STR         255
    epoch       UTC         0001-01-01T00:00:00Z
END

# list of source exposures -- updated as exposures are seen
# summitExp.imfiles is updated as filesets are queried default value should be
# -1 as NULL or 0 might be a valid value (empty fileset)
summitExp METADATA
    exp_name    STR         64      # Primary Key
    camera      STR         64      # Primary Key
    telescope   STR         64      # Primary Key
    dateobs     UTC         NULL
    exp_type    STR         64
    uri         STR         255
    imfiles     S32         0
    fault       S16         0       # Key NOT NULL
    epoch       UTC         0001-01-01T00:00:00Z
END

# class == type of file
# class_id == type set id
# list of source images -- updated as exposures/filesets are queried
summitImfile METADATA
    exp_name    STR         64      # Primary Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
    camera      STR         64      # Primary Key
    telescope   STR         64      # Primary Key
    file_id     STR         64      # Key
    bytes       S32         0
    md5sum      STR         32
    class       STR         64      # Primary Key
    class_id    STR         64      # Primary Key
    uri         STR         255
    epoch       UTC         0001-01-01T00:00:00Z
END

# list of exposures that have had their imfiles/files registered (but not
# downloaded) 
pzDownloadExp METADATA
    exp_name    STR         64      # Primary Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
    camera      STR         64      # Primary Key
    telescope   STR         64      # Primary Key
    state       STR         64      # Key 
    epoch       UTC         0001-01-01T00:00:00Z
END

pzDownloadImfile METADATA
    exp_name    STR         64      # Primary Key fkey(exp_name, camera, telescope) ref pzDownloadExp(exp_name, camera, telescope)
    camera      STR         64      # Primary Key fkey(exp_name, camera, telescope, class, class_id) ref summitImfile(exp_name, camera, telescope, class, class_id)
    telescope   STR         64      # Primary Key
    class       STR         64      # Primary Key
    class_id    STR         64      # Primary Key
    uri         STR         255
    fault       S16         0       # Key NOT NULL
    epoch       UTC         0001-01-01T00:00:00Z
END

newExp METADATA
    exp_id      S64         0       # Primary Key AUTO_INCREMENT
    tmp_exp_name STR        64      # Key
    tmp_camera    STR       64      # Key
    tmp_telescope STR       64      # Key
    state       STR         64      # Key
    workdir     STR         255     # destination for output files
    workdir_state STR       64      # key
    reduction   STR         64      # Reduction class
    dvodb       STR         255
    tess_id     STR         64
    end_stage   STR         64      # Key
    label       STR         64      # Key
    epoch       UTC         0001-01-01T00:00:00Z
END

# class needs to be carried here so it can go into rawImfile and be normalized
# from there
newImfile METADATA
    exp_id      S64         64      # Primary Key fkey(exp_id) ref newExp(exp_id)
    tmp_class_id STR        64      # Primary Key
    uri         STR         255
    epoch       UTC         0001-01-01T00:00:00Z
END

# paired with rawImfile
rawExp METADATA
    exp_id      S64         64      # Primary Key fkey(exp_id) ref newExp(exp_id)
    exp_name    STR         64      # Key
    camera      STR         64
    telescope   STR         64
    dateobs     UTC         0001-01-01T00:00:00Z
    exp_tag     STR         255
    exp_type    STR         64
    filelevel   STR         64
    workdir     STR         255     # destination for output files
    reduction   STR         64      # Reduction class
    dvodb       STR         255
    tess_id     STR         64
    end_stage   STR         64      # Key
    filter      STR         64
    comment     STR         80
    airmass     F32         0.0
    ra          F64         0.0
    decl        F64         0.0
    exp_time    F32         0.0
    sat_pixel_frac F32      0.0
    bg          F64         0.0
    bg_stdev    F64         0.0
    bg_mean_stdev   F64     0.0
    alt         F64         0.0
    az          F64         0.0
    ccd_temp    F32         0.0
    posang      F64         0.0 
    m1_x        F32         0.0
    m1_y        F32         0.0
    m1_z        F32         0.0
    m1_tip      F32         0.0
    m1_tilt     F32         0.0
    m2_x        F32         0.0
    m2_y        F32         0.0
    m2_z        F32         0.0
    m2_tip      F32         0.0
    m2_tilt     F32         0.0
    env_temperature F32     0.0
    env_humidity    F32     0.0
    env_wind_speed  F32     0.0
    env_wind_dir    F32     0.0
    teltemp_m1      F32     0.0
    teltemp_m1cell  F32     0.0
    teltemp_m2      F32     0.0
    teltemp_spider  F32     0.0
    teltemp_truss   F32     0.0
    teltemp_extra   F32     0.0
    pon_time        F32     0.0
    user_1      F64         0.0
    user_2      F64         0.0
    user_3      F64         0.0
    user_4      F64         0.0
    user_5      F64         0.0
    object      STR         64
    solang      F32         0.0
    hostname    STR         64
    fault       S16         0       # Key NOT NULL
    epoch       UTC         0001-01-01T00:00:00Z
END

rawImfile METADATA
    exp_id      S64         64      # Primary Key fkey(exp_id, tmp_class_id) ref newImfile(exp_id, tmp_class_id)
    exp_name    STR         64      # UINDEX(exp_id, tmp_class_id)
    camera      STR         64
    telescope   STR         64
    dateobs     UTC         0001-01-01T00:00:00Z
    tmp_class_id    STR     64      # Key
    class_id    STR         64      # Primary Key
    uri         STR         255
    exp_type    STR         64
# This field is used to set the per exp filelevel. Thus the values for all of
# the imfiles in an exposure need to be sanity checked to make sure that this
# value is in argeement.
    filelevel   STR         64 
    filter      STR         64
    comment     STR         80
    airmass     F32         0.0
    ra          F64         0.0
    decl        F64         0.0
    exp_time    F32         0.0
    sat_pixel_frac F32      0.0
    bg          F64         0.0
    bg_stdev    F64         0.0
    bg_mean_stdev   F64     0.0
    alt         F64         0.0
    az          F64         0.0
    ccd_temp    F32         0.0
    posang      F64         0.0 
    m1_x        F32         0.0
    m1_y        F32         0.0
    m1_z        F32         0.0
    m1_tip      F32         0.0
    m1_tilt     F32         0.0
    m2_x        F32         0.0
    m2_y        F32         0.0
    m2_z        F32         0.0
    m2_tip      F32         0.0
    m2_tilt     F32         0.0
    env_temperature F32     0.0
    env_humidity    F32     0.0
    env_wind_speed  F32     0.0
    env_wind_dir    F32     0.0
    teltemp_m1      F32     0.0
    teltemp_m1cell  F32     0.0
    teltemp_m2      F32     0.0
    teltemp_spider  F32     0.0
    teltemp_truss   F32     0.0
    teltemp_extra   F32     0.0
    pon_time        F32     0.0
    user_1      F64         0.0
    user_2      F64         0.0
    user_3      F64         0.0
    user_4      F64         0.0
    user_5      F64         0.0
    object      STR         64
    hostname    STR         64
    fault       S16         0       # Key NOT NULL
    epoch       UTC         0001-01-01T00:00:00Z
END
