KiCad PCB EDA Suite
Loading...
Searching...
No Matches
database_connection.cpp
Go to the documentation of this file.
1/*
2 * This program source code file is part of KiCad, a free EDA CAD application.
3 *
4 * Copyright (C) 2022 Jon Evans <[email protected]>
5 * Copyright (C) 2022-2023 KiCad Developers, see AUTHORS.txt for contributors.
6 *
7 * This program is free software: you can redistribute it and/or modify it
8 * under the terms of the GNU General Public License as published by the
9 * Free Software Foundation, either version 3 of the License, or (at your
10 * option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful, but
13 * WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 * General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License along
18 * with this program. If not, see <http://www.gnu.org/licenses/>.
19 */
20
21#include <boost/locale.hpp>
22#include <fmt/core.h>
23#include <nanodbc/nanodbc.h>
24
25// Some outdated definitions are used in sql.h
26// We need to define them for "recent" dev tools
27#define INT64 int64_t
28#define UINT64 uint64_t
29
30#ifdef __MINGW32__
31#define BYTE uint8_t
32#define WORD uint16_t
33#define DWORD uint32_t
34#define HWND uint32_t /* dummy define */
35#endif
36
37#ifdef WIN32
38#include <windows.h> // for sql.h
39#endif
40
41#include <sql.h> // SQL_IDENTIFIER_QUOTE_CHAR
42
43#include <wx/log.h>
44
47#include <core/profile.h>
48
49
50const char* const traceDatabase = "KICAD_DATABASE";
51
65nanodbc::string fromUTF8( const std::string& aString )
66{
67 return boost::locale::conv::utf_to_utf<nanodbc::string::value_type>( aString );
68}
69
70
76std::string toUTF8( const nanodbc::string& aString )
77{
78 return boost::locale::conv::utf_to_utf<char>( aString );
79}
80
81
82DATABASE_CONNECTION::DATABASE_CONNECTION( const std::string& aDataSourceName,
83 const std::string& aUsername,
84 const std::string& aPassword, int aTimeoutSeconds,
85 bool aConnectNow ) :
86 m_quoteChar( '"' )
87{
88 m_dsn = aDataSourceName;
89 m_user = aUsername;
90 m_pass = aPassword;
91 m_timeout = aTimeoutSeconds;
92
93 init();
94
95 if( aConnectNow )
96 Connect();
97}
98
99
100DATABASE_CONNECTION::DATABASE_CONNECTION( const std::string& aConnectionString,
101 int aTimeoutSeconds, bool aConnectNow ) :
102 m_quoteChar( '"' )
103{
104 m_connectionString = aConnectionString;
105 m_timeout = aTimeoutSeconds;
106
107 init();
108
109 if( aConnectNow )
110 Connect();
111}
112
113
115{
116 Disconnect();
117 m_conn.reset();
118}
119
120
122{
123 m_cache = std::make_unique<DB_CACHE_TYPE>( 10, 1 );
124}
125
126
127void DATABASE_CONNECTION::SetCacheParams( int aMaxSize, int aMaxAge )
128{
129 if( !m_cache )
130 return;
131
132 if( aMaxSize < 0 )
133 aMaxSize = 0;
134
135 if( aMaxAge < 0 )
136 aMaxAge = 0;
137
138 m_cache->SetMaxSize( static_cast<size_t>( aMaxSize ) );
139 m_cache->SetMaxAge( static_cast<time_t>( aMaxAge ) );
140}
141
142
144{
145 nanodbc::string dsn = fromUTF8( m_dsn );
146 nanodbc::string user = fromUTF8( m_user );
147 nanodbc::string pass = fromUTF8( m_pass );
148 nanodbc::string cs = fromUTF8( m_connectionString );
149
150 try
151 {
152 if( cs.empty() )
153 {
154 wxLogTrace( traceDatabase, wxT( "Creating connection to DSN %s" ), m_dsn );
155 m_conn = std::make_unique<nanodbc::connection>( dsn, user, pass, m_timeout );
156 }
157 else
158 {
159 wxLogTrace( traceDatabase, wxT( "Creating connection with connection string" ) );
160 m_conn = std::make_unique<nanodbc::connection>( cs, m_timeout );
161 }
162 }
163 catch( nanodbc::database_error& e )
164 {
165 m_lastError = e.what();
166 return false;
167 }
168
169 m_tables.clear();
170
171 if( IsConnected() )
172 getQuoteChar();
173
174 return IsConnected();
175}
176
177
179{
180 if( !m_conn )
181 {
182 wxLogTrace( traceDatabase, wxT( "Note: Disconnect() called without valid connection" ) );
183 return false;
184 }
185
186 try
187 {
188 m_conn->disconnect();
189 }
190 catch( boost::locale::conv::conversion_error& exc )
191 {
192 wxLogTrace( traceDatabase, wxT( "Disconnect() error \"%s\" occured." ), exc.what() );
193 return false;
194 }
195
196 return !m_conn->connected();
197}
198
199
201{
202 if( !m_conn )
203 return false;
204
205 return m_conn->connected();
206}
207
208
209bool DATABASE_CONNECTION::CacheTableInfo( const std::string& aTable,
210 const std::set<std::string>& aColumns )
211{
212 if( !m_conn )
213 return false;
214
215 try
216 {
217 nanodbc::catalog catalog( *m_conn );
218 nanodbc::catalog::tables tables = catalog.find_tables( fromUTF8( aTable ) );
219
220 if( !tables.next() )
221 {
222 wxLogTrace( traceDatabase, wxT( "CacheTableInfo: table '%s' not found in catalog" ),
223 aTable );
224 return false;
225 }
226
227 std::string key = toUTF8( tables.table_name() );
228 m_tables[key] = toUTF8( tables.table_type() );
229
230 try
231 {
232 nanodbc::catalog::columns columns =
233 catalog.find_columns( NANODBC_TEXT( "" ), tables.table_name() );
234
235 while( columns.next() )
236 {
237 std::string columnKey = toUTF8( columns.column_name() );
238
239 if( aColumns.count( columnKey ) )
240 m_columnCache[key][columnKey] = columns.data_type();
241 }
242
243 }
244 catch( nanodbc::database_error& e )
245 {
246 m_lastError = e.what();
247 wxLogTrace( traceDatabase, wxT( "Exception while syncing columns for table '%s': %s" ),
248 key, m_lastError );
249 return false;
250 }
251 }
252 catch( nanodbc::database_error& e )
253 {
254 m_lastError = e.what();
255 wxLogTrace( traceDatabase, wxT( "Exception while caching table info: %s" ), m_lastError );
256 return false;
257 }
258
259 return true;
260}
261
262
264{
265 if( !m_conn )
266 return false;
267
268 try
269 {
270 nanodbc::string qc = m_conn->get_info<nanodbc::string>( SQL_IDENTIFIER_QUOTE_CHAR );
271
272 if( qc.empty() )
273 return false;
274
275 m_quoteChar = *toUTF8( qc ).begin();
276
277 wxLogTrace( traceDatabase, wxT( "Quote char retrieved: %c" ), m_quoteChar );
278 }
279 catch( nanodbc::database_error& )
280 {
281 wxLogTrace( traceDatabase, wxT( "Exception while querying quote char: %s" ), m_lastError );
282 return false;
283 }
284
285 return true;
286}
287
288
289std::string DATABASE_CONNECTION::columnsFor( const std::string& aTable )
290{
291 if( !m_columnCache.count( aTable ) )
292 {
293 wxLogTrace( traceDatabase, wxT( "columnsFor: requested table %s missing from cache!" ),
294 aTable );
295 return "*";
296 }
297
298 if( m_columnCache[aTable].empty() )
299 {
300 wxLogTrace( traceDatabase, wxT( "columnsFor: requested table %s has no columns mapped!" ),
301 aTable );
302 return "*";
303 }
304
305 std::string ret;
306
307 for( const auto& [ columnName, columnType ] : m_columnCache[aTable] )
308 ret += fmt::format( "{}{}{}, ", m_quoteChar, columnName, m_quoteChar );
309
310 // strip tailing ', '
311 ret.resize( ret.length() - 2 );
312
313 return ret;
314}
315
316//next step, make SelectOne take from the SelectAll cache if the SelectOne cache is missing.
317//To do this, need to build a map of PK->ROW for the cache result.
318bool DATABASE_CONNECTION::SelectOne( const std::string& aTable,
319 const std::pair<std::string, std::string>& aWhere,
320 DATABASE_CONNECTION::ROW& aResult )
321{
322 if( !m_conn )
323 {
324 wxLogTrace( traceDatabase, wxT( "Called SelectOne without valid connection!" ) );
325 return false;
326 }
327
328 auto tableMapIter = m_tables.find( aTable );
329
330 if( tableMapIter == m_tables.end() )
331 {
332 wxLogTrace( traceDatabase, wxT( "SelectOne: requested table %s not found in cache" ),
333 aTable );
334 return false;
335 }
336
337 const std::string& tableName = tableMapIter->first;
339
340 if( m_cache->Get( tableName, cacheEntry ) )
341 {
342 if( cacheEntry.count( aWhere.second ) )
343 {
344 wxLogTrace( traceDatabase, wxT( "SelectOne: `%s` with parameter `%s` - cache hit" ),
345 tableName, aWhere.second );
346 aResult = cacheEntry.at( aWhere.second );
347 return true;
348 }
349 }
350 else
351 {
352 wxLogTrace( traceDatabase, wxT( "SelectOne: table `%s` not in row cache; will SelectAll" ),
353 tableName, aWhere.second );
354
355 selectAllAndCache( tableName, aWhere.first );
356
357 if( m_cache->Get( tableName, cacheEntry ) )
358 {
359 if( cacheEntry.count( aWhere.second ) )
360 {
361 wxLogTrace( traceDatabase, wxT( "SelectOne: `%s` with parameter `%s` - cache hit" ),
362 tableName, aWhere.second );
363 aResult = cacheEntry.at( aWhere.second );
364 return true;
365 }
366 }
367 }
368
369 if( !m_columnCache.count( tableName ) )
370 {
371 wxLogTrace( traceDatabase, wxT( "SelectOne: requested table %s missing from column cache" ),
372 tableName );
373 return false;
374 }
375
376 auto columnCacheIter = m_columnCache.at( tableName ).find( aWhere.first );
377
378 if( columnCacheIter == m_columnCache.at( tableName ).end() )
379 {
380 wxLogTrace( traceDatabase, wxT( "SelectOne: requested column %s not found in cache for %s" ),
381 aWhere.first, tableName );
382 return false;
383 }
384
385 const std::string& columnName = columnCacheIter->first;
386
387 std::string cacheKey = fmt::format( "{}{}{}", tableName, columnName, aWhere.second );
388
389 std::string queryStr = fmt::format( "SELECT {} FROM {}{}{} WHERE {}{}{} = ?",
390 columnsFor( tableName ),
391 m_quoteChar, tableName, m_quoteChar,
392 m_quoteChar, columnName, m_quoteChar );
393 nanodbc::string query = fromUTF8( queryStr );
394
395 PROF_TIMER timer;
396 nanodbc::statement statement;
397
398 try
399 {
400 statement.prepare( *m_conn, query );
401 statement.bind( 0, aWhere.second.c_str() );
402 }
403 catch( nanodbc::database_error& e )
404 {
405 m_lastError = e.what();
406 wxLogTrace( traceDatabase, wxT( "Exception while preparing statement for SelectOne: %s" ),
407 m_lastError );
408
409 // Exception may be due to a connection error; nanodbc won't auto-reconnect
410 m_conn->disconnect();
411
412 return false;
413 }
414
415 wxLogTrace( traceDatabase, wxT( "SelectOne: `%s` with parameter `%s`" ), toUTF8( query ),
416 aWhere.second );
417
418 nanodbc::result results;
419
420 try
421 {
422 results = nanodbc::execute( statement );
423 }
424 catch( nanodbc::database_error& e )
425 {
426 m_lastError = e.what();
427 wxLogTrace( traceDatabase, wxT( "Exception while executing statement for SelectOne: %s" ),
428 m_lastError );
429
430 // Exception may be due to a connection error; nanodbc won't auto-reconnect
431 m_conn->disconnect();
432
433 return false;
434 }
435
436 timer.Stop();
437
438
439 if( !results.first() )
440 {
441 wxLogTrace( traceDatabase, wxT( "SelectOne: no results returned from query" ) );
442 return false;
443 }
444
445 wxLogTrace( traceDatabase, wxT( "SelectOne: %ld results returned from query in %0.1f ms" ),
446 results.rows(), timer.msecs() );
447
448 aResult.clear();
449
450 try
451 {
452 for( short i = 0; i < results.columns(); ++i )
453 {
454 std::string column = toUTF8( results.column_name( i ) );
455
456 switch( results.column_datatype( i ) )
457 {
458 case SQL_DOUBLE:
459 case SQL_FLOAT:
460 case SQL_REAL:
461 case SQL_DECIMAL:
462 case SQL_NUMERIC:
463 {
464 try
465 {
466 aResult[column] = fmt::format( "{:G}", results.get<double>( i ) );
467 }
468 catch( nanodbc::null_access_error& )
469 {
470 // Column was empty (null)
471 aResult[column] = std::string();
472 }
473
474 break;
475 }
476
477 default:
478 aResult[column] = toUTF8( results.get<nanodbc::string>( i, NANODBC_TEXT( "" ) ) );
479 }
480 }
481 }
482 catch( nanodbc::database_error& e )
483 {
484 m_lastError = e.what();
485 wxLogTrace( traceDatabase, wxT( "Exception while parsing results from SelectOne: %s" ),
486 m_lastError );
487 return false;
488 }
489
490 return true;
491}
492
493
494bool DATABASE_CONNECTION::selectAllAndCache( const std::string& aTable, const std::string& aKey )
495{
496 nanodbc::statement statement( *m_conn );
497
498 nanodbc::string query = fromUTF8( fmt::format( "SELECT {} FROM {}{}{}", columnsFor( aTable ),
499 m_quoteChar, aTable, m_quoteChar ) );
500
501 wxLogTrace( traceDatabase, wxT( "selectAllAndCache: `%s`" ), toUTF8( query ) );
502
503 PROF_TIMER timer;
504
505 try
506 {
507 statement.prepare( query );
508 }
509 catch( nanodbc::database_error& e )
510 {
511 m_lastError = e.what();
512 wxLogTrace( traceDatabase,
513 wxT( "Exception while preparing query for selectAllAndCache: %s" ),
514 m_lastError );
515
516 // Exception may be due to a connection error; nanodbc won't auto-reconnect
517 m_conn->disconnect();
518
519 return false;
520 }
521
522 nanodbc::result results;
523
524 try
525 {
526 results = nanodbc::execute( statement );
527 }
528 catch( nanodbc::database_error& e )
529 {
530 m_lastError = e.what();
531 wxLogTrace( traceDatabase,
532 wxT( "Exception while executing query for selectAllAndCache: %s" ),
533 m_lastError );
534
535 // Exception may be due to a connection error; nanodbc won't auto-reconnect
536 m_conn->disconnect();
537
538 return false;
539 }
540
541 timer.Stop();
542
544
545 auto handleException =
546 [&]( std::runtime_error& aException, const std::string& aExtraContext = "" )
547 {
548 m_lastError = aException.what();
549 std::string extra = aExtraContext.empty() ? "" : ": " + aExtraContext;
550 wxLogTrace( traceDatabase,
551 wxT( "Exception while parsing result %d from selectAllAndCache: %s%s" ),
552 cacheEntry.size(), m_lastError, extra );
553 };
554
555 while( results.next() )
556 {
557 short columnCount = 0;
558 ROW result;
559
560 try
561 {
562 columnCount = results.columns();
563 }
564 catch( nanodbc::database_error& e )
565 {
566 handleException( e );
567 return false;
568 }
569
570 for( short j = 0; j < columnCount; ++j )
571 {
572 std::string column;
573 std::string columnExtraDbgInfo;
574 int datatype = SQL_UNKNOWN_TYPE;
575
576 try
577 {
578 column = toUTF8( results.column_name( j ) );
579 datatype = results.column_datatype( j );
580 columnExtraDbgInfo = fmt::format( "column index {}, name '{}', type {}", j, column,
581 datatype );
582 }
583 catch( nanodbc::index_range_error& e )
584 {
585 handleException( e, columnExtraDbgInfo );
586 return false;
587 }
588
589 switch( datatype )
590 {
591 case SQL_DOUBLE:
592 case SQL_FLOAT:
593 case SQL_REAL:
594 case SQL_DECIMAL:
595 case SQL_NUMERIC:
596 {
597 try
598 {
599 result[column] = fmt::format( "{:G}", results.get<double>( j ) );
600 }
601 catch( nanodbc::null_access_error& )
602 {
603 // Column was empty (null)
604 result[column] = std::string();
605 }
606 catch( std::runtime_error& e )
607 {
608 handleException( e, columnExtraDbgInfo );
609 return false;
610 }
611 break;
612 }
613
614 default:
615 {
616 try
617 {
618 result[column] = toUTF8( results.get<nanodbc::string>( j,
619 NANODBC_TEXT( "" ) ) );
620 }
621 catch( std::runtime_error& e )
622 {
623 handleException( e, columnExtraDbgInfo );
624 return false;
625 }
626 }
627 }
628 }
629
630 m_cache->Put( aTable, cacheEntry );
631 wxASSERT( result.count( aKey ) );
632 std::string keyStr = std::any_cast<std::string>( result.at( aKey ) );
633 cacheEntry[keyStr] = result;
634 }
635
636 wxLogTrace( traceDatabase, wxT( "selectAllAndCache from %s completed in %0.1f ms" ), aTable,
637 timer.msecs() );
638
639 m_cache->Put( aTable, cacheEntry );
640 return true;
641}
642
643
644bool DATABASE_CONNECTION::SelectAll( const std::string& aTable, const std::string& aKey,
645 std::vector<ROW>& aResults )
646{
647 if( !m_conn )
648 {
649 wxLogTrace( traceDatabase, wxT( "Called SelectAll without valid connection!" ) );
650 return false;
651 }
652
653 auto tableMapIter = m_tables.find( aTable );
654
655 if( tableMapIter == m_tables.end() )
656 {
657 wxLogTrace( traceDatabase, wxT( "SelectAll: requested table %s not found in cache" ),
658 aTable );
659 return false;
660 }
661
663
664 if( !m_cache->Get( aTable, cacheEntry ) )
665 {
666 if( !selectAllAndCache( aTable, aKey ) )
667 {
668 wxLogTrace( traceDatabase, wxT( "SelectAll: `%s` cache fill failed" ), aTable );
669 return false;
670 }
671
672 // Now it should be filled
673 m_cache->Get( aTable, cacheEntry );
674 }
675
676 if( !m_cache->Get( aTable, cacheEntry ) )
677 {
678 wxLogTrace( traceDatabase, wxT( "SelectAll: `%s` failed to get results from cache!" ),
679 aTable );
680 return false;
681 }
682
683 wxLogTrace( traceDatabase, wxT( "SelectAll: `%s` - returning cached results" ), aTable );
684
685 aResults.reserve( cacheEntry.size() );
686
687 for( auto &[ key, row ] : cacheEntry )
688 aResults.emplace_back( row );
689
690 return true;
691}
CacheValueType CACHE_VALUE
bool CacheTableInfo(const std::string &aTable, const std::set< std::string > &aColumns)
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 selectAllAndCache(const std::string &aTable, const std::string &aKey)
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.
Definition: profile.h:49
void Stop()
Save the time when this function was called, and set the counter stane to stop.
Definition: profile.h:88
double msecs(bool aSinceLast=false)
Definition: profile.h:149
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)