Menu
▾
▴
METHODS
go to: HOME, DESCRIPTION or WHERE
METHODS
NOTE: See EXTENDED EXAMPLES to see some use cases.
Following methods can be exported:
Constructor (method new)
SQL::SimpleOps->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".
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.
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.
Global Options
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.
Defaults: no defaults
interface:
Sets the interface module to use. The current version support only "interface=dbi".
Defaults: The defaul value is "dbi".
interface_options:
See options for DBI module.
Defaults: The default values for "interface=dbi" are: "RaiseError=0" and "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.
Defaults: no defaults
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 (default);
SQL_SIMPLE_LOG_ALL: Write messages on Syslog/STDERR both.
Defaults: The default value is "SQL_SIMPLE_LOG_STD".
NOTE: You can enable Syslog and "STDERR" simultanely. Use the "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-SimpleOps"
message_syslog_facility:
Sets the Syslog Facility for the messages on the System Syslog Messages, must be: "local0" to "local7". See: Sys::Syslg
Defaults: The default value is "local0".
password:
The password/profile for authentication process.
Defaults: no defaults
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:
DB2: 50000/tcp
MySQL: 3306/tcp
Oracle: 1521/tcp
Postgres: 5432/tcp
SyBase: 5000/tcp
SQLite: not required
...
Defaults: The default value depend of database engine.
quote:
Sets the quote caracter on string commands (see "setQuote" method), must be: apostrophe or quote.
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_save_bydate:
Sets enable/disable writing log files into distinct folders. The folder will be create as:
[sql_save_dir]/"YYYY/YYYYmm/YYYYmmdd"/[logfile]
Defaults: The option is disabled. See "sql_save_dir" option.
sql_save_dir:
Sets the folder for writes log file process.
Defaults: (unix/linux) /var/spool/sql/ or (windows) c:\windows\temp
NOTE: The folder must be previously created.
sql_save_name:
Sets the filename string on writes log file process. The string will prefix the name in the logfile.
Defaults: The default value is "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 the return code will be "SQL_SIMPLE_RC_ERROR".
tables:
Sets the list of aliases rules for the tables and fields. See "Aliases Table".
Defaults: no defaults.
The follow options can be temporarily modified by the methods:
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.
* 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.
Example1: The database "my_db" using "interface_options". see DBI
my $mymod = SQL::SimpleOps->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
my $mymod = SQL::SimpleOps->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-SimpleOps")
...
);
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.
my $mymod = SQL::SimpleOps->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.
database_name:
Consists in the "my_db" database value.
today:
Current day formated as "YYYYmmdd" (year+month+day).
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 splited by date.
my $mymod = SQL::SimpleOps->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]/my_cmds.my_db.[today].[pid].[counter]
Example5: The database "my_db" forcing commit for each update command.
my $mymod = SQL::SimpleOps->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.
When can i use it? The "commit" option can be used at the end of a large number 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::SimpleOps->Commit(); # (simple commit command)
or
SQL::SimpleOps->Update
(
table => "my_table",
...
commit => 1, # (default is OFF)
);
Openning MySQL/MariaDB Engine. Format: see DBI
my $mymod = SQL::SimpleOps->new
(
driver => "mysql" | "mariadb",
interface_options =>
{
mysql_auto_reconnect => 0 | 1,
...
},
...
);
Example1: The database as "my_info", loging "my_user", password "my_auth":
my $mymod = SQL::SimpleOps->new
(
driver => "mysql",
db => "my_info",
login => "my_user",
password => "my_auth",
interface_options => { mysql_auto_reconnect => 0 },
);
Openning SQLite Engine. Format: see DBI
my $mymod = SQL::SimpleOps->new
(
driver => "sqlite",
db => "my_info",
dbfile => "my_file",
interface_options =>
{
# no special options
},
...
);
NOTE: The default valor for "dbfile" is "[db].db".
Example1: The database as "my_info" and fileset as "my_info.db":
my $mymod = SQL::SimpleOps->new
(
driver => "sqlite",
db => "my_info",
);
Example2: The database as "my_db" and fileset as "my_file.db":
my $mymod = SQL::SimpleOps->new
(
driver => "sqlite",
db => "my_db"
dbfile => "/var/tmp/my_file.db" # (or: dbfile => "my_file")
interface_options =>
{
RaiseError => 1, # (the default is 0)
PrintError => 1, # (the default is 0)
},
);
NOTE: The default location is the current folder in use. The Fullpath on "dbfile" is mandatory for specific location.
Openning Postgres Engine. Format: see DBI
my $mymod = SQL::SimpleOps->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:
my $mymod = SQL::SimpleOps->new
(
driver => "pg",
login => "my_user",
password => "my_auth",
);
Example2: Using default database, "my_schema" schema, as "my_user" and "my_auth" password:
my $mymod = SQL::SimpleOps->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:
my $mymod = SQL::SimpleOps->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:
my $mymod = SQL::SimpleOps->new
(
driver => "pg",
db => "my_database",
schema => "my_schema",
login => "my_user",
password => "my_auth",
);
Delete
This method removes the selected rows from a table based the conditions in the "where" clause.
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.
SQL::SimpleOps->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, in this case, you must use the function "getRows" to get the number of read rows.
Example1: Forcing remove all rows.
SQL::SimpleOps->Delete
(
table => "my_table",
force => 1,
);
Example2: Forcing no matchs removals completed successful
SQL::SimpleOps->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".
Insert
This method insert the selected rows from a table.
SQL::SimpleOps->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::SimpleOps-> # (insert be fields/values array)
(
table => "my_table",
fields => [ col_1, col_2, ... ],
values => [ val_1, val_2, ... ],
...
);
or
SQL::SimpleOps->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::SimpleOps->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.
SQL::SimpleOps->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.
Example2: Inserting rows with "conflict" option. If the key already only updates will be done.
my $my_time = time();
SQL::SimpleOps->Insert
(
table => "my_table",
fields =>
{
key => 1, # (it is mais key)
value_1 => value, # (it is a value)
},
conflict =>
{
my_update => $my_time, # (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.
my $my_time = time();
SQL::SimpleOps->Insert
(
table => "my_table",
fields =>
{
key => 1, # (it is mais key)
value_1 => value, # (it is a value)
},
conflict =>
{
my_update => $my_time, # (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::SimpleOps->Insert
(
table => "my_table",
fields => [ "key", "value_1" ],
values => [ 1, value ],
);
Select
NOTE: See "Loading Data into Applicatons" how to get the data.
This method extract the selected rows from a tables based the conditions
in the "where" clause.
SQL::SimpleOps->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_buffer_ref, # mandatory for extract the data
flush => 1 | 0, # options, reset buffer before load
# default: buffer will be reseted
);
The option "fields" is not mandatory, however: If you using "Aliases Table" the module will written only defined fields defined in this item, to bypass you must use "" in the field list. If you does not using "Aliases Table" the module will written as "" (all fields) in the field list.
Example1: Extract all fields from the table without "notfound" option.
SQL::SimpleOps->Select
(
table => table_1
where =>
[
my_conditions
],
buffer => \@my_buffer,
);
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.
Example2: Extract all fields from the table using "notfound" option.
SQL::SimpleOps->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.
Example3: Extract and Order
SQL::SimpleOps->Select
(
order_by =>
[
{col1 => SQL_SIMPLE_ORDER_ASC},
{col2 => SQL_SIMPLE_ORDER_DESC}
],
...
);
Example4: Extract and Order using defaults (ascending)
SQL::SimpleOps->Select
(
order_by => [ "col1", "col2" ],
...
);
NOTE: Both columns (col1 and col2) will use ascending ordered.
Example5: Extract with distinct columns
SQL::SimpleOps->Select
(
fields => [ "distinct", "col1" ],
or
fields => [ "distinct" => "col1" ], # see: without '{ ... }'
...
);
NOTE: Distinct by "col1".
Example5: Extract with functions
SQL::SimpleOps->Select
(
fields => [ "count(*)" ],
...
);
NOTE: The column must be single field.
Example5: Extract with functions
SQL::SimpleOps->Select
(
fields => [ "max(col1)", "min(col1)", "count(col1)", "substr(col1,1,8)" ],
...
);
NOTE: The column must be single field.
Example6: Escaping field, no validation will be.
SQL::SimpleOps->Select
(
fields => [ "\\my_expression" ],
...
);
NOTE: The '\' (backslash) will escape all validations.
Example7: Assigning alias field for specific select command.
SQL::SimpleOps->Select
(
fields => [ { "field1" => "alias1" }, field2, ... ],
...
);
NOTE: Use 'alias1' as buffer reference to get field1 data.
Example8: Requesting all fields from table with "Aliases Table" definition
SQL::SimpleOps->Select( fields => "*", ... );
NOTE: If you omitted the "fieldlist" the module shown only defined fields of your "Aliases Table"
Exampl9: Requesting all fields from table without "Aliases Table" definition
SQL::SimpleOps->Select( ... );
NOTE: To list all fields you can omitte the "fieldlist" or use the same option for "Aliases Table" (fields = "*").
SelectCursor
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.
The "where" and "order_by" are not required. The method will insert the conditions based on "cursor_key" and "cusor_info" information.
SQL::SimpleOps->SelectCursor
(
...
cursor_command =>
SQL_SIMPLE_CURSOR_TOP | # read first page
SQL_SIMPLE_CURSOR_LAST | # read last page
SQL_SIMPLE_CURSOR_NEXT | # read forward page, based last pointer
SQL_SIMPLE_CURSOR_BACK | # read backward page, based last pointer
SQL_SIMPLE_CURSOR_RELOAD, # read current page, based last pointer
...
cursor => current_cursor_value,
...
cursor_key => col_1,
or
cursor_key => [ col1, col2, ... ],
...
cursor_info => %cursor_info | @cursor_info | $cursor_info,
...
cursor_order =>
SQL_SIMPLE_ORDER_ASC | # buffer using enforced order_by ASC
SQL_SIMPLE_ORDER_DESC # buffer using enforced order_by DESC
...
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.
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.
Defaults: "SQL_SIMPLE_CURSOR_TOP", to enforce the "buffer_info" initialization.
cursor:
Sets an specific starter cursor value for the search. This options is not mandatory, if used must have the same number of itens as "cursor_key".
The method use the "cursor_info" option as base to scan the pages, however, the "cursor" option have preference if presented.
cursor_key:
Sets the keys that will use to search. You can use only one key or multiple keys based arrayref list.
Defaults: no defaults
The multiple keys will provide an "arrayref" for "first" and "last" items in the "cursor_info".
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 in the pagination process. At the end of each retrieve, the cursor of page will be saved in "cursor_info" option.
The paging process will use the first and last keys and scan the table using these values as starting pointer. The scanning depends on the command that to be executed.
You don't need make any changes in your retrieved "cursor_info", just save it and use as an arguments in your next "SelectCursor" call.
Defaults: no defaults, if omitted this information must be obtained by the "getLastCursor" method.
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, previouscmd => cmd };
or
hash_ref => { lines => no_lines, first => [last_arrayref], last => [last_arrayref], rc => rc, previouscmd => cmd };
Example1: Using single key:
%buffer_info =
{
rc => value, # Single value
lines => value, # Single value
first => value-first-key, # Single value
last => value-last-key, # Single value
previouscmd => value-cmd # Single value
};
Example2: Using multiple keys:
%buffer_info =
{
rc => value, # Single value
lines => value, # Single value
first => [ first1, ..., firstN, ], # Array
last => [ first1, ..., firstN, ], # Array
previouscmd => value, # Single value
};
array_ref:
array_ref => [ rc, no_lines, first_key, last_key, previouscmd ];
or
array_ref => [ rc, no_lines, [first_last_arrayref], [last_last_arrayref], previouscmd];
Example1: Using single key:
@buffer_info =
[
value, # RC value, indexed by #0
value, # Lines value, indexed by #1
value-first-key, # First value, indexed by #2
value-last-key, # Last value, indexed by #3
value-last-cmd, # Last Command, indexed by #4
];
Example2: Using multiple keys:
@buffer_info =
[
value, # RC value, indexed by #0
value, # Lines value, indexed by #1
[ first1, ..., firstN, ], # First Array values, indexed by #2
[ first1, ..., firstN, ], # Last Array values, indexed by #3
value, # Last Command, indexed by #4
];
scalar_ref:
scalar_ref => "[rc] [no_lines] [first_key] [last_key] [previouscmd]"
or
scalar_ref => "[rc] [no_lines] [first_key1] [first_key2] ... [last_key1] [last_key2] ... [previouscmd]"
Example1: Using single key:
$buffer_info = "value value value value value" # separeted by spaces
# RC value, first word
# Lines value, second word
# First value, third word
# Last value, fourth word
# Previous Command value, fifth word
BEWARE: This type of returns is not recommended for keys that have spaces or null values.
Example2: Using multiple keys:
$buffer_info = "value value first1 ... firstN last1 ... lastN prevCmd"
# RC value, first word
# Lines value, second word
# First value, depends on the number of keys
# Last value, depends on the number of keys
# Previous Command value, fifth word
NOTE: The number of "first_keyX" and "last_keyX" depends of number of keys in the "cursor_key".
BEWARE: This type of returns is not recommended for keys that have spaces or null values.
The "rc", "no_lines", "first_key" and "last_key" values has present for all formats.
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.
previouscmd:
Consists the last executed command, used with "RELOAD" command to define which command to reuse (if omitted, it will be use 'if it were "NEXT" command with the option "first"').
cursor_order:
The "cursor_order" was created to enforced how to the data will be ordered in the "buffer". The affected option depends on the type of "buffer" in use ("arrayref" or "hashref").
Using "buffer=arrayref" the option "buffer" will be ordered.
Using "buffer=hashref" the option "buffer_hashindex" will be ordered (if option in use).
The option "buffer_info" will not be affected.
NOTE: The "buffer_info" is a reserved area where it is not recommended to make changes.
By default the following ordered rules will be applied:
SQL_SIMPLE_COMMAND_TOP ascending
SQL_SIMPLE_COMMAND_BACK descending
SQL_SIMPLE_COMMAND_NEXT ascending
SQL_SIMPLE_COMMAND_LAST descending
SQL_SIMPLE_COMMAND_RELOAD ascending
In this case the first and last rows in the buffer will not be aligned with the cursor keys ("cursor_info").
i.e, explemplifying: Supose the list of rows and reads using "limit=10" and "arrayref" as "buffer"
# col1
1: k01
..
10: k10
11: k11
..
20: k20
..
..
90: k90
..
99: k99
The list has the value "col1=k01" as the first line and the last line as the value "col=k99". The following scan was executed (using "limmit=10"):
a) read the top of rows, page1
b) read forwarding, page2
c) read backwarding, page1
d) read the last of rows, page9
The results will be:
Step 'a': The "buffer" contains the value "k01" and "k09" as first and last buffered data. The "cursor_info" contains the first as "k01" and last as "k09".
Step 'b': The "buffer" contains the value "k11" and "k19" as first and last buffered data. The "cursor_info" contains the first as "k11" and last as "k19".
Step 'c': The "buffer" contains the value "k10" and "k01" as first and last buffered data. The "cursor_info" contains the first as "k01" and last as "k10".
Step 'd': The "buffer" contains the value "k99" and "k91" as first and last buffered data. The "cursor_info" contains the first as "k91" and last as "k99".
The "cursor_order" was created to enforce order in the buffer without making any changes to "cursor_info".
Now we will apply the option "cursor_order=ASC" where the results will be:
Step 'a': The "buffer" contains the value "k01" and "k09" as first and last buffered data. The "cursor_info" contains the first as "k01" and last as "k09".
Step 'b': The "buffer" contains the value "k11" and "k19" as first and last buffered data. The "cursor_info" contains the first as "k11" and last as "k19".
See changes in the buffer sequence and the cursor is unchanged.
Step 'c': The "buffer" contains the value "k01" and "k10" as first and last buffered data. The "cursor_info" contains the first as "k01" and last as "k10".
Step 'd': The "buffer" contains the value "k91" and "k99" as first and last buffered data. The "cursor_info" contains the first as "k91" and last as "k99".
See changes in the buffer sequence and the cursor is unchanged.
And, applying the option "cursor_order=DESC" where the results will be:
Step 'a': The "buffer" contains the value "k10" and "k01" as first and last buffered data. The "cursor_info" contains the first as "k01" and last as "k09".
Step 'b': The "buffer" contains the value "k20" and "k10" as first and last buffered data. The "cursor_info" contains the first as "k11" and last as "k19".
See changes in the buffer sequence and the cursor is unchanged.
Step 'c': The "buffer" contains the value "k10" and "k01" as first and last buffered data. The "cursor_info" contains the first as "k01" and last as "k10".
Step 'd': The "buffer" contains the value "k99" and "k91" as first and last buffered data. The "cursor_info" contains the first as "k91" and last as "k99".
See changes in the buffer sequence and the cursor is unchanged.
limit:
Sets the maximum number of lines to retrieve. 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.
# at end of each retrieve the 'cursor_info' option is up to date.
my %cursor_info;
# the scan will retrieve 'number_of_lines' lines into the 'buffer' options
SQL::SimpleOps->SelectCursor
(
table => "my_table",
where => [ ... ],
...
cursor_command => SQL_SIMPLE_CURSOR_TOP,
cursor_info => \%cursor_info,
buffer => array_ref,
...
limit => number_of_lines,
);
...
# the scan will retrieve the next 'number_of_lines' lines based in saved
SQL::SimpleOps->SelectCursor
(
table => "my_table",
where => [ ... ],
...
cursor_command => SQL_SIMPLE_CURSOR_NEXT,
cursor_info => \%cursor_info,
buffer => array_ref,
...
limit => number_of_lines,
);
...
# the scan jump at end of table and retrieve the next 'number_of_lines'
SQL::SimpleOps->SelectCursor
(
table => "my_table",
where => [ ... ],
...
cursor_command => SQL_SIMPLE_CURSOR_LAST,
cursor_info => \%cursor_info,
...
buffer => array_ref,
limit => number_of_lines,
);
...
# the scan back page based the current cursor
SQL::SimpleOps->SelectCursor
(
table => "my_table",
where => [ ... ],
...
cursor_command => SQL_SIMPLE_CURSOR_BACK,
cursor_info => \%cursor_info,
...
buffer => array_ref,
limit => number_of_lines,
);
SelectSubQuery
This method updates the selected to create "select" into the "select" as subqueries process.
SQL::SimpleOps->SelectSubQuery
(
... # see: Select options and format
);
or
SQL::SimpleOps->Select
(
... # see: Select options and format
subquery => 1,
);
The command returns the SQL command in the format:
\(SELECT ... )
NOTE: The backslash and (...) will be sent as escape caracter, required in the "where" clause.
Update
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::SimpleOps->Update
(
table => "my_table",
or
table => [ "mytab1", "mytab2", ... ],
fields =>
{
col_1 => value,
col_2 => value,
},
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.
Call
This is the method that execute SQL Commands. It is implicitly called to execute all commands created by this module (Insert, Update, Delete, Select and Commit). It is owner of the process to preparation, execution and written the data on the buffer.
SQL::SimpleOps->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. The default value is enabled, all buffer area will be initialized.
getAliasCols
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 translate the aliases names.
SQL::SimpleOps->getAliasCols
(
colname,
options
);
The options define where the field will be used and must be:
SQL_SIMPLE_ALIAS_INSERT - used as: INSERT INTO ... (field) ...
SQL_SIMPLE_ALIAS_UPDATE - used as: UPDATE ... SET field = ...
SQL_SIMPLE_ALIAS_DELETE - not applied
SQL_SIMPLE_ALIAS_SELECT - used as: SELECT field .... FROM ....
SQL_SIMPLE_ALIAS_WHERE - used as: ... WHERE field ....
SQL_SIMPLE_ALIAS_ORDERBY - used as: ... ORDER BY field ...
SQL_SIMPLE_ALIAS_GROUPBY - used as: ... GROUP BY fild ...
getAliasTable
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.
NOTE: This method can be used on PLUGINS to translate the aliases names.
getDBH
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.
$dbh = SQL::SimpleOps->getDBH();
getLastCursor
Consists of the method to get the last cursor information
$hash_ref = SQL::SimpleOps->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,
};
getLastSave
Consists of the method to get the last SQL Log File saved on the disk.
$last_saved_logfile = SQL::SimpleOps->getLastSave();
getLastSQL
Consists of the method to get the last executed SQL command.
$last_sql_command = SQL::SimpleOps->getLastSQL();
getMessage
Consists of the method to get the last message.
$message = SQL::SimpleOps->getMessage();
getRC
Consists of the method to get the last return code.
$rc = SQL::SimpleOps->getRC();
getRows
Consists of the method to get the number of extracted rows from the last SQL Command.
$rows = SQL::SimpleOps->getRows();
getWhere
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.
$rc = SQL::SimpleOps->getWhere
(
table => table1
or
table => [ table1, table2, ... ],
buffer => scalar_ref,
where => [ condition1, ... ], # see Where Clause
);
NOTE: The method uses the "Aliases Table" as a reference base for the translation.
Example1:
use SQL::SimpleOps;
my %contents =
(
table1 =>
{
name => "real_name",
cols =>
{
_number => "my_number",
_text => "my_text",
}
}
);
my $mymod = SQL::SimpleOps->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";
Results:
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' ]
setDumper
Consists of the method to enable/disable the previous argument list for each call method. This option can be used for debug propose only to certify the list of arguments in your call. The arguments will be written on "Data::Dumper" format in the STDERR.
REMEMBER: Do not use this option in production. You will have to force to disable if it no longer needed.
$state = SQL::SimpleOps->setDumper ( 0 | 1 ); # use '1' to enable the shown
Example1:
use SQL::SimpleOps;
...
my $mymod = SQL::SimpleOps->new ( ... );
$mymod->setDumper(1); # use '1' to enable the show
$mymod->Select( ... );
print "mySQL: ".$mymod->getLastSQL()."\n";
$mymod->setDumper(0); # use '0' to disable the show
...
Results:
select = { # this text will be sent in STDERR
... # your select command arguments
}
mySQL: SELECT .... # your create SQL command
ENDED
