Sunday, February 28, 2021

PostgreSQL Data ModeL


A postgreSQL Data ModeL related to customer's order : 

CREATE TABLE customer (
    c_since timestamp with time zone NOT NULL,
    c_id integer NOT NULL,
    c_w_id integer NOT NULL,
    c_d_id smallint NOT NULL,
    c_payment_cnt smallint NOT NULL,
    c_delivery_cnt smallint NOT NULL,
    c_first character varying(16) NOT NULL,
    c_middle character(2) NOT NULL,
    c_last character varying(16) NOT NULL,
    c_street_1 character varying(20) NOT NULL,
    c_street_2 character varying(20) NOT NULL,
    c_city character varying(20) NOT NULL,
    c_state character(2) NOT NULL,
    c_zip character(9) NOT NULL,
    c_phone character(16) NOT NULL,
    c_credit character(2) NOT NULL,
    c_credit_lim numeric(12,2) NOT NULL,
    c_discount numeric(4,4) NOT NULL,
    c_balance numeric(12,2) NOT NULL,
    c_ytd_payment numeric(12,2) NOT NULL,
    c_data character varying(500) NOT NULL,

    CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id)
CREATE UNIQUE INDEX customer_i2 ON customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id);

CREATE TABLE district (
    d_w_id integer NOT NULL,
    d_next_o_id integer NOT NULL,
    d_id smallint NOT NULL,
    d_ytd numeric(12,2) NOT NULL,
    d_tax numeric(4,4) NOT NULL,
    d_name character varying(10) NOT NULL,
    d_street_1 character varying(20) NOT NULL,
    d_street_2 character varying(20) NOT NULL,
    d_city character varying(20) NOT NULL,
    d_state character(2) NOT NULL,
    d_zip character(9) NOT NULL,

    CONSTRAINT district_i1 PRIMARY KEY (d_w_id, d_id)

CREATE TABLE history (
    h_date timestamp with time zone NOT NULL,
    h_c_id integer,
    h_c_w_id integer NOT NULL,
    h_w_id integer NOT NULL,
    h_c_d_id smallint NOT NULL,
    h_d_id smallint NOT NULL,
    h_amount numeric(6,2) NOT NULL,
    h_data character varying(24) NOT NULL

    i_id integer NOT NULL,
    i_im_id integer NOT NULL,
    i_name character varying(24) NOT NULL,
    i_price numeric(5,2) NOT NULL,
    i_data character varying(50) NOT NULL,

    CONSTRAINT item_i1 PRIMARY KEY (i_id)

CREATE TABLE new_order (
    no_w_id integer NOT NULL,
    no_o_id integer NOT NULL,
    no_d_id smallint NOT NULL,

    CONSTRAINT new_order_i1 PRIMARY KEY (no_w_id, no_d_id, no_o_id)

CREATE TABLE order_line (
    ol_delivery_d timestamp with time zone,
    ol_o_id integer NOT NULL,
    ol_w_id integer NOT NULL,
    ol_i_id integer NOT NULL,
    ol_supply_w_id integer NOT NULL,
    ol_d_id smallint NOT NULL,
    ol_number smallint NOT NULL,
    ol_quantity smallint NOT NULL,
    ol_amount numeric(6,2),
    ol_dist_info character(24),

    CONSTRAINT order_line_i1 PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)

    o_entry_d timestamp with time zone NOT NULL,
    o_id integer NOT NULL,
    o_w_id integer NOT NULL,
    o_c_id integer NOT NULL,
    o_d_id smallint NOT NULL,
    o_carrier_id smallint,
    o_ol_cnt smallint NOT NULL,
    o_all_local smallint NOT NULL,

    CONSTRAINT orders_i1 PRIMARY KEY (o_w_id, o_d_id, o_id)
CREATE UNIQUE INDEX orders_i2 ON orders USING btree (o_w_id, o_d_id, o_c_id, o_id);

    s_i_id integer NOT NULL,
    s_w_id integer NOT NULL,
    s_ytd integer NOT NULL,
    s_quantity smallint NOT NULL,
    s_order_cnt smallint NOT NULL,
    s_remote_cnt smallint NOT NULL,
    s_dist_01 character(24) NOT NULL,
    s_dist_02 character(24) NOT NULL,
    s_dist_03 character(24) NOT NULL,
    s_dist_04 character(24) NOT NULL,
    s_dist_05 character(24) NOT NULL,
    s_dist_06 character(24) NOT NULL,
    s_dist_07 character(24) NOT NULL,
    s_dist_08 character(24) NOT NULL,
    s_dist_09 character(24) NOT NULL,
    s_dist_10 character(24) NOT NULL,
    s_data character varying(50) NOT NULL,

    CONSTRAINT stock_i1 PRIMARY KEY (s_w_id, s_i_id)

CREATE TABLE warehouse (
    w_id integer NOT NULL,
    w_name character varying(10) NOT NULL,
    w_street_1 character varying(20) NOT NULL,
    w_street_2 character varying(20) NOT NULL,
    w_city character varying(20) NOT NULL,
    w_state character(2) NOT NULL,
    w_zip character(9) NOT NULL,
    w_tax numeric(4,4) NOT NULL,
    w_ytd numeric(12,2) NOT NULL,

    CONSTRAINT warehouse_i1 PRIMARY KEY (w_id)

MS SQLS port 1433

PowersheLL commands for Microsoft SQL Server :  

Get-Service | Where-Object {$_.Name -like "*SQL*"}
gsv | where {$_.Name -like "*SQL*"}

Get-NetTCPConnection | Where-Object {$_.LocalPort -like "143*"}
Test-NetConnection -ComputerName localhost -Port 1433

Monday, February 15, 2021

Install Microsoft SQL Server with PowersheLL DSC


Install-Module -Name SqlServerDsc
Get-DscResource -Module SqlServerDsc
New-Item -Path C:\SQL2017 -ItemType Directory
$mountResult = Mount-DiskImage -ImagePath 'C:\vagrant\SQLServer2017-x64-ENU.iso' -PassThru
$volumeInfo = $mountResult | Get-Volume
echo $volumeInfo
$driveInfo = Get-PSDrive -Name $volumeInfo.DriveLetter
echo $driveInfo
Copy-Item -Path ( Join-Path -Path $driveInfo.Root -ChildPath '*' ) -Destination C:\SQL2017\ -Recurse
Dismount-DiskImage -ImagePath 'C:\vagrant\SQLServer2017-x64-ENU.iso'

cd C:\vagrant\scripts\
PS C:\vagrant\scripts\> dir
    Directory: C:\Scripts\MSSQLS
Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----        2/14/2021   5:20 AM            627 SQLInstallConfiguration.ps1

. .\SQLInstallConfiguration.ps1

Start-DscConfiguration -Path C:\Scripts\MSSQLS\SQLInstall -Wait -Force -Verbose
Get-Service -Name *SQL*

Get-Command -ModuleName sqlserver



Vagrant.configure("2") do |config| = "StefanScherer/windows_2019" "private_network", ip: ""

   - downloaded from :
   Microsoft Command Line Utilities 14.0 for SQL Server

About sqlcmd Utility

Download the latest version of sqlcmd Utility

Download ODBC Driver for SQL Server

Install SQL Server with PowerShell Desired State Configuration