2 * Copyright 2001-2007 Internet2
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
8 * http://www.apache.org/licenses/LICENSE-2.0
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
20 * Storage Service using ODBC
23 #if defined (_MSC_VER) || defined(__BORLANDC__)
\r
24 # include "config_win32.h"
\r
26 # include "config.h"
\r
30 # define _CRT_NONSTDC_NO_DEPRECATE 1
\r
31 # define _CRT_SECURE_NO_DEPRECATE 1
\r
35 # define ODBCSTORE_EXPORTS __declspec(dllexport)
37 # define ODBCSTORE_EXPORTS
40 #include <log4cpp/Category.hh>
41 #include <xercesc/util/XMLUniDefs.hpp>
42 #include <xmltooling/XMLToolingConfig.h>
43 #include <xmltooling/util/NDC.h>
44 #include <xmltooling/util/StorageService.h>
45 #include <xmltooling/util/Threads.h>
46 #include <xmltooling/util/XMLHelper.h>
51 using namespace xmltooling;
52 using namespace xercesc;
53 using namespace log4cpp;
56 #define PLUGIN_VER_MAJOR 1
57 #define PLUGIN_VER_MINOR 0
59 #define LONGDATA_BUFLEN 16384
61 #define COLSIZE_KEY 255
62 #define COLSIZE_CONTEXT 255
63 #define COLSIZE_STRING_VALUE 255
65 #define STRING_TABLE "STRING_TABLE"
66 #define TEXT_TABLE "TEXT_TABLE"
68 /* tables definitions - not used here
70 #define STRING_TABLE \
71 "CREATE TABLE STRING_TABLE ( " \
72 "context varchar(255), " \
73 "key varchar(255), " \
74 "value varchar(255), " \
75 "expires datetime, " \
76 "version smallint, " \
77 "PRIMARY KEY (context, key)" \
82 "CREATE TABLE TEXT_TABLE ( "\
83 "context varchar(255), " \
84 "key varchar(255), " \
86 "expires datetime, " \
87 "version smallint, " \
88 "PRIMARY KEY (context, key)" \
93 static const XMLCh cleanupInterval[] = UNICODE_LITERAL_15(c,l,e,a,n,u,p,I,n,t,e,r,v,a,l);
94 static const XMLCh ConnectionString[] = UNICODE_LITERAL_16(C,o,n,n,e,c,t,i,o,n,S,t,r,i,n,g);
96 // RAII for ODBC handles
98 ODBCConn(SQLHDBC conn) : handle(conn) {}
100 SQLRETURN sr = SQLEndTran(SQL_HANDLE_DBC, handle, SQL_COMMIT);
101 SQLFreeHandle(SQL_HANDLE_DBC,handle);
102 if (!SQL_SUCCEEDED(sr))
103 throw IOException("Failed to commit connection.");
105 operator SQLHDBC() {return handle;}
109 struct ODBCStatement {
110 ODBCStatement(SQLHSTMT statement) : handle(statement) {}
111 ~ODBCStatement() {SQLFreeHandle(SQL_HANDLE_STMT,handle);}
112 operator SQLHSTMT() {return handle;}
116 class ODBCStorageService : public StorageService
119 ODBCStorageService(const DOMElement* e);
120 virtual ~ODBCStorageService();
122 void createString(const char* context, const char* key, const char* value, time_t expiration) {
123 return createRow(STRING_TABLE, context, key, value, expiration);
125 int readString(const char* context, const char* key, string* pvalue=NULL, time_t* pexpiration=NULL, int version=0) {
126 return readRow(STRING_TABLE, context, key, pvalue, pexpiration, version, false);
128 int updateString(const char* context, const char* key, const char* value=NULL, time_t expiration=0, int version=0) {
129 return updateRow(STRING_TABLE, context, key, value, expiration, version);
131 bool deleteString(const char* context, const char* key) {
132 return deleteRow(STRING_TABLE, context, key);
135 void createText(const char* context, const char* key, const char* value, time_t expiration) {
136 return createRow(TEXT_TABLE, context, key, value, expiration);
138 int readText(const char* context, const char* key, string* pvalue=NULL, time_t* pexpiration=NULL, int version=0) {
139 return readRow(TEXT_TABLE, context, key, pvalue, pexpiration, version, true);
141 int updateText(const char* context, const char* key, const char* value=NULL, time_t expiration=0, int version=0) {
142 return updateRow(TEXT_TABLE, context, key, value, expiration, version);
144 bool deleteText(const char* context, const char* key) {
145 return deleteRow(TEXT_TABLE, context, key);
148 void reap(const char* context) {
149 reap(STRING_TABLE, context);
150 reap(TEXT_TABLE, context);
153 void updateContext(const char* context, time_t expiration) {
154 updateContext(STRING_TABLE, context, expiration);
155 updateContext(TEXT_TABLE, context, expiration);
158 void deleteContext(const char* context) {
159 deleteContext(STRING_TABLE, context);
160 deleteContext(TEXT_TABLE, context);
165 void createRow(const char *table, const char* context, const char* key, const char* value, time_t expiration);
166 int readRow(const char *table, const char* context, const char* key, string* pvalue, time_t* pexpiration, int version, bool text);
167 int updateRow(const char *table, const char* context, const char* key, const char* value, time_t expiration, int version);
168 bool deleteRow(const char *table, const char* context, const char* key);
170 void reap(const char* table, const char* context);
171 void updateContext(const char* table, const char* context, time_t expiration);
172 void deleteContext(const char* table, const char* context);
175 SQLHSTMT getHSTMT(SQLHDBC);
176 pair<int,int> getVersion(SQLHDBC);
177 void log_error(SQLHANDLE handle, SQLSMALLINT htype);
179 static void* cleanup_fn(void*);
183 int m_cleanupInterval;
184 CondWait* shutdown_wait;
185 Thread* cleanup_thread;
192 StorageService* ODBCStorageServiceFactory(const DOMElement* const & e)
194 return new ODBCStorageService(e);
197 // convert SQL timestamp to time_t
198 time_t timeFromTimestamp(SQL_TIMESTAMP_STRUCT expires)
202 t.tm_sec=expires.second;
203 t.tm_min=expires.minute;
204 t.tm_hour=expires.hour;
205 t.tm_mday=expires.day;
206 t.tm_mon=expires.month-1;
207 t.tm_year=expires.year-1900;
209 #if defined(HAVE_TIMEGM)
212 ret = mktime(&t) - timezone;
217 // conver time_t to SQL string
218 void timestampFromTime(time_t t, char* ret)
222 struct tm* ptime=gmtime_r(&t,&res);
224 struct tm* ptime=gmtime(&t);
226 strftime(ret,32,"{ts '%Y-%m-%d %H:%M:%S'}",ptime);
229 // make a string safe for SQL command
230 // result to be free'd only if it isn't the input
231 static char *makeSafeSQL(const char *src)
237 // see if any conversion needed
238 for (s=(char*)src; *s; nc++,s++) if (*s=='\''||*s=='\\') ns++;
239 if (ns==0) return ((char*)src);
241 char *safe = new char[(nc+2*ns+1)];
242 for (s=safe; *src; src++) {
243 if (*src=='\''||*src=='\\') *s++ = '\\';
250 void freeSafeSQL(char *safe, const char *src)
257 ODBCStorageService::ODBCStorageService(const DOMElement* e) : m_log(Category::getInstance("XMLTooling.StorageService")),
258 m_cleanupInterval(900), shutdown_wait(NULL), cleanup_thread(NULL), shutdown(false), m_henv(SQL_NULL_HANDLE)
261 xmltooling::NDC ndc("ODBCStorageService");
264 const XMLCh* tag=e ? e->getAttributeNS(NULL,cleanupInterval) : NULL;
266 m_cleanupInterval = XMLString::parseInt(tag);
267 if (!m_cleanupInterval)
268 m_cleanupInterval = 900;
270 if (m_henv == SQL_NULL_HANDLE) {
271 // Enable connection pooling.
272 SQLSetEnvAttr(SQL_NULL_HANDLE, SQL_ATTR_CONNECTION_POOLING, (void*)SQL_CP_ONE_PER_HENV, 0);
274 // Allocate the environment.
275 if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv)))
276 throw XMLToolingException("ODBC failed to initialize.");
279 SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
281 m_log.info("ODBC initialized");
284 // Grab connection string from the configuration.
285 e = e ? XMLHelper::getFirstChildElement(e,ConnectionString) : NULL;
286 if (!e || !e->hasChildNodes()) {
287 SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
288 throw XMLToolingException("ODBC StorageService requires ConnectionString element in configuration.");
290 auto_ptr_char arg(e->getFirstChild()->getNodeValue());
291 m_connstring=arg.get();
293 // Connect and check version.
294 ODBCConn conn(getHDBC());
295 pair<int,int> v=getVersion(conn);
297 // Make sure we've got the right version.
298 if (v.first != PLUGIN_VER_MAJOR) {
299 SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
300 m_log.crit("unknown database version: %d.%d", v.first, v.second);
301 throw XMLToolingException("Unknown database version for ODBC StorageService.");
304 // Initialize the cleanup thread
305 shutdown_wait = CondWait::create();
306 cleanup_thread = Thread::create(&cleanup_fn, (void*)this);
309 ODBCStorageService::~ODBCStorageService()
312 shutdown_wait->signal();
313 cleanup_thread->join(NULL);
314 delete shutdown_wait;
315 SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
318 void ODBCStorageService::log_error(SQLHANDLE handle, SQLSMALLINT htype)
328 ret = SQLGetDiagRec(htype, handle, ++i, state, &native, text, sizeof(text), &len);
329 if (SQL_SUCCEEDED(ret))
330 m_log.error("ODBC Error: %s:%ld:%ld:%s", state, i, native, text);
331 } while(SQL_SUCCEEDED(ret));
334 SQLHDBC ODBCStorageService::getHDBC()
337 xmltooling::NDC ndc("getHDBC");
342 SQLRETURN sr=SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &handle);
343 if (!SQL_SUCCEEDED(sr)) {
344 m_log.error("failed to allocate connection handle");
345 log_error(m_henv, SQL_HANDLE_ENV);
346 throw IOException("ODBC StorageService failed to allocate a connection handle.");
349 sr=SQLDriverConnect(handle,NULL,(SQLCHAR*)m_connstring.c_str(),m_connstring.length(),NULL,0,NULL,SQL_DRIVER_NOPROMPT);
350 if (!SQL_SUCCEEDED(sr)) {
351 m_log.error("failed to connect to database");
352 log_error(handle, SQL_HANDLE_DBC);
353 throw IOException("ODBC StorageService failed to connect to database.");
356 sr = SQLSetConnectAttr(handle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, NULL);
357 if (!SQL_SUCCEEDED(sr))
358 throw IOException("ODBC StorageService failed to disable auto-commit mode.");
359 sr = SQLSetConnectAttr(handle, SQL_ATTR_TXN_ISOLATION, (SQLPOINTER)SQL_TXN_SERIALIZABLE, NULL);
360 if (!SQL_SUCCEEDED(sr))
361 throw IOException("ODBC StorageService failed to enable transaction isolation.");
366 SQLHSTMT ODBCStorageService::getHSTMT(SQLHDBC conn)
369 SQLRETURN sr=SQLAllocHandle(SQL_HANDLE_STMT,conn,&hstmt);
370 if (!SQL_SUCCEEDED(sr)) {
371 m_log.error("failed to allocate statement handle");
372 log_error(conn, SQL_HANDLE_DBC);
373 throw IOException("ODBC StorageService failed to allocate a statement handle.");
378 pair<int,int> ODBCStorageService::getVersion(SQLHDBC conn)
380 // Grab the version number from the database.
381 ODBCStatement stmt(getHSTMT(conn));
383 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)"SELECT major,minor FROM version", SQL_NTS);
384 if (!SQL_SUCCEEDED(sr)) {
385 m_log.error("failed to read version from database");
386 log_error(stmt, SQL_HANDLE_STMT);
387 throw IOException("ODBC StorageService failed to read version from database.");
392 SQLBindCol(stmt,1,SQL_C_SLONG,&major,0,NULL);
393 SQLBindCol(stmt,2,SQL_C_SLONG,&minor,0,NULL);
395 if ((sr=SQLFetch(stmt)) != SQL_NO_DATA)
396 return pair<int,int>(major,minor);
398 m_log.error("no rows returned in version query");
399 throw IOException("ODBC StorageService failed to read version from database.");
402 void ODBCStorageService::createRow(const char *table, const char* context, const char* key, const char* value, time_t expiration)
405 xmltooling::NDC ndc("createRow");
409 timestampFromTime(expiration, timebuf);
411 // Get statement handle.
412 ODBCConn conn(getHDBC());
413 ODBCStatement stmt(getHSTMT(conn));
415 // Prepare and exectute insert statement.
416 char *scontext = makeSafeSQL(context);
417 char *skey = makeSafeSQL(key);
418 char *svalue = makeSafeSQL(value);
419 string q = string("INSERT ") + table + " VALUES ('" + scontext + "','" + skey + "','" + svalue + "'," + timebuf + "', 1)";
420 freeSafeSQL(scontext, context);
421 freeSafeSQL(skey, key);
422 freeSafeSQL(svalue, value);
423 m_log.debug("SQL: %s", q.c_str());
425 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
426 if (!SQL_SUCCEEDED(sr)) {
427 m_log.error("insert record failed (t=%s, c=%s, k=%s)", table, context, key);
428 log_error(stmt, SQL_HANDLE_STMT);
429 throw IOException("ODBC StorageService failed to insert record.");
433 int ODBCStorageService::readRow(
434 const char *table, const char* context, const char* key, string* pvalue, time_t* pexpiration, int version, bool text
438 xmltooling::NDC ndc("readRow");
441 SQLCHAR *tvalue = NULL;
443 // Get statement handle.
444 ODBCConn conn(getHDBC());
445 ODBCStatement stmt(getHSTMT(conn));
447 // Prepare and exectute select statement.
448 char *scontext = makeSafeSQL(context);
449 char *skey = makeSafeSQL(key);
450 string q("SELECT version");
455 q = q + " FROM " + table + " WHERE context='" + scontext + "' AND key='" + skey + "' AND expires > NOW()";
456 freeSafeSQL(scontext, context);
457 freeSafeSQL(skey, key);
458 m_log.debug("SQL: %s", q.c_str());
460 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
461 if (!SQL_SUCCEEDED(sr)) {
462 m_log.error("error searching for (t=%s, c=%s, k=%s)", table, context, key);
463 log_error(stmt, SQL_HANDLE_STMT);
464 throw IOException("ODBC StorageService search failed.");
468 SQL_TIMESTAMP_STRUCT expiration;
470 SQLBindCol(stmt,1,SQL_C_SSHORT,&ver,0,NULL);
472 SQLBindCol(stmt,2,SQL_C_TYPE_TIMESTAMP,&expiration,0,NULL);
474 if ((sr=SQLFetch(stmt)) == SQL_NO_DATA)
478 *pexpiration = timeFromTimestamp(expiration);
481 return version; // nothing's changed, so just echo back the version
485 SQLCHAR buf[LONGDATA_BUFLEN];
\r
486 while ((sr=SQLGetData(stmt,pexpiration ? 3 : 2,SQL_C_CHAR,buf,sizeof(buf),&len)) != SQL_NO_DATA) {
\r
487 if (!SQL_SUCCEEDED(sr)) {
\r
488 m_log.error("error while reading text field from result set");
\r
489 log_error(stmt, SQL_HANDLE_STMT);
\r
490 throw IOException("ODBC StorageService search failed to read data from result set.");
\r
492 pvalue->append((char*)buf);
\r
499 int ODBCStorageService::updateRow(const char *table, const char* context, const char* key, const char* value, time_t expiration, int version)
502 xmltooling::NDC ndc("updateRow");
505 if (!value && !expiration)
506 throw IOException("ODBC StorageService given invalid update instructions.");
508 // Get statement handle.
509 ODBCConn conn(getHDBC());
510 ODBCStatement stmt(getHSTMT(conn));
512 // First, fetch the current version for later, which also ensures the record still exists.
513 char *scontext = makeSafeSQL(context);
514 char *skey = makeSafeSQL(key);
515 string q("SELECT version FROM ");
516 q = q + table + " WHERE context='" + scontext + "' AND key='" + key + "' AND expires > NOW()";
518 m_log.debug("SQL: %s", q.c_str());
520 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
521 if (!SQL_SUCCEEDED(sr)) {
522 freeSafeSQL(scontext, context);
523 freeSafeSQL(skey, key);
524 m_log.error("error searching for (t=%s, c=%s, k=%s)", table, context, key);
525 log_error(stmt, SQL_HANDLE_STMT);
526 throw IOException("ODBC StorageService search failed.");
530 SQLBindCol(stmt,1,SQL_C_SSHORT,&ver,0,NULL);
531 if ((sr=SQLFetch(stmt)) == SQL_NO_DATA) {
532 freeSafeSQL(scontext, context);
533 freeSafeSQL(skey, key);
538 if (version > 0 && version != ver) {
539 freeSafeSQL(scontext, context);
540 freeSafeSQL(skey, key);
544 // Prepare and exectute update statement.
545 q = string("UPDATE ") + table + " SET ";
548 char *svalue = makeSafeSQL(value);
549 q = q + "value='" + svalue + "'" + ",version=version+1";
550 freeSafeSQL(svalue, value);
555 timestampFromTime(expiration, timebuf);
558 q = q + "expires = '" + timebuf + "' ";
561 q = q + " WHERE context='" + scontext + "' AND key='" + key + "'";
562 freeSafeSQL(scontext, context);
563 freeSafeSQL(skey, key);
565 m_log.debug("SQL: %s", q.c_str());
566 sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
568 return 0; // went missing?
569 else if (!SQL_SUCCEEDED(sr)) {
570 m_log.error("update of record failed (t=%s, c=%s, k=%s", table, context, key);
571 log_error(stmt, SQL_HANDLE_STMT);
572 throw IOException("ODBC StorageService failed to update record.");
578 bool ODBCStorageService::deleteRow(const char *table, const char *context, const char* key)
581 xmltooling::NDC ndc("deleteRow");
584 // Get statement handle.
585 ODBCConn conn(getHDBC());
586 ODBCStatement stmt(getHSTMT(conn));
588 // Prepare and execute delete statement.
589 char *scontext = makeSafeSQL(context);
590 char *skey = makeSafeSQL(key);
591 string q = string("DELETE FROM ") + table + " WHERE context='" + scontext + "' AND key='" + skey + "'";
592 freeSafeSQL(scontext, context);
593 freeSafeSQL(skey, key);
594 m_log.debug("SQL: %s", q.c_str());
596 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
599 else if (!SQL_SUCCEEDED(sr)) {
600 m_log.error("error deleting record (t=%s, c=%s, k=%s)", table, context, key);
601 log_error(stmt, SQL_HANDLE_STMT);
602 throw IOException("ODBC StorageService failed to delete record.");
609 void ODBCStorageService::cleanup()
612 xmltooling::NDC ndc("cleanup");
615 Mutex* mutex = Mutex::create();
619 m_log.info("cleanup thread started... running every %d secs", m_cleanupInterval);
622 shutdown_wait->timedwait(mutex, m_cleanupInterval);
628 catch (exception& ex) {
629 m_log.error("cleanup thread swallowed exception: %s", ex.what());
633 m_log.info("cleanup thread exiting...");
640 void* ODBCStorageService::cleanup_fn(void* cache_p)
642 ODBCStorageService* cache = (ODBCStorageService*)cache_p;
645 // First, let's block all signals
646 Thread::mask_all_signals();
649 // Now run the cleanup process.
654 void ODBCStorageService::updateContext(const char *table, const char* context, time_t expiration)
657 xmltooling::NDC ndc("updateContext");
660 // Get statement handle.
661 ODBCConn conn(getHDBC());
662 ODBCStatement stmt(getHSTMT(conn));
665 timestampFromTime(expiration, timebuf);
667 char *scontext = makeSafeSQL(context);
669 q = q + table + " SET expires = '" + timebuf + "' WHERE context='" + scontext + "' AND expires > NOW()";
670 freeSafeSQL(scontext, context);
672 m_log.debug("SQL: %s", q.c_str());
674 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
675 if ((sr!=SQL_NO_DATA) && !SQL_SUCCEEDED(sr)) {
676 m_log.error("error updating records (t=%s, c=%s)", table, context ? context : "all");
677 log_error(stmt, SQL_HANDLE_STMT);
678 throw IOException("ODBC StorageService failed to update context expiration.");
682 void ODBCStorageService::reap(const char *table, const char* context)
685 xmltooling::NDC ndc("reap");
688 // Get statement handle.
689 ODBCConn conn(getHDBC());
690 ODBCStatement stmt(getHSTMT(conn));
692 // Prepare and execute delete statement.
695 char *scontext = makeSafeSQL(context);
696 q = string("DELETE FROM ") + table + " WHERE context='" + scontext + "' AND expires <= NOW()";
697 freeSafeSQL(scontext, context);
700 q = string("DELETE FROM ") + table + " WHERE expires <= NOW()";
702 m_log.debug("SQL: %s", q.c_str());
704 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
705 if ((sr!=SQL_NO_DATA) && !SQL_SUCCEEDED(sr)) {
706 m_log.error("error expiring records (t=%s, c=%s)", table, context ? context : "all");
707 log_error(stmt, SQL_HANDLE_STMT);
708 throw IOException("ODBC StorageService failed to purge expired records.");
712 void ODBCStorageService::deleteContext(const char *table, const char* context)
715 xmltooling::NDC ndc("deleteContext");
718 // Get statement handle.
719 ODBCConn conn(getHDBC());
720 ODBCStatement stmt(getHSTMT(conn));
722 // Prepare and execute delete statement.
723 char *scontext = makeSafeSQL(context);
724 string q = string("DELETE FROM ") + table + " WHERE context='" + scontext + "'";
725 freeSafeSQL(scontext, context);
726 m_log.debug("SQL: %s", q.c_str());
728 SQLRETURN sr=SQLExecDirect(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);
729 if ((sr!=SQL_NO_DATA) && !SQL_SUCCEEDED(sr)) {
730 m_log.error("error deleting context (t=%s, c=%s)", table, context);
731 log_error(stmt, SQL_HANDLE_STMT);
732 throw IOException("ODBC StorageService failed to delete context.");
736 extern "C" int ODBCSTORE_EXPORTS xmltooling_extension_init(void*)
\r
738 // Register this SS type
\r
739 XMLToolingConfig::getConfig().StorageServiceManager.registerFactory("ODBC", ODBCStorageServiceFactory);
\r
743 extern "C" void ODBCSTORE_EXPORTS xmltooling_extension_term()
\r
745 XMLToolingConfig::getConfig().StorageServiceManager.deregisterFactory("ODBC");
\r