Postgres Keyword SERIAL creates Default But the Default is ignored by Sqlpp23

[SimpleForest]

Summary

Used DB: psql (17.6 (Ubuntu 17.6-1))

In my first steps I realized that I have to explicitly give an integer value to add_values for a column created with the Keyword SERIAL

serial SERIAL PRIMARY KEY

If I dont give a precomputed serial from cpp I get an error.

I expect this to be unwanted behavior, as the SERIAL has a Default given. In this example it is

serial integer NOT NULL DEFAULT nextval('news.article_serial_seq'::regclass)

Code to Reproduce:

Create Database - not minimal but also not complex:

CREATE SCHEMA IF NOT EXISTS news;

CREATE TABLE IF NOT EXISTS news.author (
	serial SERIAL PRIMARY KEY,
	fullname VARCHAR(255) 
);

CREATE TABLE IF NOT EXISTS  news.publisher (
	serial SERIAL PRIMARY KEY,
	fullname VARCHAR(255) UNIQUE
);

CREATE TABLE IF NOT EXISTS  news.journal (
	serial SERIAL PRIMARY KEY,
	publisher_serial INTEGER NOT NULL,
	FOREIGN KEY (publisher_serial) REFERENCES news_collection.publisher(serial),
	fullname VARCHAR(255) UNIQUE
);

CREATE TABLE IF NOT EXISTS  news.article (
	serial SERIAL PRIMARY KEY,
	summary VARCHAR(2055) UNIQUE,
	author_serial INTEGER NOT NULL,
	journal_serial INTEGER NOT NULL,
   	FOREIGN KEY (author_serial) REFERENCES news_collection.author(serial),
	FOREIGN KEY (journal_serial) REFERENCES news_collection.journal (serial),
	url VARCHAR(255),
	title VARCHAR(255),
	article VARCHAR(10000),
	publish_date date
);

INSERT INTO news.publisher (fullname) VALUES ('Zeitverlag Gerd Bucerius GmbH & Co. KG');
INSERT INTO news.journal (fullname, publisher_serial) VALUES('Die Zeit', 1);
INSERT INTO news.author (fullname, publisher_serial) VALUES('Der Investigative Reporter', 1);

Create the db header file with ddl2cpp

pg_dump -U postgres -h localhost -p 5432 -d searchable_news -W -f ~/searchable_news.sql
./ddl2cpp ~/searchable_news.sql ~/news_dbpp.h news

Cpp code chunk, place into ur hello world:

    auto multi_insert2 = insert_into(table).columns(table.url, table.title, table.summary, table.authorSerial, table.journalSerial);
    multi_insert2.add_values(
                           table.url = "abc1",
                           table.title = "abc1",
                           table.summary = "abc1  asdyxc",
                           table.authorSerial = 1,
                           table.journalSerial = 1);
    multi_insert2.add_values(
        table.url = "abc2",
        table.title = "abc2",
        table.summary = "abc2  asdyxc",
        table.authorSerial = 1,
        table.journalSerial = 1);
    auto& db = sndb::getConnection();
    auto tx = start_transaction(db);
    try
    {
        db(multi_insert2);
        tx.commit();
    }
    catch (...)
    {
        std::cerr << "Something terrible happened here" << std::endl;
        //tx.rollback();
    }
    ```

Error received

/usr/local/include/sqlpp23/core/clause/insert_value_list.h:138: error: static assertion failed: at least one required column is missing in columns()
/usr/local/include/sqlpp23/core/clause/insert_value_list.h:138:19: error: static assertion failed: at least one required column is missing in columns()
  138 |     static_assert(wrong<T...>,
      |                   ^~~~~~~~~~~
/usr/local/include/sqlpp23/core/clause/insert_value_list.h:138:19: note: ‘sqlpp::wrong<>’ evaluates to false

coming from

class assert_all_required_columns_t : public wrapped_static_assert {
 public:
  template <typename... T>
  static void verify(T&&...) {
    static_assert(wrong<T...>,
                        "at least one required column is missing in columns()");
  }
};

Reason

This is because the serial has in the generated header definition

    struct Serial : public ::sqlpp::name_tag_base {
      SQLPP_CREATE_NAME_TAG_FOR_SQL_AND_CPP(serial, serial);
      using data_type = ::sqlpp::integral;
      using has_default = std::true_type;
    };   
    

    using _required_insert_columns = sqlpp::detail::type_set<
               sqlpp::column_t<sqlpp::table_t<NewsArticle_>, Serial>,
               sqlpp::column_t<sqlpp::table_t<NewsArticle_>, AuthorSerial>,
               sqlpp::column_t<sqlpp::table_t<NewsArticle_>, JournalSerial>>;

Solution

• so using has_default = std::false_type; is set wrongly
• we also do not want the entry sqlpp::column_t<sqlpp::table_t<NewsArticle_>, Serial>, in the required_insert_columns construction.

I tested the solution manually. I did not dive into adapting the ddl2cpp.

[SimpleForest]

And a big thumbs up for ur great work. Hello!

[rbock]

Thanks for reaching out! And thanks for the flowers :-)

However, I am not sure how to reproduce? I copied your table definitions into a news.ddl and ran scripts/sqlpp23-ddl2cpp --path-to-ddl news.ddl --path-to-header news.h --namespace test.

I get:

    using _required_insert_columns = sqlpp::detail::type_set<
               sqlpp::column_t<sqlpp::table_t<NewsArticle_>, AuthorSerial>,
               sqlpp::column_t<sqlpp::table_t<NewsArticle_>, JournalSerial>>;

[SimpleForest]

Hello

I will double check on my workspace soon, but it seems you replaced the script since release 0.66 which I use.
https://github.com/rbock/sqlpp23/tree/0.66/scripts
At least the script has another name now.

I write back or close today after double checking with new version of the script.

[MeanSquaredError]

@SimpleForest

I tried initializing a database using the provided ddl script but it failed. I put the ddl into a file called test.ddl, create an empty database searchable_news and then tried loading the provided ddl into the empty database:

root@fedora:~# psql -U postgres -d searchable_news < ./test.ddl 
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
ERROR:  schema "news_collection" does not exist
ERROR:  schema "news_collection" does not exist
INSERT 0 1
ERROR:  relation "news.journal" does not exist
LINE 1: INSERT INTO news.journal (fullname, publisher_serial) VALUES...
                    ^
ERROR:  column "publisher_serial" of relation "author" does not exist
LINE 1: INSERT INTO news.author (fullname, publisher_serial) VALUES(...

So it would probably help, if you could provide a minimal example, that is a .ddl file that we could turn into a .h file using ddl2cpp and also a complete .cpp file that we could try compiling in order to see the error message.

Other than that, it really looks like your ddl2cpp is a one before this commit was added to it. But on the other hand you say that you used ddl2cpp from v0.66, which already has that commit included. So I am a bit at a loss here. Could it be that when you installed sqlpp23 v0.66, the ddl2cpp script actually was not updated on your filesystem and you actually used an older script?

[SimpleForest]

Yes sorry I made a mistake:

CREATE SCHEMA IF NOT EXISTS news;

CREATE TABLE IF NOT EXISTS news.author (
	serial SERIAL PRIMARY KEY,
	fullname VARCHAR(255) 
);

CREATE TABLE IF NOT EXISTS  news.publisher (
	serial SERIAL PRIMARY KEY,
	fullname VARCHAR(255) UNIQUE
);

CREATE TABLE IF NOT EXISTS  news.journal (
	serial SERIAL PRIMARY KEY,
	publisher_serial INTEGER NOT NULL,
	FOREIGN KEY (publisher_serial) REFERENCES news.publisher(serial),
	fullname VARCHAR(255) UNIQUE
);

CREATE TABLE IF NOT EXISTS  news.article (
	serial SERIAL PRIMARY KEY,
	summary VARCHAR(2055) UNIQUE,
	author_serial INTEGER NOT NULL,
	journal_serial INTEGER NOT NULL,
   	FOREIGN KEY (author_serial) REFERENCES news.author(serial),
	FOREIGN KEY (journal_serial) REFERENCES news.journal (serial),
	url VARCHAR(255),
	title VARCHAR(255),
	article VARCHAR(10000),
	publish_date date
);

INSERT INTO news.publisher (fullname) VALUES ('Zeitverlag Gerd Bucerius GmbH & Co. KG');
INSERT INTO news.journal (fullname, publisher_serial) VALUES('Die Zeit', 1);
INSERT INTO news.author (fullname, publisher_serial) VALUES('Der Investigative Reporter', 1);

[SimpleForest]

The above code lines have changed used schema name from news_collection to news. Was still a typo in my documentation.

I can confirm that 0.66 on my disc has the commit included you gave @MeanSquaredError .

Now with:

searchable_news.sql

and I get this header as txt :

newsDbCpp.txt

[SimpleForest]

This issue remains:

  struct NewsCollectionNews_ : public ::sqlpp::name_tag_base {
    struct Serial : public ::sqlpp::name_tag_base {
      SQLPP_CREATE_NAME_TAG_FOR_SQL_AND_CPP(serial, serial);
      using data_type = ::sqlpp::integral;
      using has_default = std::false_type;
    };
    struct Summary : public ::sqlpp::name_tag_base {
.....
    using _required_insert_columns = sqlpp::detail::type_set<
               sqlpp::column_t<sqlpp::table_t<NewsCollectionNews_>, Serial>,
               sqlpp::column_t<sqlpp::table_t<NewsCollectionNews_>, AuthorSerial>,
               sqlpp::column_t<sqlpp::table_t<NewsCollectionNews_>, JournalSerial>>;

[SimpleForest]

I have sqlpp23 build and installed with version 0.66

This runs well with

sqlpp23-ddl2cpp searchable_news.ddl news_dbpp.h news

But the result has the mentioned issue.

If I run a local checkout of the master with same syntax

~/Documents/workspace/sqlpp23/scripts/sqlpp23-ddl2cpp searchable_news.ddl news_dbpp.h news 

I receive:

r:~$ ~/Documents/workspace/sqlpp23/scripts/sqlpp23-ddl2cpp searchable_news.ddl news_dbpp.h news 
usage: sqlpp23-ddl2cpp [-h] [--path-to-ddl [PATH_TO_DDL ...]] [--namespace NAMESPACE] [--path-to-module PATH_TO_MODULE] [--path-to-header PATH_TO_HEADER] [--path-to-header-directory PATH_TO_HEADER_DIRECTORY] [--path-to-datatype-file PATH_TO_DATATYPE_FILE] [--module-name MODULE_NAME]
                       [--suppress-timestamp-warning] [--assume-auto-id] [--naming-style {camel-case,identity}] [--generate-table-creation-helper] [--use-import-sqlpp23] [--use-import-std] [--self-test]
sqlpp23-ddl2cpp: error: unrecognized arguments: searchable_news.ddl news_dbpp.h news

[MeanSquaredError]

@SimpleForest
The command-line arguments of ddl2cpp have changed recently. So the command-line to build the .h file from the .ddl should be something like this:

sqlpp23-ddl2cpp --path-to-ddl ./searchable_news.sql --namespace news --path-to-header ./news_dbpp.h

Regarding the issue itself, what is causing the problem is that you are using the .ddl to create a database and then dump the database back to a .ddl file. During this roundtrip the serial type (which is not a real type but a pseudotype emulated by the PostgreSQL using sequences) is lost and you get regular int NOT NULL columns instead.
If you take a look at searchable_news.sql that you provided, you will see that the columns called serial no longer have the serial column types and they don't have default values.

[rbock]

@SimpleForest Sorry about causing confusion with the change in ddl2cpp. I actually also tried with 0.66 and it works as it should, if you use the ddl the provided in the original post.

Btw, this is a good reminder that I should release 0.67!

@MeanSquaredError Awesome attention to detail! I admit I totally missed the round trip through the database in the original post.

[rbock]

Tagged 0.67 :-)

@SimpleForest Fingers crossed that things work as they should without the round-trip!

Note that in case you are using cte, you will have to replace it with sqlpp::cte in 0.67. Otherwise, I believe the API remained unchanged, see also /docs/change_log.md.

[MeanSquaredError]

@rbock

Sorry about causing confusion with the change in ddl2cpp.

Actually I think that the new command-line syntax of ddl2cpp is much better than the old one. The old syntax was way too confusing and before using ddl2cpp, I always had to look into the order of the arguments. So no need to be sorry about the change!

It's only that the arguments need to be better documented, but that's something that I should do. I actually started documenting the new arguments' style, but then the new documentation that I wrote did not sound very clear to me, so I put it on hold until I got the inspiration to word the docs in a better way. And then I got sidetracked with other tasks... :-) Anyway, I will try to get the docs finished in the next couple of days.

[SimpleForest]

Hello

Statements in #59 (comment) are correct.

But:

Regarding the issue itself, what is causing the problem is that you are using the .ddl to create a database and then dump the database back to a .ddl file.

I think this is kind of the normal case for many postgresql users. They use databases which they dont created themselves and they have to use pg_dump as I did. I only had the handwritten SQL Statements which I posted in this topic`s origin because this was kind of an hello world project.

So I expect that I am not the only one running into this trap here.

---

Lets cite:

https://neon.com/postgresql/postgresql-tutorial/postgresql-serial

Behind the scenes, the following statement:

CREATE TABLE table_name(
    id SERIAL
);

is equivalent to the following statements:

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;

---

unfortunately this is not exactly how my pgdump case behaves

CREATE TABLE news.article (
    serial integer NOT NULL,
    summary character varying(2055),
    author_serial integer NOT NULL,
    journal_serial integer NOT NULL,
    url character varying(255),
    title character varying(255),
    article character varying(10000),
    publish_date date
);


ALTER TABLE news.article OWNER TO postgres;

--
-- Name: article_serial_seq; Type: SEQUENCE; Schema: news; Owner: postgres
--

CREATE SEQUENCE news.article_serial_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE news.article_serial_seq OWNER TO postgres;

---

So If you agree on my opinion that it is worth preparing your script also for users who have to rely on pg_dump to create those input files you might want to treat a case

CREATE TABLE table (
    column integer NOT NULL
);

differently and not tag as NO DEFAULT

if you can parse as well those two SQL chunks

CREATE SEQUENCE table_column_seq
    AS integer * (I Ithink this is enough to parse for)


ALTER SEQUENCE news.article_serial_seq OWNER TO *;

Then you might want to Tag them as NOT NULL DEFAULT - with nextval('table_name_id_seq'). in mind.

[rbock]

So I expect that I am not the only one running into this trap here.

You are certainly the first to report it here or in sqlpp11 :-)

But agreed, it would be nice to be able to parse these sequence statements!

I'd be happy to review and accept a PR (I currently don't have the cycles to make the change myself.).