django 4

python manage.py shell

from music.models import Album, Song

>>> a = Album(artist='Taylor Swift', album_title='Red', genre='Country', album_logo='https://www.google.com/imgres?imgur
l=https%3A%2F%2Ff4.bcbits.com%2Fimg%2F0012335631_10.jpg&imgrefurl=https%3A%2F%2Ftunes.mamabirdrecordingco.com%2Falbum%2F
i-need-to-start-a-garden&docid=D9UBJ_P_Bkn6RM&tbnid=82ronHe4dPoZtM%3A&vet=10ahUKEwiVm7XLjdrgAhXLqZ4KHbPUBDUQMwiMASgVMBU.
.i&w=1200&h=900&bih=583&biw=971&q=album&ved=0ahUKEwiVm7XLjdrgAhXLqZ4KHbPUBDUQMwiMASgVMBU&iact=mrc&uact=8')
>>> Album.objects.all()
<QuerySet []>
>>> a.save()
>>> Album.objects.all()
<QuerySet [<Album: Album object (1)>]>
>>> a.artist
'Taylor Swift'
>>> a.album_title
'Red'
>>> a.id
1
>>>

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

>>> b = Album()
>>> b.artist = 'Myth'
>>> b.album_title = 'High School'
>>> b.genre = 'punk'
>>> b.album_logo = 'https://www.google.com/imgres?imgurl=https%3A%2F%2Fwww.graphistudio.com%2Fwp-content%2Fuploads%2F201
8%2F01%2Fluce_up.png&imgrefurl=https%3A%2F%2Fwww.graphistudio.com%2Fen%2Fthe-digital-matted-album%2F&docid=XWMXDGCi9Ureh
M&tbnid=qJPwFHsm06zxDM%3A&vet=10ahUKEwiVm7XLjdrgAhXLqZ4KHbPUBDUQMwiOASgXMBc..i&w=956&h=764&bih=583&biw=971&q=album&ved=0
ahUKEwiVm7XLjdrgAhXLqZ4KHbPUBDUQMwiOASgXMBc&iact=mrc&uact=8'
>>> b.save()
>>> b.album_logo
'https://www.google.com/imgres?imgurl=https%3A%2F%2Fwww.graphistudio.com%2Fwp-content%2Fuploads%2F2018%2F01%2Fluce_up.pn
g&imgrefurl=https%3A%2F%2Fwww.graphistudio.com%2Fen%2Fthe-digital-matted-album%2F&docid=XWMXDGCi9UrehM&tbnid=qJPwFHsm06z
xDM%3A&vet=10ahUKEwiVm7XLjdrgAhXLqZ4KHbPUBDUQMwiOASgXMBc..i&w=956&h=764&bih=583&biw=971&q=album&ved=0ahUKEwiVm7XLjdrgAhX
LqZ4KHbPUBDUQMwiOASgXMBc&iact=mrc&uact=8'
>>>

Silicon Valley Girl

django 3

music/models.py

from django.db import models

class Album(models.Model):

    artist = models.CharField(max_length=50)

    album_title = models.CharField(max_length=50)

    genre = models.CharField(max_length=50)

    album_logo = models.CharField(max_length=500)

class Song(models.Model):

    album = models.ForeignKey(Album, on_delete=models.CASCADE)

    file_type = models.CharField(max_length=50)

    song_title = models.CharField(max_length=50)

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

#sync model with database
python manage.py migrate

#update model
python manage.py makemigrations music

music\migrations\0001_initial.py
  - Create model Album
  - Create model Song

python manage.py sqlmigrate music 0001

BEGIN;
--
-- Create model Album
--
CREATE TABLE "music_album" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "artist" varchar(50) NOT NULL, "album_title
" varchar(50) NOT NULL, "genre" varchar(50) NOT NULL, "album_logo" varchar(500) NOT NULL);
--
-- Create model Song
--
CREATE TABLE "music_song" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "file_type" varchar(50) NOT NULL, "song_titl
e" varchar(50) NOT NULL, "album_id" integer NOT NULL REFERENCES "music_album" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE INDEX "music_song_album_id_62a413c8" ON "music_song" ("album_id");
COMMIT;

python manage.py migrate

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, music, sessions
Running migrations:
  Applying music.0001_initial… OK

django 2

project1\urls.py

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('music/', include('music.urls')),
    path('watchman/', include('watchman.urls')),
]

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

music\urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]

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

music\views.py

from django.shortcuts import render

from django.http import  HttpResponse

def index(request):

    return HttpResponse('<h1>music app homepage</h1>')

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

project1\settings.py

INSTALLED_APPS = [

    'django.contrib.admin',

    'django.contrib.auth',

    'django.contrib.contenttypes',

    'django.contrib.sessions',

    'django.contrib.messages',

    'django.contrib.staticfiles',

    'watchman',

]

django 1

python --version
Python 3.7.2

easy_install django
Installed

django-admin --version
2.2b1

#easy_install pywatchman
#error: Setup script exited with error: Microsoft Visual C++ 14.0

pip install django-watchman

INSTALLED_APPS = (
    ...
    'watchman',
)

urlpatterns = [
    path('admin/', admin.site.urls),
    path('music/', include('music.urls')),
    path('watchman/', include('watchman.urls')),
]

http://127.0.0.1:8000/watchman/
{"databases": [{"default": {"ok": true}}], "caches": [{"default": {"ok": true}}], "storage": {"ok": true}}

http://127.0.0.1:8000/watchman/dashboard/

python -m pip install --upgrade pip
Successfully installed pip-19.0.3

C:\Users\chuanshuo\Desktop> django-admin startproject project1
cd project1
ls

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        25/02/2019   1:17 PM            .idea
d----        25/02/2019   1:00 PM            project1
-a---        25/02/2019   1:00 PM        649 manage.py

python manage.py runserver

python manage.py startapp music

reference:
https://www.youtube.com/watch?v=qgGIqRFvFFk&index=1&list=PL6gx4Cwl9DGBlmzzFcLgDhKTTfNLfX1IK
https://wiki.python.org/moin/WindowsCompilers
https://developercommunity.visualstudio.com/content/problem/409173/error-microsoft-visual-c-140-is-required.html
https://stackoverflow.com/questions/43858836/python-installing-clarifai-vs14-0-link-exe-failed-with-exit-status-1158
https://github.com/mwarkentin/django-watchman

python try except

try:
    age = int(input('Age: '))
    income = 20000
    risk = income / age
    print(risk)
except ZeroDivisionError:
    print('Age cannot be 0')
except ValueError:
    print('Invalid value')

Age: u
Invalid value

Age: 0
Age cannot be 0

Age: 10
2000.0

python dictionary

customer = {
    'name': 'sam',
    'age': 30,
    'employed': True
}

print(customer['name'])
print(customer.get('birthdate', 'Jan 1 1980'))

print(customer['birthdate'])

customer['name'] = 'Jack'
print(customer['name'])

customer['birthdate'] = 'Dec 31, 1979'
print(customer['birthdate'])

sam
Jan 1 1980
Jack

Traceback (most recent call last):
  File "c:\Users\bob\python1\.vscode\tempCodeRunnerFile.py", line 9, in <module>
    print(customer['birthdate'])
KeyError: 'birthdate'

Dec 31, 1979

python filter, map

items = [
    ('product1', 10),
    ('product2', 9),
    ('product3', 12),
]

filtered = list(filter(lambda item: item[1] >= 10, items))
print(filtered)

[('product1', 10), ('product3', 12)]

items = [
    ('product1', 10),
    ('product2', 9),
    ('product3', 12),
]

prices = list(map(lambda item: item[1], items))
print(prices)

[10, 9, 12]

python string

message = """
Hi bob,

congratulate on your work anniversary.

sent from linkedin
"""

print(message)

Hi bob,

congratulate on your work anniversary.

sent from linkedin

message = "python \"string"
print(message)

python "string

message = "python \nstring"
print(message)

python
string

a = 'python'
b = 'string'
print(f'{a} {b}')

python string

a = 'python'
b = 'string'
print(a + ' ' + b)

python string

subject = ' python string '
print(subject.upper())
print(subject.lower())
print(subject.title())
print(subject.rstrip())
print(subject.lstrip())
print(subject.strip())
print(subject.find('str'))
print(subject.replace('n', '*'))
print('str' in subject)

 PYTHON STRING
 python string
 Python String
 python string
python string
python string
8
 pytho* stri*g
True

python lists

letters = ['a', 'b', 'c']
matrix = [[0, 1], [2, 3]]

zeros = [0]*5
combined = zeros + letters
print(combined)

[0, 0, 0, 0, 0, 'a', 'b', 'c']

print(list(range(10)))

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

print(list('hello world'))

['h', 'e', 'l', 'l', 'o', ' ', 'w', 'o', 'r', 'l', 'd']

print(len(list('hello world')))

11

letters = ['a', 'b', 'c']
print(letters[-1])

c

letters = ['a', 'b', 'c']
print(letters[0:2])

['a', 'b']

letters = ['a', 'b', 'c', 'd', 'e', 'f']
print(letters[::2])

['a', 'c', 'e']

letters = ['a', 'b', 'c', 'd', 'e', 'f']
print(letters[1::2])

['b', 'd', 'f']

letters = ['a', 'b', 'c', 'd', 'e', 'f']
print(letters[::-2])

['f', 'd', 'b']

letters = ['a', 'b', 'c']
first, second, third = letters
print(first, second, third)

a b c

letters = ['a', 'b', 'c', 'd', 'e', 'f']
first, second, *other = letters
print(first, other)

a ['c', 'd', 'e', 'f']

letters = ['a', 'b', 'c', 'd', 'e', 'f']
first, *other, last = letters
print(first, other, last)

a ['b', 'c', 'd', 'e'] f

letters = ['a', 'b', 'c']
for letter in enumerate(letters):
    print(letter)

(0, 'a')
(1, 'b')
(2, 'c')

letters = ['a', 'b', 'c']
for letter in enumerate(letters):
    print(letter[0], letter[1])

0 a
1 b
2 c

letters = ['a', 'b', 'c']
for index, letter in enumerate(letters):
    print(index, letter)

0 a
1 b
2 c

letters = ['a', 'b', 'c']
letters.append('d')
letters.insert(1, '-')
print(letters)

['a', '-', 'b', 'c', 'd']

letters = ['a', 'b', 'c', 'd']
letters.pop()
print(letters)

['a', 'b', 'c']

letters = ['a', 'b', 'c', 'd']
letters.pop(1)
print(letters)

['a', 'c', 'd']

letters = ['a', 'b', 'c', 'd']
letters.remove('c')
print(letters)

['a', 'b', 'd']

letters = ['a', 'b', 'c', 'd']
del letters[1:3]
print(letters)

['a', 'd']

letters = ['a', 'b', 'c', 'd']
letters.clear()
print(letters)

[]

letters = ['a', 'b', 'c', 'd']
print(letters.index('c'))

2

letters = ['a', 'b', 'c', 'b', 'd']
if 'b' in letters:
    print(letters.count('b'))

2

numbers = [3,2,1,4,5]
numbers2 = numbers.copy()
numbers2.sort()
numbers2.reverse()
print(numbers2)

[5, 4, 3, 2, 1]

numbers = [2,2,4,6,3,4,6,1]
uniques = []
for number in numbers:
    if number not in uniques:
        uniques.append(number)
print(uniques)

[2, 4, 6, 3, 1]

reference:
https://www.youtube.com/watch?v=9OeznAkyQz4

Python Functions - def, *args and **kwargs

def pirnt_num(*numbers):
    print(numbers)

pirnt_num(2,3,4,5)

(2, 3, 4, 5)

def save_user(**user):
    print(user)

save_user(id=1, name='John', age=22)

{'id': 1, 'name': 'John', 'age': 22}

Cities: Skylines 都市：天际线

build in phone # functions

postgres tutorial 18 tablespace

A tablespace is a location on disk where PostgreSQL stores data files containing database objects e.g., indexes., and tables. PostgreSQL uses a tablespace to map a logical name to a physical location on disk.

CREATE TABLESPACE demo LOCATION 'c:/data/demo';

create a new database named dbdemoand set its tablespace to demo:

CREATE DATABASE dbdemo TABLESPACE = demo;

create a new table named testin the dbdemoand set it tablespaceto demo:

CREATE TABLE test (
 ID serial PRIMARY KEY,
 title VARCHAR (255) NOT NULL
) TABLESPACE demo;

ALTER DATABASE dbdemo
SET TABLESPACE = pg_default;

DROP TABLESPACE demo;

postgres tutorial 17 role

CREATE ROLE doe WITH PASSWORD 'pgSecpas1970' VALID UNTIL '2020-01-01';

role can bypass all authorization checks

CREATE ROLE bigboss SUPERUSER;

role to have database creation privilege

CREATE ROLE admin CREATEDB;

role that has creation privilege

CREATE ROLE security CREATEROLE;

grant membership in the group role to individual user roles.

CREATE ROLE sales;
GRANT sales TO doe;
REVOKE sales FROM doe;

Group and user role inheritance

CREATE ROLE doe LOGIN INHERIT;
CREATE ROLE sales NOINHERIT;
CREATE ROLE marketing NOINHERIT;
GRANT sales to doe;
GRANT marketing to sales;

doe has privilege from sales, but doesn't have privilege from marketing.
sales doesn't have privilege from marketing.
because marketing is none inherit

restore the original privilege

RESET ROLE;

he LOGIN, SUPERUSER, CREATEROLE, and CREATEDB are the special role that cannot be inherited

Removing roles

DROP ROLE role_name;

postgres tutorial 16 index

EXPLAIN SELECT * FROM address WHERE phone = '223664661973';

database engine had to scan the whole address table to look for the address because there is no index available for the phone column.

CREATE INDEX idx_address_phone ON address(phone);

EXPLAIN SELECT * FROM address WHERE phone = '223664661973';

CREATE UNIQUE INDEX idx_employees_mobile_phone
ON employees(mobile_phone);

CREATE INDEX idx_ic_last_name
ON customer(LOWER(last_name));

CREATE INDEX idx_customer_inactive
ON customer(active)
WHERE active = 0;

CREATE INDEX idx_people_names
ON people (last_name, first_name);

postgres tutorial 15 view

CREATE VIEW customer_master AS
  SELECT cu.customer_id AS id,
    cu.first_name || ' ' || cu.last_name AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'
            ELSE ''
        END AS notes,
    cu.store_id AS sid,
    cu.email
   FROM customer cu
     INNER JOIN address a USING (address_id)
     INNER JOIN city USING (city_id)
     INNER JOIN country USING (country_id);

SELECT
 *
FROM
 customer_master;

ALTER VIEW customer_master RENAME TO customer_info;

DROP VIEW IF EXISTS customer_info;

postgres tutorial 14 trigger

The INSTEAD OF is used only for INSERT, UPDATE, or DELETE on the views.

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
   ON table_name
   [FOR [EACH] {ROW | STATEMENT}]
   EXECUTE PROCEDURE trigger_function
 
checks if the last name of employee changes, it will insert the old last name into the employee_audits table including employee id, last name, and the time of change.

CREATE TABLE employees(
   id serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL
);

CREATE TABLE employee_audits (
   id serial primary key,
   employee_id int4 NOT NULL,
   last_name varchar(40) NOT NULL,
   changed_on timestamp(6) NOT NULL
)

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS trigger AS $$
BEGIN
 IF NEW.last_name <> OLD.last_name THEN
 INSERT INTO employee_audits(employee_id,last_name,changed_on)
 VALUES(OLD.id,OLD.last_name,now());
 END IF;

 RETURN NEW;
END; $$
LANGUAGE plpgsql;

bind the trigger function to the employees table.

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

INSERT INTO employees (first_name, last_name)
VALUES ('Lily', 'Bush');

employees

UPDATE employees
SET last_name = 'Brown'
WHERE ID = 2;

employees

employee audit

ALTER TRIGGER last_name_changes ON employees
RENAME TO log_last_name_changes;

ALTER TABLE employees
DISABLE TRIGGER log_last_name_changes;

ALTER TABLE employees
DISABLE TRIGGER ALL;

DROP TRIGGER log_last_name_changes ON employees;

网上房屋买卖兴起

美国互联网房地产买卖经纪公司Redfin这个星期进入加拿大市场参加房地产经纪行业的竞争，该公司的杀手锏是只收1%的房地产买卖经纪费，既在网上提供大量的房地产信息和3D视频看房服务、也提供房地产经纪人服务，不过其房地产经纪人由公司发工资而不是挣客户的佣金。

　　加拿大传统的房地产经纪人公司为卖房者售出房产一般要收取5%的佣金，75万的房产则佣金是37,500加元，如果买方与卖方的经纪人不同，则两个经纪人平分这5%的佣金。而Redfin公司只收1%的佣金，这就为卖房者省下少则1.5%、多则4%的佣金。
　　Redfin这个2004年在美国西雅图成立的新型房地产经纪公司的经营模式是利用网络科技提供物美价廉的房屋买卖服务，从而挑战房地产经纪领域传统的服务模式。

　　Redfin公司首席执行官Glenn Kelman说，他们公司在美国做到了让卖房者口袋里得到更多的钱、房屋卖出的速度更快，希望能在加拿大市场取得同样的成果。

　　Redfin公司2月中旬先在加拿大最大城市多伦多开办了分支，随后在今年春天会在温哥华开设分支。

　　分析师们指出，Redfin公司虽然通过2017年上市卖股票获得了大笔融资、达到17亿美元的市值，但在美国市场还没有实现盈利，需要有更多的客户才能实现盈利。

　　不过，在Redfin进军加拿大市场前，已经有两个网络房地产经纪公司打入了加拿大市场，一个是英国不收取佣金费用的网上房地产买卖公司Purplebricks，另一个是美国的网上房屋挂牌和手机APP房屋买卖服务公司 Zillow。

　　加拿大多伦多大学商学院教授Will Strange认为，加拿大房地产经纪人市场已经准备好迎接革命性的变化。

　　房地产经纪人市场的变革之风始于美国，那里现在已是以网络为基础的新型房地产经纪公司百花齐放的局面。

　　有提供快速卖房的iBuyers服务，比如美国的Opendoor公司和Offerpad公司。提供这种快速卖房服务的公司用功能强大的电脑软件分析得出目标房产的市场价值，给卖房者初始报价，随后派出验房师检查房屋，然后再报出最后出价，这种模式一般能在一个星期内让卖房者卖掉房产。这种服务模式的好处是快速完成卖房过程、卖房者自己选择搬家的日期；不那么好的地方是要缴纳低则6%、高则13%的佣金。

　　iBuyers服务的一个变种是房屋置换服务，比如Knock公司。 该公司先为卖房者买下其中意的新房，然后对其准备出手的旧房进行必要的装修后上市出售。该公司两位创始人之一的Sean Black指出，美国房地产市场购房者中有至少70%的人同时要出售自己的旧房，对于这些人来说先买房还是先卖房是让他们纠结苦恼的问题；Knock公司的服务正好是为他们排难解忧，而公司得到的回报是对买的新房和卖的旧房各收取3%的佣金。

postgres tutorial 13 procedure

CREATE OR REPLACE PROCEDURE transfer(INT, INT, DEC)
LANGUAGE plpgsql   
AS $$
BEGIN
    -- subtracting the amount from the sender's account
    UPDATE accounts
    SET balance = balance - $3
    WHERE id = $1;

    -- adding the amount to the receiver's account
    UPDATE accounts
    SET balance = balance + $3
    WHERE id = $2;

    COMMIT;
END;
$$;

CALL transfer(1,2,1000);

postgres tutorial 12 cursor

CREATE OR REPLACE FUNCTION get_film_titles(p_year INTEGER)
   RETURNS text AS $$
DECLARE
 titles TEXT DEFAULT '';
 rec_film   RECORD;
 cur_films CURSOR(p_year INTEGER)
 FOR SELECT title, release_year
 FROM film
 WHERE release_year = p_year;
BEGIN
   -- Open the cursor
   OPEN cur_films(p_year);

   LOOP
    -- fetch row into the film
      FETCH cur_films INTO rec_film;
    -- exit when no more row to fetch
      EXIT WHEN NOT FOUND;

    -- build the output
      IF rec_film.title LIKE '%ful%' THEN
         titles := titles || ',' || rec_film.title || ':' || rec_film.release_year;
      END IF;
   END LOOP;
 
   -- Close the cursor
   CLOSE cur_films;

   RETURN titles;
END; $$

LANGUAGE plpgsql;

-------------------------------------
By default, a cursor gets the next row if you don’t specify the direction explicitly. The following is valid for the  cursor:

MOVE cur_films2;
MOVE LAST FROM cur_films;
MOVE RELATIVE -1 FROM cur_films;
MOVE FORWARD 3 FROM cur_films;

UPDATE table_name
SET column = value, ...
WHERE CURRENT OF cursor_variable;

DELETE FROM table_name
WHERE CURRENT OF cursor_variable;

postgres tutorial 11 functions

CREATE FUNCTION function_name(p1 type, p2 type)
 RETURNS type AS
BEGIN
 -- logic
END;
LANGUAGE language_name;

CREATE OR REPLACE FUNCTION get_sum(
 a NUMERIC,
 b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
 RETURN a + b;
END; $$

LANGUAGE plpgsql;

select get_sum(2,3);
-----------------------------------

CREATE OR REPLACE FUNCTION hi_lo(
 a NUMERIC,
 b NUMERIC,
 c NUMERIC,
        OUT hi NUMERIC,
 OUT lo NUMERIC)
AS $$
BEGIN
 hi := GREATEST(a,b,c);
 lo := LEAST(a,b,c);
END; $$

LANGUAGE plpgsql;

SELECT hi_lo(10,20,30);

(30,10)

---------------------------------------
CREATE OR REPLACE FUNCTION square(
 INOUT a NUMERIC)
AS $$
BEGIN
 a := a * a;
END; $$
LANGUAGE plpgsql;

SELECT square(4);

--------------------
accept a variable number of arguments with one condition that all arguments have the same data type
The sum_avg() function accepts a list of numbers, calculates the total and average, and returns both values.

CREATE OR REPLACE FUNCTION sum_avg(
 VARIADIC list NUMERIC[],
 OUT total NUMERIC,
        OUT average NUMERIC)
AS $$
BEGIN
   SELECT INTO total SUM(list[i])
   FROM generate_subscripts(list, 1) g(i);

   SELECT INTO average AVG(list[i])
   FROM generate_subscripts(list, 1) g(i);

END; $$
LANGUAGE plpgsql;

SELECT * FROM sum_avg(10,20,30);
total | average
60 20

--------------------------------
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR)
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
)
AS $$
BEGIN
 RETURN QUERY SELECT
 title,
 cast( release_year as integer)
 FROM
 film
 WHERE
 title ILIKE p_pattern ;
END; $$

LANGUAGE 'plpgsql';

-------------------------
SELECT
 *
FROM
 get_film ('Al%');

---------------------
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT)
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
) AS $$
DECLARE
    var_r record;
BEGIN
 FOR var_r IN(SELECT
 title,
 release_year
                FROM film
 WHERE title ILIKE p_pattern AND
 release_year = p_year)
 LOOP
        film_title := upper(var_r.title) ;
 film_release_year := var_r.release_year;
        RETURN NEXT;
 END LOOP;
END; $$
LANGUAGE 'plpgsql';

--------------------------------
SELECT
 *
FROM
 get_film ('%er', 2006);

--------------------------------------------
CREATE OR REPLACE FUNCTION get_customer_service (p_customer_id INTEGER)
 RETURNS VARCHAR (25) AS $$
DECLARE
    total_payment NUMERIC ;
    service_level VARCHAR (25) ;
BEGIN
 -- get the rate based on film_id
     SELECT
 INTO total_payment SUM (amount)
     FROM
 payment
     WHERE
 customer_id = p_customer_id ;

   CASE
      WHEN total_payment > 200 THEN
         service_level = 'Platinum' ;
      WHEN total_payment > 100 THEN
 service_level = 'Gold' ;
      ELSE
         service_level = 'Silver' ;
   END CASE ;

   RETURN service_level ;
END ; $$
LANGUAGE plpgsql;

---------------------------------------------
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
 RETURNS INTEGER AS $$
DECLARE
   counter INTEGER := 0 ;
   i INTEGER := 0 ;
   j INTEGER := 1 ;
BEGIN

 IF (n < 1) THEN
 RETURN 0 ;
 END IF;

 LOOP 
 EXIT WHEN counter = n ;
 counter := counter + 1 ;

 SELECT j, i + j INTO i, j ;
 END LOOP ;

 RETURN i ;
END ;
$$ LANGUAGE plpgsql;

select fibonacci(6);
8

---------------------------------------
DO $$
BEGIN
  FOR counter IN 1..6 BY 2 LOOP
    RAISE NOTICE 'Counter: %', counter;
  END LOOP;
END; $$

NOTICE:  Counter: 1
NOTICE:  Counter: 3
NOTICE:  Counter: 5

DO $$
BEGIN
   FOR counter IN REVERSE 5..1 LOOP
      RAISE NOTICE 'Counter: %', counter;
   END LOOP;
END; $$

NOTICE:  Counter: 5
NOTICE:  Counter: 4
NOTICE:  Counter: 3
NOTICE:  Counter: 2
NOTICE:  Counter: 1

----------------------------------------
CREATE OR REPLACE FUNCTION for_loop_through_query(
   n INTEGER DEFAULT 10
)
RETURNS VOID AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT title
        FROM film
        ORDER BY title
        LIMIT n
    LOOP
 RAISE NOTICE '%', rec.title;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT for_loop_through_query(5);

NOTICE:  Academy Dinosaur
NOTICE:  Ace Goldfinger
NOTICE:  Adaptation Holes
NOTICE:  Affair Prejudice
NOTICE:  African Egg

postgres tutorial 10 procedure declare variable, constant, raise message

 postgreSQL evaluates the default values and set them for the variables when declared

DO $$ 

DECLARE

   counter    INTEGER := 1;

   first_name CONSTANT  VARCHAR(50) := 'John';

   last_name CONSTANT   VARCHAR(50) := 'Doe';

   payment    NUMERIC(11,2) := 20.5;

   created_at time := NOW();

BEGIN 

   RAISE NOTICE '% % % has been paid % USD @ %', counter, first_name, last_name, payment, created_at;

   PERFORM pg_sleep(1);

   counter := counter + 1;

   payment := 25;

   RAISE NOTICE '% correction % % has been paid % USD @ %', counter,  first_name, last_name, payment, created_at;

END $$;

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

NOTICE:  1 John Doe has been paid 20.50 USD @ 21:39:00.540055

NOTICE:  2 correction John Doe has been paid 25.00 USD @ 21:39:00.540055

DO

Query returned successfully in 1 secs 24 msec.

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

define a variable named city_name with the same data type as name the name column of the city table

city_name city.name%TYPE := 'San Francisco';

define an alias for any variable as follows:

new_name ALIAS FOR old_name;

----------------------------------
RAISE statement

DO $$
BEGIN
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

DO $$
DECLARE
 email varchar(255) := 'info@postgresqltutorial.com';
BEGIN
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email
 USING HINT = 'Check the email again';
END $$;

If the condition evaluates to TRUE, ASSERT statement does nothing. If the condition evaluates to FALSE or NULL, the ASSERT_FAILURE is raised.

DO $$
DECLARE
 counter integer := -1;
BEGIN
   ASSERT counter = 0
   MESSAGE 'Expect counter starts with 0';
END $$;

postgres tutorial 9 row_number, rank, first_value, last_value, lag, lead

SELECT
 product_name,
 price,
 group_name,
 AVG (price) OVER (PARTITION BY group_name)
FROM
 products
INNER JOIN product_groups USING (group_id);

------------------------------
ROW_NUMBER() function assigns a running serial number to rows in each partition.

SELECT
 product_name,
 group_name,
 price,
 ROW_NUMBER () OVER (
 PARTITION BY group_name
 ORDER BY
 price
 )
FROM
 products
INNER JOIN product_groups USING (group_id);

-----------------------------------
The RANK() function assigns ranking within an ordered partition.  If the values of the two rows are the same, the  RANK() function assigns the same rank, with the next ranking(s) skipped.

SELECT
 product_name,
 group_name,
  price,
 RANK () OVER (
 PARTITION BY group_name
 ORDER BY
 price
 )
FROM
 products
INNER JOIN product_groups USING (group_id);

------------------------------
SELECT
 product_name,
 group_name,
 price,
 FIRST_VALUE (price) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS lowest_price_per_group
FROM
 products
INNER JOIN product_groups USING (group_id);

----------------------------------
SELECT
 product_name,
 group_name,
 price,
 LAST_VALUE (price) OVER (
 PARTITION BY group_name
 ORDER BY
 price RANGE BETWEEN UNBOUNDED PRECEDING
 AND UNBOUNDED FOLLOWING
 ) AS highest_price_per_group
FROM
 products
INNER JOIN product_groups USING (group_id);

-----------------------------
LAG() function to return the prices from the previous row

SELECT
 product_name,
 group_name,
 price,
 LAG (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS prev_price,
 price - LAG (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS cur_prev_diff
FROM
 products
INNER JOIN product_groups USING (group_id);

--------------------------------
LEAD() function to return the prices from the next row

SELECT
 product_name,
 group_name,
 price,
 LEAD (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS next_price,
 price - LEAD (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS cur_next_diff
FROM
 products
INNER JOIN product_groups USING (group_id);

postgres tutorial 8 with, recursive

Common Table Expressions only exist during the execution of the query.

WITH cte_film AS (
    SELECT
        film_id,
        title,
        (CASE
            WHEN length < 30 THEN 'Short'
            WHEN length >= 30 AND length < 90 THEN 'Medium'
            WHEN length > 90 THEN 'Long'
        END) length   
    FROM
        film
)
SELECT
    film_id,
    title,
    length
FROM
    cte_film
WHERE
    length = 'Long'
ORDER BY
    title;

-----------------------------------
INSERT INTO employees (
 employee_id,
 full_name,
 manager_id
)
VALUES
 (1, 'Michael North', NULL),
 (2, 'Megan Berry', 1),
 (3, 'Sarah Berry', 1),
 (4, 'Zoe Black', 1),
 (5, 'Tim James', 1),
 (6, 'Bella Tucker', 2),
 (7, 'Ryan Metcalfe', 2),
 (8, 'Max Mills', 2),
 (9, 'Benjamin Glover', 2),
 (10, 'Carolyn Henderson', 3),
 (11, 'Nicola Kelly', 3),
 (12, 'Alexandra Climo', 3),
 (13, 'Dominic King', 3),
 (14, 'Leonard Gray', 4),
 (15, 'Eric Rampling', 4),
 (16, 'Piers Paige', 7),
 (17, 'Ryan Henderson', 7),
 (18, 'Frank Tucker', 8),
 (19, 'Nathan Ferguson', 8),
 (20, 'Kevin Rampling', 8);

 WITH RECURSIVE subordinates AS (
 SELECT
 employee_id,
 manager_id,
 full_name
 FROM
 employees
 WHERE
 employee_id = 2
 UNION
 SELECT
 e.employee_id,
 e.manager_id,
 e.full_name
 FROM
 employees e
 INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
 *
FROM
 subordinates;

first round

 employee_id | manager_id |  full_name
-------------+------------+-------------
           2 |          1 | Megan Berry
 
second round

 employee_id | manager_id |    full_name
-------------+------------+-----------------
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover
 
third round

employee_id | manager_id |    full_name
-------------+------------+-----------------
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling
 
union all

employee_id | manager_id |    full_name
-------------+------------+-----------------
           2 |          1 | Megan Berry
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling
(10 rows)

postgres tutorial 7 case, coalesce, nullif, cast

mass if the rental rate is 0.99
economic if the rental rate is 1.99
luxury if the rental rate is 4.99
the number of films that belongs mass, economic or luxury price

SELECT
 SUM (
 CASE
 WHEN rental_rate = 0.99 THEN
 1
 ELSE
 0
 END
 ) AS "Mass",
 SUM (
 CASE
 WHEN rental_rate = 2.99 THEN
 1
 ELSE
 0
 END
 ) AS "Economic",
 SUM (
 CASE
 WHEN rental_rate = 4.99 THEN
 1
 ELSE
 0
 END
 ) AS "Luxury"
FROM
 film;

 ---------------------------
 COALESCE function returns the first argument that is not null.

 SELECT
 product,
 (price - COALESCE(discount,0)) AS net_price
FROM
 items;

 =

 SELECT
 product,
 (
 price - CASE
 WHEN discount IS NULL THEN
 0
 ELSE
 discount
 END
 ) AS net_price
FROM
 items;

 ------------------------------
 The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.

SELECT
 NULLIF (1, 1); -- return NULL

SELECT
 NULLIF (1, 0); -- return 1

SELECT
 NULLIF ('A', 'B'); -- return A

 ---------------------------
SELECT
 CAST ('100' AS INTEGER);

SELECT
 CAST ('2015-01-01' AS DATE);

SELECT
 CAST ('10.2' AS DOUBLE PRECISION);

 SELECT
 '100'::INTEGER;

SELECT
 '01-OCT-2015'::DATE;

postgres tutorial 6 select into, create as

SELECT
    column_list
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name
FROM
    table_name
WHERE
    condition;

creates a new table named film_r

SELECT
    film_id,
    title,
    rental_rate
INTO TABLE film_r
FROM
    film
WHERE
    rating = 'R'
AND rental_duration = 5
ORDER BY
    title;

SELECT
    *
FROM
    film_r;

 creates a temporary table

 SELECT
    film_id,
    title,
    length
INTO TEMP TABLE short_film
FROM
    film
WHERE
    length < 60
ORDER BY
    title;

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

CREATE TEMP TABLE new_table_name
AS query;

CREATE TABLE IF NOT EXISTS film_rating (rating, film_count)
AS 
SELECT
    rating,
    COUNT (film_id)
FROM
    film
GROUP BY
    rating;

postgres tutorial 5 delete/update join, upsert

delete data of a table based on values in another table

DELETE FROM table
USING another_table
WHERE table.id = another_table.id AND …

DELETE FROM link
USING link_tmp
WHERE
    link.id = link_tmp.id;

=

DELETE FROM table
WHERE table.id = (SELECT id FROM another_table);

--------------------------
UPDATE join - update data of a table based on values in another table

UPDATE A
SET A.c1 = expresion
FROM B
WHERE A.c2 = B.c2;

UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;

------------------------------------
upsert -  to insert or update data if the row that is being inserted already exists in the table.

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

#SELECT * FROM customers;
 customer_id |   name    |         email         | active
-------------+-----------+-----------------------+--------
           1 | IBM       | contact@ibm.com       | t
           2 | Microsoft | contact@microsoft.com | t
           3 | Intel     | contact@intel.com     | t
(3 rows)

INSERT INTO customers (name, email)
VALUES
 (
 'Microsoft',
 'hotline@microsoft.com'
 )
ON CONFLICT (name)
DO NOTHING;

 concatenate the new email with the old email when inserting a customer that already exists,

INSERT INTO customers (name, email)
VALUES
 (
 'Microsoft',
 'hotline@microsoft.com'
 )
ON CONFLICT (name)
DO
 UPDATE
   SET email = EXCLUDED.email || ';' || customers.email;

#SELECT * FROM customers;
 customer_id |   name    |                    email                    | active
-------------+-----------+---------------------------------------------+--------
           1 | IBM       | contact@ibm.com                             | t
           3 | Intel     | contact@intel.com                           | t
           2 | Microsoft | hotline@microsoft.com;contact@microsoft.com | t
(3 rows)

夜空中最亮的星 The Brightest Star In The Sky

postgres tutorial 4 subquery

Find customers who have at least one payment whose amount is greater than 11.

SELECT first_name,
       last_name
FROM customer c
WHERE EXISTS
    (SELECT 1
     FROM payment p
     WHERE p.customer_id = c.customer_id
       AND amount > 11 )
ORDER BY first_name,
         last_name;

If the subquery returns NULL, EXISTS returns true.

------------------
get films that have the returned date between 2005-05-29 and 2005-05-30

SELECT
 film_id,
 title
FROM
 film
WHERE
 film_id IN (
 SELECT
 inventory.film_id
 FROM
 rental
 INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
 WHERE
 return_date BETWEEN '2005-05-29'
 AND '2005-05-30'
 );

-------------------------------
finds the films whose lengths are greater than or equal to the maximum length of any film category :

SELECT title
FROM film
WHERE length >= ANY(
    SELECT MAX( length )
    FROM film
    INNER JOIN film_category USING(film_id)
    GROUP BY  category_id );


The = ANY is equivalent to IN operator.

SELECT
    title,
    category_id
FROM
    film
INNER JOIN film_category
        USING(film_id)
WHERE
    category_id = ANY(
        SELECT
            category_id
        FROM
            category
        WHERE
            NAME = 'Action'
            OR NAME = 'Drama'
    );

-------------------------------------------
column_name > ALL (subquery) the expression evaluates to true if a value is greater than the biggest value returned by the subquery.
column_name >= ALL (subquery) the expression evaluates to true if a value is greater than or equal to the biggest value returned by the subquery.
column_name < ALL (subquery) the expression evaluates to true if a value is less than the smallest value returned by the subquery.
column_name <= ALL (subquery) the expression evaluates to true if a value is less than or equal to the smallest value returned by the subquery.
column_name = ALL (subquery) the expression evaluates to true if a value is equal to any value returned by the subquery.
column_name != ALL (subquery) the expression evaluates to true if a value is not equal to any value returned by the subquery

SELECT
    film_id,
    title,
    length
FROM
    film
WHERE
    length > ALL (
            SELECT
                ROUND(AVG (length),2)
            FROM
                film
            GROUP BY
                rating
    )
ORDER BY
    length;

postgres tutorial 3 grouping

SELECT
 GROUPING(brand) grouping_brand,
 GROUPING(segment) grouping_segement,
 brand,
 segment,
 SUM (quantity)
FROM
 sales
GROUP BY
 GROUPING SETS (
 (brand, segment),
 (brand),
 (segment),
 ()
 )
ORDER BY
 brand,
 segment;

CUBE (c1,c2,c3) makes all eight possible grouping sets:

(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1,c3)
(c1)
(c2)
(c3)
()

However, the ROLLUP(c1,c2,c3) generates only four grouping sets, assuming the hierarchy c1 > c2 > c3 as follows:

(c1, c2, c3)
(c1, c2)
(c1)
()

SELECT
    EXTRACT (YEAR FROM rental_date) y,
    EXTRACT (MONTH FROM rental_date) M,
    EXTRACT (DAY FROM rental_date) d,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    ROLLUP (
        EXTRACT (YEAR FROM rental_date),
        EXTRACT (MONTH FROM rental_date),
        EXTRACT (DAY FROM rental_date)
    );

一起走到/We Will Get There (孙燕姿/Stefanie Sun)

postgres tutorial 2

加拿大6大福利

1. 带薪假期

加拿大的法律规定：全职员工每年可以获得两周(十天)的带薪假期。连续工作五年以后，可以获得三周的有薪假期。

2. CPP

CPP是加拿大退休金计划, 最早可以在你60岁的时候申请. 当然你也可以65、70岁再申请，越晚申请拿到的金额会越多。

2017年CPP的最高领取额度是$1,114.17元/月，平均领取额度为$653.27元/月，不过大部分人都拿不到最高额度。

政府规定，要想领取满额的CPP，必须在18岁到65岁之间(47年)，有83% 的时间 (39年) 都在向政府缴纳CPP。也就是说，必须至少有39年都在工作，都在给自己的CPP供款(25岁开始工作，一直到64岁才能拿到，想想都累了)，不然退休时就拿不满。

3. 失业保险

以下这些人都不算失业：

　　1. 主动辞职

　　2. 犯了错误被老板开除

　　总的来说，就是因为个人原因而失去工作的人，都不算失业!

那么什么人可以算是失业呢?

　　由于工作短缺、季节性工作、或大量裁员所导致的失业，或者是因为生病、生孩子而不能继续工作的人，才可以领失业保险金。

　　如果符合失业的标准，那么可以向政府申请失业金。失业金是按照你以前工资标准的55%,并且可以连续领取十个月左右。

4. 产假

从去年12月3日开始，加拿大产后假期延至18个月，产前假期增至预产期前12周。这样算下来，在加拿大生个孩子你可以有1年9个月不上班不说，还能拿到最多高达2万加币的产假薪酬津贴。

在休产假期间，雇主必须要保留你的职位。如果公司待遇好的话，你还会获得产假津贴Top Up(补足制)，也就是说，公司会补齐政府支付的津贴和实际收入相差的部分。

　　但如果公司没有补助的话也不用担心!因为还有怀孕保险，也就是EI(失业保险)。

　　对于大部分人来说，EI保险是指55%的周薪，按照2017年最高参保年收入$51,300来计算，每周最多可以得到$543，一个月也就是将近$2000刀

5. 延伸医疗保险

如果你工作的企业够好的话，也许他们会购买集体延伸医疗保险.

延伸医疗保险包括处方药、牙齿治疗费用、按摩治疗、视力检查、物理治疗等方面。

6. OAS

OAS，全称叫作Old Age Security ， 这才真的是政府给的“福利“，因为这跟你的工作、薪水都没关系，只要你18岁后在加拿大居住时间满足10年，到65岁时就能拿到这份补贴

但是，要想拿到满额，必须从18岁到65岁之间在加拿大住满40年。不满40年的，按比例扣除一部分。

postgres tutorial 1

download postgres 9.6
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

download sample database
http://www.postgresqltutorial.com/postgresql-sample-database/

ER diagram

load database to postgres
http://www.postgresqltutorial.com/load-postgresql-sample-database/

reference:
http://www.postgresqltutorial.com/

postgres 15 text search

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
 ?column?
----------
 t

 SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

select the ten most recent documents that contain create and table in the title or body:

SELECT title
FROM pgweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

Highlighting Results

SELECT ts_headline('english',
  'The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.',
  to_tsquery('query & similarity'),
  'StartSel = <, StopSel = >');
                      ts_headline                     
-------------------------------------------------------
 containing given <query> terms
 and return them in order of their <similarity> to the
 <query>.

https://www.postgresql.org/docs/current/mvcc.html