Mass Update and Insert in Oracle

UPDATE tab1 t1 SET col_1 = (SELECT col_1 FROM tab2 t2 WHERE t1.id=t2.id);

INSERT INTO table1 SELECT * FROM table2 WHERE ...

INSERT INTO table1 (col_1, col_2) SELECT col_1, col_2 FROM table2 WHERE ... 

Oracle triggers

Oracle trigger can be used to modify data before update or insert in the same table.

Oracle update trigger:

CREATE OR REPLACE TRIGGER update_time_col
  BEFORE
    UPDATE OF col_1, col_2
  ON tab
  FOR EACH ROW
BEGIN
  CASE
    WHEN UPDATING('col_1') THEN
      :new.time_col := SYSTIMESTAMP - INTERVAL '1' DAY;
      :new.col_1 := 'updated ' || :old.col_1;
    WHEN UPDATING('col_2') THEN
      :new.time_col := SYSTIMESTAMP + INTERVAL '1' DAY;
      :new.col_2 := 'updated ' || :old.col_2;    
  END CASE;
END;
/

Oracle insert trigger:

CREATE OR REPLACE TRIGGER insert_time_col
  BEFORE
    INSERT
  ON tab
  FOR EACH ROW
BEGIN
  :new.time_col := SYSTIMESTAMP + INTERVAL '10' DAY;
  :new.col_1 := 'insert ' || :new.col_1;  
  :new.col_2 := 'insert ' || :new.col_2;
END;
/

Oracle 12c parsing table row by row with for and switch case

Function that will be used:

CREATE OR REPLACE FUNCTION fun2( p_c_id IN CHAR, p_name IN VARCHAR2, p_regio IN NUMBER) 
RETURN VARCHAR2 
AS 
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO CON (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
  VALUES ( p_c_id, p_name, p_regio);
COMMIT;
RETURN p_name || ' mod';
END;
/

Parse row by row using CASE SWITCH:

SELECT REGION_ID,
       CASE region_id
           WHEN 2 THEN fun2(country_id, country_name, region_id )
           WHEN 4 THEN 'four'
           ELSE 'not'
       END AS c
FROM countries

Parse row by row using loop

BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) 
  LOOP 
    my_proc(c.field1, c.field2);
  END LOOP;
END;

Xdebug install and usage with PhpStorm

get info about which version of debug you need to download - https://xdebug.org/wizard.php
install - https://www.youtube.com/watch?v=OlcsQ8TCU3A
usage https://www.youtube.com/watch?v=RiViVMIrbh0
xdebug - extensions for firefox and chrome

settings in php.ini with PhpStorm

[xdebug]
zend_extension = /usr/lib/php/20160303/xdebug.so
xdebug.default_enable=1
xdebug.idekey=PHPSTORM
xdebug.remote_enable=1
xdebug.remote_port=9000
xdebug.remote_connect_back=1

cURL in Command Line

Following snippets can be used for testing RESTful API:

# simulate form submission 
curl -X POST -F 'name=test' -F 'password=123' -F 'csrf_token=abc' http://127.0.0.1:8000/login
# or
curl -X POST -H "Content-Type: application/x-www-form-urlencoded" -d 'name=test&password=123&csrf_token=abc' http://127.0.0.1:8000/login

# post JSON
curl -X POST -H "Content-Type: application/json" -d '{"name":"title1", "role":"role1"}' http://127.0.0.1:8000/user

# plain GET
curl -X GET http://127.0.0.1:8000/user/1
curl -X GET http://127.0.0.1:8000/user

# PUT
curl -X PUT -H "Content-Type: application/json" -d '{"name":"other title1",  "role":"other role1"}' http://127.0.0.1:8000/user/1

# DELETE
curl -X DELETE http://127.0.0.1:8000/user/1

Set Apache Alias

If you want to put WordPress to sub folder you need to create file wp03.conf with following content:

Alias /wp03 "/var/www/wp03"
<Directory "/var/www/wp03">
 AllowOverride All
 Options FollowSymlinks
 Order deny,allow
 Deny from all
 Allow from all
 Require all granted
</Directory>

Now you can access your blog on http://localhost/wp03

Exchanging credentials for bearer token

Lufthansa API gives bearer token with expiration time. So you need to exchange client secret, client id and grant type for bearer token. Here's the PHP script. Don't forget to place you credentials.

<?php
 $ch = curl_init();
 curl_setopt($ch, CURLOPT_URL,"https://api.lufthansa.com/v1/oauth/token/");
 curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
 curl_setopt($ch, CURLOPT_POST, 1);
 curl_setopt($ch, CURLOPT_POSTFIELDS, 'client_id=****&client_secret=****&grant_type=client_credentials');
 $headers = [
        'Content-Type: application/x-www-form-urlencoded'
 ];
 curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
 $data = curl_exec($ch);
 curl_close($ch);

 // Handle response data
 $response = json_decode($data);
 // get bearer token
 $tok = $response->access_token;
 
 $ch = curl_init();
 // end-point
 curl_setopt($ch, CURLOPT_URL,"https://api.lufthansa.com/v1/references/countries/DK?limit=20&offset=0");
 curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
 $headers = [
     'Accept: application/json',
     'Authorization: Bearer '.$tok,
     'X-Originating-Ip: '.$_SERVER['SERVER_ADDR']
 ];
 curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
 $server_output = curl_exec ($ch);
 curl_close ($ch);
 $response = json_decode($server_output,true);
 echo"<pre>"; 
 var_dump($response);

PHP script for Yelp API v3

Yelp provides bearer token with no expiration date when you register your app. Place it in third line.

<?php
 // place your bearer token from Yelp API
 $token = '';
 if($token == "") die ("place your credentials");
 $unsigned_url = "https://api.yelp.com/v3/businesses/search?term=hotel&location=sf&limit=20";
 $ch = curl_init();
 curl_setopt($ch, CURLOPT_URL, $unsigned_url);
 curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
 $headers = [
     'Authorization: Bearer ' . $token
 ];
 curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
 $data = curl_exec($ch); // Yelp response
 curl_close($ch);

 // Handle Yelp response data
 $response = json_decode($data);

 // maximal number of API calls reached?
 if(isset($response->error->id) && $response->error->id = "EXCEEDED_REQS") die ("You have reached maximum API calls");
 
 // handle no search results
 if($response->businesses[0]->name == NULL) die ('<h1>No search results match your query.</h1>');

 // dump name, url, location, address, latitude and longitude
 echo"<p>name, url, location, address, latitude and longitude</p>";
 echo"<pre>";
 var_dump(
  $response->businesses[0]->name,
  $response->businesses[0]->url,
  $response->businesses[0]->location->display_address[0],
  $response->businesses[0]->location->display_address[1],
  $response->businesses[0]->coordinates->latitude,
  $response->businesses[0]->coordinates->longitude
 );
?>

PHP generates JWT

This script generates basic JWT token

<?php
$decode = file_get_contents('php://input');
$arr = json_decode($decode, true);
if ($arr['email'] == 'me@example.com' && $arr['password'] == '123') {
    $key = 'very-secret-value-only-on-server';
    // header
    $h = ["alg" => "HS256", "typ" => "JWT"];
    $h = base64_encode(json_encode($h));
    
    //payload
    $p = ["username" => "username", "role" => "admin"];
    $p = base64_encode(json_encode($p));
    
    // encryption and signing
    $signature = hash_hmac('sha256', "$h.$p", $key, true);
    $signature = base64_encode($signature);

    $token = "$h.$p.$signature";
    echo $token;
}

You should add iat and exp to payload. Debugger for JWT.