NAME

SQL::SimpleOps - SQL Simple Operations

PREFACE

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/restrictions and the EXTENDED EXAMPLES to see some use cases.

I wish it to be useful in the development of your applications.

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

       # 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_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 =>                                                 # (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)

DESCRIPTION

This module was created to execute basic SQL commands in a database engine, where it proposes the most common and basic operations with low parameter complexity.

The module allow switch between database engine without recoding. It makes the necessary adjusments in SQL statement according the database engine in use, reducing the complexity in the applications's code.

The current version has built-in support for the databases: MySQL, MariaDB, Postgres and SQLite3.

However, the module does not convert the fields format in the databases, where each one has its restrictions and rules.

By definition, we assume that switching databases will use supported fields between the engines.

By default, evey invoked command will be effectively executed in database. To disable the execution use make_only option.

All parameters set in the Constructor (method new) are defined as Global Parameters, and some options can be enable/disable by the Methods, for more information see "Global Options".

All executed commands can saved in flat files for Debugging and/or Control and/or Recovery purposes, for more infomration se "Global Options".

All implemented methods deliver standards return code, where can be:

• rc < 0, Parameters and/or options have syntax errors;

• rc = 0, SQL command successful executed;

• rc = 1, SQL command execute with errors;

• rc = 2, SQL command successful executed without match.

The message details and return code must be extracted by:

• For Constructor (new) Method: Use SQL::SimpleOps::err and SQL::SimpleOps::errstr values.

• For Other Methods: Use getRC and getMessage modules (recommended).

By default the module send the messages on STDERR, unless that requested by the application (see message_log option in "Global Options").

Constructor Initialization

The constructor (new method) will load and validate all options and initialize the controls.

The execution of the constructor is a mandatory requirement for the others methods. You must load the constructor and use the object address created by it to run the methods.

  my $mymod = SQL::SimpleOps->new ( ... );
  ...
  my $rc = $mymod->[methods] ( ... );
  ...

However, in this document have references and examples using the format "SQL::SimpleOps->[method]" just for simple illustration, but this format is not supported in encoding. (see EXAMPLES)

The controls can be initialized by the Options Arguments and/or "Configuration File". The module will load the arguments and at end will apply the "Configuration File" changes, where the "Configuration File" is the highest priority value over the arguments.

Configuration File

The config file is not mandatory.

It is the resource used to load the options to initialize the constructor and was created to minize the risk of code changes. You can specify all options and provide changes without recoding.

The format of config file must be JSON and respect the struct below.

  {
    "db":"database_name",
    "schema":"schema-name",
    "driver":"mysql | mariadb | postgres | sqlite",
    "login":"login",
    "password":"password",
    "server":"hostname",
    "commit":"0 | 1",
    "connect":"0 | 1",
    "interface":"dbi",
    "interface_options":{
        "options1":"value",
        "options2":"value",
        "...":"..."                                     <- no comma at end is allowed
     },
    "message_log":"value",
    "message_syslog_facility":"string",
    "message_syslog_service":"string",
    "port":"tcp_port",
    "quote":"apostrophe|quote",
    "sql_save":"0 | 1",
    "sql_save_ignore":"0 | 1",
    "sql_save_name":"string",
    "sql_save_dir":"string",
    "sql_save_bydate":"0 | 1",
    "tables":{
       "table1_alias":{
           "cols":{
              "col1_alias_name":"col1_real_name",
              "...":"..."                               <- no comma at end is allowed
           },
          "name":"real_table_name_on_database"
        },
       "...":"..."                                      <- no comma at end is allowed
     }                                                  <- no comma at end is allowed
  }

NOTE: The example listed above is a simple illustration, where you do not need to parameterize all the options. you must need use only the mandatory options for your application.

REMEMBER: The JSON format does not allow comma at the end of last field in braces.

Supported Arguments Format

The table and fields can be configured using the following formats:

  select( table => "table_name", ... );

  select( table => [ "table_name1", ... ], ... );

  my $table = "table_name";
  select( table => $table, ... );

  my $table = [ "table_name1", ... ];
  select( table => $table, ... );

  my @table = [ "table_name", ... ];
  select( table => @table, ... );

  my @table = ( "table_name", ... );
  select( table => \@table, ... );

The where, order_by and group_by can be configured using the following formats:

  select( where => [ where1, ... ], ... );

  my $where = [ where1, ... ];
  select( where => $where, ... );

  my @where = [ where1, ... ];
  select( where => @where, ... );

  my @where = ( where1, ...  );
  select( where => \@where, ... );

Loading Data into Applications (buffer, buffer_options, buffer_hashkey and buffer_arrayref options)

Exists two different process to manipulate the application data with the module. The first update data into the database (Insert, Delete and Update) and the second get data from the database (Select and SelectCursor).

The first process the data will be sent by arguments, where the module will convert to SQL command and run it.

The second process the data will be load into variable sent by arguments, where the module will store the data.

Example1: Putting the data into the database.

  SQL::SimpleOps->Insert
  (
     table => "my_table",
     fields =>                                  # (assign by hash)
     {
       id => 1,
       name => "my_name",
       ...
     }
  );
  or
  SQL::SimpleOps->Insert
  (
     table => "my_table",
     fields => [ id, name ],                    # (array of fields)
     values => [ [ 1, "my_name"] ],             # (array into array)
  );

NOTE: The return code must be SQL_SIMPLE_RC_OK for successful or SQL_SIMPLE_RC_ERROR if any errors.

Example2: Getting the data into the database using the buffer option.

  SQL::SimpleOps->Select
  (
     table => "my_table",
     where => [ id => 1 ],
     ...
     buffer => hash_ref | array_ref | scalar_ref | callback_ref,
  );

NOTE: The return code must be SQL_SIMPLE_RC_OK for successful or SQL_SIMPLE_RC_ERROR if any errors.

We have four different types of return values for the buffer option, can be:

Extracting Single Row

The Single Extraction consists in queries that return only one row. The best type of buffer is the hash_ref, where each column will be the index key in the hash.

Example3:

  SQL::SimpleOps->Select( buffer => \%my_buffer, ... );

  foreach my $id(sort(keys(%my_buffer)))
  {
     print "id=".$id." -- value=".$my_buffer{$id}."\n";
  }

BEWARE: Only the last row will be returned for queries with multiple rows.

Extracting Multiple Rows

The Multiple Extraction consists in queries that return tow or more rows. The best type of buffer is the array_ref, where each line will be a hash_ref for each extracted row.

Example4:

  SQL::SimpleOps->Select( buffer => \@my_buffer, ... );

  foreach my $ref(@my_buffer)
  {
     foreach my $id(sort(keys(%{$my_buffer{$ref}})))
     {
        print "id=".$id." -- value=".$my_buffer{$ref}->{$id}."\n";
     }
  } 

Extracting Multiple Rows as Single List

The Single List consists in queries that return an unique field and multiple rows. The best typ of buffer is the array_ref with buffer_arrayref swtiched to OFF (buffer_arrayref=0).

Example5:

  SQL::SimpleOps->Select( buffer => \@my_buffer, buffer_arrayref => 0, ... );

  print "values=".join(", ",@my_buffer)."\n";

Extracting Multiple Rows using field as index

The Multiple Extraction consists in queries that return tow or more rows. The best type of buffer is the hash_ref with buffer_hashkey option, where each line will be indexed by the value of field key.

Example6:

  SQL::SimpleOps->Select( buffer => \%my_buffer, buffer_hashkey => "id", fields => [ "id", ... ], ... );

  foreach my $id(sort(keys(%my_buffer)))
  {
     foreach my $field(sort(keys(%{$my_buffer{$id}})))
     {
        print "id=".$id." -- ".$field."=".$my_buffer{$id}{$field}."\n";
     }
  } 

NOTE: The buffer_hashkey value must be in the command field list.

NOTE: The buffer_hashkey will not be found in the field list results.

REMEMBER: Using 'hash buffers' the ordering is compromised. Hash tables are not ordered and the c<order_by> option does not make sense in this case.

Example7:

  SQL::SimpleOps->Select( buffer => \%my_buffer, buffer_hashkey => ["key1","key2"], fields => [ "key1"," key2", ... ], ... );

  foreach my $key1(sort(keys(%my_buffer)))
  {
     foreach my $key2(sort(keys(%{$my_buffer{$key1})))
     {
        foreach my $field(sort(keys(%{$my_buffer{$key1}{$key2}})))
        {
           ...
        }
     }
  }

NOTE: The buffer_hashkey value must be in the command field list.

NOTE: The buffer_hashkey will not be found in the field list results.

Extracting Specfic Column in Single Row

The Single Extraction for specific Column in queries that return only one row/column. The best type of buffer is the scalar_ref, where the column will be stored.

Example8:

  SQL::SimpleOps->Select( buffer => \$my_scalar, ... );

  print "id=".$my_scalar."\n":

BEWARE: Only the last value will be returned for queries with multiple rows.

Extracting as HASH ordered

The HASH's extraction keys is not ordered and you can not scan in the real sequence in the table. Some cases you need scan in the sequence one HASH by the keys. The option buffer_hashindex is an arraydef with the lists of hashkeys, where the 0 (zero) until 'max' is the correct sequence in the table. This option can be used with buffer=hashref to index the real scan sequence of the table.

Example9:

  SQL::SimpleOps->Select( buffer => \%my_hash, buffer_hashkey => 'id', buffer_hashindex => \@my_index, ... );

  foreach my $key1(@my_index)
  {
        print "key: ".$key.", buffer: ".$my_hash{$key},"\n";

  }

REMEMBER: You can use this option if your buffer is a hashref, otherwise, use the buffer's option arrayref.

Complex Extractions

The most case you need provide a lot number of process before select the rows. The callback will be executed for each extracted rows and depending on the return code, the lines will buffered or ignored (no stored).

Example10:

  my @any_info;

  SQL::SimpleOps->Select
  (
     table => table1,
     fields => [ id, register, counter ],
     where => [ id => 1 ],
     buffer => \&subrot_1,
     buffer_options => \@any_info,
  );
  ...
  sub subrot_1()
  {
     my $ref = shift;                   # my hash of extracted row from table1
     my $any = shift;                   # my 'buffer_options' option.
     ...
     return SQL::SimpleOps->Select      # returing the code of call
     (
        table => table2,
        where => [ id => $ref->{register} ],
        buffer => \&subrot_2,
        buffer_options = $any,
     );
  }
  ...
  sub subrot_2()
  {
     my $ref = shift;           # my hash of extracted row from table2
     my $any = shift;           # my 'buffer_options' options created on table1
     ...
     return 1 if (...any condition..);  # rc != 0 will ignore the row
     ...
     push(@{any},$ref);         # putting the data into @any_info array
     return 0;
  };

NOTE: This example is not a real use case, it is simple sample for references.

SubQueries

The subquery is a select into select, available to create on merged ou cross reference list of data.

Example11:

  my @any_info;

  my $mymod = new SQL::SimplesOps(...);

  $mymod->SimpleOps->Select
  (
     table => table1,
     where => [ id => $mymod->SelectSubQuery( table => "table2", field => "my_tb2_id", where => [ ... ] ) ],
     buffer => @buffer,
  );
  ...

The following SQL Command will be executed:

  SELECT * FROM table1 WHERE id IN (SELECT my_tb2_id FROM table2 WHERE [ .... ] );

The results will be written into the arrayref (for this example).

NOTE: This example is not a real use case, it is simple sample for references.

Aliases Table

The can be used to abstract the real names on the tables and the columns, reducing the recoding.

The Aliases Table is not mandatory. See EXAMPLES

When mapped the Aliases Column these aliases will be the keys in the hashref buffers

Why should I? Some places disallow tables using generic names (out of the norms), in this case, you can use aliases to abstract the real name in your the databases.

REMEMBER: Do not use reserved words, like function names (etc), as aliases. This keywords are reserved and is not allowed to assign as aliases (SQL will be fail).

Format:

  my %contents =
  (
     aliases_table_0 =>                         # table without list of fields
     {
        name => realname_table_0,
     },
     aliases_table_1 =>                         # table with list of fields
     {
        name => realname_table_1,
        cols =>
        {
           aliases_col_1_table_1 => realname_col_1_table_1,
           aliases_col_2_table_1 => realname_col_2_table_1,
           ...
        }
     },
     ...
  );

Example1:

  my %contents =
  (
     users =>
     {
        name => "table_for_users",
        cols =>
        {
           _id   => 'fld_id',
           _name => 'fld_name',
           _desc => 'fld_description',
        },
     },
  );

  SQL::SimpleOps->Select ( table => "users", fields => [ "_id", "_name", "_desc" ], ... );
  or
  SQL::SimpleOps->Select ( table => "users", ... );     # without fields option

Results:

  SELECT fld_id _id, fld_name _name, fld_description _desc FROM table_for_users;
  or
  SELECT * FROM table_for_users;

NOTE: The '*' option will not assign alias names in the SQL Command.

NOTE: <NOTE:> You can mix your select with Alias Table Fields and non-Alias entries. but I must remember this: select in tables without the fields option will be limited to the definition of alias table, in this case, to obtain all fields you must impose fields = "*">:

  SQL::SimpleOps->Select ( ... fields => "*", ... );

Escape or Backslash

The escape operand (or backslash) is the format to force the methods to ignore the translating data as arguments. The data with escape information will passed to SQL command without check or decode.

To escape the data, you must use as prefix the backslash caracter ('\').

REMEMBER: The backslash is a control character in the Perl. To use you must insert double backslash.

You can escape the following options:

 SQL::SimpleOps->Select ( ... , fields => [ '\\my_by_pass_as_fields', ... ], ... );

 SQL::SimpleOps->Insert ( ... , conflict => { my_col => '\\my_bypass_as_value', }, ... );

 SQL::SimpleOps->Update ( ... , fields => { my_col => '\\my_bypass_as_value', }, ... );

 SQL::SimpleOps->getWhere ( ... , where => [ '\\my_bypass_as_col_name' => '\\my_bypass_as_value', ... ], ... );

NOTE: The escape is valid in the 'where' clause for Select, SelectCursor, Delete and Update command.

Example1: Select or SelectCursor

 SQL::SimpleOps->Select ( ... fields => [ '\\concat(col1,col2,col3)' ], table => tb1, ... );

Result:

 SELECT ... concat(col1,col2,col3) FROM tb1 ... ;

Example2: Insert

 SQL::SimpleOps->Insret ( ... table => tb1, conflict => { last_update => '\\concat(substr(col1_date,1,4),"-01-01")' ], ... );

Result:

 INSERT INTO tb1 ... ON DUPLICATE last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
 (mySQL / MariaDB engine)

 or
 INSERT INTO tb1 ... ON CONFLICT last_update = concat(substr(col1_date,1,4),"-01-01") ... ;
 (SQLite / Postgres engine)

Example3: Update

 SQL::SimpleOps->Update ( ... table => tb1, fields => { last_update => '\\concat(substr(col1_date,1,4),"-01-01")' ], ... );

Result:

 UPDATE tb1 ... SET last_update = concat(substr(col1_date,1,4),"-01-01") ... ;

Example4: getWhere

 SQL::SimpleOps->getWhere ( ... where => [ col1 => '\\my_expression', '\\my_col_expression' => my_value ], ... );

Result:

 ... col1 = my_expression AND my_col_expression = 'my_value' ...

NOTE: The 'my_value' will have quote as applied and 'no quotes' will be in escaped expressions.

Where Clause

We belive that the most basic situations is supported.

The Where Clause is be defined by array_ref values, 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), basd on this definition, the best choice is the array_ref format.

If any item (or sub-item) specifies hash_ref, the process is aborted with SQL_SIMPLE_RC_SYNTAX as the return code.

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

Example1:

  SQL::SimpleOps->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'               # use '%' if required

       fld130 => '\\concat(fld130,"xxxx")',             # ... fld130 = concat(fld130,"xxxx")    # without quote

       fld200 => '\\fld210',                            # ... fld200 = fld210           # without quote

       fld220 => [ "!", '\\fld230' ],                   # ... fld220 != fld240          # 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.

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".

Example6: Extract with functions

  SQL::SimpleOps->Select
  (
     fields => [ "count(*)" ],
     ...
  );

NOTE: The column must be single field.

Example7: Extract with functions

  SQL::SimpleOps->Select
  (
     fields => [ "max(col1)", "min(col1)", "count(col1)", "substr(col1,1,8)" ],
     ...
  );

NOTE: The column must be single field.

Example8: Escaping field, no validation will be.

  SQL::SimpleOps->Select
  (
     fields => [ "\\my_expression" ],
     ...
  );

NOTE: The '\' (backslash) will escape all validations.

Example9: Assigning alias field for specific select command.

  SQL::SimpleOps->Select
  (
     fields => [ { "field1" => "alias1" }, field2, ... ],
     ...
  );

NOTE: Use 'alias1' as buffer reference to get field1 data.

Example10: 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

Example11: 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, however, you can use additional conditions for this items.

  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 used '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. Now 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.

Examples:

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 used
  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

CONSTANTS

The constants was created to identify the values and actions for some options.

sql_save option

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

  my $mymod = SQL::SimpleOps->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::SimpleOps->Select( sql_save => 1, ... );         # (if global disabled)
  or
  SQL::SimpleOps->Select( sql_save => 0, ... );         # (if global enabled)

message_log option

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
  SQL_SIMPLE_LOG_SYS            # Log is enabled for syslog service
  SQL_SIMPLE_LOG_STD            # Log is enabled for STDERR (default)
  SQL_SIMPLE_LOG_ALL            # Log is enabled for STDERR/Syslog

  my $mymod = SQL::SimpleOps->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.

Default: SQL_SIMPLE_LOG_STD

NOTE: You can override (temporary) this option on each SQL execution (valid for all command).

  SQL::SimpleOps->Select( message_log => 1, ... );              # (if global disabled)
  or
  SQL::SimpleOps->Select( message_log => 0, ... );              # (if global enabled)

cursor_command option

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)
  SQL_SIMPLE_CURSOR_RELOAD      # Cursor Command Reload the Current Page (ascending order)

Default: no defaults

order_by option

The parameters can be used to determine the sort ordered on columns.

  SQL_SIMPLE_ORDER_OFF          # Order is disabled (for SelectCursor only)
  SQL_SIMPLE_ORDER_ASC          # Order is Ascending
  SQL_SIMPLE_ORDER_DESC         # Order is Descending

Default: 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.

Return Codes (all methods)

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::SimpleOps->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::SimpleOps->Select( ..., notfound => 1, buffer => \@mybuffer, ... ))
  {
     ## here your code to process sql condition error.
  }
  elsif (!SQL::SimpleOps->Rows() || !@mybyffer)
  {
     ## here your code to process no data on query.
  }
  else
  {
     ## here your code for process the data.
  }
  ...

Command Alias Process

This values are used with getAliasCols to define how to translate the name to a real name, where the translation depends on the action in question in the command, some parts of command the aliases is allowed and an others not.

  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 ...

PLUGINS

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
  PreFetch Method

NOTE: The methods are not required. If they are omitted, the module will ignore them.

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.

PreFetch Method

This method is called before the 'prepare operations'. It's can be used to provide the last changes or checks before the SQL call.

Plugin Constructor Method

The Plugin Constructor will receive the address of control data. This address can be changed or used to call the methods.

The constructor must be:

  # called by: SQL::SimpleOps::[interface]::[driver]->new ( sql_simple => $self );

  sub new()
  {
     my $class = shift; $class = ref($class) || $class || 'SQL::SimpleOps::[interface]::[driver]';
     my $self = {@};    # <-- sql_simple as argument
     bless($self,$class);
  }

MySQL/MariaDB

This plugins embeded and establish the dsname data, argument #1 from DBI->connect()

  SQL::SimpleOps::DBI::MySQL

  sub new()
  {
        my $class = shift; $class = ref($class) || $class || 'SQL::SimpleOps::DBI::MySQL';
        my $self = {@_};

        $self->{sql_simple}->{init}{plugin_id} = "MySQL";
        $self->{sql_simple}->{init}{schema} = 0;
        $self->{sql_simple}->{init}{test_server} = 1;
        $self->{sql_simple}->{init}{alias_with_as} = 0;

        bless($self,$class);
  }

  sub Open()
  {
     my $self = shift;
     my $argv = shift;

     my @options;
     push(@options,"database=".$self->{sql_simple}->{argv}{db}) if (defined($self->{sql_simple}->{argv}{db}) && $self->{sql_simple}->{argv}{db}ne "");
     push(@options,"host=".$self->{sql_simple}->{argv}{server}) if (defined($self->{sql_simple}->{argv}{server}) && $self->{sql_simple}->{argv}{server} ne "");
     push(@options,$self->{sql_simple}->{argv}{port}) if (defined($self->{sql_simple}->{argv}{port}) && $self->{sql_simple}->{argv}{port} ne "");
     $self->{sql_simple}->{argv}{dsname} = "DBI:mysql:".join(';',@options);

     return 0;
  }

NOTE: No more changes need.

Postgress

This plugins embeded and establish the dsname data, argument #1 from DBI->connect()

  SQL::SimpleOps::DBI::PG

  sub new()
  {
        my $class = shift; $class = ref($class) || $class || 'SQL::SimpleOps::DBI::PG';
        my $self = {@_};

        $self->{sql_simple}->{init}{plugin_id} = "PG";
        $self->{sql_simple}->{init}{schema} = 1;
        $self->{sql_simple}->{init}{test_server} = 1;
        $self->{sql_simple}->{init}{alias_with_as} = 1;

         bless($self,$class);
  }

  sub Open()
  {
     my $self = shift;
     my $argv = shift;

     my @options;
     push(@options,"dbname=".$self->{sql_simple}->{argv}{db}) if (defined($self->{sql_simple}->{argv}{db}) && $self->{sql_simple}->{argv}{db} ne "");
     push(@options,"host=".$self->{sql_simple}->{argv}{server}) if (defined($self->{sql_simple}->{argv}{server}) && $self->{sql_simple}->{argv}{server} ne "");
     push(@options,$self->{sql_simple}->{argv}{port}) if (defined($self->{sql_simple}->{argv}{port}) && $self->{sql_simple}->{argv}{port} ne "");

     $self->{sql_simple}->{argv}{dsname} = "DBI:Pg:".join(';',@options);
     return 0;
  }

NOTE: No more changes need.

SQLite

This plugins embeded and establish the dsname data, argument #1 from DBI->connect()

  SQL::SimpleOps::DBI::SQLite

  sub new()
  {
        my $class = shift; $class = ref($class) || $class || 'SQL::SimpleOps::DBI::SQLite';
        my $self = {@_};

        if ($self->{sql_simple}->{argv}{db} eq "" && $self->{sql_simple}->{argv}{dbfile} eq "")
        {
                $self->{sql_simple}->setMessage($self,"new",-1,"001");
                return undef;
        }
        $self->{sql_simple}->{init}{plugin_id} = "SQLite";
        $self->{sql_simple}->{init}{schema} = 0;
        $self->{sql_simple}->{init}{test_server} = 0;
        $self->{sql_simple}->{init}{alias_with_as} = 0;

        bless($self,$class);
  }

  sub Open()
  {
     my $self = shift;
     my $argv = shift;

     $self->{sql_simple}->{argv}{dbfile} = $self->{sql_simple}->{argv}{db}.".db" if (!defined($self->{sql_simple}->{argv}{dbfile}) || $self->{sql_simple}->{argv}{dbfile} eq "");
     $self->{sql_simple}->{argv}{dsname} = "DBI:SQLite:dbname=$self->{sql_simple}->{argv}{dbfile}";

     return 0;
  }

NOTE: No more changes need.

Custom

This sample show a simple struct to be create to support your addon.

The function PreFetch will be call befere the prepare operations where specias adjusts can be done.

  ## 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::SimpleOps::[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::SimpleOps::DBI::MyPlugin';
        my $self = {@_};

        # give my plugin name
        $self->{sql_simple}->{init}{plugin_id} = "MyPlugin";

        # use '1' if your db have schema option format
        $self->{sql_simple}->{init}{schema} = 1;

        # use '1' if your args must have server/tcport values
        $self->{sql_simple}->{init}{test_server} = 1;

        # use '1' if between your field/alias infomation have 'as' value
        $self->{sql_simple}->{init}{alias_with_as} = 0;

        bless($self,$class);
  }

  sub Open()
  {
        my $self = shift;
        my $argv = shift;

        ## sets the dsnam here
        ## $self->{sql_simple}->{argv}{dsname} = ...

        return 0;
  }

  sub Select()
  {
        my $self = shift;
        my $argv = shift;
        return 0;
  }

  sub SelectCursor()
  {
        my $self = shift;
        my $argv = shift;
        return 0;
  }

  sub Delete()
  {
        my $self = shift;
        my $argv = shift;
        return 0;
  }

  sub Update()
  {
        my $self = shift;
        my $argv = shift;
        return 0;
  }

  sub Call()
  {
        my $self = shift;
        my $argv = shift;
        return 0;
  }

  sub PreFetch()
  {
        my $self = shift;
        my $argv = shift;
        return 0;
  }

EXAMPLES

The following example allow simple test between the SQLite, MySQL/MariaDB and Postgres Databases (see: t/testFirst.t)

NOTE: For more examples see test modules (t/testSQL.t, t/testWhere.t and t/testDB.t) in source code.

To test you need:

 (a) Create a temporary database, as described below (SQLite, MySQL, MariaDB or Postgres);
 (b) Copy/Past the Source Code and;
 (c) Make the test.

Create SQLite Database

 # 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

Create MySQL/MariaDB Database

 # 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'@'localhost' IDENTIFIED BY 'password_read';
 CREATE USER 'user_update'@'localhost' IDENTIFIED BY 'password_update';

 GRANT SELECT ON test_db.* TO 'user_read'@'localhost';
 GRANT SELECT,INSERT,UPDATE,DELETE ON test_db.* TO 'user_update'@'localhost';

 EOF

Create Postgres Database

 # 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

Source Code

NOTE: Copy and Paste the following lines, until __END__:

  #!/usr/bin/perl
  #
  ## file t/testFirst.t
  #
  ## CoryRight (C) - Carlos Celso
  #

  ## load external libs

  use Getopt::Long;
  use SQL::SimpleOps;
  use Pod::Usage;
  use Test::More;

  ## defaults values

  our $PARM_DB = "test_db";
  our $PARM_DBFILE = "/tmp/test_db.db"; # for database im memory use: ":memory:" 
  our $PARM_SCHEMA = "test_schema";
  our $PARM_SERVER = "localhost";
  our $PARM_USER = "user_update";
  our $PARM_PASSWORD = "password_update";

  ## help or doit

  (@ARGV) ?
        &my_init() :
        note("$0 -drive=[driver] -db=[db] -schema=[sch] -server=[host] -port=[port] -user=[user] -password=[pwd]");

  done_testing();       # test done
  exit;

  ## test starter

  sub my_init()
  {
          ## parsing options

          our $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::SimpleOps module
          ## remember: the defauls values por RaiseError and PrintError is ZERO

          our $mymod = new SQL::SimpleOps
          (
                 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',
                           },
                        },
                 }
          );

          ## do it

          &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
  }

  ## 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, notfound => 1 );
      $mymod->Delete ( table => "my_slave", force => 1, notfound => 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__

Results

 # 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

 # perl /tmp/my_first_program.pl -driver=mysql
 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

 # perl /tmp/my_first_program.pl -driver=postgres
 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

EXTENDED EXAMPLES

This data consists in the list of test in 't/testSQL.t'

The test run two differnts context. The first use an "Aliases Table" as contents tables. The second does not use an "Aliases Table" (no aliases available).

Glossary

 fld_realX              Field's realname.
 fld_aliasX             Field's alias name.
 fld_noalias    Field without alias name.
 tab_realX              Table's realname.
 tab_aliasX             Table's alias name.
 tab_noalias    Table without alias name.
 bad_alias              Table without alias and not in table list.

Tests with "Aliases Table"

Aliases Table Definition

REMEMBER: The translation depends of two information, the table name and the field name: Correct field name and wrong table name will not be translated. Correct table name and wrong field name will not be translated.

        $mymod = new SQL::SimpleOps
        (
                ...
                tables =>
                {
                        tab_alias1 =>
                        {
                                name => 'tab_real1',
                                cols =>
                                {
                                        fld_alias1 => 'fld_real1',
                                        fld_alias2 => 'fld_real2',
                                        fld_aliasX => 'fld_realX',
                                },
                        },
                        tab_alias2 =>
                        {
                                name => 'tab_real2',
                                cols =>
                                {
                                        fld_alias1 => 'fld_real1',
                                        fld_alias2 => 'fld_real2',
                                        fld_aliasY => 'fld_realY',
                                },
                        },
                        tab_alias3 =>
                        {
                                name => 'tab_real3',
                        },
                },
                ...
        );

DELETE COMMANDS

 Delete( table => "tab_noalias", force => 1 )
 returns: DELETE FROM tab_noalias

 Delete( table => "tab_alias1", force => 1 )
 returns: DELETE FROM tab_real1

 Delete( table => "tab_real1", force => 1 )
 returns: DELETE FROM tab_real1

 Delete( table => "tab_alias1", force => 1 )
 returns: DELETE FROM tab_real1

 Delete( table => "tab_noalias", where => [ "fld_alias1" => 1 ] )
 returns: DELETE FROM tab_noalias WHERE fld_alias1 = '1'

 Delete( table => "tab_alias1", where => [ "fld_alias1" => "value1" ] )
 returns: DELETE FROM tab_real1 WHERE fld_real1 = 'value1'

 Delete( table => "tab_real1", where => [ "fld_alias1" => "value1" ] )
 returns: DELETE FROM tab_real1 WHERE fld_real1 = 'value1'

 Delete( table => "tab_alias1", where => [ "fld_noalias1" => 1 ] )
 returns: DELETE FROM tab_real1 WHERE fld_noalias1 = 'value1'

 Delete( table => "tab_alias1", where => [ "fld_real1" => 1 ] )
 returns: DELETE FROM tab_real1 WHERE fld_real1 = 'value1'

 Delete( table => "tab_noalias", where => [ "tab_noalias.fld_alias1" => "value1" ] )
 returns: DELETE FROM tab_noalias WHERE fld_alias1 = 'value1'

 Delete( table => "tab_alias1", where => [ "tab_alias1.fld_alias1" => "value1" ] )
 returns: DELETE FROM tab_real1 WHERE fld_real1 = 'value1'

 Delete( table => "tab_real1", where => [ "tab_alias1.fld_alias1" => "value1" ] )
 returns: DELETE FROM tab_real1 WHERE fld_real1 = 'value1'

INSERT COMMANDS

 Insert( table => "tab_noalias", fields => { "fld_alias1" => "value1" } )
 returns: INSERT INTO tab_noalias (fld_alias1) VALUES ('value1')

 Insert( table => "tab_noalias", fields => [ "fld_alias1" ], values => [ "value1" ]
 returns: INSERT INTO tab_noalias (fld_alias1) VALUES ('value1')

 Insert( table => "tab_noalias", fields => { "fld_alias1" => "value1", "fld_alias2" => "value2" } )
 returns: INSERT INTO tab_noalias (fld_alias1,fld_alias2) VALUES ('value1','value2')

 Insert( table => "tab_noalias", fields => [ "fld_alias1","fld_alias2" ], values => [ "value1","value2" ] )
 returns: INSERT INTO tab_noalias (fld_alias1,fld_alias2) VALUES ('value1','value2')

 Insert( table => "tab_noalias", fields => [ "fld_alias1" ], values => [ "value1","value2" ] )
 returns: INSERT INTO tab_noalias (fld_alias1) VALUES ('value1'),('value2')

 Insert( table => "tab_noalias", fields => [ "tab_noalias.fld_alias1" ], values => [ "value1" ] )
 returns: INSERT INTO tab_noalias (fld_alias1) VALUES ('value1')

 Insert( table => "tab_noalias", fields => [ "bad_table.fld_alias1" ], values => [ "value1" ] )
 returns: INSERT INTO tab_noalias (bad_table.fld_alias1) VALUES ('value1')

 Insert( table => "tab_alias1", fields => { "fld_alias1" => "value1" } )
 returns: INSERT INTO tab_real1 (fld_real1) VALUES ('value1')

 Insert( table => "tab_real1", fields => { "fld_alias1" => "value1" } )
 returns: INSERT INTO tab_real1 (fld_real1) VALUES ('value1')

 Insert( table => "tab_alias1", fields => { "tab_alias1.fld_alias1" => "value1" } )
 returns: INSERT INTO tab_real1 (fld_real1) VALUES ('value1')

 Insert( table => "tab_real1", fields => { "tab_real1.fld_alias1" => "value1" } )
 returns: INSERT INTO tab_real1 (fld_real1) VALUES ('value1')

 Insert( table => "tab_alias1", fields => { "tab_alias1.fld_real1" => "value1" } )
 returns: INSERT INTO tab_real1 (fld_real1) VALUES ('value1')

 Insert( table => "tab_real1", fields => { "tab_real1.fld_alias1" => "value1" } )
 returns: INSERT INTO tab_real1 (fld_real1) VALUES ('value1')

SELECT COMMAND

 Select( table => "tab_noalias", fields => "fld_alias1" )
 returns: SELECT fld_alias1 FROM tab_noalias

 Select( table => "tab_noalias", fields => [ "fld_alias1" ] )
 returns: SELECT fld_alias1 FROM tab_noalias

 Select( table => "tab_alias1", fields => "fld_alias1" )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ "fld_alias1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ "fld_real1" ] )
 returns: SELECT fld_real1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ "fld_real1" ] )
 returns: SELECT fld_real1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ "fld_noalias" ] )
 returns: SELECT fld_noalias FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ "fld_noalias" ] )
 returns: SELECT fld_noalias FROM tab_real1 tab_alias1

 Select( table => "tab_noalias", fields => "tab_noalias.fld_alias1" )
 returns: SELECT tab_noalias.fld_alias1 FROM tab_noalias

 Select( table => "tab_alias1", fields => [ "tab_alias1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ "tab_real1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ "tab_alias1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ "tab_real1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ "bad_alias1.fld_alias1" ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_noalias", fields => [ "bad_alias1.fld_alias1" ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_noalias", fields => [ "bad_real1.fld_alias1" ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_noalias1","tab_noalias2"], fields => "fld_alias1" )
 returns: SELECT fld_alias1 FROM tab_noalias1, tab_noalias2

 Select( table => ["tab_noalias1","tab_noalias2"], fields => [ "fld_alias1" ] )
 returns: SELECT fld_alias1 FROM tab_noalias1, tab_noalias2

 Select( table => ["tab_alias1","tab_alias2"], fields => "fld_alias1" )
 note: The fld_alias1 defined in both tables, cannot translate, us: [table].[field]
 returns: SELECT fld_alias1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_real1","tab_real2"], fields => "fld_alias1" )
 note: The fld_alias1 defined in both tables, cannot translate, us: [table].[field]
 returns: SELECT fld_alias1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_noalias1","tab_noalias2"], fields => "tab_noalias3.fld_alias1 )
 message: 010E [select] Field 'tab_noalias3.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_real1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_real1","tab_real2"], fields => [ "tab_real1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_real1","tab_real2"], fields => [ "bad_alias1.fld_alias1" ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_real1","tab_real2"], fields => [ "bad_real1.fld_alias1" ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_noalias1","tab_noalias2"], fields => [ "bad_alias1.fld_alias1" ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_noalias1","tab_noalias2"], fields => [ "bad_real1.fld_alias1" ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_noalias", fields => [ {"fld_alias1"=>"my1"} ] )
 returns: SELECT fld_alias1 my1 FROM tab_noalias

 Select( table => "tab_alias1", fields => [ {"fld_alias1"=>"my1"} ] )
 returns: SELECT fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ {"fld_alias1"=>"my1"} ] )
 returns: SELECT fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ {"fld_real1"=>"my1"} ] )
 returns: SELECT fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ {"fld_real1"=>"my1"} ] )
 returns: SELECT fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ {"fld_noalias"=>"my1} ] )
 returns: SELECT fld_noalias my1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ {"fld_noalias"=>"my1"} ] )
 returns: SELECT fld_noalias my1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ {"tab_alias1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => [ {"tab_real1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ {"tab_alias1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ {"tab_real1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1

 Select( table => "tab_real1", fields => [ {"bad_alias1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_real1", fields => [ {"bad_real1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_noalias", fields => [ {"bad_alias1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_noalias", fields => [ {"bad_real1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_noalias1","tab_noalias2"], fields => [ {"fld_alias1"=>"my1"} ] )
 returns: SELECT fld_alias1 my1 FROM tab_noalias1, tab_noalias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ {"fld_real1"=>"my1"} ] )
 returns: SELECT fld_real1 my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_real1","tab_real2"], fields => [ {"fld_real1"=>"my1"} ] )
 returns: SELECT fld_real1 my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ {"fld_noalias"=>"my1"} ] )
 returns: SELECT fld_noalias my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => "tab_real1", fields => [ {"fld_noalias"=>"my1"} ] )
 returns: SELECT fld_noalias my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ {"tab_alias1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ {"tab_real1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ {"tab_alias1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_real1","tab_real2"], fields => [ {"tab_real1.fld_alias1"=>"my1"} ] )
 returns: SELECT tab_alias1.fld_real1 my1 FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_real1","tab_real2"], fields => [ {"bad_alias1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_real1","tab_real2"], fields => [ {"bad_real1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_noalias1","tab_noalias2"], fields => [ {"bad_alias1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_alias1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => ["tab_noalias1","tab_noalias2"], fields => [ {"bad_real1.fld_alias1"=>"my1"} ] )
 message: 010E [select] Field 'bad_real1.fld_alias1' not mapped in table list
 note: *** no SQL command returns -- invalid arguments ***

 Select( table => "tab_noalias1" )
 returns: SELECT * FROM tab_noalias1

 Select( table => "tab_alias1" )
 returns: SELECT fld_real1 fld_alias1, fld_real2 fld_alias2, fld_realX fld_aliasX FROM tab_real1 tab_alias1

 Select( table => "tab_real1" )
 returns: SELECT fld_real1 fld_alias1, fld_real2 fld_alias2, fld_realX fld_aliasX FROM tab_real1 tab_alias1

 Select( table => "tab_alias1", fields => "*" )
 returns: SELECT * FROM tab_real1 tab_alias1

 Select( table => ["tab_alias1","tab_alias2"], fields => "*" )
 returns: SELECT * FROM tab_real1 tab_alias1, tab_real2 tab_alias2

 Select( table => ["tab_alias1","tab_noalias"], fields => "*" )
 returns: SELECT * FROM tab_real1 tab_alias1, tab_noalias

 Select( table => ["tab_noalias1","tab_noalias2"] )
 returns: SELECT * FROM tab_noalias1, tab_noalias2

SELECT AND GROUPBY COMMAND

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "fld_alias1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY fld_real1

 Select( table => "tab_alias1", fields => [ "fld_real1" ], group_by => [ "fld_real1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY fld_real1

 Select( table => "tab_alias1", fields => [ "fld_real1" ], group_by => [ "fld_noalias" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY fld_noalias

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "fld_alias1","fld_alias2" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY fld_real1, fld_real2

 Select( table => "tab_alias1", fields => [ "fld_real1" ], group_by => [ "fld_real1","fld_real2" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY fld_real1, fld_real2

 Select( table => "tab_alias1", fields => [ "fld_real1" ], group_by => [ "fld_noalias1","fld_noalias2" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY fld_noalias1, fld_noalias2

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "tab_alias1.fld_alias1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY tab_alias1.fld_real1

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "tab_alias1.fld_real1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY tab_alias1.fld_real1

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "tab_real1.fld_alias1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY tab_alias1.fld_real1

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "tab_real1.fld_real1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY tab_alias1.fld_real1

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], group_by => [ "tab_real1.fld_noalias1" ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 GROUP BY tab_alias1.fld_noalias1

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "fld_alias1","fld_alias2" ], group_by => [ "tab_real1.fld_alias1","tab_real2.fld_alias2" ] )
 note: The fld_alias1 & fld_alias2 defined in both tables, cannot translate, us: [table].[field]
 returns: SELECT fld_alias1, fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 GROUP BY tab_alias1.fld_real1, tab_alias2.fld_real2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "fld_alias1","fld_alias2" ], group_by => [ "tab_real1.fld_real1","tab_real2.fld_real2" ] )
 note: The fld_alias1 & fld_alias2 defined in both tables, cannot translate, us: [table].[field]
 returns: SELECT fld_alias1, fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 GROUP BY tab_alias1.fld_real1, tab_alias2.fld_real2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "fld_alias1","fld_alias2" ], group_by => [ "tab_real1.fld_noalias1","tab_real2.fld_noalias2" ] )
 note: The fld_alias1 & fld_alias2 defined in both tables, cannot translate, us: [table].[field]
 returns: SELECT fld_alias1, fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 GROUP BY tab_alias1.fld_noalias1, tab_alias2.fld_noalias2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ {"fld_alias1"=>"my1"},{"fld_alias2"->"my2"} ], group_by => [ "tab_alias1.my1","tab_alias1.my2" ] )
 warning: translate the table name 'tab_real1' but 'my2' is not assigned in same table -- DOT NOT USE
 returns: SELECT fld_alias1 my1, fld_alias2 my2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 GROUP BY tab_alias1.my1, tab_alias1.my2

SELECT AND ORDERBY COMMAND

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"fld_alias1"=>"asc"} ] )
 note: lower case is supported
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY fld_real1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"fld_alias1"=>"ASC"} ] )
 note: upper case is supported
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY fld_real1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"fld_alias1"=>"AsC"} ] )
 note: mixed case is supported
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY fld_real1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"fld_real1"=>"asc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY fld_real1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"fld_noalias1"=>"asc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY fld_noalias1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"fld_alias1"=>"desc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY fld_real1 DESC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_alias1.fld_real1"=>"asc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_alias1.fld_real1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_real1.fld_real1"=>"desc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_alias1.fld_real1 DESC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_alias1.fld_real1"=>"desc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_alias1.fld_real1 DESC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_real1.fld_alias1"=>"desc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_alias1.fld_real1 DESC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_noalias.fld_noalias1"=>"asc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_noalias.fld_noalias1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_noalias.fld_alias1"=>"asc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_noalias.fld_alias1 ASC

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], order_by => [ {"tab_noalias.fld_real1"=>"asc"} ] )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 ORDER BY tab_noalias.fld_real1 ASC

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1","tab_alias2.fld_alias2" ], order_by => [ {"tab_alias1.fld_alias1"=>"asc"},{"tab_alias2.fld_alias2"=>"asc"} ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1, tab_alias2.fld_real2 fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 ORDER BY tab_alias1.fld_real1 ASC, tab_alias2.fld_real2 ASC

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1","tab_alias2.fld_alias2" ], order_by => [ {"tab_real1.fld_alias1"=>"asc"},{"tab_real2.fld_alias2"=>"asc"} ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1, tab_alias2.fld_real2 fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 ORDER BY tab_alias1.fld_real1 ASC, tab_alias2.fld_real2 ASC

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1","tab_alias2.fld_alias2" ], order_by => [ "tab_real1.fld_alias1","tab_real2.fld_alias2" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1, tab_alias2.fld_real2 fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 ORDER BY tab_alias1.fld_real1, tab_alias2.fld_real2

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1","tab_alias2.fld_alias2" ], order_by => "tab_real1.fld_alias1" ] )
 returns: SELECT tab_alias1.fld_real1 fld_alias1, tab_alias2.fld_real2 fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 ORDER BY tab_alias1.fld_real1

 Select( table => ["tab_alias1","tab_alias2"], fields => [ "tab_alias1.fld_alias1","tab_alias2.fld_alias2" ], order_by => {"tab_real1.fld_alias1"=>"desc"} )
 returns: SELECT tab_alias1.fld_real1 fld_alias1, tab_alias2.fld_real2 fld_alias2 FROM tab_real1 tab_alias1, tab_real2 tab_alias2 ORDER BY tab_alias1.fld_real1 DESC

SELECT AND SUBQUERY COMMAND

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], where => [ "fld_alias1" => \$mymod->SelectSubQuery( table => "ta_alias2", fields => [ "fld_alias2" ], where => [ "fld_alias2" => "value2" ] ) } )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 WHERE fld_real1 IN (SELECT fld_real2 fld_alias2 FROM tab_real2 tab_alias2 WHERE fld_real2 = 'value2')

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], where => [ "fld_alias1" => [ "!", \$mymod->SelectSubQuery( table => "ta_alias2", fields => [ "fld_alias2" ], where => [ "fld_alias2" => "value2" ] ) ] } )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 WHERE fld_real1 NOT IN (SELECT fld_real2 fld_alias2 FROM tab_real2 tab_alias2 WHERE fld_real2 = 'value2')

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], where => [ "fld_alias1" => \$mymod->Select( table => "ta_alias2", fields => [ "fld_alias2" ], where => [ "fld_alias2" => "value2" ], subquery => 1 ) } )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 WHERE fld_real1 IN (SELECT fld_real2 fld_alias2 FROM tab_real2 tab_alias2 WHERE fld_real2 = 'value2')

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], where => [ "fld_alias1" => [ "!", \$mymod->SelectSubQuery( table => "ta_alias2", fields => [ "fld_alias2" ], where => [ "fld_alias2" => "value2" ] ) ] } )
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 WHERE fld_real1 NOT IN (SELECT fld_real2 fld_alias2 FROM tab_real2 tab_alias2 WHERE fld_real2 = 'value2')

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], where => [ "fld_alias1" => [ \$mymod->Select( table => "tab_alias2", fields => [ "fld_alias2" ], where => [ "fld_alias2" => "value2" ], subquery => 1 ), "..", \$mymod->Select( table => "tab_alias2", fields => [ "fld_alias2" ], where => [ "fld_alias2" => "value3" ], subquery => 1,), ], "fld_noalias1" => "value1" ] )
 note: The option 'subquery=1' is mandatory for Select option, the SQL command results is string as return
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 WHERE fld_real1 BETWEEN ((SELECT fld_real2 fld_alias2 FROM tab_real2 tab_alias2 WHERE fld_real2 = 'value2'),(SELECT fld_real2 fld_alias2 FROM tab_real2 tab_alias2 WHERE fld_real2 = 'value3')) AND fld_noalias1 = 'value1'

SELECTCURSOR COMMAND

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_NEXT, limit=>100 )
 note: Command=NEXT, Cursor is first(1) and last(100)
 returns: SELECT a, b, c FROM t1 WHERE a > '100' ORDER BY a ASC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_BACK, limit=>100 )
 note: Command=BACK, Cursor is first(101) and last(200)
 returns: SELECT a, b, c FROM t1 WHERE a < '101' ORDER BY a DESC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_RELOAD, limit=>100 )
 note: Command=RELOAD, Cursor is first(1) and last(100)
 returns: SELECT a, b, c FROM t1 WHERE a >= '1' ORDER BY a ASC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_LAST, limit=>100 )
 note: Command=LAST, Cursor is first(1) and last(100)
 returns: SELECT a, b, c FROM t1 ORDER BY a DESC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_TOP, limit=>0 )
 note: Command=TOP, Limit is ZERO
 returns: SELECT a, b, c FROM t1 ORDER BY a ASC

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100 )
 note: Option cursor_command is omited
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE ((t1.a > 'a') OR (t1.a = 'a' AND t2.c > '100')) ORDER BY t1.a ASC, t2.c ASC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100, cursor_command=>SQL_SIMPLE_CURSOR_TOP )
 note: The cursor_info is ignored
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 ORDER BY t1.a ASC, t2.c ASC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100, cursor_command=>SQL_SIMPLE_CURSOR_NEXT )
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE ((t1.a > 'a') OR (t1.a = 'a' AND t2.c > '100')) ORDER BY t1.a ASC, t2.c ASC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100, cursor_command=>SQL_SIMPLE_CURSOR_BACK 
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE ((t1.a < 'a') OR (t1.a = 'a' AND t2.c < '1')) ORDER BY t1.a DESC, t2.c DESC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100, cursor_command=>SQL_SIMPLE_CURSOR_LAST )
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 ORDER BY t1.a DESC, t2.c DESC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100, cursor_command=>SQL_SIMPLE_CURSOR_RELOAD
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE ((t1.a >= 'a') OR (t1.a = 'a' AND t2.c >= '1')) ORDER BY t1.a ASC, t2.c ASC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], where => ["t1.a" => "\t2.a"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], limit=>100 )
 note: Cursor command is omitted
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE t1.a = t2.a AND (((t1.a > 'a') OR (t1.a = 'a' AND t2.c > '100'))) ORDER BY t1.a ASC, t2.c ASC LIMIT 100

 SelectCursor( table=>["t1","t2"], fields => [ "t1.a","t1.b","t2.c"], where => ["t1.a" => "\t2.a"], cursor_info => \%cursor, cursor_key=>["t1.a","t2.c"], cursor_command=>SQL_SIMPLE_CURSOR_RELOAD, limit=>100 )
 returns: SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE t1.a = t2.a AND (((t1.a >= 'a') OR (t1.a = 'a' AND t2.c >= '1'))) ORDER BY t1.a ASC, t2.c ASC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_TOP, limit=>100 )
 note: Command=TOP, Cursor is empty
 returns: SELECT a, b, c FROM t1 ORDER BY a ASC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", limit=>100 )
 note: Command=TOP, Cursor is empty
 returns: SELECT a, b, c FROM t1 ORDER BY a ASC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_NEXT, limit=>100 )
 note: Command=NEXT, Cursor is empty
 returns: SELECT a, b, c FROM t1 ORDER BY a ASC LIMIT 100

 SelectCursor( table=>"t1", fields => [ "a","b","c"], cursor_info => \%cursor , cursor_key=>"a", cursor_command=>SQL_SIMPLE_CURSOR_BACK, limit=>100 )
 note: Command=BACK, Cursor is empty
 returns: SELECT a, b, c FROM t1 ORDER BY a DESC LIMIT 100

UPDATE COMMANDS

 Update( table => "tab_noalias", fields => { "fld_alias1" => "value1" }, force => 1
 returns: UPDATE tab_noalias SET fld_alias1 = 'value1'

 Update( table => "tab_noalias", fields => { "fld_alias1" => "value1", "fld_alias2" => "value2" }, force => 1 )
 returns: UPDATE tab_noalias SET fld_alias1 = 'value1', fld_alias2 = 'value2'

 Update( table => "tab_alias1", fields => { "fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_real1 = 'value1'

 Update( table => "tab_real1", fields => { "fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_real1 = 'value1'

 Update( table => "tab_alias1", fields => { "fld_alias1" => "value1", "fld_alias2" => "value2", }, force => 1 )
 returns: UPDATE tab_real1 SET fld_real1 = 'value1', fld_real2 = 'value2'

 Update( table => "tab_real1", fields => { "fld_alias1" => "value1", "fld_alias2" => "value2", }, force => 1 )
 returns: UPDATE tab_real1 SET fld_real1 = 'value1', fld_real2 = 'value2'

 Update( table => "tab_alias1", fields => { "fld_noalias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_noalias = 'value1'

 Update( table => "tab_real1", fields => { "fld_noalias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_noalias = 'value1'

 Update( table => "tab_noalias", fields => { "tab_noalias.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_noalias SET fld_alias1 = 'value1'

 Update( table => "tab_noalias", fields => { "tab_noalias.fld_alias1" => "value1", "tab_noalias.fld_alias2" => "value2" }, force => 1 )
 returns: UPDATE tab_noalias SET fld_alias1 = 'value1', fld_alias2 = 'value2'

 Update( table => "tab_alias1", fields => { "tab_alias1.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_real1 = 'value1'

 Update( table => "tab_real1", fields => { "tab_real1.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_real1 = 'value1'

 Update( table => "tab_alias1", fields => { "tab_alias1.fld_noalias" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_noalias = 'value1'

 Update( table => "tab_real1", fields => { "tab_real1.fld_noalias" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 SET fld_noalias = 'value1'

 Update( table => "tab_alias1", fields => { "bad_alias1.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1 SET bad_alias1.fld_alias1 = 'value1'

 Update( table => "tab_real1", fields => { "bad_alias1.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1 SET bad_alias1.fld_alias1 = 'value1'

 Update( table => ["tab_noalias1","tab_noalias2"], fields => { "tab_noalias1.fld_alias1" => "value1", "tab_noalias2.fld_alias2" => "value2", }, force => 1 )
 returns: UPDATE tab_noalias1, tab_noalias2 SET tab_noalias1.fld_alias1 = 'value1', tab_noalias2.fld_alias2 = 'value2'

 Update( table => ["tab_alias1","tab_noalias2"], fields => { "bad_alias1.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_noalias2 SET bad_alias1.fld_alias1 = 'value1'

 Update( table => ["tab_alias1","tab_alias2"], fields => { "tab_alias1.fld_alias1" => "value1", "tab_real2.fld_alias2" => "value2" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET tab_alias1.fld_real1 = 'value1', tab_alias2.fld_real2 = 'value2'

 Update( table => ["tab_alias1","tab_real2"], fields => { "tab_alias1.fld_alias1" => "value1", "tab_alias2.fld_alias2" => "value2" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET tab_alias1.fld_real1 = 'value1', tab_alias2.fld_real2 = 'value2'

 Update( table => ["tab_alias1","tab_alias2"], fields => { "tab_alias1.fld_alias1" => "value1", "tab_real2.fld_alias2" => "value2" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET tab_alias1.fld_real1 = 'value1', tab_alias2.fld_noalias = 'value2'

 Update( table => ["tab_alias1","tab_real2"], fields => { "tab_alias1.fld_alias1" => "value1", "tab_alias2.fld_alias2" => "value2" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET tab_alias1.fld_real1 = 'value1', tab_alias2.fld_noalias = 'value2'

 Update( table => ["tab_alias1","tab_real2"], fields => { "bad_alias.fld_alias1" => "value1" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET bad_alias.fld_alias1 = 'value1'

 Update( table => ["tab_alias1","tab_real2"], fields => { "bad_alias.fld_alias1" => "value1", "tab_alias1.fld_alias1" => "value2" }, force => 1 )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET bad_alias.fld_alias1 = 'value1', tab_alias1.fld_real1 = 'value2'

 Update( table => "tab_noalias", fields => { "fld_alias1" => "value2" }, where => [ "fld_alias1" => "value1" ] )
 returns: UPDATE tab_noalias SET fld_alias1 = 'value2' WHERE fld_alias1 = 'value1'

 Update( table => "tab_alias1", fields => { "fld_alias1" => "value2" }, where => [ "fld_alias1" => "value1" ] )
 returns: UPDATE tab_real1 SET fld_real1 = 'value2' WHERE fld_real1 = 'value1'

 Update( table => "tab_real1", fields => { "fld_alias1" => "value2" }, where => [ "fld_alias1" => "value1" ] )
 returns: UPDATE tab_real1 SET fld_real1 = 'value2' WHERE fld_real1 = 'value1'

 Update( table => "tab_alias1", fields => { "fld_alias1" => "value2" }, where => [ "fld_noalias1" => "value1" ] )
 returns: UPDATE tab_real1 SET fld_real1 = 'value2' WHERE fld_noalias1 = 'value1'

 Update( table => "tab_alias1", fields => { "fld_alias1" => "value2" }, where => [ "fld_real1" => 1 ] )
 returns: UPDATE tab_real1 SET fld_real1 = 'value2' WHERE fld_real1 = 'value1'

 Update( table => "tab_noalias", fields => { "fld_alias1" => "value2" }, where => [ "tab_noalias.fld_alias1" => "value1" ] )
 returns: UPDATE tab_noalias SET fld_alias1 = 'value2' WHERE fld_alias1 = 'value1'

 Update( table => "tab_alias1", fields => { "fld_alias1" => "value2" }, where => [ "tab_alias1.fld_alias1" => "value1" ] )
 returns: UPDATE tab_real1 SET fld_real1 = 'value2' WHERE fld_real1 = 'value1'

 Update( table => "tab_real1", fields => { "fld_alias1" => "value2" }, where => [ "tab_alias1.fld_alias1" => "value1" ] )
 returns: UPDATE tab_real1 SET fld_real1 = 'value2' WHERE fld_real1 = 'value1'

 Update( table => ["tab_noalias1","tab_noalias2"], fields => { "tab_noalias1.fld_alias1" => "value2", "tab_noalias2.fld_alias2" => "value1" }, where => [ "tab_noalias1.fld_alias1" => "value1", "tab_noalias2.fld_alias2" => "value2" ] )
 returns: UPDATE tab_noalias1, tab_noalias2 SET tab_noalias1.fld_alias1 = 'value2', tab_noalias2.fld_alias2 = 'value1' WHERE tab_noalias1.fld_alias1 = 'value1' AND tab_noalias2.fld_alias2 = 'value2'

 Update( table => ["tab_alias1","tab_alias2"], fields => { "tab_alias1.fld_alias1" => "value2", "tab_alias2.fld_alias2" => "value1" }, where => [ "tab_alias1.fld_alias1" => "value1", "tab_alias2.fld_alias2" => "value2" ] )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET tab_alias1.fld_real1 = 'value2', tab_alias2.fld_real2 = 'value1' WHERE tab_alias1.fld_real1 = 'value1' AND tab_alias2.fld_real2 = 'value2'

 Update( table => ["tab_real1","tab_real2"], fields => { "tab_real1.fld_alias1" => "value2", "tab_real2.fld_alias2" => "value1" }, where => [ "tab_real1.fld_alias1" => "value1", "tab_real2.fld_alias2" => "value2" ] )
 returns: UPDATE tab_real1 tab_alias1, tab_real2 tab_alias2 SET tab_alias1.fld_real1 = 'value2', tab_alias2.fld_real2 = 'value1' WHERE tab_alias1.fld_real1 = 'value1' AND tab_alias2.fld_real2 = 'value2'

Tests without "Aliases Table"

DELETE COMMANDS

 Delete( table=>"t1", where => [ fld => 123 ] )
 returns: DELETE FROM t1 WHERE fld = '123'

INSERT COMMANDS

 Insert( table=>"t1", fields => { a => 1, b => 2, c => 3 } )
 returns: INSERT INTO t1 (a,b,c) VALUES ('1','2','3')

 Insert( table=>"t1", fields => [ "a","b","c" ], values => [ 1,2,3 ] )
 returns: INSERT INTO t1 (a,b,c) VALUES ('1','2','3')

SELECT COMMANDS

 Select( table=>"t1", fields => [ "a","b","c"] )
 returns: SELECT a, b, c FROM t1

 Select( table=>"t1", fields => [ "a","b","c"], where => [ d=>4 ] )
 returns: SELECT a, b, c FROM t1 WHERE d = '4'

 Select( table=>"t1", fields => [ "a","b","c"], where => [ d=>4, e=>5 ] )
 returns: SELECT a, b, c FROM t1 WHERE d = '4' AND e = '5'

 Select( table=>"t1", fields => [ "a","b","c"], where => [ d=>'\substr(e,1,8)' ] )
 returns: SELECT a, b, c FROM t1 WHERE d = substr(e,1,8)

 Select( table=>["t1","t2"], fields => [ "t1.a","t2.b" ], where => [ "t1.a" => "\t2.b" ] )
 returns: SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.b

 Select( table=>"t1", fields => [ {"a"=>"aa"} ], where => [ 'a' => '0' ] )
 returns: SELECT a aa FROM t1 WHERE a = '0'

 Select( table=>"t1", fields => [ {"t1.a"=>"aa"} ], where => [ 't1.a' => '0' ] )
 returns: SELECT t1.a aa FROM t1 WHERE a = '0'

 Select( table=>["t1","t2"], fields => [ {"t1.a"=>"aa"}, {"t2.b"=>"bb"} ], where => [ "t1.a" => "\t2.b" ] )
 returns: SELECT t1.a aa, t2.b bb FROM t1, t2 WHERE t1.a = t2.b

 Select( table=>"t1", fields => [ {"sum(a)"=>"a1"}, {"sum(t1.a)"=>"a2"}, {"\sum(a)"=>"a3"} ], where => [ 'a' => '0' ] )
 returns: SELECT sum(a) a1, sum(t1.a) a2, sum(a) a3 FROM t1 WHERE a = '0'

 Select( table=>"t1", fields => [ "distinct", "a" ] )
 note: Select with DISTINCT array sequence
 returns: SELECT DISTINCT a FROM t1

 Select( table=>"t1", fields => [ "distinct" => "a" ] )
 note: Select with DISTINCT based hash
 returns: SELECT DISTINCT a FROM t1

 Select( table=>"t1", fields => [ "count(*)" ] )
 returns: SELECT count(*) FROM t1

 Select( table=>"t1", fields => [ "max(t1.a)" ] )
 returns: SELECT max(t1.a) FROM t1

 Select( table=>"t1", fields => [ "max(a)" ] )
 returns: SELECT max(a) FROM t1

 Select( table=>"t1", fields => [ "substr(a,1,8)" ] )
 returns: SELECT substr(a,1,8) FROM t1

 Select( table=>"t1", fields => [ "\aaa.bbb.ccc" ] )
 returns: SELECT aaa.bbb.ccc FROM t1

 Select( table=>"t1", fields => [ "distinct","\aaa.bbb.ccc" ] )
 returns: SELECT DISTINCT aaa.bbb.ccc FROM t1

 Select( table=>["t1","t2"], fields => [ "t1.a","t2.b" ], where => [ 't1.a' => 't2.b' ], sql_save=>1 )
 note: SQL_SAVE enabled
 returns: SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = 't2.b'
 savefile: /tmp/2023/202310/20231016/sql.teste.20231016.4352.1
 savefile: removed

 Select( table=>"t1", order_by => "t1.a" )
 returns: SELECT * FROM t1 ORDER BY t1.a

 Select( table=>"t1", order_by => [ {"t1.a" => "asc"} ] )
 returns: SELECT * FROM t1 ORDER BY t1.a ASC

 Select( table=>"t1", order_by => [ {"t1.a" => "desc"} ] )
 returns: SELECT * FROM t1 ORDER BY t1.a DESC

 Select( table=>"t1", order_by => [ "t1.a", "t1.b" ] )
 returns: SELECT * FROM t1 ORDER BY t1.a, t1.b

 Select( table=>"t1", order_by => [ {"t1.a" => "asc"}, "t1.b" ] )
 returns: SELECT * FROM t1 ORDER BY t1.a ASC, t1.b

 Select( table=>"t1", order_by => [ "t1.a", {"t1.b"=>"desc"} ] )
 returns: SELECT * FROM t1 ORDER BY t1.a, t1.b DESC

 Select( table=>"t1", order_by => {"t1.b"=>"desc"} )
 returns: SELECT * FROM t1 ORDER BY t1.b DESC

 Select( table=>"t1", fields => [{"t1.abc"=>"_abc"},"t1.cde",{"t1.fgh"=>"_fgh"}], where => [ "_abc" => 123 ] )
 returns: SELECT t1.abc _abc, t1.cde, t1.fgh _fgh FROM t1 WHERE abc = '123'

 Select( table=>"t1", fields => [{"t1.abc"=>"_abc"},"t1.cde",{"t1.fgh"=>"_fgh"}], where => [ "_abc" => 123, "cde" => 234, "t1.abc" => 345] )
 returns: SELECT t1.abc _abc, t1.cde, t1.fgh _fgh FROM t1 WHERE abc = '123' AND cde = '234' AND abc = '345'

 Select( table=>["t1","t2"], fields => [{"t1.abc"=>"_abc"},"t1.cde",{"t2.fgh"=>"_fgh"},"t2.ijk"], where => [ "_abc" => 123, "cde" => 234, "t1.abc" => 345, "ijk" => 456] )
 returns: SELECT t1.abc _abc, t1.cde, t2.fgh _fgh, t2.ijk FROM t1, t2 WHERE t1.abc = '123' AND cde = '234' AND t1.abc = '345' AND ijk = '456'

 Select( table=>"t1", fields => [{"t1.abc"=>"_a"}], where => [ "substr(_a,1,4)" => 1234 ] )
 returns: SELECT t1.abc _a FROM t1 WHERE substr(abc,1,4) = '1234'

 Select( table=>"t1", fields => [{"t1.abc"=>"_a"}], where => [ "concat(substr(_a,1,3),1)" => 1231 ] )
 returns: SELECT t1.abc _a FROM t1 WHERE concat(substr(abc,1,4),1) = '1231'

 Select( table=>"t1", fields => [{"t1.abc"=>"_a"}], where => [ "func1(func2(_a))" => 1231 ] )
 returns: SELECT t1.abc _a FROM t1 WHERE func1(func2(abc)) = '1231'

 Select( table=>"t1", fields => [{"t1.abc"=>"_a"}], where => [ "func1(_a)" => 123, "func1(t1.abc)" => 456 ] )
 returns: SELECT t1.abc _a FROM t1 WHERE func1(abc) = '123' AND func1(t1.abc) = '456'

UPDATE COMMANDS

 Update( table=>"t1", fields => { a => 1, b => 2 }, where => [ c => [ "!", 3 ] ] )
 returns: UPDATE t1 SET a = '1', b = '2' WHERE c != '3'

 Update( table=>"t1", fields => { a => '\concat(a,"xxxx")' }, force => 1 )
 returns: UPDATE t1 SET a = concat(a,"xxxx")

MESSAGES

The module messages can be extracted from the SQL::SimpleOps:errstr or getMessage method.

001E [command] Database is missing

• Error: The database name value omitted or empty.

• Action: Check the Constructor Command and validate the db option.

    my $mymod = SQL::SimpleOps->new( db => dsname, ... );

• Command: Constructor (method new)

002E [command] Server is missing

• Error: The server name value omitted or empty.

• Action: Check the Constructor Command and validate the server option.

    my $mymod = SQL::SimpleOps->new( server => servername, ... );

• Command: Constructor (method new)

003E [command] Interface invalid

• 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:

    my $mymod = SQL::SimpleOps->new( interface => "dbi", ... );

• Command: Constructor (method new)

004E [command] The Database driver is omitted or empty

• 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:

    my $mymod = SQL::SimpleOps->new( driver=> 'mysql' or 'mariadb' or 'postgres' or 'sqlite3' , ... );

• Command: Constructor (method new)

005E [command] Table is missing or invalid

• Error: The table alias name omitted, empty or is not a single value.

• Action: Check the Command and validate the table option. The format depends of the command in use:

    SQL::SimpleOps->Select( table => aliases_table_name, ... );           # single select
    ot
    SQL::SimpleOps->Select( table => [ table1, table2, ... ], ... );              # merged selected

• Command: Select, Delete, Insert and Update

006E [command] Table invalid, must be single-value or array

• Error: The table alias name is not a single value or array value.

• Action: Check the Select Command and validate the table option.

    SQL::SimpleOps->Select( table => single_value_name, ... );            # single select
    or
    SQL::SimpleOps->Select( table => [ table1, table2, ... ], ... );              # merged select

• Command: Select and SelectCursor or getWhere Method

007E [command] Fields invalid, must be array

• Error: The list of fields is not array.

• Action: Check the Select Command and validate the fields option.

    SQL::SimpleOps->Select( fields => [ field1, field2, ... ], ... );

• Command: Select and SelectCursor

008E [command] Group_by invalid, must be single-value or array

• Error: The option group_by is not a single value or array value.

• Action: Check the Select Command and validente the group_by option. Example:

    SQL::SimpleOps->Select( group_by => col_name, ... );
    or
    SQL::SimpleOps->Select( group_by => [ col1_name, col2_name, ... ], ... );

• Command: Select and SelectCursor

009E [command] Order_by invalid, must be single-value or array-pairs

• Error: The option order_by is not a single value or array value.

• Action: Check the Select Command and validente the order_by option. Example:

    SQL::SimpleOps->Select( order_by => col_name, ... );
    or
    SQL::SimpleOps->Select(
       order_by => [ col1_name => order1, col2_name => order2, ... ],
       ...
    );

• Command: Select and SelectCursor

010E [command] Field '%s' not mapped in table list"

• Error: The field is using a non-valid table/field identifier.

• Action: Check the field syntax, we can have: Fields with a table identifier where it is not in the "Aliases Table" and is not in the command's table list. Fields without a table identifier that have the same "Aliases Table" for multiple tables.

    SQL::SimpleOps->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.
    # c) Backslash is mandatory.

• Command: Select and SelectCursor

012I [command] Key not found

• 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::SimpleOps->Select( notfound => 1, ... );

• Command: Select, Delete, Insert, Update

013E [command] Cursor is missing or invalid

• Error: The Cursor information is omitted or empty.

• Action: Check the cursor and validate the cursor option on the SelectCursor Command.

    SQL::SimpleOps->SelectCursor( cursor => value, ... );

• Command: SelectCursor

014E [command] Cursor-key is missing or invalid

• 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::SimpleOps->SelectCursor( cursor_key => value, ... );

• Command: SelectCursor

015E [command] Cursor Command invalid

• Error: The Cursor Command information is not invalid.

• Action: Check the cursor command and validade the cursor_command option. The option must be:

    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::SimpleOps->SelectCursor( cursor_command => command, ... );

• Command: SelectCursor

016W [command] Key is missing, option 'force' is required

• 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::SimpleOps->Delete( table => t1 );
  
    # correct command
    SQL::SimpleOps->Delete( table => t1, force => 1 );

• Command: Delete and Update

017E [command] Fields is missing

• Error: The List of Fields is omitted or empty.

• Action: Check the list of fields and validate the fields option.

    # rejected command
    SQL::SimpleOps->Insert( fields => col1, ... );
    or
    SQL::SimpleOps->Insert( fields => { col1 }, ... );
  
    # correct command
    SQL::SimpleOps->Insert( fields => { col1 => value, ... }, ... );

• Command: Insert and Update

018E [command] Fields Format error, must be hash-pairs or arrayref

• 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::SimpleOps->Update( fields => col1, ... );
    or
    SQL::SimpleOps->Update( fields => { col1 }, ... );
  
    # correct command
    SQL::SimpleOps->Update( fields => { col1 => value, ... }, ... );

• Command: Insert and Update

019E [command] Interface '[interface]::[driver]' missing

• 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

020E [command] Where Clause invalid

• 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::SimpleOps->Select( where => [], ... );
    or
    SQL::SimpleOps->Select( where => [ a => 1, b => [] ], ... );
  
    # correct where
    SQL::SimpleOps->Select( where => [ mycondition => myvalue ], ... );
    or
    SQL::SimpleOps->Select(
       where => [ a => 1, b => [ myvalue, ... ], ],
       ...
     );

• Command: Select, Delete, Insert and Update

021E [command] Where invalid, must be single-value or array

• Error: The where option or one subitem in the where option is not single value or array value.

• Action: Check the where option on the Command. see "Where Clause".

    # invalid where
    SQL::SimpleOps->Select( where => { a => 1 }, ... );
  
    # correct where
    SQL::SimpleOps->Select( where => [ a => 1 ], ... );

• Command: Select, Delete, Insert and Update

022E [command] Database is not open

• 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

023E [command] SQL Command is missing

• Error: The SQL Command is omitted or empty.

• Action: Check the SQL Command and validate the command option on the Call Command.

    SQL::SimpleOps->Call( command => my-sql-command, ... );

• Command: Call

024E [command] Buffer Type invalid, must be hashref, arrayref, scalaref or callback_ref

• 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::SimpleOps->Call( buffer => \%myHash, ... );
  
    # returning on array_ref
    SQL::SimpleOps->Call( buffer => \@myArray, ... );
  
    # returning on scalar_ref
    SQL::SimpleOps->Call( buffer => \$myScalar, ... );
  
    # returning by callback_ref
    SQL::SimpleOps->Call( buffer => \@mySubRot, ... );
    ...
    sub mySubRot() { my $ref = shift; ... ; return 0; }

• Command: Select, Delete, Insert, Update and Call

025S [command] Make Folder error, system-message

• System: The Save Option was been aborted with system error during make folder to write.

• Action: Check the system-message to fix. Common errors:

    a) Permission denied: Check the folder ACL;
    b) No left space: Check the folder free space or quota;
    c) Invalid filename: Check the filename and use supported characters for your system.

• Command: Select, Delete, Insert, Update and Call

026S [command] Open File error, system-message

• System: The Save Option was been aborted with system errors during open file to write.

• Action: Check the system-message to fix. Common errors:

    a) Permission denied: Check the Folder ACL;
    b) Permission denied: Check if filer already using an onther ACL;
    c) No left space: Check the folder free space or quota;
    d) Invalid filename: Check the filename and use supported characters for your system.

  Command: Select, Delete, Insert, Update and Call

028E [command] Table/Field Value invalid, must be single-value or array

• Error: The assigned value for one field is not single value or array value.

• Action: Check the subitem in the where option.

    # invalid
    SQL::SimpleOps->Select( where => [ a => \&callback ], ... );
    or
    SQL::SimpleOps->Select( where => [ b => { c, 1 }, ... );
  
    # correct
    SQL::SimpleOps->Select( where => [ a => value, ], ... );
    or
    SQL::SimpleOps->Select( where => [ b => [ c, 1 ], ... );

• Command: Select, Delete, Insert, and Update

029E [command] The TCP Port invalid, must be numeric and between 1-65535

• Error: The TCP Port value exced the range 1-65535.

• Action: Check and validate the port option on the Constructor.

    my $mymod = SQL::SimpleOps->new( port => 1..65535, ... );
  
    # NOTE: The 'port' option is not required for standard installation.

• Command: Constructor (method new)

030E [command] Aliases Table is not hashref

• 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"

• Command: Constructor (method new)

031E [command] Aliases 'table' invalid, table_cols must be hashref

• Error: The cols option in the Aliases Table must be hash_ref.

• Action: Check the struct and validate the tables option on the Constructor. See "Aliases Table"

• Command: Constructor (method new)

032E [command] Aliases 'table' invalid, table_cols invalid format

• Error: The cols option in the Aliases Table is empty.

• Action: Check the struct validate the tables option on the Constructor. See "Aliases Table"

• Command: Constructor (method new)

033E [command] Table 'table' already, there can be only one

• Error: One table in the Aliases Table already defined.

• Action: Check the struct validate the tables option on the Constructor. See "Aliases Table"

    # invalid
    SQL::SimpleOps->Select( table => [ t1, t2, t1 ], ... );
  
    # correct
    SQL::SimpleOps->Select( table => [ t1, t2 ], ... );

• Command: Select and SelectCursor

034E [command] Aliases 'table' invalid, table_name is missing

• 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"

• Command: Constructor (method new)

035S [command] Interface Load error 'interface::plugin', system-message

• Plugin: Plugin loader error

• Action: The require load process fail for interface and drive options.

036S [command] Interface 'plugin' load error

• Plugin: The 'new' method in plugin finished with errors.

• Action: Check the options for the plugin or review your code into plugin

037S [command] Interface 'plugin' error, plugin-message

• Plugin: Plugin aborted with errors

• Action: Check the message for Plugin on drive option

038E [command] Syslog Facility invalid, must be 'local0' to 'local7'

• Error: The facility option must be local0 to local7.

• Action: Check the facility option on Constructor method.

• Command: Constructor (new)

039E [command] Syslog Service invalid, must contains 'alphanumeric' characters

• Error: The Service option must be alphanumeric characters.

• Action: Check the service option on Constructor method.

• Command: Constructor (new)

040E [command] Log File invalid, must contains 'alphanumeric' characters

• Error: The logfile option must be alphanumeric characters.

• Action: Check the logfile option on Constructor method.

• Command: Constructor (new)

041E [command] Values Format error, must be arrayref

• Error: The values option must be an arrayref.

• Action: Check the values option an insert command.

• Command: Insert

042E [command] Conflict/Duplicate Format error, must be hashref

• Error: The conflict option must be an hashref.

• Action: Check the conflict option an insert command.

• Command: Insert

043E [command] Limit is missing

• Error: The limit options must be an numeric value.

• Action: Check the limit option an insert command.

• Command: SelectCursor

044E Buffer hashkey invalid, buffer is not hashref

• Error: The buffer_hashkey option should only be used with buffer hashref

• Action: Check the buffer option.

• Command: Select and SelectCursor

045E Buffer hashkey not mapped

• Error: The buffer_hashkey value does not exist as an index key in the field list.

• Action: Check the field list or buffer_hashkey value.

• Command: Select and SelectCursor

046E Buffer hashkey must be scalaref or arrayref

• Error: The buffer_hashkey support only scalaref or arrayref.

• Action: Check the buffer_hashkey option.

• Command: Select and SelectCursor

047E Buffer arrayref Off not allowed for multiple field list

• Error: The buffer_arrayref only supports single fields in the selection.

• Action: Check the list of fields in selection.

• Command: Select and SelectCursor

048E Buffer hashindex must be arrayref

• Error: The buffer_hashindex is not arrayref format.

• Action: Check the list of value in selection.

• Command: Select and SelectCursor

099S [command] dbh-interface-message

• 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.

999S [message] invalid code 'message-code'

• Error: The message code not found on the Table os Messages.

• Action: Internal Module Error, open a issue.

SEE ALSO

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

EXTERNAL REFERENCES

MySQL, MariaDB, Postgres, SQLite, SQL Wiki, W3C

AUTHOR

Carlos Celso, <ccelso@cpan.org>

COPYRIGHT

Copyright (C) 2008-2023 by Carlos Celso

LICENSE

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/.

cpanm SQL::SimpleOps

perl -MCPAN -e shell
install SQL::SimpleOps