use strict;
use warnings;
use feature "say";

# Hash where every value is "?", but that remembers the order in which the keys where accessed
package PlaceHolderHash
{
  use Tie::Hash;
  our @ISA = qw(Tie::StdHash);

  my %use_order;

  sub FETCH
  {
    my $self = shift;
    push @{ $use_order{$self} }, shift;
    "?";
  }

  sub get_required
  {
    $use_order{$_[0]};
  }
}

# A dummy DBI that prints "PREPARE $string" instead of actually calling prepare
package MyDBTemplate
{
  use Data::Dump qw( pp );
  sub new
  {
    bless {};
  }

  sub prepare 
  { 
    say qq<PREPARE "$_[1]">;
  }

  sub xprepare 
  {
    my ($self, $params) = @_;
    $self->prepare($params->{Prepare});
    
    my %query = %{$params}{qw(Default Required)}; # Yeah, hash-slice!
    bless \%query, 'MyQuery';
  }

  sub xdo 
  {
    my $self = shift;
    $self->xprepare(@_)->xexecute();
  }
}

# A dummy query object that prints "EXECUTE $string" instead of actually calling execute
package MyQuery
{
  sub execute
  {
    my $self = shift;
    local $" = ", ";
    say "EXECUTE (@_)";
  }

  sub xexecute
  {
    my $self = shift;
    my %values = (%{$self->{Default}}, @_);
    my @required = @{ $self->{Required} };
    exists $values{$_} or die "Param $_ required but not defined" for @required;
    $self->execute(@values{@required});
  }
}

# called as ph BLOCK HASH, 
# returns a hash with 
# - Prepare: the string to send with prepare()
# - Default: the default value for named placeholders
# - Required: the list of required values, and the order they were used in
sub ph(&%)
{
  tie my %prepare, 'PlaceHolderHash';
  local *_ = \%prepare;
  my $sub = shift;

  { 
    Prepare   => $sub->(),
    Default   => { @_ }, 
    Required  => (tied %prepare)->get_required()
  };
}

my $dbh = new MyDBTemplate;
my %default = (a => "A", b => "X");
my $table = "table";
say "First query";
# Table isn't part of %_, so do not use a place older
my $query = $dbh->xprepare(ph { "SELECT * FROM $table WHERE _a = $_{a} OR _b1 = $_{b} AND _c = $_{c} AND _b2 = $_{b}" } %default);
$table = "I can change it now, it doesn't matter";

eval { $query->xexecute() } or print "$@";
$query->xexecute(b => "B", c => "C");
$query->xexecute(b => "B2", c => "C2");

say "\nSecond query";
my %values = (rank  => 'Prior',
              xp    => 42);
$table = "users";
$dbh->xdo(ph { "SELECT * FROM $table WHERE rank = $_{rank} AND xp > $_{xp}" } %values);


##</code><code>##

First query
PREPARE "SELECT * FROM table WHERE _a = ? OR _b1 = ? AND _c = ? AND _b2 = ?"
Param c required but not defined at pm_1210536.pl line 71.
EXECUTE (A, B, C, B)
EXECUTE (A, B2, C2, B2)

Second query
PREPARE "SELECT * FROM users WHERE rank = ? AND xp > ?"
EXECUTE (Prior, 42)