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/scoped_free.h>
26 #include <dpl/noncopyable.h>
27 #include <dpl/assert.h>
38 namespace // anonymous
44 SqlConnection::SynchronizationObject *m_synchronizationObject;
47 explicit ScopedNotifyAll(
48 SqlConnection::SynchronizationObject *synchronizationObject)
49 : m_synchronizationObject(synchronizationObject)
55 if (!m_synchronizationObject)
58 LogPedantic("Notifying after successful synchronize");
59 m_synchronizationObject->NotifyAll();
62 } // namespace anonymous
64 SqlConnection::DataCommand::DataCommand(SqlConnection *connection,
66 : m_masterConnection(connection),
69 Assert(connection != NULL);
71 // Notify all after potentially synchronized database connection access
72 ScopedNotifyAll notifyAll(connection->m_synchronizationObject.get());
76 int ret = sqlite3_prepare_v2(connection->m_connection,
77 buffer, strlen(buffer),
82 LogPedantic("Data command prepared successfuly");
85 else if (ret == SQLITE_BUSY)
87 LogPedantic("Collision occurred while preparing SQL command");
89 // Synchronize if synchronization object is available
90 if (connection->m_synchronizationObject)
92 LogPedantic("Performing synchronization");
93 connection->m_synchronizationObject->Synchronize();
97 // No synchronization object defined. Fail.
101 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
103 LogPedantic("SQL prepare data command failed");
104 LogPedantic(" Statement: " << buffer);
105 LogPedantic(" Error: " << error);
107 ThrowMsg(Exception::SyntaxError, error);
110 LogPedantic("Prepared data command: " << buffer);
112 // Increment stored data command count
113 ++m_masterConnection->m_dataCommandsCount;
116 SqlConnection::DataCommand::~DataCommand()
118 LogPedantic("SQL data command finalizing");
120 if (sqlite3_finalize(m_stmt) != SQLITE_OK)
121 LogPedantic("Failed to finalize data command");
123 // Decrement stored data command count
124 --m_masterConnection->m_dataCommandsCount;
127 void SqlConnection::DataCommand::CheckBindResult(int result)
129 if (result != SQLITE_OK)
131 const char *error = sqlite3_errmsg(
132 m_masterConnection->m_connection);
134 LogPedantic("Failed to bind SQL statement parameter");
135 LogPedantic(" Error: " << error);
137 ThrowMsg(Exception::SyntaxError, error);
141 void SqlConnection::DataCommand::BindNull(
142 SqlConnection::ArgumentIndex position)
144 CheckBindResult(sqlite3_bind_null(m_stmt, position));
145 LogPedantic("SQL data command bind null: ["
149 void SqlConnection::DataCommand::BindInteger(
150 SqlConnection::ArgumentIndex position,
153 CheckBindResult(sqlite3_bind_int(m_stmt, position, value));
154 LogPedantic("SQL data command bind integer: ["
155 << position << "] -> " << value);
158 void SqlConnection::DataCommand::BindInt8(
159 SqlConnection::ArgumentIndex position,
162 CheckBindResult(sqlite3_bind_int(m_stmt, position,
163 static_cast<int>(value)));
164 LogPedantic("SQL data command bind int8: ["
165 << position << "] -> " << value);
168 void SqlConnection::DataCommand::BindInt16(
169 SqlConnection::ArgumentIndex position,
172 CheckBindResult(sqlite3_bind_int(m_stmt, position,
173 static_cast<int>(value)));
174 LogPedantic("SQL data command bind int16: ["
175 << position << "] -> " << value);
178 void SqlConnection::DataCommand::BindInt32(
179 SqlConnection::ArgumentIndex position,
182 CheckBindResult(sqlite3_bind_int(m_stmt, position,
183 static_cast<int>(value)));
184 LogPedantic("SQL data command bind int32: ["
185 << position << "] -> " << value);
188 void SqlConnection::DataCommand::BindInt64(
189 SqlConnection::ArgumentIndex position,
192 CheckBindResult(sqlite3_bind_int64(m_stmt, position,
193 static_cast<sqlite3_int64>(value)));
194 LogPedantic("SQL data command bind int64: ["
195 << position << "] -> " << value);
198 void SqlConnection::DataCommand::BindFloat(
199 SqlConnection::ArgumentIndex position,
202 CheckBindResult(sqlite3_bind_double(m_stmt, position,
203 static_cast<double>(value)));
204 LogPedantic("SQL data command bind float: ["
205 << position << "] -> " << value);
208 void SqlConnection::DataCommand::BindDouble(
209 SqlConnection::ArgumentIndex position,
212 CheckBindResult(sqlite3_bind_double(m_stmt, position, value));
213 LogPedantic("SQL data command bind double: ["
214 << position << "] -> " << value);
217 void SqlConnection::DataCommand::BindString(
218 SqlConnection::ArgumentIndex position,
227 // Assume that text may disappear
228 CheckBindResult(sqlite3_bind_text(m_stmt, position,
229 value, strlen(value),
232 LogPedantic("SQL data command bind string: ["
233 << position << "] -> " << value);
236 void SqlConnection::DataCommand::BindString(
237 SqlConnection::ArgumentIndex position,
240 BindString(position, ToUTF8String(value).c_str());
243 void SqlConnection::DataCommand::BindInteger(
244 SqlConnection::ArgumentIndex position,
245 const Optional<int> &value)
250 BindInteger(position, *value);
253 void SqlConnection::DataCommand::BindInt8(
254 SqlConnection::ArgumentIndex position,
255 const Optional<int8_t> &value)
260 BindInt8(position, *value);
263 void SqlConnection::DataCommand::BindInt16(
264 SqlConnection::ArgumentIndex position,
265 const Optional<int16_t> &value)
270 BindInt16(position, *value);
273 void SqlConnection::DataCommand::BindInt32(
274 SqlConnection::ArgumentIndex position,
275 const Optional<int32_t> &value)
280 BindInt32(position, *value);
283 void SqlConnection::DataCommand::BindInt64(
284 SqlConnection::ArgumentIndex position,
285 const Optional<int64_t> &value)
290 BindInt64(position, *value);
293 void SqlConnection::DataCommand::BindFloat(
294 SqlConnection::ArgumentIndex position,
295 const Optional<float> &value)
300 BindFloat(position, *value);
303 void SqlConnection::DataCommand::BindDouble(
304 SqlConnection::ArgumentIndex position,
305 const Optional<double> &value)
310 BindDouble(position, *value);
313 void SqlConnection::DataCommand::BindString(
314 SqlConnection::ArgumentIndex position,
315 const Optional<String> &value)
318 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());
331 int ret = sqlite3_step(m_stmt);
333 if (ret == SQLITE_ROW)
335 LogPedantic("SQL data command step ROW");
338 else if (ret == SQLITE_DONE)
340 LogPedantic("SQL data command step DONE");
343 else if (ret == SQLITE_BUSY)
345 LogPedantic("Collision occurred while executing SQL command");
347 // Synchronize if synchronization object is available
348 if (m_masterConnection->m_synchronizationObject)
350 LogPedantic("Performing synchronization");
353 m_synchronizationObject->Synchronize();
358 // No synchronization object defined. Fail.
362 const char *error = sqlite3_errmsg(m_masterConnection->m_connection);
364 LogPedantic("SQL step data command failed");
365 LogPedantic(" Error: " << error);
367 ThrowMsg(Exception::InternalError, error);
371 void SqlConnection::DataCommand::Reset()
375 * http://www.sqlite.org/c3ref/stmt.html
377 * if last sqlite3_step command on this stmt returned an error,
378 * then sqlite3_reset will return that error, althought it is not an error.
379 * So sqlite3_reset allways succedes.
381 sqlite3_reset(m_stmt);
383 LogPedantic("SQL data command reset");
386 void SqlConnection::DataCommand::CheckColumnIndex(
387 SqlConnection::ColumnIndex column)
389 if (column < 0 || column >= sqlite3_column_count(m_stmt))
390 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
393 bool SqlConnection::DataCommand::IsColumnNull(
394 SqlConnection::ColumnIndex column)
396 LogPedantic("SQL data command get column type: [" << column << "]");
397 CheckColumnIndex(column);
398 return sqlite3_column_type(m_stmt, column) == SQLITE_NULL;
401 int SqlConnection::DataCommand::GetColumnInteger(
402 SqlConnection::ColumnIndex column)
404 LogPedantic("SQL data command get column integer: [" << column << "]");
405 CheckColumnIndex(column);
406 int value = sqlite3_column_int(m_stmt, column);
407 LogPedantic(" Value: " << value);
411 int8_t SqlConnection::DataCommand::GetColumnInt8(
412 SqlConnection::ColumnIndex column)
414 LogPedantic("SQL data command get column int8: [" << column << "]");
415 CheckColumnIndex(column);
416 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
417 LogPedantic(" Value: " << value);
421 int16_t SqlConnection::DataCommand::GetColumnInt16(
422 SqlConnection::ColumnIndex column)
424 LogPedantic("SQL data command get column int16: [" << column << "]");
425 CheckColumnIndex(column);
426 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
427 LogPedantic(" Value: " << value);
431 int32_t SqlConnection::DataCommand::GetColumnInt32(
432 SqlConnection::ColumnIndex column)
434 LogPedantic("SQL data command get column int32: [" << column << "]");
435 CheckColumnIndex(column);
436 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
437 LogPedantic(" Value: " << value);
441 int64_t SqlConnection::DataCommand::GetColumnInt64(
442 SqlConnection::ColumnIndex column)
444 LogPedantic("SQL data command get column int64: [" << column << "]");
445 CheckColumnIndex(column);
446 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
447 LogPedantic(" Value: " << value);
451 float SqlConnection::DataCommand::GetColumnFloat(
452 SqlConnection::ColumnIndex column)
454 LogPedantic("SQL data command get column float: [" << column << "]");
455 CheckColumnIndex(column);
456 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
457 LogPedantic(" Value: " << value);
461 double SqlConnection::DataCommand::GetColumnDouble(
462 SqlConnection::ColumnIndex column)
464 LogPedantic("SQL data command get column double: [" << column << "]");
465 CheckColumnIndex(column);
466 double value = sqlite3_column_double(m_stmt, column);
467 LogPedantic(" Value: " << value);
471 std::string SqlConnection::DataCommand::GetColumnString(
472 SqlConnection::ColumnIndex column)
474 LogPedantic("SQL data command get column string: [" << column << "]");
475 CheckColumnIndex(column);
477 const char *value = reinterpret_cast<const char *>(
478 sqlite3_column_text(m_stmt, column));
480 LogPedantic("Value: " << (value ? value : "NULL"));
483 return std::string();
485 return std::string(value);
488 Optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
489 SqlConnection::ColumnIndex column)
491 LogPedantic("SQL data command get column optional integer: ["
493 CheckColumnIndex(column);
494 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
495 return Optional<int>::Null;
496 int value = sqlite3_column_int(m_stmt, column);
497 LogPedantic(" Value: " << value);
498 return Optional<int>(value);
501 Optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
502 SqlConnection::ColumnIndex column)
504 LogPedantic("SQL data command get column optional int8: ["
506 CheckColumnIndex(column);
507 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
508 return Optional<int8_t>::Null;
509 int8_t value = static_cast<int8_t>(sqlite3_column_int(m_stmt, column));
510 LogPedantic(" Value: " << value);
511 return Optional<int8_t>(value);
514 Optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
515 SqlConnection::ColumnIndex column)
517 LogPedantic("SQL data command get column optional int16: ["
519 CheckColumnIndex(column);
520 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
521 return Optional<int16_t>::Null;
522 int16_t value = static_cast<int16_t>(sqlite3_column_int(m_stmt, column));
523 LogPedantic(" Value: " << value);
524 return Optional<int16_t>(value);
527 Optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
528 SqlConnection::ColumnIndex column)
530 LogPedantic("SQL data command get column optional int32: ["
532 CheckColumnIndex(column);
533 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
534 return Optional<int32_t>::Null;
535 int32_t value = static_cast<int32_t>(sqlite3_column_int(m_stmt, column));
536 LogPedantic(" Value: " << value);
537 return Optional<int32_t>(value);
540 Optional<int64_t> SqlConnection::DataCommand::GetColumnOptionalInt64(
541 SqlConnection::ColumnIndex column)
543 LogPedantic("SQL data command get column optional int64: ["
545 CheckColumnIndex(column);
546 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
547 return Optional<int64_t>::Null;
548 int64_t value = static_cast<int64_t>(sqlite3_column_int64(m_stmt, column));
549 LogPedantic(" Value: " << value);
550 return Optional<int64_t>(value);
553 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 Optional<float>::Null;
561 float value = static_cast<float>(sqlite3_column_double(m_stmt, column));
562 LogPedantic(" Value: " << value);
563 return Optional<float>(value);
566 Optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
567 SqlConnection::ColumnIndex column)
569 LogPedantic("SQL data command get column optional double: ["
571 CheckColumnIndex(column);
572 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
573 return Optional<double>::Null;
574 double value = sqlite3_column_double(m_stmt, column);
575 LogPedantic(" Value: " << value);
576 return Optional<double>(value);
579 Optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
580 SqlConnection::ColumnIndex column)
582 LogPedantic("SQL data command get column optional string: ["
584 CheckColumnIndex(column);
585 if (sqlite3_column_type(m_stmt, column) == SQLITE_NULL)
586 return Optional<String>::Null;
587 const char *value = reinterpret_cast<const char *>(
588 sqlite3_column_text(m_stmt, column));
589 LogPedantic("Value: " << value);
590 String s = FromUTF8String(value);
591 return Optional<String>(s);
594 void SqlConnection::Connect(const std::string &address,
598 if (m_connection != NULL) {
599 LogPedantic("Already connected.");
602 LogPedantic("Connecting to DB: " << address << "...");
604 // Connect to database
606 if (type & Flag::UseLucene) {
607 result = db_util_open_with_options(
613 m_usingLucene = true;
614 LogPedantic("Lucene index enabled");
616 result = sqlite3_open_v2(
622 m_usingLucene = false;
623 LogPedantic("Lucene index disabled");
626 if (result == SQLITE_OK) {
627 LogPedantic("Connected to DB");
629 LogPedantic("Failed to connect to DB!");
630 ThrowMsg(Exception::ConnectionBroken, address);
633 // Enable foreign keys
637 void SqlConnection::Disconnect()
639 if (m_connection == NULL)
641 LogPedantic("Already disconnected.");
645 LogPedantic("Disconnecting from DB...");
647 // All stored data commands must be deleted before disconnect
648 Assert(m_dataCommandsCount == 0 &&
649 "All stored procedures must be deleted"
650 " before disconnecting SqlConnection");
656 result = db_util_close(m_connection);
660 result = sqlite3_close(m_connection);
663 if (result != SQLITE_OK)
665 const char *error = sqlite3_errmsg(m_connection);
666 LogPedantic("SQL close failed");
667 LogPedantic(" Error: " << error);
668 Throw(Exception::InternalError);
673 LogPedantic("Disconnected from DB");
676 bool SqlConnection::CheckTableExist(const char *tableName)
678 if (m_connection == NULL)
680 LogPedantic("Cannot execute command. Not connected to DB!");
684 DataCommandAutoPtr command =
685 PrepareDataCommand("select tbl_name from sqlite_master where name=?;");
687 command->BindString(1, tableName);
689 if (!command->Step())
691 LogPedantic("No matching records in table");
695 return command->GetColumnString(0) == tableName;
698 SqlConnection::SqlConnection(const std::string &address,
701 SynchronizationObject *synchronizationObject)
702 : m_connection(NULL),
703 m_usingLucene(false),
704 m_dataCommandsCount(0),
705 m_synchronizationObject(synchronizationObject)
707 LogPedantic("Opening database connection to: " << address);
710 SqlConnection::Connect(address, flag, option);
712 if (!m_synchronizationObject)
714 LogPedantic("No synchronization object defined");
718 SqlConnection::~SqlConnection()
720 LogPedantic("Closing database connection");
722 // Disconnect from DB
725 SqlConnection::Disconnect();
727 Catch (Exception::Base)
729 LogPedantic("Failed to disconnect from database");
733 void SqlConnection::ExecCommand(const char *format, ...)
735 if (m_connection == NULL)
737 LogPedantic("Cannot execute command. Not connected to DB!");
743 LogPedantic("Null query!");
744 ThrowMsg(Exception::SyntaxError, "Null statement");
750 va_start(args, format);
752 if (vasprintf(&rawBuffer, format, args) == -1)
757 ScopedFree<char> buffer(rawBuffer);
761 LogPedantic("Failed to allocate statement string");
765 LogPedantic("Executing SQL command: " << buffer.Get());
767 // Notify all after potentially synchronized database connection access
768 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)
785 errorMsg = errorBuffer;
786 sqlite3_free(errorBuffer);
789 if (ret == SQLITE_OK)
792 if (ret == SQLITE_BUSY)
794 LogPedantic("Collision occurred while executing SQL command");
796 // Synchronize if synchronization object is available
797 if (m_synchronizationObject)
799 LogPedantic("Performing synchronization");
800 m_synchronizationObject->Synchronize();
804 // No synchronization object defined. Fail.
808 LogPedantic("Failed to execute SQL command. Error: " << errorMsg);
809 ThrowMsg(Exception::SyntaxError, errorMsg);
813 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
817 if (m_connection == NULL)
819 LogPedantic("Cannot execute data command. Not connected to DB!");
820 return DataCommandAutoPtr();
826 va_start(args, format);
828 if (vasprintf(&rawBuffer, format, args) == -1)
833 ScopedFree<char> buffer(rawBuffer);
837 LogPedantic("Failed to allocate statement string");
838 return DataCommandAutoPtr();
841 LogPedantic("Executing SQL data command: " << buffer.Get());
843 return DataCommandAutoPtr(new DataCommand(this, buffer.Get()));
846 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
848 return static_cast<RowID>(sqlite3_last_insert_rowid(m_connection));
851 void SqlConnection::TurnOnForeignKeys()
853 ExecCommand("PRAGMA foreign_keys = ON;");
856 SqlConnection::SynchronizationObject *
857 SqlConnection::AllocDefaultSynchronizationObject()
859 return new NaiveSynchronizationObject();