Question/Suggestion: Creation of CREATE TABLE statements about sqlpp11 HOT 22 CLOSED

Hi,

Some time ago I started to work on the same functionality for PPGEN, but still not finished.
The idea is that SQLPP_DECLARE_TABLE() contains a 'static const char *' member that can be used by any databases front-ends.

At the moment, the generation of the request of creating tables for sqlite3 is implemented (not completely).
Using this PPGEN code:

SQLPP_DECLARE_TABLE(
    (tab_person
        ,SQLPP_ENGINE("InnoDB")
        ,SQLPP_CHARACTER_SET("utf-8")
        ,SQLPP_COMMENT("'tab_person' table comments")
    )
    ,
    (id, int
        ,SQLPP_PRIMARY_KEY
        ,SQLPP_AUTO_INCREMENT
    )
    (name, varchar(255)
        ,SQLPP_NOT_NULL
        ,SQLPP_DEFAULT('some name')
        ,SQLPP_INDEX() // auto-generated index name
    )
    (feature, int
        ,SQLPP_NOT_NULL
        ,SQLPP_INDEX("col_feature_index") // hardcoded index name
    )
    (age, int
    )
)

this request can be generated:

    static constexpr const char* _create_table = "BEGIN;\n"
                                                 "CREATE TABLE "
                                                 ""
                                                 "tab_person"
                                                 "("
                                                 "\n\t"
                                                 "id"
                                                 " "
                                                 "int"
                                                 " "
                                                 "PRIMARY KEY "
                                                 "AUTOINCREMENT "
                                                 "\n\t"
                                                 ","
                                                 "name"
                                                 " "
                                                 "varchar(255)"
                                                 " "
                                                 "NOT NULL "
                                                 "DEFAULT "
                                                 "'some name'"
                                                 "\n\t"
                                                 ","
                                                 "feature"
                                                 " "
                                                 "int"
                                                 " "
                                                 "NOT NULL "
                                                 "\n\t"
                                                 ","
                                                 "age"
                                                 " "
                                                 "int"
                                                 " "
                                                 "\n);\n"
                                                 "COMMIT;";

from sqlpp11.

Hi,

Glad you like the library and that it helped you with your understanding of C++11 :-)

I like the idea of generating CREATE TABLE statements using sqlpp11. It is on my wish list :-)

As of now, my goal is to

• add a few more optional fields to the C++ column and table specification types, e.g. primary_key or constexpr x default_value = 3 etc.
• add a create_table function to sqlpp11
• provide a default interpretation in sqlpp11
• provide specialized interpretations in the connector libraries

Should not be that hard, actually.

Of course, it would make sense to extend and use @niXman 's PPGEN to specify the C++ types for this, see examples/ppgen.cpp

from sqlpp11.

I am very interested in this feature too... can I ask what is its status, and if there is anything that we can do to speed up development?

from sqlpp11.

@niXman Maybe you could comment?

In any case, the best way to speed things up is to create a pull request :-)

from sqlpp11.

I have some ideas in mind for a pull request, indeed! :)

However, I am a very new user of the library and I would therefore appreciate an headstart:

• is there any way to query the fields of a table, i.e. to retrieve a list of fields without knowing them in advance? Is this an acceptable way to go, in case this feature is just "not ready yet"?
• in the same way, is there a way to obtain a list of field tags (e.g. "not null", "default", etc.)
• how do I translate the tags or field types to SQL commands? is there a sqlpp11 function or should I look in the connectors?
• is boost.hana an acceptable dependency?

Thank you again for the useful library!

from sqlpp11.

Hi,

• table types contain a _column_tuple_t. This would give you the data you need. In both, table and column, _alias_t::_name_t should be used to obtain the name.
• can_be_null is available already, however there is data missing like AUTOINCREMENT or a default value. These could be added to the column specifications.
• I would try to go for a default implementation in sqlpp11 which could be (partially) overridden in the connector libraries
• boost.hana is C++14, while sqlpp11 is C++11. Also, I try to keep dependencies to a minimum.

Cheers,

Roland

from sqlpp11.

Thank you for the information. That already got me started.
@niXman Would you mind share your code for this feature, so I can take over where you left it?
Maybe @blapid can do the same?

from sqlpp11.

I've had a semi-functioning proof of concept back when I posted this issue, but unfortunately did not have the time to keep working on it.
I'll post the snippets below, I hope you'll find them useful but I'll understand if you wont like them ;)
When I've worked on this, I've detached from v0.35, I hope this will still work for newer versions.

Alright, let's get into it:
The first thing I did is add type names to some data types. For example for the boolean data type, I changed the file /include/sqlpp11/data_types/boolean/data_type.h like this:

     template <typename T>
     using _is_valid_operand = is_boolean_t<T>;
+
+    static constexpr const char _literal[] =  "BOOLEAN";
+    using _name_t = make_char_sequence<sizeof(_literal), _literal>;

I've done this for bool, integer and text. Remember that these might change depending on the actual DB that we're using. So this might actually belong in the connector api? Maybe with default values in the library itself? You'd probably have to do some more template structs that takes an sqlpp11 data type as typename and hold it's own relevant name under _name_t or uses the default from the library. But I may be missing an easier solution :).

Then I had to use variadic template magic to go through all of the columns and create the CREATE query. I've used include/sqlpp11/interpret_tuple.h as a reference, mainly copying what interpret_tuple does with slight alterations.
The full snippet:

#include <tuple>
#include <sqlpp11/type_traits.h>
#include <sqlpp11/serialize.h>
#include <sqlpp11/detail/index_sequence.h>
#include <sqlpp11/container/connection.h>
#include <stdio.h>
namespace sqlpp_test
{
  template <typename Element, typename Separator, typename Context, typename UseBraces>
  static void interpret_tuple_element(
      const Element& element, const Separator& separator, Context& context, const UseBraces&, size_t index)
  {
    if (index)
      context << separator;
     /*
    if (UseBraces::value)
      serialize_operand(element, context);
    else
      serialize(element, context);
     */
     context <<  Element::_alias_t::_name_t::char_ptr() << " " << Element::_traits::_value_type::_name_t::char_ptr();
     if (sqlpp::detail::is_superset_of<typename Element::_traits::_tags, sqlpp::make_traits<sqlpp::tag::must_not_insert,
         typename sqlpp::tag::must_not_update>::_tags>::value)
     {
         context << " AUTOINCREMENT";
     }
     if (!sqlpp::detail::is_element_of<typename sqlpp::tag::can_be_null, typename Element::_traits::_tags>::value)
     {
         context << " NOT NULL";
     }

  }

  template <typename Tuple, typename Separator, typename Context, typename UseBraces, size_t... Is>
  auto interpret_tuple_impl(const Tuple& t,
                            const Separator& separator,
                            Context& context,
                            const UseBraces& useBraces,
                            const sqlpp::detail::index_sequence<Is...>&) -> Context &
  {
    // Note: A braced-init-list does guarantee the order of evaluation according to 12.6.1 [class.explicit.init]
    // paragraph 2 and 8.5.4 [dcl.init.list] paragraph 4.
    // See for example: "http://en.cppreference.com/w/cpp/utility/integer_sequence"
    // See also: "http://stackoverflow.com/questions/6245735/pretty-print-stdtuple/6245777#6245777"
    // Beware of gcc-bug: "http://gcc.gnu.org/bugzilla/show_bug.cgi?id=51253", otherwise an empty swallow struct could
    // be used
    using swallow = int[];
    (void)swallow{0,  // workaround against -Wpedantic GCC warning "zero-size array 'int [0]'"
                  (sqlpp_test::interpret_tuple_element(std::get<Is>(t), separator, context, useBraces, Is), 0)...};
    return context;
  }

  template <typename Tuple, typename Separator, typename Context>
  auto interpret_tuple(const Tuple& t, const Separator& separator, Context& context) -> Context &
  {
    return sqlpp_test::interpret_tuple_impl(t, separator, context, std::true_type{},
                                sqlpp::detail::make_index_sequence<std::tuple_size<Tuple>::value>{});
  }

  template <typename Tuple, typename Separator, typename Context>
  auto interpret_tuple_without_braces(const Tuple& t, const Separator& separator, Context& context) -> Context &
  {
    return sqlpp_test::interpret_tuple_impl(t, separator, context, std::false_type{},
                                sqlpp::detail::make_index_sequence<std::tuple_size<Tuple>::value>{});
  }
}

The main changes being:

     ...
     context <<  Element::_alias_t::_name_t::char_ptr() << " " << Element::_traits::_value_type::_name_t::char_ptr();
     if (sqlpp::detail::is_superset_of<typename Element::_traits::_tags, sqlpp::make_traits<sqlpp::tag::must_not_insert,
         typename sqlpp::tag::must_not_update>::_tags>::value)
     {
         context << " AUTOINCREMENT";
     }
     if (!sqlpp::detail::is_element_of<typename sqlpp::tag::can_be_null, typename Element::_traits::_tags>::value)
     {
         context << " NOT NULL";
     }
     ...

The first line adds the name of the column, followed by its type name (that we've added above).
The second part checks that the columns traits have must_not_insert and must_not_update (iirc), and if so, adds "AUTOINCREMENT".
The third part checks can_be_null as @rbock mentioned.

This may be used as following:

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/ppgen.h>

SQLPP_DECLARE_TABLE(
	(tab_test)
	,
	(id     , int         , SQLPP_PRIMARY_KEY)
	(name   , varchar(255), SQLPP_NOT_NULL   )
	(feature, int         , SQLPP_NOT_NULL   )
)

int foo(int, char* [])
{
  sqlpp::container::context_t context;

  context << "CREATE TABLE " << tab_test::tab_test::_alias_t::_name_t::char_ptr() << "(";
  sqlpp_test::interpret_tuple(tab_test::tab_test::_column_tuple_t{}, ',', context);
  context << ");";
  printf("%s\n",context.str().c_str());
}

First, we declare the table using PPGEN, however, any table would work assuming it has the right traits.
Then, in our function, we start the CREATE query with the table's name, then use interpret_tuple to add the rest of the information.
Finally, we close the query and printf.

FIN

As I've said, proof of concept, but I think it's possible to integrate it in if you have the time. As @rbock said, default value (and probably other stuff that we're missing) need to be added to column specification or to the library.
Hope this helps!

from sqlpp11.

@blapid, @rbock, @matpen, I'm sorry, but I will not be able to reply about subj until the end of the week.

from sqlpp11.

Thank you @blapid for your snippets: my plan is to get them to work, and fork a branch where we can share progress. I would like to ask @rbock about his opinion on the type names: storing them in the data types (data_types.h) seems logical, but maybe it is harder to override in the connectors? Should we centralize all types in a separate function?

@niXman Thank you for posting: do not worry, I have limited free time at the moment, too, so I won't be doing much before you find the type to reply.

from sqlpp11.

@matpen I would use the serialize function for serializing data types. Then you can use specialization in the connectors to override where necessary (just as with everything else).

from sqlpp11.

Currently sqlpp11 supports the data type date (via sqlpp::day_point), datetime (via sqlpp::time_point). I would like to ask if there is any corresponding update on the files under sqlpp11/ppgen/colops ?

from sqlpp11.

Unfortunately I did not have time to work on those files. So no, there is no change, at least from my side.

from sqlpp11.

@choiip done: #185

from sqlpp11.

I assume since it has been a year since the last comment this feature never made it to the light of day. How do you recommend initializing a database system at the moment? Running the queries from the cmd line manually via a file input?

Is there a way in the C++ lib to detect if the tables do not already exist to trigger this cmd line functionality?

from sqlpp11.

I am adding this to the wish list I am currently compiling.

from sqlpp11.

Any update on this feature?

from sqlpp11.

No update for sqlpp11. Admittedly, I am hoping for someone who actually has a use case (I don't) :-)

I wrote a prototype in sqlpp17. I won't finish that library, but you could try to migrate that code to sqlpp11.

from sqlpp11.

@rbock I'm working at a different level, using LLVM (clang, libclang, libtooling) to synchronise [bidirectionally] OpenAPI with the code. I've done the same in Python, and WiP for Rust, Swift, Java, and Kotlin.

I am currently weighing up ODB, Oat++, SOCI, cppdb, and sqlpp11 for the ORM layer, and will decide what to use for the REST API layer once that first decision is made (as some of these were developed as part of a REST API ++ framework).

With my compiler driven approach, I expect people to be able to rapidly produce REST API + db backends with full test and doc coverage, and be able to immediately translate the interfaces across language boundaries (e.g., to frontends, or to rewrite a slow-performing Python backend in C++ or Rust).

Currently focussing on the non-Turing-complete aspect of this, translating just the interfaces, tests, docs, defaults, and types.

Happy to beta test, but I've got this more immediate problem and I don't want to get distracting (unless I get into the game of actually maintaining ORMs… but doubt I have the time)

from sqlpp11.

I am sharing the same predicament: Time is too scarce.

Providing interfaces to create tables via sqlpp11 is possible. But it is difficult to offer one interface across all database vendors as they all have their own specific nuts and bolts. That is why I left the table creation logic outside of the actual C++ code. Or in other words: My C++ code usually expects the database to exist before the program gets started.

I am not sure I fully understand your goal, but I think I am aiming for something similar (long term): Being able to translate fluently between objects in C++ and their representation in databases using reflection (in C++26 or so).

from sqlpp11.

@rbock Happy to assist. What I need are examples inputs and outputs, say with guards or similar, for each database. Then I'll using LLVM libclang libtooling to translate a POC++C (Plain Old C++ Class) to a "database table" and back again.

But I don't know what it's meant to look like, which is why I'm seeking a ready-made framework for this…

from sqlpp11.

Let me point out that you can create tables in sqlpp11 using the connection's execute method. It obviously does not have the same type safety guarantees as the rest of the library offers, but it is an option, see for instance:

https://github.com/rbock/sqlpp11-connector-sqlite3/blob/3da71bbc7b060e2579147eaf5a6a2c353af37e5e/tests/SampleTest.cpp#L53

Personally, I won't write a create table method for sqlpp11 any time soon. There are just too many different flavors of how to create tables between different databases. IMO, the cost of creating such functions for each connector is not justified by the benefits.

from sqlpp11.