2 * Copyright (c) 2014 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 #pragma GCC diagnostic push
23 #pragma GCC diagnostic warning "-Wdeprecated-declarations"
26 #include <dpl/db/sql_connection.h>
27 #include <dpl/db/naive_synchronization_object.h>
28 #include <dpl/noncopyable.h>
29 #include <dpl/assert.h>
30 #include <dpl/scoped_ptr.h>
39 namespace // anonymous
41 class ScopedNotifyAll :
45 SqlConnection::SynchronizationObject *m_synchronizationObject;
48 explicit ScopedNotifyAll(
49 SqlConnection::SynchronizationObject *synchronizationObject) :
50 m_synchronizationObject(synchronizationObject)
55 if (!m_synchronizationObject) {
59 LogPedantic("Notifying after successful synchronize");
60 m_synchronizationObject->NotifyAll();
63 } // namespace anonymous
65 SqlConnection::DataCommand::DataCommand(SqlConnection *connection,
67 m_masterConnection(connection),
70 Assert(connection != NULL);
72 // Notify all after potentially synchronized database connection access
73 ScopedNotifyAll notifyAll(connection->m_synchronizationObject.get());
76 int ret = sqlcipher3_prepare_v2(connection->m_connection,
77 buffer, strlen(buffer),
80 if (ret == SQLCIPHER_OK) {
81 LogPedantic("Data command prepared successfuly");
83 } else if (ret == SQLCIPHER_BUSY) {
84 LogPedantic("Collision occurred while preparing SQL command");
86 // Synchronize if synchronization object is available
87 if (connection->m_synchronizationObject) {
88 LogPedantic("Performing synchronization");
89 connection->m_synchronizationObject->Synchronize();
93 // No synchronization object defined. Fail.
97 const char *error = sqlcipher3_errmsg(m_masterConnection->m_connection);
99 LogPedantic("SQL prepare data command failed");
100 LogPedantic(" Statement: " << buffer);
101 LogPedantic(" Error: " << error);
103 ThrowMsg(Exception::SyntaxError, error);
106 LogPedantic("Prepared data command: " << buffer);
108 // Increment stored data command count
109 ++m_masterConnection->m_dataCommandsCount;
112 SqlConnection::DataCommand::~DataCommand()
114 LogPedantic("SQL data command finalizing");
116 if (sqlcipher3_finalize(m_stmt) != SQLCIPHER_OK) {
117 LogPedantic("Failed to finalize data command");
120 // Decrement stored data command count
121 --m_masterConnection->m_dataCommandsCount;
124 void SqlConnection::DataCommand::CheckBindResult(int result)
126 if (result != SQLCIPHER_OK) {
127 const char *error = sqlcipher3_errmsg(
128 m_masterConnection->m_connection);
130 LogPedantic("Failed to bind SQL statement parameter");
131 LogPedantic(" Error: " << error);
133 ThrowMsg(Exception::SyntaxError, error);
137 void SqlConnection::DataCommand::BindNull(
138 SqlConnection::ArgumentIndex position)
140 CheckBindResult(sqlcipher3_bind_null(m_stmt, position));
141 LogPedantic("SQL data command bind null: ["
145 void SqlConnection::DataCommand::BindInteger(
146 SqlConnection::ArgumentIndex position,
149 CheckBindResult(sqlcipher3_bind_int(m_stmt, position, value));
150 LogPedantic("SQL data command bind integer: ["
151 << position << "] -> " << value);
154 void SqlConnection::DataCommand::BindInt8(
155 SqlConnection::ArgumentIndex position,
158 CheckBindResult(sqlcipher3_bind_int(m_stmt, position,
159 static_cast<int>(value)));
160 LogPedantic("SQL data command bind int8: ["
161 << position << "] -> " << value);
164 void SqlConnection::DataCommand::BindInt16(
165 SqlConnection::ArgumentIndex position,
168 CheckBindResult(sqlcipher3_bind_int(m_stmt, position,
169 static_cast<int>(value)));
170 LogPedantic("SQL data command bind int16: ["
171 << position << "] -> " << value);
174 void SqlConnection::DataCommand::BindInt32(
175 SqlConnection::ArgumentIndex position,
178 CheckBindResult(sqlcipher3_bind_int(m_stmt, position,
179 static_cast<int>(value)));
180 LogPedantic("SQL data command bind int32: ["
181 << position << "] -> " << value);
184 void SqlConnection::DataCommand::BindInt64(
185 SqlConnection::ArgumentIndex position,
188 CheckBindResult(sqlcipher3_bind_int64(m_stmt, position,
189 static_cast<sqlcipher3_int64>(value)));
190 LogPedantic("SQL data command bind int64: ["
191 << position << "] -> " << value);
194 void SqlConnection::DataCommand::BindFloat(
195 SqlConnection::ArgumentIndex position,
198 CheckBindResult(sqlcipher3_bind_double(m_stmt, position,
199 static_cast<double>(value)));
200 LogPedantic("SQL data command bind float: ["
201 << position << "] -> " << value);
204 void SqlConnection::DataCommand::BindDouble(
205 SqlConnection::ArgumentIndex position,
208 CheckBindResult(sqlcipher3_bind_double(m_stmt, position, value));
209 LogPedantic("SQL data command bind double: ["
210 << position << "] -> " << value);
213 void SqlConnection::DataCommand::BindString(
214 SqlConnection::ArgumentIndex position,
222 // Assume that text may disappear
223 CheckBindResult(sqlcipher3_bind_text(m_stmt, position,
224 value, strlen(value),
225 SQLCIPHER_TRANSIENT));
227 LogPedantic("SQL data command bind string: ["
228 << position << "] -> " << value);
231 void SqlConnection::DataCommand::BindString(
232 SqlConnection::ArgumentIndex position,
235 BindString(position, ToUTF8String(value).c_str());
238 void SqlConnection::DataCommand::BindInteger(
239 SqlConnection::ArgumentIndex position,
240 const boost::optional<int> &value)
245 BindInteger(position, *value);
249 void SqlConnection::DataCommand::BindInt8(
250 SqlConnection::ArgumentIndex position,
251 const boost::optional<int8_t> &value)
256 BindInt8(position, *value);
260 void SqlConnection::DataCommand::BindInt16(
261 SqlConnection::ArgumentIndex position,
262 const boost::optional<int16_t> &value)
267 BindInt16(position, *value);
271 void SqlConnection::DataCommand::BindInt32(
272 SqlConnection::ArgumentIndex position,
273 const boost::optional<int32_t> &value)
278 BindInt32(position, *value);
282 void SqlConnection::DataCommand::BindInt64(
283 SqlConnection::ArgumentIndex position,
284 const boost::optional<int64_t> &value)
289 BindInt64(position, *value);
293 void SqlConnection::DataCommand::BindFloat(
294 SqlConnection::ArgumentIndex position,
295 const boost::optional<float> &value)
300 BindFloat(position, *value);
304 void SqlConnection::DataCommand::BindDouble(
305 SqlConnection::ArgumentIndex position,
306 const boost::optional<double> &value)
311 BindDouble(position, *value);
315 void SqlConnection::DataCommand::BindString(
316 SqlConnection::ArgumentIndex position,
317 const boost::optional<String> &value)
320 BindString(position, ToUTF8String(*value).c_str());
326 bool SqlConnection::DataCommand::Step()
328 // Notify all after potentially synchronized database connection access
329 ScopedNotifyAll notifyAll(
330 m_masterConnection->m_synchronizationObject.get());
333 int ret = sqlcipher3_step(m_stmt);
335 if (ret == SQLCIPHER_ROW) {
336 LogPedantic("SQL data command step ROW");
338 } else if (ret == SQLCIPHER_DONE) {
339 LogPedantic("SQL data command step DONE");
341 } else if (ret == SQLCIPHER_BUSY) {
342 LogPedantic("Collision occurred while executing SQL command");
344 // Synchronize if synchronization object is available
345 if (m_masterConnection->m_synchronizationObject) {
346 LogPedantic("Performing synchronization");
349 m_synchronizationObject->Synchronize();
354 // No synchronization object defined. Fail.
358 const char *error = sqlcipher3_errmsg(m_masterConnection->m_connection);
360 LogPedantic("SQL step data command failed");
361 LogPedantic(" Error: " << error);
363 ThrowMsg(Exception::InternalError, error);
367 void SqlConnection::DataCommand::Reset()
371 * http://www.sqllite.org/c3ref/stmt.html
373 * if last sqlcipher3_step command on this stmt returned an error,
374 * then sqlcipher3_reset will return that error, althought it is not an error.
375 * So sqlcipher3_reset allways succedes.
377 sqlcipher3_reset(m_stmt);
379 LogPedantic("SQL data command reset");
382 void SqlConnection::DataCommand::CheckColumnIndex(
383 SqlConnection::ColumnIndex column)
385 if (column < 0 || column >= sqlcipher3_column_count(m_stmt)) {
386 ThrowMsg(Exception::InvalidColumn, "Column index is out of bounds");
390 bool SqlConnection::DataCommand::IsColumnNull(
391 SqlConnection::ColumnIndex column)
393 LogPedantic("SQL data command get column type: [" << column << "]");
394 CheckColumnIndex(column);
395 return sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL;
398 int SqlConnection::DataCommand::GetColumnInteger(
399 SqlConnection::ColumnIndex column)
401 LogPedantic("SQL data command get column integer: [" << column << "]");
402 CheckColumnIndex(column);
403 int value = sqlcipher3_column_int(m_stmt, column);
404 LogPedantic(" Value: " << value);
408 int8_t SqlConnection::DataCommand::GetColumnInt8(
409 SqlConnection::ColumnIndex column)
411 LogPedantic("SQL data command get column int8: [" << column << "]");
412 CheckColumnIndex(column);
413 int8_t value = static_cast<int8_t>(sqlcipher3_column_int(m_stmt, column));
414 LogPedantic(" Value: " << value);
418 int16_t SqlConnection::DataCommand::GetColumnInt16(
419 SqlConnection::ColumnIndex column)
421 LogPedantic("SQL data command get column int16: [" << column << "]");
422 CheckColumnIndex(column);
423 int16_t value = static_cast<int16_t>(sqlcipher3_column_int(m_stmt, column));
424 LogPedantic(" Value: " << value);
428 int32_t SqlConnection::DataCommand::GetColumnInt32(
429 SqlConnection::ColumnIndex column)
431 LogPedantic("SQL data command get column int32: [" << column << "]");
432 CheckColumnIndex(column);
433 int32_t value = static_cast<int32_t>(sqlcipher3_column_int(m_stmt, column));
434 LogPedantic(" Value: " << value);
438 int64_t SqlConnection::DataCommand::GetColumnInt64(
439 SqlConnection::ColumnIndex column)
441 LogPedantic("SQL data command get column int64: [" << column << "]");
442 CheckColumnIndex(column);
443 int64_t value = static_cast<int64_t>(sqlcipher3_column_int64(m_stmt, column));
444 LogPedantic(" Value: " << value);
448 float SqlConnection::DataCommand::GetColumnFloat(
449 SqlConnection::ColumnIndex column)
451 LogPedantic("SQL data command get column float: [" << column << "]");
452 CheckColumnIndex(column);
453 float value = static_cast<float>(sqlcipher3_column_double(m_stmt, column));
454 LogPedantic(" Value: " << value);
458 double SqlConnection::DataCommand::GetColumnDouble(
459 SqlConnection::ColumnIndex column)
461 LogPedantic("SQL data command get column double: [" << column << "]");
462 CheckColumnIndex(column);
463 double value = sqlcipher3_column_double(m_stmt, column);
464 LogPedantic(" Value: " << value);
468 std::string SqlConnection::DataCommand::GetColumnString(
469 SqlConnection::ColumnIndex column)
471 LogPedantic("SQL data command get column string: [" << column << "]");
472 CheckColumnIndex(column);
474 const char *value = reinterpret_cast<const char *>(
475 sqlcipher3_column_text(m_stmt, column));
477 LogPedantic("Value: " << (value ? value : "NULL"));
480 return std::string();
483 return std::string(value);
486 boost::optional<int> SqlConnection::DataCommand::GetColumnOptionalInteger(
487 SqlConnection::ColumnIndex column)
489 LogPedantic("SQL data command get column optional integer: ["
491 CheckColumnIndex(column);
492 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
493 return boost::optional<int>();
495 int value = sqlcipher3_column_int(m_stmt, column);
496 LogPedantic(" Value: " << value);
497 return boost::optional<int>(value);
500 boost::optional<int8_t> SqlConnection::DataCommand::GetColumnOptionalInt8(
501 SqlConnection::ColumnIndex column)
503 LogPedantic("SQL data command get column optional int8: ["
505 CheckColumnIndex(column);
506 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
507 return boost::optional<int8_t>();
509 int8_t value = static_cast<int8_t>(sqlcipher3_column_int(m_stmt, column));
510 LogPedantic(" Value: " << value);
511 return boost::optional<int8_t>(value);
514 boost::optional<int16_t> SqlConnection::DataCommand::GetColumnOptionalInt16(
515 SqlConnection::ColumnIndex column)
517 LogPedantic("SQL data command get column optional int16: ["
519 CheckColumnIndex(column);
520 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
521 return boost::optional<int16_t>();
523 int16_t value = static_cast<int16_t>(sqlcipher3_column_int(m_stmt, column));
524 LogPedantic(" Value: " << value);
525 return boost::optional<int16_t>(value);
528 boost::optional<int32_t> SqlConnection::DataCommand::GetColumnOptionalInt32(
529 SqlConnection::ColumnIndex column)
531 LogPedantic("SQL data command get column optional int32: ["
533 CheckColumnIndex(column);
534 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
535 return boost::optional<int32_t>();
537 int32_t value = static_cast<int32_t>(sqlcipher3_column_int(m_stmt, column));
538 LogPedantic(" Value: " << value);
539 return boost::optional<int32_t>(value);
542 boost::optional<int64_t> SqlConnection::DataCommand::GetColumnOptionalInt64(
543 SqlConnection::ColumnIndex column)
545 LogPedantic("SQL data command get column optional int64: ["
547 CheckColumnIndex(column);
548 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
549 return boost::optional<int64_t>();
551 int64_t value = static_cast<int64_t>(sqlcipher3_column_int64(m_stmt, column));
552 LogPedantic(" Value: " << value);
553 return boost::optional<int64_t>(value);
556 boost::optional<float> SqlConnection::DataCommand::GetColumnOptionalFloat(
557 SqlConnection::ColumnIndex column)
559 LogPedantic("SQL data command get column optional float: ["
561 CheckColumnIndex(column);
562 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
563 return boost::optional<float>();
565 float value = static_cast<float>(sqlcipher3_column_double(m_stmt, column));
566 LogPedantic(" Value: " << value);
567 return boost::optional<float>(value);
570 boost::optional<double> SqlConnection::DataCommand::GetColumnOptionalDouble(
571 SqlConnection::ColumnIndex column)
573 LogPedantic("SQL data command get column optional double: ["
575 CheckColumnIndex(column);
576 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
577 return boost::optional<double>();
579 double value = sqlcipher3_column_double(m_stmt, column);
580 LogPedantic(" Value: " << value);
581 return boost::optional<double>(value);
584 boost::optional<String> SqlConnection::DataCommand::GetColumnOptionalString(
585 SqlConnection::ColumnIndex column)
587 LogPedantic("SQL data command get column optional string: ["
589 CheckColumnIndex(column);
590 if (sqlcipher3_column_type(m_stmt, column) == SQLCIPHER_NULL) {
591 return boost::optional<String>();
593 const char *value = reinterpret_cast<const char *>(
594 sqlcipher3_column_text(m_stmt, column));
595 LogPedantic("Value: " << value);
596 String s = FromUTF8String(value);
597 return boost::optional<String>(s);
600 void SqlConnection::Connect(const std::string &address,
603 if (m_connection != NULL) {
604 LogPedantic("Already connected.");
607 LogPedantic("Connecting to DB: " << address << "...");
609 // Connect to database
611 result = sqlcipher3_open_v2(
617 if (result == SQLCIPHER_OK) {
618 LogPedantic("Connected to DB");
620 LogPedantic("Failed to connect to DB!");
621 ThrowMsg(Exception::ConnectionBroken, address);
624 // Enable foreign keys
628 static std::string rawToHexString(const std::vector<unsigned char> &raw) {
629 std::string str(raw.size() * 2, '0');
630 for (std::size_t i = 0; i < raw.size(); i++)
631 sprintf(&str[i*2], "%02X", raw[i]);
635 void SqlConnection::SetKey(const std::vector<unsigned char> &rawPass){
636 if (m_connection == NULL) {
637 LogPedantic("Cannot set key. No connection to DB!");
640 std::string pass = "x'" + rawToHexString(rawPass) + "'";
641 int result = sqlcipher3_key(m_connection, pass.c_str(), pass.length());
642 if (result == SQLCIPHER_OK) {
643 LogPedantic("Set key on DB");
645 //sqlcipher3_key fails only when m_connection == NULL || key == NULL ||
647 LogPedantic("Failed to set key on DB");
648 ThrowMsg(Exception::InvalidArguments, result);
654 void SqlConnection::ResetKey(const std::vector<unsigned char> &rawPassOld,
655 const std::vector<unsigned char> &rawPassNew) {
656 if (m_connection == NULL) {
657 LogPedantic("Cannot reset key. No connection to DB!");
660 // sqlcipher3_rekey requires for key to be already set
664 std::string pass = "x'" + rawToHexString(rawPassNew) + "'";
665 int result = sqlcipher3_rekey(m_connection, pass.c_str(), pass.length());
666 if (result == SQLCIPHER_OK) {
667 LogPedantic("Reset key on DB");
669 //sqlcipher3_rekey fails only when m_connection == NULL || key == NULL ||
671 LogPedantic("Failed to reset key on DB");
672 ThrowMsg(Exception::InvalidArguments, result);
674 //Wiping out the key (required)
675 pass.assign(pass.length(), '0');
676 for(std::size_t i = 0; i < pass.length(); i++) {
678 LogPedantic("Wiping key failed.");
679 ThrowMsg(Exception::InternalError, "Wiping key failed");
684 void SqlConnection::Disconnect()
686 if (m_connection == NULL) {
687 LogPedantic("Already disconnected.");
691 LogPedantic("Disconnecting from DB...");
693 // All stored data commands must be deleted before disconnect
694 AssertMsg(m_dataCommandsCount == 0,
695 "All stored procedures must be deleted"
696 " before disconnecting SqlConnection");
700 result = sqlcipher3_close(m_connection);
702 if (result != SQLCIPHER_OK) {
703 const char *error = sqlcipher3_errmsg(m_connection);
704 LogPedantic("SQL close failed");
705 LogPedantic(" Error: " << error);
706 Throw(Exception::InternalError);
711 LogPedantic("Disconnected from DB");
714 bool SqlConnection::CheckTableExist(const char *tableName)
716 if (m_connection == NULL) {
717 LogPedantic("Cannot execute command. Not connected to DB!");
721 DataCommandAutoPtr command =
722 PrepareDataCommand("select tbl_name from sqlcipher_master where name=?;");
724 command->BindString(1, tableName);
726 if (!command->Step()) {
727 LogPedantic("No matching records in table");
731 return command->GetColumnString(0) == tableName;
734 SqlConnection::SqlConnection(const std::string &address,
736 SynchronizationObject *synchronizationObject) :
738 m_dataCommandsCount(0),
739 m_synchronizationObject(synchronizationObject),
742 LogPedantic("Opening database connection to: " << address);
745 SqlConnection::Connect(address, option);
747 if (!m_synchronizationObject) {
748 LogPedantic("No synchronization object defined");
752 SqlConnection::~SqlConnection()
754 LogPedantic("Closing database connection");
756 // Disconnect from DB
759 SqlConnection::Disconnect();
761 Catch(Exception::Base)
763 LogPedantic("Failed to disconnect from database");
767 void SqlConnection::ExecCommand(const char *format, ...)
769 if (m_connection == NULL) {
770 LogPedantic("Cannot execute command. Not connected to DB!");
774 if (format == NULL) {
775 LogPedantic("Null query!");
776 ThrowMsg(Exception::SyntaxError, "Null statement");
782 va_start(args, format);
784 if (vasprintf(&rawBuffer, format, args) == -1) {
790 CharUniquePtr buffer(rawBuffer);
793 LogPedantic("Failed to allocate statement string");
797 LogPedantic("Executing SQL command: " << buffer.get());
799 // Notify all after potentially synchronized database connection access
800 ScopedNotifyAll notifyAll(m_synchronizationObject.get());
805 int ret = sqlcipher3_exec(m_connection,
811 std::string errorMsg;
813 // Take allocated error buffer
814 if (errorBuffer != NULL) {
815 errorMsg = errorBuffer;
816 sqlcipher3_free(errorBuffer);
819 if (ret == SQLCIPHER_OK) {
823 if (ret == SQLCIPHER_BUSY) {
824 LogPedantic("Collision occurred while executing SQL command");
826 // Synchronize if synchronization object is available
827 if (m_synchronizationObject) {
828 LogPedantic("Performing synchronization");
829 m_synchronizationObject->Synchronize();
833 // No synchronization object defined. Fail.
837 LogPedantic("Failed to execute SQL command. Error: " << errorMsg);
838 ThrowMsg(Exception::SyntaxError, errorMsg);
842 SqlConnection::DataCommandAutoPtr SqlConnection::PrepareDataCommand(
846 if (m_connection == NULL) {
847 LogPedantic("Cannot execute data command. Not connected to DB!");
848 return DataCommandAutoPtr();
854 va_start(args, format);
856 if (vasprintf(&rawBuffer, format, args) == -1) {
862 CharUniquePtr buffer(rawBuffer);
865 LogPedantic("Failed to allocate statement string");
866 return DataCommandAutoPtr();
869 LogPedantic("Executing SQL data command: " << buffer.get());
871 return DataCommandAutoPtr(new DataCommand(this, buffer.get()));
874 SqlConnection::RowID SqlConnection::GetLastInsertRowID() const
876 return static_cast<RowID>(sqlcipher3_last_insert_rowid(m_connection));
879 void SqlConnection::TurnOnForeignKeys()
881 ExecCommand("PRAGMA foreign_keys = ON;");
884 void SqlConnection::BeginTransaction()
886 ExecCommand("BEGIN;");
889 void SqlConnection::RollbackTransaction()
891 ExecCommand("ROLLBACK;");
894 void SqlConnection::CommitTransaction()
896 ExecCommand("COMMIT;");
899 SqlConnection::SynchronizationObject *
900 SqlConnection::AllocDefaultSynchronizationObject()
902 return new NaiveSynchronizationObject();
907 #pragma GCC diagnostic pop