1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
-- =============================================
-- project: GNUmed
-- license: GPL v2 or later
-- author: Karsten.Hilbert@gmx.net
-- =============================================
-- force terminate + exit(3) on errors if non-interactive
\set ON_ERROR_STOP 1
-- ---------------------------------------------
\unset ON_ERROR_STOP
drop schema gm cascade;
\set ON_ERROR_STOP 1
create schema gm authorization "gm-dbo";
-- ---------------------------------------------
create or replace function gm.concat_table_structure_v1()
returns text
language 'plpgsql'
security definer
as '
declare
_row record;
_total text;
begin
_total := '''';
-- schema.table.column.data_type
for _row in
select * from information_schema.columns cols
where cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
-- those which hold clinical data
tabs.table_schema in (''public'', ''dem'', ''clin'', ''blobs'') and
tabs.table_type = ''BASE TABLE''
)
order by
md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type)
loop
_total := _total
|| _row.table_schema || ''.''
|| _row.table_name || ''.''
|| _row.column_name || ''::''
|| _row.udt_name || E''\n'';
end loop;
return _total;
end;
';
comment on function gm.concat_table_structure_v1() is
'copy of gm_concat_table_structure() until gnumed_v5, works on public, dem, clin, blobs';
-- ---------------------------------------------
create or replace function gm.concat_table_structure_v2()
returns text
language 'plpgsql'
security definer
as '
declare
_row record;
_total text;
begin
_total := '''';
-- schema.table.column.data_type
for _row in
select * from information_schema.columns cols
where cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
-- those which hold clinical data
tabs.table_schema in (''dem'', ''clin'', ''blobs'', ''cfg'', ''ref'', ''i18n'') and
tabs.table_type = ''BASE TABLE''
)
order by
md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type)
loop
_total := _total
|| _row.table_schema || ''.''
|| _row.table_name || ''.''
|| _row.column_name || ''::''
|| _row.udt_name || E''\n'';
end loop;
return _total;
end;
';
comment on function gm.concat_table_structure_v2() is
'new concat_table_structure() starting with gnumed_v6, works on dem, clin, blobs, cfg, ref, i18n';
-- ---------------------------------------------
create or replace function gm.concat_table_structure(integer)
returns text
language 'plpgsql'
security definer
as '
declare
_db_ver alias for $1;
_struct text;
begin
if _db_ver < 6 then
select into _struct gm.concat_table_structure_v1();
return _struct;
end if;
select into _struct gm.concat_table_structure_v2();
return _struct;
end;
';
-- ---------------------------------------------
create or replace function gm.concat_table_structure()
returns text
language 'plpgsql'
security definer
as '
declare
_struct text;
begin
select into _struct gm.concat_table_structure_v2();
return _struct;
end;
';
-- ---------------------------------------------
create or replace function gm.log_script_insertion(text, text) returns text as '
declare
_filename alias for $1;
_version alias for $2;
_hash text;
begin
delete from gm_schema_revision where filename = _filename;
insert into gm_schema_revision (filename, version) values (
_filename,
_version
);
select into _hash md5(gm.concat_table_structure());
return _hash;
end;' language 'plpgsql';
-- ---------------------------------------------
-- cleanup
drop function public.gm_concat_table_structure() cascade;
drop function public.log_script_insertion(text, text) cascade;
drop table public.gm_database_revision cascade;
-- permissions
grant usage on schema gm to group "gm-doctors";
-- =============================================
select gm.log_script_insertion('$RCSfile: gm-schema.sql,v $', '$Revision: 1.3 $');
-- =============================================
|
