SQL::Simple - SQL Simple Operations
This module was created for my personal projects and now, after standardization for CPAN Project, i released it for general use.
This module is not a statement parser, contrariwise, it consists of an SQL command builder and executor.
This documment do not will provide concepts about SQL Commands and Relational Database Services. We understand that the developer has adequate knowledge of these parameters and commands.
Before any implementation, we recommend seeing the section DESCRIPTION for considerations and restrictions.
I wish it to be useful in the development of your applications.
use SQL::Simple; new $my_module = SQL::Simple->new ( # load by config file configfile => filename, # load options by configfile # connections process interface => interface_name, # (default: 'dbi') interface_options => { ... # (see interface options) }, driver => mysql | mariadb | postgres | sqlite, # (no defaults) db => dnsname, # (no defaults) server => dbservername, # (no defaults) port => tcpport_number, # (optional) login => login_name, # (optional) password => login_password, # (optional) # tables definitions # (no defaults) tables => { table1_alias => { name => real_table_name_on_database, cols => { col1_alias_name => col1_real_name, }, }, table2_alias => { ... }, ... }, # generic options quote => character, # (default: apostrophe) connect => 0 | 1, # (default: 1 ) commit => 0 | 1, # (default: 0 ) # message log options # (default: SQL_SIMPLE_LOG_STD) message_log => SQL_SIMPLE_LOG_OFF | SQL_SIMPLE_LOG_SYS + SQL_SIMPLE_LOG_STD, message_syslog_facility => string, # (default: 'local0') message_syslog_service => string, # (default: 'SQL-Simple') # sql_save options # (default: disabled) sql_save => SQL_SIMPLE_CMD_OFF | SQL_SIMPLE_CMD_ON | SQL_SIMPLE_CMD_ALL, sql_save_bydate => 0 | 1, # (default: 0) sql_save_dir => fullpath, # (default: (linux) '/var/spool/sql') # (default: (windows) 'c:/windows/temp') sql_save_name => string, # (default: 'sql') sql_save_ignore => 0 | 1, # (default: 1) );
Open $rc = SQL::Simple->Open(); Wait $rc = SQL::Simple->Wait( count => number, # (default: 1 occurs) interval => number, # (default: 5 secs) ); Close $rc = SQL::Simple->Close();
Commit $rc = SQL::Simple->Commit(); Delete $rc = SQL::Simple->Delete ( # table alias name table => table_alias_name, # where clause where => [ condition1, ... ], # (see below: WHERE) # generic options force => 0 | 1, # (default: 0) notfound => 0 | 1 # (default: 0) commit => 0 | 1, # (default: global value) message_log => 0 | 1, # (default: global value) sql_save => 0 | 1, # (default: global value) make_only => 0 | 1, # (default: 0) flush => 0 | 1, # (default: 1) ); Insert $rc = SQL::Simple->Insert ( # table alias name table => table_alias_name # fields to insert fields => # (no defaults) { col1_alias => value, col2_alias => value, ... }, or fields => [ col_1,col_2,... ], # (multiple fields) values => # (multiple lines) [ [ val_01,val_02,... ], [ val_11,val_12,... ], ... ] or fields => [ col_1 ], # (one field only) values => [ val_11,val_21,... ], # (multiple lines) # fields for update if already exists conflict => # (no defaults) { col1_alias => value, col2_alias => value, ... }, conflict_key => col_name, # (no defaults) # generic options commit => 0 | 1, # (default: global value) message_log => 0 | 1, # (default: global value) sql_save => 0 | 1, # (default: global value) quote => string, # (default: global value) make_only => 0 | 1, # (default: 0) flush => 0 | 1, # (default: 1) ); Select $rc = SQL::Simple->Select ( # list of tables table => table_alias_name, or table => [ table1_alias, table2_alias, ... ], # list of fields fields => [ col1_alias, col2_alias, ... ], # where clause where => [ condition1, ... ], # (see below: WHERE) # group by options group_by => col_alias, or group_by => [ col1_alias, col2_alias, ... ], # order by options order_by => undef, # (disable the order process) or order_by => col_alias, or order_by => [ col1_alias => SQL_SIMPLE_ORDER_ASC | USERSOPTS_ORDER_DESC, col2_alias => ... ... ], # return buffer # (no defaults) buffer => hash_ref | array_ref | scalar_ref | callback_ref, buffer_options => hash_ref | array_ref | scalar_ref | value_ref, # generic options limit => value_max_lines, # (default: ulimited lines) notfound => 0 | 1, # (default: 0) message_log => 0 | 1, # (default: global value) quote => string, # (default: global value) sql_save => 0 | 1, # (default: global value) make_only => 0 | 1, # (default: 0) flush => 0 | 1, # (default: 1) ); SelectCursor $rc = SQL::Simple->SelectCursor ( # list of tables table => table_alias_name, or table => [ table1_alias, table2_alias, ... ], # see Command Select for Fields, Where and More ... # cursor options cursor => current_cursor_key, # (no defaults) cursor_key => col_alias, # (no defaults) cursor_info = array_ref | hash_ref | scalar_ref>, # (no defaults) cursor_command => # (default: TOP) SQL_SIMPLE_CURSOR_RELOAD | SQL_SIMPLE_CURSOR_BACK | SQL_SIMPLE_CURSOR_NEXT | SQL_SIMPLE_CURSOS_TOP | SQL_SIMPLE_CURSOR_LAST, ... limit => # (default: no defaults) ); Update $rc = SQL::Simple->Update ( table => table_alias_name, fields => # (no defaults) { col1_alias => value, col2_alias => value, ... }, # where clause where => [ condition1, ... ], # (see below: WHERE) # options commit => 0 | 1, # (default: global value) force => 0 | 1, # (default: 0) notfound => 0 | 1, # (default: 0) commit => 0 | 1, # (default: global value) message_log => 0 | 1, # (default: global value) sql_save => 0 | 1, # (default: global value) quote => string, # (default: global value) make_only => 0 | 1, # (default: 0) flush => 0 | 1, # (default: 1) );
Call $rc = SQL::Simple->Call ( # sql command command => sql_command_string, # (no defaults) # return values # (no defaults) buffer => hash_ref | array_ref | scalar_ref | callback_ref, buffer_options => any_ref_type, # options commit => 0 | 1, # (default: global value) message_log => 0 | 1, # (default: global value) sql_save => 0 | 1, # (default: global value) make_only => 0 | 1, # (default: 0) flush => 0 | 1, # (default: 1) );
getAliasCols $realname_cols = SQL::Simple->getAliasCols(alias_table,alias_cols); getAliasTable $realname_table = SQL::Simple->getAliasCols(alias_table); getDBH $dbh = SQL::Simple->getDBH(); getLastCursor $hash_ref = SQL::Simple->getLastCursor(); getLastSave $last_saved_logfile = SQL::Simple->getLastSave(); getLastSQL $last_sql_command = SQL::Simple->getLastSQL(); getMessage $message = SQL::Simple->getMessage(); getRC $rc = SQL::Simple->getRC(); getRows $rows = SQL::Simple->getRows(); getWhere $rc = SQL::Simple->getWhere ( # list of tables table => table, or table => [ table1, table2, ... ], # where clause where => where_clause, # (se: Where Clause) # return buffer buffer => scalar_ref, );
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 of the fields between the databases, where each one has its restrictions and rules. We understand that the table in use have same format and data type.
By default, evey command invoked will be effectively executed in database, however this process can be suppressed if requested and the SQL command can retrieve (if neeeded).
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".
The module can save the executed SQL Commands in text 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::Simple::err and SQL::Simple::errstr values.
SQL::Simple::err
SQL::Simple::errstr values
For Other Methods: Use getRC and getMessage modules (recommended).
getRC
getMessage
By default the module send the messages on STDERR, unless that requested by the application (see message_log option in "Global Options").
STDERR
message_log
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::Simple->new ( ... ); ... my $rc = $mymod->[methods] ( ... ); ...
In this document have references and examples using the format "SQL::Simple->[method]" just for simple illustration, but this format is not supported in encoding. (see EXAMPLES)
The controls can 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.
The config file is the resource used to load the options to initialize the constructor. It 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: You must specify only the mandatory options. The list above it is a sample of all options fields.
REMEMBER: The JSON format does not allow comma at the end of last field in braces.
Exists two different process to manipulate the application data with the module. The first put data into the database and the second get data from the database.
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::Simple->Insert ( table => "my_table", fields => # (assign by hash) { id => 1, name => "my_name", ... } ); or SQL::Simple->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.
SQL_SIMPLE_RC_OK
SQL_SIMPLE_RC_ERROR
Example2 Getting the data into the database using the buffer option.
buffer
SQL::Simple->Select ( table => "my_table", where => [ id => 1 ], ... buffer => hash_ref | array_ref | scalar_ref | callback_ref, );
We have four different types of return values for the buffer option, can be:
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.
hash_ref
Example3:
SQL::Simple->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.
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.
array_ref
Example4:
SQL::Simple->Select( buffer => \@my_buffer, ... ); my $line=0; foreach my $ref(@my_buffer) { foreach my $id(sort(keys(%{$my_buffer{$ref}}))) { print $line." -- id=".$id." -- value=".$my_buffer{$ref}->{$id}."\n"; } $line++; }
Specfic Column
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.
scalar_ref
Example5:
SQL::Simple->Select( buffer => \$my_scalar, ... ); print "id=".$my_scalar."\n":
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).
callback
Example6:
my @any_info; SQL::Simple->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::Simple->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.
The can be used to abstract the real names on the tables and the columns, reducing the recoding.
The Aliases Table is not mandatory, use it you necessary.
When mapped the Aliases Column thes aliases will be the keys in the hashref buffers
hashref buffers
Why should I? Some companies 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).
Defaults: no Defaults.
Format:
Simple Struct:
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, ... } }, ... );
Examples:
my %contents = ( users => { name => "table_for_users", cols => { _id => 'fld_id', _name => 'fld_name', _desc => 'fld_description', }, }, ); SQL::Simple->Select ( table => "users", fields => [ "_id", "_name", "_desc" ], ... ); or SQL::Simple->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: This last will not assign the alias names in the SQL Command.
The escape operand (or backslash) is the format used 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::Simple->Select ( ... , fields => [ '\\my_by_pass_as_fields', ... ], ... ); SQL::Simple->Insert ( ... , conflict => { my_col => '\\my_bypass_as_value', }, ... ); SQL::Simple->Update ( ... , fields => { my_col => '\\my_bypass_as_value', }, ... ); SQL::Simple->getWhere ( ... , where => [ '\\my_bypass_as_col_name' => '\\my_bypass_as_value', ... ], ... );
NOTE: The escape is valid in the 'where' clause of Select, SelectCursor, Delete and Update command.
Example1: Select or SelectCursor
SQL::Simple->Select ( ... fields => [ '\\concat(col1,col2,col3)' ], table => tb1, ... );
Result:
SELECT ... concat(col1,col2,col3) FROM tb1 ... ;
Example2: Insert
SQL::Simple->Insret ( ... table => tb1, conflict => { last_update => '\\concat(substr(col1_date,1,4),"-01-01")' ], ... );
INSERT INTO tb1 ... ON DUPLICATE last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
Example3: Update
SQL::Simple->Update ( ... table => tb1, fields => { last_update => '\\concat(substr(col1_date,1,4),"-01-01")' ], ... );
UPDATE tb1 ... SET last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
Example4: getWhere
SQL::Simple->getWhere ( ... where => [ col1 => '\\my_expression', '\\my_col_expression' => my_value ], ... );
... WHERE 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.
We belive that the most important situations will was created, however we understand that some format will not be supported.
The Where Clause was be create based array_ref, no others options exists. We must remember that some engines explore the sequence in the Where Clause to establish de best way to extract data (index and match condition), using this point of view the best way is this type of struct.
If you any item (or sub-item) specifies hash_ref, the process is aborted with SQL_SIMPLE_RC_SYNTAX as the return code.
SQL_SIMPLE_RC_SYNTAX
Operators: The follow operators was been builtin:
Operator Description undef "is null" = "equal" condition (default) ! "not equal" != "not equal" <> "not equal" > "greater than" < "less than" >= "greater or equal than" <= "less or equal than" !> "less or equal than" !< "greater or equal than" %% "like %[value]%" ^% "like %[value]" %^ "like [value]%" ^^ "like [value]" # no '%' will be added !%% "not like %[value]%" !^% "not like %[value]" !%^ "not like [value]%" !^^ "not like [value]" # no '%' will be added \ "escape value" # no quote will be applied
SQL::Simple->Select ( where => [ fld000 => undef, # ... fld000 IS NULL fld010 => 1, # ... fld010 = '1' fld020 => [ '>', 1 ], # ... fld020 > '1' fld021 => [ '!', undef ], # ... fld021 NOT NULL fld030 => [ 1, 2 ], # ... fld030 IN ('1','2') fld031 => [ '!', 1, 2 ], # ... fld031 NOT IN ('1','2') fld040 => [ 1, '..', 3 ], # ... fld040 BETWEEN ('1','3') fld041 => [ '!', 1, '..', 3 ], # ... fld041 NOT BETWEEN ('1','3') [ fld050 => 1, fld051 => 2 ], # ... ( fld050 = '1' and fld051 = '2' ) [ fld060 => 1, 'or', fld061 => 2 ], # ... ( fld060 = '1' or fld061 = '2' ) [ fld070 => 1, 'and', fld071 => 2 ], # ... ( fld070 = '1' and fld071 = '2' ) fld080 => 1, 'or', [ fld081 => 2, fld082 => 3 ], # ... fld080 = '1' or ( fld081 = '2' and fld082 = '3' ) fld090 => 1, [ fld091 => 2, 'or', fld092 => 3 ], # ... fld090 = '1' and ( fld091 = '2' or fld092 = '3' ) fld100 => [ '%%', 'abc' ], # ... fld100 LIKE '%abc%' fld110 => [ '^%', 'abc' ], # ... fld110 LIKE '%abc' fld120 => [ '%^', 'abc' ], # ... fld120 LIKE 'abc%' fld121 => [ '^^', 'ab%de' ], # ... fld121 LIKE 'ab%de' (you must add the '%' fld130 => '\\concat(fld130,"xxxx")', # ... fld130 = concat(fld130,"xxxx") (without quote) ], );
IMPORTANT: Do not use aliases column name on the right side. The translation for this side does not apply.
BEWARE: The operators (if used) must be the first field on the array_ref.
Restrictions and Limitations
The functions like SUBSTR, LEFT, RIGHT, and others are not supported. See "EXTERNAL REFERENCES"
SUBSTR
LEFT
RIGHT
Following methods can be exported:
SQL::-Simple->new ( # your global options );
The method load the interface driver and your interface options. It is highly recommended to see DBI to understand about the interface_options, see "Global Options".
interface_options
By default, the constructor establish the first connect on database engine. If you do not need open the database yet, you can disable by the Constructor using the connect option, disabling the connection process at initialization of module. However, the first connect will automatic create before the first SQL execution.
connect
REMEMBER: Before implementing the SQL Open Command, make sure the login and password are valid. Perform a iteractive tests from command line, connecting to the database using the login and password, performing the basic functions that the profile needs to do.
BEWARE: If some wrong happens the module will not be die by croack or die operations. You must interpreter the return code and abort it.
croack
die
All parameters set in the Constructor (method new) are defined as Global Parameters and part of then can be temporarily modified for a specific SQL Command (if needed).
commit: Enable/disable commit after updates. Do not to be confused with the commit available in the options for interface driver (see DBI), it has an other escope.
Defaults: The default value is disabled.
db: The name of database name. The value depend of type of interface and driver option. see DBI module.
Defaults: no defaults
driver: Sets the name of database engine. see DBI module.
interface: Sets the interface module to use. The current version support only interface=dbi.
interface=dbi
Defaults: The defaul value is dbi.
dbi
interface_options: See options for DBI module.
Defaults: The default values for interface=dbi are: RaiseError=0 and PrintError=0.
RaiseError=0
PrintError=0
login: Sets the user/profile login for authenticated connection. For security reasons and best practices, the module expects that all connections will be authenticated. Unauthenticated will not be accepted.
NOTE: For driver=sqlite this item is ignored.
driver=sqlite
message_log: Sets message log mode, can be:
SQL_SIMPLE_LOG_OFF: No message will be shown. Use getMessage required to get the messages; SQL_SIMPLE_LOG_SYS: Write messages on System Syslog Services; SQL_SIMPLE_LOG_STD: Write messages on STDERR. SQL_SIMPLE_LOG_ALL: Write messages on Syslog/STDERR both.
Defaults: The default value is SQL_SIMPLE_LOG_STD.
SQL_SIMPLE_LOG_STD
NOTE: You can enable Syslog and STDERR simultanely. Use the SQL_SIMPLE_LOG_ALL.
SQL_SIMPLE_LOG_ALL
message_syslog_service: Sets an identifier string on the System Syslog Messages. see: Sys::Syslg
Defaults: The default value is message_syslog_service=SQL-Simple
message_syslog_service=SQL-Simple
message_syslog_facility: Sets the Syslog Facility for the messages on the System Syslog Messages, must be: local0 to local7. see: Sys::Syslg
local0
local7
Defaults: The default value is local0.
password: The password/profile for authentication process.
port: This option consists the TCP Port to use for connection. The option is not mandatory because it is common to use the default database port, however some installations may modify the default port. The following defaul pors are known:
Defaults: The default value depend of database engine:
DB2: 50000/tcp MySQL: 3306/tcp Oracle: 1521/tcp Postgres: 5432/tcp SyBase: 5000/tcp SQLite: not required
quote: Sets the quote caracter on string commands (see setQuote method), must be: apostrophe or quote.
setQuote
Defaults: apostrophe.
server: Sets the hostname or ip address of hosted database server.
Defaults: no defaults.
sql_save: Sets the SQL log file mode, can be:
SQL_SIMPLE_CMD_OFF: No log file will be written; SQL_SIMPLE_CMD_ON: Write only update commands; SQL_SIMPLE_CMD_ALL: Write all commands.
BEWARE: You can use this feature as "Recover Database" if your engine does not support this process. However, it is highly recommended that you use the Database Native Resource for this purpose. Examples: Archive Logs, Backup Mode, and Others.
Defaults: The default value is SQL_SIMPLE_CMD_OFF.
SQL_SIMPLE_CMD_OFF
sql_save_bydate: Sets enable/disable writing log files into distinct folders. The folder will be create as:
[sql_save_dir]/YYYY/YYYYmm/YYYYmmdd/[logfile]
YYYY/YYYYmm/YYYYmmdd
Defaults: The option is disabled. See sql_save_dir option.
sql_save_dir
sql_save_dir: Sets the folder for writes log file process.
Defaults: (unix/linux) /var/spool/sql/ or (windows) c:\windows\temp
sql_save_name: Sets the filename string for writes log file process.
Defaults: The default value is sql.
sql
sql_save_ignore: Sets enable/disable option to abort if there are errors wrtten to the Log File.
Defaults: The default value is disabled. If errors abort will be requested.
tables: Sets the list of aliases rules for the tables and fields. See "Aliases Table".
The follow options can be temporarily modified:
commit: Enable/Disable the commit after a specific update command.
message_log: Enable/Disable the written on System Syslog Services.
quote: Change the quote character.
sql_save: Enable/Disable the written for SQL Log Files.
NOTE: This Options can be modified with each SQL Command execution. These changes will be valid only in this process, where the global values will be restored after each execution.
Example1: The database "my_db" using "interface_options". see DBI
SQL::Simple->new ( db => "my_db", ... interface_options => { RaiserError => 1, # (default: 0) PrintError => 1, # (default: 0) AutoCommit => 1, InactiveDestroy => 1, AutoInactiveDestroy => 1, }, );
NOTE: Do not use this example as template for your implementations, you must see DBI do understand about each options.
Example2: The database "my_db" writing on System Syslog Service. see Sys::Syslog
SQL::Simple->new ( driver => "mariadb", db => "my_db", ... message_log => SQL_SIMPLE_LOG_SYS, # (default is STDERR) message_syslog_facility => "local7", # (default is "local0") message_syslog_service => "my_service", # (default is "SQL-Simple") ... );
Notifications will be sent to the system messages file (in Linux the file /var/log/messages).
We recommended to use the "System Syslog Services" as standard in your applications and create rules to write in separeted log files. For more information see the documentation related to the "Syslog Service" on your System.
Example3: The database "my_db" writing SQL Log Files.
SQL::Simple->new ( driver => "mysql", db => "my_db", ... sql_save => SQL_SIMPLE_CMD_ALL, # (default is OFF) sql_save_name => "my_cmds", # (default is "sql") sql_save_dir => "/var/tmp/sql", # (default is "/var/spool/sql") ... );
The SQL Command will be written as flat file and your the filesystem name will be:
/var/tmp/sql/[sql_save_name].[database_name].[today].[pid].[counter]
Where:
sql_save_name: Consists in the sql_save_name option value.
sql_save_name
database_name: Consists in the my_db database value.
my_db
today: Current day formated as YYYYmmdd (year+month+day).
YYYYmmdd
pid: Current Pid (system process identifier) in execution.
counter: Indexer for each SQL Command executed by the Current Pid. A Pid that runs multiple commands will have the multiple SQL Log Files.
Example4: The database "my_db" writing SQL Log Files splitig by date.
SQL::Simple->new ( driver => "mysql", db => "my_db", ... sql_save => SQL_SIMPLE_CMD_ALL, # (default is OFF) sql_save_name => "my_cmds", # (default is "sql") sql_save_dir => "/var/tmp/sql", # (default is "/var/spool/sql") sql_save_bydate => 1, # (default is OFF) ... );
The SQL Command will be written as flat file separeted into distinct folders by date identifier.
The folder will be create as:
/var/tmp/sql/YYYY/YYYYmm/YYYYmmdd/[sql_save_name].[database_name].[today].[pid].[counter]
Example5: The database "my_db" forcing commit for each update command.
SQL::Simple->new ( driver => "mysql", db => "my_db", ... commit => 1, # (default is OFF) ... );
The commit option for each update command can cause degradation in the process or even the database engine. Use this option wisely.
commit
When can i use it? The commit option (or command) can be used at the end of a long process of updates, and that at a certain point you need to create relational consistency to reduce rollback process.
In this case, you can either execute the Commit SQL Command or execute the last update command of the cycle using commit option.
SQL::Simple->Commit(); # (simple commit command) or SQL::Simple->Update ( table => "my_table", ... commit => 1, # (default is OFF) );
Format: see DBI
SQL::Simple->new ( driver => "mysql" | "mariadb", interface_options => { mysql_auto_reconnect => 0 | 1, ... }, ... );
Example1: The database as "my_info", loging "my_user", password "my_auth":
SQL::Simple->new ( driver => "mysql", db => "my_info", login => "my_user", password => "my_auth", interface_options => { mysql_auto_reconnect => 0 }, );
SQL::Simple->new ( driver => "sqlite", interface_options => { # no special options }, ... );
Example1: The database as "my_info" and fileset as "my_info.db":
SQL::Simple->new ( driver => "sqlite", db => "my_info", );
Example2: The database as "my_db" and fileset as "my_file.db":
SQL::Simple->new ( driver => "sqlite", db => "my_db" dbfile => "my_file.db" # (or: dbfile => "my_file") interface_options => { RaiseError => 1, # (the default is 0) PrintError => 1, # (the default is 0) }, );
SQL::Simple->new ( driver => "pg", db => "my_db", schema => "my_schema", interface_options => { # no special options }, );
Example1: Using default database, public schema, as "my_user" and "my_auth" password:
SQL::Simple->new ( driver => "pg", login => "my_user", password => "my_auth", );
Example2: Using default database, "my_schema" schema, as "my_user" and "my_auth" password:
SQL::Simple->new ( driver => "pg", schema => "my_schema", login => "my_user", password => "my_auth", );
Example3: Using "my_database" database, public schema, as "my_user" and "my_auth" password:
SQL::Simple->new ( driver => "pg", db => "my_database", login => "my_user", password => "my_auth", );
Example4: Using "my_database" database, "my_schema" schema, as "my_user" and "my_auth" password:
SQL::Simple->new ( driver => "pg", db => "my_database", schema => "my_schema", login => "my_user", password => "my_auth", );
This method removes the selected rows from a table based the conditions in the where clause.
where
For security rasons the command does not perform removals if where clause is omitted or empty, in this case, you must add the force option to do.
force
SQL::Simple->Delete( ( table => "my_table", where => [ my_conditions ], ... );
NOTE: If no match found the command will return the condition code SQL_SIMPLE_RC_EMPTY, however, using the notfound option will force to SQL_SIMPLE_RC_OK if no matchs.
SQL_SIMPLE_RC_EMPTY
notfound
Example1: Forcing remove all rows.
SQL::Simple->Delete ( table => "my_table", force => 1, );
Example2: Forcing no matchs removals completed successful
SQL::Simple->Delete ( table => "my_table", where => [ id => my_id, ], notfound => 1, );
NOTE: This command force the return code to SQL_SIMPLE_RC_OK if ther is no match, where without it the notfound option must be SQL_SIMPLE_RC_EMPTY.
This method insert the selected rows from a table.
SQL::Simple->Insert # (insert by fields based hash) ( table => "my_table", fields => { col_1 => value, col_2 => value, }, conflict => { col_1 => value, col_2 => value, }, conflict_key => col_name ... ); or SQL::Simple-> # (insert be fields/values array) ( table => "my_table", fields => [ col_1, col_2, ... ], values => [ val_1, val_2, ... ], ... ); or SQL::Simple->Insert # (insert by fields on array) ( table => "my_table", fields => [ col_1, col_2, col_3,, ... ], values => [ [ val_1_1, val_1_2, val_1_3, ... ], [ val_2_1, val_2_2, val_2_3, ... ], ... ], ... ); or SQL::Simple->Insert # (insert unique field) ( table => "my_table", fields => [ col_1 ], values => [ val_1_1, val_2_1, ... ], ... );
NOTE: The addition options are valid for all formats.
Example1: Inserting rows without conflict option.
conflict
SQL::Simple->Insert ( table => "my_table", fields => { key => 1, # (it is a key) value_1 => value, # (it is a value) }, );
NOTE: If the key already the return code is not zero. See DBI.
not zero
Example2: Inserting rows with conflict option. If the key already only updates will be done.
SQL::Simple->Insert ( table => "my_table", fields => { key => 1, # (it is mais key) value_1 => value, # (it is a value) }, conflict => { update => CURRENT_TIMESTAMP, # (make systime update if already) } );
NOTE: The 'conflict_key' option is not required for MySQL and MariaDB databases.
Example3: Inserting rows with conflict and conflict_key options. If the key already only updates will be done.
conflict_key
SQL::Simple->Insert ( table => "my_table", fields => { key => 1, # (it is mais key) value_1 => value, # (it is a value) }, conflict => { update => CURRENT_TIMESTAMP, # (make systime update if already) } conflict_key => "key", );
NOTE: The 'conflict_key' option is required for Postgres and SQLite databases.
Example4: Inserting classic format
SQL::Simple->Insert ( table => "my_table", fields => [ "key", "value_1" ], values => [ 1, value ], );
This method extract the selected rows from a tables based the conditions in the where clause.
SQL::Simple->Select ( table => my table list, # mandatory fields => { my list of fields }, # optional for single table, defaul: all columns # mandatory for multiple tables list where => [ my where conditions ], # mandatory for 'where' rules order_by => [ my order list ], # mandatory for 'order by' rules group_by => [ my group list ], # mandatory for 'group by' rules buffer => my buffe ref, # mandatory for extract the data flush => 1 | 0, # options, reset buffer before load # default: buffer will be reseted );
Example1: Extract all fields from the table without notfound option.
SQL::Simple->Select ( table => table_1 where => [ my_conditions ], buffer => \@my_buffer, );
Example2: Extract all fields from the table using notfound option.
SQL::Simple->Select ( table => table_1 where => [ my_conditions ], buffer => \@my_buffer, notfound => 1, );
NOTE: You must check the number of lines on my_buffer array or validate by getRows method to identify the not found condition.
my_buffer
getRows
Example3: Extract and Order
SQL::Simple->Select ( order_by => [ col1 => SQL_SIMPLE_ORDER_ASC, col2 => SQL_SIMPLE_ORDER_DESC ], ... );
Example4: Extract and Order using defaults (ascending)
SQL::Simple->Select ( order_by => [ "col1", "col2" ], ... );
NOTE: Both columns (col1 and col2) will use ascending ordered.
Example5: Extract with distinct columns
SQL::Simple->Select ( fields => [ "distinct", "col1" ], or fields => [ "distinct" => "col1" ], ... );
NOTE: Distinct by "col1".
Example5: Extract with functions
SQL::Simple->Select ( fields => [ "count(*)" ], ... );
NOTE: The column must be single field.
SQL::Simple->Select ( fields => [ "max(col1)", "min(col1)", "count(col1)", "substr(col1,1,8)" ], ... );
Example6: Escaping field, no validation will be.
SQL::Simple->Select ( fields => [ "\\my_expression" ], ... );
NOTE: The '\' (backslash) will escape all validations.
This method is the "Select Method" and was created to scan tables based on key and cursor on. For this case, some options must be specified and controls returned, items to create pagination concepts.
SQL::Simple->SelectCursor ( ... cursor_command => SQL_SIMPLE_CURSOR_TOP | SQL_SIMPLE_CURSOR_LAST | SQL_SIMPLE_CURSOR_NEXT | SQL_SIMPLE_CURSOR_BACK | SQL_SIMPLE_CURSOR_RELOAD, cursor => current_cursor_value, cursor_key => col_1, cursor_info => %cursor_info | @cursor_info | $cursor_info, ... limit => no_lines, );
NOTE: The notfound option is not required.
cursor_command: Sets the type of command to be executed in the search, must be:
SQL_SIMPLE_CURSOR_TOP: Go to the first page of the search. No cursor_info will be used.
cursor_info
SQL_SIMPLE_CURSOR_LAST: Go to the last page search. No cursor_info will be used.
SQL_SIMPLE_CURSOR_NEXT: Go to the next search page. Will use the 'first' info on cursor_info option. If 'cursor' is missing (or undef) the module will search as 'SQL_SIMPLE_CURSOR_TOP' command.
SQL_SIMPLE_CURSOR_BACK: Go to the pervious search page. Will use the 'first' info on cursor_info option. If 'cursor' is missing (or undef) the module will search as 'SQL_SIMPLE_CURSOR_LAST' command.
SQL_SIMPLE_CURSOR_RELOAD: Reload current cursor. Will use the 'first' info on cursor_info option.
cursor: Sets the starter cursor value for the search. This options is not mandatory.
The method use the cursor_info option as base to scan the pages, however, the cursor option have preference if presented.
cursor
cursor_key: Sets the key that will use to search. You must use only one key, multiple keys are not supported.
cursor_info: Sets the return buffer to getting the page controls. This informantion should be used in the paging process.
We highly recommend using this option to help the pagination process.
At the end of each retrieve the cursor of page will be saved in cursor_info option.
Defaults: no defaults, if omitted this information must be obtained by the getLastCursor method.
getLastCursor
The buffer reference can be hash_ref, array_ref or scalar_ref, for each case we will have:
hash_ref:
hash_ref => { lines => no_lines, first => first_key, last => last_key, rc => rc };
array_ref:
array_ref => [ rc, no_lines, first_key, last_key ];
scalar_ref:
scalar_ref => "[rc] [no_lines] [first_key] [last_key]"
The rc, no_lines, first_key and last_key values has present for all formats.
rc
no_lines
first_key
last_key
rc: Consists the return code of SQL Command.
no_lines: Consists the number of extracted lines.
first: Consists the first key value.
last: Consists the last key value.
BEWARE: For keys that use space values do not use the scalar_ref format to capture the first_key and last_key.
limit: Sets the maximum number of lines to retrive. The option must be specified. Use '0' (zero) for unlimited lines.
Example1: This example we have four stage of retrieve. The 1st go to the first page on the table. The 2nd read the second page. The 3rd go to last page on the table. The 4th read the penultimate page.
NOTE: The number of page is limited by limit option.
limit
# at end of each retrieve the 'cursor_info' option is up to date. SQL::Simple->SelectCursor ( table => "my_table", where => [ ... ], ... cursor_command => SQL_SIMPLE_CURSOR_TOP, cursor_info => hash_ref | array_ref | scalar_ref, buffer => array_ref, ... limit => no_lines, ); # the scan will retrieve 'no_lines' lines into the 'buffer' options, ... SQL::Simple->SelectCursor ( table => "my_table", where => [ ... ], ... cursor_command => SQL_SIMPLE_CURSOR_NEXT, cursor_info => hash_ref | array_ref | scalar_ref, buffer => array_ref, ... limit => no_lines, ); # the scan will retrieve the next 'no_lines' lines based in saved ... SQL::Simple->SelectCursor ( table => "my_table", where => [ ... ], ... cursor_command => SQL_SIMPLE_CURSOR_LAST, cursor_info => hash_ref | array_ref | scalar_ref, ... buffer => array_ref, limit => no_lines, ); ... SQL::Simple->SelectCursor ( table => "my_table", where => [ ... ], ... cursor_command => SQL_SIMPLE_CURSOR_BACK, cursor_info => hash_ref | array_ref | scalar_ref, ... buffer => array_ref, limit => no_lines, ); # the scan jump at end of table and retrieve the next 'no_lines'
This method updates the selected rows from a table based the conditions in the where clause.
For security rasons the command does not perform updates if where clause is omitted or empty, in this case, you must add the force option to do.
SQL::Simple->Update ( table => "my_table", fields => { col_1 => value, col_2 => value, }, where => [ my_conditions ], ... );
This is the method that execute SQL Commands. It is implicitly called to execute all commands created by this module. It is owner of the process to preparation, execution and written the data on the buffer.
SQL::Simple->Call ( command => string, buffer => hash_ref | array_ref | scalar_ref | callbacks, flush => 1 | 0, );
command: Consists of the SQL Command to be executed (fully formatted)
buffer: Consists of the Return Buffer Area, where the method will written the columns and rows, can be:
hash_ref Address of HASH; array_ref Address of ARRAY; scalar_ref Address of SCALAR; callback_ref Address of Your Subroutine.
flush: Force the clean up in the return buffer area, by default all buffer area will be initialized.
Consists of the method to get the realname for columns mapped on "Aliases Table".
The method return the Column Arguments Value if the table or column argument does not exists.
NOTE: This method can be used on PLUGINS to help to translate the aliases names.
Consists of the method to get the realname for table mapped on "Aliases Table".
The method return the Table Arguments Value if the table argument does not exists.
Consists of the method to get the interface entry point address of the database.
For interface=dbi, this is the entry point for the DBI->new() method. =back
DBI->new()
SQL::Simple->Call ( command => string, buffer => hash_ref | array_ref | scalar_ref | callbacks, );
buffer: Consists of the Return Buffer Arear, where the method will written the columns and rows, can be:
For interface=dbi, this is the entry point for the DBI->new() method.
$dbh = SQL::Simple->getDBH();
Consists of the method to get the last cursor information
$hash_ref = SQL::Simple->getLastCursor();
The method return a HASH struct with the values:
$hash_ref => { rc => retun_code, lines => number_of_lines, first => first_key_value, last => last_key_value, };
Consists of the method to get the last SQL Log File saved on the disk.
$last_saved_logfile = SQL::Simple->getLastSave();
Consists of the method to get the last executed SQL command.
$last_sql_command = SQL::Simple->getLastSQL();
Consists of the method to get the last message.
$message = SQL::Simple->getMessage();
Consists of the method to get the last return code.
$rc = SQL::Simple->getRC();
Consists of the method to get the number of extracted rows from the last SQL Command.
$rows = SQL::Simple->getRows();
Consists of the method for testing the Where Clause. It is not performing any functions associated with SQL Commands. It was created as support tool to certify and test the syntax of the 'Where Clause' format.
Where Clause
$rc = SQL::Simple->getWhere ( table => table1 or table => [ table1, table2, ... ], buffer => scalar_ref, where => [ condition1, ... ], # see Where Clause );
Example1:
use SQL::Simple; my %contents = ( table1 => { name => "real_name", cols => { _number => "my_number", _text => "my_text", } } ); my $mymod = SQL::Simple->new ( db=> "dummy", server=> "dummy", driver => "mysql", connect => 0, tables => \%contents ); my $buffer; $mymod->getWhere ( table => "table1", buffer => \$buffer, where => [ [ _no => 1, "or", _no => [ 2, 3 ] ], _text => "myname" ], ); print "My #1 Where is [",$buffer," ]\n"; $mymod->getWhere ( table => "table1", buffer => \$buffer, where => [ _no => [ 1, "..", 3 ], "and", _text => "myname" ] ); print "My #2 Where is [",$buffer," ]\n";
My #1 Where is [ (_no = 1 or _no IN (2,3)) and _text = 'myname' ] My #2 Where is [ _no BETWEEN (1,3) and _text = 'myname' ]
The parameters can be used to enable the Save SQL Command Process. This process write on local disks the SQL Command when you can use for Debuging or Recover Database.
SQL_SIMPLE_CMD_OFF # Save Command is disabled (default) SQL_SIMPLE_CMD_ON # Save Command is enabled for update SQL_SIMPLE_CMD_ALL # Save Command is enabled for update/read SQL::Simple->new( sql_save => SQL_SIMPLE_CMD_OFF | SQL_SIMPLE_CMD_ON | SQL_SIMPLE_CMD_ALL, ... );
Default: SQL_SIMPLE_CMD_OFF
NOTE: You can override (temporary) this option on each SQL execution (valid for all commands).
SQL::Simple->Select( sql_save => 1, ... ); # (if global disabled) or SQL::Simple->Select( sql_save => 0, ... ); # (if global enabled)
The parameters can be used to enable the Log Process. This process send the conditional message on STDERR and/or Syslog Service. The send messages does not abort the module.
We recommended using the Syslog Service for complexed applications.
SQL_SIMPLE_LOG_OFF # Log is disabled (default) SQL_SIMPLE_LOG_SYS # Log is enabled for syslog service SQL_SIMPLE_LOG_STD # Log is enabled for STDERR SQL_SIMPLE_LOG_ALL # Log is enabled for STDERR/Syslog SQL::Simple->new ( message_log => SQL_SIMPLE_LOG_OFF | SQL_SIMPLE_LOG_SYS | SQL_SIMPLE_LOG_STD | SQL_SIMPLE_LOG_ALL, ... );
The SQL_SIMPLE_LOG_OFF is recomended for high intensive transactional, like webservices. The SQL_SIMPLE_LOG_SYS is recomended for application services, like daemons. The SQL_SIMPLE_LOG_STD is recomended for iteractive Command Line Interfaces. However, you can do anything.
SQL_SIMPLE_LOG_OFF
SQL_SIMPLE_LOG_SYS
Default: SQL_SIMPLE_LOG_STD
NOTE: You can override (temporary) this option on each SQL execution (valid for all command).
The parameters can be used to establish the operations on extract list.
SQL_SIMPLE_CURSOR_TOP # Cursor Command Top of List (ascending order) SQL_SIMPLE_CURSOR_BACK # Cursor Command Backward (descending order) SQL_SIMPLE_CURSOR_NEXT # Cursor Command Forward (ascending order) SQL_SIMPLE_CURSOR_LAST # Cursor Command Bottom of List (descending order)
Default: no defaults
The parameters can be used to determine the sort ordered on columns.
SQL_SIMPLE_ORDER_OFF # Order is disbled (for SelectCursor only) SQL_SIMPLE_ORDER_ASC # Order is Ascending SQL_SIMPLE_ORDER_DESC # Order is Descending
Default: SQL_SIMPLE_ORDER_ASC
SQL_SIMPLE_ORDER_ASC
NOTE: The value SQL_SIMPLE_ORDER_OFF is onle valid for the SelectCursor Command. This value disables the sorting process. The command, by default, does the sorting using the cursor_key option.
SQL_SIMPLE_ORDER_OFF
cursor_key
This values estabilish the termination code after the method has been done.
SQL_SIMPLE_RC_SYNTAX # Return Code Syntax Error SQL_SIMPLE_RC_OK # Return Code Execution SQL Successful SQL_SIMPLE_RC_ERROR # Return Code Execution SQL with errors SQL_SIMPLE_RC_EMPTY # Return Code Execution SQL Successful with no Rows
NOTE: If you run queries with no results (no rows found), the termination code will be SQL_SIMPLE_RC_EMPTY. However, you can use the notfound option to force this query to be SQL_SIMPLE_RC_OK.
SQL::Simple->Select( ..., where [ id => 'i not exists' ], notfound => 1, ... );
BEWARE: In this case, you must validade the Number of Rows (getRows method) or Number of Extracted Data in Buffer to verify whether exists data to be processed.
... if (SQL::Simple->Select( ..., notfound => 1, buffer => \@mybuffer, ... )) { ## here your code to process sql condition error. } elsif (!SQL::Simple->Rows() || !@mybyffer) { ## here your code to process no data on query. } else { ## here your code for process the data. } ...
BEWARE: Use Wisely
The plugins consists in addon to customize the process and/or to create data and/or adjusts the arguments on the Methods. They are not mandatory, however is available to use. It is can apply over:
Call Method Delete Method Insert Method Open Method Select Method SelectCursor Method Update Method
Create in your plugin Methods using same name above. The module will check it before trying to run.
All modules receive as an argument the hash_ref address as unique data. You can run the public methods and/or make changes or adjustements in the data before the module execute the operation.
The plugin must return the following termination code:
rc < 0 Syntax error and abort the action; rc = 0 Successful and continue the action; rc = 1 Error and abort the action; rc = 2 Successful but skip the action.
BEWARE: Do not make changes on currents plugins.
This plugin establish the dsname data, argument #1 from DBI->connect()
dsname
argument #1
DBI->connect()
SQL::Simple::DBI::MySQL sub Open() { my $self = shift; my $self_sql_simple = shift; $self->{argv}{dsname} = "DBI::mysql:database=[database]:host=[server]:port=[port]"; return 0; }
NOTE: No more changes need.
SQL::Simple::DBI::PG Open Methods { my $self = shift; my $self_sql_simple = shift; $self->{argv}{dsname} = "DBI::Pg:dbnam=[database]:host=[server]:port=[port]"; return 0; }
SQL::Simple::DBI::SQLite Open Methods { my $self = shift; my $self_sql_simple = shift; $self->{argv}{dsname} = "DBI::SQLite:dbname=[dbfile]"; return 0; }
This sample show a simple struct to be create to support your addon.
## The "Open" method must initialize the "dsname" environment. # ## The "dsname" will be used as the first argument on the "DBI->connect" # ## rc < 0 - syntax error and abort ## rc = 0 - successfull and continue ## rc = 1 - errors and abort ## rc = 2 - successfull but do not make nothing package SQL::Simple::[interface]::[driver]; use 5.006001; use strict; use Exporter; our @ISA = qw ( Exporter ); our @EXPORT = qw( Open $VERSION ); our @EXPORT_OK = @EXPORT; our %EXPORT_TAGS = ( all => [@EXPORT_OK] ); 1; sub new() { my $class = shift; $class = ref($class) || $class || 'SQL::Simple::[interface]::[driver]'; my $self = {}; $self->{argv} = {@_}; bless($self,$class); } # the dsname must initialize here. # rc=0 keep running # rc<0 abort sub Open() { my $self = shift; my $sql_simple = shift; $sql_simple->{argv}{dsname} = "[my_database_name]"; return 0; # rc=0 to continue } # this procedure must initialize the "dsname" enviroment. # rc=0 keep running # rc<0 abort sub Delete() { my $self = shift; my $sql_simple = shift; ... return 0; # rc=0 to continue } # rc=0 keep running # rc<0 abort sub Select() { my $self = shift; my $sql_simple = shift; ... return 0; # rc=0 to continue } # rc=0 keep running # rc<0 abort sub SelectCursor() { my $self = shift; my $sql_simple = shift; ... return 0; # rc=0 to continue } # rc=0 keep running # rc<0 abort sub Insert() { my $self = shift; my $sql_simple = shift; ... return 0; # rc=0 to continue } # rc=0 keep running # rc<0 abort sub Update() { my $self = shift; my $sql_simple = shift; ... return 0; # rc=0 to continue } # rc=0 keep running # rc<0 abort sub Call() { my $self = shift; my $sql_simple = shift; ... return 0; # rc=0 to continue } ##Ended
Error: The database name value omitted or empty.
Action: Check the Constructor Command and validate the db option.
db
SQL::Simple->new( db => dsname, ... );
Command: Constructor (method new)
Error: The server name value omitted or empty.
Action: Check the Constructor Command and validate the server option.
server
SQL::Simple->new( server => servername, ... );
Error: The inteface driver name omitted or invalid.
Action: Check the Constructor Command and validate the interface option. The current module support only dbi interface. The Open Command must be use the option:
interface
SQL::Simple->new( interface => "dbi", ... );
Error: The database driver name omitted or invalid for the interface driver.
Action: Check the Open Command and validate the driver option. For interface=dbi use:
driver
SQL::Simple->new( driver=> 'mysql' or 'mariadb' or 'postgres' or 'sqlite3' , ... );
Error: The table alias name omitted or empty.
Action: Check the Command and validate the table option. Remember: the you must specify the aliases table name assigned on contents table.
table
SQL::Simple->Select( table => aliases_table_name, ... ); # single select or SQL::Simple->Select( table => [ table1, table2, ... ], ... ); # merged selected
Command: Select, Delete, Insert and Update
Error: The table alias name is not a single value or array value.
Action: Check the Select Command and validate the table option.
SQL::Simple->Select( table => single_value_name, ... ); # single select or SQL::Simple->Select( table => [ table1, table2, ... ], ... ); # merged select
Command: Select and SelectCursor or getWhere Method
Error: The list of fields is not array.
Action: Check the Select Command and validate the fields option.
fields
SQL::Simple->Select( fields => [ field1, field2, ... ], ... );
Command: Select and SelectCursor
Error: The option group_by is not a single value or array value.
group_by
Action: Check the Select Command and validente the group_by option. Example:
SQL::Simple->Select( group_by => col_name, ... ); or SQL::Simple->Select( group_by => [ col1_name, col2_name, ... ], ... );
Error: The option order_by is not a single value or array value.
order_by
Action: Check the Select Command and validente the order_by option. Example:
SQL::Simple->Select( order_by => col_name, ... ); or SQL::Simple->Select( order_by => [ col1_name => order1, col2_name => order2, ... ], ... );
Error: The indexed field is using a non-valid table/field identifier.
Action: The table must specified on the table option. The fields is omitted, have multiples tables on the table option and one or more tables does not exists on the "Aliases Table". Check the aliases of the table names specified in the fields in use of the where option.
SQL::Simple->Select( table => [ t1, t2 ], where => [ t1.id => t2.id, t3.name => "dummy" ], ... ); # The 't3' is not mapped on the Select Command. To fix you must: # a) Use be 't1' or 't2' as aliases on the field 'name' or; # b) Specify the 't3' on the 'table' option.
Error: The SQL Command was successful executed without extracted lines (results).
Action: Validade the conditions on the where option or use notfound option to force SQL_SIMPLE_RC_OK return code.
SQL::Simple->Select( notfound => 1, ... );
Command: Select, Delete, Insert, Update
Error: The Cursor information is omitted or empty.
Action: Check the cursor and validate the cursor option on the SelectCursor Command.
SQL::Simple->SelectCursor( cursor => value, ... );
Command: SelectCursor
Error: The Cursor Key information is omitted or empty.
Action: Check the cursor key and validate the cursor_key option, on the SelectCursor Command.
SQL::Simple->SelectCursor( cursor_key => value, ... );
Error: The Cursor Command information is not invalid.
Action: Check the cursor command and validade the cursor_command option. The option must be:
cursor_command
SQL_SIMPLE_CURSOR_TOP # goto first page SQL_SIMPLE_CURSOR_NEXT # goto next page, based current cursor SQL_SIMPLE_CURSOR_BACK # goto backward page, based current cursor SQL_SIMPLE_CURSOR_LAST # goto last page SQL::Simple->SelectCursor( cursor_command => command, ... );
Warning: One Write Command (Delete or Update) was execute without where option. For secure rasons, full updates must use force options.
Beware: For secure rasons, full updates must use force option.
# rejected command SQL::Simple->Delete( table => t1 ); # correct command SQL::Simple->Delete( table => t1, force => 1 );
Command: Delete and Update
Error: The List of Fields is omitted or empty.
Action: Check the list of fields and validate the fields option.
# rejected command SQL::Simple->Insert( fields => col1, ... ); or SQL::Simple->Insert( fields => { col1 }, ... ); # correct command SQL::Simple->Insert( fields => { col1 => value, ... }, ... );
Command: Insert and Update
Error: The List of Fields is malformed. One field must have assigned value. The arrayref format is valid only for 'insert' command.
Action: Check the list of fliends and validate the fields option and/or conflict option.
# rejected command SQL::Simple->Update( fields => col1, ... ); or SQL::Simple->Update( fields => { col1 }, ... ); # correct command SQL::Simple->Update( fields => { col1 => value, ... }, ... );
Error: The Interface and/or Driver does not exists.
Action: Check the interface and driver options and libraries on Perl Installation.
List of folder to check:
# perl -E 'print join("\n",@INC),"\n";'
How to check:
# ls [inc_folder]/[interface]/[plugin_name].pm
Error: The where option or one subitem in the where option is empty.
Action: Check the where option on the Command. see "Where Clause".
# invalid where SQL::Simple->Select( where => [], ... ); or SQL::Simple->Select( where => [ a => 1, b => [] ], ... ); # correct where SQL::Simple->Select( where => [ mycondition => myvalue ], ... ); or SQL::Simple->Select( where => [ a => 1, b => [ myvalue, ... ], ], ... );
Error: The where option or one subitem in the where option is not single value or array value.
# invalid where SQL::Simple->Select( where => { a => 1 }, ... ); # correct where SQL::Simple->Select( where => [ a => 1 ], ... );
Error: These was a database access before a successful Open Command.
Action: Check the Open Command process to grant the opened status to the database.
Command: Select, Delete, Insert, Update and Call
Error: The SQL Command is omitted or empty.
Action: Check the SQL Command and validate the command option on the Call Command.
command
SQL::Simple->Call( command => my-sql-command, ... );
Command: Call
Error: The Call Command was been executed using a non-valid struct as return buffer.
Action: Check and validate the buffer option on the Command. The buffer must be:
# returning on hash_ref SQL::Simple->Call( buffer => \%myHash, ... ); # returning on array_ref SQL::Simple->Call( buffer => \@myArray, ... ); # returning on scalar_ref SQL::Simple->Call( buffer => \$myScalar, ... ); # returning by callback_ref SQL::Simple->Call( buffer => \@mySubRot, ... ); ... sub mySubRot() { my $ref = shift; ... ; return 0; }
System: The Save Option was been aborted with system error during make folder to write.
Action: Check the system-message to fix. Common errors:
system-message
a) C<Permission denied>: Check the folder ACL; b) C<No left space>: Check the folder free space or quota; c) C<Invalid filename>: Check the filename and use supported characters for your system.
System: The Save Option was been aborted with system errors during open file to write.
a) C<Permission denied>: Check the Folder ACL; b) C<Permission denied>: Check if filer already using an onther ACL; c) C<No left space>: Check the folder free space or quota; d) C<Invalid filename>: Check the filename and use supported characters for your system.
Error: The assigned value for one field is not single value or array value.
Action: Check the subitem in the where option.
# invalid SQL::Simple->Select( where => [ a => \&callback ], ... ); or SQL::Simple->Select( where => [ b => { c, 1 }, ... ); # correct SQL::Simple->Select( where => [ a => value, ], ... ); or SQL::Simple->Select( where => [ b => [ c, 1 ], ... );
Command: Select, Delete, Insert, and Update
Error: The TCP Port value exced the range 1-65535.
Action: Check and validate the port option on the Constructor.
port
SQL::Simple->new( port => 1..65535, ... ); # NOTE: The 'port' option is not required for standard installation.
Error: The Aliases Table was defined but is not a hashref.
Action: Check the struct and validate the tables option on the Constructor. See "Aliases Table"
tables
Error: The cols option in the Aliases Table must be hash_ref.
cols
Error: The cols option in the Aliases Table is empty.
Action: Check the struct validate the tables option on the Constructor. See "Aliases Table"
Error: One table in the Aliases Table already defined.
Aliases Table
# invalid SQL::Simple->Select( table => [ t1, t2, t1 ], ... ); # correct SQL::Simple->Select( table => [ t1, t2 ], ... );
Error: The "Aliases Table" with missed fields, no table_name mapped.
Action: Check the list of aliases table and add assign the real table name. "Aliases Table"
Plugin: Plugin loader error
Action: The require load process fail for interface and drive options.
drive
Plugin: The 'new' method in plugin finished with errors.
Action: Check the options for the plugin or review your code into plugin
Plugin: Plugin aborted with errors
Action: Check the message for Plugin on drive option
Error: The facility option must be local0 to local7.
facility
Action: Check the facility option on Constructor method.
Command: Constructor (new)
Error: The Service option must be alphanumeric characters.
Service
alphanumeric
Action: Check the service option on Constructor method.
service
Error: The logfile option must be alphanumeric characters.
logfile
Action: Check the logfile option on Constructor method.
Error: The values option must be an arrayref.
values
Action: Check the values option an insert command.
Command: Insert
Error: The conflict option must be an hashref.
Action: Check the conflict option an insert command.
Error: The limit options must be an numeric value.
Action: Check the limit option an insert command.
System: The interface or driver abort with errors.
Action: Check the interface and driver reference guide.
Command: Open, Close, Connect, Select, Delete, Insert, Update and Call.
Error: The message code not found on the Table os Messages.
Action: Internal Module Error, open a issue.
NOTE: For more examples see:
# perl t/testSQL.t
The following example allow simple test between the SQLite, MySQL/MariaDB and Postgres Databases.
To test you need: (a) Create a temporary database, as described below (SQLite, MySQL, MariaDB or Postgres) and; (b) Copy/Past the Source Code and make the test.
# sqlite3 -batch -echo /tmp/test_db.db <<EOF CREATE TABLE master ( i_m_id integer primary key autoincrement, s_m_code text, s_m_name text, s_m_desc text ); CREATE TABLE slave ( i_s_id integer primary key autoincrement, s_m_code text, s_s_code text, s_s_name text, s_s_desc text ); EOF
# mysql -v <<EOF CREATE SCHEMA IF NOT EXISTS test_db DEFAULT CHARACTER SET 'UTF8' ; USE test_db ; CREATE TABLE IF NOT EXISTS test_db.master ( i_m_id int auto_increment unique, s_m_code varchar(32), s_m_name varchar(255), s_m_desc varchar(255) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS test_db.slave ( i_s_id int auto_increment unique, s_m_code varchar(32), s_s_code varchar(32), s_s_name varchar(255), s_s_desc varchar(255) ) ENGINE = InnoDB; CREATE USER 'user_read'@'*' IDENTIFIED BY 'password_read'; CREATE USER 'user_update'@'*' IDENTIFIED BY 'password_update'; GRANT SELECT ON test_db.* TO 'user_read'@'*'; GRANT SELECT,INSERT,UPDATE,DELETE ON test_db.* TO 'user_update'@'*'; EOF
# psql -U postgres <<EOF CREATE DATABASE test_db ENCODING 'UTF8'; EOF # psql -U postgres -b test_db <<EOF CREATE SCHEMA IF NOT EXISTS test_schema ; CREATE TABLE IF NOT EXISTS test_schema.master ( i_m_id smallserial unique, s_m_code varchar(32), s_m_name varchar(255), s_m_desc varchar(255) ); CREATE TABLE IF NOT EXISTS test_schema.slave ( i_s_id smallserial unique, s_m_code varchar(32), s_s_code varchar(32), s_s_name varchar(255), s_s_desc varchar(255) ); CREATE ROLE user_read LOGIN PASSWORD 'password_read'; CREATE ROLE user_update LOGIN PASSWORD 'password_update'; GRANT SELECT ON TABLE test_schema.master, test_schema.slave TO user_read; GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE test_schema.master, test_schema.slave TO user_update; EOF
#!/usr/bin/perl # ## file /tmp/my_first_program.pl # ## CoryRight (C) 2022 - Carlos Celso # ## load external libs use Getopt::Long; use SQL::Simple; use Test::More; use Pod::Usage; ## show my usage format pod2usage("$0 -drive=[driver] -db=[db] -schema=[sch] -server=[host] -port=[port] -user=[user] -password=[pwd]") if (!@ARGV); ## defaults values my $PARM_DB = "test_db"; my $PARM_DBFILE = "/tmp/test_db.db"; # for database im memory use: ":memory:" my $PARM_SCHEMA = "test_schema"; my $PARM_SERVER = "localhost"; my $PARM_USER = "user_update"; my $PARM_PASSWORD = "password_update"; ## parsing options my $get = new Getopt::Long::Parser; $get->configure("pass_through"); $get->getoptions ( 'driver=s' => \$PARM_DRIVER, 'db=s' => \$PARM_DB, 'dbfile=s' => \$PARM_DBFILE, 'schema=s' => \$PARM_SCHEMA, 'server=s' => \$PARM_SERVER, 'port=s' => \$PARM_PORT, 'user=s' => \$PARM_USER, 'password=s' => \$PARM_PASSWORD, ); ## loading SQL::Simple module ## remember: the defauls values por RaiseError and PrintError is ZERO my $mymod = new SQL::Simple ( driver => $PARM_DRIVER, db => $PARM_DB, dbfile => $PARM_DBFILE, schema => $PARM_SCHEMA, server => $PARM_SERVER, port => $PARM_PORT, login => $PARM_USER, password => $PARM_PASSWORD, tables => { my_master => ## sets aliases entries for master table { name => "master", cols => { my_i_m_id => 'i_m_id', my_s_m_code => 's_m_code', my_s_m_name => 's_m_name', my_s_m_desc => 's_m_desc', }, }, my_slave => ## sets aliases entries for slave table { name => "slave", cols => { my_i_s_id => 'i_s_id', my_s_m_code => 's_m_code', my_s_s_code => 's_s_code', my_s_s_name => 's_s_name', my_s_s_desc => 's_s_desc', }, }, } ); exit if (!defined($mymod)); ## my test &my_upload(); # initialize the tables &my_get_master(); # test master table &my_get_slave(); # test slave table &my_get_merge(); # test merge between master and slave ## finishing test $mymod->Close(); # do not forgot me done_testing(); # test done exit; ## my upload data ## remove previous data ## creating dynamic data into the master and slave table sub my_upload() { ## remove previous data $mymod->Delete ( table => "my_master", force => 1 ); $mymod->Delete ( table => "my_slave", force => 1 ); ## initializing master/slave table foreach my $code(0..9) { my $er=0; my $ok=0; ## inserting data into master $code = sprintf("%04i",$code); $mymod->Insert ( table => "my_master", fields => { my_s_m_code => "master_".$code, my_s_m_name => "name_".$code, my_s_m_desc => "description_".$code, } ); ($mymod->getRC()) ? $er++ : $ok++; ## inserting data into slave based master data foreach my $subcode(10..19) { $subcode = sprintf("%04i",$subcode); $mymod->Insert ( table => "my_slave", fields => { my_s_m_code => "master_".$code, my_s_s_code => "slave_".$subcode, my_s_s_name => "name_".$subcode, my_s_s_desc => "description_".$subcode, } ); ($mymod->getRC()) ? $er++ : $ok++; } ## shown counters fail("Number of ".$er." errors (master+slave), Code ".$code) if ($er); pass("Number of ".$ok." successful (master+slave), Code ".$code) if ($ok); } } ## simple test of load master data ## load all master data into buffer sub my_get_master() { my @buffer; $mymod->Select ( table => "my_master", buffer => \@buffer, order_by => "my_i_m_id", ); ## test number of loaded rows ok($mymod->getRows()==10,"Master select, rows ".$mymod->getRows()); } ## simple test of load slave data ## load all slave data into buffer sub my_get_slave() { my @buffer; $mymod->Select ( table => "my_slave", buffer => \@buffer, order_by => "my_i_s_id", ); ## test number of loaded rows ok($mymod->getRows()==100,"Slave select, rows ".$mymod->getRows()); } ## simple test of merge between master and slave tables sub my_get_merge() { my @buffer; $mymod->Select ( table => [ "my_master","my_slave" ], buffer => \@buffer, fields => [ "my_master.my_s_m_code", "my_slave.my_s_s_code" ], ); ## test number of loaded rows ok($mymod->getRows()==1000,"Master/Slave merge-1, rows ".$mymod->getRows()); $mymod->Select ( table => [ "my_master","my_slave" ], buffer => \@buffer, fields => [ "my_master.my_s_m_code", "my_slave.my_s_s_code" ], where => [ "my_master.my_s_m_code" => "my_slave.my_s_m_code" ] ); ## test number of loaded rows ok($mymod->getRows()==100,"Master/Slave merge-2, rows ".$mymod->getRows()); $mymod->Select ( table => [ "my_master","my_slave" ], buffer => \@buffer, fields => [ "my_master.my_s_m_code", "my_slave.my_s_s_code" ], where => [ "my_master.my_s_m_code" => [ "!", "my_slave.my_s_m_code" ], ] ); ## test number of loaded rows ok($mymod->getRows()==900,"Master/Slave merge-3, rows ".$mymod->getRows()); } __END__
# perl /tmp/my_first_program.pl -driver=sqlite ok 1 - Number of 11 successful (master+slave), Code 0000 ok 2 - Number of 11 successful (master+slave), Code 0001 ok 3 - Number of 11 successful (master+slave), Code 0002 ok 4 - Number of 11 successful (master+slave), Code 0003 ok 5 - Number of 11 successful (master+slave), Code 0004 ok 6 - Number of 11 successful (master+slave), Code 0005 ok 7 - Number of 11 successful (master+slave), Code 0006 ok 8 - Number of 11 successful (master+slave), Code 0007 ok 9 - Number of 11 successful (master+slave), Code 0008 ok 10 - Number of 11 successful (master+slave), Code 0009 ok 11 - Master select, rows 10 ok 12 - Slave select, rows 100 ok 13 - Master/Slave merge-1, rows 1000 ok 14 - Master/Slave merge-2, rows 100 ok 15 - Master/Slave merge-3, rows 900 1..15
DBD::mysql, DBD::Pg, DBD::SQLite, DBI, Date::Calc, File::Path, File::Spec, JSON, Sys::Syslog
MySQL, MariaDB, Postgres, SQLite, Wiki, W4C
Carlos Celso, <ccelso@cpan.org>
Copyright (C) 2008-2022 by Carlos Celso
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.
To install SQL::Simple, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Simple
CPAN shell
perl -MCPAN -e shell install SQL::Simple
For more information on module installation, please visit the detailed CPAN module installation guide.