Index: openacs.org-dev/www/bboard-migr/#index.html#
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/#index.html#,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/#index.html# 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,57 @@
+
+OACS 3.x -> 4 Bboard Migration scripts.
+
+OACS 3.x -> 4 Bboard Migration scripts.
+
+These scripts will transfer the entire contents of a 3.x bboard
+into a 4.x forums package instance. The first script will also
+minimally transfer users (just name-email-password), and is smart
+enough to not overwrite or duplicate users already registered on the
+new site. (A full user-migration script would be handy -- any volunteers?)
+They have been moderately tested, and I know Don has been using them to
+test (and fix!) 4.x forum performance issues. They were done mainly
+for internal use, however, and thus the output is not, er, polished.
+
+
Some notes:
+
+- The scripts can be run repeatedly to update already-migrated
+boards. They avoid duplication by keeping track of their work in
+
migr_*_map tables, so there is some sql to be loaded
+before running them.
+
+ - They assume they are being run under OACS4, with one pool named
+"acs32" pointing at the 3.x DB. This pool also has to be excluded
+from use by 4.x, see
+this thread
+
+
- They put all messages into a single forums instance, currently
+through a hardcoded package_id.
+
+
- They don't write out progress along the way, but they do
+everything inside a db_transaction and if there's an error, they'll
+tell you how far they got, without actually commiting the changes.
+They provide two forms of output: first, in the order performed, then
+sorted by the action taken (i.e. whether or not they're already mgirated)
+
+
- Logically, they have to be run in the order listed (users, forums,
+categories, messages, subscriptions)
+
+
- Remaining work: i didn't spend the time to write the code
+that would reliably regsub for bboard links inside the messages
+themselves. It's pretty neccessary, but could also be done ex post
+facto.
+
+
+ Files:
+
+And here are all the files:
+bboard-migr.tar
Index: openacs.org-dev/www/bboard-migr/bboard-migr.tar
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/bboard-migr.tar,v
diff -u
Binary files differ
Index: openacs.org-dev/www/bboard-migr/cat-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/cat-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/cat-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,90 @@
+#hardcode if you're not in the correct package instance...
+
+#set package_id [ad_conn package_id]
+set package_id 3189
+
+#hardcode regexp for link replacement
+#... later
+
+set olddb [ns_db gethandle acs32]
+
+set n_tried_old 0
+
+set selection [ns_db select $olddb "
+select distinct category, topic_id from bboard
+where bboard.category is not null
+order by topic_id, category
+"]
+
+set forum_mapping_sql "
+select new_forum_id from migr_forum_map
+where old_forum_id = :topic_id
+ and package_id = :package_id"
+
+
+set mapped_category_sql "
+select old_category as old_map_id,
+new_category_id as new_map_id
+from migr_category_map where old_category=:category"
+
+set map_category_sql "
+insert into migr_category_map
+(old_category, new_category_id, package_id, new_forum_id)
+values
+(:category, :new_id, :package_id, :new_forum_id)
+"
+
+
+
+set nil [set new [list]]
+set output ""
+
+db_transaction {
+while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+
+ #also lookup forum mapping
+ db_1row q $forum_mapping_sql
+
+ incr n_tried_old
+
+ if [db_0or1row r $mapped_category_sql] {
+ ##already mapped as well, sets new_map_id, old_map_id
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+ set new_created_id [bboard_category_new -short_name $category \
+ -forum_id $new_forum_id]
+
+ set new_id $new_created_id
+ set action "new"
+ db_dml q $map_category_sql
+ }
+
+ set html "
+ $category => $new_id (forum $new_forum_id)
+
"
+
+ lappend $action $html
+
+ append output "$action: $html"
+
+}
+} on_error {
+ set output "Error:
$errmsg
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonicategory
+ Migrating Categorys
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- categorys already created & mapped
[join $nil]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+
Index: openacs.org-dev/www/bboard-migr/drop-maps.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/drop-maps.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/drop-maps.sql 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,4 @@
+drop table migr_user_map;
+drop table migr_forum_map;
+drop table migr_category_map;
+drop table migr_message_map;
Index: openacs.org-dev/www/bboard-migr/forum-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/forum-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/forum-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,71 @@
+#hardcode if you're not in the correct package instance...
+
+#set package_id [ad_conn package_id]
+set package_id 3189
+
+set olddb [ns_db gethandle acs32]
+
+set n_tried_old 0
+
+set selection [ns_db select $olddb "
+select * from bboard_topics order by topic_id"]
+
+
+set mapped_forum_sql "
+select old_forum_id as old_map_id,
+new_forum_id as new_map_id
+from migr_forum_map where old_forum_id=:topic_id and package_id=:package_id"
+
+set map_insert_sql "insert into migr_forum_map
+(old_forum_id, new_forum_id, package_id)
+values (:topic_id, :new_id, :package_id)"
+
+
+set nil [set new [list]]
+set output ""
+
+db_transaction {
+while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+
+ incr n_tried_old
+ if [db_0or1row tt $mapped_forum_sql] {
+ ##already created, sets $new_map_id, $short_name
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+ #make a new one!
+ set new_created_id [bboard_forum_new -short_name $topic \
+ -charter $blather -bboard_id $package_id]
+
+ set new_id $new_created_id
+ set action "new"
+ db_dml q $map_insert_sql
+ }
+
+ set html "
+ $topic_id => $new_id:
+ $topic
"
+
+ lappend $action $html
+ append output "$action: $html"
+
+}
+} on_error {
+ set output "Error:
$errmsg
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonicforum
+ Migrating Forums
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- forums already created & mapped
[join $nil]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+
Index: openacs.org-dev/www/bboard-migr/index.html
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/index.html,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/index.html 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,57 @@
+
+OACS 3.x -> 4 Bboard Migration scripts.
+
+OACS 3.x -> 4 Bboard Migration scripts.
+
+These scripts will transfer the entire contents of a 3.x bboard
+into a 4.x forums package instance. The first script will also
+minimally transfer users (just name-email-password), and is smart
+enough to not overwrite or duplicate users already registered on the
+new site. (A full user-migration script would be handy -- any volunteers?)
+They have been moderately tested, and I know Don has been using them to
+test (and fix!) 4.x forum performance issues. They were done mainly
+for internal use, however, and thus the output is not, er, polished.
+
+
Some notes:
+
+- The scripts can be run repeatedly to update already-migrated
+boards. They avoid duplication by keeping track of their work in
+
migr_*_map tables, so there is some sql to be loaded
+before running them.
+
+ - They assume they are being run under OACS4, with one pool named
+"acs32" pointing at the 3.x DB. This pool also has to be excluded
+from use by 4.x, see
+this thread
+
+
- They put all messages into a single forums instance, currently
+through a hardcoded package_id.
+
+
- They don't write out progress along the way, but they do
+everything inside a db_transaction and if there's an error, they'll
+tell you how far they got, without actually commiting the changes.
+They provide two forms of output: first, in the order performed, then
+sorted by the action taken (i.e. whether or not they're already mgirated)
+
+
- Logically, they have to be run in the order listed (users, forums,
+categories, messages, subscriptions)
+
+
- Remaining work: i didn't spend the time to write the code
+that would reliably regsub for bboard links inside the messages
+themselves. It's pretty neccessary, but could also be done ex post
+facto.
+
+
+ Files:
+
+And here are all the files:
+bboard-migr.tar
Index: openacs.org-dev/www/bboard-migr/index.vuh
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/index.vuh,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/index.vuh 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,89 @@
+ad_page_contract {
+ Redirects requests that are addressed to (Open)ACS 3 style url's
+ of bboard postings to their (Open)ACS 4 equivalents. Requires the
+ presence of the mappings tables that are created by the bboard-migr
+ scripts.
+
+ Place this under /www/bboard/index.vuh and adjust the mount point
+ of the new bboard package below.
+
+ @author Tilmann Singer (tils@tils.net)
+} {
+ { msg_id "" }
+ { topic_id "" }
+}
+
+# Mount point of new forum to redirect to, no trailing slash.
+set new_mount_point "/forums"
+
+set target [lindex [ad_conn urlv] end]
+
+regsub {\.tcl$} $target {} target
+
+switch $target {
+ q-and-a-fetch-msg {
+ # a simple message
+
+ if { [db_0or1row get_new_ids {
+ select mm.new_message_id, fm.forum_id from migr_message_map mm
+ join bboard_forum_message_map fm
+ on mm.new_message_id=fm.message_id
+ where mm.old_message_id=:msg_id
+ }] } {
+ # Found a mapping, return new link.
+
+ set new_link "[ad_conn location]$new_mount_point/message?message_id=$new_message_id&forum_id=$forum_id"
+ ns_set put [ns_conn outputheaders] Location $new_link
+ ns_return 301 text/html "
+Redirection
+
+Redirection
+The requested URL has moved here permanently.
+
+
+"
+ return
+ }
+ }
+
+ q-and-a {
+ # a topic
+ if { [db_0or1row get_new_forum_id {
+ select new_forum_id from migr_forum_map
+ where old_forum_id=:topic_id
+ }] } {
+ set new_link "[ad_conn location]$new_mount_point/forum?forum_id=$new_forum_id"
+ ns_set put [ns_conn outputheaders] Location $new_link
+ ns_return 301 text/html "
+Redirection
+
+Redirection
+The requested URL has moved here permanently.
+
+
+"
+ return
+ }
+ }
+
+ default {
+ ns_log notice "A request for [ad_conn url]?[ad_conn query] occured - you might want to add it to /bboard/index.vuh"
+ }
+}
+
+
+# No success above.
+ns_return 404 text/html "
+
+Not Found
+
+Not Found
+You seem to refer to an old bboard posting that has been moved somewhere
+else or deleted from this server. Sorry.
+
+
+You might want to start searching from the start page.
+
+
+"
+
Index: openacs.org-dev/www/bboard-migr/links-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/links-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/links-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,139 @@
+# Search for occurences of links that point to other threads
+# in this bboard and replace them with their new form.
+#
+# @author Tilmann Singer (tils@tils.net)
+
+# The regular expression that matches the old link. Customize this to
+# your needs. Must match the full link, and the old msg_id is expected
+# to be in the first parenthesis submatch.
+set old_link_regex {http://openacs.org/bboard/q-and-a-fetch-msg\.tcl\?msg_id=([0-9A-Za-z]+)(&topic(_id)?=[0-9A-Za-z%]*){0,2}}
+
+# Full url and mount point of the new bboard package, without trailing
+# slash.
+set new_bboard_url "http://openacs.org/forums"
+
+
+# Some notes:
+
+# To get some testing material I wget'd a bunch of pages from
+# openacs.org that appear as result to a search on "this
+# thread". topic_id and topic are being used sometimes both, sometimes
+# not at all, and sometimes they even contain each others value. It's
+# important that they are fully matched by the regex. Hopefully the
+# thing above does.
+
+# What it would not match would be something like
+# href="...&topic=OpenACS Testing" because the space would need some
+# alternative regex. Seems that this never occurs anyway.
+
+# There might be links to q-and-a-search that would be nice to replace
+# too.
+
+# Did not find any relative links in the wget'd stuff so they are not
+# yet considered here. If they are added then I think care should be
+# taken so that they are only replaced when inside a
+# clause to minimize the risk of confusing discussion about the bboard
+# code itself.
+
+
+ns_write "HTTP/1.0 200 OK
+Content-type: text/plain
+
+"
+
+ns_write $old_link_regex
+
+ad_script_abort
+return
+
+
+ns_write "
+
+Looping through all messages to substitute links.
+
+"
+
+set ids [db_list ids "select message_id from bboard_forum_message_map"]
+#set ids [list 4590]
+set test_counter 0
+
+foreach id $ids {
+
+ # loop through possibly several revisions of one message
+ db_foreach get_message {
+ select r.revision_id, r.content from cr_revisions r
+ where item_id=:id
+ } {
+ ns_write "-- checking $id\n"
+
+ # split content into a list where each element is either a
+ # matching link or a text blurb in between.
+ set content_list [list]
+ set to_search $content
+ while { [regexp -indices $old_link_regex $to_search match] } {
+ lappend content_list [string range $to_search 0 [expr [lindex $match 0] - 1]]
+ lappend content_list [string range $to_search [lindex $match 0] [lindex $match 1]]
+ set to_search [string range $to_search [expr [lindex $match 1] +1] end]
+ }
+
+ if { [llength $content_list] == 0 } {
+ # nothing to replace in this revision
+ continue
+ }
+
+ # append the tail
+ lappend content_list $to_search
+
+ set replaced_p 0
+
+ for { set idx 0 } { $idx < [llength $content_list] } { incr idx } {
+ set element [lindex $content_list $idx]
+
+ if { [regexp $old_link_regex $element match old_id] } {
+ # this is a link
+
+ if { [db_0or1row get_ids_for_new_link { select m.new_message_id, f.forum_id
+ from migr_message_map m
+ join bboard_forum_message_map f
+ on m.new_message_id=f.message_id
+ where old_message_id=:old_id
+ }] } {
+ # we found a mapping
+ set new_link "$new_bboard_url/message?message_id=$new_message_id&forum_id=$forum_id"
+
+ set content_list [lreplace $content_list $idx $idx $new_link]
+
+ ns_write "new message_id: $id
+revision_id: $revision_id
+old_id: $old_id
+new forum_id: $forum_id
+new link: $new_link
+"
+ set replaced_p 1
+
+ } else {
+ # no mapping found
+ ns_write "warning: no mapping entry in migr_message_map for old id: $old_id\n"
+ }
+
+
+ incr test_counter
+ if { $test_counter > 100 } {
+ ad_script_abort
+ }
+
+ }
+
+ if { $replaced_p } {
+ set new_content [join $content_list ""]
+ db_dml update_content "update cr_revisions set content=:new_content where revision_id=:revision_id"
+ }
+ }
+ }
+}
+
+ns_write "
+
+
+Done.
+"
Index: openacs.org-dev/www/bboard-migr/maps.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/maps.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/maps.sql 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,29 @@
+
+create table migr_user_map (
+ old_user_id integer unique,
+ new_user_id integer unique,
+ primary key (old_user_id, new_user_id)
+);
+
+create table migr_forum_map (
+ old_forum_id integer,
+ new_forum_id integer unique,
+ package_id integer,
+ primary key (old_forum_id, new_forum_id)
+);
+
+create table migr_category_map (
+ old_category varchar(200),
+ new_category_id integer unique,
+ package_id integer,
+ new_forum_id integer,
+ primary key (old_category, new_category_id)
+);
+
+create table migr_message_map (
+ old_message_id char(6),
+ new_message_id integer unique,
+ package_id integer,
+ primary key (old_message_id, new_message_id)
+);
+
Index: openacs.org-dev/www/bboard-migr/msg-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/msg-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/msg-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,122 @@
+#hardcode if you're not in the correct package instance...
+
+#set package_id [ad_conn package_id]
+set package_id 3189
+
+set olddb [ns_db gethandle acs32]
+
+set n_tried_old 0
+
+set selection [ns_db select $olddb "
+select * from bboard order by msg_id"]
+
+set mapped_message_sql "
+select old_message_id as old_map_id,
+new_message_id as new_map_id
+from migr_message_map where old_message_id=:msg_id"
+
+
+set nil [set new [list]]
+set output ""
+
+db_transaction {
+ while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+ incr n_tried_old
+
+ #topic/forum
+ db_1row q "select new_forum_id from migr_forum_map
+ where old_forum_id = :topic_id and package_id = :package_id"
+
+ if [db_0or1row tt $mapped_message_sql] {
+
+ ##already mapped, sets new_map_id, old_map_id
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+
+ #lookups:
+ #user
+ db_1row q "select new_user_id from migr_user_map
+ where old_user_id = :user_id"
+
+ #thread
+ if {![empty_string_p $refers_to]} {
+ db_1row q "select new_message_id as new_reply_to_id
+ from migr_message_map
+ where old_message_id = :refers_to
+ and package_id = :package_id"
+ } else {
+ set new_reply_to_id ""
+ }
+
+ #mime
+ if {$html_p == "t"} {
+ set mime_type "text/html"
+ } else {
+ set mime_type "text/plain; format=flowed"
+ }
+
+ set new_created_id [bboard_message_new -reply_to $new_reply_to_id \
+ -sender $new_user_id -title $one_line -mime_type $mime_type \
+ -content $message -context_id $new_forum_id \
+ -creation_ip $originating_ip \
+ -sent_date $posting_time
+]
+
+ bboard_message_set_status \
+ -message_id $new_created_id \
+ -forum_id $new_forum_id -status "unmoderated"
+
+ if {![empty_string_p $category]} {
+ db_1row q "select category_id as new_category_id
+ from bboard_categories
+ where short_name = '$category'
+ and forum_id=$new_forum_id"
+
+ db_dml q "insert into bboard_category_message_map
+ (message_id, category_id)
+ values (:new_created_id, :new_category_id)"
+
+ }
+
+
+ db_dml q "insert into migr_message_map
+ (old_message_id, new_message_id, package_id)
+ values
+ ('$msg_id', $new_created_id, $package_id)"
+
+ set new_id $new_created_id
+ set action "new"
+ }
+
+ set html "
+ $msg_id => $new_id (forum $new_forum_id):
+ $one_line
"
+
+ lappend $action $html
+
+ append output "$action: $html"
+ }
+} on_error {
+ set output "Error:
$errmsg
+ $msg_id t $topic_id r $refers_to rid $new_reply_to_id u $user_id p $package_id: $one_line
+ c $category id $new_category_id"
+# : a $action"
+#
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonicmessage
+ Migrating Messages
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- messages already created & mapped
[join $nil]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+
Index: openacs.org-dev/www/bboard-migr/oacs-bboard-migrate-links.tar
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/oacs-bboard-migrate-links.tar,v
diff -u
Binary files differ
Index: openacs.org-dev/www/bboard-migr/subs-forum-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/subs-forum-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/subs-forum-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,89 @@
+#hardcode if you're not in the correct package instance...
+
+#set package_id [ad_conn package_id]
+set package_id 3189
+
+set olddb [ns_db gethandle acs32]
+
+set n_tried_old 0
+
+set selection [ns_db select $olddb "
+select user_id, topic_id from bboard_email_alerts
+order by topic_id"]
+
+set forum_mapping_sql "
+select new_forum_id from migr_forum_map
+where old_forum_id = :topic_id
+ and package_id = :package_id"
+
+set user_mapping_sql "
+select new_user_id from migr_user_map
+where old_user_id = :user_id
+"
+
+
+#note: no migr_map neccessary for subscriptions
+# or even possible -- covered by user and topic map
+
+set mapped_subs_sql "
+select forum_id || '-' || subscriber_id as new_map_id
+ from bboard_forum_subscribers
+where forum_id = :new_forum_id
+and subscriber_id = :new_user_id"
+
+set map_subs_sql "
+insert into bboard_forum_subscribers
+(forum_id, subscriber_id) values
+(:new_forum_id, :new_user_id)"
+
+set nil [set new [list]]
+set output ""
+
+db_transaction {
+while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+
+ #also lookup forum and user mapping
+ db_1row q $forum_mapping_sql
+ db_1row q $user_mapping_sql
+
+ incr n_tried_old
+
+ if [db_0or1row r $mapped_subs_sql] {
+ ##already mapped, sets new_map_id
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+ #it's new; insert it.
+ db_dml q $map_subs_sql
+ set new_id "$new_forum_id-$new_user_id"
+ set action "new"
+ }
+
+ set html "
+ forum-user: $new_id
+
"
+
+ lappend $action $html
+
+ append output "$action: $html"
+
+}
+} on_error {
+ set output "Error:
$errmsg
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonisubscripforum
+ Migrating Forum Subscrtiptions
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- categorys already created & mapped
[join $nil]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+
Index: openacs.org-dev/www/bboard-migr/subs-thread-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/subs-thread-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/subs-thread-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,90 @@
+#hardcode if you're not in the correct package instance...
+
+#set package_id [ad_conn package_id]
+set package_id 3189
+
+set olddb [ns_db gethandle acs32]
+
+set n_tried_old 0
+
+set selection [ns_db select $olddb "
+select user_id, thread_id
+from bboard_thread_email_alerts
+order by thread_id"]
+
+set thread_mapping_sql "
+select new_message_id as new_thread_id from migr_message_map
+where old_message_id = :thread_id
+ and package_id = :package_id"
+
+set user_mapping_sql "
+select new_user_id from migr_user_map
+where old_user_id = :user_id
+"
+
+
+#note: no migr_map neccessary for subscriptions
+# or even possible -- covered by user and topic map
+
+set mapped_subs_sql "
+select thread_id || '-' || subscriber_id as new_map_id
+ from bboard_thread_subscribers
+where thread_id = :new_thread_id
+and subscriber_id = :new_user_id"
+
+set map_subs_sql "
+insert into bboard_thread_subscribers
+(thread_id, subscriber_id) values
+(:new_thread_id, :new_user_id)"
+
+set nil [set new [list]]
+set output ""
+
+db_transaction {
+while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+
+ #also lookup thread and user mapping
+ db_1row q $thread_mapping_sql
+ db_1row q $user_mapping_sql
+
+ incr n_tried_old
+
+ if [db_0or1row r $mapped_subs_sql] {
+ ##already mapped, sets new_map_id
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+ #it's new; insert it.
+ db_dml q $map_subs_sql
+ set new_id "$new_thread_id-$new_user_id"
+ set action "new"
+ }
+
+ set html "
+ thread-user: $new_id
+
"
+
+ lappend $action $html
+
+ append output "$action: $html"
+
+}
+} on_error {
+ set output "Error:
$errmsg
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonisubscripforum
+ Migrating Forum Subscrtiptions
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- categorys already created & mapped
[join $nil]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+
Index: openacs.org-dev/www/bboard-migr/user-migr.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/user-migr.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/user-migr.tcl 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,82 @@
+
+set olddb [ns_db gethandle acs32]
+
+# NOTE: only migrates minimum: email, name, passwd, url
+# for posting users
+
+set selection [ns_db select $olddb "
+ select last_name, first_names, email, password, url,
+ user_id as old_user_id, registration_date
+ from users
+"]
+
+set n_tried_old 0
+
+set new_user_sql "
+select user_id as new_lookup_id, lower(email) from cc_users where lower(email)=lower(:email)"
+
+set mapped_user_sql "
+select old_user_id as old_map_id,
+new_user_id as new_map_id
+from migr_user_map where old_user_id=:old_user_id"
+
+
+set nil [set new [set map [list]]]
+set output ""
+db_transaction {
+while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+
+ incr n_tried_old
+ if [db_0or1row tt $new_user_sql] {
+
+ if [db_0or1row r $mapped_user_sql] {
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+ db_dml q "insert into migr_user_map
+ (old_user_id, new_user_id) values ($old_user_id, $new_lookup_id)"
+ set new_id $new_lookup_id
+ set action "map"
+ }
+ } elseif {[empty_string_p $first_names] || [empty_string_p $last_name] || [empty_string_p $email]} {
+ set action "nil"
+
+ } else {
+ set new_created_id [ad_user_new $email $first_names $last_name $password "" "" $url "t" "approved" "" $registration_date]
+ db_dml q "insert into migr_user_map
+ (old_user_id, new_user_id) values ($old_user_id, $new_created_id)"
+
+# set new_created_id newtest
+
+ set new_id $new_created_id
+ set action "new"
+ }
+
+ lappend $action "
+ $old_user_id => $new_id:
+ $last_name, $first_names $email
"
+
+ append output "$action: $old_user_id => $new_id:
+ $last_name, $first_names $email
"
+}
+} on_error {
+ set output "Error:
$errmsg
+
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonicuserfama
+ Migrating Users
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- users already created & mapped
[join $nil]
+[llength $map] map -- users already created, but unmapped
[join $map]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+
Index: openacs.org-dev/www/bboard-migr/user-migr.tcl~
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/www/bboard-migr/user-migr.tcl~,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs.org-dev/www/bboard-migr/user-migr.tcl~ 10 Jul 2002 19:29:04 -0000 1.1
@@ -0,0 +1,80 @@
+
+set olddb [ns_db gethandle acs32]
+
+# NOTE: only migrates minimum: email, name, passwd, url
+# for posting users
+
+set selection [ns_db select $olddb "
+ select last_name, first_names, email, password, url,
+ user_id as old_user_id
+ from users
+ where user_id in (select distinct user_id from bboard)
+"]
+
+set n_tried_old 0
+
+set new_user_sql "
+select user_id as new_lookup_id, email from cc_users where email=:email"
+
+set mapped_user_sql "
+select old_user_id as old_map_id,
+new_user_id as new_map_id
+from migr_user_map where old_user_id=:old_user_id"
+
+
+set nil [set new [set map [list]]]
+
+db_transaction {
+while { [ns_db getrow $olddb $selection] } {
+ set_variables_after_query
+
+ incr n_tried_old
+ if [db_0or1row tt $new_user_sql] {
+
+ if [db_0or1row r $mapped_user_sql] {
+ set new_id $new_map_id
+ set action "nil"
+ } else {
+ db_dml q "insert into migr_user_map
+ (old_user_id, new_user_id) values ($old_user_id, $new_lookup_id)"
+ set new_id $new_lookup_id
+ set action "map"
+ }
+ } else {
+ set new_created_id [ad_user_new $email $first_names $last_name $password "" "" $url "t" "approved" ""]
+ db_dml q "insert into migr_user_map
+ (old_user_id, new_user_id) values ($old_user_id, $new_created_id)"
+
+# set new_created_id newtest
+
+ set new_id $new_created_id
+ set action "new"
+ }
+
+ lappend $action "
+ $old_user_id => $new_id:
+ $last_name, $first_names $email
"
+
+ append output "$action: $old_user_id => $new_id:
+ $last_name, $first_names $email
"
+}
+} on_error {
+ set output "Error:
$errmsg
+
$output"
+}
+
+ns_db releasehandle $olddb
+
+ns_return 200 text/html "
+Migratonicuserfama
+ Migrating Users
+
Unsorted Output:
$output
Sorted Output:
+[llength $nil] nil -- users already created & mapped
[join $nil]
+[llength $map] map -- users already created, but unmapped
[join $map]
+[llength $new] new -- uncreated and unmapped
[join $new]
+
+"
+
+
+
+