Perl x Open Food Facts Hackathon: Paris, France - May 24-25 Learn more

 / 
Fry-Lib-CDBI-Basic-0.15
River stage zero No dependents
/ Fry::Lib::CDBI::Basic

NAME

Fry::Lib::CDBI::Basic - A basic library of Class::DBI functions for use with Fry::Shell.

VERSION

This document describes version 0.14.

DESCRIPTION

This module contain wrappers around Class::DBI methods for common database functions such as creating,deleting,inserting and updating records. There are also some basic functions to enable and view DBI::Profile logs.

COMMANDS


            

              
              
Search
        *search_abstract
        *cdbi_search
        *cdbi_search_like
        *cdbi_search_regex
Search based
        cdbi_delete
        *cdbi_update
        *verify_no_delim
        *replace
        cdbi_find_or_create
Menu based
        cdbi_delete_obj
        cdbi_update_obj
        verify_no_delim_obj
Debugging via DBI::Profile
        set_dbi_log_level       
        print_dbi_log
        clear_dbi_log
Other
        cdbi_create
        cdbi_multi_insert
        display_table_list
        print_columns
Note: Any command with a * is affected by the variable action_columns

Search Commands

These commands search and give back Class::DBI objects.


            

              
              
cdbi_search(@search_term): wrapper around &Class::DBI::search
cdbi_search_like(@search_term): wrapper around &Class::DBI::search_like
cdbi_search_regex(@search_term): does regular expression searches (ie REGEXP for Mysql or ~ for Postgresql)
search_abstract(@search_term): wrapper around Class::DBI::AbstractSearch::search_where,
        by default does regular expression searches, change this via
        $cls->Var('abstract_opts')->{cmp}

These commands have a common input format that supports searching a column by a value. A column constraint is in the regular expression form:


            

              
              
$column$splitter$operator?$column_value

The above form will be represented by $search_term in any argument descriptions of functions. $splitter is controlled by the splitter variable. $operator is only used by &search_abstract and has the possible values:


            

              
              
> :  greater than
>= : greater than or equal to
< : less than
<= : less than or equal to
= : equal to
!= : not equal to

Like Class::DBI's search method, multiple column constraints are anded together. To specify multiple column constraints, separate them with white space.

Examples:

Using &search, the input 'hero=superman weakness=kryptonite' translates to (hero=>'superman',weakness=>'kryptonite') being passed to &search and the sql where part being: WHERE hero = 'superman' AND weakness = 'kryptonite'

Using &search_abstract, the input 'id=>41 module=Class::DBI' translates to the sql where part being: WHERE id >= 41 AND module ~ 'Class::DBI'.

Note: To set the columns and tables for a query look at OPTIONS under Fry::Lib::CDBI::Load.

Search based Commands

These commands get the results of a search and then do something with it. The variable cdbi_search contains the search command called for any of these functions. This variable is found in other CDBI libraries and is also an option for easily changing search types.


            

              
              
cdbi_delete(@search_term): deletes result objects
cdbi_update(@search_term): result objects printed to a file, user changes file and objects updated
        This function contains two flags, safe_update and only_modified. By
        default, both flags are set. The safe_update flag calls &verify_no_delim_obj to
        verify none of the results contain a display delimiter. If any are found, the command exits
        early. For many records, this may be slow, in which case run
        &verify_no_delim on all the objects once and then turn off the flag.
        The only_modified flag modifies the command to only call &update on
        objects that have been changed. With the flag off, &update would be called
        on all objects. If you don't mind this and want to speed up the update,
        then you can turn off the flag.
replace(@search_term,$perl_operation): evaluates a perl operation on each column value of the results,
treating each value as $_
                 
        For example if one result row had the following values:
        '4','many','amazing','some bold punk' 
        and you did the perl operation 's/o/a/g', the result row would be
        converted to:
        '4','many','amazing','same bald punk' 
        note: Since $operation is distinguished from @search_terms by a
        white space, $operation can't contain any white space.
verify_no_delim(@search_term): Verifies that result objects do not contain the display
        delimiter.  Since this delimiter can be used to separate fields in a
        file, having them in the data could result in incorrect parsing. The
        delimiter is specified by the variable field_delimiter
cdbi_find_or_create(@search_term): If no result objects found then one is created

            

          

            

              
              
cdbi_delete_obj(@cdbi): same functionality as cdbi_delete
cdbi_update_obj(@cdbi): same functionality as cdbi_update
verify_no_delim_obj(@cdbi): same functionality as verify_no_delim

The three menu commands take Class::DBI row objects as input. The only way to currently enter objects as input is via the menu option. To use these commands, first execute a search command with the -m option


            

              
              
`-m search_abstract tags=goofy`

Then execute one of the menu based commands with numbers specifying which objects you choose from the numbered menu.


            

              
              
`cdbi_delete_obj 1-4,8-10`

Why not just use the corresponding search based command? You'd use a menu based command when you want to pick only certain results and perform actions on them.

Debugging via DBI::Profile.

There are three commands that wrap around DBI::Profile that manage benchmark data useful in debugging DBI statements, set_dbi_log_level, print_dbi_log and clear_dbi_log. These commands respectively set the log level (which is between -15 and 15), print the current log, and clear the log. To enable debugging, you must first set a log level via &set_dbi_log_level. See DBI::Profile for more details.

Other Commands


            

              
              
cdbi_create(($value$delim)+): wrapper around &Class::DBI::create. &cdbi_create uses
        &aliasInsert to parse the input into values for the table's columns. The
        columns which map to the parsed values are defined via the variable insert_columns.
        Ie if @insert_columns = ('car','year') and the insert delimiter is ',,' and your
        input is 'chevy,,57' then &create will create a record with car='chevy' and
        year='57'
        note: records with multi-line data can't be inserted this way 
cdbi_multi_insert($file): same input format as &cdbi_create,reads several lines from
        file and inserts them as new records
display_table_list(): lists tables in the database
print_columns(): prints the current table's columns

Library Variables


            

              
              
editor: sets the editor used by &cdbi_update
splitter: separates column from its value in arguments of search-based functions and used
        for &Class::DBI::AbstractSearch::search_where searches      
abstract_opts: optional parameters passed to &Class::DBI:AbstractSearch::search_where
delim: hash with the following keys:
        display: delimits column values when editing records in file with &cdbi_update
        insert: delimits values when using &cdbi_insert
        tag: delimits values used in CDBI::Tags library.
insert_columns(\@): implicit order of columns for

Miscellaneous

Input Aliasing

If there are queries you do often then you can alias them to an even shorter command via &aliasInput. The default &aliasInput aliases 'a' to returning all rows of a table and replaces anything matching /c\d/ with the corresponding column.

Changing Output Format

Via the subhook viewsub, it's possible to choose your own subroutine to format your output. By default all search results are displayed using &View::CLI::objAoH. If you want an aligned output similar to most database shells, use &printTextTable ie (-v=tt s id=48).

Writing Class::DBI Libraries

Make sure you've read Fry::Shell's 'Writing Libraries' section.

When writing a Class::DBI library:


            

              
              
1. Define 'CDBI::Load' as dependent module in your &_default_data.
2. Refer to Fry::Lib::CDBI::Load for a list of core Class::DBI global data
to use in your functions.

I encourage not only wrapper libraries around Class::DBI::* modules but any DBI modules. Even libraries that use tables of a specific schema are welcome (see Fry::Lib::CDBI::Tags).

Suggested Modules

Three functions are dependent on external modules. Since their require statements are wrapped in an eval, the functions fail safely if not found.


            

              
              
&cdbi_update: File::Temp
&search_abstract: Class::DBI::AbstractSearch
&print_text_table: Text::Reform

See Also

Fry::Shell, Class::DBI

TO DO


            

              
              
-port old TESTS!
-defining relations between tables with has_*
-provide direct SQL queries
-support shell-like parsing of quotes to allow spaces in queries
-specify sorting and limit of queries
-embed sql or database functions in queries
-create an easily-parsable syntax for piecing chunks into 'or' and 'and' parts
to be passed to Class::DBI::AbstractSearch

Thanks

I give a shot out to Kwan for encouraging me to check out Postgresql and Perl when my ideas of a database shell were simply bash and a text file.

A shot out also to Jeff Bisbee for pointing me to Class::DBI when I was pretty naive in the perl world.

AUTHOR

Me. Gabriel that is. I welcome feedback and bug reports to cldwalker AT chwhat DOT com . If you like using perl,linux,vim and databases to make your life easier (not lazier ;) check out my website at www.chwhat.com.

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.