Menu ▾ ▴

EXAMPLES

Carlos Celso de Almeida

go to: HOME, MESSAGES or EXAMPLES_EXTENDED

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

Step1 - creating the database

# psql -U postgres <<EOF
CREATE DATABASE test_db ENCODING 'UTF8';
EOF

Step2 - creating tables and rules

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

  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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
#!/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__

Testing

# 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

ENDED

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.