byterock

Well how about some coding today?

Lets look at the first little bits from my last post the 'View' and 'Element' and I am going to focus in on using it with SQL in this post. So we have this;

 

{

    view => 'person',

    elements => [

        { name => 'name' },

        {

          name => 'street',

          view => 'address'

        },

        {

          name => 'country'

          view => 'address'

        }

        {

          name => 'city',

          view => 'address'

        }

    ],

}

Now the question, 'How am I going to express the above in the code of my Data Accessor?' Do I just have one attribute that stores the above in a hash-ref? Do I break them down into two attributes a string and an array-ref of hashes Like this;

 

has view => (

    is     => 'rw',

    isa    => 'Str',

);


has elements => (

    is      => 'rw',

    isa     => 'ArrayRef',

);

Well the 'View' in SQL terms just the name of the Table (or View) that we are interested in, simple enough. Looking a little deeper most if not all implementations of SQL add in the concept 'Alias' for any table. So I can't have a simple scalar attribute I need a hash-ref at least. So my model expands

 

{

    view => 'person',

    alias => 'Me',

... 

Now looking at an 'Element' which corresponds to a 'Field' in a 'Table' in SQL and I have already expanded my model by making it an array ref of 'Elements'. In SQL a Field can also have an 'Alias' and one can use expressions, functions, constants or even other SQL queries as a 'Field'. Now add on top of that the syntax for a field my change when they used in the differing 'SQL Clauses' one gets the picture that the Element is not simple at all.

What I did in the Data Accessor was to separate out the out these components into something like this

 

has view => (

    is     => 'rw',

    isa    => 'Object',

);


has elements => (

    isa    => 'ArrayRef',

    is      => 'rw',

);

and two packages like this

 

{  package 

           DA::View;

    use Moose;

    has 'name' => (

        required => 1,

        isa      => 'Str'

        is     => 'rw',

    );

    has 'alias' => (

        required => 1,

        isa      => 'Str'

        is     => 'rw',

    );

}

{  package 

           DA::Element;

    use Moose;

    has 'name' => (

        required => 1,

        isa      => 'Str',

        is     => 'rw',

    );

    has 'alias' => (

        required => 1,

        isa      => 'Str',

        is     => 'rw',

    );

}

Now I need some way to express the above in SQLish so lets on a sub like this to each

 

    sub sql {

        my $self = shift;

        if ( $self->alias() ) {

            return $self->name() . "  AS " . $self->alias();

        }

        else {

            return $self->name();

        }

    }

and I think we are in business. Just need to put them together somehow and I have defined that before with the '_execute' clause and in there I just do this

 

sub _execute {

    my $self = shift;

    my $delimiter = " ";

    my $sql       = "SELECT ";


    foreach my $element ( @{ $self->elements() } ) {

        $sql .= $delimiter . $element->sql();

        $delimiter = ", ";

    }

    return   $sql ." FROM ". $self->view()->sql();

}

And we are off to the races. Now to type this up and write up a test or two and see what come up.