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

       driver => mysql | mariadb | postgres | sqlite, # (no defaults)
       interface => interface_name,                   # (default: 'dbi')
       interface_options =>
       {
          ...   # driver additional options, see interface DBD::[driver]
       },


       # driver information, based predefined plugin

       db => dnsname,               # (no defaults)
       server => dbservername,      # (no defaults)
       port => tcpport_number,      # (optional)
       login => login_name,         # (optional)
       password => login_password,  # (optional)

       or                           # based without predefined plugin

       dsname => dsname,            # dsname string format, depends on the driver
       login => login_name,         # (depends on the driver)
       password => login_password,  # (depends on the driver)

       # tables definitions (no defaults)

       tables =>
       {
          table1_alias =>
          {
             name => real_table_name_on_database,
             cols =>
             {
                col1_alias_name => col1_real_name,
             },
          },
          table2_alias => { ... },
          ...
       },

       # generic options

       alias_with_as => 0 | 1,   # (default: 0, driver=pg the default: 1)
       commit => 0 | 1,          # (default: 0 )
       connect => 0 | 1,         # (default: 1 )
       ignore_plugin => 0 | 1,   # (default: 0)
       quote => character,       # (default: apostrophe)

       # 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')

       message_warning_off => 0 | 1,       # (default: 0)

       # sql_save options (default: SQL_SIMPLE_CMD_OFF)

       sql_save => SQL_SIMPLE_CMD_OFF | SQL_SIMPLE_CMD_ON | SQL_SIMPLE_CMD_ALL,

       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)
       sql_save_bydate => 0 | 1,  # (default: 0)
    );

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 }, ... ],
       or
       fields => "*"

       # where clause

       where => [ condition1, ... ],   # (see below: WHERE)

       # group by options

       group_by => col_alias,
       or
       group_by => [ col1_alias, col2_alias, ... ],

       # having clause

       having => [ condition1, ... ],  # (see below: HAVING)

       # 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)
       buffer_reverse => 0 | 1,        # (default: 0)

       # 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_key => [ col_alias1, ... col_alias2 ],
       ...
       cursor_info = array_ref | hash_ref | scalar_ref>,  # (no defaults)
       ...
       cursor_command =>                                  # (default: TOP)
          SQL_SIMPLE_CURSOR_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 => value,                                    # (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

       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)
       buffer_reverse => 0 | 1,         # (defaunt: 0)

       # 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,

       # return buffer

       buffer => scalar_ref,
    );

 setDumper
    $state = SQL::SimpleOps->setDumper( false | true );  # (default: false)

SEE ALSO

DBI, DBD::mysql, DBD::Pg, DBD::SQLite

EXTERNAL REFERENCES

MySQL
MariaDB
Postgres
SQLite
SQL Wiki
W3C

ENDED