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 The 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
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 */
20
21#include <algorithm>
22
23#include <boost/algorithm/string.hpp>
24#include <wx/log.h>
25
28#include <core/profile.h>
29
30
31DATABASE_CONNECTION::DATABASE_CONNECTION( std::unique_ptr<DATABASE_BACKEND> aBackend, bool aConnectNow ) :
32 m_backend( std::move( aBackend ) ),
33 m_cache( std::make_unique<DB_CACHE_TYPE>( 10, 1 ) )
34{
35 wxASSERT_MSG( m_backend, "DATABASE_CONNECTION needs a backend" );
36
37 if( aConnectNow )
38 Connect();
39}
40
41
43 m_connection( aConnection )
44{
45 std::lock_guard lock( m_connection.m_queryMutex );
46
47 m_connection.m_backend->BeginLoadCycle( aStopped );
48}
49
50
52{
53 std::lock_guard lock( m_connection.m_queryMutex );
54
55 m_connection.m_backend->EndLoadCycle();
56}
57
58
59void DATABASE_CONNECTION::SetCacheParams( int aMaxSize, int aMaxAge )
60{
61 if( !m_cache )
62 return;
63
64 if( aMaxSize < 0 )
65 aMaxSize = 0;
66
67 if( aMaxAge < 0 )
68 aMaxAge = 0;
69
70 m_cache->SetMaxSize( static_cast<size_t>( aMaxSize ) );
71 m_cache->SetMaxAge( static_cast<time_t>( aMaxAge ) );
72}
73
74
76{
77 std::lock_guard lock( m_queryMutex );
78
79 if( !m_backend->Connect() )
80 return false;
81
82 m_tables.clear();
83 m_tableFailures.clear();
84
85 return true;
86}
87
88
90{
91 std::lock_guard lock( m_queryMutex );
92
93 m_backend->Disconnect();
94
95 return !m_backend->IsConnected();
96}
97
98
100{
101 return m_backend->IsConnected();
102}
103
104
105bool DATABASE_CONNECTION::CacheTableInfo( const std::string& aTable, const std::set<std::string>& aRequiredColumns,
106 const std::set<std::string>& aOptionalColumns )
107{
108 std::lock_guard lock( m_queryMutex );
109
110 if( !m_backend->IsConnected() )
111 return false;
112
113 std::optional<DB_TABLE_INFO> info = m_backend->DescribeTable( aTable );
114
115 // Catalog names are lowercase; normalize here so differently-cased columns still match
116 std::set<std::string> requiredLower;
117 std::set<std::string> optionalLower;
118
119 for( const std::string& col : aRequiredColumns )
120 requiredLower.insert( boost::to_lower_copy( col ) );
121
122 for( const std::string& col : aOptionalColumns )
123 optionalLower.insert( boost::to_lower_copy( col ) );
124
125 if( !info )
126 {
127 wxLogTrace( traceDatabase, wxT( "CacheTableInfo: table '%s' not found in catalog" ), aTable );
128
129 // The next table's catalog read can clear the backend's error, so keep this table's own
130 TABLE_FAILURE& failed = m_tableFailures[aTable];
131 failed.failure = m_backend->GetLastFailure();
132 failed.error = m_backend->GetLastError();
133 failed.columns.insert( requiredLower.begin(), requiredLower.end() );
134 failed.columns.insert( optionalLower.begin(), optionalLower.end() );
135 return false;
136 }
137
138 // Another library entry on this table may still lack the columns its failed lookup asked for, and a describe
139 // that lists no columns does not show they are readable
140 if( auto failed = m_tableFailures.find( aTable ); failed != m_tableFailures.end() )
141 {
142 const bool covered = std::all_of( failed->second.columns.begin(), failed->second.columns.end(),
143 [&]( const std::string& aColumn )
144 {
145 return requiredLower.count( aColumn ) || optionalLower.count( aColumn );
146 } );
147
148 if( covered && !info->columnsUnavailable )
149 m_tableFailures.erase( failed );
150 }
151
152 TABLE_STATE& table = m_tables[info->name];
153 table.info = *info;
154
155 // The table is still usable with SELECT * when the catalog cannot list its columns
156 if( info->columnsUnavailable )
157 {
158 wxLogTrace( traceDatabase, wxT( "CacheTableInfo: columns of table '%s' unavailable" ), info->name );
159 return false;
160 }
161
162 std::set<std::string> columnsReported;
163
164 for( const DB_COLUMN& column : info->columns )
165 {
166 const std::string lower = boost::to_lower_copy( column.name );
167
168 if( !requiredLower.count( lower ) && !optionalLower.count( lower ) )
169 continue;
170
171 columnsReported.insert( lower );
172
173 if( !column.supported )
174 {
175 wxLogTrace( traceDatabase, wxT( "CacheTableInfo: column '%s' of table '%s' cannot be read; dropped" ),
176 column.name, info->name );
177 continue;
178 }
179
180 table.columns.insert( column.name );
181 }
182
183 // SQLite's ODBC driver doesn't report generated columns via SQLColumns, so required
184 // columns are trusted and added even when absent from the catalog
185 for( const std::string& requestedCol : aRequiredColumns )
186 {
187 const std::string requestedLower = boost::to_lower_copy( requestedCol );
188
189 if( !columnsReported.count( requestedLower ) && !requestedCol.empty() )
190 {
191 wxLogTrace( traceDatabase,
192 wxT( "CacheTableInfo: column '%s' not found in catalog for table '%s', adding anyway" ),
193 requestedCol, info->name );
194
195 table.columns.insert( requestedCol );
196 }
197 }
198
199 // Unlike required columns, a missing optional column is dropped, not added, but is
200 // still worth a warning since it usually means a misconfigured .kicad_dbl mapping
201 for( const std::string& requestedCol : aOptionalColumns )
202 {
203 if( !columnsReported.count( boost::to_lower_copy( requestedCol ) ) && !requestedCol.empty() )
204 {
205 wxLogWarning( wxT( "Database table '%s' has no column '%s'; ignoring the "
206 "misconfigured properties mapping." ),
207 info->name, requestedCol );
208 }
209 }
210
211 return true;
212}
213
214
215std::vector<std::string> DATABASE_CONNECTION::columnNames( const TABLE_STATE& aTable )
216{
217 std::vector<std::string> names;
218
219 for( const std::string& spelling : aTable.columns )
220 names.push_back( spelling );
221
222 if( names.empty() )
223 wxLogTrace( traceDatabase, wxT( "columnNames: table %s has no columns mapped!" ), aTable.info.name );
224
225 return names;
226}
227
228
229std::optional<size_t> DATABASE_CONNECTION::findLabel( const std::vector<std::string>& aLabels,
230 const std::string& aKey )
231{
232 for( size_t i = 0; i < aLabels.size(); ++i )
233 {
234 if( aLabels[i] == aKey )
235 return i;
236 }
237
238 for( size_t i = 0; i < aLabels.size(); ++i )
239 {
240 if( boost::iequals( aLabels[i], aKey ) )
241 return i;
242 }
243
244 return std::nullopt;
245}
246
247
248DATABASE_CONNECTION::ROW DATABASE_CONNECTION::makeRow( const std::vector<std::string>& aLabels,
249 std::vector<DB_VALUE>&& aValues )
250{
251 ROW row;
252
253 for( size_t i = 0; i < aLabels.size() && i < aValues.size(); ++i )
254 {
255 if( std::string* text = std::get_if<std::string>( &aValues[i] ) )
256 row[aLabels[i]] = std::move( *text );
257 else
258 row[aLabels[i]] = DatabaseValueToString( aValues[i] );
259 }
260
261 return row;
262}
263
264
265bool DATABASE_CONNECTION::readAndCache( const std::string& aTable, const std::string& aKey,
267{
268 const TABLE_STATE& table = m_tables.at( aTable );
269
270 PROF_TIMER timer;
272 std::vector<std::string> labels;
273 std::optional<size_t> keyIndex;
274 bool keyResolved = false;
275
276 // Labels are known before the first row arrives
277 auto sink =
278 [&]( std::vector<DB_VALUE>&& aValues )
279 {
280 if( !keyResolved )
281 {
282 keyResolved = true;
283 keyIndex = findLabel( labels, aKey );
284
285 if( !keyIndex )
286 {
287 wxLogTrace( traceDatabase, wxT( "readAndCache: warning: key %s not found in result set" ),
288 aKey );
289 }
290 }
291
292 if( !keyIndex || *keyIndex >= aValues.size() )
293 return;
294
295 std::string key = DatabaseValueToString( aValues[*keyIndex] );
296 built[std::move( key )] = makeRow( labels, std::move( aValues ) );
297 };
298
299 // A pass the backend could not verify must leave nothing behind, not even rows whose keys a later pass lacks
300 auto restart =
301 [&]()
302 {
303 built.clear();
304 keyResolved = false;
305 keyIndex.reset();
306 };
307
308 if( !m_backend->ReadAllRows( table.info, columnNames( table ), labels, sink, restart ) )
309 return false;
310
311 timer.Stop();
312
313 wxLogTrace( traceDatabase, wxT( "readAndCache from %s completed in %0.1f ms" ), aTable, timer.msecs() );
314
315 // The cache is best effort; with no room for the table the rows are still returned
316 m_cache->Put( aTable, built );
317 aEntry = std::move( built );
318
319 return true;
320}
321
322
323bool DATABASE_CONNECTION::SelectOne( const std::string& aTable, const std::pair<std::string, std::string>& aWhere,
324 DATABASE_CONNECTION::ROW& aResult )
325{
326 std::lock_guard lock( m_queryMutex );
327
328 if( !m_backend->IsConnected() )
329 {
330 wxLogTrace( traceDatabase, wxT( "Called SelectOne without valid connection!" ) );
331 return false;
332 }
333
334 auto tableIter = m_tables.find( aTable );
335
336 if( tableIter == m_tables.end() )
337 {
338 wxLogTrace( traceDatabase, wxT( "SelectOne: requested table %s not found in cache" ), aTable );
339 return false;
340 }
341
342 const std::string& tableName = tableIter->first;
343 const TABLE_STATE& table = tableIter->second;
345
346 bool cached = m_cache->Get( tableName, cacheEntry );
347
348 if( !cached )
349 wxLogTrace( traceDatabase, wxT( "SelectOne: table `%s` not in row cache; will SelectAll" ), tableName );
350
351 if( cached || readAndCache( tableName, aWhere.first, cacheEntry ) )
352 {
353 if( auto hit = cacheEntry.find( aWhere.second ); hit != cacheEntry.end() )
354 {
355 wxLogTrace( traceDatabase, wxT( "SelectOne: `%s` with parameter `%s` - cache hit" ), tableName,
356 aWhere.second );
357 aResult = hit->second;
358 return true;
359 }
360 }
361
362 auto keyColumn = table.columns.find( aWhere.first );
363
364 if( keyColumn == table.columns.end() )
365 {
366 keyColumn = std::find_if( table.columns.begin(), table.columns.end(),
367 [&]( const std::string& aColumn )
368 {
369 return boost::iequals( aColumn, aWhere.first );
370 } );
371 }
372
373 if( keyColumn == table.columns.end() )
374 {
375 wxLogTrace( traceDatabase, wxT( "SelectOne: requested column %s not found in cache for %s" ), aWhere.first,
376 tableName );
377 return false;
378 }
379
380 std::vector<std::string> labels;
381 std::vector<DB_VALUE> values;
382
383 std::optional<bool> found = m_backend->ReadRow( table.info, columnNames( table ), *keyColumn,
384 aWhere.second, labels, values );
385
386 if( found )
387 {
388 if( !*found )
389 return false;
390
391 aResult = makeRow( labels, std::move( values ) );
392 return true;
393 }
394
395 // No indexed lookup, and the cache or a fresh read has no such key; the refresh bounds how stale the cache gets
396 return false;
397}
398
399
400std::optional<DATABASE_CONNECTION::TABLE_FAILURE>
401DATABASE_CONNECTION::GetTableFailure( const std::string& aTable ) const
402{
403 std::lock_guard lock( m_queryMutex );
404
405 if( auto failed = m_tableFailures.find( aTable ); failed != m_tableFailures.end() )
406 return failed->second;
407
408 return std::nullopt;
409}
410
411
412bool DATABASE_CONNECTION::SelectAll( const std::string& aTable, const std::string& aKey, std::vector<ROW>& aResults,
413 QUERY_STATUS* aStatus )
414{
415 std::lock_guard lock( m_queryMutex );
416
417 // Copied under the query lock, so another thread's query cannot replace the failure first
418 auto fail =
419 [&]()
420 {
421 if( aStatus )
422 {
423 *aStatus = QUERY_STATUS{ m_backend->IsConnected(), m_backend->GetLastFailure(),
424 m_backend->GetLastError() };
425 }
426
427 return false;
428 };
429
430 if( !m_backend->IsConnected() )
431 {
432 wxLogTrace( traceDatabase, wxT( "Called SelectAll without valid connection!" ) );
433 return fail();
434 }
435
436 // A table another library entry described is in m_tables too, so its failure must win
437 if( auto failed = m_tableFailures.find( aTable ); failed != m_tableFailures.end() )
438 {
439 wxLogTrace( traceDatabase, wxT( "SelectAll: table %s could not be described" ), aTable );
440
441 if( aStatus )
442 *aStatus = QUERY_STATUS{ m_backend->IsConnected(), failed->second.failure, failed->second.error };
443
444 return false;
445 }
446
447 if( !m_tables.count( aTable ) )
448 {
449 wxLogTrace( traceDatabase, wxT( "SelectAll: requested table %s not found in cache" ), aTable );
450
451 if( aStatus )
452 *aStatus = QUERY_STATUS{ m_backend->IsConnected(), DB_FAILURE::UNKNOWN, std::string() };
453
454 return false;
455 }
456
458
459 if( !m_cache->Get( aTable, cacheEntry ) )
460 {
461 if( !readAndCache( aTable, aKey, cacheEntry ) )
462 {
463 wxLogTrace( traceDatabase, wxT( "SelectAll: `%s` cache fill failed" ), aTable );
464 return fail();
465 }
466 }
467 else
468 {
469 wxLogTrace( traceDatabase, wxT( "SelectAll: `%s` - returning cached results" ), aTable );
470 }
471
472 aResults.reserve( cacheEntry.size() );
473
474 for( auto& [key, row] : cacheEntry )
475 aResults.emplace_back( std::move( row ) );
476
477 return true;
478}
479
480
481bool DATABASE_CONNECTION::SelectSample( const std::string& aTable, size_t aMaxRows,
482 std::vector<std::string>& aColumns, std::vector<ROW>& aRows )
483{
484 std::lock_guard lock( m_queryMutex );
485
486 aColumns.clear();
487 aRows.clear();
488
489 if( !m_backend->IsConnected() )
490 return false;
491
492 // A table nobody described yet is read by the name it was given
493 auto tableIter = m_tables.find( aTable );
494 DB_TABLE_INFO info = tableIter != m_tables.end() ? tableIter->second.info : DB_TABLE_INFO{ aTable, {} };
495
496 auto sink =
497 [&]( std::vector<DB_VALUE>&& aValues )
498 {
499 aRows.emplace_back( makeRow( aColumns, std::move( aValues ) ) );
500 };
501
502 if( !m_backend->ReadTable( info, {}, aMaxRows, aColumns, sink ) )
503 {
504 aColumns.clear();
505 aRows.clear();
506 return false;
507 }
508
509 return true;
510}
511
512
513bool DATABASE_CONNECTION::GetTables( std::vector<std::string>& aTables )
514{
515 std::lock_guard lock( m_queryMutex );
516
517 aTables.clear();
518
519 if( !m_backend->IsConnected() )
520 return false;
521
522 return m_backend->ListTables( aTables );
523}
524
525
526bool DATABASE_CONNECTION::GetColumns( const std::string& aTable, std::vector<std::string>& aColumns )
527{
528 std::lock_guard lock( m_queryMutex );
529
530 aColumns.clear();
531
532 if( !m_backend->IsConnected() )
533 return false;
534
535 return m_backend->ListColumns( aTable, aColumns );
536}
LOAD_CYCLE(DATABASE_CONNECTION &aConnection, DB_STOP_CHECK aStopped)
bool SelectAll(const std::string &aTable, const std::string &aKey, std::vector< ROW > &aResults, QUERY_STATUS *aStatus=nullptr)
Retrieves all rows from a database table.
DATABASE_CONNECTION(std::unique_ptr< DATABASE_BACKEND > aBackend, bool aConnectNow=true)
bool GetColumns(const std::string &aTable, std::vector< std::string > &aColumns)
Lists the columns of a table in catalog order.
std::map< std::string, std::any > ROW
std::unique_ptr< DATABASE_BACKEND > m_backend
static ROW makeRow(const std::vector< std::string > &aLabels, std::vector< DB_VALUE > &&aValues)
Converts one row delivered by the backend to text keyed by result-column label.
std::map< std::string, TABLE_STATE > m_tables
void SetCacheParams(int aMaxSize, int aMaxAge)
std::unique_ptr< DB_CACHE_TYPE > m_cache
std::map< std::string, TABLE_FAILURE > m_tableFailures
Why CacheTableInfo could not describe each table, so a later query on it reports that table's own fai...
static std::vector< std::string > columnNames(const TABLE_STATE &aTable)
bool GetTables(std::vector< std::string > &aTables)
Lists the tables and views visible through this connection, sorted by name.
bool SelectSample(const std::string &aTable, size_t aMaxRows, std::vector< std::string > &aColumns, std::vector< ROW > &aRows)
Reads the first rows of a table, bypassing the row cache.
DATABASE_CACHE< std::map< std::string, ROW > > DB_CACHE_TYPE
bool SelectOne(const std::string &aTable, const std::pair< std::string, std::string > &aWhere, ROW &aResult)
Retrieves a single row from a database table.
static std::optional< size_t > findLabel(const std::vector< std::string > &aLabels, const std::string &aKey)
bool readAndCache(const std::string &aTable, const std::string &aKey, DB_CACHE_TYPE::CACHE_VALUE &aEntry)
Reads a whole table into aEntry and offers it to the row cache.
std::optional< TABLE_FAILURE > GetTableFailure(const std::string &aTable) const
bool CacheTableInfo(const std::string &aTable, const std::set< std::string > &aRequiredColumns, const std::set< std::string > &aOptionalColumns={})
Caches schema information for a table so that subsequent queries know which columns exist.
A small class to help profiling.
Definition profile.h:46
void Stop()
Save the time when this function was called, and set the counter stane to stop.
Definition profile.h:86
double msecs(bool aSinceLast=false)
Definition profile.h:148
std::function< bool()> DB_STOP_CHECK
True once the caller no longer wants what a read would deliver.
std::string DatabaseValueToString(const DB_VALUE &aValue)
Convert a database value to the text KiCad stores for it.
const wxChar *const traceDatabase
Flag to enable database library tracing.
STL namespace.
What a failed query left behind, copied before another thread's query can replace it.
Why CacheTableInfo could not describe a table.
std::set< std::string > columns
Lowercase columns the failed lookups asked for; only a lookup asking for all of them clears the failu...
A table the backend described, with the columns queries against it read.
std::set< std::string > columns
The column spellings queries read. Spellings differing only in case are distinct columns.
A column as the backend reports it.
std::string name
bool supported
False for columns the backend can never read, such as attachments.
std::string name
The table's name as the backend spells it.
const std::string table