Building and returning structured records from a query

AidanLee has asked for the wisdom of the Perl Monks concerning the following question:

Hello again all,

I'm working on some generalized code, of which the following is an example case. I have the following structure in a hash (brought to you today by Data::Dumper):

'structure' => {
                 'groups' => {
                               'properties' => {
                                                 'groupid' => undef,
                                                 'grouptitle' => undef
                                               },
                               'source' => 'user.groups'
                             },
                 'identity' => {
                                 'properties' => {
                                                    'username' => unde
+f,
                                                    'id' => undef
                                                  },
                                 'source' => undef
                               }
              },
[download]

And I have a SQL query that will retrieve the values for groupid,grouptitle,username and id. It may return one or many rows, but what I'd like to do is to take every record that comes through and stuff it into the above structure, and then pass a list of these hashrefs back to the calling code.

I've already got ideas on how to handle one-to-many relationships within records. Case in point is the 'groups' hashref. When i do a lookup on it's 'source' attribute, i get back that it has a toMany relationship with the primary key for this query (which in this case is 'id' ). So I know that the record structure i return will look like this:

{
    'groups' => [
                  {
                    groupid=> '0',
                    grouptitle=>'SysAdmins',
                  },
                  {
                    groupid=> '1',
                    grouptitle=>'PerlGurus',
                  }
                ]
    'identity' => {
                    username=> 'LWall',
                    id=>1
                  }
}
[download]

I intend to handle this by having the records stored in a hash (using the query's primary key for the hash's key), and an array of references to the hash entries to maintain order (important as the sql queries will often have Order By clauses). I'll then return either the array or a reference to that array to the calling code.

My question is how to get there from here. What would people suggest for the best approach for:

making a new record structure in the hash of records when a new value of the primary key is encountered
stuffing the values returned from the query into this structure

Comment on Building and returning structured records from a query Select or Download Code

Replies are listed 'Best First'.
Re (tilly) 1: Building and returning structured records from a query by tilly (Archbishop) on Jun 22, 2001 at 16:33 UTC
I suspect you have to think through your desired data structure more before you can write the code you want. However as a general idea I would suggest having the initial insertion code always assume the general case (multiple keys) and then perform a second pass to simplify the surprised excess. For an example of how to perform the second pass you can study how XML::Simple does it.	[reply]
Re: Building and returning structured records from a query by AidanLee (Chaplain) on Jun 22, 2001 at 18:21 UTC
From the chatterbox I seem to have caused confusion among those Monks who would enlighten me. My appologies.... In Ages Past... (ok, ok about december of 2000, and more recently for the past week or so) I've been looking to do away with some of the repetitive coding i've been doing in my CGI work. This particular work focuses around retrieving/displaying information from a database. I've also just taken up using the Template Toolkit which has really been a burden off my scripts' shoulders. It occurred to me that a good model for thinking about the database info retrieval is that I'm thinking in terms of site resources (users, articles, user groups, etc), and that the display of them are certain "views" of these resources. So I posited that it would be best if I could write some structure that let me think in terms of these resources when coding. I came up with this xml format (which down the road I intend to making formally RDF): <?xml version="1.0" ?> <RDF> <resource id="user" primary_table="csUsers" > <property id="username" source="db://csUsers.UserName" /> <property id="id" source="db://csUsers.ID" /> <property id="date_created" source="db://csUsers.Date_Created" / +> <property id="groups" source="rs://group" relation="toMany" /> </resource> <resource id="group" primary_table="csGroups"> <property id="title" source="db://csGroups_lang.Title" /> <property id="id" source="db://csGroups.ID" /> <property id="users" source="rs://user" relation="toMany" /> </resource> <view id="user_summary" resource="user" key="id"> <group id="identity"> <property id="username" source="rs://user.username" /> <property id="id" source="rs://user.id" /> </group> <group id="groups" source="user.groups" > <property id="grouptitle" source="rs://user.groups.title" /> <property id="groupid" source="rs://user.groups.id" /> </group> </view> <link table1="csUsers" table2="csGroups" type="indirect" > <link type="direct" table1="csUsers" table2="csUsers_Groups" key1= +"ID" key2="UserID" /> <link type="direct" table1="csGroups" table2="csUsers_Groups" key1 +="ID" key2="GroupID" /> </link> <link type="direct" table1="csUsers" table2="csUsers_lang" key1="ID" + key2="ID" /> <link type="direct" table1="csGroups" table2="csGroups_lang" key1="I +D" key2="ID" /> </RDF> [download] Now, I've got code written that both caches this file format so i don't have to re-parse every go-round, and code that generates a SQL query for a given view, along with conditional,sort, and range limit parameters supplied by the caller. What I want to return to the caller is a list of hashrefs that mimic the structure of the view from the xml file. That way it can send it onto it's Template handler so that in the template I can just say something like: `<table> [% FOREACH user = query_results %] <tr> <td> <a href="userdetails.cgi?id=[% user.identity.id %]">[% user.iden +tity.username %]</a> </td> <td> [% FOREACH group user.groups %] <a href="groupdetails.cgi?id=[% group.groupid %]">[% group.group +title %]</a> [% END %] </td> </tr> [% END %] </table>` [download] To that end I am currently thinking of how to create that data structure to return to the caller. My current thoughts are to return a reference to an array (to maintain sort order). The elements in the array are references into a hash. This hash stores all the records returned by the query, keyed by the view's 'key' property from the xml file. The question is how to store the query into those hash entries. I've thought of storing the path to the location of a property (e.g., from above the path to the value for 'username' would be 'identity,username'). a view's root elements would be pathless. I could see using symbolic references or evals to do the mapping: `$paths{username} = '{identity}{username}'; ... eval "$hash{$id}$path = $username"; ... push @array_to_return, $hash{$id}` [download] but in my mind this does not seem like a particularly desireable solution. I'd like to stay away from symbolic references as well, for the love of strict. So here we have my dilemma...how to store the returned records from the query into the desired structure? If anything else needs clarified, I will try. I've been pondering this for quite some time and could use the scrutiny of some fellow monks	[reply] [d/l] [select]