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>
34 namespace SecurityManager {
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));
592 LogPedantic("Value: " << value);
593 String s = FromUTF8String(value);
594 return boost::optional<String>(s);
597 void SqlConnection::Connect(const std::string &address,
601 if (m_connection != NULL) {
602 LogPedantic("Already connected.");
605 LogPedantic("Connecting to DB: " << address << "...");
607 // Connect to database
609 if (type & Flag::UseLucene) {
610 result = db_util_open_with_options(
616 m_usingLucene = true;
617 LogPedantic("Lucene index enabled");
619 result = sqlite3_open_v2(
625 m_usingLucene = false;
626 LogPedantic("Lucene index disabled");
629 if (result == SQLITE_OK) {
630 LogPedantic("Connected to DB");
632 LogPedantic("Failed to connect to DB!");
633 ThrowMsg(Exception::ConnectionBroken, address);
636 // Enable foreign keys
640 void SqlConnection::Disconnect()
642 if (m_connection == NULL) {
643 LogPedantic("Already disconnected.");
647 LogPedantic("Disconnecting from DB...");
649 // All stored data commands must be deleted before disconnect
650 AssertMsg(m_dataCommandsCount == 0,
651 "All stored procedures must be deleted"
652 " before disconnecting SqlConnection");
657 result = db_util_close(m_connection);
659 result = sqlite3_close(m_connection);
662 if (result != SQLITE_OK) {
663 const char *error = sqlite3_errmsg(m_connection);
664 LogPedantic("SQL close failed");
665 LogPedantic(" Error: " << error);
666 Throw(Exception::InternalError);
671 LogPedantic("Disconnected from DB");
674 bool SqlConnection::CheckTableExist(const char *tableName)
676 if (m_connection == NULL) {
677 LogPedantic("Cannot execute command. Not connected to DB!");
681 DataCommandAutoPtr command =
682 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
684 command->BindString(1, tableName);
686 if (!command->Step()) {
687 LogPedantic("No matching records in table");
691 return command->GetColumnString(0) == tableName;
694 SqlConnection::SqlConnection(const std::string &address,
697 SynchronizationObject *synchronizationObject) :
699 m_usingLucene(false),
700 m_dataCommandsCount(0),
701 m_synchronizationObject(synchronizationObject)
703 LogPedantic("Opening database connection to: " << address);
706 SqlConnection::Connect(address, flag, option);
708 if (!m_synchronizationObject) {
709 LogPedantic("No synchronization object defined");
713 SqlConnection::~SqlConnection()
715 LogPedantic("Closing database connection");
717 // Disconnect from DB
720 SqlConnection::Disconnect();
722 Catch(Exception::Base)
724 LogPedantic("Failed to disconnect from database");
728 void SqlConnection::ExecCommand(const char *format, ...)
730 if (m_connection == NULL) {
731 LogPedantic("Cannot execute command. Not connected to DB!");
735 if (format == NULL) {
736 LogPedantic("Null query!");
737 ThrowMsg(Exception::SyntaxError, "Null statement");
743 va_start(args, format);
745 if (vasprintf(&rawBuffer, format, args) == -1) {
751 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
754 LogPedantic("Failed to allocate statement string");
758 LogPedantic("Executing SQL command: " << buffer.get());
760 // Notify all after potentially synchronized database connection access
761 ScopedNotifyAll notifyAll(m_synchronizationObject.get());
766 int ret = sqlite3_exec(m_connection,
772 std::string errorMsg;
774 // Take allocated error buffer
775 if (errorBuffer != NULL) {
776 errorMsg = errorBuffer;
777 sqlite3_free(errorBuffer);
780 if (ret == SQLITE_OK) {
784 if (ret == SQLITE_BUSY) {
785 LogPedantic("Collision occurred while executing SQL command");
787 // Synchronize if synchronization object is available
788 if (m_synchronizationObject) {
789 LogPedantic("Performing synchronization");
790 m_synchronizationObject->Synchronize();
794 // No synchronization object defined. Fail.
798 LogPedantic("Failed to execute SQL command. Error: " << errorMsg);
799 ThrowMsg(Exception::SyntaxError, errorMsg);
803 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
807 if (m_connection == NULL) {
808 LogPedantic("Cannot execute data command. Not connected to DB!");
809 return DataCommandAutoPtr();
815 va_start(args, format);
817 if (vasprintf(&rawBuffer, format, args) == -1) {
823 std::unique_ptr<char[],free_deleter> buffer(rawBuffer);
826 LogPedantic("Failed to allocate statement string");
827 return DataCommandAutoPtr();
830 LogPedantic("Executing SQL data command: " << buffer.get());
832 return DataCommandAutoPtr(new DataCommand(this, buffer.get()));
835 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
837 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
840 void SqlConnection::TurnOnForeignKeys()
842 ExecCommand("PRAGMA foreign_keys = ON;");
845 void SqlConnection::BeginTransaction()
847 ExecCommand("BEGIN;");
850 void SqlConnection::RollbackTransaction()
852 ExecCommand("ROLLBACK;");
855 void SqlConnection::CommitTransaction()
857 ExecCommand("COMMIT;");
860 SqlConnection::SynchronizationObject *
861 SqlConnection::AllocDefaultSynchronizationObject()
863 return new NaiveSynchronizationObject();
866 } // namespace SecurityManager