Refs

1. Koa中文文档

Why Koa ?

Smaller, more expressive, more robust.

• VS Epxress
  The key difference between Koa and Express is how they handle middleware. Express includes routing and templates in the application framework. Koa, on the other hand, requires modules for these features, therefore making it more modular or customizable.

Refs

1. Vuetify

What is Vuetify ?

A material-design UI framework for UI.

Install

• For vue-cli created project, type vue add vuetify in project root folder.

Use vuetify icons offline

• Vuetify offline icons
• By default vuetify requires material icons and requesting the icon lib in China may be blocked.

1

yarn add @mdi/font -D

1
2
3
4
5
6
7
8
9
10
11
12

// src/plugnis/vuetify.js
import '@mdi/font/css/materialdesignicons.css' // Ensure you are using css-loader
import Vue from 'vue'
import Vuetify from 'vuetify/lib'

Vue.use(Vuetify)

export default new Vuetify({
  icons: {
    iconfont: 'mdi', // default - only for display purposes
  }
})

Refs

1. Element plus

Components

Config Provider

• Provides global configuration. Wrap the entire application in <el-config-provider> for configuration to work.
• Properties in <el-config-provider> will pass to all sub-components. See available properties

Example:

Refs

1. Node.js Worker Threads
2. Deep dive into threads and processes in Node.js
3. How do cluster and worker threads work in node.js

Multithreading: Worker thread

What and why

A thread that enables node.js to execute JavaScript in parallel. Useful to handle CPU intensive jobs.

How-to

Create a worker file => Make a promise in caller file => Define on message/error/exit hooks
worker.js file:

1
2
3
4
5
6

const { workerData, parentPort }	= require('worker_threads')

console.log('Technical Articles on ' + workerData);

parentPort.postMessage(
	{ fileName: workerData, status: 'Done' })

index.js file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

const { Worker } = require('worker_threads')

function runService(workerData) {
	return new Promise((resolve, reject) => {
		const worker = new Worker(
				'./worker.js', { workerData });
		worker.on('message', resolve);
		worker.on('error', reject);
		worker.on('exit', (code) => {
			if (code !== 0)
				reject(new Error(
`Stopped the Worker Thread with the exit code: ${code}`));
		})
	})
}

async function run() {
	const result = await runService('GeeksForGeeks')
	console.log(result);
}

run().catch(err => console.error(err))

Key points:

1. Worker use workerData to receive data from caller and parentPort to post data to caller.
2. Worker use parentPort.postMessage to send data to caller and caller use on('message') to receive.

Multiprocess: Fork and Cluster

An example to address the problem of single-threaded node.js

Single threaded node.js will block on a time-consuming request.
Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

const http = require('http');
const longComputation = () => {
  let sum = 0;
  for (let i = 0; i < 1e10; i++) {
    sum += i;
  };
  return sum;
};
const server = http.createServer();
server.on('request', (req, res) => {
  if (req.url === '/compute') {
    console.info('计算开始',new Date());
    const sum = longComputation();
    console.info('计算结束',new Date());
    return res.end(`Sum is ${sum}`);
  } else {
    res.end('Ok')
  }
});

server.listen(3000);

Call localhost:3000/compute will block

Pros and cons of single-threaded node.js

Pros:

1. Simple, no creation and switching of threads.
2. Event loop and non-blocking asynchronous mechanism ensures high performance for high concurrency.
   Cons:
3. CPU intensive calculation may block entire node.js app.
4. An error may kill the thread, thus kill entire app. A daemon thread should be considered.
5. Single thread does not take advantage of a multi-core CPU.

Fork

Use child_process.fork to create new process.
Main.js:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

const http = require('http');
const fork = require('child_process').fork;

const server = http.createServer((req, res) => {
    if(req.url == '/compute'){
        const compute = fork('./compute.js');
        compute.send('开启一个新的子进程');

        // 当一个子进程使用 process.send() 发送消息时会触发 'message' 事件
        compute.on('message', sum => {
            res.end(`Sum is ${sum}`);
            compute.kill();
        });

        // 子进程监听到一些错误消息退出
        compute.on('close', (code, signal) => {
            console.log(`收到close事件，子进程收到信号 ${signal} 而终止，退出码 ${code}`);
            compute.kill();
        })
    }else{
        res.end(`ok`);
    }
});
server.listen(3000, '127.0.0.1', () => {
    console.log(`server started at http://127.0.0.1:3000`);
});

compute.js:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

const computation = () => {
    let sum = 0;
    console.info('计算开始');
    console.time('计算耗时');

    for (let i = 0; i < 1e10; i++) {
        sum += i
    };

    console.info('计算结束');
    console.timeEnd('计算耗时');
    return sum;
};

process.on('message', msg => {
    console.log(msg, 'process.pid', process.pid); // 子进程id
    const sum = computation();

    // 如果Node.js进程是通过进程间通信产生的，那么，process.send()方法可以用来给父进程发送消息
    process.send(sum);
})

Cluster

cluster can create worker process in a single file.
Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

const http = require('http');
const numCPUs = require('os').cpus().length;
const cluster = require('cluster');
if(cluster.isMaster){
    console.log(`Master process id is ${process.pid}, cpu number ${numCPUs}`);
    // fork workers
    for(let i= 0;i<numCPUs;i++){
        cluster.fork();
    }
    cluster.on('exit',function(worker,code,signal){
        console.log('worker process died,id',worker.process.pid)
    })
}else{
    // Worker can share the same TCP connection
    // It's an http server here
    console.log(`created worker pid ${process.pid}`)
    http.createServer(function(req,res){
        res.writeHead(200);
        res.end(String(process.pid));
    }).listen(8000);

}

Running code above, we got:

1
2
3
4
5

Master process id is 18428, cpu number 4
created slave pid 16672
created slave pid 9896
created slave pid 14676
created slave pid 1460

We can find these process in task manager:

Now send a request in browser, and id of one of the four server process is returned:

Try refresh many times and different pids may return(It depends, maybe one unlucky process shoulders all the workload).
Now kill one the worker 1460 in task manger and we got:

1

worker process died,id 1460

Refresh the browser and result is another pid other than 1460:

You see, now our server is much more robust than before. We got four worker process, killing one of them and there are still three working.

Cluster calls the same fork method from child_process module under the hood. Cluster is a master-slave model, where master manages and schedules slaves.

Why no Error: EADDRINUSE when multiple processes listens on the same port?
The child processes aren’t listening to the same port. Incoming socket connections to the master process are being delegated to the child processes. There’s special handling for clustered process in server.listen(), it calls a method named listenInCluster() in some circumstances. See explanation here

Multithreading vs multiprocess

cluster

• One process is launched on each CPU and can communicate via IPC.
• Each process has its own memory with its own Node (v8) instance. Creating tons of them may create memory issues.
• Great for spawning many HTTP servers that share the same port b/c the master process will multiplex the requests to the child processes.

worker threads

• One process total
• Creates multiple threads with each thread having one Node instance (one event loop, one JS engine). Most Node API’s are available to each thread except a few. So essentially Node is embedding itself and creating a new thread.
• Shares memory with other threads (e.g. SharedArrayBuffer)
• Great for CPU intensive tasks like processing data or accessing the file system. Because NodeJS is single threaded, synchronous tasks can be made more efficient with workers

How it happened

Using node oracledb to develop a backend api and encountered NJS-016: buffer is too small for OUT binds exception for a simple select query: select a, b, c from tab@dblink

Debugging

1. Googled NJS-016: buffer is too small for OUT binds and found little help. None of them suits my case.
2. Maybe it has something to do with database link ? Charset for local database is AMERICAN_AMERICA.AL32UTF8 and for remote database is AMERICAN_AMERICA.ZHS16GBK. Changed charset environment variable to the same as remote database, still no luck.
3. Found that column b has some weired behavior: for chinese character value 神经阻滞 it only shows first two characters 神经 when environment variable is set to AMERICAN_AMERICA.AL32UTF8, different from database charset AMERICAN_AMERICA.ZHS16GBK. Column b definition is varchar2(8). Maybe has something to do with this ?

Reasoning

For charset AMERICAN_AMERICA.ZHS16GBK, each chinese character is 2 bytes wide.
For charset AMERICAN_AMERICA.AL32UTF8, each chinese character is 3 bytes wide.
With varchar2(8), it can hold 4 characters on AMERICAN_AMERICA.ZHS16GBK, 2 characters on AMERICAN_AMERICA.AL32UTF8. That’s why it only shows 2 characters when environment variable is AMERICAN_AMERICA.AL32UTF8.
NJS-016 indicates out binds with varhcar2(8) cannot hold 神经阻滞 as it is 4×3=12 bytes wide.

Solution

1. Expand column width from varchar2(8) to a at least varchar2(12)(Intrusive, not recommended).
2. Avoid using database links when charset is different on remote database. Create a distinct connection pool for remote database and execute query on a connection fetched from the pool.

PL/SQL Concepts

Ref

1. PL/SQL Wiki

Definition

• Procedural Language for SQL is a procedural extesion for SQL.
• Most commonly used features include:
  • Program units
    • Block
    • Function
    • Procedure
    • Package
    • Trigger
  • Data types
    • Numeric
    • Character
    • Date
    • Exceptions
    • Specifi columns
  • Conditional statements
  • Array handling
    • Associative arrays(index-by tables)
    • Nested tables
    • Varrays(variable-size arrays)
  • Cursors
  • Looping
  • Dynamic SQL

Trigger

Stored Procedure and Function

Refs

1. Oracle Procedures
2. PL/SQL Tutorial

Stored Procedure vs Function

Function vs Stored Procedure:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Stored Procedure

Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- create a procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

-- drop a procedure:
DROP PROCEDURE procedure_name;

• Parameter types:
  • IN: Passed into the procedure, must assign a value, immutable.
  • OUT: Not required to assign a value, mutable.
  • IN OUT: Passed into the procedure, must assign a value, mutable.

Test Data

• Initalize test data for the examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create table customers (
  id number primary key,
  name varchar2(50),
  age number,
  address varchar2(50),
  salary number
);

insert into customers values(1, 'Ramesh', 32, 'Abmedabad', 2000);
insert into customers values(2, 'Khilan', 25, 'Delhi', 1500);
insert into customers values(3, 'Kaushik', 23, 'Kota', 2000);
insert into customers values(4, 'Chaitali', 25, 'Mumbai', 6500);
insert into customers values(5, 'Hardik', 27, 'Bhopal', 8500);
insert into customers values(6, 'Komal', 22, 'MP', 4500);

Cursor

• A cursor is a pointer that points to the result of a query.

• There are two types of cursors:

  • Implicit cursor
    • Automatically created by Oracle whenever an SQL statement is executed.
    • Whenever a DML(INSERT, UPDATE and DELETE) statement is executed, an implicit cursor is associated with this statement.
    • For INSERT statement, the cursor holds the data to be inserted; For UPDATE and DELETE statement, the cursor identifies the rows that would be affected.
  • Explicit cursor
    • Created on a SELECT statement which returns more than one row.

• Cursor attributes

  Attribute	Description
  %FOUND	Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
  %NOTFOUND	The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
  %ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
  %ROWCOUNT	Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Examples

If you run the examples in PL/SQL Developer, remember to run the code of the procedure creation in a program windows instead of a sql window. Sql window does not throw errors!

• Implicit cursor:

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE
  total_rows number(2);
BEGIN
  UPDATE customers SET salary = salary + 500;
  IF sql%notfound THEN
    dbms_output.put_line('no customers updated');
  ELSIF sql%found THEN
    total_rows := sql%rowcount;
    dbms_output.put_line(total_rows || ' customers updated ');
  END IF;
  COMMIT;
END;
-- output: 6 customers updated 

• Explicit cursor:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

DECLARE 
   c_id customers.id%type; 
   c_name customers.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
-- output:
-- 1 Ramesh Abmedabad
-- 2 Khilan Delhi
-- 3 Kaushik Kota
-- 4 Chaitali Mumbai
-- 5 Hardik Bhopal
-- 6 Komal MP

• Simplest procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 

-- Run the procedure in different ways:
-- 1. CALL
call greetings();
-- 2. EXECUTE
execute greetings; -- Notice this cannot be used in IDEs like PL/SQL Developer. Only viable in sql*plus or Oracle SQL Developer. See why here: https://stackoverflow.com/questions/13722307/ora-00900-invalid-sql-statement-when-run-a-procedure-in-oracle-10g
-- 3. in a PL/SQL block:
begin 
  greetings;
  greetings(); -- For procedure without parameters, brackets are optional.
end;
-- Hello World!

• Procedure with parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END; 

  
BEGIN 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END;
-- output: Minimum of (23, 45) : 23

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  


BEGIN 
   a:= 23; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
-- output:  Square of (23): 529

• Exception handling:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DECLARE
  c_id   customers.id%type := 8;
  c_name customerS.Name%type;
  c_addr customers.address%type;
BEGIN
  SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id;
  DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);
  DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('No such customer!');
  WHEN others THEN
    dbms_output.put_line('Error!');
END;
--output: No such customer!

CTE

Ref

• 1. SQL CTE

Definition

• Common Table Expression, a query defined within another query.
• It’s like a subquery, but can be assigned a name and reused many times.
• It’s also like a ‘one-shot’ view.
• Also called a SQL WITH clause as it uses the WITH keyword.

Example

Initialization data

• Initialize data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE employee (
  emp_id NUMBER(5),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  dept_id NUMBER(5),
  manager_id NUMBER(5),
  office_id NUMBER(5)
);
 
CREATE TABLE department (
  dept_id NUMBER(5),
  dept_name VARCHAR2(50)
);
 
INSERT ALL
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (1, 'Sally', 'Jones', 3, 2, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (2, 'Mark', 'Smith', 2, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (3, 'John', 'Andrews', 1, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (4, 'Michelle', 'Johnson', 2, NULL, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (5, 'Brian', 'Grand', 2, 2, 3)
SELECT * FROM dual;
 
INSERT ALL
INTO department (dept_id, dept_name) VALUES (1, 'Sales')
INTO department (dept_id, dept_name) VALUES (2, 'IT')
INTO department (dept_id, dept_name) VALUES (3, 'Support')
SELECT * FROM dual;

This creates the following data:

Simple Example: Get employee name with department employe count

1
2
3
4
5
6

WITH d_count AS
 (SELECT dept_id, COUNT(*) AS dept_count FROM employee GROUP BY dept_id)
SELECT e.first_name, e.last_name, d.dept_count
  FROM employee e
 INNER JOIN d_count d
    ON e.dept_id = d.dept_id;

Recursive CTE Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14

WITH cteEmp(emp_id,
first_name,
manager_id,
emplevel) AS
 (SELECT emp_id, first_name, manager_id, 1
    FROM employee
   WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.first_name, e.manager_id, r.emplevel + 1
    FROM employee e, cteEmp r
   WHERE e.manager_id = r.emp_id)
SELECT emp_id, first_name, manager_id, emplevel
  FROM cteEmp
 ORDER BY emplevel;

• This produces the following result:

The above result is generated in the following process:

1. Get data from the initial query where manager_id IS NULL, which returns one row;
   Notice that a recursive CTE must have be UNION query connecting a initial qery and a recursive query.
2. The recursive query FROM employee e, cteEmp r WHERE e.manager_id = r.emp_id associates the original table with data from the initial query, returning more data;
3. Repeats step 2, until no more data is yielded.

Materialized View

Ref

1. Materialized Views in Oracle

Concept

• A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.

Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Normal
CREATE MATERIALIZED VIEW [VIEW_NAME]
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[START WITH ... NEXT ...]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Pre-Built
CREATE MATERIALIZED VIEW [VIEW_NAME]
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[START WITH ... NEXT ...]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

• Build options:

  • IMMEDIATE: The materialized view is populated immediately.
  • DEFERRED: The materialized view is populated on the first request refresh.

• Refresh types:

  • FAST: A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
  • COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
  • FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.
  • FAST VS COMPLETE: FAST refresh only updates rows while COMPLETE refresh completely removes all data and inserts all data from the query.

• Trigger types:

  • ON COMMIT: The refresh is triggered by a comitted data change in one of the dependent tables.
  • ON DEMAND: The refresh is initiated by a manual request or a scheduled task.
  • Refreshing on commi is very intensive on a volative base table.

• Prebuilt:

  • ON PREBUILT TABLE: use existing table which must have same name as materialized view and same column structure as the query.
  • QUERY REWRITE: tells optimizer if materilized view should be considered for qery rewrite operations.

• Schedule:

  • The START WITH ... NEXT ... specifies a schedule.

Refresh on demand

• A materialized view can be refreshed either manually or as part of a refresh group or via a schedule.

• Refresh manually:

  1 2 3 4 5

  EXEC DMBS_MVIEW.refresh('mvt1'); -- For PL/SQL, use this: begin DBMS_MVIEW.REFRESH('mvt1'); end;

• Create a refresh group:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  BEGIN DBMS_REFRESH.make( name => 'SCOTT.MINUTE_REFRESH', list => '', next_date => SYSDATE, interval => '/*1:Mins*/ SYSDATE + 1/(60*24)', implicit_destroy => FALSE, lax => FALSE, job => 0, rollback_seg => NULL, push_deferred_rpc => TRUE, refresh_after_errors => TRUE, purge_option => NULL, parallelism => NULL, heap_size => NULL); END; / BEGIN DBMS_REFRESH.add( name => 'SCOTT.MINUTE_REFRESH', list => 'SCOTT.EMP_MV', lax => TRUE); END; /

• Create a schedule(must be specified at the creation of the materialized view):

  1 2 3 4 5 6

  create materialized view mvt1 build immediate refresh force on demand start with sysdate next sysdate + 1/24/60 -- Refresh every minute as select * from mvtt;

  The materialized view mvt1 will be refreshed every minute. An Oracle job is also created at the same time.
  To find out the job id, use this query:

1
2
3
4
5
6
7
8
9

select m.owner, m.mview_name, r.job
  from dba_refresh r
 inner join dba_refresh_children rc
    on rc.rowner = r.rowner
   and rc.rname = r.rname
 inner join dba_mviews m
    on m.owner = rc.owner
   and m.mview_name = rc.name
 where m.mview_name = 'MVT1'

Examples

• Create a test table mvtt:

• Create a materialized view:

1
2
3
4
5

create materialized view mvt1
build immediate
refresh force on demand
as
select * from mvtt;

Now mvt1 has the same data as mvtt. Insert a new row will not trigger an update in mvtt because its trigger type is on demand.

Analytic Function/Window Function

• Ref

Key points

• Analytic functions calculate an aggregate value based on a group of rows and return multiple rows for each group.
• Analytic functions are just like GROUP BY aggregate functions, but do not reduce rows retured.
• Syntax:
  • analytic_function([ arguments ]) OVER (analytic_clause)
    • Anytic clause can be broken down as: [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

• Get average department salary for each row

1
2
3

SELECT empno, deptno, sal,
       AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
FROM   emp;

• Get first value order by salary, put NULL values last in he sorted query results
  • Note:
    • In terms of sorting, SQL sorts NULL values to last in ASC order and first in DESC
    • MAX and MIN ignores NULL values

1
2
3
4
5

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal IGNORE NULLS) OVER(PARTITION BY deptno order by sal asc nulls last) AS first_sal_in_dept
  FROM emp;

• Windowing clause: an extension of the order_by_clause, can only be used when order_by_clause is present.
• When there’s order_by_clause, a default windowing clause is attached: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • The fact it is RANGE, not ROWS, means it includes all rows with the same value as the value in the current row, even if they are further down the result set. As a result, the window may extend beyond the current row, even though you may not think this is the case.

1
2
3

RANGE BETWEEN start_point AND end_point
ROWS BETWEEN start_point AND end_point
GROUPS BETWEEN start_point AND end_point (21c onward)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT empno, deptno, sal, 
       AVG(sal) OVER (PARTITION BY deptno ORDER BY sal) AS avg_dept_sal_sofar
FROM   emp;

     EMPNO     DEPTNO        SAL AVG_DEPT_SAL_SOFAR
---------- ---------- ---------- ------------------
      7934         10       1300               1300
      7782         10       2450               1875
      7839         10       5000         2916.66667

      7369         20        800                800
      7876         20       1100                950
      7566         20       2975               1625
      7788         20       3000               2175
      7902         20       3000               2175

      7900         30        950                950
      7654         30       1250               1150
      7521         30       1250               1150
      7844         30       1500             1237.5
      7499         30       1600               1310
      7698         30       2850         1566.66667

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT empno, deptno, sal, 
       AVG(sal) OVER (PARTITION BY deptno ORDER BY sal
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_avg,
       AVG(sal) OVER (PARTITION BY deptno ORDER BY sal
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_avg
FROM   emp;

     EMPNO     DEPTNO        SAL  RANGE_AVG   ROWS_AVG
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       1300
      7782         10       2450       1875       1875
      7839         10       5000 2916.66667 2916.66667

      7369         20        800        800        800
      7876         20       1100        950        950
      7566         20       2975       1625       1625
      7788         20       3000       2175    1968.75
      7902         20       3000       2175       2175

      7900         30        950        950        950
      7654         30       1250       1150       1100
      7521         30       1250       1150       1150
      7844         30       1500     1237.5     1237.5
      7499         30       1600       1310       1310
      7698         30       2850 1566.66667 1566.66667

• LAG: Access the row at a given offset prior of the current row without using a self-join.
• Ref
• Syntax:

1
2
3
4
5

LAG(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT e.empno,
       e.deptno,
       e.sal,
       LAG(sal, 1) OVER(partition by deptno ORDER BY sal) sal_lower_closest
  FROM emp e
 order by deptno, sal


 EMPNO     DEPTNO     SAL        SAL_LOWER_CLOSEST
---------- ---------- ---------- ----------
      7934	10	1300.00	
      7782	10	2450.00	1300
      7839	10	5000.00	2450
      7369	20	800.00	
      7876	20	1100.00	800
      7566	20	2975.00	1100
      7788	20	3000.00	2975
      7902	20	3000.00	3000
      7900	30	950.00	
      7654	30	1250.00	950
      7521	30	1250.00	1250
      7844	30	1500.00	1250
      7499	30	1600.00	1500
      7698	30	2850.00	1600

• SUM also supports windowing:

1
2
3
4
5
6

SELECT e.empno,
       e.deptno,
       e.sal,
       sum(sal) OVER(partition by deptno ORDER BY sal) dept_accum_sal
  FROM emp e
 order by deptno, sal

Others

Database link

1
2
3
4
5
6
7

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'tns_service_name'

1
2
3

-- user和password都不需要引号，如果passwrd有特殊字符，那就用双引号""包起来
create public database link dbl_term connect to user1
identified by "password1" using '192.168.100.100/orcl'

Find all database links:

• DBA_DB_LINKS - All DB links defined in the database
• ALL_DB_LINKS - All DB links the current user has access to
• USER_DB_LINKS - All DB links owned by current user

INSERT MULTIPLE ROWS

• Use INSERT ALL to insert multiple rows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE employee (
  emp_id NUMBER(5),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  dept_id NUMBER(5),
  manager_id NUMBER(5),
  office_id NUMBER(5)
);
 
INSERT ALL
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (1, 'Sally', 'Jones', 3, 2, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (2, 'Mark', 'Smith', 2, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (3, 'John', 'Andrews', 1, 4, 3)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (4, 'Michelle', 'Johnson', 2, NULL, 5)
INTO employee (emp_id, first_name, last_name, dept_id, manager_id, office_id) VALUES (5, 'Brian', 'Grand', 2, 2, 3)
SELECT * FROM dual;

MD5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE FUNCTION MD5(

                               passwd IN VARCHAR2)

 RETURN VARCHAR2

 IS

  retval varchar2(32);

BEGIN

  retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd));

  RETURN retval;

END;

Tools

Regexper: Visulization tool for Regexp
Regex101: Write, test and debug Regexp

Syntax

Vue 3

Refs

1. Composition API - An animated explanation
2. Vue2 to Vue3 — What’s changed?
3. Why vite ?
4. A definitive guide to Vue 3 components
5. ViteJS
6. What is the difference between “vite” and “vite preview”?

What is redis

An distributed in-memory key-value database.
distributed: redis can scale from a single instance to a distributed system.
in-memory: that’s why redis is fast and commonly serves as cache.
key-value: redis stores key-value pairs.

Why redis

As an in-memory cache, redis is much faster than traditional RDBMS.
Helps reduce database workload.

Install

Server: See offical install procedure here.
As no offical windows install file provided, tporadowski provides an unoffical install file.

Client: redis server ships with a cli tool redis-cli. For GUI client, check RedisInsight out.

Config

Difference between redis.windows-service.conf and redis.windows.conf

redis.windows-service.conf: Configuration file for windows service.
reids.windows.conf: Configuration file for command line.

Security

Default redis requires no auth, but cannot access from machine other than localhost.
To enable remote access (only tested on windows):
In file redis.windows-service.conf:

1. Comment bind 127.0.0.1(line 64). This makes redis listen to all interfaces.
2. Change procted-mode yes to protected-mode no(line 83). All clients are now able to connect in.
3. Uncomment requirepass foobared(line 503) and replace foobared to your own password.

Use

Different types

Commands

redis-cli: connects to localhost on port 6379.
redis-cli -h [host] -p [port] -a [password]: connect to specified host and port. If password contains special character, wrap it in quotes.

node redis

Note that redis does not work in browser environment.