package DBIx::Custom;

use Object::Simple -base;

our $VERSION = '0.1718';

use 5.008001;

use Carp 'croak';

use DBI;

use DBIx::Custom::Result;

use DBIx::Custom::Query;

use DBIx::Custom::QueryBuilder;

use DBIx::Custom::Where;

use DBIx::Custom::Model;

use DBIx::Custom::Tag;

use DBIx::Custom::Order;

use DBIx::Custom::Util qw/_array_to_hash _subname/;

use Encode qw/encode encode_utf8 decode_utf8/;

use Scalar::Util qw/weaken/;

use constant DEBUG => $ENV{DBIX_CUSTOM_DEBUG} || 0;

use constant DEBUG_ENCODING => $ENV{DBIX_CUSTOM_DEBUG_ENCODING} || 'UTF-8';

has [qw/connector dsn password quote user exclude_table user_table_info

        user_column_info/],

    cache => 0,

    cache_method => sub {

        sub {

            my $self = shift;

            $self->{_cached} ||= {};

            if (@_ > 1) {

                $self->{_cached}{$_[0]} = $_[1];

            }

            else {

                return $self->{_cached}{$_[0]};

            }

        }

    },

    dbi_option => sub { {} },

    default_dbi_option => sub {

        {

            RaiseError => 1,

            PrintError => 0,

            AutoCommit => 1

        }

    },

    filters => sub {

        {

            encode_utf8 => sub { encode_utf8($_[0]) },

            decode_utf8 => sub { decode_utf8($_[0]) }

        }

    },

    last_sql => '',

    models => sub { {} },

    query_builder => sub {

        my $self = shift;

        my $builder = DBIx::Custom::QueryBuilder->new(dbi => $self);

        weaken $builder->{dbi};

        return $builder;

    },

    result_class  => 'DBIx::Custom::Result',

    safety_character => '\w',

    separator => '.',

    stash => sub { {} },

    tag_parse => 1;

sub available_datatype {

    my $self = shift;

    my $data_types = '';

    foreach my $i (-1000 .. 1000) {

         my $type_info = $self->dbh->type_info($i);

         my $data_type = $type_info->{DATA_TYPE};

         my $type_name = $type_info->{TYPE_NAME};

         $data_types .= "$data_type ($type_name)\n"

           if defined $data_type;

    }

    return "Data Type maybe equal to Type Name" unless $data_types;

    $data_types = "Data Type (Type name)\n" . $data_types;

    return $data_types;

}

sub available_typename {

    my $self = shift;

    # Type Names

    my $type_names = {};

    $self->each_column(sub {

        my ($self, $table, $column, $column_info) = @_;

        $type_names->{$column_info->{TYPE_NAME}} = 1

          if $column_info->{TYPE_NAME};

    });

    my @output = sort keys %$type_names;

    unshift @output, "Type Name";

    return join "\n", @output;

}

our $AUTOLOAD;

sub AUTOLOAD {

    my $self = shift;

    # Method name

    my ($package, $mname) = $AUTOLOAD =~ /^([\w\:]+)\:\:(\w+)$/;

    # Call method

    $self->{_methods} ||= {};

    if (my $method = $self->{_methods}->{$mname}) {

        return $self->$method(@_)

    }

    elsif ($self->{dbh} && (my $dbh_method = $self->dbh->can($mname))) {

        $self->dbh->$dbh_method(@_);

    }

    else {

        croak qq{Can't locate object method "$mname" via "$package" }

            . _subname;

    }

}

sub assign_param {

    my ($self, $param) = @_;

    # Create set tag

    my @params;

    my $safety = $self->safety_character;

    foreach my $column (sort keys %$param) {

        croak qq{"$column" is not safety column name } . _subname

          unless $column =~ /^[$safety\.]+$/;

        my $column_quote = $self->_q($column);

        $column_quote =~ s/\./$self->_q(".")/e;

        push @params, ref $param->{$column} eq 'SCALAR'

          ? "$column_quote = " . ${$param->{$column}}

          : "$column_quote = :$column";

    }

    my $tag = join(', ', @params);

    return $tag;

}

sub column {

    my $self = shift;

    my $option = pop if ref $_[-1] eq 'HASH';

    my $real_table = shift;

    my $columns = shift;

    my $table = $option->{alias} || $real_table;

    # Columns

    unless ($columns) {

        $columns ||= $self->model($real_table)->columns;

    }

    # Separator

    my $separator = $self->separator;

    # Column clause

    my @column;

    $columns ||= [];

    push @column, $self->_q($table) . "." . $self->_q($_) .

      " as " . $self->_q("${table}${separator}$_")

      for @$columns;

    return join (', ', @column);

}

sub connect {

    my $self = ref $_[0] ? shift : shift->new(@_);

    my $connector = $self->connector;

    if (!ref $connector && $connector) {

        require DBIx::Connector;

        my $dsn = $self->dsn;

        my $user = $self->user;

        my $password = $self->password;

        my $dbi_option = {%{$self->dbi_options}, %{$self->dbi_option}};

        my $connector = DBIx::Connector->new($dsn, $user, $password,

          {%{$self->default_dbi_option} , %$dbi_option});

        $self->connector($connector);

    }

    # Connect

    $self->dbh;

    return $self;

}

sub count { shift->select(column => 'count(*)', @_)->fetch_first->[0] }

sub dbh {

    my $self = shift;

    # Set

    if (@_) {

        $self->{dbh} = $_[0];

        return $self;

    }

    # Get

    else {

        # From Connction manager

        if (my $connector = $self->connector) {

            croak "connector must have dbh() method " . _subname

              unless ref $connector && $connector->can('dbh');

            $self->{dbh} = $connector->dbh;

        }

        # Connect

        $self->{dbh} ||= $self->_connect;

        # Quote

        if (!defined $self->reserved_word_quote && !defined $self->quote) {

            my $driver = $self->_driver;

            my $quote =  $driver eq 'odbc' ? '[]'

                       : $driver eq 'ado' ? '[]'

                       : $driver eq 'mysql' ? '`'

                       : '"';

            $self->quote($quote);

        }

        return $self->{dbh};

    }

}

sub delete {

    my ($self, %args) = @_;

    # Arguments

    my $table = $args{table} || '';

    croak qq{"table" option must be specified. } . _subname

      unless $table;

    my $where            = delete $args{where} || {};

    my $append           = delete $args{append};

    my $allow_delete_all = delete $args{allow_delete_all};

    my $where_param      = delete $args{where_param} || {};

    my $id = delete $args{id};

    my $primary_key = delete $args{primary_key};

    croak "update method primary_key option " .

          "must be specified when id is specified " . _subname

      if defined $id && !defined $primary_key;

    $primary_key = [$primary_key] unless ref $primary_key eq 'ARRAY';

    my $prefix = delete $args{prefix};

    # Where

    $where = $self->_create_param_from_id($id, $primary_key) if defined $id;

    my $where_clause = '';

    if (ref $where eq 'ARRAY' && !ref $where->[0]) {

        $where_clause = "where " . $where->[0];

        $where_param = $where->[1];

    }

    elsif (ref $where) {

        $where = $self->_where_to_obj($where);

        $where_param = keys %$where_param

                     ? $self->merge_param($where_param, $where->param)

                     : $where->param;

        # String where

        $where_clause = $where->to_string;

    }

    elsif ($where) { $where_clause = "where $where" }

    croak qq{"where" must be specified } . _subname

      if $where_clause eq '' && !$allow_delete_all;

    # Delete statement

    my @sql;

    push @sql, "delete";

    push @sql, $prefix if defined $prefix;

    push @sql, "from " . $self->_q($table) . " $where_clause";

    push @sql, $append if defined $append;

    my $sql = join(' ', @sql);

    # Execute query

    return $self->execute($sql, $where_param, table => $table, %args);

}

sub delete_all { shift->delete(allow_delete_all => 1, @_) }

sub DESTROY {}

sub create_model {

    my $self = shift;

    # Arguments

    my $args = ref $_[0] eq 'HASH' ? $_[0] : {@_};

    $args->{dbi} = $self;

    my $model_class = delete $args->{model_class} || 'DBIx::Custom::Model';

    my $model_name  = delete $args->{name};

    my $model_table = delete $args->{table};

    $model_name ||= $model_table;

    # Create model

    my $model = $model_class->new($args);

    weaken $model->{dbi};

    $model->name($model_name) unless $model->name;

    $model->table($model_table) unless $model->table;

    # Apply filter(DEPRECATED logic)

    if ($model->{filter}) {

        my $filter = ref $model->filter eq 'HASH'

                   ? [%{$model->filter}]

                   : $model->filter;

        $filter ||= [];

        warn "DBIx::Custom::Model filter method is DEPRECATED!"

          if @$filter;

        $self->_apply_filter($model->table, @$filter);

    }

    # Set model

    $self->model($model->name, $model);

    return $self->model($model->name);

}

sub each_column {

    my ($self, $cb, %options) = @_;

    my $user_column_info = $self->user_column_info;

    if ($user_column_info) {

        $self->$cb($_->{table}, $_->{column}, $_->{info}) for @$user_column_info;

    }

    else {

        my $re = $self->exclude_table || $options{exclude_table};

        # Tables

        my %tables;

        $self->each_table(sub { $tables{$_[1]}++ });

        # Iterate all tables

        my @tables = sort keys %tables;

        for (my $i = 0; $i < @tables; $i++) {

            my $table = $tables[$i];

            # Iterate all columns

            my $sth_columns;

            eval {$sth_columns = $self->dbh->column_info(undef, undef, $table, '%')};

            next if $@;

            while (my $column_info = $sth_columns->fetchrow_hashref) {

                my $column = $column_info->{COLUMN_NAME};

                $self->$cb($table, $column, $column_info);

            }

        }

    }

}

sub each_table {

    my ($self, $cb, %option) = @_;

    my $user_table_infos = $self->user_table_info;

    # Iterate tables

    if ($user_table_infos) {

        $self->$cb($_->{table}, $_->{info}) for @$user_table_infos;

    }

    else {

        my $re = $self->exclude_table || $option{exclude};

        my $sth_tables = $self->dbh->table_info;

        while (my $table_info = $sth_tables->fetchrow_hashref) {

            # Table

            my $table = $table_info->{TABLE_NAME};

            next if defined $re && $table =~ /$re/;

            $self->$cb($table, $table_info);

        }

    }

}

our %VALID_ARGS = map { $_ => 1 } qw/append allow_delete_all

  allow_update_all bind_type column filter id join param prefix primary_key

  query relation sqlfilter table table_alias type type_rule_off type_rule1_off

  type_rule2_off wrap/;

sub execute {

    my $self = shift;

    my $query = shift;

    my $param;

    $param = shift if @_ % 2;

    my %args = @_;

    # Arguments

    my $p = delete $args{param} || {};

    $param ||= $p;

    my $tables = delete $args{table} || [];

    $tables = [$tables] unless ref $tables eq 'ARRAY';

    my $filter = delete $args{filter};

    $filter = _array_to_hash($filter);

    my $bind_type = delete $args{bind_type} || delete $args{type};

    $bind_type = _array_to_hash($bind_type);

    my $type_rule_off = delete $args{type_rule_off};

    my $type_rule_off_parts = {

        1 => delete $args{type_rule1_off},

        2 => delete $args{type_rule2_off}

    };

    my $query_return = delete $args{query};

    my $table_alias = delete $args{table_alias} || {};

    my $sqlfilter = $args{sqlfilter};

    # Check argument names

    foreach my $name (keys %args) {

        croak qq{"$name" is wrong option } . _subname

          unless $VALID_ARGS{$name};

    }

    $query = $self->_create_query($query, $sqlfilter) unless ref $query;

    # Save query

    $self->last_sql($query->sql);

    return $query if $query_return;

    # DEPRECATED! Merge query filter

    $filter ||= $query->{filter} || {};

    # Tables

    unshift @$tables, @{$query->{tables} || []};

    my $main_table = @{$tables}[-1];

    # DEPRECATED! Cleanup tables

    $tables = $self->_remove_duplicate_table($tables, $main_table)

      if @$tables > 1;

    # Type rule

    my $type_filters = {};

    unless ($type_rule_off) {

        foreach my $i (1, 2) {

            unless ($type_rule_off_parts->{$i}) {

                $type_filters->{$i} = {};

                foreach my $alias (keys %$table_alias) {

                    my $table = $table_alias->{$alias};

                    foreach my $column (keys %{$self->{"_into$i"}{key}{$table} || {}}) {

                        $type_filters->{$i}->{"$alias.$column"} = $self->{"_into$i"}{key}{$table}{$column};

                    }

                }

                $type_filters->{$i} = {%{$type_filters->{$i}}, %{$self->{"_into$i"}{key}{$main_table} || {}}}

                  if $main_table;

            }

        }

    }

    # DEPRECATED! Applied filter

    if ($self->{filter}{on}) {

        my $applied_filter = {};

        foreach my $table (@$tables) {

            $applied_filter = {

                %$applied_filter,

                %{$self->{filter}{out}->{$table} || {}}

            }

        }

        $filter = {%$applied_filter, %$filter};

    }

    # Replace filter name to code

    foreach my $column (keys %$filter) {

        my $name = $filter->{$column};

        if (!defined $name) {

            $filter->{$column} = undef;

        }

        elsif (ref $name ne 'CODE') {

          croak qq{Filter "$name" is not registered" } . _subname

            unless exists $self->filters->{$name};

          $filter->{$column} = $self->filters->{$name};

        }

    }

    # Create bind values

    my $bind = $self->_create_bind_values(

        $param,

        $query->columns,

        $filter,

        $type_filters,

        $bind_type

    );

    # Execute

    my $sth = $query->sth;

    my $affected;

    eval {

        for (my $i = 0; $i < @$bind; $i++) {

            my $bind_type = $bind->[$i]->{bind_type};

            $sth->bind_param(

                $i + 1,

                $bind->[$i]->{value},

                $bind_type ? $bind_type : ()

            );

        }

        $affected = $sth->execute;

    };

    $self->_croak($@, qq{. Following SQL is executed.\n}

      . qq{$query->{sql}\n} . _subname) if $@;

    # DEBUG message

    if (DEBUG) {

        print STDERR "SQL:\n" . $query->sql . "\n";

        my @output;

        foreach my $b (@$bind) {

            my $value = $b->{value};

            $value = 'undef' unless defined $value;

            $value = encode(DEBUG_ENCODING(), $value)

              if utf8::is_utf8($value);

            push @output, $value;

        }

        print STDERR "Bind values: " . join(', ', @output) . "\n\n";

    }

    # Select statement

    if ($sth->{NUM_OF_FIELDS}) {

        # DEPRECATED! Filter

        my $filter = {};

        if ($self->{filter}{on}) {

            $filter->{in}  = {};

            $filter->{end} = {};

            push @$tables, $main_table if $main_table;

            foreach my $table (@$tables) {

                foreach my $way (qw/in end/) {

                    $filter->{$way} = {

                        %{$filter->{$way}},

                        %{$self->{filter}{$way}{$table} || {}}

                    };

                }

            }

        }

        # Result

        my $result = $self->result_class->new(

            sth => $sth,

            dbi => $self,

            default_filter => $self->{default_in_filter},

            filter => $filter->{in} || {},

            end_filter => $filter->{end} || {},

            type_rule => {

                from1 => $self->type_rule->{from1},

                from2 => $self->type_rule->{from2}

            },

        );

        return $result;

    }

    # Not select statement

    else { return $affected }

}

sub get_table_info {

    my ($self, %args) = @_;

    my $exclude = delete $args{exclude};

    croak qq/"$_" is wrong option/ for keys %args;

    my $table_info = [];

    $self->each_table(

        sub { push @$table_info, {table => $_[1], info => $_[2] } },

        exclude => $exclude

    );

    return [sort {$a->{table} cmp $b->{table} } @$table_info];

}

sub get_column_info {

    my ($self, %args) = @_;

    my $exclude_table = delete $args{exclude_table};

    croak qq/"$_" is wrong option/ for keys %args;

    my $column_info = [];

    $self->each_column(

        sub { push @$column_info, {table => $_[1], column => $_[2], info => $_[3] } },

        exclude_table => $exclude_table

    );

    return [

      sort {$a->{table} cmp $b->{table} || $a->{column} cmp $b->{column} }

        @$column_info];

}

sub insert {

    my $self = shift;

    # Arguments

    my $param;

    $param = shift if @_ % 2;

    my %args = @_;

    my $table  = delete $args{table};

    croak qq{"table" option must be specified } . _subname

      unless defined $table;

    my $p = delete $args{param} || {};

    $param  ||= $p;

    my $append = delete $args{append} || '';

    my $id = delete $args{id};

    my $primary_key = delete $args{primary_key};

    croak "insert method primary_key option " .

          "must be specified when id is specified " . _subname

      if defined $id && !defined $primary_key;

    $primary_key = [$primary_key] unless ref $primary_key eq 'ARRAY';

    my $prefix = delete $args{prefix};

    # Merge parameter

    if (defined $id) {

        my $id_param = $self->_create_param_from_id($id, $primary_key);

        $param = $self->merge_param($id_param, $param);

    }

    # Insert statement

    my @sql;

    push @sql, "insert";

    push @sql, $prefix if defined $prefix;

    push @sql, "into " . $self->_q($table) . " " . $self->insert_param($param);

    push @sql, $append if defined $append;

    my $sql = join (' ', @sql);

    # Execute query

    return $self->execute($sql, $param, table => $table, %args);

}

sub insert_param {

    my ($self, $param) = @_;

    # Create insert parameter tag

    my $safety = $self->safety_character;

    my @columns;

    my @placeholders;

    foreach my $column (sort keys %$param) {

        croak qq{"$column" is not safety column name } . _subname

          unless $column =~ /^[$safety\.]+$/;

        my $column_quote = $self->_q($column);

        $column_quote =~ s/\./$self->_q(".")/e;

        push @columns, $column_quote;

        push @placeholders, ref $param->{$column} eq 'SCALAR'

          ? ${$param->{$column}} : ":$column";

    }

    return '(' . join(', ', @columns) . ') ' . 'values ' .

           '(' . join(', ', @placeholders) . ')'

}

sub include_model {

    my ($self, $name_space, $model_infos) = @_;

    # Name space

    $name_space ||= '';

    # Get Model infomations

    unless ($model_infos) {

        # Load name space module

        croak qq{"$name_space" is invalid class name } . _subname

          if $name_space =~ /[^\w:]/;

        eval "use $name_space";

        croak qq{Name space module "$name_space.pm" is needed. $@ }

            . _subname

          if $@;

        # Search model modules

        my $path = $INC{"$name_space.pm"};

        $path =~ s/\.pm$//;

        opendir my $dh, $path

          or croak qq{Can't open directory "$path": $! } . _subname

        $model_infos = [];

        while (my $module = readdir $dh) {

            push @$model_infos, $module

              if $module =~ s/\.pm$//;

        }

        close $dh;

    }

    # Include models

    foreach my $model_info (@$model_infos) {

        # Load model

        my $model_class;

        my $model_name;

        my $model_table;

        if (ref $model_info eq 'HASH') {

            $model_class = $model_info->{class};

            $model_name  = $model_info->{name};

            $model_table = $model_info->{table};

            $model_name  ||= $model_class;

            $model_table ||= $model_name;

        }

        else { $model_class = $model_name = $model_table = $model_info }

        my $mclass = "${name_space}::$model_class";

        croak qq{"$mclass" is invalid class name } . _subname

          if $mclass =~ /[^\w:]/;

        unless ($mclass->can('isa')) {

            eval "use $mclass";

            croak "$@ " . _subname if $@;

        }

        # Create model

        my $args = {};

        $args->{model_class} = $mclass if $mclass;

        $args->{name}        = $model_name if $model_name;

        $args->{table}       = $model_table if $model_table;

        $self->create_model($args);

    }

    return $self;

}

sub map_param {

    my $self = shift;

    my $param = shift;

    my %map = @_;

    # Mapping

    my $map_param = {};

    foreach my $key (keys %map) {

        my $value_cb;

        my $condition;

        my $map_key;

        # Get mapping information

        if (ref $map{$key} eq 'ARRAY') {

            foreach my $some (@{$map{$key}}) {

                $map_key = $some unless ref $some;

                $condition = $some->{if} if ref $some eq 'HASH';

                $value_cb = $some if ref $some eq 'CODE';

            }

        }

        else {

            $map_key = $map{$key};

        }

        $value_cb ||= sub { $_[0] };

        $condition ||= sub { defined $_[0] && length $_[0] };

        # Map parameter

        my $value;

        if (ref $condition eq 'CODE') {

            $map_param->{$map_key} = $value_cb->($param->{$key})

              if $condition->($param->{$key});

        }

        elsif ($condition eq 'exists') {

            $map_param->{$map_key} = $value_cb->($param->{$key})

              if exists $param->{$key};

        }

        else { croak qq/Condition must be code reference or "exists" / . _subname }

    }

    return $map_param;

}

sub merge_param {

    my ($self, @params) = @_;

    # Merge parameters

    my $merge = {};

    foreach my $param (@params) {

        foreach my $column (keys %$param) {

            my $param_is_array = ref $param->{$column} eq 'ARRAY' ? 1 : 0;

            if (exists $merge->{$column}) {

                $merge->{$column} = [$merge->{$column}]

                  unless ref $merge->{$column} eq 'ARRAY';

                push @{$merge->{$column}},

                  ref $param->{$column} ? @{$param->{$column}} : $param->{$column};

            }

            else {

                $merge->{$column} = $param->{$column};

            }

        }

    }

    return $merge;

}

sub method {

    my $self = shift;

    # Register method

    my $methods = ref $_[0] eq 'HASH' ? $_[0] : {@_};

    $self->{_methods} = {%{$self->{_methods} || {}}, %$methods};

    return $self;

}

sub model {

    my ($self, $name, $model) = @_;

    # Set model

    if ($model) {

        $self->models->{$name} = $model;

        return $self;

    }

    # Check model existance

    croak qq{Model "$name" is not included } . _subname

      unless $self->models->{$name};

    # Get model

    return $self->models->{$name};

}

sub mycolumn {

    my ($self, $table, $columns) = @_;

    # Create column clause

    my @column;

    $columns ||= [];

    push @column, $self->_q($table) . "." . $self->_q($_) .

      " as " . $self->_q($_)

      for @$columns;

    return join (', ', @column);

}

sub new {

    my $self = shift->SUPER::new(@_);

    # Check attributes

    my @attrs = keys %$self;

    foreach my $attr (@attrs) {

        croak qq{Invalid attribute: "$attr" } . _subname

          unless $self->can($attr);

    }

    # DEPRECATED

    $self->{_tags} = {

        '?'     => \&DBIx::Custom::Tag::placeholder,

        '='     => \&DBIx::Custom::Tag::equal,

        '<>'    => \&DBIx::Custom::Tag::not_equal,

        '>'     => \&DBIx::Custom::Tag::greater_than,

        '<'     => \&DBIx::Custom::Tag::lower_than,

        '>='    => \&DBIx::Custom::Tag::greater_than_equal,

        '<='    => \&DBIx::Custom::Tag::lower_than_equal,

        'like'  => \&DBIx::Custom::Tag::like,

        'in'    => \&DBIx::Custom::Tag::in,

        'insert_param' => \&DBIx::Custom::Tag::insert_param,

        'update_param' => \&DBIx::Custom::Tag::update_param

    };

    return $self;

}

my $not_exists = bless {}, 'DBIx::Custom::NotExists';

sub not_exists { $not_exists }

sub order {

    my $self = shift;

    return DBIx::Custom::Order->new(dbi => $self, @_);

}

sub register_filter {

    my $self = shift;

    # Register filter

    my $filters = ref $_[0] eq 'HASH' ? $_[0] : {@_};

    $self->filters({%{$self->filters}, %$filters});

    return $self;

}

sub select {

    my ($self, %args) = @_;

    # Arguments

    my $table = delete $args{table};

    my $tables = ref $table eq 'ARRAY' ? $table

               : defined $table ? [$table]

               : [];

    my $columns   = delete $args{column};

    my $where     = delete $args{where} || {};

    my $append    = delete $args{append};

    my $join      = delete $args{join} || [];

    croak qq{"join" must be array reference } . _subname

      unless ref $join eq 'ARRAY';

    my $relation = delete $args{relation};

    warn "select() relation option is DEPRECATED!"

      if $relation;

    my $param = delete $args{param} || {}; # DEPRECATED!

    warn "select() param option is DEPRECATED!"

      if keys %$param;

    my $where_param = delete $args{where_param} || $param || {};

    my $wrap = delete $args{wrap};

    my $id = delete $args{id};

    my $primary_key = delete $args{primary_key};

    croak "update method primary_key option " .

          "must be specified when id is specified " . _subname

      if defined $id && !defined $primary_key;

    $primary_key = [$primary_key] unless ref $primary_key eq 'ARRAY';

    my $prefix = delete $args{prefix};

    # Add relation tables(DEPRECATED!);

    $self->_add_relation_table($tables, $relation);

    # Select statement

    my @sql;

    push @sql, 'select';

    # Prefix

    push @sql, $prefix if defined $prefix;

    # Column clause

    if ($columns) {

        $columns = [$columns] unless ref $columns eq 'ARRAY';

        foreach my $column (@$columns) {

            if (ref $column eq 'HASH') {

                $column = $self->column(%$column) if ref $column eq 'HASH';

            }

            elsif (ref $column eq 'ARRAY') {

                if (@$column == 3 && $column->[1] eq 'as') {

                    warn "[COLUMN, as => ALIAS] is DEPRECATED! use [COLUMN => ALIAS]";

                    splice @$column, 1, 1;

                }

                $column = join(' ', $column->[0], 'as', $self->_q($column->[1]));

            }

            unshift @$tables, @{$self->_search_tables($column)};

            push @sql, ($column, ',');

        }

        pop @sql if $sql[-1] eq ',';

    }

    else { push @sql, '*' }

    # Table

    push @sql, 'from';

    if ($relation) {

        my $found = {};

        foreach my $table (@$tables) {

            push @sql, ($self->_q($table), ',') unless $found->{$table};

            $found->{$table} = 1;

        }

    }

    else {

        my $main_table = $tables->[-1] || '';

        push @sql, $self->_q($main_table);

    }

    pop @sql if ($sql[-1] || '') eq ',';

    croak "Not found table name " . _subname

      unless $tables->[-1];

    # Add tables in parameter

    unshift @$tables,

            @{$self->_search_tables(join(' ', keys %$where_param) || '')};

    # Where

    my $where_clause = '';

    $where = $self->_create_param_from_id($id, $primary_key) if defined $id;

    if (ref $where eq 'ARRAY' && !ref $where->[0]) {

        $where_clause = "where " . $where->[0];

        $where_param = $where->[1];

    }

    elsif (ref $where) {

        $where = $self->_where_to_obj($where);

        $where_param = keys %$where_param

                     ? $self->merge_param($where_param, $where->param)

                     : $where->param;

        # String where

        $where_clause = $where->to_string;

    }

    elsif ($where) { $where_clause = "where $where" }

    # Add table names in where clause

    unshift @$tables, @{$self->_search_tables($where_clause)};

    # Push join

    $self->_push_join(\@sql, $join, $tables);

    # Add where clause

    push @sql, $where_clause;

    # Relation(DEPRECATED!);

    $self->_push_relation(\@sql, $tables, $relation, $where_clause eq '' ? 1 : 0);

    # Append

    push @sql, $append if defined $append;

    # Wrap

    if ($wrap) {

        croak "wrap option must be array refrence " . _subname

          unless ref $wrap eq 'ARRAY';

        unshift @sql, $wrap->[0];

        push @sql, $wrap->[1];

    }

    # SQL

    my $sql = join (' ', @sql);

    # Execute query

    my $result = $self->execute($sql, $where_param, table => $tables, %args);

    return $result;

}

sub setup_model {

    my $self = shift;

    # Setup model

    $self->each_column(

        sub {

            my ($self, $table, $column, $column_info) = @_;

            if (my $model = $self->models->{$table}) {

                push @{$model->columns}, $column;

            }

        }

    );

    return $self;

}

sub show_datatype {

    my ($self, $table) = @_;

    croak "Table name must be specified" unless defined $table;

    print "$table\n";

    my $result = $self->select(table => $table, where => "'0' <> '0'");

    my $sth = $result->sth;

    my $columns = $sth->{NAME};

    my $data_types = $sth->{TYPE};

    for (my $i = 0; $i < @$columns; $i++) {

        my $column = $columns->[$i];

        my $data_type = $data_types->[$i];

        print "$column: $data_type\n";

    }

}

sub show_typename {

    my ($self, $t) = @_;

    croak "Table name must be specified" unless defined $t;

    print "$t\n";

    $self->each_column(sub {

        my ($self, $table, $column, $infos) = @_;

        return unless $table eq $t;

        my $typename = $infos->{TYPE_NAME};

        print "$column: $typename\n";