SQLite3++ - C++ wrapper of SQLite3 API

Related tags

Database sqlite3pp
Overview

ANNOUNCEMENTS

Use files in headeronly_src directory. The files in src are exactly same but in the form of h/cpp files, which you need to compile and link with.
boost_src is no longer maintained. Do not use unless you need to use pre-c++1x. It requires boost library.

sqlite3pp

This library makes SQLite3 API more friendly to C++ users. It supports almost all of SQLite3 features using C++ classes such as database, command, query, and transaction. The query class supports iterator concept for fetching records.

With ext::function class, it's also easy to use the sqlite3's functions and aggregations in C++.

Usage

database

sqlite3pp::database db("test.db");
db.execute("INSERT INTO contacts (name, phone) VALUES ('Mike', '555-1234')");

command

sqlite3pp::command cmd(
  db, "INSERT INTO contacts (name, phone) VALUES (?, ?)");
cmd.binder() << "Mike" << "555-1234";
cmd.execute();
sqlite3pp::command cmd(db, "INSERT INTO contacts (name, phone) VALUES (?, ?)");
cmd.bind(1, "Mike", sqlite3pp::nocopy);
cmd.bind(2, "555-1234", sqlite3pp::nocopy);
cmd.execute();
sqlite3pp::command cmd(
  db, "INSERT INTO contacts (name, phone) VALUES (?100, ?101)");
cmd.bind(100, "Mike", sqlite3pp::nocopy);
cmd.bind(101, "555-1234", sqlite3pp::nocopy);
cmd.execute();
sqlite3pp::command cmd(
  db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
cmd.bind(":user", "Mike", sqlite3pp::nocopy);
cmd.bind(":phone", "555-1234", sqlite3pp::nocopy);
cmd.execute();
sqlite3pp::command cmd(
  db,
  "INSERT INTO contacts (name, phone) VALUES (:user, '555-0000');"
  "INSERT INTO contacts (name, phone) VALUES (:user, '555-1111');"
  "INSERT INTO contacts (name, phone) VALUES (:user, '555-2222')");
cmd.bind(":user", "Mike", sqlite3pp::nocopy);
cmd.execute_all();

transaction

sqlite3pp::transaction xct(db);
{
  sqlite3pp::command cmd(
    db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
  cmd.bind(":user", "Mike", sqlite3pp::nocopy);
  cmd.bind(":phone", "555-1234", sqlite3pp::nocopy);
  cmd.execute();
}
xct.rollback();

query

sqlite3pp::query qry(db, "SELECT id, name, phone FROM contacts");

for (int i = 0; i < qry.column_count(); ++i) {
  cout << qry.column_name(i) << "\t";
}
for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
  for (int j = 0; j < qry.column_count(); ++j) {
    cout << (*i).get<char const*>(j) << "\t";
  }
  cout << endl;
}
for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
  int id;
  char const* name, *phone;
  std::tie(id, name, phone) =
    (*i).get_columns<int, char const*, char const*>(0, 1, 2);
  cout << id << "\t" << name << "\t" << phone << endl;
}
for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
  string name, phone;
  (*i).getter() >> sqlite3pp::ignore >> name >> phone;
  cout << "\t" << name << "\t" << phone << endl;
}
for (auto v : qry) {
  string name, phone;
  v.getter() >> sqlite3pp::ignore >> name >> phone;
  cout << "\t" << name << "\t" << phone << endl;
}

attach

sqlite3pp::database db("foods.db");
db.attach("test.db", "test");

sqlite3pp::query qry(
  db,
  "SELECT epi.* FROM episodes epi, test.contacts con WHERE epi.id = con.id");

backup

sqlite3pp::database db("test.db");
sqlite3pp::database backupdb("backup.db");

db.backup(backupdb);
db.backup(
  backupdb,
  [](int pagecount, int remaining, int rc) {
    cout << pagecount << "/" << remaining << endl;
    if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED) {
      // sleep or do nothing.
    }
  });

callback

struct rollback_handler
{
  void operator()() {
    cout << "handle_rollback" << endl;
  }
};

sqlite3pp::database db("test.db");

db.set_commit_handler([]{ cout << "handle_commit\n"; return 0; });
db.set_rollback_handler(rollback_handler());
int handle_authorize(int evcode, char const* p1, char const* p2,
                     char const* dbname, char const* tvname) {
  cout << "handle_authorize(" << evcode << ")" << endl;
  return 0;
}

db.set_authorize_handler(&handle_authorize);
struct handler
{
  handler() : cnt_(0) {}

  void handle_update(int opcode, char const* dbname,
                     char const* tablename, int64_t rowid) {
    cout << "handle_update(" << opcode << ", " << dbname << ", "
         << tablename << ", " << rowid << ") - " << cnt_++ << endl;
  }
  int cnt_;
};

using namespace std::placeholders;

db.set_update_handler(std::bind(&handler::handle_update, &h, _1, _2, _3, _4));

function

int test0()
{
  return 100;
}

sqlite3pp::database db("test.db");
sqlite3pp::ext::function func(db);

func.create<int ()>("test0", &test0);
void test1(sqlite3pp::ext::context& ctx)
{
  ctx.result(200);
}

void test2(sqlite3pp::ext::context& ctx)
{
  string args = ctx.get<string>(0);
  ctx.result(args);
}

void test3(sqlite3pp::ext::context& ctx)
{
  ctx.result_copy(0);
}

func.create("test1", &test1);
func.create("test2", &test2, 1);
func.create("test3", &test3, 1);
func.create<int ()>("test4", []{ return 500; });
string test5(string const& value)
{
  return value;
}

string test6(string const& s1, string const& s2, string const& s3)
{
  return s1 + s2 + s3;
}

func.create<int (int)>("test5", [](int i){ return i + 10000; });
func.create<string (string, string, string)>("test6", &test6);
sqlite3pp::query qry(
  db,
  "SELECT test0(), test1(), test2('x'), test3('y'), test4(), test5(10), "
  "test6('a', 'b', 'c')");

aggregate

void step(sqlite3pp::ext::context& c)
{
  int* sum = (int*) c.aggregate_data(sizeof(int));

  *sum += c.get<int>(0);
}
void finalize(sqlite3pp::ext::context& c)
{
  int* sum = (int*) c.aggregate_data(sizeof(int));
  c.result(*sum);
}

sqlite3pp::database db("foods.db");
sqlite3pp::ext::aggregate aggr(db);

aggr.create("aggr0", &step, &finalize);
struct mycnt
{
  void step() {
    ++n_;
  }
  int finish() {
    return n_;
  }
  int n_;
};

aggr.create<mycnt>("aggr1");
struct strcnt
{
  void step(string const& s) {
    s_ += s;
  }
  int finish() {
    return s_.size();
  }
  string s_;
};

struct plussum
{
  void step(int n1, int n2) {
    n_ += n1 + n2;
  }
  int finish() {
    return n_;
  }
  int n_;
};

aggr.create<strcnt, string>("aggr2");
aggr.create<plussum, int, int>("aggr3");
sqlite3pp::query qry(
  db,
  "SELECT aggr0(id), aggr1(type_id), aggr2(name), aggr3(id, type_id) "
  "FROM foods");

loadable extension

#define SQLITE3PP_LOADABLE_EXTENSION
#include <sqlite3ppext.h>

int sqlite3_extension_init(
  sqlite3 *pdb,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  sqlite3pp:database db(sqlite3pp::ext::borrow(pdb));
  // pdb is not closed since db just borrows it.
}

See also

Comments
  • sqlite3pp as header-only library

    sqlite3pp as header-only library

    The code is mostly forwarding to sqlite3 functions directly, and no build system is provided as sqlite3pp is meant to be included directly into the project tree (as far as I understand it). I think this makes it a perfect candidate for being a header-only library.

    I would imagine it needs only a few changes:

    • Declare all exportable symbols (probably just class methods) as inline.
    • Rename .cpp files to .ipp and include them from the .h
    • Put all of that into a folder include/sqlite3pp, so parent projects can add [sqlite3pp-repo]/include to their include directories.
    opened by jpetso 11
  • statement doesn't have a bind(int, std::string)

    statement doesn't have a bind(int, std::string)

    Hi, what is the reasoning behind that missing bind function? I would do:

    int statement::bind(int idx, const std::string& value)
    {
      return sqlite3_bind_blob(stmt_, idx, value.c_str(), value.size(),  SQLITE_TRANSIENT);
    }
    
    opened by ReallyNiceGuy 8
  •  access to the sqlite3* pointer inside class database

    access to the sqlite3* pointer inside class database

    There are many sqlite3 features that are not supported by sqlite3pp::database. As a workaround, it would be nice if user can use the internal sqlite3* pointer.

    Some useful features that are not supported;

    1. sqlite3_changes(): tell how many rows are changed.
    2. sqlite3_extended_errcode(): can be used to route errors based on more detailed classification.
    opened by mutantdeveloper 6
  • Eliminate conversion error

    Eliminate conversion error

    sqlite3pp was converting a std::size_t to an int, this generates a warning when warnings are turned all the way up on 64-bit platforms. It can also be considered a little dangerous to do this conversion without ensuring the value will fit in an 'int'.

    To be a little more correct/robust, I added a function details::trim_strlen that ensures the value will fit into an int or it truncates the value to fit. Using this function eliminates the warning on 64-bit platforms.

    opened by ohz10 6
  • library without boost

    library without boost

    Hi I love your library - just what I needed,... but I can't use boost. I have a large project that does not use boost and I can't add it now just for this. I think a lot of what you use boost for is now part of the standard c++ library, like std::tuple, std::function, and most compilers are now C++11. I really think you should try to get rid of boost. I tried to give it a shot, but got stuck on the iterator part. I will try to see if the boost headers are 'header only' - maybe I can include them in my library that way. Any thoughts on this?

    Best Regards, Peter

    opened by peterritter 6
  • added the possibility to assign an existent database handler

    added the possibility to assign an existent database handler

    this is required by the loadext doc: see there When we load an extension, the database handler is already there before we can call any sqlite3pp::database constructor, hence the assignement overload with sqlite3*.

    There is still some boilerplate to manage to make the extension loadable:

    sqlite3pp::database dbe;
    
    extern "C" {
    int sqlite3_extension_init(/* <== Change this name, maybe */
                               sqlite3 *db,
                               char **pzErrMsg,
                               const sqlite3_api_routines *pApi)
    {
        int rc = SQLITE_OK;
        SQLITE_EXTENSION_INIT2(pApi);
        dbe = db;
        sqlite3pp::ext::aggregate aggr(dbe);
        aggr.create<strcnt, string>("aggr2");
        aggr.create<plussum, int, int>("aggr3");
        return rc;
    }
    }
    
    opened by fradav 4
  • SQLite Online Backup API

    SQLite Online Backup API

    I had to use SQLiteCpp as it provides a wrapper for sqlite3_backup_init, but I'd prefer to use sqlite3pp!

    https://github.com/SRombauts/SQLiteCpp/blob/master/include/SQLiteCpp/Backup.h

    opened by calumr 4
  • usability features

    usability features

    Hello Wongoo,

    Thank you very much for the excellent sqlite3pp library. We've been using it for 3 years now, and it has worked great. Today, I went looking for a sqlite wrapper that did not require boost, and I discovered that you've already removed the boost dependency!

    As I merged your update into our code today, I noticed quite a few little things that we've tweaked in the last 3 years. I don't know if you'll want any of our tweaks, and it's ok if you don't, but in the spirit of open source software, I thought I should at least share them with you.

    If you have any questions or notice something broken, please let me know.

    Thanks again, Ryan

    opened by ryanjmulder 4
  • Can not build with Visual Studio 2013

    Can not build with Visual Studio 2013

    Using a fresh clone of the source code, I see two errors when attempting to build with Visual Studio 2013.

    sqlite3pp.cpp Line 49 Error C2146: syntax error : missing ';' before identifier 'noncopyable'

    sqlite3pp.cpp Line 49 Error C4430: missing type specifier - int assumed. Note: C++ does not support default-int

    Test code looks like this:

    #include "sqlite3pp.h"
    
    class MyApp {
    public:
        sqlite3pp::database db;
        void setup();    
    }
    
    void MyApp::setup() {
        db.connect("test.sqlite");
    }
    
    opened by jeremyfromearth 4
  • Update sqlite3pp.ipp

    Update sqlite3pp.ipp

    Get rid of 64bit warnings under Visual Studio 2015 update 1. There was a more complicated proposal to fix the same issue in the past, which was rejected. Please let me know if this idea is more in line with the library spirit.

    opened by andreygorbachev 3
  • query::begin() is a misnomer

    query::begin() is a misnomer

    My coworker just spent a few hours trying to find out why he was getting a crash from this innocuous block of code:

    sqlite3pp::query qry(...);
    // not listed: initializing the statement, binding variables
    
    if (qry.begin() != qry.end()) {
        sqlite3pp::query::iterator it = qry.begin();
        do_stuff((*it).get<std::string>(0)); // this crashes despite being declared as TEXT NOT NULL
    }
    

    As the author who has dealt with SQLite extensively, you might quickly be able to tell what's going on there:

    • query::begin() is implemented as "create query_iterator, which calls sqlite3_step() in its constructor".
    • If query::begin() is called twice, this will just call sqlite3_step() a second time, which means it is at an invalid row now because the result only had a single row.

    If I write the same code using sqlite_*() functions, I won't make this error. However, by using begin() as an alias for step(), you implement a well-defined C++ concept with unexpected (one might say wrong) behavior. Popular libraries teach us that we can have as many iterators as we want, they're all independent from each other as long as the underlying structure isn't changed, and begin() always gives you the first element.

    In the current code, this behavior is more similar to Java's next()/hasNext() than C++'s begin()/end(). It appears SQLite doesn't really allow you to have multiple iterators/cursors for the same prepared statement, so the best course of action would be to rename begin() so people don't expect the wrong things from it.

    My suggestion would be to rename it to execute() (the same name that command is using as well) but unlike command, let it continue returning the iterator. The query_iterator constructor would call sqlite3_reset() before calling sqlite3_step(). Everything else from then on would stay the same, end() does match the established C++ semantics so I think it's okay to leave it with its current name. The resulting code would then look like this:

    sqlite3pp::query::iterator it = qry.execute();
    if (it != qry.end()) {
        do_stuff((*it).get<std::string>(0)); // this crashes despite being declared as TEXT NOT NULL
    }
    

    and the code from the current README.md example also still makes sense:

    for (sqlite3pp::query::iterator i = qry.execute(); i != qry.end(); ++i) {
      for (int j = 0; j < qry.column_count(); ++j) {
        cout << (*i).get<char const*>(j) << "\t";
      }
      cout << endl;
    }
    

    In addition, my coworker's original code would clearly indicate what it's doing:

    if (qry.execute() != qry.end()) { // first execution of the query
        sqlite3pp::query::iterator it = qry.execute(); // second execution of the query
        // reset() doesn't clear bindings, so the second execution still behaves as expected.
        do_stuff((*it).get<std::string>(0)); // doesn't crash, only needlessly inefficient
    }
    

    Because it's clear that it's doing the wrong thing, it would probably never get written this way - but even in this form, it behaves as the developer would expect it to behave.

    Either way, begin() deserves to be deprecated or removed. Thank you for your time and patience :)

    opened by jpetso 3
  • sqlitepp::database construct  error with  memory leak?

    sqlitepp::database construct error with memory leak?

    I had a question with the constructor of sqlite3pp::database obeject. The sqlite_open_v2() function is called in by function sqlite3pp::connect() in file sqlite3pp.ipp. It means also called by sqlite3pp::database constructor function.

      database::database(char const* dbname, int flags, char const* vfs) : db_(nullptr), borrowing_(false)
      {
        if (dbname) {
          auto rc = connect(dbname, flags, vfs);                      ///< sqlite3_open_v2() is called here
          if (rc != SQLITE_OK)                                                   
            throw database_error("can't connect database");   ///< if exception throws, sqlite3_close() will not called
        }
      }
    

    But if there is error occured, like a illegal path, the constructor function will throw exception. And sqlite3_close() function will not called, which was contrary to sqlite3 docs(https://www.sqlite.org/capi3ref.html#sqlite3_open). Sqlite3 docs declares: Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

    In fact , I can catch exception and csalled sqlite3_close() by myself. But I need to get raw sqlite3 obeject managerd by sqlite3pp. I don't know if it was designed, or there is better way to solve this question? Thanks for help!

    By the way, I tried use illegal path to make exceptions while constructing. It did have some memory leak, perhaps 70-100KB each 100 times if I did not called sqlite3_close(). The enviroment is win10, vs2015.

    opened by snoopyIV 0
  • License

    License

    Hi, can you please add a license.md ? I saw in the code that it's should be licensed under MIT, adding a license.md should be straightforward and optimal for everyone. Thanks!

    opened by MasterDrake 1
  • Row count of table?

    Row count of table?

    Hello, I've taken a look at this & can't seem to find a decent answer.

    Is there any suggested method to get the row count (i.e. length) of a table? I've tried using COUNT(*) without success & also something trivial like:

    int row_count = 0;
    sqlite3pp::query qry(this->db, "SELECT * FROM some_table");
    auto i = qry.begin();
    if (i != qry.end())
        row_count++;
    

    Thanks

    opened by OOHehir 0
  • Add a method wrapping `sqlite3_clear_bindings`

    Add a method wrapping `sqlite3_clear_bindings`

    The statement::reset method calls sqlite3_reset, which makes the sqlite3_stmt ready to run again, but does not clear bindings. That means if you run the statement again but don't re-bind every parameter, some of the old bindings stay in effect.

    There should be a method that calls sqlite3_clear_bindings, to ensure that all bindings are restored to null.

    opened by snej 0
  • how to insert a NULL value ?

    how to insert a NULL value ?

    Hi! Thank you for this library! I wanted to know the syntax i could use to insert a NULL value in a database ? For now I insert an empty string using:

    sqlite3pp::command cmd(
      this->m_database,
      "INSERT INTO table (a, b, c) VALUES (?,?,?)"
    );
    cmd.binder() << a << b << "";
    cmd.execute();
    

    Thank you!

    opened by Becheler 0
Releases(v1.0.8)
Owner
Wongoo Lee
Wongoo Lee
VSQLite++ - A welldesigned and portable SQLite3 Wrapper for C++ (C)

VSQLite++ - A welldesigned and portable SQLite3 Wrapper for C++ (C)

Vinzenz 'evilissimo' Feenstra 27 Dec 29, 2021
This is a demo for sqlite3.

说明 This is a demo for sqlite3. sqlite3 基础知识 sqlite3 命令分两类 系统命令 以 . 开头的命令 .q 退出sqlite3命令模式 .open 创建一个数据库 .databases 列出数据库 .schema 列出表结构 .tables 列出数据库中的

流浪小兵 1 Nov 24, 2021
Simple-MySQL-API is a free and easy API to manipulate MySQL with C99 and GCC compiler under GNU/Linux OS.

Simple-MySQL-API is a free and easy API to manipulate MySQL with C99 and GCC compiler under GNU/Linux OS.

Neptune 8 Aug 21, 2022
Yet another SQLite wrapper for Nim

Yet another SQLite wrapper for Nim Features: Design for ARC/ORC, you don’t need to close the connection manually Use importdb macro to create helper f

Code Hz 9 Jan 4, 2023
C++11 wrapper for the LMDB embedded B+ tree database library.

lmdb++: a C++11 wrapper for LMDB This is a comprehensive C++ wrapper for the LMDB embedded database library, offering both an error-checked procedural

D.R.Y. C++ 263 Dec 27, 2022
The C++14 wrapper around sqlite library

sqlite modern cpp wrapper This library is a lightweight modern wrapper around sqlite C api . #include<iostream> #include <sqlite_modern_cpp.h> using n

null 720 Dec 29, 2022
Lightweight C++ wrapper for SQLite

NLDatabase Lightweight C++ wrapper for SQLite. Requirements C++11 compiler SQLite 3 Usage Let's open a database file and read some rows: #include "NLD

Raven 0 Sep 20, 2019
The official C++ client API for PostgreSQL.

libpqxx Welcome to libpqxx, the C++ API to the PostgreSQL database management system. Home page: http://pqxx.org/development/libpqxx/ Find libpqxx on

Jeroen Vermeulen 718 Jan 3, 2023
The PostgreSQL client API in modern C++

C++ client API to PostgreSQL {#mainpage} Dmitigr Pgfe (PostGres FrontEnd, hereinafter referred to as Pgfe) - is a C++ client API to PostgreSQL servers

Dmitry Igrishin 137 Dec 14, 2022
SQLiteC++ (SQLiteCpp) is a smart and easy to use C++ SQLite3 wrapper.

SQLiteC++ SQLiteC++ (SQLiteCpp) is a smart and easy to use C++ SQLite3 wrapper. About SQLiteC++: SQLiteC++ offers an encapsulation around the native C

Sébastien Rombauts 1.6k Dec 31, 2022
VSQLite++ - A welldesigned and portable SQLite3 Wrapper for C++ (C)

VSQLite++ - A welldesigned and portable SQLite3 Wrapper for C++ (C)

Vinzenz 'evilissimo' Feenstra 27 Dec 29, 2021
An Sqlite3 Elixir library

Exqlite An SQLite3 library with an Ecto adapter implementation. Caveats When using the Ecto adapter, all prepared statements are cached using an LRU c

Matthew Johnston 147 Dec 30, 2022
An SQLite3 driver for Elixir

Exqlite An Elixir SQLite3 library. If you are looking for the Ecto adapater, take a look at the Ecto SQLite3 library. Documentation: https://hexdocs.p

elixir-sqlite 147 Dec 30, 2022
DOS CMD line build of a current SQLite3

DOSQLite This is a DOS/32 build of the command line tool for SQLite 3 based on sqlite-amalgamation-3340100. It was built using DJGPP like my other pro

null 8 Nov 25, 2022
An extra-lightweight Ruby gem for working with SQLite3 databases

Extralite Extralite is an extra-lightweight SQLite3 wrapper for Ruby. It provides a single class with a minimal set of methods to interact with an SQL

Digital Fabric 115 Dec 14, 2022
This is a demo for sqlite3.

说明 This is a demo for sqlite3. sqlite3 基础知识 sqlite3 命令分两类 系统命令 以 . 开头的命令 .q 退出sqlite3命令模式 .open 创建一个数据库 .databases 列出数据库 .schema 列出表结构 .tables 列出数据库中的

流浪小兵 1 Nov 24, 2021
Sol3 (sol2 v3.0) - a C++ <-> Lua API wrapper with advanced features and top notch performance - is here, and it's great! Documentation:

sol2 sol2 is a C++ library binding to Lua. It currently supports all Lua versions 5.1+ (LuaJIT 2.0+ and MoonJIT included). sol2 aims to be easy to use

The Phantom Derpstorm 3.3k Jan 4, 2023
Wrapper library for the BSD sockets API with a nicer C99 interface

A wrapper library for the BSD sockets API. Why? This library trades the series of getaddrinfo, socket, connect, bind, listen, etc. functions and their

Scott Vokes 136 Dec 10, 2022
Implement yolov5 with Tensorrt C++ api, and integrate batchedNMSPlugin. A Python wrapper is also provided.

yolov5 Original codes from tensorrtx. I modified the yololayer and integrated batchedNMSPlugin. A yolov5s.wts is provided for fast demo. How to genera

weiwei zhou 46 Dec 6, 2022
cudnn_frontend provides a c++ wrapper for the cudnn backend API and samples on how to use it

cuDNN Frontend API Introduction The cuDNN Frontend API is a C++ header-only library that demonstrates how to use the cuDNN C backend API. The cuDNN C

NVIDIA Corporation 127 Dec 28, 2022