Menu
▾
▴
DESCRIPTION
DESCRIPTION
This module was created to execute basic SQL commands in a database engine, where it proposes the most common and basic operations with low parameter complexity.
The module allow switch between database engine without recoding. It makes the necessary adjusments in SQL statement according the database engine in use, reducing the complexity in the applications's code.
The current version has built-in support for the databases: MySQL, MariaDB, Postgres and SQLite3.
However, the module does not convert the fields format in the databases, where each one has its restrictions and rules.
By definition, we assume that switching databases will use supported fields between the engines.
By default, evey invoked command will be effectively executed in database. To disable the execution use "make_only" option.
All parameters set in the Constructor (method new) are defined as Global Parameters, and some options can be enable/disable by the Methods, for more information see "Global Options".
All executed commands can saved in flat files for Debugging and/or Control and/or Recovery purposes, for more infomration se "Global Options".
All implemented methods deliver standards return code, where can be:
* rc < 0, Parameters and/or options have syntax errors;
* rc = 0, SQL command successful executed;
* rc = 1, SQL command execute with errors;
* rc = 2, SQL command successful executed without match.
The message details and return code must be extracted by:
-
For Constructor (new) Method: Use "SQL::SimpleOps::err" and "SQL::SimpleOps::errstr values".
-
For Other Methods: Use "getRC" and "getMessage" modules (recommended).
By default the module send the messages on "STDERR", unless that requested by the application (see "message_log" option in "Global Options").
Constructor Initialization
The constructor (new method) will load and validate all options and initialize the controls.
The execution of the constructor is a mandatory requirement for the others methods. You must load the constructor and use the object address created by it to run the methods.
my $mymod = SQL::SimpleOps->new ( ... );
...
my $rc = $mymod->[methods] ( ... );
...
However, in this document have references and examples using the format "SQL::SimpleOps->[method]" just for simple illustration, but this format is not supported in encoding. (see EXAMPLES)
The controls can be initialized by the Options Arguments and/or "Configuration File". The module will load the arguments and at end will apply the "Configuration File" changes, where the "Configuration File" is the highest priority value over the arguments.
Configuration File
The config file is not mandatory.
It is the resource used to load the options to initialize the constructor and was created to minize the risk of code changes. You can specify all options and provide changes without recoding.
The format of config file must be JSON and respect the struct below.
{
"db":"database_name",
"schema":"schema-name",
"driver":"mysql | mariadb | postgres | sqlite",
"login":"login",
"password":"password",
"server":"hostname",
"commit":"0 | 1",
"connect":"0 | 1",
"interface":"dbi",
"interface_options":{
"options1":"value",
"options2":"value",
"...":"..." <- no comma at end is allowed
},
"message_log":"value",
"message_syslog_facility":"string",
"message_syslog_service":"string",
"port":"tcp_port",
"quote":"apostrophe|quote",
"sql_save":"0 | 1",
"sql_save_ignore":"0 | 1",
"sql_save_name":"string",
"sql_save_dir":"string",
"sql_save_bydate":"0 | 1",
"tables":{
"table1_alias":{
"cols":{
"col1_alias_name":"col1_real_name",
"...":"..." <- no comma at end is allowed
},
"name":"real_table_name_on_database"
},
"...":"..." <- no comma at end is allowed
} <- no comma at end is allowed
}
NOTE: The example listed above is a simple illustration, where you do not need to parameterize all the options. you must need use only the mandatory options for your application.
REMEMBER: The JSON format does not allow comma at the end of last field in braces.
Supported Arguments Format
The "table" and "fields" can be configured using the following formats:
select( table => "table_name", ... );
select( table => [ "table_name1", ... ], ... );
my $table = "table_name";
select( table => $table, ... );
my $table = [ "table_name1", ... ];
select( table => $table, ... );
my @table = [ "table_name", ... ];
select( table => @table, ... );
my @table = ( "table_name", ... );
select( table => \@table, ... );
The "where", "order_by" and "group_by" can be configured using the following formats:
select( where => [ where1, ... ], ... );
my $where = [ where1, ... ];
select( where => $where, ... );
my @where = [ where1, ... ];
select( where => @where, ... );
my @where = ( where1, ... );
select( where => \@where, ... );
Loading Data into Applications ("buffer", "buffer_options", "buffer_hashkey" and "buffer_arrayref" options)
Exists two different process to manipulate the application data with the module. The first update data into the database ("Insert", "Delete" and "Update") and the second get data from the database ("Select" and "SelectCursor").
The first process the data will be sent by arguments, where the module will convert to SQL command and run it.
The second process the data will be load into variable sent by arguments, where the module will store the data.
Example1: Putting the data into the database.
SQL::SimpleOps->Insert
(
table => "my_table",
fields => # (assign by hash)
{
id => 1,
name => "my_name",
...
}
);
or
SQL::SimpleOps->Insert
(
table => "my_table",
fields => [ id, name ], # (array of fields)
values => [ [ 1, "my_name"] ], # (array into array)
);
NOTE: The return code must be "SQL_SIMPLE_RC_OK" for successful or "SQL_SIMPLE_RC_ERROR" if any errors.
Example2: Getting the data into the database using the "buffer" option.
SQL::SimpleOps->Select
(
table => "my_table",
where => [ id => 1 ],
...
buffer => hash_ref | array_ref | scalar_ref | callback_ref,
);
NOTE: The return code must be "SQL_SIMPLE_RC_OK" for successful or "SQL_SIMPLE_RC_ERROR" if any errors.
We have four different types of return values for the "buffer" option, can be:
Extracting Single Row
The Single Extraction consists in queries that return only one row. The best type of "buffer" is the "hash_ref", where each column will be the index key in the hash.
Example3:
SQL::SimpleOps->Select( buffer => \%my_buffer, ... );
foreach my $id(sort(keys(%my_buffer)))
{
print "id=".$id." -- value=".$my_buffer{$id}."\n";
}
BEWARE: Only the last row will be returned for queries with multiple rows.
Extracting Multiple Rows
The Multiple Extraction consists in queries that return tow or more rows. The best type of "buffer" is the "array_ref", where each line will be a hash_ref for each extracted row.
Example4:
SQL::SimpleOps->Select( buffer => \@my_buffer, ... );
foreach my $ref(@my_buffer)
{
foreach my $id(sort(keys(%{$my_buffer{$ref}})))
{
print "id=".$id." -- value=".$my_buffer{$ref}->{$id}."\n";
}
}
Extracting Multiple Rows as Single List
The Single List consists in queries that return an unique field and multiple rows. The best typ of "buffer" is the "array_ref" with "buffer_arrayref" swtiched to OFF ("buffer_arrayref=0").
Example5:
SQL::SimpleOps->Select( buffer => \@my_buffer, buffer_arrayref => 0, ... );
print "values=".join(", ",@my_buffer)."\n";
Extracting Multiple Rows using field as index
The Multiple Extraction consists in queries that return tow or more rows. The best type of "buffer" is the "hash_ref" with "buffer_hashkey" option, where each line will be indexed by the value of field key.
Example6:
SQL::SimpleOps->Select( buffer => \%my_buffer, buffer_hashkey => "id", fields => [ "id", ... ], ... );
foreach my $id(sort(keys(%my_buffer)))
{
foreach my $field(sort(keys(%{$my_buffer{$id}})))
{
print "id=".$id." -- ".$field."=".$my_buffer{$id}{$field}."\n";
}
}
NOTE: The "buffer_hashkey" value must be in the command field list.
NOTE: The "buffer_hashkey" will not be found in the field list results.
REMEMBER: Using 'hash buffers' the ordering is compromised. Hash tables are not ordered and the c<order_by> option does not make sense in this case.</order_by>
Example7:
SQL::SimpleOps->Select( buffer => \%my_buffer, buffer_hashkey => ["key1","key2"], fields => [ "key1"," key2", ... ], ... );
foreach my $key1(sort(keys(%my_buffer)))
{
foreach my $key2(sort(keys(%{$my_buffer{$key1})))
{
foreach my $field(sort(keys(%{$my_buffer{$key1}{$key2}})))
{
...
}
}
}
NOTE: The "buffer_hashkey" value must be in the command field list.
NOTE: The "buffer_hashkey" will not be found in the field list results.
Extracting Specfic Column in Single Row
The Single Extraction for specific Column in queries that return only one row/column. The best type of "buffer" is the "scalar_ref", where the column will be stored.
Example8:
SQL::SimpleOps->Select( buffer => \$my_scalar, ... );
print "id=".$my_scalar."\n":
BEWARE: Only the last value will be returned for queries with multiple rows.
Extracting as HASH ordered
The HASH's extraction keys is not ordered and you can not scan in the real sequence in the table. Some cases you need scan in the sequence one HASH by the keys. The option "buffer_hashindex" is an arraydef with the lists of hashkeys, where the 0 (zero) until 'max' is the correct sequence in the table. This option can be used with "buffer=hashref" to index the real scan sequence of the table.
Example9:
SQL::SimpleOps->Select( buffer => \%my_hash, buffer_hashkey => 'id', buffer_hashindex => \@my_index, ... );
foreach my $key1(@my_index)
{
print "key: ".$key.", buffer: ".$my_hash{$key},"\n";
}
REMEMBER: You can use this option if your "buffer" is a "hashref", otherwise, use the buffer's option "arrayref".
Complex Extractions
The most case you need provide a lot number of process before select the rows. The "callback" will be executed for each extracted rows and depending on the return code, the lines will buffered or ignored (no stored).
Example10:
my @any_info;
SQL::SimpleOps->Select
(
table => table1,
fields => [ id, register, counter ],
where => [ id => 1 ],
buffer => \&subrot_1,
buffer_options => \@any_info,
);
...
sub subrot_1()
{
my $ref = shift; # my hash of extracted row from table1
my $any = shift; # my 'buffer_options' option.
...
return SQL::SimpleOps->Select # returing the code of call
(
table => table2,
where => [ id => $ref->{register} ],
buffer => \&subrot_2,
buffer_options = $any,
);
}
...
sub subrot_2()
{
my $ref = shift; # my hash of extracted row from table2
my $any = shift; # my 'buffer_options' options created on table1
...
return 1 if (...any condition..); # rc != 0 will ignore the row
...
push(@{any},$ref); # putting the data into @any_info array
return 0;
};
NOTE: This example is not a real use case, it is simple sample for references.
SubQueries
The subquery is a select into select, available to create on merged ou cross reference list of data.
Example11:
my @any_info;
my $mymod = new SQL::SimplesOps(...);
$mymod->SimpleOps->Select
(
table => table1,
where => [ id => $mymod->SelectSubQuery( table => "table2", field => "my_tb2_id", where => [ ... ] ) ],
buffer => @buffer,
);
...
The following SQL Command will be executed:
SELECT * FROM table1 WHERE id IN (SELECT my_tb2_id FROM table2 WHERE [ .... ] );
The reults will be written into the arrayref (for this example).
NOTE: This example is not a real use case, it is simple sample for references.
Aliases Table
The can be used to abstract the real names on the tables and the columns, reducing the recoding.
The Aliases Table is not mandatory. See EXAMPLES
When mapped the Aliases Column these aliases will be the keys in the "hashref buffers"
Why should I? Some places disallow tables using generic names (out of the norms), in this case, you can use aliases to abstract the real name in your the databases.
REMEMBER: Do not use reserved words, like function names (etc), as aliases. This keywords are reserved and is not allowed to assign as aliases (SQL will be fail).
Format:
my %contents =
(
aliases_table_0 => # table without list of fields
{
name => realname_table_0,
},
aliases_table_1 => # table with list of fields
{
name => realname_table_1,
cols =>
{
aliases_col_1_table_1 => realname_col_1_table_1,
aliases_col_2_table_1 => realname_col_2_table_1,
...
}
},
...
);
Example1:
my %contents =
(
users =>
{
name => "table_for_users",
cols =>
{
_id => 'fld_id',
_name => 'fld_name',
_desc => 'fld_description',
},
},
);
SQL::SimpleOps->Select ( table => "users", fields => [ "_id", "_name", "_desc" ], ... );
or
SQL::SimpleOps->Select ( table => "users", ... ); # without fields option
Results:
SELECT fld_id _id, fld_name _name, fld_description _desc FROM table_for_users;
or
SELECT * FROM table_for_users;
NOTE: The '*' option will not assign alias names in the SQL Command.
Escape or Backslash
The "escape" operand (or "backslash") is the format to force the methods to ignore the translating data as arguments. The data with escape information will passed to SQL command without check or decode.
To escape the data, you must use as prefix the backslash caracter ('\').
REMEMBER: The backslash is a control character in the Perl. To use you must insert double backslash.
You can "escape" the following options:
SQL::SimpleOps->Select ( ... , fields => [ '\\my_by_pass_as_fields', ... ], ... );
SQL::SimpleOps->Insert ( ... , conflict => { my_col => '\\my_bypass_as_value', }, ... );
SQL::SimpleOps->Update ( ... , fields => { my_col => '\\my_bypass_as_value', }, ... );
SQL::SimpleOps->getWhere ( ... , where => [ '\\my_bypass_as_col_name' => '\\my_bypass_as_value', ... ], ... );
NOTE: The escape is valid in the 'where' clause for "Select", "SelectCursor", "Delete" and "Update" command.
Example1: "Select" or "SelectCursor"
SQL::SimpleOps->Select ( ... fields => [ '\\concat(col1,col2,col3)' ], table => tb1, ... );
Result:
SELECT ... concat(col1,col2,col3) FROM tb1 ... ;
Example2: "Insert"
SQL::SimpleOps->Insret ( ... table => tb1, conflict => { last_update => '\\concat(substr(col1_date,1,4),"-01-01")' ], ... );
Result:
INSERT INTO tb1 ... ON DUPLICATE last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
(mySQL / MariaDB engine)
or
INSERT INTO tb1 ... ON CONFLICT last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
(SQLite / Postgres engine)
Example3: "Update"
SQL::SimpleOps->Update ( ... table => tb1, fields => { last_update => '\\concat(substr(col1_date,1,4),"-01-01")' ], ... );
Result:
UPDATE tb1 ... SET last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
Example4: "getWhere"
SQL::SimpleOps->getWhere ( ... where => [ col1 => '\\my_expression', '\\my_col_expression' => my_value ], ... );
Result:
... col1 = my_expression AND my_col_expression = 'my_value' ...
NOTE: The 'my_value' will have quote as applied and 'no quotes' will be in escaped expressions.
ENDED
