Forum OpenACS Q&A: Response to Move content from one ETP instance to another

I had to bulk load some data into that went along with an etp instance each time a new instance was created. I ended up the pgplsql below to accomplish it. You'll probably end up doing something similar, so while this won't solve your problem it might be a useful starting point.

In the example below, the table '_dataload' contains a bunch of messages that I needed to put in etp. For your case, you'll want to collect source data by joining against the cr_revisions table to get at your existing etp instance. My description's pretty brief, but hopefully the code will make sense.

--I need to branch etp_create_page because the original function has no return value
--drop function pr_etp_create_page(integer, varchar, varchar, varchar);
create function pr_etp_create_page(integer, varchar, varchar, varchar)
returns integer as '
declare
  p_package_id alias for $1;
  p_name alias for $2;
  p_title alias for $3;
  p_content_type alias for $4;  -- default null -> use content_revision
  v_item_id integer;
  v_revision_id integer;
  v_content_type varchar;
  v_folder_id integer;
begin
  v_item_id := acs_object__new(null, ''content_item'', now(), null, null, p_package_id);

  v_folder_id := etp_get_folder_id(p_package_id);
  raise notice ''package_id = %, folder_id = %  '', p_package_id,v_folder_id;


  insert into cr_items (
    item_id, parent_id, name, content_type
  ) values (
    v_item_id, v_folder_id, p_name, v_content_type
  );

  -- would like to use p_content_type here, but since there''s 
  -- no table that corresponds to it, we get an error from
  -- the dynamic sql in acs_object__delete.  so just use content_revision.

  v_content_type := ''content_revision'';
  v_revision_id := acs_object__new(null, v_content_type);

  insert into cr_revisions (revision_id, item_id, title, 
                            publish_date, mime_type) 
  values (v_revision_id, v_item_id, p_title, now(), ''text/html'');

  update cr_items set live_revision = v_revision_id
                  where item_id = v_item_id;

  return v_revision_id;
end;
' language 'plpgsql';


-- pr_etp_load_msg: marshall arguments for nag message and make etp call
--drop function pr_etp_loadmsg (int, varchar,varchar, varchar,varchar,varchar,varchar,varchar);
create function pr_etp_loadmsg (int, varchar,varchar, varchar,varchar,varchar,varchar,varchar)
returns integer as '
declare
v_package_id alias for $1;
v_content_type alias for $2;
v_title  alias for $3;
v_body   alias for $4; --not used 
v_name   alias for $5;
v_sender alias for $6;
v_recipient alias for $7;
v_content alias for $8;
v_sender_attr_id int;
v_recpt_attr_id	 int;
v_object_id	 int;
begin

select attribute_id into v_sender_attr_id
from acs_attributes 
where object_type = v_content_type
AND attribute_name = ''sender_role'';

select attribute_id into v_recpt_attr_id
from acs_attributes 
where object_type = v_content_type
AND attribute_name = ''recipient_role'';

v_object_id := pr_etp_create_page(
	  v_package_id,
	  v_name,
          v_title,
	  v_content_type
	);

INSERT INTO acs_attribute_values (object_id, attribute_id, attr_value)
VALUES (v_object_id, v_sender_attr_id, v_sender);

INSERT INTO acs_attribute_values (object_id, attribute_id, attr_value)
VALUES (v_object_id, v_recpt_attr_id, v_recipient);

UPDATE cr_revisions 
SET content = v_content
WHERE revision_id = v_object_id;

--  return v_package_id;
  return v_object_id;
end;' language 'plpgsql';

-- This procedure will create a copy of all messages for a new package instance
--drop function pr_nag_message_init(int);
create function pr_nag_message_init (int)
returns integer as '
declare 
p_journal_group_id alias for $1;
v_package_id int;
v_content_type varchar;
v_revision_id int;
v_title  varchar;
v_name   varchar;
v_sender varchar;
v_recipient varchar;
v_content varchar;
my_rec record;
begin


--
select package_id into v_package_id 
from pr_journals 
where journal_group_id = p_journal_group_id;

select ''nag_letter'' into v_content_type;


	FOR my_rec  IN select subject, num, sender,  recip, content	
	     from _dataload 
	LOOP

	v_revision_id := pr_etp_loadmsg( 
	v_package_id,  
	v_content_type,	       
	my_rec.subject,
	null,
	my_rec.num,
	my_rec.sender,
	my_rec.recip,
	my_rec.content);

	raise notice ''created revision %  of type % '', v_revision_id, v_content_type;
	END LOOP;


  return 1;
end;' language 'plpgsql';

Also, thanks for the W32 AOLServer binary... I'm one of the few who develops (but not yet deploys) on that platform.