Is it possible to have a read-only column in a Cayenne model?

[Marco A Gonzalez <ma...@mac.com>]

Hi,
We’re using Cayenne 4.0 with PostgreSQL and for various reasons need to use a read-only column whose value is calculated by a SQL function. 

Accessing the column value using a simple SELECT clause works great. Also, when generating the SQL for an UPDATE statement, Cayenne is smart enough not to include the columns whose values haven’t changed. Since we haven’t figured out how to mark a column as read-only in Cayenne Modeler, Cayenne includes it when generating the SQL for the INSERT statement which obviously fails (because there’s no place for the value to be stored).

Is there a way to mark a column as read-only, even if we have to edit the relevant XML file by hand?
If this isn’t the way to do it, is there a way to tell Cayenne NOT to include the column when generating the SQL for an INSERT/UPDATE statement?

- Marco A.

Re: Is it possible to have a read-only column in a Cayenne model?

[Andrus Adamchik <an...@objectstyle.org>]

Hi Marco,

Unfortunately there's no way to *map* a persistent property that can be read into an object, but ignored on updates. One possible solution is to use the new multi-result ObjectSelect from 4.0.M6 to fetch extra values together with corresponding persistent objects and then manually copy them into a non-persistent property of each object. E.g.:

	// define some reusable metadata
	Property<MyEntity> self = Property.createSelf(MyEntity.class);
	Property<Long> relCount = MyEntity.RELATED_LIST.count();

	// build and run query
	Object[] result = ObjectSelect
	   .columnQuery(MyEntity.class, self, relCount)
	   .select(context);

	// postprocess the result
	List<MyEntity> finalResult = result
	   .stream()
	   .map(array -> {
	      MyEntity e = array[1];
	      e.setXyz((Long) array[0]);
	      return e;
	   }).collect(Collectors.toList());
	 
Whether this will work or not depends on what SQL function you need there (Cayenne will need to support it [1]), and will also not work is MyEntity is retrieved via a relationship instead of a query that you can control.

Maybe someone will chime in with a better workaround?

Andrus

[1] https://github.com/apache/cayenne/blob/master/cayenne-server/src/test/java/org/apache/cayenne/exp/FunctionExpressionFactoryTest.java

> On May 10, 2017, at 1:09 AM, Marco A Gonzalez <ma...@mac.com> wrote:
> 
> Hi,
> We’re using Cayenne 4.0 with PostgreSQL and for various reasons need to use a read-only column whose value is calculated by a SQL function. 
> 
> Accessing the column value using a simple SELECT clause works great. Also, when generating the SQL for an UPDATE statement, Cayenne is smart enough not to include the columns whose values haven’t changed. Since we haven’t figured out how to mark a column as read-only in Cayenne Modeler, Cayenne includes it when generating the SQL for the INSERT statement which obviously fails (because there’s no place for the value to be stored).
> 
> Is there a way to mark a column as read-only, even if we have to edit the relevant XML file by hand?
> If this isn’t the way to do it, is there a way to tell Cayenne NOT to include the column when generating the SQL for an INSERT/UPDATE statement?
> 
> - Marco A.
> 
>