PHP ZCE mock test, interview preparation, daily lessons under chalk talk

Tuesday, June 7, 2016

MySQL - For each Row Print Columns/Flags with value equal to 1


One of my colleague came to me with this problem. It enjoyed solving it. Here, it is for you. Say, you are given a table like this with a given number of columns.

   id  Flag_1  Flag_2  Flag_3  Flag_4  Flag_5
------  --------  -------  --------  --------  -------
    1       1       1       1       1         1
    2       0       0       0       0         0
    3       1       1       0       1         0
    4       0       1       0       0         1
    5       1       0       1       0         1

Desired output is to have a comma separated list of "Flag Names" that are ON for each row. i.e.

   id  onFlags                             
------  ------------------------------------
    1  Flag_1,Flag_2,Flag_3,Flag_4,Flag_5  
    2                                      
    3  Flag_1,Flag_2,Flag_4,               
    4  Flag_2,Flag_5                       
    5  Flag_1,Flag_3,Flag_5   

It's not a generally required to print column names as a result of query. One easy solution is to fetch data in sql and then use some programming language to manipulate it. However, try running sql only to fetch it. I recommend to minimize the browser and try it on your own.

=============================================

SELECT
 id, CONCAT(flag1, flag2, flag3, flag4,flag5) AS onFlags
FROM
 (SELECT
   IF(Flag_1 = '1', 'Flag_1,', '') AS flag1,
   IF(Flag_2 = '1', 'Flag_2,', '') AS flag2,
   IF(Flag_3 = '1', 'Flag_3,', '') AS flag3,
   IF(Flag_4 = '1', 'Flag_4,', '') AS flag4,
   IF(Flag_5 = '1', 'Flag_5', '') AS flag5,
   id
 FROM
   Flags) AS a ;

Developing a Progressive Web App - MnLite

Introduction:
There is a lot of work that has been done in the web development in recent years but when it comes to experience on mobile it is defined by native apps. So the latest trending web technology i.e Progressive Web Apps, pushing heavily over the last few months to give a similar feel like native apps. It combines the ease of web development and gives App-like experience to the user. Icing on the cake is that it also works in offline and on slow internet connections. It uses web's newest features like service worker, App Shell, indexDB and others.

Technology Stack:
Frontend:
  • React
  • React-router
  • react-swipeable-views
  • Material-ui
  • Node-fetch (to support IOS)
Build Tools:
  • NodeJs
  • Browserify
  • Uglify-js
Web Features:
  • Service worker
  • Sw-toolbox

Service Workers:
It provides offline capabilities for the app, push notification, content caching and others. Service worker act as a proxy server between client and remote server. It is a script that runs in the background at browser independent of the app. It listens all the network requests that are in it's scope. The network requests can be intercepted and can be cached which helps it work in offline mode. It also supports push browser notifications. We worked out to run our app even without service worker for the browsers that do not support it yet. In such cases it executes plain ReactJS code in browser.

App Shell:
It is a minimal content of js and css which gets rendered until API’s responses are loaded. App shell can be a placeholder for the content that has to be loaded. With the help of service worker and its caching APIs the app shell page can be displayed offline. It removes the white screen for the user that comes before page loading and gives a better experience.

Implementing React and React Flux
From a developer's perspective if the code is written modular fashion it is easier to debug and maintain. In react we can divide the UI into components and reuse them. My advice is to make components as small as possible. Below is how we divided our screen into components.
Very less documentation on react-flux were found in starting days. React flux has three major components Dispatcher, Store, and the View (React components). API calling code is written in actions which invoke the dispatcher after getting response from server, which will update the store variables and the change will get reflected in react views for that variable. For first time user it becomes little difficult to catch how the UI will get updated when store variables gets changed in react flux. It is having a unidirectional flow which means if dispatcher is already handling some event and another request get triggered in meanwhile it won’t accept and will throw an error(‘can not dispatch elements in the middle of dispatch’) . However,to avoid this we can use waitFor().

Project Challenges:-
1. Moving to HTTPS:- Here at www.meritnation.com, we first implemented our Textbook Solutions feature with new methodology. And, in order to use service worker we needed to serve the requests in https. We created new https subdomains for serving images/js/css calls. Initially we planned to move our entire mobile site on https. However, looking at the increase in Connection Time for other pages, we wisely decided to move only relevant module to https. However, running half a site on https and rest on http has its own issues eg.
  • Cookies set on https are not passed to http. We had to remove our cookie dependent code.
  • Referer is not passed when switching from https to http. Here, meta referrer tag came to our rescue.
   <meta name="referrer" content="always">
  • Service worker essentially works on https only. Though it allows us to use “localhost” domain during development. However, in order to test our new app on mobile device we required DNS change. As changing the IP of localhost is not advisable, we needed original SSL certificate setup.
  • We were making API calls to our parent domain that runs on http. We configured our domain to serve on both http and https. To avoid CORS errors of cross origin access, we did below setting on local apache server.
  Header set Access-Control-Allow-Origin "http://localhost"

2. Server Side Rendering:- To render a page through service worker bundle.js has to imported. When a request comes to service worker it checks whether it is having defined route for that url or not. All API calls must be placed in componetDidMount method and, the same script has to be executed on both client and server side. To facilitate the server side rendering:-
  • Use “match” to match the routes to a location without rendering
  • RouterContext for rendering of route components

Client Side:
render(<Router history={history} routes={routes} />, mountNode)
Server Side:
match({ history, routes }, (error, redirectLocation, renderProps) => {
renderToString(<Router {...renderProps} />, mountNode)
})

3. Versioning System:- As we are saving the url responses on service worker, so the data served has to be updated when there is any backend or frontend change. We are using sw-toolbox for fetching the url responses and storing them in indexedDb in cacheFirst mode, so if same request comes again to service worker, it should get served from indexedDb.
  • Version for APIs: We included version number in the API url.
  • Version for bundle.js: Updating version number in bundle.js url is not sufficient as the file request with particular version number has been cached with server worker. In order to ensure that bundle.js gets updated in service worker, we need to set max-age header 0 for worker.js

<FilesMatch "\worker.js$">
<ifModule mod_headers.c>
Header set Cache-Control "max-age=0"
</ifModule>
</FilesMatch>

4. Filtering Urls in Service Worker:- Service worker intercepts all the requests that are falling in it’s scope. As we are registering worker on entire mobile domain, there are some modules whose requests should get filtered out so that they are not intercepted. We applied regex rules for particular requests that should go in service worker. Passing urls that are not  handled in react-router used to give Router Context error.

5. Implementing Swipe:- There was a requirement that data should be swipeable. To implement this we used react-swipeable-views library but there are certain issues that we faced while using it. :-
  • If number of slides increases the swiping becomes very tedious.
  • If the content in a slide has scroll width then its horizontal scroll won’t work. There is no facility to distinguish between swipe and scroll.
  • On mozilla, swiping causes auto scroll of all the slides
          
6. Issue on Safari:- To get the webservice’s response, fetch Api is used which is not currently supported in safari. Problem was fixed by using node-fetch.

7. Storage Space Limit of IndexDB:- 1/3rd of the available space of the /home directory becomes a shared pool to be used by all apps. An individual app can use up to 20% of the shared pool.

Limit of IndexDb is ~ 0.2*(Available space/3)
You can confirm this using.

window.webkitStorageInfo.queryUsageAndQuota(webkitStorageInfo.TEMPORARY,
function(used, remaining) {
 console.log("Used quota: " + used + ", remaining quota: " + remaining);
}, function(e) {
 console.log('Error', e);
} );

8.Offline Google Analytics:- We did not want to loose our GA tracking of offline visits. We queued the visits and played them back once the network becomes reachable.

9. Attempt at Caching Constructed Page in Service Worker:- In the initial phase of the project we tried to cache the complete constructed page in service worker. We were calling APIs in ComponentWillMount. And, the final page with all the DOM updations used to get cached in service worker. It has its own downsides e.g. It was not progressive any more as in it used to display white page unless all the API’s response have been received. Also, as we were running our entire frontend logic in ComponentWillMount, it used to get executed in service worker context. As service worker does not allow DOM access, It caused various “document not defined” errors.

Progressive Web Apps are a new era of web development. We are excited to learn and do more on it. Feel free to reach us in case any doubts.

Authors: Charanjeet Kaur, Prashant Singh