specdb

#!/usr/bin/env python3

"""
SpecDB Command Line Interface.
"""

import argparse
import json
import os
import sys

from specdb import Create, Forms, Insert, Query, Summary, Backup

env_keys = ['SPECDB_DB', 'SPECDB_ROOT', 'SPECDB_BACKUP', 'SPECDB_GS_CRED']

examples = """
example command lines:
  %(prog)s create --db my.db --backup /backups/my.backup.db
  %(prog)s query --db my.db --sql "SELECT * FROM table users LIMIT 10"
  %(prog)s insert --file specdb.yaml --env my.db --write
  %(prog)s backup --db my.db --backup /backups/my.backup.db
  %(prog)s forms --table user project --num 3 1
  %(prog)s summary --table user --db my.db
"""

# top-level parser 
sdb_top = argparse.ArgumentParser(prog='specdb',
	description='Command line tool for interfacing with SpecDB',
	formatter_class=argparse.RawDescriptionHelpFormatter,
    epilog=examples)
sdb_subs = sdb_top.add_subparsers(dest='command', help='command description')
sdb_subs.required = False

# configure level parser
sdb_configure = sdb_subs.add_parser('create',
	help='instantiate a new database')
sdb_configure.add_argument('--db', type=str, metavar='<path>', required=True,
	help='path and name for a new database to be made created')
sdb_configure.add_argument('--backup', type=str, metavar='<path>',
	required=True, help='path where incremental backup will be saved')

# forms level parser
sdb_forms = sdb_subs.add_parser('forms',
	help=''.join((
		("generate a template form for requested table\n"),
		('tables to generate forms for are: `user`, `project`, `target` '),
		('`construct`, `expression`, `batch`, `buffer`, `pst`, '),
		('`spectrometer`, and a JSON for a session'))))
sdb_forms.add_argument('--table', nargs='+', metavar='<str>', required=True,
	help=''.join((
		('tables to generate a form for\n'),
		('tables to generate forms for are: `user`, `project`, `target` '),
		('`construct`, `expression`, `purification_batch`, `buffer`, '),
		('`buffer_components`, `pst`, `batch_components`, `spectrometer`, and'),
		(' `session`'))))
sdb_forms.add_argument('--num', nargs='+', metavar='<int>', required=False,
	default=None, help=''.join((
		('number of forms to generate for each requested table'))))

# insert level parser
sdb_insert = sdb_subs.add_parser('insert',
	help='insert a single json file into SpecDB')
sdb_insert.add_argument('--form', type=str, metavar='<path>', required=True,
	help='path to a single yaml file to insert')
sdb_insert.add_argument('--db', type=str, metavar='<path>', required=True,
	help=''.join((
		('path to specific specdb database file to use for'),
		(' insertion. use `specdb create` to create the .db file'))))
sdb_insert.add_argument('--write', action='store_true', required=False,
	help=''.join((
		('write new/altered information in JSON to database, used only once'))))

# sumary level parser
sdb_summary = sdb_subs.add_parser('summary', 
	help=''.join((
		("make a summary report for SpecDB database. if ran with no"),
		(" table provided, then a summary for every table is made."))))
sdb_summary.add_argument('--db', type=str, metavar='<path>', required=True,
	help=''.join((
		('path to specific specdb database file to use.'),
		(' use `specdb create` to create a new database file'))))
sdb_summary.add_argument('--table', type=str, metavar='<str>', required=True,
	help='provide which table a summary is needed for')

# query level parser
sdb_query = sdb_subs.add_parser('query',
	help=''.join((
		("query records from SpecDB summary table. If no --output is given"),
		(" then results are simply print to screen"))))
sdb_query.add_argument('--sql', nargs='+', type=str, metavar='<str>',
	required=False, default=False, help=''.join((
		('query using sql syntax. The query can be on any table.'),
		(' If no --output format is given, results are printed to screen.'))))
sdb_query.add_argument('--star', action='store_true', required=False,
	help='whether to write a NMR-STAR file for each FID in the query results')
sdb_query.add_argument('--db', type=str, metavar='<path>', required=True,
	help=''.join((
		('path to specific specdb database file to use.'),
		(' use `specdb create` to create a new database file'))))
sdb_query.add_argument('--out', type=str, metavar='<path>', required=False,
	help='directory to place results of the query')
sdb_query.add_argument('--indices', type=str, metavar='<str>', nargs='+',
	required=False, default=None, help=''.join((
		("provide a list of row ids in the summary table to collect\n"),
		("users can provide a list of ids directly on the command line space "),
		("separated, or in a .csv file with all ids comma separated "),
		("on first line"))))

# backup level parser
sdb_backup = sdb_subs.add_parser('backup',
	help='perform incremental backup. specdb configure must be ran first')
sdb_backup.add_argument('--db', type=str, metavar='<path>', required=True,
	help=''.join((
		('path to specific specdb database to backup'))))
sdb_backup.add_argument('--objects', type=str, metavar='<path>', required=False,
	default='objects', help=''.join((
		('path where incremental object_dir is located\n'),
		('by default, an objects/ directory is made in the location where '),
		('database to be backed resides.'))))
sdb_backup.add_argument('--shafile', type=str, metavar='<path>', required=False,
	default='backup.txt', help=''.join((
		('path where sha256 hashes for SQLite pages are stored\n'),
		('by default a backup.txt file is made'))))

sdb_restore = sdb_subs.add_parser('restore',
	help='perform database restoration from a SpecDB backup')
sdb_restore.add_argument('--backup', type=str, metavar='<path>', required=True,
	help=''.join((
		('provide path to the backup database (not the database to be '),
		(' backed up. Ex: specdb.backup.db, not '),
		(' specdb.db)'))))
sdb_restore.add_argument('--shafile', type=str, metavar='<path>',
	required=False, default='backup.txt', help=''.join((
		('location of text file for sha256 hash values for SQLite database'))))
sdb_restore.add_argument('--objects', type=str, metavar='<path>',
	required=False, default='objects', help=''.join((
		('path to objects directory for backup'))))

sdb = sdb_top.parse_args()
## Commands ##

if   sdb.command == 'create':
	if not sdb.db.endswith('.db'):
		print(f'{sdb.db} does not end in .db')
		print('change database name with .db at the end')
		print('Aborting')
		sys.exit()
	
	if not sdb.backup.endswith('.db'):
		print(f'{sdb.backup} does not end in .db')
		print('change database name with .db at the end')
		print('Aborting')
		sys.exit()
		
	specdb_path = os.path.abspath(sdb.db)
	backup_path = os.path.abspath(sdb.backup)
	
	if os.path.isfile(specdb_path):
		print(f'db file {sdb.db} already exists')
		print(f'is there an already existing db at {sdb.db}?')
		print('Aborting')
		sys.exit()
	
	if os.path.isfile(specdb_path):
		print(f'db file {sdb.backup} already exists')
		print(f'is there an already existing db at {sdb.backup}?')
		print('Aborting')
		sys.exit()
		
	# initial SQLite database at SPECDB_DB location
	Create.init(location=specdb_path)
	Create.init(location=backup_path)
	print('db configured and initialized')
	print('Done')

elif sdb.command == 'forms':
	form = Forms.forms(table=sdb.table, num=sdb.num)
	print(form)

elif sdb.command == 'insert':
	Insert.insert(file=os.path.abspath(sdb.form), db=sdb.db, write=sdb.write)

elif sdb.command == 'summary':
	Summary.summary(db=sdb.db, table=sdb.table)

elif sdb.command == 'query':
# 	print(sdb.indices)
# 	print(type(sdb.indices))
# 	print(sdb.sql)
	
	if sdb.indices:
		if len(sdb.indices) == 1:
			if sdb.indices[0].endswith('.csv.'):
				sdb.indices = str(sdb.indices[0])
	
	Query.query(
		db=sdb.db,
		sql=str(sdb.sql[0]) if type(sdb.sql) == list else None,
		indices=sdb.indices,
		star=sdb.star,
		output_dir=sdb.out)

elif sdb.command == 'backup':
	Backup.backup(db=sdb.db, object_dir=sdb.objects, backup_file=sdb.shafile)

elif sdb.command == 'restore':
	Backup.restore(
		backup=sdb.backup,
		backup_file=sdb.shafile,
		object_dir=sdb.objects)