blib/lib/Rex/Commands/DB.pm untested

filter: Statementstmt 0.0% 0 / 92 Branchbran 0.0% 0 / 16 Conditioncond 0.0% 0 / 20 MC/DCmcdc 0.0% 0 / 9 Subroutinesub 0.0% 0 / 10 Podpod 0.0% 0 / 3 totaltotal 0.0% 0 / 150 CC 9 SCAR 35.1
CC
9
Cov
0%
db
72.0
import
6.0
BEGIN
2.0
CRAP 33.6
1 #
2 # (c) Jan Gehring <jan.gehring@gmail.com>
3 #
4  
5 =head1 NAME
6  
7 Rex::Commands::DB - Simple Database Access
8  
9 =head1 DESCRIPTION
10  
11 This module gives you simple access to a database. Currently I<select>, I<delete>, I<insert> and I<update> is supported.
12  
13 Version <= 1.0: All these functions will not be reported.
14  
15 =head1 SYNOPSIS
16  
17 use Rex::Commands::DB {
18 dsn => "DBI:mysql:database=test;host=dbhost",
19 user => "username",
20 password => "password",
21 };
22  
23 task "list", sub {
24 my @data = db select => {
25 fields => "*",
26 from => "table",
27 where => "enabled=1",
28 };
29  
30 db insert => "table", {
31 field1 => "value1",
32 field2 => "value2",
33 field3 => 5,
34 };
35  
36 db update => "table", {
37 set => {
38 field1 => "newvalue",
39 field2 => "newvalue2",
40 },
41 where => "id=5",
42 };
43  
44 db delete => "table", {
45 where => "id < 5",
46 };
47  
48 };
49  
50  
51 =head1 EXPORTED FUNCTIONS
52  
53 =cut
54  
55 package Rex::Commands::DB;
56  
57 use v5.14.4;
58 use warnings;
59  
60 our $VERSION = '1.16.1'; # VERSION
61  
62 BEGIN {
63 use Rex::Require;
64 DBI->require;
65 }
66  
67 use Rex::Logger;
68 use Data::Dumper;
69 use Symbol;
70  
71 use vars qw(@EXPORT $dbh);
72  
73 @EXPORT = qw(db);
74  
75 =head2 db
76  
77 Do a database action.
78  
79 my @data = db select => {
80 fields => "*",
81 from => "table",
82 where => "host='myhost'",
83 };
84  
85 db insert => "table", {
86 field1 => "value1",
87 field2 => "value2",
88 field3 => 5,
89 };
90  
91 db update => "table", {
92 set => {
93 field1 => "newvalue",
94 field2 => "newvalue2",
95 },
96 where => "id=5",
97 };
98  
99 db delete => "table", {
100 where => "id < 5",
101 };
102  
103 =cut
104  
105 sub db {
106  
107 my ( $type, $table, $data ) = @_;
108 if ( ref($table) ) {
109 my %d = %{$table};
110 delete $d{"from"};
111 $data = \%d;
112  
113 $table = $table->{"from"};
114 }
115  
116 unless ($table) {
117 Rex::Logger::info("No table defined...')");
118 return;
119 }
120  
121 if ( $type eq "select" ) {
122 my $sql = sprintf(
123 "SELECT %s FROM %s WHERE %s",
124 $data->{"fields"} || "*",
125 $table, $data->{"where"} || "1=1"
126 );
127 if ( defined $data->{"order"} ) {
128 $sql .= " ORDER BY " . $data->{"order"};
129 }
130 Rex::Logger::debug("sql: $sql");
131  
132 my $sth = $dbh->prepare($sql);
133 $sth->execute or die( $sth->errstr );
134  
135 my @return;
136  
137 while ( my $row = $sth->fetchrow_hashref ) {
138 push @return, $row;
139 }
140 $sth->finish;
141  
142 return @return;
143 }
144 elsif ( $type eq "insert" ) {
145 my $sql = "INSERT INTO %s (%s) VALUES(%s)";
146  
147 my @values;
148 for my $key ( keys %{$data} ) {
149 push( @values, "?" );
150 }
151  
152 $sql =
153 sprintf( $sql, $table, join( ",", keys %{$data} ), join( ",", @values ) );
154 Rex::Logger::debug("sql: $sql");
155  
156 my $sth = $dbh->prepare($sql);
157 my $i = 1;
158 for my $key ( keys %{$data} ) {
159 $data->{$key} ||= '';
160 Rex::Logger::debug( "sql: binding: " . $data->{$key} );
161 $sth->bind_param( $i, $data->{$key} ) or die( $sth->errstr );
162 $i++;
163 }
164  
165 $sth->execute or die( $sth->errstr );
166 }
167 elsif ( $type eq "update" ) {
168 my $sql = "UPDATE %s SET %s WHERE %s";
169  
170 my @values;
171 for my $key ( keys %{ $data->{"set"} } ) {
172 push( @values, "$key = ?" );
173 }
174  
175 $sql = sprintf( $sql, $table, join( ",", @values ), $data->{"where"} );
176 Rex::Logger::debug("sql: $sql");
177  
178 my $sth = $dbh->prepare($sql);
179 my $i = 1;
180 for my $key ( keys %{ $data->{"set"} } ) {
181 Rex::Logger::debug( "sql: binding: " . $data->{"set"}->{$key} );
182 $sth->bind_param( $i, $data->{"set"}->{$key} ) or die( $sth->errstr );
183 $i++;
184 }
185  
186 $sth->execute or die( $sth->errstr );
187 }
188 elsif ( $type eq "delete" ) {
189 my $sql = sprintf( "DELETE FROM %s WHERE %s", $table, $data->{"where"} );
190 my $sth = $dbh->prepare($sql);
191 $sth->execute or die( $sth->errstr );
192 }
193 else {
194 Rex::Logger::info("DB: action $type not supported.");
195 }
196  
197 }
198  
199 sub import {
200  
201 my ( $class, $opt ) = @_;
202  
203 if ($opt) {
204 $dbh = DBI->connect(
205 $opt->{"dsn"}, $opt->{"user"},
206 $opt->{"password"} || "", $opt->{"attr"}
207 );
208 $dbh->{mysql_auto_reconnect} = 1;
209 }
210  
211 my ( $ns_register_to, $file, $line ) = caller;
212  
213 for my $func_name (@EXPORT) {
214 my $ref_to_function = qualify_to_ref( $func_name, $ns_register_to );
215 *{$ref_to_function} = \&$func_name;
216 }
217  
218 }
219  
220 1;