LSSTApplications  10.0-2-g4f67435,11.0.rc2+1,11.0.rc2+12,11.0.rc2+3,11.0.rc2+4,11.0.rc2+5,11.0.rc2+6,11.0.rc2+7,11.0.rc2+8
LSSTDataManagementBasePackage
DbTsvStorage.cc
Go to the documentation of this file.
1 // -*- lsst-c++ -*-
2 
3 /*
4  * LSST Data Management System
5  * Copyright 2008, 2009, 2010 LSST Corporation.
6  *
7  * This product includes software developed by the
8  * LSST Project (http://www.lsst.org/).
9  *
10  * This program is free software: you can redistribute it and/or modify
11  * it under the terms of the GNU General Public License as published by
12  * the Free Software Foundation, either version 3 of the License, or
13  * (at your option) any later version.
14  *
15  * This program is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18  * GNU General Public License for more details.
19  *
20  * You should have received a copy of the LSST License Statement and
21  * the GNU General Public License along with this program. If not,
22  * see <http://www.lsstcorp.org/LegalNotices/>.
23  */
24 
25 
40 #ifndef __GNUC__
41 # define __attribute__(x) /*NOTHING*/
42 #endif
43 static char const* SVNid __attribute__((unused)) = "$Id$";
44 
46 
47 #include <iomanip>
48 #include <stdlib.h>
49 #include <string.h>
50 #include <unistd.h>
51 
52 #include <mysql/mysql.h>
53 
54 #include "lsst/pex/exceptions.h"
55 #include "lsst/daf/base/DateTime.h"
58 
60 
61 namespace lsst {
62 namespace daf {
63 namespace persistence {
64 
67 DbTsvStorage::DbTsvStorage(void) : _saveTemp(false) {
68 }
69 
73 }
74 
79  _tempPath = "/tmp";
80  if (policy && policy->exists("TempPath")) {
81  _tempPath = policy->getString("TempPath");
82  }
83  if (policy && policy->exists("SaveTemp") && policy->getBool("SaveTemp")) {
84  _saveTemp = true;
85  }
86 }
87 
92  _persisting = true;
93  _location = location.locString();
94  // Set the timezone for any DATE/TIME/DATETIME fields.
95  setenv("TZ", "UTC", 1);
96 }
97 
102  _persisting = false;
104 }
105 
110 }
111 
115  if (!_persisting) {
117  return;
118  }
119 
120  // close stream
121  _osp->close();
122 
123  MYSQL* db = mysql_init(0);
124  if (db == 0) {
125  throw LSST_EXCEPT(lsst::pex::exceptions::RuntimeError, "Unable to allocate MySQL connection");
126  }
128  unsigned int port = strtoul(dbLoc.getPort().c_str(), 0, 10);
129  if (mysql_real_connect(db,
130  dbLoc.getHostname().c_str(),
131  dbLoc.getUsername().c_str(),
132  dbLoc.getPassword().c_str(),
133  dbLoc.getDbName().c_str(),
134  port, 0,
135  CLIENT_COMPRESS | CLIENT_LOCAL_FILES) == 0) {
136  throw LSST_EXCEPT(lsst::pex::exceptions::RuntimeError,
137  "Unable to connect to MySQL database: " + _location);
138  }
139  if (mysql_options(db, MYSQL_OPT_LOCAL_INFILE, 0) != 0) {
140  throw LSST_EXCEPT(lsst::pex::exceptions::RuntimeError,
141  std::string("Unable to set LOCAL INFILE option - * ") +
142  mysql_error(db));
143  }
144 
145  std::string query = "LOAD DATA LOCAL INFILE";
146  query += " '";
147  query += _fileName;
148  query += "'";
149  query += " REPLACE";
150  query += " INTO TABLE `" + _tableName;
151  query += "` (";
152  for (std::map<std::string, int>::const_iterator it = _colMap.begin();
153  it != _colMap.end(); ++it) {
154  _rowBuffer[it->second] = it->first;
155  }
156  for (std::vector<std::string>::const_iterator it = _rowBuffer.begin();
157  it != _rowBuffer.end(); ++it) {
158  if (it != _rowBuffer.begin()) query += " ,";
159  query += *it;
160  }
161  query += ")";
162 
163  if (mysql_query(db, query.c_str()) != 0) {
164  mysql_close(db);
165  throw LSST_EXCEPT(lsst::pex::exceptions::RuntimeError,
166  "Unable to load data into database table: " + _tableName +
167  "- * " + mysql_error(db));
168  }
169  mysql_close(db);
170 
171  // unlink file
172  if (!_saveTemp) {
173  unlink(_fileName);
174  }
175  delete[] _fileName;
176 }
177 
183 void DbTsvStorage::createTableFromTemplate(std::string const& tableName,
184  std::string const& templateName,
185  bool mayAlreadyExist) {
186  if (_persisting) {
187  DbStorage dbs;
189  dbs.startTransaction();
190  dbs.createTableFromTemplate(tableName, templateName, mayAlreadyExist);
191  dbs.endTransaction();
192  }
193  else {
194  DbStorage::createTableFromTemplate(tableName, templateName,
195  mayAlreadyExist);
196  }
197 }
198 
202 void DbTsvStorage::dropTable(std::string const& tableName) {
203  if (_persisting) {
204  DbStorage dbs;
206  dbs.startTransaction();
207  dbs.dropTable(tableName);
208  dbs.endTransaction();
209  }
210  else {
211  DbStorage::dropTable(tableName);
212  }
213 }
214 
218 void DbTsvStorage::truncateTable(std::string const& tableName) {
219  if (_persisting) {
220  DbStorage dbs;
222  dbs.startTransaction();
223  dbs.truncateTable(tableName);
224  dbs.endTransaction();
225  }
226  else {
227  DbStorage::truncateTable(tableName);
228  }
229 }
230 
234 void DbTsvStorage::setTableForInsert(std::string const& tableName) {
235  _tableName = tableName;
236  std::string templ = _tempPath + "/" + tableName + ".XXXXXX";
237  _fileName = new char[templ.size() + 1];
238  strncpy(_fileName, templ.c_str(), templ.size());
239  _fileName[templ.size()] = '\0';
240  int fd = mkstemp(_fileName);
241  // \todo check for errors
242  close(fd);
243  _osp.reset(new std::ofstream(_fileName));
244 }
245 
252 int DbTsvStorage::_getColumnIndex(std::string const& columnName) {
253  std::map<std::string, int>::iterator i = _colMap.find(columnName);
254  if (i == _colMap.end()) {
255  _colMap.insert(std::pair<std::string, int>(columnName,
256  _rowBuffer.size()));
257  _rowBuffer.push_back(std::string());
258  return _rowBuffer.size() - 1;
259  }
260  else {
261  return i->second;
262  }
263 }
264 
269 template <typename T>
270 void DbTsvStorage::setColumn(std::string const& columnName, T const& value) {
271  int colIndex = _getColumnIndex(columnName);
272 
273  // set value in row buffer
274  // \todo quote value as appropriate
275  _convertStream.str(std::string());
276  _convertStream << value;
277  _rowBuffer[colIndex] = _convertStream.str();
278 
279  // \todo Optimization: if next column, output now, plus any others saved
280 }
281 
282 // Specialization for char to persist as TINYINT instead of [VAR]CHAR(1).
283 template<>
284 void DbTsvStorage::setColumn(std::string const& columnName,
285  char const& value) {
286  int colIndex = _getColumnIndex(columnName);
287  _convertStream.str(std::string());
288  _convertStream << static_cast<int>(value);
289  _rowBuffer[colIndex] = _convertStream.str();
290 }
291 
292 // Specializations for float and double to set precision correctly.
293 template<>
294 void DbTsvStorage::setColumn(std::string const& columnName,
295  double const& value) {
296  int colIndex = _getColumnIndex(columnName);
297  _convertStream.str(std::string());
298  _convertStream << std::setprecision(17) << value;
299  _rowBuffer[colIndex] = _convertStream.str();
300 }
301 
302 template<>
303 void DbTsvStorage::setColumn(std::string const& columnName,
304  float const& value) {
305  int colIndex = _getColumnIndex(columnName);
306  _convertStream.str(std::string());
307  _convertStream << std::setprecision(9) << value;
308  _rowBuffer[colIndex] = _convertStream.str();
309 }
310 
311 // Specialization for DateTime.
312 template<>
313 void DbTsvStorage::setColumn(std::string const& columnName,
314  DateTime const& value) {
315  int colIndex = _getColumnIndex(columnName);
316  _convertStream.str(std::string());
317  struct tm t = value.gmtime();
318  char buf[20];
319  strftime(buf, sizeof(buf), "%F %T", &t);
320  _rowBuffer[colIndex] = std::string(buf);
321 }
322 
326 void DbTsvStorage::setColumnToNull(std::string const& columnName) {
327  int colIndex = _getColumnIndex(columnName);
328  _rowBuffer[colIndex] = "\\N";
329 }
330 
335  // Output row to stream
336  for (std::vector<std::string>::const_iterator i = _rowBuffer.begin();
337  i != _rowBuffer.end(); ++i) {
338  if (i != _rowBuffer.begin()) *_osp << '\t';
339  *_osp << *i;
340  }
341  // \todo Optimization: if columns all outputted, just put out endl
342  *_osp << std::endl;
343 }
344 
345 
346 // Forward template member functions to the base class.
347 
356 template <typename T>
357 void DbTsvStorage::outParam(std::string const& columnName, T* location,
358  bool isExpr) {
359  DbStorage::outParam<T>(columnName, location, isExpr);
360 }
361 
367 template <typename T>
368 void DbTsvStorage::condParam(std::string const& paramName, T const& value) {
369  DbStorage::condParam<T>(paramName, value);
370 }
371 
375 template <typename T>
376 T const& DbTsvStorage::getColumnByPos(int pos) {
377  return DbStorage::getColumnByPos<T>(pos);
378 }
379 
380 
381 // Explicit template member function instantiations.
382 // Ignore for doxygen processing.
384 template void DbTsvStorage::setColumn<>(std::string const& columnName, char const& value);
385 template void DbTsvStorage::setColumn<>(std::string const& columnName, short const& value);
386 template void DbTsvStorage::setColumn<>(std::string const& columnName, int const& value);
387 template void DbTsvStorage::setColumn<>(std::string const& columnName, long const& value);
388 template void DbTsvStorage::setColumn<>(std::string const& columnName, long long const& value);
389 template void DbTsvStorage::setColumn<>(std::string const& columnName, float const& value);
390 template void DbTsvStorage::setColumn<>(std::string const& columnName, double const& value);
391 template void DbTsvStorage::setColumn<>(std::string const& columnName, std::string const& value);
392 template void DbTsvStorage::setColumn<>(std::string const& columnName, bool const& value);
393 template void DbTsvStorage::setColumn<>(std::string const& columnName, DateTime const& value);
394 
395 template void DbTsvStorage::outParam<>(std::string const& columnName, char* location, bool isExpr);
396 template void DbTsvStorage::outParam<>(std::string const& columnName, short* location, bool isExpr);
397 template void DbTsvStorage::outParam<>(std::string const& columnName, int* location, bool isExpr);
398 template void DbTsvStorage::outParam<>(std::string const& columnName, long* location, bool isExpr);
399 template void DbTsvStorage::outParam<>(std::string const& columnName, long long* location, bool isExpr);
400 template void DbTsvStorage::outParam<>(std::string const& columnName, float* location, bool isExpr);
401 template void DbTsvStorage::outParam<>(std::string const& columnName, double* location, bool isExpr);
402 template void DbTsvStorage::outParam<>(std::string const& columnName, std::string* location, bool isExpr);
403 template void DbTsvStorage::outParam<>(std::string const& columnName, bool* location, bool isExpr);
404 template void DbTsvStorage::outParam<>(std::string const& columnName, DateTime* location, bool isExpr);
405 
406 template void DbTsvStorage::condParam<>(std::string const& paramName, char const& value);
407 template void DbTsvStorage::condParam<>(std::string const& paramName, short const& value);
408 template void DbTsvStorage::condParam<>(std::string const& paramName, int const& value);
409 template void DbTsvStorage::condParam<>(std::string const& paramName, long const& value);
410 template void DbTsvStorage::condParam<>(std::string const& paramName, long long const& value);
411 template void DbTsvStorage::condParam<>(std::string const& paramName, float const& value);
412 template void DbTsvStorage::condParam<>(std::string const& paramName, double const& value);
413 template void DbTsvStorage::condParam<>(std::string const& paramName, std::string const& value);
414 template void DbTsvStorage::condParam<>(std::string const& paramName, bool const& value);
415 template void DbTsvStorage::condParam<>(std::string const& paramName, DateTime const& value);
416 
417 template char const& DbTsvStorage::getColumnByPos<>(int pos);
418 template short const& DbTsvStorage::getColumnByPos<>(int pos);
419 template int const& DbTsvStorage::getColumnByPos<>(int pos);
420 template long const& DbTsvStorage::getColumnByPos<>(int pos);
421 template long long const& DbTsvStorage::getColumnByPos<>(int pos);
422 template float const& DbTsvStorage::getColumnByPos<>(int pos);
423 template double const& DbTsvStorage::getColumnByPos<>(int pos);
424 template std::string const& DbTsvStorage::getColumnByPos<>(int pos);
425 template bool const& DbTsvStorage::getColumnByPos<>(int pos);
426 template DateTime const& DbTsvStorage::getColumnByPos<>(int pos);
428 
429 }}} // namespace lsst::daf::persistence
Class for handling dates/times, including MJD, UTC, and TAI.
Definition: DateTime.h:58
int _getColumnIndex(std::string const &columnName)
virtual void setPersistLocation(LogicalLocation const &location)
Definition: DbTsvStorage.cc:91
std::vector< std::string > _rowBuffer
Definition: DbTsvStorage.h:108
Class for logical location of a persisted Persistable instance.
virtual std::string const & getPort(void) const
virtual void setPolicy(lsst::pex::policy::Policy::Ptr policy)
Definition: DbTsvStorage.cc:78
char * _fileName
Full pathname for temporary TSV file.
Definition: DbTsvStorage.h:103
virtual void setColumnToNull(std::string const &columnName)
boost::shared_ptr< Policy > Ptr
Definition: Policy.h:172
#define __attribute__(x)
Definition: DbTsvStorage.cc:41
std::string _location
Database location URL.
Definition: DbTsvStorage.h:104
virtual void dropTable(std::string const &tableName)
Definition: DbStorage.cc:121
Class for database storage.
Definition: DbStorage.h:63
void outParam(std::string const &columnName, T *location, bool isExpr=false)
std::map< std::string, int > _colMap
Map from column names to positions.
Definition: DbTsvStorage.h:106
std::ostringstream _convertStream
Stream to convert to text.
Definition: DbTsvStorage.h:107
virtual void startTransaction(void)
Definition: DbStorage.cc:95
std::string _tempPath
Directory pathname for temporary TSV file.
Definition: DbTsvStorage.h:102
Location of a persisted Persistable instance in a database.
virtual void truncateTable(std::string const &tableName)
bool _saveTemp
Do not delete temporary TSV file if true.
Definition: DbTsvStorage.h:101
boost::scoped_ptr< std::ofstream > _osp
Output TSV stream.
Definition: DbTsvStorage.h:109
std::string const & locString(void) const
Interface for DbTsvStorage class.
virtual std::string const & getPassword(void) const
virtual void setTableForInsert(std::string const &tableName)
virtual void setRetrieveLocation(LogicalLocation const &location)
Definition: DbStorage.cc:89
virtual void endTransaction(void)
Definition: DbStorage.cc:101
Interface for DateTime class.
virtual std::string const & getHostname(void) const
#define LSST_EXCEPT(type,...)
Definition: Exception.h:46
struct tm gmtime(void) const
Definition: DateTime.cc:492
virtual void createTableFromTemplate(std::string const &tableName, std::string const &templateName, bool mayAlreadyExist=false)
Interface for LogicalLocation class.
virtual void setPersistLocation(LogicalLocation const &location)
Definition: DbStorage.cc:82
void setColumn(std::string const &columnName, T const &value)
virtual void createTableFromTemplate(std::string const &tableName, std::string const &templateName, bool mayAlreadyExist=false)
Definition: DbStorage.cc:112
virtual std::string const & getDbName(void) const
virtual void setRetrieveLocation(LogicalLocation const &location)
void condParam(std::string const &paramName, T const &value)
virtual void dropTable(std::string const &tableName)
virtual std::string const & getUsername(void) const
Include files required for standard LSST Exception handling.
virtual void truncateTable(std::string const &tableName)
Definition: DbStorage.cc:128
Interface for DbStorageLocation class.