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 {
182 if ($self->transaction_mode) {
183 die "already transaction started";
187 $self->{_transactions} = 2;
188 $self->{_tr_dbh} = $dbh;
196 return if !$self->transaction_mode;
198 if ($self->{_transactions} == 1) {
199 $self->{_tr_dbh}->commit;
200 $self->{_tr_dbh}->disconnect;
202 delete $self->{_tr_dbh};
204 $self->{_transactions} -= 1;
209 return if !$self->transaction_mode;
211 #$self->set_error($self->{_tr_dbh}->errstr);
213 $self->{_tr_dbh}->rollback;
214 $self->{_tr_dbh}->disconnect;
215 delete $self->{_tr_dbh};
216 $self->{_transactions} = 0;
219 ########## Utility functions
222 my ($self, $table) = @_;
224 my $dbh = $self->connect_db;
225 my $DB_NAME = $self->{options}->{Database}->{name};
228 SELECT * FROM information_schema.TABLES
229 WHERE TABLE_SCHEMA = ?
233 my $sth = $dbh->prepare($sql);
234 $sth->execute($DB_NAME, $table);
235 my $rs = $sth->fetchall_arrayref({});
236 $self->disconnect_db;
245 if ($self->options->{readonly}) {
246 $self->set_error("readonly mode");
252 ########## error handling
255 $self->{_error} = undef;
256 $self->{_errorno} = undef;
260 my ($self, $error, $errorno) = @_;
261 $self->{_error} = $error;
262 $self->{_errorno} = $errorno if $errorno;
265 my ($self, $errorno) = @_;
266 $self->{_errorno} = $errorno;
270 my ($self, $err) = @_;
271 $self->{_error} = $err if defined $err;
272 return $self->{_error};
276 my ($self, $errno) = @_;
277 $self->{_errorno} = $errno if defined $errno;
278 return $self->{_errorno};
281 ########## Utility functions
282 sub calculate_time_range {
283 my ($self, $params) = @_;
285 my $date = $params->{date};
286 my $year = $params->{year};
287 my $month = $params->{month};
288 my $day = $params->{day};
290 my $years = $params->{years};
291 my $months = $params->{months};
292 my $days = $params->{days};
293 my $weeks = $params->{weeks};
294 my $hours = $params->{hours};
295 my $minutes = $params->{minutes};
297 my $until = $params->{until};
298 my $since = $params->{since};
300 my $offset_sec = $params->{offset_sec};
301 my ($dt_since, $dt_until);
303 my $range_mode = ($years || $months || $days || $weeks || $hours || $minutes);
307 if (ref($since) eq 'HASH') {
310 elsif ($since =~ m/^\d{4}-\d{2}-\d{2}$/) {
311 $dt_since = DateTime::Format::MySQL->parse_date($since);
314 $dt_since = DateTime::Format::MySQL->parse_datetime($since);
318 if (ref($until) eq 'HASH') {
321 elsif ($until =~ m/^\d{4}-\d{2}-\d{2}$/) {
322 $dt_until = DateTime::Format::MySQL->parse_date($until);
325 $dt_until = DateTime::Format::MySQL->parse_datetime($until);
329 $dt_since = DateTime->new(year => $year,
330 month => $month || 1,
334 if ($date eq 'today') {
335 $dt_since = DateTime->today;
338 $dt_since = DateTime::Format::MySQL->parse_date($date);
349 $dt_until = $dt_since->clone;
350 $dt_until->add("years" => $years) if $years;
351 $dt_until->add("months" => $months) if $months;
352 $dt_until->add("days" => $days) if $days;
353 $dt_until->add("weeks" => $weeks) if $weeks;
354 $dt_until->add("hours" => $hours) if $hours;
355 $dt_until->add("minutes" => $minutes) if $minutes;
358 $dt_until = DateTime->now;
359 $dt_since = $dt_until->clone;
360 $dt_since->add("years" => -$years) if $years;
361 $dt_since->add("months" => -$months) if $months;
362 $dt_since->add("days" => -$days) if $days;
363 $dt_since->add("weeks" => -$weeks) if $weeks;
364 $dt_since->add("hours" => -$hours) if $hours;
365 $dt_since->add("minutes" => -$minutes) if $minutes;
370 $term = "month" if $month;
371 $term = "day" if $day;
373 $dt_until = $dt_since->clone;
374 $dt_until->add("${term}s" => 1);
378 $dt_since->add(seconds => -$offset_sec) if $dt_since;
379 $dt_until->add(seconds => -$offset_sec) if $dt_until;
383 $from = DateTime::Format::MySQL->format_datetime($dt_since) if $dt_since;
384 $to = DateTime::Format::MySQL->format_datetime($dt_until) if $dt_until;
390 =head2 build_where_clause(unique_keys => $unique_keys,
392 timestamps => $timestamps,
393 where => $where_params,
396 build SQL WHERE clause.
406 STRING or ARRAYREF or HASHREF.
407 If STRING, $keys contains unique keys.
408 If ARRAREF, @$keys contains unique keys.
409 If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.
411 'unique key' is a column name defined with 'UNIQUE' or 'PRIMARY'.
413 If you want to use key which is not correspod to table's column,
414 use alias. When aliases given, the aliases are replaced to keys in SQL.
420 STRING or ARRAYREF or HASHREF.
421 If STRING, $keys contains non-unique keys.
422 If ARRAREF, @$keys contains non-unique keys.
423 If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.
429 HASHREF to query parameters
433 when list context, returns ($clause, \@values, $unique).
434 when scalar context, return hashref like:
435 { clause => $clause, values => \@values, unique => $unique };
441 sub build_where_clause {
447 if ($args->{keys} || $args->{unique_keys}) {
448 $uniques = $args->{unique_keys};
449 $keys = $args->{keys};
452 $uniques = $self->unique_keys;
453 $keys = $self->get_keys("non-unique");
455 my $timestamp = $args->{timestamp} || $self->{timestamp};
457 my $params = $args->{params};
458 my $where = $args->{where} || $params;
463 # check and process "OR" parameter
464 if (defined $where->{OR}) {
465 my $sub_params = $where->{OR};
466 croak "invalid OR parameter" if (ref($sub_params) ne "HASH");
468 my ($sub_clause, $sub_values, $sub_unique) = $self->_parse_where_clause($uniques, $keys, $timestamp, $sub_params);
469 my $or_clause = join(" OR ", @$sub_clause);
471 push @clauses, "( $or_clause )";
472 push @values, @$sub_values;
476 my ($sub_clauses, $sub_values, $unique) = $self->_parse_where_clause($uniques, $keys, $timestamp, $where);
477 push @clauses, @$sub_clauses;
478 push @values, @$sub_values;
482 $clause = "WHERE " . join(" AND ", @clauses);
485 return wantarray ? ($clause, \@values, $unique)
486 : { clause => $clause,
488 unique => $unique, };
493 sub _parse_where_clause {
494 my ($self, $uniques, $keys, $timestamp, $params) = @_;
501 my $decode_param = sub {
504 # simply equal clause
506 return ("$k = ?", $v);
510 if (ref($v) eq 'ARRAY') {
511 my $placeholder = join(", ", map { "?" } @$v);
512 return ("$k IN ($placeholder)", $v);
515 # gt/lt/ge/le (>, <, >=, <=)
516 if (ref($v) eq 'HASH') {
517 return ("$k > ?", $v->{gt}) if defined $v->{gt};
518 return ("$k < ?", $v->{lt}) if defined $v->{lt};
519 return ("$k >= ?", $v->{ge}) if defined $v->{ge};
520 return ("$k <= ?", $v->{le}) if defined $v->{le};
523 die "invalid query parameter: $k, $v";
527 my $decode_and_set_params = sub {
528 my ($k, $p, $is_unique) = @_;
529 my ($c, $v) = $decode_param->($k, $p);
530 push @clauses, $c if $c;
532 if (ref($v) eq 'ARRAY') {
537 $unique = 1 if $is_unique;
543 # reqularize keys to hashref
544 my $ky = $self->_keys_to_hash($keys);
546 for my $k (keys(%$ky)) {
547 if (defined $params->{$k}) {
548 $decode_and_set_params->($ky->{$k}, $params->{$k});
552 # reqularize uniques to hashref
553 my $u = $self->_keys_to_hash($uniques);
555 for my $k (keys(%$u)) {
556 if (defined $params->{$k}) {
557 $decode_and_set_params->($u->{$k}, $params->{$k}, 1);
562 # year, month, day, years, months, days, date
564 my ($begin, $end) = $self->calculate_time_range($params);
566 push @clauses, "$timestamp >= ?";
567 push @values, $begin;
570 push @clauses, "$timestamp < ?";
575 return (\@clauses, \@values, $unique);
578 # reqularize keys to hashref
580 my ($self, $keys) = @_;
581 return if !defined $keys;
584 if(ref($keys) eq 'HASH') {
587 elsif (ref($keys) eq 'ARRAY') {
593 elsif (!ref($keys)) {
594 $ky = { $keys => $keys };
599 =head2 build_limit_clause(params => \@params)
601 build SQL's LIMIT clause.
611 HASHREF to query parameters
615 when list context, returns ($clause, \@values).
616 when scalar context, return hashref like:
617 { clause => $clause, values => \@values };
623 sub build_limit_clause {
627 my $params = $args->{params};
628 my $limit = $args->{default};
629 my $offset = $args->{default_offset};
631 $limit = $args->{default_limit} if defined $args->{default_limit};
632 $limit = $params->{limit} if defined $params->{limit};
633 $offset = $params->{offset} if defined $params->{offset};
637 if (defined $limit) {
638 push @clauses, "LIMIT ?";
639 push @values, $limit;
641 if (defined $offset) {
642 push @clauses, "OFFSET ?";
643 push @values, $offset;
646 my $clause = join(" ", @clauses);
648 return wantarray ? ($clause, \@values)
649 : { clause => $clause, values => \@values };
652 =head2 build_order_by_clause(keys => $keys, params => \@params)
654 build SQL ORDER BY clause.
665 If ARRAREF, @$keys contains selectSable columns.
666 If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.
670 HASHREF to query parameters
674 when list context, returns ($clause, \@values).
675 when scalar context, return hashref like:
676 { clause => $clause, values => \@values };
682 sub build_order_by_clause {
686 my $unique_keys = $args->{unique_keys} || $self->unique_keys;
687 my $keys = $args->{keys} || $self->get_keys("all");
689 my $params = $args->{params};
690 my $order_by = $params->{order_by};
692 if (!$keys || !$params || !$order_by) {
693 return wantarray ? ("", [])
694 : { clause => "", values => [] };
702 # convert $keys to hash style
703 if (ref($keys) eq 'HASH') {
704 $k_names = [keys %$keys];
707 if (ref($keys) eq 'ARRAY') {
710 elsif (!ref($keys)) {
714 # convert $order_by to hash style
715 if (ref($order_by) eq "ARRAY") {
717 for my $item (@$order_by) {
718 $hash->{$item} = "ASC";
722 elsif (!ref($order_by)) {
723 $order_by = {$order_by => "ASC"};
726 # when $order_by is not ARRAY or SCALAR or HASH,
727 # this block is passed.
728 if (ref($order_by) eq "HASH") {
729 for my $k (keys %$order_by) {
730 next if !any {$_ eq $k} @$k_names;
732 my $order = uc($order_by->{$k});
734 if ($use_alias && $keys->{$k}) {
735 $target = $keys->{$k};
737 push @clauses, "$target $order" if $target;
743 $clause = "ORDER BY " . join(", ", @clauses);
746 return wantarray ? ($clause, \@values)
747 : { clause => $clause, values => \@values };
750 ########## Insert method
754 return if $self->check_readonly;
757 my $table = $args->{table} || $self->primary_table;
759 $self->set_error("table not given", -1);
765 $keys = $self->_build_keys($args->{keys});
768 $keys = $self->get_keys("all");
771 my $params = $args->{params};
773 $self->set_error("params not given", -1);
781 # extract key and values
782 for my $k (keys %$keys) {
783 next if !defined $params->{$k};
784 if (!ref($params->{$k})) {
785 push @cols, $keys->{$k};
786 push @values, $params->{$k};
787 push @placeholders, "?";
789 elsif(ref($params->{$k}) eq "HASH") {
790 for my $subkey (keys %{$params->{$k}}) {
791 if (lc($subkey) eq "function") {
792 push @cols, $keys->{$k};
793 push @placeholders, $params->{$k}->{$subkey};
799 if (!@cols || !@values) {
800 $self->set_error("no valid values", -1);
804 my $cols_clause = join(", ", @cols);
805 my $values_clause = join(", ", @placeholders);
808 my $sql = "INSERT INTO $table ($cols_clause) VALUES ($values_clause)";
809 $self->_last_query($sql, \@values);
812 #warn Dumper @values;
814 my $dbh = $self->connect_db;
815 my $rs = $dbh->do($sql, undef, @values);
816 $self->disconnect_db;
822 ########## Select method
824 =head2 generic_select(table => $table, uniques => $uniques, keys => $keys, params => $params)
826 build SQL's ORDER BY clause.
840 ARRAYREF to unique keys. 'unique key' is a column name
841 defined with 'UNIQUE' or 'PRIMARY'.
847 ARRAYREF to acceptable keys (column names)
853 HASHREF to query parameters
857 when list context, returns ($clause, \@values).
858 when scalar context, return hashref like:
859 { clause => $clause, values => \@values };
868 my $table = $args->{table} || $self->primary_table;
870 $self->set_error("table no given");
873 my $params = $args->{params} || {};
875 if ($args->{uniques}) {
876 warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
877 $args->{unique_keys} ||= $args->{uniques};
879 my $uniques = $args->{unique_keys};
880 my $keys = $args->{keys};
881 my $timestamp = $args->{timestamp};
884 my ($values, $orderby, $limit, $where, $unique_query);
886 ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
888 timestamp => $timestamp,
890 push @arguments, @$values if @$values;
892 ($orderby, $values) = $self->build_order_by_clause(keys => $keys, params => $params);
893 push @arguments, @$values if @$values;
895 ($limit, $values) = $self->build_limit_clause(params => $params);
896 push @arguments, @$values if @$values;
898 my $dbh = $self->connect_db;
899 my $generic_sql = <<"EOSQL";
906 my $sql = $args->{sql} || $generic_sql;
907 $self->_last_query($sql, \@arguments);
909 my $sth = $dbh->prepare($sql);
910 $sth->execute(@arguments);
911 my $rs = $sth->fetchall_arrayref(+{});
913 $self->set_error("select failed", $dbh->errstr, $dbh->err);
914 $self->disconnect_db;
917 $self->disconnect_db;
921 return $rs->[0] if @$rs;
927 ########## Count method
929 =head2 generic_count(
931 target => $target_column,
932 timestamp => $timestamp_column,
936 offset_sec => $offset_sec,
937 join => $join_clause,
938 where => $where_clause)
956 =item $timestamp_column
966 target month (omissible)
970 target day (omissible)
974 additional WHERE clause (must not include 'WHERE' !; omissible)
978 additional JOIN clause (must include 'JOIN' !; omissible)
992 my $table = $params->{table} || $self->primary_table;
993 my $target = $params->{target};
994 my $timestamp = $params->{timestamp} || $self->timestamp;
996 return if (!$table || !$target || !$timestamp || !$params->{year});
999 $term = "month" if !$params->{day};
1000 $term = "year" if !$params->{month};
1002 my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
1003 $year = 1 if (!$year || $year !~ m/^[0-9]{4}$/);
1004 $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
1005 $day = 1 if (!$day || $day !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);
1007 my $offset = $params->{offset_sec} || 0;
1008 $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;
1010 my $dt = DateTime->new(year => $year,
1013 $dt->add(seconds => -$offset);
1014 my $dt_string = DateTime::Format::MySQL->format_datetime($dt);
1016 # create end of term datetime
1017 # we must consider timezone offset, so use relative day/month.
1018 # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, DATE_ADD function add simply 30 days...
1019 my $dt_end = DateTime->new(year => $year,
1022 if ($term eq "month") {
1023 $dt_end->add(months => 1);
1025 elsif ($term eq "year") {
1026 $dt_end->add(years => 1);
1028 $dt->add(seconds => -$offset);
1029 my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);
1033 my $where_clause = "";
1034 if ($params->{where}) {
1035 $where_clause = "$params->{where} AND ";
1037 my $join_clause = $params->{join} || "";
1040 if ($term eq "day") {
1041 # `stories` table not contain display/non-display flag,
1044 SELECT COUNT($table.$target) AS count FROM $table
1047 $table.$timestamp >= ?
1048 AND $table.$timestamp < DATE_ADD(?, INTERVAL 1 DAY)
1050 push @attrs, $dt_string, $dt_string;
1052 elsif ($term eq "month") {
1054 SELECT TIMESTAMPDIFF(DAY, ?, $table.$timestamp) AS day,
1055 COUNT($table.$target) AS count
1059 $table.$timestamp >= ?
1060 AND $table.$timestamp < ?
1061 GROUP BY TIMESTAMPDIFF(DAY, ?, $table.$timestamp)
1064 push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
1066 elsif ($term eq "year") {
1068 SELECT TIMESTAMPDIFF(MONTH, ?, $table.$timestamp) AS month,
1069 COUNT($table.$target) AS count
1073 $table.$timestamp >= ?
1074 AND $table.$timestamp < ?
1075 GROUP BY TIMESTAMPDIFF(MONTH, ?, $table.$timestamp)
1078 push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
1080 my $dbh = $self->connect_db;
1081 my $sth = $dbh->prepare($sql);
1083 $self->_last_query($sql, \@attrs);
1084 $sth->execute(@attrs);
1085 my $rs = $sth->fetchall_arrayref({});
1088 $self->disconnect_db;
1091 $self->disconnect_db;
1095 if ($term eq "day") {
1096 return $rs->[0]->{count};
1098 elsif ($term eq "month") {
1101 elsif ($term eq "year") {
1108 for my $counts (@$rs) {
1109 # day / month is differential from base datetime, so add 1
1110 $hash->{$counts->{$key} + 1} = $counts->{count};
1118 ########## Update method
1120 =head2 generic_update(table => $table,
1121 updatable_keys => $updatables,
1122 addable_keys => $addables,
1123 condition_keys => $conditions,
1126 execute UPDATE SQL command.
1140 ARRAYREF or HASHREF to updatable keys. 'updatable key' is a updatable column name.
1144 ARRAYREF or HASHREF to addable keys. 'addable key' is a addable column name.
1148 ARRAYREF or HASHREF to conditional keys. 'conditional key' is a column name which can use in WHERE clause.
1152 HASHREF to query parameters
1156 when list context, returns ($clause, \@values).
1157 when scalar context, return hashref like:
1158 { clause => $clause, values => \@values };
1164 sub generic_update {
1166 return if $self->check_readonly;
1170 my $table = $args->{table} || $self->primary_table;
1172 $self->set_error("table given", -1);
1176 my $updatables = $args->{updatable_keys} || $self->get_keys("updatable");
1177 my $addables = $args->{addable_keys} || $self->get_keys("addable");
1179 my $conditions = $args->{condition_keys};
1180 my $params = $args->{params} || {};
1181 my $where = $args->{where} || $params->{where};
1184 $conditions = $args->{condition_keys} || $self->get_keys("all");
1187 $conditions = $args->{condition_keys} || $self->get_keys("primary");
1190 if (!$conditions || !%$conditions) {
1191 $self->set_error("no_condition_keys", -1);
1194 my $u_hash = $self->_keys_to_hash($updatables);
1195 #my $a_hash = $self->_keys_to_hash($addables);
1197 # first, create WHERE clause
1198 my ($where_clause, $where_values) = $self->build_where_clause(keys => $conditions,
1201 if (!$where_clause) {
1202 $self->set_error("no_where_clauses", -1);
1210 for my $col (keys %$u_hash) {
1211 next if !defined $params->{$col};
1212 my $c = $u_hash->{$col};
1214 # if $params->{$col} is SCALAR, set to the value
1215 if (!ref($params->{$col})) {
1216 push @values, $params->{$col};
1217 push @set_clauses, "$c = ?";
1219 # if $params->{$col} is HASH, do given operation
1220 elsif (ref($params->{$col}) eq 'HASH') {
1223 for my $k (keys %{$params->{$col}}) {
1224 $p->{lc($k)} = $params->{$col}->{$k};
1227 if (defined $p->{max} && defined $p->{min}) {
1228 push @values, $p->{min};
1229 push @values, $p->{max};
1230 push @values, $p->{add};
1231 push @set_clauses, "$c = GREATEST(?, LEAST(?, $c + ?))";
1233 elsif (defined $p->{max}) {
1234 push @values, $p->{min};
1235 push @values, $p->{add};
1236 push @set_clauses, "$c = GREATEST(?, $c + ?)";
1238 elsif (defined $p->{min}) {
1239 push @values, $p->{min};
1240 push @values, $p->{add};
1241 push @set_clauses, "$c = LEAST(?, $c + ?)";
1244 push @values, $p->{add};
1245 push @set_clauses, "$c = $c + ?";
1248 if ($p->{function}) {
1249 push @set_clauses, "$c = ($p->{function})";
1254 return 0 if !@set_clauses;
1255 my $set_clause = join(", ", @set_clauses);
1257 my $sql = "UPDATE $table SET $set_clause $where_clause";
1258 push @values, @$where_values;
1259 $self->_last_query($sql, \@values);
1262 #warn Dumper @values;
1264 my $dbh = $self->connect_db;
1265 my $rs = $dbh->do($sql, undef, @values);
1266 $self->disconnect_db;
1271 ########## count helper
1273 sub build_interval_times {
1276 my $params = $attr->{params} || {};
1279 return if !$params->{year};
1280 $target = "month" if !$params->{day};
1281 $target = "year" if !$params->{month};
1283 my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
1284 $year = 1 if (!$year || $year !~ m/^[0-9]{4}$/);
1285 $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
1286 $day = 1 if (!$day || $day !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);
1288 my $offset = $params->{offset_sec} || 0;
1289 $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;
1291 my $dt = DateTime->new(year => $year,
1294 $dt->add(seconds => -$offset);
1295 my $dt_string = DateTime::Format::MySQL->format_datetime($dt);
1297 # create end of term datetime
1298 # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, this function add simply 30 days...
1299 my $dt_end = DateTime->new(year => $year,
1302 if ($target eq "month") {
1303 $dt_end->add(months => 1);
1305 elsif ($target eq "year") {
1306 $dt_end->add(years => 1);
1308 $dt->add(seconds => -$offset);
1309 my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);
1311 return wantarray ? ($target, $dt_string, $dt_end_string)
1312 : { target => $target,
1313 start => $dt_string,
1314 end => $dt_end_string };
1318 ########## Delete method
1320 =head2 generic_delete(table => $table, uniques => $uniques, keys => $keys, params => $params)
1322 DELETE item from table
1336 ARRAYREF to unique keys. 'unique key' is a column name
1337 defined with 'UNIQUE' or 'PRIMARY'.
1343 ARRAYREF to acceptable keys (column names)
1349 HASHREF to query parameters
1353 when list context, returns ($clause, \@values).
1354 when scalar context, return hashref like:
1355 { clause => $clause, values => \@values };
1361 sub generic_delete {
1364 my $table = $args->{table} || $self->primary_table;
1366 $self->set_error("table no given");
1369 my $params = $args->{params} || {};
1371 if ($args->{uniques}) {
1372 warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
1373 $args->{unique_keys} ||= $args->{uniques};
1375 my $uniques = $args->{unique_keys};
1376 my $keys = $args->{keys};
1377 my $timestamp = $args->{timestamp};
1380 my ($values, $orderby, $limit, $where, $unique_query);
1382 ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
1384 timestamp => $timestamp,
1386 push @arguments, @$values if @$values;
1388 my $dbh = $self->connect_db;
1389 my $generic_sql = <<"EOSQL";
1394 my $sql = $args->{sql} || $generic_sql;
1395 $self->_last_query($sql, \@arguments);
1397 my $rs = $dbh->do($sql, undef, @arguments);
1399 $self->set_error("delete_failed", $dbh->errstr, $dbh->err);
1400 $self->disconnect_db;
1403 $self->disconnect_db;
1408 ######### data export / import functions
1410 =head2 export_json()
1412 export as json data.
1414 $obj->export_json(file => "foobar.json",
1416 exclude => [qw(foo bar)],
1417 sort_key => [qw(hoge moge)],
1436 my $table = $params->{table};
1439 my $keys = $params->{sort_key} || [];
1444 my $query_params = {};
1445 if ($params->{sort_key}) {
1446 $query_params->{order_by} = $keys;
1449 my $datas = $self->generic_select(table => $table, keys => $keys, params => $query_params);
1452 my $exclude = $params->{exclude} || [];
1453 if (!ref($exclude)) {
1454 $exclude = [$exclude];
1457 for my $data (@$datas) {
1458 for my $k (@$exclude) {
1463 #warn Dumper($datas);
1469 if ($params->{file}) {
1470 my $bin_data = $j->utf8->encode($datas);
1471 my $fh = FileHandle->new($params->{file}, "w");
1473 $self->set_error($!);
1476 $fh->print($bin_data);
1480 return JSON::to_json($datas);
1483 =head2 import_json()
1485 export as json data.
1487 $obj->export_json(file => "foobar.json",
1489 exclude => [qw(foo bar)],
1493 $obj->export_json(json => '{ "foo": "bar", "hoge": 1 }',
1495 exclude => [qw(foo bar)],
1515 my $table = $params->{table};
1518 if ($params->{json} && $params->{file}) {
1523 if ($params->{file}) {
1524 my $fh = FileHandle->new($params->{file}, "r");
1526 $self->set_error($!);
1529 my $json = do { local $/; <$fh> };
1531 $datas = JSON::decode_json($json);
1533 if ($params->{json}) {
1534 $datas = JSON::from_json($params->{json});
1538 return if ref($datas) ne "ARRAY";
1540 my $primary_key = $params->{unique_key};
1541 my $exclude = $params->{exclude} || [];
1542 if (!ref($exclude)) {
1543 $exclude = [$exclude];
1546 my $dbh = $self->start_transaction;
1548 for my $data (@$datas) {
1549 for my $k (@$exclude) {
1557 for my $k (keys(%$data)) {
1558 #my $quoted_k = '`' . $k . '`';
1559 my $quoted_k = $dbh->quote_identifier($k);
1560 push @cols, $quoted_k;
1562 push @vals, $data->{$k};
1563 if ($k ne $primary_key) {
1564 push @updates, "$quoted_k = ?";
1565 push @update_vals, $data->{$k};
1568 my $cols_clause = join(", ", @cols);
1569 my $placeholders = join(", ", @plhs);
1570 my $update_clauses = join(", ", @updates);
1572 my $sql = <<"EOSQL";
1577 ON DUPLICATE KEY UPDATE
1580 push @vals, @update_vals;
1581 $self->_last_query($sql, \@vals);
1583 my $rs = $dbh->do($sql, undef, @vals);
1594 ########## virtual method for O/R mapping function
1595 sub key_definition { return {}; }
1598 my ($self, $target) = @_;
1599 return {} if !$target;
1600 return $target if ref($target) eq "HASH";
1601 if (!ref($target)) {
1602 $target = [$target];
1605 my $def = $self->key_definition || {};
1607 if ($def->{basename}) {
1608 $basename = "$def->{basename}.";
1610 elsif ($def->{table}) {
1611 $basename = "$def->{table}.";
1615 for my $uk (@$target) {
1616 $rs->{$uk} = "$basename$uk";
1617 for my $k (keys %{$def->{aliases}}) {
1618 if ($uk eq $def->{aliases}->{$k}) {
1619 $rs->{$k} = "$basename$uk";
1629 my $def = $self->key_definition || {};
1630 return $def->{table};
1635 my $def = $self->key_definition || {};
1636 return $self->_build_keys($def->{primary});
1641 my $def = $self->key_definition || {};
1642 return $self->_build_keys($def->{timestamp});
1647 my $def = $self->key_definition || {};
1648 return $self->_build_keys($def->{datetime});
1653 my $def = $self->key_definition || {};
1654 return $self->_build_keys($def->{other});
1659 my $def = $self->key_definition || {};
1660 my $uniq = $self->_build_keys($def->{unique});
1661 my $primary = $self->_build_keys($def->{primary});
1662 return $self->_merge_keys($uniq, $primary);
1667 my $def = $self->key_definition || {};
1668 return $self->_build_keys($def->{addable});
1674 my $def = $self->key_definition || {};
1677 if (defined $def->{$target}) {
1678 push @keys, @{_to_array($def->{$target})};
1680 elsif ($target eq "all") {
1681 push @keys, $def->{primary} if $def->{primary};
1683 @{_to_array($def->{unique})},
1684 @{_to_array($def->{datetime})},
1685 @{_to_array($def->{addable})},
1686 @{_to_array($def->{other})};
1688 elsif ($target eq "non-unique") {
1690 @{_to_array($def->{datetime})},
1691 @{_to_array($def->{addable})},
1692 @{_to_array($def->{other})};
1694 elsif ($target eq "updatable") {
1696 @{_to_array($def->{unique})},
1697 @{_to_array($def->{datetime})},
1698 @{_to_array($def->{addable})},
1699 @{_to_array($def->{other})};
1702 return $self->_build_keys(\@keys) if @keys;
1708 return [] if !defined $item;
1709 return $item if (ref($item) eq 'ARRAY');
1710 return [$item] if !ref($item);
1719 next if !defined $keys;
1720 if (ref($keys) eq 'ARRAY') {
1721 for my $k (@$keys) {
1725 elsif (ref($keys) eq 'HASH') {
1726 for my $k (keys %$keys) {
1727 $rs->{$k} = $keys->{$k};
1730 elsif (!ref($keys)) {
1731 $rs->{$keys} = $keys;
1738 ########## END OF FILE