Architecture of Constructing Complex SQL

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

Dear Brethren,

I have a MySQL database which I am searching using MySQL's fulltext indexing. The search results are inserted into a table so they can be saved for later. Right now, I am constructing queries manually like so:

my $mquery =  "INSERT INTO Search_Results ( search_id, page_id )
         SELECT ?, P.id
         FROM Pages P, Documents D, Dirs DI
         WHERE MATCH ( P.pgdata ) AGAINST ( ? IN BOOLEAN MODE )
              AND P.doc_id = D.id
              AND D.dir_id = DI.id ";
[download]

For the basic case, that works fine, but over time, the code to build and munge the SQL has gotten more complex, as more features are added to the interface. Users now do searches via author and date range in addition to text, they include and exclude certain subsets of documents, and so on. All of this leads to a very complex (and buggy) form handling script which must know about a plethora of form options and construct a query dynamically.

For example, here's what the query code looks like for a more complex case, when the user might be searching within an existing result set.

my $mquery =  "INSERT INTO Search_Results ( search_id, page_id )
                  SELECT ?, P.id
                  FROM Pages P, Documents D, Dirs DI ";

if( $ref_sid ) {
    # constrain search to a previous result set.
    $mquery .= ", Search_Results SR ";
}

$mquery .=     "WHERE MATCH ( P.pgdata ) AGAINST ( ? IN BOOLEAN MODE )
                    AND P.doc_id = D.id
                   AND D.dir_id = DI.id ";

if( $ref_sid ) {
    $mquery .= "AND P.id = SR.page_id AND SR.search_id = ? ";
    push @query_args, $ref_sid;
}
[download]

I got myself into this hole and now I want to climb out by rewriting the search stuff over the coming holiday. I have a few ideas for how I can handle this better.

Use Template Toolkit to generate the SQL.
I've used this strategy elsewhere, though not for an application as complex as this one. At the end of the day, I don't know if my SQL templates will be any easier to maintain as the Perl code now.

Use Class::DBI, passing in a hash of constraints to find. This seems like it might be rather slow, and it would be disastrous if I have to retrieve all the records and then insert them into the Search_Results table. Fulltext searching is very expensive and the server is quite underpowered. With more than half a million entries and many searches that return tens of thousands of rows, this might not be a valid solution.

Write a separate query for every possible search situation. There are currently eleven basic permutations of options, so I would write eleven separate queries. This has the disadvantage of duplicating a lot of SQL, making it difficult to maintain if the table structure is adjusted or new metadata fields are added.

I'm sure the problem of dynamically generating SQL is a common one. Any experience you can share, even if not directly related, could be helpful. Thanks.

friedo

Comment on Architecture of Constructing Complex SQL Select or Download Code

Replies are listed 'Best First'.
Re: Architecture of Constructing Complex SQL by zby (Vicar) on Dec 22, 2005 at 08:11 UTC
You can have a look at SQL::Builder and SQL::Abstract modules.	[reply]
Re: Architecture of Constructing Complex SQL by beachbum (Beadle) on Dec 22, 2005 at 20:08 UTC
I've written a bit of dynamic SQL many different ways... I generally group the statements by the FROM clause. In this case, the format of your queries look pretty similar. I might try Something Like(tm) this: `my ($sr_from, $sr_where); if( $ref_sid ) { $sr_from = ", Search_Results SR "; $sr_where = "AND P.id = SR.page_id AND SR.search_id = ? "; push @query_args, $ref_sid; } my $mquery =<<"SQL" INSERT INTO Search_Results ( search_id, page_id ) SELECT ?, P.id FROM Pages P, Documents D, Dirs DI $sr_from WHERE MATCH ( P.pgdata ) AGAINST ( ? IN BOOLEAN MODE ) AND P.doc_id = D.id AND D.dir_id = DI.id $sr_where SQL` [download] I'm unsure if the bind parameter '?' is buying you anything in this query as each statement prepare() would have to see what is being SELECTed. Either way, be careful not let the interpolated variables be changed through the program interface. All said, I'm thinking that SQL::Builder does look very interesting.	[reply] [d/l]