Wednesday, October 16, 2013

Sparrow Framework Day 2. Learned How To Use Atlas

Tuesday, October 15, 2013

Use IMMUTABLE on PostgreSQL function to avoid optimization fence

I thought this is the PostgreSQL equivalent of SQL Server's efficient inline table-valued function:
create or replace function get_orgs() returns table
(
ID int,
Favorite int
)
as
$$ 
    select p.person_id, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
$$ language sql;


Using that function on this expression..
select p.*, o.* 
from person p
join get_orgs() o on p.person_id = o.ID
where p.person_id < 4
..produces a divide-by-zero error on PostgreSQL, while SQL Server does not materializes the rows that are greater than or equal to 4, hence this doesn't produce an error on SQL Server:

ERROR:  division by zero
CONTEXT:  SQL function "get_orgs" statement 1

********** Error **********

ERROR: division by zero
SQL state: 22012
Context: SQL function "get_orgs" statement 1


To achieve the same optimized execution plan of SQL Server on PostgreSQL, must add IMMUTABLE on the function:
create or replace function get_orgs() returns table
(
ID int,
Favorite int
)
as
$$ 
    select person_id, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
$$ language sql IMMUTABLE;
When that function is used on the the query above, it doesn't yield divide-by-zero anymore, it's now efficient, it doesn't eager load the function's result, it's like the function is inlined on the query itself, the query that joins to the IMMUTABLE function behaves like a table-deriving query, very efficient, no divide-by-zero will occur, i.e., the RDBMS just expand the function's query to other query, divide-by-zero error won't happen, to wit:
select p.*, o.* 
from person p
join
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
o on p.person_id = o.ID
where p.person_id < 4;
Produces this output, has no divide by zero error:
 person_id | lastname  |  firstname   | nickname | favorite_number | id | favorite 
-----------+-----------+--------------+----------+-----------------+----+----------
         2 | mccartney | james paul   | paul     |                 |  2 |        7
         3 | harrison  | george       |          |                 |  3 |        7
         1 | lemon     | john winston | john     |                 |  1 |        7
(3 rows)
However, PostgreSQL, unlike SQL Server, has an optimization fence on its CTE, i.e., PostgreSQL eagerly loads the result of the CTE. SQL Server's CTE is more efficient than PostgreSQL's CTE on this regard:
with o as
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
select p.*, o.* 
from person p
join o on p.person_id = o.ID
where p.person_id < 4;
That CTE yields the following error on Postgresql, a proof that Postgresql eagerly load the result of the CTE; while on SQL Server it has no error, SQL Server doesn't eagerly load the result of CTE, a proof that SQL Server sees CTE as subject to execution plan rather than an eager-loading mechanism to fetch the results. Eagerly-loaded results has no chance to be optimized, as it looks like a blackbox from the caller, the caller can't optimize what's inside
ERROR:  division by zero

********** Error **********

ERROR: division by zero
SQL state: 22012
If we will follow C language principle design, I wishes PostgreSQL to use an existing keyword to indicate we don't want an optimization fence on a CTE:
with o immutable as
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
select p.*, o.* 
from person p
join o on p.person_id = o.ID
where p.person_id < 4;
If a CTE's query can be re-used, the query is better be made as view instead, a query inside a view don't have optimization fence too, i.e., it also behaves as table-deriving query, view's query just get expanded to other query, this won't produce divide-by-zero error:
create or replace view vw_get_orgs as
    select person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then person_id / 0 else 7 end
    ) eorg(ounits) on true;


select p.*, o.* 
from person p
join vw_get_orgs o on p.person_id = o.ID
where p.person_id < 4;


Happy Coding!

Very efficient JSON in PostgreSQL

From this old approach:

  select array_to_json(array_agg(row_to_json(t))) 
  from (select firstname, lastname, favorite_number from person) t;


To this new one:
  select json_agg(t)
  from (select firstname, lastname, favorite_number from person) t;

Sunday, October 13, 2013

Sunday, October 6, 2013

Bye bye C#, PHP, Java, Ruby. You are so long, and thanks for all the fish*

I told my friend(whose career is built around PHP) about AngularJS, like everybody else who discovered the joys of using AngularJS, he's very excited learning something revolutionary. He shared that one time he's learning AngularJS up to 3 AM.




What he likes in AngularJS is the PHP's role in a web application development is taking a backseat, and the realization that he doesn't even need PHP at all, any server-side languages that can serve JSON would do, be it from C#, Java, Ruby and whathaveyou. He also don't like it that PHP and HTML are being interwoven too much. I don't know if Mark Zuckerberg shares the same sentiment.


One thing is for sure though, given that interweaving PHP and HTML is unavoidable, the integration should feel natural and make the integration cross-site-scripting-attack-proof. Facebook took a page from ASP.NET MVC Razor capabilities on this matter, Facebok created XHP. With XHP, they made interweaving PHP and HTML very seamless, PHP's output is now automatically HTML-escaped too, cross-site-scripting attacks can be avoided. Of course, I'm just massaging the ego of ASP.NET MVC programmers here, open-source folks doesn't need our innovations :-) Seamless integration between programming language and markup language is something everyone are pining for ever since everyone moved to web for rapid application development. I digress.




It's not surprising that AngularJS is the easiest to use JavaScript MVC framework, given that Misko Hevery created AngularJS as an easy-to-use framework for his JSON service business, which is priced by the megabyte. JSON service is a bad kind of business though, if they really wanted to earn well they should serve XML instead of JSON :D



While everyone is abuzz and getting nerdgasmic with the release of iPhone 5S last September 10, I'm eagerly anticipating and excited on the release of PostgreSQL 9.3 last September 9. JSON in PostgreSQL is becoming more and more a first-class language construct, API-wise and syntax-wise.



As for the reason why the excitement and how it relates to AngularJS, let's get to the core principle of AngularJS:


Every web-application is really just once complex marshaling problem. We marshal data from the DB, through the HTTP into the heap and to the DOM for the user to read, and then we do it all again in reverse order. At first this is fun, but after you build few applications, you very quickly get tired of marshaling your data through these layers, and I wanted to do something about it. -- Misko Hevery



While my friend is happy with lesser role of PHP in a web application development using AngularJS, any server-side languages for that matter, I'm stoked at the idea that we don't even need server-side languages at all. If only RDBMSes can expose RESTful services the way ServiceStack, ASP.NET Web API, WCF, JAX-RS and Recess Framework can do, the days of server-side languages would be numbered. Bye bye C#, PHP, Java, Ruby, you are so long, and thanks for all the fish.



RDBMS RESTful? I'm fancying that. But before we get there, let's see how easy it is to make web applications with AngularJS and the supercharged PostgreSQL JSON functionalities.



Here's how easy it is to serve JSON with PostgreSQL:

northwind=# with company_summary_info as 
(
    select c."CustomerID", c."CompanyName", c."ContactName"
    from customers c where c."ContactName" like 'A%'
)
select json_agg(i.*)
from company_summary_info i;
                                                 json_agg                                                  
-----------------------------------------------------------------------------------------------------------
 [{"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactName":"Ana Trujillo"}, +
  {"CustomerID":"ANTON","CompanyName":"Antonio Moreno Taquería","ContactName":"Antonio Moreno"},          +
  {"CustomerID":"EASTC","CompanyName":"Eastern Connection","ContactName":"Ann Devon"},                    +
  {"CustomerID":"FAMIA","CompanyName":"Familia Arquibaldo","ContactName":"Aria Cruz"},                    +
  {"CustomerID":"GOURL","CompanyName":"Gourmet Lanchonetes","ContactName":"André Fonseca"},               +
  {"CustomerID":"LAMAI","CompanyName":"La maison d'Asie","ContactName":"Annette Roulet"},                 +
  {"CustomerID":"MORGK","CompanyName":"Morgenstern Gesundkost","ContactName":"Alexander Feuer"},          +
  {"CustomerID":"ROMEY","CompanyName":"Romero y tomillo","ContactName":"Alejandra Camino"},               +
  {"CustomerID":"SPLIR","CompanyName":"Split Rail Beer & Ale","ContactName":"Art Braunschweiger"},        +
  {"CustomerID":"TRADH","CompanyName":"Tradição Hipermercados","ContactName":"Anabela Domingues"}]
(1 row)


Very easy.


The plus signs doesn't turn up in actual JSON result, it's just presented that way by PostgreSQL's commandline tool. The type returned is not a string, PostgreSQL has a JSON datatype. Being a first-class data type that it is, you can manipulate it further with PostgreSQL's superb JSON API and operators.




Returning a JSON paged result(along with total records count) with the latest PostgreSQL:

create or replace function get_customers(p_pageNumber int, p_pageSize int) returns json
as
$$
begin

return (
    with src as
    (
        select c."CustomerID", c."CompanyName", c."ContactTitle", c."ContactName", c."City"
        from customers c
        order by c."CompanyName"
    ),
    paged as
    (
        select * 
        from src
        offset ((p_pageNumber - 1) * p_pageSize)        
        limit p_pageSize
    ),
    returnedJson as
    (
        select  
            (select json_agg(p.*) from paged p) as list,
            (select count(*) from src) as count
    )
    select row_to_json(r.*) from returnedJson r
);  
    
end;
$$ language 'plpgsql';    
 
 


Sample result:

northwind=# select get_customers(1,10);
                                                                            get_customers                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"list":[{"CustomerID":"ALFKI","CompanyName":"Alfreds Futterkiste","ContactTitle":"Sales Representative","ContactName":"Maria Anders","City":"Berlin"},            +
  {"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactTitle":"Owner","ContactName":"Ana Trujillo","City":"México D.F."},               +
  {"CustomerID":"ANTON","CompanyName":"Antonio Moreno Taquería","ContactTitle":"Owner","ContactName":"Antonio Moreno","City":"México D.F."},                        +
  {"CustomerID":"AROUT","CompanyName":"Around the Horn","ContactTitle":"Sales Representative","ContactName":"Thomas Hardy","City":"London"},                        +
  {"CustomerID":"BSBEV","CompanyName":"B's Beverages","ContactTitle":"Sales Representative","ContactName":"Victoria Ashworth","City":"London"},                     +
  {"CustomerID":"BERGS","CompanyName":"Berglunds snabbköp","ContactTitle":"Order Administrator","ContactName":"Christina Berglund","City":"Luleå"},                 +
  {"CustomerID":"BLAUS","CompanyName":"Blauer See Delikatessen","ContactTitle":"Sales Representative","ContactName":"Hanna Moos","City":"Mannheim"},                +
  {"CustomerID":"BLONP","CompanyName":"Blondesddsl père et fils","ContactTitle":"Marketing Manager","ContactName":"Frédérique Citeaux","City":"Strasbourg"},        +
  {"CustomerID":"BONAP","CompanyName":"Bon app'","ContactTitle":"Owner","ContactName":"Laurence Lebihan","City":"Marseille"},                                       +
  {"CustomerID":"BOTTM","CompanyName":"Bottom-Dollar Markets","ContactTitle":"Accounting Manager","ContactName":"Elizabeth Lincoln","City":"Tsawassen"}],"count":91}
(1 row)


Here's how to extract the values from JSON:

create or replace function save_customer(p_customer json) returns json
as
$$
begin
    
    if not exists(
        select * from customers
        where "CustomerID" = p_customer->>'CustomerID') then

        insert into customers
        ("CustomerID","CompanyName","ContactName","ContactTitle","City",
        "Region", "PostalCode", "Country", "Phone", "Fax"
        )   
        select
            p_customer->>'CustomerID',
            p_customer->>'CompanyName',
            p_customer->>'ContactName',
            p_customer->>'ContactTitle',
            p_customer->>'City',
            '', -- Region
            '', -- PostalCode
            '', -- Country
            '', -- Phone
            ''; -- Fax
                        
    else

        update Customers set    
            "CompanyName" = p_customer->>'CompanyName',
            "ContactName" = p_customer->>'ContactName',
            "ContactTitle" = p_customer->>'ContactTitle',
            "City" = p_customer->>'City'            
        where "CustomerID" = p_customer->>'CustomerID';
        
    end if;

    return (with src as
    (
        select c."CustomerID", c."CompanyName", c."ContactTitle", c."ContactName", c."City"
        from customers c
        where c."CustomerID" = p_customer->>'CustomerID'
        order by c."CompanyName"
    )
    select row_to_json(c.*) from src c);

end;
$$ language 'plpgsql';


Now, how about our PHP web developers? What is now the use of their decades-long experience on PHP? Following code is the thinnest and fastest marshaller of data to your web app, courtesy of PostgreSQL's superb JSON functionality


Serving paged results:

<?php 
    header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With');
    header("Access-Control-Allow-Origin: *"); 

    $pageNumber = $_GET["PageNumber"];
    $pageSize = $_GET["PageSize"];
    
    $con = pg_connect("host=localhost port=5433 dbname=northwind user=postgres password=opensesame93");

    $res = pg_query_params($con, "select get_customers($1,$2)", array($pageNumber, $pageSize));
    
    if ($res) {
        $row = pg_fetch_row($res);
        echo $row[0];
    }
    else {
        echo "Error";       
    }
?>


Serving persistence requests:

<?php 

    header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With');
    header("Access-Control-Allow-Origin: *"); 
    
    $con = pg_connect("host=localhost port=5433 dbname=northwind user=postgres password=opensesame93");

    
    $data = file_get_contents("php://input");   
    
    /*  
    $getData = json_decode($data);  
    $CompanyName = $getData->CompanyName;
    */
        
    $res = pg_query_params($con, "select save_customer($1)", array($data));
        
    if ($res) {
        $row = pg_fetch_row($res);
        echo $row[0];
    }
    else {
        echo "Error";       
    }
  
?>


For our web designer bretheren, they would be delighted that there's no more server-side languages that gets in the way between them and their beloved HTML and CSS, they could change the tags and css to their heart's content as long they keep in mind some basic HTML principles like ng-repeat and moustache tags. With AngularJS, web designer could now work in parallel and independently from web developers.


Customers.html
<!DOCTYPE html>
<html ng-app="sampleApp">
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <link rel="stylesheet" href="angularjs-twitter-bootstrap/assets/bootstrap.css"/>

    <script src="angularjs/angular.min.js"></script>

    <script src="angularjs-twitter-bootstrap/ui-bootstrap-0.6.0.min.js"></script>

    <script src="app.js"></script>

    <title></title>
</head>
<body ng-controller="CustomersCtrl">

<p>PostgreSQL loved by DBAs and Application Developers alike</p>


<div class="form-inline">
    <div class="btn-group">
    <button type="button" class="btn btn-default btn-sm" ng-click="prevPage()" ng-disabled="!(pageNumber > 1)">Previous</button>
        <button type="button" class="btn btn-default btn-sm"  ng-click="nextPage()" ng-disabled="isLastPage()">Next</button>
    </div>

    <input type="number" ng-model="pageNumber" ng-change="getCustomers()" style="width:50px">
    <select ng-model="pageSize" ng-options="opt for opt in pageSizeOptions" ng-change="getCustomers()"></select>
</div>


<p></p>


<div class="panel panel-default">
    <div class="panel-heading">Customers: {{customers.count}}</div>
    <table class="table">
        <thead>
            <th>
                <i class="icon-large icon-plus-sign" style="width: 20px"></i>
            </th>
            <th>Company</th>
            <th>Contact Title</th>
            <th>Contact</th>
            <th>City</th>
        </thead>

        <tbody>
            <tr ng-repeat="c in customers.list">
                <td ng-click="showForm(c)"
                    style="cursor: pointer;">
                    <i  class="icon-large icon-pencil"
                        style="width: 20px">
                     </i>
                </td>
                <td>{{c.CompanyName}}</td>
                <td>{{c.ContactTitle}}</td>
                <td>{{c.ContactName}}</td>
                <td>{{c.City}}</td>
            </tr>
        </tbody>

    </table>
</div>


</body>

</html>


CustomersForm.html
<div class="modal-header">
    <h3>{{Customer.CustomerID}}</h3>
</div>
<div class="modal-body">

    <div class="form-inline">
    Company: <input type="text" ng-model="Customer.CompanyName">
    </div>


</div>
<div class="modal-footer">
    <button class="btn btn-primary" ng-click="ok()">OK</button>
    <button class="btn btn-warning" ng-click="cancel()">Cancel</button>
</div>


Here's the AngularJS code, the glue framework. This is now where the battle-scarred web application developers need to turn their discipline to:

var theApp = angular.module('sampleApp', ['ui.bootstrap']);


theApp
    .controller('CustomersCtrl',['$scope','$http', '$modal', function ($scope, $http, $modal) {

        $scope.customers = { count: 0 };
        $scope.pageNumber = 1;
        $scope.pageSize = 10;
        $scope.pageSizeOptions = [5, 10, 15, 20, 25];


        $scope.pageNumber = 1;



        $scope.getCustomers = function() {
            var pageNumber = $scope.pageNumber;
            var pageSize = $scope.pageSize;

            $http({
                url: 'http://192.168.254.100/getCustomers.php',
                method: 'GET',
                params: { PageNumber: pageNumber, PageSize : pageSize }
            }).success(function (data, status, headers, config) {
                    $scope.customers = data;
            }).error(function(data, status, headers, config) {
                    $scope.message = "error";
            });

        }



        $scope.showForm = function(customer) {
            var modalInstance = $modal.open({
                templateUrl: 'CustomersForm.html',
                controller: 'CustomersFormCtrl',
                resolve: {
                    Customer: function () {
                        return angular.copy(customer);
                    },
                    refresher : function() {
                        return $scope.refresh;
                    }
                }
            });
        };

        $scope.refresh = function() {
            $scope.getCustomers();
        };


        $scope.nextPage = function() {
            ++$scope.pageNumber;
            $scope.getCustomers();
        };

        $scope.prevPage = function() {
            if ($scope.pageNumber == 1) return;
            --$scope.pageNumber;
            $scope.getCustomers();
        };

        $scope.isLastPage = function() {
            return Math.ceil($scope.customers.count / $scope.pageSize) == $scope.pageNumber;
        };



        $scope.getCustomers();


    }])

    .controller('CustomersFormCtrl', ['$scope','$http', '$modalInstance', 'Customer', 'refresher',
        function ($scope, $http, $modalInstance, Customer, refresher) {

        $scope.Customer = Customer;


        $scope.ok = function () {

            $http({
                url: 'http://192.168.254.100/saveCustomer.php',
                method: 'POST',
                data: $scope.Customer,
                headers: {'Content-Type': "application/json"}
            }).success(function (data, status, headers, config) {

                $modalInstance.dismiss('ok');
                refresher();

            }).error(function(data, status, headers, config) {

                // called asynchronously if an error occurs
                // or server returns response with an error status.

                $scope.message = "error";
            });


        };

        $scope.cancel = function () {
            $modalInstance.dismiss('cancel');
        };

    }]);


* The humor of the phrase is not lost on me. The phrase was alluded to what the dolphin said on Hitchhiker's Guide To The Galaxy. Dolphin, being a MySQL mascot, could have titled this blog post as "So long, and thanks for all the dolphins" lol



Happy Coding! ツ

Wednesday, October 2, 2013

Access-Control-Allow-Headers error

Should not be done separately:

header('Access-Control-Allow-Headers: Content-Type, application/json');
header('Access-Control-Allow-Headers: X-Requested-With'); 


All access should be included on one setting:
header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With');