OpenACS

davidam
committed
MAIN:davidam:20070827093900:1
on 27 Aug 07
file xowiki-portlet-admin.sql was initially added on branch HEAD.
MAIN:davidam:20070827093900:1
/upgrade/upgrade-5.7.0d10-5.7.0d11.sql (deleted)
1  
2   create or replace function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean,boolean,varchar,varchar,boolean,varchar,varchar,varchar)
3   returns integer as '
4   declare
5     p_object_type            alias for $1; 
6     p_attribute_name         alias for $2; 
7     p_datatype               alias for $3; 
8     p_pretty_name            alias for $4; 
9     p_pretty_plural          alias for $5;  -- default null
10     p_table_name             alias for $6;  -- default null
11     p_column_name            alias for $7;  -- default null
12     p_default_value          alias for $8;  -- default null
13     p_min_n_values           alias for $9;  -- default 1
14     p_max_n_values           alias for $10; -- default 1
15     p_sort_order             alias for $11; -- default null
16     p_storage                alias for $12; -- default ''type_specific''
17     p_static_p               alias for $13; -- default ''f''
18     p_create_column_p        alias for $14; -- default ''f''
19     p_database_type          alias for $15; -- default null
20     p_size                   alias for $16; -- default null
21     p_null_p                 alias for $17; -- default ''t''
22     p_references             alias for $18; -- default null
23     p_check_expr             alias for $19; -- default null
24     p_column_spec            alias for $20; -- default null
25  
26     v_sort_order            acs_attributes.sort_order%TYPE;
27     v_attribute_id          acs_attributes.attribute_id%TYPE;
28     v_column_spec           text;
29     v_table_name            text;
30     v_constraint_stub       text;
31     v_column_name           text;
32     v_datatype              record;
33  
34   begin
35  
36     if not exists (select 1
37                    from acs_object_types
38                    where object_type = p_object_type) then
39       raise exception ''Object type % does not exist'', p_object_type;
40     end if;
41  
42     if p_sort_order is null then
43       select coalesce(max(sort_order), 1) into v_sort_order
44       from acs_attributes
45       where object_type = p_object_type
46       and attribute_name = p_attribute_name;
47     else
48       v_sort_order := p_sort_order;
49     end if;
50  
51     select nextval(''t_acs_attribute_id_seq'') into v_attribute_id;
52  
53     insert into acs_attributes
54       (attribute_id, object_type, table_name, column_name, attribute_name,
55        pretty_name, pretty_plural, sort_order, datatype, default_value,
56        min_n_values, max_n_values, storage, static_p)
57     values
58       (v_attribute_id, p_object_type,
59        p_table_name, p_column_name,
60        p_attribute_name, p_pretty_name,
61        p_pretty_plural, v_sort_order,
62        p_datatype, p_default_value,
63        p_min_n_values, p_max_n_values,
64        p_storage, p_static_p);
65  
66     if p_create_column_p then
67  
68       select table_name into v_table_name from acs_object_types
69       where object_type = p_object_type;
70  
71       if not exists (select 1
72                      from pg_class
73                      where relname = lower(v_table_name)) then
74         raise exception ''Table % for object type % does not exist'', v_table_name, p_object_type;
75       end if;
76  
77       -- Add the appropriate column to the table
78  
79       -- We can only create the table column if
80       -- 1. the attribute is declared type_specific (generic storage uses an auxillary table)
81       -- 2. the attribute is not declared static
82       -- 3. it does not already exist in the table
83  
84       if p_storage <> ''type_specific'' then
85         raise exception ''Attribute % for object type % must be declared with type_specific storage'',
86           p_attribute_name, p_object_type;
87       end if;
88  
89       if p_static_p then
90         raise exception ''Attribute % for object type % can not be declared static'',
91           p_attribute_name, p_object_type;
92       end if;
93  
94       if p_table_name is not null then
95         raise exception ''Attribute % for object type % can not specify a table for storage'', p_attribute_name, p_object_type;
96       end if;
97  
98       if exists (select 1
99                  from pg_class c, pg_attribute a
100                  where c.relname::varchar = v_table_name
101                    and c.oid = a.attrelid
102                    and a.attname = lower(p_attribute_name)) then
103         raise exception ''Column % for object type % already exists'',
104           p_attribute_name, p_object_type;
105       end if;
106  
107       -- all conditions for creating this column have been met, now let''s see if the type
108       -- spec is OK
109  
110       if p_column_spec is not null then
111         if p_database_type is not null
112           or p_size is not null
113           or p_null_p is not null
114           or p_references is not null
115           or p_check_expr is not null then
116         raise exception ''Attribute % for object type % is being created with an explicit column_spec, but not all of the type modification fields are null'',
117           p_attribute_name, p_object_type;
118         end if;
119         v_column_spec := p_column_spec;
120       else
121         select coalesce(p_database_type, database_type) as database_type,
122           coalesce(p_size, column_size) as column_size,
123           coalesce(p_check_expr, column_check_expr) as check_expr
124         into v_datatype
125         from acs_datatypes
126         where datatype = p_datatype;
127    
128         v_column_spec := v_datatype.database_type;
129  
130         if v_datatype.column_size is not null then
131           v_column_spec := v_column_spec || ''('' || v_datatype.column_size || '')'';
132         end if;
133  
134         v_constraint_stub := '' constraint '' || p_object_type || ''_'' ||
135           p_attribute_name || ''_'';
136  
137         if v_datatype.check_expr is not null then
138           v_column_spec := v_column_spec || v_constraint_stub || ''ck check('' ||
139             p_attribute_name || v_datatype.check_expr || '')'';
140         end if;
141  
142         if not p_null_p then
143           v_column_spec := v_column_spec || v_constraint_stub || ''nn not null'';
144         end if;
145  
146         if p_references is not null then
147           v_column_spec := v_column_spec || v_constraint_stub || ''fk references '' ||
148             p_references || '' on delete'';
149           if p_null_p then
150             v_column_spec := v_column_spec || '' set null'';
151           else
152             v_column_spec := v_column_spec || '' cascade'';
153           end if;
154         end if;
155  
156       end if;
157          
158       execute ''alter table '' || v_table_name || '' add '' || p_attribute_name || '' '' ||
159               v_column_spec;
160  
161     end if;
162  
163     return v_attribute_id;
164  
165   end;' language 'plpgsql';
166  
167   create or replace function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean)
168   returns integer as '
169   declare
170     p_object_type            alias for $1; 
171     p_attribute_name         alias for $2; 
172     p_datatype               alias for $3; 
173     p_pretty_name            alias for $4; 
174     p_pretty_plural          alias for $5;  -- default null
175     p_table_name             alias for $6;  -- default null
176     p_column_name            alias for $7;  -- default null
177     p_default_value          alias for $8;  -- default null
178     p_min_n_values           alias for $9;  -- default 1
179     p_max_n_values           alias for $10; -- default 1
180     p_sort_order             alias for $11; -- default null
181     p_storage                alias for $12; -- default ''type_specific''
182     p_static_p               alias for $13; -- default ''f''
183   begin
184     return acs_attribute__create_attribute(p_object_type,
185       p_attribute_name, p_datatype, p_pretty_name,
186       p_pretty_plural, p_table_name, p_column_name,
187       p_default_value, p_min_n_values,
188       p_max_n_values, p_sort_order, p_storage,
189       p_static_p, ''f'', null, null, null, null, null, null);
190   end;' language 'plpgsql';
191  
192   create or replace function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,integer,varchar,boolean)
193   returns integer as '
194   begin
195       return acs_attribute__create_attribute ($1, $2, $3, $4, $5, $6, $7, cast ($8 as varchar), $9, $10, $11, $12, $13);
196   end;' language 'plpgsql';
197  
198   create or replace function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar, boolean, boolean)
199   returns integer as '
200   declare
201     p_object_type            alias for $1; 
202     p_pretty_name            alias for $2; 
203     p_pretty_plural          alias for $3; 
204     p_supertype              alias for $4; 
205     p_table_name             alias for $5;  -- default null
206     p_id_column              alias for $6;  -- default null
207     p_package_name           alias for $7;  -- default null
208     p_abstract_p             alias for $8;  -- default ''f''
209     p_type_extension_table   alias for $9;  -- default null
210     p_name_method            alias for $10; -- default null
211     p_create_table_p         alias for $11; -- default ''f''
212     p_dynamic_p              alias for $12; -- default ''f''
213     v_package_name                      acs_object_types.package_name%TYPE;
214     v_supertype                         acs_object_types.supertype%TYPE;
215     v_name_method                       varchar;
216     v_idx                               integer;
217     v_temp_p                            boolean;
218     v_supertype_table                   acs_object_types.table_name%TYPE;
219     v_id_column                         acs_object_types.id_column%TYPE;
220     v_table_name                        acs_object_types.table_name%TYPE;
221   begin
222       v_idx := position(''.'' in p_name_method);
223       if v_idx <> 0 then
224            v_name_method := substr(p_name_method,1,v_idx - 1) ||
225                          ''__'' || substr(p_name_method, v_idx + 1);
226       else
227            v_name_method := p_name_method;
228       end if;
229  
230       -- If we are asked to create the table, provide reasonable default values for the
231       -- table name and id column.  Traditionally OpenACS uses the plural form of the type
232       -- name.  This code appends "_t" (for "table") because the use of english plural rules
233       -- does not work well for all languages.
234  
235       if p_create_table_p and (p_table_name is null or p_table_name = '''') then
236         v_table_name := p_object_type || ''_t'';
237       else
238         v_table_name := p_table_name;
239       end if;
240  
241       if p_create_table_p and (p_id_column is null or p_id_column = '''') then
242         v_id_column := p_object_type || ''_id'';
243       else
244         v_id_column := p_id_column;
245       end if;
246  
247       if p_package_name is null or p_package_name = '''' then
248         v_package_name := p_object_type;
249       else
250         v_package_name := p_package_name;
251       end if;
252  
253       if p_object_type <> ''acs_object'' then
254         if p_supertype is null or p_supertype = '''' then
255           v_supertype := ''acs_object'';
256         else
257           v_supertype := p_supertype;
258           if not acs_object_type__is_subtype_p(''acs_object'', p_supertype) then
259             raise exception ''%s is not a valid type'', p_supertype;
260           end if;
261         end if;
262       end if;
263  
264       insert into acs_object_types
265         (object_type, pretty_name, pretty_plural, supertype, table_name,
266          id_column, abstract_p, type_extension_table, package_name,
267          name_method, dynamic_p)
268       values
269         (p_object_type, p_pretty_name,
270          p_pretty_plural, v_supertype,
271          v_table_name, v_id_column,
272          p_abstract_p, p_type_extension_table,
273          v_package_name, v_name_method, p_dynamic_p);
274  
275       if p_create_table_p then
276  
277         if exists (select 1
278                    from pg_class
279                    where relname = lower(v_table_name)) then
280           raise exception ''Table "%" already exists'', v_table_name;
281         end if;
282  
283         loop
284           select table_name,object_type into v_supertype_table,v_supertype
285           from acs_object_types
286           where object_type = v_supertype;
287           exit when v_supertype_table is not null;
288         end loop;
289    
290         execute ''create table '' || v_table_name || '' ('' ||
291           v_id_column || '' integer constraint '' || v_table_name ||
292           ''_pk primary key '' || '' constraint '' || v_table_name ||
293           ''_fk references '' || v_supertype_table || '' on delete cascade)'';
294       end if;
295  
296       return 0;
297   end;' language 'plpgsql';