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/

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"

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

  1. Log on to the Domain Controller / certificate server that has the target Certificate Authority installed.
  2. Open the Certificate Authority MMC (run certsrv.msc).
  3. Right-click the CA name in the tree (“npgftl-FTLRNPGDC1-CA” in the example), and select All Tasks > Back up CA.
  4. On the Certification Authority Backup Wizard screen click Next
  5. 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.
  6. 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.
  7. 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.
  8. 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

Hidden Admin User On Mac

dscl . list /Users | grep -v '^_'
dscacheutil -q user | grep -A 3 -B 2 -e name:/ aRoot

echo -n 'Password: '
read -s password
sudo dscl . -create /Users/aRoot IsHidden 1
sudo dscl . -create /Users/aRoot UserShell /bin/bash
sudo dscl . -create /Users/aRoot NFSHomeDirectory /Local/Users/aRoot
sudo dscl . -create /Users/aRoot RealName 'ASB Admin'
sudo dscl . -create /Users/aRoot UniqueID 401
sudo dscl . -create /Users/aRoot PrimaryGroupID 400
sudo dscl . -passwd /Users/aRoot $password
sudo dscl . -append /groups/admin GroupMembership aRoot

dscl . -read /groups/admin GroupMembership

echo -n 'HostName: '
read hostname
sudo scutil --set HostName $hostname
sudo scutil --set LocalHostName $hostname
sudo scutil --set ComputerName $hostname

dscacheutil -flushcache
Remote PC

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

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.

Continue reading “macOS Catalina moving to zsh”