SELECT
v2.document_cd,
v2.sort_order,
v2.forum_tag,
v2.book_cd,
v2.chapter,
v2.iverse,
v2.subverse,
v2.base_forum_tag,
group_concat(if(v2.interlin_translation is null, v2.word_utf8, concat(
"<span class=\"interlinear\"><span onclick=\"element = this.nextSibling; element.style.display = 'block'; return false;\">",
if(find_in_set(v2.word_utf8, v2.highlight_list), concat("<span class=\"highlight\">", v2.word_utf8, "</span>"), v2.word_utf8),
"</span><span class=\"shadow\"><span class=\"popup\"> <span xml:lang=\"en\" lang=\"en\">",
"[<a href=\"https://lexicon.katabiblon.com/?search=", word_utf8, "\">lexicon</a>][<a href=\"https://lexicon.katabiblon.com/inflect.php?lemma=", word_utf8, "\">inflect</a>][<a href=\"#\" onclick=\"element = this.parentNode.parentNode.parentNode; element.style.display = 'none'; return false;\">close</a>]<br />",
pos_ext, "</span><br />",
lexical_long_name_ext, "<br />",
v2.uncontracted_word, "<br /><span xml:lang=\"en\" lang=\"en\">",
v2.parse, "<br />",
v2.definition,
"</span></span></span>",
"</span>"
)) order by v2.position separator " ") AS text,
v2.highlight_list,
v2.wiki_translation,
v2.commentary_html
FROM (
SELECT
w2.document_cd,
v1.sort_order,
v1.forum_tag,
v1.book_cd,
v1.chapter,
v1.iverse,
v1.subverse,
v1.base_forum_tag,
v1.highlight_list,
v1.translation as wiki_translation,
v1.commentary_html,
w2.position,
normalized.word_utf8,
cast(group_concat(concat(
vwi.lexical_long_name_lex,
case
when vwi.lexical_name_ext is null then ""
when left(vwi.lexical_name_ext, 1) = "," then vwi.lexical_name_ext
else concat(" (", vwi.lexical_name_ext, ")")
end,
ifnull(dpp.principal_parts_list, "")
) order by vwi.pos_sort_num separator "; ") as char)
AS lexical_long_name_ext,
group_concat(binary case vwi.pos_sort_num when 8 then "Verb, Participle" when 9 then "Participle" else dpp.pos_ext end order by vwi.pos_sort_num separator "; ")
AS pos_ext, #"binary" appears to be necessary for dpp.pos_ext, but not vwi.pos_ext; vwi.pos_ext differentiates verbs and participles; dpp.pos_ext can be updated dynamically
group_concat(vwi.uncontracted_word_list order by vwi.pos_sort_num separator "; ")
AS uncontracted_word,
group_concat(vwi.parse_list order by vwi.pos_sort_num separator "; ")
AS parse,
group_concat(
binary #group_concat order-by goes kablooey with "dynamic" text if group_concat_max_len > 21844 and charset = utf8, therefore convert to binary (or latin1)
trim(concat(vwi.safe_formatted_user_definition, " ", ifnull(vwi.safe_definition_long, "")))
order by vwi.pos_sort_num separator "; ")
AS definition,
group_concat(
binary #group_concat order-by goes kablooey with "dynamic" text if group_concat_max_len > 21844 and charset = utf8, therefore convert to binary (or latin1)
vwi.safe_interlinear_translation
order by vwi.pos_sort_num separator "; ")
AS interlin_translation
FROM
(((
SELECT
orig.document_cd
, b.sort_order
, w.forum_tag
, w.book_cd
, w.chapter
, w.iverse
, w.subverse
, if(locate("-", dn.maps_to_subverse), concat(dn.maps_to_book_cd, " ", dn.maps_to_chapter, ":", dn.maps_to_subverse), concat(base.book_cd, " ", base.chapter, ":", base.subverse)) AS base_forum_tag
, replace(ifnull(orig.modified_html, ""), "class=\"annotate\"", "class=\"annotate\" xml:lang=\"en\" lang=\"en\"") AS text
, group_concat(n.word_utf8 separator ",") AS highlight_list #, group_concat(n.word_utf8 separator "~") AS highlight_list
#replace(
# ifnull(vwt.user_html, ""),
# "<a href=\"#N", concat("<a onclick=\"winopen(this); return false\" title=\"www.ebible.org\" href=\"http://www.ebible.org/web/", b.webhtm_cd, ".htm#N")
#) AS translation
#, ifnull(vwt.user_html, base.normalized_xml) AS translation
, ifnull(vwt.user_html, ifnull((
SELECT group_concat(concat("(", base.iverse, ") ", base.normalized_xml) separator " ") FROM kata_text.eng_webhtm_verses AS base WHERE locate("-", dn.maps_to_subverse) and (base.book_cd, base.chapter) = (dn.maps_to_book_cd, dn.maps_to_chapter) and base.iverse >= floor(left(dn.maps_to_subverse, locate("-", dn.maps_to_subverse) -1)) and base.iverse <= floor(mid(dn.maps_to_subverse, locate("-", dn.maps_to_subverse) + 1))
), base.normalized_xml)) AS translation,
if(vwt.tid is null, null,
(SELECT group_concat(concat("<sup>", y.reference_mark, "</sup> ", cast(y.user_html as binary)) order by y.reference_mark separator "\t")
FROM kata_wiki.wiki_translations_commentary AS y
WHERE y.tid = vwt.tid
GROUP BY y.tid)
) AS commentary_html
#Schenkl (1894, 1916) [Epicteti dissertationes ab Arriano digestae. ed. Heinrich Schenkl, Leipzig 1894/1916]
/*, b1.source_nm_i18n AS source_nm_i18n
, b1.source_nm_lang AS source_nm_lang
, ifnull(b1.source_long_nm_i18n, b1.source_nm_i18n) AS source_long_nm_i18n
, ifnull(b1.source_long_nm_lang, b1.source_nm_lang) AS source_long_nm_lang
, b1.source_href*/
FROM
(
kata_text.grc_robinson_normalized_words AS n #e.g., kata_text.robinson_normalized_words
, kata_text.grc_robinson_words AS w #e.g., kata_text.robinson_modified_words
, kata_text.grc_robinson_verses AS orig #e.g., kata_text.robinson_modified_text
, kata_system.document_books b use index (i_book_doc_sort))
LEFT JOIN kata_lexicon.grc_look_alike_words AS law
ON law.look_alike_word = binary n.word_utf8 #GNT = n.normalized_word, LXX = n.word_utf8
#AND law.strict_id < 0 #no filtering (e.g., do not differentiate ὁ and ὅ)
AND law.strict_id = 0 #least strict (e.g., differentiate ὁ and ὅ; but do not differentiate φίλων[n.] and φιλῶν[v.])
#AND law.strict_id < 2 #more strict (e.g., differentiate φίλων[n.] and φιλῶν[v.])
LEFT JOIN kata_wiki.view_wiki_translations_verse AS vwt
ON (vwt.book_cd, vwt.chapter, vwt.iverse, vwt.subverse, vwt.wiki_translation_cd) = (w.book_cd, w.chapter, w.iverse, w.subverse, ?)
LEFT JOIN kata_system.document_normalization AS dn use index (i_maps_to) on dn.id = (SELECT ifnull(max(if(dn2.iverse is null, null, dn2.id)), ifnull(max(if(dn2.chapter is null, null, dn2.id)), max(dn2.id))) FROM kata_system.document_normalization dn2 WHERE (dn2.orig_document_cd, dn2.base_document_cd, dn2.book_cd, ifnull(dn2.chapter, w.chapter), ifnull(dn2.iverse, w.iverse), ifnull(dn2.subverse, w.subverse)) = (orig.document_cd, "bible-web", w.book_cd, w.chapter, w.iverse, w.subverse)) LEFT JOIN kata_text.eng_webhtm_verses AS base on (base.book_cd, base.chapter, base.iverse, base.subverse) = (ifnull(dn.maps_to_book_cd, w.book_cd), ifnull(dn.maps_to_chapter, w.chapter), ifnull(dn.maps_to_iverse, w.iverse) + ifnull(dn.adjustment, 0), if(dn.adjustment is null or dn.adjustment = 0, ifnull(dn.maps_to_subverse, w.subverse), base.subverse))
WHERE
n.base_word like ?
AND elt(1,1, ?)
and (1=? or kata_system.func_match_diacritics(?, n.decomposed_word, n.base_word)) #1=1 ignores diacritics; 1=0 triggers func_match_diacritics()
AND w.word_utf8 = n.word_utf8
AND (w.book_cd, w.chapter) = (?, ?) and elt(1, 1, ?, ?, ?) and w.subverse = ? and elt(1, 1, ?)
AND (orig.book_cd, orig.chapter, orig.iverse, orig.subverse) = (w.book_cd, w.chapter, w.iverse, w.subverse)
AND (b.book_cd, b.document_cd) = (orig.book_cd, orig.document_cd)
GROUP BY w.book_cd, w.chapter, w.iverse, w.subverse #, orig.modified_html
ORDER BY b.sort_order, w.chapter, w.iverse, w.subverse
) AS v1
, kata_text.grc_robinson_words AS w2 #e.g., kata_text.robinson_modified_words
, kata_text.grc_robinson_normalized_words AS normalized #e.g., kata_text.robinson_normalized_words
, kata_system.documents AS d)
# ***LOOK-ALIKES***
LEFT JOIN kata_lexicon.grc_look_alike_words AS law ON law.look_alike_word = binary normalized.normalized_word
#AND law.strict_id < 0 #no filtering (e.g., do not differentiate ὁ and ὅ)
AND law.strict_id = 0 #least strict (e.g., differentiate ὁ and ὅ; but do not differentiate φίλων[n.] and φιλῶν[v.])
#AND law.strict_id < 2 #more strict (e.g., differentiate φίλων[n.] and φιλῶν[v.])
# ***INTERLINEAR DEFINITIONS***
LEFT JOIN kata_lexicon.view_wiki_interlinear AS vwi
ON (vwi.inflected_word, binary vwi.inflected_word) = (normalized.base_word collate utf8_general_ci, binary normalized.base_word) #collate necessary in order to not blow up when joining to heb_westminster_normalized_words
AND (vwi.unsubscripted_fl = "N" OR vwi.unsubscripted_fl = d.grc_unsubscripted_fl) #controls whether θύραι matches both θύραι and θύρᾳ
AND vwi.super_lemma = ifnull(law.super_lemma, vwi.super_lemma))
LEFT JOIN kata_lexicon.grc_dictionary_posx_pparts AS dpp
ON dpp.super_lemma = vwi.super_lemma
WHERE (w2.book_cd, w2.chapter, w2.iverse, w2.subverse) = (v1.book_cd, v1.chapter, v1.iverse, v1.subverse)
AND normalized.word_utf8 = w2.word_utf8
AND d.document_cd = w2.document_cd
GROUP BY v1.book_cd, v1.chapter, v1.iverse, v1.subverse, w2.position
) AS v2
GROUP BY v2.book_cd, v2.chapter, v2.iverse, v2.subverse
ORDER BY v2.sort_order, v2.chapter, v2.iverse, v2.subverse
ERROR (1146): Table 'kata_lexicon.grc_dictionary_posx_pparts' doesn't exist
