fhirbase 数据结构分析

wanghaisheng commented 9 years ago

数据结构 https://raw.githubusercontent.com/fhirbase/fhirbase-build/master/fhirbase.sql

wanghaisheng commented 9 years ago

CREATE FUNCTION admin_disk_usage_top(_limit integer) RETURNS TABLE(relname text, size text) LANGUAGE sql AS $$ SELECT nspname || '.' || relname AS "relation", -- admin:1 pg_size_pretty(pg_relation_size(C.oid)) AS "size" -- admin:2 FROM pg_class C -- admin:3 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) -- admin:4 WHERE nspname NOT IN ('pg_catalog', 'information_schema') -- admin:5 ORDER BY pg_relation_size(C.oid) DESC -- admin:6 LIMIT _limit; -- admin:7 $$;

参考https://wiki.postgresql.org/wiki/Disk_Usage

Finding the size of your biggest relations

Performance Snippets

Disk usage Works with PostgreSQL

=8.1 Written in

SQL Depends on

Nothing

Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. Tables which have both regular and TOAST pieces will be broken out into separate components; an example showing how you might include those into the main total is available in the documentation, and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:

Note that all of the queries below this point on this page show you the sizes for only those objects which are in the database you are currently connected to.

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

Example output (from a database created with pgbench, scale=25):

    relation        |    size

查询schema中诸如表与索引之间的关系等此类对象的大小 relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts.

wanghaisheng commented 9 years ago


CREATE FUNCTION _butlast(_ar_ anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $$
  --- cut last element of array -- coll:2
  SELECT _ar_[array_lower(_ar_,1) : array_upper(_ar_,1) - 1] -- coll:3
$$;

这个函数移除了一位数组中的最后一个值

PostgreSQL 缺省使用以一为基的数组习惯，也就是说，一个 n 元素的数组从array[1]开始，到 array[n] 结束。

array_lower(anyarray, int) int 返回指定的数组维数的下界 array_lower(array_prepend(0, ARRAY[1,2,3]), 1) 0 array_upper(anyarray, int) int 返回指定数组维数的上界 array_upper(ARRAY[1,2,3,4], 1) 4

看起来这两个函数返回的是值的序列 lower看起来是从0开始数的 upper又看起来是从1开始数的似乎是矛盾的

但查阅了http://stackoverflow.com/questions/2072776/remove-array-values-in-pgsql/2073790#2073790 中的例子发现他们都返回的是具体的值而非值对应的序列，函数中传入的参数是序列号序列号是从1开始计算的这样的话上面的例子就是合理的

CREATE FUNCTION _last(_ar_ anyarray) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $$
  --- return last element of collection -- coll:10
  SELECT _ar_[array_length(_ar_,1)]; -- coll:11
$$;

取出一位数组的最后一个值


CREATE FUNCTION _rest(_ar_ anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $$
  --- return rest of array -- coll:6
  SELECT _ar_[2 : array_upper(_ar_,1)]; -- coll:7
$$;

取出一位数组中第一个以外的所有值

参考资料 1、http://blog.csdn.net/cdnight/article/details/28629211 2、http://www.cnblogs.com/stephen-liu74/archive/2012/05/07/2295273.html

wanghaisheng commented 9 years ago

CREATE FUNCTION conformance(_cfg jsonb) RETURNS jsonb
    LANGUAGE sql IMMUTABLE
    AS $$
  SELECT json_build_object( -- conformance:3
    'resourceType', 'Conformance', -- conformance:4
    'identifier', _cfg->'identifier', -- conformance:5
    'version', _cfg->'version', -- conformance:6
    'name', _cfg->'name', -- conformance:7
    'publisher', _cfg->'publisher', -- conformance:8
    'telecom', _cfg->'telecom', -- conformance:9
    'description', _cfg->'description', -- conformance:10
    'status', 'active', -- conformance:11
    'date', _cfg->'date', -- conformance:12
    'software', _cfg->'software', -- conformance:13
    'fhirVersion', _cfg->'fhirVersion', -- conformance:14
    'acceptUnknown', _cfg->'acceptUnknown', -- conformance:15
    'format', _cfg->'format', -- conformance:16
    'rest', ARRAY[json_build_object( -- conformance:17
      'mode', 'server', -- conformance:18
      'operation', ARRAY['{ "code": "transaction" }'::json, '{ "code": "history-system" }'::json], -- conformance:19
      'cors', _cfg->'cors', -- conformance:20
      'resource', -- conformance:21
        COALESCE((SELECT json_agg( -- conformance:22
            json_build_object( -- conformance:23
              'type', r.logical_id, -- conformance:24
              'profile', json_build_object( -- conformance:25
                'reference', _cfg->>'base' || '/Profile/' || r.logical_id -- conformance:26
              ), -- conformance:27
              'readHistory', true, -- conformance:28
              'updateCreate', true, -- conformance:29
              'operation', ARRAY['{ "code": "read" }'::json, '{ "code": "vread" }'::json, '{ "code": "update" }'::json, '{ "code": "history-instance" }'::json, '{ "code": "create" }'::json, '{ "code": "history-type" }'::json], -- conformance:30
              'searchParam',  ( -- conformance:31
                SELECT  array_agg(sp.content)  FROM searchparameter sp -- conformance:32
                WHERE sp.base = r.logical_id -- conformance:33
              ) -- conformance:34
            ) -- conformance:35
          ) -- conformance:36
          FROM structuredefinition r -- conformance:37
          WHERE r.installed = true -- conformance:38
        ), '[]'::json) -- conformance:39
    )] -- conformance:40
  )::jsonb -- conformance:41
$$;

参考资料 1、http://stackoverflow.com/questions/26486784/return-as-array-of-json-objects-in-sql-postgres/26486910#26486910 2\http://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins/13227451#13227451

wanghaisheng / fhir-cn

fhirbase 数据结构分析 #22