Adam,

I like it. Bunch of comments follow.

A "Multi-row field" represents a collection, so I think we should call it a
"collection field". To me "Multi-row" suggests a particular SQL
implementation.

These collections are unordered, right?

Re your audit trail example for adding to a collection:

```
User 'Joe' updated Tree 482, added 'watered' by 'Jim' on '3/3/2003'
```

I think it would need to look something like this:

```
User 'Joe' updated Tree 482, added to 'watered' (by='Jim', on='3/3/2003')
```

Re scalar implementation:
* Consider calling the model column containing scalar values
"udf_scalar_values" instead of "udf"
* Consider using "model_type" instead of "model" as a UDFD column name

Re the table storing collection values:

```
Multi User Defined Field Value (MUDFV)
id - primary key
data - hstore
field_def - Foreign Key to MUDFD
```

Comments:
* Consider calling it "UDF Collection Values"
* field_def would be a foreign key to the "UDFD" table (not "MUDFD")
* It needs a "model_id" field

Re your scalar search example:

```
{'tree.udf.planting_user': {'IS': 5}}

SELECT * FROM treemap_tree WHERE udf->planting_user = 5;
```

Since hstore values are strings that would have to be:

```
SELECT * FROM treemap_tree WHERE udf->planting_user = '5';
```

Re your collection search example:

```
{'tree.udf.watered_at.waterdate': {'LT': '5/5/2005'}}

SELECT * FROM treemap_tree AS t
LEFT OUTER JOIN multi_udf_data AS m
ON t.pk = m.model_id
WHERE m.model = 'Tree' AND m.data->waterdate < '5/5/2005'
```

I think the WHERE clause would need to be:

```
WHERE m.field_def = 'UDF:26' AND to_date(m.data->waterdate) < '5/5/2005'
```

Since hstore values are strings, we have to convert them to dates before
comparing. Will that be an efficiency problem in a million-tree instance?
You can index an hstore, but you have to use strings. Maybe we should store
our dates as e.g. "20130723" so we can use string comparisons.

Re auditing, I think it needs a bit more mechanism for collection fields
* An "operation" field ('change' | 'add' | 'remove'), where scalar audits
are always 'change'
* Would current_value and previous_value for collections use hstore syntax,
e.g. "by=>'Jim',on='3/3/2003'"


On Mon, Aug 5, 2013 at 3:06 PM, Justin Walgran <notifications@github.com>wrote:

> In doc/userdefinedfields.md:
>
> > +
> > +Searching in multi-row situations becomes a bit harder. Using the
> > +'watered at' example about we may want to search for all trees that
> > +haven't been watered for a week (assume that 5/5/2005 was a week ago).
> > +
> > +```
> > +{'tree.udf.watered_at.waterdate': {'LT': '5/5/2005'}}
> > +```
> > +
> > +This transforms in to the slightly more complicated SQL:
> > +
> > +```
> > +SELECT * FROM treemap_tree AS t
> > +LEFT OUTER JOIN multi_udf_data AS m
> > +ON t.pk = m.model_id
> > +WHERE m.model = 'Tree' AND m.data->waterdate < '5/5/2005'
>
> Looks like this query would give you any tree watered before 5/5/2005,
> even if it had been watered since. I think you need
>
> WHERE m.model = 'Tree' AND MAX(m.data->waterdate) < '5/5/2005'
>
> However, this aggregation would prevent you from doing a select * so it
> would need to be pushed down into a subquery that just returns tree_idthat could be JOINed as a filter.
>
> —
> Reply to this email directly or view it on GitHub<https://github.com/azavea/OTM2/pull/141/files#r5587029>
> .
>



-- 
Rick Mohr, Software Developer

Azavea |  340 N 12th St, Ste 402, Philadelphia, PA
rmohr@azavea.com  | T 215.701.7504  | F 215.925.2663
Web azavea.com <http://www.azavea.com/>  |  Blog
azavea.com/blogs<http://www.azavea.com/Blogs>
| Twitter @azavea <http://twitter.com/azavea>