21#include <boost/locale.hpp>
23#include <nanodbc/nanodbc.h>
28#define UINT64 uint64_t
65nanodbc::string
fromUTF8(
const std::string& aString )
67 return boost::locale::conv::utf_to_utf<nanodbc::string::value_type>( aString );
76std::string
toUTF8(
const nanodbc::string& aString )
78 return boost::locale::conv::utf_to_utf<char>( aString );
83 const std::string& aUsername,
84 const std::string& aPassword,
int aTimeoutSeconds,
88 m_dsn = aDataSourceName;
101 int aTimeoutSeconds,
bool aConnectNow ) :
123 m_cache = std::make_unique<DB_CACHE_TYPE>( 10, 1 );
138 m_cache->SetMaxSize(
static_cast<size_t>( aMaxSize ) );
139 m_cache->SetMaxAge(
static_cast<time_t
>( aMaxAge ) );
155 m_conn = std::make_unique<nanodbc::connection>( dsn, user, pass,
m_timeout );
159 wxLogTrace(
traceDatabase, wxT(
"Creating connection with connection string" ) );
163 catch( nanodbc::database_error& e )
182 wxLogTrace(
traceDatabase, wxT(
"Note: Disconnect() called without valid connection" ) );
190 catch( boost::locale::conv::conversion_error& exc )
192 wxLogTrace(
traceDatabase, wxT(
"Disconnect() error \"%s\" occured." ), exc.what() );
196 return !
m_conn->connected();
205 return m_conn->connected();
210 const std::set<std::string>& aColumns )
217 nanodbc::catalog catalog( *
m_conn );
218 nanodbc::catalog::tables tables = catalog.find_tables(
fromUTF8( aTable ) );
222 wxLogTrace(
traceDatabase, wxT(
"CacheTableInfo: table '%s' not found in catalog" ),
227 std::string key =
toUTF8( tables.table_name() );
232 nanodbc::catalog::columns columns =
233 catalog.find_columns( NANODBC_TEXT(
"" ), tables.table_name() );
235 while( columns.next() )
237 std::string columnKey =
toUTF8( columns.column_name() );
239 if( aColumns.count( columnKey ) )
244 catch( nanodbc::database_error& e )
247 wxLogTrace(
traceDatabase, wxT(
"Exception while syncing columns for table '%s': %s" ),
252 catch( nanodbc::database_error& e )
270 nanodbc::string qc =
m_conn->get_info<nanodbc::string>( SQL_IDENTIFIER_QUOTE_CHAR );
279 catch( nanodbc::database_error& )
293 wxLogTrace(
traceDatabase, wxT(
"columnsFor: requested table %s missing from cache!" ),
300 wxLogTrace(
traceDatabase, wxT(
"columnsFor: requested table %s has no columns mapped!" ),
307 for(
const auto& [ columnName, columnType ] :
m_columnCache[aTable] )
311 ret.resize( ret.length() - 2 );
319 const std::pair<std::string, std::string>& aWhere,
324 wxLogTrace(
traceDatabase, wxT(
"Called SelectOne without valid connection!" ) );
328 auto tableMapIter =
m_tables.find( aTable );
330 if( tableMapIter ==
m_tables.end() )
332 wxLogTrace(
traceDatabase, wxT(
"SelectOne: requested table %s not found in cache" ),
337 const std::string& tableName = tableMapIter->first;
340 if(
m_cache->Get( tableName, cacheEntry ) )
342 if( cacheEntry.count( aWhere.second ) )
344 wxLogTrace(
traceDatabase, wxT(
"SelectOne: `%s` with parameter `%s` - cache hit" ),
345 tableName, aWhere.second );
346 aResult = cacheEntry.at( aWhere.second );
353 wxLogTrace(
traceDatabase, wxT(
"SelectOne: requested table %s missing from column cache" ),
358 auto columnCacheIter =
m_columnCache.at( tableName ).find( aWhere.first );
362 wxLogTrace(
traceDatabase, wxT(
"SelectOne: requested column %s not found in cache for %s" ),
363 aWhere.first, tableName );
367 const std::string& columnName = columnCacheIter->first;
369 std::string cacheKey = fmt::format(
"{}{}{}", tableName, columnName, aWhere.second );
371 std::string queryStr = fmt::format(
"SELECT {} FROM {}{}{} WHERE {}{}{} = ?",
376 nanodbc::statement statement( *
m_conn );
377 nanodbc::string query =
fromUTF8( queryStr );
383 statement.prepare( query );
384 statement.bind( 0, aWhere.second.c_str() );
386 catch( nanodbc::database_error& e )
389 wxLogTrace(
traceDatabase, wxT(
"Exception while preparing statement for SelectOne: %s" ),
401 nanodbc::result results;
405 results = nanodbc::execute( statement );
407 catch( nanodbc::database_error& e )
410 wxLogTrace(
traceDatabase, wxT(
"Exception while executing statement for SelectOne: %s" ),
422 if( !results.first() )
424 wxLogTrace(
traceDatabase, wxT(
"SelectOne: no results returned from query" ) );
428 wxLogTrace(
traceDatabase, wxT(
"SelectOne: %ld results returned from query in %0.1f ms" ),
429 results.rows(), timer.
msecs() );
435 for(
short i = 0; i < results.columns(); ++i )
437 std::string column =
toUTF8( results.column_name( i ) );
439 switch( results.column_datatype( i ) )
449 aResult[column] = fmt::format(
"{:G}", results.get<
double>( i ) );
451 catch( nanodbc::null_access_error& )
454 aResult[column] = std::string();
461 aResult[column] =
toUTF8( results.get<nanodbc::string>( i, NANODBC_TEXT(
"" ) ) );
465 catch( nanodbc::database_error& e )
468 wxLogTrace(
traceDatabase, wxT(
"Exception while parsing results from SelectOne: %s" ),
478 std::vector<ROW>& aResults )
482 wxLogTrace(
traceDatabase, wxT(
"Called SelectAll without valid connection!" ) );
486 auto tableMapIter =
m_tables.find( aTable );
488 if( tableMapIter ==
m_tables.end() )
490 wxLogTrace(
traceDatabase, wxT(
"SelectAll: requested table %s not found in cache" ),
497 if(
m_cache->Get( aTable, cacheEntry ) )
499 wxLogTrace(
traceDatabase, wxT(
"SelectAll: `%s` - cache hit" ), aTable );
501 aResults.reserve( cacheEntry.size() );
503 for(
auto &[ key, row ] : cacheEntry )
504 aResults.emplace_back( row );
509 nanodbc::statement statement( *
m_conn );
511 nanodbc::string query =
fromUTF8( fmt::format(
"SELECT {} FROM {}{}{}",
columnsFor( aTable ),
520 statement.prepare( query );
522 catch( nanodbc::database_error& e )
525 wxLogTrace(
traceDatabase, wxT(
"Exception while preparing query for SelectAll: %s" ),
534 nanodbc::result results;
538 results = nanodbc::execute( statement );
540 catch( nanodbc::database_error& e )
543 wxLogTrace(
traceDatabase, wxT(
"Exception while executing query for SelectAll: %s" ),
554 auto handleException =
555 [&]( std::runtime_error& aException,
const std::string& aExtraContext =
"" )
558 std::string extra = aExtraContext.empty() ?
"" :
": " + aExtraContext;
560 wxT(
"Exception while parsing result %d from SelectAll: %s%s" ),
564 while( results.next() )
566 short columnCount = 0;
571 columnCount = results.columns();
573 catch( nanodbc::database_error& e )
575 handleException( e );
579 for(
short j = 0; j < columnCount; ++j )
582 std::string columnExtraDbgInfo;
583 int datatype = SQL_UNKNOWN_TYPE;
587 column =
toUTF8( results.column_name( j ) );
588 datatype = results.column_datatype( j );
589 columnExtraDbgInfo = fmt::format(
"column index {}, name '{}', type {}", j, column,
592 catch( nanodbc::index_range_error& e )
594 handleException( e, columnExtraDbgInfo );
608 result[column] = fmt::format(
"{:G}", results.get<
double>( j ) );
610 catch( nanodbc::null_access_error& )
613 result[column] = std::string();
615 catch( std::runtime_error& e )
617 handleException( e, columnExtraDbgInfo );
627 result[column] =
toUTF8( results.get<nanodbc::string>( j,
628 NANODBC_TEXT(
"" ) ) );
630 catch( std::runtime_error& e )
632 handleException( e, columnExtraDbgInfo );
639 aResults.emplace_back( std::move( result ) );
642 wxLogTrace(
traceDatabase, wxT(
"SelectAll from %s completed in %0.1f ms" ), aTable,
645 for(
const ROW& row : aResults )
647 wxASSERT( row.count( aKey ) );
648 std::string keyStr = std::any_cast<std::string>( row.at( aKey ) );
649 cacheEntry[keyStr] = row;
652 m_cache->Put( aTable, cacheEntry );
CacheValueType CACHE_VALUE
bool CacheTableInfo(const std::string &aTable, const std::set< std::string > &aColumns)
std::string m_connectionString
std::map< std::string, std::any > ROW
std::map< std::string, std::map< std::string, int > > m_columnCache
Map of table -> map of column name -> data type.
void SetCacheParams(int aMaxSize, int aMaxAge)
bool SelectAll(const std::string &aTable, const std::string &aKey, std::vector< ROW > &aResults)
Retrieves all rows from a database table.
DATABASE_CONNECTION(const std::string &aDataSourceName, const std::string &aUsername, const std::string &aPassword, int aTimeoutSeconds=DEFAULT_TIMEOUT, bool aConnectNow=true)
std::unique_ptr< DB_CACHE_TYPE > m_cache
std::string columnsFor(const std::string &aTable)
std::unique_ptr< nanodbc::connection > m_conn
bool SelectOne(const std::string &aTable, const std::pair< std::string, std::string > &aWhere, ROW &aResult)
Retrieves a single row from a database table.
std::map< std::string, std::string > m_tables
A small class to help profiling.
void Stop()
Save the time when this function was called, and set the counter stane to stop.
double msecs(bool aSinceLast=false)
const char *const traceDatabase
std::string toUTF8(const nanodbc::string &aString)
Converts a string from nanodbc-native to KiCad-native.
nanodbc::string fromUTF8(const std::string &aString)
When Unicode support is enabled in nanodbc, string formats are used matching the appropriate characte...
const char *const traceDatabase
static bool empty(const wxTextEntryBase *aCtrl)