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/log/wrt_log.h>
24 #include <dpl/db/sql_connection.h>
25 #include <dpl/db/naive_synchronization_object.h>
26 #include <dpl/free_deleter.h>
28 #include <dpl/noncopyable.h>
29 #include <dpl/assert.h>
37 namespace // anonymous
39 class ScopedNotifyAll :
43 SqlConnection::SynchronizationObject *m_synchronizationObject;
46 explicit ScopedNotifyAll(
47 SqlConnection::SynchronizationObject *synchronizationObject) :
48 m_synchronizationObject(synchronizationObject)
53 if (!m_synchronizationObject) {
57 WrtLogD("Notifying after successful synchronize");
58 m_synchronizationObject->NotifyAll();
61 } // namespace anonymous
63 SqlConnection::DataCommand::DataCommand(SqlConnection *connection,
65 m_masterConnection(connection),
68 Assert(connection != NULL);
70 // Notify all after potentially synchronized database connection access
71 ScopedNotifyAll notifyAll(connection->m_synchronizationObject.get());
74 int ret = sqlite3_prepare_v2(connection->m_connection,
75 buffer, strlen(buffer),
78 if (ret == SQLITE_OK) {
79 WrtLogD("Data command prepared successfuly");
81 } else if (ret == SQLITE_BUSY) {
82 WrtLogD("Collision occurred while preparing SQL command");
84 // Synchronize if synchronization object is available
85 if (connection->m_synchronizationObject) {
86 WrtLogD("Performing synchronization");
87 connection->m_synchronizationObject->Synchronize();
91 // No synchronization object defined. Fail.
95 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
97 WrtLogD("SQL prepare data command failed");
98 WrtLogD(" Statement: %s", buffer);
99 WrtLogD(" Error: %s", error);
101 ThrowMsg(Exception::SyntaxError, error);
104 WrtLogD("Prepared data command: %s", buffer);
106 // Increment stored data command count
107 ++m_masterConnection->m_dataCommandsCount;
110 SqlConnection::DataCommand::~DataCommand()
112 WrtLogD("SQL data command finalizing");
114 if (sqlite3_finalize(m_stmt) != SQLITE_OK) {
115 WrtLogD("Failed to finalize data command");
118 // Decrement stored data command count
119 --m_masterConnection->m_dataCommandsCount;
122 void SqlConnection::DataCommand::CheckBindResult(int result)
124 if (result != SQLITE_OK) {
125 const char *error = sqlite3_errmsg(
126 m_masterConnection->m_connection);
128 WrtLogD("Failed to bind SQL statement parameter");
129 WrtLogD(" Error: %s", error);
131 ThrowMsg(Exception::SyntaxError, error);
135 void SqlConnection::DataCommand::BindNull(
136 SqlConnection::ArgumentIndex position)
138 CheckBindResult(sqlite3_bind_null(m_stmt, position));
139 WrtLogD("SQL data command bind null: [%i]", position);
142 void SqlConnection::DataCommand::BindInteger(
143 SqlConnection::ArgumentIndex position,
146 CheckBindResult(sqlite3_bind_int(m_stmt, position, value));
147 WrtLogD("SQL data command bind integer: [%i] -> %i", 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 WrtLogD("SQL data command bind int8: [%i] -> %i", position, value);
159 void SqlConnection::DataCommand::BindInt16(
160 SqlConnection::ArgumentIndex position,
163 CheckBindResult(sqlite3_bind_int(m_stmt, position,
164 static_cast<int>(value)));
165 WrtLogD("SQL data command bind int16: [%i] -> %i", position, value);
168 void SqlConnection::DataCommand::BindInt32(
169 SqlConnection::ArgumentIndex position,
172 CheckBindResult(sqlite3_bind_int(m_stmt, position,
173 static_cast<int>(value)));
174 WrtLogD("SQL data command bind int32: [%i] -> %i", position, value);
177 void SqlConnection::DataCommand::BindInt64(
178 SqlConnection::ArgumentIndex position,
181 CheckBindResult(sqlite3_bind_int64(m_stmt, position,
182 static_cast<sqlite3_int64>(value)));
183 WrtLogD("SQL data command bind int64: [%i] -> %lli", position, value);
186 void SqlConnection::DataCommand::BindFloat(
187 SqlConnection::ArgumentIndex position,
190 CheckBindResult(sqlite3_bind_double(m_stmt, position,
191 static_cast<double>(value)));
192 WrtLogD("SQL data command bind float: [%i] -> %f", position, value);
195 void SqlConnection::DataCommand::BindDouble(
196 SqlConnection::ArgumentIndex position,
199 CheckBindResult(sqlite3_bind_double(m_stmt, position, value));
200 WrtLogD("SQL data command bind double: [%i] -> %f", position, value);
203 void SqlConnection::DataCommand::BindString(
204 SqlConnection::ArgumentIndex position,
212 // Assume that text may disappear
213 CheckBindResult(sqlite3_bind_text(m_stmt, position,
214 value, strlen(value),
217 WrtLogD("SQL data command bind string: [%i] -> %s", position, value);
220 void SqlConnection::DataCommand::BindString(
221 SqlConnection::ArgumentIndex position,
224 BindString(position, ToUTF8String(value).c_str());
227 void SqlConnection::DataCommand::BindInteger(
228 SqlConnection::ArgumentIndex position,
229 const boost::optional<int> &value)
234 BindInteger(position, *value);
238 void SqlConnection::DataCommand::BindInt8(
239 SqlConnection::ArgumentIndex position,
240 const boost::optional<int8_t> &value)
245 BindInt8(position, *value);
249 void SqlConnection::DataCommand::BindInt16(
250 SqlConnection::ArgumentIndex position,
251 const boost::optional<int16_t> &value)
256 BindInt16(position, *value);
260 void SqlConnection::DataCommand::BindInt32(
261 SqlConnection::ArgumentIndex position,
262 const boost::optional<int32_t> &value)
267 BindInt32(position, *value);
271 void SqlConnection::DataCommand::BindInt64(
272 SqlConnection::ArgumentIndex position,
273 const boost::optional<int64_t> &value)
278 BindInt64(position, *value);
282 void SqlConnection::DataCommand::BindFloat(
283 SqlConnection::ArgumentIndex position,
284 const boost::optional<float> &value)
289 BindFloat(position, *value);
293 void SqlConnection::DataCommand::BindDouble(
294 SqlConnection::ArgumentIndex position,
295 const boost::optional<double> &value)
300 BindDouble(position, *value);
304 void SqlConnection::DataCommand::BindString(
305 SqlConnection::ArgumentIndex position,
306 const boost::optional<String> &value)
309 BindString(position, ToUTF8String(*value).c_str());
315 bool SqlConnection::DataCommand::Step()
317 // Notify all after potentially synchronized database connection access
318 ScopedNotifyAll notifyAll(
319 m_masterConnection->m_synchronizationObject.get());
322 int ret = sqlite3_step(m_stmt);
324 if (ret == SQLITE_ROW) {
325 WrtLogD("SQL data command step ROW");
327 } else if (ret == SQLITE_DONE) {
328 WrtLogD("SQL data command step DONE");
330 } else if (ret == SQLITE_BUSY) {
331 WrtLogD("Collision occurred while executing SQL command");
333 // Synchronize if synchronization object is available
334 if (m_masterConnection->m_synchronizationObject) {
335 WrtLogD("Performing synchronization");
338 m_synchronizationObject->Synchronize();
343 // No synchronization object defined. Fail.
347 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
349 WrtLogD("SQL step data command failed");
350 WrtLogD(" Error: %s", error);
352 ThrowMsg(Exception::InternalError, error);
356 void SqlConnection::DataCommand::Reset()
360 * http://www.sqlite.org/c3ref/stmt.html
362 * if last sqlite3_step command on this stmt returned an error,
363 * then sqlite3_reset will return that error, althought it is not an error.
364 * So sqlite3_reset allways succedes.
366 sqlite3_reset(m_stmt);
368 WrtLogD("SQL data command reset");
371 void SqlConnection::DataCommand::CheckColumnIndex(
372 SqlConnection::ColumnIndex column)
374 if (column < 0 || column >= sqlite3_column_count(m_stmt)) {
375 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
379 bool SqlConnection::DataCommand::IsColumnNull(
380 SqlConnection::ColumnIndex column)
382 WrtLogD("SQL data command get column type: [%i]", column);
383 CheckColumnIndex(column);
384 return sqlite3_column_type(m_stmt, column) == SQLITE_NULL;
387 int SqlConnection::DataCommand::GetColumnInteger(
388 SqlConnection::ColumnIndex column)
390 WrtLogD("SQL data command get column integer: [%i]", column);
391 CheckColumnIndex(column);
392 int value = sqlite3_column_int(m_stmt, column);
393 WrtLogD(" Value: %i", value);
397 int8_t SqlConnection::DataCommand::GetColumnInt8(
398 SqlConnection::ColumnIndex column)
400 WrtLogD("SQL data command get column int8: [%i]", column);
401 CheckColumnIndex(column);
402 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
403 WrtLogD(" Value: %i", value);
407 int16_t SqlConnection::DataCommand::GetColumnInt16(
408 SqlConnection::ColumnIndex column)
410 WrtLogD("SQL data command get column int16: [%i]", column);
411 CheckColumnIndex(column);
412 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
413 WrtLogD(" Value: %i", value);
417 int32_t SqlConnection::DataCommand::GetColumnInt32(
418 SqlConnection::ColumnIndex column)
420 WrtLogD("SQL data command get column int32: [%i]", column);
421 CheckColumnIndex(column);
422 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
423 WrtLogD(" Value: %i", value);
427 int64_t SqlConnection::DataCommand::GetColumnInt64(
428 SqlConnection::ColumnIndex column)
430 WrtLogD("SQL data command get column int64: [%i]", column);
431 CheckColumnIndex(column);
432 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
433 WrtLogD(" Value: %lli", value);
437 float SqlConnection::DataCommand::GetColumnFloat(
438 SqlConnection::ColumnIndex column)
440 WrtLogD("SQL data command get column float: [%i]", column);
441 CheckColumnIndex(column);
442 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
443 WrtLogD(" Value: %f", value);
447 double SqlConnection::DataCommand::GetColumnDouble(
448 SqlConnection::ColumnIndex column)
450 WrtLogD("SQL data command get column double: [%i]", column);
451 CheckColumnIndex(column);
452 double value = sqlite3_column_double(m_stmt, column);
453 WrtLogD(" Value: %f", value);
457 std::string SqlConnection::DataCommand::GetColumnString(
458 SqlConnection::ColumnIndex column)
460 WrtLogD("SQL data command get column string: [%i]", column);
461 CheckColumnIndex(column);
463 const char *value = reinterpret_cast<const char *>(
464 sqlite3_column_text(m_stmt, column));
466 WrtLogD(" Value: %s", value);
469 return std::string();
472 return std::string(value);
475 boost::optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
476 SqlConnection::ColumnIndex column)
478 WrtLogD("SQL data command get column optional integer: [%i]", column);
479 CheckColumnIndex(column);
480 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
481 return boost::optional<int>();
483 int value = sqlite3_column_int(m_stmt, column);
484 WrtLogD(" Value: %i", value);
485 return boost::optional<int>(value);
488 boost::optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
489 SqlConnection::ColumnIndex column)
491 WrtLogD("SQL data command get column optional int8: [%i]", column);
492 CheckColumnIndex(column);
493 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
494 return boost::optional<int8_t>();
496 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
497 WrtLogD(" Value: %i", value);
498 return boost::optional<int8_t>(value);
501 boost::optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
502 SqlConnection::ColumnIndex column)
504 WrtLogD("SQL data command get column optional int16: [%i]", column);
505 CheckColumnIndex(column);
506 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
507 return boost::optional<int16_t>();
509 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
510 WrtLogD(" Value: %i", value);
511 return boost::optional<int16_t>(value);
514 boost::optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
515 SqlConnection::ColumnIndex column)
517 WrtLogD("SQL data command get column optional int32: [%i]", column);
518 CheckColumnIndex(column);
519 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
520 return boost::optional<int32_t>();
522 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
523 WrtLogD(" Value: %i", value);
524 return boost::optional<int32_t>(value);
527 boost::optional<int64_t> SqlConnection::DataCommand::GetColumnOptionalInt64(
528 SqlConnection::ColumnIndex column)
530 WrtLogD("SQL data command get column optional int64: [%i]", column);
531 CheckColumnIndex(column);
532 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
533 return boost::optional<int64_t>();
535 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
536 WrtLogD(" Value: %lli", value);
537 return boost::optional<int64_t>(value);
540 boost::optional<float> SqlConnection::DataCommand::GetColumnOptionalFloat(
541 SqlConnection::ColumnIndex column)
543 WrtLogD("SQL data command get column optional float: [%i]", column);
544 CheckColumnIndex(column);
545 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
546 return boost::optional<float>();
548 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
549 WrtLogD(" Value: %f", value);
550 return boost::optional<float>(value);
553 boost::optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
554 SqlConnection::ColumnIndex column)
556 WrtLogD("SQL data command get column optional double: [%i]", column);
557 CheckColumnIndex(column);
558 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
559 return boost::optional<double>();
561 double value = sqlite3_column_double(m_stmt, column);
562 WrtLogD(" Value: %f", value);
563 return boost::optional<double>(value);
566 boost::optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
567 SqlConnection::ColumnIndex column)
569 WrtLogD("SQL data command get column optional string: [%i]", column);
570 CheckColumnIndex(column);
571 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
572 return boost::optional<String>();
574 const char *value = reinterpret_cast<const char *>(
575 sqlite3_column_text(m_stmt, column));
577 WrtLogD(" Value: %s", value);
580 return boost::optional<String>();
583 String s = FromUTF8String(value);
584 return boost::optional<String>(s);
587 void SqlConnection::Connect(const std::string &address,
591 if (m_connection != NULL) {
592 WrtLogD("Already connected.");
595 WrtLogD("Connecting to DB: %s...", address.c_str());
597 // Connect to database
600 while( result != SQLITE_OK){
601 if (type & Flag::UseLucene) {
602 result = db_util_open_with_options(
608 m_usingLucene = true;
609 WrtLogD("Lucene index enabled");
611 result = sqlite3_open_v2(
617 m_usingLucene = false;
618 WrtLogD("Lucene index disabled");
621 if (result == SQLITE_OK) {
622 WrtLogD("Connected to DB");
624 WrtLogD("Failed to connect to DB! ret=%d", result);
626 ThrowMsg(Exception::ConnectionBroken, address);
630 // Enable foreign keys
634 void SqlConnection::Disconnect()
636 if (m_connection == NULL) {
637 WrtLogD("Already disconnected.");
641 WrtLogD("Disconnecting from DB...");
643 // All stored data commands must be deleted before disconnect
644 AssertMsg(m_dataCommandsCount == 0,
645 "All stored procedures must be deleted"
646 " before disconnecting SqlConnection");
651 result = db_util_close(m_connection);
653 result = sqlite3_close(m_connection);
656 if (result != SQLITE_OK) {
657 const char *error = sqlite3_errmsg(m_connection);
658 WrtLogD("SQL close failed");
659 WrtLogD(" Error: %s", error);
660 Throw(Exception::InternalError);
665 WrtLogD("Disconnected from DB");
668 bool SqlConnection::CheckTableExist(const char *tableName)
670 if (m_connection == NULL) {
671 WrtLogD("Cannot execute command. Not connected to DB!");
675 DataCommandAutoPtr command =
676 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
678 command->BindString(1, tableName);
680 if (!command->Step()) {
681 WrtLogD("No matching records in table");
685 return command->GetColumnString(0) == tableName;
688 SqlConnection::SqlConnection(const std::string &address,
691 SynchronizationObject *synchronizationObject) :
693 m_usingLucene(false),
694 m_dataCommandsCount(0),
695 m_synchronizationObject(synchronizationObject)
697 WrtLogD("Opening database connection to: %s", address.c_str());
700 SqlConnection::Connect(address, flag, option);
702 if (!m_synchronizationObject) {
703 WrtLogD("No synchronization object defined");
707 SqlConnection::~SqlConnection()
709 WrtLogD("Closing database connection");
711 // Disconnect from DB
714 SqlConnection::Disconnect();
716 Catch(Exception::Base)
718 WrtLogD("Failed to disconnect from database");
722 void SqlConnection::ExecCommand(const char *format, ...)
724 if (m_connection == NULL) {
725 WrtLogD("Cannot execute command. Not connected to DB!");
729 if (format == NULL) {
730 WrtLogD("Null query!");
731 ThrowMsg(Exception::SyntaxError, "Null statement");
737 va_start(args, format);
739 if (vasprintf(&rawBuffer, format, args) == -1) {
745 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
748 WrtLogD("Failed to allocate statement string");
752 WrtLogD("Executing SQL command: %s", buffer.get());
754 // Notify all after potentially synchronized database connection access
755 ScopedNotifyAll notifyAll(m_synchronizationObject.get());
760 int ret = sqlite3_exec(m_connection,
766 std::string errorMsg;
768 // Take allocated error buffer
769 if (errorBuffer != NULL) {
770 errorMsg = errorBuffer;
771 sqlite3_free(errorBuffer);
774 if (ret == SQLITE_OK) {
778 if (ret == SQLITE_BUSY) {
779 WrtLogD("Collision occurred while executing SQL command");
781 // Synchronize if synchronization object is available
782 if (m_synchronizationObject) {
783 WrtLogD("Performing synchronization");
784 m_synchronizationObject->Synchronize();
788 // No synchronization object defined. Fail.
792 WrtLogD("Failed to execute SQL command. Error: %s", errorMsg.c_str());
793 ThrowMsg(Exception::SyntaxError, errorMsg);
797 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
801 if (m_connection == NULL) {
802 WrtLogD("Cannot execute data command. Not connected to DB!");
803 return DataCommandAutoPtr();
809 va_start(args, format);
811 if (vasprintf(&rawBuffer, format, args) == -1) {
817 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
820 WrtLogD("Failed to allocate statement string");
821 return DataCommandAutoPtr();
824 WrtLogD("Executing SQL data command: %s", buffer.get());
826 return DataCommandAutoPtr(new DataCommand(this, buffer.get()));
829 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
831 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
834 void SqlConnection::TurnOnForeignKeys()
836 ExecCommand("PRAGMA foreign_keys = ON;");
839 void SqlConnection::BeginTransaction()
841 ExecCommand("BEGIN;");
844 void SqlConnection::RollbackTransaction()
846 ExecCommand("ROLLBACK;");
849 void SqlConnection::CommitTransaction()
851 ExecCommand("COMMIT;");
854 SqlConnection::SynchronizationObject *
855 SqlConnection::AllocDefaultSynchronizationObject()
857 return new NaiveSynchronizationObject();