Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d3-5.7.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d3-5.7.0d4.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d3-5.7.0d4.sql 30 Jan 2011 01:36:33 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d3-5.7.0d4.sql 20 Feb 2011 00:34:00 -0000 1.2 @@ -5,6 +5,11 @@ insert into acs_datatypes (datatype, database_type) +(select 'text', 'text' from dual + where not exists (select 1 from acs_datatypes where datatype = 'text')); + +insert into acs_datatypes + (datatype, database_type) (select 'richtext', 'text' from dual where not exists (select 1 from acs_datatypes where datatype = 'richtext')); @@ -181,314 +186,6 @@ return ''to_char('' || p_attribute_name || '', ''''YYYY-MM-DD HH24:MI:SS'''')''; end;' language 'plpgsql'; --- New tables to model object-based views. Since view names must be unique in SQL --- we force them to be unique in our datamodel, too (rather than only unique to the --- object type). - -create table acs_views ( - object_view text - constraint acs_views__pk - primary key, - object_type text - constraint acs_views_object_type_fk - references acs_object_types - on delete cascade, - pretty_name text - constraint acs_views_pretty_name_nn - not null, - root_view_p boolean default 'f' - constraint acs_views_root_view_p_nn - not null -); - -comment on table acs_views is ' - Track information on object type-based views, including the initial view created for - an object type -'; - -comment on column acs_views.object_view is ' - The name of the view. The initial view for an object type is given the name - "object_type_name_v". If the object type the view references is deleted, the acs_view - will be dropped, too. -'; - -comment on column acs_views.object_type is ' - The object type this view is built from. -'; - -comment on column acs_views.pretty_name is ' - Pretty name for this view -'; - -create table acs_view_attributes ( - attribute_id integer - constraint acs_view_attributes_attribute_id_fk - references acs_attributes - on delete cascade, - view_attribute text, - object_view text - constraint acs_view_attributes_object_view_fk - references acs_views(object_view) - on delete cascade, - pretty_name text - constraint acs_views_pretty_name_nn - not null, - sort_order integer - constraint acs_views_sort_order - not null, - col_expr text - constraint acs_view_attributes_type_col_spec_nn - not null, - constraint acs_view_attributes_pk primary key (object_view, attribute_id) -); - -comment on table acs_view_attributes is ' - Track information on view attributes. This extends the acs_attributes table with - view-specific attribute information. If the view or object type attribute referenced - by the view attribute is deleted, the view attribute will be, too. -'; - -comment on column acs_view_attributes.attribute_id is ' - The acs_attributes row we are augmenting with view-specific information. This is not - used as the primary key because multiple views might use the same acs_attribute. -'; - -comment on column acs_view_attributes.view_attribute is ' - The name assigned to this column in the view. Usually it is the acs_attribute name, - but if multiple attributes have the same name, they are disambiguated with suffixes - of the form _N. -'; - -comment on column acs_view_attributes.object_view is ' - The name of the view this attribute is being declared for. -'; - -comment on column acs_view_attributes.pretty_name is ' - The pretty name of the view. -'; - -comment on column acs_view_attributes.sort_order is ' - The order of display when shown to a user. A bit odd to have it here, but - the original object attributes have a sort_order defined, so for consistency we will - do the same for view attributes. -'; - -comment on column acs_view_attributes.col_expr is ' - The expression used to build the column. Usually just the acs_attribute name, but certain - datatypes might call a function on the attribute value (i.e. "to_char()" for timestamp - types). -'; - -select define_function_args('acs_view__drop_sql_view','object_view'); - -create or replace function acs_view__drop_sql_view (varchar) -returns integer as ' -declare - p_view alias for $1; -begin - if table_exists(p_view) then - execute ''drop view '' || p_view; - end if; - return 0; -end;' language 'plpgsql'; - -select define_function_args('acs_view__create_sql_view','object_view'); - -create or replace function acs_view__create_sql_view (varchar) -returns integer as ' -declare - p_view alias for $1; - v_cols varchar; - v_tabs varchar; - v_joins varchar; - v_first_p boolean; - v_join_rec record; - v_attr_rec record; - v_tree_sortkey_found_p boolean; -begin - - if length(p_view) > 64 then - raise exception ''View name "%" cannot be longer than 64 characters.'',p_type; - end if; - - if not exists (select 1 - from acs_views - where object_view = p_view) then - raise exception ''No object type named "%" exists'',p_view; - end if; - - v_tabs := ''''; - v_joins := ''''; - v_first_p := ''t''; - v_tree_sortkey_found_p := ''f''; - v_cols := ''acs_objects.object_id as '' || p_view || ''_id''; - - for v_join_rec in select ot2.object_type, ot2.table_name, ot2.id_column, - tree_level(ot2.tree_sortkey) as level - from acs_object_types ot1, acs_object_types ot2, acs_views ov - where ov.object_view = p_view - and ot1.object_type = ov.object_type - and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey) - order by ot2.tree_sortkey desc - loop - if v_join_rec.table_name is not null then - - if not v_tree_sortkey_found_p and column_exists(v_join_rec.table_name, ''tree_sortkey'') then - v_cols := v_cols || '','' || v_join_rec.table_name || ''.tree_sortkey''; - v_tree_sortkey_found_p := ''t''; - end if; - - if not v_first_p then - v_tabs := v_tabs || '', ''; - end if; - v_tabs := v_tabs || v_join_rec.table_name; - - - if v_join_rec.table_name <> ''acs_objects'' then - if not v_first_p then - v_joins := v_joins || '' and ''; - end if; - v_joins := v_joins || '' acs_objects.object_id = '' || v_join_rec.table_name || - ''.'' || v_join_rec.id_column; - end if; - - v_first_p := ''f''; - - end if; - end loop; - - for v_attr_rec in select view_attribute, col_expr - from acs_view_attributes - where object_view = p_view - order by sort_order - loop - v_cols := v_cols || '','' || v_attr_rec.col_expr || '' as '' || v_attr_rec.view_attribute; - end loop; - - if v_joins <> '''' then - v_joins := '' where '' || v_joins; - end if; - - if table_exists(p_view) then - execute ''drop view '' || p_view; - end if; - - execute ''create or replace view '' || p_view || '' as select '' || - v_cols || '' from '' || v_tabs || v_joins; - - return 0; -end;' language 'plpgsql'; - --- Create the attributes select view for a type. The view is given the type's table --- name appended with "v". The only id column returned is object_id, which avoids duplicate --- column name issues. - -select define_function_args('acs_object_type__refresh_view','object_type'); - --- Need to create the view and view attribute metadata ... - -create or replace function acs_object_type__refresh_view (varchar) -returns integer as ' -declare - p_type alias for $1; - v_attr_rec record; - v_type_rec record; - v_dupes integer; - v_view_attribute text; - v_col_expr text; - v_sort_order integer; - v_view text; -begin - - if not exists (select 1 - from acs_object_types - where object_type = p_type) then - raise exception ''No object type named "%" exists'',p_type; - end if; - - v_view := replace(p_type, '':'', ''_'') || ''_v''; - - delete from acs_views where object_view = v_view; - - insert into acs_views - (object_view, object_type, pretty_name, root_view_p) - select v_view, p_type, pretty_name, ''t'' - from acs_object_types - where object_type = p_type; - - v_sort_order := 1; - - for v_type_rec in select ot2.object_type, ot2.table_name, ot2.id_column, - tree_level(ot2.tree_sortkey) as level - from acs_object_types ot1, acs_object_types ot2 - where ot1.object_type = p_type - and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey) - order by ot2.tree_sortkey desc - loop - - for v_attr_rec in select a.attribute_name, d.column_output_function, a.attribute_id, - a.pretty_name - from acs_attributes a, acs_datatypes d - where a.object_type = v_type_rec.object_type - and a.storage = ''type_specific'' - and a.table_name is null - and a.datatype = d.datatype - loop - - v_view_attribute := v_attr_rec.attribute_name; - v_col_expr := v_type_rec.table_name || ''.'' || v_view_attribute; - - if v_attr_rec.column_output_function is not null then - execute ''select '' || v_attr_rec.column_output_function || ''('''''' || v_col_expr || - '''''')'' into v_col_expr; - end if; - - -- The check for dupes could be rolled into the select above but it is far more - -- readable when broken out, I think. - - v_dupes := count(*) - from acs_attributes - where attribute_name = v_attr_rec.attribute_name - and object_type in (select ot2.object_type - from acs_object_types ot1, acs_object_types ot2 - where ot1.object_type = v_type_rec.object_type - and ot1.tree_sortkey - between tree_left(ot2.tree_sortkey) - and tree_right(ot2.tree_sortkey)); - if v_dupes > 0 then - v_view_attribute := v_view_attribute || ''_'' || substr(to_char(v_dupes, ''9''),2,1); - end if; - - insert into acs_view_attributes - (attribute_id, view_attribute, object_view, pretty_name, sort_order, col_expr) - values - (v_attr_rec.attribute_id, v_view_attribute, v_view, v_attr_rec.pretty_name, v_sort_order, - v_col_expr); - - v_sort_order := v_sort_order + 1; - - end loop; - end loop; - - perform acs_view__create_sql_view(replace(p_type, '':'', ''_'') || ''_v''); - - -- Now fix all subtypes (really only necessary for the attributes view when an attribute - -- has been added or dropped, but there is no harm in doing it always). The supertype - -- not equal to object_type bit is again due to the fact that acs_object has itself - -- as its supertype rather than null. - - for v_type_rec in select object_type - from acs_object_types - where supertype = p_type - and supertype <> object_type - loop - perform acs_object_type__refresh_view(v_type_rec.object_type); - end loop; - - return 0; -end;' language 'plpgsql'; - - select define_function_args('acs_object_type__create_type','object_type,pretty_name,pretty_plural,supertype,table_name,id_column,package_name,abstract_p;f,type_extension_table,name_method,create_table_p;f,dynamic_p;f'); create or replace function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar, boolean, boolean) @@ -933,4 +630,3 @@ return acs_attribute__drop_attribute($1, $2, ''f''); end;' language 'plpgsql'; -select acs_object_type__refresh_view('acs_object');