Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
executable file 528 lines (440 sloc) 18.4 KB
#!/bin/gawk -f
# A webapp for managing Taxon × Character data with a MySQL DB
# (c) Cam Webb <cw@camwebb.info>, 2019; License: GPL v3
# INSTALLATION
#
# 1. If using Apache httpd, install this file in a cgi executable
# location and create a .htaccess file, minimally:
# Options +ExecCGI -Indexes
# <Files "do">
# SetHandler cgi-script
# </Files>
# DirectoryIndex do
# Optionally add:
# <FilesMatch "^\.pw$">
# Deny from all
# </FilesMatch>
# AuthType Basic
# AuthName "Password Protected"
# AuthUserFile /full/path/to/.pw
# Require valid-user
#
# 2. Install function libraries from https://github.com/camwebb/awk-lib
# and include, using full path
@include "/usr/local/share/awk/db_sql.awk"
@include "/usr/local/share/awk/util_http.awk"
@include "/usr/local/share/awk/db_secrets.awk"
# 3. On and available MySQL or MariaDB DB, install the empty DB at
# http://alaskaflora.org/chars/chars.sql and modify as needed. Make sure
# the access info is in the included db_secrets.awk
BEGIN{
PROCINFO["sorted_in"] = "@ind_str_asc"
db_secrets()
# READ QUERY STRING
split(ENVIRON["QUERY_STRING"], qs, "&");
for (i in qs) {
split(qs[i], qp, "=");
f[qp[1]] = substr(urldecode(qp[2]),1,2000);
}
# DETERMINE AND EXECUTE METHOD
if (f["page"] == "matrix") matrix()
else if (f["page"] == "cell") cell()
else if (f["page"] == "tnote") taxonnote()
else if (f["page"] == "copy") copy()
else defaultpage() ;
}
function matrix( ntaxa, charID, taxaID, haschar, charx, name, \
i, j, s, out, gen, warningtext, guid) {
header("Data matrix")
print "<p style=\"text-align:right;font-weight:bold\"><a href=\"do\">Home</a></p>"
# Table header
db_query("akchars", "SELECT `char`, `state`, `id` FROM chars \
ORDER BY `char`, `state`;")
for (i = 1; i <= DBQr; i++) {
charID[i] = DBQ[i, "id"]
char[i] = DBQ[i, "char"]
state[i] = DBQ[i, "state"]
charx[char[i]][state[i]] = DBQ[i, "id"]
}
print "<table>"
print "<tr>"
print "<td>Taxon</td>"
print "<td>Syn-<br/>onyms</td>"
print "<td>ID</td>"
for (i in charx)
print "<td><b>" i "</b></td>"
print "<td>taxon<br/>notes</td>"
print "<td>fill</td>"
print "</tr>"
db_clear()
# Taxon names
db_query("akchars", "SELECT `name`, `guid`, `id`, `genID`, `syn`, `tnote` FROM \
`taxa` WHERE `name` LIKE '" f["genus"] "%'ORDER BY `name`;")
ntaxa = DBQr
for (i = 1; i <= DBQr; i++) {
name[i] = DBQ[i, "name"]
guid[i] = DBQ[i, "guid"]
taxaID[i] = DBQ[i, "id"]
syn[i] = DBQ[i, "syn"]
tnote[i] = DBQ[i, "tnote"]
if (DBQ[i, "genID"]) gen[taxaID[i]] = DBQ[i, "genID"]
}
db_clear()
# Table data
db_query("akchars", "SELECT `taxaID`, `charID` FROM `TxC`;")
for (i = 1 ; i <= DBQr; i++)
haschar[DBQ[i, "taxaID"], DBQ[i, "charID"]] = 1
db_clear()
for (i = 1 ; i <= ntaxa; i++) {
print "<tr>"
print "<td><b>" name[i] "</b></td>"
print "<td class=\"syn1\"><div class=\"syn2\">"gensub(/.+/,"...","G",syn[i])"</div><div class=\"syn3\">" syn[i] "</div></td>"
print "<td>" guid[i] "</td>"
for (j in charx) {
print "<td onclick=\"location.href='do?page=cell&amp;char=" j \
"&amp;taxaID=" taxaID[i]"&amp;genus=" f["genus"] "';\">"
out = ""
sn = 0
for (s in charx[j]) {
# test for char at genus level
if (haschar[gen[taxaID[i]], charx[j][s]]) out = out \
"<span style=\"color:blue;\">" s "</span>, "
if (haschar[taxaID[i], charx[j][s]]) {
out = out s ", "
sn++
}
if (!gen[taxaID[i]] && (sn > 1))
warningtext = warningtext "<p style=\"color:red;\">WARNING: genus " name[i] " has >1 states for '" j "'</p>"
}
gsub(/,\ $/,"",out)
if (!out) out = "&#160;"
print out
print "</td>"
}
print "<td onclick=\"location.href='do?page=tnote&amp;taxaID=" \
taxaID[i] "&amp;genus=" f["genus"] "';\" class=\"syn1\"><div class=\"syn2\">"gensub(/.+/,"...","G",tnote[i])"</div><div class=\"syn3\">" tnote[i] "</div></td>"
print "<td onclick=\"location.href='do?page=copy&amp;taxaID=" \
taxaID[i]"&amp;genus=" f["genus"] "';\"/>"
print "</tr>"
}
print "</table>"
if (warningtext) print warningtext
footer()
exit 0
}
# The Cell editing page
function cell( name, actiontext, ES, i,s ,srcfail) {
# If reentering into this page after an action
if (f["preaction"] == "delete") {
ES = db_sql("akchars", "DELETE FROM `TxC` WHERE `id` = " f["charID"] ";")
if (ES) actiontext = "<p style=\"color:red;\">UPDATE FAILURE</p>"
# else actiontext = "<p>Status: character state datum " f["charID"] \
# " deleted</p>"
if (!actiontext) matrix()
}
else if (f["preaction"] == "new") {
# first test for accepable sources
split(gensub(/\ /,"","G",f["sources"]),s,",")
for (i in s)
if (s[i] !~ /^(and|cody|dall|fna|hult|jep|judd|welsh|walt|inat|ASC|oth|apw)$/) srcfail = 1
if (srcfail) actiontext = \
"<p style=\"color:red;\">INCORRECT SOURCE ABBREVIATION</p>"
else if (!f["sources"]) actiontext = \
"<p style=\"color:red;\">AT LEAST ONE SOURCE NEEDED</p>"
else if ((f["sources"] ~ /(oth|inat)/) && !f["notes"]) actiontext = \
"<p style=\"color:red;\">IF 'oth' or 'inat' THEN SOURCE INFO NEEDED</p>"
else {
gsub(/'/,"\\'",f["notes"])
gsub(/"/,"\\\"",f["notes"])
ES = db_sql("akchars", "INSERT INTO `TxC` \
(`taxaID`, `charID`, `person`, `sources`, `notes`) \
VALUES (" f["taxaID"] "," f["charID"]", '" \
f["person"] "','" f["sources"] "','" f["notes"] "');")
if (ES) actiontext = "<p style=\"color:red;\">INSERT FAILURE</p>"
# else actiontext = "<p>Status: character state datum " f["charID"]
# " added</p>"
if (!actiontext) matrix()
}
}
header("Data cell")
print "<p style=\"position:absolute;right:30px;top:15px;font-weight:bold;\"><a href=\"do?page=matrix&amp;genus=" f["genus"] \
"\">Matrix</a></p>"
# Taxon names
db_query("akchars", "SELECT `name`, `guid`, `genID` FROM \
`taxa` WHERE `id` = " f["taxaID"] ";")
name = "<b>" DBQ[1,"name"] "</b> (" DBQ[1,"guid"] ")"
db_clear()
# top-level table
print "<table style=\"border:none;\"><tr><td style=\"border:none;vertical-align:top;width:400px;\">"
print "<table><tr><td><i>Taxon</i></td><td>" name "</td></tr>"
print "<tr><td><i>Character</i></td><td><b>" f["char"] \
"</b></td></tr></table>"
db_query("akchars", "SELECT `state`, `timestamp`, `person`, `sources`, \
`TxC`.`id`, `TxC`.`notes` FROM `TxC`, \
`chars` WHERE `taxaID` = " f["taxaID"] " AND `chars`.`id` = \
`TxC`.`charID` AND `chars`.`char` = '" f["char"] "';")
print "<div style=\"padding:15px;\">"
for (i = 1; i <= DBQr; i++) {
print "<b>" DBQ[i, "state"] "</b>"
print "&#160;&#160;&#160;<span style=\"font-size: 80%;\">(<a href=\"do?page=cell&amp;char=" urlencode(f["char"]) "&amp;taxaID=" f["taxaID"] "&amp;genus=" f["genus"] "&amp;preaction=delete&amp;charID=" DBQ[i,"id"] "\">DELETE</a>)</span>"
print "<div style=\"padding:15px;\">"
print "<table>"
print "<tr><td><i>Sources</i></td><td style=\"width:400px;\">" DBQ[i, "sources"] "</td></tr>"
print "<tr><td><i>Notes</i></td><td>" DBQ[i, "notes"] "</td></tr>"
print "<tr><td><i>By</i></td><td>" DBQ[i, "person"] "</td></tr>"
print "<tr><td><i>Date</i></td><td>" gensub(/^([^ ]+).*/,"\\1","G", \
DBQ[i, "timestamp"]) "</td></tr>"
print "</table>"
print "</div>"
}
print "</div>"
db_clear()
print "</td><td style=\"border:none;vertical-align:top;\">"
print "<form action=\"do\"><p>"
print "<input type=\"hidden\" name=\"page\" value=\"cell\"/>"
print "<input type=\"hidden\" name=\"preaction\" value=\"new\"/>"
print "<input type=\"hidden\" name=\"char\" value=\"" f["char"] "\"/>"
print "<input type=\"hidden\" name=\"taxaID\" value=\"" f["taxaID"] "\"/>"
print "<input type=\"hidden\" name=\"genus\" value=\"" f["genus"] "\"/>"
print "Add new character state:</p>"
print "<div style=\"padding:15px;\">"
print "<select name=\"charID\" style=\"font-weight:bold;font-size:1em;\">"
db_query("akchars", "SELECT `state`, `id` FROM `chars` WHERE `char` = \
'" f["char"] "';")
for (i = 1; i <= DBQr; i++)
print "<option value=\"" DBQ[i, "id"] "\">" DBQ[i, "state"] "</option>"
db_clear()
print "</select>"
print "&#160;&#160;&#160;(<input type=\"submit\" value=\"GO\"/>)"
print "<div style=\"padding:15px;\">"
print "<table>"
print "<tr><td><i>Sources*</i></td>"
print "<td><input type=\"text\" name=\"sources\" style=\"width:300px;\"/></td></tr>"
print "<tr><td><i>Notes</i></td>"
print "<td><textarea name=\"notes\" cols=\"40\" rows=\"4\"></textarea></td></tr>"
print "<tr><td><i>By</i></td>"
print "<td><select name=\"person\">"
print "<option value=\"Brian\">Brian</option>"
print "<option value=\"Cam\">Cam</option>"
print "<option value=\"Steffi\">Steffi</option>"
print "</select></td></tr>"
print "</table><br/>(* Allowed: <code>and, apw, cody, dall, fna, hult, jep, judd,<br/>walt, welsh, <i>inat</i>, <i>oth</i>, <b>ASC</b></code>; <i>inat</i> and <i>oth</i> require notes)</div>"
print "</div></form></td></tr></table>"
if (actiontext) print actiontext
footer()
exit 0
}
function copy( name, i, actiontext, sql, ES){
header("Data copy")
print "<p style=\"position:absolute; right:30px; top:15px; \
font-weight:bold;\"><a href=\"do?page=matrix&amp;genus=" \
f["genus"] "\">Matrix</a></p>"
if (f["preaction"] == "copy") {
ES = db_sql("akchars", "DELETE FROM `TxC` WHERE `taxaID` = " \
f["totaxaID"] ";")
if (ES) actiontext = "<p style=\"color:red;\">DELETE FAILURE</p>"
db_clear()
db_query("akchars", "SELECT `charID`,`sources`,`person`, `taxa`.`name`,\
`taxa`.`guid` FROM `TxC`,`taxa` WHERE `taxa`.`id` = \
`TxC`.`taxaID` AND `taxaID` = " f["fromtaxaID"] ";")
for (i = 1; i <= DBQr; i++) {
sql = "INSERT INTO `TxC` (`taxaID`, `charID`, `person`, `sources`, \
`notes`) VALUES (" f["totaxaID"] "," DBQ[i, "charID"] ", '" \
DBQ[i, "person"] "','" DBQ[i, "sources"] \
"', 'Data copied from " DBQ[i,"name"] " (" DBQ[i,"guid"] ")'); "
ES = db_sql("akchars", sql)
if (ES) actiontext = actiontext "<p style=\"color:red;\">FAILED: '" \
sql "'</p>"
}
db_clear()
if (!actiontext) print "<p>Successfully copied</p>"
}
else {
# Taxon names
db_query("akchars", "SELECT `name`, `guid`, `genID` FROM \
`taxa` WHERE `id` = " f["taxaID"] ";")
name = "<b>" DBQ[1,"name"] "</b> (" DBQ[1,"guid"] ")"
db_clear()
print "<form action=\"do\"><p>"
print "<input type=\"hidden\" name=\"page\" value=\"copy\"/>"
print "<input type=\"hidden\" name=\"preaction\" value=\"copy\"/>"
print "<input type=\"hidden\" name=\"totaxaID\" value=\"" f["taxaID"] "\"/>"
print "<input type=\"hidden\" name=\"genus\" value=\"" f["genus"] "\"/>"
print "Copy data <i>into</i> " name " from "
print "<select name=\"fromtaxaID\">"
db_query("akchars", "SELECT `name`, `guid`, `id` FROM \
`taxa` WHERE `genID` IS NOT NULL AND `id` != " f["taxaID"] \
" AND `name` LIKE '" f["genus"] "%' ORDER BY `name`;")
for (i = 1; i <= DBQr; i++)
print "<option value=\"" DBQ[i, "id"] "\">" DBQ[i, "name"] "</option>"
db_clear()
print "</select>"
print "<input type=\"submit\" value=\"GO\"/></p>"
print "</form>"
print "<p>(<i style=\"color:red;\">All exiting data will be "\
"overwritten</i>)</p>"
}
if (actiontext) print actiontext
footer()
exit 0
}
function taxonnote( name, i, actiontext, ES){
header("Data copy")
print "<p style=\"position:absolute; right:30px; top:15px; \
font-weight:bold;\"><a href=\"do?page=matrix&amp;genus=" \
f["genus"] "\">Matrix</a></p>"
if (f["preaction"] == "tnote") {
gsub(/'/,"\\'",f["tnote"])
gsub(/"/,"\\\"",f["tnote"])
ES = db_sql("akchars", "UPDATE `taxa` SET `tnote` = '" f["tnote"] "' , `tnoteauth` = '" f["tnoteauth"] "' WHERE id = " f["taxaID"] ";")
if (ES) actiontext = actiontext "<p style=\"color:red;\">FAILED</p>"
else actiontext = actiontext "<p style=\"color:green;\">Success</p>"
}
# Taxon names
db_query("akchars", "SELECT `name`, `guid`, `tnote`, `tnoteauth` FROM \
`taxa` WHERE `id` = " f["taxaID"] ";")
name = "<b>" DBQ[1,"name"] "</b> (" DBQ[1,"guid"] ")"
print "<form action=\"do\"><p>"
print "<input type=\"hidden\" name=\"page\" value=\"tnote\"/>"
print "<input type=\"hidden\" name=\"preaction\" value=\"tnote\"/>"
print "<input type=\"hidden\" name=\"taxaID\" value=\"" f["taxaID"] "\"/>"
print "<input type=\"hidden\" name=\"genus\" value=\"" f["genus"] "\"/>"
print "Make a note about " name "</p>"
# print "<p>(Currently: '" DBQ[1, "tnote"] "')</p>"
print "<p><textarea name=\"tnote\" cols=\"60\" rows=\"10\">"
print DBQ[1, "tnote"]
print "</textarea> (" DBQ[1, "tnoteauth"] ")</p>"
print "<p>By: "
print "<select name=\"tnoteauth\">"
print "<option value=\"Brian\">Brian</option>"
print "<option value=\"Cam\">Cam</option>"
print "<option value=\"Steffi\">Steffi</option>"
print "</select></p>"
print "<p><input type=\"submit\" value=\"GO\"/></p>"
print "</form>"
print "<p>(<i style=\"color:red;\">Note: exiting entry will be " \
"overwritten</i>)</p>"
db_clear()
if (actiontext) print actiontext
footer()
exit 0
}
function header(title) {
# Use html5
print "Content-type: text/html\n"
print "<!DOCTYPE html>"
print "<html xmlns=\"http://www.w3.org/1999/xhtml\">"
print "<head><title>" title "</title>"
print "<meta http-equiv=\"Content-Type\" content=\"text/html; \
charset=utf-8\" />"
print "<link href=\"https://fonts.googleapis.com/css?family=Montserrat\" \
rel=\"stylesheet\"/>"
print "<link href=\"../img/akflora.ico\" rel=\"shortcut icon\" \
type=\"image/x-icon\"/>"
print "<style>"
print "body { font-size: 14px; width: 1200px; font-family: 'Montserrat', \
Verdana, Arial, Helvetica, sans-serif; padding: 20px; \
position: absolute }"
print "select , option { font-size: 14px }"
print "table { max-width: 1200px; border-collapse: collapse }"
print "td, th { border: 1px solid black; padding: 3px }"
print "a { color:#15358d; text-decoration:none; border-bottom-style:none }"
print "a:visited { color:#9f1dbc }"
print "a:hover {color:#15358d; border-bottom-style:solid; \
border-bottom-width:thin }"
print ".syn1:hover .syn2 {display: none;}"
print ".syn1:hover .syn3 {display: block;}"
print ".syn3 {display: none;}"
print "</style>"
print "</head>\n<body>"
}
function defaultpage( i)
{
header("Character entry webapp");
print "<h1>Character entry webapp</h1>"
print "<table style=\"border:none;\"><tr><td style=\"border:none;"\
"vertical-align:top;padding:20px;width:500px\">"
print "<p>Select genus:</p>"
print "<table>"
print "<tr><th>Family</th><th>Genus</th><th>N taxa</th><th>% done</th></tr>"
# get taxa
db_query("akchars", "SELECT * FROM taxa ORDER BY `fam`, `name`;")
for (i = 1; i <= DBQr; i++) {
if (!DBQ[i,"genID"]) {
fam[++g] = DBQ[i,"fam"]
name[g] = DBQ[i,"name"]
gid[g] = DBQ[i,"id"]
}
else
glist[DBQ[i,"genID"]][DBQ[i,"id"]] = 1
}
db_clear()
# get states
db_query("akchars", "SELECT * FROM `chars`;")
for (i = 1; i <= DBQr; i++) {
char[DBQ[i,"id"]] = DBQ[i,"char"]
charx[DBQ[i,"char"]] = 1
}
db_clear()
# count the total number of chars (not states)
for (i in charx) nchars++
# fill a taxa x chars matrix
db_query("akchars", "SELECT * FROM TxC;")
for (i = 1; i <= DBQr; i++) {
chard[DBQ[i,"taxaID"], char[DBQ[i,"charID"]]] = 1
}
db_clear()
# now, for each genus
for (n = 1; n <= g; n++) {
filled = ntaxa = pcdone = 0
# for each taxon in genus
if (isarray(glist[gid[n]])) {
for (i in glist[gid[n]]) {
ntaxa++
# for each char
for (j in charx)
if (chard[i, j] || chard[gid[n], j]) filled++
}
pcdone = sprintf("%.2f", filled / (ntaxa * nchars))
}
if (pcdone == "0.00") pcdone = ""
else if (pcdone == "1.00") pcdone = "<span style=\"color:green;\">" \
pcdone "</span>"
if (ntaxa && fam[n] !~ /^(Cyperaceae|Lycopodiaceae|Pinaceae|Poaceae|\
Woodsiaceae|Dryopteridaceae|Cupressaceae|Aspleniaceae)$/)
print "<tr><td>" fam[n] "</td><td><a href=\"do?page=matrix&amp;genus=" \
name[n] "\">" name[n] "</a></td><td>" ntaxa "</td><td>" pcdone \
"</td></tr>"
}
# print "<tr><th>Family</th><th>Genus</th><th>N taxa<br/>scored</th><th>N char<br/>scored</th></tr>"
# db_query("akchars", "SELECT `fam`, `taxa`.`name`, COUNT(`b`.`genID`) \
# AS `ct`, SUM(`c`) AS `sc` FROM `taxa` LEFT JOIN (SELECT `genID`, \
# COUNT(`charID`) AS `c` \
# FROM `taxa`, (SELECT `taxaID`, `charID` FROM `TxC`) AS `a` WHERE \
# `a`.`taxaID` = `taxa`.`id` AND `genID` IS NOT NULL GROUP BY `name`) \
# AS `b` ON `taxa`.`id` = `b`.`genID` WHERE `taxa`.`genID` IS NULL \
# GROUP BY `taxa`.`id` ORDER BY `fam`, `name`;")
# for (i = 1; i <= DBQr; i++)
# print "<tr><td>" DBQ[i,"fam"] "</td><td><a href=\"do?page=matrix&amp;genus=" DBQ[i, "name"] "\">" \
# DBQ[i, "name"] "</a></td><td>" gensub(/0/,"","G", DBQ[i,"ct"]) "</td><td>" DBQ[i,"sc"] "</td></tr>"
# db_clear()
print "</table>"
print "</td><td style=\"border:none;vertical-align:top;padding:20px;\">"
print "<p>Recent activity:</p>"
print "<table>"
print "<tr><th>Family</th><th>Taxon</th><th>Char</th><th>State</th><th>Date</th></tr>"
db_query("akchars", "select fam, taxa.name, `char`, `state`, \
date(`timestamp`) as dt from TxC, taxa, chars where TxC.charID = \
`chars`.id and TxC.taxaID = taxa.id order by `timestamp` \
desc limit 10;")
for (i = 1; i <= DBQr; i++)
print "<tr><td>" DBQ[i,"fam"] "</td><td>" DBQ[i, "name"] "</td><td>" DBQ[i,"char"] "</td><td>" DBQ[i,"state"] "</td><td>" DBQ[i,"dt"] "</td></tr>"
db_clear()
print "</table>"
print "</td></tr></table>"
footer()
}
function footer()
{
print "</body>\n</html>";
}
You can’t perform that action at this time.