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>
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 LogPedantic("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 LogPedantic("Data command prepared successfuly");
80 } else if (ret == SQLITE_BUSY) {
81 LogPedantic("Collision occurred while preparing SQL command");
83 // Synchronize if synchronization object is available
84 if (connection->m_synchronizationObject) {
85 LogPedantic("Performing synchronization");
86 connection->m_synchronizationObject->Synchronize();
90 // No synchronization object defined. Fail.
94 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
96 LogPedantic("SQL prepare data command failed");
97 LogPedantic(" Statement: " << buffer);
98 LogPedantic(" Error: " << error);
100 ThrowMsg(Exception::SyntaxError, error);
103 LogPedantic("Prepared data command: " << buffer);
105 // Increment stored data command count
106 ++m_masterConnection->m_dataCommandsCount;
109 SqlConnection::DataCommand::~DataCommand()
111 LogPedantic("SQL data command finalizing");
113 if (sqlite3_finalize(m_stmt) != SQLITE_OK) {
114 LogPedantic("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 LogPedantic("Failed to bind SQL statement parameter");
128 LogPedantic(" 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 LogPedantic("SQL data command bind null: ["
142 void SqlConnection::DataCommand::BindInteger(
143 SqlConnection::ArgumentIndex position,
146 CheckBindResult(sqlite3_bind_int(m_stmt, position, value));
147 LogPedantic("SQL data command bind integer: ["
148 << position << "] -> " << value);
151 void SqlConnection::DataCommand::BindInt8(
152 SqlConnection::ArgumentIndex position,
155 CheckBindResult(sqlite3_bind_int(m_stmt, position,
156 static_cast<int>(value)));
157 LogPedantic("SQL data command bind int8: ["
158 << position << "] -> " << value);
161 void SqlConnection::DataCommand::BindInt16(
162 SqlConnection::ArgumentIndex position,
165 CheckBindResult(sqlite3_bind_int(m_stmt, position,
166 static_cast<int>(value)));
167 LogPedantic("SQL data command bind int16: ["
168 << position << "] -> " << value);
171 void SqlConnection::DataCommand::BindInt32(
172 SqlConnection::ArgumentIndex position,
175 CheckBindResult(sqlite3_bind_int(m_stmt, position,
176 static_cast<int>(value)));
177 LogPedantic("SQL data command bind int32: ["
178 << position << "] -> " << value);
181 void SqlConnection::DataCommand::BindInt64(
182 SqlConnection::ArgumentIndex position,
185 CheckBindResult(sqlite3_bind_int64(m_stmt, position,
186 static_cast<sqlite3_int64>(value)));
187 LogPedantic("SQL data command bind int64: ["
188 << position << "] -> " << value);
191 void SqlConnection::DataCommand::BindFloat(
192 SqlConnection::ArgumentIndex position,
195 CheckBindResult(sqlite3_bind_double(m_stmt, position,
196 static_cast<double>(value)));
197 LogPedantic("SQL data command bind float: ["
198 << position << "] -> " << value);
201 void SqlConnection::DataCommand::BindDouble(
202 SqlConnection::ArgumentIndex position,
205 CheckBindResult(sqlite3_bind_double(m_stmt, position, value));
206 LogPedantic("SQL data command bind double: ["
207 << position << "] -> " << value);
210 void SqlConnection::DataCommand::BindString(
211 SqlConnection::ArgumentIndex position,
219 // Assume that text may disappear
220 CheckBindResult(sqlite3_bind_text(m_stmt, position,
221 value, strlen(value),
224 LogPedantic("SQL data command bind string: ["
225 << position << "] -> " << value);
228 void SqlConnection::DataCommand::BindString(
229 SqlConnection::ArgumentIndex position,
232 BindString(position, ToUTF8String(value).c_str());
235 void SqlConnection::DataCommand::BindInteger(
236 SqlConnection::ArgumentIndex position,
237 const boost::optional<int> &value)
242 BindInteger(position, *value);
246 void SqlConnection::DataCommand::BindInt8(
247 SqlConnection::ArgumentIndex position,
248 const boost::optional<int8_t> &value)
253 BindInt8(position, *value);
257 void SqlConnection::DataCommand::BindInt16(
258 SqlConnection::ArgumentIndex position,
259 const boost::optional<int16_t> &value)
264 BindInt16(position, *value);
268 void SqlConnection::DataCommand::BindInt32(
269 SqlConnection::ArgumentIndex position,
270 const boost::optional<int32_t> &value)
275 BindInt32(position, *value);
279 void SqlConnection::DataCommand::BindInt64(
280 SqlConnection::ArgumentIndex position,
281 const boost::optional<int64_t> &value)
286 BindInt64(position, *value);
290 void SqlConnection::DataCommand::BindFloat(
291 SqlConnection::ArgumentIndex position,
292 const boost::optional<float> &value)
297 BindFloat(position, *value);
301 void SqlConnection::DataCommand::BindDouble(
302 SqlConnection::ArgumentIndex position,
303 const boost::optional<double> &value)
308 BindDouble(position, *value);
312 void SqlConnection::DataCommand::BindString(
313 SqlConnection::ArgumentIndex position,
314 const boost::optional<String> &value)
317 BindString(position, ToUTF8String(*value).c_str());
323 bool SqlConnection::DataCommand::Step()
325 // Notify all after potentially synchronized database connection access
326 ScopedNotifyAll notifyAll(
327 m_masterConnection->m_synchronizationObject.get());
330 int ret = sqlite3_step(m_stmt);
332 if (ret == SQLITE_ROW) {
333 LogPedantic("SQL data command step ROW");
335 } else if (ret == SQLITE_DONE) {
336 LogPedantic("SQL data command step DONE");
338 } else if (ret == SQLITE_BUSY) {
339 LogPedantic("Collision occurred while executing SQL command");
341 // Synchronize if synchronization object is available
342 if (m_masterConnection->m_synchronizationObject) {
343 LogPedantic("Performing synchronization");
346 m_synchronizationObject->Synchronize();
351 // No synchronization object defined. Fail.
355 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
357 LogPedantic("SQL step data command failed");
358 LogPedantic(" Error: " << error);
360 ThrowMsg(Exception::InternalError, error);
364 void SqlConnection::DataCommand::Reset()
368 * http://www.sqlite.org/c3ref/stmt.html
370 * if last sqlite3_step command on this stmt returned an error,
371 * then sqlite3_reset will return that error, althought it is not an error.
372 * So sqlite3_reset allways succedes.
374 sqlite3_reset(m_stmt);
376 LogPedantic("SQL data command reset");
379 void SqlConnection::DataCommand::CheckColumnIndex(
380 SqlConnection::ColumnIndex column)
382 if (column < 0 || column >= sqlite3_column_count(m_stmt)) {
383 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
387 bool SqlConnection::DataCommand::IsColumnNull(
388 SqlConnection::ColumnIndex column)
390 LogPedantic("SQL data command get column type: [" << column << "]");
391 CheckColumnIndex(column);
392 return sqlite3_column_type(m_stmt, column) == SQLITE_NULL;
395 int SqlConnection::DataCommand::GetColumnInteger(
396 SqlConnection::ColumnIndex column)
398 LogPedantic("SQL data command get column integer: [" << column << "]");
399 CheckColumnIndex(column);
400 int value = sqlite3_column_int(m_stmt, column);
401 LogPedantic(" Value: " << value);
405 int8_t SqlConnection::DataCommand::GetColumnInt8(
406 SqlConnection::ColumnIndex column)
408 LogPedantic("SQL data command get column int8: [" << column << "]");
409 CheckColumnIndex(column);
410 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
411 LogPedantic(" Value: " << value);
415 int16_t SqlConnection::DataCommand::GetColumnInt16(
416 SqlConnection::ColumnIndex column)
418 LogPedantic("SQL data command get column int16: [" << column << "]");
419 CheckColumnIndex(column);
420 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
421 LogPedantic(" Value: " << value);
425 int32_t SqlConnection::DataCommand::GetColumnInt32(
426 SqlConnection::ColumnIndex column)
428 LogPedantic("SQL data command get column int32: [" << column << "]");
429 CheckColumnIndex(column);
430 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
431 LogPedantic(" Value: " << value);
435 int64_t SqlConnection::DataCommand::GetColumnInt64(
436 SqlConnection::ColumnIndex column)
438 LogPedantic("SQL data command get column int64: [" << column << "]");
439 CheckColumnIndex(column);
440 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
441 LogPedantic(" Value: " << value);
445 float SqlConnection::DataCommand::GetColumnFloat(
446 SqlConnection::ColumnIndex column)
448 LogPedantic("SQL data command get column float: [" << column << "]");
449 CheckColumnIndex(column);
450 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
451 LogPedantic(" Value: " << value);
455 double SqlConnection::DataCommand::GetColumnDouble(
456 SqlConnection::ColumnIndex column)
458 LogPedantic("SQL data command get column double: [" << column << "]");
459 CheckColumnIndex(column);
460 double value = sqlite3_column_double(m_stmt, column);
461 LogPedantic(" Value: " << value);
465 std::string SqlConnection::DataCommand::GetColumnString(
466 SqlConnection::ColumnIndex column)
468 LogPedantic("SQL data command get column string: [" << column << "]");
469 CheckColumnIndex(column);
471 const char *value = reinterpret_cast<const char *>(
472 sqlite3_column_text(m_stmt, column));
474 LogPedantic("Value: " << (value ? value : "NULL"));
477 return std::string();
480 return std::string(value);
483 boost::optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
484 SqlConnection::ColumnIndex column)
486 LogPedantic("SQL data command get column optional integer: ["
488 CheckColumnIndex(column);
489 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
490 return boost::optional<int>();
492 int value = sqlite3_column_int(m_stmt, column);
493 LogPedantic(" Value: " << value);
494 return boost::optional<int>(value);
497 boost::optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
498 SqlConnection::ColumnIndex column)
500 LogPedantic("SQL data command get column optional int8: ["
502 CheckColumnIndex(column);
503 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
504 return boost::optional<int8_t>();
506 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
507 LogPedantic(" Value: " << value);
508 return boost::optional<int8_t>(value);
511 boost::optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
512 SqlConnection::ColumnIndex column)
514 LogPedantic("SQL data command get column optional int16: ["
516 CheckColumnIndex(column);
517 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
518 return boost::optional<int16_t>();
520 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
521 LogPedantic(" Value: " << value);
522 return boost::optional<int16_t>(value);
525 boost::optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
526 SqlConnection::ColumnIndex column)
528 LogPedantic("SQL data command get column optional int32: ["
530 CheckColumnIndex(column);
531 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
532 return boost::optional<int32_t>();
534 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
535 LogPedantic(" Value: " << value);
536 return boost::optional<int32_t>(value);
539 boost::optional<int64_t> SqlConnection::DataCommand::GetColumnOptionalInt64(
540 SqlConnection::ColumnIndex column)
542 LogPedantic("SQL data command get column optional int64: ["
544 CheckColumnIndex(column);
545 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
546 return boost::optional<int64_t>();
548 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
549 LogPedantic(" Value: " << value);
550 return boost::optional<int64_t>(value);
553 boost::optional<float> SqlConnection::DataCommand::GetColumnOptionalFloat(
554 SqlConnection::ColumnIndex column)
556 LogPedantic("SQL data command get column optional float: ["
558 CheckColumnIndex(column);
559 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
560 return boost::optional<float>();
562 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
563 LogPedantic(" Value: " << value);
564 return boost::optional<float>(value);
567 boost::optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
568 SqlConnection::ColumnIndex column)
570 LogPedantic("SQL data command get column optional double: ["
572 CheckColumnIndex(column);
573 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
574 return boost::optional<double>();
576 double value = sqlite3_column_double(m_stmt, column);
577 LogPedantic(" Value: " << value);
578 return boost::optional<double>(value);
581 boost::optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
582 SqlConnection::ColumnIndex column)
584 LogPedantic("SQL data command get column optional string: ["
586 CheckColumnIndex(column);
587 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL) {
588 return boost::optional<String>();
590 const char *value = reinterpret_cast<const char *>(
591 sqlite3_column_text(m_stmt, column));
594 return boost::optional<String>();
596 LogPedantic("Value: " << value);
597 String s = FromUTF8String(value);
598 return boost::optional<String>(s);
601 void SqlConnection::Connect(const std::string &address,
605 if (m_connection != NULL) {
606 LogPedantic("Already connected.");
609 LogPedantic("Connecting to DB: " << address << "...");
611 // Connect to database
614 while( result != SQLITE_OK){
615 if (type & Flag::UseLucene) {
616 result = db_util_open_with_options(
622 m_usingLucene = true;
623 LogPedantic("Lucene index enabled");
625 result = sqlite3_open_v2(
631 m_usingLucene = false;
632 LogPedantic("Lucene index disabled");
635 if (result == SQLITE_OK) {
636 LogPedantic("Connected to DB");
638 LogPedantic("Failed to connect to DB! ret="<<result);
640 ThrowMsg(Exception::ConnectionBroken, address);
644 // Enable foreign keys
648 void SqlConnection::Disconnect()
650 if (m_connection == NULL) {
651 LogPedantic("Already disconnected.");
655 LogPedantic("Disconnecting from DB...");
657 // All stored data commands must be deleted before disconnect
658 AssertMsg(m_dataCommandsCount == 0,
659 "All stored procedures must be deleted"
660 " before disconnecting SqlConnection");
665 result = db_util_close(m_connection);
667 result = sqlite3_close(m_connection);
670 if (result != SQLITE_OK) {
671 const char *error = sqlite3_errmsg(m_connection);
672 LogPedantic("SQL close failed");
673 LogPedantic(" Error: " << error);
674 Throw(Exception::InternalError);
679 LogPedantic("Disconnected from DB");
682 bool SqlConnection::CheckTableExist(const char *tableName)
684 if (m_connection == NULL) {
685 LogPedantic("Cannot execute command. Not connected to DB!");
689 DataCommandAutoPtr command =
690 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
692 command->BindString(1, tableName);
694 if (!command->Step()) {
695 LogPedantic("No matching records in table");
699 return command->GetColumnString(0) == tableName;
702 SqlConnection::SqlConnection(const std::string &address,
705 SynchronizationObject *synchronizationObject) :
707 m_usingLucene(false),
708 m_dataCommandsCount(0),
709 m_synchronizationObject(synchronizationObject)
711 LogPedantic("Opening database connection to: " << address);
714 SqlConnection::Connect(address, flag, option);
716 if (!m_synchronizationObject) {
717 LogPedantic("No synchronization object defined");
721 SqlConnection::~SqlConnection()
723 LogPedantic("Closing database connection");
725 // Disconnect from DB
728 SqlConnection::Disconnect();
730 Catch(Exception::Base)
732 LogPedantic("Failed to disconnect from database");
736 void SqlConnection::ExecCommand(const char *format, ...)
738 if (m_connection == NULL) {
739 LogPedantic("Cannot execute command. Not connected to DB!");
743 if (format == NULL) {
744 LogPedantic("Null query!");
745 ThrowMsg(Exception::SyntaxError, "Null statement");
751 va_start(args, format);
753 if (vasprintf(&rawBuffer, format, args) == -1) {
759 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
762 LogPedantic("Failed to allocate statement string");
766 LogPedantic("Executing SQL command: " << buffer.get());
768 // Notify all after potentially synchronized database connection access
769 ScopedNotifyAll notifyAll(m_synchronizationObject.get());
774 int ret = sqlite3_exec(m_connection,
780 std::string errorMsg;
782 // Take allocated error buffer
783 if (errorBuffer != NULL) {
784 errorMsg = errorBuffer;
785 sqlite3_free(errorBuffer);
788 if (ret == SQLITE_OK) {
792 if (ret == SQLITE_BUSY) {
793 LogPedantic("Collision occurred while executing SQL command");
795 // Synchronize if synchronization object is available
796 if (m_synchronizationObject) {
797 LogPedantic("Performing synchronization");
798 m_synchronizationObject->Synchronize();
802 // No synchronization object defined. Fail.
806 LogPedantic("Failed to execute SQL command. Error: " << errorMsg);
807 ThrowMsg(Exception::SyntaxError, errorMsg);
811 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
815 if (m_connection == NULL) {
816 LogPedantic("Cannot execute data command. Not connected to DB!");
817 return DataCommandAutoPtr();
823 va_start(args, format);
825 if (vasprintf(&rawBuffer, format, args) == -1) {
831 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
834 LogPedantic("Failed to allocate statement string");
835 return DataCommandAutoPtr();
838 LogPedantic("Executing SQL data command: " << buffer.get());
840 return DataCommandAutoPtr(new DataCommand(this, buffer.get()));
843 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
845 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
848 void SqlConnection::TurnOnForeignKeys()
850 ExecCommand("PRAGMA foreign_keys = ON;");
853 void SqlConnection::BeginTransaction()
855 ExecCommand("BEGIN;");
858 void SqlConnection::RollbackTransaction()
860 ExecCommand("ROLLBACK;");
863 void SqlConnection::CommitTransaction()
865 ExecCommand("COMMIT;");
868 SqlConnection::SynchronizationObject *
869 SqlConnection::AllocDefaultSynchronizationObject()
871 return new NaiveSynchronizationObject();