MySQL DDL (Data Definition Language) Operations

DDL Document

Column Operations

PHP Cheating Sheet

USort and Heap

class Solution {

    /**
     * Leetcode 253
     * @param Integer[][] $intervals
     * @return Integer
     */
    function minMeetingRooms($intervals) {
        usort($intervals, function($a, $b) {
            return $a[0] > $b[0];
        });

        $heap = new SplMinHeap();
        $ans = 1;
        $heap->insert($intervals[0][1]);

        for ($i=1; $i<count($intervals); $i++) {
            $top = $heap->top();
            if ($top <= $intervals[$i][0]) {
                $heap->extract();

            } else {
                $ans++;
            }
            $heap->insert($intervals[$i][1]);
        }

        return $ans;
    }
}

__invoke() magic method

The __invoke() method is called when a script tries to call an object as a function.

<?php
class CallableClass
{
    public function __invoke($x)
    {
        var_dump($x);
    }
}
$obj = new CallableClass;

//will do var_dump (int 5)
$obj(5);

//bool true
var_dump(is_callable($obj));

::class

SomeClass::class will return the fully qualified name of SomeClass including the namespace.

use WW\PayAPI\Middlewares\AuthUsers;

//same as $magic = "WW\PayAPI\Middlewares\AuthUsers";
$magic = AuthUsers::class;

List extensions installed

php -m 

GuzzleHttp\Client API Call

<?php
//get.php
//need to install library  ./composer.phar require guzzlehttp/guzzle 7.5.0
//usage: php get.php
require 'vendor/autoload.php';

use GuzzleHttp\Client;

$client = new Client();
$url = 'https://bin-lookup.test-api.io/v1/search/519396****8565';

// Make a GET request
$response = $client->request('GET', $url, [
    'headers' => [
        'Accept' => 'application/json',
        'x-api-key' => 'ksjddnd24Sj65vUhyqqqqwww'
    ],
    // You can pass query parameters as well
    'query' => [
        'param1' => 'value1',
        'param2' => 'value2'
    ]
]);

// Get the response status code
$statusCode = $response->getStatusCode();
echo "Status Code: $statusCode\n";

$body = $response->getBody();

// Convert JSON response to associative array
$data = json_decode($body, true);

// Print the response data
print_r($data);

<?php
//post.php
/**
 * usage: php post.php
 */
require 'vendor/autoload.php';

$client = new \GuzzleHttp\Client();

$url = "http://local-support.myapitest.com/rest/v1/orders/purchase-lookup";

$response = $client->post($url, [
    'headers' => [
        'Content-Type' => 'application/json',
        'x-api-key' => 'whasjsjsjsjjsjjsjsuuuu|4'
    ],
    //post body sent will be json encoded string
    'json' => [
        'email' => 'mytest@testp.com'
    ]
]);

$body = $response->getBody();
$ans = json_decode($body, true);

var_dump($ans);

$statusCode = $response->getStatusCode();
echo "Status Code: $statusCode\n";

ngrok tunnel

./ngrok http -host-header=rewrite local.reeyysy.cc:80

Use config file to run multi tunnels

• find config file location.

  ./ngrok authtoken 23siywfFYojsSVe8ojvO0yTEgf4_7dsx9xsdsfffdwww

• set up config file
• start tunnels

  ./ngrok start --all

Sample config file

bind_tls means https only to save endpoints

authtoken: 23siywfFYojsSVe8ojvO0yTEgf4_7dcnsnsdfskflfl
tunnels:
  webpack:
    proto: http
    addr: 8090
    host_header: rewrite
    domain: webpack.myseieie.ninja
    bind_tls: true
  cartSite:
    proto: http
    addr: 80
    domain: localhost
    host_header: rewrite
    bind_tls: true

React Context

React Context Docs

How to work

• Create a context
• Create a context provider component. Add values in the provider
• Wrap components with the provider component
• For these wrapped components, can use useContext to access values

Create Context Sample Code

export const placeContext = createContext();

//create a function component PlaceContextProvider
const PlaceContextProvider = ({ children }) => {
  const [places, dispatch] = useReducer(placeReducer, []);
  return (
    <placeContext.Provider value={{ places, dispatch }}>
      {children}
    </placeContext.Provider>
  );
};

export default PlaceContextProvider;

Main parts to create a context

1. call createContext()// const placeContext = createContext();
2. Create a context provider component. Set values property when create this component.
3. export the above context provider

Use context provider

import PlaceContextProvider from "./contexts/place";

function App() {
  return (
    <div className="App">
      <PlaceContextProvider>
        <Nav />
        <PlaceList />

        <PlaceForm />
      </PlaceContextProvider>
    </div>
  );
}

export default App;

Main parts to use context provider

1. import context provider
2. wrap components which will use context in the context provider

Use context in a component

import { placeContext } from "../contexts/place";

export default function Nav() {
  const { places } = useContext(placeContext);
  return <h1>You have visited {places.length} places.</h1>;
}

Main parts to use context in a component

1. import context (not context provider)
2. pass the context as paramter of useConext function
3. destruct the result which is values we pass to context provider
4. use these values in the component

Stripe Webhook

Stripe provides with webhooks for event such as checkout.session.completed.

Set up webhook

• log into Stripe
• Click developer tab
• Click webhook tab
• following instruction to create a webhook

See Webhook successful or failed event

• log into Stripe and go to webhook tab (see above)
• click a webhook in the list of webhooks
• click one event log in the list of event
• Should see the details of request and response of the event

Test webhook in local server

• ./ngrok http -host-header=rewrite local.reeyysy.cc:80

• set up webhook using url generated by ngrok
• try to fire an event
• if failed, log into Stripe and find the failed event. Click resend to send event again.

Google Pay

Introduction

Google Pay for the web is built on the Payment Request API, an open web standard candidate that relies on the customer's browser as a secure intermediary for payments.

What is Payment Request API

It is one part of Web Payments standard.

Web Payments is an emerging web standard being developed by the W3C to simplify online payments. Web Payments consist of the following web standards:

• Payment Request API
• Payment Handler API
• Payment Method Identifiers
• Payment Method Manifest

//sample code for Payment Request
const request = new PaymentRequest(paymentMethods, paymentDetails);
request.canMakePayment().then(result => {
  if (result) {
    // This browser supports the specified payment method.
  } else {
    // This browser does NOT support the specified payment method.
  }
}).catch(e => {
  // An exception
});

Standalone page to show Google Pay

Google Pay Tutorial

<html>
<head>
    <script async src="https://pay.google.com/gp/p/js/pay.js" onload="onGooglePayLoaded()"></script>
    <script>
        const tokenizationSpec = {
            type: 'PAYMENT_GATEWAY',
            parameters: {
                gateway: 'example',
                gatewayMerchantId: 'gatewayMerchantId'
            }
        };

        const cardPaymentMethod = {
            type: 'CARD',
            tokenizationSpecification: tokenizationSpec,
            parameters: {
                allowedCardNetworks: ['VISA', 'AMEX'],
                allowedAuthMethods:
                    ['PAN_ONLY', 'CRYPTOGRAM_3DS'],
                billingAddressRequired: true,
                billingAddressParameters: {
                    format: 'FULL',
                    phoneNumberRequired: true
                }
            }
        }

        function onGooglePayLoaded() {
            const googlePayClient = new google.payments.api.PaymentsClient({ environment: "TEST" });

            const clientConfiguration = {
                apiVersion: 2,
                apiVersionMinor: 0,
                allowedPaymentMethods: [cardPaymentMethod]
            };

            googlePayClient.isReadyToPay(clientConfiguration)
                .then(function (response) {
                    console.log(response)
                    if (response.result) {
                        //create button
                        let button = googlePayClient.createButton({
                            buttonColor: 'default',
                            buttonType: 'long',
                            onClick: handleSubmit
                        })

                        document.getElementById('gp').appendChild(button);
                    }
                }).catch(function (err) {
                });

            function handleSubmit() {
                const paymentDataRequest = Object.assign({}, clientConfiguration);
                paymentDataRequest.transactionInfo = {
                    totalPriceStatus: 'FINAL',
                    totalPrice: '123.45',
                    currencyCode: 'USD'
                }

                paymentDataRequest.merchantInfo = {
                    merchantId: '0123456789',
                    merchantName: 'Example Merchant'
                }

                googlePayClient.loadPaymentData(paymentDataRequest)
                    .then(function (paymentData) {
                        console.log("payment data:", paymentData)
                    })
            }
        }

        
    </script>
</head>

<body>
    <p>Try Google Pay</p>
    <p id="gp"></p>
</body>

</html>

Local development

• if it is localhost, Google allows it to be http
• let a gmail to joined into sandbox test group and do not need to provide card when click "Google Pay button"

Join sandbox test group

JSON Web Tokens

JSON Web Tokens are an open, industry standard RFC 7519 method for representing claims securely between two parties.

For JWT token, it consists of three parts:

• header about alg and type
• payload
• signature

PHP codes to generate a JWT

    function createJWT($payload, $secret)
    {
        //this is alg used
        $header = ["typ" => "JWT", "alg" => "HS256"];

        $encodedHead = base64url_encode(json_encode($header));
        $encodedPayLoad = base64url_encode(json_encode($payload));

        //build the first two parts
        $encodedHeaderAndPayload = $encodedHead . '.' . $encodedPayLoad;

        $signature = base64url_encode(hash_hmac('sha256', $encodedHeaderAndPayload, $secret, true));

        //build token
        return $encodedHeaderAndPayload. '.' . $signature;
    }

    function base64url_encode($data)
    {
        return rtrim(strtr(base64_encode($data), '+/', '-_'), '=');
    }

Contents

• Apple Pay and Braintree
• AWS API Gateway Custom Domain
• AWS Questions
• AWS Schedule post to an end point using Amazon EventBridge Rules
• AWS WAF
• Block Chain
• Browscap
• Debug
• Docker
• Environment variable in Docker
• Git Cheating Sheet
• Git Cherry Pick
• GitHub Copilot
• GitHub Copilot Agents
• Github two accounts on one machines
• Go Cheating Sheet
• Google reCAPTCHA
• Google Pay
• JavaScript Bind This
• JavaScript Cheating Sheet (Including Promise)
• JavaScript Node.js Module
• JavaScript Promise
• Java Cheating Sheet
• Jenkins
• JWT
• Linux Commands
• MySQL DDL
• MySQL Tips
• More MySQL Tips
• MySQL Query
• Ngrok
• Ngrok Set Up
• NPM
• OpenAi
• PHP Cheating Sheet
• PHP Composer
• PHP Laravel
• PHP Slim 4
• PHP Storm
• PHPUnit 11
• Postman
• Public Key and Private Key
• Python Cheating Sheet
• React
• React Context
• Regular Expression
• Ruby Cheating Sheet
• Set up localhost
• SFTP Server
• SSH Config
• SSL Certificate
• Stop http to https in Chrome
• Stripe Webhook
• TypeScript
• VS codes
• WSL (Window Subsystem for Linux)

Multiple UpSell

React Hook Form

React Hook Form Tutorial

//sample
const [books, dispatch] = useReducer(bookReducer, [])

useReducer Hook

The idea behind it is the same as reduc. Here is useReducer doc

React Context

Here is react context toturial

• Provider needs to wrap a children when return a provider. Here children has a special meaning of children components
• in App.js, components will be wrapped by context provider
• In the component itself, it can access context in three ways
  • contextType
  • consumer
  • const {isLight, light} = useContext(ThemeContext)

In Context file, we need to export both context and context provider

• export const AuthContext = createContext()
• export default AuthConextProvider (This is a component with AuthContext.Provider)

Environment variables in docker

To view variables

1. Log into container

2. printenv

3. printenv PATH

Other options

//in linux
env

//use echo
echo $PATH

To set variables

//to set
export <NAME>='<value>'

//for example to set value for MYPET
export MYPET='cutedog'

//to confirm
printenv MYPET

Set env variable in aws cloudformation

To inject sensitive data into your containers as environment variables, use the secrets container definition parameter.

• Create parameter in AWS parameter store
• Add it in cloudformation template

//code snippet. ExecutionRoleArn is needed because of Secrets and the role needs read permission to access parameter store
TaskDefinition:
    Type: "AWS::ECS::TaskDefinition"
    Properties:
      Family: !Sub "${AWS::StackName}"
      NetworkMode: bridge
      TaskRoleArn: !FindInMap [BuildEnvironment, !Ref 'EnvironmentType', "role"]
      ExecutionRoleArn: !FindInMap [BuildEnvironment, !Ref "EnvironmentType", "executionRole"]
      ContainerDefinitions:
          Secrets:
            - Name: GOOGLE_DRIVE_CREDENTIALS
              ValueFrom: !Sub "arn:aws:ssm:${AWS::Region}:12345678:parameter/GOOGLE_ACCOUNT-${EnvironmentType}"

Use shell script to check environment variables

#check to see if env variable HOME is set
if [[ -n "${HOME}" ]]; then
    echo "$HOME"
else
    echo "Homeless"
fi

Set environment varaibles in Dockerfile

FROM php:8.3.11RC2-zts-alpine3.20

ENV GCT_PROJECT='fluid-fiber-84848484'
ENV GCT_KEY='AIzaSyCejejsllswororowwppw'

Google reCAPTCHA

Step one: sign up recaptcha in Google.

It is free. Get GOOGLE_RECHAPTCHA_SECRET and GOOGLE_RECHAPTCHA_SITE_KEY

Need to add domain with this recaptcha. Do not need to add sub domain. Can add multiple domains

Step two: client side

<script src="https://www.google.com/recaptcha/api.js" async defer></script>
<div class="g-recaptcha" data-callback="recaptchaCallback" data-sitekey="{$siteKey}"></div>

//after check, may want to hide error. Put codeback code here
var recaptchaCallback = function() {

}

Step three: server side validation

User response can be found in $_POST['g-recaptcha-response']

public static function verifyRecaptcha($userResponse)
{
    $url = "https://www.google.com/recaptcha/api/siteverify";
    $curl = curl_init($url);

    $post = ['secret' => getenv('GOOGLE_RECHAPTCHA_SECRET'), 'response' => $userResponse];
    $post = http_build_query($post);

    curl_setopt($curl, CURLOPT_POSTFIELDS, $post);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");

    $response = curl_exec($curl);
    curl_close($curl);

    //see what we get
    $result = json_decode($response, true);

    return $result['success'] ?? false;
}

React component for Google reCAPTCHA v2

npm install --save react-google-recaptcha

Stripe Javascript Cart

create a session

        \Stripe\Stripe::setApiKey($this->getSecret());
        $data = [
            'payment_method_types' => ['card'],
            'line_items' => [[
                'price_data' => [
                    'currency' => $currencyCode,
                    'product_data' => [
                        'name' => $productName,
                    ],
                    'unit_amount' => $amountWithoutDecimal,
                ],
                'quantity' => $quantity,
            ]],
            'metadata' => [
                "order_id" => $orderID,
                'order_type' => $orderType,
                'user_id'=> $userId,
               
            ],
            'client_reference_id' => "{$orderType}_{$orderID}",
            'mode' => 'payment',
            'success_url' => $this->getSuccessUrl(),
            'cancel_url' => $this->getCancelUrl(),
        ];

        try {
            $session = \Stripe\Checkout\Session::create($data);
            return $session;
        } catch (Exception $e) {
            echo $e->getMessage();
        }

Initiate Cart By JavaScript

<script  type="text/javascript" src="https://js.stripe.com/v3/"></script>
const stripeSession = await fetch("/stripe/create-checkout-session.php", {
                method: "POST",
                headers: {
                    "Content-Type": "application/json",
                },
                //info needed to create session
                body: JSON.stringify({
                    price: myPrice,
                    type: "product",
                    .....
                    
                }),
            })

            const data = await stripeSession.json()

            var stripe = Stripe('{$stripePublicKey}');
            stripe.redirectToCheckout({
                sessionId: data.sessionId,
            })

Process After Payment

When set up successful url, we can use the placeholder: session_id={CHECKOUT_SESSION_ID}

$successUrl = "https://example.com?session_id={CHECKOUT_SESSION_ID}";

when we create session, we can remember strip checkout session id in server side. Do compare in the successful url page and we know it is paid. Then we can do what we want for a successful transaction.

Create SSL/TSL Certificate in AWS ACM

cname name and cname value

cname allows to point a hostname to any other hostname. hostname1 maps to hostname2. Here hostname1 is called cname name and hostname2 is called cname value. cname name can not be root domain name. something.mydom.com works, but mydom.com can not be used to be cname name.

Can use dig command to find cname /cname value pair.

dig checkout.mytest.com

Request a certification in AWS ACM

1. request a public certification. Also choose DNS validation
2. • If record is in AWS route 53, AWS will do validation automatically and issued the certificate
   • If record is not in AWS route 53, grab the cname name and cname value provided by the certificate. Create a cname record in your domain registra, and AWS will verify it and issue the certificate

Add certificate to ELB

The default Limit for Certificates per Application Load Balancers is 25

• Select the load balancer.
• On the Listeners and rules tab, choose the text in the Protocol:Port (443) column to open the detail page for the listener.
• On the Certificates tab, choose Add certificate.
• Within the ACM and IAM certificates table, select the certificates to add and choose Include as pending below
• Choose Add pending certificates.

Delete a certificate

1. Got to ACM to find that certificate. Check if it is in use. If it is in use, write down which elbs are using it.
2. If in use, go to elb. click cetificates, and check that certificate. Then click remove.
3. After it is removed from all associated elbs, go back to ACM. Choose the certificate, and click delete

API Access-Control-Allow header

$response = $response->withHeader('Access-Control-Allow-Origin', "*");
$response = $response->withHeader('Access-Control-Allow-Credentials', 'true');
return $response->withHeader('Access-Control-Allow-Headers', 'X-Requested-With,
Content-Type, Accept, Origin, Authorization, X-Authorization-JWT, X-CSRF-Token')
->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');

MySQL Query

rank

# see leetcode 2173 https://leetcode.com/problems/longest-winning-streak/description/
select
    player_id,
    result,
    RANK () OVER (
        PARTITION BY player_id,
        result
        ORDER BY
            match_day
    ) as my_rank
from
    Matches
order by
    match_day

generate sequence

# 1 to 100
WITH RECURSIVE seq AS (
    SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 100
    )

create help tables

with platforms as (
select 'Android' as platform
union
select 'IOS' as platform
union
select 'Web' as platform
),

 activities as (
  select 'Reading' as experiment_name
  union
  select 'Sports' as experiment_name
  union
  select 'Programming' as experiment_name
)

More Queries

#day function converts timestamp to day. 2023-10-11 23:33:33 to 11

select day(created), partner_fid, count(*) from cpa_conversion where created>="2024-9-1" group by day(created), partner_fid;

where is my docker-compose

After upgrade docker desktop, docker-compose command can not be found.

But look at docker information the below, docker compose should be there.

which docker
/usr/local/bin/docker

cd /usr/local/bin/docker

//see a symoblic link
docker-compose -> /Applications/Docker.app/Contents/Resources/bin/docker-compose/docker-compose

//go to /Applications/Docker.app/Contents/Resources/bin/docker-compose and not find docker-compose
//ha ha directory structure changed in this new version of docker desktop
//to fix, in /usr/local/bin
rm docker-compose
ln -s /Applications/Docker.app/Contents/Resources/bin/docker-compose docker-compose

AWS Certificate Manager

DNS validation

ACM provides you with one or more CNAME records that must be added to this database. These records contain a unique key-value pair that serves as proof that you control the domain.

If use AWS Route 53, you can create a record of CNAME type for that domain. The CNAME name and CNAME value provided by ACM should be used.

Modify Request Headers in Chrome

• Install ModHeader extension in Chrome
• Use that extension to set a header
• Open developer tool, network tab
• Visit a page
• View request headers in developer tool
• Make sure header sent indeed

docker compose

extra_hosts

Add hostname mappings. Function the same as the docker client --add-host parameter.

//example
extra_hosts:
  - "somehost:162.242.195.82"
  - "otherhost:50.31.209.229"

An entry with the ip address and hostname is created in /etc/hosts inside containers for this service

host.docker.internal

For docker for mac or window, it is docker host ip. However, for docker for Linux, need to add this in compose file

#For docker for Mac or Window, should not add this. Otherwise,
#host.docker.internal may not be host ip.  It is for Linux only
extra_hosts:
    - host.docker.internal:host-gateway

Google Tag Management System

What is tag

Tag is a JS code snippet inserted into a web page to track user event. Meta pixel and Goggle ad are some example of tags

What problem we have if we use multiple tags in a web page

• Will need to add a code snippet for every tag. For example, if we use five track tools from ten different company, we need to add 10 extra code snippets in different pages
• If we want to change some thing such as account id, we need to go to every page to modify them

Google Tag Management System

Tag management system likes an container. You set up and update tags in the tag management system, and you only need one extra code snippent in a web page no matter how many tags you want to use. Google Tag Management System is one of these systems.

Use Google Tag Management System, and you can

• Set up tags
• Set up event rules

gtag.js

gtag.js is the JavaScript framework that is used to add Google tags directly to web pages. It can not add third party tags.

Here what we need is a tag id

// Google tag (gtag.js)
<script async src="https://www.googletagmanager.com/gtag/js?id=TAG_ID"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments)};
  gtag('js', new Date());

  gtag('config', 'TAG_ID');
</script>

The Google tag lets you send data from your website to linked Google product destinations. By now only Google Ads accounts and web data streams in GA4 properties can be destinations.

You can use a single Google tag across your entire website and connect the tag ID to multiple destinations.