Is there any way to create tables? about sqlpp11 HOT 27 CLOSED

That is a neat way of doing it. Thanks for sharing!

from sqlpp11.

Based on @mlimber comment.
What if we add the functionality he described (gen_create_table_cmd.sh) into ddl2cpp?
So, it will also save full create table query from ddl into schema.h file, so later we can get it from there?

With this approach we do not miss something (pkeys etc.). We do not need any changes to sqlpp11 itself. We can even add a flag to connection_config that will create table on the first run (when it does not exist). For example, connection_config.create_tables_if_not_exists.

@rbock and others
What do you think?

from sqlpp11.

Hi,

Starting with the bad news: No, there is not, at least not as part of sqlpp11.

Personally I believe that that the script installing the software should be responsible for setting up the environment (including tables). But questions/requests around this topic keep coming up, though, so maybe I am wrong about this.

I am also not sure how to do this. One issue is that the table types do not contain all information for creating tables. For instance

• primary keys are unknown
• other constraints, like foreign keys or unique are unknown as well
• indexes are unknown
• defaults are unknown
• ...

Also, databases are constructed in quite different ways depending on the vendor, for instance a MySQL table might be constructed with ENGINE=InnoDB DEFAULT, which does not make any sense in other databases.

Thus, as of now, the only way to create tables is to use db.execute("somecommand"), as it is done in the examples of the connector libraries. If you have better ideas, please let me know.

The exists method that you expected also does not exist yet, but that would be relatively easy, of course :-)

Regards,

Roland

from sqlpp11.

Just now I am working on meta-code preprocessor generator, and I think I can implement the necessary meta-code generation for creating tables.
Syntax example:

SQLPP_DECLARE_TABLE(
    table_name
    ,
    (id, int, NOT_NULL, PRIMARY_KEY, AUTO_INCREMENT)
    (name, varchar(64), NOT_NULL, INDEX("name_index"), DEFAULT("any name"))
    (age, int, NOT_NULL, INDEX("age_index"), UNIQUE, COMMENT("some comments"))
)

from sqlpp11.

That looks quite promising. Looking forward to seeing more of that :-)

from sqlpp11.

I need to know what code to be generated for PRIMARY_KEY/AUTO_INCREMENT/INDEX()/DEFAULT()/UNIQUE/COMMENT() ?

from sqlpp11.

You might want to take a look at the python code generator in ./scripts

Most of these have no impact for the code types, but for instance

• AUTO_INCREMENT could result in tag::must_not_insert and tag::must_not_update since you probably do not want to overwrite the auto-generated value
• any column that has NOT_NULL and no DEFAULT or AUTO_INCREMENT could tag::require_insert
• a missing NOT_NULL should result in tag::can_be_null

It could be argued that some of these choices are a matter of taste.

from sqlpp11.

For table props like ENGINE I can change the first argument for SQLPP_DECLARE_TABLE() to pp-tuple:

SQLPP_DECLARE_TABLE(
    (table_name, ENGINE("InnoDB"), CHARACTER_SET("utf-8"))
    ,
    (id, int, NOT_NULL, PRIMARY_KEY, AUTO_INCREMENT)
    (name, varchar(64), NOT_NULL, INDEX("name_index"), DEFAULT("any name"))
    (age, int, NOT_NULL, INDEX("age_index"), UNIQUE, COMMENT("some comments"))
)

from sqlpp11.

Ok, for AUTO_INCREMENT I have no more questions, but I have an another question: does the sqlpp11 provide the support for PRIMARY_KEY/INDEX()/DEFAULT()/UNIQUE/COMMENT()/CHARACTER_SET() ?

from sqlpp11.

As of now, these are irrelevant for sqlpp11.

from sqlpp11.

BTW: You might want to get in touch with Andreas: https://github.com/arBmind

He is also thinking about ways to construct and migrate tables (if the required scheme has changed since the table has been created).

from sqlpp11.

Oh, one more thing: Please use the development version. The representation of tables has changed a bit.

This is quite rare, but sometimes necessary. In this case, it provides substantially better control over names of columns.

I am planning a release soon (hopefully within the next two weeks).

from sqlpp11.

Ok, thanks.

from sqlpp11.

Initial implementation you cat see here:
https://github.com/niXman/sqlpp11/commit/1820e9cf79fc83f89d08ede1f7c49809ac01d2f6

Now I still have the traits generation to make.

from this PP code:

SQLPP_DECLARE_TABLE(
    (table, ENGINE("InnoDB"), CHARACTER_SET("utf-8"))
    ,
    (id, int, NOT_NULL, PRIMARY_KEY, AUTO_INCREMENT)
    (name, varchar(64), NOT_NULL, INDEX("name_index"), DEFAULT("any name"))
    (age, int, NOT_NULL, INDEX("age_index"), UNIQUE, COMMENT("some comments"))
)

this code will be generated: https://gist.github.com/niXman/fb6013e9b96ee1cfb91e

from sqlpp11.

And yes, the PRIMARY_KEY/INDEX()/DEFAULT()/UNIQUE/COMMENT()/CHARACTER_SET() will be removed, yet.

from sqlpp11.

You, you're getting pretty close already.

Instead of removing, you might want to keep that extra information for the create table method, too.

from sqlpp11.

You, you're getting pretty close already.

Yes, when I completed the implementation without the support for tables creation, I hope that you will accept my PR :)

Instead of removing, you might want to keep that extra information for the create table method, too.

It's a good idea...

from sqlpp11.

Probably yes, a preprocessor version of the code generator has been on the wish list by several people :-)

from sqlpp11.

This is really impressive!

from sqlpp11.

Thank you!
I have not finished yet...

from sqlpp11.

Nice solution.

I still don't like macros. But I see that meta-programming in C++ is too far off the road. Too complicated and also not maintainable. So macros are easy and sane route to go, for today.

If you want to do it right, I would suggest writing a little code generator. They have the big advantage that you can design your error checks and error messages in a way that really helps users.

from sqlpp11.

I really like the macros, and I often use macros to generate a really complex code, as can be seen by viewing such my projects:
https://github.com/niXman/yarmi
https://github.com/niXman/config-ctor
https://github.com/niXman/yal
https://github.com/niXman/enum-gen
https://github.com/niXman/throw
https://github.com/niXman/static_if

At the moment I'm interested only in the preprocessor generator.

from sqlpp11.

Well, that's the nice thing about open source projects: People can contribute stuff they are interested and or specialized in and others can add other things. A preprocessor generator for tables is certainly something that's been asked for several times and also something that I would not have written in any reasonable amount of time (my greatest preprocessor-based piece of code is very similar to https://github.com/niXman/enum-gen and it took me longer than I am willing to admit ;-) ).

Thus, I really appreciate your effort, looking forward to the next results!

from sqlpp11.

Done: #24

from sqlpp11.

So is there a way to use this new functionality to create the declared table in the DB?

from sqlpp11.

sqlpp11 has no way of creating tables in the DB.

I am always using the DDL as the base for my C++ code as I have long-lived databases. Creating the tables therefore is not happening in any of my code.

But it might make sense for others to create tables from the C++ code, of course.

I'd be happy to look into pull requests :-) I think you need both generic code in sqlpp11 and specific code in the connectors.

from sqlpp11.

I was hoping the commit by @niXman had the functionality in the original post at the top, but alas it doesn't. I ended up sticking with the DDL approach. The preprocessor generation seems to boil down to an alternate way to define my table structs instead of having a separate script and a DDL file, which would be fine, except to create the table, I need something like the DDL anyway.

In my case, I'm creating a log database, which may or may not exist when I start up. I use the DDL file as a checked-in schema for my DB. I use that to generate the table types by using the ddl2cpp script. Then I wrote my own little script to create a simple header file with the create table command from the DDL file embedded in it:

// Generated by gen_create_table_cmd.sh
#pragma once
namespace
{
    const auto s_createTableIfNotExistsCmd = R"(
        create table if not exists logEntry(
          id        integer      primary key autoincrement,
          timeStamp datetime     not null,
          type      text         not null,
          name      text         not null,
          value     text )
    )";
} // namespace

Note that in the script to generate the header I also change "create table" to "create table if not exists", which is valid for recent versions of SQLite and does no harm if the log already exists. Then I include that header where I want to create the DB, and call db.execute( s_createTableIfNotExistsCmd ).

from sqlpp11.