1
|
#!/usr/bin/perl -w -s
|
2
|
|
3
|
############################# migrate.pl ###############################
|
4
|
# #
|
5
|
# This perl script creates the new 2011 db and migrates #
|
6
|
# from the old db by... #
|
7
|
# 1. creates the new database using create.sql #
|
8
|
# 2. maps the data from the old db to the new db #
|
9
|
# 3. drops the excess data and schema #
|
10
|
# #
|
11
|
# Usage: migrate.pl [options] #
|
12
|
# options: #
|
13
|
# -olddb='<DATABASENAME>' Name of the old STRI Database #
|
14
|
# -newdb='<DATABASENAME>' Name of the new STRI Database #
|
15
|
# -user='<USERNAME>' MySQL username #
|
16
|
# -passwd='<PASSWORD>' Password of the MySQL user #
|
17
|
# -cfile='<FILEPATH>' Absolute path to create.sql #
|
18
|
# -dfile='<FILEPATH>' Absolute path to drop.sql #
|
19
|
# -mysql='<FILEPATH>' Absolute path to mysql #
|
20
|
# -mysqldump='<FILEPATH>' Absolute path to mysqldump #
|
21
|
# -quiet Ignores errors #
|
22
|
# #
|
23
|
########################################################################
|
24
|
|
25
|
############################## Developers ##############################
|
26
|
# #
|
27
|
# Bradley University, Computer Science Dept. #
|
28
|
# Smithsonian Tropical Research Institute #
|
29
|
# #
|
30
|
# 2011 Capstone Project #
|
31
|
# Mario Campos, Michael Koeber, Zach Caschetta #
|
32
|
# #
|
33
|
########################################################################
|
34
|
|
35
|
use strict;
|
36
|
use DBI;
|
37
|
use Fcntl;
|
38
|
|
39
|
our($olddb,$newdb,$user,$passwd,$cfile,$dfile,$mysql,$mysqldump,$quiet);
|
40
|
BEGIN {
|
41
|
$olddb or print 'Enter the old database name:' and chomp($olddb=<>);
|
42
|
$newdb or print 'Enter the new database name:' and chomp($newdb=<>);
|
43
|
$user or print 'Enter the username:' and chomp($user=<>);
|
44
|
$passwd or print 'Enter the password:' and chomp($passwd=<>);
|
45
|
$cfile or print 'Enter the path of the sql create file:' and chomp($cfile=<>);
|
46
|
$dfile or print 'Enter the path of the sql drop file:' and chomp($dfile=<>);
|
47
|
$mysql or $mysql='mysql';
|
48
|
$mysqldump or $mysqldump='mysqldump';
|
49
|
sysopen FILE,'migrate.log',O_CREAT|O_EXCL|O_WRONLY,0755 or die $!;
|
50
|
}
|
51
|
|
52
|
# create new db
|
53
|
print "Creating `$newdb`...";
|
54
|
system "$mysql -u $user -p$passwd -e 'create database $newdb;'";
|
55
|
print "done!\n";
|
56
|
|
57
|
# dump old db
|
58
|
print "Dumping `$olddb`...";
|
59
|
system "$mysqldump --opt -u $user -p$passwd $olddb > $olddb";
|
60
|
print "done!\n";
|
61
|
|
62
|
# inserting data into new db
|
63
|
print "Undumping into `$newdb`...";
|
64
|
system "$mysql -u $user -p$passwd $newdb < $olddb";
|
65
|
print "done!\n";
|
66
|
|
67
|
# running create.sql
|
68
|
system "$mysql -v -u $user -p$passwd $newdb < $cfile";
|
69
|
|
70
|
|
71
|
# connect to MySQL
|
72
|
my $dbh = DBI->connect("DBI:mysql:database=$newdb;host=localhost",$user,$passwd);
|
73
|
|
74
|
# create coordinates of plot points
|
75
|
my $sql = 'SELECT PlotID, Latitude, Longitude, Elevation FROM Site';
|
76
|
my $sth = $dbh->prepare("$sql");
|
77
|
$sth->execute;
|
78
|
my $tbl_ref = $sth->fetchall_hashref('PlotID');
|
79
|
foreach my $key (keys %$tbl_ref) {
|
80
|
$sql = 'INSERT INTO Coordinates (PlotID,GX,GY';
|
81
|
defined $tbl_ref->{$key}->{'Elevation'} and $sql.=',GZ';
|
82
|
$sql.=") VALUES ($key,$tbl_ref->{$key}->{Longitude},$tbl_ref->{$key}->{Latitude}";
|
83
|
defined $tbl_ref->{$key}->{'Elevation'} and $sql.=",$tbl_ref->{$key}->{'Elevation'}";
|
84
|
$sql.=')';
|
85
|
if($dbh->do($sql)) {
|
86
|
print "$sql\n";
|
87
|
} else {
|
88
|
die "Error executing: $sql\n" unless defined $quiet;
|
89
|
print FILE "Error executing: $sql\n";
|
90
|
}
|
91
|
}
|
92
|
|
93
|
# create coordinates of quadrat points
|
94
|
$sql = 'SELECT QuadratID, PlotID, StartX, StartY FROM Quadrat';
|
95
|
$sth = $dbh->prepare("$sql");
|
96
|
$sth->execute;
|
97
|
$tbl_ref = $sth->fetchall_hashref('QuadratID');
|
98
|
foreach my $key (keys %$tbl_ref) {
|
99
|
$sql = "INSERT INTO Coordinates (QuadratID,PlotID,PX,PY) VALUES ($key,$tbl_ref->{$key}->{PlotID},$tbl_ref->{$key}->{StartX},$tbl_ref->{$key}->{StartY})";
|
100
|
if($dbh->do($sql)) {
|
101
|
print "$sql\n";
|
102
|
} else {
|
103
|
die "Error executing: $sql\n" unless defined $quiet;
|
104
|
print FILE "Error executing: $sql\n";
|
105
|
}
|
106
|
}
|
107
|
|
108
|
$sql = 'SELECT DISTINCT DimX, DimY, PlotID FROM Quadrat';
|
109
|
$sth = $dbh->prepare("$sql");
|
110
|
$sth->execute;
|
111
|
$tbl_ref = $sth->fetchall_hashref('PlotID');
|
112
|
foreach my $key (keys %$tbl_ref) {
|
113
|
if(defined $tbl_ref->{$key}->{'DimX'} && defined $tbl_ref->{$key}->{'DimY'}) {
|
114
|
$sql = "UPDATE Site SET QDimX=$tbl_ref->{$key}->{DimX}, QDimY=$tbl_ref->{$key}->{DimY} WHERE PlotID=$key";
|
115
|
if($dbh->do($sql)) {
|
116
|
print "$sql\n";
|
117
|
} else {
|
118
|
die "Error executing: $sql\n" unless defined $quiet;
|
119
|
print FILE "Error executing: $sql\n";
|
120
|
}
|
121
|
}
|
122
|
}
|
123
|
|
124
|
# LogTreeHistory
|
125
|
$sql = 'SELECT TreeID,ChangeDate,ChangeDescription,ChangeCodeID,QuadratID,PlotID,Tag,X,Y,SpeciesID,SubSpeciesID FROM LogTreeHistory ORDER BY ChangeDate';
|
126
|
$sth = $dbh->prepare("$sql");
|
127
|
$sth->execute;
|
128
|
my @row = ();
|
129
|
while( @row = $sth->fetchrow_array ) {
|
130
|
$sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
|
131
|
defined $row[1] and $sql .= 'ChangeDate,';
|
132
|
defined $row[2] and $sql .= 'Description,';
|
133
|
$sql .= "New) VALUES ('Tree',$row[0],";
|
134
|
defined $row[1] and $sql .= "'$row[1]',";
|
135
|
defined $row[2] and $sql .= "'$row[2]',";
|
136
|
$sql .= "'ChangeCodeID=$row[3],QuadratID=$row[4],PlotID=$row[5],Tag=$row[6],X=$row[7],Y=$row[8],SpeciesID=$row[9],SubspeciesID=$row[10]')";
|
137
|
if($dbh->do($sql)) {
|
138
|
print "$sql\n";
|
139
|
} else {
|
140
|
die "Error executing: $sql\n" unless defined $quiet;
|
141
|
print FILE "Error executing: $sql\n";
|
142
|
}
|
143
|
}
|
144
|
|
145
|
# LogMeasurementHistory
|
146
|
$sql = 'SELECT MeasureID,CensusID,DateOfChange,DescriptionOfChange,StemID,TreeID,DBH,HOM,ExactDate FROM LogMeasurementHistory ORDER BY DateOfChange';
|
147
|
$sth = $dbh->prepare("$sql");
|
148
|
$sth->execute;
|
149
|
while( @row = $sth->fetchrow_array ) {
|
150
|
$sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
|
151
|
defined $row[2] and $sql .= 'ChangeDate,';
|
152
|
defined $row[3] and $sql .= 'Description,';
|
153
|
$sql .= "New) VALUES ('Measurement','MeasureID=$row[0],CensusID=$row[1]',";
|
154
|
defined $row[2] and $sql .= "'$row[2]',";
|
155
|
defined $row[3] and $sql .= "'$row[3]',";
|
156
|
$sql .= "'StemID=$row[4],TreeID=$row[5],DBH=$row[6],HOM=$row[7],ExactDate=$row[8]')";
|
157
|
|
158
|
if($dbh->do($sql)) {
|
159
|
print "$sql\n";
|
160
|
} else {
|
161
|
die "Error executing: $sql\n" unless defined $quiet;
|
162
|
print FILE "Error executing: $sql\n";
|
163
|
}
|
164
|
}
|
165
|
|
166
|
# LogMAttrHistory
|
167
|
$sql = 'SELECT MeasureID,CensusID,TSMID,DateOfChange,DescriptionOfChange FROM LogMAttrHistory ORDER BY DateOfChange';
|
168
|
$sth = $dbh->prepare($sql);
|
169
|
$sth->execute;
|
170
|
while( @row = $sth->fetchrow_array ) {
|
171
|
$sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
|
172
|
defined $row[3] and $sql .= 'ChangeDate,';
|
173
|
defined $row[4] and $sql .= 'Description,';
|
174
|
$sql .= "New) VALUES ('MeasurementAttributes','MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]',";
|
175
|
defined $row[3] and $sql .= "'$row[3]',";
|
176
|
defined $row[4] and $sql .= "'$row[4]',";
|
177
|
$sql .= "'MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]')";
|
178
|
|
179
|
if($dbh->do($sql)) {
|
180
|
print "$sql\n";
|
181
|
} else {
|
182
|
die "Error executing: $sql\n" unless defined $quiet;
|
183
|
print FILE "Error executing: $sql\n";
|
184
|
}
|
185
|
}
|
186
|
|
187
|
# LogTreeAttrHistory
|
188
|
$sql = 'SELECT CensusID,TreeID,TSMID,DateOfChange,DescriptionOfChange FROM LogTreeAttrHistory ORDER BY DateOfChange';
|
189
|
$sth = $dbh->prepare("$sql");
|
190
|
$sth->execute;
|
191
|
while( @row = $sth->fetchrow_array ) {
|
192
|
$sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
|
193
|
defined $row[3] and $sql .= 'ChangeDate,';
|
194
|
defined $row[4] and $sql .= 'Description,';
|
195
|
$sql .= "New) VALUES ('TreeAttributes','CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]',";
|
196
|
defined $row[3] and $sql .= "'$row[3]',";
|
197
|
defined $row[4] and $sql .= "'$row[4]',";
|
198
|
$sql .= "'CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]')";
|
199
|
|
200
|
if($dbh->do($sql)) {
|
201
|
print "$sql\n";
|
202
|
} else {
|
203
|
die "Error executing: $sql\n" unless defined $quiet;
|
204
|
print FILE "Error executing: $sql\n";
|
205
|
}
|
206
|
}
|
207
|
|
208
|
# execute drop.sql
|
209
|
system "$mysql -v -u $user -p$passwd $newdb < $dfile" or die $!;
|
210
|
|
211
|
close FILE or die $!;
|