albert's blog

2018/05/31

2018/05/30

2018/05/29

2018/05/15

[PostgreSQL] window function: row_number() over (partition by col order by col)

Here has a query SQL statement and its result:

select p.identifier, i.value 
from project p, intent i
where identifier = 'qa_chat' and p.id = i.project_id
order by i.id desc
limit 10

We can find out the first column of the preceding picture has duplicate values (i.e. qa_chat), if I would like to leave blank when the value of first column is equal to preceding record:

We can use window function to fulfill this requirement, the SQL statement will look like:

with source as (
    select row_number() over(partition by p.identifier order by i.id desc) as row_number, 
           p.identifier, i.value 
    from project p, intent i
    where identifier = 'qa_chat' and p.id = i.project_id
    limit 10
)
select case when row_number = 1 then identifier else '' end as identifier, value
from source

Reference
[1] https://www.postgresql.org/docs/9.3/static/functions-window.html

2018/05/14

[Neo4j] Fail to start Neo4j Server

Problem
As I try to startup neo4j server, I get this error message:

F:\software\neo4j-community-3.3.3\bin>neo4j start
Invoke-Neo4j : 找不到任何服務名稱為 'neo4j' 的服務。
位於 線路:1 字元:213
+ ... community-3.3.3\bin\Neo4j-Management.psd1'; Exit (Invoke-Neo4j start)
+                                                       ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Invoke-Neo4j

How-To
Try to uninstall service, install service and start again

F:\software\neo4j-community-3.3.3\bin>neo4j uninstall-service
Neo4j uninstalled

F:\software\neo4j-community-3.3.3\bin>neo4j install-service
Neo4j service installed

F:\software\neo4j-community-3.3.3\bin>neo4j start
警告: 正在等候 'Neo4j Graph Database - neo4j (neo4j)' 服務啟動...
Neo4j windows service started

Referance
[1] https://github.com/neo4j/neo4j/issues/9257

2018/05/13

[PostgreSQL] How to get the first record in SELECT statement

Problem
I will get multiple record from this select statement:

  select * 
  from sentence 
  where intent_id in (
      select id 
      from intent 
      where project_id=5
  )

I only want the first record, how to do it?

How-To
Add LIMIT 1 at the end of the select statement:

  select * 
  from sentence 
  where intent_id in (
      select id 
      from intent 
      where project_id=5
  )
  limit 1

2018/05/12

[snakeyaml] How to get rid of single quote in YAML when value is true / false?

Problem
The expected YAML is:

  intents:
  - greet
  - inform
  - others
  - question
  entities:
  - userid
  - is_account_locked
  - pass_company_check
  action_masking: true
  action_factory: remote
  actions:
  - utter_default
  - utter_greet
  - confirm:
      dependency:
        slots:
        - userid
  - unlock:
      dependency:
        slots:
        - userid
        - is_account_locked

Here has the original source code:

  package test.albert.snakeyaml;
  
  import java.util.Arrays;
  import java.util.HashMap;
  import java.util.List;
  import java.util.Map;
  
  import org.yaml.snakeyaml.DumperOptions;
  import org.yaml.snakeyaml.Yaml;
  import org.yaml.snakeyaml.constructor.Constructor;
  import org.yaml.snakeyaml.nodes.Tag;
  import org.yaml.snakeyaml.representer.Representer;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.extern.slf4j.Slf4j;
  
  @Slf4j
  public class SnakeYamlTest {
  
      public static void main(String[] args) {
          // Set DumperOptions options
          DumperOptions options = new DumperOptions();
          options.setDefaultFlowStyle(DumperOptions.FlowStyle.BLOCK);
          options.setPrettyFlow(true);
  
          // overwrite Representer
          Representer representer = new Representer();
          representer.addClassTag(ActionAttribute.class, Tag.MAP);
  
          // Create Yaml instance with Representer and DumperOptions
          Yaml yaml = new Yaml(new Constructor(), representer, options);
  
          // Prepare map data for YAML
          Map<String, Object> actionMap1 = new HashMap<>();
          actionMap1.put("confirm", new ActionAttribute(new Dependency(Arrays.asList("userid"))));
  
          Map<String, Object> actionMap2 = new HashMap<>();
          actionMap2.put("unlock", new ActionAttribute(new Dependency(Arrays.asList("userid", "is_account_locked"))));
  
          Map<String, Object> map = new HashMap<>();
          map.put("intents", Arrays.asList("greet", "inform", "others", "question"));
          map.put("entities", Arrays.asList("userid", "is_account_locked", "pass_company_check"));
          map.put("action_factory", "remote");
          map.put("actions", Arrays.asList("utter_default", "utter_greet", actionMap1, actionMap2));
          map.put("action_masking", "true");
  
          // Serialize a Java object into a YAML String
          String output = yaml.dump(map);
          log.info("\n" + output);
      }
  
      @Data
      @AllArgsConstructor
      private static class ActionAttribute {
          Dependency dependency;
      }
  
      @Data
      @AllArgsConstructor
      private static class Dependency {
          private List<String> slots;
      }
  }

But the action_masking value had been added single quote to its front and rear, how to remove single quote?

  intents:
  - greet
  - inform
  - others
  - question
  entities:
  - userid
  - is_account_locked
  - pass_company_check
  action_masking: 'true'
  action_factory: remote
  actions:
  - utter_default
  - utter_greet
  - confirm:
      dependency:
        slots:
        - userid
  - unlock:
      dependency:
        slots:
        - userid
        - is_account_locked

How-To
According to snakeyaml source code, these values will regard as boolean value and add single quote from it front and rear

Therefore, you need to create a custom resolver to overwrite its value (remove true/false)

  package test.albert.snakeyaml;
  
  import org.yaml.snakeyaml.nodes.Tag;
  import org.yaml.snakeyaml.resolver.Resolver;
  
  public class CustomResolver extends Resolver {
      protected void addImplicitResolvers() {
          addImplicitResolver(Tag.BOOL, BOOL, "yYnNoO");
      }
  }

Then create Yaml instance with this custom resolver

  package test.albert.snakeyaml;
  
  import java.util.Arrays;
  import java.util.HashMap;
  import java.util.List;
  import java.util.Map;
  
  import org.yaml.snakeyaml.DumperOptions;
  import org.yaml.snakeyaml.Yaml;
  import org.yaml.snakeyaml.constructor.Constructor;
  import org.yaml.snakeyaml.nodes.Tag;
  import org.yaml.snakeyaml.representer.Representer;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.extern.slf4j.Slf4j;
  
  @Slf4j
  public class SnakeYamlTest {
  
      public static void main(String[] args) {
          // Set DumperOptions options
          DumperOptions options = new DumperOptions();
          options.setDefaultFlowStyle(DumperOptions.FlowStyle.BLOCK);
          options.setPrettyFlow(true);
  
          // overwrite Representer
          Representer representer = new Representer();
          representer.addClassTag(ActionAttribute.class, Tag.MAP);
  
          // Create Yaml instance with Representer, DumperOptions, CustomResolver
          Yaml yaml = new Yaml(new Constructor(), representer, options, new CustomResolver());
  
          // Prepare map data for YAML
          Map<String, Object> actionMap1 = new HashMap<>();
          actionMap1.put("confirm", new ActionAttribute(new Dependency(Arrays.asList("userid"))));
  
          Map<String, Object> actionMap2 = new HashMap<>();
          actionMap2.put("unlock", new ActionAttribute(new Dependency(Arrays.asList("userid", "is_account_locked"))));
  
          Map<String, Object> map = new HashMap<>();
          map.put("intents", Arrays.asList("greet", "inform", "others", "question"));
          map.put("entities", Arrays.asList("userid", "is_account_locked", "pass_company_check"));
          map.put("action_factory", "remote");
          map.put("actions", Arrays.asList("utter_default", "utter_greet", actionMap1, actionMap2));
          map.put("action_masking", "true");
  
          // Serialize a Java object into a YAML String
          String output = yaml.dump(map);
          log.info("\n" + output);
      }
  
      @Data
      @AllArgsConstructor
      private static class ActionAttribute {
          Dependency dependency;
      }
  
      @Data
      @AllArgsConstructor
      private static class Dependency {
          private List<String> slots;
      }
  }

2018/05/11

[Angular] How to add serial number in ng-grid

Problem
If I would like to add serial number in ng-grid, how to do it?
I would like to implement in client side instead of server side.

The column configuration in ng-grid is as bellows:

  $scope.tab4.synonymCols  = [{
            field : 'value',
            displayName : 'value',
            cellClass : 'text-left',
            width : '25%'
        }, {
            field : 'synonyms',
            displayName : 'synonyms',
            cellClass : 'text-left',
            width : '70%'
        } ];

How-To
The updated column configuration in ng-grid is as bellows:

       $scope.tab4.synonymCols  = [{
            field : 'seq',
            displayName : 'no',
            cellClass : 'text-left',
            width : '5%',
            cellTemplate: '<div class="ngCellText ng-scope col1 colt1" ng-class="col.colIndex()"><span ng-cell-text="">{{row.rowIndex + 1}}</span></div>'
        }, {
            field : 'value',
            displayName : 'value',
            cellClass : 'text-left',
            width : '25%'
        }, {
            field : 'synonyms',
            displayName : 'synonyms',
            cellClass : 'text-left',
            width : '70%'
        } ];

2018/05/10

[Java] How to compute variance and standard deviation via Java

Problem
Assume I have a List of double, I would like to compute its variance and standard deviation.

How to implement it with Java?

How-To
Here has sample code:

    public static void main(String[] args) {
        List<Double> data = Arrays.asList(0.51, 0.53, 0.49, 0.74, 0.55, 0.47, 0.59, 0.47, 0.45, 0.72);
        DoubleSummaryStatistics summaryStatistics = data.stream().mapToDouble(d -> d.doubleValue()).summaryStatistics();
        Double mean = summaryStatistics.getAverage();

        Double variance = 0d;
        for (Double num : data) {
            variance += Math.pow(num - mean, 2);
        }
        variance = variance / 10;
        Double standardDeviation = Math.sqrt(variance);

        DecimalFormat df = new DecimalFormat("0.000");

        log.info("variance = " + df.format(variance));
        log.info("standardDeviation = " + df.format(standardDeviation));
    }

2018/05/09

[Microsoft Excel] 如何運用 Excel 計算出變異數 (variance) 與標準差 (standard deviation)

Problem
假設我有十筆資料

How-To
可以利用 Excel 內建的 varp 與 stdevp 計算出上述資料的變異數 (variance) 與標準差 (standard deviation)

2018/05/08

[Bootbox] How to set button focus in bootbox dialog

Problem
I am using Bootbox.js to create a custom dialog

The default behavior is focusing on the second button, i.e. EXCEL, how to I change its focus to the first button.

The code snippet is as bellows:

  $scope.doExport = function() {
     bootbox.dialog({
     message: "請選擇要匯出 JSON 或 Excel",
         title: '匯出',
         buttons: {
            json: {
                label: 'JSON',
                callback: function() {
                    $scope.exportJson();
                }
            },
            excel: {
                label: 'Excel',
                callback: function() {
                    $scope.exportExcel();
                }
            }
         }
     });
  };

How-To
You can simply add btn-primay class to the first button, the code snippet is as following:

   $scope.doExport = function() {
     bootbox.dialog({
         message: "請選擇要匯出 JSON 或 Excel",
         title: '匯出',
         buttons: {
             json: {
                 label: 'JSON',
                 className: 'btn-primary',
                 callback: function() {
                     $scope.exportJson();
                 }
             },
             excel: {
                 label: 'Excel',
                 callback: function() {
                     $scope.exportExcel();
                 }
             }
         }
     });
   };

Here has the updated dialog:

[Windows] Execute command every 10-sec in batch file

Problem
I would like to do two commands every 10 seconds via batch file in Windows:

 taskkill /IM test.exe /F
 del /F /Q C:\test\Bak\*.*

How to do it?

How-To
Here has the example:

:loop
 taskkill /IM test.exe /F
 del /F /Q C:\test\Bak\*.*
 timeout /t 10
 goto loop

2018/05/07

[Java] Use Apache Commons StringUtils.Replace Instead of String.replace

Quote

In general, the String.replace method works fine and is pretty efficient, especially if you’re using Java 9. But if your application requires a lot of replace operations and you haven’t updated to the newest Java version, it still makes sense to check for faster and more efficient alternatives.

Here has a simple experiments:

package your.simple.java;

import java.util.Calendar;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

public class StringReplacementTest {

    private String test = "test";
    private int count = 100000000;

    public static void main(String[] args) {
        new StringReplacementTest().test1();
        new StringReplacementTest().test2();
    }

    public void test1() {
        Date from = Calendar.getInstance().getTime();
        for (int i = 0; i < count; i++) {
            test.replace("test", "simple test");
        }

        Date to = Calendar.getInstance().getTime();
        long seconds = (to.getTime() - from.getTime()) / 1000;
        System.out.println("[test1] seconds = " + seconds);
    }

    public void test2() {
        Date from = Calendar.getInstance().getTime();
        for (int i = 0; i < count; i++) {
            StringUtils.replace(test, "test", "simple test");
        }

        Date to = Calendar.getInstance().getTime();
        long seconds = (to.getTime() - from.getTime()) / 1000;
        System.out.println("[test2] seconds = " + seconds);
    }

}

Execution result:

[test1] seconds = 56
[test2] seconds = 9

Reference
[1] https://dzone.com/articles/11-simple-java-performance-tuning-tips?edition=334833&utm_source=Daily%20Digest&utm_medium=email&utm_campaign=Daily%20Digest%202017-11-03

2018/05/06

[JavaScript] Utilize window.getSelection to get selected text

Problem
If I would like to get the selected text from a TextField via JavaScript, how to do it?

How-To
You can make good use of window.getSelection to fulfill this requirement.

HTML code snippet:

       <div class="row">
           <div class="col-sm-offset-1 col-sm-10">
             <div class="form-group">
               <label for="operaName" class="control-label">連續劇</label>
               <input type="text" class="form-control input-lg" id="operaName" data-ng-model="operaName" 
                      data-ng-mouseup="showOperaSelectedText()" >
             </div>
             <div class="form-group">
              <label class="control-label">起訖</label>
              <span> {{operaNameFromIndex}} ~ {{operaNameToIndex}}</span>
             </div>
             <div class="form-group">
                 <label class="control-label">選擇文字</label>
                 <span> {{operaNameSelected}}</span>
             </div>
           </div>
       </div>

JavaScript code snippet:

     $scope.operaName = "又，吳海英";
     $scope.operaNameFromIndex = 0;
     $scope.operaNameToIndex = 0;
     $scope.operaNameSelected = '';
      
     $scope.showOperaSelectedText = function() {
     // if selected
     if (window.getSelection) {
          // get selected text
          $scope.operaNameSelected = window.getSelection().toString();
          // get from index
          $scope.operaNameFromIndex = $('#operaName').prop('selectionStart');
          // get end index
         $scope.operaNameToIndex = $('#operaName').prop('selectionEnd');
         }
     }

Screenshot: