Contacting Authors and creating SQL

So yesterday I was having a problem. I wanted to send an email using MIME::Lite from a PERL
script,
but such that auto responders wouldn't send back.

I trawled through the RFC to see what things they suggested. It is a lot of boring documentation, but I found 3 useful things

1) Subjects should have autoreply in them
2) The auto-submitted header should be set
3) Auto responders should ignore anything that comes with a Precendence: list header

The first two I could programmatically cope with (if (x) { ignore in some way }).

The third needed me to change my outgoing posts. So, off to the documentation for MIME::Lite to find how to set the Precendence header.

Unfortunately, I couldn't find this, and just using MIME::Lite->new(Precendence => 'list'); wasn't working. Give up I did not! I mailed the very nice man who wrote MIME::Lite as to whether a) it did it, or b) if it would be put in a future release.

Bingo, he mailed me back. Simply do MIME::Lite->new('Precendence:' => 'list'); Hey presto,it worked - brilliant. (My biggest thanks to eryq for his quick informative response!)

In fact, he mentioned you can set any of the non-standard MIME::Lite setting headers in this way. A very useful thing to know indeed.

So, one quick change and a test later, and we have this in svn!

On to the next issue - generating advanced queries in SQL programmatically.

So, I have been set for this sprint to write an advanced query page. I have taken this slowly and steadily, as I want to get it right! The first task is just to get it self generating some multi-table queries, that also use join tables, performing a simple one statement select.

Easy or hard? That is the question.

The way I have looked at it is that I, the wonderful software guru, know the database tables. After all, why wouldn't I? In a search model, I create 4 new methods.

1) advanced_search - this is to generate the SQL, perform it and return the results
2) search_for - this returns a hash of all the required fields and corresponding tables based on what the user has requested.
3) search_conditions - this returns a hash of what fields are being selected on, and corresponding tables for those. Here, I can write some additional extra WHERE statements to group together more than one field lookup if needed (which for loader was)
4) table_links - this returns a hash of all tables that have foreign keys, and the table for that foreign key (all keys have the format "id_").

With this and some looped code, I have managed to generate some rather complex single SELECT statements in the format of

SELECT DISTINCT tablea.id_tablea, tablea.name, tableb.id_tableb, tablec.comments
FROM tablea, tableb, tablec, tabled
WHERE tabela.position IN (x,y,z)
AND tablea.id_tabled = tabled.id_tabled
AND tabled.id_tablec = tablec.id_tablec
AND tablec.comment LIKE '%good%'
AND tablec.id_tableb = tableb.id_tableb

I have chosen to put DISTINCT in as a default, as sometimes, when x,y,z are all chosen, it can return multiple rows that are the same.

This seems to be going quite fast, however my ever faithful friend Test::Perl::Critic says that method advanced_search has a high complexity score, so it definitely needs a bit of refactoring.

However, it seems to be going fairly well. So, refactoring can wait until next week.

By for now