Webman-framework

Lightweight, Component-based, and Database-oriented Web Application Framework

About | Overview | Documentation

 

Documentation > Modules and APIs > DB_Utilities

DB_Utilities

 

Description:

The core module to simplify database operations based on CPAN's DBI module.

 

Dependencies:

CPAN's Modules:

  • DBI (Composition)
Webman-framework's Core Modules:
  • GMM_CGI (Composition)

 

1. Instantiation and Basic Parameter Setting

The $db_conn is an instance of database connection created using DBI module.

my $dbu = new DB_Utilities;
$dbu->set_DBI_Conn($db_conn);



 
2. DB Connection Configuration File

An alternative to create $db_conn instance via configuration file.

my $db_conn = $dbu->make_DBI_Conn("dbi_connection.conf");

The dbi_connection.conf configuration file is simply a text based file contains database connection information as follows:

db_host = localhost
db_driver = mysql
db_name = ???
db_user_name = ???
db_password = ???


 
3. HTML Tag Characters Escape

Set $num to '0' to escape basic HTML's tag characters ('&', '<', '>', '"') for database item fields values acquired using $dbu->get_Item(...) and $dbu->get_Items(...) member functions (see section 6). These basic HTML's tag characters set will be converted to ("&amp;", "&lt;", "&gt;", "&quot;") respectively.

$dbu->set_Escape_HTML_Tag($num);



 
4. Database Table Selection

Table name that will be affected by the database operations made via DB_Utilities moudule member functions.

$dbu->set_Table($table_name);



 
5. Basic Insert/Update/Delete Operations

Functions to simplified insert/update/delete operations. The $exec_opt can be set to '0' to only prepare the sql statement. If left unset the default value is 1.


$dbu->insert_Row($fields, $values, $exec_opt);
$dbu->update_Item($fields, $values, $key_fields, $key_values, $exec_opt);
$dbu->delete_Item($key_fields, $key_values, $exec_opt);


The arguments $fields, $values, $key_fields, and $key_values are string to represent database item fields and values separated by single space characters. Below is an example of update function call and its equivalent SQL statement:

$dbu->set_Table("registration");
$dbu->update_Item("programme course", "Computer\\ Science SCK", "session semester", "2011/2012 1");

SQL: update registration set programme='Computer Science', course='SCK' where session='2011/2012' and semester='1'

 
6. Database Items Retrieval

The function $dbu->get_Item(...) will return single row/field database item. Use $dbu->get_Items(...) function for multi rows/fields database items retrieval.

my $field_value = $dbu->get_Item($field_name, $key_fields, $key_values);

my @ahr = $dbu->get_Items($fields, $key_fields, $key_values, $order_fields, $distinct_mode);

The argument $field_name is a string of single field name while $fields, $key_fields, and $key_values are fields names and values separated by single space characters. The argument $order_fields is part of complete SQL statement that normally placed just after the "order by" sentence. The $distinct_mode can be set to '1' or '0' (default is 0). The arguments $key_fields, $key_values, $order_fields, and $distinct_mode are optional.

The @ahr list variable is an array of hash reference. The code line: $ahr[0]->{course}; is an example to get the value of item field course at the first row of the returned list.


 
7. Other Item's Information Queries

my $max_val = $dbu->get_MAX_Item($field_name, $key_fields, $key_values);
my $min_val = $dbu->get_MIN_Item($field_name, $key_fields, $key_values);

my $found = $dbu->find_Item($key_fields, $key_values);
my $total = $dbu->count_Item($key_fields, $key_values, $distinct_mode);



 
8. SQL String Information

Return the exact SQL string generated when/after functions explained in sections 5, 6, and 7 are called.

my $sql_str = $dbu->get_SQL;



 
9. SQL Keys Strings

The $key_fields and $key_values arguments passed to member functions describe in sections 5, 6, and 7 are used to generate simple SQL conditional keys strings coresspond to intended database operations. If there are more than single field-value pairs represent by these two arguments then they are combined using "and" SQL conditional statement (see example in section 5).

Users can provide more complex keys strings inside the generated SQL command by using $dbu->set_Keys_Str($key_str) function. The $key_str is any possible valid SQL conditional statements not including the SQL's "where" clause.

$dbu->set_Keys_Str($keys_str);
### next is to call the database operations functions
...
...
$dbu->set_Keys_Str(undef); 

The line $dbu->set_Keys_Str(undef) must be called after each call to $dbu->set_Keys_Str($keys_str) to reset the key string value or it will be bring forward and effect the next different functions call.

 
10. Unique number/key generator functions

The argument $field_name is table's primary key type of integer. If ignored, by default $min_num is set to 100000 and $max_num is set to 999999

my $rndnum = $dbu->get_Unique_Random_Num($field_name, $min_num, $max_num);


The argument $field_name is table's primary key type of varchar($length) with suggested minimum length is 6, if ignored $length by default is set to 6. The returned result $rnd36base is a string represent 32 based number constructed by the combination of 32 characters (0,...,9,A,...,Z).
my $rnd36base = $dbu->get_Unique_Random_36Base($field_name, $length);


The argument $field_name is table's primary key type of binary($length) with suggested minimum length is 6, if ignored $length by default is set to 6. The returned result $rnd62base is a string represent 62 based number constructed by the combination of 62 characters (0,...,9,A,...,Z,a,...,z).
my $rnd62base = $dbu->get_Unique_Random_62Base($field_name, $length); 



 
11. Miscellaneous functions on Database Table and Field

Get the list of all tables from current connected database.

my @table_list = $dbu->get_Table_List;


Check the existence of current selected table.
my $exist = $dbu->table_Exist;


Get detail structure/schema of current selected table.
my @ahr = $dbu->get_Table_Structure;


Check if field $field_name exist in current selected table.
my $var_ = $dbu->field_Exist($field_name);


Drop current selected table.
$dbu->drop_Table;



 
12. Item Access Control

Functions to enable database item access control based on user's authentication via framework's "Convention over Configurations" (CoC) implementation.

$dbu->set_DB_Item_Auth_Info($login_name, \@groups, "webman_???_db_item_auth", "webman_???_user_group");
$dbu->set_Error_Back_Link($error_back_link_content_);


Check if database item access control is applied on particular field with name $field_name and value $field_value for operation type $operation. The $operation can be "INSERT", "UPDATE", or "DELETE".
my %field_nv = ($field_name => $field_value);               
my $db_auth = $dbu->check_DB_Item_Auth($operation, \%field_nv);

if (!$db_auth) {
    $db_item_access_error = $dbu->get_Auth_Error_Message;
    
    ### display and handle item access control error
    ...
    ...
    ...
}


Check and handle database item access control after database operation (delete) is executed.
$dbu->delete_Item("key_1 ... key_n", "val_1 ... val_n");   

$db_item_access_error = $dbu->get_Auth_Error_Message;

if ($db_item_access_error ne "") {
    ### display and handle item access control error
    ...
    ...
    ...
}