alaska.saedelere has asked for the wisdom of the Perl Monks concerning the following question:

dear monkz.

up to this day i have been able to struggle myself through that behemoth that is called Perl, but now i am in need of assistance.

i have inherited a piece of corporate software that makes my eyes bleed and my brain hurt. i am tasked to refactor it (at least there is SOME sanity in this) prior to implement new features. for corporate concerns i cannot post any "real" code. i try to get along with pseudo-code.

the software in question is a Perl module (used in a web-app) that receives three lists. first is a list of "group fields" (like 'username', 'book_id'), second is a list of "value fields" (like 'no_of_book_sales', 'comission_total'), third is an optional list containg filter definitions. the fields may come from up to 4 different tables. it returns the SQL to make a 'sensible' query to our database.

the parameters come from a web fronted that is designed to make it easy for our clients to create user designed queries via browser. depending on the incoming query fields the module decides from which table to select which fields in which order. there are also lots of optimizations and special cases (most of them to ensure a tight search when filtering) which are handled by a nightmarish construction of try catch, if and elseif blocks. not to mention that beast of a dispatch table that is alone capable of getting you 3 grey hairs (per day you work on it).

a very important special case is the "stats" table of which we have 3 instances. "stats_all", "stats_fast", "stats_faster". the last two are condensed (by dropping group fields and summing) versions of stats_all, each about 20%-30% of the size the bigger table. each table has a different duration. the data in stats_all only lasts 3 months, the data in stats_fast 1year and the data in stats_faster lasts (hopefully) for ever.

The current module investigate the "wanted fields" lists and selects the appropriate table to use.

At least, the connections between the source tables are relatively easy to handle. the mentioned "3 tier table" is always leading and there are only left joins.

Honestly, I have no idea how to tackle this. I read about sql-dictionaries, SQL::Abstract (which is unfriendly to nesting anonymous sub queries), and others.

someone got a cluebat for me?

Replies are listed 'Best First'.
Re: Inheriting Bullsh!t
by talexb (Chancellor) on Nov 30, 2009 at 19:26 UTC
      At least, the connections between the source tables are relatively easy to handle. the mentioned "3 tier table" is always leading and there are only left joins.

    Your explanation sounds very straightforward to me where it ends at this point. I would imagine you want to confirm that when certain different lists are presented on the inputs, you get sane SQL as the output.

      Honestly, I have no idea how to tackle this. I read about sql-dictionaries, SQL::Abstract (which is unfriendly to nesting anonymous sub queries), and others.

    Start with the simplest possible input list, and confirm that the correct SQL comes out, using tests of course. Start to make your lists more complex and confirm that as you do that, your queries keep pace. Do that as far as you can.

    When you start to run out of steam there, try the web interface controls and see what other combinations and permutations you can think of for the inputs, and make sure that they produce the right SQL.

    You might be able to use SQL::Abstract to reduce the complexity of some of this code -- but I would hold off doing that until you're re-writing this code based on the work you've done building the test suite.

    Sounds like fun -- good luck!

    Alex / talexb / Toronto

    Team website: Forex Chart Monkey, Forex Technical Analysis and Pickpocket Prevention

    "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

[reply]
      And you should note that if this works, you've started writing your test suite! Go ahead and use Test::More/etc. to write this code; you can then keep checking in the tests as you make more of them to figure out what the code does.

      You can simulate the Web stuff by sending URLs to your backend server - even if there's a lot of Javascript in the front end, you can just look at the access log to see what URLs were visited and visit those directly with WWW::Mechanize.

      Finally, make sure that the code is checked into a source code control system - whatever one you're comfortable with if it's not already in source control. You want the option to explore the code and back out changes that turn out to be wrong.

[reply]
Re: Inheriting Bullsh!t
by moritz (Cardinal) on Nov 30, 2009 at 18:42 UTC
[reply]
Re: Inheriting Bullsh!t
by zentara (Cardinal) on Nov 30, 2009 at 17:32 UTC
    Honestly, I have no idea how to tackle this.

    ...... after reading your bullsh*t bingo problem description..... neither do i... :-)

    I'm not really a human, but I play one on earth.
    Old Perl Programmer Haiku
[reply]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Inheriting Bullsh!t
by jeffreyray (Sexton) on Dec 01, 2009 at 06:16 UTC
    You might be interested in Fey to generate your SQL.
[reply]
      Thank you very much. This looks veeeeeeeeeery promising.

      Also many thanks for the interesting reading (moritz) and the thought of using a tested approach.

      When the first complete Perl6 implementation is finished it will spontaneously gain intelligence. - Damien Bombay
      Not if I can hinder it. - Rick Deckard
[reply]
Re: Inheriting Bullsh!t
by Limbic~Region (Chancellor) on Dec 01, 2009 at 15:43 UTC
[reply]

Back to Seekers of Perl Wisdom