2024/08/17

MonetDB

MonetDB is an open-source column-oriented relational database management system (RDBMS) originally developed at the Centrum Wiskunde & Informatica (CWI) in the Netherlands. It is designed to provide high performance on complex queries against large databases, such as combining tables with hundreds of columns and millions of rows.

MonetDB architecture is represented in three layers, each with its own set of optimizers. The front end is the top layer, providing query interface for SQL. Queries are parsed into domain-specific representations, like relational algebra for SQL, and optimized. The generated logical execution plans are then translated into MonetDB Assembly Language (MAL) instructions, which are passed to the next layer. The middle or back-end layer provides a number of cost-based optimizers for the MAL. The bottom layer is the database kernel, which provides access to the data stored in Binary Association Tables (BATs). Each BAT is a table consisting of an Object-identifier and value columns, representing a single column in the database.

MonetDB internal data representation also relies on the memory addressing ranges of contemporary CPUs using demand paging of memory mapped files, and thus departing from traditional DBMS designs involving complex management of large data stores in limited memory.


使用 RPM 安裝以後,使用下列的方式開啟資料庫服務:

sudo systemctl start monetdbd.service

使用下列的方式查詢目前的狀態:

sudo systemctl status monetdbd.service

使用下列的方式停止服務:

sudo systemctl stop monetdbd.service

下面建立一個 demo 資料庫。

sudo monetdb create demo

還需要 release 才能夠使用:

sudo monetdb release demo

然後就可以使用 mclient 連線進行測試:

mclient -u monetdb -d demo

一般而言可以將 user/password 記錄在 .monetdb 中,這樣就不需要輸入帳號密碼,只需要提供資料庫名稱。 下面是內容設定的例子:

user=monetdb
password=monetdb

如果想要刪除資料庫,可以使用 stop 與 destroy:

sudo monetdb stop demo
sudo monetdb destroy demo

使用下列的指令建立一個使用者 danilo:

CREATE USER "danilo" WITH PASSWORD 'danilo' NAME 'Danilo' SCHEMA "sys";
CREATE SCHEMA "danilo" AUTHORIZATION "danilo";
ALTER USER "danilo" SET SCHEMA "danilo";

使用 mclient 連線驗證:

mclient -u danilo demo

MonetDB/e Embedded

MonetDB/e is the embedded version of MonetDB, embed the power of an analytical SQL database engine in your Python or C/C++ applications.

相關連結

2024/08/15

NoSQL (Not only SQL)

一個資料庫在最基礎的層次上需要完成兩件事情:當你把資料交給資料庫時,它應當把資料儲存起來;而後當你向資料庫要資料時,它應當把資料返回給你。

如果要了解 SQL 為什麼重要,我們需要從 NoSQL (Not only SQL) 開始出發。NoSQL 其實是資料庫使用觀念的復古運動、正確觀念的復興運動。 也就是關聯式資料庫並不是唯一且最適的解法,要根據資料的使用特性,選擇適當的儲存機制,所謂的 NoSQL (Not only SQL)。

規模可伸縮性優先考慮是那些必須具備無限可伸縮性的應用,能夠不受限制的擴展比更豐富的功能更加重要。 這些應用包括很多需要高可伸縮性的網站,如 Facebook。 有些網站使用了關聯型資料庫系統,而有些並未採用之。 這些服務的共通性在於對規模可伸縮性的需求比功能更重要,他們無法將應用使用一個單一 RDBMS 解決。 因此,要怎麼處理資料仍然是要不要採用 NoSQL 資料庫的重點(傳統 row-based 的 RDBMS 也有叢集的解決方案, 只是擴張節點相對於某些類型 NoSQL 資料庫而言,比較沒有彈性,不過目前分散式的 RDBMS 解決方案已經出現並且已經有應用實例), 而不是因為某個 NoSQL "awesome" 所以採用,使用了錯誤的資料模型在專案上將容易導致專案的失敗。

比較流行的 NoSQL 資料庫有下列的形式:

  • Key/Value store: map (key, value),是一種簡單的資料模型,適用的情況通常是作為 cache 使用。
  • Document-store: The central concept of a document store is the notion of a "document". 目前比較常見的是 JSON 和 XML 文件。文件模式的資料庫在處理關聯性時反而比較弱, 因為文件應該是 self-contained 的,所以資料之間會有關聯性的情況下儘量不要考慮這個資料模型。
  • Wide column store: For software developers, it can sometimes be helpful to think of them as a key-value collection where each value in the collection is either a simple data type or another key-value collection. For example: map (key, map (key, value)),與傳統的 RDBMS 資料庫(例如 PostgreSQL, CUBRID, MariaDB, MySQL)相比, 適用的範圍是需要良好的可伸縮性與大量資料範圍查詢時的情況。 另外,就是可能會有一個解決方案是使用 SQL 或者是類似的查詢語言來處理與管理 Wide column store 的資料。
  • Graph: This kind of database is designed for data whose relations are well represented as a graph (elements interconnected with an undetermined number of relations between them), 適用於多對多關係(例如社群網站)的情況。與其它模式相比,資料模式十分複雜。 但是注意,多對多關係也不一定只能使用 Graph 資料庫,例如臉書一開始是使用 MySQL 實作。
  • Search Engines:搜尋引擎並不是資料庫,但是可以用來檢索資料並且作為資料庫的輔助工具。 有些搜尋引擎結合文件資料庫或者是 RDBMS 而成為一個完整的資料庫產品。

關聯式資料庫一般而言可以分為二個類型, OLTP (Online transaction processing) 與 OLAP (Online analytical processing), 當然也有試著融合二者的 HTAP 資料庫,不過一般而言還是按照 OLTP 與 OLAP 來分類。 NoSQL 資料庫的使用情況也可以按照這二個類型來分類。

SQL 是為了存取或操作關聯式資料庫所設計的語言。SQL 的出現,解決了以往程式與資料庫相依性過高的問題, 透過 SQL 存取資料庫使得後端資料庫較容易更換(雖然有語法上的相容問題,還是比 NoSQL 資料庫之間的切換簡單), 因此達成資料庫的獨立性,而前端的介面也可獨立使用不同的開發工具。如此將資料層分離出來,儲存到資料庫伺服器, 對於維護與安全都更有保障。

對我來說,過度強調 RDBMS 缺乏良好的可伸縮性是一種 FUD (Fear, Uncertainty, Doubt), 如果一個 RDBMS 其儲存層是建立在分布式系統之上,而且使用 Raft 算法來解決一致性的問題, 那就你就有一個具有良好的可伸縮性的 RDBMS,例如 TiDB。 另外,有一些 SQL solution 使用了 NoSQL 資料庫作為存儲層(雖然有可能不支援或者是僅是有限度的支援 transaction, 不過也不是所有的關聯式資料庫都支援 transaction,一些 OLAP 類型的可能就不支援), 例如 Apache Phoenix 就是建立在 Apache HBase 之上,在這個情況下使用者一樣可以使用 SQL 語言存取 NoSQL 資料庫。

並不是 NoSQL 資料庫就表示有良好的可伸縮性,至少以我所認知的各種資料庫來說, 一般而言為 Wide column store 具有良好的可伸縮性(也就是 Apache HBase 與 Apache Cassandra,以及其它類似技術的資料庫), 其餘形式的資料庫大多數也都是使用傳統 RDBMS 橫向拓展的方法,在可伸縮性這點而言並未具有優勢明顯。 因此對比 RDBMS 來說,NoSQL 資料庫通常是為了解決一些特定的問題而使用,缺點就是程式與資料庫相依性過高(有可能造成被廠商綁定的問題), 缺少一部份 RDBMS 提供的功能而需要自己再造一次輪子,以及安全性上的考量。大部份的情況下還是應該先考慮 RDBMS 是否可以適用。

最後一點,就是用 SQL 代稱某種類型的資料庫是不夠好的說法,畢竟 SQL 是一個查詢語言,因此不應該作為某種資料庫的代稱。 就我個人來說,SQL 這個查詢語言是個很好用的工具,而目前的發展也證明他是個歷久彌新的查詢語言。

CUBRID database

CUBRID 是一個開放原始碼的關係資料庫管理系統, 為高效執行線上交易處理進行了高度優化,特別是需要處理大數據量和高並發請求的複雜商務服務, 支援 Windows 與 Linux 平台。 CUBRID 這個名稱,實際上是兩個單詞的組合:"Cube"(立方體)和"Bride"(橋梁)。 對 CUBRID 而言,"Bride"代表"data bridge"(數據橋), 而"Cube"代表儲存數據的盒子,寓意為放在其中的數據提供安全。

CUBRID 架構特別的地方是加入了 Broker 的設計,Broker 是一個 middleware, 用來處理應用程式與資料庫 server 之間的連線。 會這麼設計的原因是因為 CUBRID 原本是南韓 Naver(Naver 為南韓搜尋引擎龍頭)的內部計畫, 用來取代內部的 Oracle 資料庫以節省資料庫方面日益增加的授權費用, 為了能夠順利過渡,所以加入了 Broker 的設計,而後才公開資料庫的原始碼並成為一個開放原始碼計畫 (Naver 也只有開放 Broker 關於 CRBRID 方面的原始碼)。


CUBRID 使用 NCurses 5 library,如果是已經升到 6 的系統可以安裝相容 ABI 的函式庫,openSUSE Tumbleweed 安裝的指令如下:

sudo zypper in libncurses5

在官網上下載安裝程式以後安裝(我目前使用 11.3),我是安裝在自己的家目錄下。然後使用下列的指令設定環境變數。
如果是使用 sh, bash 或者是 zsh:

source .cubrid.sh

如果是使用 csh 或者是 tcsh:

source .cubrid.csh

如果要啟動 CUBRID 的服務,使用下列的指令:

cubrid service start

如果要停止 CUBRID 的服務,使用下列的指令:

cubrid service stop

在安裝目錄下的 databases 建立一個目錄 testdb,使用下列的指令建立新的資料庫 testdb:

cubrid createdb testdb en_US.utf8

如果需要刪除資料庫,使用下列的指令:

cubrid deletedb testdb

如果要在 database server 開始的時候指定要使用的資料庫,可以這樣執行指令:

cubrid server start testdb

或者是修改 conf 目錄下的 cubrid.conf,加入下面的設定:

# The list of database servers in all by 'cubrid service start' command.
# This property is effective only when the above 'service' property contains 'server' keyword.
server=testdb

(如果使用設定 cubrid.conf 的方式,就不用使用 cubrid server start,而是在 cubrid service start 時就會跟著啟動 server。)


CUBRID 提供了 csql 工具可以用來下達命令。使用 CSQL 連到 testdb 資料庫(使用 dba 帳號):

csql -u dba testdb

管理帳號 dba 預設的密碼為空白,如果想要設置一個密碼,下面是一個範例:

alter user dba password 'dba';

下面是新增一個使用者與設定其密碼的例子:

CREATE USER danilo;
ALTER USER danilo PASSWORD 'danilo';

相關連結

2024/06/15

星海爭霸

《星海爭霸》 (StarCraft) 是由暴雪娛樂製作發行的即時戰略遊戲,於 1998 年 3 月 31 日正式發行。 遊戲有一部資料片,為《星海爭霸:怒火燎原》(StarCraft: Brood War)。

遊戲描述了26世紀初期,位於銀河系中心的三個種族在克普魯星際空間中爭奪霸權的故事。三個種族分別是:地球人的後裔人族(Terran)、 一種進化迅速的生物群體蟲族(Zerg),以及一支高度文明並具有心靈力量的遠古種族神族(Protoss)。

《星海爭霸》使用了二項資源,Minerals 與 Vespene Gas。

三族提供人口數的建築或者是單位: Terran - Command Center 與 Supply Depot、Zerg - Hatchery 與 Overlord、 Protoss - Nexus 與 Pylon。 而 Pylon 有個重要的地方,除了 Nexus 與 Pylon 本身,大多數的建築都需要在 Pylon 能量範圍內才能夠建造。

在 1.15.2 的更新 Patch 新增了免光碟功能,Windows 平台的 StarCraft 玩家將 CD 裡頭的 install.exe 複製到安裝目錄下, 並重新命名為 StarCraft.mpq,BroodWar 玩家將 CD 裡頭的 install.exe 複製到安裝目錄下,並重新命名為 BroodWar.mpq, 這樣就完成免光碟的功能。1.17 版則是最後一個可以離線安裝的版本。

《星海爭霸重製版》於 2017 年 8 月15 日正式發售。舊版本的星海爭霸在 2017 年 4 月 19 日開始成為免費遊戲。

Detection

《星海爭霸:怒火燎原》因為引進了新單位 Dark Templar (Protoss) 與 Lurker (Zerg), 所以能夠偵測隱形以及遁地的能力比在原版的星海爭霸中更重要,同時也加快了遊戲的節奏。

Protoss

  • Photon Cannon
  • Observer

Terran

  • Comsat Station
  • Missile Turret
  • Science Vessel

Zerg

  • Spore Colony
  • Overlord

2024/04/28

uHex

uHex 是一個簡單的 hex editor, 原本是 DOS 下的應用程式,但是也可以在支援 POSIX 標準並且有安裝 NCurses 函式庫開發檔案的環境編譯。 主要用來作為觀看二進位檔案以及進行簡單的搜尋與編輯。

因為是個小工具,所以在 Linux 系統我自己採用自行編譯的方式。

下面就是他的操作按鍵:
ALT+H - Help (F1 works too)
ALT+J - Jump to offset
ALT+F - Find an ASCII or HEX string occurence
ALT+S - Save file, applying all modifications
ALT+U - Undo all modifications (reverts the file from disk)
ESC - Quit uHex

2024/04/26

DOSBos

DOSBos 是用於執行適用 MS-DOS 相容作業系統的模擬器 (主要的執行目標是遊戲軟體)。

下面是在 openSUSE Tumbleweed 安裝的指令:

sudo zypper in dosbox

要注意的是,openSUSE Tumbleweed 目前所使用 DOSBox 版本為 DOSBox Staging, 這是自 DOSBox 衍生的一個開發版本,其設定檔案在家目錄下的 .config/dosbox/dosbox.conf。

在按鍵部份,如果要切換到 full-screen(或者是切換回來),使用下列的按鍵組合: ALT-ENTER
如果要關閉 DOSBos: CTRL-F9
捕抓或者是釋放滑鼠: CTRL-F10

我習慣建立一個 DOSBox 目錄作為 c:

[autoexec]
# Lines in this section will be run at startup.
# You can put your MOUNT lines here.
MOUNT C /home/danilo/DOSBox/C -freesize 10240
c:

下面是其它的設定:
fullresolution = original
output = openglnb
memsize = 64
aspect = stretch
gus = false
(openSUSE Tumbleweed 的預設值 gus 是 true,需要設為 false,不然音訊輸出會不正常)

如果要掛載一個 iso 檔案,下面是一個例子:

imgmount d "/home/danilo/DOSBox/ISO/MyISO.iso" -t iso

2024/04/22

PostgreSQL

PostgreSQL是一個開源的物件型關聯式資料庫管理系統,在類似 BSD 授權與 MIT 授權的 PostgreSQL 授權下發行。

Install on openSUSE Tumbleweed

如果不是首次安裝而是升級,需要使用 pg_dump 執行 dump the databases,先對目前的資料進行備份。

首先是安裝(在這裡是 PostgreSQL 16):

sudo zypper install postgresql16-server postgresql16 postgresql16-devel \
postgresql16-contrib postgresql-devel
如果需要設定 postgres 的密碼,
sudo passwd postgres
先切換到 root,再切換到 postgres,
sudo su postgres
然後 init database:
/usr/bin/initdb /var/lib/pgsql/data
如果要手動開啟服務,使用:
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
關閉:
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile stop
或者是使用下列的方式開啟服務:
sudo service postgresql start
關閉服務:
sudo service postgresql stop

如果要開機的時候就啟動服務,使用:
sudo chkconfig postgresql on
如果不要,使用:
sudo chkconfig postgresql off

安裝完成以後,可以使用 psql postgres (使用者登入要使用 postgres)來確定目前的設定檔:
SHOW config_file;
目前的 server 版本:
SHOW server_version;
如果要使用 psql 列出目前的 database,使用 \l 命令:
\l
如果要查詢目前的使用者連線:
SELECT * FROM pg_stat_activity;
Digging into the currently opened database in psql one can use the \d command.
\d

Add user to PostgreSQL

如果要使用指令增加使用者和資料庫(需要切換使用者身份到 postgres):
createuser danilo

使用 PostgreSQL SQL shell (psql) 增加使用者。

1. Add a user called danilo

Type the following command to create a user called danilo with a password called danilo:
template1=# CREATE USER danilo WITH PASSWORD 'danilo';

2. Add a database called danilo

Type the following command:
template1=# CREATE DATABASE danilo WITH OWNER danilo ENCODING 'UTF8';

3. Now grant all privileges on database

template1=# GRANT ALL PRIVILEGES ON DATABASE danilo to danilo;


如果要改使用者密碼,下面是 user danilo 的範例:
template1=# ALTER USER "danilo" WITH PASSWORD 'danilo';

或者是使用下列的方式設定 postgres:
template1=# \password postgres

要讓密碼生效,要修改 /var/lib/pgsql/data (或者是指定的 PostgreSQL DATADIR) 目錄下的 pg_hba.conf。 預設的認證方式為 trust,這表示不會做任何的密碼檢查,如果要開啟密碼檢查, 要把 trust 改為 scram-sha-256 或者是其它的認證方式(例如 md5 或者是 peer)。

如果設為 peer 就只會讓 local 同樣的 username 連線。
在需要讓其它的本機使用者連線的情況下,可以改為 scram-sha-256,下面是一個例子:

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

如果想知道目前的 username 與 password,可以使用 psql 查詢 pg_shadow 以後列出來:

psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

如果要查詢目前的使用者列表:

\du

如果要設定某一個使用者為 Superuser:

ALTER USER danilo WITH SUPERUSER;

如果要設定某一個使用者為 No Superuser

ALTER USER danilo WITH NOSUPERUSER;

Configuration

PostgreSQL uses three main configuration files to control overall operations. You can find these files in the initialized data cluster (the folder specified during the initialization process using initdb -d).

如果查詢 source code,我們發現 PostgreSQL 設定來源有14種,如default、environment variable、configuration file、client等,具體如下:
const char *constGucSource_Names[] =
{
        /* PGC_S_DEFAULT */ "default",
        /* PGC_S_DYNAMIC_DEFAULT */ "default",
        /* PGC_S_ENV_VAR */ "environment variable",
        /* PGC_S_FILE */ "configuration file",
        /* PGC_S_ARGV */ "command line",
        /* PGC_S_GLOBAL */ "global",
        /* PGC_S_DATABASE */ "database",
        /* PGC_S_USER */ "user",
        /* PGC_S_DATABASE_USER */ "database user",
        /* PGC_S_CLIENT */ "client",
        /* PGC_S_OVERRIDE */ "override",
        /* PGC_S_INTERACTIVE */ "interactive",
        /* PGC_S_TEST */ "test",
        /* PGC_S_SESSION */ "session"
};

如果無法確定設定的來源,可以使用下列的 SQL 述句查詢:

select name, setting, source from pg_settings;

There are several different types of configuration settings, divided up based on the possible inputs they take

  • Boolean: true, false, on, off
  • Integer: Whole numbers (2112)
  • Float: Decimal values (21.12)
  • Memory / Disk: Integers (2112) or "computer units" (512MB, 2112GB). Avoid integers--you need to know the underlying unit to figure out what they mean.
  • Time: "Time units" aka d,m,s (30s). Sometimes the unit is left out; don't do that
  • Strings: Single quoted text ('pg_log')
  • ENUMs: Strings, but from a specific list ('WARNING', 'ERROR')
  • Lists: A comma separated list of strings ('"$user",public,tsearch2)

下面 14 個項定項目是大多數的使用者可能會需要設定的項目:

  1. listen_address
  2. max_connections
  3. shared_buffers
  4. work_mem
  5. maintenance_work_mem
  6. max_fsm_pages
  7. synchronous_commit
  8. checkpoint_segments
  9. wal_buffers
  10. autovacuum
  11. effective_cache_size
  12. default_statistics_target
  13. constraint_exclusion
  14. log_destination & log settings

之所以需要設定一些項目的理由,在於根據自己機器的狀況設定更好的設定值, 可以讓 PostgreSQL 執行的更好。

Each backend process allocates a local memory area for query processing; each area is divided into several sub-areas – whose sizes are either fixed or variable.

  • work_mem: Executor uses this area for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables by merge-join and hash-join operations.
  • maintenance_work_mem: Some kinds of maintenance operations (e.g., VACUUM, REINDEX) use this area.
  • temp_buffers: Executor uses this area for storing temporary tables.

A shared memory area is allocated by a PostgreSQL server when it starts up. This area is also divided into several fix sized sub-areas.

  • shared buffer pool: PostgreSQL loads pages within tables and indexes from a persistent storage to here, and operates them directly.
  • WAL buffer: To ensure that no data has been lost by server failures, PostgreSQL supports the WAL mechanism. WAL data (also referred to as XLOG records) are transaction log in PostgreSQL; and WAL buffer is a buffering area of the WAL data before writing to a persistent storage.
  • commit log: Commit Log(CLOG) keeps the states of all transactions (e.g., in_progress,committed,aborted) for Concurrency Control (CC) mechanism.

如果要查詢目前的設定,可以使用下面的命令:

SHOW ALL;

如果要查詢單項,以 work_mem 來說:

show work_mem;
或者是查詢 pg_settings (a “system view” can be queried),
SELECT * FROM pg_settings WHERE name = 'work_mem';

All these can be edited with a text editor.

File Purpose
postgresql.conf Controls the listening port, IP, and default query planner settings, memory settings, path settings, and logging settings. Can be queried via pg_settings database view.
pg_hba.conf Controls the authentication models used by PostgreSQL and can be set per user, per database, per IP range, or a combination of all.
pg_indent.conf Controls mapping of an OS user to a PostgreSQL user.

postgresql.conf

The following settings are all located in the postgresql.conf file. Remember that these are default settings; many of these you can choose to override for each session, for each database, or for each user/role.

Option Description
listen_addresses Use ‘*’ to listen on all IPs of the server, ‘localhost’ to listen on just local, or a comma separated list of IPs to listen on. Requires service restart if changed and can only be set globally.
port Defaults to 5432, but can be changed to allow multiple postgresql daemon clusters/versions to coexist using same IP but different ports.
search_path List of default schemas that don’t need schema qualification. First schema is where non-schema qualified objects are created.
constraint_exclusion Options: on, off, or partial. Partial was introduced in 8.4 and is the new default. Allows planner to skip over tables if constraint ensures query conditions cannot be satisfied by the table. Mostly used for table partitioning via table inheritance.
shared_buffers Controls how much memory is allocated to PostgreSQL and shared across all processes. Requires service restart and can only be set globally.

In PostgreSQL 9.4, a new SQL construction ALTER SYSTEM was introduced that allows you to set these settings at the system level without editing the postgresql.conf. For many, you still need to do a service restart and for others at least a:

SELECT pg_reload_conf();

pg_hba.conf

PostgreSQL supports many authentication schemes to control access to the database. The pg_hba.conf file dictates which schemes are used based on the rules found in this file. You can mix and match various authentication schemes at the same time. The rules are applied sequentially such that the first match fitting a connection is the one that is used. This is important to remember because if you have a more restrictive rule above a less restrictive, then the more restrictive is the one that trumps.

The most commonly used authentication schemes are trust (which allows connections without a password) and md5 (which authenticates with md5 encrypted passwords). Others include: reject, crypt, password (this is plain text), krb5, ident (authenticate simply by identity of user in OS), pam, and ldap.


如果我們需要每個 query 的執行時間,可以在 psq 使用下列的命令開啟設定(預設值有可能是關閉):
\timing

Tools

PostgreSQL comes bundled with several tools useful for administration and query writing.

Tool Description
psql Command-line client packaged with PostgreSQL. Good for automating SQL jobs, copying data, outputing simple HTML reports.
createdb, dropdb For creating and dropping a database from the OS shell.
pgAdminIII Popular graphical user interface packaged with PostgreSQL.
pg_restore Command-line tool for restoring compressed or .tar backups.
pg_dump Command-line tool for doing backups. Great for automated backups.
pg_dumpall Command-line tool for dumping all databases into a single backup.
pgAgent A daemon/service that can be downloaded from http://www.pgadmin.org/download/pgagent.php. Used for scheduling SQL jobs and batch shell jobs. Jobs can be added easily and monitored using the PgAdmin III job interface.
pg_basebackup Used for doing filesystem hot backup of db data cluster.
pg_upgrade Used for updating in place from one major version of PostgreSQL to another.

SQL statement

Auto-increment column

PostgreSQL 並不支援標準語法,而是提供一個自己實作的近似語法:
CREATE TABLE t1 (col1 SERIAL PRIMARY KEY);

UPSERT

在 PostgreSQL 15 之前 PostgreSQL 的 UPSERT 語法並未採用標準的 merge,而是使用自己實作的語法。 不過在 PostgreSQL 15 開始,就已經支援 SQL 的 merge 語法。

舉例來說,我們建立下面一個表格:

create table notes (title varchar(255), body text, modified timestamp, primary key(title));
於是可以使用 ON CONFLICT DO UPDATE SET 方式來決定是要 update 或者是 insert(PS. 舉例是用 GOLANG 的常數宣告):
        const insertString = `
              insert into notes (title, body, modified)
              values($1, $2, now()) ON CONFLICT (title)
              DO UPDATE SET (body,modified) = (EXCLUDED.body,now())`

Views

You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table:
CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.

Foreign Keys

Foreign Keys 是維護你資料的 referential integrity 的方法,當插人一個新資料的時候,確定參考的資料是存在的。

下面是一個使用的範例:
CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

Unique Constraints

Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The syntax is:

CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
when written as a column constraint, and:
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);

Transactions

In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands.

By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a regular function or aggregate function. The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY list within OVER specifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.

下面是一個範例:
SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
結果:
  depname  | empno | salary | rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

As shown here, the rank function produces a numerical rank within the current row's partition for each distinct ORDER BY value, in the order defined by the ORDER BY clause. rank needs no explicit parameter, because its behavior is entirely determined by the OVER clause.

The rows considered by a window function are those of the "virtual table" produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways by means of different OVER clauses, but they all act on the same collection of rows defined by this virtual table.

Inheritance

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

下面是一個使用的範例:
CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);
當你查詢的時候,會列出 cities 和 capitals 的結果,如果只要 cities 的部份,下面是一個使用的範例:
SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

WITH Queries (Common Table Expressions)

WITH 是用來建立暫存的資料集,稱為通用資料表運算式 CTE (common table expression) 的暫存具名結果集。 通用資料表運算式可以包括指向本身的參考。 這稱為遞迴通用資料表運算式。

下面的資料來自於 PostgreSQL 網站

In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable.

The WITH query being CTE query, is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.

The optional RECURSIVE modifier changes WITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using RECURSIVE, a WITH query can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output. Such a query is executed as follows:

  1. Evaluate the non-recursive term. For UNION (but not UNION ALL), discard duplicate rows. Include all remainingrows in the result of the recursive query, and also place them in a temporary working table.

  2. So long as the working table is not empty, repeat these steps:

    1. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION (but not UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.

    2. Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table.

Recursive queries are typically used to deal with hierarchical or tree-structured data.
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

Index

下面是一個建立 index 的範例:
CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

CREATE INDEX test1c_content_index ON test1c (content);

GIN and GiST Index Types

There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable.

CREATE INDEX name ON table USING GIN (column);
and:
CREATE INDEX name ON table USING GIST (column);

GIN indexes are the preferred text search index type. As inverted indexes, they contain an index entry for each word (lexeme), with a compressed list of matching locations. Multi-word searches can find the first match, then use the index to remove rows that are lacking additional words. GIN indexes store only the words (lexemes) of tsvector values, and not their weight labels. Thus a table row recheck is needed when using a query that involves weights.

A GiST index is lossy, meaning that the index might produce false matches, and it is necessary to check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when needed.) GiST indexes are lossy because each document is represented in the index by a fixed-length signature. The signature is generated by hashing each word into a single bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct.

Lossiness causes performance degradation due to unnecessary fetches of table records that turn out to be false matches. Since random access to table records is slow, this limits the usefulness of GiST indexes. The likelihood of false matches depends on several factors, in particular the number of unique words, so using dictionaries to reduce this number is recommended.

Full Text Search

Full text searching in PostgreSQL is based on the match operator @@, which returns true if a tsvector (document) matches a tsquery (query).

下面是一個使用的例子:
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
 ?column?
----------
 t

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
----------
 f
另外的例子:
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
 ?column?
----------
 t
The @@ operator also supports text input, allowing explicit conversion of a text string to tsvector or tsquery to be skipped in simple cases. The variants available are:
tsvector @@ tsquery
tsquery  @@ tsvector
text @@ tsquery
text @@ text
It is possible to do a full text search without an index. A simple query to print the title of each row that contains the word friend in its body field is:
SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
PostgreSQL 可以使用 GIN index 來加快 text search 的速度:
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));

UUID

可以安裝 uuid-ossp 模組來處理 UUID。下面是安裝的方式(需要有 Superuser 的權限):

CREATE EXTENSION "uuid-ossp";

於是就可以使用下列的方式生成 UUID:

select uuid_generate_v4();

或者也可以使用下列的方式生成 UUID:

select (md5(random()::text || clock_timestamp()::text))::uuid;

NoSQL 與 PostgreSQL

PostgreSQL 提供了可選的 HStore key-value store extension, 並且 HStore extension 具有將 key-value store 轉為 JSON 的能力(hstore_to_json function)。

測試 PostgreSQL 是否有支援 hstore(使用 CREATE EXTENSION 語句):
package require tdbc::postgres
tdbc::postgres::connection create db -user postgres -password postgres -port 5432

set statement [db prepare {
     CREATE EXTENSION hstore
}]

$statement foreach row {
 puts $row
}

$statement close
db close
下面是一個使用的範例:
CREATE TABLE products (
  id serial PRIMARY KEY,
  name varchar,
  attributes hstore
);
From here you can insert whatever you want into the attributes column. And then query based on those various keys or values.
INSERT INTO products (name, attributes) VALUES (
 'Geek Love: A Novel',
 'author    => "Katherine Dunn",
  pages     => 368,
  category  => fiction'
);

SELECT name, attributes->'author' as author
FROM products
WHERE attributes->'category' = 'fiction'
The obvious benefit here is flexibility, but where it really shines is being able to leverage various index types. In particular, a GIN or GiST index will index every key and value within the hstore. This way when you filter on something it’ll use the index if it makes sense to the planner within Postgres.

PostgreSQL and JSON

在 PostgreSQL 9.2 (及以後)版本中,查詢結果可以轉換為 JSON data type 後返回。 PostgreSQL 也增加了 PLv8 extension,可以使用 JavaScript 來撰寫 PostgreSQL stored procedure。

That has changed with 9.4, with the introduction of the JSONB data type, which stores JSON data in binary form, such that it is both more compact and more efficient than the textual form. Moreover, the same GIN and GIST indexes that now are able to work so well with HStore data also are able to work well, and quickly, with JSONB data. So you can search for and retrieve text from JSONB documents as easily (or more) as would have been the case with a document database, such as MongoDB.

PostgreSQL provides two native operators -> and ->> to help you query JSON data.
  • The operator -> returns JSON object field by key.
  • The operator ->> returns JSON object field by text.
下面是一個使用的例子:
CREATE TABLE integrations (id UUID, data JSONB);
INSERT INTO integrations VALUES (
  uuid_generate_v4(),
  '{
    "service": "salesforce",
    "id": "AC347D212341XR",
    "email": "craig@citusdata.com",
    "occurred_at": "8/14/16 11:00:00",
    "added": {
      "lead_score": 50
    },
    "updated": {
      "updated_at": "8/14/16 11:00:00"
    }
    }')
下面是一個最簡單的例子:
CREATE TABLE people(
    id serial primary key,
    data jsonb not null
);

INSERT INTO people(data) VALUES ('{
    "name": "Bob",
    "addresses": [
        {
            "street": "Centre",
            "streetnumber": 24,
            "town": "Thornlie",
            "state": "WesternAustralia",
            "country": "Australia"
        },
        {
            "street": "Example",
            "streetnumber": "4/311-313",
            "town": "Auckland",
            "country": "NewZealand"
        }
    ],
    "phonenumbers": [
        {
            "type": "mobile",
            "number": "12345678"
        }
    ]
}');

INSERT INTO people(data) VALUES ('{
  "name": "Fred",
  "phonenumbers": [
    { "type": "mobile", "number": "12345678" }
  ]
}');

INSERT INTO people(data) VALUES ('{
  "name": "John Doe"
}');
下面則是使用 inner join 查詢 phonenumbers 的做法:
select
  p1.id AS person1,
  p2.id AS person2,
  p1.data ->> 'name' AS "p1 name",
  p2.data ->> 'name' AS "p2 name",
  pns1 ->> 'type' AS "type",
  pns1 ->> 'number' AS "number"
from people p1
  inner join people p2
    on (p1.id > p2.id)
  cross join lateral jsonb_array_elements(p1.data -> 'phonenumbers') pns1
  inner join lateral jsonb_array_elements(p2.data -> 'phonenumbers') pns2
    on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');
The ? operator will tell us if some part of JSON has a top level key:
SELECT *
FROM companies
WHERE data->'company'->'tags' ? 'B2B'
如果要回來的 JSONB 結果比較漂亮,可以使用:
SELECT jsonb_pretty(data)
FROM companies;

PostgreSQL and XML

PostgreSQL 提供了 XML data type and support functions 的功能 (SQL/XML), 並且提供了一定程度的 XPATH 支援(xpath_exists function) 與輸出 XML 文件的能力,因此也可以考慮將 PostgreSQL 作為一個 XML 文件資料庫使用。

下面是一個 PostgreSQL XPath function 處理 XML 字串的例子(要注意例子中在 xpath() 的第三個參數設定了 namespace 的別名 n):
WITH x AS ( SELECT
'<promotions xmlns="http://www.demandware.com/xml/impex/promotion/2008-01-31">
    <campaign campaign-id="2013-1st-semester-jet-giveaways">
        <description>2013 1st Semester Jet Giveaways</description>
        <enabled-flag>true</enabled-flag>
        <start-date>2013-01-01T05:00:00.000Z</start-date>
        <end-date>2013-07-01T04:00:00.000Z</end-date>
        <customer-groups>
            <customer-group group-id="Everyone"/>
        </customer-groups>
    </campaign>
 </promotions>'::xml AS t
)
SELECT xpath('/n:promotions/n:campaign/n:description/text()', t
           , '{{n,http://www.demandware.com/xml/impex/promotion/2008-01-31}}')
FROM   x;

List extensions

如果要列出目前系統上的 PostgreSQL extension name(使用 TDBC):

package require tdbc::postgres
tdbc::postgres::connection create db -user postgres -password postgres -port 5432

set statement [db prepare {
     select extname from pg_extension
}]

$statement foreach row {
 puts $row
}

$statement close
db close

List tables

使用 psql 列出 schema public 下的所有 table:
\dt public.*
You can select the tables from information_schema:
SELECT * FROM information_schema.tables WHERE table_schema = 'public';
或者也可以使用下列的方式:
select * from pg_tables where schemaname='public';

Client

C API (libpq)

The libpq library is the C interface to PostgreSQL. It is a set of library functions that allow client programs to interact with PostgreSQL.

下面是一個使用的範例,例印 PostgreSQL libpq 的版本:
#include <libpq-fe.h>

int main() {

    int lib_ver = PQlibVersion();

    printf("Version of libpq: %d\n", lib_ver);

    return 0;
}
使用下列的方式得到 include/library path:
$ pg_config --includedir
/usr/include/postgresql
$ pg_config --libdir
/usr/lib/postgresql95/lib64
所以可以使用下列的方式編譯:
gcc lib_version.c -I`pg_config --includedir` -L`pg_config --libdir` -lpq -std=c99 -o test

再來是列印 PostgreSQL server 的版本:
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

void do_exit(PGconn *conn) {

    PQfinish(conn);
    exit(1);
}

int main() {

    PGconn *conn = PQconnectdb("user=postgres password=postgres dbname=postgres");

    if (PQstatus(conn) == CONNECTION_BAD) {

        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
        do_exit(conn);
    }

    int ver = PQserverVersion(conn);

    printf("Server version: %d\n", ver);

    PQfinish(conn);

    return 0;
}

下面是一個簡單的 query 程式:
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

void do_exit(PGconn *conn) {

    PQfinish(conn);
    exit(1);
}

int main() {

    PGconn *conn = PQconnectdb("user=postgres password=postgres dbname=postgres");i

    if (PQstatus(conn) == CONNECTION_BAD) {

        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
        do_exit(conn);
    }

    PGresult *res = PQexec(conn, "SELECT VERSION()");

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {

        printf("No data retrieved\n");
        PQclear(res);
        do_exit(conn);
    }

    printf("%s\n", PQgetvalue(res, 0, 0));

    PQclear(res);
    PQfinish(conn);

    return 0;
}

ODBC (Linux/openSUSE)

下載 PostgreSQL ODBC Driver 編譯並且安裝以後,

unixODBC 需要設定二個檔案:/etc/unixODBC/odbc.ini/etc/unixODBC/odbcinst.ini。 下面則是我目前 PostgreSQL 的設定。

odbcinst.ini:

[PSQL]
Description=PostgreSQL
Driver64=/usr/lib64/psqlodbcw.so
UsageCount=1

odbc.ini:

[PostgreSQL]
Description=PostgreSQL Data Source
Driver=PSQL
Database=postgres
UserName=postgres
Password=postgres
Host=localhost
Port=5432
ReadOnly=No
ShowSystemTables=No

下面就是測試的 Tcl script:

package require tdbc::odbc

set connStr "DSN=PostgreSQL; UID=postgres; PWD=postgres;"
tdbc::odbc::connection create db $connStr

set statement [db prepare {
    SELECT VERSION()
}]

$statement foreach row {
    puts [dict get $row version]
}

$statement close
db close

JDBC

PostgreSQL 提供了 JDBC driver 可以使用。

下面是使用 TDBCJDBC 的範例:
package require tdbc::jdbc

set className    {org.postgresql.Driver}
set url          jdbc:postgresql://localhost:5432/danilo
set username     danilo
set password     danilo

tdbc::jdbc::connection create db $className $url $username $password -readonly 0

set statement [db prepare {select extname, extversion from pg_extension}]
puts "List extension name and version:"
$statement foreach row {
    puts "[dict get $row extname] - [dict get $row extversion]"
}

$statement close

db close

Node.js

我們使用 node-postgres 來連線。

首先建立一個目錄 pg-test,並且在目錄下執行

npm init -y

這會建立一個 package.json 檔案,我們可以對需要修改的部份再進行修改。修改 package.json,加入下列的設定:

  "type": "module",

再來使用 NPM 安裝 node-postgres:

npm install pg --save

建立 index.js,內容如下:

import pg from 'pg';

const config = {
    host: 'localhost',
    user: 'danilo',
    password: 'danilo',
    database: 'danilo',
    port: 5432,
    ssl: false
};

const client = new pg.Client(config);

client.connect(err => {
    if (err) {
        console.log("Connection failed.");
        throw err;
    }
});

const query = 'SELECT VERSION()';

client.query(query)
.then(res => {
   const rows = res.rows;
   console.log(`PostgreSQL version: ${rows[0].version}`);
   client.end();
   process.exit();
}).catch(err => {
   console.log(err);
});

執行的時候使用 node index.js 來執行。

Tcl client side interface

Pgtcl is a Tcl package for client programs to interface with PostgreSQL servers. 建立在 libpq 的基礎之上。

下面是一個最簡單的例子:

package require Pgtcl

set db [pg_connect -conninfo [list host = localhost user = postgres password = postgres dbname = postgres]]

pg_select $db "select version() as versoin" version {
    parray version
}

pg_disconnect $db

和 JDBC 單純的使用 ? 來代表參數不同,Pgtcl/libpq 使用 $1, $2 等來代表要傳遞的參數。


如果要使用共同的資料庫存取介面,建議使用 Tcl 8.6 內建的 TDBC 所提供的 PostgreSQL driver 來與 PostgreSQL server 進行連線。 Pgtcl 只有在單純使用 PostgreSQL 時才需要使用。

要注意的是,TDBC-Postgres 使用 PQprepare 來送出 SQL statement,同時並不指定 type,而是讓 PostgreSQL 來推斷 type, 再使用 PQdescribePrepared 來取得相關的 param 資料。但是 Pgtcl 不支援 PQprepare(只送出 SQL statement 到 server 分析), 所以需要使用 PREPARE statement 才行。

PostgreSQL: Foreign data wrappers

In 2003, a new specification called SQL/MED ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects from SQL databases. In 2011, PostgreSQL 9.1 was released with read-only support of this standard, and in 2013 write support was added with PostgreSQL 9.3.

在使用 FDW 的情況下,PostgreSQL 具有存取外部資料源的能力,並且可以作為一個 SQL query engine 使用。 也就是在 FDW 的處理範圍內,PostgreSQL 也有讀取 Hadoop/HBase big data store 的能力(看 FDW 所提供的能力而定)。

PostgreSQL FDW 的 Column-Oriented Wrappers 有二個選擇, 一個是 monetdb_fdw, 一個是 cstore_fdw

monetdb_fdw

使用 monetdb_fdw 來進行 PostgreSQL Foreign_data_wrappers 的測試。

如果不從 PostgreSQL source code 一起編譯,使用下列的方式:
make USE_PGXS=1
如果 MonetDB 的 MAPI lib 與 include 目錄不正確,需要修改 Makefile。

下面是 Makefile 修改的範例:
SHLIB_LINK = -L/usr/local/lib64 -lmapi
PG_CPPFLAGS = -I/usr/local/include/monetdb

再來是安裝:
sudo make USE_PGXS=1 install
需要修改 /var/lib/pgsql/data/postgresql.conf,加入下列的設定:
shared_preload_libraries = 'monetdb_fdw'

要注意的是,重新啟動 PostgreSQL 前要確定 monetdb_fdw 可以正確的尋找到 MonetDB MAPI library 的路徑位置, 否則要設定才行,不然 PostgreSQL 會無法正確啟動

下面是一個例子:
export LD_LIBRARY_PATH=/usr/local/lib64:$LD_LIBRARY_PATH
然後重新啟動 PostgreSQL。


下面是使用 psql 來進行是否可以使用的測試:
CREATE EXTENSION monetdb_fdw;
再來是建立 server object:
CREATE SERVER monetdb_server FOREIGN DATA WRAPPER monetdb_fdw;
然後建立與 MonetDB table 對應的 foreign table。假設 MonetDB 有一個 person 的 table, 我們可以對應過來以後,就可以對這個表格進行操作。

下面是一個例子:
CREATE FOREIGN TABLE person (id integer, name varchar(40)) SERVER monetdb_server
OPTIONS (host 'localhost', port '50000', user 'monetdb', passwd 'monetdb', dbname 'demo', table 'person');
然後就可以查詢 MonetDB person 表格:
SELECT * FROM person;

如果要刪除不用的 FDW 並且完整的移除,要先刪除全部的 Foreign table:
DROP FOREIGN TABLE person;
Then drop the monetdb server and extension:
DROP SERVER monetdb_server;
DROP EXTENSION monetdb_fdw;
Then remove monetdb_fdw from shared_preload_libraries in your postgresql.conf:
#shared_preload_libraries = 'monetdb_fdw'
Finally remove the monetdb_fdw files, into the monetdb_fdw development folder, execute:
sudo make USE_PGXS=1 uninstall

openSUSE 切換輸入法框架

雖然目前 openSUSE 仍沒有切換輸入法框架的工具,但只要設定好 INPUT_METHOD 這個環境變數,就可以覆蓋原先為不同語言所設定的預設值。 例如可以設定 INPUT_METHOD="ibus",下次登入就會使用 ibus 輸入法框架。 而可用的輸入法框架,可以參考 /etc/X11/xim.d(openSUSE Tumbleweed 的位置在 /usr/etc/X11/xim.d)目錄下的設定。

一般而言,這些設定都是在設定 GTK_IM_MODULE, QT_IM_MODULE 與 XMODIFIERS 這幾個環境變數, 並且啟動 input method server。以 fcitx5 為例:

#make sure set these vars before dbus-launch
export LC_CTYPE=$LANG
export XMODIFIERS="@im=fcitx"
export GTK_IM_MODULE=fcitx
export QT_IM_SWITCHER=imsw-multi
export QT_IM_MODULE=fcitx

# FIXME: a little bit of delay is required to avoid race (boo#947576)
(sleep 2; fcitx5 -d) &

# success:
return 0

在 openSUSE leap 42.1 以及之後的環境(包含 openSUSE Tumbleweed), INPUT_METHOD 環境變數可以在 ~/.i18n 中設定。下面是 iBus 的設定範例。

#
# Override system wide input method here
#

export INPUT_METHOD=ibus

然後重新登入就會使用指定的輸入法框架。

ibus 的設定(包含輸入法引擎)使用 gconf 來儲存,可以用來觀察目前有的選項,如果因為某些因素需要透過非設定程式的方式修改, 或者在開發程式的過程中想要刪除自己開發過程中最後不用的設定項目,可以使用 dconf-editor 來修改。 dconf-editor 安裝的方式如下:

sudo zypper in dconf-editor

在 openSUSE Leap 15.5 因為修改 systemd xdg autostart 啟動方式,導致 ibus 在 KDE 無法正確啟動, 按照 Bug 1211977 的暫時解法, 需要修改二個檔案:

/usr/bin/ibus-autostart

# boo#1211977: xdg-autostart-generator cannot launch ibus with -d
if [ "$XDG_CURRENT_DESKTOP" = "KDE" ]; then
    exec ibus-daemon --xim
fi

ibus-daemon --xim -d
exit 0
/etc/xdg/autostart/ibus-autostart.desktop
[Desktop Entry]
Name=IBus
GenericName=IBus Daemon
Comment=Start IBus daemon
Exec=ibus-autostart
Icon=ibus-setup
Terminal=false
Type=Application
StartupNotify=false
NoDisplay=true
#X-GNOME-Autostart-Phase=Applications
#X-GNOME-AutoRestart=false
#X-GNOME-Autostart-Notify=false
X-KDE-autostart-after=panel
X-KDE-StartupNotify=false

如果原本沒有安裝,現在要在 openSUSE Tumbleweed 使用 Fcitx 5 並且安裝一些輸入法(包含酷音輸入法), 使用下列的指令:

sudo zypper install fcitx5 fcitx5-table-extra fcitx5-zhuyin fcitx5-chewing

並且在 ~/.i18n 中設定 :

#
# Override system wide input method here
#

export INPUT_METHOD=fcitx5

openSUSE Tumbleweed 可以使用上述的方式加入 Fcitx 5。

另外,.i18n 這個檔案除了可以用來設定輸入法框架,也可以用來設定 locale。 對於 Fcitx 5 而言,除了 /etc/locale.conf,還可以在設定 INPUT_METHOD 環境變數之前設定 LANG, 即可設定 Fcitx 5 的 locale。 Fcitx5 使用系統設定,而不是桌面系統的設定決定使用何種 locale,如果你二者的設定不同,可以在這裡設定你想要的。

另外,Fcitx 5 有個麻煩的地方,就是目前家目錄設定 .i18n 的方式如果切換到其它輸入法框架,Fcitx 5 會對其它的框架造成干擾。 原因是 Fcitx 5 套件會在 /etc/xdg/autostart 放檔案 org.fcitx.Fcitx5, 即使目前的輸入法框架不是使用 Fcitx 5 一樣會自動啟動,如果有需要使用其它的輸入法框架在檔案 org.fcitx.Fcitx5 加入下列的設定。

Hidden=true

下面的小知識與如何切換輸入法框架無關。如果需要檢查 keyboard event(press and release), 在 X Window 系統下可以使用 xev 指令檢查:

xev -event keyboard

如果是在 Wayland 系統,則可以使用 wev 指令檢查。

2024/04/21

Basic Linux commands

Commands

systemd and sysvinit

systemd 是 Linux 作業系統之下的一套中央化系統及設定管理程式(init),包括有常駐程式、函式庫以及應用軟體, 因為使用了 cgroup 與 fanotify 等 Linux 組件以實現其特性,所以只適用於 Linux。 有不少 Linux distribution 已將預設 init 程式從 SysVinit 改為使用 systemd, 但是目前仍然也有使用 SysVinit 或者是其它 init 程式的 Linux distribution。

Linux kernel 開機後,第一個執行的程式為 /sbin/init,如果使用 systemd 的系統,那麼這個檔案會是指向 systemd 的連結。 如果要檢查目前系統使用的 init 程式:

ps 1

檢查 systemd 是否有在使用:

stat /sbin/init

也可以使用 readlink 檢查:

readlink /sbin/init

如果是使用 systemd 的系統,Linux 可以選擇從 CLI 或者是 GUI 開機,如果要知道目前的選項,使用下面的命令:

sudo systemctl get-default

如果只是要 run-time 改變,改為 CLI:

sudo systemctl isolate multi-user.target

改為 GUI:

sudo systemctl isolate graphical.target

如果是要修改預設值從 CLI 開機:

sudo systemctl set-default multi-user.target

修改預設值從 GUI 開機:

sudo systemctl set-default graphical.target

如果預設為 GUI 環境,使用 ALT + CTRL + F1 ~ F6 可以切換到 console,如果要切回來, 使用指令 w 列出目前登入的使用者與 ttyx 的使用情況,按 ALT + Fx (x=1, 2, 3.., etc) 切回來。


如果是 sysvinit 系統,修改 /etc/inittab 檔案(修改預設值從 CLI 開機,那麼將 5 改為 3):

id:5:initdefault:

Shell

Shell 是使用者與 Linux kernel 溝通的橋樑,使用者輸入命令來執行各種各樣的任務。

UNIX 的 shell 有兩大主流,一個是 Bourne shell, 為貝爾實驗室 Stephen Bourne 在 1977 年在 Version 7 Unix 中針對大學與學院發布; 一個是 C shell,為 Bill Joy 在 BSD 系統上開發。

目前大多數的 Linux distribution 預設的 Shell 為 GNU Bash。 Bash (Bourne Again SHell) 是在 1987 年由布萊恩·福克斯為了 GNU 計劃而編寫,是 Bourne shell 的後繼相容版本與開放原始碼版本。 1989 年釋出第一個正式版本,原先是計劃用在 GNU 作業系統上,但能執行於大多數類 Unix 系統的作業系統之上。

下面是我的個人設定(使用家目錄下的 .bashrc),用來設定 Prompt 與一些命令的彩色化設定。

# Prompt
export PS1="\[\e[1;36m\]\u\[\e[0m\]@\h:\[\e[38;5;214m\]\w\[\e[0m\]> "

# Bash setting
export HISTCONTROL=ignoreboth

# alias command
alias ls="ls --color=auto"
alias diff="diff --color"
alias grep="grep --color=auto"

# Colors in Man Pages
export GROFF_NO_SGR=1         # For Konsole and Gnome-terminal
man() {
    LESS_TERMCAP_mb=$'\e[01;31m' \
    LESS_TERMCAP_md=$'\e[01;31m' \
    LESS_TERMCAP_me=$'\e[0m' \
    LESS_TERMCAP_se=$'\e[0m' \
    LESS_TERMCAP_so=$'\e[01;44;33m' \
    LESS_TERMCAP_ue=$'\e[0m' \
    LESS_TERMCAP_us=$'\e[01;32m' \
    command man "$@"
}

Tcsh 是一個向下相容 c shell 的 Unix shell。 它本質上是為 c shell 增加命令補完,命令編輯等其他功能。 Tcsh 目前是 FreeBSD 和其延伸發行版的預設 shell。

下面是我的個人設定(使用家目錄下的 .tcshrc),用來設定 Prompt 與一些命令的彩色化設定。

# Prompt
set promptchars = "%#"
set _orange="%{\033[38;5;214m%}"
set _cyan="%{\033[1;36m%}"
set _white="%{\033[0m%}"
set prompt="${_cyan}%n${_white}@%m:${_orange}%~${_white}%# "

unset _orange
unset _cyan
unset _white

# Tcsh setting
set histdup=erase

# alias command
alias ls="ls --color=auto"
alias diff="diff --color"
alias grep="grep --color=auto"

# Colors in Man Pages
set GROFF_NO_SGR=1         # For Konsole and Gnome-terminal
setenv LESS_TERMCAP_mb $'\e[01;31m'
setenv LESS_TERMCAP_md $'\e[01;31m'
setenv LESS_TERMCAP_me $'\e[0m'
setenv LESS_TERMCAP_se $'\e[0m'
setenv LESS_TERMCAP_so $'\e[01;44;33m'
setenv LESS_TERMCAP_ue $'\e[0m'
setenv LESS_TERMCAP_us $'\e[01;32m'

bash 可以使用 history 列出之前使用過的命令。
history -c 可以清除記錄的命令。

如果要將工作放入背景執行,在命令的最後加入 & 即可。
如果是要將工作暫停放入背景中,使用 CTRL + Z
承上,那要怎麼知道目前背景有哪些工作? 使用 jobs 可以列出來(-l 還會列出 PID 的號碼,-r:列出正在背景工具的程序, -s:則是列出正在背景中暫停的工作)。
承上,觀察上面的列表,在前面會有代表的號碼。可以使用 fg 或者是 fg %jobnumber 來將背景的工作恢復為前景運行中。
如果需要刪除背景的工作,可以使用 kill,方式為 kill %jobnumber
Bash 也有支援將工作送到背景的指令,使用 bg 或者是 bg %jobnumber

上面描述的都是 bash 產出的子程序,所以 logout 之後背景程序一樣會消失。 如果需要將工作放到系統背景,可以使用 nohup

File system

Linux 採用索引式檔案系統 (indexed allocation),通常會將這兩部份的資料分別存放在不同的區塊,權限與屬性放置到 inode 中, 至於實際資料則放置到 data block 區塊中。 另外,還有一個超級區塊 (superblock) 會記錄整個檔案系統的整體資訊,包括 inode 與 block 的總量、使用量、剩餘量等。

每個 inode 與 block 都有編號,至於這三個資料的意義可以簡略說明如下:

  • superblock:記錄此 filesystem 的整體資訊,包括 inode/block 的總量、使用量、剩餘量, 以及檔案系統的格式與相關資訊等;
  • inode:記錄檔案的屬性,一個檔案佔用一個inode,同時記錄此檔案的資料所在的 block 號碼;
  • block:實際記錄檔案的內容,若檔案太大時,會佔用多個 block 。

由於每個 inode 與 block 都有編號,而每個檔案都會佔用一個 inode ,inode 內則有檔案資料放置的 block 號碼。 因此,如果能夠找到檔案的 inode 的話,那麼自然就會知道這個檔案所放置資料的 block 號碼, 也就能夠讀出該檔案的實際資料。

File Permission

Every file in Unix has the following attributes −

  • Owner permissions − The owner's permissions determine what actions the owner of the file can perform on the file.

  • Group permissions − The group's permissions determine what actions a user, who is a member of the group that a file belongs to, can perform on the file.

  • Other (world) permissions − The permissions for others indicate what action all other users can perform on the file.

The permissions of a file are the first line of defense in the security of a Unix system. The basic building blocks of Unix permissions are the read, write, and execute permissions.

UNIX 系統使用 chmod 修改 permission。
下面是一個使用的例子(要注意 Unix permission 採用八進位計算):

chmod 644 index.html

如果要加上執行權限:

chmod +x index.html

如果要刪除執行權限:

chmod -x index.html

如果要更改擁有者或者是檔案的群組,使用 chown 進行修改。

Superuser

Linux 系統最高權限的管理者帳號為 root(user id 為 0 的帳號),也就是超級使用者(superuser)帳號。 因為 root 帳號權限很高,通常不建議直接使用 root 登入系統進行管理系統的動作。

su 指令可以讓一般使用者取得 root 權限,取得 root 權限的使用者就如同 root 一樣可以對系統進行各種管理動作。 另外,su 除了可以讓一般使用者取得 root 權限之外,也可以取得其他的帳號權限。下面是一個例子:

su danilo

sudo 指令類似 su,也是用來取得 root 或是其他帳號的權限,一般使用者可以在指令前加上 sudo 來以 root 身分執行任何指令。 系統會提示使用者提供 root 密碼。如果驗證成功,便會以 root 身分執行指令。

id -un 指令會列印目前使用者的登入名稱:

sudo id -un

sudo 可以使用 -u 指定要切換的使用者,-g 指定要切換的群組。

sudo 的主要規則組態檔案為 /etc/sudoers(如果是 openSUSE Tumbleweed,那麼使用 /usr//etc/sudoers)。 如果檔案格式錯誤,使用者可能無法順桘進入系統,因此強烈建議使用 visudo 來進行編輯。 visudo 可以防止發生編輯衝突,並會在儲存修改內容之前檢查語法錯誤。

Locale

Locale 設定由三個部分所組成:語言代碼 (Language Code)、地域 (Territory)、編碼 (Encoding)。 Locale 的設定名稱就是由這三個一起組成。

可以執行 locale 查詢目前的設定。

  1. LC_CTYPE: 字元分類及處理方式。
  2. LC_COLLATE: 字元順序與字串比較。
  3. LC_MESSAGES: 程式中用何種語言來顯示訊息。
  4. LC_MONETARY: 貨幣顯式格式。
  5. LC_NUMERIC: 數字顯式格式。
  6. LC_TIME: 日期與時間的顯式格式。
  7. LC_ALL: 一次設定以上所有的類別。
  8. LANG: 也可用來一次設定所有的 locale 環境,優先權低於 LC_ALL。

如果要列出目前支援的語系,使用下列的指令:

locale -a

Linux distribution 各個設定檔案(以及設定檔案位置)可能不同。

語系設定可以分為二個部份,系統預設的語系與桌面環境的設定。openSUSE 系統的設定在 /etc/locale.conf
如果在 KDE 環境下,KDE 會參考家目錄下的 .config/plasma-localerc 設定,因此桌面環境的設定可以不同於系統的設定。

如果要修改系統語系,可以嘗試下列的指令:

localectl set-locale LANG=en_US.utf8

如果想使用修改設定檔的方式,下面是修改 /etc/locale.conf 設定主語系 LANG 變數的例子:

LANG=zh_TW.UTF-8

系統所採用的 locale 設定優先性是 LC_ 環境變數高於 LANG 環境變數。LC_ALL 的優先性則是所有類別中最高的, 不過一般不建議設定 LC_ALL。

How long the system is up

使用 uptime 可以列出系統已經運作的時間。

Get the machine architecture

使用 arch 或者是 uname -m 可以列出系統的硬體架構。

Get the name of the host

使用 hostname 或者是 uname -n 可以列出目前的主機名稱。

Print the user name

使用 whoami 可以取得目前的使用者名稱。

List Current Logged In Users

使用 w 或者是 who command。

Date

使用 date 可以列出目前的時間。

如果只是要印出日期,可以使用:

date +%F

Calendar

使用 cal 可以列出這個月的月曆。

Output strings

如果需要顯示訊息,可以使用 echo
如果需要輸出格式化的訊息,可以使用 printf

printf "%s is %d years old.\n" Orange 19

Display result and output to file

如果需要顯示程式的結果並且儲存到檔案,可以使用 tee。

下面是一個例子:

ls -al | tee result

如果你只想要將結果儲存到檔案但是不顯示到 stdout,

下面是一個例子:

ls -al | tee result > /dev/null

Display file content

如果需要顯示檔案的內容,可以使用 cat。

下面是一個例子:

cat result

Display binary file content

如果使用 cat 觀察一些非文字檔案會顯示亂碼。 這時候可以嘗試使用 od 這個命令來顯示非文字檔檔案的內容,有以下的選項可以使用:

  • -A:選擇以何種進位表示位址偏移,-Ax 為十六進位,-An 表示不顯示位址偏移。
  • -a:具名字元,例如換行字元顯示為 nl。
  • -b:以八進制格式顯示。
  • -c:以 ASCII 字符格式顯示,換行字元會顯示為 \n。
  • -d:以有符號十進制格式顯示。
  • -x:以十六進制格式顯示。

文字檔案換行符號轉換

Linux/Unix-like 系列的作業系統文字檔案的換行字元為 \n; 與 Dos (以及 Windows 平台)的換行字元為 \r\n 不同。

可以使用 dos2unixunix2dos 嘗試換行符號轉換。

語系編碼轉換

可以使用 iconv 嘗試編碼轉換。

iconv -f big5 -t utf8 input_file -o output_file

Count the number of lines, words, and bytes in the file

如果需要計算檔案的行數,字數或者是大小,可以使用 wc。

其中 -l 為計算行數, -w 為計算 word 數, -c 為計算檔案的全部 bytes 數。

下面就是透過 ls 與計算行數的方式來算出目前目錄下的檔案與目錄數目:

ls | wc -l

Clears the terminal screen

如果需要清除 termial screen 的內容,可以使用 clear。
或者也可以使用 Ctrl + L 的組合鍵。

Copy and remove files

使用 cp 可以複製檔案或者是目錄。cp -r 可以遞迴地複製子目錄下的檔案。
使用 rm 可以刪除檔案或者是目錄。如果要強制刪除一個目錄以及目錄下的檔案,使用 rm -rf
如果要搬移目錄或者是檔案,使用 mv 這個命令。

建立連結檔

使用 ln 可以建立一個連結檔案。
所謂的硬連結就是使用相同 inode 的連結檔案,ln 指令預設就是建立硬連結(可以使用 ls -i 檢查)。
軟連結(符號連結)則是靠著絕對路徑或相對路徑來指向目標檔案的連結檔,若要使用 ln 指令建立軟連結,可以加上 -s 參數。

Create and remove directory

使用 mkdir 可以建立目錄。
使用 rmdir 可以刪除目錄。

Current working directory

使用 pwd 可以取得目前的目錄路徑。

grep

如果要搜尋檔案的內容,可以使用 grep,如果列出在哪一列 (-n) 以及搜尋各個目錄下的各個檔案 (-r),下面是一個範例:

grep -rn "tcl" .

find

如果要搜尋某些檔案以後殺掉,可以用:

find . -name "FILE-TO-FIND" -exec rm -rf {} \;

或者針對檔案的寫法:

find . -type f -name "FILE-TO-FIND" -exec rm -f {} \; 

如果要找出哪個 c 檔案中有 main 函式並且印出,則可以使用:

find . -type f -name *.c -print -exec grep main {} \;

head and tail

如果沒有指定參數,head 會列出一個檔案前十列的內容,而 tail 會列出一個檔案最後十列的內容。

下面是列出 Aapche Http Server error_log 最後十行的例子。

sudo tail /var/log/apache2/error_log

Compare text files

可以使用 diff 來比較文字檔案間的不同,我通常會使用 -Naur 參數取得更詳細的比較內容。

Compare binary files

可以嘗試使用下列的指令列出差異:

cmp -l file1.bin file2.bin | gawk '{printf "%08X %02X %02X\n", $1, strtonum(0$2), strtonum(0$3)}'

(這時候不是以 0,而是以 1 開始計算位置,和一般的慣例略有差異)

tar

tar 可以將多個目錄或檔案打包成一個檔案,而且可以透過 gzip/bzip2 的支援,對打包後的檔案進行壓縮。

壓 縮:tar -jcv -f filename.tar.bz2 要被壓縮的檔案或目錄名稱
查 詢:tar -jtv -f filename.tar.bz2
解壓縮:tar -jxv -f filename.tar.bz2 -C 欲解壓縮的目錄
選項與參數:
-c :建立打包檔案,可搭配 -v 來察看過程中被打包的檔名(filename)
-t :察看打包檔案的內容含有哪些檔名,重點在察看『檔名』就是了;
-x :解打包或解壓縮的功能,可以搭配 -C (大寫) 在特定目錄解開
特別留意的是, -c, -t, -x 不可同時出現在一串指令列中。
-J :透過 xz 的支援進行壓縮/解壓縮:此時檔名最好為 *.tar.xz
-j :透過 bzip2 的支援進行壓縮/解壓縮:此時檔名最好為 *.tar.bz2
-z :透過 gzip 的支援進行壓縮/解壓縮:此時檔名最好為 *.tar.gz
-v :在壓縮/解壓縮的過程中,將正在處理的檔名顯示出來!
-f filename:-f 後面要立刻接要被處理的檔名!建議 -f 單獨寫一個選項囉!
-C 目錄 :這個選項用在解壓縮,若要在特定目錄解壓縮,可以使用這個選項。

Report the file system disk space usage

可以使用 df 這個命令。

df -h

List block devices

可以使用 lsblk 這個命令來列出 block devices。

如果要確認,可以使用 fdisk 來確認:

sudo fdisk -l

List systemd services

如果需要列出目前 systemd 正在執行的 services,可以使用下列的指令:

pstree

工作管理員程式

top (table of processes)是一個工作管理員程式,用於監看目前所有程式的執行狀況, 顯示有關 CPU 和記憶體利用率的資訊。在程式執行後,按 q 可以離開程式。

檢查處理程序狀態

使用者可以使用 ps 指令來尋找正在執行中的處理程序,並顯示這些處理程序的相關資訊。

顯示所有在系統上執行的處理程序:

ps -ef

也可以使用下列的參數 (-aux) 顯示所有在系統上執行的處理程序:

ps -aux

在 Linux 中掛載 ISO 檔案

如果需要在 Linux 中掛載 ISO 檔案來存取資料的話,下面是如何掛載的範例:

# cd /mnt
# mkdir cdrom
# mount /home/danilo/Desktop/openSUSE-11.3-DVD-i586.iso /mnt/cdrom -o loop

在 Linux 中建立 ISO 檔案

使用 mkisofs 建立。下面是一個範例:

mkisofs -o home.iso /home/danilo

Create usb boot disk

  • 下載 live image
  • 使用下列的 command:
dd if=image.iso of=/dev/sdb bs=4M;sync

其中 image.iso 和 /dev/sdb 要換成符合自己環境的參數。

Increase The Maximum Number Of Open Files / File Descriptors (FD)

Use the following command command to display maximum number of open file descriptors:

cat /proc/sys/fs/file-max

The hard limit is the ceiling for the soft limit. The soft limit is what is actually enforced for a session or process. This allows the administrator (or user) to set the hard limit to the maximum usage they wish to allow. Other users and processes can then use the soft limit to self-limit their resource usage to even lower levels if they so desire.

針對各個使用者的設定,如果要查詢目前的情況:

ulimit -Sn
ulimit -Hn

要變更設定,可以編輯 /etc/security/limits.conf,下面是設定範例:

danilo soft nofile 80920
danilo hard nofile 102400

要重開機以後才會生效。

參考資料