sql server - Perl Module for Simplifying SQL Query -

sql server - Perl Module for Simplifying SQL Query -

over years, i've noticed few issues sql queries in perl scripts:

they're hard read most developers aren't sql.

let's have next tables:

pet pet_type color ======== ========== ============ pet_id pet_type color_id name description description owner pet_type color_id

and, want find reddish dogs, i'll have produce next sql query.

find pet_id, name, owner pet, pet_type, color color_id.description = 'red' , pet_type.description = 'dog' , pet.color_id = color.color_id , pet.pet_type = pet_type.pet_type

i perl module simplify producing querying. allow me create database connection, predefine how tables linked. 1 time done, developer have simplified query.

i imagine interface this:

# # create new database connection # $db = some:module->new(\%options); # # describe how these tables relate each other. # should possible reuse info # multiple queries. maybe create subroutine # or company wide perl module # developer. # $db->link_tables ( pet => "color_id", color => "color_id ); $db->link_tables ( pet => "pet_type", pet_type => "pet_type", ); # # you've created link database, # , you've describe how tables relate # each other, let's create query. i'm not 100% # sure of interface, it'll # this. there might multiple `query` # statements. # $query = $db->query ( pet_type => "description = dog", color => "description = red" ); # # now, execute query # $db->execute_query; while (%row = $db->fetch) { "dog's name $row{pet.name}"; "the owner $row{pet.owner}"; } # # let's find cats named wiskers. # we've described links, # doesn't have redone. # $query = $db->query ( pet_type => "description = cat", pet => "name = wiskers" );

i know description incomplete, , no matter how complete module, there has emergency escape hatch can raw sql query. however, thought concentrate programme on want (i want reddish dogs or cats named whiskers) instead of various fields , spending of where clause on describing links between tables. create easier programme , easier understand program.

if there no such module this, i'll produce one. however, before spending few weeks on endeavor, want create sure i'm not duplicating work.

i think looking orm, , there plenty of those. while not describing, comes pretty close in functional terms. have @ class::dbi , dbix::class, or google perl orms.

sql-server perl dbi