1
|
# In order to complete the migration to Redmine, we need to preserve date
|
2
|
# and username. To do this, we need to fix the database.
|
3
|
|
4
|
use strict;
|
5
|
use warnings;
|
6
|
use DBI;
|
7
|
|
8
|
my $db = 'redmine_default';
|
9
|
my $server = 'localhost'; # Il est possible de mettre une adresse IP
|
10
|
my $user = 'root'; # identifiant
|
11
|
my $passwd = 'tata';
|
12
|
my $port = '';
|
13
|
|
14
|
|
15
|
##### Create DB connection
|
16
|
my $dbh = DBI->connect( "DBI:mysql:database=$db;host=$server;port=$port", $user, $passwd, { RaiseError => 1, } ) or die "Connection impossible à la base de données $db !\n $! \n $@\n$DBI::errstr";
|
17
|
|
18
|
|
19
|
|
20
|
###### Initialisation of hashes needed to fix URL
|
21
|
open(my $fix_url_issues_csv, '<', 'fix_url_issues.csv') or die "Couldn't open fix_url_issues.csv";
|
22
|
my %name2iid = ();
|
23
|
my %nid2iid = ();
|
24
|
my %cid2iid = ();
|
25
|
my %cid2post_nb = ();
|
26
|
|
27
|
while (my $line = <$fix_url_issues_csv>)
|
28
|
{
|
29
|
chomp $line;
|
30
|
my ($nid, $name, $iid) = split ",", $line;
|
31
|
$name2iid{$name} = $iid;
|
32
|
$nid2iid{$nid} = $iid;
|
33
|
}
|
34
|
close $fix_url_issues_csv;
|
35
|
|
36
|
open(my $fix_url_comments_csv, '<', 'fix_url_comments.csv') or die "Couldn't open fix_url_comments.csv";
|
37
|
while (my $line = <$fix_url_comments_csv>)
|
38
|
{
|
39
|
chomp $line;
|
40
|
my ($cid, $iid, $post_nb) = split ",", $line;
|
41
|
$cid2iid{$cid} = $iid;
|
42
|
$cid2post_nb{$cid} = $post_nb;
|
43
|
}
|
44
|
close $fix_url_comments_csv;
|
45
|
|
46
|
|
47
|
|
48
|
###### Definition of functions
|
49
|
sub fix_issues_link {
|
50
|
my $text = $_[0];
|
51
|
|
52
|
# Find all links in text
|
53
|
my @links = ($text =~ m/:http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)(?:content\/t%C3%A2che|node)\/(.*)/g);
|
54
|
|
55
|
if ( @links )
|
56
|
{
|
57
|
foreach (@links)
|
58
|
{
|
59
|
print $_ ."\n";
|
60
|
$text =~ s/"http:\/\/assos\.centrale-marseille\.fr(\/|\/lessive\/)(content\/t%C3%A2che|node)\/.*":http:\/\/assos\.centrale-marseille\.fr(\/|\/lessive\/)(content\/t%C3%A2che|node)\/$_/#$cid2iid{$_}#note-$cid2post_nb{$_}/g;
|
61
|
}
|
62
|
}
|
63
|
return $text;
|
64
|
}
|
65
|
|
66
|
sub fix_comments_link {
|
67
|
my $text = $_[0];
|
68
|
|
69
|
# Find all links in text
|
70
|
my @links = $text =~ m/"http:\/\/assos\.centrale-marseille\.fr(\/|\/lessive\/)comment\/.*"/g;
|
71
|
|
72
|
# Foreach link, get comment cid.
|
73
|
my @cids = ();
|
74
|
if (@links)
|
75
|
{
|
76
|
foreach (@links)
|
77
|
{
|
78
|
my $cid =~ s/"http:\/\/assos\.centrale-marseille\.fr(\/|\/lessive\/)comment\/(.*)#.*"/$1/g;
|
79
|
push @cids, $cid;
|
80
|
}
|
81
|
|
82
|
foreach (@cids)
|
83
|
{
|
84
|
$text =~ s/"http:\/\/assos\.centrale-marseille\.fr(\/|\/lessive\/)comment\/$_#$_":http:\/\/assos\.centrale-marseille\.fr\/comment\/$_#$_/#$name2iid{$_}/g;
|
85
|
}
|
86
|
}
|
87
|
|
88
|
return $text;
|
89
|
}
|
90
|
|
91
|
|
92
|
|
93
|
###### Update issues
|
94
|
my $sql_update_issue =<<"SQL";
|
95
|
UPDATE issues
|
96
|
SET author_id = ?, created_on = ?
|
97
|
WHERE id = ?
|
98
|
SQL
|
99
|
|
100
|
my $sql_fix_links_issue =<<"SQL";
|
101
|
UPDATE issues
|
102
|
SET description = ?
|
103
|
WHERE id = ?
|
104
|
SQL
|
105
|
|
106
|
my $req_update_issue = $dbh->prepare($sql_update_issue);
|
107
|
my $req_fix_links_issue = $dbh->prepare($sql_fix_links_issue);
|
108
|
|
109
|
# Reading file
|
110
|
open(my $issues, '<issues.csv') or die "Couldn't open issues.txt\n";
|
111
|
|
112
|
while (my $line = <$issues>)
|
113
|
{
|
114
|
# Update author and date of creation
|
115
|
chomp $line;
|
116
|
|
117
|
my ($iid, $author_id, $created_on, $nid, $name) = split ",", $line;
|
118
|
|
119
|
$req_update_issue->execute($author_id, $created_on, $iid);
|
120
|
|
121
|
## Fix links
|
122
|
# issues
|
123
|
my $select_description = "SELECT description FROM issues WHERE id = 1569";
|
124
|
|
125
|
my ($description) = $dbh->selectrow_array($select_description);
|
126
|
|
127
|
$description = fix_issues_link($description);
|
128
|
|
129
|
die "stop";
|
130
|
|
131
|
print $description . "\n";
|
132
|
# print $links[0];
|
133
|
# print $name2iid{$links[0]};
|
134
|
# Comments
|
135
|
# $description = fix_comments_link($description);
|
136
|
|
137
|
# $req_fix_links_issue->execute($description, $iid);
|
138
|
}
|
139
|
|
140
|
$req_update_issue->finish;
|
141
|
$req_fix_links_issue->finish;
|
142
|
|
143
|
|
144
|
###### Update journals
|
145
|
my $sql_get_id =<<"SQL";
|
146
|
SELECT id FROM journals WHERE journalized_id = ?
|
147
|
ORDER BY created_on ASC
|
148
|
SQL
|
149
|
|
150
|
my $sql_update_journals =<<"SQL";
|
151
|
UPDATE journals
|
152
|
SET created_on = ?, user_id = ?
|
153
|
WHERE id = ?
|
154
|
SQL
|
155
|
|
156
|
my $sql_updated_on =<<"SQL";
|
157
|
UPDATE issues SET updated_on = ?
|
158
|
WHERE id = ?
|
159
|
SQL
|
160
|
|
161
|
my $sql_fix_links_comment =<<"SQL";
|
162
|
UPDATE journals
|
163
|
SET notes = ?
|
164
|
WHERE id = ?
|
165
|
SQL
|
166
|
|
167
|
my $req_get_id = $dbh->prepare($sql_get_id);
|
168
|
my $req_update_journals = $dbh->prepare($sql_update_journals);
|
169
|
my $req_updated_on = $dbh->prepare($sql_updated_on);
|
170
|
my $req_fix_links_comment = $dbh->prepare($sql_fix_links_comment);
|
171
|
|
172
|
# Reading file.
|
173
|
open(my $comments, '<comments.csv') or die "Couldn't open comments.txt\n";
|
174
|
|
175
|
# We must remember current iid in order to fetch result or execute query: req_get_id return
|
176
|
# more than one value
|
177
|
my $current_iid = -1;
|
178
|
|
179
|
while (my $line = <$comments>)
|
180
|
{
|
181
|
chomp $line;
|
182
|
|
183
|
my ($iid, $user_id, $created_on) = split ",", $line;
|
184
|
|
185
|
# We get the id of the comment.
|
186
|
if ($current_iid != $iid)
|
187
|
{
|
188
|
$current_iid = $iid;
|
189
|
$req_get_id->execute($iid);
|
190
|
}
|
191
|
|
192
|
my ($id) = $req_get_id->fetchrow_array;
|
193
|
|
194
|
# We do the update.
|
195
|
$req_update_journals->execute($created_on, $user_id, $id);
|
196
|
$req_updated_on->execute($created_on, $iid);
|
197
|
|
198
|
## Fix links.
|
199
|
# issues
|
200
|
my $select_notes = "SELECT notes FROM journals WHERE id = $id";
|
201
|
my ($notes) = $dbh->selectrow_array($select_notes);
|
202
|
# $notes = fix_issues_link($notes);
|
203
|
# Comments
|
204
|
# $notes = fix_comments_link($notes);
|
205
|
|
206
|
# $req_fix_links_comment->execute($notes, $id);
|
207
|
}
|
208
|
|
209
|
$req_get_id->finish;
|
210
|
$req_update_journals->finish;
|
211
|
$req_updated_on->finish;
|
212
|
|
213
|
####### Close the db connection.
|
214
|
$dbh->disconnect;
|