rbock / sqlpp11 Goto Github PK

When SQL include a negative integer value, ddl2cpp failed to convert the SQL.

CREATE TABLE TEST (
  id varchar(255) PRIMARY KEY,
  num integer DEFAULT -1
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I think we need to add something like this to support negative numbers

negativeSign = Literal('-')
ddlNum     = Combine(Optional(negativeSign) + Word(nums + "."))

Hi,

In PostgreSQL there are many types, however in sqlpp11 I only see the types: boolean, integral, floating_point and text.

Take a look at: http://www.postgresql.org/docs/9.3/static/datatype.html

In other words, lots of types.

Is there a possibility to extend the library in such a way that it can support any type? For example, I would like to put a time / date field in an appropriate type (for example boost::posix_time::ptime)

Regards, Matthijs

When I try to convert the following SQL to C++ header,

CREATE TABLE TEST (
  id varchar(255) PRIMARY KEY,
  flag boolean
) ENGINE=InnoDB DEFAULT CHARSET=utf8

, I got

Traceback (most recent call last):
  File "/home/mtakeda/src/cpp/test_sqlpp11/sqlpp11/scripts/ddl2cpp", line 159, in <module>
    traitslist = [NAMESPACE + '::' + types[sqlColumnType]];
KeyError: 'boolean'

The 'types' table in ddl2cpp has the key 'bool' instead of 'boolean'. Is this just a typo? I'm using the revision 'e152e29'.

I recently came accross this library and it looks great however my use-case almost always needs async behavior. Since I'm not familiar with the code, the question might be a stupid one so please bear with me.

Would it be easy or difficult to extend the api to have async callbacks (lambdas) on completion of requests? Is anything like that planned?

What is the recommended way to store a prepared statement?

I have achieved to store a prepared select like this:

// structure for storing prepared statements
struct PreparedStatements
{
    std::unique_ptr<decltype(m_db->prepare(select(count(objects.Id).as(numObjects)).from(objects).where(objects.ParentId == parameter(objects.ParentId))))> childCount;
};

void Database::prepareStatements()
{
    auto childCount = m_db->prepare(
        select(count(objects.Id).as(numObjects))
        .from(objects)
        .where(objects.ParentId == parameter(objects.ParentId))
    );

    m_statements->childCount.reset(new decltype(childCount)(std::move(childCount)));
}

But this is not very intuitive and it forces me to duplicate the query to obtain the type of the prepared statement.
The deleted default constructor of prepared_statement_t also forces the use of a pointer to store the select.

Can this be done in a simpler way?

Hi!
I was wandering if you ever think about writing a python parser for creating c++ code for custom user functions, similar to how you create c++ code for tables?
IMHO It should not be so hard... but I can be wrong :)

I start implementing a sybase connector for sqlpp und I found some issues that is not easy to implement with sybase. I used this skeleton and I am currently a bit in trouble to implement this.

As far as I can see the statement will be executed and then the resultset is read row by row and for each row every single variable is filled with some bind option column by column. Is this true or am I missunderstanding something?

Sybase supports binding, but before the first row is read, you have to specify the variables for each column and bind it directly, meaing with each row you read the varibables are filles by Syase.

Is this kind of binding support, too?

FunctionTest.cpp compile error with gcc(4.8.1), error stack:

In file included from
sqlpp11/include/sqlpp11/table_base.h:31:0,
from sqlpp11/tests/TabSample.h:30,
from sqlpp11/tests/FunctionTest.cpp:26:
sqlpp11/tests/FunctionTest.cpp: In function ‘int main()’:
sqlpp11/include/sqlpp11/alias.h:41:5: error: invalid declaration of member template in local class
template
^
sqlpp11/tests/FunctionTest.cpp:377:3: note: in expansion of macro ‘SQLPP_ALIAS_PROVIDER_GENERATOR’
SQLPP_ALIAS_PROVIDER_GENERATOR(kaesekuchen);
^

Hello there and thank you for a really neat library! I would like to use autoincrement primary keys but am seeing some unexpected behavior.

CREATE TABLE user (
id int AUTO_INCREMENT PRIMARY KEY,
phone varchar(255) DEFAULT NULL
)

If I insert into this table with: db(insert_into(table).default_values() and then do a select of all rows, I see my id field with _is_null set to true and a value of 0, no matter how many times I insert. select statements that query by id do not return any results. The return value from my insert calls are auto-incrementing, so it seems like the data's getting down to the sqlite3 layer. Any idea what might be going on?

Thanks again.

Hi,

I'm trying to use the dynamic select statements like dynamic_where and dynamic_limit like that:

auto s = dynamic_select(m_db, all_of(entry)).from(entry).dynamic_where();
s.add_where(true);

but I got the following compilation error:

error: ‘struct sqlpp::statement_t<sqlpp::sqlite3::connection, sqlpp::no_with_t, sqlpp::select_t, sqlpp::no_select_flag_list_t, sqlpp::select_column_list_t<...>, sqlpp::from_t<void, sdn_sql::Entry>, sqlpp::no_extra_tables_t, sqlpp::where_t<sqlpp::sqlite3::connection>, sqlpp::no_group_by_t, sqlpp::no_having_t, sqlpp::no_order_by_t, sqlpp::no_limit_t, sqlpp::no_offset_t, sqlpp::no_union_t>’ 
has no member named ‘add_where’

My code seems similar to the one on the wiki. I search the code and it does not seem to have the add_where method. Did you change the usage of dynamic select statements?

Best regards,
Simon

Some parameters (like "${include_dir}/*.h" and "${CMAKE_BINARY_DIR}/tests") are passed to CMake commands in your build scripts without enclosing them by quotation marks. I see that these places will result in build difficulties if the contents of the used variables will contain special characters like semicolons.

I would recommend to apply advices from a wiki article.

See title :-)

Hi Roland,

Perhaps I missed it while browsing the code, but is there a way to specify the database in a query? I need to work with multiple databases (e.g., via ATTACH in SQLite), and I am unable to perform operations outside of the main (default) database. E.g., I perform the following:

ATTACH 'a.db' AS a;
ATTACH 'b.db' AS b;
SELECT t1.foo, t2.bar FROM a.my_table AS t1, b.my_table AS t2 WHERE t1.id = t2.id;

Currently, I'm unaware of a mechanism to provide such queries in a type-safe manner.

I would like to perform the following request SELECT * FROM entry WHERE id IN (SELECT id FROM entry WHERE ...); but could not find any documentation on how to use the in operator.

does sqlpp11 support union operator ?

x IN () is invalid SQL. Basically it should evaluate to FALSE.

Hi,

I need to check if user 'Bob' exists in the table:

CREATE TABLE users(
   id INT PRIMARY KEY,
   nickname varchar(32)
)

Usually I would have run the following query:

SELECT COUNT(*) FROM users WHERE nickname='Bob'

But I can not understand how to do this using sqlpp11?

Thanks!

Hi.

The stock clang version from the latest Xcode version (6.1.1) on OS X Yosemite crashes with a segmentation fault where building the library's tests (specifically, when building ResultTest.cpp.

While this is clearly a bug in the compiler, not in the library, this means the tests cannot currently be run on OS X and this is a problem for the library in the first place. It would be nice to get a reduced test case out of that bug so I can send a bug report to clang's folks.

I'm also going to test if the problem happens with clang trunk development branch and I will update the issue here.

Hello Roland,

At the moment I get errors like these when linking the program:

main.o:/home/matthijs/sometest/../sqlpp11/include/sqlpp11/verbatim_table.h:86: first defined here
rss/rss.os: In function `insert':
/home/matthijs/sometest/../sqlpp11/include/sqlpp11/insert.h:99: multiple definition of `sqlpp::insert()'
main.o:/home/matthijs/sometest/../sqlpp11/include/sqlpp11/insert.h:99: first defined here
rss/rss.os: In function `remove':
/home/matthijs/sometest/../sqlpp11/include/sqlpp11/remove.h:103: multiple definition of `sqlpp::remove()'
main.o:/home/matthijs/sometest/../sqlpp11/include/sqlpp11/remove.h:103: first defined here
rss/rss.os: In function `select':
/home/matthijs/sometest/../sqlpp11/include/sqlpp11/select.h:83: multiple definition of `sqlpp::select()'
main.o:/home/matthijs/sometest/../sqlpp11/include/sqlpp11/select.h:83: first defined here
clang: error: linker command failed with exit code 1 (use -v to see invocation)
scons: *** [main] Error 1

Probably these functions need to be inlined.

Regards, Matthijs

const auto a = select(count(tab.id)).from(tab).where(true).group_by(tab.name).as(sqlpp::alias::a);
for (const auto& row : db(select(a.count).from(a).where(a.count > 1)))
{
   // compiles fine, but produces an error in the DB, because the subselect is
   // SELECT COUNT(tab.id) FROM tab;
   // It should be COUNT(tab.id) AS "_COUNT" from tab;
   // Or something similar...
}

That is annoying, because I cannot just always serialize it as AS _COUNT, since it might be used in an expression like COUNT(id) % 2, for instance.

SQLPP_DECLARE_TABLE(
    (clients)
    ,
    (login,     int,            SQLPP_PRIMARY_KEY)
    (name,      text,           SQLPP_NOT_NULL)
    (address,   text,           SQLPP_NOT_NULL)
)

const auto clients = clients::clients{};
auto i = insert_into(clients).columns(clients.login, clients.name);

Output:

insert_value_list.h(550): error C2338: at least one column argument has a must_not_insert tag in its definition

In sqlpp11\include\sqlpp11\ppgen\colops\primary_key.h I see such code

#define SQLPP_DECLARE_COLUMN_GEN_TRAITS_PROC_SQLPP_PRIMARY_KEY(...) \
  ::sqlpp::tag::must_not_insert, ::sqlpp::tag::must_not_update

which prevent from inserting

Hi all,

Playing around a bit here http://webcompiler.cloudapp.net/ I found a simple workaround to the constexpr problem reported here: #34 (comment)

It is pushed to the develop branch. Who can give it a try with VC 2015 and report the remaining errors?

Best,

Roland

Hi Roland,

I appreciate your work on Sqlpp11 library, quite impressed with the concept and would like to use it in my project but unfortunately your code does not compile with VS2013. Can you please add support to get this compile with VS2013?

I know you mentioned somewhere you have used C++11 features that were not supported by VS 2013 compiler but there are alternatives you can use bu substituting with equivalent boost library implementation.

Can you please add support to get this compile using MSVC 2013?

Thanks
Praveen

Is it possible to perform requests with case statements using sqlpp11?

SELECT CASE WHEN col < 100 
            THEN col 
            ELSE othercol 
       END AS colname 
FROM table

Hi,

First I just want to say I really like this library, I think you've done a great job here and hacking on the code helped me improve my understanding of Cpp11 a great deal. So thanks for that! :)

One of the things I've wrote is a little helper function that creates a CREATE TABLE statement from a given table. For example if we use the following PPGEN code
SQLPP_DECLARE_TABLE(
(tab_test),
(id , int , SQLPP_PRIMARY_KEY)
(name , varchar(255), SQLPP_NOT_NULL )
(feature, int , SQLPP_NOT_NULL )
)

My function outputs:
CREATE TABLE tab_test(id INTEGER AUTOINCREMENT NOT NULL,name TEXT NOT NULL,feature INTEGER NOT NULL);

Currently this only supports AUTOINCREMENT, NOT NULL and basic types (Everything is INTEGER or TEXT). But obviously this is just a quick&dirty proof of concept!

I was wondering if this is something you'd like to merge into the library. If so I'll work on making it more robust.

Hello Roland,

An easy to fix bug:

The ddl2cpp emits for each table an include:

#include <sqlpp11/table_base.h>

However that file does not exist, it should probably be table.h:

#include <sqlpp11/table.h>

Regards, Matthijs

Hello Roland!

Is there any way to make multi-row insert with prepared statement?

Hello Roland,

I tried to build the tests but they fail with the following error:

-- The C compiler identification is GNU 4.9.1
-- The CXX compiler identification is GNU 4.9.1
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Using /usr/bin/c++ (compiler id: GNU)
-- Configuring done
CMake Error at tests/CMakeLists.txt:5 (add_executable):
  Cannot find source file:

    ResultTest.cpp

  Tried extensions .c .C .c++ .cc .cpp .cxx .m .M .mm .h .hh .h++ .hm .hpp
  .hxx .in .txx
Call Stack (most recent call first):
  tests/CMakeLists.txt:18 (build_and_run)


-- Build files have been written to: /home/matthijs/sqlpp11/build

Regards, Matthijs

Hi,

Tell me please, is there any way to create tables?
I.e. I was expected something like this:

if ( !db.exists<Table>() )
   db.create<Table>();

Roland,
what's the status of sqlpp11? I remember one year some discussion in boost mailing list but then I didn't see more info.
Is the lib still under development?
Any plan to submit to boost to be reviewed?

Thanks in advance

When binding parameters for prepared statements: tvin cannot be used.
e.g:

preparedItemAdd.params.RefId = sqlpp::tvin(item.refId);

generates a compilation error

Hi,

So we just brought down a server by running something like

db(select(all_of(tabA)).from(tabA, tabB).where(true));

tabA and tabB are quite big, their unconditional join is ginormous. Too big for the server as it turned out.

One of the main purposes of sqlpp11 is to protect developers from those small mistakes that can kill your business if they are not caught. That's why for instance you cannot omit the where condition. You have to say where(true) to show your intent.

I therefore intend to disallow implicit joins like from(tabA, tabB) in future releases. This would enforce explicit joins

db(select(all_of(tabA)).from(tabA.join(tabB).on(someCondition)).where(true));

It would also enforce the join condition, making it much less likely that somebody forgets to add that condition to the where expression.

I guess I should deprecate the old functionality before removing it (although I am not exactly sure how to do this as [[deprecated]] is a C++14 feature)

Other thoughts?

Thanks in advance,

Roland

Although the intention of sqlpp11 is to encourage expression tree usage over raw strings, there are still times when it is useful to pass a simple string SQL statement where no table static table definition can be relied on (for pragmas, working with master, etc.). Rather than rely on string quoting regimes and lexical casts, it would be helpful to have bound arguments to connection::execute(). For example:

bool writableSchema = /*...*/;
db->execute("PRAGMA writeable_schema = ?", writableSchema);

Jan Nikolas has implemented a nice variadic implementation which accomplishes this type of functionality utilizing variadics: http://boost-talk.blogspot.com/2012/04/sqlite-oop-v2-c11.html. It seems like it wouldn't be too hard to add on to sqlpp11.

There is a clang-format configuration file in the repository, but the formatting of the sources files does not match it at all.

Which one should be corrected? The sources or the configuration file?

When binding parameters for prepared statements: verbatim cannot be used.
e.g:

preparedItemAdd.params.Metadata = sqlpp::verbatim<sqlpp::integer>("last_insert_rowid()");

generates a compilation error

The Database page in the wiki links to https://github.com/rbock/sqlpp11/blob/master/database_api/api.h, which does no longer exist.

It probably should point to https://github.com/rbock/sqlpp11/blob/master/connector_api/connection.h

Hi,

Do you have any plans to add Windows support using the Microsoft compiler?
The latest preview of Visual Studio 2015 looks very promising (it compiles all my C++14 code).

(I use Linux as my primary platform, but some projects has to be x-platform, and under Windows, I tend to use the Microsoft compiler).

Which version should I use as a consumer of the library:

• master branch
• develop branch
• or the latest tag?

This important question also applies to the connector libraries.

I downloaded the connector for mysql project and ran:

cmake CMakeLists.txt
make

and get this error:

Scanning dependencies of target sqlpp-mysql
[ 6%] Building CXX object src/CMakeFiles/sqlpp-mysql.dir/connection.cpp.o
In file included from /Users/juandent/Programs/C++/sqlpp11-connector-mysql-master/src/connection.cpp:30:
/Users/juandent/Programs/C++/sqlpp11-connector-mysql-master/src/detail/prepared_statement_handle.h:31:10: fatal error: 'mysql/mysql.h' file not
found

Hi,

I read on the limitations of outer join... is there a workaround?

Thanks!
Juan

Hello,

What kind of dumps are supported by the ddl2cpp script? I was trying a PostgreSQL dump but that did not work out...

Regards, Matthijs

Hi,

sqlpp11 does not support these types, really?
Why? Can anybody tell me, what should I do to implement support for these types?

Hi, sqlpp11 is really neat, I encounter compiler error with the following test case with 0.31,
while as I think this will compile and run with 0.29. Thanks, Mark

tvin.cpp:

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/sqlite3/sqlite3.h>
#include "tab.h"

using sqlpp_connection = sqlpp::sqlite3::connection;
using sqlpp_connection_config = sqlpp::sqlite3::connection_config;

int main(int argc, char *argv[])
{
    sqlpp_connection_config config;
    config.path_to_database = ":memory:";
    config.flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
    config.debug = true;
    sqlpp_connection db(config);
  db.execute(R"(CREATE TABLE tab (
    id integer not null,
    str character varying(10)
    ))");
  T::Tab t;
  db(remove_from(t).where(true));
  std::string s;
  db(sqlpp::insert_into(t).set(t.id = 1, t.str = sqlpp::tvin(s)));
  return 0;
}

tab.sql:

CREATE TABLE tab (
  id integer not null,
  str varchar
);

Makefile:

tvin:   tvin.cpp tab.h
    g++ -std=gnu++14 tvin.cpp

tab.h:  tab.sql
    ddl2cpp tab.sql tab T

Compiler error with g++-4.9.2:

-*- mode: compilation; default-directory: "/data/eng/dev/cxx/sqlpp/tvin/" -*-
Compilation started at Fri Mar 13 21:33:55

make
g++ -std=gnu++14 tvin.cpp
In file included from /usr/include/sqlpp11/boolean.h:35:0,
                 from /usr/include/sqlpp11/column_types.h:30,
                 from /usr/include/sqlpp11/sqlpp11.h:31,
                 from tvin.cpp:1:
/usr/include/sqlpp11/tvin.h: In instantiation of 'static void sqlpp::assert_tvin_with_correct_operator_t::_() [with T = void]':
/usr/include/sqlpp11/sqlite3/connection.h:282:60:   required from 'decltype (t._run((*(sqlpp::sqlite3::connection*)this))) sqlpp::sqlite3::connection::operator()(const T&) [with T = sqlpp::statement_t<void, sqlpp::insert_t, sqlpp::into_t<void, T::Tab>, sqlpp::insert_list_t<void, sqlpp::assignment_t<sqlpp::column_t<T::Tab, T::Tab_::Id>, sqlpp::integral_operand>, sqlpp::assignment_t<sqlpp::column_t<T::Tab, T::Tab_::Str>, sqlpp::tvin_arg_t<sqlpp::text_operand> > > >; decltype (t._run((*(sqlpp::sqlite3::connection*)this))) = long unsigned int]'
tvin.cpp:22:65:   required from here
/usr/include/sqlpp11/tvin.h:66:4: error: static assertion failed: tvin may only be used with operators =, == and !=
    static_assert(wrong_t<T>::value, "tvin may only be used with operators =, == and !=");
    ^
Makefile:2: recipe for target 'tvin' failed
make: *** [tvin] Error 1

Compilation exited abnormally with code 2 at Fri Mar 13 21:33:56

Hi!
Postgresql forces columns to be in group_by clause or aggregate function when you use it side by side with other aggregate function, so compiling this code
https://gist.github.com/cszawisza/054a94c527cd86afb1a5 should fail, but instead it generates a buggy code, which generates SQLSTATE[42803]: Grouping error

Is there any some kind of fallback interface to send raw query through connector to database?
It could be quite useful when some features are not implemented and you agree to loose some speed doing string conversions.
For example, I suppose views are not supported and implementing them within sqlpp is a long work, but I could still query them using plain "SELECT blah-blah"

Sorry if it's too much trivial :)

In https://github.com/rbock/sqlpp11/wiki/NULL ( tvin() explanation ):

select(all_of(tab)).from(tab).where(tab.alpha == sqlpp::tin(a));
=>
select(all_of(tab)).from(tab).where(tab.alpha == sqlpp::tvin(a));

I made an ODBC connector for sqlpp11. From what I can tell, using an ODBC connector for MySQL will allow you to connect to a MySQL database without becoming GPL because ODBC uses replaceable binaries (.so/.dll files) for the connectors and MySQL's components can easily be replaced by any other ODBC compliant database. While ODBC is available on Windows I don't think sqlpp11 is capable of wide characters, so ASCII encoded SQL would be a requirement. For thread safety, I would not share a connection object between threads, but you could pass the connection_config object to make a new connection in each thread.

Let me know if there are any issues I should correct.

Certain queries are not possible to express with sqlpp11 due to excessive parentheses that are generated during serialization. For example, the query select(x).from(A).where(x.not_in(select(y).from(B))) will create an extra set of parentheses around the SELECT statement in the IN clause. This breaks on sqlite3. As an example (run with sqlite3 -echo < test.sql):

DROP TABLE IF EXISTS A;
CREATE TABLE A (x INTEGER);
DROP TABLE IF EXISTS B;
CREATE TABLE B (y INTEGER);

INSERT INTO A (x) VALUES (1), (2), (3);
INSERT INTO B (y) VALUES (1), (2), (3);

-- OK: correctly returns no results
SELECT x FROM A WHERE x NOT IN (SELECT y FROM B);

-- ERROR: returns '2' and '3' - this is the format that sqlpp11 generates
SELECT x FROM A WHERE x NOT IN ((SELECT y FROM B));

SQLite supports the "INSERT OR REPLACE" syntax for MERGE statements. It doesn't look like this is supported in sqlpp11. Are there plans to add this?

Hi Roland

Does your project compile with the new Visual Studion 2015 version? Has anyone chequed this? I can't use your code unless I can also compile it on Windows with Visual Studio.
Peter