# Take Henry's record database from ASCII export files to MySQL.

default: msg

msg:
	@echo \
        "First run './transfer <LATEST_QA4_DIRECTORY>', then run 'make data.'"

# Change from CRLF to LF format, and remove the final ^Z (ASCII 26)
# from each file.  This rule is meant to be run manually.
sanitize:
	@sed -e 's/[\r\d026]$$//g' < records.asc > tmp && mv tmp records.asc
	@sed -e 's/[\r\d026]$$//g' < orch.asc    > tmp && mv tmp orch.asc
	@sed -e 's/[\r\d026]$$//g' < instrum.asc > tmp && mv tmp instrum.asc
	@sed -e 's/[\r\d026]$$//g' < chamber.asc > tmp && mv tmp chamber.asc
	@sed -e 's/[\r\d026]$$//g' < song.asc    > tmp && mv tmp song.asc
	@sed -e 's/[\r\d026]$$//g' < oprec.asc   > tmp && mv tmp oprec.asc
	@sed -e 's/[\r\d026]$$//g' < opera.asc   > tmp && mv tmp opera.asc

upload: song.xml chamber.xml opera.xml orchestral.xml henryrec.sql all-completable-lists.js
	@./upload-all

# Convert QA ASCII exports to an XML format, for publication.
xml: song.asc oprec.asc chamber.asc records.asc
	@echo "Converting ASCII exports to XML (watch for CHECK warnings)..."
	@./asc-to-xml.pl --song < song.asc > song.xml
	@./asc-to-xml.pl --chamber < chamber.asc > chamber.xml
	@./asc-to-xml.pl --opera < oprec.asc > opera.xml
	@./asc-to-xml.pl --orchestral < records.asc > orchestral.xml
	@echo "Done."

# There's one file we don't publish, because it's idiosyncratic and
# incomplete.  We change its name right now, so that oprec.asc (which
# we *do* publish) can become opera.xml without confusing us.
hide: 
	@if [ -f opera.asc ]; then mv -f opera.asc forbidden-opera.asc; fi

mussorgskize:
	@for name in orchestral chamber song opera; do      \
          sed -e 's/MOUSSORGSKY/Mussorgsky/g'               \
	      < $${name}.xml > tmp && mv tmp $${name}.xml;  \
          sed -e 's/Moussorgsky/Mussorgsky/g'               \
	      < $${name}.xml > tmp && mv tmp $${name}.xml;  \
          sed -e 's/moussorgsky/Mussorgsky/g'               \
	      < $${name}.xml > tmp && mv tmp $${name}.xml;  \
        done

# Convert the XML format to SQL, for searching.
sql: hide mussorgskize
	@echo "Converting XML files to SQL..."
	@./make-sql
	@echo "Done."

# Load the SQL into the database.
db: sqlite3
sqlite: sqlite3
sqlite3:
	@echo "Creating and loading the SQLite database..."
	@if [ -f henryrec.sqlite3 ]; then             \
          mv henryrec.sqlite3 SAFE_henryrec.sqlite3;  \
        fi
	@# # We set sqlite3's SYNCHRONOUS and JOURNAL pragmas to OFF
	@# # for the duration of the data load.  This speeds up the 
	@# # load by a factor of approximately a gazillion.  Seriously,
	@# # it's like night and day -- half an hour versus 1 second.
	@# # There's no point syncing to to disk on every insert nor
	@# # staging every step in a journal log file when all we're
	@# # doing is loading an SQL file that we already have.  See
	@# # https://www.sqlite.org/pragma.html for more information.
	@# # 
	@# # I don't think end-pragmas is actually necessary, because
	@# # the pragmas in start-pragmas are only in effect for the
	@# # duration of this sqlite3 command anyway.  But I'd prefer
	@# # to leave the campground as we found it, in case we ever
	@# # need to chain more sql files in the future.
	@cat start-pragmas create_henryrec.sql henryrec.sql end-pragmas \
             | sqlite3 henryrec.sqlite3
	@echo "Done."