Table aliases containing a space are not supported

[alranel]

Since 980e9fbaa0bc73c13c54102807258b671a74dfad, table aliases are parsed using the \w+ regular expression:

  foreach my $col (@cols) {
    # extract alias, if any
    if ($col =~ /^\s*         # ignore insignificant leading spaces
                 (.*[^|\s])   # any non-empty string, not ending with ' ' or '|'
                 \|           # followed by a literal '|'
                 (\w+)        # followed by a word (the alias))
                 \s*          # ignore insignificant trailing spaces
                 $/x) {
      $aliased_columns{$2} = $1;
      $col = $self->column_alias($1, $2);
    }
  }

However, an alias containing whitespace is legal on most RDBMS.

[soldemuth]

I believe @alranel is actually referring to column aliases (https://github.com/damil/SQL-Abstract-More/blob/master/lib/SQL/Abstract/More.pm#L369). Is there a reason column alias including whitespace is not supported?

use SQL::Abstract::More;
my @columns = ('tD.districtId|Fairbanks ID');
my ($sql, undef) =
    SQL::Abstract::More->new(
    column_alias => '%s %s',
    name_sep     => '.',
    quote_char   => q{`},
    table_alias  => '%s %s',
)->select(
        -columns => \@columns,
        -from => '',
    );
print $sql;

SELECT `tD`.`districtId|Fairbanks ID`

[damil]

Mmmh .. it never occurred to me that anyone would want spaces in column aliases, because it's more cumbersome to handle :

• one can no longer use qw// for lists of columns with aliases : select(-columns => [qw/long_col_1|alias1 very_long_col2|alias2/)
• when retrieving the results , hash entries must be surrounded with quotes : $row->{'Fairbanks ID'} instead of $row->{alias1}

But if some users want spaces in their alias names, there is no technical objection to that, so I will fix it in the next version.

[soldemuth]

Of course I couldn't utilize your fancy column_alias => sub... to work around since it never gets as far as the column_alias call. In the interim, I used Class::Method::Modifiers as follows:

use parent 'SQL::Abstract::More';
use Class::Method::Modifiers;

my $sqlaParams = {};

$sqlaParams->{column_alias} = sub {
    my ($self, $name, $alias) = @_;

    #return $name if !$alias;
    return $name unless defined $alias; # what if alias is zero

    $alias =~ s/__/ /g; # temporary fix to properly handle SQL function calls

    # quote $name unless it is an SQL expression (then the user should quote it)
    $name = $self->_quote($name) unless $name =~ /[()]/;

    # assemble syntax
    my $sql = sprintf '%s %s', $name, $self->_quote($alias);

    # return a string ref to avoid quoting by SQLA
    return \$sql;
};

around 'new' => sub {
    my $orig = shift; # we need to shift off the orig sub

    return $orig->(
        @_,
        $sqlaParams,
    );
};

around 'select' => sub {
    my $orig = shift; # we need to shift off the orig sub

    my ($sql, @bind) = $orig->(@_);

    # temporary fix for https://github.com/damil/SQL-Abstract-More/issues/15
    $sql =~ s/\|(\w+[\w\s]+\w+)`/` `$1`/gsm;

    return ($sql, @bind);
};

1;