Thursday, October 27, 2016

PostgreSQL - GROUP LIMIT

Salam semua,

Bersawang dah blog ni... lama gila x menulis.

nak share bende baru bagi aku. sebelum ni tak pernah guna advance(sebab aku reti guna select,insert,update,delete shj) SQL.

Pengunaan nya, aku nak group kan mengikut column dan nak limit 3 result teratas sahaja utk setiap grouping.

lepas tanya sifu postgres(Helmi), dia suggest pakai menatang ni.

WITH table_name as (
 ..... complicated queries with join & condition etc......
)  

SELECT
  * 
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS r,
    t.*
  FROM
    table_name t) x
WHERE
  x.r <= 3;

As postgres support WITH, so aku prefer asingkan main complicated queries dgn GROUP LIMIT kan. Semua select/join and where condition buat dlm WITH.

Enjoy.

Sunday, August 17, 2014

CORS - Cross Origin Resources Sharing - Isu

JSON FTW... yeay.... everything is JSON... membuatkan hidup anda gembira... hahahhahahhaa.... tapi hilang kegembiraan itu bila error x leh guna JSON yg dah di kongsikan.... hahahahhaha....

Isu yang selalu timbul... xmlhttprequest gagal sebab cross domain request oleh javascript anda... tension kan?... hahhaha

Solusi yg saya pakai adalah... create satu simple server side scipt (perl/php) untuk jalankan tugas ini utk anda. dlm kes ni saya tunjukkan cth mudah mengunakan PHP.

PHP:


$qry_str = $_POST["json"];
if(!$qry_str){
    $qry_str = $_GET["json"];
}

$ch = curl_init();

header("Content-type: application/json");

$http_origin = $_SERVER['HTTP_ORIGIN'];
if(!$http_origin){$http_origin = $_SERVER['HTTP_REFERER'];}

//replace 1234|abcd|... with ur regex origin domain 
if (preg_match('/(1234|abcd)/i', $http_origin)){  
    header("Access-Control-Allow-Origin: $http_origin");n
}

// Set query data here with the URL
curl_setopt($ch, CURLOPT_URL, $qry_str); 

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, '10');
$content = trim(curl_exec($ch));

curl_close($ch);

if($content){
    if ($GET['callback'] != ''){
        print $GET['callback']."( $content )";
    }else{
        $json = json_decode($content);
        
        if(json_last_error() == JSON_ERROR_NONE){
            print $content;
        }else{
            print '{"status": "ERROR","errormsg" : "Invalid Content"}';
        }
        exit; 
    }
    exit;
    
}else{
    print '{"status": "ERROR", "errormsg" : "Empty '.$_GET["json"].'"}';
    exit;
}


kita namakan nya 'json.php'

di javascript kita pula, saya bg cth mengunakan angularjs $http method.

var jsonurl = "http://abc.com/capaian/ke/file/json.json";
        
$http({
    method: 'GET', 
    url: '/json.php?json=' + jsonurl 
}).
success(function(data, status, headers, config) {
    //cek if status == ERROR utk kenalpasti jika ada masalah dgn data anda.
    $scope.prosesdataanda(data)
   
}).
error(function(data, status, headers, config) {
    //hati-hati disini... error bila ada masalah di 'json.php' bukan sumber json anda.
    console.log(data, status);
});

Setel

NGINX - POST request return error

Mengahadapi masalah lagi dgn NGINX. kali ini bila nak post data dari javascript mengunakan xmlhttprequest.

mula2 ingat disebabkan oleh CORS (cross origin resource sharing) isu... puas la godek NGINX sampai lebam.... *penangan x reti nak tgk error log ler ni...

bila terpikir tgk error log.. baru perasan error dia mcm ni:

2014/08/15 10:52:02 [crit] 33772#0: *1 open() "/usr/local/var/run/nginx/client_body_temp/0000000001" failed (13: Permission denied), client: 127.0.0.1, server: hairul, request: "POST /*****/***/json.php/***.geojson HTTP/1.1", host: "hairul:8080", referrer: "http://hairul/******/***/"

bila google terjumpa satu artikel yang menerangkan isu apabila folder 'client_body_temp' dibuat oleh user pertama yang execute, hanya boleh di baca/gunakan oleh user pertama sahaja.... user seterusnya tidak boleh mengunakan folder ini lagi...

Solusinya, define 'client_body_temp_path' untuk setiap virtual host kita.

client_body_temp_path /usr/local/var/run/nginx/client_temp 1 2;

Setel

Friday, July 25, 2014

NGINX - Isu Berkaitan Path/URL

Orang dah beranak pinak, aku baru nak mengurat... hahahha perumpamaan aku dgn NGINX.

sebelum ni aku pakai apache httpd sahaja untuk pembangunan dkt local machine. sblm tu lg aku pakai sambar server. cuma lately ni baru berjinak2 dgn NGINX bila ada keperluan.

Isunya, php script tak jalan bila kita passkan url style REST ... cth: index.php/id/file/lain.

puas godek... almklum la org baru nak bercinta... jumpa gak penyelesaiannya (mungkin bagi anda bende kecik.. tapi besar sungguh masalah ini pada saya... noob la katakan..)

nginx.conf:

location ~ [^/]\.php(/|$) {
    fastcgi_split_path_info ^((?U).+\.php)(.*)$;

    try_files $fastcgi_script_name =404;

    set $path_info $fastcgi_path_info;
    fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    fastcgi_param PATH_INFO $path_info;
    fastcgi_param PATH_TRANSLATED $document_root$path_info;
    include fastcgi_params;
    
    fastcgi_pass   127.0.0.1:9000;
    fastcgi_index index.php;
}

Setel...

Saturday, June 7, 2014

Google Directions Points Decode

https://maps.googleapis.com/maps/api/directions/json?.....

Google Direction API membantu kita untuk mendapatkan laluan dari point A ke point B.

Kalau kita baca balik JSON(prefered) data yang google return, google akan bagi steps start & end location point. dimana kalau kita translate terus diatas peta akan nampak cacat sedikit sebab polyline tak ikut exactly atas jalan.

Goole ada sertakan detail point diantara start & end point, melalui atribute routes->legs->steps[$_]->polyline->points

Cuma, 'cuma' ia telah di encode kan supaya size data nya tidak besar. saya agak sahaja. sebab google ada bagi tips/cara yg digunakan untuk encode points ni. cuma dia tak bagi complete function kat kita.... hampeh kan?... hehehhe

Saya nak buat sendiri x reti... bila cari kat internet diaorg kata dah ada org buat guna javascript tapi saya cari x jumpa... link broken... yang saya jumpa mamat ni dah convert ker PHP code. dia translate dr javascript... saya plak nak kan yg javascript... bila cari tak jumpa... saya translate balik ke javascript.... hahahhaa.... . may be ada mistake.. tapi saya test ok jer... hehehhe

ini code nya dlm javascript function

/*
 * Copyright (c) 2008 Peter Chng, http://unitstep.net/
 * 
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the
 * "Software"), to deal in the Software without restriction, including
 * without limitation the rights to use, copy, modify, merge, publish,
 * distribute, sublicense, and/or sell copies of the Software, and to
 * permit persons to whom the Software is furnished to do so, subject to
 * the following conditions:
 * 
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
 * LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
 * OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
 * WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */

/*
 * convert to javascipt by hairul@mbek@ayun, http://blog.mbek.net 
*/

function decodePolylineToArray(encoded){
  var length = encoded.length;
  var index = 0;
  var points = [];
  var lat = 0;
  var lng = 0;

  while (index < length)
  {
    // Temporary variable to hold each ASCII byte.
    var b = 0;

    // The encoded polyline consists of a latitude value followed by a
    // longitude value.  They should always come in pairs.  Read the
    // latitude value first.
    var shift = 0;
    var result = 0;
    do
    {
      // The `ord(substr(encoded, $index++))` statement returns the ASCII
      //  code for the character at $index.  Subtract 63 to get the original
      // value. (63 was added to ensure proper ASCII characters are displayed
      // in the encoded polyline string, which is `human` readable)
      //b = ord(substr(encoded, index++)) - 63;
      b = encoded.charCodeAt(index++) - 63;

      // AND the bits of the byte with 0x1f to get the original 5-bit `chunk.
      // Then left shift the bits by the required amount, which increases
      // by 5 bits each time.
      // OR the value into $results, which sums up the individual 5-bit chunks
      // into the original value.  Since the 5-bit chunks were reversed in
      // order during encoding, reading them in this way ensures proper
      // summation.
      result |= (b & 0x1f) << shift;
      shift += 5;
    }
    // Continue while the read byte is >= 0x20 since the last `chunk`
    // was not OR'd with 0x20 during the conversion process. (Signals the end)
    while (b >= 0x20);

    // Check if negative, and convert. (All negative values have the last bit
    // set)
    dlat = ((result & 1) ? ~(result >> 1) : (result >> 1));

    // Compute actual latitude since value is offset from previous value.
    lat += dlat;

    // The next values will correspond to the longitude for this point.
    shift = 0;
    result = 0;
    do
    {
      //b = ord(substr(encoded, index++)) - 63;
      b = encoded.charCodeAt(index++) - 63;
      result |= (b & 0x1f) << shift;
      shift += 5;
    }
    while (b >= 0x20);

    dlng = ((result & 1) ? ~(result >> 1) : (result >> 1));
    lng += dlng;

    // The actual latitude and longitude values were multiplied by
    // 1e5 before encoding so that they could be converted to a 32-bit
    // integer representation. (With a decimal accuracy of 5 places)
    // Convert back to original values.
    points.push([lat * 1e-5, lng * 1e-5]);
  }

  return points;
}

Wednesday, February 19, 2014

Apache Cordova

Salam,

sudah lama saya ingin involve dlm mobile application development. tapi java & C# merupakan bahasa alien yg saya tak tercapai2 lagi nak menguasainya... hehehehhehehe

Tahun lepas saya ada cuba phonegap, setelah membaca kebolehan kita develop mobile apps pakai html, css & javascript... excited... install.... dan..... gagal utk dpt run kan hatta sample apps sekalipun... hahahha.... saya ngaku kalah.... saya tinggalkan phonegap dgn perasaan sayu...

dan awal bulan ni.... ada kawan call tanya boleh buat android apps tak.... saya dgn nada sedih ckp tak reti nak buat.... sedih lagi.... tapi saya cuba balik bertanyakan pakcik google manatau phonegap ada ciri2 baru yg lebih memudahkan..... tetiba... terjumpa la CORDOVA (under apache foundation).

Cordova adalah phonegap yg di edarkan secara percuma dibawah apache foundation. dgn cordova (aku terus x pandang/ambik tahu pasal phonegap) ia membenarkan apps kita di compile sendiri tidak seperti mula2 phonegap di lancarkan dimana perlu hantar apps kita untuk dicompile di server phonegap.

Walaupun byk lagi yg aku perlu belajar, tapi cordova membuatkan hati aku berbunga2 kembali... heheheehe..

Setakat ini aku dah buat beberapa simple apps,

  1. launcher web app supaya nampak mcm native apps dlm IOS.
  2. apps mcm instagram & foursquare di combine untuk mengesan lokasi & geocoding sesuatu gambar yg di ambil. beserta sedikit maklumat seperti tajuk & note. yg ni pakai elasticsearch utk query laju.
  3. POC utk satu agensi... yg ni complicated apps skit involve geocoding, photo/image, mobile printing, and client server comunication.

Banyak lagi nak kene belajar.... tapi menyeronokkan...

Wednesday, January 29, 2014

Mojolicious

Salam,

Saya mmg tgh mencari pekerjaan tetap... hehehehe... kalau ada yg nak offer kerja boleh la... terima kasih didahulukan.

Ada hantar resume kat satu company ni... dia dah reply, tetapi kene buat satu assessment test. syarat nya kene pakai Mojolicious (http://mojolicio.us/) framework.

Pertama kali install and bermain dgn mojo ni... slow skit sebab nak pahamkan flow dia etc... as sebelum ni hampir kesemua development pakai framework sendiri. bila nak study framework org ni ambik masa skit.

Assessment dah hantar tak tau le mcmana result... sebab basic sgt buatnya... sebab x abis xplore mojo ni... byk kene study lg...

ada sesiapa biasa pakai? bleh la share skit....