Dust: Thinking about 'how I did it'

People have asked me 'how' I wrote Seeing the Psalter. There is a brief 4-pager here. I could write a book on it. But one part I have never shared is that for almost every panel in the screens in the brief 4-pager, I wrote a lot of code like the stuff below. Hundreds of bits of it actually over a period of 7 years - or this is what it looks like when one of these snippets is fully expanded by the GX-LEAF software that I used to help me.

GX-LEAF helped in so many ways as I felt my way into the Hebrew of the Psalms and decided what direction I should take with this very complex bit of poetic history. It held my data one character at a time. It let me draw and explore diagrams of the psalms as I learned them. It allowed queries and graphs to experiment with the data I had entered. And it provided the development environment for the panels that would let me control the translation process with queries and updates such as the snippet below.

This is an example of the code that creates a cross-tab from the database given a number of user-defined search parameters. I designed it to allow up to 40 columns. Of course there may not be that many columns so each column header is conditioned by a separate query to determine whether it will let itself be visible or not. Altogether, one does not want to read this unless necessary. But there you have a bit of the background to the tables that you may have seen in other posts like the recent ones on Psalm 90 and Psalm 109.

SELECT ROOT,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,
       P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,
       AF,AG,AH,AI,AJ,AK,AL,AM,AN,VSS,REALROOT
FROM 
  (SELECT kwd_cd root, (decode(vs,1,bl,NULL)) a, (decode(vs,2,bl,NULL)) b, (decode(vs,3,bl,NULL)) c, (decode(vs,4,bl,NULL)) d, (decode(vs,5,bl,NULL)) e, (decode(vs,6,bl,NULL)) f, (decode(vs,7,bl,NULL)) g, (decode(vs,8,bl,NULL)) h, (decode(vs,9,bl,NULL)) i, (decode(vs,10,bl,NULL)) j, (decode(vs,11,bl,NULL)) k, (decode(vs,12,bl,NULL)) l, (decode(vs,13,bl,NULL)) m, (decode(vs,14,bl,NULL)) n, (decode(vs,15,bl,NULL)) o, (decode(vs,16,bl,NULL)) p, (decode(vs,17,bl,NULL)) q, (decode(vs,18,bl,NULL)) r, (decode(vs,19,bl,NULL)) s, (decode(vs,20,bl,NULL)) t, (decode(vs,21,bl,NULL)) u, (decode(vs,22,bl,NULL)) v, (decode(vs,23,bl,NULL)) w, (decode(vs,24,bl,NULL)) x, (decode(vs,25,bl,NULL)) y, (decode(vs,26,bl,NULL)) z, (decode(vs,27,bl,NULL)) aa, (decode(vs,28,bl,NULL)) ab, (decode(vs,29,bl,NULL)) ac, (decode(vs,30,bl,NULL)) ad, (decode(vs,31,bl,NULL)) ae, (decode(vs,32,bl,NULL)) af, (decode(vs,33,bl,NULL)) ag, (decode(vs,34,bl,NULL)) ah, (decode(vs,35,bl,NULL)) ai, (decode(vs,36,bl,NULL)) aj, (decode(vs,37,bl,NULL)) ak, (decode(vs,38,bl,NULL)) al, (decode(vs,39,bl,NULL)) am, (decode(vs,40,bl,NULL)) an,
grp_cd realroot,
vss
   FROM 
(SELECT dgm_grp_cd, kwd_seq, kwd_id, kwd_cd,
   (SELECT max(vs)
    FROM
      (SELECT DGM_GRP_CD,
              VS
       FROM
         (SELECT dgm_grp_cd,
                 rownum vs
          FROM
            (SELECT dgm_grp_cd,
                    min(kwd_seq),
                    min(kwd_id)
             FROM
               (SELECT count(kwd_loc) over (partition BY dgm_grp_cd) keyword_count,
                                                                     min(kwd_id) over (partition BY dgm_grp_cd
                                                                                       ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_id,
dgm_grp_cd,
min(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))) over (partition BY dgm_grp_cd
                                                                                                                         ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_seq, replace(kwd_loc,'-','') kwd_loc,
(SELECT
   (SELECT nvl(max(comm_fld),upper('2')) comm_fld
    FROM cs_udpv
    WHERE udg_cd = 'SVARS'
      AND udp_cd = 'KEYVALPR'
      AND udpv_gen_key1 = USER
      AND udpv_gen_key2 = upper('kwd_first')
      AND udpv_gen_key3 IS NULL
      AND udpv_gen_key4 IS NULL)
 FROM dual) fst_only,
            kwd_gen_key1
                FROM cs_dgmkwd d
WHERE instr(
              (SELECT nvl(max(comm_fld),upper('`L,YHVH,)LVH,KY,)L,)T')) comm_fld
               FROM cs_udpv
               WHERE udg_cd = 'SVARS'
                 AND udp_cd = 'KEYVALPR'
                 AND udpv_gen_key1 = USER
                 AND udpv_gen_key2 = upper('excl_root')
                 AND udpv_gen_key3 IS NULL
                 AND udpv_gen_key4 IS NULL),dgm_grp_cd)=0
AND kwd_tp =
(SELECT nvl(max(comm_fld),upper('PSALMS')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('b_books')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND (kwd_loc LIKE substr(
                           (SELECT nvl(max(comm_fld),upper('--1')) comm_fld
                            FROM cs_udpv
                            WHERE udg_cd = 'SVARS'
                              AND udp_cd = 'KEYVALPR'
                              AND udpv_gen_key1 = USER
                              AND udpv_gen_key2 = upper('kwd_loc_st')
                              AND udpv_gen_key3 IS NULL
                              AND udpv_gen_key4 IS NULL),1,3)||'%')
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) BETWEEN
(SELECT nvl(max(comm_fld),upper('001')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_st')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('176')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_end')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) NOT BETWEEN
(SELECT nvl(max(comm_fld),upper('998')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_xst')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('999')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_xend')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
                ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))), kwd_id)
WHERE keyword_count >
(SELECT nvl(max(comm_fld),upper('1')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_count1')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND keyword_count <=
(SELECT nvl(max(comm_fld),upper('99')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_count2')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND (fst_only= '2'
     OR fst_only=decode(kwd_gen_key1,NULL,'0','1'))
             GROUP BY dgm_grp_cd
             ORDER BY 2,
                    3)))) maxvs,
(SELECT vs
FROM
(SELECT DGM_GRP_CD,
  VS
FROM
(SELECT dgm_grp_cd,
    rownum vs
FROM
(SELECT dgm_grp_cd,
      min(kwd_seq),
      min(kwd_id)
FROM
 (SELECT count(kwd_loc) over (partition BY dgm_grp_cd) keyword_count,
                                                       min(kwd_id) over (partition BY dgm_grp_cd
                                                                         ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_id,
dgm_grp_cd,
min(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))) over (partition BY dgm_grp_cd
                                                                                                                         ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_seq, replace(kwd_loc,'-','') kwd_loc,
(SELECT
   (SELECT nvl(max(comm_fld),upper('2')) comm_fld
    FROM cs_udpv
    WHERE udg_cd = 'SVARS'
      AND udp_cd = 'KEYVALPR'
      AND udpv_gen_key1 = USER
      AND udpv_gen_key2 = upper('kwd_first')
      AND udpv_gen_key3 IS NULL
      AND udpv_gen_key4 IS NULL)
 FROM dual) fst_only,
            kwd_gen_key1
  FROM cs_dgmkwd d
WHERE instr(
              (SELECT nvl(max(comm_fld),upper('`L,YHVH,)LVH,KY,)L,)T')) comm_fld
               FROM cs_udpv
               WHERE udg_cd = 'SVARS'
                 AND udp_cd = 'KEYVALPR'
                 AND udpv_gen_key1 = USER
                 AND udpv_gen_key2 = upper('excl_root')
                 AND udpv_gen_key3 IS NULL
                 AND udpv_gen_key4 IS NULL),dgm_grp_cd)=0
AND kwd_tp =
(SELECT nvl(max(comm_fld),upper('PSALMS')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('b_books')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND (kwd_loc LIKE substr(
                           (SELECT nvl(max(comm_fld),upper('--1')) comm_fld
                            FROM cs_udpv
                            WHERE udg_cd = 'SVARS'
                              AND udp_cd = 'KEYVALPR'
                              AND udpv_gen_key1 = USER
                              AND udpv_gen_key2 = upper('kwd_loc_st')
                              AND udpv_gen_key3 IS NULL
                              AND udpv_gen_key4 IS NULL),1,3)||'%')
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) BETWEEN
(SELECT nvl(max(comm_fld),upper('001')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_st')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('176')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_end')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) NOT BETWEEN
(SELECT nvl(max(comm_fld),upper('998')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_xst')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('999')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_seq_xend')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
  ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))), kwd_id)
WHERE keyword_count >
(SELECT nvl(max(comm_fld),upper('1')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_count1')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND keyword_count <=
(SELECT nvl(max(comm_fld),upper('99')) comm_fld
 FROM cs_udpv
 WHERE udg_cd = 'SVARS'
   AND udp_cd = 'KEYVALPR'
   AND udpv_gen_key1 = USER
   AND udpv_gen_key2 = upper('kwd_count2')
   AND udpv_gen_key3 IS NULL
   AND udpv_gen_key4 IS NULL)
AND (fst_only= '2'
     OR fst_only=decode(kwd_gen_key1,NULL,'0','1'))
GROUP BY dgm_grp_cd
ORDER BY 2,
       3)))
WHERE dgm_grp_cd = e.dgm_grp_cd) vs,
                       vss,
                       kwd_gen_key1,
                       grp_cd,
                       bl
 FROM
(SELECT count(kwd_loc) over (partition BY dgm_grp_cd) keyword_count,substr(kwd_loc,5) vss, replace(replace(substr(kwd_cd,1,instr(kwd_cd,'&')-1)||'░'||'','color','background-color'),'* ','') bl, kwd_id,kwd_cd, dgm_grp_cd,
(SELECT
(SELECT nvl(max(comm_fld),upper('2')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_first')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
FROM dual) fst_only,
   decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'), kwd_loc) kwd_seq,
   replace(kwd_loc,'-','') kwd_loc,
   kwd_gen_key1,
   grp_cd
FROM cs_dgmkwd d
WHERE instr(
        (SELECT nvl(max(comm_fld),upper('`L,YHVH,)LVH,KY,)L,)T')) comm_fld
     FROM cs_udpv
     WHERE udg_cd = 'SVARS'
       AND udp_cd = 'KEYVALPR'
       AND udpv_gen_key1 = USER
       AND udpv_gen_key2 = upper('excl_root')
       AND udpv_gen_key3 IS NULL
       AND udpv_gen_key4 IS NULL),dgm_grp_cd)=0
AND kwd_tp =
(SELECT nvl(max(comm_fld),upper('PSALMS')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('b_books')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (kwd_loc LIKE substr(
                     (SELECT nvl(max(comm_fld),upper('--1')) comm_fld
                  FROM cs_udpv
                  WHERE udg_cd = 'SVARS'
                    AND udp_cd = 'KEYVALPR'
                    AND udpv_gen_key1 = USER
                    AND udpv_gen_key2 = upper('kwd_loc_st')
                    AND udpv_gen_key3 IS NULL
                    AND udpv_gen_key4 IS NULL),1,3)||'%')
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) BETWEEN
(SELECT nvl(max(comm_fld),upper('001')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('176')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_end')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) NOT BETWEEN
(SELECT nvl(max(comm_fld),upper('998')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xst')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('999')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xend')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
ORDER BY kwd_seq,
 kwd_id) e
WHERE keyword_count >
(SELECT nvl(max(comm_fld),upper('1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count1')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND keyword_count <=
(SELECT nvl(max(comm_fld),upper('99')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count2')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (fst_only= '2'
OR fst_only=decode(kwd_gen_key1,NULL,'0','1'))
 ORDER BY 2,
3))

Dust

Pages

Friday 6 September 2013

Thinking about 'how I did it'

No comments:

Post a Comment