It case out day here in the Moose-Pen
After looking at some more examples of 'CASE' in SQL I cam across one I have not encountered yet;
SELECT CASE
WHEN Products.Price 10 THEN 'under 10$'
WHEN (Products.Price >= 10
AND Products.Price = 30)
OR (Products.Price >= 40
AND Products.Price =50) THEN '10~30$ or 40~50$'
ELSE 'Over 50$'
END
FROM Products
/users/byterock/2018/08/index.html
Another Case Day here in the Moose-pen
So I decided to do some more case work today specificity their rather odd little SQL to start;
SELECT CASE WHEN Price 100 THEN 'under 100$'
WHEN Price in (105, 110,120) THEN Price 105,110 or 120$'
ELSE 'Over 120$' END
FROM Products
and the above comes out as this test;
{
caption => 'Retrieve with case using In clause',
…
Its Sort the Case day here in the Moose-Pen
It appears that one of the more common uses for the CASE statement in SQL is in a 'GROUP BY' or at least a good 80% of the tutorials for SQL I have looked at have it as an example so let see how well the present Driver::DBI handles it.
To start I just add this test to the '60_order_by.t' test case;
{
key => 'sorts',
sorts => [{whens=>[{ left => { name => 'Price', },
right => { value => '10' },
…
Its another case day here in the Moose-Pen
Yesterday I left off with this result
Expected--> SELECT CASE WHEN Price ? THEN ? WHEN Price >= ? AND Price = ? THEN ? WHEN Price > ? AND Price = ? THEN ? ELSE ? END AS price_group FROM Products
Generated-> SELECT CASE WHEN Price ? THEN ? WHEN Price >= ?andPrice = ? THEN ? WHEN Price > ?andPrice = ? THEN ? ELSE ? END price_group FROM Products
In the above I see at least fourt pro…
Finally its Driver::DBI Case day in the Moose-Pen
Has it really been since August 15th that I have been mucking about with Case? I guess after almost two weeks I am glad to finally get into the DBI::Driver part of things. As usual to start (well recap really) lets look at this SQL;
SELECT ProductName,
CASE WHEN Price 10 THEN 'under 10$'
WHEN Price >=10 AND Price = 30 THEN '10~30'
WHEN Price >30 and Price = 10…
Well it why stop a good thing day here in the Moose-Pen
I usually try once a week to do a full regression test on both Database::Accessor and Driver::DBI so I figure I might as well keep that going and have another testing postette again.
Looking at Database::Accessor I see that we had quite a few changes since my last full regression
8 files changed, 579 insertions(+), 158 deletions(-)
but I have been trying to keep up so I am expecting fairly good results. On the first test run I get;
/users/byterock/2018/08/index.html
No!! Still on a case here in the Moose-Pen.
I just started my first test for '22_fields_extended.t' and then I saw one other little point I forgot to take into account. I should add in some validation for my 'Case' class as it is invalid to have a case that has only one condition.
Therefore this test;
like(exception {Database::Accessor::Case->new( {
whens => [
{
left => { name => 'Price', },
right => { value => '10' },
oper…
Oh well! Still stuck in DA day here in the Moose-Pen.
Well I still have a little more work to do as I just read that you can have a 'case' in a 'Join/link' as this;
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
ELSE NULL
END = a.con…
It't clean-up test day again here in the Moose-Pen
Well over the past few days I seem to be just about ready to go back to Driver::DBI but I find yet another little thing wrong with my Database::Accessor when playing with my new test Class.
The first thing is to clean up the test case '15_case.t' as it hard 255 fails with;
Can't call method "statement" on unblessed reference at 15_case.t line 281.
That one is really just an update to take into account yesterday's change from a flat array-ref to an array-ref…
It still stuck on Case here in the Moose-Pen
Same sort of story as my last post I as looking at my tests and just cleaning up a few typos and a little perl tidy and I notice the when I have a 'case' like this;
whens => [
{
left => { name => 'Price', },
right => { value => '10' },
operator => '',
statement => { value => 'under 10$' }
},
[
{
l…
Its change my case yet again day here in the Moose-Pen
Yesterday as I had finishing off my tests for the Case class I was just about to move over and start writing up the code for Driver::DBI when I remember that I should have a test or two to ensure that Database::Accessor can use the new 'Case' class and that is passed it correctly down to the DAD.
I am using it right now as a field and I can test by just adding to the '31_elements.t' test case.
Frist I add in just a simple 'case' as I have already tested all the funny permutations in the '15_case.t' test case;
/users/byterock/2018/08/index.html
Its back-fix code day here in the Moose-pen
In yesterday's post-ette I ranted on about how much of a smarty pants I was to use a 'param' in as the type for my 'message' part of a case statement. That was working fine until I blundered across this perfectly valid SQL
SELECT StockID, OnOrderQuantity,
CASE
WHEN Stock - OnOrderQuantity > 30 THEN 'Over Stocked'
WHEN Stock - OnOrderQuantity > 0 AND Stock-OnOrderQuantity =30 THEN 'Stocked'
ELSE Stock - OnOrderQuantity
END
FROM Stock;
The 'else' in this case is an 'ex…
It's explain post-ette day there in the Moose-Pen
As I reviewing some of my code for 'Case' statements I had chanch to stop and think about why I was using a Param class for the message on a case. Right now I have something like this;
{
left => { name => 'Price', },
right => { value => '10' },
operator => '',
message => { value => 'under 10$' }
},
Before I had that just as a simple str…
Its test post-ette day again here in the Moose-Pen
I guess I am following a very predictable pattern The weekend is here and I just do a post on test results. Well why spoil a good thing?
Since my last test round I have changed a good number of things about but I am fairly sure that Database::Accessor is in good shape though I am not looking forward to see what sort of state Driver ::DBI is in.
As I suspected there was only very minor problems with Database::Accessor;
Looks like you planned 2 tests but ran 6.
t/15_cas…
Still case day here in the Moose-Pen
Following on from yesterday's post I first want to make a little changes to my 'Case' class; I think it will be better to have this
--has 'case' => (
++has 'whens' => (
isa => 'ArrayRefofWhens',
is => 'ro',
required => 1,
);
and that jibes a little better with the type that I am using. I will of course have …
It is another head case day here in the Moose Pen.
So yesterday I left you hanging on how I was going to incorporate the 'case' statement into Database::Accessor hopefully today I will find some solution for it.
To start off my attempts to use the present class structure ended in failure and from my analysys of the problem I have one of two choices.
- Add in new attributes
- Add in a new class
The main disadvantage of the first solution is I will have a number of attributes that will be useless 99.99% of the time. The …
Its get off my case day here in the Moose-Pen
Well I am getting close to running out of things to add into Driver ::DBI as I have all the operators covered, functions, expressions, elements, etc etc so I do see and end in sight maybe I won't get a year of blog posts in after all??
Today I am going to try and tackle on of the much abused SQL commands the 'CASE' statement. The question is where to test this one as it can appear anywhere a predicate can so in a 'sort/order by', 'element/field', even a 'link/join'. I was thinking the best spot for it would be back in 32_where_ope…
Its little validation day here in the Moose-Pen
Since I didn't put any new code in yesterday just take out I figure I might as well do that little validation I talked about in my last post namely you can only have one aggregate function inside another aggregate. As usual I start with a test;
{
caption => 'Can not have a an aggregate in an aggregate',
type => 'exception',
key => 'elements',
elements => [
{
function => 'count'…
Its plan but do not code days here in Moose Pen
So I was all prepped and ready to start on the aggregate command for SQL as I had research the MongoDB and other similar non-sql dbs and they all shared the same basic set of commands. I even started writing up some tests starting with this SQL
SELECT person.last_name,SUM(person.pay) FROM person GROUP BY person.last_name
all well and good you need to use the group by with that aggregate. However I forgot that about 90% of the dbs out there let you do this
/users/byterock/2018/08/index.html
It the last operator day here in the Moose-Pen
Well today is a very short postette but at least I am adding in the last operator 'ANY' and this time I need only this test;
{
caption => 'ANY Operator with Data::Accessor',
key => 'conditions',
conditions => [
{
left => {value=>Test::Utils::in_da_sql()},
operator => 'ANY',
},
],
create => {
container => $container,/users/byterock/2018/08/index.html
Its penultimate operator day here in the Moose-Pen
This is just a quick postette today add in what I think is next to last special 'operator' the 'All' and as before I start with a test;
{
caption => 'Any Operator with Data::Accessor',
key => 'conditions',
conditions => [
{
left => {value=>Test::Utils::in_da_sql()},
operator => 'aLl',
},
],
create => {
container =…
Its solipsism day here in the Moose Pen
Getting down to the last few operators on my list and today I will hopefully be an easy one 'Exists'
At least this one should be easy to validate as you can only ever use it with a sub-query so I will add that in from the start but as always before I hit the Driver::DBI code a test;
{
caption => 'Exists Operator with Data::Accessor',
key => 'conditions',
conditions => [
{
left => {value=>Test::Utils::in…
Its one more operator day here in the Moose-pen
Today I am finally going to get around to the last two of my operators the 'Like' and 'Not Like'. This is where we get into sort a sticky wicket and we are entering the realm of 'regular expression' queries which may work quite differently on the various SQL boxes and I know 100% different in MongoDB and other non SQL dbs. So what to do??
Well I might as well go with the standard SQL ones '%' and '_' meaning multiple and single characters, and the next question do I validate for their present in a 'Like' as this sql
…
It is catch up day here in the Moose-Pen.
I was just about to add in the next operator on my, the 'Like' command but before I did that I added a few more tests for the 'in' operator and with this test;
{ caption => 'in right must not be an hash ref',
type => 'exception',
key => 'conditions',
conditions => [
{
left => { name => 'cost' },
right => { name => 'cost' } ,
operator => 'IN',
},
],/users/byterock/2018/08/index.html
Its is in day here in the Moose-pen
Now that I have a few little API things worked out from yesterday's post I better carry on and do the next two 'operators' 'In' and 'Not In'
My original though was these two operators would now require a little parameter validation like I did for the 'Between' as most of us know the very common form of an SQL 'In' queries one like these two;
SELECT * FROM users WHERE id in (SELECT user_id FROM drivers) ;
SELECT * FROM users WHERE id in (109,100,22) ;
should just be…
It fix the API (yet again) day here in the Moose-Pen
So as we all know I have been playing with the extended operators over the last few posts and I just notices something in my code;
I can enter a mixed case operator like this 'BeETween' and things will still work. This is good but I think I left a part out on the Database::Deriver side of things and in Driver ::DBI I do this;
elsif (uc($predicate->operator) eq Database::Accessor::Driver::DBI::SQL::BETWEEN) {
I uc or upper case that operator and if I ta…
Its yet another operator day here in the Moos Pen
Hmm I could just copy and past yesterday's postette here and and in 'NOT' in the correct places to get today's 'Is Not Null' operator postette but I an not going to take the schlock path today.
As I implied it is the turn of the 'Is Not Null' operator today another very easy one for a postette ad I start with the same test as yesterday and just add Not where needed, no need for that here.
As for the Driver::DBI code I could just add in another 'else if' into that _predicate_sql' sub but I think I will take a little…
It is operator carry on day in the Moose-Pen
Just a quick postette on adding in another operator to Driver::DBI. Today I am adding the 'Is Null' operator. First of course a set of tests to add into '32_where_operators.t' along this vain;
{
caption => 'Is Null Operator Params',
key => 'conditions',
conditions => [
{
left => {
name => 'salary',
view => 'people'
},
…
It extend test helper day here in the Moose-pen.
Yesterday I managed to get the 'Between' operator to work but there was one little thing that was bugging my and that was I had some 40 lines of new code and only three new tests for the possible exception the 'Between' operator might generate. To boot the three new tests where an anti-pattern.
Now there is no problem with having anti-patterns in test code you just end up with many more lines in your than you need and they may be problematic to fix if they ever go wrong. Normally I would not bother to re-factor just three test…
Its advance my code day here in the Moose-Pen.
Seems I have hit a milestone in the Database::Accessor/Driver::DBI project I have completed all the easy stuff and I am moving over to the more complicated or at least ignored till now parts.
I decided to work on some of the more advance operators found in conditional statements namely 'Between', 'In', 'Not In', 'Like', 'Is Null' and 'Is Not Null'. Now one problem with these six operators is they are very SQLish, epically the 'Like' operator, and despite me trying to come up with a more generic set of operator names just looke…
It is more order code day here in the Moose-Pen
Yesterday I left off with the choice of either a change to do to my tests or a change to my code to solve this error;
# Expected SQL-> ORDER BY people.last_name, people.first_name
# Generated SQL-> ORDER BY people.last_name ASC, people.first_name ASC
Now this comes from how how I define the 'order' attribute in the 'Database::Accessor::Roles::Element' role;
has order => (
is => 'rw',
…
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations