db-cli

#!/usr/bin/env python
#
# (c)oded 2015, Marek Chalupa
# E-mail: statica@fi.muni.cz
#
# Permission to use, copy, modify, distribute, and sell this software and its
# documentation for any purpose is hereby granted without fee, provided that
# the above copyright notice appear in all copies and that both that copyright
# notice and this permission notice appear in supporting documentation, and
# that the name of the copyright holders not be used in advertising or
# publicity pertaining to distribution of the software without specific,
# written prior permission. The copyright holders make no representations
# about the suitability of this software for any purpose. It is provided "as
# is" without express or implied warranty.
#
# THE COPYRIGHT HOLDERS DISCLAIM ALL WARRANTIES WITH REGARD TO THIS SOFTWARE,
# INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO
# EVENT SHALL THE COPYRIGHT HOLDERS BE LIABLE FOR ANY SPECIAL, INDIRECT OR
# CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE,
# DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER
# TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
# OF THIS SOFTWARE.
#
# On arran we have only python2, so use python2

import os
import sys
from database import get_db_credentials, check_db_credentials, DatabaseConnection

def print_help():
    print('When given query as the only argument on command line,\n'
          'it will be processed and the tool will exit.')
    print('\nInteractively you can use these commands:')
    print('\tmysql\t\tLog into interactive mysql')
    print('\tdelete\t\tDelete entry from db')
    print('\t  tool [id | name version/note]')
    print('\t  result [not implemented]')

def print_result(res):
    for r in res:
        sys.stdout.write('||')
        for it in r:
            sys.stdout.write(' {0} |'.format(it))
        sys.stdout.write('|\n')

def get_input(prompt = '> '):
    try:
        s = str(raw_input(prompt))
        return s.strip()
    except EOFError:
        return None
    except KeyboardInterrupt:
        return None

def cmd_delete(db, cmd):
    if cmd.startswith('tool '):
        return delete_tool(db, cmd[5:])
    elif cmd.startswith('result '):
        return delete_result(db, cmd[7:])
    else:
        return False

def cmd_show_different(db, cmd):
    try:
        # the map can rise ValueError too
        ids = map(int, cmd.split())
        if len(ids) != 3:
            raise ValueError
    except ValueError:
        print('Invalid id, syntax: tool1_id tool2_id category_id')
        return False

    cmd = """SELECT name, t1.result, t2.result
             FROM task_results as t1
                JOIN task_results as t2 ON t1.task_id = t2.task_id
                JOIN tasks ON t1.task_id = tasks.id
             WHERE t1.tool_id = {0} and t2.tool_id = {1}
                AND tasks.category_id = {2} and t1.result != t2.result
          """.format(ids[0], ids[1], ids[2])

    res = db.query(cmd)
    if not res:
        print('Faile executing query:')
        print(cmd)
        return False

    for r in res:
        print('{0:5} <-> {1:5} | {2}'.format(r[1], r[2], r[0]))

    return True

def quote(s):
    if s[0] == '\'' and s[-1] == '\'':
        return s
    else:
        return '\'{0}\''.format(s)

def delete_tool(db, tool):
    try:
        id = int(tool)
    except ValueError:
        id = None

    if id:
        if not db.query('SELECT id FROM tools WHERE id = {0}'.format(id)):
            print('No such tool')
            return False

        db.query('DELETE FROM tools WHERE id = {0}'.format(id))
        db.commit()
        return True

    parts = tool.split(' ', 1)
    if len(parts) != 2:
        print('Too few arguments. Need version or note to distinguish tool')
        return True

    res1 = db.query('SELECT id FROM tools WHERE name = {0}'
                    ' and version = {1}'.format(quote(parts[0]), quote(parts[1])))
    res2 = db.query('SELECT id FROM tools WHERE name = {0}'
                    ' and note = {1}'.format(quote(parts[0]), quote(parts[1])))

    if len(res1) + len(res2) > 1:
        print('Found more candidates, choose one (use id)')

        res1 = db.query('SELECT id, name, version, note FROM tools WHERE name = {0}'
                        ' and version = {1}'.format(quote(parts[0]), quote(parts[1])))
        res2 = db.query('SELECT id, name, version, note FROM tools WHERE name = {0}'
                        ' and note = {1}'.format(quote(parts[0]), quote(parts[1])))
        print_result(res1)
        print_result(res2)
        return True

    if res1:
        id = int(res1[0][0])
    elif res2:
        id = int(res2[0][0])
    else:
        print('No match')
        return True

    db.query('DELETE FROM tools WHERE id = {0}'.format(id))
    db.commit()
    return True

def delete_result(db, res):
    pass

def cmd_stats():
    res = db.query('SELECT year, count(*) '
                   'FROM tasks '
                        'INNER JOIN categories ON category_id = categories.id '
                        'INNER JOIN years ON year_id = years.id '
                    'GROUP BY year_id')
    print('Number of tasks:')
    print(' -- Year -- Count --\n')
    for r in res:
        print('   {0:^6}  {1:^6}'.format(r[0], r[1]))

    res = db.query('SELECT year, count(*) '
                   'FROM task_results '
                        'INNER JOIN tasks ON task_id = tasks.id '
                        'INNER JOIN categories ON category_id = categories.id '
                        'INNER JOIN years ON year_id = years.id '
                    'GROUP BY year_id')
    print('\nResults in database:')
    print(' -- Year -- Count --\n')
    for r in res:
        print('   {0:^6}  {1:^6}'.format(r[0], r[1]))

if __name__ == "__main__":
    argc = len(sys.argv)

    if argc > 2:
        sys.stderr.write('0 or 1 argument excepted (mysql query)\n')
        sys.exit(1)

    db = DatabaseConnection()

    # if we got query given on command line, process it and exit
    if argc == 2:
        res = db.query(sys.argv[1])
        print_result(res)

        db.commit() # commit result if it was modification
        sys.exit(0)

    line = get_input()
    while not line is None:
        err = False
        if line == 'mysql':
            host, user, passwd, db = get_db_credentials()
            check_db_credentials(host, user, passwd, db)

            cmd = 'mysql --user={0} --password={1} --host={2} {3}'.format(user, passwd, host, db)
            os.system(cmd)
        elif line == 'help':
            print_help()
        elif line.startswith('delete '):
            if not cmd_delete(db, line[7:]):
                err = True
            else:
                print('OK')
        elif line.startswith('showdiff '):
            if not cmd_show_different(db, line[9:]):
                err = True
            else:
                print('OK')
        elif line == 'stats':
            cmd_stats()
        else:
            if line != '':
                err = True

        if err:
            print('Wrong command')
        line = get_input()

    print('') # print last newline