#!/usr/bin/env python
"""
Copyright (c) 2007, Muharem Hrnjadovic

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.
    * Neither the name of Muharem Hrnjadovic nor the names of other
      contributors may 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.

---------------------------------------------------------------------------

PostgreSQL tools
"""
# created: Thu Oct 25 21:35:50 2007
__version__ = "$Id:$"
# $HeadURL $

import psycopg2 as ps2

class PGT(object):
    """Utility functions for using a PostgreSQL database with python"""

    def __init__(self):
        """initialiser"""
        # super(NEW_CLASS, self).__init__()

    @staticmethod
    def prepareByteaString(byteaSeq):
        """
        Given a sequence of byte arrays this function prepares a properly
        quoted string to be used for inserting database rows with Bytea
        arrays.

        Given e.g. a table like the following
            Create table baex(byteaa Bytea ARRAY[16]);
        the resulting string ('bas') can be used as follows:
            cursor.execute("INSERT INTO baex(byteaa) values('{%s}')" % bas))

        Parameters:
        - byteaSeq: a sequence of byte arrays each corresponding to a Bytea
                    value in the database
        Returns:
        string: containing all the byte arrays from the 'byteaSeq' properly
                quoted for utilisation in an INSERT statement
        """
        # in a first step
        #   1. quote all the byte arrays (using the psycopg2 Binary()
        #      function)
        #   2. strip away the single quotes on the left and right side
        #   3. escape any double quote characters with a backslash
        # The last step is necessary because we will use the double quote as
        # the quote/delimiter for the byte arrays
        baSeq = [str(ps2.Binary(ba))[1:-1].replace(r'"', r'\"') for ba in byteaSeq]
        # join the prep'ed byte arrays into a single string
        bas = "\"%s\"" % '","'.join(baSeq)
        # double the number of backslashes (needed because we're inserting a
        # Bytea array as opposed to a single Bytea)
        bas = bas.replace('\\', '\\\\')
        # done!
        return(bas)

if __name__ == '__main__':
    ### TEST code ********************************************************
    import os, sys
    from random import random as rand

    # connect to test database
    db = ps2.connect("dbname='test' user='postgres'")
    cursor = db.cursor()
    # create test table
    cursor.execute('Create Table public.baex(id Serial, byteaa Bytea ARRAY[16])')

    sys.stdout.flush()
    print "\n******************** Bytea data generated: ********************"
    # generate 3 byte array sequences
    for rowId in range(1, 4):
        byteaSeq = []
        # generate 1-7 random byte arrays
        for numOfSstrings in range(1, int(rand()*8)):
            bytea = ''.join([chr(int(rand()*256)) for x in range(int(rand()*5))])
            byteaSeq.append(bytea)
        print byteaSeq
        # get the INSERT string for the byte array sequence generated
        bas = PGT.prepareByteaString(byteaSeq)
        # insert the row into the table
        cursor.execute("INSERT INTO public.baex(id, byteaa) VALUES(%s, '{%s}')" \
                       % (rowId, bas))
    db.commit()
    sys.stdout.flush()

    print "\n******************** Bytea data inserted: ********************"
    sys.stdout.flush()
    # show the data inserted
    os.system("psql -d test -U postgres -c 'SELECT * FROM public.baex'")
    cursor.execute('DROP TABLE public.baex')
    db.commit()