2022年6月26日 星期日

postgresql for everybody 2022

general

Charles Russell Severance


psql -h pg.pg4e.com -p 5432 -U pg4e_4f1d91fcd9 pg4e_4f1d91fcd9

\l

\s
\q

\d+
\dt

init

CREATE TABLE pg4e_debug (

  id SERIAL,

  query VARCHAR(4096),

  result VARCHAR(4096),

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(id)

);

select * from pg4e_debug;

select count(*) from pg4e_debug;

SELECT query, result, created_at FROM pg4e_debug;


CREATE TABLE pg4e_result (
  id SERIAL,
  link_id INTEGER UNIQUE,
  score FLOAT,
  title VARCHAR(4096),
  note VARCHAR(4096),
  debug_log VARCHAR(8192),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);


insert

create table ages (name varchar(128), age integer);

select count(*) from ages;

insert into ages (name, age) values ('Darcey', 23);

select * from ages;

INSERT INTO ages (name, age) VALUES ('Evann', 15);
INSERT INTO ages (name, age) VALUES ('Holly', 23);
INSERT INTO ages (name, age) VALUES ('Kiaran', 21);
INSERT INTO ages (name, age) VALUES ('Mehmet', 22);

create

create table automagic (id serial, name varchar(32) not null, height float not null);

Musical Track Database (CSV)



wget https://www.pg4e.com/tools/sql/library.csv
curl -O https://www.pg4e.com/tools/sql/library.csv

create table track_raw (title text, artist text, album text, count integer, rating integer, len integer);
\copy track_raw(title,artist,album,count,rating,len) from 'library.csv' with delimiter ',' CSV;
SELECT title, album FROM track_raw ORDER BY title LIMIT 3;


Cars (integer references, on delete cascade)

CREATE TABLE make (
    id SERIAL,
    name VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

CREATE TABLE model (
  id SERIAL,
  name VARCHAR(128),
  make_id INTEGER REFERENCES make(id) ON DELETE CASCADE,
  PRIMARY KEY(id)
);

insert into model (name, make_id) values ('Silverado C15 2WD', (select id from make where name='Chevrolet'));
insert into model (name, make_id) values ('Silverado C15 Cab Chassis 2WD', (select id from make where name='Chevrolet'));
insert into model (name, make_id) values ('Silverado C15 XFE 2WD', (select id from make where name='Chevrolet'));
insert into model (name, make_id) values ('Portofino', (select id from make where name='Ferrari'));
insert into model (name, make_id) values ('Testarossa', (select id from make where name='Ferrari'));

SELECT make.name, model.name
    FROM model
    JOIN make ON model.make_id = make.id
    ORDER BY make.name LIMIT 5;

Building a many-to-many roster


CREATE TABLE student (
    id SERIAL,
    name VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

DROP TABLE course CASCADE;
CREATE TABLE course (
    id SERIAL,
    title VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

DROP TABLE roster CASCADE;
CREATE TABLE roster (
    id SERIAL,
    student_id INTEGER REFERENCES student(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES course(id) ON DELETE CASCADE,
    role INTEGER,
    UNIQUE(student_id, course_id),
    PRIMARY KEY (id)
);

for data
Parmin, si106, Instructor
Marybeth, si106, Learner
Qirui, si106, Learner
Stacey, si106, Learner
Tighan, si106, Learner
Shelbie, si110, Instructor
Andi, si110, Learner
Arron, si110, Learner
Bruin, si110, Learner
Stewarty, si110, Learner
Tamika, si206, Instructor
Aimie, si206, Learner
Jaslyn, si206, Learner
Nidba, si206, Learner
Sarahlouise, si206, Learner

\copy student(name) from 'students.csv' with delimiter ',' CSV;
insert into course (title) values ('si106'), ('si110'), ('si206');

18,1,1
19,1,0
20,1,0
21,1,0
22,1,0
23,2,1
24,2,0
25,2,0
26,2,0
27,2,0
28,3,1
29,3,0
30,3,0
31,3,0
32,3,0

\copy roster(student_id, course_id, role) from 'relation.csv' with delimiter ',' CSV;

SELECT student.name, course.title, roster.role
    FROM student 
    JOIN roster ON student.id = roster.student_id
    JOIN course ON roster.course_id = course.id
    ORDER BY course.title, roster.role DESC, student.name;

Intermediate SQL


Alter table
alter table pg4e_debug add neon311 integer;
SELECT neon311 FROM pg4e_debug LIMIT 1;

describe table
\d+ taxdata
  Column  |          Type          |
----------+------------------------+
 id       | integer                |
 ein      | integer                |
 name     | character varying(255) |
 year     | integer                |
 revenue  | bigint                 |
 expenses | bigint                 |
 purpose  | text                   |
 ptid     | character varying(255) |
 ptname   | character varying(255) |
 city     | character varying(255) |
 state    | character varying(255) |
 url      | character varying(255) |

distinct
select distinct state from taxdata order by state limit 5;

stored procedure

create table
CREATE TABLE keyvalue ( 
  id SERIAL,
  key VARCHAR(128) UNIQUE,
  value VARCHAR(128) UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY(id)
);

create function
create or replace function trigger_set_timestamp()
returns trigger as $$
begin
  NEW.updated_at = NOW();
  return NEW;
end
$$ language plpgsql;

create trigger
create trigger set_timestamp
before update on keyvalue
for each row
execute procedure trigger_set_timestamp();

insert
INSERT INTO keyvalue (key, value) VALUES ('apple', '1') RETURNING *;

Musical Tracks Many-to-One


create table
CREATE TABLE album (
  id SERIAL,
  title VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);
CREATE TABLE track (
    id SERIAL,
    title VARCHAR(128),
    len INTEGER, rating INTEGER, count INTEGER,
    album_id INTEGER REFERENCES album(id) ON DELETE CASCADE,
    UNIQUE(title, album_id),
    PRIMARY KEY(id)
);
DROP TABLE IF EXISTS track_raw;
CREATE TABLE track_raw
 (title TEXT, artist TEXT, album TEXT, album_id INTEGER,
  count INTEGER, rating INTEGER, len INTEGER);

csv
\copy track_raw(title, artist, album, count, rating, len) from 'library.csv' with delimiter ',' CSV;

distinct albums into the album table
insert into album (title) select distinct album from track_raw order by album;

update album_id
UPDATE track_raw SET album_id = (SELECT album.id FROM album WHERE album.title = track_raw.album);

insert into track
insert into track (title, len, rating, count, album_id) select title, len, rating, count, album_id from track_raw;

Unesco Heritage Sites Many-to-One

psql -h pg.pg4e.com -p 5432 -U pg4e_ecb4afa439 pg4e_ecb4afa439

create raw table (unnormalized, from csv)

DROP TABLE unesco_raw;
CREATE TABLE unesco_raw
 (name TEXT, description TEXT, justification TEXT, year INTEGER,
    longitude FLOAT, latitude FLOAT, area_hectares FLOAT,
    category TEXT, category_id INTEGER, state TEXT, state_id INTEGER,
    region TEXT, region_id INTEGER, iso TEXT, iso_id INTEGER);

\copy unesco_raw(name,description,justification,year,longitude,latitude,area_hectares,category,state,region,iso) FROM 'whc-sites-2018-small.csv' WITH DELIMITER ',' CSV HEADER;

lookup tables

CREATE TABLE category (
  id SERIAL,
  name VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);

CREATE TABLE iso (
  id SERIAL,
  name VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);

CREATE TABLE state (
  id SERIAL,
  name VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);

CREATE TABLE region (
  id SERIAL,
  name VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);

insert into category (name) select distinct category from unesco_raw order by category;
insert into iso (name) select distinct iso from unesco_raw order by iso;
insert into state (name) select distinct state from unesco_raw order by state;
insert into region (name) select distinct region from unesco_raw order by region;

add id to raw table

UPDATE unesco_raw SET category_id = (SELECT category.id FROM category WHERE category.name = unesco_raw.category);
UPDATE unesco_raw SET iso_id = (SELECT iso.id FROM iso WHERE iso.name = unesco_raw.iso);
UPDATE unesco_raw SET state_id = (SELECT state.id FROM state WHERE state.name = unesco_raw.state);
UPDATE unesco_raw SET region_id = (SELECT region.id FROM region WHERE region.name = unesco_raw.region);

create normalised table (by dropping text columns and rename table)

ALTER TABLE unesco_raw DROP COLUMN category;
ALTER TABLE unesco_raw DROP COLUMN iso;
ALTER TABLE unesco_raw DROP COLUMN state;
ALTER TABLE unesco_raw DROP COLUMN region;
ALTER TABLE unesco_raw RENAME TO unesco;

Musical Track Database plus Artists


DROP TABLE album CASCADE;
CREATE TABLE album (
    id SERIAL,
    title VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

DROP TABLE track CASCADE;
CREATE TABLE track (
    id SERIAL,
    title TEXT, 
    artist TEXT, 
    album TEXT, 
    album_id INTEGER REFERENCES album(id) ON DELETE CASCADE,
    count INTEGER, 
    rating INTEGER, 
    len INTEGER,
    PRIMARY KEY(id)
);

DROP TABLE artist CASCADE;
CREATE TABLE artist (
    id SERIAL,
    name VARCHAR(128) UNIQUE,
    PRIMARY KEY(id)
);

DROP TABLE tracktoartist CASCADE;
CREATE TABLE tracktoartist (
    id SERIAL,
    track VARCHAR(128),
    track_id INTEGER REFERENCES track(id) ON DELETE CASCADE,
    artist VARCHAR(128),
    artist_id INTEGER REFERENCES artist(id) ON DELETE CASCADE,
    PRIMARY KEY(id)
);

\copy track(title,artist,album,count,rating,len) FROM 'library.csv' WITH DELIMITER ',' CSV;

INSERT INTO album (title) SELECT DISTINCT album FROM track;
UPDATE track SET album_id = (SELECT album.id FROM album WHERE album.title = track.album);


INSERT INTO tracktoartist (track, artist) SELECT DISTINCT title, artist from track;

INSERT INTO artist (name) SELECT DISTINCT artist FROM track;

UPDATE tracktoartist SET track_id = (select track.id from track where track.title = tracktoartist.track);

UPDATE tracktoartist SET artist_id = (select artist.id from artist where artist.name = tracktoartist.artist);

ALTER TABLE track DROP COLUMN album;
ALTER TABLE track DROP COLUMN artist;
ALTER TABLE tracktoartist DROP COLUMN track;
ALTER TABLE tracktoartist DROP COLUMN artist;

SELECT track.title, album.title, artist.name
FROM track
JOIN album ON track.album_id = album.id
JOIN tracktoartist ON track.id = tracktoartist.track_id
JOIN artist ON tracktoartist.artist_id = artist.id
LIMIT 3;

Generating Text

expected
This is record number 100000 of quite a few text records.
This is record number 100001 of quite a few text records.
...
This is record number 199998 of quite a few text records.
This is record number 199999 of quite a few text records.

from generate_series(100000,199999) i
create table bigtext (content text);
insert into bigtext (content)
select CONCAT('This is record number ', i, ' of quite a few text records.') from generate_series(100000,199999) i;

Regular Expression Queries

looking for lines where the very first character was an upper case character letter
SELECT purpose FROM taxdata WHERE purpose ~ '^[A-Z]' ORDER BY purpose DESC LIMIT 3;

Lines that are all upper case letters and spaces and nothing else
SELECT purpose FROM taxdata WHERE purpose ~ '^[A-Z\s]+$' ORDER BY purpose DESC LIMIT 3;
SELECT purpose FROM taxdata WHERE purpose ~ '^[A-Z[:space:]]+$' ORDER BY purpose DESC LIMIT 3;
SELECT purpose FROM taxdata WHERE purpose ~ '^[[:upper:][:space:]]+$' ORDER BY purpose DESC LIMIT 3;

 YOUTH WRITING WORKSHOPS ACADEMIC SUPPORT 
 YOUTH WRESTLING CLUB SUPPORTED YOUTH WRESTLING
 YOUTH WORK THERAPY PROGRAMS

Regular Expressions


For 'hello'
the greedy h.+l matches 'hell', longest possible string.
the lazy h.+?l matches 'hel', shortest possible string.


Reverse Index in SQL


https://www.pg4e.com/lectures/05-FullText.txt


drop table if exists docs01 cascade;
CREATE TABLE docs01 (id SERIAL, doc TEXT, PRIMARY KEY(id));
INSERT INTO docs01 (doc) VALUES
('Type help copyright credits or license for more'),
('The  prompt is the Python interpreters way of asking'),
('you What do you want me to do next Python is ready to have a'),
('conversation with you All you have to know is how to speak the Python'),
('Lets say for example that you did not know even the simplest Python'),
('language words or sentences You might want to use the standard line'),
('that astronauts use when they land on a faraway planet and try to speak'),
('with the inhabitants of the planet'),
('I come in peace please take me to your leader'),
('This is not going so well Unless you think of something quickly the');
select * from docs01;

drop table if exists invert01;
CREATE TABLE invert01 (
  keyword TEXT,
  doc_id INTEGER REFERENCES docs01(id) ON DELETE CASCADE
);


INSERT INTO invert01 (doc_id, keyword)
SELECT DISTINCT id, s.keyword AS keyword
FROM docs01, unnest(string_to_array(lower(docs01.doc), ' ')) s(keyword)
ORDER BY id;

SELECT keyword, doc_id FROM invert01 ORDER BY keyword, doc_id LIMIT 10;
   keyword    | doc_id 
--------------+--------
              |      2
 a            |      3
 a            |      7
 all          |      4
 and          |      7
 asking       |      2
 astronauts   |      7
 come         |      9
 conversation |      4
 copyright    |      1
(10 rows)


Reverse Index (with stop words) in SQL


drop table if exists docs02 cascade;
CREATE TABLE docs02 (id SERIAL, doc TEXT, PRIMARY KEY(id));
INSERT INTO docs02 (doc) VALUES
('Type help copyright credits or license for more'),
('The  prompt is the Python interpreters way of asking'),
('you What do you want me to do next Python is ready to have a'),
('conversation with you All you have to know is how to speak the Python'),
('Lets say for example that you did not know even the simplest Python'),
('language words or sentences You might want to use the standard line'),
('that astronauts use when they land on a faraway planet and try to speak'),
('with the inhabitants of the planet'),
('I come in peace please take me to your leader'),
('This is not going so well Unless you think of something quickly the');
select * from docs02;

CREATE TABLE stop_words (word TEXT unique);
INSERT INTO stop_words (word) VALUES 
('i'), ('a'), ('about'), ('an'), ('are'), ('as'), ('at'), ('be'), 
('by'), ('com'), ('for'), ('from'), ('how'), ('in'), ('is'), ('it'), ('of'), 
('on'), ('or'), ('that'), ('the'), ('this'), ('to'), ('was'), ('what'), 
('when'), ('where'), ('who'), ('will'), ('with');

drop table if exists invert02;
CREATE TABLE invert02 (
  keyword TEXT,
  doc_id INTEGER REFERENCES docs02(id) ON DELETE CASCADE
);
INSERT INTO invert02 (doc_id, keyword)
SELECT DISTINCT id, s.keyword AS keyword
FROM docs02, unnest(string_to_array(lower(docs02.doc), ' ')) s(keyword)
where s.keyword not in (select * from stop_words)
ORDER BY id;

SELECT keyword, doc_id FROM invert02 ORDER BY keyword, doc_id LIMIT 10;
   keyword    | doc_id 
--------------+--------
              |      2
 all          |      4
 and          |      7
 asking       |      2
 astronauts   |      7
 come         |      9
 conversation |      4
 copyright    |      1
 credits      |      1
 did          |      5
(10 rows)

String Array GIN Index

-- create table
drop table if exists docs03 cascade;
CREATE TABLE docs03 (id SERIAL, doc TEXT, PRIMARY KEY(id));
INSERT INTO docs03 (doc) VALUES
('Type help copyright credits or license for more'),
('The  prompt is the Python interpreters way of asking'),
('you What do you want me to do next Python is ready to have a'),
('conversation with you All you have to know is how to speak the Python'),
('Lets say for example that you did not know even the simplest Python'),
('language words or sentences You might want to use the standard line'),
('that astronauts use when they land on a faraway planet and try to speak'),
('with the inhabitants of the planet'),
('I come in peace please take me to your leader'),
('This is not going so well Unless you think of something quickly the');

-- seq scan
EXPLAIN SELECT id, doc FROM docs03 WHERE '{conversation}' <@ string_to_array(lower(doc), ' ');
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Seq Scan on docs03  (cost=0.00..32.23 rows=6 width=36)
   Filter: ('{conversation}'::text[] <@ string_to_array(lower(doc), ' '::text))
(2 rows)


-- create index
CREATE INDEX array03 ON docs03 USING gin(string_to_array(lower(doc), ' ')  array_ops);


-- filler rows to make sure PSQL uses its index
INSERT INTO docs03 (doc) SELECT 'Neon ' || generate_series(10000,20000);

-- bitmap heap scan
EXPLAIN SELECT id, doc FROM docs03 WHERE '{conversation}' <@ string_to_array(lower(doc), ' ');
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on docs03  (cost=12.01..16.03 rows=1 width=15)
   Recheck Cond: ('{conversation}'::text[] <@ string_to_array(lower(doc), ' '::text))
   ->  Bitmap Index Scan on array03  (cost=0.00..12.01 rows=1 width=0)
         Index Cond: ('{conversation}'::text[] <@ string_to_array(lower(doc), ' '::text))
(4 rows)

GIN ts_vector Index

-- create index
CREATE INDEX fulltext03 ON docs03 USING gin(to_tsvector('english', doc));

-- filler rows to make sure PSQL uses its index
INSERT INTO docs03 (doc) SELECT 'Neon ' || generate_series(10000,20000);

-- bitmap heap scan
EXPLAIN SELECT id, doc FROM docs03 WHERE to_tsquery('english', 'instructions') @@ to_tsvector('english', doc);
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Bitmap Heap Scan on docs03  (cost=12.39..81.75 rows=50 width=15)
   Recheck Cond: ('''instruct'''::tsquery @@ to_tsvector('english'::regconfig, doc))
   ->  Bitmap Index Scan on fulltext03  (cost=0.00..12.38 rows=50 width=0)
         Index Cond: ('''instruct'''::tsquery @@ to_tsvector('english'::regconfig, doc))
(4 rows)

Making a connection from Python


pip install psycopg2-binary

simple.py


# install psycopg2 (if needed)
# pip3 install psycopg2    # (or pip)

# https://www.pg4e.com/code/simple.py

# https://www.pg4e.com/code/hidden-dist.py
# copy hidden-dist.py to hidden.py
# edit hidden.py and put in your credentials

# python3 simple.py

# To check the results, use psql and look at the
# pythonfun table

import psycopg2
import hidden

# Load the secrets
secrets = hidden.secrets()

conn = psycopg2.connect(host=secrets['host'],
        port=secrets['port'],
        database=secrets['database'], 
        user=secrets['user'], 
        password=secrets['pass'], 
        connect_timeout=3)

cur = conn.cursor()

sql = 'DROP TABLE IF EXISTS pythonfun CASCADE;'
print(sql)
cur.execute(sql)

sql = 'CREATE TABLE pythonfun (id SERIAL, line TEXT);'
print(sql)
cur.execute(sql)

conn.commit()    # Flush it all to the DB server

for i in range(10) : 
    txt = "Have a nice day "+str(i)
    sql = 'INSERT INTO pythonfun (line) VALUES (%s);'
    cur.execute(sql, (txt, ))

conn.commit()

sql = "SELECT id, line FROM pythonfun WHERE id=5;" 
print(sql)
cur.execute(sql)

row = cur.fetchone()
if row is None : 
    print('Row not found')
else:
    print('Found', row)

sql = 'INSERT INTO pythonfun (line) VALUES (%s) RETURNING id;'
cur.execute(sql, (txt, ))
id = cur.fetchone()[0]
print('New id', id)

# Lets make a mistake
sql = "SELECT line FROM pythonfun WHERE mistake=5;"
print(sql)
cur.execute(sql)

conn.commit()
cur.close()

hidden.py

# Keep this file separate

# https://www.pg4e.com/code/hidden-dist.py

# psql -h pg.pg4e.com -p 5432 -U pg4e_be9e729093 pg4e_be9e729093

# %load_ext sql
# %config SqlMagic.autocommit=False
# %sql postgresql://pg4e_be9e729093:pg4e_p_d5fab7440699124@pg.pg4e.com:5432/pg4e_be9e729093
# %sql SELECT 1 as "Test"

def secrets():
    return {"host": "pg.pg4e.com",
            "port": 5432,
            "database": "pg4e_026ee23427",
            "user": "pg4e_026ee23427",
            "pass": "pg4e_p_7a84820f4ce3880"}

def elastic() :
    return {"host": "www.pg4e.com",
            "prefix" : "elasticsearch",
            "port": 443,
            "scheme": "https",
            "user": "pg4e_86f9be92a2",
            "pass": "2008_9d454b1f"}

def readonly():
    return {"host": "pg.pg4e.com",
            "port": 5432,
            "database": "readonly",
            "user": "readonly",
            "pass": "readonly_password"}

# Return a psycopg2 connection string

# import hidden
# secrets = hidden.readonly()
# sql_string = hidden.psycopg2(hidden.readonly())

# 'dbname=pg4e_data user=pg4e_data_read password=pg4e_p_d5fab7440699124 host=pg.pg4e.com port=5432'

def psycopg2(secrets) :
     return ('dbname='+secrets['database']+' user='+secrets['user']+
        ' password='+secrets['pass']+' host='+secrets['host']+
        ' port='+str(secrets['port']))

# Return an SQLAlchemy string

# import hidden
# secrets = hidden.readonly()
# sql_string = hidden.alchemy(hidden.readonly())

# postgresql://pg4e_data_read:pg4e_p_d5fab7440699124@pg.pg4e.com:5432/pg4e_data

def alchemy(secrets) :
    return ('postgresql://'+secrets['user']+':'+secrets['pass']+'@'+secrets['host']+
        ':'+str(secrets['port'])+'/'+secrets['database'])

A Sequence of Numbers

import psycopg2
import hidden

# Load the secrets
secrets = hidden.secrets()

conn = psycopg2.connect(host=secrets['host'],
        port=secrets['port'],
        database=secrets['database'], 
        user=secrets['user'], 
        password=secrets['pass'], 
        connect_timeout=3)

cur = conn.cursor()

sql = 'DROP TABLE IF EXISTS pythonseq CASCADE;'
print(sql)
cur.execute(sql)

sql = 'CREATE TABLE pythonseq (iter INTEGER, val INTEGER);'
print(sql)
cur.execute(sql)

conn.commit()    # Flush it all to the DB server

number = 242412
for i in range(300) :
    print(i+1, number)
    sql = 'INSERT INTO pythonseq (iter, val) VALUES (%s, %s);'
    cur.execute(sql, (i+1, number,))
    number = int((number * 22) / 7) % 1000000

conn.commit()
cur.close()


Interacting with the PokéAPI

conn = psycopg2.connect(host=secrets['host'],
                        port=secrets['port'],
                        database=secrets['database'],
                        user=secrets['user'],
                        password=secrets['pass'],
                        connect_timeout=3)
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS pokeapi (id INTEGER, body JSONB);")

url: str = "https://pokeapi.co/api/v2/pokemon"
response = requests.get(url)
text = response.text
js = json.loads(text)
results = js.get("results", [])

sql = "INSERT INTO pokeapi (id, body) VALUES ( %s, %s );"
cur.execute(sql, (str(id), json.dumps(body),))
cur.execute(sql, (str(id), json.dumps(body),))
cur.execute(sql, (str(id), json.dumps(body),))
cur.execute(sql, (str(id), json.dumps(body),))
cur.execute(sql, (str(id), json.dumps(body),))
conn.commit()
cur.close()

Course 4 Database Architecture, Scale, and NoSQL

week 1 question

========
To Multi-Master or not to Multi-Master - that is the question
Your organization is reaching the limits of its relational database application as it scales and you are in meeting to decide to move to a multi-master approach or not. Expanding the size of the single-master instance is quite expensive and requires a long-term contract. The multi-master approach is much less expensive and can use commodity hardware. Take a position for or against the switch to multi-master proposal. Write 3-5 paragraphs and share your reasoning on the position you have taken. Feel free to bring prior experience that you might have into the conversation. Feel free to now more than the instructor on this topic :) Let's see what we can learn from talking and listening to each other's perspectives. The primary goal is to have a conversation.

Don't take off points for little mistakes. If they seem to have done the assignment give them full credit. Feel free to make suggestions if there are small mistakes. Please keep your comments positive and useful. If you do not take grading seriously, the instructors may delete your response and you will lose points.

answers
=======

I am for the switch to multi-master proposal. For a long term project development and business sustainability, the data size would reaches a single machine limit of that single master soonner or later. Horizontal scaling should be taken into account.

Expanding the size of the current single-master may solve the problem in a short term, which lead to a shorter lead time to solve our problem. However it requires a higher hardware cost. And we are at the age of high speed hardware development where the highest end of computer could possibly be replaced by a tiny micro computing device in the future. In other words the high end hardware that we may be purchasing today could depreciate drastically in the coming years. This happens in the recent past that the high end mainframe computational power is surpassed by a handheld smartphone.

Switching from a single-master to multi-master usually requires a slightly more complex data flow design. For example we need to figure out whether there may be any part of data that does not require strong data consistency but only eventual consistency. In this way the multi-master approach may not require a high end machine. Google did that many years ago by returning the search results from thousands of commidity hardware within fraction of seconds. We should be able to follow the same approach if we design the data flow correctly.

Peer 1
======

The advantages of a multi-master concept are higher availability and potentially faster server responses. This would allow us to continue operating our database for the foreseeable future.

On the other hand, this approach brings a number of disadvantages. By having multiple servers that can perform write operations, we get increasing system complexity and communication latency. This limits the scalability of this approach. Another disadvantage is that we lose consistency in our database. What does this mean for our application?

Another approach would be to try to use read-only replicas first. This might be sufficient for the near future and is easy to implement. This would allow us to avoid the complexity of a multi-master approach. In the long term, we can move to a BASE type database. The biggest change here is that we lose consistency, but we lose that anyway.

Peer 2
======

As I learned from the lecture, it is hard to absolutely for or against the Multi-Master. It should depend on:
1) what kind of data we are going to store
2) Does the data size increase continuously
3) tolerance trade-off between budget and stability/consistency
 
Once paid, problem solved
Expanding the size of singer master instance is quite expensive, but if the data size might keep stable and does not increase in the future, it is a good solution to keep the consistency and stability,  

Permanent Solution but more work involved
If we are facing continuously increase of data size, then we need to consider another permanent solution --  multi-master. It is cheaper but it increases the risk of over-communication and overboarding between masters. Maybe we could use/think about an engine which manages the source-usage/communication/transaction/jobs between different masters, like Spark for hdfs, to make the database more performant.


Peer 3
======
The only tenable decision is to choose to switch to a multi-master implementation.  If your organization really is reaching the limits of its relational database application then that alone means that a scaleable solution must be chosen.  In the real world, the fact that expanding the size of the single-master instance is quite expensive would likely be a decisive factor.  The obligation of a long-term contract per size expansion (and assuming a successful business this is likely) is a non-mutually-exclusive additional reason to chose to change to a multi-master.

The lower cost of the multi-master change is also a telling factor is the decision.  The fact that commodity hardware(presumably hardware that already exists and is commonly in use for databases) can be used is an additional non-mutually-exclusive reason to choose to go to multi-master

If the stated assumptions prove to be incorrect then it may be necessary to continue to use single-master application.  If in reality it turns out that you can't go to multi-master then to stay in business you have to go back to the more expensive alternative.

Peer 4
======
A Multi-Master setup has multiple nodes of a database cluster accepting the write requests. It typically depends on the client to pick a Master node, or an abstracted proxy may choose one. As soon as a Master node gets the write request, it accepts it and applies the changes. Once the update is made on one Master, it is propagated to all other Master nodes of the cluster asynchronously, making the data eventually consistent.

Peer 5
======
The decision on whether to move to a multi-master approach really depends on several factors.  Some of these are listed below:

The expected growth of the demands on the proposed system over the next 5-10 years.
The projected rate of growth of data increase, and processing requirements over the next 5-10 years
The type of business i.e. This affects the how vital is the data consistency. A bank or financial institution would require strong adherence to ACID.  A business with more static data could use a more eventual consistency
Pro - After thinking it over, assuming that the "The multi-master approach is much less expensive and can use commodity hardware" is a valid assumption. I believe that over a 10-year time period the master-master system approach would be best.  As a consultant, I've found that this approach is favored by large clients, for example the City and County of Denver. The use of multiple master databases and replica (slave databases) increase the efficiency of the system by basically dividing CRUD between the master and replica databases.  In addition, entities such as the government, have a strong IT department that can support this approach and have the resources to configure and deploy such a system.  Once the initial system is confiqured and deployed, additional master and replicas can be added as needed

Con- The major disadvantages are the time and resources required to configure and deploy a master-master system. For smaller entities, a simple master-slave (replica), might be best. This approach is best for environments where the rate of growth of the demands on the system is relatively low and stable.


Week 2


On ACID and BASE Architectures
The topic for this week is to compare, contrast, and explore the interplay between ACID and BASE style architectures. In this mini-paper (3-5 paragraphs) explain your take or views on these competing approaches to database architecture. Feel free to bring prior experience that you might have into the conversation. It is quite OK to take and support a position that is different from the position in this week's lectures. Let's see what we can learn from talking and listening to each other's perspectives. The main goal is to have a conversation.

Don't take off points for little mistakes. Feel free to make suggestions if there are small mistakes. Please keep your comments positive and useful. Provide comments that will help us all think about the question that is posed. Some of the points will be given by the instructors - it might take a while for the instructor points to appear.

answer
======

BASE model allows database instance to spread and replicating with its cluster and so data are basically available but data may not b immediately be consistent when updated that enters its soft state. Although immediate consistency cannot be guaranteed, eventually data will be consistent and even before that the data can be accessed which allows highest availablility.

ACID model enforces a transaction which is a sequence of operation is atomic and all operations will be all executed or none will be executed. Data will be always consistent and following the constraints enforced by the schema.

The choice between ADID and ACID model has no definite answer. For cases where an approximate results are fine and user expects low response time, BASE is usually a better option. When user expect accurate result based on the consistency of data, we should give ACID databsae a go.

There are some examples that BASE model maybe favored. This includes processing unstructured data for insight, figuring data patterns, handling data that involes individuals hobbies or minds, reporting dynamic status like hybrid types of network traffic or public health statistics.

There are some examples that ADIC model maybe favored. This includes financial transaction, scientific research, marking or scoring systems.


peer 1
======
I dont need any ACID nor BASE database to tell that this is a direct copy-pasted answer from https://neo4j.com/blog/acid-vs-base-consistency-models-explained/.

peer 2
======
We want to compare ACID and BASE databases to get a better understandig of their strengths and weaknesses, and when to choose which. To do this, we look at a number of selected aspects: Organization of the data, data replication, and the consistency of the data. A brief conclusion follows at the end.

Organization of the data: In ACID databases, data is organized in rows and columns. This has the advantage that we can formulate strong constraints on the data and thus efficiently perform operations on the data. In contrast, in BASE databases, the data is organized into documents that contain key-value pairs (it looks like a Python dictionary). This provides greater freedom in the form of our data, at the cost of unpredictability in the runtime of operations.

Data replication: In ACID systems we try to avoid data replication. Therefore, we create lookup tables for text columns and refer to the values in this tables with integer keys. In the case of BASE databases, we accept replication of data, which is forced at a certain point by the size or scale of the application. The disadvantage is the higher demand for storage space.

Consistency: In ACID databases, great efforts are made to ensure data consistency. At the cost of vertical scaling. In BASE systems, on the other hand, we accept a certain level of inconsistency to allow better vertical scaling of the system.

In summary, if we assume ACID databases as the default choice, we have seen that BASE databases become superior if the number of accesses and the size of the data are large enough.

peer 3
======
I dont need any ACID nor BASE database to tell that this is a direct copy-pasted answer from https://phoenixnap.com/kb/acid-vs-base#:~:text=The%20ACID%20database%20transaction%20model,(e.g.%2C%20data%20warehousing).

peer 4
======
First of, the ACID model provides a consistent system while the BASE model provides high availability. Main principals of ACID is: Atomicity, Consistency, Isolation and durability. So we can suppose that this type of database is highly normalized, on other hand we have BASE. BASE stands for Basically-Available-soft serve - Eventual consistent. There`s no answer which type is better because all of them have their own pros and cons. Collections of operations that form a single logical unit of work are called transactions and the database system must ensure proper execution of transactions and the ACID database transaction model ensures that a performed transaction is always consistent. The rise in popularity of NoSQL databases provided a flexible and fluidity with ease to manipulate data and as a result, a new database model BASE was designed, reflecting these properties. 

peer 5
======
It is not possible to give a straight answer to the question of which database model is better. Therefore, a decision must be reached by considering all the aspects of the project.

Given their highly structured nature, ACID-compliant databases will be a better fit for those who require consistency, predictability, and reliability.

Those who consider growth to be among their priorities will likely want to choose the BASE model, because it enables easier scaling up and provides more flexibility. However, BASE also requires developers who will know how to deal with the limitations of the model.

Week 3


Assignment 1
pip install 'elasticsearch<7.14.0'


sadasd
as
a
das
ad
das
da
das
d
asd
a
s
da


end

2022年6月22日 星期三

Zabbix on Ubuntu 20.04 Focal MySQL PostgreSQL


https://www.zabbix.com/download?zabbix=6.0&os_distribution=ubuntu&os_version=20.04_focal&db=mysql&ws=apache

2022-06-22

6.0 LTS
Ubuntu 20.04 (Focal), not 22.04 Jammy
MySQL, not PostgreSQL
Apache, not NGINX


# wget https://repo.zabbix.com/zabbix/6.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_6.0-1+ubuntu20.04_all.deb
# dpkg -i zabbix-release_6.0-1+ubuntu20.04_all.deb
# sudo apt update

# sudo apt install zabbix-server-mysql zabbix-frontend-php zabbix-apache-conf zabbix-sql-scripts zabbix-agent


TBC, not done

2022年6月7日 星期二

Installing Pinyin Input Method on Ubuntu 20.04

 https://www.linkedin.com/pulse/installing-pinyin-input-method-ubuntu-2004-chinese-post-ron-li

https://www.dell.com/support/kbdoc/zh-hk/000181184/how-to-add-chinese-pinyin-input-to-xps-9310-laptops-that-ship-with-ubuntu-20-04-in-english


Procedure

man ibus
Ubuntu Software > iBus Preferences
Ubuntu Software > ibus pinyin > PinYin (Not ZhuYin)
ibus restart (dont use root)
ibus-setup
Add > Chinese > Intelligent Pinyin
Settings > Region and Language > Add > Chinese (Intelligent Pinyin)
Use shift

2022年5月28日 星期六

Compile QuickFix

https://github.com/quickfix/quickfix

download zip

sudo apt-get install libtool

./bootstrap

./configure

make // N4100 takes over 10 minutes

make check // failed

sudo make install

2022年4月18日 星期一

2022 Network Cards and Switches 10G Solarflare Hardware Timestamping

Cards

SFN6322F10Gb $220

https://www.bhphotovideo.com/c/product/1017856-REG/solarflare_sfn6322f_solarflare_srvr_adptr_crd.html

PCIe x8 Precision Time Stamping (Port 0)

SO_TIMESTAMPING PTPv2 daemon


SFN5122F $500

SFN8522 $1000

SNF7322 $600


SO_TIMESTAMPING

https://docs.kernel.org/networking/timestamping.html

err = setsockopt(fd, SOL_SOCKET, SO_TIMESTAMPING, &val, sizeof(val));


MSI B450M Mortar Max Onboard NIC

Realtek RTL8111/8168/8400 PCIE GBE
enp34s0
sudo ethtool -T enp34s0

Time stamping parameters for enp34s0:
Capabilities:
software-transmit     (SOF_TIMESTAMPING_TX_SOFTWARE)
software-receive      (SOF_TIMESTAMPING_RX_SOFTWARE)
software-system-clock (SOF_TIMESTAMPING_SOFTWARE)
PTP Hardware Clock: none
Hardware Transmit Timestamp Modes: none
Hardware Receive Filter Modes: none

MSI B450M Mortar Max PCIE_3 NIC
Intel 8260NGW
wlo1
sudo ethtool -T wlo1
Time stamping parameters for wlo1:
Capabilities:
software-receive      (SOF_TIMESTAMPING_RX_SOFTWARE)
software-system-clock (SOF_TIMESTAMPING_SOFTWARE)
PTP Hardware Clock: none
Hardware Transmit Timestamp Modes: none
Hardware Receive Filter Modes: none

Online some one NIC
https://networkengineering.stackexchange.com/questions/23369/how-to-check-if-a-nic-supports-hardware-timestamps
Time stamping parameters for eth0:
Capabilities:
    hardware-transmit     (SOF_TIMESTAMPING_TX_HARDWARE)
    software-transmit     (SOF_TIMESTAMPING_TX_SOFTWARE)
    hardware-receive      (SOF_TIMESTAMPING_RX_HARDWARE)
    software-receive      (SOF_TIMESTAMPING_RX_SOFTWARE)
    software-system-clock (SOF_TIMESTAMPING_SOFTWARE)
    hardware-raw-clock    (SOF_TIMESTAMPING_RAW_HARDWARE)
PTP Hardware Clock: 0
Hardware Transmit Timestamp Modes:
    off                   (HWTSTAMP_TX_OFF)
    on                    (HWTSTAMP_TX_ON)
Hardware Receive Filter Modes:
    none                  (HWTSTAMP_FILTER_NONE)
    all                   (HWTSTAMP_FILTER_ALL)
    ptpv1-l4-sync         (HWTSTAMP_FILTER_PTP_V1_L4_SYNC)
    ptpv1-l4-delay-req    (HWTSTAMP_FILTER_PTP_V1_L4_DELAY_REQ)
    ptpv2-l4-sync         (HWTSTAMP_FILTER_PTP_V2_L4_SYNC)
    ptpv2-l4-delay-req    (HWTSTAMP_FILTER_PTP_V2_L4_DELAY_REQ)
    ptpv2-l2-sync         (HWTSTAMP_FILTER_PTP_V2_L2_SYNC)
    ptpv2-l2-delay-req    (HWTSTAMP_FILTER_PTP_V2_L2_DELAY_REQ)
    ptpv2-event           (HWTSTAMP_FILTER_PTP_V2_EVENT)
    ptpv2-sync            (HWTSTAMP_FILTER_PTP_V2_SYNC)
    ptpv2-delay-req       (HWTSTAMP_FILTER_PTP_V2_DELAY_REQ)

SIOCSHWTSTAMP

https://elixir.bootlin.com/linux/v4.2/source/Documentation/networking/timestamping/timestamping.c
https://gitlab.com/etoricky/linuxsocket/-/blob/master/28timestamp/28timestamp.cpp
./28timestamp wlo1 SOF_TIMESTAMPING_RX_HARDWARE
SIOCSHWTSTAMP: Operation not permitted


Switches

N3K-C3548P-XL $43000 2022-04 TB Price
N3K-C3524P-XL $36000 
N3K-C3132Q-V $14000 2022-04 XY Price





End


Netgear R6400 v1 to Merlin for AiMesh

 Netgear R6400 v1 to Merlin for AiMesh


CPU
R6400V1 Broadcom BCM4708A0 800MHz 2C KFEBG 256MB RAM 128MB Flash
R6400V2 Broadcom BCM4708C0 1GHz 2C 256MB RAM 128MB Flash

BCM4708A0 800MHz
AC68U

BCM4708C0 1GHz
AC66U B1

Merlin

R6400_380.63_2_X7.2.1.trx
R6400_380.63_2_X7.2.1.chk
https://upsangel.com/security/home-security/shadowsocks-vpn-server-on-merlin-r6400-guide/
Koolshare_Merlin_New_Gen_384
R6400的改版386固件,也就是利用沧海遗珠的R6300 V2(与R6400的硬件参数一直)刷改CFE,实现386固件的刷入,具备梅林386的一些特性,如AiMesh

Status

Too difficult to find old .chk file. Failed



End

2022年4月17日 星期日

2022 Intel J4125 NUC

 High

7700K 9662 95W 4C8T 2016Q4
6700K 8960 95W 4C8T 2015Q2

Low
8260U 7854 15W 4C8T 2020Q2
8250U 5942 15W 4C8T 2017Q2
N6005 4513 10W 4C4T 2021Q4
N5105 4161 10W 4C4T 2021Q2
N5095 4087 15W 4C4T 2021Q2
8145U 3842 15W 2C4T 2018Q4
7300U 3745 15W 2C4T 2017Q1
8130U 3647 15W 2C4T 2018Q2
N5040 3518 10W 4C4T 2020Q1
7200U 3413 15W 2C4T 2016Q4
6500U 3280 15W 2C4T 2015Q2
J5005 3039 10W 4C4T 2017Q4
J4125 3002 10W 4C4T 2020Q1
J4105 2911 10W 4C4T 2018Q1
N6000 2869 6W 4C4T 2021Q1
N5030 2692 6W 4C4T 2020Q1
N5000 2596 6W 4C4T 2018Q1
5250U 2504 15W 2C4T 2015Q1
N4120 2493 6W 4C4T 2020Q1
N4100 2453 6W 4C4T 2018Q2
2540M 2293 35W 2C4T 2010Q4
j3455 2235 10W 4C4T 2016Q4
4200u 2209 15W 2C4T 2013Q1
N3450 1931 6W 4C4T 2016Q4
N4000 1398 6W 2C2T 2018Q2
J3160 1216 6W 4C4T 2016Q2
J3355 1195 10W 2C2T 2017Q1
N3150 1193 6W 4C4T 2015Q2
N3160 1154 6W 4C4T 2016Q2
J1900 1135 10W 4C4T 2014Q1
3215U 1144 15W 2C2T 2016Q1
N3350 1128 6W 2C2T 2016Q4
2980U 1056 15W 2C2T 2014Q2
1037U 969 17W 2C2T 2013Q2
Z8350 910 4W 4C4T 2016Q2
P8600 851 25W 2C2T 2008Q4
P8400 827 25W 2C2T 2008Q4
1007U 798 17W 2C2T 2013Q1
N3000 604 4W 2C2T 2015Q2
N3050 591 6W 2C2T 2015Q2
N2840 581 7W 2C2T 2014Q4
J1800 583 10W 2C2T 2014Q1 huge stocks
N450 177 5.5W 1C2T 2010Q1

Bought

$680 NingMei MoFang M1T J4125 8GB 128G mSATA 2242 12V2A
BIOS TPM or Linux will brick this device!

Price

2022 XY Prices

$730=$580+150 Kodlix GD41 0+0 n4100 lan2, if 8GB +$150

$850 Ningmei 8+512 (rejected)
$759 Ningmei 8+256



$1000 Teclast 24inch J4125 8+256
$880 4lan j4125 8+60  

$800 2lan J4125 8G+1TB

More

N5105 expensive
N5095 Fan
$688 0+0 
N6000 expensive
J5005 Fanless
$750 nuc7pjyh

J4125
$780 Beelink GKMini J4125 8+128 SATA Fan Beautiful
$760 Minisforum J4125 8+256 nvme Fan
$766 Minisforum J4125 8+0 Fan
$750 Beelink GK55 8+128 SATA Fan 
$660 ITX 17x17 Fanless

J4105
$700 Beelink X45 J4105 6+128

N4100
$610 Kodlix GD41 4+0
$600 Kodlix GD41 0+0
$580 Kodlix GN41 8+64 SATA
$550 Kodlix GN41 8+64 SATA Fanless
$550 N41-H 8+0 NVME

J3455
$600 NUC6CAYH 4+60
$560 NUC6CAYH 0+0
$350 DIY 4+0 WIFI HDMI

Beelink = Lingke



2007 to 2023 HP and Dell Servers Comparison

  HP Gen5 to Gen11  using ChatGPT HP ProLiant Gen Active Years CPU Socket Popular HP CPUs Cores Base Clock Max RAM Capacity Comparable Dell ...