IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 42609


Ignore:
Timestamp:
Feb 6, 2024, 10:06:26 AM (2 years ago)
Author:
eugene
Message:

fix order of xcstackRun and related tables; add xcForceRun and related; add xcstack stuff to drop tables

Location:
branches/eam_branches/ipp-20230313/ippTools/share
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql

    r42572 r42609  
    21762176) ENGINE=innodb DEFAULT CHARSET=latin1;
    21772177
    2178 -- xcsky and xccal tables
    2179 CREATE TABLE xcskyRun (
    2180       xcsky_id BIGINT AUTO_INCREMENT, -- unique identifier
    2181       state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
    2182       workdir VARCHAR(255) NOT NULL, -- working directory
    2183       label VARCHAR(64),             -- processing label
    2184       data_group VARCHAR(64),        -- group for data
    2185       dist_group VARCHAR(64),        -- group for distribution
    2186       reduction VARCHAR(64),         -- reduction class (for altering recipe)
    2187       note VARCHAR(255),             -- note
    2188       registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
    2189       PRIMARY KEY(xcsky_id),
    2190       KEY(state),
    2191       KEY(label),
    2192       KEY(data_group),
    2193       KEY(dist_group)
    2194 ) ENGINE=innodb DEFAULT CHARSET=latin1;
    2195 
    2196 -- Inputs for xcsky analysis
    2197 CREATE TABLE xcskyInput (
    2198       xcsky_id BIGINT,             -- static sky identifier
    2199       xcstack_id BIGINT,          -- stack identifier
    2200       PRIMARY KEY(xcsky_id,xcstack_id),
    2201       KEY(xcstack_id),
    2202       FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id),
    2203       FOREIGN KEY(xcstack_id) REFERENCES xcstackSumSkyfile(xcstack_id)
    2204 ) ENGINE=innodb DEFAULT CHARSET=latin1;
    2205 
    2206 -- Result of static sky analysis
    2207 CREATE TABLE xcskyResult (
    2208       xcsky_id BIGINT,             -- static sky identifier
    2209       path_base VARCHAR(255) NOT NULL, -- root name for outputs
    2210       dtime_phot FLOAT,                -- elapsed time for photometry
    2211       dtime_script FLOAT,              -- elapsed time for script
    2212       sources INT,                     -- number of sources
    2213       num_inputs INT,                  -- number of input images
    2214       hostname VARCHAR(64) NOT NULL,   -- host that executed script
    2215       good_frac FLOAT,                 -- good fraction of skycell
    2216       quality SMALLINT NOT NULL,       -- bad quality flag
    2217       fault SMALLINT NOT NULL,         -- fault code
    2218       PRIMARY KEY(xcsky_id),
    2219       KEY(good_frac),
    2220       KEY(fault),
    2221       KEY(quality),
    2222       FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id)
    2223 ) ENGINE=innodb DEFAULT CHARSET=latin1;
    2224 
    2225 CREATE TABLE xccalRun (
    2226       xccal_id BIGINT AUTO_INCREMENT, -- unique identifier
    2227       xcsky_id BIGINT,
    2228       xcstack_id BIGINT,
    2229       state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
    2230       workdir VARCHAR(255) NOT NULL, -- working directory
    2231       label VARCHAR(64),             -- processing label
    2232       data_group VARCHAR(64),        -- group for data
    2233       dist_group VARCHAR(64),        -- group for distribution
    2234       reduction VARCHAR(64),         -- reduction class (for altering recipe)
    2235       registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
    2236       note VARCHAR(255),             -- note
    2237       PRIMARY KEY(xccal_id),
    2238       KEY(state),
    2239       KEY(label),
    2240       KEY(data_group),
    2241       KEY(dist_group),
    2242       KEY(xcsky_id),
    2243       KEY(xcstack_id),
    2244       FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id),
    2245       FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id)
    2246 ) ENGINE=innodb DEFAULT CHARSET=latin1;
    2247 
    2248 -- Result of sky calibration analysis
    2249 CREATE TABLE xccalResult (
    2250       xccal_id BIGINT,
    2251       path_base VARCHAR(255) NOT NULL,
    2252       dtime_script FLOAT,
    2253       dtime_astrom FLOAT,
    2254       sigma_ra FLOAT,
    2255       sigma_dec FLOAT,
    2256       n_astrom INT,
    2257       n_detections INT,
    2258       n_extended INT,
    2259       n_forced INT,
    2260       zpt_obs FLOAT,
    2261       zpt_stdev FLOAT,
    2262       fwhm_major  FLOAT,
    2263       fwhm_minor  FLOAT,
    2264       quality SMALLINT NOT NULL,
    2265       hostname VARCHAR(64) NOT NULL,
    2266       software_ver VARCHAR(64),
    2267       fault SMALLINT NOT NULL,
    2268       PRIMARY KEY(xccal_id),
    2269       KEY(fault),
    2270       KEY(quality),
    2271       FOREIGN KEY(xccal_id) REFERENCES xccalRun(xccal_id)
    2272 ) ENGINE=innodb DEFAULT CHARSET=latin1;
    2273 
    22742178CREATE TABLE skycell (
    22752179    tess_id     VARCHAR(64),
    … …  
    25772481) ENGINE=innodb DEFAULT CHARSET=latin1;
    25782482
     2483CREATE TABLE xcstackCamera (
     2484        camera_id BIGINT AUTO_INCREMENT,
     2485        camera VARCHAR(255),
     2486        PRIMARY KEY(camera_id)
     2487) ENGINE=innodb DEFAULT CHARSET=latin1;
     2488
    25792489CREATE TABLE xcstackRun (
    25802490        xcstack_id BIGINT AUTO_INCREMENT,
    … …  
    26052515        camera_id BIGINT,
    26062516        stack_id BIGINT,
    2607         PRIMARY KEY(xcstack_id, stack_id),
    2608         FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id)
     2517        PRIMARY KEY(xcstack_id, camera_id, stack_id),
     2518        KEY(xcstack_id),
     2519        KEY(stack_id),
     2520        FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id),
     2521        FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id)
    26092522) ENGINE=innodb DEFAULT CHARSET=latin1;
    26102523
    … …  
    26292542) ENGINE=innodb DEFAULT CHARSET=latin1;
    26302543
    2631 CREATE TABLE xcstackCamera (
    2632         camera_id BIGINT,
    2633         camera VARCHAR(255),
    2634         PRIMARY KEY(camera_id)
     2544-- A static sky analysis set
     2545CREATE TABLE xcskyRun (
     2546      xcsky_id BIGINT AUTO_INCREMENT, -- unique identifier
     2547      state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
     2548      workdir VARCHAR(255) NOT NULL, -- working directory
     2549      label VARCHAR(64),             -- processing label
     2550      data_group VARCHAR(64),        -- group for data
     2551      dist_group VARCHAR(64),        -- group for distribution
     2552      reduction VARCHAR(64),         -- reduction class (for altering recipe)
     2553      note VARCHAR(255),             -- note
     2554      registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
     2555      PRIMARY KEY(xcsky_id),
     2556      KEY(state),
     2557      KEY(label),
     2558      KEY(data_group),
     2559      KEY(dist_group)
     2560) ENGINE=innodb DEFAULT CHARSET=latin1;
     2561
     2562-- Inputs for static sky analysis
     2563CREATE TABLE xcskyInput (
     2564      xcsky_id BIGINT,             -- static sky identifier
     2565      xcstack_id BIGINT,          -- stack identifier
     2566      PRIMARY KEY(xcsky_id,xcstack_id),
     2567      KEY(xcstack_id),
     2568      FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id),
     2569      FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id)
     2570) ENGINE=innodb DEFAULT CHARSET=latin1;
     2571
     2572-- Result of static sky analysis
     2573CREATE TABLE xcskyResult (
     2574      xcsky_id BIGINT,             -- static sky identifier
     2575      path_base VARCHAR(255) NOT NULL, -- root name for outputs
     2576      dtime_phot FLOAT,                -- elapsed time for photometry
     2577      dtime_script FLOAT,              -- elapsed time for script
     2578      sources INT,                     -- number of sources
     2579      num_inputs INT,                  -- number of input images
     2580      hostname VARCHAR(64) NOT NULL,   -- host that executed script
     2581      good_frac FLOAT,                 -- good fraction of skycell
     2582      quality SMALLINT NOT NULL,       -- bad quality flag
     2583      fault SMALLINT NOT NULL,         -- fault code
     2584      PRIMARY KEY(xcsky_id),
     2585      KEY(good_frac),
     2586      KEY(fault),
     2587      KEY(quality),
     2588      FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id)
     2589) ENGINE=innodb DEFAULT CHARSET=latin1;
     2590
     2591CREATE TABLE xccalRun (
     2592      xccal_id BIGINT AUTO_INCREMENT, -- unique identifier
     2593      xcsky_id BIGINT,
     2594      xcstack_id BIGINT,
     2595      state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
     2596      workdir VARCHAR(255) NOT NULL, -- working directory
     2597      label VARCHAR(64),             -- processing label
     2598      data_group VARCHAR(64),        -- group for data
     2599      dist_group VARCHAR(64),        -- group for distribution
     2600      reduction VARCHAR(64),         -- reduction class (for altering recipe)
     2601      registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
     2602      note VARCHAR(255),             -- note
     2603      PRIMARY KEY(xccal_id),
     2604      KEY(state),
     2605      KEY(label),
     2606      KEY(data_group),
     2607      KEY(dist_group),
     2608      KEY(xcsky_id),
     2609      KEY(xcstack_id),
     2610      FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id),
     2611      FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id)
     2612) ENGINE=innodb DEFAULT CHARSET=latin1;
     2613
     2614-- Result of sky calibration analysis
     2615CREATE TABLE xccalResult (
     2616      xccal_id BIGINT,
     2617      path_base VARCHAR(255) NOT NULL,
     2618      dtime_script FLOAT,
     2619      dtime_astrom FLOAT,
     2620      sigma_ra FLOAT,
     2621      sigma_dec FLOAT,
     2622      n_astrom INT,
     2623      n_detections INT,
     2624      n_extended INT,
     2625      n_forced INT,
     2626      zpt_obs FLOAT,
     2627      zpt_stdev FLOAT,
     2628      fwhm_major  FLOAT,
     2629      fwhm_minor  FLOAT,
     2630      quality SMALLINT NOT NULL,
     2631      hostname VARCHAR(64) NOT NULL,
     2632      software_ver VARCHAR(64),
     2633      fault SMALLINT NOT NULL,
     2634      PRIMARY KEY(xccal_id),
     2635      KEY(fault),
     2636      KEY(quality),
     2637      FOREIGN KEY(xccal_id) REFERENCES xccalRun(xccal_id)
     2638) ENGINE=innodb DEFAULT CHARSET=latin1;
     2639
     2640CREATE TABLE xcForceRun (
     2641    xcff_id         BIGINT NOT NULL AUTO_INCREMENT,
     2642    xccal_id        BIGINT, -- NOT a foreign key: reference to entry in xcamera db
     2643    sources_path_base VARCHAR(255),
     2644    state           VARCHAR(64),
     2645    workdir         VARCHAR(255),
     2646    label           VARCHAR(64),
     2647    skycell_id      VARCHAR(64),
     2648    tess_id         VARCHAR(64),
     2649    data_group      VARCHAR(64),
     2650    dist_group      VARCHAR(64),
     2651    note            VARCHAR(255),
     2652    reduction       VARCHAR(64),
     2653    registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
     2654    PRIMARY KEY(xcff_id),
     2655    KEY(state),
     2656    KEY(label),
     2657    KEY(data_group),
     2658    KEY(xccal_id)
     2659) ENGINE=innodb DEFAULT CHARSET=latin1;
     2660
     2661CREATE TABLE xcForceInput (
     2662    xcff_id              BIGINT,
     2663    warp_id              BIGINT,
     2664    camera_id            BIGINT,
     2665    PRIMARY KEY(xcff_id, warp_id, camera_id),
     2666    FOREIGN KEY(xcff_id)   REFERENCES xcForceRun(xcff_id),
     2667    FOREIGN KEY(warp_id)   REFERENCES warpRun(warp_id),
     2668    FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id)
     2669) ENGINE=innodb DEFAULT CHARSET=latin1;
     2670
     2671CREATE TABLE xcForceResult (
     2672    xcff_id         BIGINT,
     2673    warp_id         BIGINT,
     2674    path_base       VARCHAR(255) NOT NULL,
     2675    dtime_script    FLOAT,
     2676    quality         SMALLINT NOT NULL,
     2677    hostname        VARCHAR(64) NOT NULL,
     2678    software_ver    VARCHAR(16),
     2679    fault           SMALLINT NOT NULL,
     2680    PRIMARY KEY(xcff_id, warp_id),
     2681    KEY(fault),
     2682    KEY(quality),
     2683    FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id),
     2684    FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id)
     2685) ENGINE=innodb DEFAULT CHARSET=latin1;
     2686
     2687CREATE TABLE xcForceSummary (
     2688    xcff_id         BIGINT,
     2689    path_base       VARCHAR(255) NOT NULL,
     2690    dtime_script    FLOAT,
     2691    quality         SMALLINT NOT NULL,
     2692    hostname        VARCHAR(64) NOT NULL,
     2693    software_ver    VARCHAR(16),
     2694    fault           SMALLINT NOT NULL,
     2695    PRIMARY KEY(xcff_id),
     2696    KEY(fault),
     2697    KEY(quality),
     2698    FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id)
    26352699) ENGINE=innodb DEFAULT CHARSET=latin1;
    26362700

  • branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_drop_tables.sql

    r42387 r42609  
    131131DROP TABLE IF EXISTS fpcamProcessedExp;
    132132DROP TABLE IF EXISTS fpcamRun;
     133DROP TABLE IF EXISTS xccalResult;
     134DROP TABLE IF EXISTS xccalRun;
     135DROP TABLE IF EXISTS xcForceInput;
     136DROP TABLE IF EXISTS xcForceResult;
     137DROP TABLE IF EXISTS xcForceRun;
     138DROP TABLE IF EXISTS xcForceSummary;
     139DROP TABLE IF EXISTS xcskyInput;
     140DROP TABLE IF EXISTS xcskyResult;
     141DROP TABLE IF EXISTS xcskyRun;
     142DROP TABLE IF EXISTS xcstackCamera;
     143DROP TABLE IF EXISTS xcstackInputSkyfile;
     144DROP TABLE IF EXISTS xcstackRun;
     145DROP TABLE IF EXISTS xcstackSumSkyfile;
    133146
    134147SET FOREIGN_KEY_CHECKS=1
Note: See TracChangeset for help on using the changeset viewer.