tutorial

#!/usr/bin/env python2.7

import curses
import os
import subprocess

from curses import textpad
from subprocess import CalledProcessError

global curses_screen

ITEM_TYPE_HEADER = 'header'
ITEM_TYPE_TEXT = 'text'
ITEM_TYPE_SQL = 'sql'

def header(value):
    return { 'type' : ITEM_TYPE_HEADER, 'value' : value, }

def text(value):
    return { 'type' : ITEM_TYPE_TEXT, 'value' : value, }

def sql(name):
    return { 'type' : ITEM_TYPE_SQL, 'value' : name, }

TUTORIAL = [
    # sections:
    [
        # pages:
        [
            # items:
            header('How To'),
            text('''
Welcome to this SQL Tutorial.  You can navigate the pages of this tutorial
at your own speed.  The basic navigation commands are the following keys:

 * 'q' : quits the tutorial
 * 'n' : moves to the next page
 * 'p' : moves to the previous page
 * 'j' : jump to section

Most of the pages are displaying actual SQL commands, and running them.  The
results are displayed inline.  All the SQL commands that appear in this
tutorial are from files in the queries/ directory.  The all are named 'tut-*'.

Once you are familiar with the commands, you can use 's' to change the prompt to
a more succinct mode.  'v' will go back to the regular prompt
                 '''),
        ],
    ],
    [
        [
            header('Intro - Tables and Data'),
            text('''
First, let's get to know the schema we will be working with.

We will do that by looking at the table data.
                 '''),
        ],
        [ sql('tut-items'), ],
        [ sql('tut-locations'), ],
        [ sql('tut-inventory'), ],
        [ sql('tut-reviews'), ],
    ],
    [
        [
            header('SELECT - specifying columns'),
            text('''
Here we will learn about what can be put in the 'SELECT' clause of a query.
                 '''),
        ],
        [ sql('tut-items-aliased'), ],
        [ sql('tut-items-coalesced'), ],
        [ sql('tut-items-distinct'), ],
    ],
    [
        [
            header('Basic JOINs'),
            text('''
To understand JOINs, it's best to take off your imperative programming hat, and
get ready to think very declaratively.  It will be tempting to try to think about
HOW the data is being retrieved, but that is often confusing, and will lead you
to try to do sub-selects instead of joins.

At first, it's best to just trust that the database can do magic, and just think
about what data you need to get back, and not about how it will happen.

Everything is tables in the database world.  When a SELECT statement is executed
the database is creating a new table, and returning that table to you.  You are in
control of what that table looks like.
                 '''),
        ],
        [ sql('tut-inventory-raw-join'), ],
        [ sql('tut-inventory-friendly-join'), ],
    ],
    [
        [
            header('AGGREGATES'),
            text('''
Aggregates are functions that act on multiple values.  You use aggregates by
gouping the data in the table into a potentially smaller number of rows.  So
aggregates always go with a GROUP BY clause.  GROUP BY comes after the WHERE
clause, and specifies columns that should be used to determine how to combine
the rows.  GROUP BY and DISTINCT are similar in a lot of ways.
                 '''),
        ],
        [ sql('tut-inventory-aggregate'), ],
        [ sql('tut-aggregate-mistake'), ],
        [ sql('tut-aggregate-mistake-raw'), ],
    ],
    [
        [
            header('OUTER JOINs'),
            text('''
Whereas a normal (INNER) JOIN is directionless (the order of the joins is
unimportant), OUTER JOIN cares about the order.  OUTER JOIN is shorthand for
LEFT OUTER JOIN, meaning the table on the left is the base.  There is a RIGHT
OUTER JOIN as well, but I have honestly never used it in real life.  I thought
I had a legitimate reason to use it once, but then discovered I could rewrite
the query with a LEFT OUTER JOIN and it was actually more readable, I think.
                 '''),
        ],
        [ sql('tut-item-inventory'), ],
        [ sql('tut-item-inventory-outer'), ],
        [ sql('tut-item-inventory-outer-steps'), ],
        [ sql('tut-item-inventory-outer-and'), ],
    ],
    [
        [
            header('HAVING'),
            text('''
HAVING will filter results after grouping, allowing filtering based on aggregates
                 '''),
        ],
        [ sql('tut-review-having'), ],
    ],
    [
        [
            header('Order of operations'),
            text('''
It's important to understand the order of operations for a query.  We will walk
through the process of creating a query that finds items that have had low
reviews from multiple locations.
                 '''),
        ],
        [ sql('tut-op-order-view'), ],
        [ sql('tut-op-order-where'), ],
        [ sql('tut-op-order-agg'), ],
        [ sql('tut-op-order-having'), ],
        [ sql('tut-op-order-report'), ],
    ],
    [
        [
            header('DML'),
            text('''
Data Manipulation Language covers insert, update, delete.  It also includes
select as well, strictly speaking, but often when you hear DML it's referring
just to insert/update/delete.
                 '''),
        ],
        [ sql('tut-insert-values'), ],
        [ sql('tut-insert-select'), ],
        [ sql('tut-update-join'), ],
        [ sql('tut-delete-join'), ],
    ],
    [
        [
            header('Tips and Tricks'),
            text('''
Random examples showing different aspects of SQL
                 '''),
        ],
        [ sql('tut-tip-count-outer'), ],
    ],
]


NORMAL_MODE = 1
EDIT_MODE = 2
JUMP_MODE = 3

VERBOSE = 1
SUCCINCT = 2
input_mode = {
    NORMAL_MODE: VERBOSE,
    EDIT_MODE: VERBOSE,
    JUMP_MODE: VERBOSE,
};
PROMPT = {
    VERBOSE: {
        NORMAL_MODE: '''Type a letter to enter a command:
(q)uit, (n)ext, (p)revious, (j)ump to section, (s)uccinct prompt
>> ''',
        EDIT_MODE: '''Type a letter to enter a command:
(d)one, (e)dit again, (r)eset editor, (b)lank editor, (s)uccinct prompt
>> ''',
        JUMP_MODE: '''Type a letter to enter a command:
(c)ancel, (N) jump to section, (s)uccinct prompt
>> ''',
    },
    SUCCINCT: {
        NORMAL_MODE: '''q,n,p,e,b,j,v >> ''',
        EDIT_MODE: '''d,e,r,b,v >> ''',
        JUMP_MODE: '''c,N,v >> ''',
    }
}
def get_next_action(mode):
    print_to_screen(colorize_nav('''
--------------------------------------------------------------------------------
{}'''.format(PROMPT[input_mode[mode]][mode])))
    command = get_character()
    return command

def display_position(position):
    if position == END_POSITION:
        print_to_screen('Finished!')
    else:
        section, page = position
        print_to_screen(colorize_nav('''Section: {}, Page: {}
--------------------------------------------------------------------------------
'''.format(section+1, page+1)))

def get_items(position):
    section, page = position
    return TUTORIAL[section][page]

def get_query_filename(name):
    return 'queries/{}.sql'.format(name)

try:
    basestring  # attempt to evaluate basestring
    def isstr(s):
        return isinstance(s, basestring)
except NameError:
    def isstr(s):
        return isinstance(s, str)

####################
# getch based on http://stackoverflow.com/questions/510357/python-read-a-single-character-from-the-user
def getch():
    try:
        return getch_win()
    except ImportError:
        return getch_nix()

def getch_win():
    import msvcrt
    return msvcrt.getch()

def getch_nix():
    import sys, tty, termios
    fd = sys.stdin.fileno()
    old_settings = termios.tcgetattr(fd)
    try:
        tty.setraw(sys.stdin.fileno())
        ch = sys.stdin.read(1)
    finally:
        termios.tcsetattr(fd, termios.TCSADRAIN, old_settings)
    return ch

# end getch
##########################

##########################
# screen interface
def get_character():
    #global curses_screen
    #return curses_screen.getch()
    return getch()

def clear_screen():
    #global curses_screen
    #curses_screen.clear()
    print '''
================================================================================
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
================================================================================
'''
    import os
    os.system('cls' if os.name == 'nt' else 'clear')

def print_to_screen(text):
    #global curses_screen
    #curses_screen.addstr(text)
    print text

# end screen interface
##########################

def display(position_or_text):
    clear_screen()
    if isstr(position_or_text):
        display_item(text(position_or_text))
    else:
        display_position(position_or_text)

        if position_or_text != END_POSITION:
            for item in get_items(position_or_text):
                display_item(item)

def display_item(item):
    value = item['value']
    if item['type'] == ITEM_TYPE_HEADER:
        print_to_screen(colorize_header('''
================================================================================
    {}
================================================================================
'''.format(value)))
    elif item['type'] == ITEM_TYPE_TEXT:
        print_to_screen(value)
    elif item['type'] == ITEM_TYPE_SQL:
        filename = get_query_filename(value)
        try:
            output = subprocess.check_output([
                'psql',
                '-P',
                'pager=off',
                '-e',
                '-f',
                filename,
            ], env=dict(os.environ,
                        PGPASSFILE='./pgpass',
                        PGHOST='localhost',
                        PGUSER='sqltut',
                        PGDATABASE='sqltut'),
                stderr=subprocess.STDOUT)
        except CalledProcessError as e:
            output = '''
ERROR: Exit code {}

{}'''.format(e.returncode, e.output)
        except Exception as e:
            output = '''
ERROR: {}
'''.format(e)

        print_to_screen(colorize_sql_output(output))

def colorize_nav(output):
    return '\033[94m' + output + '\033[0m'

def colorize_header(output):
    return '\033[92m' + output + '\033[0m'

def colorize_sql_output(output):
    import re
    output = re.sub(r'/\*', '\033[93m/*', output)
    output = re.sub(r'\*/', '*/\033[0m', output)
    return output

def execute_raw_sql(sql):
    p = subprocess.Popen([
        'psql',
        '-P',
        'pager=off',
        '-e',
    ], env=dict(os.environ,
                PGPASSFILE='./pgpass',
                PGHOST='localhost',
                PGUSER='sqltut',
                PGDATABASE='sqltut'),
        stderr=subprocess.PIPE,
        stdout=subprocess.PIPE,
        stdin=subprocess.PIPE,
        universal_newlines=True
    )
    out, err = p.communicate(input=sql)
    return err or out


def get_table_of_contents():
    toc = 'Choose a section:\n\n'
    for index, section in enumerate(TUTORIAL):
        page = section[0]
        headers = [i for i in page if i['type'] == ITEM_TYPE_HEADER]
        header = headers[0]['value'] if len(headers) > 0 else '???'
        toc += '{} - {}\n'.format(index+1, header)
    return toc



def create_position(section, page):
    assert section >= 0 and section < len(TUTORIAL)
    assert page >= 0 and page < len(TUTORIAL[section])
    return (section, page)

END_POSITION = (-1, -1)
INITIAL_POSITION = (0, 0)

def next_position(position):
    if position == END_POSITION:
        return position # next has nowhere to go
    section, page = position
    if len(TUTORIAL[section]) > page+1:
        return create_position(section, page+1)
    else:
        if len(TUTORIAL) > section+1:
            return create_position(section+1, 0)
        else:
            return END_POSITION

def prev_position(position):
    if position == INITIAL_POSITION:
        return position # prev has nowhere to go
    if position == END_POSITION:
        return create_position(len(TUTORIAL)-1, len(TUTORIAL[-1])-1)
    section, page = position
    if page > 0:
        return create_position(section, page-1)
    else:
        # section must be > 0 here
        return create_position(section-1, len(TUTORIAL[section-1])-1)

def do_edit(position_or_text):
    global curses_screen
    sql = ''
    if position_or_text:
        if isstr(position_or_text):
            sql = position_or_text
        else:
            items = get_items(position_or_text)
            items = [i for i in items if i['type'] == ITEM_TYPE_SQL]
            for item in items:
                with open(get_query_filename(item['value']), 'r') as f:
                    sql += f.read()
                    sql += '\n'
    editor = curses_screen
    display(sql, editor)
    box = textpad.Textbox(editor)
    box.edit()
    sql = box.gather()
    output = execute_raw_sql(sql)
    return output, sql

def run_tutorial(screen):
    global curses_screen
    curses_screen = screen
    action = None
    current_position = INITIAL_POSITION
    output_text = None # kind of an override
    editor_text = None
    current_mode = NORMAL_MODE
    while True:
        display(output_text or current_position)
        action = get_next_action(current_mode)
        if action == ord('q') or action == 'q':
            return
        #elif action == ord('b'):
        #    current_mode = EDIT_MODE
        #    output_text, editor_text = do_edit(None, screen)
        elif action == ord('v') or action == 'v':
            input_mode[current_mode] = VERBOSE
        elif action == ord('s') or action == 's':
            input_mode[current_mode] = SUCCINCT
        elif current_mode == NORMAL_MODE:
            if action == ord('n') or action == 'n':
                current_position = next_position(current_position)
            elif action == ord('p') or action == 'p':
                current_position = prev_position(current_position)
            #elif action == ord('e'):
            #    current_mode = EDIT_MODE
            #    output_text, editor_text = do_edit(current_position, screen)
            elif action == ord('j') or action == 'j':
                current_mode = JUMP_MODE
                output_text = get_table_of_contents()
        #elif current_mode == EDIT_MODE:
        #    if action == ord('d'):
        #        current_mode = NORMAL_MODE
        #        output_text = None
        #        editor_text = None
        #    elif action == ord('e'):
        #        output_text, editor_text = do_edit(editor_text, screen)
        #    elif action == ord('r'):
        #        output_text, editor_text = do_edit(current_position, screen)
        elif current_mode == JUMP_MODE:
            if action == ord('c') or action == 'c':
                current_mode = NORMAL_MODE
                output_text = None
                editor_text = None
            else:
                try:
                    try:
                        section = int(chr(action))
                    except TypeError:
                        section = int(action)
                    if section > 0 and section <= len(TUTORIAL):
                        current_position = create_position(section-1, 0)
                        current_mode = NORMAL_MODE
                        output_text = None
                        editor_text = None
                    else:
                        output_text = '{} - {}'.format(action, chr(section))
                except ValueError:
                    output_text = 'oops'


#curses.wrapper(run_tutorial)
run_tutorial(None)