41 # define __attribute__(x) 46 #include "boost/regex.hpp" 57 #include <mysql/mysql.h> 76 namespace persistence {
91 template<> enum_field_types
93 template<> enum_field_types
95 template<> enum_field_types
97 template<> enum_field_types
100 template <
typename N> enum_field_types
104 template<> enum_field_types
120 template<> enum_field_types
124 template<> enum_field_types
128 template<> enum_field_types
132 template<> enum_field_types
145 lsst::daf::
base::Citizen(typeid(*this)), _data(0) {
198 setenv(
"TZ",
"UTC", 1);
207 unsigned int port = strtoul(dbloc.
getPort().
c_str(), 0, 10);
208 if (mysql_real_connect(
_db,
237 if (
_db == 0)
error(
"Database session not initialized " 238 "in DbStorage::startTransaction()",
false);
239 if (mysql_autocommit(
_db,
false))
error(
"Unable to turn off autocommit");
245 if (
_db == 0)
error(
"Database session not initialized " 246 "in DbStorage::endTransaction()",
false);
247 if (mysql_commit(
_db))
error(
"Unable to commit transaction");
248 if (mysql_autocommit(
_db,
true))
error(
"Unable to turn on autocommit");
259 error(
"No DB connection for query: " + query,
false);
262 if (mysql_query(
_db, query.
c_str()) != 0) {
263 error(
"Unable to execute query: " + query);
270 std::string::size_type pos = name.
find(
'.');
271 if (pos == std::string::npos)
return '`' + name +
'`';
290 boost::shared_array<char> mem(
new char[size]);
306 bool mayAlreadyExist) {
308 if (mayAlreadyExist) query +=
"IF NOT EXISTS ";
309 query +=
quote(tableName) +
" LIKE " +
quote(templateName);
344 error(
"Attempt to insert into read-only database",
false);
354 template <
typename T>
358 size_t size =
sizeof(T);
361 BoundVarMap::value_type(columnName,
364 else if (bv->second._length != size) {
368 bv->second._isNull =
false;
370 bv->second._length = size;
371 memcpy(bv->second._data, &value, size);
378 size_t size = value.
length();
381 BoundVarMap::value_type(columnName,
384 else if (bv->second._length != size) {
388 bv->second._isNull =
false;
390 bv->second._length = size;
391 memcpy(bv->second._data, value.
data(), size);
398 size_t size =
sizeof(MYSQL_TIME);
401 BoundVarMap::value_type(columnName,
404 else if (bv->second._length != size) {
408 bv->second._isNull =
false;
410 bv->second._length = size;
412 MYSQL_TIME* t =
reinterpret_cast<MYSQL_TIME*
>(bv->second._data);
413 t->year = v.tm_year + 1900;
414 t->month = v.tm_mon + 1;
417 t->minute = v.tm_min;
418 t->second = v.tm_sec;
421 static_cast<unsigned long>((value.
nsecs() % 1000000000
LL) / 1000);
431 BoundVarMap::value_type(columnName,
434 bv->second._isNull =
true;
435 bv->second._length = 1;
443 error(
"Attempt to insert into read-only database",
false);
459 query +=
quote(it->first);
462 MYSQL_BIND& bind(binder[i]);
465 bind.buffer_type = MYSQL_TYPE_NULL;
468 bind.buffer_type = bv.
_type;
469 bind.buffer = bv.
_data;
470 bind.buffer_length = bv.
_length;
478 query +=
") VALUES (";
491 error(
"Unable to initialize statement: " + query);
494 stError(
"Unable to prepare statement: " + query);
496 if (mysql_stmt_bind_param(
_statement, binder.get())) {
497 stError(
"Unable to bind variables in: " + query);
500 stError(
"Unable to execute statement: " + query);
516 if (
_db == 0)
error(
"Database session not initialized in DbStorage::setTableForQuery()",
false);
533 if (
_db == 0)
error(
"Database session not initialized in DbStorage::setTableListForQuery()",
false);
535 it != tableNameList.
end(); ++it) {
568 template <
typename T>
570 T* location,
bool isExpr) {
573 size_t size =
sizeof(T);
575 BoundVarMap::value_type(col,
BoundVar(location)));
577 error(
"Duplicate column name requested: " + columnName,
false);
593 BoundVarMap::value_type(
596 error(
"Duplicate column name requested: " + columnName,
false);
612 size_t size =
sizeof(MYSQL_TIME);
614 BoundVarMap::value_type(
617 error(
"Duplicate column name requested: " + columnName,
false);
632 template <
typename T>
634 setColumn<T>(paramName, value);
698 boost::regex re(
":([A-Za-z_]+)");
701 boost::regex_iterator<std::string::iterator>
m;
702 for (boost::regex_iterator<std::string::iterator> i(
704 i != boost::regex_iterator<std::string::iterator>(); ++i) {
706 std::copy(m->prefix().first, m->prefix().second, out);
708 assert(m->size() == 2);
711 if (m != boost::regex_iterator<std::string::iterator>()) {
712 std::copy(m->suffix().first, m->suffix().second, out);
717 query +=
" WHERE " + result;
730 new MYSQL_BIND[whereBindings.
size()]);
731 memset(inBinder.get(), 0, whereBindings.
size() *
sizeof(MYSQL_BIND));
732 for (
size_t i = 0; i < whereBindings.
size(); ++i) {
733 MYSQL_BIND& bind(inBinder[i]);
736 error(
"Unbound variable in WHERE clause: " + whereBindings[i],
740 bind.buffer_type = bv.
_type;
741 bind.buffer = bv.
_data;
742 bind.buffer_length = bv.
_length;
753 error(
"Unable to initialize prepared statement");
757 stError(
"Unable to prepare statement: " + query);
762 unsigned int params = mysql_stmt_param_count(
_statement);
765 error(
"Unbound WHERE clause parameters: " + query,
false);
769 if (params != whereBindings.
size()) {
770 error(
"Mismatch in number of WHERE clause parameters: " + query,
773 if (mysql_stmt_bind_param(
_statement, inBinder.get())) {
774 stError(
"Unable to bind WHERE parameters: " + query);
779 MYSQL_RES* queryMetadata = mysql_stmt_result_metadata(
_statement);
780 if (!queryMetadata) {
781 stError(
"No query metadata: " + query);
785 error(
"Mismatch in number of SELECT items: " + query,
false);
792 stError(
"MySQL query failed: " + query);
801 memset(outBinder.
get(), 0, _numResultFields *
sizeof(MYSQL_BIND));
806 MYSQL_BIND& bind(outBinder[i]);
808 bind.buffer_type = MYSQL_TYPE_STRING;
810 bind.buffer_length = 0;
813 bind.is_unsigned = (
_resultFields[i].flags & UNSIGNED_FLAG) != 0;
824 bind.buffer_type = bv.
_type;
826 bind.buffer =
reinterpret_cast<char*
>(bv.
_data) +
830 bind.buffer =
reinterpret_cast<char*
>(bv.
_data) +
834 bind.buffer = bv.
_data;
836 bind.buffer_length = bv.
_length;
844 stError(
"Unable to bind results: " + query);
853 error(
"Statement not initialized in DbStorage::next()",
false);
862 error(
"Unbound variable in SELECT clause: " +
873 char* cp =
reinterpret_cast<char*
>(bv.
_data) +
875 MYSQL_TIME* t =
reinterpret_cast<MYSQL_TIME*
>(cp);
878 t->hour, t->minute, t->second,
885 if (ret == MYSQL_NO_DATA)
return false;
887 stError(
"Error fetching next row");
895 template <
typename T>
899 os <<
"Nonexistent column: " << pos;
903 memset(&bind, 0,
sizeof(MYSQL_BIND));
908 bind.buffer_length =
sizeof(T);
911 if (mysql_stmt_fetch_column(
_statement, &bind, pos, 0)) {
913 os <<
"Error fetching column: " << pos;
923 os <<
"Nonexistent column: " << pos;
927 memset(&bind, 0,
sizeof(MYSQL_BIND));
929 error(
"Invalid type for string retrieval",
false);
934 bind.buffer = t.
get();
938 if (mysql_stmt_fetch_column(
_statement, &bind, pos, 0)) {
940 os <<
"Error fetching string column: " << pos;
952 os <<
"Nonexistent column: " << pos;
956 memset(&bind, 0,
sizeof(MYSQL_BIND));
961 error(
"Invalid type for DateTime retrieval",
false);
967 bind.buffer_length =
sizeof(MYSQL_TIME);
970 if (mysql_stmt_fetch_column(
_statement, &bind, pos, 0)) {
972 os <<
"Error fetching DateTime column: " << pos;
988 os <<
"Nonexistent column: " << pos;
1005 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
char const& value);
1006 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
short const& value);
1007 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
int const& value);
1008 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
long const& value);
1009 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
long long const& value);
1010 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
float const& value);
1011 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
double const& value);
1012 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
std::string const& value);
1013 template void dafPer::DbStorageImpl::setColumn<>(
std::string const& columnName,
bool const& value);
1016 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
char* location,
bool isExpr);
1017 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
short* location,
bool isExpr);
1018 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
int* location,
bool isExpr);
1019 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
long* location,
bool isExpr);
1020 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
long long* location,
bool isExpr);
1021 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
float* location,
bool isExpr);
1022 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
double* location,
bool isExpr);
1023 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
std::string* location,
bool isExpr);
1024 template void dafPer::DbStorageImpl::outParam<>(
std::string const& columnName,
bool* location,
bool isExpr);
1027 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
char const& value);
1028 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
short const& value);
1029 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
int const& value);
1030 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
long const& value);
1031 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
long long const& value);
1032 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
float const& value);
1033 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
double const& value);
1034 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
std::string const& value);
1035 template void dafPer::DbStorageImpl::condParam<>(
std::string const& paramName,
bool const& value);
1038 template char const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1039 template short const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1040 template int const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1041 template long const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1042 template long long const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1043 template float const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1044 template double const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1045 template std::string const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1046 template bool const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
1047 template dafBase::DateTime const& dafPer::DbStorageImpl::getColumnByPos<>(
int pos);
std::shared_ptr< Policy > Ptr
std::vector< boost::shared_array< char > > _bindingMemory
Memory for bound variables.
virtual void setQueryWhere(std::string const &whereClause)
Set the condition for the WHERE clause of the query.
T const & getColumnByPos(int pos)
Get the value of a column of the query result row by position.
DbStorageImpl(void)
Default constructor.
Class for handling dates/times, including MJD, UTC, and TAI.
virtual std::string const & getDbName(void) const
Accessor for database name.
table::Key< std::string > name
virtual void setTableForInsert(std::string const &tableName)
Set the table to insert rows into.
void outParam(std::string const &columnName, T *location, bool isExpr)
Request a column in the query output and bind a destination location.
void setColumn(std::string const &columnName, T const &value)
Set the value to insert in a given column.
std::vector< std::string > _queryTables
Names of tables to select from.
struct tm gmtime(Timescale scale) const
Get date as a tm struct, with truncated fractional seconds.
virtual void executeSql(std::string const &sqlStatement)
Execute an arbitrary SQL statement.
Class for logical location of a persisted Persistable instance.
virtual void endTransaction(void)
End a transaction.
virtual void setPolicy(pexPolicy::Policy::Ptr policy)
Allow a Policy to be used to configure the DbStorage.
std::string const & locString(void) const
Accessor.
boost::shared_array< unsigned long > _fieldLengths
Space for lengths of result fields.
virtual void finishQuery(void)
Indicate that query processing is finished.
virtual void outColumn(std::string const &columnName, bool isExpr)
Request a column in the query output.
virtual std::string const & getHostname(void) const
Accessor for database hostname.
int _numResultFields
Number of result fields.
virtual void groupBy(std::string const &expression)
Request that the query output be grouped by an expression.
void * allocateMemory(size_t size)
virtual void startSession(std::string const &location)
Start a database session.
virtual std::string const & getPassword(void) const
Accessor for password.
BoundVar(void)
Default constructor.
virtual ~DbStorageImpl(void)
Destructor.
LSST DM logging module built on log4cxx.
Include files required for standard LSST Exception handling.
Location of a persisted Persistable instance in a database.
MYSQL * _db
MySQL database connection pointer.
virtual void startTransaction(void)
Start a transaction.
#define LOGLS_DEBUG(logger, message)
Log a debug-level message using an iostream-based interface.
A base class for image defects.
virtual std::string const & getUsername(void) const
Accessor for username.
long long nsecs(Timescale scale=TAI) const
Get date as nanoseconds since the unix epoch.
void error(std::string const &text, bool mysqlCaused=true)
virtual std::string const & getPort(void) const
Accessor for database port number.
bool columnIsNull(int pos)
Determine if the value of a column is NULL.
virtual void setRetrieveLocation(LogicalLocation const &location)
Set the database location to retrieve from.
MYSQL_STMT * _statement
Prepared query statement.
void condParam(std::string const ¶mName, T const &value)
Bind a value to a WHERE condition parameter.
boost::shared_array< my_bool > _fieldNulls
Space for null flags of result fields.
virtual void query(void)
Execute the query.
Interface for DateTime class.
virtual void setTableListForQuery(std::vector< std::string > const &tableNameList)
Set a list of tables to query (multiple-table queries).
MYSQL_FIELD * _resultFields
Query result field metadata.
virtual void orderBy(std::string const &expression)
Request that the query output be sorted by an expression.
virtual void truncateTable(std::string const &tableName)
Truncate a table.
std::string _location
Database location string saved for use by raw MySQL interface.
#define LSST_EXCEPT(type,...)
Create an exception with a given type and message and optionally other arguments (dependent on the ty...
static enum_field_types mysqlType
virtual void setColumnToNull(std::string const &columnName)
Set a given column to NULL.
Citizen(const std::type_info &)
std::string quote(std::string const &name)
Quote a name in ANSI-standard fashion.
virtual void createTableFromTemplate(std::string const &tableName, std::string const &templateName, bool mayAlreadyExist)
Create a new table from an existing template table.
virtual bool next(void)
Move to the next (first) row of the query result.
void stError(std::string const &text)
BoundVarMap _inputVars
Input variable bindings.
Interface for DbStorageImpl class.
BoundVarMap _outputVars
Output variable bindings.
static enum_field_types mysqlType
virtual void setPersistLocation(LogicalLocation const &location)
Set the database location to persist to.
void executeQuery(std::string const &query)
Execute a query string.
virtual void dropTable(std::string const &tableName)
Drop a table.
#define LOG_GET(logger)
Returns a Log object associated with logger.
Interface for LogicalLocation class.
bool _readonly
Remember if we are supposed to be read-only.
std::vector< std::string > _outColumns
virtual void insertRow(void)
Insert the row.
std::string _insertTable
Name of table into which to insert.
Interface for DbStorageLocation class.
virtual void setTableForQuery(std::string const &tableName, bool isExpr)
Set the table to query (single-table queries only).