3 # GForge AS to FusionForge database migration script
4 # Copyright 2009, Roland Mas
6 # This file is part of FusionForge.
8 # FusionForge is free software; you can redistribute it and/or modify
9 # it under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 2 of the License, or
11 # (at your option) any later version.
13 # FusionForge is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 # General Public License for more details.
18 # You should have received a copy of the GNU General Public License along
19 # with FusionForge; if not, write to the Free Software Foundation, Inc.,
20 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
25 # 1. Rename the GForge AS database to "gfas"
26 # 2. Create the initial FusionForge database as "gforge"
27 # 3. Store a copy of the /var/lib/gforge/filesystem as /tmp/filesystem
28 # 4. export DB_PW=foobar (the password of the databases)
29 # 5. run migrate-from-gforge-as.pl
30 # In case something breaks, to get back to step 2:
31 # su - postgres -c 'dropdb gforge'
32 # /usr/share/gforge/bin/install-db.sh configure
33 # 6. If no error appears, uncomment the last line of this script and re-run it
35 # This script isn't complete, but it migrates the most important data.
36 # Feel free to adapt to your needs.
44 require "/usr/share/gforge/lib/sqlhelper.pm" ;
46 use vars qw/$dbhAS $dbhFF $map @arrayAS $sthAS $sthFF/ ;
48 $dbhAS = DBI->connect("DBI:Pg:dbname=gfas;host=localhost","gforge","$ENV{DB_PW}") ;
49 $dbhFF = DBI->connect("DBI:Pg:dbname=gforge;host=localhost","gforge","$ENV{DB_PW}") ;
53 sub migrate_with_mapping ( $$$;$ ) {
57 my $where = shift || "" ;
59 my @scols = keys %$mapping ;
61 my $sql1 = "SELECT " . join (", ", @scols) . " FROM $tsrc $where" ;
62 my $sth1 = $dbhAS->prepare ($sql1) ;
63 # print STDERR Dumper $sql1 ;
65 my $sql2 = "INSERT INTO $tdest (" . join (", ", map { $mapping->{$_} } @scols)
66 . ") VALUES (" . join (", ", map { "?" } @scols) . ")" ;
67 my $sth2 = $dbhFF->prepare ($sql2) ;
68 # print STDERR Dumper $sql2 ;
71 while (my @arr = $sth1->fetchrow_array) {
72 unless ($sth2->execute (@arr)) {
73 print STDERR "$sql2\n" ;
74 print STDERR Dumper \@arr ;
78 $sth1->finish ; $sth2->finish ;
85 'user_id' => 'user_id',
86 'unix_name' => 'user_name',
89 'password_md5' => 'user_pw',
90 'password_crypt' => 'unix_pw',
91 'extract (epoch from create_date)::integer' => 'add_date',
92 'confirm_hash' => 'confirm_hash',
93 'email_new' => 'email_new',
94 'timezone' => 'timezone',
95 "address" => 'address',
96 "address2" => 'address2',
100 "firstname" => 'firstname',
101 "lastname" => 'lastname',
102 "firstname || ' ' || lastname" => 'realname',
104 'language_id' => 'language',
106 print STDERR "Migrating users\n" ;
107 migrate_with_mapping ('public.user', 'users', $map, "where unix_name not in ('admin', 'None')")
113 print STDERR "Updating users\n" ;
114 $dbhFF->do ("update users set status='A' where status='1'") ;
115 $dbhFF->do ("update users set status='N' where status='2'") ;
116 $dbhFF->do ("update users set status='P' where status='0'") ;
118 $dbhFF->do ("update users set language = 23 where language = 4") ;
119 $dbhFF->do ("update users set language = 4 where language = 2") ;
120 $dbhFF->do ("update users set language = 2 where language = 9") ;
121 $dbhFF->do ("update users set language = 22 where language = 5") ;
122 $dbhFF->do ("update users set language = 11 where language = 6") ;
123 $dbhFF->do ("update users set language = 6 where language = 8") ;
124 $dbhFF->do ("update users set language = 8 where language = 7") ;
125 $dbhFF->do ("update users set language = 7 where language = 3") ;
127 foreach my $i (qw/address address2 title firstname lastname realname/) {
128 $dbhFF->do ("update users set $i=convert_from (convert (convert_to ($i, 'UTF8'), 'UTF8', 'ISO-8859-9'), 'UTF8') where $i LIKE '%Ã%'") or die $i ;
131 $sthAS = $dbhAS->prepare ("select user_unix.user_id, unix_shell.path from user_unix, unix_shell where user_unix.unix_shell_id = unix_shell.unix_shell_id") ;
132 $sthFF = $dbhFF->prepare ("update users set unix_status='A', shell=? where user_id=? and status='A'") ;
134 while (@arrayAS = $sthAS->fetchrow_array) {
135 my $uid = $arrayAS[0] ;
136 my $shell = $arrayAS[1] ;
137 if ($shell eq '/bin/cvssh.pl') { $shell = '/bin/cvssh' ; }
138 $sthFF->execute ($shell, $uid) ;
145 'user_id' => 'user_id',
146 'preference_name' => 'preference_name',
147 'preference_value' => 'preference_value',
148 'extract (epoch from set_date)::integer' => 'set_date',
150 print STDERR "Migrating user preferences\n" ;
151 migrate_with_mapping ('user_preference', 'user_preferences', $map) ;
154 # First need to get rid of the template project
155 $dbhFF->do ("delete from groups where group_id = 5") ;
156 $dbhFF->do ("delete from forum_group_list where group_id = 5") ;
159 'project_id' => 'group_id',
160 "project_name" => 'group_name',
161 'unix_name' => 'unix_group_name',
162 'homepage_url' => 'homepage',
163 'is_public' => 'is_public',
164 'status' => 'status',
165 "substr (description, 0, 255)" => 'short_description',
166 "register_purpose" => 'register_purpose',
167 "register_license_other" => 'license_other',
168 'extract (epoch from create_date)::integer' => 'register_time',
170 print STDERR "Migrating groups\n" ;
171 migrate_with_mapping ('project', 'groups', $map, "where project_id > 4")
177 print STDERR "Updating groups\n" ;
178 $dbhFF->do ("update groups set status='A' where status='1'") ;
179 $dbhFF->do ("update groups set status='H' where status='3'") ;
183 'tracker' => 'tracker',
184 'docman' => 'docman',
192 $sthAS = $dbhAS->prepare ("select project_plugin.project_id, count (plugin.plugin_id) from plugin, project_plugin where project_plugin.plugin_id = plugin.plugin_id and plugin.plugin_name=? group by project_plugin.project_id") ;
193 foreach my $i (keys %$map) {
194 $dbhFF->do ("update groups set use_$map->{$i} = 0") ;
196 $sthAS->execute ($i) ;
197 $sthFF = $dbhFF->prepare ("update groups set use_$map->{$i} = ? where group_id = ?") ;
198 while (@arrayAS = $sthAS->fetchrow_array) {
199 my $project_id = $arrayAS[0] ;
200 my $count = $arrayAS[1] ;
201 $sthFF->execute ($count, $project_id) ;
207 foreach my $i (qw/short_description group_name register_purpose license_other/) {
208 $dbhFF->do ("update groups set $i=convert_from (convert (convert_to ($i, 'UTF8'), 'UTF8', 'ISO-8859-9'), 'UTF8') where $i LIKE '%Ã%'") ;
211 ### Group memberships for users
213 'role_id' => 'role_id',
214 'project_id' => 'group_id',
215 'role_name' => 'role_name',
217 print STDERR "Migrating roles\n" ;
218 migrate_with_mapping ('role', 'role', $map, "where project_id > 4")
225 'user_project.user_id' => 'user_id',
226 'user_project.project_id' => 'group_id',
227 'user_project_role.role_id' => 'role_id',
229 print STDERR "Migrating group memberships\n" ;
230 migrate_with_mapping ('user_project, user_project_role', 'user_group', $map, "where user_project.project_id > 4 and user_project.user_project_id = user_project_role.user_project_id")
236 print STDERR "Updating siteadmin permissions\n" ;
237 my $siteadmin_roleid = 1 ;
238 $sthFF = $dbhFF->prepare ("select role_id from role where group_id=1 and role_name='Admin'") ;
240 if (@arrayFF = $sthFF->fetchrow_array) {
241 $siteadmin_roleid = $arrayFF[0] ;
245 $sthFF = $dbhFF->prepare ("insert into user_group (user_id, group_id, role_id) values (?, 1, ?)") ;
246 $sthAS = $dbhAS->prepare ("select user_id from site_admin where user_id != 101") ;
248 while (@arrayAS = $sthAS->fetchrow_array) {
249 my $uid = $arrayAS[0] ;
250 $sthFF->execute ($uid, $siteadmin_roleid) ;
257 'role_id' => 'role_id',
258 'section' => 'section_name',
259 'ref_id' => 'ref_id',
262 print STDERR "Migrating role settings\n" ;
263 migrate_with_mapping ('role_setting', 'role_setting', $map, "where role_id in (select role_id from role where project_id > 4)")
268 $dbhFF->do ("update role_setting set value='A' where value='1' and section_name='projectadmin'") ;
269 $dbhFF->do ("update user_group set admin_flags=(select value from role_setting where role_setting.role_id = user_group.role_id and section_name='projectadmin')") ;
271 ### Group join requests
273 'project_id' => 'group_id',
274 'user_id' => 'user_id',
275 "comments" => 'comments',
276 'extract (epoch from request_date)::integer' => 'request_date',
278 print STDERR "Migrating group join requests\n" ;
279 migrate_with_mapping ('project_join_request', 'group_join_request', $map)
285 ### Not migrating trove map categories (default values are identical), only mappings
287 'trove_link.ref_id' => 'group_id',
288 'trove_link.trove_category_id' => 'trove_cat_id',
289 'trove_category.root_trove_category_id' => 'trove_cat_root',
291 print STDERR "Migrating trove categorisation\n" ;
292 migrate_with_mapping ('trove_link, trove_category', 'trove_group_link', $map, "where trove_link.trove_category_id = trove_category.trove_category_id and trove_link.section = 'project'")
300 'forum_id' => 'group_forum_id',
301 'forum_name' => 'forum_name',
302 'is_public' => 'is_public',
303 'description' => 'description',
304 'send_all_posts_to' => 'send_all_posts_to',
305 'moderation_level' => 'moderation_level',
306 'ref_id' => 'group_id',
308 print STDERR "Migrating forums\n" ;
309 migrate_with_mapping ('forum', 'forum_group_list', $map, "where section = 'project'")
316 'forum_message.forum_message_id' => 'msg_id',
317 'forum_message.forum_thread_id' => 'thread_id',
318 'forum_message.created_by' => 'posted_by',
319 'forum_message.subject' => 'subject',
320 'forum_message.body' => 'body',
321 'extract (epoch from forum_message.post_date)::integer' => 'post_date',
322 'forum_message.parent_forum_message_id' => 'is_followup_to',
323 'forum_thread.forum_id' => 'group_forum_id',
324 'extract (epoch from forum_thread.most_recent_date)::integer' => 'most_recent_date',
326 print STDERR "Migrating forum messages\n" ;
327 migrate_with_mapping ('forum_message, forum_thread', 'forum', $map, "where forum_message.is_approved = 't' and forum_message.forum_thread_id = forum_thread.forum_thread_id")
332 migrate_with_mapping ('forum_message, forum_thread', 'forum_pending_messages', $map, "where forum_message.is_approved = 'f' and forum_message.forum_thread_id = forum_thread.forum_thread_id")
338 ### File release system
340 'frs_package_id' => 'package_id',
341 'project_id' => 'group_id',
342 'package_name' => 'name',
343 'status_id' => 'status_id',
344 'is_public' => 'is_public',
346 print STDERR "Migrating files\n" ;
347 migrate_with_mapping ('frs_package', 'frs_package', $map)
354 'frs_release_id' => 'release_id',
355 'frs_package_id' => 'package_id',
356 'release_name' => 'name',
357 'release_notes' => 'notes',
358 'changes' => 'changes',
359 'status_id' => 'status_id',
360 'preformatted' => 'preformatted',
361 'extract (epoch from release_date)::integer' => 'release_date',
362 'released_by' => 'released_by',
364 migrate_with_mapping ('frs_release', 'frs_release', $map, "where status_id != 0")
370 $sthAS = $dbhAS->prepare ("
371 select filesystem.file_name_safe, filesystem.ref_id,
372 filesystem.file_size, filesystem.file_type, filesystem.posted_by,
373 filesystem.download_count, extract (epoch from
374 frs_release.release_date)::integer, frs_release.release_name,
375 frs_package.package_name, project.unix_name, filesystem.filesystem_id
377 from filesystem, frs_release, frs_package, project
379 where filesystem.section = 'frsrelease'
380 and filesystem.ref_id = frs_release.frs_release_id
381 and frs_release.frs_package_id = frs_package.frs_package_id
382 and frs_package.project_id = project.project_id
383 and frs_release.status_id != 0") ;
385 $sthFF = $dbhFF->prepare ("insert into frs_file (filename, release_id, type_id, file_size, release_time, post_date, processor_id) values (?, ?, ?, ?, ?, ?, ?)") ;
387 while (@arrayAS = $sthAS->fetchrow_array) {
388 my $filename = $arrayAS[0] ;
389 my $releaseid = $arrayAS[1] ;
390 my $filesize = $arrayAS[2] ;
391 my $filetype = $arrayAS[3] ;
392 my $postedby = $arrayAS[4] ;
393 my $downloadcount = $arrayAS[5] ;
394 my $releasedate = $arrayAS[6] ;
395 my $releasename = $arrayAS[7] ;
396 my $packagename = $arrayAS[8] ;
397 my $projectname = $arrayAS[9] ;
398 my $fsid = $arrayAS[10] ;
401 'application/binary' => 9999,
402 'application/gzip' => 3110,
403 'application/java-archive' => 5900,
404 'application/octet-stream' => 9999,
405 'application/ogg' => 9999,
406 'application/pdf' => 8300,
407 'application/x-compressed-tar' => 5900,
408 'application/x-gtar' => 5900,
409 'application/x-gzip' => 3110,
410 'application/x-java-archive' => 5900,
411 'application/x-msdos-program' => 9999,
412 'application/x-zip-compressed' => 3000,
413 'application/zip' => 3000,
416 my $typeid = $mimemap->{$filetype} ;
418 $packagename =~ s/[^a-zA-Z0-9_.-]//g ;
419 $releasename =~ s/[^a-zA-Z0-9_.-]//g ;
421 my $destdir = "/var/lib/gforge/download/$projectname/$packagename/$releasename" ;
422 my $destfile = "$destdir/$filename" ;
424 my $srcdir = "/tmp/filesystem/frsrelease/" . join ('/', split ('', sprintf ("%03d", substr ($fsid, 0, 3)))) . "/$fsid" ;
425 my $srcfile = "$srcdir/$filename" ;
427 # print STDERR "Copying $srcfile to $destfile\n" ;
429 system "mkdir -p $destdir" ;
430 system "touch $destfile" ; # Need to actually put the contents there...
433 $sthFF->execute ($filename, $releaseid, $typeid, $filesize, $releasedate, $releasedate, 8000) ;
440 'docman_folder_id' => 'doc_group',
441 'project_id' => 'group_id',
442 'folder_name' => 'groupname',
443 'parent_folder_id' => 'parent_doc_group',
445 print STDERR "Migrating docman\n" ;
446 migrate_with_mapping ('docman_folder', 'doc_groups', $map)
454 $sthAS = $dbhAS->prepare ("
455 select filesystem.file_name_safe, filesystem.filesystem_id,
456 extract (epoch from docman_file_version.create_date)::integer,
457 docman_file_version.created_by, docman_file.docman_folder_id,
458 filesystem.file_type, filesystem.file_size, docman_folder.project_id,
459 docman_folder.is_public
461 from docman_file, docman_file_version, docman_folder, filesystem
463 where filesystem.section='docmanfileversion'
464 and filesystem.ref_id = docman_file_version.docman_file_version_id
465 and docman_file_version.docman_file_id = docman_file.docman_file_id
466 and docman_file.docman_folder_id = docman_folder.docman_folder_id
467 and filesystem.file_type != 'URL'
469 $sthFF = $dbhFF->prepare ("insert into doc_data (doc_group, description, title, data, updatedate, createdate, created_by, filename, filetype, group_id, filesize, stateid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") ;
471 while (@arrayAS = $sthAS->fetchrow_array) {
472 my $filename = $arrayAS[0] ;
473 my $fsid = $arrayAS[1] ;
474 my $createdate = $arrayAS[2] ;
475 my $createdby = $arrayAS[3] ;
476 my $docgroup = $arrayAS[4] ;
477 my $filetype = $arrayAS[5] ;
478 my $size = $arrayAS[6] ;
479 my $groupid = $arrayAS[7] ;
480 my $ispublic = $arrayAS[8] ;
482 my $srcdir = "/tmp/filesystem/docmanfileversion/" . join ('/', split ('', sprintf ("%03d", substr ($fsid, 0, 3)))) . "/$fsid" ;
483 my $srcfile = "$srcdir/$filename" ;
485 # print STDERR "Copying $srcfile to database\n" ;
488 # while (my $l = <F>) {
493 $data = encode_base64 ($data) ;
495 my $stateid = $ispublic ? 1 : 5 ;
497 $sthFF->execute ($docgroup, $filename, $filename, $data, $createdate, $createdate, $createdby, $filename, $filetype, $groupid, $size, $stateid) ;
504 'project_id' => 'group_id',
505 'list_name' => 'list_name',
506 'is_public' => 'is_public',
507 'list_password' => 'password',
508 'created_by' => 'list_admin',
509 'status' => 'status',
510 'list_description' => 'description',
512 print STDERR "Migrating mailing lists\n" ;
513 migrate_with_mapping ('mailman', 'mail_group_list', $map)
521 'tracker_id' => 'group_artifact_id',
522 'project_id' => 'group_id',
523 'tracker_name' => 'name',
524 'description' => 'description',
525 'is_public' => 'is_public',
526 'not restrict_browse' => 'allow_anon',
527 'email_all_updates' => 'email_all_updates',
528 'email_address' => 'email_address',
529 'due_period' => 'due_period',
530 'submit_instructions' => 'submit_instructions',
531 'browse_instructions' => 'browse_instructions',
534 print STDERR "Migrating trackers\n" ;
535 migrate_with_mapping ('tracker', 'artifact_group_list', $map, 'where datatype=1')
542 'ti.tracker_item_id' => 'artifact_id',
543 'ti.tracker_id' => 'group_artifact_id',
544 'case when ti.status_id = 0 then 2 else ti.status_id end' => 'status_id',
545 'ti.priority' => 'priority',
546 'ti.submitted_by' => 'submitted_by',
547 'extract (epoch from ti.open_date)::integer' => 'open_date',
548 'ti.summary' => 'summary',
549 'ti.details' => 'details',
550 'extract (epoch from ti.last_modified_date)::integer' => 'last_modified_date',
551 'tia.assignee' => 'assigned_to',
552 'case when ti.status_id = 0 then extract (epoch from ti.close_date)::integer else 0 end' => 'close_date'
554 migrate_with_mapping ('tracker_item ti, tracker_item_assignee tia, tracker t', 'artifact', $map, 'where t.datatype=1 and ti.tracker_id=t.tracker_id and tia.tracker_item_id=ti.tracker_item_id and tia.assignee = (select max(assignee) from tracker_item_assignee where tracker_item_id=ti.tracker_item_id)') # An artifact can't be assigned to several users in FusionForge, so we arbitrarily pick the one most recently created
561 'tim.tracker_item_message_id' => 'id',
562 'tim.tracker_item_id' => 'artifact_id',
563 'tim.submitted_by' => 'submitted_by',
564 'extract (epoch from tim.adddate)::integer' => 'adddate',
565 'tim.body' => 'body',
566 '\'\'' => 'from_email',
568 migrate_with_mapping ('tracker_item_message tim, tracker_item ti, tracker t', 'artifact_message', $map, 'where tim.tracker_item_id = ti.tracker_item_id and ti.tracker_id = t.tracker_id and t.datatype = 1')
575 'tef.tracker_extra_field_id' => 'extra_field_id',
576 'tef.tracker_id' => 'group_artifact_id',
577 'tef.field_name' => 'field_name',
578 'tef.field_type' => 'field_type',
579 'tef.attribute1' => 'attribute1',
580 'tef.attribute2' => 'attribute2',
581 'tef.is_required' => 'is_required',
582 'tef.alias' => 'alias',
584 migrate_with_mapping ('tracker_extra_field tef, tracker t', 'artifact_extra_field_list', $map, 'where tef.tracker_id = t.tracker_id and t.datatype = 1 and tef.field_type != 8') # FusionForge doesn't have a concept of "Found/Fixed in revision X"
591 'tefe.element_id' => 'element_id',
592 'tefe.tracker_extra_field_id' => 'extra_field_id',
593 'tefe.element_name' => 'element_name',
594 'tefe.status_id' => 'status_id',
596 migrate_with_mapping ('tracker_extra_field_element tefe, tracker_extra_field tef, tracker t', 'artifact_extra_field_elements', $map, 'where tefe.tracker_extra_field_id = tef.tracker_extra_field_id and tef.tracker_id = t.tracker_id and t.datatype = 1 and tef.field_type != 8') # FusionForge doesn't have a concept of "Found/Fixed in revision X"
603 'tefd.tracker_extra_field_data_id' => 'data_id',
604 'tefd.tracker_item_id' => 'artifact_id',
605 'tefd.field_data' => 'field_data',
606 'tefd.tracker_extra_field_id' => 'extra_field_id',
608 migrate_with_mapping ('tracker_extra_field_data tefd, tracker_extra_field tef, tracker_item ti, tracker t', 'artifact_extra_field_data', $map, 'where tefd.tracker_item_id = ti.tracker_item_id and ti.tracker_id = t.tracker_id and t.datatype = 1 and tefd.tracker_extra_field_id = tef.tracker_extra_field_id and tef.field_type != 8') # FusionForge doesn't have a concept of "Found/Fixed in revision X"
615 'tcr.tracker_canned_response_id' => 'id',
616 'tcr.title' => 'title',
617 'tcr.body' => 'body',
618 'tcr.tracker_id' => 'group_artifact_id',
620 migrate_with_mapping ('tracker_canned_response tcr, tracker t', 'artifact_canned_responses', $map, 'where tcr.tracker_id = t.tracker_id and t.datatype = 1')
627 'tq.tracker_query_id' => 'artifact_query_id',
628 'tq.user_id' => 'user_id',
629 'tq.query_name' => 'query_name',
630 'tq.tracker_id' => 'group_artifact_id',
632 migrate_with_mapping ('tracker_query tq, tracker t', 'artifact_query', $map, 'where tq.tracker_id = t.tracker_id and t.datatype = 1')
639 'tqf.tracker_query_id' => 'artifact_query_id',
640 'tqf.query_field_type' => 'query_field_type',
641 'tqf.query_field_id' => 'query_field_id',
642 'tqf.query_field_values' => 'query_field_values',
644 migrate_with_mapping ('tracker_query_field tqf, tracker_query tq, tracker t', 'artifact_query_fields', $map, 'where tqf.tracker_query_id = tq.tracker_query_id and tq.tracker_id = t.tracker_id and t.datatype = 1')
652 'tracker_id' => 'group_project_id',
653 'project_id' => 'group_id',
654 'tracker_name' => 'project_name',
655 'description' => 'description',
656 'is_public' => 'is_public',
657 'email_address' => 'send_all_posts_to',
659 print STDERR "Migrating task managers\n" ;
660 migrate_with_mapping ('tracker', 'project_group_list', $map, 'where datatype=2')
667 'ti.tracker_item_id' => 'project_task_id',
668 'ti.tracker_id' => 'group_project_id',
669 'case when ti.status_id = 0 then 2 else ti.status_id end' => 'status_id',
670 'ti.priority' => 'priority',
671 'ti.submitted_by' => 'created_by',
672 'extract (epoch from ti.open_date)::integer' => 'start_date',
673 'extract (epoch from ti.close_date)::integer' => 'end_date',
674 'ti.summary' => 'summary',
675 'ti.details' => 'details',
676 'extract (epoch from ti.last_modified_date)::integer' => 'last_modified_date',
677 'ti.parent_id' => 'parent_id',
679 migrate_with_mapping ('tracker_item ti, tracker t', 'project_task', $map, 'where ti.tracker_id = t.tracker_id and t.datatype = 2')
685 $sthAS = $dbhAS->prepare ("select tefd.field_data, ti.tracker_item_id from tracker_extra_field_data tefd, tracker_extra_field tef, tracker_item ti, tracker t where tefd.tracker_item_id = ti.tracker_item_id and ti.tracker_id = t.tracker_id and t.datatype = 2 and tefd.tracker_extra_field_id = tef.tracker_extra_field_id and field_name = 'Estimated Effort (Hours)'") ;
686 $sthFF = $dbhFF->prepare ("update project_task set hours=? where project_task_id=?") ;
688 while (@arrayAS = $sthAS->fetchrow_array) {
689 my $hours = $arrayAS[0] ;
690 my $ptid = $arrayAS[1] ;
691 next unless $hours =~ /^[0-9]+$/ ;
692 $sthFF->execute ($hours, $ptid) ;
697 $sthAS = $dbhAS->prepare ("select tefd.field_data, ti.tracker_item_id from tracker_extra_field_data tefd, tracker_extra_field tef, tracker_item ti, tracker t where tefd.tracker_item_id = ti.tracker_item_id and ti.tracker_id = t.tracker_id and t.datatype = 2 and tefd.tracker_extra_field_id = tef.tracker_extra_field_id and field_name = 'Percent Complete (0-100)'") ;
698 $sthFF = $dbhFF->prepare ("update project_task set percent_complete=? where project_task_id=?") ;
700 while (@arrayAS = $sthAS->fetchrow_array) {
701 my $percent = $arrayAS[0] ;
702 my $ptid = $arrayAS[1] ;
703 next unless $percent =~ /^[0-9]+$/ ;
704 $sthFF->execute ($percent, $ptid) ;
710 'tia.tracker_item_id' => 'project_task_id',
711 'tia.assignee' => 'assigned_to_id',
713 migrate_with_mapping ('tracker_item_assignee tia, tracker_item ti, tracker t', 'project_assigned_to', $map, 'where tia.tracker_item_id = ti.tracker_item_id and ti.tracker_id = t.tracker_id and t.datatype = 2')
720 'tim.tracker_item_message_id' => 'project_message_id',
721 'tim.tracker_item_id' => 'project_task_id',
722 'tim.submitted_by' => 'posted_by',
723 'extract (epoch from tim.adddate)::integer' => 'postdate',
724 'tim.body' => 'body',
726 migrate_with_mapping ('tracker_item_message tim, tracker_item ti, tracker t', 'project_messages', $map, 'where tim.tracker_item_id = ti.tracker_item_id and ti.tracker_id = t.tracker_id and t.datatype = 2')
733 sub push_sequence_for_table {
736 my $seqname = shift ;
738 my $sql = "SELECT max ($field) FROM $table" ;
739 my $sth = $dbhFF->prepare ($sql) ;
741 my @arr = $sth->fetchrow_array ;
744 print STDERR "Pushing $seqname to $cur\n" ;
745 &bump_sequence_to ($dbhFF, $seqname, $cur) ;
747 print STDERR "Not pushing $seqname\n" ;
752 print STDERR "Pushing sequences to appropriate values\n" ;
753 &push_sequence_for_table ('groups', 'group_id', 'groups_pk_seq') ;
754 &push_sequence_for_table ('users', 'user_id', 'users_pk_seq') ;
755 &push_sequence_for_table ('role', 'role_id', 'role_role_id_seq') ;
756 &push_sequence_for_table ('user_group', 'user_group_id', 'user_group_pk_seq') ;
757 &push_sequence_for_table ('trove_group_link', 'trove_group_id', 'trove_group_link_pk_seq') ;
758 &push_sequence_for_table ('forum_group_list', 'group_forum_id', 'forum_group_list_pk_seq') ;
759 &push_sequence_for_table ('forum', 'msg_id', 'forum_pk_seq') ;
760 &push_sequence_for_table ('forum_pending_messages', 'msg_id', 'forum_pending_messages_msg_id_seq') ;
761 &push_sequence_for_table ('frs_package', 'package_id', 'frs_package_pk_seq') ;
762 &push_sequence_for_table ('frs_release', 'release_id', 'frs_release_pk_seq') ;
763 &push_sequence_for_table ('frs_file', 'file_id', 'frs_file_pk_seq') ;
764 &push_sequence_for_table ('doc_groups', 'doc_group', 'doc_groups_pk_seq') ;
765 &push_sequence_for_table ('doc_data', 'docid', 'doc_data_pk_seq') ;
766 &push_sequence_for_table ('mail_group_list', 'group_list_id', 'mail_group_list_pk_seq') ;
767 &push_sequence_for_table ('artifact_group_list', 'group_artifact_id', 'artifact_grou_group_artifac_seq') ;
768 &push_sequence_for_table ('artifact', 'artifact_id', 'artifact_artifact_id_seq') ;
769 &push_sequence_for_table ('artifact_message', 'id', 'artifact_message_id_seq') ;
770 &push_sequence_for_table ('artifact_extra_field_list', 'extra_field_id', 'artifact_extra_field_list_extra_field_id_seq') ;
771 &push_sequence_for_table ('artifact_extra_field_elements', 'element_id', 'artifact_extra_field_elements_element_id_seq') ;
772 &push_sequence_for_table ('artifact_extra_field_data', 'data_id', 'artifact_extra_field_data_data_id_seq') ;
773 &push_sequence_for_table ('artifact_canned_responses', 'id', 'artifact_canned_response_id_seq') ;
774 &push_sequence_for_table ('artifact_query', 'artifact_query_id', 'artifact_query_artifact_query_id_seq') ;
775 &push_sequence_for_table ('project_group_list', 'group_project_id', 'project_group_list_pk_seq') ;
776 &push_sequence_for_table ('project_task', 'project_task_id', 'project_task_pk_seq') ;
777 &push_sequence_for_table ('project_messages', 'project_message_id', 'project_messages_project_message_id_seq') ;
779 print STDERR "Migration script completed OK\n" ;
780 # $dbhFF->commit ; print STDERR "Committed\n" ;