From 7895f48ee6d1d4634f6d49c5ee6192da2ead2da1 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Thu, 4 Jan 2018 23:22:44 +0000 Subject: [PATCH] lib/php/db.php --- lib/php/db.php | 1 + lib/php/db/config.php | 1 - lib/postgres/function/CIDR_RANGE.sql | 5 +++++ lib/postgres/function/DATE.sql | 5 +++++ lib/postgres/function/GROUP_CONCAT.sql | 16 ++++++++++++++++ lib/postgres/function/STRFTIME.sql | 5 +++++ 6 files changed, 32 insertions(+), 1 deletion(-) create mode 100644 lib/postgres/function/CIDR_RANGE.sql create mode 100644 lib/postgres/function/DATE.sql create mode 100644 lib/postgres/function/GROUP_CONCAT.sql create mode 100644 lib/postgres/function/STRFTIME.sql diff --git a/lib/php/db.php b/lib/php/db.php index db307cf3..7f5e9f29 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -685,6 +685,7 @@ class Db extends nb { # Load all files into a $dbs if #files is not a hash # + $DBQ = []; $dbs = []; $done = []; foreach ((array)$files as $file) { diff --git a/lib/php/db/config.php b/lib/php/db/config.php index 3ac6562a..df5dd195 100644 --- a/lib/php/db/config.php +++ b/lib/php/db/config.php @@ -2,7 +2,6 @@ require_once(realpath(dirname(__FILE__).'/../config.php')); require_once(NB_ROOT.'/lib/php/db.php'); if (empty($_SERVER['HOME'])) $_SERVER['HOME'] = ''; -if (empty($DBQ)) $DBQ = []; $DB_CONFS = array_merge([], # ::ROOT_DIR diff --git a/lib/postgres/function/CIDR_RANGE.sql b/lib/postgres/function/CIDR_RANGE.sql new file mode 100644 index 00000000..988ce60e --- /dev/null +++ b/lib/postgres/function/CIDR_RANGE.sql @@ -0,0 +1,5 @@ +DROP FUNCTION IF EXISTS CIDR_RANGE(cidr text, ip text); +CREATE OR REPLACE FUNCTION CIDR_RANGE(cidr text, ip text) +RETURNS bool AS $$ +SELECT ip::inet << cidr::cidr +$$ LANGUAGE sql; diff --git a/lib/postgres/function/DATE.sql b/lib/postgres/function/DATE.sql new file mode 100644 index 00000000..8ab3e512 --- /dev/null +++ b/lib/postgres/function/DATE.sql @@ -0,0 +1,5 @@ +DROP FUNCTION IF EXISTS DATE(sdate text, sinterval text); +CREATE OR REPLACE FUNCTION DATE(sdate text DEFAULT 'now', sinterval text DEFAULT '0 seconds') +RETURNS date AS $$ +SELECT to_char(sdate::date + sinterval::interval,'YYYY-MM-DD')::date; +$$ LANGUAGE sql; diff --git a/lib/postgres/function/GROUP_CONCAT.sql b/lib/postgres/function/GROUP_CONCAT.sql new file mode 100644 index 00000000..d6327122 --- /dev/null +++ b/lib/postgres/function/GROUP_CONCAT.sql @@ -0,0 +1,16 @@ +-- NB 22.06.16 CREATE OR REPLACE FUNCTION GROUP_CONCAT(anyarray, sep text DEFAULT ',', nullstr text DEFAULT '') +-- NB 22.06.16 RETURNS text AS $$ +-- NB 22.06.16 SELECT array_to_string( ARRAY( SELECT coalesce(v::text, $3) FROM unnest($1) g(v) ), $2 ) +-- NB 22.06.16 $$ LANGUAGE sql; + +CREATE OR REPLACE FUNCTION GROUP_CONCAT(str text,sep text DEFAULT ',', nullstr text DEFAULT '') +RETURNS text AS $$ +SELECT array_to_string(array_agg(coalesce(str,nullstr)),sep) +$$ LANGUAGE sql; + +-- NB 21.06.16 CREATE AGGREGATE GROUP_CONCAT(field,sep=',') +-- NB 21.06.16 sfunc = array_append, +-- NB 21.06.16 stype = anyarray, +-- NB 21.06.16 initcond = '{}' +-- NB 21.06.16 -- return array_to_string(array_agg(field),sep) +-- NB 21.06.16 } diff --git a/lib/postgres/function/STRFTIME.sql b/lib/postgres/function/STRFTIME.sql new file mode 100644 index 00000000..7d6f9ce8 --- /dev/null +++ b/lib/postgres/function/STRFTIME.sql @@ -0,0 +1,5 @@ +DROP FUNCTION IF EXISTS STRFTIME(format text,sdate text); +CREATE OR REPLACE FUNCTION STRFTIME(format text,sdate text) +RETURNS text AS $$ +SELECT to_char(sdate::date,format) +$$ LANGUAGE sql; -- 2.47.3