Menu
▾
▴
USAGE
go to: HOME, CHANGELOG or DESCRIPTION
NAME
SQL::SimpleOps - SQL Simple Operations
SYNOPSIS
Constructor (method new)
use SQL::SimpleOps;
new
$my_module = SQL::SimpleOps->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-SimpleOps')
# 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)
);
Database Initializations
Open
$rc = SQL::SimpleOps->Open();
Wait
$rc = SQL::SimpleOps->Wait(
count => number, # (default: 1 occurs)
interval => number, # (default: 5 secs)
);
Close
$rc = SQL::SimpleOps->Close();
SQL Commands
Commit
$rc = SQL::SimpleOps->Commit();
Delete
$rc = SQL::SimpleOps->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::SimpleOps->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::SimpleOps->Select
(
# list of tables
table => table_alias_name,
or
table => [ table1_alias, table2_alias, ... ],
# list of fields
fields => [ col1_alias, col2_alias, ... ],
or
fields => [ col1_alias, { col2_alias => newalias }, ... ],
# 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, is default)
or
order_by => col_alias, # (default is SQL_SIMPLE_ORDER_ASC)
or
order_by => { col_alias => SQL_SIMPLE_ORDER_ASC | SQL_SIMPLE_ORDER_DESC },
or
order_by => [ col1_alias, col2_alias, ... ],
or
order_by => [
{ col1_alias => SQL_SIMPLE_ORDER_ASC | SQL_SIMPLE_ORDER_DESC },
{ col2_alias => SQL_SIMPLE_ORDER_ASC | SQL_SIMPLE_ORDER_DESC },
...
],
# return buffer # (no defaults)
buffer => hash_ref | array_ref | scalar_ref | callback_ref,
buffer_options => hash_ref | array_ref | scalar_ref | value_ref,
...
buffer_hashkey => column_name,
or
buffer_hashkey => [col1,col2,...],
...
buffer_arrayref => 0 | 1, # (default: 1)
buffer_hashindex => arrayref # (must be arrayref)
# generic options
subquery => 0 | 1, # (default: 0)
limit => value_max_lines, # (default: unlimited 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::SimpleOps->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)
or
cursor_ky => [ col_alias1, ... col_alias2 ],
...
cursor_info = array_ref | hash_ref | scalar_ref>, # (no defaults)
...
cursor_command => # (default: TOP)
SQL_SIMPLE_CURSOS_TOP |
SQL_SIMPLE_CURSOR_BACK |
SQL_SIMPLE_CURSOR_NEXT |
SQL_SIMPLE_CURSOR_RELOAD |
SQL_SIMPLE_CURSOR_LAST,
...
cursor_order => # (default: no defaults)
SQL_SIMPLE_ORDER_ASC |
SQL_SIMPLE_ORDER_DESC
...
limit => # (default: no defaults)
);
SelectSubQuery
$rc = SQL::SimpleOps->SelectSubQuery
(
# all fields valids for Select / SelectCursor
# see: Select and SelectCursor
);
Update
$rc = SQL::SimpleOps->Update
(
# list of tables
table => table_alias_name,
or
table => [ table1_alias, table2_alias, ... ],
# fiels list
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 SQL
Call
$rc = SQL::SimpleOps->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,
...
buffer_hashkey => column_name,
or
buffer_hashkey => [col1,col2,...],
...
buffer_arrayref => 0 | 1, # (default: 1)
buffer_hashindex => arrayreaf # (no defaults)
# 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)
);
General Methods
getAliasCols
$realname_cols = SQL::SimpleOps->getAliasCols(alias_table,alias_cols);
getAliasTable
$realname_table = SQL::SimpleOps->getAliasCols(alias_table);
getDBH
$dbh = SQL::SimpleOps->getDBH();
getLastCursor
$hash_ref = SQL::SimpleOps->getLastCursor();
getLastSave
$last_saved_logfile = SQL::SimpleOps->getLastSave();
getLastSQL
$last_sql_command = SQL::SimpleOps->getLastSQL();
getMessage
$message = SQL::SimpleOps->getMessage();
getRC
$rc = SQL::SimpleOps->getRC();
getRows
$rows = SQL::SimpleOps->getRows();
getWhere
$rc = SQL::SimpleOps->getWhere
(
# list of tables
table => table,
or
table => [ table1, table2, ... ],
# where clause
where => where_clause, # (se: Where Clause)
# return buffer
buffer => scalar_ref,
);
setDumper
$state = SQL::SimpleOps->setDumper( false | true ); (default: false)
SEE ALSO
DBD::mysql, DBD::Pg, DBD::SQLite, DBI
EXTERNAL REFERENCES
MySQL
MariaDB
Postgres
SQLite
SQL Wiki
W3C
ENDED
