The answer to this is actually a non-technical solution. You'd really be doing yourself a favor is you make those variable key-pairs fit into a generic table and then add a mapping table for the various keys.
Then when you look one up, use dbic to join your generic table to the map and you'll be fine. The bottom line is to abstract it enough so you don't need a column for each key. I know that's not specifically the question you asked, but that's probably the answer you'll need - be it now or a few weeks and new curse words later. ;) -frank Frank Speiser CTO TAKKLE, Inc. -----Original Message----- From: Richard Robinson <[EMAIL PROTECTED]> Date: Sat, 16 Jun 2007 18:24:10 To:[email protected] Subject: [Dbix-class] add_column oddness - what am I missing ? (a newbie question)] Hi. I'm new both to DBIx-Class and to SQL; I started using them (via Catalyst) a few months ago, and have vast amounts yet to learn, but I seem to have reached a point where the base of my project begins to work well enough that it'd be constructive to go back and try to understand some of the issues I've been dodging so far. The data I'm dealing with contains key/value pairs, where I don't necessarily know beforehand what the keys will be. I am storing this in a single table. [ I mentioned this to Matt last night, and he reacted as I expected - "what are you trying to achieve, that's probably not the best way to go about it", and I'm sure that's right, but it drags in so many wider issues that I'd like to shelve it for a moment, if I could, in favour of asking the simple question first :- ] Since I don't know in advance what keys it might meet, I can't create all the possible columns beforehand and give a complete list to the __PACKAGE__->add_columns startup call, I have to deal with them on the fly. On startup, look at the state of the sql table to see what columns it does contain, and on new input create new columns in it as necessary (using resultset->resultsource->storage->dbh to get at the sql, is there a higher-level way to do these ? I don't think so), and then in both cases do a ResultSource::add_column(). My question is :- where I list a column, say T, in the startup call, I can access it with either $row->get_column('T') or simply with $row->T, whereas if it's done in a later add_column call, the first of these returns the data as expected, but the second throws an exception - "Can't locate object method "T" in package ...". Which obviously is not a showstopper, but it looks like the kind of niggling little point which, if properly chewed on, might lead to a greater understanding. And I'm stuck, I can't see it. Why the difference ? Much reading of manuals, code, errors, etc, suggests that "accessor" is the word to chase, but I can't find anything that makes a difference ... what am I missing here ? As to the wider questions, "you shouldn't be doing it that way in the first place" - I'm a newbie, my head simply isn't big enough to reach a perfect understanding of everything first time round, so I'm going to have to iterate. My first try at this was 3 tables; one to list the items, one to list unique key/value pairs, and one to assign a set of key/values to a particular item; which looks more elegant, but being new also to SQL, I found myself out of my depth with the relationships involved, and simply wasn't able to get it running efficiently enough to be usable, so I shelved it in favour of the easier-to-think-about layout. And that seems to work quite nicely for me, but I'm getting the feeling from all over the place that That's Not One Of The Many Ways To Do It. So, would anyone be so kind as to point me towards some Clues as to why not ? -- Richard Robinson "The whole plan hinged upon the natural curiosity of potatoes" - S. Lem _______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
_______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
