selfDir_uZPPqC := $(dir $(lastword $(MAKEFILE_LIST)))

root := $(selfDir_uZPPqC)..

include $(root)/lib/common.Makefile

##### Configuration

# Command line

full_import ?=

import_source ?= 1

log ?= $(if $(test),,1)

profile ?=

quiet ?=

reverify ?= 1

schema_only ?=

use_staged ?= $(by_col)

# Makefile

exts ?= csv tsv tab txt dat dmp xml

test_n ?= 2

##### Vars/functions

# Paths

datasrc := $(patsubst .%,%,$(notdir $(realpath .)))

bin := $(root)/bin

mappings := $(root)/mappings

# Make

SHELL := /bin/bash

selfMake = $(MAKE) --makefile=../input.Makefile

subMake = $(MAKE) $(@:$(root)/%=%) --directory=$(root)

+_ = $(+:_%=)

addBeforeExt = $(basename $(2))$(1)$(suffix $(2))

# Terminal

termCols := $(shell tput cols)

# Commands

MKDIR = mkdir -p

mkdir = $(MKDIR) $(@D)

diff = diff --unified=2

diffIgnoreSpace = $(diff) --ignore-space-change

diffVerbose = $(if $(verbose),diff --side-by-side --left-column\

--width=$(termCols),$(diff))

# BIEN commands

sortFilenames = $(shell $(bin)/sort_filenames $(1))

selfMap = $(bin)/cols 0 0

psqlAsBien := $(bin)/psql_verbose_vegbien

psqlNoSearchPath := env no_search_path=1 $(psqlAsBien)

# Usage: ($(inDatasrc); cat $(file))|$(psqlCmd)

inDatasrc := echo 'SET search_path TO "$(datasrc)";'

# SVN

setSvnIgnore = svn propset svn:ignore $(2) $(1)

define addDirWithIgnore

$(addDir)

$(setSvnIgnore)

endef

##### Environment

export PATH := $(bin):$(PATH)

##### General targets

all: _always maps ;

clean: _always

	$(RM) $(all)

remake: _always clean

	+$(selfMake)

# re-run make so that cache of existing files is reset

# Only remake if doesn't exist. This prevents unintentional remaking when the

# make script is newly checked out from svn (which sets the mod time to now) but

# the output is synced externally.

# Can't remove prereq to do this, because it determines when the rule applies.

make_script = $(if $(wildcard $@),,"time" ./$< >$@)

%/: %/map.csv _always ;

%/: % _always ;

%: %.make

	$(make_script)

.PRECIOUS: % # save partial outputs of aborted src make scripts

##### Tables discovery

sortFile := import_order.txt

noImportFile := _no_import

dontImport = $(wildcard $(1)/$(noImportFile))$(if\

$(import_source),,$(filter Source,$(1)))

tables := $(if $(wildcard $(sortFile)),$(shell cat $(sortFile)))

    # $(shell) replaces "\n" with " "

allSubdirs := $(call wildcard/,*/)

allTables := $(call sortFilenames,$(filter-out _% verify logs,$(allSubdirs:%/=%)))

joinedTables := $(filter-out $(tables),$(allTables))

allTables := $(strip $(joinedTables) $(tables))# move joined tables to beginning

ifeq ($(tables),)# none specified in sort file

tables := $(allTables)

endif

importTables := $(foreach table,$(tables),$(if\

$(call dontImport,$(table)),,$(table)))

##### SVN

svnFilesGlob:= */{,$(noImportFile),{,.}{map,VegBIEN}.csv{,.*},*header.*,*.sql,test.xml.ref}

svnFilesGlob := {map.csv,*{schema,~}*.sql,{,*/}{*.make,*terms.csv},$(svnFilesGlob)}

_svnFilesGlob := {_MySQL/{,*schema*.sql,*.make},{,*/}{*.{md5,url,pdf},*README.TXT}}

svnFiles = $(filter-out _% logs/%,$(call wildcard/,$(svnFilesGlob)))\

$(call wildcard/,$(_svnFilesGlob))

# To update all inputs with these settings: make inputs/add

add: _always Source/add Source/map.csv $(allTables:%=%/add)

	$(call setSvnIgnore,.,'*')

	$(call addDirWithIgnore,logs,$$'*.log.sql\n*.trace')

	$(call addDirWithIgnore,verify,$$'*.out\n*.csv\n*.xls\n*.xlsx')

	$(call addFile,import_order.txt)

	$(if $(wildcard _MySQL/),$(call addDirWithIgnore,_MySQL,'*'))

	$(if $(wildcard _src/),$(call addDirWithIgnore,_src,'*'))

	$(if $(wildcard _archive/),$(call addDirWithIgnore,_archive,'*'))

	$(call add*,$(svnFiles))

# Adds a new table subdir

%/add: _always

	$(call addDirWithIgnore,$*,'*')

	$(call addDirWithIgnore,$*/logs,$$'*.log.sql\n*.trace')

##### Existing maps discovery

anyMap := %/map.csv %/VegBIEN.csv %/unmapped_terms.csv %/new_terms.csv

exts := $(call ci,$(exts))

extsFilter := $(addprefix %.,$(exts))

dataOnly = $(filter $(extsFilter),$(1))

anyTest = $*/test.%

srcsOnly = $(filter-out $(anyMap) $(anyTest) %/logs,$(call dataOnly,$(1)))

srcDict := map.csv

vocab := $(mappings)/VegCore.vocab.csv

thesaurus := $(mappings)/VegCore.thesaurus.csv

coreMap := $(mappings)/VegCore-VegBIEN.csv

viaMaps := $(importTables:%=%/map.csv)

autogenMaps := $(subst map.,VegBIEN.,$(viaMaps))

directMaps := $(autogenMaps) $(filter-out $(autogenMaps),\

$(wildcard */VegBIEN.csv))

##### Sources

srcs = $(call sortFilenames,$(call srcsOnly,$(wildcard $*/*)))

nonHeaderSrcs = $(filter-out %/header.csv,$(srcs))

isRef = $(if $(nonHeaderSrcs),,1)

    # empty subdir, so references an already-installed staging table

isXml = $(filter %.xml,$(nonHeaderSrcs))

nonXml = $(if $(isXml),,1)

isCsv = $(if $(nonHeaderSrcs),$(if $(isXml),,1))

    # true if $(srcs) non-empty and contains no *.xml

catSrcs = $(bin)/cat$(if $(nonXml),_csv) $(nonHeaderSrcs)

withCatSrcs = $(catSrcs:$(bin)/%=$(bin)/with_%) --

# Run with `make -s` to avoid echoing make commands

cat: $(importTables:%=%/cat) _always ;

%/cat: _always

	$(catSrcs)

##### Input data retrieval

# Must come before `%.sql: _MySQL/%.sql` to override it

%.sql: %.sql.make

	$(make_script)

# The export must be created with:

# `--compatible=postgresql --add-locks=false --set-charset --no-create-info`

# Must come before `%.sql: _MySQL/%.sql` to override it

%.data.sql: _MySQL/%.data.sql

	$(if $(wildcard $@),,$(bin)/my2pg.data <$< >$@)

# The export must be created with:

# `--compatible=postgresql --add-locks=false --set-charset`

# Add `--no-data` to create a schema-only export.

%.sql: _MySQL/%.sql

	$(if $(wildcard $@),,$(bin)/my2pg <$< >$@)

##### Staging tables installation

srcTable := %.src

dbExportsWildcard = $(sort $(patsubst _MySQL/%.make,%,$(wildcard\

$(1) _MySQL/$(1).make)))

dbExports := $(call dbExportsWildcard,*schema*.sql)# schemas first

ifeq ($(schema_only),) # add rest of .sql files

dbExports += $(filter-out $(dbExports),$(call dbExportsWildcard,*.sql))

endif

dbExports := $(strip $(dbExports))# += adds extra whitespace

allInstalls := $(if $(dbExports),sql) $(filter-out Source,$(allTables))

install: _always schema $(allInstalls:%=%/install) ;

uninstall: _always confirm_rm_schema rm_schema ;

# rm_schema will also drop all staging tables

reinstall: _always uninstall install ;

confirm_rm_schema: _always

	$(if $(filter TNRS,$(datasrc)),$(call confirm,WARNING: This will delete the\

TNRS cache!,To save it: make backups/TNRS.backup-remake))

schema: _always

	-echo 'CREATE SCHEMA "$(datasrc)";'|$(psqlNoSearchPath)

# ignore errors if schema exists

rm_schema: _always

	echo 'DROP SCHEMA IF EXISTS "$(datasrc)" CASCADE;'|$(psqlNoSearchPath)

installLog := logs/install.log.sql

logInstall = $(if $(quiet),$(2)$(1)$(installLog) 2>&1,2>&1|tee $(3)\

$(1)$(installLog))

logInstallRoot = $(call logInstall,,>)

logInstall* = $(call logInstall,$*/,>)

logInstall*Add = $(call logInstall,$*/,>>,-a)# append to log

# Must come before %/install to override it

sql/install: $(dbExports)

	($(inDatasrc); cat $+|pg_dump_limit)|"time" $(psqlNoSearchPath) \

--set=schema='"$(datasrc)"' $(logInstallRoot)

cleanup = $(if $(wildcard $*/cleanup.sql),($(inDatasrc); cat $*/cleanup.sql)\

|"time" $(psqlNoSearchPath) --echo-all --set=table='"$*"' $(logInstall*Add),\

(export schema=$(datasrc) table=$*; . $(bin)/vegbien_dest; unset schemas; \

$(bin)/csv2db) $(logInstall*Add))

%/header.csv:

	echo 'COPY (SELECT * FROM "$(datasrc)"."$*" LIMIT 0) TO STDOUT CSV HEADER;'|\

env no_search_path=1 $(bin)/psql_script_vegbien >$*/header.csv

exportHeader = $(selfMake) $*/header.csv

# Don't try to edit a view. Must come before %/install to override it.

%_view/install: _always ;

%.sql/run: _always

	$(if $(wildcard $(@D)),($(inDatasrc); cat $(@D))|(cd '$(*D)';\

"time" env no_search_path=1 ../$(bin)/psql_verbose_vegbien \

--set=table='"$(*D)"' --set=table_str=\''"$(*D)"'\'))

%/postprocess: _always %/postprocess.sql/run

	$(if $(wildcard $*/import),$*/import)

# For staging tables which are derived by joining together other staging tables.

%/install %/header.csv: %/create.sql _always

	($(inDatasrc); echo 'CREATE TABLE "$*" AS'; cat $<; echo ';')|"time" \

$(psqlNoSearchPath) --echo-all --set=schema='"$(datasrc)"' --set=table='"$*"' \

$(logInstall*)

	$(selfMake) $*/postprocess

	$(exportHeader)

	$(cleanup)

.PRECIOUS: %/header.csv

%/install: _always

	$(if $(nonXml),$(import_install_))

	$(selfMake) $*/postprocess

	$(exportHeader)

	$(cleanup)

define import_install_

(. $(bin)/vegbien_dest; unset schemas; "time" nice -n +5\

env schema=$(datasrc) table=$* $(bin)/csv2db $(catSrcs) $(logInstall*))

$(if $(filter $(srcTable),$*),($(inDatasrc);\

echo 'ALTER TABLE "$(datasrc)"."$*" RENAME row_num TO "$*.row_num";')|"time"\

$(psqlNoSearchPath) --echo-all --set=table='"$*"' $(logInstall*Add))

endef

# table-scope src table's row_num col to allow joining it with other tables

%/uninstall: _always

	echo 'DROP TABLE IF EXISTS "$(datasrc)"."$*" CASCADE;'|$(psqlNoSearchPath)

%/reinstall: _always %/uninstall %/install ;

postprocess: _always $(allTables:%=%/postprocess) ;

cleanup: _always $(tables:%=%/cleanup) ;

# WARNING: This removes any index comments, due to a PostgreSQL bug.

# This occurs because ALTER TABLE recreates the index but not its comment.

%/cleanup: _always

	$(cleanup)

##### Maps building

# Maps to (try to) build are added to this

maps :=

srcRoot = $(mappings)/root.sh

mkSrcMap = (. $(srcRoot); env datasrc=$(datasrc) $(bin)/src_map <$*/header.csv\

>$@)

translate = $(if $(wildcard $(1)),$(bin)/in_place $< $(bin)/translate_ci 1 $(1))

$(srcDict):# empty target in case it doesn't exist

# Via maps cleanup

ifneq ($(filter %/.map.csv.last_cleanup,$(MAKECMDGOALS)),)

%/.map.csv.last_cleanup: %/map.csv $(vocab) $(thesaurus) $(coreMap) $(srcDict)

	$(call translate,$(srcDict))

	$(bin)/in_place $< $(bin)/canon 1 $(vocab)

	$(call translate,$(thesaurus))

	$(bin)/in_place $< $(bin)/fix_line_endings

	touch $@

	+$(selfMake) $(<:%/map.csv=%/unmapped_terms.csv)

	+$(selfMake) $(<:%/map.csv=%/new_terms.csv)

.PRECIOUS: %/.map.csv.last_cleanup

else

%/map.csv: _always

	$(if $(wildcard $@),,$(if $(nonXml),$(mkSrcMap)))

	+$(selfMake) $(@:%/map.csv=%/.map.csv.last_cleanup)

.PRECIOUS: %/map.csv

endif

%/VegBIEN.csv: %/map.csv $(coreMap)

	<$< $(bin)/cat_cols 1 2|$(bin)/join $(coreMap)|$(bin)/sort_map >$@

maps += $(autogenMaps)

maps: $(maps) _always ;

all += $(maps)

##### Maps validation

# `tail -n +2`: Remove header before running filter_out_ci because filter_out_ci

# only removes the header if it matches the vocabulary's header.

%/unmapped_terms.csv: %/map.csv $(coreMap)

	tail -n +2 $<|$(bin)/cols 1|$(bin)/filter_out_ci 0 $(coreMap) >$@

	$(bin)/autoremove $@

%/new_terms.csv: %/map.csv $(vocab) $(thesaurus) %/unmapped_terms.csv

	$(newTerms)

	$(bin)/autoremove $@

newTerms = tail -n +2 $<|$(bin)/filter_out_ci 0 $(vocab)|$(bin)/filter_out_ci 0\

$(thesaurus) $(if $(wildcard $(word 4,$+)),|$(bin)/filter_out_ci 0 $(word 4,$+))\

|grep -vE -e '^"?:' -e 'UNUSED' >$@; exit 0 # grep exits nonzero if no match

termsSubdirs := $(importTables)

include $(root)/lib/mappings.Makefile

##### External dependencies

$(root)/%: _always

	+$(subMake)

.PRECIOUS: $(root)/% # let ext. dir's Makefile decide whether to delete on error

##### Mapping

+maps = $(filter %/map.csv %/VegBIEN.csv $(mappings)/%,$(+_))

map2db = env in_database=vegbien in_schema=$(datasrc) in_table=$*\

out_database=vegbien $(root)/map $(+maps)

##### Import to VegBIEN

import: $(importTables:%=%/import) _always ;

rm: _always

	echo "DELETE FROM source WHERE shortname = '$(datasrc)';"\

|"time" $(psqlAsBien)

reimport: _always rm import ;

profileTest = $(if $(profile),$(if $(test),1))

profileOnly = -env profile_to=/dev/fd/3 $(map2db) 3>&1 1>&2|\

$(bin)/profile_stats /dev/fd/0

log_ = $*/logs/$(if $(n),n=$(n).,)$(version).log.sql

# Displays the import log file path

# Run with `make -s` to avoid echoing make commands

%/log_file: _always

	echo $$(pwd)/$(log_)

trace = $(log_:.log.sql=.trace)

import = -$(if $(profileTest),$(profileOnly),(set -x; date; "time" env commit=1\

$(if $(profile),profile_to=$(trace)) $(map2db))$(if $(log), >>$(log_) 2>&1))

# don't abort on import errors, which often relate to invalid input data

import? = $(if $(call and,$(full_import),$(call dontImport,.)),,$(import))

%/import: %/VegBIEN.csv _always

	$(import?)

# default:

%/import: _always ;

#### Taxonomic scrubbing

scrub: _always

	$(selfMake) $(root)/scrub & echo $$!

# Removes TNRS taxondeterminations

unscrub: _always

	echo "SELECT delete_scrubbed_taxondeterminations('$(datasrc)');"|\

"time" env no_query_results=1 $(psqlAsBien)

rescrub: _always unscrub scrub ;

import_scrub: _always import scrub ;

reimport_scrub: _always reimport scrub ;

##### Log files from import

logs := $(wildcard */logs/*.log.sql */logs/*.trace)

rm_logs: _always

	$(RM) $(logs)

##### Verification of import

verifyTables := $(patsubst verify/%.ref,%,$(wildcard verify/*.ref))

verify: $(verifyTables:%=%/verify) _always ;

%/verify: verify/%.ref verify/%.out _always

	-$(diffVerbose) $(+_)

# don't abort on verification errors, which are expected during development

# default:

%/verify: verify/%.out _always

	$(if $(shell test -e $< && echo t),cat $<)

# don't run if verify/%.out's default do-nothing action was used

# can't use $(wildcard) because it won't recheck file after verify/%.out is run

psqlExport := "time" $(psqlNoSearchPath) --no-align --field-separator=$$'\t'\

--pset=footer=off --pset=null=NULL

# Note that using $(inDatasrc) will not work with datasources whose tables are

# the same name as VegBIEN tables (likely only VegBank), because the datasource

# is first in the search_path.

verify = $(if $(reverify),($(inDatasrc); cat $<)|$(psqlExport)\

--set=datasource="'$(datasrc)'" >$@)

verify/%.out: $(mappings)/verify.%.sql _always

	$(verify)

.PRECIOUS: verify/%.out # save partial output in case of error to help debugging

# default:

verify/%.out: _always ;

all += $(wildcard verify/*.out)

%.ref: %.ref.sql

	($(inDatasrc); cat $<)|$(psqlExport) >$@

.PRECIOUS: %.ref # there must always be a .ref for the make rules to work

##### Editing import

rename/%: _always

	echo "UPDATE source SET shortname = '$*' WHERE shortname = '$(datasrc)';"\

|$(psqlAsBien)

##### Testing

testRefOutput = $(subst .by_col,,$(1))

testRef = $(testRefOutput).ref

hasOwnRef = $(filter $@,$(call testRefOutput,$@))

# filter returns non-empty if they are equal

# `rm $@`: Remove outputs of successful tests to reduce clutter

# `$(foreach use_staged...)`: Run with use_staged=1

define runTest

@echo "Testing $(abspath $@)..."

>$@ env test=1 n=$(test_n) $(1) $(foreach use_staged,1,$(map2db))

$(if $(hasOwnRef),,-)@(set -x; $(diffIgnoreSpace) $(call testRef,$@) $@) 2>&1\

&& rm $@ || { e=$$?; $(if $(wildcard $(call testRef,$@)),,cat $@;)\

$(if $(hasOwnRef),\

{\

read -p $(emph)'Accept new test output? (y/n)'$(endEmph) REPLY;\

if test "$$REPLY" = y; then\

(set -x; $(MAKE) $@-ok --directory=$(realpath .) --makefile=../input.Makefile);\

exit 0;\

fi;\

};,\

echo $(emph)"Note: The preceding failed test is compared to another test's\

output"$(endEmph);\

echo $(emph)"When it fails, this always indicates a bug"$(endEmph);\

)\

exit $$e;}

endef

tests :=

%/test: %/test.xml _always ;

# Requires staging tables. To create them, run `make inputs/<datasrc>/install`.

# Non-flat-file inputs fall back to mimicking a successful test

%/test.xml: %/VegBIEN.csv _always

	$(if $(nonXml),$(call runTest,by_col=))

tests += %/test.xml

%/test.by_col.xml: %/VegBIEN.csv _always

	$(if $(nonXml),$(call runTest,by_col=1))

# Only run column-based tests if column-based mode enabled, because these tests

# are much slower than the row-based tests for small numbers of rows

ifneq ($(by_col),)

tests += %/test.by_col.xml

endif

testOutputs := $(foreach test,$(tests),$(importTables:%=$(test)))

.PRECIOUS: $(testOutputs) # save outputs of failed tests so they can be accepted

test: _always $(testOutputs) ;

all += $(wildcard %/test*.xml)

# Accepts a test output: make <test_output_path>-ok

%-ok: _always

	mv $* $(call testRef,$*)

accept-all: _always

	+yes|$(selfMake) test

##### Documentation

steps = $(selfMake) -s $*/import test=1 by_col=1 verbosity=2 n=100\

2>&1|$(bin)/debug2redmine >$@

%/logs/steps.by_col.log.sql: _always

	+$(steps)