Prince PARK

February 12, 2022February 12, 2022

JSON Data in SQL Server

The write way to declare JSON column
- min NVARCHAR(4000) is required to identify as JSON DATA

CREATE TABLE BLOG
(
    blogID  INT                   IDENTITY(1,1),
    blogTitle  NVARCHAR(100),
    blogText  NVARCHAR(MAX),
    blogDetails    NVARCHAR(4000) 
    CONSTRAINT pk_blog_blodID             PRIMARY KEY(blogID),
    CONSTRAINT chk_blog_blogDetails CHECK(ISJSON(blogDetails) = 1 )
)

JSON_VALUE(<field>,<path>)

JSON_QUERY(<field>,<path>)

JSON_MODIFY(<field>,<path>,<new value>)

SELECT * FROM OPENROWSET(BULK N’C:\data\data.json’ SINGLE_CLOB) as jData
— returns a table with ‘BulkColumn’ field

OPENJSON & CROSSAPPLY

SELECT * FROM OPENROWSET(BULK N’C:\data\data.json’ SINGLE_CLOB) as jData
CROSS APPLY OPENJSON(BulkColumn)

https://bertwagner.com/posts/the-ultimate-sql-server-json-cheat-sheet/

Basics of Working with JSON in SQL Server

JSON for SQL Server. Part 1

SQL Server JSON – Part 2

Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server

Grouping Data using the OVER and PARTITION BY Functions

April 13, 2021April 13, 2021

Ultimate Calendar Table by Avi Singh

let
    /*
    ****This Calendar was created and provided by Avi Singh****
    ****This can be freely shared as long as this text comment is retained.****
    http://www.youtube.com/PowerBIPro
    www.LearnPowerBI.com by Avi Singh
    */
    #"LearnPowerBI.com by Avi Singh" = 1,
    StartDate = #date(2014, 1, 1),
    EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())) /*was "#date(2017, 1, 1)" Updated on 201802027: hard Coded End of Year caused some formulas to break, switching to dynamic date*/,
    //Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1)
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    // Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
    FiscalYearEndMonth = 6,
    #"==SET PARAMETERS ABOVE==" = 1,
    #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    // As far as Power BI is concerned, the 'Date' column is all that is needed :-) But we will continue and add a few Human-Friendly Columns
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"==Add Calendar Columns==" = #"Changed Type to Date",
    #"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
    #"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
    #"Added Calendar Year" = Table.AddColumn(#"Added Calendar Quarter", "Year", each Date.Year([Date]), Int64.Type),
    #"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
    #"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
    #"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
    #"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
    #"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
    #"Added FiscalYear" = Table.AddColumn(#"Added FiscalQuarter", "FiscalYear", each "FY" & 
Text.End(
  Text.From(
    if [MonthNum] > FiscalYearEndMonth
    then [Year] + 1
    else [Year]
  )
  , 2
)),

    #"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
    // Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
    #"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
    // Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
    #"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each /*Year Difference*/
       ( Date.Year([Date]) - Date.Year(CurrentDate) )*4
       /*Quarter Difference*/
      + Number.RoundUp(Date.Month([Date]) / 3) 
      - Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
    // Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
    #"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
    // Can be used to for example filter out all future dates
    #"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
    // FiscalYearOffset is the only Offset that is different.
    // FiscalQuarterOffset = is same as CurQuarterOffset
    // FiscalMonthOffset = is same as CurMonthOffset
    #"==Add FiscalYearOffset==" = #"Added FutureDate Flag",
    #"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
    CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
    #"Continue...Orig Table" = #"==Add FiscalYearOffset==",
    #"Added CurFiscalYearOffset" = Table.AddColumn(#"Continue...Orig Table", "CurFiscalYearOffset", each Number.From(Text.Range([FiscalYear],2,2)) - 
Number.From(Text.Range(CurrentFiscalYear,2,2))
/*Extract the numerical portion, e.g. FY18 = 18*/),
    #"==Add General Columns==" = #"Added CurFiscalYearOffset",
    // Used as 'Sort by Column' for MonthYear columns
    #"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
    #"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
    #"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
    #"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
    #"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
    #"Added WeekdayWeekend" = Table.AddColumn(#"Added Weekday Name", "WeekdayWeekend", each if [WeekdayNum] = 0 or [WeekdayNum] = 6
then "Weekend"
else "Weekday"),
    #"==Improve Ultimate Table" = #"Added WeekdayWeekend",
    #"----Add WeekSequenceNum----" = #"==Improve Ultimate Table",
    #"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"----Add WeekSequenceNum----", each ([WeekdayNum] = 0)),
    #"Added Index WeekSequenceNum" = Table.AddIndexColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekSequenceNum", 2, 1),
    #"Merged Queries Ultimate Table to WeekSequenceNum" = Table.NestedJoin(#"==Improve Ultimate Table",{"Date"},#"Added Index WeekSequenceNum",{"Date"},"Added Index WeekNum",JoinKind.LeftOuter),
    #"Expanded Added Index WeekNum" = Table.ExpandTableColumn(#"Merged Queries Ultimate Table to WeekSequenceNum", "Added Index WeekNum", {"WeekSequenceNum"}, {"WeekSequenceNum"}),
    // somehow it ends up being unsorted after Expand Column, should not matter for the end table, but makes it harder to debug and check everything is correct. Thus sorting it.
    #"ReSorted Rows by Date" = Table.Sort(#"Expanded Added Index WeekNum",{{"Date", Order.Ascending}}),
    #"Filled Down WeekSequenceNum" = Table.FillDown(#"ReSorted Rows by Date",{"WeekSequenceNum"}),
    #"Replaced Value WeekSequenceNum null with 1" = Table.ReplaceValue(#"Filled Down WeekSequenceNum",null,1,Replacer.ReplaceValue,{"WeekSequenceNum"}),
    #"Inserted Start of Week (WeekDate)" = Table.AddColumn(#"Replaced Value WeekSequenceNum null with 1", "WeekDate", each Date.StartOfWeek([Date]), type date),
    // Added 2019-Oct
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Start of Week (WeekDate)", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"----WeekSequenceNum Complete----" = #"Inserted Week of Year",
    Current_WeekSequenceNum = #"----WeekSequenceNum Complete----"{[Date = CurrentDate]}?[WeekSequenceNum],
    #"Added Custom CurWeekOffset" = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type),
    // Adding a DayofYear 1 to 365
    // And YTD, QTD, MTD Columns (can help with showing YTD Numbers across multiple years)
    #"==Updates 2019-Feb DayofYear and YTD QTD MTD Columns" = #"Added Custom CurWeekOffset",
    // This maybe useful in some DAX Calculations
    #"Inserted Day of Year" = Table.AddColumn(#"==Updates 2019-Feb DayofYear and YTD QTD MTD Columns", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Added Flag_YTD" = Table.AddColumn(#"Inserted Day of Year", "Flag_YTD", each if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate)
 then "YTD"
 else null),
    #"Added Flag_MTD" = Table.AddColumn(#"Added Flag_YTD", "Flag_MTD", each if Date.Day([Date]) <= Date.Day(CurrentDate)
 then "MTD"
 else null),
    #"Added Flag_QTD" = Table.AddColumn(#"Added Flag_MTD", "Flag_QTD", each //Compare Month Number in Quarter (1,2,3) for [Date] and CurrentDate
if Number.Mod(Date.Month([Date])-1, 3) + 1
<= Number.Mod(Date.Month(CurrentDate)-1, 3) + 1
then "QTD"
else null),
    #"==Update 2019-Mar CurrentDatOffset" = #"Added Flag_QTD",
    #"Added CurrentDayOffset" = Table.AddColumn(#"==Update 2019-Mar CurrentDatOffset", "CurrentDayOffset", each [Date] - CurrentDate),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added CurrentDayOffset",{{"CurrentDayOffset", Int64.Type}})
in
    #"Changed Type1"

February 15, 2021February 15, 2021

Generate Domain CA validated SSL for PowerSchool Test Server

Scenario

I want to add a proper SSL certificate to my PowerSchool test server which is running inside my local domain (AD Joined) using open SSL.

Assumption

domain name : school.local
powerschool test server name : abc001 (abc001.school.local)
internal local microsoft server action as local CA : ca.school.local

Export Internal Root CA with Private Key from Microsoft Certificate

In order to validate the certificate for the PowerSchool test server within the local domain, we need to get the rootCA certificate and key

Log on to the Domain Controller / certificate server that has the target Certificate Authority installed.
Open the Certificate Authority MMC (run certsrv.msc).
Right-click the CA name in the tree (“npgftl-FTLRNPGDC1-CA” in the example), and select All Tasks > Back up CA.
On the Certification Authority Backup Wizard screen click Next
On the Items to Back Up page, select Private key and CA certificate, enter a location in which to save the file, and click Next.
On the Select, a Password page, enter a password and confirm it. This password will be required when processing and importing the key into another server.
Click Next and then Finish. When the process is complete, you will have a .p12 file (example CA_name.p12) file in the folder you specified. This file contains both the public key and private key for the certificate.
From the backup location get the rootCA.p12 file and to extract public/private key from a PKCS#12 container (Generate .key and .crt from PKCS12 file)

# PKCS#1 Private key
openssl pkcs12 -in rootCA.p12 -nocerts -out rootCA-key.pem

# Certificates
openssl pkcs12 -in rootCA.p12 -clcerts -nokeys -out rootCA-cert.pem

Generate configuration file ‘abc001.school.local.csr.cnf’

Create a CSR (Certificate Signing request) using a notepad similar as below

[req]
default_bits = 2048
prompt = no
default_md = sha256
distinguished_name = dn

[dn]
C=BH
ST=Riffa
L=Riffa
O=ABC School Bahrain
OU=PowerSchool
emailAddress=admin@abc.com
CN= abc001.school.local

Create a v3.ext file with a list of local SAN domains `v3.ext`

authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names
[alt_names]
DNS.1=abc001.school.local
DNS.2=172.168.10.160

Create a private key and certificate-signing request (CSR) for the abc001.school.local certificate

openssl req -new -sha256 -nodes -out abc001.school.local.csr 
-newkey rsa:2048 -keyout abc001.school.local.key 
-config abc001.school.local.csr.cnf

Issue a certificate via the root SSL certificate and the CSR created earlier

openssl x509 -req -in abc001.school.local.csr -CA rootCA-cert.pem 
-CAkey rootCA-key.pem -CAcreateserial -out abc001.school.local.crt 
-days 500 -sha256 -extfile v3.ext

Convert generated PKCS8 Format Key to Traditional RSA key format

PowerSchool will only accept RSA based key.

openssl rsa -in abc001.school.local.key -text 
-out abc001.school.local_rsa.key

#Traditional RSA key format
------ BEGIN RSA PRIVATE KEY-----
[...]
-----END RSA PRIVATE KEY-----

# PKCS8 Format
------ BEGIN PRIVATE KEY-----
[...]
-----END PRIVATE KEY-----

Resources

[How to Create Trusted Self-Signed SSL Certificates and Local Domains for Testing](https://medium.com/better-programming/trusted-self-signed-certificate-and-local-domains-for-testing-7c6e6e3f9548)
[How to Export Internal Root CA with Private Key from Microsoft Certificate](https://support.citrix.com/article/CTX224970)
[Create Your Own Self Signed X509 Certificate](https://www.youtube.com/watch?v=1xtBkukWiek)
[Create & sign SSL/TLS certificates with openssl](https://www.youtube.com/watch?v=7YgaZIFn7mY)

# Additional Notes
# Merge Key (pem) and Certificate (Pem) to a single file (pkcs12)
openssl pkcs12 -export 
-in my-cert.pem -inkey my-key.pem 
-out my-pfx-cer.pfx

August 11, 2020August 11, 2020

How to install NodeJs on Mac

Best way to install NodeJs is using NVM (Node Version Manager)

Installing NVM

Check bash_profile file exist

[ -f ~/.bash_profile ] && echo "File exist" || echo "File does not exist"

Apple has announced that in macOS 10.15 Catalina the default shell will be zsh. Therefore you have to rename your configuration files. .bashrc is now .zshrc and .bash_profile is now .zprofile.

The main change you will see is that the prompt character.

zsh uses the % character as the default prompt
bash uses the $ character as the default prompt

Basic syntax/commands

see all available shells in macOS Catalina => cat /etc/shells
to change to bash shell, run and relaunch Terminal => chsh -s /bin/bash
to change to bash shell, run and relaunch Terminal => chsh -s /bin/zsh

Resources

To install nvm run script, do:

curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.35.2/install.sh | bash

To verify that nvm has been installed, do:

command -v nvm

Some useful commands

% nvm list                      # list all installed node versions
% nvm install node              # "node" is an alias for the latest version
% nvm ls-remote                 # list available versions online

% nvm use node
% nvm run node --version
% nvm exec 4.2 node --version   # run any arbitrary command in a subshell with the desired version of node:

% nvm which current


% nvm current
% nvm -v

Install MacPorts

MacPorts initiative to design an easy-to-use system for compiling, installing, and upgrading either command-line, X11 or Aqua based open-source software on the Mac operating system. To Install MacPorts for your version of the Mac operating system, go to https://www.macports.org/install.php and select macOS Catalina v10.15

After installation run below command to check for the proper installation if MacPorts on your mac

sudo port selfupdate

Install Yarn using MacPorts

sudo port install yarn
yarn --version

Some information about Yarn installation

yarn stores data in:

~/.yarn
~/.yarnrc
~/.cache/yarn
~/.config/yarn

By default, these packages will be installed in ~/.config/yarn/global, and soft-linked to /opt/local/bin.

To uninstall them all:

ls -1 $HOME/.config/yarn/global/node_modules/  | xargs sudo yarn > global remove

You may then remove the directories listed above and uninstall yarn. yarn is meant to replace NPM and may cause conflicts if you use both. You may override the default global installation directory by setting > the PREFIX environment variable, e.g.

mkdir -p $HOME/.config/yarn/bin # Or wherever you want
export PREFIX=$HOME/.config/yarn/bin
export PATH=$PREFIX:$PATH

Top npm modules

npm i http-server
npm i fs-extra
npm i moment
npm i async
npm i underscore underscore.string
npm i validator
npm i csv           # npm i csv-generate csv-parse stream-transform csv-stringify
npm i axios
npm i objection     # An SQL-friendly ORM for Node.js
npm i typescript

February 21, 2020February 21, 2020

macOS Catalina moving to zsh

Apple has announced that in macOS 10.15 Catalina the default shell will be zsh.

In this series, I will document my experiences moving bash settings, configurations, and scripts over to zsh.

Posts

JSON Data in SQL Server

Ultimate Calendar Table by Avi Singh

Generate Domain CA validated SSL for PowerSchool Test Server

Export Internal Root CA with Private Key from Microsoft Certificate

Generate configuration file ‘abc001.school.local.csr.cnf’

Create a v3.ext file with a list of local SAN domains `v3.ext`

Create a private key and certificate-signing request (CSR) for the abc001.school.local certificate

Issue a certificate via the root SSL certificate and the CSR created earlier

Convert generated PKCS8 Format Key to Traditional RSA key format

Resources

Hidden Admin User On Mac

Editing Text Files with Nano

Building an App with SPFx, React, and TypeScript

SharePoint Framework (SPFx): Getting Started – Quick and Dirty

PS Enrolments Chrome Extensions

How to install NodeJs on Mac

Installing NVM

Resources

Some useful commands

Install MacPorts

Install Yarn using MacPorts

Some information about Yarn installation

Top npm modules

macOS Catalina moving to zsh

Export Internal Root CA with Private Key from Microsoft Certificate

Generate configuration file ‘abc001.school.local.csr.cnf’

Create a v3.ext file with a list of local SAN domains v3.ext

Create a private key and certificate-signing request (CSR) for the abc001.school.local certificate

Issue a certificate via the root SSL certificate and the CSR created earlier

Convert generated PKCS8 Format Key to Traditional RSA key format

Resources

Installing NVM

Resources

Some useful commands

Some information about Yarn installation

Top npm modules

Create a v3.ext file with a list of local SAN domains `v3.ext`