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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
-- ==============================================================
-- GNUmed database schema change script
--
-- License: GPL v2 or later
-- Author: karsten.hilbert@gmx.net
--
-- ==============================================================
-- $Id: v10-i18n-dynamic.sql,v 1.5 2008-12-25 23:34:30 ncq Exp $
-- $Revision: 1.5 $
-- --------------------------------------------------------------
--set default_transaction_read_only to off;
\set ON_ERROR_STOP 1
\set check_function_bodies 1
-- --------------------------------------------------------------
comment on column i18n.curr_lang.db_user is
'The database account this language setting applies to.';
\unset ON_ERROR_STOP
drop function gm.user_exists(name) cascade;
\set ON_ERROR_STOP 1
create or replace function gm.user_exists(name)
returns boolean
language 'plpgsql'
as E'
BEGIN
perform 1 from pg_user where usename = $1;
if not FOUND then
raise notice ''Cannot set database language. User % does not exist.'', $1;
return false;
end if;
return true;
END;';
delete from i18n.curr_lang where db_user not in (
select usename from pg_user
);
alter table i18n.curr_lang
add constraint user_must_exist
check (gm.user_exists(db_user) is true);
-- --------------------------------------------------------------
\unset ON_ERROR_STOP
drop function i18n.unset_curr_lang(name) cascade;
\set ON_ERROR_STOP 1
create or replace function i18n.unset_curr_lang(name)
returns void
language 'plpgsql'
security definer
as E'
BEGIN
delete from i18n.curr_lang where db_user = $1;
return;
END;';
comment on function i18n.unset_curr_lang(name) is 'unset the db language for a user (thereby reverting to the default English)';
-- --------------------------------------------------------------
\unset ON_ERROR_STOP
drop function i18n.unset_curr_lang() cascade;
\set ON_ERROR_STOP 1
create or replace function i18n.unset_curr_lang()
returns void
language sql
as 'select i18n.unset_curr_lang(CURRENT_USER);';
comment on function i18n.unset_curr_lang() is 'unset the db language for the current user';
-- --------------------------------------------------------------
\unset ON_ERROR_STOP
drop function i18n.set_curr_lang(text, name) cascade;
\set ON_ERROR_STOP 1
create or replace function i18n.set_curr_lang(text, name)
returns boolean
language 'plpgsql'
security definer
as E'
DECLARE
_lang ALIAS FOR $1;
_db_user ALIAS FOR $2;
lang_has_tx boolean;
BEGIN
select into lang_has_tx exists(select pk from i18n.translations where lang = _lang);
if lang_has_tx is False then
raise notice ''Cannot set current language to [%]. No translations available.'', _lang;
return False;
end if;
delete from i18n.curr_lang where db_user = _db_user;
insert into i18n.curr_lang (db_user, lang) values (_db_user, _lang);
return true;
END;
';
comment on function i18n.set_curr_lang(text, name) is
'set language to first argument for the user named in
the second argument if translations are available';
-- --------------------------------------------------------------
\unset ON_ERROR_STOP
drop function i18n.set_curr_lang(text) cascade;
\set ON_ERROR_STOP 1
create function i18n.set_curr_lang(text)
returns boolean
language 'plpgsql'
as '
DECLARE
_lang ALIAS FOR $1;
_status boolean;
BEGIN
select into _status i18n.set_curr_lang(_lang, CURRENT_USER);
return _status;
END;
';
comment on function i18n.set_curr_lang(text) is
'set preferred language:
- for "current user"
- only if translations for this language are available';
-- =============================================
create or replace function i18n.get_curr_lang(text)
returns text
language sql
as 'select lang from i18n.curr_lang where db_user = $1'
;
create or replace function i18n.get_curr_lang()
returns text
language sql
as 'select i18n.get_curr_lang(CURRENT_USER)'
;
-- =============================================
create or replace function i18n._(text, text)
returns text
language 'plpgsql'
security definer
as '
DECLARE
_orig alias for $1;
_lang alias for $2;
trans_str text;
BEGIN
select into trans_str trans from i18n.translations where lang = _lang and orig = _orig;
if not found then
return _orig;
end if;
return trans_str;
END;
';
comment on function i18n._(text, text) is
'will return either the translation into <text>
(2nd argument) for the current user or the input,
created in public schema for easy access';
-- =============================================
create or replace function i18n._(text)
returns text
language sql
as 'select i18n._($1, i18n.get_curr_lang())';
comment on function i18n._(text) is
'will return either the translation into
i18n.curr_lang.lang for the current user
or the input,
created in public schema for easy access';
-- --------------------------------------------------------------
select gm.log_script_insertion('$RCSfile: v10-i18n-dynamic.sql,v $', '$Revision: 1.5 $');
-- ==============================================================
-- $Log: v10-i18n-dynamic.sql,v $
-- Revision 1.5 2008-12-25 23:34:30 ncq
-- - fix comment
--
-- Revision 1.4 2008/12/25 16:59:33 ncq
-- - user exists check in curr_lang
-- - unset_curr_lang
--
-- Revision 1.3 2008/10/26 01:24:21 ncq
-- - adjust for "user" -> db_user
--
-- Revision 1.2 2008/10/25 20:50:32 ncq
-- - forward port "user" fixes for v9 -> v10, too, since someone might upgrade an unpatched v9
--
-- Revision 1.1 2008/10/12 14:58:07 ncq
-- - new
--
--
|
