KiCad PCB EDA Suite
Loading...
Searching...
No Matches
test_database.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
8* modify it under the terms of the GNU General Public License
9* as published by the Free Software Foundation; either version 2
10* of the License, or (at your option) any later version.
11*
12* This program is distributed in the hope that it will be useful,
13* but WITHOUT ANY WARRANTY; without even the implied warranty of
14* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15* GNU 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 <cstdlib>
22#include <filesystem>
23#include <random>
24
25#include <fmt/format.h>
26#include <boost/test/unit_test.hpp>
27#include <nanodbc/nanodbc.h>
28
29#ifdef _WIN32
30#include <windows.h> // for sql.h
31#endif
32
33#include <sql.h>
34
37
38BOOST_AUTO_TEST_SUITE( Database )
39
40
41static std::unique_ptr<DATABASE_BACKEND> odbcBackend( const std::string& aConnectionString )
42{
43 return std::make_unique<ODBC_BACKEND>( "", "", "", aConnectionString, 2 );
44}
45
46
48{
49 std::string cs = fmt::format( "Driver={{SQLite3}};Database={}/database.sqlite",
50 QA_DATABASE_FILE_LOCATION );
51
52 // Construct and connect
54 BOOST_CHECK_NO_THROW( dc.Connect() );
55 BOOST_CHECK( dc.IsConnected() );
56
57 dc.Disconnect();
58 BOOST_CHECK( !dc.IsConnected() );
59
60 dc.Connect();
61 BOOST_CHECK( dc.IsConnected() );
62
63 dc.Disconnect();
64
65 // Scoped connection should self-disconnect
66 {
68 }
69
70 dc.Connect();
71 dc.CacheTableInfo( "Resistors", { "Part ID", "MPN" } );
72 dc.CacheTableInfo( "Capacitors", { "Part ID", "Cost" } );
73 BOOST_CHECK( dc.IsConnected() );
74
76
77 BOOST_CHECK( dc.SelectOne( "Resistors", std::make_pair( "Part ID", "RES-001" ), result ) );
78
79 BOOST_CHECK( !result.empty() );
80 BOOST_CHECK( result.count( "MPN" ) );
81 BOOST_CHECK_NO_THROW( std::any_cast<std::string>( result.at( "MPN" ) ) );
82 BOOST_CHECK_EQUAL( std::any_cast<std::string>( result.at( "MPN" ) ), "RC0603FR-0710KL" );
83
84 BOOST_CHECK( dc.SelectOne( "Capacitors", std::make_pair( "Part ID", "CAP-003" ), result ) );
85
86 BOOST_CHECK( !result.empty() );
87 BOOST_CHECK( result.count( "Cost" ) );
88 BOOST_CHECK_NO_THROW( std::any_cast<std::string>( result.at( "Cost" ) ) );
89 BOOST_CHECK_EQUAL( std::any_cast<std::string>( result.at( "Cost" ) ), "1.95" );
90}
91
92
102BOOST_AUTO_TEST_CASE( GeneratedColumns )
103{
104 std::string cs = fmt::format( "Driver={{SQLite3}};Database={}/database.sqlite",
105 QA_DATABASE_FILE_LOCATION );
106
108 BOOST_CHECK( dc.IsConnected() );
109
110 // Cache table info including the generated column "FullDesc"
111 // The SQLite ODBC driver won't report this column via SQLColumns,
112 // but our fix should add it to the cache anyway
113 dc.CacheTableInfo( "GeneratedTest", { "part id", "value", "resistance", "fulldesc" } );
114
116
117 BOOST_CHECK( dc.SelectOne( "GeneratedTest", std::make_pair( "Part ID", "GEN-001" ), result ) );
118 BOOST_CHECK( !result.empty() );
119
120 // Verify we can read the generated column value
121 BOOST_CHECK( result.count( "FullDesc" ) );
122 BOOST_CHECK_NO_THROW( std::any_cast<std::string>( result.at( "FullDesc" ) ) );
123 BOOST_CHECK_EQUAL( std::any_cast<std::string>( result.at( "FullDesc" ) ), "10K - 10000ohm" );
124
125 // Also verify regular columns still work
126 BOOST_CHECK( result.count( "Value" ) );
127 BOOST_CHECK_EQUAL( std::any_cast<std::string>( result.at( "Value" ) ), "10K" );
128}
129
130
133{
135 m_path( std::filesystem::temp_directory_path()
136 / fmt::format( "kicad_qa_{}.sqlite", std::random_device()() ) )
137 {
138 }
139
141 {
142 std::error_code ec;
143 std::filesystem::remove( m_path, ec );
144 }
145
146 std::string ConnectionString() const
147 {
148 return fmt::format( "Driver={{SQLite3}};Database={}", m_path.string() );
149 }
150
151 void Execute( const std::string& aSql ) const
152 {
153 std::string cs = ConnectionString();
154 nanodbc::connection conn( nanodbc::string( cs.begin(), cs.end() ) );
155 nanodbc::execute( conn, nanodbc::string( aSql.begin(), aSql.end() ) );
156 }
157
158 std::filesystem::path m_path;
159};
160
161
162// Embedded quote characters in table and column names must be doubled in the generated SQL
163BOOST_AUTO_TEST_CASE( QuoteCharactersInIdentifiers )
164{
166 db.Execute( "CREATE TABLE \"Part \"\"A\"\"\" (\"Part ID\" TEXT, \"Va\"\"lue\" TEXT)" );
167 db.Execute( "INSERT INTO \"Part \"\"A\"\"\" VALUES ('P1', '10K'), ('P2', '22K')" );
168
169 const std::string table = "Part \"A\"";
170 const std::string column = "Va\"lue";
171
172 {
174 BOOST_REQUIRE( dc.IsConnected() );
175 BOOST_REQUIRE( dc.CacheTableInfo( table, { "Part ID", column } ) );
176
177 std::vector<DATABASE_CONNECTION::ROW> rows;
178 BOOST_REQUIRE_MESSAGE( dc.SelectAll( table, "Part ID", rows ), dc.GetLastError() );
179 BOOST_CHECK_EQUAL( rows.size(), 2 );
180 }
181
182 {
184 BOOST_REQUIRE( dc.IsConnected() );
185 BOOST_REQUIRE( dc.CacheTableInfo( table, { "Part ID", column } ) );
186
187 // A zero-size row cache forces the direct query
188 dc.SetCacheParams( 0, 0 );
189
191 BOOST_REQUIRE_MESSAGE( dc.SelectOne( table, std::make_pair( "Part ID", "P2" ), result ), dc.GetLastError() );
192 BOOST_CHECK_EQUAL( std::any_cast<std::string>( result.at( column ) ), "22K" );
193 }
194}
195
196
197// REAL columns must keep every significant digit rather than the six that {:G} gives
198BOOST_AUTO_TEST_CASE( NumericPrecision )
199{
201 db.Execute( "CREATE TABLE Nums (\"Part ID\" TEXT, Val REAL)" );
202 db.Execute( "INSERT INTO Nums VALUES ('P1', 10012345), ('P2', 1234.5678)" );
203
205 BOOST_REQUIRE( dc.IsConnected() );
206 BOOST_REQUIRE( dc.CacheTableInfo( "Nums", { "Part ID", "Val" } ) );
207
208 std::vector<DATABASE_CONNECTION::ROW> rows;
209 BOOST_REQUIRE_MESSAGE( dc.SelectAll( "Nums", "Part ID", rows ), dc.GetLastError() );
210 BOOST_REQUIRE_EQUAL( rows.size(), 2 );
211
212 BOOST_CHECK_EQUAL( std::any_cast<std::string>( rows[0].at( "Val" ) ), "10012345" );
213 BOOST_CHECK_EQUAL( std::any_cast<std::string>( rows[1].at( "Val" ) ), "1234.5678" );
214}
215
216
217static boost::test_tools::assertion_result hasPostgresConnection( boost::unit_test::test_unit_id )
218{
219 const char* connection = std::getenv( "QA_DBLIB_PG_CONNECTION" );
220 boost::test_tools::assertion_result result( connection && *connection );
221 result.message() << "QA_DBLIB_PG_CONNECTION is not set";
222 return result;
223}
224
225
226// NUMERIC values must arrive as exact text, which SQLite cannot prove, so this runs against PostgreSQL
227BOOST_AUTO_TEST_CASE( DecimalPrecision, *boost::unit_test::precondition( hasPostgresConnection ) )
228{
229 const std::string cs = std::getenv( "QA_DBLIB_PG_CONNECTION" );
230 const std::string table = fmt::format( "kicad_qa_decimal_{}", std::random_device()() );
231
232 {
233 nanodbc::connection conn( nanodbc::string( cs.begin(), cs.end() ) );
234 nanodbc::just_execute( conn, NANODBC_TEXT( "CREATE TABLE " ) + nanodbc::string( table.begin(), table.end() )
235 + NANODBC_TEXT( " (part_id TEXT, val NUMERIC(30,10))" ) );
236 }
237
239 {
241 {
242 try
243 {
244 nanodbc::connection conn( nanodbc::string( m_cs.begin(), m_cs.end() ) );
245 nanodbc::just_execute( conn, NANODBC_TEXT( "DROP TABLE IF EXISTS " )
246 + nanodbc::string( m_table.begin(), m_table.end() ) );
247 }
248 catch( const std::exception& e )
249 {
250 BOOST_TEST_MESSAGE( "Leaked table " << m_table << ": " << e.what() );
251 }
252 }
253
254 std::string m_cs;
255 std::string m_table;
256 } dropTable{ cs, table };
257
258 {
259 nanodbc::connection conn( nanodbc::string( cs.begin(), cs.end() ) );
260 nanodbc::just_execute( conn, nanodbc::string( NANODBC_TEXT( "INSERT INTO " ) )
261 + nanodbc::string( table.begin(), table.end() )
262 + NANODBC_TEXT( " VALUES ('P1', 12345678901234567.89)" ) );
263
264 nanodbc::result res = nanodbc::execute( conn, NANODBC_TEXT( "SELECT val FROM " )
265 + nanodbc::string( table.begin(), table.end() ) );
266
267 BOOST_REQUIRE( res.columns() == 1 );
268 BOOST_CHECK( res.column_datatype( 0 ) == SQL_NUMERIC || res.column_datatype( 0 ) == SQL_DECIMAL );
269 }
270
272 BOOST_REQUIRE( dc.IsConnected() );
273 BOOST_REQUIRE_MESSAGE( dc.CacheTableInfo( table, { "part_id", "val" } ), dc.GetLastError() );
274
275 std::vector<DATABASE_CONNECTION::ROW> rows;
276 BOOST_REQUIRE_MESSAGE( dc.SelectAll( table, "part_id", rows ), dc.GetLastError() );
278
279 BOOST_CHECK_EQUAL( std::any_cast<std::string>( rows[0].at( "val" ) ), "12345678901234567.89" );
280}
281
282
283// A table name is a catalog search pattern, so underscore and percent must match only themselves
284BOOST_AUTO_TEST_CASE( PatternCharactersInTableNames )
285{
287
288 // The distractors come first and match the unescaped pattern "Part_%Data"
289 db.Execute( "CREATE TABLE PartAData (\"Part ID\" TEXT, Value TEXT)" );
290 db.Execute( "CREATE TABLE PartABCData (\"Part ID\" TEXT, Value TEXT)" );
291 db.Execute( "CREATE TABLE \"Part_%Data\" (\"Part ID\" TEXT, Value TEXT)" );
292 db.Execute( "INSERT INTO PartAData VALUES ('P1', 'wrong A')" );
293 db.Execute( "INSERT INTO PartABCData VALUES ('P1', 'wrong ABC')" );
294 db.Execute( "INSERT INTO \"Part_%Data\" VALUES ('P1', 'right')" );
295
297 BOOST_REQUIRE( dc.IsConnected() );
298 BOOST_REQUIRE( dc.CacheTableInfo( "Part_%Data", { "Part ID", "Value" } ) );
299
301 BOOST_REQUIRE_MESSAGE( dc.SelectOne( "Part_%Data", std::make_pair( "Part ID", "P1" ), result ), dc.GetLastError() );
302 BOOST_CHECK_EQUAL( std::any_cast<std::string>( result.at( "Value" ) ), "right" );
303}
304
305
A read-only source of tables.
A read-only view of a database library source.
std::map< std::string, std::any > ROW
STL class.
STL namespace.
std::string m_table
std::string m_cs
A throwaway SQLite database whose tables are created by the test.
void Execute(const std::string &aSql) const
std::filesystem::path m_path
std::string ConnectionString() const
BOOST_AUTO_TEST_SUITE(CadstarPartParser)
BOOST_CHECK(res.column_datatype(0)==SQL_NUMERIC||res.column_datatype(0)==SQL_DECIMAL)
const std::string table
BOOST_REQUIRE_EQUAL(rows.size(), 1)
static std::unique_ptr< DATABASE_BACKEND > odbcBackend(const std::string &aConnectionString)
DATABASE_CONNECTION dc(odbcBackend(cs))
BOOST_AUTO_TEST_CASE(Connect)
struct DROP_TABLE cs
BOOST_CHECK_EQUAL(std::any_cast< std::string >(rows[0].at("val")), "12345678901234567.89")
static boost::test_tools::assertion_result hasPostgresConnection(boost::unit_test::test_unit_id)
BOOST_REQUIRE_MESSAGE(dc.CacheTableInfo(table, { "part_id", "val" }), dc.GetLastError())
nanodbc::result res
std::vector< DATABASE_CONNECTION::ROW > rows
BOOST_REQUIRE(res.columns()==1)
BOOST_AUTO_TEST_SUITE_END()
BOOST_TEST_MESSAGE("Polyline has "<< chain.PointCount()<< " points")
wxString result
Test unit parsing edge cases and error handling.