gistfile1.txt

WITH RECURSIVE
  scales(x) AS (VALUES($base_lod) UNION ALL SELECT x+1 FROM scales WHERE x+1 < $num_scales),
  zrange(lod,left_tsc,start_expand,right_tsc) AS (
    SELECT x, $left_tsc, $left_tsc - min($base_width << x, $left_tsc), $right_tsc FROM scales
    UNION ALL VALUES($num_scales, $tsc_min, $tsc_min, $tsc_max)
  )
SELECT tmz.rowid AS zone_id, tmz.fullname_id AS fullname_id, tmz.filename_id AS filename_id, tmz.path_id AS path_id, tmz.depth AS depth,
  tmz.lock_ptr AS lock_ptr, tmz.count AS count, tmz.filled AS filled, tmz.lod AS lod, tmz.line AS line, tmz.flags AS flags,
  tmz.start_tsc AS start_tsc, tmz.end_tsc AS end_tsc, tmz.process_id AS process_id, tmz.thread_id AS thread_id,
  etext.content AS econtent,
  ptext.content AS pcontent,
  ftext.content AS fcontent
  FROM zrange
  CROSS JOIN tmzones tmz ON tmz.lod=zrange.lod AND (tmz.start_tsc BETWEEN zrange.start_expand AND zrange.right_tsc) AND zrange.left_tsc <= tmz.end_tsc
  LEFT JOIN tmexpandedtext ftext ON ftext.id=tmz.filename_id
  LEFT JOIN tmexpandedtext etext ON etext.id=tmz.fullname_id
  LEFT JOIN tmexpandedtext ptext ON ptext.id=tmz.path_id
UNION ALL
SELECT 0 AS zone_id, 0 AS fullname_id, 0 AS filename_id, 0 AS path_id, tmdz.depth AS depth,
  0 AS lock_ptr, tmdz.count AS count, tmdz.filled AS filled, tmdz.lod AS lod, 0 AS line, $deadflags AS flags,
  tmdz.start_tsc AS start_tsc, tmdz.start_tsc + tmdz.duration AS end_tsc, tmdz.process_id AS process_id, tmdz.thread_id AS thread_id,
  NULL AS econtent, NULL AS pcontent, NULL AS fcontent
  FROM zrange
  CROSS JOIN tmdeadzones tmdz ON tmdz.lod=zrange.lod AND (tmdz.start_tsc BETWEEN zrange.start_expand AND zrange.right_tsc) AND zrange.left_tsc <= (tmdz.start_tsc + tmdz.duration);




WITH RECURSIVE
  lods(lod) AS (VALUES($lod) UNION ALL SELECT lod+1 FROM lods WHERE lod+1 < $num_lods)
SELECT tp.fullname_id, tp.lod, tp.tsc, tp.value, tp.sample_index, ts.min_l, ts.max_l, ts.new_l, ts.sum_l, ts.min_r, ts.max_r, ts.new_r, ts.sum_r
  FROM (
    SELECT lods.lod AS lod, coalesce(max(pl_lft.tsc), $left_tsc) AS tsc
    FROM lods
    LEFT JOIN tmplots2 pl_lft ON pl_lft.fullname_id=$fullname_id AND pl_lft.lod=lods.lod AND pl_lft.tsc < $left_tsc
    GROUP BY lods.lod
  ) range_l JOIN (
    SELECT lods.lod AS lod, coalesce(min(pl_rgt.tsc), $right_tsc) AS tsc
    FROM lods
    LEFT JOIN tmplots2 pl_rgt ON pl_rgt.fullname_id=$fullname_id AND pl_rgt.lod=lods.lod AND pl_rgt.tsc > $right_tsc
    GROUP BY lods.lod
  ) range_r ON range_l.lod=range_r.lod
  CROSS JOIN tmplots2 tp ON tp.fullname_id=$fullname_id AND tp.lod=range_l.lod AND (tp.tsc BETWEEN range_l.tsc AND range_r.tsc)
  LEFT JOIN tmplots2summary ts ON ts.record_id = tp.record_id
ORDER BY tp.sample_index;