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

root := $(selfDir_uZPPqC)..

include $(root)/lib/common.Makefile

##### Configuration

# Command line

continue ?=

debug ?=

full_import ?=

import_source ?= 1

is_view ?=

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

noclobber ?=

profile ?=

quiet ?=

reverify ?= 1

schema_only ?=

use_staged ?= $(by_col)

# Makefile

exts ?= csv tsv tab txt dat dmp

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)

+_ = $(+:_%=)

# used to filter the output of embedded $(shell make ...) invocations

filter_make := grep -vF -e lib -e ".Makefile'."

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 $(noImportFile))$(wildcard $(1)/$(noImportFile))$(if\

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

ifeq ($(sort_file_updated),)# keep $(sortFile) up-to-date

$(shell sort_file_updated=1 $(selfMake) $(sortFile)|$(filter_make) >&2)

export sort_file_updated=1

endif

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

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

tables := $(sort_file_tables)

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

has_visible_files = $(call wildcard/,$(1)/*)

allTables := $(foreach table,$(allTables),$(if\

$(call has_visible_files,$(table)),$(table)))

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

tables := $(allTables)

endif

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

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

list_tables: _always # use `make -s` to avoid echoing commands

	@for table in $(tables); do echo "$$table"; done

$(sortFile): _always

	# add any missing tables to $(sortFile)

	$(if $(filter-out $(sort_file_tables),$(tables)),$(selfMake) -s list_tables >$@)

##### SVN

svnFilesGlob:= */{,{,.}{data,map,VegBIEN}.csv{,.*},*header.*,*.sql,test.xml.ref}

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

_svnFilesGlob := {_MySQL/{,*.make},{,*/}{$(noImportFile),*{run,Makefile},*.{md5,url},*README.TXT}}

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

$(call wildcard/,$(_svnFilesGlob))

add: _always $(if $(call dontImport,.),,add!) ;

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

add!: _always Source/add $(allTables:%=%/add)

	$(call setSvnIgnore,.,'*')

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

	$(call addDirWithIgnore,verify,$$'*.csv\n*.out\n*.tsv\n*.txt\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,$$'*.gz\n*.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 := $(tables:%=%/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

catSrcs = $(bin)/cat_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 := $(filter-out grants.sql,$(dbExports))

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

allInstalls := $(if $(dbExports),sql) $(allTables)

install: _always

	$(if $(wildcard ./run),./run,+$(selfMake) install_oldstyle)

install_oldstyle: _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

log_dir = $(1)logs

has_log_dir = $(call and,$(wildcard $(log_dir)),$(call not,$(noclobber)))

logInstall = $(if $(has_log_dir),$(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)

	set -o pipefail; ($(inDatasrc); cat $+|pg_dump_limit; $(if $(wildcard\

grants.sql),cat grants.sql))|"time" env no_search_path=1 \

$(bin)/psql_$(if $(debug),verbose,script)_vegbien --set=schema='"$(datasrc)"' \

$(logInstallRoot)

# $debug option runs the *.sql import verbosely, to display which statements are

# being run. this should only be used for SQL files that use COPY FROM to import

# data, to avoid echoing pages of insert statements.

cleanup = set -o pipefail; \

$(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:

	set -o pipefail; \

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.sql: $(thesaurus) _always

	$(if $(wildcard $*/run),$(bin)/in_place $@ env text=1 $(bin)/repl $<)

%/postprocess: _always

	$(if $(wildcard $*/run),,$(selfMake) "$*/postprocess.sql/run")

%/map_table: _always

	$(if $(wildcard $*/run),$*/run map_table)

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

%/install: %/create.sql _always

	set -o pipefail; \

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

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

$(logInstall*)

	$(selfMake) "$*/postprocess"

	$(exportHeader)

	$(cleanup)

%/install: _always

	$(import_install_)

	$(selfMake) "$*/postprocess"

	$(exportHeader)

	$(cleanup)

define import_install_

(. $(bin)/vegbien_dest; unset schemas; "time" $(nice)\

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 $(if\

$(is_view),VIEW,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 $@),,$(mk_map_csv))

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

define mk_map_csv

+$(selfMake) "$*/header.csv"

$(mkSrcMap)

endef

endif

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

	$(if $(wildcard $*/run),-ln -s "../$(coreMap)" "$@"\

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

# ignore errors if symlink exists

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 := $(tables)

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 source=$(datasrc).new $(root)/map $(+maps)

##### Import to VegBIEN

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

import: _always import_temp publish validate ; # removes temp suffix when done

rm: _always

	echo "SELECT datasource_rm('$(datasrc)');"|"time" $(psqlAsBien)

reimport: _always import ; # import replaces the previous 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)

restart_row = $(shell set -x; grep -F Partition: $(log_)|tail -1|$(sed)\

's/^.* rows ([[:digit:]]+)-.*$$/\1/')

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

$(if $(profile),profile_to=$(trace)) $(if $(continue),start=$(restart_row))\

$(map2db))$(if $(log), >>$(log_) 2>&1))

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

%/import_temp: %/VegBIEN.csv _always

	$(if $(full_import),-)$(import?)

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

# default:

%/import_temp: _always ;

%/import: %/import_temp _always ;

#### Taxonomic scrubbing

scrub: _always

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

# using & (background process) also ignores TNRS errors, so that TNRS bugs do

# not prevent the remaining tables from being imported even if TNRS can't be run

# 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 ;

%/import_scrub: _always %/import scrub ;

##### Log files from import

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

rm_logs: _always

	$(RM) $(logs)

##### Verification of import

### new-style aggregating validations (also work w/ old-style import)

# must be in input.Makefile instead of table.run because some datasources that

# we validate still use old-style import

validate/install: _always validations.sql/run ;

validate: _always

	echo "SELECT remake_diff_tables('$(datasrc)');"\

|$(psqlAsBien)$(if $(log), >>.$(log_) 2>&1)

### old-style aggregating validations (not related to old-style 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: verify/%.out.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)

%/publish: _always # usage: make inputs/src/src.version/publish

	echo "SELECT datasource_publish('$*');"|$(psqlAsBien)

publish: _always $(datasrc).new/publish ; # usage: make inputs/src/publish

##### 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 $(if $(by_col),%/test.by_col.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

	$(call runTest,by_col=)

tests += %/test.xml

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

	$(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),$(tables:%=$(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)