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! ツ