[ic] Way to [query] tall instead of wide?

[Marty Tennison]

On Saturday, September 03, 2011 07:16:34 PM Paul Jordan wrote:
> Usually you can query select *, grab a bunch of fields, iterate over the
> results found. What if though, I wanted to a bunch of key-value pairs from
> a mess of rows?
> 
> As an example to grasp, let's say that all text on every page was editable.
> You're not going to make tables with a ton of awkward columns, you going to
> produce something like this (key omitted for brevity):
> 
> page_id     area_name      area_value
> 1                title                  My Designer Yoyo's
> 1                heading           Welcome to my......
> 1                email_link        Hit me up!
> 1                step_1_desc    To do step 1, just....
> 1                step 1_title       Do this first!
> (Assume a multi-part uniqueness on page_id+area_name)
> 
> Is there some way to query this and still end up with: [sql-param title]
> [sql-param heading] and so on? The body would not loop of course.
> 
> I would have thought something like this would be common - does it exist?
> If not, what would you suggest... a new purpose-built query tag, and
> enhancement to [query], or something else?
> 
> I would think with something like this, with the correct query, one could
> not only easily make each page's text customizable (with generic fall
> back), but also build in a permission system with the addition to a user
> column. With just one efficient query, you end up with no need for
> either/or/if/else to achieve what I mentioned.
> 
> I am guessing something sort of similar is done with the [L] language
> stuff?
> 
> Thank you all for your thoughts.
> 
> Paul
> 

Hi Paul,

Your on your way to an EAV structure...

http://en.wikipedia.org/wiki/Entity-attribute-value_model

But in my opinion you're looking for a scheme-less database solution.  You may 
want to checkout Mongodb. It lets you add arbitrary fields and values as needed 
and it has a Perl driver for it.   And soon there will be a full stack 
Mojolicous framework with native Mongodb support.   We use MongoDB and 
Mojolicous for just the issues you talk about.  It's just a matter of creating 
a usertag or globalsub for access to MonogoDB or use the Mojolicous web server 
and make restful calls to the resource you are looking for.  

I'm sure there are other alternatives. that's just my 2 cents.

I hope that helps. 
-- 
- - -- ---- ---------------------------------------------------- --- -- - -
Marty Tennison
- -- --- ---------------------------------------------------------- --- --