Cargo Cult Programming with COALESCE

I notice one query that do this:

select sum(coalesce(qty,0)) from sample;

Yes, this would result to null:

 select 1 + 2 + null as total;

However, like with most things in life, NULL interpretation in SQL is not symmetrical, given the values above and materializing it to rows:

insert into sample(item,qty) values('keyboard',1); insert into sample(item,qty) values('mouse',2); insert into sample(item,qty) values('cpu',null);

This query would not yield null:

select sum(qty) as total from sample;

That would result to sum of 3, not null.

And given a table with no rows, which of the following would report a value of 0 when there's no rows?

create table table_with_no_row ( item varchar(50) not null, qty int ); select sum( coalesce(qty, 0) ) as grand_total from table_with_no_row; select coalesce( sum(qty), 0 ) as grand_total from table_with_no_row;

Surprise! Ok not so surprising to some, it's the latter that will yield 0. The first one will yield null, despite having coalesce too

Another good use of coalesce:

select h.order_id, h.order_date, coalesce(sum(d.qty),0) as total from order_header h left join order_detail d on d.order_id = h.order_id group by h.order_id     ,h.order_date; select h.order_id, h.order_date, sum(coalesce(d.qty,0)) as total from order_header h left join order_detail d on d.order_id = h.order_id group by h.order_id     ,h.order_date;

Both queries above would yield the same result.

order_id    order_date total ----------- ---------- ----------- 1           1940-10-09 2 2           1956-10-28 0 (2 row(s) affected)

Given they have the same result, why should we opt for sum( coalesce(d.qty,0) )?

Aggregate functions automatically discard null values, hence it's superfluous to put a coalesce inside an aggregate function. Placing a coalesce inside an aggregate hurts performance, as coalesce is repeatedly executed on every row. I once optimized a very slow query just by removing the COALESCE inside of SUM and placing it outside of SUM, way back long time ago since the dawn of time

Cargo Cult Programming is rearing its ugly head. Cargo Cult Programming is wreaking havoc on database performance

Happy Computing! ツ

Implementing a database admin interface in AngularJS is a breeze

Implementing a database interface(think phpPgAdmin) via web with semantics (read: HTML5) and AngularJS is a walk in the park

View:

<div ng-repeat='p in personTable'>
    {{p.label }} <input ng-model='person[p.fieldName]' type='{{p.type}}' />
</div>


<div>Favorite Number via slider: <input ng-model='person.FavoriteNumber' type='range'/></div>


<hr/>


{{person}}

Controller and models:

function Something($scope) {
    $scope.personTable = [
        { fieldName: 'Lastname', type : 'text' },
        { fieldName: 'Firstname', type : 'text' },
        { fieldName: 'Birthday', type : 'date' },    
        { fieldName: 'FavoriteNumber', type : 'number', label: 'Favorite #' }
    ];
    
    for(var fieldIndex in $scope.personTable) {
        var field = $scope.personTable[fieldIndex];
        if (field.label == undefined) 
            field.label = field.fieldName;
    };
    
    $scope.person = { Lastname : 'Lennon', Firstname : 'John', Birthday: '1940-10-01', FavoriteNumber : 9 };
    
}

Works on Chrome

Live code: http://jsfiddle.net/5WvWP/

Please RTFM

Got my fair share of RTFM last week. I've written a REST-adhering AngularJS code for deletions. I'd written it this way:

$http.delete('/departments', { Id: d.DepartmentId }).success(function (responseData) {
            alert('ok');
});

But the record is not deleted.

I assumed the second parameter is a data parameter, I should thought early that second parameter has many options. Another lesson learned, aside from not reading the AngularJS doc, handling errors should be our second nature, the errors provided by ServiceStack helps a lot for debugging why the particular method for RESTful deletion is not routed to its proper service method.

Upon adding .error callback, lot of guessworks were avoided on why there's a 405 error, which naturally led me to read AngularJS doc. $http delete has a config parameter, the second parameter is not directly a data paramater


To correctly pass the data parameter with AngularJS:

$http.delete('/departments', { params : { Id: d.DepartmentId } }).success(function (responseData) {
    alert('ok');
}).error(function(data, status, headers, config) {
    console.log(data);
    alert(data.ResponseStatus.Message);
    alert(status);
});

Obligatory xkcd reference: http://xkcd.com/293/

Got multitude of jQuery approaches to offer? Thanks but no thanks, I got AngularJS

Just when I thought I already unearthed all the possible techniques for obtaining the value from a dropdown selection:

$('#CountryCode').change(function () {


 // Why use this?
 // alert($('option:selected', '#CountryCode').val());

 // Or this?
 // alert($('option:selected', $(this)).val());


 // When this will suffice:
 alert($(this).val());
});

Apparently, I lack imagination, I saw this code from a dropdown change event:

$('#CountryCode > :selected').attr('value');

Something is fundamentally wrong on that last code but I can't quite put my finger on it. Oh darn, now I remember.. that attr('value') is so leaking the abstraction!


Getting the value of something is a staple in our everyday job, hence we should give it a first-class treatment, give it a proper abstraction, i.e. we should be able to use this: $(this).val(), not a hodge-podge of stringly-typed voodoo magic that $('#Country > :selected').attr('value') is


I'm not gonna remember all the possible jQuery combinations the mankind has ever known just to obtain that elusive value from a dropdown control, what other approaches should I remember?


Ok, thanks, but no thanks, I got AngularJS, I just have to remember this: $scope.CountryCode



The question I posed above is rhetorical, but you can probably show other jQuery approaches for getting the value from a dropdown change event that I missed, be it a shorter code or long code, seriously.


Being abstract is something profoundly different from being vague... The purpose of abstraction is not to be vague, but to create a new semantic level in which one can be absolutely precise. -- Edsger Dijkstra

Javascript mental gymnastic

var s = 
     '(function() {\
        return function(n){\
            return n * n;\
        };\
     })()';
     
     
var f = eval(s);
console.log(f);
alert(f(3));

Give semantics to your web app using AngularJS

Make percent inputs conform to their true semantics, i.e. 27% is 0.27, just like in Excel. Not the textual 27 which necessitates manually dividing the number by 100 when using it further on expression.



When you input 27 on Interest Rate Per Annum textbox, the model’s returned value should be 0.27. Note the custom attribute percent below. AngularJS can augment your existing HTML and introduce new attributes to them, it can even create new HTML tags. The percent attribute automatically convert the percent input to its percent value.

<input type='text' ng-model='InterestRatePerAnnum' percent/>
  
<input type=’text’ ng-model='TotalContractPrice' />
 
.
.
.

var result = $scope.InterestRatePerAnnum * $scope.TotalContractPrice;

Live code: http://jsfiddle.net/b75xaj69/



Contrast to manual approach:

<input type='text' id='InterestRatePerAnnum' />
 
<input type=’text’ id='TotalContractPrice' />

.
.
.
 
var result = ($('#InterestRatePerAnnum').val() / 100) * $('#TotalContractPrice').val();

Binding vs DOM manipulation. AngularJS vs jQuery

Bind JavaScript values (aka JSON) to HTML so incessant element placeholders won’t be needed as much

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script type="text/javascript" src="angular.min.js"></script>
</head>

<body ng-app>


<label for="Multiplier">Multiplier</label><input ng-model="Multiplier" type="text"><br/>
<label for="Multiplicand">Multiplier</label><input ng-model="Multiplicand" type="text"><br/>
<input value="Multiply" type="button"/><br/>

<b>Product of {{Multiplier}} and {{Multiplicand}} is {{Multiplier * Multiplicand}}</b>




</body>
</html>

DOM Manipulation, very tedious:

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script type="text/javascript" src="jquery-1.9.0.min.js"></script>
</head>

<body>


<label for="Multiplier">Multiplier</label><input id="Multiplier" type="text"/><br/>
<label for="Multiplicand">Multiplier</label><input id="Multiplicand" type="text"/><br/>
<input value="Multiply" type="button" id="Multiply"/><br/>

<b>Product of <span id="MultiplierSpan"></span> and <span id="MultiplicandSpan"></span> is <span id="ProductSpan"></span></b>


<script type="text/javascript">

    $(function() {

        $('#Multiplier').keyup(function() {
            var multiplier = $('#Multiplier').val();
            $('#MultiplierSpan').text(multiplier);


            computeProduct();
        });

        $('#Multiplicand').keyup(function() {
            var multiplicand = $('#Multiplicand').val();
            $('#MultiplicandSpan').text(multiplicand);


            computeProduct();
        });


        function computeProduct() {
            var multiplier = $('#Multiplier').val();
            var multiplicand = $('#Multiplicand').val();

            var product = multiplier * multiplicand;
            $('#ProductSpan').text(product);
        }



    });

</script>

</body>
</html>

jQuery is too imperative, get declarative with AngularJS

Imperative approach is too ceremonial:

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script type="text/javascript" src="jquery-1.9.0.min.js"></script>
</head>
<body>

Show Favorite Numbers: <input id="ShowFaveNumbers" type="checkbox">

<ul id="FaveNumbers">
    <li>5</li>
    <li>2</li>
    <li>0</li>
</ul>



<script>
    $(function() {

        function showOrHide() {
            var isChecked = $('#ShowFaveNumbers').is(':checked');

            var fn = $('#FaveNumbers');
            if (isChecked)
                fn.show();
            else
                fn.hide();
        }


        $('#ShowFaveNumbers').change(function() {
            showOrHide();
        });

        showOrHide();

    });
</script>


</body>
</html>

Get declarative with AngularJS:

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script type="text/javascript" src="angular.min.js"></script>

</head>

<body ng-app>

Show Favorite Numbers: <input ng-model="ShowFaveNumbers" type="checkbox">

<ul ng-show="ShowFaveNumbers">
    <li>5</li>
    <li>2</li>
    <li>0</li>
</ul>



</body>
</html>

Live codes:

AngularJS: http://jsfiddle.net/t83UC/

jQuery: http://jsfiddle.net/ZzeuR/

jQuery many rules makes me embrace AngularJS more

Converting jQuery prop method to is method:

var isChecked = $('#ShowFaveNumbers').prop('checked');

Is not a mere changing of the method:

var isChecked = $('#ShowFaveNumbers').is('checked');

You have to remember some rules, must do this:

var isChecked = $('#ShowFaveNumbers').is(':checked');

In AngularJS, it's just a mere getting of model

var isChecked = $scope.ShowFaveNumbers;