CODEWITHSUNDEEP
mysqleclipseant
Paul Bellora
Paul Bellora

Reputation: 55223

Removing dependence on dynamic SQL through Ant scripting

I've been given responsibility over a number of stored procedures, which take the form:

create procedure getFoos()
begin
    set @v_sql :=                '';
    set @v_sql := concat(@v_sql, 'select distinct ', getFooFields('f', 'b'), ' ');
    set @v_sql := concat(@v_sql, 'from Foos f ');
    set @v_sql := concat(@v_sql, '  left join Bars b on f.barID= b.barID');
    set @v_sql := concat(@v_sql, 'where f.someDate is null ');
    set @v_sql := concat(@v_sql, '  and b.someID in (1, 2, 3) ');
    set @v_sql := concat(@v_sql, '  and b.someBool = true ');
    set @v_sql := concat(@v_sql, 'order by f.name ');

    prepare s1 from @v_sql;
    execute s1;
    deallocate prepare s1;  
end;

As you can see, dynamic SQL is being used so that a function getFooFields can be inlined. That function is used simply to build up the string of selected fields:

create function getFooFields(
    i_aliasForFoo varchar(32),
    i_aliasForBar varchar(32)
) returns text
reads sql data
begin
    declare v_fields text default '';

    set v_fields := concat(v_fields, i_aliasForFoo, '.fooID as fooId', ', ');
    set v_fields := concat(v_fields, i_aliasForFoo, '.someDate as someDate ', ', ');
    set v_fields := concat(v_fields, i_aliasForFoo, '.name as name', ', ');
    -- additional Foo fields
    set v_fields := concat(v_fields, i_aliasForBar, '.someID as someID ', ', ');
    set v_fields := concat(v_fields, i_aliasForBar, '.someBool as someBool ', ', ');
    -- additional Bar fields

    return v_fields;
end;

The reason for this seems to be better maintainability - when the Foos or Bars tables are altered, changes only have to be made in the one function instead of many procedures. However this is coming at the cost of using dynamic SQL, as well as an extra function which builds up the same string every single time it's called. I'm wondering if there's a way to keep the maintainability while moving the work to before runtime.

As it happens, we are managing our SQL in Eclipse and using Ant to massage these files before running them on the database prior to every deployment. Is there a way we could script Ant to do the work of getFooFields instead? I imagine something like the following:

select distinct <%foofields%> from Foos
...

Where Ant would replace <%foofields%> with a string similar to what getFooFields is building. But of course this is further complicated by the fact that getFooFields takes parameters so I'm not sure how that would work.

Is this idea completely misguided? I hardly have any experience with Ant so I can't tell. Alternatively, what are other ways I could remove these procedures' dependence on dynamic SQL while keeping the them maintainable?

Upvotes: 1

Views: 111

Answers (1)

Rob Allen
Rob Allen

Reputation: 17749

If getFooFields is most often used to select all fields from table foo then you are better off using select * from foo than the combination of dynamic SQL and function.

If it is a subset of the fields in foo that occur often as a group, then you might be better served by creating that subset as a view and querying against the view.

If getFooFields is taking a list of fields from another application to select from foo then you have to examine whether or not that makes sense. How many fields are in foo? How often do you need only a small number of those fields? Are you pulling a large enough number of records for this type of "optimization" to be meaningful? Most likely, you aren't and would be better served just pulling the common fields or all of them.

Upvotes: 1

Related Questions