Line data Source code
1 : /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 : /*
3 : * This file is part of the LibreOffice project.
4 : *
5 : * This Source Code Form is subject to the terms of the Mozilla Public
6 : * License, v. 2.0. If a copy of the MPL was not distributed with this
7 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 : */
9 :
10 : #include "dbtest_base.cxx"
11 :
12 : #include <boost/scoped_ptr.hpp>
13 : #include <osl/file.hxx>
14 : #include <osl/process.h>
15 : #include <osl/time.h>
16 : #include <rtl/ustrbuf.hxx>
17 : #include <tools/stream.hxx>
18 : #include <unotools/tempfile.hxx>
19 :
20 : #include <com/sun/star/beans/XPropertySet.hpp>
21 : #include <com/sun/star/frame/XStorable.hpp>
22 : #include <com/sun/star/lang/XComponent.hpp>
23 : #include <com/sun/star/sdb/XOfficeDatabaseDocument.hpp>
24 : #include <com/sun/star/sdbc/XColumnLocate.hpp>
25 : #include <com/sun/star/sdbc/XConnection.hpp>
26 : #include <com/sun/star/sdbc/XParameters.hpp>
27 : #include <com/sun/star/sdbc/XPreparedStatement.hpp>
28 : #include <com/sun/star/sdbc/XResultSet.hpp>
29 : #include <com/sun/star/sdbc/XRow.hpp>
30 : #include <com/sun/star/sdbc/XStatement.hpp>
31 : #include <com/sun/star/util/XCloseable.hpp>
32 :
33 : using namespace ::com::sun::star;
34 : using namespace ::com::sun::star::beans;
35 : using namespace ::com::sun::star::frame;
36 : using namespace ::com::sun::star::lang;
37 : using namespace ::com::sun::star::sdb;
38 : using namespace ::com::sun::star::sdbc;
39 : using namespace ::com::sun::star::uno;
40 :
41 0 : void normaliseTimeValue(TimeValue* pVal)
42 : {
43 0 : pVal->Seconds += pVal->Nanosec / 1000000000;
44 0 : pVal->Nanosec %= 1000000000;
45 0 : }
46 :
47 0 : void getTimeDifference(const TimeValue* pTimeStart,
48 : const TimeValue* pTimeEnd,
49 : TimeValue* pTimeDifference)
50 : {
51 : // We add 1 second to the nanoseconds to ensure that we get a positive number
52 : // We have to normalise anyway so this doesn't cause any harm.
53 : // (Seconds/Nanosec are both unsigned)
54 0 : pTimeDifference->Seconds = pTimeEnd->Seconds - pTimeStart->Seconds - 1;
55 0 : pTimeDifference->Nanosec = 1000000000 + pTimeEnd->Nanosec - pTimeStart->Nanosec;
56 0 : normaliseTimeValue(pTimeDifference);
57 0 : }
58 :
59 0 : OUString getPrintableTimeValue(const TimeValue* pTimeValue)
60 : {
61 : return OUString::number(
62 0 : (sal_uInt64(pTimeValue->Seconds) * SAL_CONST_UINT64(1000000000)
63 0 : + sal_uInt64(pTimeValue->Nanosec))/ 1000000
64 0 : );
65 : }
66 :
67 : /*
68 : * The reccomended way to run this test is:
69 : * 'SAL_LOG="" DBA_PERFTEST=YES make CppunitTest_dbaccess_embeddeddb_performancetest'
70 : * This blocks the unnecessary exception output and show only the performance data.
71 : *
72 : * You also need to create the file dbacess/qa/unit/data/wordlist, this list cannot
73 : * contain any unescaped apostrophes (since the words are used directly to assemble
74 : * sql statement), apostrophes are escaped using a double apostrophe, i.e. ''.
75 : * one easy way of generating a list is using:
76 : * 'for WORD in $(aspell dump master); do echo ${WORD//\'/\'\'}; done > dbaccess/qa/unit/data/wordlist'
77 : *
78 : * Note that wordlist cannot have more than 220580 lines, this is due to a hard
79 : * limit in our hsqldb version.
80 : *
81 : * Also note that this unit test "fails" when doing performance testing, this is
82 : * since by default unit test output is hidden, and thus there is no way of
83 : * reading the results.
84 : */
85 6 : class EmbeddedDBPerformanceTest
86 : : public DBTestBase
87 : {
88 : private:
89 : const static OUString our_sEnableTestEnvVar;
90 :
91 : // We store the results and print them at the end due to the amount of warning
92 : // noise present which otherwise obscures the results.
93 : OUStringBuffer m_aOutputBuffer;
94 :
95 : void printTimes(const TimeValue* pTime1, const TimeValue* pTime2, const TimeValue* pTime3);
96 :
97 : void doPerformanceTestOnODB(const OUString& rDriverURL,
98 : const OUString& rDBName,
99 : const bool bUsePreparedStatement);
100 :
101 : void setupTestTable(uno::Reference< XConnection >& xConnection);
102 :
103 : SvFileStream *getWordListStream();
104 :
105 : // Individual Tests
106 : void performPreparedStatementInsertTest(
107 : uno::Reference< XConnection >& xConnection,
108 : const OUString& rDBName);
109 : void performStatementInsertTest(
110 : uno::Reference< XConnection >& xConnection,
111 : const OUString& rDBName);
112 : void performReadTest(
113 : uno::Reference< XConnection >& xConnection,
114 : const OUString& rDBName);
115 :
116 : // Perform all tests on a given DB.
117 : void testFirebird();
118 : void testHSQLDB();
119 :
120 : public:
121 : void testPerformance();
122 :
123 4 : CPPUNIT_TEST_SUITE(EmbeddedDBPerformanceTest);
124 2 : CPPUNIT_TEST(testPerformance);
125 4 : CPPUNIT_TEST_SUITE_END();
126 : };
127 :
128 0 : SvFileStream* EmbeddedDBPerformanceTest::getWordListStream()
129 : {
130 0 : OUString wlPath;
131 0 : createFileURL("wordlist", wlPath);
132 :
133 0 : SvFileStream *pFile(new SvFileStream(wlPath, STREAM_READ));
134 :
135 0 : if (!pFile)
136 : {
137 0 : fprintf(stderr, "Please ensure the wordlist is present\n");
138 0 : CPPUNIT_ASSERT(false);
139 : }
140 :
141 0 : return pFile;
142 : }
143 :
144 0 : void EmbeddedDBPerformanceTest::printTimes(
145 : const TimeValue* pTime1,
146 : const TimeValue* pTime2,
147 : const TimeValue* pTime3)
148 : {
149 : m_aOutputBuffer.append(
150 0 : getPrintableTimeValue(pTime1) + "\t" +
151 0 : getPrintableTimeValue(pTime2) + "\t" +
152 0 : getPrintableTimeValue(pTime3) + "\t"
153 : "\n"
154 0 : );
155 0 : }
156 :
157 2 : const OUString EmbeddedDBPerformanceTest::our_sEnableTestEnvVar("DBA_PERFTEST");
158 :
159 : // TODO: we probably should create a document from scratch instead?
160 :
161 2 : void EmbeddedDBPerformanceTest::testPerformance()
162 : {
163 2 : OUString sEnabled;
164 2 : osl_getEnvironment(our_sEnableTestEnvVar.pData, &sEnabled.pData);
165 :
166 2 : if (sEnabled.isEmpty())
167 4 : return;
168 :
169 0 : m_aOutputBuffer.append("---------------------\n");
170 0 : testFirebird();
171 0 : m_aOutputBuffer.append("---------------------\n");
172 0 : testHSQLDB();
173 0 : m_aOutputBuffer.append("---------------------\n");
174 :
175 0 : fprintf(stdout, "Performance Test Results:\n");
176 : fprintf(stdout, "%s",
177 : OUStringToOString(m_aOutputBuffer.makeStringAndClear(),
178 : RTL_TEXTENCODING_UTF8)
179 : .getStr()
180 0 : );
181 :
182 : // We want the results printed, but unit test output is only printed on failure
183 : // Hence we deliberately fail the test.
184 0 : CPPUNIT_ASSERT(false);
185 : }
186 :
187 0 : void EmbeddedDBPerformanceTest::testFirebird()
188 : {
189 :
190 0 : m_aOutputBuffer.append("Standard Insert\n");
191 0 : doPerformanceTestOnODB("sdbc:embedded:firebird", "Firebird", false);
192 0 : m_aOutputBuffer.append("PreparedStatement Insert\n");
193 0 : doPerformanceTestOnODB("sdbc:embedded:firebird", "Firebird", true);
194 0 : }
195 :
196 0 : void EmbeddedDBPerformanceTest::testHSQLDB()
197 : {
198 0 : m_aOutputBuffer.append("Standard Insert\n");
199 0 : doPerformanceTestOnODB("sdbc:embedded:hsqldb", "HSQLDB", false);
200 0 : m_aOutputBuffer.append("PreparedStatement Insert\n");
201 0 : doPerformanceTestOnODB("sdbc:embedded:hsqldb", "HSQLDB", true);
202 0 : }
203 :
204 : /**
205 : * Use an existing .odb to do performance tests on. The database cannot have
206 : * a table of the name PFTESTTABLE.
207 : */
208 0 : void EmbeddedDBPerformanceTest::doPerformanceTestOnODB(
209 : const OUString& rDriverURL,
210 : const OUString& rDBName,
211 : const bool bUsePreparedStatement)
212 : {
213 0 : ::utl::TempFile aFile;
214 0 : aFile.EnableKillingFile();
215 :
216 : {
217 : uno::Reference< XOfficeDatabaseDocument > xDocument(
218 0 : m_xSFactory->createInstance("com.sun.star.sdb.OfficeDatabaseDocument"),
219 0 : UNO_QUERY_THROW);
220 0 : uno::Reference< XStorable > xStorable(xDocument, UNO_QUERY_THROW);
221 :
222 0 : uno::Reference< XDataSource > xDataSource = xDocument->getDataSource();
223 0 : uno::Reference< XPropertySet > xPropertySet(xDataSource, UNO_QUERY_THROW);
224 0 : xPropertySet->setPropertyValue("URL", Any(rDriverURL));
225 :
226 0 : xStorable->storeAsURL(aFile.GetURL(), uno::Sequence< beans::PropertyValue >());
227 : }
228 :
229 : uno::Reference< XOfficeDatabaseDocument > xDocument(
230 0 : loadFromDesktop(aFile.GetURL()), UNO_QUERY_THROW);
231 :
232 : uno::Reference< XConnection > xConnection =
233 0 : getConnectionForDocument(xDocument);
234 :
235 0 : setupTestTable(xConnection);
236 :
237 0 : if (bUsePreparedStatement)
238 0 : performPreparedStatementInsertTest(xConnection, rDBName);
239 : else
240 0 : performStatementInsertTest(xConnection, rDBName);
241 :
242 0 : performReadTest(xConnection, rDBName);
243 0 : }
244 :
245 0 : void EmbeddedDBPerformanceTest::setupTestTable(
246 : uno::Reference< XConnection >& xConnection)
247 : {
248 0 : uno::Reference< XStatement > xStatement = xConnection->createStatement();
249 :
250 : // Although not strictly necessary we use quoted identifiers to reflect
251 : // the fact that Base always uses quoted identifiers.
252 0 : xStatement->execute(
253 : "CREATE TABLE \"PFTESTTABLE\" ( \"ID\" INTEGER NOT NULL PRIMARY KEY "
254 : ", \"STRINGCOLUMNA\" VARCHAR (50) "
255 0 : ")");
256 :
257 0 : xConnection->commit();
258 0 : }
259 :
260 0 : void EmbeddedDBPerformanceTest::performPreparedStatementInsertTest(
261 : uno::Reference< XConnection >& xConnection,
262 : const OUString& rDBName)
263 : {
264 : uno::Reference< XPreparedStatement > xPreparedStatement =
265 0 : xConnection->prepareStatement(
266 : "INSERT INTO \"PFTESTTABLE\" ( \"ID\", "
267 : "\"STRINGCOLUMNA\" "
268 : ") VALUES ( ?, ? )"
269 0 : );
270 :
271 0 : uno::Reference< XParameters > xParameters(xPreparedStatement, UNO_QUERY_THROW);
272 :
273 0 : ::boost::scoped_ptr< SvFileStream > pFile(getWordListStream());
274 :
275 0 : OUString aWord;
276 0 : sal_Int32 aID = 0;
277 :
278 : TimeValue aStart, aMiddle, aEnd;
279 0 : osl_getSystemTime(&aStart);
280 :
281 0 : while (pFile->ReadByteStringLine(aWord, RTL_TEXTENCODING_UTF8))
282 : {
283 0 : xParameters->setInt(1, aID++);
284 0 : xParameters->setString(2, aWord);
285 0 : xPreparedStatement->execute();
286 : }
287 0 : osl_getSystemTime(&aMiddle);
288 0 : xConnection->commit();
289 0 : osl_getSystemTime(&aEnd);
290 :
291 :
292 : TimeValue aTimeInsert, aTimeCommit, aTimeTotal;
293 0 : getTimeDifference(&aStart, &aMiddle, &aTimeInsert);
294 0 : getTimeDifference(&aMiddle, &aEnd, &aTimeCommit);
295 0 : getTimeDifference(&aStart, &aEnd, &aTimeTotal);
296 0 : m_aOutputBuffer.append("Insert: " + rDBName + "\n");
297 0 : printTimes(&aTimeInsert, &aTimeCommit, &aTimeTotal);
298 :
299 0 : pFile->Close();
300 0 : }
301 :
302 0 : void EmbeddedDBPerformanceTest::performStatementInsertTest(
303 : uno::Reference< XConnection >& xConnection,
304 : const OUString& rDBName)
305 : {
306 : uno::Reference< XStatement > xStatement =
307 0 : xConnection->createStatement();
308 :
309 0 : ::boost::scoped_ptr< SvFileStream > pFile(getWordListStream());
310 :
311 0 : OUString aWord;
312 0 : sal_Int32 aID = 0;
313 :
314 : TimeValue aStart, aMiddle, aEnd;
315 0 : osl_getSystemTime(&aStart);
316 :
317 0 : while (pFile->ReadByteStringLine(aWord, RTL_TEXTENCODING_UTF8))
318 : {
319 0 : xStatement->execute(
320 : "INSERT INTO \"PFTESTTABLE\" ( \"ID\", "
321 : "\"STRINGCOLUMNA\" "
322 : ") VALUES ( "
323 0 : + OUString::number(aID++) + ", '" + aWord + "' )"
324 0 : );
325 : }
326 0 : osl_getSystemTime(&aMiddle);
327 0 : xConnection->commit();
328 0 : osl_getSystemTime(&aEnd);
329 :
330 : TimeValue aTimeInsert, aTimeCommit, aTimeTotal;
331 0 : getTimeDifference(&aStart, &aMiddle, &aTimeInsert);
332 0 : getTimeDifference(&aMiddle, &aEnd, &aTimeCommit);
333 0 : getTimeDifference(&aStart, &aEnd, &aTimeTotal);
334 0 : m_aOutputBuffer.append("Insert: " + rDBName + "\n");
335 0 : printTimes(&aTimeInsert, &aTimeCommit, &aTimeTotal);
336 :
337 0 : pFile->Close();
338 0 : }
339 :
340 0 : void EmbeddedDBPerformanceTest::performReadTest(
341 : uno::Reference< XConnection >& xConnection,
342 : const OUString& rDBName)
343 : {
344 0 : uno::Reference< XStatement > xStatement = xConnection->createStatement();
345 :
346 : TimeValue aStart, aMiddle, aEnd;
347 0 : osl_getSystemTime(&aStart);
348 :
349 0 : uno::Reference< XResultSet > xResults = xStatement->executeQuery("SELECT * FROM PFTESTTABLE");
350 :
351 0 : osl_getSystemTime(&aMiddle);
352 :
353 0 : uno::Reference< XRow > xRow(xResults, UNO_QUERY_THROW);
354 :
355 0 : while (xResults->next())
356 : {
357 0 : xRow->getString(2);
358 : }
359 0 : osl_getSystemTime(&aEnd);
360 :
361 : TimeValue aTimeSelect, aTimeIterate, aTimeTotal;
362 0 : getTimeDifference(&aStart, &aMiddle, &aTimeSelect);
363 0 : getTimeDifference(&aMiddle, &aEnd, &aTimeIterate);
364 0 : getTimeDifference(&aStart, &aEnd, &aTimeTotal);
365 0 : m_aOutputBuffer.append("Read from: " + rDBName + "\n");
366 0 : printTimes(&aTimeSelect, &aTimeIterate, &aTimeTotal);
367 0 : }
368 :
369 2 : CPPUNIT_TEST_SUITE_REGISTRATION(EmbeddedDBPerformanceTest);
370 :
371 8 : CPPUNIT_PLUGIN_IMPLEMENT();
372 :
373 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
