| 1 | package Coteng; | ||
| 2 | use 5.008005; | ||
| 3 | use strict; | ||
| 4 | use warnings; | ||
| 5 | |||
| 6 | our $VERSION = "0.11"; | ||
| 7 | our $DBI_CLASS = 'Coteng::DBI'; | ||
| 8 | |||
| 9 | use Carp (); | ||
| 10 | use Module::Load (); | ||
| 11 | use Scope::Container; | ||
| 12 | use Scope::Container::DBI; | ||
| 13 | use SQL::NamedPlaceholder (); | ||
| 14 | use Class::Accessor::Lite::Lazy ( | ||
| 15 | rw => [qw( | ||
| 16 | dbh | ||
| 17 | current_dbname | ||
| 18 | connect_info | ||
| 19 | )], | ||
| 20 | rw_lazy => [qw( | ||
| 21 | sql_builder | ||
| 22 | )], | ||
| 23 | new => 1 | ||
| 24 | ); | ||
| 25 | |||
| 26 | use Coteng::DBI; | ||
| 27 | use Coteng::QueryBuilder; | ||
| 28 | |||
| 29 | |||
| 30 | sub db { | ||
| 31 | my ($self, $dbname) = @_; | ||
| 32 | $dbname or Carp::croak "dbname required"; | ||
| 33 | $self->current_dbname($dbname); | ||
| 34 | $self->dbh($dbname); | ||
| 35 | $self; | ||
| 36 | } | ||
| 37 | |||
| 38 | sub dbh { | ||
| 39 | my ($self, $dbname) = @_; | ||
| 40 | $dbname ||= $self->current_dbname or Carp::croak 'dbname or current_dbname required'; | ||
| 41 | |||
| 42 | my $db_info = $self->{connect_info}->{$dbname} || Carp::croak "'$dbname' doesn't exist"; | ||
| 43 | |||
| 44 | my ($dsn, $user, $passwd, $attr) = ('', '', '', {}); | ||
| 45 | if (ref($db_info) eq 'HASH') { | ||
| 46 | $dsn = $db_info->{dsn} || Carp::croak "dsn required"; | ||
| 47 | $user = defined $db_info->{user} ? $db_info->{user} : ''; | ||
| 48 | $passwd = defined $db_info->{passwd} ? $db_info->{passwd} : ''; | ||
| 49 | $attr = $db_info->{attr}; | ||
| 50 | } | ||
| 51 | elsif (ref($db_info) eq 'ARRAY') { | ||
| 52 | ($dsn, $user, $passwd, $attr) = @$db_info; | ||
| 53 | } | ||
| 54 | else { | ||
| 55 | Carp::croak 'connect_info->{$dbname} must be HASHref, or ARRAYref'; | ||
| 56 | } | ||
| 57 | |||
| 58 | load_if_class_not_loaded($DBI_CLASS); | ||
| 59 | |||
| 60 | unless (in_scope_container) { | ||
| 61 | # define $CONTEXT forcelly to enable Scope::Container::DBI cache | ||
| 62 | $self->{_dbh_container_dummy} = start_scope_container(); | ||
| 63 | } | ||
| 64 | |||
| 65 | $attr->{RootClass} ||= $DBI_CLASS; | ||
| 66 | my $dbh = Scope::Container::DBI->connect($dsn, $user, $passwd, $attr); | ||
| 67 | $dbh; | ||
| 68 | } | ||
| 69 | |||
| 70 | sub _build_sql_builder { | ||
| 71 | my ($self) = @_; | ||
| 72 | return Coteng::QueryBuilder->new(driver => $self->dbh->{Driver}{Name}); | ||
| 73 | } | ||
| 74 | |||
| 75 | sub single_by_sql { | ||
| 76 | my ($self, $sql, $binds, $class) = @_; | ||
| 77 | |||
| 78 | my $row = $self->dbh->select_row($sql, @$binds) || ''; | ||
| 79 | if ($class && $row) { | ||
| 80 | load_if_class_not_loaded($class); | ||
| 81 | $row = $class->new($row); | ||
| 82 | } | ||
| 83 | return $row; | ||
| 84 | } | ||
| 85 | |||
| 86 | sub single_named { | ||
| 87 | my ($self, $sql, $bind_values, $class) = @_; | ||
| 88 | ($sql, my $binds) = SQL::NamedPlaceholder::bind_named($sql, $bind_values); | ||
| 89 | my $row = $self->single_by_sql($sql, $binds, $class); | ||
| 90 | return $row; | ||
| 91 | } | ||
| 92 | |||
| 93 | sub search_by_sql { | ||
| 94 | my ($self, $sql, $binds, $class) = @_; | ||
| 95 | my $rows = $self->dbh->select_all($sql, @$binds) || []; | ||
| 96 | if ($class && @$rows) { | ||
| 97 | load_if_class_not_loaded($class); | ||
| 98 | $rows = [ map { $class->new($_) } @$rows ]; | ||
| 99 | } | ||
| 100 | return $rows; | ||
| 101 | } | ||
| 102 | |||
| 103 | sub search_named { | ||
| 104 | my ($self, $sql, $bind_values, $class) = @_; | ||
| 105 | ($sql, my $binds) = SQL::NamedPlaceholder::bind_named($sql, $bind_values); | ||
| 106 | my $rows = $self->search_by_sql($sql, $binds, $class); | ||
| 107 | return $rows; | ||
| 108 | } | ||
| 109 | |||
| 110 | sub execute { | ||
| 111 | my $self = shift; | ||
| 112 | $self->dbh->query($self->_expand_args(@_)); | ||
| 113 | } | ||
| 114 | |||
| 115 | sub _validate_where { | ||
| 116 | my ($self, $where) = @_; | ||
| 117 | if (ref($where) ne "HASH" && ref($where) ne "ARRAY" | ||
| 118 | && ref($where) ne "SQL::Maker::Condition" && ref($where) ne "SQL::QueryMaker") { | ||
| 119 | Carp::croak "'where' required to be HASH or ARRAY or SQL::Maker::Condition or SQL::QueryMaker"; | ||
| 120 | } | ||
| 121 | } | ||
| 122 | |||
| 123 | sub single { | ||
| 124 | my ($self, $table, $where, $opt) = @_; | ||
| 125 | my $class = do { | ||
| 126 | my $klass = pop; | ||
| 127 | ref($klass) ? undef : $klass; | ||
| 128 | }; | ||
| 129 | if (ref($opt) ne "HASH") { | ||
| 130 | $opt = {}; | ||
| 131 | } | ||
| 132 | |||
| 133 | $self->_validate_where($where); | ||
| 134 | |||
| 135 | $opt->{limit} = 1; | ||
| 136 | |||
| 137 | my ($sql, @binds) = $self->sql_builder->select( | ||
| 138 | $table, | ||
| 139 | $opt->{columns} || ['*'], | ||
| 140 | $where, | ||
| 141 | $opt | ||
| 142 | ); | ||
| 143 | my $row = $self->single_by_sql($sql, \@binds, $class); | ||
| 144 | return $row; | ||
| 145 | } | ||
| 146 | |||
| 147 | sub search { | ||
| 148 | my ($self, $table, $where, $opt) = @_; | ||
| 149 | my $class = do { | ||
| 150 | my $klass = pop; | ||
| 151 | ref($klass) ? undef : $klass; | ||
| 152 | }; | ||
| 153 | if (ref($opt) ne "HASH") { | ||
| 154 | $opt = {}; | ||
| 155 | } | ||
| 156 | |||
| 157 | $self->_validate_where($where); | ||
| 158 | |||
| 159 | my ($sql, @binds) = $self->sql_builder->select( | ||
| 160 | $table, | ||
| 161 | $opt->{'columns'} || ['*'], | ||
| 162 | $where, | ||
| 163 | $opt | ||
| 164 | ); | ||
| 165 | my $rows = $self->search_by_sql($sql, \@binds, $class); | ||
| 166 | return $rows; | ||
| 167 | } | ||
| 168 | |||
| 169 | sub fast_insert { | ||
| 170 | my ($self, $table, $args, $prefix) = @_; | ||
| 171 | |||
| 172 | my ($sql, @binds) = $self->sql_builder->insert( | ||
| 173 | $table, | ||
| 174 | $args, | ||
| 175 | { prefix => $prefix }, | ||
| 176 | ); | ||
| 177 | $self->execute($sql, @binds); | ||
| 178 | return $self->dbh->last_insert_id($table); | ||
| 179 | } | ||
| 180 | |||
| 181 | sub insert { | ||
| 182 | my $self = shift; | ||
| 183 | my ($table, $args, $opt) = @_; | ||
| 184 | my $class = do { | ||
| 185 | my $klass = pop; | ||
| 186 | ref($klass) ? undef : $klass; | ||
| 187 | }; | ||
| 188 | if (ref($opt) ne "HASH") { | ||
| 189 | $opt = {}; | ||
| 190 | } | ||
| 191 | |||
| 192 | if (ref($args) ne "HASH" && ref($args) ne "ARRAY") { | ||
| 193 | Carp::croak "'args' required to be HASH or ARRAY"; | ||
| 194 | } | ||
| 195 | |||
| 196 | $opt->{primary_key} ||= "id"; | ||
| 197 | |||
| 198 | my $id = $self->fast_insert($table, $args, $opt->{prefix}); | ||
| 199 | return $self->single($table, { $opt->{primary_key} => $id }, $class); | ||
| 200 | } | ||
| 201 | |||
| 202 | sub bulk_insert { | ||
| 203 | my ($self, $table, $args) = @_; | ||
| 204 | |||
| 205 | return undef unless scalar(@{$args || []}); | ||
| 206 | |||
| 207 | my $dbh = $self->dbh; | ||
| 208 | my $can_multi_insert = $dbh->{Driver}{Name} eq 'mysql' ? 1 : 0; | ||
| 209 | |||
| 210 | if ($can_multi_insert) { | ||
| 211 | my ($sql, @binds) = $self->sql_builder->insert_multi($table, $args); | ||
| 212 | $self->execute($sql, @binds); | ||
| 213 | } else { | ||
| 214 | # use transaction for better performance and atomicity. | ||
| 215 | my $txn = $dbh->txn_scope(); | ||
| 216 | for my $arg (@$args) { | ||
| 217 | $self->insert($table, $arg); | ||
| 218 | } | ||
| 219 | $txn->commit; | ||
| 220 | } | ||
| 221 | } | ||
| 222 | |||
| 223 | sub update { | ||
| 224 | my ($self, $table, $args, $where) = @_; | ||
| 225 | |||
| 226 | my ($sql, @binds) = $self->sql_builder->update($table, $args, $where); | ||
| 227 | $self->execute($sql, @binds); | ||
| 228 | } | ||
| 229 | |||
| 230 | sub delete { | ||
| 231 | my ($self, $table, $where) = @_; | ||
| 232 | |||
| 233 | my ($sql, @binds) = $self->sql_builder->delete($table, $where); | ||
| 234 | $self->execute($sql, @binds); | ||
| 235 | } | ||
| 236 | |||
| 237 | sub count { | ||
| 238 | my ($self, $table, $column, $where, $opt) = @_; | ||
| 239 | |||
| 240 | if (ref $column eq 'HASH') { | ||
| 241 | Carp::croak('Do not pass HashRef to second argument. Usage: $db->count($table[, $column[, $where[, $opt]]])'); | ||
| 242 | } | ||
| 243 | |||
| 244 | $column ||= '*'; | ||
| 245 | |||
| 246 | my ($sql, @binds) = $self->sql_builder->select($table, [\"COUNT($column)"], $where, $opt); | ||
| 247 | |||
| 248 | my ($cnt) = $self->dbh->select_one($sql, @binds); | ||
| 249 | $cnt; | ||
| 250 | } | ||
| 251 | |||
| 252 | sub last_insert_id { | ||
| 253 | my $self = shift; | ||
| 254 | $self->dbh->last_insert_id; | ||
| 255 | } | ||
| 256 | |||
| 257 | sub txn_scope { | ||
| 258 | my $self = shift; | ||
| 259 | $self->dbh->txn_scope; | ||
| 260 | } | ||
| 261 | |||
| 262 | sub _expand_args (@) { | ||
| 263 | my ($class, $query, @args) = @_; | ||
| 264 | |||
| 265 | if (@args == 1 && ref $args[0] eq 'HASH') { | ||
| 266 | ( $query, my $binds ) = SQL::NamedPlaceholder::bind_named($query, $args[0]); | ||
| 267 | @args = @$binds; | ||
| 268 | } | ||
| 269 | |||
| 270 | return ($query, @args); | ||
| 271 | } | ||
| 272 | |||
| 273 | sub load_if_class_not_loaded { | ||
| 274 | my $class = shift; | ||
| 275 | if (! is_class_loaded($class)) { | ||
| 276 | Module::Load::load $class; | ||
| 277 | } | ||
| 278 | } | ||
| 279 | |||
| 280 | # stolen from Mouse::PurePerl | ||
| 281 | sub is_class_loaded { | ||
| 282 | my $class = shift; | ||
| 283 | |||
| 284 | return 0 if ref($class) || !defined($class) || !length($class); | ||
| 285 | |||
| 286 | my $pack = \%::; | ||
| 287 | |||
| 288 | foreach my $part (split('::', $class)) { | ||
| 289 | $part .= '::'; | ||
| 290 | return 0 if !exists $pack->{$part}; | ||
| 291 | |||
| 292 | my $entry = \$pack->{$part}; | ||
| 293 | return 0 if ref($entry) ne 'GLOB'; | ||
| 294 | $pack = *{$entry}{HASH}; | ||
| 295 | } | ||
| 296 | |||
| 297 | return 0 if !%{$pack}; | ||
| 298 | |||
| 299 | # check for $VERSION or @ISA | ||
| 300 | return 1 if exists $pack->{VERSION} | ||
| 301 | && defined *{$pack->{VERSION}}{SCALAR} && defined ${ $pack->{VERSION} }; | ||
| 302 | return 1 if exists $pack->{ISA} | ||
| 303 | && defined *{$pack->{ISA}}{ARRAY} && @{ $pack->{ISA} } != 0; | ||
| 304 | |||
| 305 | # check for any method | ||
| 306 | foreach my $name( keys %{$pack} ) { | ||
| 307 | my $entry = \$pack->{$name}; | ||
| 308 | return 1 if ref($entry) ne 'GLOB' || defined *{$entry}{CODE}; | ||
| 309 | } | ||
| 310 | |||
| 311 | # fail | ||
| 312 | return 0; | ||
| 313 | } | ||
| 314 | |||
| 315 | 1; | ||
| 316 | __END__ |