« back to all changes in this revision
Viewing changes to Install/file2sql.php
- browse files at revision 89
- compare with another revision
- compare with revision 896
- download diff
- download tarball
- view history from revision 89
- Committer: V. Keith Hughitt
- Date: 2008-07-09 18:59:47 UTC
- Revision ID: hughitt1@kore-20080709185947-lsdp2jwnuc1bs5nd
nightly build 07-09-2008: re-writing db population script to fix errors during tile processing
- files added:
- install
- files removed:
- Install
- images/TimeBrowser
- files modified:
- getEvents.php
added
removed
Install/file2sql.php
1
<?php
2
/* Example usage:
3
* php file2sql.php /path/to/tiles
4
*/
5
set_time_limit(0);
6
ini_set('display_errors', 'On');
7
8
$GLOBALS['limit'] = 0;
9
$GLOBALS['count'] = 0;
10
$GLOBALS['cwd'] = str_replace('\\', '/', getcwd());
11
$GLOBALS['tables'] = array();
12
$GLOBALS['tiles'] = array();
13
14
$dir = ($_GET['dir'] ? $_GET['dir'] : $_SERVER['argv'][1]);
15
16
if (!mysql_connect('localhost', 'helioviewer', 'helioviewer')) die ("error connecting to db");
17
mysql_select_db('esahelio_svdb0');
18
19
//header("Content-type: text/plain");
20
parseDir($dir);
21
populateDb();
22
23
//print_r($GLOBALS['observatory']);
24
25
echo "Insertion successful\n";
26
27
function parseDir($dir) {
28
echo "Parsing $dir\n";
29
$GLOBALS['count']++;
30
if ($GLOBALS['count'] == $GLOBALS['limit']) exit;
31
$hd = opendir($dir);
32
$imgs = array();
33
while ($entry = readdir($hd)) {
34
if ($entry == '.' || $entry == '..') continue;
35
if (is_dir("$dir/$entry")) parseDir("$dir/$entry");
36
elseif (substr($entry, -4) == '.jpg' || substr($entry, -4) == '.png') {
37
$tiles[] = "$dir/$entry";
38
//echo "Found tile: $dir/$entry\n";
39
}
40
}
41
closedir($hd);
42
43
$num = count($tiles);
44
if ($num > 0) {
45
sort($tiles);
46
$pathinfo = pathinfo($tiles[0]);
47
$filename = $pathinfo['basename'];
48
$extension = $pathinfo['extension'];
49
$map = substr($filename, 0, 34);
50
list($year, $month, $day, $time, $observatory, $instrument, $detector, $measurement) =
51
explode('_', $map);
52
$hour = substr($time, 0, 2);
53
$min = substr($time, 2, 2);
54
$sec = substr($time, 4, 2);
55
56
//$ratio = (float) substr($filename, 47, 7);
57
58
59
//Note: MySQL stores dates using the server's local timezone, but outputs UNIX_TIMESTAMP() in GMT time. Rather than requiring the
60
// server switch to using UTC to store dates by default, which is not entirely straight-forward, it is easier to pass it a
61
// local date, so that when UNIX_TIMESTAMP() is called, the returned timestamp is the actual UTC time.
62
$ts = convertToLocalDate("$year-$month-$day $hour:$min:$sec");
63
64
//echo "original date: " . "$year-$month-$day $hour:$min:$sec\n";
65
//echo "offseted date: $ts\n";
66
67
$GLOBALS['observatory'][$observatory]['abbreviation'] = $observatory;
68
$GLOBALS['observatory'][$observatory]['instrument'][$instrument]['abbreviation'] = $instrument;
69
$GLOBALS['observatory'][$observatory]['instrument'][$instrument]['detector'][$detector]['abbreviation'] = $detector;
70
//$GLOBALS['observatory'][$observatory]['instrument'][$instrument]['detector'][$detector]['sunImgRatio'] = $ratio;
71
// ToDo: Determine lowestRegularZoomLevel (lowest zoom level that still has the same image/sun diameter ratio as higher levels. The ratio decreases by the factor 2 for lower zoom levels)
72
$GLOBALS['observatory'][$observatory]['instrument'][$instrument]['detector'][$detector]['measurement'][$measurement]['abbreviation'] = $measurement;
73
$GLOBALS['observatory'][$observatory]['instrument'][$instrument]['detector'][$detector]['measurement'][$measurement]['image'][] = array('timestamp' => $ts, 'measurement' => $measurement, 'filetype' => $extension, 'tiles' => $tiles);
74
75
76
77
/*
78
$GLOBALS['tables']['observatory'][$observatory] = array('abbreviation' => $observatory);
79
$GLOBALS['tables']['instrument'][] = array('abbreviation' => $instrument, 'observatory' => $observatory);
80
$GLOBALS['tables']['detector'][] = array('abbreviation' => $detector, 'instrument' => $instrument);
81
$GLOBALS['tables']['measurement'][] = array('abbreviation' => $measurement, 'detector' => $detector);
82
$GLOBALS['tables']['image'][] = array('timestamp' => "$year-$month-$day $hour:$min:$sec", 'measurement' => $measurement);
83
*/
84
//$GLOBALS['tiles'][$map] = $tiles;
85
86
/*
87
echo "Adding image $map...\n";
88
89
$query = sprintf("INSERT IGNORE INTO maps VALUES ('%s',
90
'%s',
91
%d, %d, %d, %d, %d, %d,
92
'%s', '%s', '%s', '%s', '%s')",
93
mysql_real_escape_string($map),
94
"$year-$month-$day $hour:$min:$sec",
95
$year, $month, $day, $hour, $min, $sec,
96
$observatory, $instrument, $detector, $measurement, $extension
97
);
98
$result = mysql_query($query);
99
if (!$result) {
100
echo "$query - failed\n";
101
die (mysql_error());
102
}
103
*/
104
// if (mysql_affected_rows() > 0) {
105
// echo "Adding tiles in $dir: ";
106
107
// }
108
}
109
110
unset($tiles);
111
}
112
113
function populateDb() {
114
parseLevels($GLOBALS['observatory'], 'observatory');
115
}
116
117
function parseLevels($level, $table, $parentTable = null, $parentId = null) {
118
foreach($level as $key => $entry) {
119
//print_r($entry);
120
$subelements = null;
121
$fields = array();
122
$tiles = false;
123
foreach ($entry as $name => $value) {
124
if ($name == 'tiles') {
125
$tiles = $value;
126
} elseif (is_array($value)) {
127
$subelements = $value;
128
} else {
129
$fields[$name] = $value;
130
}
131
}
132
133
if ($parentTable) $fields[$parentTable] = $parentId;
134
// insert fields into table
135
// get and save the id
136
$fields['id'] = getIdOrInsert($table, $fields, $parentTable, $parentId);
137
138
if ($tiles) addTiles($tiles, $fields['id']);
139
//if ($parentTable) {
140
//echo "$table($fields[id]) -> $parentTable($parentId)\n";
141
//}
142
if ($subelements) parseLevels($subelements, $name, $table, $fields['id']);
143
}
144
}
145
146
147
function getIdOrInsert($table, $fields, $parentTable = null, $parentId = null) {
148
//echo "parentField: ${parentTable}Id ($parentId)\n";
149
// Check if row exists
150
$query = "SELECT id FROM $table WHERE";
151
$and = false;
152
foreach ($fields as $field => $value) {
153
if ($and) $query .= " AND";
154
else $and = true;
155
156
if ($field == $parentTable) $query .= " ${parentTable}Id=$parentId";
157
else $query .= " $field='$value'";
158
}
159
$result = mysql_query($query);
160
//echo "$query\n";
161
162
if (mysql_num_rows($result) > 0) {
163
// row exists
164
$row = mysql_fetch_array($result);
165
return $row['id'];
166
} else {
167
// row doesn't exist
168
$query = "INSERT INTO $table (";
169
if ($fields['abbreviation']) {
170
$comma = true;
171
$query .= 'name';
172
} else {
173
$comma = false;
174
}
175
foreach ($fields as $field => $value) {
176
if ($comma) $query .= ", ";
177
else $comma = true;
178
179
if ($field == $parentTable) $query .= "${parentTable}Id";
180
else $query .= "$field";
181
}
182
183
$query .= ") VALUES (";
184
if ($fields['abbreviation']) {
185
$comma = true;
186
$query .= "'$fields[abbreviation]'";
187
} else {
188
$comma = false;
189
}
190
foreach ($fields as $field => $value) {
191
if ($comma) $query .= ", ";
192
else $comma = true;
193
194
if ($field == $parentTable) $query .= $parentId;
195
else $query .= "'$value'";
196
}
197
$query .= ")";
198
//echo "$query\n";
199
//return 'TBD';
200
$result = mysql_query($query);
201
return mysql_insert_id();
202
}
203
}
204
205
function addTiles($tiles, $imageId) {
206
$c = 0;
207
$res = 5;
208
$next = $res;
209
$num = count($tiles);
210
211
foreach($tiles as $filepath) {
212
addTile($filepath, $imageId);
213
$p = (int)($c / $num * 100);
214
if ($p >= $next) {
215
echo "$p% ";
216
$next += $res;
217
}
218
$c++;
219
}
220
221
echo "100%\n";
222
}
223
224
function addTile($filepath, $imageId) {
225
//global $ratio;
226
$pathinfo = pathinfo($filepath);
227
$filename = $pathinfo['basename'];
228
$filetype = $pathinfo['extension'];
229
$map = substr($filename, 0, 34);
230
$zoom = substr($filename, 35, 2);
231
$x = substr($filename, 38, 2);
232
$y = substr($filename, 41, 2);
233
//if ($x == 0 && $y == 0) {
234
// $ratio = (float) substr($filename, 47, 7);
235
//}
236
//echo "Adding tile: $filename\n";
237
//echo "$zoom, $x, $y, $ratio\n";
238
$query = sprintf("INSERT IGNORE INTO tile (imageId, x, y, zoom, tile) VALUES ($imageId, $x, $y, $zoom, '%s')", mysql_real_escape_string(file_get_contents($filepath)));
239
$result = mysql_query($query);
240
if (!$result) {
241
echo "$query - failed\n";
242
die (mysql_error());
243
}
244
}
245
246
function convertToLocalDate ($timestamp) {
247
$time = date_create($timestamp);
248
$offset = date_offset_get($time);
249
$time->modify($offset . " seconds");
250
return $time->format('Y-m-d H:i:s');
251
}
252
?>
253
Loggerhead is a web-based interface for Breezy
Version: 2.0.1