#!/bin/bash -e

. "$(dirname "${BASH_SOURCE[0]}")"/../../../lib/runscripts/mysql.table.run

if self_not_included; then

full_table="${table%_plants}"

herbaria_filter.ih.csv_/make()

{

	begin_target; check_target_exists

	use_local

	limit= query='SELECT DISTINCT "specimenHolderInstitutions" FROM herbaria.ih ORDER BY "specimenHolderInstitutions"' \

to_target pg_export

}

ih_herbarium/make() # stores IH herbaria

{

	begin_target

	use_local_remote

	local table="$target_stem"

	if remaking; then mk_drop|mysql; fi

	herbaria_filter.ih.csv_/make

	(benign_error=1 mysql <<'EOF'

CREATE TABLE `ih_herbarium` (

 `institution_code` varchar(255) NOT NULL PRIMARY KEY

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='from IH.vegpath.org?&Index+Herbariorum'

EOF

) || { skip_table; return 0; }

	append=1 mysql_import <"$top_dir"/herbaria_filter.ih.csv_

}

plant_fraction/make() # runtime: 1 hr ("10603 rows affected (53 min 44.68 sec)")

{

	begin_target; mk_table_esc

	use_local_remote

	if remaking; then table=plant_fraction table_esc= mk_drop|mysql; fi

	(benign_error=1 mysql <<'EOF'

CREATE TABLE `plant_fraction` (

 `institution_code` varchar(255) NOT NULL PRIMARY KEY,

 `plant_fraction` float NOT NULL,

 KEY `plant_fraction` (`plant_fraction`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

EOF

) || { table=plant_fraction skip_table; return 0; }

	mysql <<EOF

INSERT INTO "plant_fraction"

SELECT

  institution_code

, COUNT(NULLIF(

	family LIKE '%aceae%' OR family IN ('Compositae','Gramineae','Palmae','Guttiferae','Cruciferae','Labiatae','Umbelliferae','Leguminosae')

	, false))/COUNT(*)

	AS plant_fraction

FROM ${limit:+(SELECT * FROM }$table_esc ${limit:+$(prefix= limit)) s}

GROUP BY institution_code

ORDER BY institution_code

EOF

}

plant_fraction/seal()

{

	begin_target

	use_local_remote

	table=plant_fraction table_esc= mysql_seal_table

}

plant_fraction_for_herbaria_filter/make()

{

	begin_target; mk_table_esc

	plant_fraction/make

	use_local_remote

	if remaking; then

		table=plant_fraction_for_herbaria_filter table_esc= mk_drop|mysql

	fi

	(benign_error=1 mysql <<'EOF'

CREATE VIEW `plant_fraction_for_herbaria_filter` AS

SELECT * FROM `plant_fraction`

WHERE plant_fraction >= 0.8

EOF

) || { table=plant_fraction_for_herbaria_filter skip_table; return 0; }

}

herbaria_filter/make() # runtime: ~0 ("0m2.235s"): CSV import, index scans only

{

	begin_target

	use_local_remote

	local table="$target_stem"

	if remaking; then mk_drop|mysql; fi

	ih_herbarium/make

	plant_fraction_for_herbaria_filter/make

	(benign_error=1 mysql <<'EOF'

CREATE TABLE `herbaria_filter` (

 `institution_code` varchar(255) NOT NULL PRIMARY KEY

) ENGINE=MyISAM DEFAULT CHARSET=utf8

EOF

) || { skip_table; return 0; }

	mysql <<EOF

INSERT IGNORE INTO $table

SELECT institution_code FROM ih_herbarium

;

INSERT IGNORE INTO $table

SELECT institution_code FROM plant_fraction_for_herbaria_filter

;

EOF

}

herbaria_filter/seal()

{

	begin_target

	use_local_remote

	table=herbaria_filter table_esc= mysql_seal_table

}

table/make() # makes the filter view

{

	begin_target; mk_table_esc

	herbaria_filter/make

	use_local_remote

	if remaking; then mk_drop|mysql; fi

	(benign_error=1 mysql <<EOF

CREATE VIEW $table AS

SELECT * FROM $full_table

WHERE deleted IS NULL

AND institution_code IN (SELECT * FROM herbaria_filter)

AND (

	    family IS NULL

	OR  (family LIKE '%aceae%' OR family IN ('Compositae','Gramineae','Palmae','Guttiferae','Cruciferae','Labiatae','Umbelliferae','Leguminosae'))

)

EOF

) || { skip_table; return 0; }

}

# table.tsv.gz/upload() runtime: 15 min ("2184253533 100% 2.30MB/s 0:15:03")

# table.tsv.gz/make() runtime: 35 min ("34m55.802s") w/o upload

func_override table.tsv/make__src_table_run

table.tsv/make() # usage: [remake=1] [limit=...] .../run table.tsv/make

# note that incremental output is provided right away with --quick (unbuffered),

# but takes awhile to become visible in Macfusion sshfs

#

# to view runtime when using `screen`:

# press Ctrl-A [ , use arrow keys/Ctrl-B/Ctrl-F, and then Esc to leave copy mode

{

	begin_target; check_target_exists

	table/make

	with_rm table.tsv/make__src_table_run

}

# import() runtime: 5 h ("294m10.322s") @starscream w/o table.tsv.gz/make()

# - table cleanup runtime: 1.5 h ("1:29:35.949391 sec") @starscream

fi