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. Fine Grained Pre-check Function

If the PostgreSQL function api_pre_check(IN TEXT,IN TEXT,IN TEXT,IN "char",IN TEXT,IN TEXT) exists, server will execute this function for every API request. Server will abandon that request 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",4]],[["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",4]],[["f4",880],["f5",0]]]

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

api_pre_check() example:

CREATE FUNCTION api_pre_check(user_id TEXT,table_name TEXT,company TEXT,crud "char",query TEXT) RETURNS BOOLEAN AS $$
BEGIN
	IF crud IN ('u','d') THEN 
		RETURN
			EXISTS (SELECT 1 FROM t43 WHERE f1=user_id AND f8)
			AND EXISTS (SELECT 1 FROM t52 WHERE f0=company AND AGE(f2) < INTERVAL '1 month');
	END IF;
	RETURN TRUE;
END $$ LANGUAGE PLPGSQL STABLE;