oatpp / oatpp-sqlite Goto Github PK

View Code? Open in Web Editor NEW

18.0 3.0 18.0 2.24 MB

SQLite adapter for oatpp ORM.

Home Page: https://oatpp.io/

License: Apache License 2.0

CMake 0.17% C++ 1.54% C 98.30%

oatpp sqlite sqlite3 orm cpp

oatpp-sqlite's Introduction

oatpp-sqlite

SQLite adapter for Oat++ ORM.

More about Oat++:

Build And Install

Pre Requirements

Install the main oatpp module.
Install SQLite.
Note: You can also use -DOATPP_SQLITE_AMALGAMATION=ON to install oatpp-sqlite together with SQLite amalgamation in which case you don't need to install SQLite

Install module

Clone this repository.

In the root of the repository run:

mkdir build && cd build
cmake ..
make install

API

Detailed documentation on Oat++ ORM you can find here.

Connect to Database

All you need to start using oatpp ORM with SQLite is to create oatpp::sqlite::Executor and provide it to your DbClient.

#include "db/MyClient.hpp"
#include "oatpp-sqlite/orm.hpp"

class AppComponent {
public:
  
  /**
   * Create DbClient component.
   * SQLite is used as an example here. For other databases declaration is similar.
   */
  OATPP_CREATE_COMPONENT(std::shared_ptr<db::MyClient>, myDatabaseClient)([] {
    /* Create database-specific ConnectionProvider */
    auto connectionProvider = std::make_shared<oatpp::sqlite::ConnectionProvider>("/path/to/database.sqlite");    
  
    /* Create database-specific ConnectionPool */
    auto connectionPool = oatpp::sqlite::ConnectionPool::createShared(connectionProvider, 
                                                                      10 /* max-connections */, 
                                                                      std::chrono::seconds(5) /* connection TTL */);
    
    /* Create database-specific Executor */
    auto executor = std::make_shared<oatpp::sqlite::Executor>(connectionPool);
  
    /* Create MyClient database client */
    return std::make_shared<MyClient>(executor);
  }());

};

License

Apache License 2.0 applies to all files of this module except SQLite amalgamation.
SQLite License applies to SQLite amalgamation files:
- /src/sqlite/sqlite3.c
- /src/sqlite/sqlite3.h

oatpp-sqlite's People

Contributors

Stargazers

Watchers

Forkers

kaungzawhtet logosdesignas sunofxv sidaf hongpq 519984307 thiminhnhut cppframework hoboatpp huangxin96 turnarond stellarex andre-hjr pedrobbastos 9bridges wcy168 linhaojun857

oatpp-sqlite's Issues

Build Failed with oat++ v1.3.0

Similar switch away from StrBuffer to its std::string representation leads to compile errors like

oatpp-sqlite/src/oatpp-sqlite/mapping/type/Blob.hpp:40:57: error: ‘StrBuffer’ is not a member of ‘oatpp::base’
   40 | typedef oatpp::data::mapping::type::ObjectWrapper<base::StrBuffer, __class::Blob> Blob;

This needs to be adjusted to match the API.

[TODO] - reuse prepared statements

Currently, all statements are created each time the Executor::execute() method is called - https://github.com/oatpp/oatpp-sqlite/blob/master/src/oatpp-sqlite/Executor.cpp#L121

We should keep prepared statements per connection and reuse them.

build fail at UWP

microsoft/vcpkg#14107

Link external SQLite library by default

How to insert string literals?

I'm trying to insert file paths, and sqlite is erroring out on the embedded '/' characters. Normally I'd just surround the string with single quotes, but it looks like your template handler doesn't like that.

i.e.

QUERY(createCapture,
      "INSERT INTO captures VALUES ("
      ":capture.id, "
      ":capture.timeStamp,"
      "':capture.sampleName',"
      "':capture.previewName',"
      "':capture.imageName',"
      "':capture.depthName',"
      "':capture.videoName',"
      "':capture.note',"
      ":capture.floor,"
      ":capture.pin,"
      ":capture:updateTimeStamp"
      ");",
      PARAM(oatpp::Object<CaptureDto>, capture))

None of the parameters surrounded with single quotes get expanded. If I put a debug log in oatpp::sqlite::Executor::execute it gets expanded to:

INSERT INTO captures VALUES (?,':capture.sampleName',':capture.previewName',':capture.imageName',':capture.depthName',':capture.videoName',':capture.note',?,?,??);

How to implement aggregate queries?

How best to implement aggregate function queries? I have it working, but it's pretty kludgy:
In my DbClient class:

    QUERY(getCaptureInfoCount,"SELECT COUNT(*) FROM captures;")

In my service class:

    auto dto = oatpp::Object<CaptureInfoDto>::createShared();
    auto dbCountResult = m_database->getCaptureInfoCount();
    auto countResult = dbCountResult->fetch<oatpp::Vector<oatpp::Fields<oatpp::Any>>>();
    dto->captureCount = countResult->at(0).getValueByKey("COUNT(*)").retrieve<oatpp::Int64>();

There must be a more elegant way of doing this?

Full text search support

Can I use fts with oatpp-sqlite? I tried but I failed:
[oatpp::sqlite::Executor::migrateSchema()]:Error. Migration failed for version 1. no such module: fts5
Getting similar error when trying to use fts4.

database is locked

how fix database is locked when run ASYNC?

Error cmake build

SQLite3 has been installed

[oatpp::sqlite::Executor::getConnection()]: Error. Can't connect

I am building a project with the given CMakeLists.txt with conan:

cmake_minimum_required(VERSION 3.1)
set(PROJECT_NAME my-project)
project(${PROJECT_NAME})

set(CMAKE_CXX_STANDARD 14)

############################### CONAN SETUP BEGIN ##########################################
if(NOT EXISTS "${CMAKE_BINARY_DIR}/conan.cmake")
    message(STATUS "Downloading conan.cmake from https://github.com/conan-io/cmake-conan")
    file(DOWNLOAD "https://raw.githubusercontent.com/conan-io/cmake-conan/v0.9/conan.cmake"
            "${CMAKE_BINARY_DIR}/conan.cmake")
endif()
include(${CMAKE_BINARY_DIR}/conan.cmake)
conan_cmake_run(CONANFILE conanfile.txt BASIC_SETUP)
############################### CONAN SETUP END ############################################

add_library(${PROJECT_NAME}-lib
        # controllers
        src/controller/StaticController.hpp
        src/controller/DeviceController.hpp

        # db
        src/db/DeviceDb.hpp

        # dto
        src/dto/PageDto.hpp
        src/dto/StatusDto.hpp
        src/dto/DeviceDto.hpp

        # services
        src/service/DeviceService.cpp
        src/service/DeviceService.hpp

        # base
        src/AppComponent.hpp
        src/DatabaseComponent.hpp
        src/ErrorHandler.cpp
        src/ErrorHandler.hpp)

## include directories
target_include_directories(${PROJECT_NAME}-lib PUBLIC src)


add_definitions(
        ## SQLite database file
        -DDATABASE_FILE="${CMAKE_CURRENT_SOURCE_DIR}/sql/db.sqlite"

        ## SQLite database test file
        -DTESTDATABASE_FILE="${CMAKE_CURRENT_SOURCE_DIR}/sql/test-db.sqlite"

        ## Path to database migration scripts
        -DDATABASE_MIGRATIONS="${CMAKE_CURRENT_SOURCE_DIR}/sql"

        ##  Server
        -DHOST="0.0.0.0"
        -DPORT=8080
)

if(CMAKE_SYSTEM_NAME MATCHES Linux)
    find_package(Threads REQUIRED)
    target_link_libraries(${PROJECT_NAME}-lib INTERFACE Threads::Threads ${CMAKE_DL_LIBS})
endif()

## add executables
add_executable(${PROJECT_NAME}-exe src/App.cpp)
target_link_libraries(${PROJECT_NAME}-exe ${PROJECT_NAME}-lib ${CONAN_LIBS})

add_executable(${PROJECT_NAME}-test
        test/tests.cpp
        test/app/TestClient.hpp
        test/app/TestDatabaseComponent.hpp
        test/app/TestComponent.hpp
        test/DeviceControllerTest.hpp
        test/DeviceControllerTest.cpp)

target_link_libraries(${PROJECT_NAME}-test ${PROJECT_NAME}-lib ${CONAN_LIBS})

enable_testing()
add_test(e2e-tests ${PROJECT_NAME}-test)

Using this yields me a working executable on my machine running ubuntu 20.04. But when I move this executable to a LXD container running ubuntu 20.04, I get the following error:

ubuntu@container:~$ ./my-project-exe 
terminate called after throwing an instance of 'std::runtime_error'
  what():  [oatpp::sqlite::Executor::getConnection()]: Error. Can't connect.
Aborted (core dumped)

ubuntu@container:~$ sudo ./my-project-exe 
terminate called after throwing an instance of 'std::runtime_error'
  what():  [oatpp::sqlite::Executor::getConnection()]: Error. Can't connect.
Aborted (core dumped)

Looks like the database file db.sqlite fails to create. How can I eliminate this error?

query param use array: SELECT...in ( array)

How to do it:

SELECT * FROM users WHERE username in (:usernames);

:usernames is array