#!/usr/bin/env python
# testProc.py
#
# Copyright (c) 2002, Daniel Dittmar
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#
#     * Redistributions of source code must retain the above copyright
#       notice, this list of conditions and the following disclaimer.
#     * Redistributions in binary form must reproduce the above copyright
#       notice, this list of conditions and the following disclaimer in
#       the documentation and/or other materials provided with the distribution.
#     * The name of the copyright holder may not be used to endorse or
#       promote products derived from this software without specific
#       prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
# IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
# THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
# PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE COPYRIGHT OWNER OR
# CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
# EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
# PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
# PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
# LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
# NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#

# TODO
# - creation of trigger
# - multiple DBProcs per file

import sys
import string
import re

import sapdb

true = 'true'
_createRE = re.compile ('create[ \t]+dbproc[ \t]+("?[a-z][a-z0-9_]*"?)', re.I)
_testComment = '--exec:'

def testProc (session, fname):
    source = open (fname, 'r').read ()
    source = string.expandtabs (source)
    match = _createRE.search (source)
    if match is None:
        sys.stderr.write (fname + ': no CREATE DBPROC found\n')
        return None
    procname = match.group (1)
    dropProc (session, procname)
    source, exampleSource = splitCreate (source)
    #
    # create dbproc
    #
    try:
        session.sql (source)
    except sapdb.SQLError, err:
        printSourceError (fname, source, err)
        return None
    #
    # test
    #
    call = session.prepare ('call ' + procname + parameterString (session, procname))
    examples = splitExamples (exampleSource)
    for example in examples:
        print procname, example,
        try:
            result = call.execute (example)
            print '=>', result
        except SQLError, err:
            print '!!!', err.message
    return true

def splitCreate (source):
    pos = string.find (source, '\n' + _testComment)
    if pos == -1:
        return source, ''
    else:
        return source [:pos], source [pos:]

def dropProc (session, fname):
    try:
        session.sql ('drop dbproc ' + fname)
    except sapdb.SQLError, err:
        if err.errorCode not in [-4016]:
            raise

def printSourceError (fname, source, err):
    errorPos = err.errorPos
    line, lineno, col = translateErrorPos (source, errorPos)
    sys.stderr.write (line + '\n')
    sys.stderr.write ((' ' * col) + '^\n')
    sys.stderr.write ('%s:%d:%d: %s\n' % (fname, lineno, col, err.message))

def translateErrorPos (source, errorPos):
    lines = string.split (source, '\n')
    pos = 0
    lineno = 1
    for line in lines:
        if errorPos < pos + len (line):
            col = errorPos - pos - 1
            return line, lineno, col
        lineno = lineno + 1
        pos = pos + len (line) + 1

def parameterString (session, procname):
    procname = getCorrectname (procname)
    cursor = session.sql ("""select parameter from dbprocedures
        where owner = usergroup and dbprocname = '%s'""" % procname)
    (count,) = cursor.next ()
    placeholder = ['?'] * count
    result = '(' + string.join (placeholder, ', ') + ')'
    return result

def getCorrectname (procname):
    if (procname [0] == '"') or (procname [-1] == '"'):
        procname = procname [1:-1]
    else:
        procname = string.upper (procname)

    return procname

def splitExamples (source):
    result = []
    commentLen = len (_testComment)
    for line in string.split (source, '\n'):
        if line [:commentLen] == _testComment:
            result.append (eval (line [commentLen:]))
    return result

#
# main and options
#
def fatalError (msg):
    sys.stderr.write (msg + '\n')
    sys.exit (2)

def createSession (options):
    if not options.userinfo:
        fatalError ('user name must be specified (option -u)')
    if not options.dbname:
        fatalError ('dabase name must be specified (option -d)')
    name, pwd = string.split (options.userinfo, ',')
    try:
        optstring = 'autocommit=on'
        if options.oracle:
            optstring = optstring + '&sqlmode=oracle'
        result = sapdb.connect (name, pwd, options.dbname, options.node, optstring)
    except sapdb.SQLError, err:
        if err.errorCode == -4008:
            name = upper (name)
            result = sapdb.connect (name, pwd, options.dbname, options.node, optstring)
    return result

def main (options, args):
    """create DBProcedures stored in files

    DBProcedures are dropped if they already exist.
    The file may contain comments of the form
    --exec: value,value,...
    These are executed with the values as the input parameters.
    See the example at the end of this file.

    """
    session = createSession (options)
    ok = true
    for arg in args:
        ok = testProc (session, arg) and ok
    if ok:
        sys.exit (0)
    else:
        sys.exit (3)

def rawMain (args):
    import optlib
    optlib.optMain2 (main, _options (), args)

def _options ():
    return [
        # (optstring, varname, typechar, default, help)
        ('u', 'userinfo', ':', None, '<user>,<pwd>'),
        ('d', 'dbname', ':', None, 'database name'),
        ('n', 'node', ':', '', 'server name'),
        ('oracle', None, '', None, 'enable Oracle (tm) SQL syntax'),
        ('version', None, '', 0.1, 'version information'),

        ]

if __name__ == '__main__':
    import sys
    rawMain (sys.argv [1:])

example = """
create dbproc "testproc" (in x int, in y int, out area int)
as
    area = new:x * y;
	whatever;

--exec: 5, 6
"""