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

NAME

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

VERSION

This document describes version 0.05.

DESCRIPTION

This module contain functions which provide commandline interfaces to search methods and Class::DBI's &delete,&update,and &create methods. Also contains some basic functions to enable and view DBI::Profile logs.

Shell functions

printcol(): prints the columns of the current table
set_db_log_level($num): sets the log level of DBI handle;
clear_dbi_log(): clears the DBI log
cdbi_insert(@search_terms): parses the input via __PACKAGE__->_delim->{insert} into values for each column

The columns which map to the parsed values is defined via the accessor &insertcol. Ie if @insertcol = ('car','year') and the insert delimiter is ',,' and your input is 'chevy,,57' then &cdbi_insert will create a record with car='chevy' and year='57'

note: records with multi-line data can't be inserted this way

Search-based functions

The following are functions which perform queries with either &Class::DBI::search* or &Class::DBI::AbstractSearch::search_where.

If Class::DBI::AbstractSearch isn't installed or the splitter matches &_default_splitter then the given query is performed with the Class::DBI search_* method specified by &_default_search. Otherwise &search_where is used. See &get_select for the decision logic.

Both methods split on white-space breaking up user's input to chunks containing a column name and value pair. This chunk is in the form:

$column$splitter$operator$column_value

$splitter:  

	Class::DBI::search* -  is &_default_splitter accessor
	search_where - is the &_splitter accessor

$operator: 

	Class::DBI::search* - doesn't support any
	search_where - can be any of '>,>=,<,<=,=,!=', valid only for &search_where   

	if no operator is given then the default operator specified in
	&_abstract_opts is used

For example the arguments 'hero=superman weakness=kryptonite' translates to: (hero=>'superman',weakness=>'kryptonite') being passed to the search function. Assuming the default for _splitter('=') and _abstract_opts under the generic table (TABLE) and generic columns (COLUMN1 COLUMN2 COLUMN3 ...):

cdbi_select(@search_terms): prints results of query
`cdbi_select tags=cool name=hack`  : select * from TABLE where tags ~ 'perl'
and name ~ 'hack'

`-c=1-3 cdbi_select id=>20 year=<=1980` : select COLUMN1,COLUMN2,COLUMN3 from TABLE
where id > 20 and year <= 1980

note: if you don't understand -c look at section 'Option C' below

cdbi_delete(@search_terms): deletes results found via query
`cdbi_delete name=[aA]cme` : delete from TABLE where name ~
'[aA]cme'
cdbi_update(@search_terms): prints results found via the query to file, user makes changes and fields updated automatically

Note: By default, the safe_update flag option is set. This prevents updating if a record containing the display delimiter is found. It is important that the delimiter used to separate fields in the file doesn't exist in the table's data. Otherwise incorrect parsing and updating of records will result.

Since this is slow for many records you may want to verify all the records only once with &verify_no_delim. To turn the flag off, you have to change it inside &_default_data. Normally, you do this via your own config but there currently isn't a way to define values in a hash in a config file.

replace(@search_terms,$operation): takes result of query and evaluates perl operation on each value of the results treating each value as $_
`-c=1-4 replace description=cool s/cool/lame/g`

This example gets the results of the SQL statement 
" select COLUMN1,COLUMN2,COLUMN3,COLUMN4 from TABLE where
description ~ 'cool' " and then performs 's/cool/lame/g' on the
specified columns of each result row, treating each
value as $_.

note: Since $operation is distinguished from @search_terms by a white space, $operation can't contain any white space.

verify_no_delim(@search_terms): verifies that no display delimiter are in any of the queried records, provides an alternative to having to run &cdbi_update safely

The next three functions take Class::DBI row objects as input. The most common way to pass these on is by first executing cdbi_select with the parse_mode= menu ('-m cdbi_select tags=goofy') and then executing one of the following functions with numbers specifying which objects you choose from the numbered menu. See handyshell.pl in the samples directory for more about menu parsing mode.

display_obj(@row_objects): prints chosen rows
delete_obj(@row_objects): deletes chosen rows
update_obj(@row_objects): updates chosen rows via file as with &cdbi_update

Global data

Here's a brief description of this module's global data:

_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	
_default_splitter: same as above except used for Class::DBI::search* searches
_default_search: Class::DBI search function called when _default_splitter appears in search functions
	possibilites are search,search_like and search_regex
_abstract_opts: optional parameters passed to &Class::DBI:AbstractSearch::search_where
_alias_print: hash mapping aliases to print functions
_print_mode: current print alias used by _alias_print to determine current print mode,
	used by &cdbi_select
_delim: hash with the following keys:
	display: delimits column values in table row printed out by &print2darr, also
		delimits column values when editing records in file with &cdbi_update
	insert: delimits values when using &cdbi_insert

Print Modes

Currently there are two main print modes (functions): text table and normal Normal is on by default and simply delimits each column with ',,' and row with "\n" by default. Text table mode prints results as an aligned text table. I don't recommend this mode for a large query as it has loop through the results beforehand to determine proper table formatting. To change modes from the commandline you could specify '-P=t' as an option. To add another printing mode add the alias to the accessor &_alias_print.

Handy Shortcuts

Input Aliasing

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

Option C

This option quickly specifes which columns to view by column numbers. Columns are numbered in their order in a table. To view a numbered list of the current table's columns type 'printcol'. For a table with columns (id,name,author,book,year):

-c=1-3  : specifies columns id,name,author
-c=1,4  : specifies columns id,book  
-c=1-2,5 : specifies columns id,name,year

Writing Class::DBI Libraries

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

When writing a Class::DBI library:

1. Define 'CDBI::BDBI' as dependent module in your &_default_data.
2. Refer to Fry::Lib::CDBI::BDBI 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 table-specific libraries are welcome as I'll soon be releasing libraries that generate outlines from a specific table format.

Suggested Modules

Three functions are dependent on external modules. Since their requirements are wrapped in an eval, the functions fail safely.

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

See Also

Fry::Shell, Class::DBI

TODO

 -defining relations between tables, should use Class::DBI::Loader to load tables 
 -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.