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