Sunday, July 28, 2013

Xcode 4.5.2 IBOutlet not showing on context menu

IBOutlet will not appear when you show contextual menu on Label:





Context menu must be done on File's Owner:

Friday, July 12, 2013

Seamless dynamic SQL using PostgreSQL

Have you seen seamless dynamic query on a SQL Server function?

Neither do I, so I will show to you a PostgreSQL approach.

To cut to the chase, here's the query:

create or replace function DynamicSql(p_fields varchar)
    returns table
    (    
        "YouKnowMyName" varchar,
        "LookupTheNumber" int
    ) as    
$$
declare
    sql varchar;    
begin

    sql = 'select ' || p_fields || ' from employees';
    
    return query execute sql;
end;
$$ language 'plpgsql';

select * from DynamicSql(' "LastName", "EmployeeID" ' );
select * from DynamicSql(' "FirstName", "EmployeeID" ' );

Look Ma! No Drama!



Contrast that on how it is done on other database *cough* Sql Server Table-Valued Function via CLR *cough*:

http://www.ienablemuch.com/2013/07/streaming-table-valued-function-via-clr.html

Accomplishing dynamic SQL on a Table-Valued Function via CLR is too ceremonial and has too much drama, y'know you gotta need to accomplish all of these: FillRowMethodName, Streaming Table-Valued Functions via yield return, assembly signing, sp_configure 'clr enabled', create asymmetric key, grant external access, blah blah and all that shiznit.


Microsoft should be included on the list of featured PostgreSQL users given that Microsoft bought Skype. I Love Technology!


"You already trust PostgreSQL, you just may not know it yet..." -- http://www.2ndquadrant.com/en/who-uses-postgresql/

Happy Coding! ツ

Wednesday, July 10, 2013

Transposing columns to rows.

You have already probably done some crosstab query from your favorite RDBMS, i.e. the rows are transposed to columns.

But how about a reverse crosstab(a.k.a. reverse pivot)? You got columns that you wanted to transpose to rows.

That is, you need to display this...

testdb-# 

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select * from p;


 person_id | lastname  |  firstname   | nickname | favorite_number |       fullname       
-----------+-----------+--------------+----------+-----------------+----------------------
         1 | lennon    | john winston | john     |                 | john winston lennon
         2 | mccartney | james paul   | paul     |                 | james paul mccartney
         3 | harrison  | george       |          |                 | george harrison
         4 | starr     | richard      | ringo    |              10 | richard starr
(4 rows)


...as this:
person_id  |      field      |        value         
-----------+-----------------+----------------------
         1 | favorite_number | 
         1 | firstname       | john winston
         1 | fullname        | john winston lennon
         1 | lastname        | lennon
         1 | nickname        | john
         1 | person_id       | 1
         2 | favorite_number | 
         2 | firstname       | james paul
         2 | fullname        | james paul mccartney
         2 | lastname        | mccartney
         2 | nickname        | paul
         2 | person_id       | 2
         3 | favorite_number | 
         3 | firstname       | george
         3 | fullname        | george harrison
         3 | lastname        | harrison
         3 | nickname        | 
         3 | person_id       | 3
         4 | favorite_number | 10
         4 | firstname       | richard
         4 | fullname        | richard starr
         4 | lastname        | starr
         4 | nickname        | ringo
         4 | person_id       | 4
(24 rows)

On other RDBMS, you got to do this query:
with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select 
    person_id,
    'firstname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'firstname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'lastname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'nickname' as field,
    nickname as value
from p
union all
select 
    person_id,
    'fullname' as field,
    fullname as value
from p
union all
select 
    person_id,
    'favorite_number' as field,
    favorite_number::text as value
from p
order by person_id, field


That's very tedious.


If you are using Postgres, luckily there is a better way:

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select
    person_id,
    skeys(hstore(p)) as field, svals(hstore(p)) as value 
from p
order by person_id, field

That's more maintainable, even if you add another column on the main query, your key value pair query need not be modified.



There's another approach (darn, PostgreSQL is too flexible! :D)
with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select 
    p.person_id,
    unnest(array['person_id', 'favorite_number', 'firstname','fullname','lastname', 'nickname']) as field_label,
    unnest(array[cast(p.person_id as text), cast(p.favorite_number as text), p.firstname, p.fullname, p.lastname, p.nickname])  as field_value
from p
order by p.person_id, field_label



That's it folks. Happy Computing! ツ


Data Source:
create table person
(
 person_id serial not null primary key,
 lastname text not null,
 firstname text not null,
 nickname text null,
 favorite_number int null
);
 
 
insert into person(lastname,firstname,nickname, favorite_number) values
('lennon','john winston','john',default),
('mccartney','james paul','paul',default),
('harrison','george',default,default),
('starr','richard','ringo', 10);

Sunday, July 7, 2013

Seamless numeric localization with AngularJS

Have you seen seamless numeric localization with jQuery?

Neither do I, so I'll just offer you an AngularJS approach.

To make an input locale-aware we just need to add an attribute to make it capable as such. And make a directive for that attribute.

Localization with AngularJS is very easy. And we don't have to change anything on the controller to make it work on other language.

To make an input locale-aware we just need to add an attribute to make it capable as such. And make a directive for that attribute.


Old code:
<input type="text" ng-model="untaintedNumber"/>

Locale-aware code:
<input type="text" ng-model="untaintedNumber" numeric decimal-places="decPlaces" ng-change="showInLog()">



This is the definition for numeric and decimal-places attribute:

module.directive('numeric', function($filter, $locale) {
    return {
        restrict: 'A',
        require: 'ngModel',
        link: function(scope, element, attr, ngModel) {
        
            var decN = scope.$eval(attr.decimalPlaces); // this is the decimal-places attribute

        
            // http://stackoverflow.com/questions/10454518/javascript-how-to-retrieve-the-number-of-decimals-of-a-string-number
            function theDecimalPlaces(num) {
                   var match = (''+num).match(/(?:\.(\d+))?(?:[eE]([+-]?\d+))?$/);
                   if (!match) { return 0; }
                      return Math.max(
                       0,
                       // Number of digits right of decimal point.
                       (match[1] ? match[1].length : 0)
                       // Adjust for scientific notation.
                       - (match[2] ? +match[2] : 0));
            }
        
            function fromUser(text) {                   
                var x = text.replaceAll($locale.NUMBER_FORMATS.GROUP_SEP, '');
                var y = x.replaceAll($locale.NUMBER_FORMATS.DECIMAL_SEP, '.');
                                           
                return Number(y); // return a model-centric value from user input y
            }

            function toUser(n) {
                return $filter('number')(n, decN); // locale-aware formatting
            }
        
            ngModel.$parsers.push(fromUser);
            ngModel.$formatters.push(toUser);
        
            element.bind('blur', function() {
                element.val(toUser(ngModel.$modelValue));
            });
        
            element.bind('focus', function() {            
                var n = ngModel.$modelValue;
                var formattedN = $filter('number')(n, theDecimalPlaces(n));
                element.val(formattedN);
            });
        
        } // link
    }; // return
}); // module


String replaceAll definition:

String.prototype.replaceAll = function(stringToFind,stringToReplace){
    if (stringToFind === stringToReplace) return this;

    var temp = this;
    var index = temp.indexOf(stringToFind);
        while(index != -1){
            temp = temp.replace(stringToFind,stringToReplace);
            index = temp.indexOf(stringToFind);
        }
        return temp;
};


This is the controller, nothing needed be changed. The controller action changeValue keeps the old code.

function Demo($scope) {

    $scope.decPlaces = 2;

    $scope.untaintedNumber = 1234567.8912;

    $scope.changeValue = function() {
    
        // The model didn't change to string type, hence we can do business as usual with numbers.
        // The proof that it doesn't even change to string type is we don't even need 
        // to use parseFloat on the untaintedNumber when adding a 7 on it. 
        // Otherwise if the model's type mutated to string type, 
        // the plus operator will be interpreted as concatenation operator: http://jsfiddle.net/vuYZp/
        // Luckily we are using AngularJS :-)        
        $scope.untaintedNumber = $scope.untaintedNumber + 7;
    
        // contrast that with jQuery where everything are string:
        // you need to call both $('elem').val() and Globalize's parseFloat, 
        // then to set the value back, you need to call Globalize's format.
    
        /*              
        var floatValue = Globalize.parseFloat($('#uxInput').val());
        floatValue = floatValue * 2;        
        var strValue = Globalize.format(floatValue, "n4");
        $('#uxInput').val(strValue);
        */
    
    };

    $scope.showInLog = function() {
        console.log($scope.untaintedNumber);     
    };

}


Live Code: http://jsfiddle.net/F5PuQ/

Wednesday, July 3, 2013

Do you have a seek sense? Can you see dead code?

The original query is redacted to protect the innocent.

What's wrong with the following query? Can you spot the dead code?

create table tosses
(
  attempt int identity(1,1) primary key,
  fate char(1),
  constraint ck_tosses check(fate in ('H','T') or fate is null)
);

insert into tosses(fate) values
('H'),
('H'),
(null),
('T'),
(null),
('H'),
('T');

select
    attempt,
    fate,
    case fate 
    when 'H' then 'Head' 
    when 'T' then 'Tail'
    when null then 'Fate not yet determined'
    else 'http://9gag.com/gag/4380545'
    end as fate_result
from tosses

Live code: http://www.sqlfiddle.com/#!3/6a61e/2

The output of that is this:



| ATTEMPT |   FATE |                 FATE_RESULT |
--------------------------------------------------
|       1 |      H |                        Head |
|       2 |      H |                        Head |
|       3 | (null) | http://9gag.com/gag/4380545 |
|       4 |      T |                        Tail |
|       5 | (null) | http://9gag.com/gag/4380545 |
|       6 |      H |                        Head |
|       7 |      T |                        Tail |

The dead code is the WHEN NULL, the else part on the query above is just a decoy ツ To correct the dead code, we should change the WHEN NULL to WHEN fate IS NULL
select
    attempt,
    fate,
    case when fate is null then 'Fate not yet determined'
    else
        case fate 
        when 'H' then 'Head' 
        when 'T' then 'Tail'
        else 'http://9gag.com/gag/4380545'
        end
    end as fate_result
from tosses

Live code: http://www.sqlfiddle.com/#!3/6a61e/3

Output:
| ATTEMPT |   FATE |             FATE_RESULT |
----------------------------------------------
|       1 |      H |                    Head |
|       2 |      H |                    Head |
|       3 | (null) | Fate not yet determined |
|       4 |      T |                    Tail |
|       5 | (null) | Fate not yet determined |
|       6 |      H |                    Head |
|       7 |      T |                    Tail |  

You can also do the other form of CASE expression:
select
    attempt,
    fate,

    case
    when fate = 'H' then 'Head'
    when fate = 'T' then 'Tail'
    when fate is null then 'Fate not yet determined'
    else 'http://9gag.com/gag/4380545'    
    end as fate_result

from tosses

Live code: http://www.sqlfiddle.com/#!3/6a61e/4

Output:
| ATTEMPT |   FATE |             FATE_RESULT |
----------------------------------------------
|       1 |      H |                    Head |
|       2 |      H |                    Head |
|       3 | (null) | Fate not yet determined |
|       4 |      T |                    Tail |
|       5 | (null) | Fate not yet determined |
|       6 |      H |                    Head |
|       7 |      T |                    Tail |  


On both form of the correct query above, the dead code in that query is no longer the NULL scenario, the dead code is the ELSE part as we have CHECK constraint in place.


Happy Coding! ツ