SQL::Routine SQL::Routine is a Perl module to specify all database tasks with SQL routines.
Download

SQL::Routine Ranking & Summary

SQL::Routine Tags

database tasks Perl module database tasks SQL routines

SQL::Routine Description

SQL::Routine is a Perl module to specify all database tasks with SQL routines. SQL::Routine is a Perl module to specify all database tasks with SQL routines.SYNOPSISThis executable code example shows how to define some simple database tasks with SQL::Routine; it only shows a tiny fraction of what the module is capable of, since more advanced features are not shown for brevity. use SQL::Routine; eval { # Create a model/container in which all SQL details are to be stored. # The two boolean options being set true here permit all the subsequent code to be as concise, # easy to read, and most SQL-string-like as possible, at the cost of being slower to execute. my $model = SQL::Routine->new_container(); $model->auto_set_node_ids( 1 ); $model->may_match_surrogate_node_ids( 1 ); # This defines 4 scalar/column/field data types (1 number, 2 char strings, 1 enumerated value type) # and 2 row/table data types; the former are atomic and the latter are composite. # The former can describe individual columns of a base table (table) or viewed table (view), # while the latter can describe an entire table or view. # Any of these can describe a 'domain' schema object or a stored procedure's variable's data type. # See also the 'person' and 'person_with_parents' table+view defs further below; these data types help describe them. $model->build_child_node_trees( , , , , , ], ], , , , , , , ], ], , , , , , , ], ], ] ); # This defines the blueprint of a database catalog that contains a single schema and a single virtual user which owns the schema. my $catalog_bp = $model->build_child_node_tree( 'catalog', 'Gene Database', , , ] ); my $schema = $catalog_bp->find_child_node_by_surrogate_id( 'Gene Schema' ); # This defines a base table (table) schema object that lives in the aforementioned database catalog. # It contains 6 columns, including a not-null primary key (having a trivial sequence generator to give it # default values), another not-null field, a surrogate key, and 2 self-referencing foreign keys. # Each row represents a single 'person', for each storing up to 2 unique identifiers, name, sex, and the parents' unique ids. my $tb_person = $schema->build_child_node_tree( 'table', { 'si_name' => 'person', 'row_data_type' => 'person', }, , , , ], ], , ], ], , ], ], , ], ], ] ); # This defines a viewed table (view) schema object that lives in the aforementioned database catalog. # It left-outer-joins the 'person' table to itself twice and returns 2 columns from each constituent, for 6 total. # Each row gives the unique id and name each for 3 people, a given person and that person's 2 parents. my $vw_pwp = $schema->build_child_node_tree( 'view', { 'si_name' => 'person_with_parents', 'view_type' => 'JOINED', 'row_data_type' => 'person_with_parents', }, , } ( 'person_id', 'name', 'father_id', 'mother_id', ), ], ], } ('self') ), ( map { , } ( 'person_id', 'name', ), ], ], } ( 'father', 'mother', ) ), , }, ], , }, ], , }, ], , }, ], , }, ], , }, ], , ], ], , ], ], ] ); # This defines the blueprint of an application that has a single virtual connection descriptor to the above database. my $application_bp = $model->build_child_node_tree( 'application', 'Gene App', , ] ); # This defines another scalar data type, which is used by some routines that follow below. my $sdt_login_auth = $model->build_child_node( 'scalar_data_type', { 'si_name' => 'login_auth', 'base_type' => 'STR_CHAR', 'max_chars' => 20, 'char_enc' => 'UTF8', } ); # This defines an application-side routine/function that connects to the 'Gene Database', fetches all # the records from the 'person_with_parents' view, disconnects the database, and returns the fetched records. # It takes run-time arguments for a user login name and password that are used when connecting. my $rt_fetch_pwp = $application_bp->build_child_node_tree( 'routine', { 'si_name' => 'fetch_pwp', 'routine_type' => 'FUNCTION', 'return_cont_type' => 'RW_ARY', 'return_row_data_type' => 'person_with_parents', }, , , , , , , ], ], , , ], ], , , , ], ], , ], ], , ], ], ] ); # This defines an application-side routine/procedure that inserts a set of records, given in an argument, # into the 'person' table. It takes an already opened db connection handle to operate through as a # 'context' argument (which would represent the invocant if this routine was wrapped in an object-oriented interface). my $rt_add_people = $application_bp->build_child_node_tree( 'routine', { 'si_name' => 'add_people', 'routine_type' => 'PROCEDURE', }, , , , ], ], , , ], ], ] ); # This defines an application-side routine/function that fetches one record # from the 'person' table which matches its argument. my $rt_get_person = $application_bp->build_child_node_tree( 'routine', { 'si_name' => 'get_person', 'routine_type' => 'FUNCTION', 'return_cont_type' => 'ROW', 'return_row_data_type' => 'person', }, , , , , ], ], , , ], ], ], ], , , , ], ], , ], ], ] ); # This defines 6 database engine descriptors and 2 database bridge descriptors that we may be using. # These details can help external code determine such things as what string-SQL flavors should be # generated from the model, as well as which database features can be used natively or have to be emulated. # The 'si_name' has no meaning to code and is for users; the other attribute values should have meaning to said external code. $model->build_child_node_trees( , , , , , , , , , ] ); # This defines one concrete instance each of the database catalog and an application using it. # This concrete database instance includes two concrete user definitions, one that can owns # the schema and one that can only edit data. The concrete application instance includes # a concrete connection descriptor going to this concrete database instance. # Note that 'user' descriptions are only stored in a SQL::Routine model when that model is being used to create # database catalogs and/or create or modify database users; otherwise 'user' should not be kept for security sake. $model->build_child_node_trees( , , ], ], , ], ], ] ); # This defines another concrete instance each of the database catalog and an application using it. $model->build_child_node_trees( , , ], ], , ], ], ] ); # This defines a third concrete instance each of the database catalog and an application using it. $model->build_child_node_trees( , , ], ], ] ); # This line will run some correctness tests on the model that were not done # when the model was being populated for execution speed efficiency. $model->assert_deferrable_constraints(); # This line will dump the contents of the model in pretty-printed XML format. # It can be helpful when debugging your programs that use SQL::Routine. print $model->get_all_properties_as_xml_str( 1 ); }; $@ and print error_to_string($@); # SQL::Routine throws object exceptions when it encounters bad input; this function # will convert those into human readable text for display by the try/catch block. sub error_to_string { my ($message) = @_; if (ref $message and UNIVERSAL::isa( $message, 'Locale::KeyedText::Message' )) { my $translator = Locale::KeyedText->new_translator( , ); my $user_text = $translator->translate_message( $message ); return q{internal error: can't find user text for a message: } . $message->as_string() . ' ' . $translator->as_string(); if !$user_text; return $user_text; } return $message; # if this isn't the right kind of object }Note that one key feature of SQL::Routine is that all of a model's pieces are linked by references rather than by name as in SQL itself. For example, the name of the 'person' table is only stored once internally; if, after executing all of the above code, you were to run "$tb_person->set_attribute( 'si_name', 'The Huddled Masses' );", then all of the other parts of the model that referred to the table would not break, and an XML dump would show that all the references now say 'The Huddled Masses'.For some more (older) examples of SQL::Routine in use, see its test suite code.Requirements:· Perl Requirements: · Perl

SQL::Routine Related Software