18.2. 存取PostgreSQL table

18.2.1. 生成資料

• URL：c

• POST資料：{"table":"table name","company":"company code","query":[column name,column value]}

• returns:["回傳值"]

舉例：

• 請求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[["f0","1"],["f4",0]]}' https://34.terarows.com/fastcgi/api/c

  可以把這道請求想像成要在dev5資料庫上面執行這道SQL指令： INSERT INTO t52 (f0,f4) VALUES ('1',0)

• 回傳：

  [["f2","2017-08-27 05:14:53.206975+00"],["f1",4]]

  因為f1與f2欄位有設定預設值，所以在insert紀錄後，回傳其值。

18.2.2. 讀取資料

• URL：r

• POST資料：{"and":boolean,"table":"table name","company":"company code","query":[[select column list],[query condition]]}

• returns:["回傳紀錄"]

舉例：

• 請求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"and":true,"table":"t53","company":"1","query":[["f1","f2","f4","f6"],[["f1","=",1,null],["f4","BETWEEN",100,99999]]]}' https://34.terarows.com/fastcgi/api/r

  可以把這道請求想像成要在dev5資料庫上面執行這道SQL指令： SELECT f1,f2,f4,f6 FROM t52 WHERE f1=1 AND f4 BETWEEN 100 AND 99999

• 回傳：

  [[1,25,"3353","5127163"],[1,24,"3111","218811993"],[1,100,"3353","1"],[1,19,"2141","1724"],[1,18,"2141","1633"],[1,21,"2171","800"]]

18.2.3. 更改資料

• URL：u

• POST資料：{"table":"table name","company":"company code","query":[[key name,key value],[column name, new column value]]}

舉例：

• 請求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[[["f0","1"],["f1",4]],[["f4",880],["f5",0]]]}' https://34.terarows.com/fastcgi/api/u

  可以把這道請求想像成要在dev5資料庫上面執行這道SQL指令： UPDATE t52 SET f4=880,f5=0 WHERE f0='1' AND f1=4

18.2.4. 刪除資料

• URL：d

• POST資料：{"table":"table name","company":"company code","query":[[key name,key value]]}

舉例：

• 請求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[["f0","1"],["f1",4]]}' https://34.terarows.com/fastcgi/api/d

  可以把這道請求想像成要在dev5資料庫上面執行這道SQL指令： DELETE FROM t52 WHERE f0='1' AND f1=4

18.2.5. 預先過濾function

如果在資料庫有PostgreSQLapi_pre_check(IN TEXT,IN TEXT,IN TEXT,IN "char",IN TEXT)的function，則伺服器於處理每一次API呼叫之前都會先執行這個function。如果這個function回傳false或raise execption的話，則伺服器忽略該次API請求，並且回傳HTTP回應碼401。（因為PostgreSQL通常被設定成記錄exception的log而佔據硬碟空間，所以請盡量避免製造exception。）

Function api_pre_check()的參數說明如下：

1. user id

2. table name

3. company code

4. c、r、u、或d

5. 條件

   以下面的API請求為例，

   curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[[["f0","1"],["f1",1]],[["f4",880],["f5",0]]]}' https://34.terarows.com/fastcgi/api/u

   條件內容就是"query" key的值：[[["f0","1"],["f1",1]],[["f4",880],["f5",0]]]

伺服器於執行API之前轉交這些參數給api_pre_check()。

設置api_pre_check()舉例：

CREATE OR REPLACE FUNCTION api_pre_check(user_id TEXT,table_name TEXT,company TEXT,crud "char",query TEXT) RETURNS BOOLEAN AS $$
DECLARE
	result BOOLEAN;
	j JSONB;
BEGIN
	IF crud IN ('u','d') THEN 
		j:=query::JSONB;
		EXECUTE FORMAT('
			SELECT EXISTS (SELECT 1 FROM t43 WHERE f1=$1 AND f8)
				AND EXISTS (SELECT 1 FROM %I WHERE %I=%L AND %I=$2 AND %I=$3 AND (%I=$4 OR %I IS NULL))'
			,table_name
			,j #>> '{0,0,0}'
			,j #>> '{0,0,1}'
			,j #>> '{0,1,0}'
			,j #>> '{1,0,0}'
			,j #>> '{1,1,0}'
			,j #>> '{1,1,0}'
		)
		INTO result
		USING 
			user_id
			,(j #>> '{0,1,1}')::INTEGER
			,(j #>> '{1,0,1}')::INTEGER
			,(j #>> '{1,1,1}')::INTEGER;
		RETURN company='1' AND result;
	END IF;
	RETURN TRUE;
END $$ LANGUAGE PLPGSQL STABLE;