2 * Copyright (c) 2011 Samsung Electronics Co., Ltd All Rights Reserved
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
8 * http://www.apache.org/licenses/LICENSE-2.0
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
17 * @file sql_connection.cpp
18 * @author Przemyslaw Dobrowolski (p.dobrowolsk@samsung.com)
20 * @brief This file is the implementation file of SQL connection
23 #include <dpl/db/sql_connection.h>
24 #include <dpl/db/naive_synchronization_object.h>
25 #include <dpl/scoped_free.h>
26 #include <dpl/noncopyable.h>
27 #include <dpl/assert.h>
35 namespace // anonymous
37 class ScopedNotifyAll :
41 SqlConnection::SynchronizationObject *m_synchronizationObject;
44 explicit ScopedNotifyAll(
45 SqlConnection::SynchronizationObject *synchronizationObject) :
46 m_synchronizationObject(synchronizationObject)
51 if (!m_synchronizationObject) {
55 LogPedantic("Notifying after successful synchronize");
56 m_synchronizationObject->NotifyAll();
59 } // namespace anonymous
61 SqlConnection::DataCommand::DataCommand(SqlConnection *connection,
63 m_masterConnection(connection),
66 Assert(connection != NULL);
68 // Notify all after potentially synchronized database connection access
69 ScopedNotifyAll notifyAll(connection->m_synchronizationObject.get());
72 int ret = sqlite3_prepare_v2(connection->m_connection,
73 buffer, strlen(buffer),
76 if (ret == SQLITE_OK) {
77 LogPedantic("Data command prepared successfuly");
79 } else if (ret == SQLITE_BUSY) {
80 LogPedantic("Collision occurred while preparing SQL command");
82 // Synchronize if synchronization object is available
83 if (connection->m_synchronizationObject) {
84 LogPedantic("Performing synchronization");
85 connection->m_synchronizationObject->Synchronize();
89 // No synchronization object defined. Fail.
93 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
95 LogPedantic("SQL prepare data command failed");
96 LogPedantic(" Statement: " << buffer);
97 LogPedantic(" Error: " << error);
99 ThrowMsg(Exception::SyntaxError, error);
102 LogPedantic("Prepared data command: " << buffer);
104 // Increment stored data command count
105 ++m_masterConnection->m_dataCommandsCount;
108 SqlConnection::DataCommand::~DataCommand()
110 LogPedantic("SQL data command finalizing");
112 if (sqlite3_finalize(m_stmt) != SQLITE_OK) {
113 LogPedantic("Failed to finalize data command");
116 // Decrement stored data command count
117 --m_masterConnection->m_dataCommandsCount;
120 void SqlConnection::DataCommand::CheckBindResult(int result)
122 if (result != SQLITE_OK) {
123 const char *error = sqlite3_errmsg(
124 m_masterConnection->m_connection);
126 LogPedantic("Failed to bind SQL statement parameter");
127 LogPedantic(" Error: " << error);
129 ThrowMsg(Exception::SyntaxError, error);
133 void SqlConnection::DataCommand::BindNull(
134 SqlConnection::ArgumentIndex position)
136 CheckBindResult(sqlite3_bind_null(m_stmt, position));
137 LogPedantic("SQL data command bind null: ["
141 void SqlConnection::DataCommand::BindInteger(
142 SqlConnection::ArgumentIndex position,
145 CheckBindResult(sqlite3_bind_int(m_stmt, position, value));
146 LogPedantic("SQL data command bind integer: ["
147 << position << "] -> " << value);
150 void SqlConnection::DataCommand::BindInt8(
151 SqlConnection::ArgumentIndex position,
154 CheckBindResult(sqlite3_bind_int(m_stmt, position,
155 static_cast<int>(value)));
156 LogPedantic("SQL data command bind int8: ["
157 << position << "] -> " << value);
160 void SqlConnection::DataCommand::BindInt16(
161 SqlConnection::ArgumentIndex position,
164 CheckBindResult(sqlite3_bind_int(m_stmt, position,
165 static_cast<int>(value)));
166 LogPedantic("SQL data command bind int16: ["
167 << position << "] -> " << value);
170 void SqlConnection::DataCommand::BindInt32(
171 SqlConnection::ArgumentIndex position,
174 CheckBindResult(sqlite3_bind_int(m_stmt, position,
175 static_cast<int>(value)));
176 LogPedantic("SQL data command bind int32: ["
177 << position << "] -> " << value);
180 void SqlConnection::DataCommand::BindInt64(
181 SqlConnection::ArgumentIndex position,
184 CheckBindResult(sqlite3_bind_int64(m_stmt, position,
185 static_cast<sqlite3_int64>(value)));
186 LogPedantic("SQL data command bind int64: ["
187 << position << "] -> " << value);
190 void SqlConnection::DataCommand::BindFloat(
191 SqlConnection::ArgumentIndex position,
194 CheckBindResult(sqlite3_bind_double(m_stmt, position,
195 static_cast<double>(value)));
196 LogPedantic("SQL data command bind float: ["
197 << position << "] -> " << value);
200 void SqlConnection::DataCommand::BindDouble(
201 SqlConnection::ArgumentIndex position,
204 CheckBindResult(sqlite3_bind_double(m_stmt, position, value));
205 LogPedantic("SQL data command bind double: ["
206 << position << "] -> " << value);
209 void SqlConnection::DataCommand::BindString(
210 SqlConnection::ArgumentIndex position,
218 // Assume that text may disappear
219 CheckBindResult(sqlite3_bind_text(m_stmt, position,
220 value, strlen(value),
223 LogPedantic("SQL data command bind string: ["
224 << position << "] -> " << value);
227 void SqlConnection::DataCommand::BindString(
228 SqlConnection::ArgumentIndex position,
231 BindString(position, ToUTF8String(value).c_str());
234 void SqlConnection::DataCommand::BindInteger(
235 SqlConnection::ArgumentIndex position,
236 const Optional<int> &value)
238 if (value.IsNull()) {
241 BindInteger(position, *value);
245 void SqlConnection::DataCommand::BindInt8(
246 SqlConnection::ArgumentIndex position,
247 const Optional<int8_t> &value)
249 if (value.IsNull()) {
252 BindInt8(position, *value);
256 void SqlConnection::DataCommand::BindInt16(
257 SqlConnection::ArgumentIndex position,
258 const Optional<int16_t> &value)
260 if (value.IsNull()) {
263 BindInt16(position, *value);
267 void SqlConnection::DataCommand::BindInt32(
268 SqlConnection::ArgumentIndex position,
269 const Optional<int32_t> &value)
271 if (value.IsNull()) {
274 BindInt32(position, *value);
278 void SqlConnection::DataCommand::BindInt64(
279 SqlConnection::ArgumentIndex position,
280 const Optional<int64_t> &value)
282 if (value.IsNull()) {
285 BindInt64(position, *value);
289 void SqlConnection::DataCommand::BindFloat(
290 SqlConnection::ArgumentIndex position,
291 const Optional<float> &value)
293 if (value.IsNull()) {
296 BindFloat(position, *value);
300 void SqlConnection::DataCommand::BindDouble(
301 SqlConnection::ArgumentIndex position,
302 const Optional<double> &value)
304 if (value.IsNull()) {
307 BindDouble(position, *value);
311 void SqlConnection::DataCommand::BindString(
312 SqlConnection::ArgumentIndex position,
313 const Optional<String> &value)
316 BindString(position, ToUTF8String(*value).c_str());
322 bool SqlConnection::DataCommand::Step()
324 // Notify all after potentially synchronized database connection access
325 ScopedNotifyAll notifyAll(
326 m_masterConnection->m_synchronizationObject.get());
329 int ret = sqlite3_step(m_stmt);
331 if (ret == SQLITE_ROW) {
332 LogPedantic("SQL data command step ROW");
334 } else if (ret == SQLITE_DONE) {
335 LogPedantic("SQL data command step DONE");
337 } else if (ret == SQLITE_BUSY) {
338 LogPedantic("Collision occurred while executing SQL command");
340 // Synchronize if synchronization object is available
341 if (m_masterConnection->m_synchronizationObject) {
342 LogPedantic("Performing synchronization");
345 m_synchronizationObject->Synchronize();
350 // No synchronization object defined. Fail.
354 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
356 LogPedantic("SQL step data command failed");
357 LogPedantic(" Error: " << error);
359 ThrowMsg(Exception::InternalError, error);
363 void SqlConnection::DataCommand::Reset()
367 * http://www.sqlite.org/c3ref/stmt.html
369 * if last sqlite3_step command on this stmt returned an error,
370 * then sqlite3_reset will return that error, althought it is not an error.
371 * So sqlite3_reset allways succedes.
373 sqlite3_reset(m_stmt);
375 LogPedantic("SQL data command reset");
378 void SqlConnection::DataCommand::CheckColumnIndex(
379 SqlConnection::ColumnIndex column)
381 if (column < 0 || column >= sqlite3_column_count(m_stmt)) {
382 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
386 bool SqlConnection::DataCommand::IsColumnNull(
387 SqlConnection::ColumnIndex column)
389 LogPedantic("SQL data command get column type: [" << column << "]");
390 CheckColumnIndex(column);
391 return sqlite3_column_type(m_stmt, column) == SQLITE_NULL;
394 int SqlConnection::DataCommand::GetColumnInteger(
395 SqlConnection::ColumnIndex column)
397 LogPedantic("SQL data command get column integer: [" << column << "]");
398 CheckColumnIndex(column);
399 int value = sqlite3_column_int(m_stmt, column);
400 LogPedantic(" Value: " << value);
404 int8_t SqlConnection::DataCommand::GetColumnInt8(
405 SqlConnection::ColumnIndex column)
407 LogPedantic("SQL data command get column int8: [" << column << "]");
408 CheckColumnIndex(column);
409 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
410 LogPedantic(" Value: " << value);
414 int16_t SqlConnection::DataCommand::GetColumnInt16(
415 SqlConnection::ColumnIndex column)
417 LogPedantic("SQL data command get column int16: [" << column << "]");
418 CheckColumnIndex(column);
419 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
420 LogPedantic(" Value: " << value);
424 int32_t SqlConnection::DataCommand::GetColumnInt32(
425 SqlConnection::ColumnIndex column)
427 LogPedantic("SQL data command get column int32: [" << column << "]");
428 CheckColumnIndex(column);
429 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
430 LogPedantic(" Value: " << value);
434 int64_t SqlConnection::DataCommand::GetColumnInt64(
435 SqlConnection::ColumnIndex column)
437 LogPedantic("SQL data command get column int64: [" << column << "]");
438 CheckColumnIndex(column);
439 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
440 LogPedantic(" Value: " << value);
444 float SqlConnection::DataCommand::GetColumnFloat(
445 SqlConnection::ColumnIndex column)
447 LogPedantic("SQL data command get column float: [" << column << "]");
448 CheckColumnIndex(column);
449 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
450 LogPedantic(" Value: " << value);
454 double SqlConnection::DataCommand::GetColumnDouble(
455 SqlConnection::ColumnIndex column)
457 LogPedantic("SQL data command get column double: [" << column << "]");
458 CheckColumnIndex(column);
459 double value = sqlite3_column_double(m_stmt, column);
460 LogPedantic(" Value: " << value);
464 std::string SqlConnection::DataCommand::GetColumnString(
465 SqlConnection::ColumnIndex column)
467 LogPedantic("SQL data command get column string: [" << column << "]");
468 CheckColumnIndex(column);
470 const char *value = reinterpret_cast<const char *>(
471 sqlite3_column_text(m_stmt, column));
473 LogPedantic("Value: " << (value ? value : "NULL"));
476 return std::string();
479 return std::string(value);
482 Optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
483 SqlConnection::ColumnIndex column)
485 LogPedantic("SQL data command get column optional integer: ["
487 CheckColumnIndex(column);
488 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
489 return Optional<int>::Null;
491 int value = sqlite3_column_int(m_stmt, column);
492 LogPedantic(" Value: " << value);
493 return Optional<int>(value);
496 Optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
497 SqlConnection::ColumnIndex column)
499 LogPedantic("SQL data command get column optional int8: ["
501 CheckColumnIndex(column);
502 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
503 return Optional<int8_t>::Null;
505 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
506 LogPedantic(" Value: " << value);
507 return Optional<int8_t>(value);
510 Optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
511 SqlConnection::ColumnIndex column)
513 LogPedantic("SQL data command get column optional int16: ["
515 CheckColumnIndex(column);
516 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
517 return Optional<int16_t>::Null;
519 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
520 LogPedantic(" Value: " << value);
521 return Optional<int16_t>(value);
524 Optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
525 SqlConnection::ColumnIndex column)
527 LogPedantic("SQL data command get column optional int32: ["
529 CheckColumnIndex(column);
530 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
531 return Optional<int32_t>::Null;
533 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
534 LogPedantic(" Value: " << value);
535 return Optional<int32_t>(value);
538 Optional<int64_t> SqlConnection::DataCommand::GetColumnOptionalInt64(
539 SqlConnection::ColumnIndex column)
541 LogPedantic("SQL data command get column optional int64: ["
543 CheckColumnIndex(column);
544 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
545 return Optional<int64_t>::Null;
547 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
548 LogPedantic(" Value: " << value);
549 return Optional<int64_t>(value);
552 Optional<float> SqlConnection::DataCommand::GetColumnOptionalFloat(
553 SqlConnection::ColumnIndex column)
555 LogPedantic("SQL data command get column optional float: ["
557 CheckColumnIndex(column);
558 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
559 return Optional<float>::Null;
561 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
562 LogPedantic(" Value: " << value);
563 return Optional<float>(value);
566 Optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
567 SqlConnection::ColumnIndex column)
569 LogPedantic("SQL data command get column optional double: ["
571 CheckColumnIndex(column);
572 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
573 return Optional<double>::Null;
575 double value = sqlite3_column_double(m_stmt, column);
576 LogPedantic(" Value: " << value);
577 return Optional<double>(value);
580 Optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
581 SqlConnection::ColumnIndex column)
583 LogPedantic("SQL data command get column optional string: ["
585 CheckColumnIndex(column);
586 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
587 return Optional<String>::Null;
589 const char *value = reinterpret_cast<const char *>(
590 sqlite3_column_text(m_stmt, column));
591 LogPedantic("Value: " << value);
592 String s = FromUTF8String(value);
593 return Optional<String>(s);
596 void SqlConnection::Connect(const std::string &address,
600 if (m_connection != NULL) {
601 LogPedantic("Already connected.");
604 LogPedantic("Connecting to DB: " << address << "...");
606 // Connect to database
608 if (type & Flag::UseLucene) {
609 result = db_util_open_with_options(
615 m_usingLucene = true;
616 LogPedantic("Lucene index enabled");
618 result = sqlite3_open_v2(
624 m_usingLucene = false;
625 LogPedantic("Lucene index disabled");
628 if (result == SQLITE_OK) {
629 LogPedantic("Connected to DB");
631 LogPedantic("Failed to connect to DB!");
632 ThrowMsg(Exception::ConnectionBroken, address);
635 // Enable foreign keys
639 void SqlConnection::Disconnect()
641 if (m_connection == NULL) {
642 LogPedantic("Already disconnected.");
646 LogPedantic("Disconnecting from DB...");
648 // All stored data commands must be deleted before disconnect
649 Assert(m_dataCommandsCount == 0 &&
650 "All stored procedures must be deleted"
651 " before disconnecting SqlConnection");
656 result = db_util_close(m_connection);
658 result = sqlite3_close(m_connection);
661 if (result != SQLITE_OK) {
662 const char *error = sqlite3_errmsg(m_connection);
663 LogPedantic("SQL close failed");
664 LogPedantic(" Error: " << error);
665 Throw(Exception::InternalError);
670 LogPedantic("Disconnected from DB");
673 bool SqlConnection::CheckTableExist(const char *tableName)
675 if (m_connection == NULL) {
676 LogPedantic("Cannot execute command. Not connected to DB!");
680 DataCommandAutoPtr command =
681 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
683 command->BindString(1, tableName);
685 if (!command->Step()) {
686 LogPedantic("No matching records in table");
690 return command->GetColumnString(0) == tableName;
693 SqlConnection::SqlConnection(const std::string &address,
696 SynchronizationObject *synchronizationObject) :
698 m_usingLucene(false),
699 m_dataCommandsCount(0),
700 m_synchronizationObject(synchronizationObject)
702 LogPedantic("Opening database connection to: " << address);
705 SqlConnection::Connect(address, flag, option);
707 if (!m_synchronizationObject) {
708 LogPedantic("No synchronization object defined");
712 SqlConnection::~SqlConnection()
714 LogPedantic("Closing database connection");
716 // Disconnect from DB
719 SqlConnection::Disconnect();
721 Catch(Exception::Base)
723 LogPedantic("Failed to disconnect from database");
727 void SqlConnection::ExecCommand(const char *format, ...)
729 if (m_connection == NULL) {
730 LogPedantic("Cannot execute command. Not connected to DB!");
734 if (format == NULL) {
735 LogPedantic("Null query!");
736 ThrowMsg(Exception::SyntaxError, "Null statement");
742 va_start(args, format);
744 if (vasprintf(&rawBuffer, format, args) == -1) {
750 ScopedFree<char> buffer(rawBuffer);
753 LogPedantic("Failed to allocate statement string");
757 LogPedantic("Executing SQL command: " << buffer.Get());
759 // Notify all after potentially synchronized database connection access
760 ScopedNotifyAll notifyAll(m_synchronizationObject.get());
765 int ret = sqlite3_exec(m_connection,
771 std::string errorMsg;
773 // Take allocated error buffer
774 if (errorBuffer != NULL) {
775 errorMsg = errorBuffer;
776 sqlite3_free(errorBuffer);
779 if (ret == SQLITE_OK) {
783 if (ret == SQLITE_BUSY) {
784 LogPedantic("Collision occurred while executing SQL command");
786 // Synchronize if synchronization object is available
787 if (m_synchronizationObject) {
788 LogPedantic("Performing synchronization");
789 m_synchronizationObject->Synchronize();
793 // No synchronization object defined. Fail.
797 LogPedantic("Failed to execute SQL command. Error: " << errorMsg);
798 ThrowMsg(Exception::SyntaxError, errorMsg);
802 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
806 if (m_connection == NULL) {
807 LogPedantic("Cannot execute data command. Not connected to DB!");
808 return DataCommandAutoPtr();
814 va_start(args, format);
816 if (vasprintf(&rawBuffer, format, args) == -1) {
822 ScopedFree<char> buffer(rawBuffer);
825 LogPedantic("Failed to allocate statement string");
826 return DataCommandAutoPtr();
829 LogPedantic("Executing SQL data command: " << buffer.Get());
831 return DataCommandAutoPtr(new DataCommand(this, buffer.Get()));
834 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
836 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
839 void SqlConnection::TurnOnForeignKeys()
841 ExecCommand("PRAGMA foreign_keys = ON;");
844 SqlConnection::SynchronizationObject *
845 SqlConnection::AllocDefaultSynchronizationObject()
847 return new NaiveSynchronizationObject();