Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

Get CodePoint-Property Pair from Scripts.txt on Unicode.org

charset PowerShell Unicode

Context

You want to make a list of pair of unicode codepoint and its character property, like below:

00009,Cc
00020,Zs
00021,Po
00024,Sc
...

Solution with PowerShell

You can make the list from ftp://ftp.unicode.org/Public/UNIDATA/PropList.txt ftp://ftp.unicode.org/Public/UNIDATA/Scripts.txt with PowerShell:

Get-Content ./Scripts.txt | `
  Where-Object { ($_ -notlike "#*") -and ($_ -notlike "") } | `
  ForEach-Object {
    $_ -match '(?<CodePoint>[0-9A-F]+(\.\.[0-9A-F]+)?)\s+;\s+(\w+) # (?<PatternName>\w+)' > $null;
    $CodePoint = $Matches.CodePoint;
    $PatternName = $Matches.PatternName;
    if ($CodePoint -like '*..*') {
      $StartCodePoint = [Convert]::ToInt32(($CodePoint -split "\.\.")[0], 16);
      $EndCodePoint = [Convert]::ToInt32(($CodePoint -split "\.\.")[1], 16);
      $StartCodePoint..$EndCodePoint | ForEach-Object {
        $_.ToString('X5') + ',' + $PatternName
      }
    } else {
      [Convert]::ToInt32($CodePoint, 16).ToString('X5') + ',' + $PatternName
    }
  } | `
  Sort-Object | Get-Unique | Out-File DetailedPropList.txt -Encoding utf8

Ordinaly SELECT clause returns ORA-00904 error on Oracle in SQL Fiddle

SQL

Context

The "Text to DDL" feature on SQL Fiddle generates DDL like below for Oracle 11g R2.

CREATE TABLE Table1
    ("value1" int)
;

INSERT ALL 
    INTO Table1 ("value1")
         VALUES (1)
SELECT * FROM dual
;

Problem

This ordinal SELECT clasuse returns ORA-00904: "VALUE1": invalid identifier eror on this table.

select value1 from table1;

Reason

This is caused by design of Oracle, and "Text to DDL" feature on SQL Fiddle.

"Text to DDL" feature on SQL Fiddle always quote column name. In Oracle, quoted column names are treated as case sensitive name, and on the other hand, unquoted column names are treated as capitalized names.

It means CREATE TABLE Table1 ("value1" int); creates a table with a column named value1 (case sensitive), and select value1 from table1; searchs for a column named VALUE1.

Solution

Remove quotes from DDL like:

CREATE TABLE Table1 (value1 int);
INSERT INTO Table1 (value1) VALUES (1);
select value1 from table1;

Or, add quotes to DML like:

CREATE TABLE Table1 ("value1" int);
INSERT INTO Table1 ("value1") VALUES (1);
select "value1" from table1;

Return value of comparison operator in SELECT clause

SQL

Background

Some RDBMS can contain the result of comparison operator in SELECT clause. However, the returned values are different between RDBMS.

Result

MySQL

I used MySQL 5.6 on SQL Fiddle.

DDL (MySQL)

CREATE TABLE Table1 (`value1` int, `value2` int, `value3` int);

INSERT INTO Table1 (`value1`, `value2`, `value3`)
VALUES             (1, 2, 3),
                   (2, 4, 6),
                   (3, 6, 9);

DML (MySQL)

select value1, value2, value3, value1=1, value2=4, value3=9 from table1;

Result (MySQL)

| value1 | value2 | value3 | value1=1 | value2=4 | value3=9 |
|--------|--------|--------|----------|----------|----------|
|      1 |      2 |      3 |        1 |        0 |        0 |
|      2 |      4 |      6 |        0 |        1 |        0 |
|      3 |      6 |      9 |        0 |        0 |        1 |

PostgreSQL

I used PostgreSQL 9.6 on SQL Fiddle.

DDL (PostgreSQL)

CREATE TABLE Table1 ("value1" int, "value2" int, "value3" int);

INSERT INTO Table1 ("value1", "value2", "value3")
VALUES             (1, 2, 3),
                   (2, 4, 6),
                   (3, 6, 9);

DML (PostgreSQL)

select value1, value2, value3, value1=1, value2=4, value3=9 from table1;

Result (PostgreSQL)

| value1 | value2 | value3 | ?column? | ?column? | ?column? |
|--------|--------|--------|----------|----------|----------|
|      1 |      2 |      3 |     true |    false |    false |
|      2 |      4 |      6 |    false |     true |    false |
|      3 |      6 |      9 |    false |    false |     true |

SQL Server 2014

I used SQL SQL Server 2014 on SQL Fiddle.

DDL (SQL Server)

CREATE TABLE Table1
    ("value1" int, "value2" int, "value3" int)
;
    
INSERT INTO Table1 ("value1", "value2", "value3") VALUES (1, 2, 3);
INSERT INTO Table1 ("value1", "value2", "value3") VALUES (2, 4, 6);
INSERT INTO Table1 ("value1", "value2", "value3") VALUES (3, 6, 9);

DML (SQL Server)

Note that value2=4 returns always 4. It doesn't cause any error, but it must be unexpected result. You have to use case when instead of = operator.

select
  value1, value2, value3, 
  case when value1 = 1 then 1 else 0 end,
  case when value2 = 4 then 1 else 0 end,
  case when value3 = 9 then 1 else 0 end
from table1;

Result (SQL Server)

| value1 | value2 | value3 |   |   |   |
|--------|--------|--------|---|---|---|
|      1 |      2 |      3 | 1 | 0 | 0 |
|      2 |      4 |      6 | 0 | 1 | 0 |
|      3 |      6 |      9 | 0 | 0 | 1 |

Oracle

I used Oracle 11g R2 on SQL Fiddle.

DDL (Oracle)

CREATE TABLE Table1
    (value1 int, value2 int, value3 int)
;
    
INSERT INTO Table1 (value1, value2, value3) VALUES (1, 2, 3);
INSERT INTO Table1 (value1, value2, value3) VALUES (2, 4, 6);
INSERT INTO Table1 (value1, value2, value3) VALUES (3, 6, 9);

DML (Oracle)

Note that value2=4 in SELECT clause causes "ORA-00923: FROM keyword not found where expected" error. You have to use case when instead of = operator.

select
  value1, value2, value3,
  case when value1 = 1 then 1 else 0 end,
  case when value2 = 4 then 1 else 0 end,
  case when value3 = 9 then 1 else 0 end
from table1;

Result (Oracle)

| VALUE1 | VALUE2 | VALUE3 | CASEWHENVALUE1=1THEN1ELSE0END | CASEWHENVALUE2=4THEN1ELSE0END | CASEWHENVALUE3=9THEN1ELSE0END |
|--------|--------|--------|-------------------------------|-------------------------------|-------------------------------|
|      1 |      2 |      3 |                             1 |                             0 |                             0 |
|      2 |      4 |      6 |                             0 |                             1 |                             0 |
|      3 |      6 |      9 |                             0 |                             0 |                             1 |

Convert deeply nested hash or array to JSON with ConvertTo-Json

PowerShell

Context

You can read a JSON file like below with ConvertFrom-Json, and write with ConvertTo-Json properly.

PS > Get-Content ./foo.json
{
  "outerHash": {
    "innerHash": {
      "key": "value"
    }
  }
}
PS > Get-Content ./foo.json | ConvertFrom-Json | ConvertTo-Json
{
  "outerHash": {
    "innerHash": {
      "key": "value"
    }
  }
}

Problem

You also can read a JSON file like below with ConvertFrom-Json, but cannot write with ConvertTo-Json properly. ConvertTo-Json write a hash in array in hash as "@{key=value}" style string.

PS > Get-Content ./bar.json
{
  "outerHash": {
    "arrayInHash": [
      {
        "key": "value"
      }
    ]
  }
}
PS > Get-Content ./bar.json | ConvertFrom-Json | ConvertTo-Json
{
  "outerHash": {
    "arrayInHash": [
      "@{key=value}"
    ]
  }
}

In this example, the result of ConvertTo-Json should be identical the result of Get-Content ./bar.json.

Reason

ConvertTo-Json has a option -Depth, and its default value is 2. This option is used when convert nested object to JSON.

Solution

Pass the appropriate value to -Depth option according to your object graph.

PS > Get-Content ./bar.json | ConvertFrom-Json | ConvertTo-Json -Depth 3
{
  "outerHash": {
    "arrayInHash": [
      {
        "key": "value"
      }
    ]
  }
}

Convert an array to CSV with PowerShell

PowerShell

Problem

You cannot convert an array with just pipeline the array to ConvertTo-Csv.

PS > $array = ("a", "b", "c", "a", "d")
PS > $array | ConvertTo-Csv
"Length"
"1"
"1"
"1"
"1"
"1"

... or just passing the array to ConvertTo-Csv.

PS > ConvertTo-Csv $array
"Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized","Count"
"5","5","1","System.Object[]","False","True","False","5"

An array in object also will produce undesired result.

PS > $obj = New-Object PSObject
PS > $obj | Add-Member -MemberType NoteProperty -Name "CSV" -Value $array
PS > $obj | ConvertTo-Csv
"CSV"
"System.Object[]"
PS > ConvertTo-Csv $obj
"CSV"
"System.Object[]"

Reason

It seems by design.

Solution

You should use Add-Member with an loop and dummy property names.

PS > $obj = New-Object PSObject
PS > for ($i=0; $i -lt $array.Length; $i++) { $obj | Add-Member -MemberType NoteProperty -Name $i -Value $array[$i] }
PS > $obj | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1
"a","b","c","a","d"