18.2. Access To PostgreSQL Tables

18.2.1. Create Dataset

• URL:c

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

• returns:["returning values"]

example:

• request:

  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

  This request can be imagined as sending in this SQL command to be performed on database dev5: INSERT INTO t52 (f0,f4) VALUES ('1',0)

• response:

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

  Because columns f1 and f2 have default values, their values are returned after the row is inserted.

18.2.2. Read Dataset

• URL:r

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

• returns:["row"]

example:

• request:

  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

  This request can be imagined as sending in this SQL command to be performed on database dev5: SELECT f1,f2,f4,f6 FROM t52 WHERE f1=1 AND f4 BETWEEN 100 AND 99999

• response:

  [[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. Update Dataset

• URL:u

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

example:

• request:

  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

  This request can be imagined as sending in this SQL command to be performed on database dev5: UPDATE t52 SET f4=880,f5=0 WHERE f0='1' AND f1=4

18.2.4. Delete Dataset

• URL:d

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

example:

• request:

  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

  This request can be imagined as sending in this SQL command to be performed on database dev5: DELETE FROM t52 WHERE f0='1' AND f1=4

18.2.5. Pre-check Function

If the PostgreSQL function api_pre_check(IN TEXT,IN TEXT,IN TEXT,IN "char",IN TEXT,IN TEXT) exists, The server will execute this function before deciding whether to continue processing the subsequent API request. The server will ignore that API request and return HTTP response code 401 if this function returns false or raises execption. (Avoid raising exceptions because PostgreSQL is often configured to log exceptions, which consumes disk space.)

Function api_pre_check() parameters are described as follows:

1. user id

2. target table name

3. company code

4. c, r, u, or d

5. condition

   If the API request is sent in as this:

   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

   , the argument condition is passed by server with the value of key "query": [[["f0","1"],["f1",1]],[["f4",880],["f5",0]]]

These arguments are automatically provided by server when it executes the API requests.

api_pre_check() example:

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;