Index: openacs-4/packages/acs-content-repository/acs-content-repository.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v
diff -u -N -r1.95 -r1.96
--- openacs-4/packages/acs-content-repository/acs-content-repository.info 30 Jan 2011 01:36:33 -0000 1.95
+++ openacs-4/packages/acs-content-repository/acs-content-repository.info 20 Feb 2011 00:34:00 -0000 1.96
@@ -7,7 +7,7 @@
t
t
-
+
OpenACS
The canonical repository for OpenACS content.
2010-06-17
@@ -20,7 +20,7 @@
GPL
3
-
+
Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v
diff -u -N -r1.56 -r1.57
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 2 Apr 2009 22:31:13 -0000 1.56
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 20 Feb 2011 00:34:00 -0000 1.57
@@ -10,33 +10,7 @@
-- License. Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html
-----------------------------------
--- CMS datatypes
-----------------------------------
--- create ats datatypes for cms
-create function inline_0 ()
-returns integer as '
-begin
- insert into acs_datatypes
- (datatype, max_n_values)
- values
- (''text'', null);
-
- insert into acs_datatypes
- (datatype, max_n_values)
- values
- (''keyword'', 1);
-
- return 0;
-end;' language 'plpgsql';
-
-select inline_0 ();
-
-drop function inline_0 ();
-
-
-
--------------------------------------------------------------
-- MIME TYPES
--------------------------------------------------------------
Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql,v
diff -u -N -r1.54 -r1.55
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 27 Jan 2010 17:34:09 -0000 1.54
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 20 Feb 2011 00:34:00 -0000 1.55
@@ -68,21 +68,10 @@
end if;
end if;
- -- create the attribute table if not already created
-
- select count(*) > 0 into v_temp_p
+ select count(*) = 0 into v_temp_p
from pg_class
where relname = lower(create_type__table_name);
- if NOT v_temp_p and create_type__table_name is not null then
- select table_name into v_supertype_table from acs_object_types
- where object_type = create_type__supertype;
-
- execute ''create table '' || create_type__table_name || '' ('' ||
- create_type__id_column || '' integer constraint '' || create_type__table_name || ''_pk primary key '' ||
- '' constraint '' || create_type__table_name || ''_fk references '' || v_supertype_table || '')'';
- end if;
-
PERFORM acs_object_type__create_type (
create_type__content_type,
create_type__pretty_name,
@@ -93,7 +82,9 @@
null,
''f'',
null,
- create_type__name_method
+ create_type__name_method,
+ v_temp_p,
+ ''f''
);
PERFORM content_type__refresh_view(create_type__content_type);
@@ -324,8 +315,6 @@
return 0;
end;' language 'plpgsql';
-
-
select define_function_args('content_type__create_attribute','content_type,attribute_name,datatype,pretty_name,pretty_plural,sort_order,default_value,column_spec;text');
create or replace function content_type__create_attribute (varchar,varchar,varchar,varchar,varchar,integer,varchar,varchar)
@@ -359,12 +348,6 @@
and c.oid = a.attrelid
and a.attname = lower(create_attribute__attribute_name);
- if NOT v_column_exists then
- execute ''alter table '' || v_table_name || '' add '' ||
- create_attribute__attribute_name || '' ''
- || create_attribute__column_spec;
- end if;
-
v_attr_id := acs_attribute__create_attribute (
create_attribute__content_type,
create_attribute__attribute_name,
@@ -378,7 +361,14 @@
1,
create_attribute__sort_order,
''type_specific'',
- ''f''
+ ''f'',
+ not v_column_exists,
+ null,
+ null,
+ null,
+ null,
+ null,
+ create_attribute__column_spec
);
PERFORM content_type__refresh_view(create_attribute__content_type);
@@ -387,7 +377,6 @@
end;' language 'plpgsql';
-
select define_function_args('content_type__drop_attribute','content_type,attribute_name,drop_column;f');
create or replace function content_type__drop_attribute (varchar,varchar,boolean)
Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.7.0d2-5.7.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.7.0d2-5.7.0d3.sql,v
diff -u -N -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.7.0d2-5.7.0d3.sql 30 Jan 2011 01:36:33 -0000 1.1
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.7.0d2-5.7.0d3.sql 20 Feb 2011 00:34:00 -0000 1.2
@@ -28,6 +28,10 @@
end if;
end if;
+ select count(*) = 0 into v_temp_p
+ from pg_class
+ where relname = lower(create_type__table_name);
+
PERFORM acs_object_type__create_type (
create_type__content_type,
create_type__pretty_name,
@@ -39,7 +43,7 @@
''f'',
null,
create_type__name_method,
- ''t'',
+ v_temp_p,
''f''
);
@@ -75,6 +79,12 @@
raise EXCEPTION ''-20000: Content type % does not exist in content_type.create_attribute'', create_attribute__content_type;
end if;
+ select count(*) > 0 into v_column_exists
+ from pg_class c, pg_attribute a
+ where c.relname::varchar = v_table_name
+ and c.oid = a.attrelid
+ and a.attname = lower(create_attribute__attribute_name);
+
v_attr_id := acs_attribute__create_attribute (
create_attribute__content_type,
create_attribute__attribute_name,
@@ -89,7 +99,7 @@
create_attribute__sort_order,
''type_specific'',
''f'',
- ''t'',
+ not v_column_exists,
null,
null,
null,
Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql,v
diff -u -N -r1.30 -r1.31
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql 10 Mar 2010 00:44:03 -0000 1.30
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql 20 Feb 2011 00:34:00 -0000 1.31
@@ -266,14 +266,22 @@
constraint acs_datatypes_datatype_pk primary key,
max_n_values integer default 1
constraint acs_datatypes_max_n_values_ck
- check (max_n_values > 0)
+ check (max_n_values > 0),
+ database_type text,
+ column_size text,
+ column_check_expr text,
+ column_output_function text
);
comment on table acs_datatypes is '
- Defines the set of available datatypes for acs_attributes. These
- datatypes are abstract, not implementation-specific, i.e., they
- are not Oracle datatypes. The set of pre-defined datatypes is
- inspired by XForms (http://www.w3.org/TR/xforms-datamodel/).
+ Defines the set of available abstract datatypes for acs_attributes, along with
+ an optional default mapping to a database type, size, and constraint to use if the
+ attribute is created with create_attribute''s storage_type param set to "type_specific"
+ and the create_storage_p param is set to true. These defaults can be overwritten by
+ the caller.
+
+ The set of pre-defined datatypes is inspired by XForms
+ (http://www.w3.org/TR/xforms-datamodel/).
';
comment on column acs_datatypes.max_n_values is '
@@ -285,80 +293,125 @@
optional, i.e., min_n_values would always be zero.
';
+comment on column acs_datatypes.database_type is '
+ The base database type corresponding to the abstract datatype. For example "varchar" or
+ "integer".
+';
+
+comment on column acs_datatypes.column_size is '
+ Optional default column size specification to append to the base database type. For
+ example "1000" for the "string" abstract datatype, or "10,2" for "number".
+';
+
+comment on column acs_datatypes.column_check_expr is '
+ 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 '
+ Function to call for this datatype when building a select view. If not null, it will
+ be called with an attribute name and is expected to return an expression on that
+ attribute. Example: date attributes will be transformed to calls to "to_char()".
+';
+
+
-- Load pre-defined datatypes.
--
-create function inline_0 ()
-returns integer as '
-begin
+begin;
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''string'', null);
+ ('string', null, 'varchar', '4000');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''boolean'', 1);
+ ('boolean', 1, 'bool', null);
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''number'', null);
+ ('number', null, 'numeric', '10,2');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''integer'', 1);
+ ('integer', 1, 'integer', null);
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''money'', null);
+ ('currency', null, 'money', null);
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_output_function)
values
- (''date'', null);
+ ('date', null, 'timestamp', 'acs_datatype__date_output_function');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_output_function)
values
- (''timestamp'', null);
+ ('timestamp', null, 'timestamp', 'acs_datatype__timestamp_output_function');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_output_function)
values
- (''time_of_day'', null);
+ ('time_of_day', null, 'timestamp', 'acs_datatype__timestamp_output_function');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''enumeration'', null);
+ ('enumeration', null, 'varchar', '100');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''url'', null);
+ ('url', null, 'varchar', '250');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''email'', null);
+ ('email', null, 'varchar', '200');
insert into acs_datatypes
- (datatype, max_n_values)
+ (datatype, max_n_values, database_type, column_size)
values
- (''file'', 1);
+ ('file', 1, 'varchar', '100');
- return 0;
-end;' language 'plpgsql';
+insert into acs_datatypes
+ (datatype, max_n_values, database_type, column_size)
+values
+ ('text', null, 'text', null);
-select inline_0 ();
+insert into acs_datatypes
+ (datatype, max_n_values, database_type)
+values
+ ('keyword', 1, 'text');
-drop function inline_0 ();
+insert into acs_datatypes
+ (datatype, max_n_values, database_type, column_size)
+values
+ ('richtext', null, 'text', null);
+insert into acs_datatypes
+ (datatype, max_n_values, database_type, column_size)
+values
+ ('filename', null, 'varchar', '100');
+insert into acs_datatypes
+ (datatype, max_n_values, database_type, column_size)
+values
+ ('float', null, 'float8', null);
+-- PG 8.x has no unsigned integer datatype
+insert into acs_datatypes
+ (datatype, max_n_values, database_type, column_size)
+values
+ ('naturalnum', null, 'integer', null);
+
+end;
+
--create table acs_input_types (
--);
@@ -512,104 +565,206 @@
from acs_object_types t) all_types
where attr.object_type = all_types.ancestor_type;
-
-----------------------
-- METADATA PACKAGES --
-----------------------
-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');
--- DRB: null table_name change
-create function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar)
+
+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)
returns integer as '
declare
- create_type__object_type alias for $1;
- create_type__pretty_name alias for $2;
- create_type__pretty_plural alias for $3;
- create_type__supertype alias for $4;
- create_type__table_name alias for $5; -- default null
- create_type__id_column alias for $6; -- default null
- create_type__package_name alias for $7; -- default null
- create_type__abstract_p alias for $8; -- default ''f''
- create_type__type_extension_table alias for $9; -- default null
- create_type__name_method alias for $10; -- default null
- v_package_name acs_object_types.package_name%TYPE;
- v_supertype acs_object_types.supertype%TYPE;
+ p_object_type alias for $1;
+ p_pretty_name alias for $2;
+ p_pretty_plural alias for $3;
+ p_supertype alias for $4;
+ p_table_name alias for $5; -- default null
+ p_id_column alias for $6; -- default null
+ p_package_name alias for $7; -- default null
+ p_abstract_p alias for $8; -- default ''f''
+ p_type_extension_table alias for $9; -- default null
+ p_name_method alias for $10; -- default null
+ p_create_table_p alias for $11;
+ p_dynamic_p alias for $12;
+ v_package_name acs_object_types.package_name%TYPE;
+ v_supertype acs_object_types.supertype%TYPE;
v_name_method varchar;
v_idx integer;
+ v_temp_p boolean;
+ v_supertype_table acs_object_types.table_name%TYPE;
+ v_id_column acs_object_types.id_column%TYPE;
+ v_table_name acs_object_types.table_name%TYPE;
begin
- v_idx := position(''.'' in create_type__name_method);
+ v_idx := position(''.'' in p_name_method);
if v_idx <> 0 then
- v_name_method := substr(create_type__name_method,1,v_idx - 1) ||
- ''__'' || substr(create_type__name_method, v_idx + 1);
+ v_name_method := substr(p_name_method,1,v_idx - 1) ||
+ ''__'' || substr(p_name_method, v_idx + 1);
else
- v_name_method := create_type__name_method;
+ v_name_method := p_name_method;
end if;
- if create_type__package_name is null or create_type__package_name = '''' then
- v_package_name := create_type__object_type;
+ -- If we are asked to create the table, provide reasonable default values for the
+ -- table name and id column. Traditionally OpenACS uses the plural form of the type
+ -- name. This code appends "_t" (for "table") because the use of english plural rules
+ -- does not work well for all languages.
+
+ if p_create_table_p and (p_table_name is null or p_table_name = '''') then
+ v_table_name := p_object_type || ''_t'';
else
- v_package_name := create_type__package_name;
+ v_table_name := p_table_name;
end if;
- if create_type__supertype is null or create_type__supertype = '''' then
- v_supertype := ''acs_object'';
- else
- v_supertype := create_type__supertype;
- end if;
+ if p_create_table_p and (p_id_column is null or p_id_column = '''') then
+ v_id_column := p_object_type || ''_id'';
+ else
+ v_id_column := p_id_column;
+ end if;
+ if p_package_name is null or p_package_name = '''' then
+ v_package_name := p_object_type;
+ else
+ v_package_name := p_package_name;
+ end if;
+
+ if p_supertype is null or p_supertype = '''' then
+ v_supertype := ''acs_object'';
+ else
+ v_supertype := p_supertype;
+ end if;
+
insert into acs_object_types
(object_type, pretty_name, pretty_plural, supertype, table_name,
id_column, abstract_p, type_extension_table, package_name,
- name_method)
+ name_method, dynamic_p)
values
- (create_type__object_type, create_type__pretty_name,
- create_type__pretty_plural, v_supertype,
- create_type__table_name, create_type__id_column,
- create_type__abstract_p, create_type__type_extension_table,
- v_package_name, v_name_method);
+ (p_object_type, p_pretty_name,
+ p_pretty_plural, v_supertype,
+ v_table_name, v_id_column,
+ p_abstract_p, p_type_extension_table,
+ v_package_name, v_name_method, p_dynamic_p);
+ if p_create_table_p then
+
+ if exists (select 1
+ from pg_class
+ where relname = lower(v_table_name)) then
+ raise exception ''Table "%" already exists'', v_table_name;
+ end if;
+
+ select table_name into v_supertype_table from acs_object_types
+ where object_type = p_supertype;
+
+ execute ''create table '' || v_table_name || '' ('' ||
+ v_id_column || '' integer constraint '' || v_table_name ||
+ ''_pk primary key '' || '' constraint '' || v_table_name ||
+ ''_fk references '' || v_supertype_table || '' on delete cascade)'';
+ end if;
+
return 0;
end;' language 'plpgsql';
+-- DRB: backwards compatibility version, don't allow for table creation.
-select define_function_args('acs_object_type__drop_type','object_type,cascade_p;f');
+create or replace function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar)
+returns integer as '
+declare
+ p_object_type alias for $1;
+ p_pretty_name alias for $2;
+ p_pretty_plural alias for $3;
+ p_supertype alias for $4;
+ p_table_name alias for $5; -- default null
+ p_id_column alias for $6; -- default null
+ p_package_name alias for $7; -- default null
+ p_abstract_p alias for $8; -- default ''f''
+ p_type_extension_table alias for $9; -- default null
+ p_name_method alias for $10; -- default null
+begin
+ return acs_object_type__create_type(p_object_type, p_pretty_name,
+ p_pretty_plural, p_supertype, p_table_name,
+ p_id_column, p_package_name, p_abstract_p,
+ p_type_extension_table, p_name_method,''f'',''f'');
+end;' language 'plpgsql';
+
+-- "cascade_p" corresponds to the more logical "drop_objects_p" in the content repository
+-- code. The name is being kept for backwards compatibilit.
+
+select define_function_args('acs_object_type__drop_type','object_type,cascade_p;f,drop_table_p;f,drop_children_p;f');
+
-- procedure drop_type
-create or replace function acs_object_type__drop_type (varchar,boolean)
+create or replace function acs_object_type__drop_type (varchar,boolean,boolean,boolean)
returns integer as '
declare
- drop_type__object_type alias for $1;
- drop_type__cascade_p alias for $2; -- default ''f''
+ p_object_type alias for $1;
+ p_drop_children_p alias for $2;
+ p_drop_table_p alias for $3;
+ p_cascade_p alias for $4;
row record;
object_row record;
+ v_table_name acs_object_types.table_name%TYPE;
begin
- if drop_type__cascade_p then
- for object_row in select object_id
- from acs_objects
- where object_type = drop_type__object_type
- loop
- PERFORM acs_object__delete (object_row.object_id);
- end loop;
- end if;
+ -- drop children recursively
+ if p_drop_children_p then
+ for row in select object_type
+ from acs_object_types
+ where supertype = p_object_type
+ loop
+ perform acs_object_type__drop_type(row.object_type, p_cascade_p, p_drop_table_p, ''t'');
+ end loop;
+ end if;
- -- drop all the attributes associated with this type
- for row in select attribute_name
- from acs_attributes
- where object_type = drop_type__object_type
+ -- drop object rows
+ if p_cascade_p then
+ for object_row in select object_id
+ from acs_objects
+ where object_type = p_object_type
loop
- PERFORM acs_attribute__drop_attribute (drop_type__object_type,
- row.attribute_name);
+ perform acs_object__delete (object_row.object_id);
end loop;
+ end if;
- delete from acs_attributes
- where object_type = drop_type__object_type;
+ -- drop all the attributes associated with this type
+ for row in select attribute_name
+ from acs_attributes
+ where object_type = p_object_type
+ loop
+ perform acs_attribute__drop_attribute (p_object_type, row.attribute_name);
+ end loop;
- delete from acs_object_types
- where object_type = drop_type__object_type;
+ -- Remove the associated table if it exists and p_drop_table_p is true
- return 0;
+ if p_drop_table_p then
+
+ select table_name into v_table_name
+ from acs_object_types
+ where object_type = p_object_type;
+
+ if found then
+ if not exists (select 1
+ from pg_class
+ where relname = lower(v_table_name)) then
+ raise exception ''Table "%" does not exist'', v_table_name;
+ end if;
+
+ execute ''drop table '' || v_table_name || '' cascade'';
+ end if;
+
+ end if;
+
+ delete from acs_object_types
+ where object_type = p_object_type;
+
+ return 0;
end;' language 'plpgsql';
+-- Retained for backwards compatibility
+create or replace function acs_object_type__drop_type (varchar,boolean)
+returns integer as '
+begin
+ return acs_object_type__drop_type($1,$2,''f'',''f'');
+end;' language 'plpgsql';
+
-- function pretty_name
create or replace function acs_object_type__pretty_name (varchar)
returns varchar as '
@@ -649,90 +804,255 @@
end;' language 'plpgsql' stable;
+select define_function_args('acs_attribute__create_attribute','object_type,attribute_name,datatype,pretty_name,pretty_plural,table_name,column_name,default_value,min_n_values;1,max_n_values;1,sort_order,storage;type_specific,static_p;f,create_column_p;f,database_type,size,null_p;t,references,check_expr,column_spec');
+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)
+returns integer as '
+declare
+ p_object_type alias for $1;
+ p_attribute_name alias for $2;
+ p_datatype alias for $3;
+ p_pretty_name alias for $4;
+ p_pretty_plural alias for $5; -- default null
+ p_table_name alias for $6; -- default null
+ p_column_name alias for $7; -- default null
+ p_default_value alias for $8; -- default null
+ p_min_n_values alias for $9; -- default 1
+ p_max_n_values alias for $10; -- default 1
+ p_sort_order alias for $11; -- default null
+ p_storage alias for $12; -- default ''type_specific''
+ p_static_p alias for $13; -- default ''f''
+ p_create_column_p alias for $14;
+ p_database_type alias for $15;
+ p_size alias for $16;
+ p_null_p alias for $17;
+ p_references alias for $18;
+ p_check_expr alias for $19;
+ p_column_spec alias for $20;
--- show errors
+ v_sort_order acs_attributes.sort_order%TYPE;
+ v_attribute_id acs_attributes.attribute_id%TYPE;
+ v_column_spec text;
+ v_table_name text;
+ v_constraint_stub text;
+ v_column_name text;
+ v_datatype record;
+begin
-select define_function_args('acs_attribute__create_attribute','object_type,attribute_name,datatype,pretty_name,pretty_plural,table_name,column_name,default_value,min_n_values;1,max_n_values;1,sort_order,storage;type_specific,static_p;f');
+ if not exists (select 1
+ from acs_object_types
+ where object_type = p_object_type) then
+ raise exception ''Object type % does not exist'', p_object_type;
+ end if;
--- create or replace package body acs_attribute
--- function create_attribute
-create or replace function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean)
-returns integer as '
-declare
- create_attribute__object_type alias for $1;
- create_attribute__attribute_name alias for $2;
- create_attribute__datatype alias for $3;
- create_attribute__pretty_name alias for $4;
- create_attribute__pretty_plural alias for $5; -- default null
- create_attribute__table_name alias for $6; -- default null
- create_attribute__column_name alias for $7; -- default null
- create_attribute__default_value alias for $8; -- default null
- create_attribute__min_n_values alias for $9; -- default 1
- create_attribute__max_n_values alias for $10; -- default 1
- create_attribute__sort_order alias for $11; -- default null
- create_attribute__storage alias for $12; -- default ''type_specific''
- create_attribute__static_p alias for $13; -- default ''f''
+ if p_sort_order is null then
+ select coalesce(max(sort_order), 1) into v_sort_order
+ from acs_attributes
+ where object_type = p_object_type
+ and attribute_name = p_attribute_name;
+ else
+ v_sort_order := p_sort_order;
+ end if;
- v_sort_order acs_attributes.sort_order%TYPE;
- v_attribute_id acs_attributes.attribute_id%TYPE;
-begin
- if create_attribute__sort_order is null then
- select coalesce(max(sort_order), 1) into v_sort_order
- from acs_attributes
- where object_type = create_attribute__object_type
- and attribute_name = create_attribute__attribute_name;
+ select nextval(''t_acs_attribute_id_seq'') into v_attribute_id;
+
+ insert into acs_attributes
+ (attribute_id, object_type, table_name, column_name, attribute_name,
+ pretty_name, pretty_plural, sort_order, datatype, default_value,
+ min_n_values, max_n_values, storage, static_p)
+ values
+ (v_attribute_id, p_object_type,
+ p_table_name, p_column_name,
+ p_attribute_name, p_pretty_name,
+ p_pretty_plural, v_sort_order,
+ p_datatype, p_default_value,
+ p_min_n_values, p_max_n_values,
+ p_storage, p_static_p);
+
+ if p_create_column_p then
+
+ select table_name into v_table_name from acs_object_types
+ where object_type = p_object_type;
+
+ if not exists (select 1
+ from pg_class
+ where relname = lower(v_table_name)) then
+ raise exception ''Table % for object type % does not exist'', v_table_name, p_object_type;
+ end if;
+
+ -- Add the appropriate column to the table
+
+ -- We can only create the table column if
+ -- 1. the attribute is declared type_specific (generic storage uses an auxillary table)
+ -- 2. the attribute is not declared static
+ -- 3. it does not already exist in the table
+
+ if p_storage <> ''type_specific'' then
+ raise exception ''Attribute % for object type % must be declared with type_specific storage'',
+ p_attribute_name, p_object_type;
+ end if;
+
+ if p_static_p then
+ raise exception ''Attribute % for object type % can not be declared static'',
+ p_attribute_name, p_object_type;
+ end if;
+
+ if p_table_name is not null then
+ raise exception ''Attribute % for object type % can not specify a table for storage'', p_attribute_name, p_object_type;
+ end if;
+
+ if exists (select 1
+ from pg_class c, pg_attribute a
+ where c.relname::varchar = v_table_name
+ and c.oid = a.attrelid
+ and a.attname = lower(p_attribute_name)) then
+ raise exception ''Column % for object type % already exists'',
+ p_attribute_name, p_object_type;
+ end if;
+
+ -- all conditions for creating this column have been met, now let''s see if the type
+ -- spec is OK
+
+ if p_column_spec is not null then
+ if p_database_type is not null
+ or p_size is not null
+ or p_null_p is not null
+ or p_references is not null
+ or p_check_expr is not null then
+ raise exception ''Attribute % for object type % is being created with an explicit column_spec, but not all of the type modification fields are null'',
+ p_attribute_name, p_object_type;
+ end if;
+ v_column_spec := p_column_spec;
else
- v_sort_order := create_attribute__sort_order;
+ select coalesce(p_database_type, database_type) as database_type,
+ coalesce(p_size, column_size) as column_size,
+ coalesce(p_check_expr, column_check_expr) as check_expr
+ into v_datatype
+ from acs_datatypes
+ where datatype = p_datatype;
+
+ v_column_spec := v_datatype.database_type;
+
+ if v_datatype.column_size is not null then
+ v_column_spec := v_column_spec || ''('' || v_datatype.column_size || '')'';
+ end if;
+
+ v_constraint_stub := '' constraint '' || p_object_type || ''_'' ||
+ p_attribute_name || ''_'';
+
+ if v_datatype.check_expr is not null then
+ v_column_spec := v_column_spec || v_constraint_stub || ''_ck check('' ||
+ p_attribute_name || v_datatype.check_expr || '')'';
+ end if;
+
+ if not p_null_p then
+ v_column_spec := v_column_spec || v_constraint_stub || ''_nn not null'';
+ end if;
+
+ if p_references is not null then
+ v_column_spec := v_column_spec || v_constraint_stub || ''fk references '' ||
+ p_references || '' on delete'';
+ if p_null_p then
+ v_column_spec := v_column_spec || '' set null'';
+ else
+ v_column_spec := v_column_spec || '' cascade'';
+ end if;
+ end if;
+
end if;
+
+ execute ''alter table '' || v_table_name || '' add '' || p_attribute_name || '' '' ||
+ v_column_spec;
- select nextval(''t_acs_attribute_id_seq'') into v_attribute_id;
+ end if;
- insert into acs_attributes
- (attribute_id, object_type, table_name, column_name, attribute_name,
- pretty_name, pretty_plural, sort_order, datatype, default_value,
- min_n_values, max_n_values, storage, static_p)
- values
- (v_attribute_id, create_attribute__object_type,
- create_attribute__table_name, create_attribute__column_name,
- create_attribute__attribute_name, create_attribute__pretty_name,
- create_attribute__pretty_plural, v_sort_order,
- create_attribute__datatype, create_attribute__default_value,
- create_attribute__min_n_values, create_attribute__max_n_values,
- create_attribute__storage, create_attribute__static_p);
+ return v_attribute_id;
- return v_attribute_id;
-
end;' language 'plpgsql';
+create or replace function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean)
+returns integer as '
+declare
+ p_object_type alias for $1;
+ p_attribute_name alias for $2;
+ p_datatype alias for $3;
+ p_pretty_name alias for $4;
+ p_pretty_plural alias for $5; -- default null
+ p_table_name alias for $6; -- default null
+ p_column_name alias for $7; -- default null
+ p_default_value alias for $8; -- default null
+ p_min_n_values alias for $9; -- default 1
+ p_max_n_values alias for $10; -- default 1
+ p_sort_order alias for $11; -- default null
+ p_storage alias for $12; -- default ''type_specific''
+ p_static_p alias for $13; -- default ''f''
+begin
+ return acs_attribute__create_attribute(p_object_type,
+ p_attribute_name, p_datatype, p_pretty_name,
+ p_pretty_plural, p_table_name, p_column_name,
+ p_default_value, p_min_n_values,
+ p_max_n_values, p_sort_order, p_storage,
+ p_static_p, ''f'', null, null, null, null, null, null);
+end;' language 'plpgsql';
+
create or replace function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,integer,varchar,boolean)
returns integer as '
begin
return acs_attribute__create_attribute ($1, $2, $3, $4, $5, $6, $7, cast ($8 as varchar), $9, $10, $11, $12, $13);
end;' language 'plpgsql';
-- procedure drop_attribute
-create function acs_attribute__drop_attribute (varchar,varchar)
+select define_function_args('acs_attribute__drop_attribute','object_type,attribute_name,drop_column_p;f');
+
+create or replace function acs_attribute__drop_attribute (varchar,varchar,boolean)
returns integer as '
declare
- drop_attribute__object_type alias for $1;
- drop_attribute__attribute_name alias for $2;
+ p_object_type alias for $1;
+ p_attribute_name alias for $2;
+ p_drop_column_p alias for $3;
+ v_table_name acs_object_types.table_name%TYPE;
begin
- -- first remove possible values for the enumeration
- delete from acs_enum_values
- where attribute_id in (select a.attribute_id
- from acs_attributes a
- where a.object_type = drop_attribute__object_type
- and a.attribute_name = drop_attribute__attribute_name);
- delete from acs_attributes
- where object_type = drop_attribute__object_type
- and attribute_name = drop_attribute__attribute_name;
+ -- Check that attribute exists and simultaneously grab the type''s table name
+ select t.table_name into v_table_name
+ from acs_object_types t, acs_attributes a
+ where a.object_type = p_object_type
+ and a.attribute_name = p_attribute_name
+ and t.object_type = p_object_type;
+
+ if not found then
+ raise exception ''Attribute %:% does not exist'', p_object_type, p_attribute_name;
+ end if;
- return 0;
+ -- first remove possible values for the enumeration
+ delete from acs_enum_values
+ where attribute_id in (select a.attribute_id
+ from acs_attributes a
+ where a.object_type = p_object_type
+ and a.attribute_name = p_attribute_name);
+
+ -- Drop the table if one were specified for the type and we''re asked to
+ if p_drop_column_p and v_table_name is not null then
+ execute ''alter table '' || v_table_name || '' drop column '' ||
+ p_attribute_name || '' cascade'';
+ end if;
+
+ -- Finally, get rid of the attribute
+ delete from acs_attributes
+ where object_type = p_object_type
+ and attribute_name = p_attribute_name;
+
+ return 0;
end;' language 'plpgsql';
+create or replace function acs_attribute__drop_attribute (varchar,varchar)
+returns integer as '
+begin
+ return acs_attribute__drop_attribute($1, $2, ''f'');
+end;' language 'plpgsql';
+
+
select define_function_args('acs_attribute__add_description','object_type,attribute_name,description_key,description');
-- procedure add_description
create function acs_attribute__add_description (varchar,varchar,varchar,text)
@@ -769,6 +1089,20 @@
return 0;
end;' language 'plpgsql';
+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:SS'''')'';
+end;' language 'plpgsql';
-- show errors
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 -N -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');