1 package Newslash::Model::Cowrapper;
2 # Cowrapper - Connection Wrapper for MySQL
12 use List::Util qw(any);
16 use DateTime::Format::MySQL;
21 return unless my $flag = shift;
23 if ($flag eq '-base') {
25 } elsif ($flag eq '-strict') {
32 push @{"${caller}::ISA"}, $flag;
35 $_->import for qw(strict warings utf8);
36 feature->import(':5.10');
40 my ($self, @rest) = @_;
41 return $self->{options} if @rest == 0;
43 my $hash = $self->{options} || {};
46 $node = $hash->{$node};
54 my $options = shift || {};
57 bless {options => {%$options, @_},
66 my ($self, $value) = @_;
68 $self->{_last_sql} = $value;
70 return $self->{_last_sql};
74 my ($self, $value) = @_;
76 $self->{_last_attr} = $value;
78 return $self->{_last_attr};
82 my ($self, $sql, $attr) = @_;
84 $self->{_last_sql} = $sql;
85 $self->{_last_attr} = $attr;
87 return ($self->{_last_sql}, $self->{_last_attr});
95 sub _dump_last_query {
98 return "+---------\n>> LAST QUERY of $cls: \n" .
99 $self->_last_sql . Dumper($self->_last_attr) .
103 ######### connect/disconnect functions
107 my $options = shift || {};
109 if ($self->transaction_mode) {
110 return $self->{_tr_dbh};
113 my $DB_HOST = $self->{options}->{Database}->{host};
114 my $DB_NAME = $self->{options}->{Database}->{name};
115 my $DB_USER = $self->{options}->{Database}->{user};
116 my $DB_PASSWORD = $self->{options}->{Database}->{password};
118 my $settings = 'mysql_read_default_group=libmysqlclient;mysql_read_default_file=/etc/mysql/my.cnf';
120 mysql_enable_utf8 => 1,
121 ShowErrorStatement => 1,
122 Callbacks => { # hack to use utf8mb4. see http://d.hatena.ne.jp/hirose31/20141028/1414496347
124 shift->do('SET NAMES utf8mb4');
131 my $dbh = DBI->connect("DBI:mysql:$DB_NAME:$DB_HOST;$settings", $DB_USER, $DB_PASSWORD, $attr);
133 $self->{_tr_dbh} = $dbh;
140 return if $self->transaction_mode;
141 return if !$self->{_tr_dbh};
143 return $self->{_tr_dbh}->disconnect;
147 ########## Transaction related functions
149 sub transaction_mode {
151 return 0 if !defined $self->{_transactions};
152 return ($self->{_transactions} > 0);
155 sub start_transaction {
157 my $options = shift || {};
159 if (!defined $self->{_transactions}) {
160 $self->{_transactions} = 0;
163 # already transaction mode
164 if ($self->transaction_mode) {
165 $self->{_transactions} += 1;
166 return $self->{_tr_dbh};
169 # start new transaction
170 $options->{AutoCommit} = 0;
171 $self->connect_db($options);
172 $self->{_transactions} = 1;
174 return $self->{_tr_dbh};
177 # use external defined dbh
178 sub use_transaction {
183 $self->{_transactions} = 2;
184 $self->{_tr_dbh} = $dbh;
192 return if !$self->transaction_mode;
194 if ($self->{_transactions} == 1) {
195 $self->{_tr_dbh}->commit;
196 $self->{_tr_dbh}->disconnect;
198 delete $self->{_tr_dbh};
200 $self->{_transactions} -= 1;
205 return if !$self->transaction_mode;
207 #$self->set_error($self->{_tr_dbh}->errstr);
209 $self->{_tr_dbh}->rollback;
210 $self->{_tr_dbh}->disconnect;
211 delete $self->{_tr_dbh};
212 $self->{_transactions} = 0;
215 ########## Utility functions
218 my ($self, $table) = @_;
220 my $dbh = $self->connect_db;
221 my $DB_NAME = $self->{options}->{Database}->{name};
224 SELECT * FROM information_schema.TABLES
225 WHERE TABLE_SCHEMA = ?
229 my $sth = $dbh->prepare($sql);
230 $sth->execute($DB_NAME, $table);
231 my $rs = $sth->fetchall_arrayref({});
232 $self->disconnect_db;
241 if ($self->options->{readonly}) {
242 $self->set_error("readonly mode");
248 ########## error handling
251 $self->{_error} = undef;
252 $self->{_errorno} = undef;
256 my ($self, $error, $errorno) = @_;
257 $self->{_error} = $error;
258 $self->{_errorno} = $errorno if $errorno;
261 my ($self, $errorno) = @_;
262 $self->{_errorno} = $errorno;
266 my ($self, $err) = @_;
267 $self->{_error} = $err if defined $err;
268 return $self->{_error};
272 my ($self, $errno) = @_;
273 $self->{_errorno} = $errno if defined $errno;
274 return $self->{_errorno};
277 ########## Utility functions
278 sub calculate_time_range {
279 my ($self, $params) = @_;
281 my $date = $params->{date};
282 my $year = $params->{year};
283 my $month = $params->{month};
284 my $day = $params->{day};
286 my $years = $params->{years};
287 my $months = $params->{months};
288 my $days = $params->{days};
289 my $weeks = $params->{weeks};
290 my $hours = $params->{hours};
291 my $minutes = $params->{minutes};
293 my $until = $params->{until};
294 my $since = $params->{since};
296 my $offset_sec = $params->{offset_sec};
297 my ($dt_since, $dt_until);
299 my $range_mode = ($years || $months || $days || $weeks || $hours || $minutes);
303 if (ref($since) eq 'HASH') {
306 elsif ($since =~ m/^\d{4}-\d{2}-\d{2}$/) {
307 $dt_since = DateTime::Format::MySQL->parse_date($since);
310 $dt_since = DateTime::Format::MySQL->parse_datetime($since);
314 if (ref($until) eq 'HASH') {
317 elsif ($until =~ m/^\d{4}-\d{2}-\d{2}$/) {
318 $dt_until = DateTime::Format::MySQL->parse_date($until);
321 $dt_until = DateTime::Format::MySQL->parse_datetime($until);
325 $dt_since = DateTime->new(year => $year,
326 month => $month || 1,
330 if ($date eq 'today') {
331 $dt_since = DateTime->today;
334 $dt_since = DateTime::Format::MySQL->parse_date($date);
345 $dt_until = $dt_since->clone;
346 $dt_until->add("years" => $years) if $years;
347 $dt_until->add("months" => $months) if $months;
348 $dt_until->add("days" => $days) if $days;
349 $dt_until->add("weeks" => $weeks) if $weeks;
350 $dt_until->add("hours" => $hours) if $hours;
351 $dt_until->add("minutes" => $minutes) if $minutes;
354 $dt_until = DateTime->now;
355 $dt_since = $dt_until->clone;
356 $dt_since->add("years" => -$years) if $years;
357 $dt_since->add("months" => -$months) if $months;
358 $dt_since->add("days" => -$days) if $days;
359 $dt_since->add("weeks" => -$weeks) if $weeks;
360 $dt_since->add("hours" => -$hours) if $hours;
361 $dt_since->add("minutes" => -$minutes) if $minutes;
366 $term = "month" if $month;
367 $term = "day" if $day;
369 $dt_until = $dt_since->clone;
370 $dt_until->add("${term}s" => 1);
374 $dt_since->add(seconds => -$offset_sec) if $dt_since;
375 $dt_until->add(seconds => -$offset_sec) if $dt_until;
379 $from = DateTime::Format::MySQL->format_datetime($dt_since) if $dt_since;
380 $to = DateTime::Format::MySQL->format_datetime($dt_until) if $dt_until;
386 =head2 build_where_clause(unique_keys => $unique_keys,
388 timestamps => $timestamps,
389 where => $where_params,
392 build SQL WHERE clause.
402 STRING or ARRAYREF or HASHREF.
403 If STRING, $keys contains unique keys.
404 If ARRAREF, @$keys contains unique keys.
405 If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.
407 'unique key' is a column name defined with 'UNIQUE' or 'PRIMARY'.
409 If you want to use key which is not correspod to table's column,
410 use alias. When aliases given, the aliases are replaced to keys in SQL.
416 STRING or ARRAYREF or HASHREF.
417 If STRING, $keys contains non-unique keys.
418 If ARRAREF, @$keys contains non-unique keys.
419 If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.
425 HASHREF to query parameters
429 when list context, returns ($clause, \@values, $unique).
430 when scalar context, return hashref like:
431 { clause => $clause, values => \@values, unique => $unique };
437 sub build_where_clause {
443 if ($args->{keys} || $args->{unique_keys}) {
444 $uniques = $args->{unique_keys};
445 $keys = $args->{keys};
448 $uniques = $self->unique_keys;
449 $keys = $self->get_keys("non-unique");
451 my $timestamp = $args->{timestamp} || $self->{timestamp};
453 my $params = $args->{params};
454 my $where = $args->{where} || $params;
459 # check and process "OR" parameter
460 if (defined $where->{OR}) {
461 my $sub_params = $where->{OR};
462 croak "invalid OR parameter" if (ref($sub_params) ne "HASH");
464 my ($sub_clause, $sub_values, $sub_unique) = $self->_parse_where_clause($uniques, $keys, $timestamp, $sub_params);
465 my $or_clause = join(" OR ", @$sub_clause);
467 push @clauses, "( $or_clause )";
468 push @values, @$sub_values;
472 my ($sub_clauses, $sub_values, $unique) = $self->_parse_where_clause($uniques, $keys, $timestamp, $where);
473 push @clauses, @$sub_clauses;
474 push @values, @$sub_values;
478 $clause = "WHERE " . join(" AND ", @clauses);
481 return wantarray ? ($clause, \@values, $unique)
482 : { clause => $clause,
484 unique => $unique, };
489 sub _parse_where_clause {
490 my ($self, $uniques, $keys, $timestamp, $params) = @_;
497 my $decode_param = sub {
500 # simply equal clause
502 return ("$k = ?", $v);
506 if (ref($v) eq 'ARRAY') {
507 my $placeholder = join(", ", map { "?" } @$v);
508 return ("$k IN ($placeholder)", $v);
511 # gt/lt/ge/le (>, <, >=, <=)
512 if (ref($v) eq 'HASH') {
513 return ("$k > ?", $v->{gt}) if defined $v->{gt};
514 return ("$k < ?", $v->{lt}) if defined $v->{lt};
515 return ("$k >= ?", $v->{ge}) if defined $v->{ge};
516 return ("$k <= ?", $v->{le}) if defined $v->{le};
519 die "invalid query parameter: $k, $v";
523 my $decode_and_set_params = sub {
524 my ($k, $p, $is_unique) = @_;
525 my ($c, $v) = $decode_param->($k, $p);
526 push @clauses, $c if $c;
528 if (ref($v) eq 'ARRAY') {
533 $unique = 1 if $is_unique;
539 # reqularize keys to hashref
540 my $ky = $self->_keys_to_hash($keys);
542 for my $k (keys(%$ky)) {
543 if (defined $params->{$k}) {
544 $decode_and_set_params->($ky->{$k}, $params->{$k});
548 # reqularize uniques to hashref
549 my $u = $self->_keys_to_hash($uniques);
551 for my $k (keys(%$u)) {
552 if (defined $params->{$k}) {
553 $decode_and_set_params->($u->{$k}, $params->{$k}, 1);
558 # year, month, day, years, months, days, date
560 my ($begin, $end) = $self->calculate_time_range($params);
562 push @clauses, "$timestamp >= ?";
563 push @values, $begin;
566 push @clauses, "$timestamp < ?";
571 return (\@clauses, \@values, $unique);
574 # reqularize keys to hashref
576 my ($self, $keys) = @_;
577 return if !defined $keys;
580 if(ref($keys) eq 'HASH') {
583 elsif (ref($keys) eq 'ARRAY') {
589 elsif (!ref($keys)) {
590 $ky = { $keys => $keys };
595 =head2 build_limit_clause(params => \@params)
597 build SQL's LIMIT clause.
607 HASHREF to query parameters
611 when list context, returns ($clause, \@values).
612 when scalar context, return hashref like:
613 { clause => $clause, values => \@values };
619 sub build_limit_clause {
623 my $params = $args->{params};
624 my $limit = $args->{default};
625 my $offset = $args->{default_offset};
627 $limit = $args->{default_limit} if defined $args->{default_limit};
628 $limit = $params->{limit} if defined $params->{limit};
629 $offset = $params->{offset} if defined $params->{offset};
633 if (defined $limit) {
634 push @clauses, "LIMIT ?";
635 push @values, $limit;
637 if (defined $offset) {
638 push @clauses, "OFFSET ?";
639 push @values, $offset;
642 my $clause = join(" ", @clauses);
644 return wantarray ? ($clause, \@values)
645 : { clause => $clause, values => \@values };
648 =head2 build_order_by_clause(keys => $keys, params => \@params)
650 build SQL ORDER BY clause.
661 If ARRAREF, @$keys contains selectSable columns.
662 If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.
666 HASHREF to query parameters
670 when list context, returns ($clause, \@values).
671 when scalar context, return hashref like:
672 { clause => $clause, values => \@values };
678 sub build_order_by_clause {
682 my $unique_keys = $args->{unique_keys} || $self->unique_keys;
683 my $keys = $args->{keys} || $self->get_keys("all");
685 my $params = $args->{params};
686 my $order_by = $params->{order_by};
688 if (!$keys || !$params || !$order_by) {
689 return wantarray ? ("", [])
690 : { clause => "", values => [] };
698 # convert $keys to hash style
699 if (ref($keys) eq 'HASH') {
700 $k_names = [keys %$keys];
703 if (ref($keys) eq 'ARRAY') {
706 elsif (!ref($keys)) {
710 # convert $order_by to hash style
711 if (ref($order_by) eq "ARRAY") {
713 for my $item (@$order_by) {
714 $hash->{$item} = "ASC";
718 elsif (!ref($order_by)) {
719 $order_by = {$order_by => "ASC"};
722 # when $order_by is not ARRAY or SCALAR or HASH,
723 # this block is passed.
724 if (ref($order_by) eq "HASH") {
725 for my $k (keys %$order_by) {
726 next if !any {$_ eq $k} @$k_names;
728 my $order = uc($order_by->{$k});
730 if ($use_alias && $keys->{$k}) {
731 $target = $keys->{$k};
733 push @clauses, "$target $order" if $target;
739 $clause = "ORDER BY " . join(", ", @clauses);
742 return wantarray ? ($clause, \@values)
743 : { clause => $clause, values => \@values };
746 ########## Insert method
750 return if $self->check_readonly;
753 my $table = $args->{table} || $self->primary_table;
755 $self->set_error("table not given", -1);
761 $keys = $self->_build_keys($args->{keys});
764 $keys = $self->get_keys("all");
767 my $params = $args->{params};
769 $self->set_error("params not given", -1);
777 # extract key and values
778 for my $k (keys %$keys) {
779 next if !defined $params->{$k};
780 if (!ref($params->{$k})) {
781 push @cols, $keys->{$k};
782 push @values, $params->{$k};
783 push @placeholders, "?";
785 elsif(ref($params->{$k}) eq "HASH") {
786 for my $subkey (keys %{$params->{$k}}) {
787 if (lc($subkey) eq "function") {
788 push @cols, $keys->{$k};
789 push @placeholders, $params->{$k}->{$subkey};
795 if (!@cols || !@values) {
796 $self->set_error("no valid values", -1);
800 my $cols_clause = join(", ", @cols);
801 my $values_clause = join(", ", @placeholders);
804 my $sql = "INSERT INTO $table ($cols_clause) VALUES ($values_clause)";
805 $self->_last_query($sql, \@values);
808 #warn Dumper @values;
810 my $dbh = $self->connect_db;
811 my $rs = $dbh->do($sql, undef, @values);
812 $self->disconnect_db;
818 ########## Select method
820 =head2 generic_select(table => $table, uniques => $uniques, keys => $keys, params => $params)
822 build SQL's ORDER BY clause.
836 ARRAYREF to unique keys. 'unique key' is a column name
837 defined with 'UNIQUE' or 'PRIMARY'.
843 ARRAYREF to acceptable keys (column names)
849 HASHREF to query parameters
853 when list context, returns ($clause, \@values).
854 when scalar context, return hashref like:
855 { clause => $clause, values => \@values };
864 my $table = $args->{table} || $self->primary_table;
866 $self->set_error("table no given");
869 my $params = $args->{params} || {};
871 if ($args->{uniques}) {
872 warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
873 $args->{unique_keys} ||= $args->{uniques};
875 my $uniques = $args->{unique_keys};
876 my $keys = $args->{keys};
877 my $timestamp = $args->{timestamp};
880 my ($values, $orderby, $limit, $where, $unique_query);
882 ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
884 timestamp => $timestamp,
886 push @arguments, @$values if @$values;
888 ($orderby, $values) = $self->build_order_by_clause(keys => $keys, params => $params);
889 push @arguments, @$values if @$values;
891 ($limit, $values) = $self->build_limit_clause(params => $params);
892 push @arguments, @$values if @$values;
894 my $dbh = $self->connect_db;
895 my $generic_sql = <<"EOSQL";
902 my $sql = $args->{sql} || $generic_sql;
903 $self->_last_query($sql, \@arguments);
905 my $sth = $dbh->prepare($sql);
906 $sth->execute(@arguments);
907 my $rs = $sth->fetchall_arrayref(+{});
909 $self->set_error("select failed", $dbh->errstr, $dbh->err);
910 $self->disconnect_db;
913 $self->disconnect_db;
917 return $rs->[0] if @$rs;
923 ########## Count method
925 =head2 generic_count(
927 target => $target_column,
928 timestamp => $timestamp_column,
932 offset_sec => $offset_sec,
933 join => $join_clause,
934 where => $where_clause)
952 =item $timestamp_column
962 target month (omissible)
966 target day (omissible)
970 additional WHERE clause (must not include 'WHERE' !; omissible)
974 additional JOIN clause (must include 'JOIN' !; omissible)
988 my $table = $params->{table} || $self->primary_table;
989 my $target = $params->{target};
990 my $timestamp = $params->{timestamp} || $self->timestamp;
992 return if (!$table || !$target || !$timestamp || !$params->{year});
995 $term = "month" if !$params->{day};
996 $term = "year" if !$params->{month};
998 my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
999 $year = 1 if (!$year || $year !~ m/^[0-9]{4}$/);
1000 $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
1001 $day = 1 if (!$day || $day !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);
1003 my $offset = $params->{offset_sec} || 0;
1004 $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;
1006 my $dt = DateTime->new(year => $year,
1009 $dt->add(seconds => -$offset);
1010 my $dt_string = DateTime::Format::MySQL->format_datetime($dt);
1012 # create end of term datetime
1013 # we must consider timezone offset, so use relative day/month.
1014 # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, DATE_ADD function add simply 30 days...
1015 my $dt_end = DateTime->new(year => $year,
1018 if ($term eq "month") {
1019 $dt_end->add(months => 1);
1021 elsif ($term eq "year") {
1022 $dt_end->add(years => 1);
1024 $dt->add(seconds => -$offset);
1025 my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);
1029 my $where_clause = "";
1030 if ($params->{where}) {
1031 $where_clause = "$params->{where} AND ";
1033 my $join_clause = $params->{join} || "";
1036 if ($term eq "day") {
1037 # `stories` table not contain display/non-display flag,
1040 SELECT COUNT($table.$target) AS count FROM $table
1043 $table.$timestamp >= ?
1044 AND $table.$timestamp < DATE_ADD(?, INTERVAL 1 DAY)
1046 push @attrs, $dt_string, $dt_string;
1048 elsif ($term eq "month") {
1050 SELECT TIMESTAMPDIFF(DAY, ?, $table.$timestamp) AS day,
1051 COUNT($table.$target) AS count
1055 $table.$timestamp >= ?
1056 AND $table.$timestamp < ?
1057 GROUP BY TIMESTAMPDIFF(DAY, ?, $table.$timestamp)
1060 push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
1062 elsif ($term eq "year") {
1064 SELECT TIMESTAMPDIFF(MONTH, ?, $table.$timestamp) AS month,
1065 COUNT($table.$target) AS count
1069 $table.$timestamp >= ?
1070 AND $table.$timestamp < ?
1071 GROUP BY TIMESTAMPDIFF(MONTH, ?, $table.$timestamp)
1074 push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
1076 my $dbh = $self->connect_db;
1077 my $sth = $dbh->prepare($sql);
1079 $self->_last_query($sql, \@attrs);
1080 $sth->execute(@attrs);
1081 my $rs = $sth->fetchall_arrayref({});
1084 $self->disconnect_db;
1087 $self->disconnect_db;
1091 if ($term eq "day") {
1092 return $rs->[0]->{count};
1094 elsif ($term eq "month") {
1097 elsif ($term eq "year") {
1104 for my $counts (@$rs) {
1105 # day / month is differential from base datetime, so add 1
1106 $hash->{$counts->{$key} + 1} = $counts->{count};
1114 ########## Update method
1116 =head2 generic_update(table => $table,
1117 updatable_keys => $updatables,
1118 addable_keys => $addables,
1119 condition_keys => $conditions,
1122 execute UPDATE SQL command.
1136 ARRAYREF or HASHREF to updatable keys. 'updatable key' is a updatable column name.
1140 ARRAYREF or HASHREF to addable keys. 'addable key' is a addable column name.
1144 ARRAYREF or HASHREF to conditional keys. 'conditional key' is a column name which can use in WHERE clause.
1148 HASHREF to query parameters
1152 when list context, returns ($clause, \@values).
1153 when scalar context, return hashref like:
1154 { clause => $clause, values => \@values };
1160 sub generic_update {
1162 return if $self->check_readonly;
1166 my $table = $args->{table} || $self->primary_table;
1168 $self->set_error("table given", -1);
1172 my $updatables = $args->{updatable_keys} || $self->get_keys("updatable");
1173 my $addables = $args->{addable_keys} || $self->get_keys("addable");
1175 my $conditions = $args->{condition_keys};
1176 my $params = $args->{params} || {};
1177 my $where = $args->{where} || $params->{where};
1180 $conditions = $args->{condition_keys} || $self->get_keys("all");
1183 $conditions = $args->{condition_keys} || $self->get_keys("primary");
1186 if (!$conditions || !%$conditions) {
1187 $self->set_error("no_condition_keys", -1);
1190 my $u_hash = $self->_keys_to_hash($updatables);
1191 #my $a_hash = $self->_keys_to_hash($addables);
1193 # first, create WHERE clause
1194 my ($where_clause, $where_values) = $self->build_where_clause(keys => $conditions,
1197 if (!$where_clause) {
1198 $self->set_error("no_where_clauses", -1);
1206 for my $col (keys %$u_hash) {
1207 next if !defined $params->{$col};
1208 my $c = $u_hash->{$col};
1210 # if $params->{$col} is SCALAR, set to the value
1211 if (!ref($params->{$col})) {
1212 push @values, $params->{$col};
1213 push @set_clauses, "$c = ?";
1215 # if $params->{$col} is HASH, do given operation
1216 elsif (ref($params->{$col}) eq 'HASH') {
1219 for my $k (keys %{$params->{$col}}) {
1220 $p->{lc($k)} = $params->{$col}->{$k};
1223 if (defined $p->{max} && defined $p->{min}) {
1224 push @values, $p->{min};
1225 push @values, $p->{max};
1226 push @values, $p->{add};
1227 push @set_clauses, "$c = GREATEST(?, LEAST(?, $c + ?))";
1229 elsif (defined $p->{max}) {
1230 push @values, $p->{min};
1231 push @values, $p->{add};
1232 push @set_clauses, "$c = GREATEST(?, $c + ?)";
1234 elsif (defined $p->{min}) {
1235 push @values, $p->{min};
1236 push @values, $p->{add};
1237 push @set_clauses, "$c = LEAST(?, $c + ?)";
1240 push @values, $p->{add};
1241 push @set_clauses, "$c = $c + ?";
1244 if ($p->{function}) {
1245 push @set_clauses, "$c = ($p->{function})";
1250 return 0 if !@set_clauses;
1251 my $set_clause = join(", ", @set_clauses);
1253 my $sql = "UPDATE $table SET $set_clause $where_clause";
1254 push @values, @$where_values;
1255 $self->_last_query($sql, \@values);
1258 #warn Dumper @values;
1260 my $dbh = $self->connect_db;
1261 my $rs = $dbh->do($sql, undef, @values);
1262 $self->disconnect_db;
1267 ########## count helper
1269 sub build_interval_times {
1272 my $params = $attr->{params} || {};
1275 return if !$params->{year};
1276 $target = "month" if !$params->{day};
1277 $target = "year" if !$params->{month};
1279 my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
1280 $year = 1 if (!$year || $year !~ m/^[0-9]{4}$/);
1281 $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
1282 $day = 1 if (!$day || $day !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);
1284 my $offset = $params->{offset_sec} || 0;
1285 $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;
1287 my $dt = DateTime->new(year => $year,
1290 $dt->add(seconds => -$offset);
1291 my $dt_string = DateTime::Format::MySQL->format_datetime($dt);
1293 # create end of term datetime
1294 # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, this function add simply 30 days...
1295 my $dt_end = DateTime->new(year => $year,
1298 if ($target eq "month") {
1299 $dt_end->add(months => 1);
1301 elsif ($target eq "year") {
1302 $dt_end->add(years => 1);
1304 $dt->add(seconds => -$offset);
1305 my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);
1307 return wantarray ? ($target, $dt_string, $dt_end_string)
1308 : { target => $target,
1309 start => $dt_string,
1310 end => $dt_end_string };
1314 ########## Delete method
1316 =head2 generic_delete(table => $table, uniques => $uniques, keys => $keys, params => $params)
1318 DELETE item from table
1332 ARRAYREF to unique keys. 'unique key' is a column name
1333 defined with 'UNIQUE' or 'PRIMARY'.
1339 ARRAYREF to acceptable keys (column names)
1345 HASHREF to query parameters
1349 when list context, returns ($clause, \@values).
1350 when scalar context, return hashref like:
1351 { clause => $clause, values => \@values };
1357 sub generic_delete {
1360 my $table = $args->{table} || $self->primary_table;
1362 $self->set_error("table no given");
1365 my $params = $args->{params} || {};
1367 if ($args->{uniques}) {
1368 warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
1369 $args->{unique_keys} ||= $args->{uniques};
1371 my $uniques = $args->{unique_keys};
1372 my $keys = $args->{keys};
1373 my $timestamp = $args->{timestamp};
1376 my ($values, $orderby, $limit, $where, $unique_query);
1378 ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
1380 timestamp => $timestamp,
1382 push @arguments, @$values if @$values;
1384 my $dbh = $self->connect_db;
1385 my $generic_sql = <<"EOSQL";
1390 my $sql = $args->{sql} || $generic_sql;
1391 $self->_last_query($sql, \@arguments);
1393 my $rs = $dbh->do($sql, undef, @arguments);
1395 $self->set_error("delete_failed", $dbh->errstr, $dbh->err);
1396 $self->disconnect_db;
1399 $self->disconnect_db;
1404 ######### data export / import functions
1406 =head2 export_json()
1408 export as json data.
1410 $obj->export_json(file => "foobar.json",
1412 exclude => [qw(foo bar)],
1413 sort_key => [qw(hoge moge)],
1432 my $table = $params->{table};
1435 my $keys = $params->{sort_key} || [];
1440 my $query_params = {};
1441 if ($params->{sort_key}) {
1442 $query_params->{order_by} = $keys;
1445 my $datas = $self->generic_select(table => $table, keys => $keys, params => $query_params);
1448 my $exclude = $params->{exclude} || [];
1449 if (!ref($exclude)) {
1450 $exclude = [$exclude];
1453 for my $data (@$datas) {
1454 for my $k (@$exclude) {
1459 #warn Dumper($datas);
1465 if ($params->{file}) {
1466 my $bin_data = $j->utf8->encode($datas);
1467 my $fh = FileHandle->new($params->{file}, "w");
1469 $self->set_error($!);
1472 $fh->print($bin_data);
1476 return JSON::to_json($datas);
1479 =head2 import_json()
1481 export as json data.
1483 $obj->export_json(file => "foobar.json",
1485 exclude => [qw(foo bar)],
1489 $obj->export_json(json => '{ "foo": "bar", "hoge": 1 }',
1491 exclude => [qw(foo bar)],
1511 my $table = $params->{table};
1514 if ($params->{json} && $params->{file}) {
1519 if ($params->{file}) {
1520 my $fh = FileHandle->new($params->{file}, "r");
1522 $self->set_error($!);
1525 my $json = do { local $/; <$fh> };
1527 $datas = JSON::decode_json($json);
1529 if ($params->{json}) {
1530 $datas = JSON::from_json($params->{json});
1534 return if ref($datas) ne "ARRAY";
1536 my $primary_key = $params->{unique_key};
1537 my $exclude = $params->{exclude} || [];
1538 if (!ref($exclude)) {
1539 $exclude = [$exclude];
1542 my $dbh = $self->start_transaction;
1544 for my $data (@$datas) {
1545 for my $k (@$exclude) {
1553 for my $k (keys(%$data)) {
1554 #my $quoted_k = '`' . $k . '`';
1555 my $quoted_k = $dbh->quote_identifier($k);
1556 push @cols, $quoted_k;
1558 push @vals, $data->{$k};
1559 if ($k ne $primary_key) {
1560 push @updates, "$quoted_k = ?";
1561 push @update_vals, $data->{$k};
1564 my $cols_clause = join(", ", @cols);
1565 my $placeholders = join(", ", @plhs);
1566 my $update_clauses = join(", ", @updates);
1568 my $sql = <<"EOSQL";
1573 ON DUPLICATE KEY UPDATE
1576 push @vals, @update_vals;
1577 $self->_last_query($sql, \@vals);
1579 my $rs = $dbh->do($sql, undef, @vals);
1590 ########## virtual method for O/R mapping function
1591 sub key_definition { return {}; }
1594 my ($self, $target) = @_;
1595 return {} if !$target;
1596 return $target if ref($target) eq "HASH";
1597 if (!ref($target)) {
1598 $target = [$target];
1601 my $def = $self->key_definition || {};
1603 if ($def->{basename}) {
1604 $basename = "$def->{basename}.";
1606 elsif ($def->{table}) {
1607 $basename = "$def->{table}.";
1611 for my $uk (@$target) {
1612 $rs->{$uk} = "$basename$uk";
1613 for my $k (keys %{$def->{aliases}}) {
1614 if ($uk eq $def->{aliases}->{$k}) {
1615 $rs->{$k} = "$basename$uk";
1625 my $def = $self->key_definition || {};
1626 return $def->{table};
1631 my $def = $self->key_definition || {};
1632 return $self->_build_keys($def->{primary});
1637 my $def = $self->key_definition || {};
1638 return $self->_build_keys($def->{timestamp});
1643 my $def = $self->key_definition || {};
1644 return $self->_build_keys($def->{datetime});
1649 my $def = $self->key_definition || {};
1650 return $self->_build_keys($def->{other});
1655 my $def = $self->key_definition || {};
1656 my $uniq = $self->_build_keys($def->{unique});
1657 my $primary = $self->_build_keys($def->{primary});
1658 return $self->_merge_keys($uniq, $primary);
1663 my $def = $self->key_definition || {};
1664 return $self->_build_keys($def->{addable});
1670 my $def = $self->key_definition || {};
1673 if (defined $def->{$target}) {
1674 push @keys, @{_to_array($def->{$target})};
1676 elsif ($target eq "all") {
1677 push @keys, $def->{primary} if $def->{primary};
1679 @{_to_array($def->{unique})},
1680 @{_to_array($def->{datetime})},
1681 @{_to_array($def->{addable})},
1682 @{_to_array($def->{other})};
1684 elsif ($target eq "non-unique") {
1686 @{_to_array($def->{datetime})},
1687 @{_to_array($def->{addable})},
1688 @{_to_array($def->{other})};
1690 elsif ($target eq "updatable") {
1692 @{_to_array($def->{unique})},
1693 @{_to_array($def->{datetime})},
1694 @{_to_array($def->{addable})},
1695 @{_to_array($def->{other})};
1698 return $self->_build_keys(\@keys) if @keys;
1704 return [] if !defined $item;
1705 return $item if (ref($item) eq 'ARRAY');
1706 return [$item] if !ref($item);
1715 next if !defined $keys;
1716 if (ref($keys) eq 'ARRAY') {
1717 for my $k (@$keys) {
1721 elsif (ref($keys) eq 'HASH') {
1722 for my $k (keys %$keys) {
1723 $rs->{$k} = $keys->{$k};
1726 elsif (!ref($keys)) {
1727 $rs->{$keys} = $keys;
1734 ########## END OF FILE