Menu ▾ ▴

EXAMPLES_EXTENDED

Carlos Celso de Almeida

go to: HOME, EXAMPLES or CHANGELOG

EXTENDED EXAMPLES

The following examples list some implementations of the methods, presenting different scenarios:

Table Aliases's Glossary

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

Commands using the feature "aliases tables"

[EXAMPLES_EXTENDED]

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

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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 HAVING COMMAND

[EXAMPLES_EXTENDED]

Select( table => "tab_alias1", fields => [ "fld_alias1" ], having => [ "count(fld_alias1)" => 1 ] )
 note: lower case is supported
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 HAVING count(fld_real1) = 1

 Select( table => "tab_alias1", fields => [ "fld_alias1" ], having => [ "count(fld_alias1)" => [ '>', 0 ], "count(fld_alias1)" => [ ',', 10 ], ] )
 note: lower case is supported
 returns: SELECT fld_real1 fld_alias1 FROM tab_real1 tab_alias1 HAVING count(fld_real1) > 0 AND 1 count(fld_real1) < 10

SELECT AND ORDERBY COMMAND
[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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'

Commands without the feature "aliases table

[EXAMPLES_EXTENDED]

DELETE COMMANDS

[EXAMPLES_EXTENDED]

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

INSERT COMMANDS

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

 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

[EXAMPLES_EXTENDED]

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

Predefined MySQL plugin

[EXAMPLES_EXTENDED]

Preparation:

# mysql << EOF
create database my_db;
create table my_db.my_users
(
login varchar(128),
password varchar(256),
uid integer unsigned,
gid integer unsigned,
name varchar(128),
home varchar(256),
shell varchar(256)
);
create user 'my_user'@'localhost' identified by 'my_password';
grant select,insert,update,delete on my_db.my_users to 'my_user'@'localhost';
EOF

Pre Test:

# mysql -p -u my_user my_db
Enter password: [pasword]
..
MariaDB [my_db]> exit

Test:

# export MY_DB=my_db
# export MY_USER=my_user
# export MY_PASSWORD=my_password
# perl dbi_mysql.pl

Results:

## [env] system required environments
[sysenv] MY_DB is my_db
[sysenv] MY_USER is my_user
[sysenv] MY_PASSWORD is my_password
[sysenv] successful

## [db] calling SQL::SimpleOps

## [db] inserting, user1, user2, user3, user4
[insert] rc:0, sql: INSERT INTO my_users (login,password,uid,gid,name,home,shell) VALUES ('user1','user1_pw','1','10','user1_inserted','user1_home','user1_shell'),('user2','user2_pw','2','20','user2_inserted','user2_home','user2_shell'),('user3','user3_pw','3','30','user3_inserted','user3_home','user3_shell'),('user4','user4_pw','4','40','user4_inserted','user4_home','user4_shell')

## [db] updating, user1
[update] rc:0, sql: UPDATE my_users SET name = 'user1_updated' WHERE login = 'user1'

## [db] deleting, user2, user3
[insert] rc:0, sql: DELETE FROM my_users WHERE login IN ('user2','user3')

## [db] selecting
[buffer] gid=10, home=user1_home, login=user1, name=user1_updated, password=user1_pw, shell=user1_shell, uid=1
[buffer] gid=40, home=user4_home, login=user4, name=user4_inserted, password=user4_pw, shell=user4_shell, uid=4
[select] rc:0, sql: SELECT * FROM my_users
[select] rows:2

## [db] delete all
[delete] rc:0, sql: DELETE FROM my_users

Code:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
#!/usr/bin/perl

use SQL::SimpleOps;
use IO::File;

print "\n## [env] system required environments\n";
my $err=0;
foreach my $env(qw(MY_DB MY_USER MY_PASSWORD))
{
    print "[sysenv] $env is ".($ENV{$env} || "missing, use: 'export $env=' to define the value")."\n";
    $err++ if (!defined($ENV{$env}));
}
if ($err)
{
    print "[sysenv] aborted, required environments is missing\n";
    exit(-1);
}
print "[sysenv] successful\n";

print "\n## [db] calling SQL::SimpleOps\n";

my $dbh = SQL::SimpleOps->new
(
    driver => 'mysql',
    db => $ENV{MY_DB},
    login => $ENV{MY_USER},
    password => $ENV{MY_PASSWORD},
    server => 'localhost',
) || die "SQL::SimpleOps errors";

print "\n## [db] inserting, user1, user2, user3, user4\n";

$dbh->Insert
(
    table => 'my_users',
    fields => [ qw(login password uid gid name home shell) ],
    values =>
    [
        [ qw(user1 user1_pw 1 10 user1_inserted user1_home user1_shell) ],
        [ qw(user2 user2_pw 2 20 user2_inserted user2_home user2_shell) ],
        [ qw(user3 user3_pw 3 30 user3_inserted user3_home user3_shell) ],
        [ qw(user4 user4_pw 4 40 user4_inserted user4_home user4_shell) ],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] updating, user1\n";

$dbh->Update
(
    table => 'my_users',
    fields =>
    {
        name => 'user1_updated',
    },
    where =>
    [
        login => 'user1',
    ],
);
print "[update] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] deleting, user2, user3\n";

$dbh->Delete
(
    table => 'my_users',
    where =>
    [
        login => ['user2','user3'],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] selecting\n";

$dbh->Select
(
    table => 'my_users',
    buffer => \&callback,
);
print "[select] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
print "[select] rows:", $dbh->getRows()."\n";

print "\n## [db] delete all\n";

$dbh->Delete
(
    table => 'my_users',
    force => 1,
);
print "[delete] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
exit;

sub callback()
{
    my $ref = shift;
    my @buf;
    foreach my $id (sort(keys(%{$ref}))) { push(@buf,$id."=".$ref->{$id}); }
    print "[buffer] ".join(", ",@buf)."\n";
    return 0;
}

Predefined Postgres plugin with Schema

[EXAMPLES_EXTENDED]

See: /var/lib/pgsql/data/pg_hba.conf

Preparation:

# su - postgres
# echo "create database my_db" | psql
# echo "create schema my_schema" | psql -d my_db

# psql my_db <<EOF
create table my_schema.my_users
(
login varchar(128),
password varchar(256),
uid integer,
gid integer,
name varchar(128),
home varchar(256),
shell varchar(256)
);
create role my_user login password 'my_password';
grant usage on schema my_schema to my_user;
grant select,insert,update,delete on all tables in schema my_schema to my_user;
EOF

Pre Test:

# psql -W -U my_user -h localhost -d my_db
Password: [pasword]
..
my_db=> \dn
       List of schemas
  Name    |       Owner       
-----------+-------------------
my_schema | postgres
..
my_db=# exit

Test:

# export MY_DB=my_db
# export MY_SCHEMA=my_schema
# export MY_USER=my_user
# export MY_PASSWORD=my_password
# perl dbi_postgres_with_schema.pl

Results:

## [env] system required environments
[sysenv] MY_DB is my_db
[sysenv] MY_SCHEMA is my_schema
[sysenv] MY_USER is my_user
[sysenv] MY_PASSWORD is my_password
[sysenv] successful

## [db] calling SQL::SimpleOps

## [db] inserting, user1, user2, user3, user4
[insert] rc:0, sql: INSERT INTO my_schema.my_users (login,password,uid,gid,name,home,shell) VALUES ('user1','user1_pw','1','10','user1_inserted','user1_home','user1_shell'),('user2','user2_pw','2','20','user2_inserted','user2_home','user2_shell'),('user3','user3_pw','3','30','user3_inserted','user3_home','user3_shell'),('user4','user4_pw','4','40','user4_inserted','user4_home','user4_shell')

## [db] updating, user1
[update] rc:0, sql: UPDATE my_schema.my_users SET name = 'user1_updated' WHERE login = 'user1'

## [db] deleting, user2, user3
[insert] rc:0, sql: DELETE FROM my_schema.my_users WHERE login IN ('user2','user3')

## [db] selecting
[buffer] gid=40, home=user4_home, login=user4, name=user4_inserted, password=user4_pw, shell=user4_shell, uid=4
[buffer] gid=10, home=user1_home, login=user1, name=user1_updated, password=user1_pw, shell=user1_shell, uid=1
[select] rc:0, sql: SELECT * FROM my_schema.my_users
[select] rows:2

## [db] delete all
[delete] rc:0, sql: DELETE FROM my_schema.my_users

Code:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
#!/usr/bin/perl

use SQL::SimpleOps;
use IO::File;

print "\n## [env] system required environments\n";
my $err=0;
foreach my $env(qw(MY_DB MY_SCHEMA MY_USER MY_PASSWORD))
{
    print "[sysenv] $env is ".($ENV{$env} || "missing, use: 'export $env=' to define the value")."\n";
    $err++ if (!defined($ENV{$env}));
}
if ($err)
{
    print "[sysenv] aborted, required environments is missing\n";
    exit(-1);
}
print "[sysenv] successful\n";

print "\n## [db] calling SQL::SimpleOps\n";

my $dbh = SQL::SimpleOps->new
(
    driver => 'pg',
    db => $ENV{MY_DB},
    schema => $ENV{MY_SCHEMA},
    login => $ENV{MY_USER},
    password => $ENV{MY_PASSWORD},
    server => 'localhost',
) || die "SQL::SimpleOps errors";

print "\n## [db] inserting, user1, user2, user3, user4\n";

$dbh->Insert
(
    table => 'my_users',
    fields => [ qw(login password uid gid name home shell) ],
    values =>
    [
        [ qw(user1 user1_pw 1 10 user1_inserted user1_home user1_shell) ],
        [ qw(user2 user2_pw 2 20 user2_inserted user2_home user2_shell) ],
        [ qw(user3 user3_pw 3 30 user3_inserted user3_home user3_shell) ],
        [ qw(user4 user4_pw 4 40 user4_inserted user4_home user4_shell) ],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] updating, user1\n";

$dbh->Update
(
    table => 'my_users',
    fields =>
    {
        name => 'user1_updated',
    },
    where =>
    [
        login => 'user1',
    ],
);
print "[update] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] deleting, user2, user3\n";

$dbh->Delete
(
    table => 'my_users',
    where =>
    [
        login => ['user2','user3'],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] selecting\n";

$dbh->Select
(
    table => 'my_users',
    buffer => \&callback,
);
print "[select] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
print "[select] rows:", $dbh->getRows()."\n";

print "\n## [db] delete all\n";

$dbh->Delete
(
    table => 'my_users',
    force => 1,
);
print "[delete] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
exit;

sub callback()
{
    my $ref = shift;
    my @buf;
    foreach my $id (sort(keys(%{$ref}))) { push(@buf,$id."=".$ref->{$id}); }
    print "[buffer] ".join(", ",@buf)."\n";
    return 0;
}

Predefined Postgres plugin without Schema

[EXAMPLES_EXTENDED]

See: /var/lib/pgsql/data/pg_hba.conf

Preparation:

# su - postgres
# echo "create database my_db" | psql

# psql my_db <<EOF
create table my_users
(
login varchar(128),
password varchar(256),
uid integer,
gid integer,
name varchar(128),
home varchar(256),
shell varchar(256)
);
create role my_user login password 'my_password';
grant select,insert,update,delete on my_users to my_user;
EOF

Pre Test:

# psql -W -U my_user -h localhost -d my_db
Password: [password]
..
my_db=# exit

Test:

# export MY_DB=my_db
# export MY_USER=my_user
# export MY_PASSWORD=my_password
# perl dbi_postgres_without_schema.pl

Results:

## [env] system required environments
[sysenv] MY_DB is my_db
[sysenv] MY_USER is my_user
[sysenv] MY_PASSWORD is my_password
[sysenv] successful

## [db] calling SQL::SimpleOps

## [db] inserting, user1, user2, user3, user4
[insert] rc:0, sql: INSERT INTO my_users (login,password,uid,gid,name,home,shell) VALUES ('user1','user1_pw','1','10','user1_inserted','user1_home','user1_shell'),('user2','user2_pw','2','20','user2_inserted','user2_home','user2_shell'),('user3','user3_pw','3','30','user3_inserted','user3_home','user3_shell'),('user4','user4_pw','4','40','user4_inserted','user4_home','user4_shell')

## [db] updating, user1
[update] rc:0, sql: UPDATE my_users SET name = 'user1_updated' WHERE login = 'user1'

## [db] deleting, user2, user3
[insert] rc:0, sql: DELETE FROM my_users WHERE login IN ('user2','user3')

## [db] selecting
[buffer] gid=40, home=user4_home, login=user4, name=user4_inserted, password=user4_pw, shell=user4_shell, uid=4
[buffer] gid=10, home=user1_home, login=user1, name=user1_updated, password=user1_pw, shell=user1_shell, uid=1
[select] rc:0, sql: SELECT * FROM my_users
[select] rows:2

## [db] delete all
[delete] rc:0, sql: DELETE FROM my_users

Code:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
#!/usr/bin/perl

use SQL::SimpleOps;
use IO::File;

print "\n## [env] system required environments\n";
my $err=0;
foreach my $env(qw(MY_DB MY_USER MY_PASSWORD))
{
    print "[sysenv] $env is ".($ENV{$env} || "missing, use: 'export $env=' to define the value")."\n";
    $err++ if (!defined($ENV{$env}));
}
if ($err)
{
    print "[sysenv] aborted, required environments is missing\n";
    exit(-1);
}
print "[sysenv] successful\n";

print "\n## [db] calling SQL::SimpleOps\n";

my $dbh = SQL::SimpleOps->new
(
    driver => 'pg',
    db => $ENV{MY_DB},
    login => $ENV{MY_USER},
    password => $ENV{MY_PASSWORD},
    server => 'localhost',
) || die "SQL::SimpleOps errors";

print "\n## [db] inserting, user1, user2, user3, user4\n";

$dbh->Insert
(
    table => 'my_users',
    fields => [ qw(login password uid gid name home shell) ],
    values =>
    [
        [ qw(user1 user1_pw 1 10 user1_inserted user1_home user1_shell) ],
        [ qw(user2 user2_pw 2 20 user2_inserted user2_home user2_shell) ],
        [ qw(user3 user3_pw 3 30 user3_inserted user3_home user3_shell) ],
        [ qw(user4 user4_pw 4 40 user4_inserted user4_home user4_shell) ],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] updating, user1\n";

$dbh->Update
(
    table => 'my_users',
    fields =>
    {
        name => 'user1_updated',
    },
    where =>
    [
        login => 'user1',
    ],
);
print "[update] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] deleting, user2, user3\n";

$dbh->Delete
(
    table => 'my_users',
    where =>
    [
        login => ['user2','user3'],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] selecting\n";

$dbh->Select
(
    table => 'my_users',
    buffer => \&callback,
);
print "[select] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
print "[select] rows:", $dbh->getRows()."\n";

print "\n## [db] delete all\n";

$dbh->Delete
(
    table => 'my_users',
    force => 1,
);
print "[delete] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
exit;

sub callback()
{
    my $ref = shift;
    my @buf;
    foreach my $id (sort(keys(%{$ref}))) { push(@buf,$id."=".$ref->{$id}); }
    print "[buffer] ".join(", ",@buf)."\n";
    return 0;
}

Predefined SQLite plugin based file

[EXAMPLES_EXTENDED]

The script will create the database as /tmp/users.sqlite

Tests:

# perl dbi_sqlite_file.pl

Results:

## [db] calling SQL::SimpleOps

## [db] create table
[call] rc:0, sql: create table users ( login varchar(128), password varchar(256), uid unsigned integer, gid unsigned integer, name varchar(128), home varchar(256), shell varchar(256))

## [db] inserting /tmp/users.sqlite, user1, user2, user3, user4
[insert] rc:0, sql: INSERT INTO users (login,password,uid,gid,name,home,shell) VALUES ('user1','user1_pw','1','10','user1_inserted','user1_home','user1_shell'),('user2','user2_pw','2','20','user2_inserted','user2_home','user2_shell'),('user3','user3_pw','3','30','user3_inserted','user3_home','user3_shell'),('user4','user4_pw','4','40','user4_inserted','user4_home','user4_shell')

## [db] updating /tmp/users.sqlite, user1
[update] rc:0, sql: UPDATE users SET name = 'user1_updated' WHERE login = 'user1'

## [db] deleting /tmp/users.sqlite, user2, user3
[insert] rc:0, sql: DELETE FROM users WHERE login IN ('user2','user3')

## [db] selecting /tmp/users.sqlite
[buffer] gid=10, home=user1_home, login=user1, name=user1_updated, password=user1_pw, shell=user1_shell, uid=1
[buffer] gid=40, home=user4_home, login=user4, name=user4_inserted, password=user4_pw, shell=user4_shell, uid=4
[select] rc:0, sql: SELECT * FROM users
[select] rows:2

## [file] removing /tmp/users.sqlite, use: 'export NO_REMOVE=1' to keep the file
.. removed, rc=1

Cleanup:

# rm /tmp/users.sqlite

Code:

    #!/usr/bin/perl

    use SQL::SimpleOps;
    use IO::File;

    my $users = "/tmp/users.sqlite";

    print "\n## [db] calling SQL::SimpleOps\n";

    my $dbh = SQL::SimpleOps->new
    (
        driver => 'sqlite',
        db => 'test_in_file',
        dbfile => $users,
    ) || die "SQL::SimpleOps errors";

    print "\n## [db] create table\n";

    $dbh->Call
    (
        command => 'create table users ( login varchar(128), password varchar(256), uid unsigned integer, gid unsigned integer, name varchar(128), home varchar(256), shell varchar(256))',
    );
    print "[call] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

    print "\n## [db] inserting $users, user1, user2, user3, user4\n";

    $dbh->Insert
    (
        table => 'users',
        fields => [ qw(login password uid gid name home shell) ],
        values =>
        [
            [ qw(user1 user1_pw 1 10 user1_inserted user1_home user1_shell) ],
            [ qw(user2 user2_pw 2 20 user2_inserted user2_home user2_shell) ],
            [ qw(user3 user3_pw 3 30 user3_inserted user3_home user3_shell) ],
            [ qw(user4 user4_pw 4 40 user4_inserted user4_home user4_shell) ],
        ],
    );
    print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

    print "\n## [db] updating $users, user1\n";

    $dbh->Update
    (
        table => 'users',
        fields =>
        {
            name => 'user1_updated',
        },
        where =>
        [
            login => 'user1',
        ],
    );
    print "[update] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

    print "\n## [db] deleting $users, user2, user3\n";

    $dbh->Delete
    (
        table => 'users',
        where =>
        [
            login => ['user2','user3'],
        ],
    );
    print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

    print "\n## [db] selecting $users\n";

    $dbh->Select
    (
        table => 'users',
        buffer => \&callback,
    );
    print "[select] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";
    print "[select] rows:", $dbh->getRows()."\n";

    print "\n## [file] removing $users, use: 'export NO_REMOVE=1' to keep the file\n";

    if ($ENV{NO_REMOVE})
        {
            print ".. remove skipped\n";
        } 
    else
        {
            print ".. removed, rc=".unlink($users)."\n";
        }
    exit;

sub callback()
{
    my $ref = shift;
    my @buf;
    foreach my $id (sort(keys(%{$ref}))) { push(@buf,$id."=".$ref->{$id}); }
    print "[buffer] ".join(", ",@buf)."\n";
    return 0;
}

Predefined SQLite plugin based memory

[EXAMPLES_EXTENDED]

The script will create the database in memory

Test:

# perl dbi_sqlite_memory.pl

Results:

## [db] calling SQL::SimpleOps

## [db] create table
[call] rc:0

## [db] insert
[insert] rc:0

## [db] updating , user1
[update] rc:0

## [db] deleting , user2, user3
[insert] rc:0

## [db] selecting 
[buffer] gid=10, home=user1_home, login=user1, name=user1_updated, password=user1_pw, shell=user1_shell, uid=1
[buffer] gid=40, home=user4_home, login=user4, name=user4_inserted, password=user4_pw, shell=user4_shell, uid=4
[select] rc:0
[select] rows:2

Cleanup:

No cleanup required.

Code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#!/usr/bin/perl

use SQL::SimpleOps;

print "\n## [db] calling SQL::SimpleOps\n";

my $dbh = SQL::SimpleOps->new
(
    driver => 'sqlite',
    db => 'test_in_memory',
    dbfile => ':memory:'
) || die "SQL::SimpleOps errors";

print "\n## [db] create table\n";

$dbh->Call
(
    command => 'create table users ( login varchar(128), password varchar(256), uid unsigned integer, gid unsigned integer, name varchar(128), home varchar(256), shell varchar(256))',
);
print "[call] rc:", $dbh->getRC()."\n";

print "\n## [db] insert\n";

$dbh->Insert
(
    table => 'users',
    fields => [ qw(login password uid gid name home shell) ],
    values =>
    [
        [ qw(user1 user1_pw 1 10 user1_inserted user1_home user1_shell) ],
        [ qw(user2 user2_pw 2 20 user2_inserted user2_home user2_shell) ],
        [ qw(user3 user3_pw 3 30 user3_inserted user3_home user3_shell) ],
        [ qw(user4 user4_pw 4 40 user4_inserted user4_home user4_shell) ],
    ],
);
print "[insert] rc:", $dbh->getRC()."\n";

print "\n## [db] updating $users, user1\n";

$dbh->Update
(
    table => 'users',
    fields =>
    {
        name => 'user1_updated',
    },
    where =>
    [
        login => 'user1',
    ],
);
print "[update] rc:", $dbh->getRC()."\n";

print "\n## [db] deleting $users, user2, user3\n";

$dbh->Delete
(
    table => 'users',
    where =>
    [
        login => ['user2','user3'],
    ],
);
print "[insert] rc:", $dbh->getRC()."\n";

print "\n## [db] selecting $users\n";

$dbh->Select
(
    table => 'users',
    buffer => \&callback,
);
print "[select] rc:", $dbh->getRC()."\n";
print "[select] rows:", $dbh->getRows()."\n";

exit;

sub callback()
{
    my $ref = shift;
    my @buf;
    foreach my $id (sort(keys(%{$ref}))) { push(@buf,$id."=".$ref->{$id}); }
    print "[buffer] ".join(", ",@buf)."\n";
    return 0;
}

Undefined DBD plugin, i.e. DBD::CSV

[EXAMPLES_EXTENDED]

This example is based Standard DBI connector.

  • No predefined plugin is required.
  • All specifications must by formated by the caller.

NOTE: The following options are required:

  • dsname (is required)
  • interface_options (is required)
  • login (depends on the DBD driver)
  • password (depends on the DBD driver)
  • schema (depends on the DBD driver)

The following options will be ignored:

  • db
  • dbfile
  • port
  • server

The script will create the file /tmp/users.csv
The first line contains the rows separated by ':'

Tests:

   # perl dbi_csv_file.pl

Results:

## [db] calling SQL::SimpleOps

## [file] creating /tmp/users.csv, user0, user1, user2

## [db] inserting /tmp/users.csv, user3, user4
[insert] rc:0, sql: INSERT INTO users (login,password,uid,gid,name,home,shell) VALUES ('user3','user3_pw','user3_uid','user3_gid','user3_inserted','user3_home','user3_shell'),('user4','user4_pw','user4_uid','user4_gid','user4_inserted','user4_home','user4_shell')

## [db] updating /tmp/users.csv, user1
[update] rc:0, sql: UPDATE users SET name = 'user1_updated' WHERE login = 'user1'

## [db] deleting /tmp/users.csv, user2, user3
[insert] rc:0, sql: DELETE FROM users WHERE login IN ('user2','user3')

## [db] selecting /tmp/users.csv
[buffer] gid=user0_gid, home=user0_home, login=user0, name=user0_embeded, password=user0_pw, shell=user0_shell, uid=user0_uid
[buffer] gid=user1_gid, home=user1_home, login=user1, name=user1_updated, password=user1_pw, shell=user1_shell, uid=user1_uid
[buffer] gid=user4_gid, home=user4_home, login=user4, name=user4_inserted, password=user4_pw, shell=user4_shell, uid=user4_uid
[select] rc:0
[select] rows:3

## [file] show /tmp/users.csv
login:password:uid:gid:name:home:shell
user0:user0_pw:user0_uid:user0_gid:user0_embeded:user0_home:user0_shell
user1:user1_pw:user1_uid:user1_gid:user1_updated:user1_home:user1_shell
user4:user4_pw:user4_uid:user4_gid:user4_inserted:user4_home:user4_shell

## [file] removing /tmp/users.csv, use: 'export NO_REMOVE=1' to keep the file
.. removed, rc=1

Cleanup:

   # rm /tmp/users.csv

Code:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
#!/usr/bin/perl

use SQL::SimpleOps;
use IO::File;

my $users = "/tmp/users.csv";

print "\n## [db] calling SQL::SimpleOps\n";

my $dbh = SQL::SimpleOps->new
(
    driver => 'CSV',
    dsname => '',
    interface_options =>
    {
        f_schema         => undef,
        f_dir            => "/tmp",
        f_dir_search     => [],
        f_ext            => ".csv",
        f_lock           => 2,
        f_encoding       => "utf8",
        csv_eol          => "\r\n",
        csv_sep_char     => ":",
        csv_quote_char   => '"',
        csv_escape_char  => '"',
        csv_class        => "Text::CSV_XS",
        csv_null         => 1,
        csv_bom          => 0,
        RaiseError       => 1,
        PrintError       => 1,
        FetchHashKeyName => "NAME_lc",
    },
    message_warning_off => 1,
) || die "SQL::SimpleOps errors";

print "\n## [file] creating $users, user0, user1, user2\n";

my $fh = IO::File->new(">".$users) || die "open $users error";
print $fh join(":",qw(login password uid gid name home shell)),"\n";
print $fh join(":",qw(user0 user0_pw user0_uid user0_gid user0_embeded user0_home user0_shell)),"\n";
print $fh join(":",qw(user1 user1_pw user1_uid user1_gid user1_embeded user1_home user1_shell)),"\n";
print $fh join(":",qw(user2 user2_pw user2_uid user2_gid user2_embeded user2_home user2_shell)),"\n";
undef($fh);

print "\n## [db] inserting $users, user3, user4\n";

$dbh->Insert
(
    table => 'users',
    fields => [ qw(login password uid gid name home shell) ],
    values =>
    [
        [ qw(user3 user3_pw user3_uid user3_gid user3_inserted user3_home user3_shell) ],
        [ qw(user4 user4_pw user4_uid user4_gid user4_inserted user4_home user4_shell) ],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] updating $users, user1\n";

$dbh->Update
(
    table => 'users',
    fields =>
    {
        name => 'user1_updated',
    },
    where =>
    [
        login => 'user1',
    ],
);
print "[update] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] deleting $users, user2, user3\n";

$dbh->Delete
(
    table => 'users',
    where =>
    [
        login => ['user2','user3'],
    ],
);
print "[insert] rc:", $dbh->getRC().", sql: ".$dbh->getLastSQL()."\n";

print "\n## [db] selecting $users\n";

$dbh->Select
(
    table => 'users',
    buffer => \&callback,
);
print "[select] rc:", $dbh->getRC()."\n";
print "[select] rows:", $dbh->getRows()."\n";

print "\n## [file] show $users\n";

my $fh = IO::File->new($users) || die "open $users error";
while (!$fh->eof) { print <$fh>; }
undef($fh);

print "\n## [file] removing $users, use: 'export NO_REMOVE=1' to keep the file\n";

if ($ENV{NO_REMOVE})
{
        print ".. remove skipped\n";
    }
    else
    {
        print ".. removed, rc=".unlink($users),"\n";
    }
exit;

sub callback()
{
    my $ref = shift;
    my @buf;
    foreach my $id (sort(keys(%{$ref}))) { push(@buf,$id."=".$ref->{$id}); }
    print "[buffer] ".join(", ",@buf)."\n";
    return 0;
}

Using MySQL database, driver DBD::mysql, without predefined plugin

[EXTENTED_EXAMPLES]

see "Predefined MySQL plugin" notes.

Code (only changes):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
#!/usr/bin/perl
...
my $dbh = SQL::SimpleOps->new
(
    driver => 'mysql',                                 ## case sentitive, must be 'mysql'
    ignore_plugin => 1,                                ## <- change here !!!
    dsname => "database=$ENV{MY_DB};host=localhost",   ## <- change here !!!
    login => $ENV{MY_USER},
    password => $ENV{MY_PASSWORD},
) || die "SQL::SimpleOps errors";
...

Using Postgres database, driver DBD::Pg, without predefined plugin

[EXTENTED_EXAMPLES]

see "Predefined Postgres plugin with Schema" notes.

Code (only changes):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
#!/usr/bin/perl
...
my $dbh = SQL::SimpleOps->new
(
    driver => 'Pg',                                    ## case sentitive, must be 'Pg'
    ignore_plugin => 1,                                ## <- change here !!!
    dsname => "dbname=$ENV{MY_DB};host=localhost",     ## <- change here !!!
    schema => $ENV{MY_SCHEMA},
    login => $ENV{MY_USER},
    password => $ENV{MY_PASSWORD},
) || die "SQL::SimpleOps errors";
...

Using SQLite database, driver DBD::SQLite, without predefined plugin

[EXTENTED_EXAMPLES]

see "Predefined SQLite plugin based file" notes.

Code (only changes):

1
2
3
4
5
6
7
8
9
#!/usr/bin/perl
...
my $dbh = SQL::SimpleOps->new
(
    driver => 'SQLite',                                ## case sentitive, must be 'SQLite'
    ignore_plugin => 1,                                ## <- change here
    dsname => "dbname=$users",                         ## <- change here
) || die "SQL::SimpleOps errors";
...

ENDED

Related

Wiki: EXAMPLES_EXTENDED

MongoDB Logo MongoDB