It take back my words day here in the Moose-Pen

I have to adjust how the SQL is produced yet again. Today I was playing about with the '60_order_by.t' tast case of Driver::DBI and had it all nicely set up and on my first run I was getting errors like this;

# Expected SQL--> INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )
# Generated SQL-> INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )
 ORDER BY people.last_name, people.first_name

# Expected SQL--> DELETE FROM peo…

Its add a little new day here in the Moose-Pen

I left off yesterday with the problem of the little build in test DB that comes with DBI not working correctly with SQL where the table names are added to the field names. So this SQL would work;

UPDATE user SET address = ? WHERE user.username = ?

SELECT user.username FROM user WHERE user.username = ?

Just another test post-ette today at the Moose-pen

Will 100% pass the result from yesterdays post I wanted to see if I could get Driver::DBI at least close to that but things did not start that well;

t/00_load.t ......... 1/3 Attribute (elements) is required at /usr/local/lib/perl/5.18.2/Class/MOP/Method/Wrapped.pm line 164# Looks like your test exited with 255 just after 2.

t/00_load.t ......... Dubious, test returned 25

Test post-ette day again here in the Moose-pen

Today just a quick test clean up for Database::Accessor, since I made quite a few changes to it over the past few days mostly making elements a required field and validating that it is not an empty array.

On my first run I got a whole bunch of these;

Attribute (elements) is required at …

t/30_view.t       

t/31_elements.t

t/43_dynamic_conditions.t

t/45_dynamic_links.t …

Its another step back day here in the Moose-Pen

Yesterday I added in a little code to Database::Accessor to stop users from tying to enter an empty 'elements' attribute like this;

my $da = Database::Accessor->new( { view => { name => 'test' } },

                                    elements=>[] );

Can't…

Its two steps backward day here in the Moose-Pen

I left off yesterday by fixing a bug on the Gather class that allowed you to create a Gather with an empty set of 'elements' like this;

gather => {

        elements => [],

        conditions=>[{

                left => {

                    name => 'last_name',

                },

                right             => { value => 'Bloggings' },

                operator       …

Its a good day for code but a bad one for posts here in the Moose-Pen

I started off today by adding in some 38 new tests to 40_joins.t in Driver::DBI and after one or two runs to get rid of some typos in my expected results they are all passing. So like the title says a good day for code but a bad one for posts as I have nothing to much to report on.

Undaunted I moved into 50_having.t and the first thing I did was rename it to 50_group_by.t as that is a little more SQLish.

After coming up with some 34 new tests and after a few runs to get rid of my usual typos in m…

So it is little progress day here in the Moose-Pen

Carrying on from yesterday's post I decided to get 57_dad_elements in order so I added in a few more tests;

 ok( $elements->[$i]->conditions->[1]

          ->predicates->left->right->left->right->view() eq 'a_country',

"Link index $i  condition 2 left->right->left->right->left inherits view"

    );

    ok($elements->[$i]->conditions->[1]->predicates->condition  eq 'AND',

"Link index $i …

Its get on with it day here in the Moose-Pen

After putting on my thinking Moose-cap and reading though some of my notes (and looking at a number of my older posts) the best solution I could come up with was to leave the ability to add a 'JOIN' to an 'UPDATE', 'CREATE' or 'DELETE' statement in.

My reason for this simple if someone wants to use a JOIN is something other than a 'SELECT' and it is perfectly valid command why should my application stop them.

The original DataAccessor code it is written in such a way that you can only do a JOIN on a SELECT as the concep…

It test postette day here in the Moose-Pen.

Today I was rerunning all my test cases to seen which ones where breaking and I got though all of the test on Database::Accessor and was most of the way though Driver::DBI and then I got stuck on '40_joins.t' with this error;

# Expected SQL--> SELECT people.first_name,
people.last_name, people.id, address.street FROM people LEFT JOIN address ON
people.id = address.user_id WHERE people.first_name = ?

# Generated SQL-> SELECT people.first_name, people.last_name, people.id,
address…

Another test postette here in the Moos-Pen today.

To finish off the '30_where_basic.t' test case I decided to add one more test; the function from hell;

(abs((People.salary + .05) * 1.5))*People.overtime+(abs(People.salary+.05)
*2)*People.doubletime)

# Expected SQL--> SELECT people.first_name, people.last_name, people.user_id FROM 

people WHERE ((abs(people.salary + ?) * ?) * people.overtime) + ((abs(pe…

It is quick fix postette day here in the Moose-Pen

Yesterday I left off with one fail in my '30_where_basic.t' test case

# Expected SQL--> SELECT people.first_name First, people.last_name Last,

 people.user_id "User ID"

# Generated SQL-> SELECT people.first_name, people.last_name, people.user_id 

FROM people

    [

            {

      …

Its move along day here in the Moose-Pen

Today I managed to get the time to create '25_exe_array.t' which tests DBI execute array and it went quite well. No real story on that one just with my new 'Test::Utils::sql_param_ok' function I was able to quickly expand it out from just 3 tests to 6 in a few minutes.

I then moved on to '30_where_basic.t' where the idea was to test the basic parts of a 'condition', The basic parts being simple single level predicates mixing elements, functions and expressions.

After creating some 64 tests and giving it a few goes to get rid o…

Its little bird day here in the Moose-Pen

How does that 'The Be Good Tanyas' song go?

And I sing, the littlest birds sing the prettiest songs …

You will remember that I left off yesterday…

Its test test and test again day here in the Moose-pen

Today I added in over 70+ tests into '20_fields.t' and I went though almost every conflagration of fields, params, expression and functions I could think of even the expression from hell from this post.

I did today's work by the book meaning I did all the tests first then I did the debugging. Now I am not going to dump 450 plus lines of hash key value pairs here as that would really piss a few people off and I might loose a reader of two, as if an…

It is putter about some more day here in the Moose-Pen

I was puttering about today with my tests and I discovered something funny on some implications of SQL and it is to do with that 'AS' when playing with 'alias'. Now I knew it was an optional 'key-word' according to the SQL standard what I did not know is a number of DBs will syntax error when an optional key-word is used. So this SQL

SELECT sys_users.last_name AS last
FROM people AS sys_users

is correct to standard there are a number of DBs out there that o…

Its fix a but post-ette here in the Moose-Pen.

Yesterday I left off with this error;

Can't locate object method "alias" via package "Database::Accessor::Param"
GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 412.

For once in the Perl world the problem and error message are quite clear. Seem I never did add in the 'Database::Accessor::Roles::Alias' into th…

Its new test day again in the Moose-Pen

Well starting with the 20_fields.t test case today and this is really just this so far;

my $in_hash = {

    da_compose_only           => 1,

    update_requires_condition => 0,

    delete_requires_condition => 0,

    view                      => { name => 'people' },

    elements => [ { name => 'first_name', }, { name => 'last_name', }, ],

};

my $container = {

    last_name  => 'Bloggings',

    first_name => 'Bill',

};

my $tests = [
…

It carry on testing developing day here in the Moose-Pen.

I left off yesterday with a new Test::Utils->sql_param_ok sub that is going to save a good deal of coding effort as I move forward once I get all the tests up to the new standard of curse, (one foot forward one and a half steps back)

Today I am going to carry on with alias testing this time I am checking basic field alias. To accomplish this I changed the 'elements' in my $in_hash to ones with field aliases;

="pr…

Its go back and fix day here in the Moose-Pen

In a recent post I mentioned that I went out and read though a number of SQL syntax books to make sure I was doing things correctly in which SQL clauses (Where, Sort, Link) goes where. That go me to thinking was the system I was using to test the SQL on a real DB using standard SQL?

Normally I test with Oracle App Express as it work well on a Windows box and has a nice UI but I just noticed that I have some odd SQL in one of my test cases 15_alias.t;

I was starting with this hash;

/users/byterock/2018/07/index.html

It back to coding here in the Moose-Pen

About a week ago I had this rather obtuse SQL expression;

(abs((people.salary + .05) * 1.5))*people.overtime+(abs(people.salary+.05) *2)*people.doubletime)

for testing parentheses. I figured I might as well re-use that little snippet of code else where as it is valid SQL anywhere you would use a 'field'.

One very odd place to use it would be in a sort if you want to order a list by how much over time and double time a person earned. So I added a new test to my '60_order_by.t' case;

/users/byterock/2018/07/index.html

Still stuck in test mode here in the Moose-Pen.

Today I am re-writing the '47_dynamic_gathers.t' test. Looking at the code I cannot really use any of it as my Gather concept moved from two array-refs (gathers,filters) to just a singleton of 'Gather' made up of, elements and condtions.

The test is a little simpler now as all I have to test for is when I add a 'gather' the 'elements' and 'conditions' are correctly passed down from the DA to the DAD and when I add another 'gather' the current one is overridden by the new one;

To start I created two gathers;

/users/byterock/2018/07/index.html

Well it tests everything again day here in the Moose-Pen

Is been almost two weeks since I started mucking about with Accessor.pm again trying to get my inherited views, parentheses, and conditions all in a row before I pass them down to the DA and now that I have things I think all settled I it time again fro me got go back and re-run the test suite to see if I did not bugger too much up.

This time out I did not fair that bad

Test Summary Report

-------------------

t/33_conditions.t         (Wstat: 1024 Tests: 12 Failed: …

Its extend API a little more Day here in the Moose-Pen

Since I was playing about with the API by adding in 'default_condition' for conditions I figured I might as well do the same for condition 'operator'. In my little abstract world the 'operator' is what I use to compare the 'left' and 'right' parts of a predicate.

Well first thing some tests;

$da = Database::Accessor->new($in_hash3);

ok($da->default_operator('!='),'Change Default operator to !=');

 $da->retrieve( Data::Test->new());

ok(

    $da->result->e…

It new attribute day here in the Moose-Pen.

Just a quick postette here today. I was thinking that it might be a good idea if I expanded my Database::Accessor API a little and give the end user the option of specifying a different 'default' condition that the present 'AND'.

As a good programmer I first added in a test;

$da = Database::Accessor->new($in_hash3);

ok($da->default_condition('OR'),'Change Defalut condition to OR');

$da->retrieve( Data::Test->new());

ok(

    $da->result->error->gather->conditions->…

Its get nowhere day here in the Moose-Pen

So I was all set to for today's post to move from playing with Database::Accessor and start playing with Driver::DBI again but just as I was finishing off the tests which I did not want to bore you with I got this:

...

ok 7 - Balanced parentheses

no ok 8 - AND not added to first gather condition predicate

ok 9 - AND added to last gather condition predicate

…

ok 26 - AND not present on first link predicate

not ok 27 - AND not present on first link predicate

not ok 28 - AN…

It is go back again day here in the Moose-pen

In my last bunch of posts I though I had finally got all my element view, parentheses and 'AND' ; checking coming out all OK so I started today by going back to here I left off in Driver::DBI. The first thing I did was re-run the test suite and I ran into this

#   Failed test 'Where with function and 1 param SQL correct'
#   at D:\GitHub\database-accessor-driver-dbi\t\lib/Test/Utils.pm line 56.
# Expected SQL--> SELECT people.first_name, people.last_name, people.user_id
 FROM people WHERE length(people.last_name) = ?
# G…

It test review day again here in the Moose-Pen

Today I was having a look at coverage that the '58_parenthes.t' test case gives and with only seven tests it is rather sparse, time to add a few more in.

What I want to first improve is the coverage for 'functions' and 'expressions' to make sure the nested nature or the left and right attributes are correct. I started with this formula 'element' make up of a number of 'expressions','functions', 'params' and 'elements' that use parentheses;

(abs((People.salary + .05) * 1.5))*People.overtime+(abs(Peop…

It is fix a bracket and AND day in the Moose-pen

I did a little more checking of my tests in '58_parenthes.t' and it would appear that my changes from yesterday's post did not actually do the trick as the second test is failing and the 'Condition' part of the has is in error;

   conditions => [

        {

            left => {

                name => 'First_1',

                view => 'People'

            },

            right …

It condiment day again here in the Moose-Pen

Time to do some more catch-up coding as my most recent round of changes to fix all my 'element->view' woes I ended up removing any checks for 'parentheses' I had in place. To recap on the original idea of the parentheses check was to make sure that there was at least one closing bracket for each opening bracket in the 'conditions' being send down to the DAD.

As it stands now I am using the '_parentheses_check' sub to check all the attributes that that may contains not just an 'element' but any class that can use a 'predicate' so …

Another quick fix postette today in the Moose-Pen

Yesterday I had my gather test fail with;

ok 46 - Second Gather element does not inherit view

not ok 47 - Third Gather element inherits view

Can't call method "predicates" on an undefined value at 57_dad_elements.t line 486.

# Looks like your test exited with 255 just after 49.