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
22 #include <dpl/db/sql_connection.h>
23 #include <dpl/db/naive_synchronization_object.h>
24 #include <dpl/scoped_free.h>
25 #include <dpl/noncopyable.h>
26 #include <dpl/assert.h>
37 namespace // anonymous
43 SqlConnection::SynchronizationObject *m_synchronizationObject;
46 explicit ScopedNotifyAll(
47 SqlConnection::SynchronizationObject *synchronizationObject)
48 : m_synchronizationObject(synchronizationObject)
54 if (!m_synchronizationObject)
57 LogPedantic("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());
75 int ret = sqlite3_prepare_v2(connection->m_connection,
76 buffer, strlen(buffer),
81 LogPedantic("Data command prepared successfuly");
84 else if (ret == SQLITE_BUSY)
86 LogPedantic("Collision occurred while preparing SQL command");
88 // Synchronize if synchronization object is available
89 if (connection->m_synchronizationObject)
91 LogPedantic("Performing synchronization");
92 connection->m_synchronizationObject->Synchronize();
96 // No synchronization object defined. Fail.
100 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
102 LogPedantic("SQL prepare data command failed");
103 LogPedantic(" Statement: " << buffer);
104 LogPedantic(" Error: " << error);
106 ThrowMsg(Exception::SyntaxError, error);
109 LogPedantic("Prepared data command: " << buffer);
111 // Increment stored data command count
112 ++m_masterConnection->m_dataCommandsCount;
115 SqlConnection::DataCommand::~DataCommand()
117 LogPedantic("SQL data command finalizing");
119 if (sqlite3_finalize(m_stmt) != SQLITE_OK)
120 LogPedantic("Failed to finalize data command");
122 // Decrement stored data command count
123 --m_masterConnection->m_dataCommandsCount;
126 void SqlConnection::DataCommand::CheckBindResult(int result)
128 if (result != SQLITE_OK)
130 const char *error = sqlite3_errmsg(
131 m_masterConnection->m_connection);
133 LogPedantic("Failed to bind SQL statement parameter");
134 LogPedantic(" Error: " << error);
136 ThrowMsg(Exception::SyntaxError, error);
140 void SqlConnection::DataCommand::BindNull(
141 SqlConnection::ArgumentIndex position)
143 CheckBindResult(sqlite3_bind_null(m_stmt, position));
144 LogPedantic("SQL data command bind null: ["
148 void SqlConnection::DataCommand::BindInteger(
149 SqlConnection::ArgumentIndex position,
152 CheckBindResult(sqlite3_bind_int(m_stmt, position, value));
153 LogPedantic("SQL data command bind integer: ["
154 << position << "] -> " << value);
157 void SqlConnection::DataCommand::BindInt8(
158 SqlConnection::ArgumentIndex position,
161 CheckBindResult(sqlite3_bind_int(m_stmt, position,
162 static_cast<int>(value)));
163 LogPedantic("SQL data command bind int8: ["
164 << position << "] -> " << value);
167 void SqlConnection::DataCommand::BindInt16(
168 SqlConnection::ArgumentIndex position,
171 CheckBindResult(sqlite3_bind_int(m_stmt, position,
172 static_cast<int>(value)));
173 LogPedantic("SQL data command bind int16: ["
174 << position << "] -> " << value);
177 void SqlConnection::DataCommand::BindInt32(
178 SqlConnection::ArgumentIndex position,
181 CheckBindResult(sqlite3_bind_int(m_stmt, position,
182 static_cast<int>(value)));
183 LogPedantic("SQL data command bind int32: ["
184 << position << "] -> " << value);
187 void SqlConnection::DataCommand::BindInt64(
188 SqlConnection::ArgumentIndex position,
191 CheckBindResult(sqlite3_bind_int64(m_stmt, position,
192 static_cast<sqlite3_int64>(value)));
193 LogPedantic("SQL data command bind int64: ["
194 << position << "] -> " << value);
197 void SqlConnection::DataCommand::BindFloat(
198 SqlConnection::ArgumentIndex position,
201 CheckBindResult(sqlite3_bind_double(m_stmt, position,
202 static_cast<double>(value)));
203 LogPedantic("SQL data command bind float: ["
204 << position << "] -> " << value);
207 void SqlConnection::DataCommand::BindDouble(
208 SqlConnection::ArgumentIndex position,
211 CheckBindResult(sqlite3_bind_double(m_stmt, position, value));
212 LogPedantic("SQL data command bind double: ["
213 << position << "] -> " << value);
216 void SqlConnection::DataCommand::BindString(
217 SqlConnection::ArgumentIndex position,
226 // Assume that text may disappear
227 CheckBindResult(sqlite3_bind_text(m_stmt, position,
228 value, strlen(value),
231 LogPedantic("SQL data command bind string: ["
232 << position << "] -> " << value);
235 void SqlConnection::DataCommand::BindString(
236 SqlConnection::ArgumentIndex position,
239 BindString(position, ToUTF8String(value).c_str());
242 void SqlConnection::DataCommand::BindInteger(
243 SqlConnection::ArgumentIndex position,
244 const Optional<int> &value)
249 BindInteger(position, *value);
252 void SqlConnection::DataCommand::BindInt8(
253 SqlConnection::ArgumentIndex position,
254 const Optional<int8_t> &value)
259 BindInt8(position, *value);
262 void SqlConnection::DataCommand::BindInt16(
263 SqlConnection::ArgumentIndex position,
264 const Optional<int16_t> &value)
269 BindInt16(position, *value);
272 void SqlConnection::DataCommand::BindInt32(
273 SqlConnection::ArgumentIndex position,
274 const Optional<int32_t> &value)
279 BindInt32(position, *value);
282 void SqlConnection::DataCommand::BindInt64(
283 SqlConnection::ArgumentIndex position,
284 const Optional<int64_t> &value)
289 BindInt64(position, *value);
292 void SqlConnection::DataCommand::BindFloat(
293 SqlConnection::ArgumentIndex position,
294 const Optional<float> &value)
299 BindFloat(position, *value);
302 void SqlConnection::DataCommand::BindDouble(
303 SqlConnection::ArgumentIndex position,
304 const Optional<double> &value)
309 BindDouble(position, *value);
312 void SqlConnection::DataCommand::BindString(
313 SqlConnection::ArgumentIndex position,
314 const Optional<String> &value)
317 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());
330 int ret = sqlite3_step(m_stmt);
332 if (ret == SQLITE_ROW)
334 LogPedantic("SQL data command step ROW");
337 else if (ret == SQLITE_DONE)
339 LogPedantic("SQL data command step DONE");
342 else if (ret == SQLITE_BUSY)
344 LogPedantic("Collision occurred while executing SQL command");
346 // Synchronize if synchronization object is available
347 if (m_masterConnection->m_synchronizationObject)
349 LogPedantic("Performing synchronization");
352 m_synchronizationObject->Synchronize();
357 // No synchronization object defined. Fail.
361 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
363 LogPedantic("SQL step data command failed");
364 LogPedantic(" Error: " << error);
366 ThrowMsg(Exception::InternalError, error);
370 void SqlConnection::DataCommand::Reset()
374 * http://www.sqlite.org/c3ref/stmt.html
376 * if last sqlite3_step command on this stmt returned an error,
377 * then sqlite3_reset will return that error, althought it is not an error.
378 * So sqlite3_reset allways succedes.
380 sqlite3_reset(m_stmt);
382 LogPedantic("SQL data command reset");
385 void SqlConnection::DataCommand::CheckColumnIndex(
386 SqlConnection::ColumnIndex column)
388 if (column < 0 || column >= sqlite3_column_count(m_stmt))
389 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
392 bool SqlConnection::DataCommand::IsColumnNull(
393 SqlConnection::ColumnIndex column)
395 LogPedantic("SQL data command get column type: [" << column << "]");
396 CheckColumnIndex(column);
397 return sqlite3_column_type(m_stmt, column) == SQLITE_NULL;
400 int SqlConnection::DataCommand::GetColumnInteger(
401 SqlConnection::ColumnIndex column)
403 LogPedantic("SQL data command get column integer: [" << column << "]");
404 CheckColumnIndex(column);
405 int value = sqlite3_column_int(m_stmt, column);
406 LogPedantic(" Value: " << value);
410 int8_t SqlConnection::DataCommand::GetColumnInt8(
411 SqlConnection::ColumnIndex column)
413 LogPedantic("SQL data command get column int8: [" << column << "]");
414 CheckColumnIndex(column);
415 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
416 LogPedantic(" Value: " << value);
420 int16_t SqlConnection::DataCommand::GetColumnInt16(
421 SqlConnection::ColumnIndex column)
423 LogPedantic("SQL data command get column int16: [" << column << "]");
424 CheckColumnIndex(column);
425 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
426 LogPedantic(" Value: " << value);
430 int32_t SqlConnection::DataCommand::GetColumnInt32(
431 SqlConnection::ColumnIndex column)
433 LogPedantic("SQL data command get column int32: [" << column << "]");
434 CheckColumnIndex(column);
435 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
436 LogPedantic(" Value: " << value);
440 int64_t SqlConnection::DataCommand::GetColumnInt64(
441 SqlConnection::ColumnIndex column)
443 LogPedantic("SQL data command get column int64: [" << column << "]");
444 CheckColumnIndex(column);
445 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
446 LogPedantic(" Value: " << value);
450 float SqlConnection::DataCommand::GetColumnFloat(
451 SqlConnection::ColumnIndex column)
453 LogPedantic("SQL data command get column float: [" << column << "]");
454 CheckColumnIndex(column);
455 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
456 LogPedantic(" Value: " << value);
460 double SqlConnection::DataCommand::GetColumnDouble(
461 SqlConnection::ColumnIndex column)
463 LogPedantic("SQL data command get column double: [" << column << "]");
464 CheckColumnIndex(column);
465 double value = sqlite3_column_double(m_stmt, column);
466 LogPedantic(" Value: " << value);
470 std::string SqlConnection::DataCommand::GetColumnString(
471 SqlConnection::ColumnIndex column)
473 LogPedantic("SQL data command get column string: [" << column << "]");
474 CheckColumnIndex(column);
476 const char *value = reinterpret_cast<const char *>(
477 sqlite3_column_text(m_stmt, column));
479 LogPedantic("Value: " << (value ? value : "NULL"));
482 return std::string();
484 return std::string(value);
487 Optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
488 SqlConnection::ColumnIndex column)
490 LogPedantic("SQL data command get column optional integer: ["
492 CheckColumnIndex(column);
493 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
494 return Optional<int>::Null;
495 int value = sqlite3_column_int(m_stmt, column);
496 LogPedantic(" Value: " << value);
497 return Optional<int>(value);
500 Optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
501 SqlConnection::ColumnIndex column)
503 LogPedantic("SQL data command get column optional int8: ["
505 CheckColumnIndex(column);
506 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
507 return Optional<int8_t>::Null;
508 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
509 LogPedantic(" Value: " << value);
510 return Optional<int8_t>(value);
513 Optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
514 SqlConnection::ColumnIndex column)
516 LogPedantic("SQL data command get column optional int16: ["
518 CheckColumnIndex(column);
519 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
520 return Optional<int16_t>::Null;
521 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
522 LogPedantic(" Value: " << value);
523 return Optional<int16_t>(value);
526 Optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
527 SqlConnection::ColumnIndex column)
529 LogPedantic("SQL data command get column optional int32: ["
531 CheckColumnIndex(column);
532 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
533 return Optional<int32_t>::Null;
534 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
535 LogPedantic(" Value: " << value);
536 return Optional<int32_t>(value);
539 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 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;
560 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
561 LogPedantic(" Value: " << value);
562 return Optional<float>(value);
565 Optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
566 SqlConnection::ColumnIndex column)
568 LogPedantic("SQL data command get column optional double: ["
570 CheckColumnIndex(column);
571 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
572 return Optional<double>::Null;
573 double value = sqlite3_column_double(m_stmt, column);
574 LogPedantic(" Value: " << value);
575 return Optional<double>(value);
578 Optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
579 SqlConnection::ColumnIndex column)
581 LogPedantic("SQL data command get column optional string: ["
583 CheckColumnIndex(column);
584 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
585 return Optional<String>::Null;
586 const char *value = reinterpret_cast<const char *>(
587 sqlite3_column_text(m_stmt, column));
588 LogPedantic("Value: " << value);
589 String s = FromUTF8String(value);
590 return Optional<String>(s);
593 void SqlConnection::Connect(const std::string &address, Flag::Type flag)
595 if (m_connection != NULL)
597 LogPedantic("Already connected.");
601 LogPedantic("Connecting to DB: " << address << "...");
603 // Connect to database
606 if (flag & Flag::UseLucene)
608 result = db_util_open_with_options(
611 SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
614 m_usingLucene = true;
615 LogPedantic("Lucene index enabled");
619 result = sqlite3_open_v2(
622 SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
625 m_usingLucene = false;
626 LogPedantic("Lucene index disabled");
629 if (result == SQLITE_OK)
631 LogPedantic("Connected to DB");
635 LogPedantic("Failed to connect to DB!");
636 ThrowMsg(Exception::ConnectionBroken, address);
639 // Enable foreign keys
643 void SqlConnection::Disconnect()
645 if (m_connection == NULL)
647 LogPedantic("Already disconnected.");
651 LogPedantic("Disconnecting from DB...");
653 // All stored data commands must be deleted before disconnect
654 Assert(m_dataCommandsCount == 0 &&
655 "All stored procedures must be deleted"
656 " before disconnecting SqlConnection");
662 result = db_util_close(m_connection);
666 result = sqlite3_close(m_connection);
669 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)
686 LogPedantic("Cannot execute command. Not connected to DB!");
690 DataCommandAutoPtr command =
691 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
693 command->BindString(1, tableName);
695 if (!command->Step())
697 LogPedantic("No matching records in table");
701 return command->GetColumnString(0) == tableName;
704 SqlConnection::SqlConnection(const std::string &address,
706 SynchronizationObject *synchronizationObject)
707 : m_connection(NULL),
708 m_usingLucene(false),
709 m_dataCommandsCount(0),
710 m_synchronizationObject(synchronizationObject)
712 LogPedantic("Opening database connection to: " << address);
715 SqlConnection::Connect(address, flag);
717 if (!m_synchronizationObject)
719 LogPedantic("No synchronization object defined");
723 SqlConnection::~SqlConnection()
725 LogPedantic("Closing database connection");
727 // Disconnect from DB
730 SqlConnection::Disconnect();
732 Catch (Exception::Base)
734 LogPedantic("Failed to disconnect from database");
738 void SqlConnection::ExecCommand(const char *format, ...)
740 if (m_connection == NULL)
742 LogPedantic("Cannot execute command. Not connected to DB!");
749 va_start(args, format);
751 if (vasprintf(&rawBuffer, format, args) == -1)
756 ScopedFree<char> buffer(rawBuffer);
760 LogPedantic("Failed to allocate statement string");
764 LogPedantic("Executing SQL command: " << buffer.Get());
766 // Notify all after potentially synchronized database connection access
767 ScopedNotifyAll notifyAll(m_synchronizationObject.Get());
773 int ret = sqlite3_exec(m_connection,
779 std::string errorMsg;
781 // Take allocated error buffer
782 if (errorBuffer != NULL)
784 errorMsg = errorBuffer;
785 sqlite3_free(errorBuffer);
788 if (ret == SQLITE_OK)
791 if (ret == SQLITE_BUSY)
793 LogPedantic("Collision occurred while executing SQL command");
795 // Synchronize if synchronization object is available
796 if (m_synchronizationObject)
798 LogPedantic("Performing synchronization");
799 m_synchronizationObject->Synchronize();
803 // No synchronization object defined. Fail.
807 LogPedantic("Failed to execute SQL command. Error: " << errorMsg);
808 ThrowMsg(Exception::SyntaxError, errorMsg);
812 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
816 if (m_connection == NULL)
818 LogPedantic("Cannot execute data command. Not connected to DB!");
819 return DataCommandAutoPtr();
825 va_start(args, format);
827 if (vasprintf(&rawBuffer, format, args) == -1)
832 ScopedFree<char> buffer(rawBuffer);
836 LogPedantic("Failed to allocate statement string");
837 return DataCommandAutoPtr();
840 LogPedantic("Executing SQL data command: " << buffer.Get());
842 return DataCommandAutoPtr(new DataCommand(this, buffer.Get()));
845 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
847 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
850 void SqlConnection::TurnOnForeignKeys()
852 ExecCommand("PRAGMA foreign_keys = ON;");
855 SqlConnection::SynchronizationObject *
856 SqlConnection::AllocDefaultSynchronizationObject()
858 return new NaiveSynchronizationObject();