Index: openacs-4/packages/acs-object-management/sql/postgresql/acs-kernel-changes-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-object-management/sql/postgresql/Attic/acs-kernel-changes-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-object-management/sql/postgresql/acs-kernel-changes-create.sql 28 Jul 2009 23:35:09 -0000 1.1 +++ openacs-4/packages/acs-object-management/sql/postgresql/acs-kernel-changes-create.sql 27 Nov 2009 00:38:50 -0000 1.2 @@ -3,6 +3,11 @@ alter table acs_datatypes add column_check_expr text; alter table acs_datatypes add column_output_function text; +insert into acs_datatypes + (datatype, database_type) +values + ('richtext', 'text'); + -- Making user and person dynamic can lead to a broken web site, so -- for now at least I won't do it. Code using these types have assumptions -- about the existence of certain attributes, and of course deleting them @@ -41,7 +46,9 @@ '; comment on column acs_datatypes.column_check_expr is ' - Optional check constraint expression to declare for the type_specific database column. + Optional check constraint expression to declare for the type_specific database column. In + Oracle, for instance, the abstract "boolean" type is declared "text", with a column + check expression to restrict the values to "f" and "t". '; comment on column acs_datatypes.column_output_function is ' @@ -55,189 +62,217 @@ -- it possible to write a high-level type specification that works in both Oracle and PG. -- DRB: add double bigint etc if Oracle supports them +begin; -update acs_datatypes -set database_type = 'varchar', - column_size = '4000' -where datatype = 'string'; + update acs_datatypes + set database_type = 'varchar', + column_size = '250' + where datatype = 'url'; -update acs_datatypes -set database_type = 'boolean' -where datatype = 'boolean'; + update acs_datatypes + set database_type = 'varchar', + column_size = '4000' + where datatype = 'string'; -update acs_datatypes -set database_type = 'numeric', - column_size = '10,2' -where datatype = 'number'; + update acs_datatypes + set database_type = 'boolean' + where datatype = 'boolean'; -update acs_datatypes -set database_type = 'integer' -where datatype = 'integer'; + update acs_datatypes + set database_type = 'numeric', + column_size = '10,2' + where datatype = 'number'; -update acs_datatypes -set database_type = 'money' -where datatype = 'money'; + update acs_datatypes + set database_type = 'integer' + where datatype = 'integer'; -update acs_datatypes -set database_type = 'timestamp' -where datatype = 'date'; + update acs_datatypes + set database_type = 'money' + where datatype = 'money'; -update acs_datatypes -set database_type = 'timestamp' -where datatype = 'timestamp'; + update acs_datatypes + set database_type = 'timestamp' + where datatype = 'date'; -update acs_datatypes -set database_type = 'timestamp' -where datatype = 'time_of_day'; + update acs_datatypes + set database_type = 'timestamp' + where datatype = 'timestamp'; -update acs_datatypes -set database_type = 'varchar', - column_size = '100' -where datatype = 'enumeration'; + update acs_datatypes + set database_type = 'timestamp' + where datatype = 'time_of_day'; -update acs_datatypes -set database_type = 'varchar', - column_size = 200 -where datatype = 'email'; + update acs_datatypes + set database_type = 'varchar', + column_size = '100' + where datatype = 'enumeration'; -update acs_datatypes -set database_type = 'varchar', - column_size = 200 -where datatype = 'file'; + update acs_datatypes + set database_type = 'varchar', + column_size = 200 + where datatype = 'email'; -update acs_datatypes -set database_type = 'text' -where datatype = 'text'; + update acs_datatypes + set database_type = 'varchar', + column_size = 200 + where datatype = 'file'; -update acs_datatypes -set database_type = 'varchar', - column_size = 100 -where datatype = 'keyword'; + update acs_datatypes + set database_type = 'text' + where datatype = 'text'; -update acs_datatypes -set column_output_function = 'acs_datatype__timestamp_output_function' -where datatype = 'date'; + update acs_datatypes + set database_type = 'varchar', + column_size = 100 + where datatype = 'keyword'; -update acs_datatypes -set column_output_function = 'acs_datatype__timestamp_output_function' -where datatype = 'timestamp'; + update acs_datatypes + set column_output_function = 'acs_datatype__date_output_function' + where datatype = 'date'; -update acs_datatypes -set column_output_function = 'acs_datatype__timestamp_output_function' -where datatype = 'time_of_day'; + update acs_datatypes + set column_output_function = 'acs_datatype__timestamp_output_function' + where datatype = 'timestamp'; + update acs_datatypes + set column_output_function = 'acs_datatype__timestamp_output_function' + where datatype = 'time_of_day'; +end; + +create or replace function acs_datatype__date_output_function(text) +returns text as ' +declare + p_attribute_name alias for $1; +begin + return ''to_char('' || p_attribute_name || '', ''''YYYY-MM-DD'''')''; +end;' language 'plpgsql'; + create or replace function acs_datatype__timestamp_output_function(text) returns text as ' declare p_attribute_name alias for $1; begin - return ''to_char('' || p_attribute_name || '', ''''YYYY-MM-DD HH24:MI'''')''; + 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_object_views ( +create table acs_views ( object_view text - constraint acs_object_views__pk + constraint acs_views__pk primary key, object_type text - constraint acs_object_views_object_type_fk + constraint acs_views_object_type_fk references acs_object_types on delete cascade, pretty_name text - constraint acs_object_views_pretty_name_nn + constraint acs_views_pretty_name_nn not null, root_view_p boolean default 'f' - constraint acs_object_views_root_view_p_nn + constraint acs_views_root_view_p_nn not null ); -comment on table acs_object_views is ' +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_object_views.object_view is ' +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_object_views.object_type is ' +comment on column acs_views.object_type is ' The object type this view is built from. '; -comment on column acs_object_views.pretty_name is ' +comment on column acs_views.pretty_name is ' Pretty name for this view '; -create table acs_object_view_attributes ( +create table acs_view_attributes ( attribute_id integer - constraint acs_object_view_attributes_attribute_id_fk + constraint acs_view_attributes_attribute_id_fk references acs_attributes on delete cascade, - col_name text, + view_attribute text, object_view text - constraint acs_object_view_attributes_object_view_fk - references acs_object_views(object_view) + constraint acs_view_attributes_object_view_fk + references acs_views(object_view) on delete cascade, pretty_name text - constraint acs_object_views_pretty_name_nn + constraint acs_views_pretty_name_nn not null, sort_order integer - constraint acs_object_views_sort_order + constraint acs_views_sort_order not null, col_expr text - constraint acs_object_view_attributes_type_col_spec_nn + constraint acs_view_attributes_type_col_spec_nn not null, - constraint acs_object_view_attributes_pk primary key (object_view, col_name) + constraint acs_view_attributes_pk primary key (object_view, attribute_id) ); -comment on table acs_object_view_attributes is ' +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_object_view_attributes.attribute_id is ' +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_object_view_attributes.col_name is ' +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_object_view_attributes.object_view is ' +comment on column acs_view_attributes.object_view is ' The name of the view this attribute is being declared for. '; -comment on column acs_object_view_attributes.pretty_name is ' +comment on column acs_view_attributes.pretty_name is ' The pretty name of the view. '; -comment on column acs_object_view_attributes.sort_order is ' +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_object_view_attributes.col_expr is ' +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_object_view__create_sql_view','object_view'); +select define_function_args('acs_view__drop_sql_view','object_view'); -create or replace function acs_object_view__create_sql_view (varchar) +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; @@ -252,7 +287,7 @@ end if; if not exists (select 1 - from acs_object_views + from acs_views where object_view = p_view) then raise exception ''No object type named "%" exists'',p_view; end if; @@ -261,11 +296,11 @@ v_joins := ''''; v_first_p := ''t''; v_tree_sortkey_found_p := ''f''; - v_cols := ''acs_objects.object_id''; + 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_object_views ov + 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) @@ -297,12 +332,12 @@ end if; end loop; - for v_attr_rec in select col_name, col_expr - from acs_object_view_attributes + 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.col_name; + v_cols := v_cols || '','' || v_attr_rec.col_expr || '' as '' || v_attr_rec.view_attribute; end loop; if v_joins <> '''' then @@ -334,7 +369,7 @@ v_attr_rec record; v_type_rec record; v_dupes integer; - v_col_name text; + v_view_attribute text; v_col_expr text; v_sort_order integer; v_view text; @@ -348,9 +383,9 @@ v_view := p_type || ''_v''; - delete from acs_object_views where object_view = v_view; + delete from acs_views where object_view = v_view; - insert into acs_object_views + 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 @@ -375,8 +410,8 @@ and a.datatype = d.datatype loop - v_col_name := v_attr_rec.attribute_name; - v_col_expr := v_type_rec.table_name || ''.'' || v_col_name; + 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 || @@ -396,21 +431,21 @@ between tree_left(ot2.tree_sortkey) and tree_right(ot2.tree_sortkey)); if v_dupes > 0 then - v_col_name := v_col_name || ''_'' || substr(to_char(v_dupes, ''9''),2,1); + v_view_attribute := v_view_attribute || ''_'' || substr(to_char(v_dupes, ''9''),2,1); end if; - insert into acs_object_view_attributes - (attribute_id, col_name, object_view, pretty_name, sort_order, col_expr) + insert into acs_view_attributes + (attribute_id, view_attribute, object_view, pretty_name, sort_order, col_expr) values - (v_attr_rec.attribute_id, v_col_name, v_view, v_attr_rec.pretty_name, v_sort_order, + (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_object_view__create_sql_view(p_type || ''_v''); + perform acs_view__create_sql_view(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 @@ -568,7 +603,7 @@ p_size alias for $16; p_null_p alias for $17; p_references alias for $18; - p_check_expr alias for $19; + p_check_expr alias for $19; p_column_spec alias for $20; v_sort_order acs_attributes.sort_order%TYPE;