www.katabiblon.com
SELECT
orig.orig_forum_tag
, orig.iverse
, orig.subverse
, pericope.pericope_nm
, orig.original_html
# ***RP/NAUBS (OR OTHER) APPARATUS***
, apparatus.ketib_qere_list_html AS apparatus_html, if(base_dn.maps_to_chapter<0, null, if(locate("-", base_dn.maps_to_subverse) or locate(",", base_dn.maps_to_subverse), concat(base_dn.maps_to_book_cd, " ", base_dn.maps_to_chapter, ":", base_dn.maps_to_subverse), concat(base.book_cd, " ", base.chapter, ":", base.subverse))) AS base_forum_tag, replace(lower(coalesce(db.site_book_short_nm_eng, db.site_book_nm_eng, db.site_book_long_nm_eng, db.document_book_nm_i18n)), " ", "_") AS bible_hub_title, ifnull(vwt.user_html, ifnull(base.normalized_xml, # ***GROUP BASE VERSES THAT MAP MANY-TO-ONE (E.G., GK EXODUS 38:16 = HEB EXODUS 37:20-22)***
(SELECT group_concat(concat("(", base.iverse, ") ", base.normalized_xml) separator " ")
FROM kata_text.lat_vulsearch_verses AS base
WHERE (base.book_cd, base.chapter) = (base_dn.maps_to_book_cd, base_dn.maps_to_chapter)
AND (
(locate("-", base_dn.maps_to_subverse) AND base.iverse BETWEEN floor(left(base_dn.maps_to_subverse, locate("-", base_dn.maps_to_subverse) -1)) AND floor(mid(base_dn.maps_to_subverse, locate("-", base_dn.maps_to_subverse) + 1)))
OR (locate(",", base_dn.maps_to_subverse) AND find_in_set(base.iverse, base_dn.maps_to_subverse))
)
))
) AS user_html
, 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, null AS brenton_forum_tag, null AS brenton_html, 0 AS message_cnt #currently forums are set up only for the GNT
, (
SELECT count(distinct uid)
FROM kata_wiki.wiki_translations_history AS wth
WHERE (wth.wiki_translation_cd, wth.book_cd, wth.chapter, wth.iverse, wth.subverse) = ("wiki-latin-hmt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
) AS editors_cnt
, (
SELECT date(max(insert_dtm))
FROM kata_wiki.wiki_translations_history AS wth
WHERE (wth.wiki_translation_cd, wth.book_cd, wth.chapter, wth.iverse, wth.subverse) = ("wiki-latin-hmt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
) AS last_edit_dtm
FROM (
SELECT
concat(v1.book_cd, v1.chapter, ":", v1.subverse) AS orig_forum_tag,
v1.document_cd,
v1.book_cd,
v1.chapter,
v1.iverse,
v1.subverse,
#group_concat(v1.start_time, if(v1.translation is null, if(char_length(v1.word_utf8) = 2, v1.word_utf8, concat(v1.word_utf8, " <sub><bdi dir=\"ltr\">", kata_system.func_heb_translit(v1.uncantillated_word), "</bdi></sub>")), concat(
group_concat(v1.start_time, if(v1.translation is null, v1.word_utf8, concat(
"<span class=\"interlinear\"><span onclick=\"element = this.nextSibling; element.style.display = 'block'; return false;\">",
v1.word_utf8,
"</span><span class=\"shadow\"><span class=\"popup\"> <span lang=\"en\">",
"[<a href=\"https://lexicon.katabiblon.com/?search=", v1.word_utf8, "\">lexicon</a>][<a href=\"https://lexicon.katabiblon.com/inflect.php?lemma=", v1.word_utf8, "\">inflect</a>][<a href=\"#\" onclick=\"element = this.parentNode.parentNode.parentNode; element.style.display = 'none'; return false;\">close</a>]<br />",
"<strong>", substring_index(v1.pos_ext, " ", 1), "</strong>", substring(v1.pos_ext, locate(" ", v1.pos_ext)), "</span><br />",
replace(replace(v1.lexical_long_name_ext, "<lemma>", ""), "</lemma>", ""), "<br />",
v1.uncontracted_word, "<br /><span lang=\"en\">",
v1.parse,
"<br />", replace(v1.translation, "~(", "~~("),
"<br />", v1.definition,
"</span></span></span>",
if(v1.strongs_numbers is null, "</span>", concat("<sup> ", v1.strongs_numbers, "</sup></span>"))
)) order by v1.position separator " ")
AS original_html
FROM (
SELECT
orig.document_cd,
orig.book_cd,
orig.chapter,
orig.iverse,
orig.subverse,
# ***GREEK WORDS***
words.position,
normalized.word_utf8 AS word_utf8,
#normalized.uncantillated_word,
# ***INTERLINEAR DEFINITIONS***
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)
trim(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 translation,
# ***STRONGS NUMBERS***
group_concat(
concat("<a rel=\"nofollow\" href=\"https://www.katabiblon.com/global/vocabulary.php?text=HB&book=Ps&ch=136&lemma=0.000000E+01D%CF%CE9EFF", strongs.super_num, "\">", strongs.super_num, "</a>")
order by binary(strongs.super_num) + 0, strongs.super_num separator " | ")
AS strongs_numbers
# ***AUDIO***
, ifnull(concat("<span class=\"audio\" title=\"", a.start_time, "\"></span>"), "") as start_time
FROM
(
0 AS orig
# ***GREEK WORDS***
, kata_text.heb_westminster_words AS words
LEFT JOIN kata_text.heb_westminster_normalized_words AS normalized ON normalized.word_utf8 = words.word_utf8
LEFT JOIN kata_lexicon.grc_look_alike_words AS law ON law.look_alike_word = binary normalized.uncantillated_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.])
, kata_system.documents AS d
)
# ***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
# ***STRONGS NUMBERS***
LEFT JOIN kata_lexicon.grc_lexicon_occurrences AS occur
ON occur.super_lemma = vwi.super_lemma
AND occur.gnt_count <= ?
LEFT JOIN kata_lexicon.grc_dictionary_strongs strongs
ON strongs.super_lemma = occur.super_lemma
AND strongs.sub_lemma = strongs.super_lemma
# ***AUDIO***
LEFT JOIN kata_wiki.audio AS a
ON (a.book_cd, a.chapter, a.verse, a.position) = (orig.book_cd, orig.chapter, orig.iverse, words.position)
AND "on" = ""
WHERE (orig.book_cd, orig.chapter) = (?, ?) and elt(1,1, ?, ?)
AND d.document_cd = orig.document_cd
AND (words.book_cd, words.chapter, words.iverse, words.subverse) = (orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
GROUP BY orig.document_cd, orig.book_cd, orig.chapter, orig.iverse, orig.subverse, words.position, words.word_utf8
) AS v1
GROUP BY v1.document_cd, v1.book_cd, v1.chapter, v1.iverse, v1.subverse
) AS orig
# ***RP/NAUBS (OR OTHER) APPARATUS***
LEFT JOIN kata_text.heb_westminster_verses AS apparatus ON (apparatus.book_cd, apparatus.chapter, apparatus.iverse, apparatus.subverse) = (orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
# ***PERICOPES***
LEFT JOIN kata_system.document_pericopes AS pericope
ON (pericope.orig_document_cd, pericope.book_cd, pericope.chapter, pericope.iverse, pericope.subverse) = (orig.document_cd, orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
# ***USER WIKI TRANSLATION***
LEFT JOIN kata_wiki.view_wiki_translations_verse AS vwt
ON (vwt.wiki_translation_cd, vwt.book_cd, vwt.chapter, vwt.iverse, vwt.subverse) = ("wiki-latin-hmt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
# ***BASE TRANSLATION***
LEFT JOIN kata_system.document_normalization AS base_dn use index (i_maps_to)
ON base_dn.id =
(
SELECT ifnull(max(if(dn.iverse is null, null, dn.id)), ifnull(max(if(dn.chapter is null, null, dn.id)), max(dn.id)))
FROM kata_system.document_normalization dn
WHERE (dn.orig_document_cd, dn.base_document_cd, dn.book_cd, ifnull(dn.chapter, orig.chapter))
= (orig.document_cd, "vulgate-clementine", orig.book_cd, orig.chapter)
AND (
(ifnull(dn.iverse, orig.iverse), ifnull(dn.subverse, orig.subverse))
= (orig.iverse, orig.subverse)
OR (dn.iverse<0 AND locate("-", dn.subverse) AND orig.iverse BETWEEN floor(left(dn.subverse, locate("-", dn.subverse) -1)) AND floor(mid(dn.subverse, locate("-", dn.subverse) + 1)))
OR (dn.iverse<0 AND locate(",", dn.subverse) AND find_in_set(orig.iverse, dn.subverse))
)
)LEFT JOIN kata_text.lat_vulsearch_verses AS base
ON (base.book_cd, base.chapter, base.iverse, base.subverse)
= (ifnull(base_dn.maps_to_book_cd, orig.book_cd)
, ifnull(base_dn.maps_to_chapter, orig.chapter)
, ifnull(base_dn.maps_to_iverse, orig.iverse + ifnull(base_dn.adjustment, 0))
#, if(base_dn.adjustment is null or base_dn.adjustment = 0, ifnull(base_dn.maps_to_subverse, orig.subverse), base.subverse)
, ifnull(base_dn.maps_to_subverse, if(orig.subverse = concat(orig.iverse), orig.subverse + ifnull(base_dn.adjustment, 0), orig.subverse))
)
LEFT JOIN kata_system.document_books AS db
ON (db.document_cd, db.book_cd) = (base.document_cd, base.book_cd)ORDER BY orig.iverse, orig.subverse
ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 AS orig
# ***GREEK WORDS***
, kata_text.heb_westminster_words AS ' at line 117