Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: local policy)
From: Boblitz John <john.boblitz@bertschi.com>
To: "users@openjpa.apache.org" <users@openjpa.apache.org>
Subject: RE: Postgres HStore implementation
Thread-Topic: Postgres HStore implementation
Thread-Index: Ac+R7qvXU97GRUwyQPCWosBI3S7TmACapK8AACYcuoA=
Date: Tue, 1 Jul 2014 06:34:22 +0000
Message-ID: 
 <88CB62524DE0644AAE21D8BAB8CFC0CA0BC2EE40@dueexs06.bertschi.domain>
References: 
 <88CB62524DE0644AAE21D8BAB8CFC0CA0BC2D899@dueexs06.bertschi.domain>
 <1404136124597-7586890.post@n2.nabble.com>
In-Reply-To: <1404136124597-7586890.post@n2.nabble.com>
Accept-Language: de-CH, en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Thanks Chris!

The part I was missing was the Annotation - I had almost completed a custom=
 implementation, but this works well now.

FWIW:  With the latest jdbc4 driver from Postgres, a lot of the legwork is =
no longer needed.

I'll do a write up of my solution and post it to the group - maybe it will =
help someone else.

Cheers,

John

> -----Original Message-----
> From: Krzysztof [mailto:yazuna@gmail.com]
> Sent: Montag, 30. Juni 2014 15:49
> To: users@openjpa.apache.org
> Subject: Re: Postgres HStore implementation
>=20
> You'd need jdbc mapper for hstore (might be included in newer jdbc driver=
s),
> I use this one:
>=20
>=20
> /*-----------------------------------------------------------------------=
------
>  *
>  *                      Gaia CU7 variability
>  *
>  *         Copyright (C) 2005-2020 Gaia Data Processing and Analysis
> Consortium
>  *
>  *
>  * CU7 variability software is free software; you can redistribute it and=
/or
>  * modify it under the terms of the GNU Lesser General Public
>  * License as published by the Free Software Foundation; either
>  * version 2.1 of the License, or (at your option) any later version.
>  *
>  * CU7 variability software is distributed in the hope that it will be us=
eful,
>  * but WITHOUT ANY WARRANTY; without even the implied warranty of
>  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
>  * Lesser General Public License for more details.
>  *
>  * You should have received a copy of the GNU Lesser General Public
>  * License along with this CU7 variability software; if not, write to the
>  * Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Bosto=
n,
>  * MA  02110-1301  USA
>  *
>=20
> *------------------------------------------------------------------------=
-----
>  */
> package gaia.cu7.om.dal.dictionary.PGType;
>=20
> /*
>=20
>  * or more contributor license agreements.  See the NOTICE file
>  * distributed with this work for additional information
>  * regarding copyright ownership.  The ASF licenses this file
>  * to you under the Apache License, Version 2.0 (the
>  * "License"); you may not use this file except in compliance
>  * with the License.  You may obtain a copy of the License at
>  *
>  * http://www.apache.org/licenses/LICENSE-2.0
>  *
>  * Unless required by applicable law or agreed to in writing,
>  * software distributed under the License is distributed on an
>  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
>  * KIND, either express or implied.  See the License for the
>  * specific language governing permissions and limitations
>  * under the License.
>  */
>=20
> import java.io.Serializable;
> import java.util.Collection;
> import java.util.Iterator;
> import java.util.LinkedHashMap;
> import java.util.Map;
> import java.util.Set;
>=20
> import org.postgresql.util.PGobject;
> import org.postgresql.util.PSQLException;
> import org.postgresql.util.PSQLState;
>=20
> /**
>  * Class that handles hstore contrib datatype.
>  * In PG JDBC uses non-standard PGObject interface instead of SQLData
>  *
>  * @author knienart inspired by Petr Jelinek
>  * @version $Id: PGhstore.java 319537 2013-10-08 20:49:15Z knienart $
>  * @since 10.0
>  */
> @SuppressWarnings("rawtypes")
> public class PGhstore extends PGobject implements Serializable, Cloneable=
,
> Map {
>=20
> 	private final static long serialVersionUID =3D 1;
> 	private Map hashList;
>=20
> 	public PGhstore() {
> 		setType("hstore");
> 	}
>=20
> 	public PGhstore(String value) throws java.sql.SQLException {
> 		this();
> 		setValue(value);
> 	}
>=20
> 	@SuppressWarnings("unchecked")
> 	public PGhstore(Map value) {
> 		this();
> 		hashList =3D new LinkedHashMap(value);
> 	}
>=20
> 	@SuppressWarnings("unchecked")
> 	@Override
> 	public void setValue(String s) throws java.sql.SQLException {
> 		hashList =3D new LinkedHashMap();
>=20
> 		if (s !=3D null) {
> 			char[] chars =3D s.toCharArray();
> 			String key =3D null;
> 			StringBuffer buffer =3D new StringBuffer();
> 			boolean insideKey =3D true;
> 			boolean insideVal =3D false;
> 			boolean insideString =3D false;
>=20
> 			for (int i =3D 0; i < chars.length; i++) {
> 				// escape character that we need to skip
> 				if (chars[i] =3D=3D '\\') {
> 					i++;
> 				}
>=20
> 				// white space
> 				else if (!insideString &&
> Character.isWhitespace(chars[i])) {
> 					continue;
> 				}
>=20
> 				// the =3D> between key and value
> 				else if (!insideString && chars[i] =3D=3D '=3D') {
> 					i++;
> 					if (i =3D=3D chars.length)
> 						throw new
> PSQLException("Unexpected end of string", PSQLState.DATA_ERROR);
>=20
> 					if (!insideKey || chars[i] !=3D '>')
> 						throw new
> PSQLException("Syntax error at position " + i, PSQLState.DATA_ERROR);
>=20
> 					insideKey =3D false;
> 					insideVal =3D true;
>=20
> 					key =3D buffer.toString();
> 					buffer.setLength(0);
>=20
> 					continue;
> 				}
>=20
> 				// quote, item separator or end of string
> 				else if (chars[i] =3D=3D '"' || (!insideString &&
> chars[i] =3D=3D ',') || i =3D=3D chars.length - 1) {
> 					if (chars[i] =3D=3D '"') {
> 						insideString =3D !insideString;
> 						if (i !=3D chars.length - 1)
> 							continue;
> 					} else if (chars[i] !=3D ',' && buffer !=3D
> null) {
> 						buffer.append(chars[i]);
> 					}
>=20
> 					String b =3D (buffer =3D=3D null) ? null :
> buffer.toString();
>=20
> 					// end of element, add it to list
> 					if (b !=3D null && (b.length() > 0 ||
> insideVal)) {
> 						hashList.put(key,
> b.equalsIgnoreCase("NULL") ? null : b);
> 					}
>=20
> 					insideKey =3D true;
> 					insideVal =3D false;
> 					buffer =3D new StringBuffer();
>=20
> 					continue;
> 				}
>=20
> 				if (buffer !=3D null)
> 					buffer.append(chars[i]);
> 			}
> 		}
> 	}
>=20
> 	@Override
> 	public String getValue() {
> 		if (hashList =3D=3D null)
> 			return null;
>=20
> 		Iterator iter =3D hashList.entrySet().iterator();
> 		if (!iter.hasNext())
> 			return null;
>=20
> 		Entry e =3D (Entry) iter.next();
> 		StringBuffer buffer =3D new StringBuffer();
> 		appendEntry(buffer, e);
>=20
> 		while (iter.hasNext()) {
> 			e =3D (Entry) iter.next();
> 			buffer.append(',');
> 			appendEntry(buffer, e);
> 		}
>=20
> 		return buffer.toString();
> 	}
>=20
> 	private void appendEntry(StringBuffer buf, Entry e) {
> 		appendValue(buf, e.getKey(), true);
> 		buf.append("=3D>");
> 		appendValue(buf, e.getValue(), false);
> 	}
>=20
> 	private void appendValue(StringBuffer buf, Object v, boolean isKey)
> {
> 		if (v =3D=3D null) {
> 			if (isKey)
> 				buf.append("\"NULL\"");
> 			else
> 				buf.append("NULL");
> 			return;
> 		}
>=20
> 		String s =3D v.toString();
>=20
> 		buf.append('"');
> 		for (int i =3D 0; i < s.length(); i++) {
> 			char c =3D s.charAt(i);
> 			if (c =3D=3D '"' || c =3D=3D '\\')
> 				buf.append('\\');
> 			buf.append(c);
> 		}
> 		buf.append('"');
> 	}
>=20
> 	public Collection values() {
> 		return hashList.values();
> 	}
>=20
> 	public int size() {
> 		return hashList.size();
> 	}
>=20
> 	public Object remove(Object key) {
> 		return hashList.remove(key);
> 	}
>=20
> 	@SuppressWarnings("unchecked")
> 	public void putAll(Map m) {
> 		hashList.putAll(m);
> 	}
>=20
> 	@SuppressWarnings("unchecked")
> 	public Object put(Object key, Object value) {
> 		return hashList.put(key, value);
> 	}
>=20
> 	public Set keySet() {
> 		return hashList.keySet();
> 	}
>=20
> 	public boolean isEmpty() {
> 		return hashList.isEmpty();
> 	}
>=20
> 	public Set entrySet() {
> 		return hashList.entrySet();
> 	}
>=20
> 	public boolean containsKey(Object key) {
> 		return hashList.containsKey(key);
> 	}
>=20
> 	public Object get(Object key) {
> 		return hashList.get(key);
> 	}
>=20
> 	public boolean containsValue(Object value) {
> 		return hashList.containsValue(value);
> 	}
>=20
> 	public void clear() {
> 		hashList.clear();
> 	}
>=20
> }
>=20
>=20
> //end of pghstore
>=20
> add it to jdbc properties:
> jdbc://yourjdbcURL?datatype.hstore=3Dgaia.cu7.om.dal.dictionary.PGType.PG
> hstore
>=20
>=20
> define the strategy:
> import gaia.cu7.om.dal.dictionary.PGType.PGhstore;
>=20
> import java.sql.Types;
> import java.util.HashMap;
> import java.util.Map;
>=20
> import org.apache.openjpa.jdbc.identifier.DBIdentifier;
> import org.apache.openjpa.jdbc.kernel.JDBCStore;
> import org.apache.openjpa.jdbc.meta.JavaSQLTypes;
> import org.apache.openjpa.jdbc.meta.ValueMapping;
> import org.apache.openjpa.jdbc.meta.strats.AbstractValueHandler;
> import org.apache.openjpa.jdbc.schema.Column;
> import org.apache.openjpa.jdbc.schema.ColumnIO;
> import org.apache.openjpa.jdbc.sql.DBDictionary;
> import org.apache.openjpa.util.InternalException;
>=20
> /**
>  * Base class for PG hstore value handlers.
>  *
>  * @author knienart
>  * @version $Id: HStoreValueHandler.java 359484 2014-04-08 13:57:13Z
> knienart $
>  * @since 10.0
>  */
> @SuppressWarnings("serial")
> public class HStoreValueHandler
>     extends AbstractValueHandler {
>     private static final String dbTypeName =3D "hstore";
>     private static final int javaSQLType =3D JavaSQLTypes.PC;
>     private static final int jdbcType =3D Types.OTHER;
>     private static final int storeSize =3D -1;
>=20
>     public Column[] map(ValueMapping vm, String name, ColumnIO io,
>         boolean adapt) {
>         DBDictionary dict =3D vm.getMappingRepository().getDBDictionary()=
;
>         DBIdentifier colName =3D DBIdentifier.newColumn(name, dict !=3D n=
ull ?
> dict.delimitAll() : false);
>         return map(vm, colName, io, adapt);
>     }
>=20
>     public Column[] map(ValueMapping vm, DBIdentifier name, ColumnIO io,
>         boolean adapt) {
>         Column col =3D new Column();
>         col.setIdentifier(name);
>         col.setJavaType(javaSQLType);
>         col.setSize(storeSize);
>         col.setTypeIdentifier(DBIdentifier.newColumnDefinition(dbTypeName=
));
>         col.setType(jdbcType);
>=20
>         return new Column[]{ col };
>     }
>=20
>     public Object toDataStoreValue(ValueMapping vm, Object val,
>         JDBCStore store) {
>         // check for null value.
>         if (val =3D=3D null)
>             return null;
>         	if(!(val instanceof Map))
>         		throw new InternalException("Wrong type - not a map but "
> + val.getClass().getCanonicalName());
>         	if(((Map)val).isEmpty())return null;
>         	PGhstore result =3D new PGhstore((Map)val);
>         	return result;
>=20
>     }
>=20
>     public Object toObjectValue(ValueMapping vm, Object val) {
>         // check for null value.
>         if (val =3D=3D null)
>             return null;
>         try {
>             /**get the object/string and marshall it into the map<key,val=
ue>
>              * We could use PGObject mapping here but initialization in J=
PA is
> problematic
>              */
>         	if(!(val instanceof HashMap))
>         		throw new InternalException("Wrong type - not a HashMap
> but " + val.getClass().getCanonicalName());
>=20
>         	return (Map) val;
>         }
>         catch (Exception e) {
>         	e.printStackTrace();
>             throw new InternalException(e);
>         }
>     }
> }
>=20
> and use it like this i.e.:
>=20
> 	@PersistentMap(fetch=3DFetchType.EAGER )
> 	@Strategy ("gaia.cu7.om.dal.dictionary.HStoreValueHandler")
> 	protected Map<String, String> otherStringParameters =3D new
> HashMap<String,
> String>();
>=20
> cheers
> Chris
>=20
>=20
>=20
> Boblitz John wrote
> > Hello,
> >
> > I'm trying to get the Postgres HStore type to work and play nice in
> > openJPA 2.3 and I've hit a wall ...
> >
> > Are there any examples around / has anyone done this before and would
> > be willing to share?`
> >
> > Thanks & Best Regards,
> >
> > John
>=20
>=20
>=20
>=20
>=20
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Postgres-HStore-implementation-
> tp7586887p7586890.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.