Global database configuration and context aware connection pool extention for psycopg2

Just created a recipe of the context aware connection pool that I am using for quite a long time now.

ActiveState Python Recipe #577072

Hope somebody would get at least some ideas for his own project.

re-assigning values to the parameters in plpythonu

As discussed in Postgres BUG #5232 assigning values to the parameters fails with the following error:

Detail: <type 'exceptions.UnboundLocalError'>: local variable 'src' 
referenced before assignment

If assignment to the parameter is still needed one can solve the issue by adding global definition for such a parameter.

CREATE OR REPLACE FUNCTION pyreplace(src text, s text) 
  RETURNS text AS 
$BODY$ 
global src
src=src.replace(s,'') 
return src 
$BODY$ 
  LANGUAGE 'plpythonu' VOLATILE 
  COST 100; 

Awesome code syntax highlighting made easy | Carter Cole's Blog

I finally tried out the highlighting JavaScript library SyntaxHighlighter. This is quite an impressive work done by the author. My respect to him. And here is a nice blog post about how no configure the library to use when posting to blogger:
Awesome code syntax highlighting made easy | Carter Cole's Blog

64 bit python installation issues on 64 bit Windows 7

After installing 64 bit Windows 7 at work, I got a problem of installing many python utilities that search for installed python locations is windows registry. distutils (setuptools-0.6c11.win32-py2.6.exe) still could not find 64 bit python installation, as well as develer's Unofficial MinGW GCC binaries for Windows (I still do not have a solution for that one :( ).

Actually, what helped me with disttools, was a http://www.mail-archive.com/distutils-sig@python.org/msg10512.html group thread. The currently available 64 bit python installation is registering python in a new location in windows registry. So one have to use a modified script for registering python the old way:

#
# script to register Python 2.0 or later for use with win32all
# and other extensions that require Python registry settings
#
# written by Joakim Loew for Secret Labs AB / PythonWare
#
# source:
# http://www.pythonware.com/products/works/articles/regpy20.htm
#
# modified by Valentine Gogichashvili as described in http://www.mail-archive.com/distutils-sig@python.org/msg10512.html

import sys

from _winreg import *

# tweak as necessary
version = sys.version[:3]
installpath = sys.prefix

regpath = "SOFTWARE\\Python\\Pythoncore\\%s\\" % (version)
installkey = "InstallPath"
pythonkey = "PythonPath"
pythonpath = "%s;%s\\Lib\\;%s\\DLLs\\" % (
    installpath, installpath, installpath
)

def RegisterPy():
    try:
        reg = OpenKey(HKEY_CURRENT_USER, regpath)
    except EnvironmentError as e:
        try:
            reg = CreateKey(HKEY_CURRENT_USER, regpath)
            SetValue(reg, installkey, REG_SZ, installpath)
            SetValue(reg, pythonkey, REG_SZ, pythonpath)
            CloseKey(reg)
        except:
            print "*** Unable to register!"
            return
        print "--- Python", version, "is now registered!"
        return
    if (QueryValue(reg, installkey) == installpath and
        QueryValue(reg, pythonkey) == pythonpath):
        CloseKey(reg)
        print "=== Python", version, "is already registered!"
        return
    CloseKey(reg)
    print "*** Unable to register!"
    print "*** You probably have another Python installation!"

if __name__ == "__main__":
    RegisterPy()

or just inject the following REG file into your registry if you are using python 2.6 installed in C:\Python26 directory

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Python]

[HKEY_CURRENT_USER\Software\Python\Pythoncore]

[HKEY_CURRENT_USER\Software\Python\Pythoncore\2.6]

[HKEY_CURRENT_USER\Software\Python\Pythoncore\2.6\InstallPath]
@="C:\\Python26"

[HKEY_CURRENT_USER\Software\Python\Pythoncore\2.6\PythonPath]
@="C:\\Python26;C:\\Python26\\Lib\\;C:\\Python26\\DLLs\\"

PyDev is finally fully open sourced!

PyDev is finally fully open sourced! These are really good news!

Getting arrays from PostgreSQL database using JDBC in Java

getArray() standard JDBC methods are working for basic types in PostgreSQL as documented in JDBC specifications.

As PostgreSQL 8.2 does not support arrays of types, and PostgreSQL JDBC driver does not support getting of the arrays of types even from PostgreSQL 8.3+, we can pass this kind of structures as text arrays, were text elements are postgres records (not types), serialized with textin(record_out(ROW(a, b, c)))::text approach (for PostgreSQL 8.2 this is the only way to serialize the record, in 8.3+ it is now possible to simply convert the record to text like ROW(a, b, c)::text) and the way of deserialization of the received text array data with java method that is shown below:

public class Utils {
...

/**
* Method parses a postgres Row into a List of Strings.
* <p>
* The postgres row is represented by a String and consists of one or more columns, that are separated by a comma.
* The row must begin with an open bracket and must end with a closing bracket.
* Each column must begin with a letter or a quote. If a column begins with a quote, the column must end with a quote.
* Inside quotation a quote is represented by a double quote or by backslash and quote, a backslash is represented by double backslash.
*
* @param value
* @return List of Strings
* @throws JBackendParserException
*/
public static List<String> postgresROW2StringList(String value) throws JBackendParserException
{
    return postgresROW2StringList(value, 128);
}

/**
* Method parses a postgres Row into a List of Strings.
* <p>
* The postgres row is represented by a String and consists of one or more columns, that are separated by a comma.
* The row must begin with an open bracket and must end with a closing bracket.
* Each column must begin with a letter or a quote. If a column begins with a quote, the column must end with a quote.
* Inside quotation a quote is represented by a double quote or by backslash and quote, a backslash is represented by double backslash.
* <p>
* The appendStringSize is the Size for StringBuilder.
*
* @param value, the postgres Row
* @param appendStringSize
* @return List of Strings
* @throws JBackendParserException
*/
public static List<String> postgresROW2StringList(String value, int appendStringSize)
throws JBackendParserException
{
    if (!(value.startsWith("(") && value.endsWith(")")))
    throw new ParseException("postgresROW2StringList() ROW must begin with '(' and end with ')': " + value);

    List<String> result = new ArrayList<String>();

    char[] c = value.toCharArray();

    StringBuilder element = new StringBuilder(appendStringSize);
    int i = 1;
    while (c[i] != ')')
    {
        if (c[i] == ',')
        {
            if (c[i+1] == ',')
            {
                result.add(new String());
            } else if (c[i+1] == ')')
            {
                result.add(new String());
            }
            i++;
        } else if (c[i] == '\"')
        {
            i++;
            boolean insideQuote = true;
            while(insideQuote)
            {
                char nextChar = c[i + 1];
                if(c[i] == '\"')
                {
                    if (nextChar == ',' || nextChar == ')')
                    {
                        result.add(element.toString());
                        element = new StringBuilder(appendStringSize);
                        insideQuote = false;
                    } else if(nextChar == '\"')
                    {
                        i++;
                        element.append(c[i]);
                    } else
                    {
                        throw new ParseException("postgresROW2StringList() char after \" is not valid");
                    }
                } else if (c[i] == '\\')
                {
                    if(nextChar == '\\' || nextChar == '\"')
                    {
                        i++;
                        element.append(c[i]);
                    } else
                    {
                        throw new ParseException("postgresROW2StringList() char after \\ is not valid");
                    }
                } else
                {
                    element.append(c[i]);
                }
                i++;
            }
        }else
        {
            while(!(c[i] == ',' || c[i] == ')'))
            {
                element.append(c[i]);
                i++;
            }
            result.add(element.toString());
            element = new StringBuilder(appendStringSize); // we aways loose the last object here, but its easier then checking for flag every time before append (definitely we loose some performance here)
        }
    }
return result;
}
...
}

We can use the following example SQL statement to demonstrate how to pack needed data structures into the serialized text arrays

select s.i as id,
'row ' || s.i as text_data,
ARRAY( select textin(record_out( ROW( 100 * s.i + a.i,
'element ' || 100 * s.i + a.i,
'constant text with some "quoting"' ) ))::text
from generate_series( 1, 5 ) as a(i) ) as serialized_row_array
from generate_series(1, 10) as s(i)

The result of the execution of this query is:



And then read these text arrays using the following java code in the springsframework row mapper

And then read these text arrays using the following java code in the
springframework row mapper (as this example uses ResultSet actually, you can see as well, how to read data directly from ResultSet in the same example):

public class ArrayRowMapper<ITEM> implements ParameterizedRowMapper<ITEM> {

...
private ITEM createEmptyItem() {
...
}
private Element createEmptyElement() {
...
}

public final ITEM mapRow(ResultSet rs, int rowNum) throws SQLException {
ITEM item = createEmptyItem();
item.setId( rs.getInt("id") );
item.setTextData( rs.getString("text_data") );
Array sqlArray = rs.getArray("serialized_row_array");
if ( sqlArray == null ) {
item.setElements(null);
} else {
String[] textArray = (String[])sqlArray.getArray();
List<Element> elements = new ArrayList<Element>(textArray.length);

for(int i = 0; i < textArray.length; i++)
{
try
{
List<String> stringResultList = Utils.postgresROW2StringList(textArray[i]);

Element element = createEmptyElement();
element.setId(Integer.parseInt(stringResultList.get(0)));
element.setTextData(stringResultList.get(1));
element.setConstantTextData(stringResultList.get(2));
elements.add(element);
}catch (JBackendParserException pe) {
logger.error("Problem parsing received ROW value [" + textArray[i] + "]: " + pe.getMessage(), pe);
}catch (Exception e) {
logger.error("Problem setting values to Element object from received ROW value [" + textArray[i] + "] : " + e.getMessage(), e);
}
}
item.setElements(elements);
}
}
}

Passing arrays to PostgreSQL database from java (JDBC)

Normally JDBC driver needs to know, how to serialize some database type so, that the database can accept it. In case of PostgreSQL JDBC driver we use 2 implementations for passing integer and text arrays.

• to pass an integer array to PostgreSQL database the following java.sql.Array implementation can be used:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>int4</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLInt4Array implements java.sql.Array {

    private final int[] intArray;
    private final String stringValue;

    public PostgreSQLInt4Array(int[] intArray) {
        this.intArray = intArray;
        this.stringValue = intArrayToPostgreSQLInt4ArrayString(intArray);
    }

    public String toString() {
        return stringValue;
    }

    /**
     * This static method can be used to convert an integer array to string representation of PostgreSQL integer array.
     * @param a source integer array
     * @return string representation of a given integer array
     */
    public static String intArrayToPostgreSQLInt4ArrayString(int[] a) {
        if ( a == null ) {
            return "NULL";
        }
        final int al = a.length;
        if ( al == 0 ) {
            return "{}";
        }
        StringBuilder sb = new StringBuilder( 2 + al * 7 ); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
        sb.append('{');
        for (int i = 0; i < al; i++) {
            if ( i > 0 ) sb.append(',');
            sb.append(a[i]);
        }
        sb.append('}');
        return sb.toString();
    }


    public static String intArrayToCommaSeparatedString(int[] a) {
        if ( a == null ) {
            return "NULL";
        }
        final int al = a.length;
        if ( al == 0 ) {
            return "";
        }
        StringBuilder sb = new StringBuilder( al * 7 ); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
        for (int i = 0; i < al; i++) {
            if ( i > 0 ) sb.append(',');
            sb.append(a[i]);
        }
        return sb.toString();
    }

    public Object getArray() throws SQLException {
        return intArray == null ? null : Arrays.copyOf(intArray, intArray.length);
    }

    public Object getArray(Map<String, Class<?>> map) throws SQLException {
        return getArray();
    }

    public Object getArray(long index, int count) throws SQLException {
        return intArray == null ? null : Arrays.copyOfRange(intArray, (int)index, (int)index + count );
    }

    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
        return getArray(index, count);
    }

    public int getBaseType() throws SQLException {
        return java.sql.Types.INTEGER;
    }

    public String getBaseTypeName() throws SQLException {
        return "int4";
    }

    public ResultSet getResultSet() throws SQLException {
        throw new UnsupportedOperationException();
    }

    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    public ResultSet getResultSet(long index, int count) throws SQLException {
        throw new UnsupportedOperationException();
    }

    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    public void free() throws SQLException {
    }

}

• the same way we can create a class to pass a string array to PostgreSQL database:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>text</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLTextArray implements java.sql.Array {

    private final String[] stringArray;
    private final String stringValue;

    /**
     * Initializing constructor
     * @param stringArray
     */
    public PostgreSQLTextArray(String[] stringArray) {
        this.stringArray = stringArray;
        this.stringValue = stringArrayToPostgreSQLTextArray(this.stringArray);

    }

    @Override
    public String toString() {
        return stringValue;
    }

    private static final String NULL = "NULL";

    /**
     * This static method can be used to convert an string array to string representation of PostgreSQL text array.
     * @param a source String array
     * @return string representation of a given text array
     */
    public static String stringArrayToPostgreSQLTextArray(String[] stringArray) {
        final int arrayLength;
        if ( stringArray == null ) {
            return NULL;
        } else if ( ( arrayLength = stringArray.length ) == 0 ) {
            return "{}";
        }
        // count the string length and if need to quote
        int neededBufferLentgh = 2; // count the beginning '{' and the ending '}' brackets
        boolean[] shouldQuoteArray = new boolean[stringArray.length];
        for (int si = 0; si < arrayLength; si++) {
            // count the comma after the first element
            if ( si > 0 )  neededBufferLentgh++;

            boolean shouldQuote;
            final String s = stringArray[si];
            if ( s == null ) {
                neededBufferLentgh += 4;
                shouldQuote = false;
            } else {
                final int l = s.length();
                neededBufferLentgh += l;
                if ( l == 0 || s.equalsIgnoreCase(NULL) ) {
                    shouldQuote = true;
                } else {
                    shouldQuote = false;
                    // scan for commas and quotes
                    for (int i = 0; i < l; i++) {
                        final char ch = s.charAt(i);
                        switch(ch) {
                            case '"':
                            case '\\':
                                shouldQuote = true;
                                // we will escape these characters
                                neededBufferLentgh++;
                                break;
                            case ',':
                            case '\'':
                            case '{':
                            case '}':
                                shouldQuote = true;
                                break;
                            default:
                                if ( Character.isWhitespace(ch) ) {
                                    shouldQuote = true;
                                }
                                break;
                        }
                    }
                }
                // count the quotes
                if ( shouldQuote ) neededBufferLentgh += 2;
            }
            shouldQuoteArray[si] = shouldQuote;
        }

        // construct the String
        final StringBuilder sb = new StringBuilder(neededBufferLentgh);
        sb.append('{');
        for (int si = 0; si < arrayLength; si++) {
            final String s = stringArray[si];
            if ( si > 0 ) sb.append(',');
            if ( s == null ) {
                sb.append(NULL);
            } else {
                final boolean shouldQuote = shouldQuoteArray[si];
                if ( shouldQuote ) sb.append('"');
                for (int i = 0, l = s.length(); i < l; i++) {
                    final char ch = s.charAt(i);
                    if ( ch == '"' || ch == '\\' ) sb.append('\\');
                    sb.append(ch);
                }
                if ( shouldQuote ) sb.append('"');
            }
        }
        sb.append('}');
        assert sb.length() == neededBufferLentgh;
        return sb.toString();
    }


    @Override
    public Object getArray() throws SQLException {
        return stringArray == null ? null : Arrays.copyOf(stringArray, stringArray.length);
    }

    @Override
    public Object getArray(Map<String, Class<?>> map) throws SQLException {
        return getArray();
    }

    @Override
    public Object getArray(long index, int count) throws SQLException {
        return stringArray == null ? null : Arrays.copyOfRange(stringArray, (int)index, (int)index + count);
    }

    @Override
    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
        return getArray(index, count);
    }

    @Override
    public int getBaseType() throws SQLException {
        return java.sql.Types.VARCHAR;
    }

    @Override
    public String getBaseTypeName() throws SQLException {
        return "text";
    }

    @Override
    public ResultSet getResultSet() throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(long index, int count) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public void free() throws SQLException {
    }

//  public static void main(String[] args) {
//      // test the method
//      String[][] stringArrayArray = new String[][] {
//              { "shm\taliko", "", null, "kluku" },
//              { "", "NULL", "NuLL", "\"kuku\"", "valiko, shmaliko" },
//              { "", "NULL", "NuLL", "\"ku\\ku\"", "valiko, shm\taliko", "shm\taliko" },
//              { }
//      };
//
//      for( String[] stringArray : stringArrayArray ) {
//          PostgreSQLTextArray a = new PostgreSQLTextArray(stringArray);
//          String s = a.toString();
//          System.out.println(s);
//      }
//  }

}

Definitely it is possible to merge these two classes so that one wrapper is used instead of two and more known database types can be added in such a wrapper. This implementation relies on the fact that PostgreSQL type names are fixed and the serialization technique does not change much from type to type. So actually all the numeric types can be serialized using an example shown in the first class.

In springframework database abstraction model these classes can be used like that:

...
String sql = "select * from test.array_accepting_procedure( :text_array_param, :int_array_param)";

MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("text_array_param", new PostgreSQLTextArray(dto.getTextArray()),    java.sql.Types.ARRAY );
namedParameters.addValue("int_array_param",  new PostgreSQLInt4Array(dto.getIntegerArray()), java.sql.Types.ARRAY );

resultList =  getSimpleJdbcTemplate().queryForList( sql, namedParameters, mapper);
...

Passing arrays to PostgreSQL database from java (JDBC) by Valentine Gogichashvili is licensed under a Creative Commons Attribution 3.0 Unported License.