# Copyright (c) 2000 Roman Milner
# See the file COPYING for more information

import CSQLRelay

class sqlrclient:

    """
    A wrapper for the sqlrelay client API.  Closely follows the C++ API.
    """

    def __init__(self, connection_params, user, password, retrytime=1, tries=1):
        """ Opens a connections to the sqlrelay server and authenticates with
        user and password.  Failed connections are retried for tries times
        at retrytime interval.  connection_parames can either be a string for
        a local unix socket socket or a tuple of host and port number for tcp
        sockets."""
        
    def __del__(self):

    def setResultSetBufferSize(self, rows):
        """
        Sets the number of rows of the result set
        to buffer at a time.  0 (the default)
        means buffer the entire result set.
        """

    def getResultSetBufferSize(self):
        """
        Returns the number of result set rows that 
        will be buffered at a time or 0 for the
        entire result set.
        """

    def ping(self):
        """
        Returns 1 if the database is up and 0
        if it's down.
        """

    def identify(self):
        """
        Returns the type of database: 
          oracle7, oracle8, postgresql, mysql, etc.
        """

    def cacheOn(self):
        """
        Sets query caching on.  Future queries
        will be cached to local files, the names of 
        which can be retrieved using 
        getCacheFileName() after each query is sent.
        
        A default time-to-live of 10 minutes is
        also set.
        """

    def setCacheTtl(self, ttl):
        """
        Sets the time-to-live for cached result
        sets. The sqlr-cachemanger will remove each 
        cached result set "ttl" seconds after it's 
        created.
        """

    def getCacheFileName(self):
        """
        Returns the name of the file containing the most
        recently cached result set.
        """

    def cacheOff(self):
        """
        Sets query caching off.
        """


    """
    If you don't need to use substitution or bind variables
    in your queries, use these two methods.
    """
    def sendQuery(self, query):
        """
        Send a SQL query to the server and
        gets a result set.
        """

    def sendFileQuery(self, path, file):
        """
        Send the SQL query in path/file to the server and
        gets a result set.
        """

    """
    If you need to use substitution or bind variables, in your
    queries use the following methods.  See the footnote for 
    information about substitution and bind variables.
    """
    def prepareQuery(self, query):
        """
        Prepare to execute query.
        """

    def prepareFileQuery(self, path, file):
        """
        Prepare to execute the contents of path/filename.
        """

    def substitution(self, variable, value, precision=0, scale=0):
        """
        Define a substitution variable.
        """

    def inputBind(self, variable, value, precision=0, scale=0):
        """
        Define an input bind varaible.
        """

    def defineOutputBind(self, variable, length):
        """
        Define an output bind varaible.
        """

    def substitutions(self, variables, values, precisions=None, scales=None):
        """
        Define substitution variables.
        """

    def inputBinds(self, variables, values, precisions=None, scales=None):
        """
        Define input bind variables.
        """

    def validateBinds(self):
        """
        If you are binding to any variables that 
        might not actually be in your query, call 
        this to ensure that the database won't try 
        to bind them unless they really are in the 
        query.
        """

    def executeQuery(self):
        """
        Execute the query that was previously
        prepared and bound.
        """

    def getOutputBind(self, variable):
        """
        Retrieve the value of an output bind variable.
        """

    def openCachedResultSet(self, filename):
        """
        Open a result set after a sendCachedQeury
        """

    def openCachedResultSetWithOffset(self, filename, offset, row):
        """
        Opens a cached result set and skips to
        "offset".  The "row" parameter tells the
        method which row that offset corresponds to.
        Returns 1 on success and 0 on failure.
        """

    def rowCount(self):
        """
        Returns the number of rows in the current result set.
        """

    def colCount(self):
        """
        Returns the number of columns in the current result set.
        """

    def totalRows(self):
        """
        Returns the total number of rows that will 
        be returned in the result set.  Not all 
        databases support this call.  Don't use it 
        for applications which are designed to be 
        portable across databases.  -1 is returned
        by databases which don't support this option.
        """

    def affectedRows(self):
        """
        Returns the number of rows that were 
        updated, inserted or deleted by the query.
        Not all databases support this call.  Don't 
        use it for applications which are designed 
        to be portable across databases.  -1 is 
        returned by databases which don't support 
        this option.
        """

    def firstRowIndex(self):
        """
        Returns the index of the first buffered row.
        This is useful when buffering only part of
        the result set at a time.
        """

    def errorMessage(self):
        """
        If the query failed, the error message will be returned
        from this method.  Otherwise, it returns None.
        """

    def endSession(self):
        """
        Ends the current session.
        """

    def suspendSession(self):
        """
        Disconnects this client from the current
        session but leaves the session open so 
        that another client can connect to it 
        using resumeSession().
        """

    def resumeSession(self, port, socket):
        """
        Resumes a session previously left open 
        using suspendSession().
        Returns 1 on success and 0 on failure.
        """

    def resumeCachedSession(self, port, socket, cachefilename):
        """
        Resumes a session previously left open
        using suspendSession() and continues caching
        the result set to "cachefilename".
        Returns 1 on success and 0 on failure.
        """

    def getConnectionPort(self):
        """
        Returns the inet port that the client is 
        communicating over. This parameter may be 
        passed to another client for use in
        the resumeSession() method.
        """

    def getConnectionSocket(self):
        """
        Returns the unix socket that the client is 
        communicating over. This parameter may be 
        passed to another client for use in
        the resumeSession() method.
        """

    def getCacheFileOffset(self):
        """
        Returns the offset in the currently open
        cache file.  This parameter may be used
        in the openCachedResultSet() method to
        jump to a particular offset in the file.
        """

    def getNullsAsEmptyStrings(self):
        """
        Tells the client to return NULL fields and output
        bind variables as empty strings.
        This is the default.
        """

    def getNullsAsNone(self):
        """
        Tells the client to return NULL fields and output
        bind variables as NULL's.
        """

    def getField(self, row, col):
        """
        Returns the value of the specified row and
        column.  col may be a column name or number.
        """

    def getRow(self, row):
        """
        Returns a list of values in the given row.
        """

    def getRowRange(self, beg, end):
        """
        Returns a list of lists of the rows between beg and end.
        Note: this function has no equivalent in other SQL Relay API's.
        """

    def getRowDictionary(self, row):
        """
        Returns the requested row as values in a dictionary
        with column names for keys.
        """

    def getColumnName(self, col):
        """
        Returns the name of column number col.
        """

    def getColumnNames(self):
        """
        Returns a list of column names in the current result set.
        """

    def getColumnType(self, col):
        """
        Returns the type of the specified column.  col may
        be a name or number.
        """

    def getColumnLength(self, col):
        """
        Returns the length of the specified column.  col may
        be a name or number.
        """

    def getLongest(self, col):
        """
        Returns the length of the specified column.  col may
        be a name or number.
        """

    def debugOn(self):
        """
        Turn verbose debugging on.
        """

    def debugOff(self):
        """
        Turn verbose debugging off.
        """

    def getDebug(self):
        """
        Returns 1 if debugging is turned on and 0 if debugging is turned off.
        """

"""
Footnote about Substitution and Bind variables...

Example query:

select
        first_name,
        middle_initial,
        last_name
from
        $(schema).people
where
        person_id=:id
        and
        age>=:youngage
        and
        age<=:oldage

In this query, $(schema) is a substitution variable and :id, :youngage and
:oldage are input bind variables.  Substitution and bind variables allow
values to be passed from your program into queries or procedural code.

Example procedural code:

        BEGIN
                :returnval:=100*50;
        END;

In this code, :returnval is an output bind variable. Output bind variables
allow values to be passed from procedural code into your program.


The substitutions() and inputBinds() methods have array parameters.

Variable and value arrays must be NULL terminated arrays of strings.
Buffer arrays must be NULL terminated arrays of (char *)'s.
Length arrays must be NULL terminated arrays of integers.

For example:

        char    *variablearray[]={"var1","var2","var3",NULL};
        char    *valuearray[]={"val1","val2","val3",NULL};

        char    *buffer1=new char[100];
        char    *buffer2=new char[100];
        char    *buffer3=new char[100];
        char    *bufferarray[]={buffer1,buffer2,buffer3,NULL};
        char    *lengtharray[]={100,100,100,NULL};
           or
        char    buffer1[100];
        char    buffer2[100];
        char    buffer3[100];
        char    *bufferarray[]={&buffer1,&buffer2,&buffer3,NULL};
        char    *lengtharray[]={100,100,100,NULL};


The substitution(), inputBind() and defineOutputBind() methods 
have individual parameters.

Individual variable and values must be strings.
Individual buffers must be (char *)'s.
Individual lengths must be integers.

For example:

        char    *variable1="var1";
        char    *variable2="var2";
        char    *variable3="var3";

        char    *value1="val1";
        char    *value2="val2";
        char    *value3="val3";

        char    *buffer1=new char[100];
        char    *buffer2=new char[100];
        char    *buffer3=new char[100];

        int     length1=100;
        int     length2=100;
        int     length3=100;


What exactly are substitution and bind variables?

Substitution and input bind variables are both methods for replacing a
variable in a query or procedural code with a corresponding value from
your program.

Output bind variables allow values to be passed from procedural code
into buffers in your program.

Substitution variable format:   $(variable)
Bind variable format:           :variable

Substitution variables are processed first, by the API.  Input bind 
variables are processed second, by the underlying RDBMS or by the 
connection daemon in the event that the RDBMS doesn't support bind 
variables.  Output bind variables are processed by the RDBMS as the
query or procedural code is executed.

Substitution variables may be anything and appear anywhere in the query.
They are frequently used to ammend where clauses with additional 
constraints and specify schemas.

Input bind variables may only be used as rvalue's either in the where 
clause of a query or in procedural statements.

Output bind variables may only be used as lvalue's in procedural
statements.

A substitution value (such as an additional where clause constraint or
chunk of procedural code) may contain bind variables.

Why should I use input bind variables instead of just using substitution 
variables for everything?

        To improve performance.

        RDBMS's which support bind variables parse the query then plug
        input bind variables into the already parsed code.  If the same query is
        run a bunch of times, even with different values for the input bind 
        variables, the RDBMS will have the code cached and won't have to parse 
        the query again.  If you don't use input bind variables, the RDBMS will
        parse the query each time because the where clause will be slightly 
        different each time and the code for all those slightly different 
        queries will clog the cache.

        Generally speaking, you should use input bind variables for
        substitutions in the where clause.

What if my RDBMS doesn't support bind variables?

        Use substitution variables instead, the bind variable API calls will
        be useless to you.
"""