JavaScript, php. - : - . .
, WEB LAMP
, . perl, bash . , . .
IE, Netscape, Opera cgi perl. . , . , PHP. , , . , .
- JS , XMLHttpRequest ActiveX. . . IE 6 . MiddleWare. php, .
php, , , , , . , - . , , , . . , : , ?...
. XMLHttpRequest WEB .
, . , , . Web . , , .
Node.js .
? !
. Middle.
.
, JavaScript Front, Middle BackEnd? . .
, . , , , .
.
, PostgreSQL, . ! BackEnd. ? ?
, .
, . , . . .
.
, , .. !
!
, . . . : ? .
, , Middle, , , IT . , - - .
nginx, . . . , - , apache, .
2010 github
ngx_postgres. JSON/CSV/.
SQL . , .
http . , . URL .
C . , , PostgreSQL.
, . .
ngx_postgres. - .
, , , , , ngx_pgcopy.
NGX_PGCOPY
, .
COPY, . , , .
, . , , .
COPY-requests CSV , .
, CSV URL:
http://some.server/csv/some_table
CSV
import.export.nginx.conf
pgcopy_server db_pub "host=127.0.0.1 dbname=testdb user=testuser password=123";
location ~/csv/(?<table>[0-9A-Za-z_]+) {
pgcopy_query PUT db_pub "COPY $table FROM STDIN WITH DELIMITER as ';' null as '';";
pgcopy_query GET db_pub "COPY $table TO STDOUT WITH DELIMITER ';';";
}
PostgreSQL JSON XML COPY STDIN. , - , -, COPY . , .
! ! nginx client_body_in_file_only on c $request_body_file pg_read_binary_file
COPY , .. , body_rejecta ngx_postgres. , ngx_pgcopy .
json/xml
import.export.nginx.conf
client_body_in_file_only on;
client_body_temp_path /var/lib/postgresql/9.6/main/import;
location ~/json/(?<table>[0-9A-Za-z_]+) {
pgcopy_query PUT db_pub
"COPY (SELECT * FROM import_json_to_simple_data('$request_body_file'))
TO STDOUT;";
pgcopy_query GET db_pub
"COPY (SELECT '['||array_to_string(array_agg(row_to_json(simple_data)),
',')||']' FROM simple_data) TO STDOUT;";
}
location ~/xml/(?<table>[0-9A-Za-z_]+) {
pgcopy_query PUT db_pub
"COPY (SELECT import_xml_to_simple_data('$request_body_file') TO STDOUT;";
pgcopy_query GET db_pub
"COPY (SELECT table_to_xml('$table', false, false, '')) TO STDOUT;";
}
, client_body_temp_path , ALTER SUPERUSER. Postgres .
, GET, , Postgres. COPY STDOUT, . (simple_data) , .
1.import.export.sql
CREATE OR REPLACE FUNCTION import_json_to_simple_data(filename TEXT)
RETURNS void AS $$
BEGIN
INSERT INTO simple_data
SELECT * FROM
json_populate_recordset(null::simple_data,
convert_from(pg_read_binary_file(filename), 'UTF-8')::json);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION import_xml_to_simple_data(filename TEXT)
RETURNS void AS $$
BEGIN
INSERT INTO simple_data
SELECT (xpath('//s_id/text()', myTempTable.myXmlColumn))[1]::text::integer AS s_id,
(xpath('//data0/text()', myTempTable.myXmlColumn))[1]::text AS data0
FROM unnest(xpath('/*/*',
XMLPARSE(DOCUMENT convert_from(pg_read_binary_file(filename), 'UTF-8'))))
AS myTempTable(myXmlColumn);
END;
$$ LANGUAGE plpgsql;
JSON . XML .
1.import.export.sql CREATE OR REPLACE FUNCTION import_vt_json(filename TEXT, target_table TEXT)
RETURNS void AS $$
BEGIN
EXECUTE format(
'INSERT INTO %I SELECT * FROM
json_populate_recordset(null::%I,
convert_from(pg_read_binary_file(%L), ''UTF-8'')::json)',
target_table, target_table, filename);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION import_vt_xml(filename TEXT, target_table TEXT)
RETURNS void AS $$
DECLARE
columns_name TEXT;
BEGIN
columns_name := (
WITH
xml_file AS (
SELECT * FROM unnest(xpath(
'/*/*',
XMLPARSE(DOCUMENT
convert_from(pg_read_binary_file(filename), 'UTF-8'))))
--read tags from file
), columns_name AS (
SELECT DISTINCT (
xpath('name()',
unnest(xpath('//*/*', myTempTable.myXmlColumn))))[1]::text AS cn
FROM xml_file AS myTempTable(myXmlColumn)
--get target table cols name and type
), target_table_cols AS ( --
SELECT a.attname, t.typname, a.attnum, cn.cn
FROM pg_attribute a
LEFT JOIN pg_class c ON c.oid = a.attrelid
LEFT JOIN pg_type t ON t.oid = a.atttypid
LEFT JOIN columns_name AS cn ON cn.cn=a.attname
WHERE a.attnum > 0
AND c.relname = target_table --'log_data'
ORDER BY a.attnum
--prepare cols to output from xpath
), xpath_type_str AS (
SELECT CASE WHEN ttca.cn IS NULL THEN 'NULL AS '||ttca.attname
ELSE '((xpath(''/*/'||attname||'/text()'',
myTempTable.myXmlColumn))[1]::text)::'
||typname||' AS '||attname
END
AS xsc
FROM target_table_cols AS ttca
)
SELECT array_to_string(array_agg(xsc), ',') FROM xpath_type_str
);
EXECUTE format('INSERT INTO %s SELECT %s FROM unnest(xpath( ''/*/*'',
XMLPARSE(DOCUMENT convert_from(pg_read_binary_file(%L), ''UTF-8''))))
AS myTempTable(myXmlColumn)', target_table, columns_name, filename);
END;
$$ LANGUAGE plpgsql;
, table_name , nginx. xml table_name/rows/cols
table_to_xml.
C
simple_data_table.sqlCREATE TABLE simple_data (
s_id SERIAL,
data0 TEXT
);
data.json[ {"s_id": 5, "data0": "five"},
{"s_id": 6, "data0": "six"} ]
data.xml<simple_data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<s_id>3</s_id>
<data0>three</data0>
</row>
<row>
<s_id>4</s_id>
<data0>four</data0>
</row>
</simple_data>
, COPY
. , . , . ?
.
. , MidleWare .
, , ?
, , .
, ! CRUD.
, - , . , . . RESTfull.
, , , . . , , , . , , http . http , . CRUD . .
, .
, ,
, .
-,
, .
, /. .
, ! / ! . , : , - . , .
HTTP GET PUT. . . . , .
, . : .
? : , .. : , .
2.jrl.log.sql:
CREATE TABLE rst_data ( --Output/result table 1/2
s_id SERIAL,
data0 TEXT, --Operating Data
data1 TEXT, --Operating Data
);
--Service variable with prefix s_, ingoring input value, it will be setting from trigers
CREATE TABLE jrl_data ( --Input/journal table 2/2
s_id SERIAL, --Service variable, Current ID of record
s_cusr TEXT, --Service variable, User name who created the record
s_tmc TEXT, --Service variable, Time when the record was created
p_trid INTEGER, --Service variable, Target ID/Parent in RST_(result) table,
-- if exists for modification
data0 TEXT,
data1 TEXT,
);
CREATE TABLE log_data ( --Input/output log table 1/1
s_id SERIAL,
s_cusr TEXT,
s_tmc TEXT,
pc_trid INTEGER, --Service variable, Target ID(ParentIN/ChilrdenSAVE)
-- in CURRENT table, if exists for modification
data0 TEXT,
data1 TEXT,
);
CREATE OR REPLACE FUNCTION trg_4_jrl() RETURNS trigger AS $$
DECLARE
update_result INTEGER := NULL;
target_tb TEXT :='rst_'||substring(TG_TABLE_NAME from 5);
BEGIN
--key::text,value::text
DROP TABLE IF EXISTS not_null_values;
CREATE TEMP TABLE not_null_values AS
SELECT key,value from each(hstore(NEW)) AS tmp0
INNER JOIN
information_schema.columns
ON information_schema.columns.column_name=tmp0.key
WHERE tmp0.key NOT LIKE 's_%'
AND tmp0.key <> 'p_trid'
AND tmp0.value IS NOT NULL
AND information_schema.columns.table_schema = TG_TABLE_SCHEMA
AND information_schema.columns.table_name = TG_TABLE_NAME;
IF NEW.p_trid IS NOT NULL THEN
EXECUTE (WITH keys AS (
SELECT (
string_agg((select key||'=$1.'||key from not_null_values), ','))
AS key)
SELECT format('UPDATE %s SET %s WHERE %s.s_id=$1.p_trid', target_tb, keys.key, target_tb)
FROM keys)
USING NEW;
END IF;
GET DIAGNOSTICS update_result = ROW_COUNT;
IF NEW.p_trid IS NULL OR update_result=0 THEN
IF NEW.p_trid IS NOT NULL AND update_result=0 THEN
NEW.p_trid=NULL;
END IF;
EXECUTE format('INSERT INTO %s (%s) VALUES (%s) RETURNING s_id',
target_tb,
(SELECT string_agg(key, ',') from not_null_values),
(SELECT string_agg('$1.'||key, ',') from not_null_values))
USING NEW;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION trg_4_log() RETURNS trigger AS $$
BEGIN
IF NEW.pc_trid IS NOT NULL THEN
EXECUTE (
WITH
str_arg AS (
SELECT key AS key,
CASE WHEN value IS NOT NULL OR key LIKE 's_%' THEN key
ELSE NULL
END AS ekey,
CASE WHEN value IS NOT NULL OR key LIKE 's_%' THEN 't.'||key
ELSE TG_TABLE_NAME||'.'||key
END AS tkey,
CASE WHEN value IS NOT NULL OR key LIKE 's_%' THEN '$1.'||key
ELSE NULL
END AS value,
isc.ordinal_position
FROM each(hstore(NEW)) AS tmp0
INNER JOIN information_schema.columns AS isc
ON isc.column_name=tmp0.key
WHERE isc.table_schema = TG_TABLE_SCHEMA
AND isc.table_name = TG_TABLE_NAME
ORDER BY isc.ordinal_position)
SELECT format('WITH upd AS (UPDATE %s SET pc_trid=%L WHERE s_id=%L)
SELECT %s FROM (VALUES(%s)) AS t(%s)
LEFT JOIN %s ON t.pc_trid=%s.s_id',
TG_TABLE_NAME, NEW.s_id, NEW.pc_trid,
string_agg(tkey, ','),
string_agg(value, ','),
string_agg(ekey, ','),
TG_TABLE_NAME, TG_TABLE_NAME)
FROM str_arg
) INTO NEW USING NEW;
NEW.pc_trid=NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
, .. . . . , .
. trg_N_. trg_0 trg_4 , . 5 9 .
!
, BEFORE INSERT. c AFTER, NEW . , , , . .
, AFTER, , . FrondEnd . , , .
Pro
URL nginx. . , map nginx.conf SQL c. , , .
. nginx
sed s/bad/good/g.
. - ! .
4 URL
http://some.server/csv/table_name/*?col1=value&col2=value&col3=value&col4=value
Horrowshow part of filters.nginx.conf
# SQL
map $args $fst0 {
default "";
"~*(?<tmp00>[a-zA-Z0-9_]+=)(?<tmp01>[a-zA-Z0-9_+-.,:]+)(:?&(?<tmp10>[a-zA-Z0-9_]+=)(?<tmp11>[a-zA-Z0-9_+-.,:]+))?(:?&(?<tmp20>[a-zA-Z0-9_]+=)(?<tmp21>[a-zA-Z0-9_+-.,:]+))?(:?&(?<tmp30>[a-zA-Z0-9_]+=)(?<tmp31>[a-zA-Z0-9_+-.,:]+))?(:?&(?<tmp40>[a-zA-Z0-9_]+=)(?<tmp41>[a-zA-Z0-9_+-.,:]+))?" "$tmp00'$tmp01' AND $tmp10'$tmp11' AND $tmp20'$tmp21' AND $tmp30'$tmp31' AND $tmp40'$tmp41'";
}
#
map $fst0 $fst1 {
default "";
"~(?<tmp0>(:?[a-zA-Z0-9_]+='[a-zA-Z0-9_+-.,:]+'(?: AND )?)+)(:?( AND '')++)?" "$tmp0";
}
map $fst1 $fst2 {
default "";
"~(?<tmp0>[a-zA-Z0-9_+-=,.'' ]+)(?= AND *$)" "$tmp0";
}
# , WHERE
map $fst2 $fst3 {
default "";
"~(?<tmp>.+)" "WHERE $tmp";
}
server {
location ~/csv/(?<table>result_[a-z0-9]*)/(?<columns>\*|[a-zA-Z0-9,_]+) {
pgcopy_query GET db_pub
"COPY (select $columns FROM $table $fst3) TO STDOUT WITH DELIMITER ';';";
}
}
C URL, nginx, base64 , . , . , nginx, .
- , sed, nginx inc .
url , . nginx-access.log . , , .
Smokeall FAQ
nginx . ?
. !
(client_body_in_file_only) !
RAM Drive .
?
plain http . . BackEnd.
?
ssl nginx. - ngx_pgcopy.
nginx c postgres, , ssh.
JS , ? JavaScript?
JS FrontEnd. .
JS?
, , Postgres xml. .. HTML . , xsl .
. , . .
, GPU?
, .
, , .
FastCGI!
.
,
C . .
ngix
client_body_in_file_only on, $request_body_file plperlu, . - :
CREATE OR REPLACE FUNCTION foo(filename TEXT) RETURNS TEXT AS $$
return `/bin/echo -n "hello world!"`;
$$ LANGUAGE plperlu;
CGI. CGI !
, . . , CGI. , CGI . , PHP .
( PostgreSQL) . , .
->
ngx_pgcopy
->
PostgreSQL COPY request
->
slim_middle_samples (
+ )
WARRNING
, . keep alive backend, , , . README .
PS. , CRUD , , . DELETE .
/ - 1998. , .
https://habrahabr.ru/post/331056/
