API design question

[Buyocat]

I am starting to put together a tool to generate basic SQL statements (i.e. CRUD statements) based on some stuff from a XML file.  In a nutshell it'll be the Active Record pattern but use the XML file to control database mirroring instead of doing some sort of query.  Setting aside the merits of one approach over another my question is for you guys the end user.  Imagine you had a set of methods, say:

 

insert(& $row);

 

and,

 

update(& $row);

 

I have them as references but assume that the function is const.  My question is given your experience would it be better to ask row to be a hash with keys = to rows in the table, or to have row be treated as an array where the keys are irrelevant?

 

The benefit of requiring row to be a hash is that the elements can be in any order, so if you imagine the data is coming from a form the fields in the form could be in a different order than the elements in table, but they would need to reflect the table's field names (this would certainly reduce the value of the utility).

 

The benefit of the other route is that it avoids the drawback of the last approach.  Since the values are keyless though they must instead be ordered such that the values are in the same order as the fields in the table.

 

As you can see both approach carries a drawback which reduces the effectiveness of the utility, but my question is in your experience which route do you often take?  Are your data houses key-value pairs which correspond to table fields, or are they ordered like the table's fields, or both?

 

Also, any suggestions to get around these issues altogether would be wonderful.

[Buyocat]

For now I've come up with what I believe is a good solution.  I will add an attribute to the xml that names the xml element.  So the application will reference the name for the xml while the name of the field can change freely.  This way the utility can access passed value as a hash, and the hash values can remain constant/independent of changes to the table.

[roopurt18]

Why don't you write it so either will work?  If the named keys can not be found, assume a numerically indexed array.  Or write two different functions.

[Buyocat]

Roopurt, the reason is because I want to allow the application code to remain constant even as table fields change.  That's the tool's real purpose.  If I write code which takes into consideration what order the fields are in then I am writing code which will break should the table structure change.  This wouldn't be a big deal if there was just one query which needed to be changed every time the table changed, but there could conceivably be many many queries, so from a maintainability standpoint it would be a nightmare.

 

Thanks for the feedback, and I hope that explains my position.

[dbo]

I was just curious where you were at on developing this utility.

[Buyocat]

dbo, I've actually changed the project slightly.  You can see it here: http://code.google.com/p/junction/

 

It's a persistance layer which would allow you to use a data object instead of dealing with the database.  Junction would take care of all the mapping of state between the database and your data object.

 

If you'd like to talk more about it private message me.