View · Index
No registered users in community rubick
in last 10 minutes

Upgrading ACS to OpenACS

This page describes how I am upgrading an ACS 3.4.10 Oracle installation to a OpenACS 4.6.2 Postgres installation.

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:

# /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>"


----------
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.

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.

 
 
References to this Page: en:index.html