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/free_deleter.h>
27 #include <dpl/noncopyable.h>
28 #include <dpl/assert.h>
29 #include <dpl/log/log.h>
36 namespace // anonymous
38 class ScopedNotifyAll :
42 SqlConnection::SynchronizationObject *m_synchronizationObject;
45 explicit ScopedNotifyAll(
46 SqlConnection::SynchronizationObject *synchronizationObject) :
47 m_synchronizationObject(synchronizationObject)
52 if (!m_synchronizationObject) {
56 LogDebug("Notifying after successful synchronize");
57 m_synchronizationObject->NotifyAll();
60 } // namespace anonymous
62 SqlConnection::DataCommand::DataCommand(SqlConnection *connection,
64 m_masterConnection(connection),
67 Assert(connection != NULL);
69 // Notify all after potentially synchronized database connection access
70 ScopedNotifyAll notifyAll(connection->m_synchronizationObject.get());
73 int ret = sqlite3_prepare_v2(connection->m_connection,
74 buffer, strlen(buffer),
77 if (ret == SQLITE_OK) {
78 LogDebug("Data command prepared successfuly");
80 } else if (ret == SQLITE_BUSY) {
81 LogDebug("Collision occurred while preparing SQL command");
83 // Synchronize if synchronization object is available
84 if (connection->m_synchronizationObject) {
85 LogDebug("Performing synchronization");
86 connection->m_synchronizationObject->Synchronize();
90 // No synchronization object defined. Fail.
94 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
96 LogDebug("SQL prepare data command failed");
97 LogDebug(" Statement : " << buffer);
98 LogDebug(" Error : " << error);
100 ThrowMsg(Exception::SyntaxError, error);
103 LogDebug("Prepared data command : " << buffer);
105 // Increment stored data command count
106 ++m_masterConnection->m_dataCommandsCount;
109 SqlConnection::DataCommand::~DataCommand()
111 LogDebug("SQL data command finalizing");
113 if (sqlite3_finalize(m_stmt) != SQLITE_OK) {
114 LogDebug("Failed to finalize data command");
117 // Decrement stored data command count
118 --m_masterConnection->m_dataCommandsCount;
121 void SqlConnection::DataCommand::CheckBindResult(int result)
123 if (result != SQLITE_OK) {
124 const char *error = sqlite3_errmsg(
125 m_masterConnection->m_connection);
127 LogDebug("Failed to bind SQL statement parameter");
128 LogDebug(" Error : " << error);
130 ThrowMsg(Exception::SyntaxError, error);
134 void SqlConnection::DataCommand::BindNull(
135 SqlConnection::ArgumentIndex position)
137 CheckBindResult(sqlite3_bind_null(m_stmt, position));
138 LogDebug("SQL data command bind null: [" << position << "]");
141 void SqlConnection::DataCommand::BindInteger(
142 SqlConnection::ArgumentIndex position,
145 CheckBindResult(sqlite3_bind_int(m_stmt, position, value));
146 LogDebug("SQL data command bind integer: [" << position << "] -> " << value);
149 void SqlConnection::DataCommand::BindInt8(
150 SqlConnection::ArgumentIndex position,
153 CheckBindResult(sqlite3_bind_int(m_stmt, position,
154 static_cast<int>(value)));
155 LogDebug("SQL data command bind int8: [" << position << "] -> " << value);
158 void SqlConnection::DataCommand::BindInt16(
159 SqlConnection::ArgumentIndex position,
162 CheckBindResult(sqlite3_bind_int(m_stmt, position,
163 static_cast<int>(value)));
164 LogDebug("SQL data command bind int16: [" << position << "] -> " << value);}
166 void SqlConnection::DataCommand::BindInt32(
167 SqlConnection::ArgumentIndex position,
170 CheckBindResult(sqlite3_bind_int(m_stmt, position,
171 static_cast<int>(value)));
172 LogDebug("SQL data command bind int32: [" << position << "] -> " << value);
175 void SqlConnection::DataCommand::BindInt64(
176 SqlConnection::ArgumentIndex position,
179 CheckBindResult(sqlite3_bind_int64(m_stmt, position,
180 static_cast<sqlite3_int64>(value)));
181 LogDebug("SQL data command bind int64: [" << position << "] -> " << value);
184 void SqlConnection::DataCommand::BindFloat(
185 SqlConnection::ArgumentIndex position,
188 CheckBindResult(sqlite3_bind_double(m_stmt, position,
189 static_cast<double>(value)));
190 LogDebug("SQL data command bind float: [" << position << "] -> " << value);
193 void SqlConnection::DataCommand::BindDouble(
194 SqlConnection::ArgumentIndex position,
197 CheckBindResult(sqlite3_bind_double(m_stmt, position, value));
198 LogDebug("SQL data command bind double: [" << position << "] -> " << value);
201 void SqlConnection::DataCommand::BindString(
202 SqlConnection::ArgumentIndex position,
210 // Assume that text may disappear
211 CheckBindResult(sqlite3_bind_text(m_stmt, position,
212 value, strlen(value),
215 LogDebug("SQL data command bind string: [" << position << "] -> " << value);
218 void SqlConnection::DataCommand::BindString(
219 SqlConnection::ArgumentIndex position,
222 BindString(position, ToUTF8String(value).c_str());
225 void SqlConnection::DataCommand::BindInteger(
226 SqlConnection::ArgumentIndex position,
227 const boost::optional<int> &value)
232 BindInteger(position, *value);
236 void SqlConnection::DataCommand::BindInt8(
237 SqlConnection::ArgumentIndex position,
238 const boost::optional<int8_t> &value)
243 BindInt8(position, *value);
247 void SqlConnection::DataCommand::BindInt16(
248 SqlConnection::ArgumentIndex position,
249 const boost::optional<int16_t> &value)
254 BindInt16(position, *value);
258 void SqlConnection::DataCommand::BindInt32(
259 SqlConnection::ArgumentIndex position,
260 const boost::optional<int32_t> &value)
265 BindInt32(position, *value);
269 void SqlConnection::DataCommand::BindInt64(
270 SqlConnection::ArgumentIndex position,
271 const boost::optional<int64_t> &value)
276 BindInt64(position, *value);
280 void SqlConnection::DataCommand::BindFloat(
281 SqlConnection::ArgumentIndex position,
282 const boost::optional<float> &value)
287 BindFloat(position, *value);
291 void SqlConnection::DataCommand::BindDouble(
292 SqlConnection::ArgumentIndex position,
293 const boost::optional<double> &value)
298 BindDouble(position, *value);
302 void SqlConnection::DataCommand::BindString(
303 SqlConnection::ArgumentIndex position,
304 const boost::optional<String> &value)
307 BindString(position, ToUTF8String(*value).c_str());
313 bool SqlConnection::DataCommand::Step()
315 // Notify all after potentially synchronized database connection access
316 ScopedNotifyAll notifyAll(
317 m_masterConnection->m_synchronizationObject.get());
320 int ret = sqlite3_step(m_stmt);
322 if (ret == SQLITE_ROW) {
323 LogDebug("SQL data command step ROW");
325 } else if (ret == SQLITE_DONE) {
326 LogDebug("SQL data command step DONE");
328 } else if (ret == SQLITE_BUSY) {
329 LogDebug("Collision occurred while executing SQL command");
331 // Synchronize if synchronization object is available
332 if (m_masterConnection->m_synchronizationObject) {
333 LogDebug("Performing synchronization");
336 m_synchronizationObject->Synchronize();
341 // No synchronization object defined. Fail.
345 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
347 LogDebug("SQL step data command failed");
348 LogDebug(" Error : " << error);
350 ThrowMsg(Exception::InternalError, error);
354 void SqlConnection::DataCommand::Reset()
358 * http://www.sqlite.org/c3ref/stmt.html
360 * if last sqlite3_step command on this stmt returned an error,
361 * then sqlite3_reset will return that error, althought it is not an error.
362 * So sqlite3_reset allways succedes.
364 sqlite3_reset(m_stmt);
366 LogDebug("SQL data command reset");
369 void SqlConnection::DataCommand::CheckColumnIndex(
370 SqlConnection::ColumnIndex column)
372 if (column < 0 || column >= sqlite3_column_count(m_stmt)) {
373 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
377 bool SqlConnection::DataCommand::IsColumnNull(
378 SqlConnection::ColumnIndex column)
380 LogDebug("SQL data command get column type: [" << column << "]");
381 CheckColumnIndex(column);
382 return sqlite3_column_type(m_stmt, column) == SQLITE_NULL;
385 int SqlConnection::DataCommand::GetColumnInteger(
386 SqlConnection::ColumnIndex column)
388 LogDebug("SQL data command get column integer: [" << column << "]");
389 CheckColumnIndex(column);
390 int value = sqlite3_column_int(m_stmt, column);
391 LogDebug(" Value : " << value);
395 int8_t SqlConnection::DataCommand::GetColumnInt8(
396 SqlConnection::ColumnIndex column)
398 LogDebug("SQL data command get column int8: [" << column << "]");
399 CheckColumnIndex(column);
400 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
401 LogDebug(" Value : " << value);
405 int16_t SqlConnection::DataCommand::GetColumnInt16(
406 SqlConnection::ColumnIndex column)
408 LgDebug("SQL data command get column int16: [" << column << "]");
409 CheckColumnIndex(column);
410 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
411 LogDebug(" Value : " << value);
415 int32_t SqlConnection::DataCommand::GetColumnInt32(
416 SqlConnection::ColumnIndex column)
418 LogDebug("SQL data command get column int32: [" << column << "]");
419 CheckColumnIndex(column);
420 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
421 LogDebug(" Value : " << value);
425 int64_t SqlConnection::DataCommand::GetColumnInt64(
426 SqlConnection::ColumnIndex column)
428 LogDebug("SQL data command get column int64: [" << column << "]");
429 CheckColumnIndex(column);
430 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
431 LogDebug(" Value : " << value);
435 float SqlConnection::DataCommand::GetColumnFloat(
436 SqlConnection::ColumnIndex column)
438 LogDebug("SQL data command get column float: [" << column << "]");
439 CheckColumnIndex(column);
440 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
441 LogDebug(" Value : " << value);
445 double SqlConnection::DataCommand::GetColumnDouble(
446 SqlConnection::ColumnIndex column)
448 LogDebug("SQL data command get column double: [" << column << "]");
449 CheckColumnIndex(column);
450 double value = sqlite3_column_double(m_stmt, column);
451 LogDebug(" Value : " << value);
455 std::string SqlConnection::DataCommand::GetColumnString(
456 SqlConnection::ColumnIndex column)
458 LogDebug("SQL data command get column string: [" << column << "]");
459 CheckColumnIndex(column);
461 const char *value = reinterpret_cast<const char *>(
462 sqlite3_column_text(m_stmt, column));
464 LogDebug(" Value : " << value);
467 return std::string();
470 return std::string(value);
473 boost::optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
474 SqlConnection::ColumnIndex column)
476 LogDebug("SQL data command get column optional integer: [" << column << "]");
477 CheckColumnIndex(column);
478 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
479 return boost::optional<int>();
481 int value = sqlite3_column_int(m_stmt, column);
482 LogDebug(" Value : " << value);
483 return boost::optional<int>(value);
486 boost::optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
487 SqlConnection::ColumnIndex column)
489 LogDebug("SQL data command get column optional int8: [" << column << "]");
490 CheckColumnIndex(column);
491 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
492 return boost::optional<int8_t>();
494 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
495 LogDebug(" Value: " << value);
496 return boost::optional<int8_t>(value);
499 boost::optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
500 SqlConnection::ColumnIndex column)
502 LogDebug("SQL data command get column optional int16: [" << column << "]");
503 CheckColumnIndex(column);
504 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
505 return boost::optional<int16_t>();
507 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
508 LogDebug(" Value : " << value);
509 return boost::optional<int16_t>(value);
512 boost::optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
513 SqlConnection::ColumnIndex column)
515 LogDebug("SQL data command get column optional int32: [" << column << "]");
516 CheckColumnIndex(column);
517 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
518 return boost::optional<int32_t>();
520 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
521 LogDebug(" Value : " << value);
522 return boost::optional<int32_t>(value);
525 boost::optional<int64_t> SqlConnection::DataCommand::GetColumnOptionalInt64(
526 SqlConnection::ColumnIndex column)
528 LogDebug("SQL data command get column optional int64: [" << column << "]");
529 CheckColumnIndex(column);
530 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
531 return boost::optional<int64_t>();
533 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
534 LogDebug(" Value : " << value);
535 return boost::optional<int64_t>(value);
538 boost::optional<float> SqlConnection::DataCommand::GetColumnOptionalFloat(
539 SqlConnection::ColumnIndex column)
541 LogDebug("SQL data command get column optional float: [" << column << "]");
542 CheckColumnIndex(column);
543 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
544 return boost::optional<float>();
546 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
547 LogDebug(" Value : " << value);
548 return boost::optional<float>(value);
551 boost::optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
552 SqlConnection::ColumnIndex column)
554 LogDebug("SQL data command get column optional double: [" << column << "]");
555 CheckColumnIndex(column);
556 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
557 return boost::optional<double>();
559 double value = sqlite3_column_double(m_stmt, column);
560 LogDebug(" Value : " << value);
561 return boost::optional<double>(value);
564 boost::optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
565 SqlConnection::ColumnIndex column)
567 LogDebug("SQL data command get column optional string: [" << column << "]");
568 CheckColumnIndex(column);
569 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
570 return boost::optional<String>();
572 const char *value = reinterpret_cast<const char *>(
573 sqlite3_column_text(m_stmt, column));
574 LogDebug(" Value : " << value);
575 String s = FromUTF8String(value);
576 return boost::optional<String>(s);
579 void SqlConnection::Connect(const std::string &address,
583 if (m_connection != NULL) {
584 LogDebug("Already connected.");
587 LogDebug("Connecting to DB: " << address << "...");
589 // Connect to database
591 if (type & Flag::UseLucene) {
592 result = db_util_open_with_options(
598 m_usingLucene = true;
599 LogDebug("Lucene index enabled");
601 result = sqlite3_open_v2(
607 m_usingLucene = false;
608 LogDebug("Lucene index disabled");
611 if (result == SQLITE_OK) {
612 LogDebug("Connected to DB");
614 LogDebug("Failed to connect to DB!");
615 ThrowMsg(Exception::ConnectionBroken, address);
618 // Enable foreign keys
622 void SqlConnection::Disconnect()
624 if (m_connection == NULL) {
625 LogDebug("Already disconnected.");
629 LogDebug("Disconnecting from DB...");
631 // All stored data commands must be deleted before disconnect
632 AssertMsg(m_dataCommandsCount == 0,
633 "All stored procedures must be deleted"
634 " before disconnecting SqlConnection");
639 result = db_util_close(m_connection);
641 result = sqlite3_close(m_connection);
644 if (result != SQLITE_OK) {
645 const char *error = sqlite3_errmsg(m_connection);
646 LogDebug("SQL close failed");
647 LogDebug(" Error : " << error);
648 Throw(Exception::InternalError);
653 LogDebug("Disconnected from DB");
656 bool SqlConnection::CheckTableExist(const char *tableName)
658 if (m_connection == NULL) {
659 LogDebug("Cannot execute command. Not connected to DB!");
663 DataCommandAutoPtr command =
664 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
666 command->BindString(1, tableName);
668 if (!command->Step()) {
669 LogDebug("No matching records in table");
673 return command->GetColumnString(0) == tableName;
676 SqlConnection::SqlConnection(const std::string &address,
679 SynchronizationObject *synchronizationObject) :
681 m_usingLucene(false),
682 m_dataCommandsCount(0),
683 m_synchronizationObject(synchronizationObject)
685 LogDebug("Opening database connection to : " << address);
688 SqlConnection::Connect(address, flag, option);
690 if (!m_synchronizationObject) {
691 LogDebug("No synchronization object defined");
695 SqlConnection::~SqlConnection()
697 LogDebug("Closing database connection");
699 // Disconnect from DB
702 SqlConnection::Disconnect();
704 Catch(Exception::Base)
706 LogDebug("Failed to disconnect from database");
710 void SqlConnection::ExecCommand(const char *format, ...)
712 if (m_connection == NULL) {
713 LogDebug("Cannot execute command. Not connected to DB!");
717 if (format == NULL) {
718 LogDebug("Null query!");
719 ThrowMsg(Exception::SyntaxError, "Null statement");
725 va_start(args, format);
727 if (vasprintf(&rawBuffer, format, args) == -1) {
733 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
736 LogDebug("Failed to allocate statement string");
740 LogDebug("Executing SQL command : " << buffer.get());
742 // Notify all after potentially synchronized database connection access
743 ScopedNotifyAll notifyAll(m_synchronizationObject.get());
748 int ret = sqlite3_exec(m_connection,
754 std::string errorMsg;
756 // Take allocated error buffer
757 if (errorBuffer != NULL) {
758 errorMsg = errorBuffer;
759 sqlite3_free(errorBuffer);
762 if (ret == SQLITE_OK) {
766 if (ret == SQLITE_BUSY) {
767 LogDebug("Collision occurred while executing SQL command");
769 // Synchronize if synchronization object is available
770 if (m_synchronizationObject) {
771 LogDebug("Performing synchronization");
772 m_synchronizationObject->Synchronize();
776 // No synchronization object defined. Fail.
780 LogDebug("Failed to execute SQL command. Error : " << errorMsg);
781 ThrowMsg(Exception::SyntaxError, errorMsg);
785 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
789 if (m_connection == NULL) {
790 LogDebug("Cannot execute data command. Not connected to DB!");
791 return DataCommandAutoPtr();
797 va_start(args, format);
799 if (vasprintf(&rawBuffer, format, args) == -1) {
805 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
808 LogDebug("Failed to allocate statement string");
809 return DataCommandAutoPtr();
812 LogDebug("Executing SQL data command : " << buffer.get());
814 return DataCommandAutoPtr(new DataCommand(this, buffer.get()));
817 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
819 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
822 void SqlConnection::TurnOnForeignKeys()
824 ExecCommand("PRAGMA foreign_keys = ON;");
827 void SqlConnection::BeginTransaction()
829 ExecCommand("BEGIN;");
832 void SqlConnection::RollbackTransaction()
834 ExecCommand("ROLLBACK;");
837 void SqlConnection::CommitTransaction()
839 ExecCommand("COMMIT;");
842 SqlConnection::SynchronizationObject *
843 SqlConnection::AllocDefaultSynchronizationObject()
845 return new NaiveSynchronizationObject();
848 int SqlConnection::db_util_open_with_options(const char *pszFilePath, sqlite3 **ppDB,
849 int flags, const char *zVfs)
853 if((pszFilePath == NULL) || (ppDB == NULL)) {
854 LogWarning("sqlite3 handle null error");
860 if((geteuid() != 0) && (access(pszFilePath, mode))) {
861 if(errno == EACCES) {
862 LogDebug("file access permission error");
868 int rc = sqlite3_open_v2(pszFilePath, ppDB, flags, zVfs);
869 if (SQLITE_OK != rc) {
870 LogError("sqlite3_open_v2 error(" << rc << ")");
874 //rc = __db_util_open(*ppDB);
880 int SqlConnection::db_util_close(sqlite3 *pDB)
882 char *pszErrorMsg = NULL;
885 int rc = sqlite3_close(pDB);
886 if (SQLITE_OK != rc) {
887 LogWarning("Fail to change journal mode : " << pszErrorMsg);
888 sqlite3_free(pszErrorMsg);
896 } // namespace VcoreDPL