in last 10 minutes
Upgrading ACS to OpenACS
Here are some other webpages to look at:
http://jongriffin.com/static/openacs/acs-conversion/upgrade
http://openacs.org/faq/one-faq?faq_id=43841
http://openacs.org/doc/openacs-3/html/oracle-to-pg-porting.html
http://grumet.net/random/mikes-migration-guide/
Ola's posting on upgrading OpenACS 3 to 4
Using two databases with OpenACS
Sloan's upgrade scripts -- woohoo! Even better, Sloan's scripts that upgraded their 3.4 installation to OpenACS 4.0! (See this link also: thread announcing file upload. This is how I would do things if I could go back and do it again
Note on sequence caching
When you're doing any sort of migration, apparently getting the next sequence values is one of the more expensive operations you'll do. To speed things up considerably, you can cache your sequence values. Both Oracle and Postgres let you do this. (caching sequences in postgres)Upgrading users
What I'm doing is set up a table that contains the old and new user_ids (so I can map the old values to the new values). It's nothing complicated, just a table with the old value and the new value. I call it upgrade-user-map. Then there is a little utility function which you can use to get the new user_id when you're importing the data. set new_user_id [upgrade_user_id $old_user_id] will do it./sql/postgresql/upgrade-user-map-create.sql
-- -- packages/upgrade-user-map-create/sql/postgresql/upgrade-user-map-create.sql -- -- @author jader@bread.com -- @creation-date 2003-03-21 -- @cvs-id $Id: upgrade-user-map-create.sql,v 1.1 2003/03/21 22:49:01 oacs Exp $ -- create table upgrade_user_map ( old_id integer constraint upgrade_user_map_old_id_nn not null, new_id integer constraint upgrade_user_map_new_id_fk references users(user_id) );
/sql/postgresql/upgrade-user-map-drop.sql
drop table upgrade_user_map;
/tcl/upgrade-user-map-procs.tcl
# /packages/upgrade-user-map/tcl/upgrade-user-map-procs.tcl ad_library { Procs for upgrade of users, and for upgrade for ACS 3.4 intranet @author Jade Rubick (jader@bread.com) @creation-date 2003-03-31 @cvs-id $Id$ } ad_proc -public upgrade_user_id { old_user_id } { returns the new user id when given the old } { set user_id [db_string get_new_user_id { SELECT u.user_id FROM users u, upgrade_user_map um WHERE u.user_id = um.new_id and um.old_id = :old_user_id } -default "0"] return $user_id }
Port common functions
Another thing to do to make the transition easier is to port some common functions over from ACS 3.4. They can be marked as deprecated, so that as you work on your packages in the future, you can gradually move to a more pure OpenACS model. I put these inside the upgrade_user_map package, /tcl/upgrade-user-map-procs.tcl file. Then all packages that I port to OpenACS will have a dependency on the upgrade-user-map package. This will make it easy to keep track of which packages depend on these functions, and I can explicitly go through later and remove than dependency. Note that these functions depend on acs-tcl, which I believe is going to be removed from OpenACS eventually. More work later!Here are a list of functions I ported into OpenACS:
- im_header
- im_footer
- im_return_template
Ported functions
im_header
ad_proc -public -deprecated im_header { {-page_title ""} {-page_focus ""} {-context_bar ""} {-extra_stuff_for_document_head ""} } { Shows page headers for a legacy page ported from an ACS 3.4 installation } { return " [ad_header -focus $page_focus $page_title $extra_stuff_for_document_head] <font size=4><b>$page_title</b></font> <br><font size=2>$context_bar</font> <hr> <link rel=\"stylesheet\" href=\"/style.css\" type=\"text/css\"> " }
im_footer
ad_proc -public -deprecated im_footer { } { Shows the footer for the legacy page ported from an ACS 3.4 installation } { return " <hr><font size=-2> Integrated Bakery Resources ©2003 <a href=/register/logout>log out</a> </font>" }
im_return_template
ad_proc -public -deprecated im_return_template { } { Returns a legacy page ported from an ACS 3.4 installation } { uplevel { return " [im_header -page_title [value_if_exists page_title] -page_focus [value_if_exists page_focus] -context_bar [value_if_exists context_bar] -extra_stuff_for_document_head [value_if_exists extra_stuff_for_document_head]] [value_if_exists page_body] [value_if_exists page_content] [im_footer] " } }
Checklist of things to check for in each ported file
- Use new data model
- Add in permissions
Exporting data from ACS 3.4
The approach I use is easy, but maybe not as elegant as you might like. I export each table to a file, and then import that file into Postgres. Another way you could do this is to define an extra database pool. I'm not sure if this would work with a different database, however.I am creating an export script for every table I need from the old Intranet, and an import script for every table in the new Intranet. This will be easiest for my own custom packages -- much more difficult for things like the projects and user_groups tables in ACS 3.4.10. I haven't quite solved that problem yet.
The export scripts looks something like this:
One caveat: this works as long as the amount of data shown on your page does not overwhelm your browser. This is not a very robust solution. You don't have to display everything out to the browser after you've debugged it. Take out the portion with the ns_write inside the db_foreach statement. Also, make sure you use a browser that doesn't timeout within 60 seconds, like the version of Safari I'm currently using.# /packages/packagename/www/upgrade/export.tcl ad_page_contract { page which exports the packagename table @author jader@bread.com @cvs-id $Id$ @creation-date 3/14/03 } { } ns_write "<html><table>" set file_stream [open /tmp/packagename.export w] db_foreach get_brand " ... " { puts $file_stream "@%@%@<oneitem>$oneitemvalue</oneitem>..." ns_write "<tr><td>$oneitemvalue</td>..." } close $file_stream ns_write "</table></html>" ----------
Another thing I did with the export scripts is at the bottom of each script, I redirect to the next export script. Add this before the </html>:
<meta HTTP-EQUIV=\"REFRESH\" CONTENT=\"3;URL=http://path/to/next/upgrade/export\">
Importing into OpenACS
The import script (unfinished) looks like this so far:# /packages/packagename/www/upgrade/import.tcl ad_page_contract { page which imports the packagename table @author jader@bread.com @cvs-id $Id$ @creation-date 3/14/03 } { } set file_stream [open /tmp/packagename.export r] set the_whole_file [read -nonewline $file_stream] close $file_stream set list_of_lines [split $the_whole_file "@%@%@"] ns_write "<html><table border=1 cellspacing=0>" db_dml delete_all "delete from packagename" # determine highest value for sequence set highest_seq_value -1 foreach line $list_of_lines { if {[exists_and_not_null line]} { regexp {<oneitem>(.*)</oneitem>} $line match oneitem ns_write " <tr> <td>$oneitem if {$index_value > $highest_seq_value} { set highest_seq_value $index_value } # get the user_id from a user_map table set user_id [db_string get_user_id xxxxxxx] db_exec_plsql new_packagetablename " select packagename__new(...)" } }
Other references
From Mike Bonnet and Andrew Grummet's migration page, I stole this script for Oracle, which shows you the references to any given table. This really helps in deciding which item to port when:select (select table_name || '.' || column_name from user_cons_columns where constraint_name = uc.constraint_name) || ' references ' || (select table_name || '.' || column_name from user_cons_columns where constraint_name = uc.r_constraint_name) as references from user_constraints uc where constraint_type = 'R' and table_name = upper('&table_name') select (select table_name || '.' || column_name from user_cons_columns where constraint_name = uc.r_constraint_name) || ' is referenced by ' || (select table_name || '.' || column_name from user_cons_columns where constraint_name = uc.constraint_name) as referenced_by from user_constraints uc where constraint_type = 'R' and r_constraint_name in (select constraint_name from user_constraints where table_name = upper('&table_name'));
An earlier version of this document is at: http://openacs.org/forums/message-view?message_id=88229.