PostgreSQL select data in JSON format

SELECT audit_log.id,

    audit_log.event_time,

    audit_log.client_addr,

    audit_log.application_name,

    audit_log.username,

    audit_log.table_name,

    audit_log.operation,

    audit_log.after_value -> 'exterior'::text AS after_value_in_json

   FROM audit.audit_log

  WHERE 1 = 1 

  AND (audit_log.after_value ->> 'exterior'::text) ~~ '%sCrIpT%'::text

  ORDER BY audit_log.event_time DESC;

/

  

SELECT 

id,

event_time,

client_addr,

application_name,

username,

table_name,

operation,

after_value -> 'exterior'as after_value_in_JSON

FROM audit.audit_log

where 1=1

and after_value ->> 'exterior' like '%sCrIpT%'

LIMIT 20;

/

PostgreSQL Audit with Trigger - "Value-based Auditing (VA)"

1/ Creating a table for Trigger Data:

-- SEQUENCE: audit.audit_log_id_seq

-- DROP SEQUENCE IF EXISTS audit.audit_log_id_seq;

CREATE SEQUENCE IF NOT EXISTS audit.audit_log_id_seq

    INCREMENT 1

    START 1

    MINVALUE 1

    MAXVALUE 9223372036854775807

    CACHE 1;

ALTER SEQUENCE audit.audit_log_id_seq OWNER TO postgres;

GRANT ALL ON SEQUENCE audit.audit_log_id_seq TO postgres;

/

-- Table: audit.audit_log

-- DROP TABLE IF EXISTS audit.audit_log;

CREATE TABLE IF NOT EXISTS audit.audit_log

(

    id bigint NOT NULL

DEFAULT nextval('audit.audit_log_id_seq'::regclass),

    event_time text COLLATE pg_catalog."default",

    client_addr inet,

    application_name text COLLATE pg_catalog."default",

    username text COLLATE pg_catalog."default",

    table_name text COLLATE pg_catalog."default",

    operation text COLLATE pg_catalog."default",

    before_value json,

    after_value json,

    CONSTRAINT audit_log_pkey PRIMARY KEY (id)

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS audit.audit_log OWNER to postgres;

GRANT ALL ON TABLE audit.audit_log TO postgres;

------------------------------ 2/ Creating a Audit Trigger:

-- FUNCTION: audit.audit_trigger()

-- DROP FUNCTION IF EXISTS audit.audit_trigger();

CREATE OR REPLACE FUNCTION audit.audit_trigger()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

DECLARE

    old_row json := NULL;

    new_row json := NULL;

BEGIN

    IF TG_OP IN ('UPDATE','DELETE') THEN

        old_row = row_to_json(OLD);

    END IF;

    IF TG_OP IN ('INSERT','UPDATE') THEN

        new_row = row_to_json(NEW);

    END IF;

    INSERT INTO audit.audit_log(

        client_addr,

        application_name,

        username,

        event_time,

        table_name,

        operation,

        before_value,

        after_value

    ) VALUES (

        inet_client_addr(),

        current_setting('application_name'),

        session_user,

        current_timestamp,

        TG_TABLE_SCHEMA ||  '.' || TG_TABLE_NAME,

        TG_OP,

        old_row,

        new_row

    );

    RETURN NEW;

END;

$BODY$;

ALTER FUNCTION audit.audit_trigger() OWNER TO postgres;

GRANT EXECUTE ON FUNCTION audit.audit_trigger() TO postgres;

------------------ 3/ Insert Trigger to Table "public.salons":

-- Trigger: audit_log__public_salons

-- DROP TRIGGER IF EXISTS audit_log__public_salons ON public.salons;

CREATE TRIGGER audit_log__public_salons

    AFTER INSERT OR DELETE OR UPDATE

    ON public.salons

    FOR EACH ROW

    EXECUTE PROCEDURE audit.audit_trigger();

------------------

4/ Result: SELECT * FROM audit.audit_log ORDER BY id ASC LIMIT 100

NGINX config for Websocket

###############
#Nginx Virtual Host Config:
#tiktok.local
server {
listen 80;
listen 443 ssl http2;
listen [::]:443 ssl http2;
server_name tiktok.local;

ssl_certificate /home/iadmin/Workspace/local-ssl/tiktok.local/tiktok.local+4.pem;

ssl_certificate_key /home/iadmin/Workspace/local-ssl/tiktok.local/tiktok.local+4-key.pem;

location /ws {
proxy_pass http://127.0.0.1:3000/ws;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection 'upgrade';
proxy_set_header Host $host;
proxy_cache_bypass $http_upgrade;
}

location / {
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Forwarded-Server $host;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_pass http://127.0.0.1:3000$request_uri;
}
}
###############

# REF:
# https://www.digitalocean.com/community/tutorials/how-to-deploy-a-react-application-with-nginx-on-ubuntu-20-04
# https://stackoverflow.com/questions/55688883/set-up-nginx-proxy-for-react-application
# https://wiki.matbao.net/kb/huong-dan-cai-dat-chung-chi-ssl-tren-nginx/
# https://stackoverflow.com/questions/63134170/create-react-app-code-changes-behind-nginx-reverse-proxy-not-reloading-in-browse

NodeJS ".env.local":

CHOKIDAR_USEPOLLING=true

WDS_SOCKET_PORT=443

RESULT:

How to generate PI (3.14) from the Gregory-Leibniz's formula

#!/usr/bin/env python3

# ---------------------------------------

# Project Name : PythonCheatSheet

# File Name    : Calculate-Pi-3-Gregory-Leibniz.py

# Created Date : 2023-02-02 12:46:25 UTC+7

# Last Modified: 2023-02-02 12:48:49 UTC+7

# ---------------------------------------

from pytictoc import TicToc

t = TicToc()  # create TicToc instance

t.tic()       # Start timer

print(f"================================")

# DO SOMETHING

# https://www.mathscareers.org.uk/calculating-pi/

# Gregory-Leibniz:

# Pi/4 = [1 - 1/3 + 1/5 - 1/7 + 1/9 - ...]

def my_pi(p_number: int) -> float:

    my_result = 0

    for i in range(1, n+1, 1):

        if i % 2 == 0:  # even

            my_result = my_result - 1/(2*i - 1)

        else:           # odd

            my_result = my_result + 1/(2*i - 1)

    return my_result*4

n = 1000000

print(f'my_pi({n}): {my_pi(n)}')

print(f"================================")

t.toc()       # Print elapsed time: "Elapsed time is <xxx> seconds."

# ---------------------------------------

# REF:

# https://mathshistory.st-andrews.ac.uk/HistTopics/1000_places/

# 3.14159265358979323846264338327950288419716939937510

# RESULT:

# WINDOWS 3900x:

# ================================

# my_pi(1.000.000): 3.1415916535897743

# ================================

# Elapsed time is 0.127023 seconds.

# Linux 3900x:

# ================================

# PI     : 3.1415916535897743

# ================================

# Elapsed time is 0.209464 seconds.

# ================================

# PI     : 3.1415916535897743

# ================================

# Elapsed time is 0.197258 seconds

# LINUX 5600g:

# n = 100  # 3.1315929035585537

# n = 500  # 3.139592655589785

# n = 700  # 3.1401640828900845

# n = 1.000  # 3.140592653839794

# n = 2.000  # 3.1410926536210413

# n = 3.000  # 3.1412593202657186

# n = 5.000  # 3.141392653591791    - 0.001042 seconds.

# n = 1.000.000   # 3.1415916535897743  - 0.074705 seconds.

# n = 10.000.000  # 3.1415925535897915  - 0.757600 seconds.

# n = 100.000.000 # 3.141592643589326   - 7.646685 seconds.

# n = 1.000.000.000     # 3.1415926525880504 - 81.178900 seconds.

# n = 1.000.000.000     # 3.1415926525880504 - 76.741032 seconds

# n = 10.000.000.000    # 3.141592653488346  - 937.967539 seconds.

[Ubuntu 20.04.LTS.x64] Install newest [Python 3.11.1]

HOW TO INSTALL NEWEST PYTHON  FROM SOURCECODE

#UBUNTU 20.04.LTS.x64
##########################
REF: https://docs.posit.co/resources/install-python-source/
Posit - Install Python From Source
##########################

sudo apt-get update
sudo apt-get install gdebi-core

sudo apt-get build-dep python
sudo apt-get install libffi-dev libgdbm-dev libsqlite3-dev libssl-dev \
zlib1g-dev



cd /opt/setup/
export PYTHON_VERSION=3.11.1
export PYTHON_MAJOR=3

curl -O https://www.python.org/ftp/python/${PYTHON_VERSION}/Python-${PYTHON_VERSION}.tgz
tar -xvzf Python-${PYTHON_VERSION}.tgz
cd Python-${PYTHON_VERSION}


./configure \
--prefix=/opt/python/${PYTHON_VERSION} \
--enable-shared \
--enable-optimizations \
--enable-ipv6 \
LDFLAGS=-Wl,-rpath=/opt/python/${PYTHON_VERSION}/lib,--disable-new-dtags

make
sudo make install


# Looking in links: /tmp/tmp1i8nsd3d
# Processing /tmp/tmp1i8nsd3d/setuptools-65.5.0-py3-none-any.whl
# Processing /tmp/tmp1i8nsd3d/pip-22.3.1-py3-none-any.whl
# Installing collected packages: setuptools, pip
# WARNING: The scripts pip3 and pip3.11 are installed in '/opt/python/3.11.1/bin' which is not on PATH.
# Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
# Successfully installed pip-22.3.1 setuptools-65.5.0
# WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
# root@3900xl /opt/setup/Python-3.11.1#






# Install pip into the version of Python that you just installed:
curl -O https://bootstrap.pypa.io/get-pip.py
sudo /opt/python/${PYTHON_VERSION}/bin/python${PYTHON_MAJOR} get-pip.py


# Verify that Python is installed by running the following command:
/opt/python/${PYTHON_VERSION}/bin/python${PYTHON_MAJOR} --version


# (Optional) Add Python to the system PATH:
# PATH=/opt/python/<PYTHON-VERSION>/bin/:$PATH
PATH=/opt/python/3.11.1/bin/:$PATH

# #GLOBAL PATH VARIABLES:
# PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/opt/jdk/bin:/opt/script:/opt/lampp/bin:/usr/local/pgsql/bin:/opt/mariadb/bin:/opt/python/3.11.1/bin
# export PATH