Re: Many to Many Relations

Hi Martin,

Martin_Snyder wrote:
> 
> Has anyone used SimpleORM in a system that requires many to many
> relationships?  I'm attempting this currently and running into some
> issues.
> 
> Normally I would do this with a simple mapping table containing
> foreign key references to the two object types.
> 
> SimpleORM however won't allow me to create a table without a primary
> key, so the obvious alternative appears to be to make every column
> part of the primary key.
> 
> When I did that however, simpleORM doesn't handle rows in a table like
> that particularly well.  I can create new records by calling
> findOrCreate with an object array with the primary key values.  At
> that point though, no matter what I do the record is never actually
> saved in the database.  Perhaps this is because it's not considered
> dirty because no non-key fields have been modified.  Of course,
> SimpleORM prevents me from updating a primary key field to mark it as
> dirty.

Use the setDirty() method to mark the object as dirty:

SConnection.begin();
Link link = (Link) Link.meta.findOrCreate(new Object[] {objA, objB});
link.setDirty();

Problem with SFieldMeta.setFieldValue

I've looked closer into the issues I've been having with my
Char/Boolean class and while I've been able to make things work to my
satisfaction (and to the satisfaction of my unit tests), I cannot seem
to find a happy medium that solves the issues created by
SFieldCharBoolean without breaking the SimpleORM unit tests.

The core of the issues is this:
  SFieldMeta.setFieldValue calls convertToField "too soon" in the
process and sets the in-memory value to be this converted value.  The
converted value is meant only for DB consumption so if a field is read
from the cached record after a set, you will see the "database value"
rather than the "in-memory" value.  The entire system works well (and
as intended as best I can determine) except for this case.

I attempted the naive solution as follows:
  1. Remove the convertToField call from setFieldValue and instead
make this call in SRecordInstance.flush.  The result of convertToField
is never cached in the runtime object.

  This failed mainly because (I assume) SRecordInstance.validateField
as overridden in the unit tests is written to receive the converted
value.  This may be wrong or it may be write, but changing it is sure
to seriously impact anyone using SimpleORM.

Step 2 in the naive progression was to convert the value solely for
consumption by SRecordInstance.validateField.  This though was not
enough to satisfy the unit tests, which is where I came to my stopping
point.

Re: Problem with SFieldMeta.setFieldValue

Hello Martin,

I think that the issue is that there can actually be three different 
representations, namely the external, internal and database ones.

First the there is external representation that some uses to call 
setField.  This should normally be Boolean.True/False but we generally 
allow as many alternatives as reasonable, eg. "Y", "Yes" "True" etc.

convertToField is called by setField to convert this to the internal 
ones, which in this case would always be Boolean.

Then writeFieldValue and queryFieldValue are used to convert between the 
internal value and the actual database value.

I don't quite see the problem with this approach from your description.

Thanks for your efforts,

Anthony

Martin_Snyder wrote:

> 
> I've looked closer into the issues I've been having with my
> Char/Boolean class and while I've been able to make things work to my
> satisfaction (and to the satisfaction of my unit tests), I cannot seem
> to find a happy medium that solves the issues created by
> SFieldCharBoolean without breaking the SimpleORM unit tests.

Re: Problem with SFieldMeta.setFieldValue

I agree with you in principle.  Let me try to explain this better.

The database representation is always either "T" or "F".  That is the
database representation

queryFieldValue converts it to a java.lang.Boolean.  That is the
internal representation

convertFieldValue accepts the external representations.  It will
access a java.lang.Boolean.  "T", "F" or case-insensitive versions of
"true" or "false".

SRecordInstance has a data member fieldValues which is an array of
Objects to represent the values of each field in the Object.  When the
object is loaded, it is always loaded from the database and thus
fieldValues will be populated with the internal representation.

When you call setObject, the fieldValue corresponding to that data
field is updated with the result of SFieldMeta.convertField.  Even if
you pass in the internal represntation (a java.lang.Boolean) it will
be converted to the database representation ("T" or "F") and stored in
fieldValues.

At that point, if you are to examine the value, you (as the caller)
are going to have to convert the database value to something else on
your own.  SFieldCharBoolean gets no chance to participate in that
conversion, so either the caller is left to deal with the database
representation or SRecordInstance has to have the details of the
conversion coded into it (in a getBoolean method for instance).

Re: Final modifications for Boolean support

Hello Martin,

I had a quick look at your work.

I think that you are using convertToField for two distinct purposes and 
you need another method which I had suggested be called writeFieldValue.

* convertToField converts form external to internal value.  (A better 
comment in SFieldMeta would help!)

* queryFieldValue converts from database to internal.

* writeFieldValue converts from internal to database.

I do not think that you can do it cleanly with just two methods.

I has also suggested that writeFieldValue actually do the setObject 
analagous to queryFieldValue.  This enables other set* methods to be used.

Also I would implement convertToField and queryFieldValue at the 
SFieldBoolean level and make them quite libral in what they will accept. 
  Eg.  "True", "Yes", "Y", != 0 etc.  But implement writeFieldValue 
strictly in SFieldTFCharBoolean.

Finally please rename SFieldCharBoolean to SFieldBooleanCharTF.  The 
reason is that I'll probably also add YN, 10 etc. later.  Also, where 
there are a number of related classes it is good to have them sort 
nicely in directory listings, eg. SDriver*.

Re: Final modifications for Boolean support

No problem.  I have emailed all of these changes to you.

--- In SimpleORM <at> yahoogroups.com, Anthony & Melissa Berglas
<berglas <at> S...> wrote:
> Hello Martin,
> 
> I had a quick look at your work.
> 
> I think that you are using convertToField for two distinct purposes and 
> you need another method which I had suggested be called writeFieldValue.
> 
> * convertToField converts form external to internal value.  (A better 
> comment in SFieldMeta would help!)
> 
> * queryFieldValue converts from database to internal.
> 
> * writeFieldValue converts from internal to database.
> 
> I do not think that you can do it cleanly with just two methods.
> 
> I has also suggested that writeFieldValue actually do the setObject 
> analagous to queryFieldValue.  This enables other set* methods to be
used.
> 
> Also I would implement convertToField and queryFieldValue at the 
> SFieldBoolean level and make them quite libral in what they will
accept. 
>   Eg.  "True", "Yes", "Y", != 0 etc.  But implement writeFieldValue 
> strictly in SFieldTFCharBoolean.

Re: Re: Final modifications for Boolean support

Hello Martin,

Looks pretty good to me.  I'll incorporate this into the next release. 
Thanks for your help.

Anthony

Martin_Snyder wrote:
> 
> No problem.  I have emailed all of these changes to you.
> 
> --- In SimpleORM <at> yahoogroups.com, Anthony & Melissa Berglas
> <berglas <at> S...> wrote:
> 
>>Hello Martin,
>>
>>I had a quick look at your work.
>>
>>I think that you are using convertToField for two distinct purposes and 
>>you need another method which I had suggested be called writeFieldValue.
>>
>>* convertToField converts form external to internal value.  (A better 
>>comment in SFieldMeta would help!)
>>
>>* queryFieldValue converts from database to internal.
>>
>>* writeFieldValue converts from internal to database.
>>
>>I do not think that you can do it cleanly with just two methods.

Converting a recordset to an array

Since you mentioned a new version I'm just going through my notes to
dig up any SimpleORM thoughts I had...

I wrote the following helper routine to convert a recordset to a typed
array.  It took me a little digging to get the syntax right to create
the typed array inside the routine like this.

If it fits in with your notion of what interface SRecordSet should,
feel free to drop it in there as a instance method.

public static Object[] toArray(SResultSet rs, Class arrayClass)
{
  List list = new LinkedList();
  while (rs.hasNext())
    list.add(rs.getRecord());

  Object array[] = (Object[])Array.newInstance(arrayClass,
list.size());
  list.toArray(array);
  return array;
}

So if you call it with EmployeeData.class, the return value will be of
type EmployeeData[] (even though obviously it has to be declared to
return an Object[].  This routine could also be changed to return
plain old "Object", or it could be changed to return a SRecordInstance
array.  I'm pretty sure those are the only three meaningful possible
return values though, but this is a discussion forum after all, so
have at it.

NULL values for foreign keys

One question I have about SimpleORM is how best to query for NULL
values for foreign key fields.

In my example, I have a heirarchy of objects for organizational
purposes.  Think of them as folders.  Each object has a parent field
which is another folder.  The top-level of the heirarchy has a value
of NULL for the parent field.

All of this works fine.  There are no tricks to the implementation.

I'd like to query to retrive the list of top-level objects in the
heirarchy though, and I can't see any easy way to do it.  If I use
newQuery().eq(PARENT, null), I get a SimpleORM exception when I try to
execute the query.  There are some isNull methods on SQuery, but their
interface indicated that it wasn't for what I was hoping.  What would
work for me logically is being able to do newQuery().isNull(PARENT).

The solution for me was to use rawQuery("parentid=null") which works
with no issue.  I only post to see if someone has a better recommendation.

Incidentally, one other thing I tried was to indicate the top-level of
the heirarchy by having the object reference itself.  SimpleORM really
doesn't like that and dies with a OutOfStackSpace error (or whatever
that's called).  I'm sure the importance is low as this has never come
up before, but it might qualify for an entry on the "lowest priority"
bug list.

- Martin

Support for EJB containers