view release on metacpan or  search on metacpan

lib/App/DBBrowser/From/Join.pm  view on Meta::CPAN

        i => $info,
        o => $options,
        d => $d
    };
    bless $sf, $class;
}


sub __available_joins {
    my ( $sf ) = @_;
    my $dbms = $sf->{i}{dbms};
    if ( $dbms eq 'SQLite' ) {
        return [ 'INNER JOIN', 'LEFT JOIN', 'CROSS JOIN' ];
    }
    elsif ( $dbms =~ /^(?:mysql|MariaDB)\z/ ) {
        return [ 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'CROSS JOIN' ];
    }
    else {
        return [ 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'FULL JOIN', 'CROSS JOIN' ];
    }
}


my $join_info     = '  Info';
my $from_subquery = '  Subquery';
my $from_cte      = '  Cte';


sub join_tables {
    my ( $sf ) = @_;
    $sf->{d}{stmt_types} = [ 'Join' ];
    my $tc = Term::Choose->new( $sf->{i}{tc_default} );
    my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $table_keys;
    if ( $sf->{o}{G}{metadata} ) {
        $table_keys = [ @{$sf->{d}{user_table_keys}}, @{$sf->{d}{sys_table_keys}} ];
    }
    else {
        $table_keys = [ @{$sf->{d}{user_table_keys}} ];
    }
    my ( $sql, $data );
    my $old_idx_master = 0;

    MASTER: while ( 1 ) {
        $sql = {};
        $ax->reset_sql( $sql );
        $data = {
            used_tables => [],
            aliases => [],
            default_alias => 'a',
        };
        my @choices = map { "- $_" } @$table_keys;
        push @choices, $from_subquery if $sf->{o}{enable}{j_derived};
        push @choices, $from_cte      if $sf->{o}{enable}{j_cte};
        push @choices, $join_info;
        my @pre = ( undef );
        my $menu = [ @pre, @choices ];
        my $info = $ax->get_sql_info( $sql );
        my $idx_master = $tc->choose(
            $menu,
            { %{$sf->{i}{lyt_v}}, info => $info, prompt => 'The Main Table:', index => 1, default => $old_idx_master }
        );
        $ax->print_sql_info( $info );
        if ( ! defined $idx_master || ! defined $menu->[$idx_master] ) {
            return;
        }
        if ( $sf->{o}{G}{menu_memory} ) {
            if ( $old_idx_master == $idx_master && ! $ENV{TC_RESET_AUTO_UP} ) {
                $old_idx_master = 0;
                next MASTER;
            }
            $old_idx_master = $idx_master;
        }
        my $master_key = $menu->[$idx_master];
        if ( $master_key eq $join_info ) {
            $sf->__get_join_info();
            $sf->__print_join_info();
            next MASTER;
        }
        my $master;
        if ( $master_key eq $from_subquery ) {
            my $sq = App::DBBrowser::From::Subquery->new( $sf->{i}, $sf->{o}, $sf->{d} );
            $master = $sq->subquery( $sql );
            if ( ! defined $master ) {
                next MASTER;
            }
        }
        elsif ( $master_key eq $from_cte ) {
            my $sq = App::DBBrowser::From::Cte->new( $sf->{i}, $sf->{o}, $sf->{d} );
            $master = $sq->cte( $sql );
            if ( ! defined $master ) {
                next MASTER;
            }
        }
        else {
            $master_key =~ s/^-\s//;
            $master = $ax->qq_table( $sf->{d}{tables_info}{$master_key} );
        }
        push @{$data->{used_tables}}, $master;
        $data->{default_alias} = 'a';
        # Alias
        my $master_alias = $ax->table_alias( $sql, 'tables_in_join', $master, $data->{default_alias} );
        push @{$data->{aliases}}, [ $master, $master_alias ];
        push @{$sql->{join_data}}, { table => $master . " " . $master_alias };
        ( $data->{col_names}{$master}, undef ) = $ax->column_names_and_types( $master . " " . $master_alias );
        if ( ! defined $data->{col_names}{$master} ) {
            next MASTER;
        }
        my @bu;
        my $old_idx_type = 0;

        JOIN_TYPE: while ( 1 ) {
            my $enough_tables = $sf->{i}{_confirm};
            my @pre = ( undef, $enough_tables );
            my $avail_joins = $sf->__available_joins();
            my $menu = [ @pre, map { "- $_" } map { s/\b(.)/uc $1/ger } map { lc } @$avail_joins ];
            my $info = $ax->get_sql_info( $sql );
            # Choose
            my $idx_type = $tc->choose(
                $menu,
                { %{$sf->{i}{lyt_v}}, info => $info, prompt => 'Choose Join Type:', index => 1, default => $old_idx_type }
            );
            $ax->print_sql_info( $info );
            if ( ! defined $idx_type || ! defined $menu->[$idx_type] ) {
                if ( @bu ) {
                    pop @{$sql->{join_data}};
                    $data = pop @bu;
                    next JOIN_TYPE;
                }
                next MASTER;
            }
            if ( $sf->{o}{G}{menu_memory} ) {
                if ( $old_idx_type == $idx_type && ! $ENV{TC_RESET_AUTO_UP} ) {
                    $old_idx_type = 0;
                    next JOIN_TYPE;
                }
                $old_idx_type = $idx_type;
            }
            if ( $menu->[$idx_type] eq $enough_tables ) {
                if ( @{$data->{used_tables}} == 1 ) {
                    return;
                }
                last JOIN_TYPE;
            }
            my $join_type = uc $menu->[$idx_type];
            $join_type =~ s/^-\s//;
            push @bu, $ax->clone_data( $data );
            push @{$sql->{join_data}}, { join_type => $join_type };
            my $ok = $sf->__add_slave_with_join_condition( $sql, $data, $table_keys );
            if ( ! $ok ) {
                $data = pop @bu;
                pop @{$sql->{join_data}}
            }
            else {
                $old_idx_type = 0;
            }
        }
        last MASTER;
    }

    my $aliases_hash = {};
    for my $ref ( @{$data->{aliases}} ) {
        push @{$aliases_hash->{$ref->[0]}}, $ref->[1];
    }
    my %col_names;
    #my $qualified_column_names;              ##
    for my $table ( @{$data->{used_tables}} ) {
        for my $col ( @{$data->{col_names}{$table}} ) {
            ++$col_names{lc $col};
            #if ( $col_names{lc $col} > 1 ) { ##
            #    $qualified_column_names = 1; ##
            #}                                ##
        }
    }
    my $columns = [];
    my $aliases = {};
    for my $table ( @{$data->{used_tables}} ) {
        for my $table_alias ( @{$aliases_hash->{$table}} ) {
            for my $col ( @{$data->{col_names}{$table}} ) {
                my $qq_col = $ax->qualified_identifier( $table_alias, $col );
                if ( any { $_ eq $qq_col } @$columns ) {
                    next;
                }
                if ( $sf->{o}{alias}{join_columns} && $col_names{lc $col} > 1 ) {
                    my $unquoted_table_alias = $ax->unquote_identifier( $table_alias );
                    my $unquoted_col = $ax->unquote_identifier( $col );
                    my $alias = $ax->alias( $sql, 'join_columns', $qq_col, $unquoted_table_alias . '_' . $unquoted_col );
                    if ( length $alias ) {
                        $aliases->{$qq_col} = $alias;
                    }
                }
                push @$columns, $qq_col;
            }
        }
    }
    my $table = $ax->get_stmt( $sql, 'Join', 'prepare' );
    return $table, $columns, $aliases;
}


sub __add_slave_with_join_condition {
    my ( $sf, $sql, $data, $table_keys ) = @_;
    my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $tc = Term::Choose->new( $sf->{i}{tc_default} );
    my @choices = map { '- '. $_ } @$table_keys;
    push @choices, $from_subquery if $sf->{o}{enable}{j_derived};
    push @choices, $from_cte     if $sf->{o}{enable}{j_cte};
    push @choices, $join_info;
    my $old_idx_slave = 0;

    SLAVE: while ( 1 ) {
        my @pre = ( undef );
        my $menu = [ @pre, @choices ];
        my $info = $ax->get_sql_info( $sql );
        # Choose
        my $idx_slave = $tc->choose(
            $menu,
            { %{$sf->{i}{lyt_v}}, info => $info, prompt => 'Add table:', index => 1,
              default => $old_idx_slave, undef => $sf->{i}{_reset} }
        );
        $ax->print_sql_info( $info );
        if ( ! defined $idx_slave || ! defined $menu->[$idx_slave] ) {
            return;
        }
        if ( $sf->{o}{G}{menu_memory} ) {
            if ( $old_idx_slave == $idx_slave && ! $ENV{TC_RESET_AUTO_UP} ) {
                $old_idx_slave = 0;
                next SLAVE;
            }
            $old_idx_slave = $idx_slave;
        }
        if ( $menu->[$idx_slave] eq $join_info ) {
            $sf->__get_join_info();
            $sf->__print_join_info();
            next SLAVE;
        }
        my $slave_key = $menu->[$idx_slave];
        my $bu_data = $ax->clone_data( $data );
        my $slave;
        if ( $slave_key eq $from_subquery ) {
            my $sq = App::DBBrowser::From::Subquery->new( $sf->{i}, $sf->{o}, $sf->{d} );
            $slave = $sq->subquery( $sql );
            if ( ! defined $slave ) {
                next SLAVE;
            }
        }
        elsif ( $slave_key eq $from_cte ) {
            my $sq = App::DBBrowser::From::Cte->new( $sf->{i}, $sf->{o}, $sf->{d} );
            $slave = $sq->cte( $sql );
            if ( ! defined $slave ) {
                next SLAVE;
            }
        }
        else {
            $slave_key =~ s/^-\s//;
            $slave = $ax->qq_table( $sf->{d}{tables_info}{$slave_key} );
        }
        push @{$data->{used_tables}}, $slave; # $slave
        # Alias
        my $slave_alias = $ax->table_alias( $sql, 'tables_in_join', $slave, ++$data->{default_alias} );
        $sql->{join_data}[-1]{table} = $slave . " " . $slave_alias;
        push @{$data->{aliases}}, [ $slave, $slave_alias ];
        ( $data->{col_names}{$slave}, undef ) = $ax->column_names_and_types( $slave . " " . $slave_alias );
        if ( ! defined $data->{col_names}{$slave} ) {
            $sf->__reset_to_backed_up_join_data( $sql, $data, $bu_data );
            next SLAVE;
        }
        if ( $sql->{join_data}[-1]{join_type} ne 'CROSS JOIN' ) {
            my $ok = $sf->__add_join_condition( $sql, $data );
            if ( ! $ok ) {
                $sf->__reset_to_backed_up_join_data( $sql, $data, $bu_data );
                next SLAVE;
            }
        }
        $ax->print_sql_info( $ax->get_sql_info( $sql ) );
        return 1;
    }
}