1 |
1549
|
aaronmk
|
#!/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 $!;
|