# Copyright 2001, 2002 Benjamin Trott. This code cannot be redistributed without
# permission from www.movabletype.org.
#
# $Id: mysql.pm,v 1.21 2002/10/30 00:48:31 btrott Exp $

## xxx todo:
## compatibility with other DBI drivers:
##     * generate_id needs to support sequences (and whatever pg uses)
##     * will the temporary table stuff work with different drivers?

package MT::ObjectDriver::DBI::mysql;
use strict;

use DBI;

use MT;
use MT::Util qw( offset_time_list );
use MT::ObjectDriver;
@MT::ObjectDriver::DBI::mysql::ISA = qw( MT::ObjectDriver );

use constant TEMP_TABLE => 'tempTable';

sub init {
    my $driver = shift;
    $driver->SUPER::init(@_);
    my $cfg = $driver->cfg;
    my $dsn = 'dbi:mysql:database=' . $cfg->Database;
    $dsn .= ';hostname=' . $cfg->DBHost if $cfg->DBHost;
    $dsn .= ';mysql_socket=' . $cfg->DBSocket if $cfg->DBSocket;
    $driver->{dbh} = DBI->connect($dsn, $cfg->DBUser, $cfg->DBPassword,
        { RaiseError => 0, PrintError => 0 })
        or return $driver->error(MT->translate("Connection error: [_1]",
             $DBI::errstr));
    $driver;
}

sub _build_sql {
    my($class, $terms, $args, $tbl) = @_;
    my(@bind, @terms);
    if ($terms) {
        if (!ref($terms)) {
            return('', [ "${tbl}_id = ?" ], [ $terms ]);
        }
        for my $col (keys %$terms) {
            my $term = '';
            if (ref($terms->{$col}) eq 'ARRAY') {
                if ($args->{range} && $args->{range}{$col}) {
                    my($start, $end) = @{ $terms->{$col} };
                    $term = "${tbl}_$col > ?", push(@bind, $start) if $start;
                    $term .= " and " if $start && $end;
                    $term .= "${tbl}_$col < ?", push(@bind, $end) if $end;
                }
            } else {
                $term = "${tbl}_$col = ?";
                push @bind, $terms->{$col};
            }
            push @terms, "($term)";
        }
    }
    if (my $sv = $args->{start_val}) {
        my $col = $args->{'sort'} || $class->primary_key;
        my $cmp = $args->{direction} eq 'descend' ? '<' : '>';
        push @terms, "(${tbl}_$col $cmp ?)";
        push @bind, $sv;
    }
    my $sql = '';
    if ($args->{'sort'} || $args->{direction}) {
        my $order = $args->{'sort'} || 'id';
        my $dir = $args->{direction} &&
                  $args->{direction} eq 'descend' ? 'desc' : 'asc';
        $sql .= "order by ${tbl}_$order $dir\n";
    }
    ($sql, \@terms, \@bind);
}

sub _prepare_from_where {
    my $driver = shift;
    my($class, $terms, $args) = @_;
    my($sql, @bind, $is_tmp);

    ## Prefix the table name with 'mt_' to make it distinct.
    my $tbl = $class->datasource;
    my $tbl_name = 'mt_' . $tbl;

    my($w_sql, $w_terms, $w_bind) = ('', [], []);
    if (my $join = $args->{'join'}) {
        my($j_class, $j_col, $j_terms, $j_args) = @$join;
        my $j_tbl = $j_class->datasource;
        my $j_tbl_name = 'mt_' . $j_tbl;

        ## If we are doing a join where we want distinct and "order by",
        ## we need to use a temporary table to get around a bug in
        ## MySQL. So we create a new temporary table, then adjust the
        ## returned SQL to select from that table.
        if ($j_args->{unique} && $j_args->{'sort'}) {
            ##     create temporary table tempTable
            ##     select <all foo cols>, <bar sort key> as temp_sort_key
            ##     from foo, bar
            ##     where foo.id = bar.foo_id
            my $dir = $j_args->{direction} eq 'descend' ? 'desc' : 'asc';
            my $ct_sql = "create temporary table tempTable\nselect ";
            my $cols = $class->column_names;
            $ct_sql .= join(', ', map "${tbl}_$_ as tempTable_$_", @$cols) .
                       ", ${j_tbl}_$j_args->{'sort'} as temp_sort_key\n";
            $ct_sql .= "from $tbl_name, $j_tbl_name\n";
            my($junk, $ct_terms, $ct_bind) =
                _build_sql($j_class, $j_terms, $j_args, $j_tbl);
            push @$ct_terms, "(${tbl}_id = ${j_tbl}_$j_col)";
            $ct_sql .= "where " . join ' and ', @$ct_terms if @$ct_terms;
            $ct_sql .= " order by ${j_tbl}_$j_args->{'sort'} $dir";

            my $dbh = $driver->{dbh};
            my $sth = $dbh->prepare($ct_sql) or return;
            $sth->execute(@$ct_bind) or return;
            $sth->finish;

            ##     select distinct <all foo cols>
            ##     from tempTable
            ##     order by temp_sort_key <asc|desc>
            $sql = "from tempTable\n";
            $w_sql = "order by temp_sort_key $dir\n";
            if (my $n = $j_args->{limit}) {
                $n =~ s/\D//g;   ## Get rid of any non-numerics.
                $w_sql .= sprintf "limit %s%d\n",
                    ($args->{offset} ? "$args->{offset}," : ""), $n;
            }
            $is_tmp = 1;
        } else {
            $sql = "from $tbl_name, $j_tbl_name\n";
            ($w_sql, $w_terms, $w_bind) =
                _build_sql($j_class, $j_terms, $j_args, $j_tbl);
            push @$w_terms, "${tbl}_id = ${j_tbl}_$j_col";

            ## We are doing a join, but some args and terms may have been
            ## specified for the "outer" piece of the join--for example, if
            ## we are doing a join of entry and comments where we end up with
            ## entries, sorted by the created_on date in the entry table, or
            ## filtered by author ID. In that case the sort or author ID will
            ## be specified in the spec for the Entry load, not for the join
            ## load.
            my($o_sql, $o_terms, $o_bind) =
                _build_sql($class, $terms, $args, $tbl);
            $w_sql .= $o_sql;
            if ($o_terms && @$o_terms) {
                push @$w_terms, @$o_terms;
                push @$w_bind, @$o_bind;
            }
        }
    } else {
        $sql = "from $tbl_name\n";
        ($w_sql, $w_terms, $w_bind) = _build_sql($class, $terms, $args, $tbl);
    }
    $sql .= "where " . join(' and ', @$w_terms) . "\n" if @$w_terms;
    $sql .= $w_sql;
    @bind = @$w_bind;
    if (my $n = $args->{limit}) {
        $n =~ s/\D//g;   ## Get rid of any non-numerics.
        $sql .= sprintf "limit %s%d\n",
            ($args->{offset} ? "$args->{offset}," : ""), $n;
    }
    ($sql, \@bind, $is_tmp);
}

sub load_iter {
    my $driver = shift;
    my($class, $terms, $args) = @_;
    my($sql, $bind, $is_tmp) =
        $driver->_prepare_from_where($class, $terms, $args);
    my $tbl = $is_tmp ? TEMP_TABLE : $class->datasource;
    my(%rec, @bind, @cols);
    my $cols = $class->column_names;
    for my $col (@$cols) {
        push @cols, $col;
        push @bind, \$rec{$col};
    }
    my $tmp = "select ";
    $tmp .= "distinct " if $args->{'join'} && $args->{'join'}[3]{unique};
    $tmp .= join(', ', map "${tbl}_$_", @cols) . "\n";
    $sql = $tmp . $sql;
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql) or return sub { };
    $sth->execute(@$bind) or return sub { };
    $sth->bind_columns(undef, @bind);
    sub {
        unless ($sth->fetch) {
            $sth->finish;
            if ($is_tmp) {
                $dbh->do("drop table " . TEMP_TABLE);
            }
            return;
        }
        my $obj = $class->new;
        ## The MySQL created_on columns are 'datetime' columns, which
        ## means they are returned in '2002-06-28 00:39:54' format. We
        ## want them in '20020628003954' format, so if the record has
        ## a created_on column, we strip out the punctuation.
        if ($rec{created_on}) {
            $rec{created_on} =~ tr/\- ://d;
        }
        $obj->set_values(\%rec);
        $obj;
    };
}

sub load {
    my $driver = shift;
    my($class, $terms, $args) = @_;
    my($sql, $bind, $is_tmp) =
        $driver->_prepare_from_where($class, $terms, $args);
    my $tbl = $is_tmp ? TEMP_TABLE : $class->datasource;
    my(%rec, @bind, @cols);
    my $cols = $class->column_names;
    for my $col (@$cols) {
        push @cols, $col;
        push @bind, \$rec{$col};
    }
    my $tmp = "select ";
    $tmp .= "distinct " if $args->{'join'} && $args->{'join'}[3]{unique};
    $tmp .= join(', ', map "${tbl}_$_", @cols) . "\n";
    $sql = $tmp . $sql;
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql) or return;
    $sth->execute(@$bind) or return;
    $sth->bind_columns(undef, @bind);
    my @objs;
    while ($sth->fetch) {
        my $obj = $class->new;
        ## The MySQL created_on columns are 'datetime' columns, which
        ## means they are returned in '2002-06-28 00:39:54' format. We
        ## want them in '20020628003954' format, so if the record has
        ## a created_on column, we strip out the punctuation.
        if ($rec{created_on}) {
            $rec{created_on} =~ tr/\- ://d;
        }
        $obj->set_values(\%rec);
        return $obj unless wantarray;
        push @objs, $obj;
    }
    $sth->finish;
    if ($is_tmp) {
        $dbh->do("drop table " . TEMP_TABLE);
    }
    @objs;
}

sub count {
    my $driver = shift;
    my($class, $terms, $args) = @_;
    my($sql, $bind) = $driver->_prepare_from_where($class, $terms, $args);
    $sql = "select count(*)\n" . $sql;
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql) or return;
    $sth->execute(@$bind) or return;
    $sth->bind_columns(undef, \my($count));
    $sth->fetch or return;
    $sth->finish;
    $count;
}

sub exists {
    my $driver = shift;
    my($obj) = @_;
    return unless $obj->id;
    my $tbl = $obj->datasource;
    my $sql = "select 1 from mt_$tbl where ${tbl}_id = ?";
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql) or return;
    $sth->execute($obj->id) or return;
    my $exists = $sth->fetch;
    $sth->finish;
    $exists;
}

sub save {
    my $driver = shift;
    my($obj) = @_;
    if ($driver->exists($obj)) {
        return $driver->update($obj);
    } else {
        return $driver->insert($obj);
    }
}

sub insert {
    my $driver = shift;
    my($obj) = @_;
    my $cols = $obj->column_names;
    unless ($obj->id) {
        ## If we don't already have an ID assigned for this object, we
        ## may need to generate one (depending on the underlying DB
        ## driver). If the driver gives us a new ID, we insert that into
        ## the new record; otherwise, we assume that the DB is using an
        ## auto-increment column of some sort, so we don't specify an ID
        ## at all.
        my $id = $driver->generate_id($obj);
        if ($id) {
            $obj->id($id);
        } else {
            $cols = [ grep $_ ne 'id', @$cols ];
        }
    }
    my $tbl = $obj->datasource;
    my $sql = "insert into mt_$tbl\n";
    $sql .= '(' . join(', ', map "${tbl}_$_", @$cols) . ')' . "\n" .
            'values (' . join(', ', ('?') x @$cols) . ')' . "\n";
    if ($obj->properties->{audit}) {
        my $blog_id = $obj->blog_id;
        my @ts = offset_time_list(time, $blog_id);
        my $ts = sprintf "%04d%02d%02d%02d%02d%02d",
            $ts[5]+1900, $ts[4]+1, @ts[3,2,1,0];
        $obj->created_on($ts) unless $obj->created_on;
        $obj->modified_on($ts);
    }
    my @bind = map $obj->column($_), @$cols;
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql)
        or return $driver->error($dbh->errstr);
    $sth->execute(@bind)
        or return $driver->error($dbh->errstr);
    $sth->finish;

    ## Now, if we didn't have an object ID, we need to grab the
    ## newly-assigned ID.
    unless ($obj->id) {
        $obj->id($sth->{mysql_insertid} || $sth->{insertid});
    }
    1;
}

sub update {
    my $driver = shift;
    my($obj) = @_;
    my $cols = $obj->column_names;
    $cols = [ grep $_ ne 'id', @$cols ];
    my $tbl = $obj->datasource;
    my $sql = "update mt_$tbl set\n";
    $sql .= join(', ', map "${tbl}_$_ = ?", @$cols) . "\n";
    $sql .= "where ${tbl}_id = '" . $obj->id . "'";
    if ($obj->properties->{audit}) {
        my $blog_id = $obj->blog_id;
        my @ts = offset_time_list(time, $blog_id);
        my $ts = sprintf "%04d%02d%02d%02d%02d%02d",
            $ts[5]+1900, $ts[4]+1, @ts[3,2,1,0];
        $obj->modified_on($ts);
    }
    my @bind = map $obj->column($_), @$cols;
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql)
        or return $driver->error($dbh->errstr);
    $sth->execute(@bind)
        or return $driver->error($dbh->errstr);
    $sth->finish;
    1;
}

sub remove {
    my $driver = shift;
    my($obj) = @_;
    my $id = $obj->id;
    return unless $id;
    my $tbl = $obj->datasource;
    my $sql = "delete from mt_$tbl where ${tbl}_id = ?";
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql)
        or return $driver->error($dbh->errstr);
    $sth->execute($id)
        or return $driver->error($dbh->errstr);
    $sth->finish;
    1;
}

sub remove_all {
    my $driver = shift;
    my($class) = @_;
    my $sql = "delete from mt_" . $class->datasource;
    my $dbh = $driver->{dbh};
    my $sth = $dbh->prepare($sql)
        or return $driver->error($dbh->errstr);
    $sth->execute
        or return $driver->error($dbh->errstr);
    $sth->finish;
    1;
}

## xxx this will need to do something different based on the
## underlying DB driver
sub generate_id { undef }

sub DESTROY {
    $_[0]->{dbh}->disconnect if $_[0]->{dbh};
}

1;