Supported dumps about sqlpp11 HOT 41 CLOSED

Hi Matthijs,

Good question. I've tested with MySQL/MariaDB/Sqlite. The script should support PostgreSQL as well, but I haven't tested with PostgreSQL yet and not all data types are supported yet. For instance, there is no support for BLOB yet.

I have concentrated on the syntax so far. Additional datatypes should be easy to add.

Would be great if you could extend the script to support your dump as well (alternatively you could send me some examples of what does not work yet).

Thanks and regards,

Roland

from sqlpp11.

Hi Roland,

Thank you for your quick response.

Attaching a dump from one specific table won't work in github, I'll paste it below.

As you can see PostgreSQL produces a CREATE TABLE statement and after inserting data the primary key and foreign keys are applied using an ALTER TABLE.

Let me see if I can come up with something for this script.

Regards, Matthijs

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: market; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE market (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    time_open time without time zone NOT NULL,
    time_close time without time zone NOT NULL
);


ALTER TABLE public.market OWNER TO postgres;

--
-- Name: market_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE market_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.market_id_seq OWNER TO postgres;

--
-- Name: market_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE market_id_seq OWNED BY market.id;


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY market ALTER COLUMN id SET DEFAULT nextval('market_id_seq'::regclass);


--
-- Name: market_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY market
    ADD CONSTRAINT market_pkey PRIMARY KEY (id);


--
-- Name: market_id_key; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
--

CREATE UNIQUE INDEX market_id_key ON market USING btree (id);


--
-- PostgreSQL database dump complete
--

from sqlpp11.

Hi Matthijs,

Thanks for the example. The current script ignores those statements outside the CREATE TABLE.

It is going to be interesting if there can be a single script for all databases? I could imagine that each connector library will get a script which is specific for the individual database.

BTW: I don't know if the connector for postgreSQL is being maintained. If you want to work on that, you might want to work with the develop branch of sqlpp11.

Regards,

Roland

from sqlpp11.

Hi Roland,

Probably a good idea to have a script per database.

I just cloned the sqlpp11-connector-postgresql from https://github.com/pbondo/sqlpp11-connector-postgresql but I get compile errors.

The header sqlpp11/raw_result_row.h could not be found. I guess that the interface has changed. Let me see if I can work out something for PostgreSQL.

I will check the develop branch.

Question, do you have plans to submit this library to boost ?

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

The compile errors were to be expected, since that connector was written by Paul Bondo when I first queried interest on the boost mailing list.

http://lists.boost.org/Archives/boost/2013/11/208388.php
http://lists.boost.org/Archives/boost/2014/02/211206.php

There is considerable interest at boost, and I plan to propose it. I am also thinking of proposing for the C++ standard, but both will take some more time. For example, I am thinking about moving the char_result_row (former raw_result_row) and the related conversions out of the main library into the connectors.

At the moment, the connectors for mysql and sqlite also fail to compile with the develop branch due to some renaming and moving around of stuff. I'll try to correct that today. There are also tons of warnings that I need to take care of (those will take a bit longer).

Regards,

Roland

from sqlpp11.

Hope you're not investing too much time in this right now. I need to clean up a few things. This might take a few more hours.

from sqlpp11.

Well, I was planning to write the postgresql connector myself from scratch, but I can wait a few more hours. ;)

from sqlpp11.

Hi,

I just pushed sqlpp11 (develop) and sqlpp11-connector-[mysql|sqlite3](both develop). The changes in the connectors are quite small. There are still a dozen or two warnings to be taken care of, but it compiles and runs again.

I am not sure what the easiest approach is with postgreSQL, but if you have a choice, I'd recommend using the binary interface. The sqlite3 connector would then probably be the better example to look at.

Sorry for the slightly chaotic current state of affairs. I am about the finish a major overhaul of the internals of sqlpp11 which have at least some impact on the connectors. I therefore did not want you to waste efforts on the current master branch.

I expect a few more hickups, but nothing major.

Cheers,

Roland

from sqlpp11.

Hi,

Thank you for the suggestion, but what is the advantage of using the binary interface vs the other interface?

No problem, major overhauls will always take time. And this is for a fun project of mine so it doesn't need to be finished yesterday. It also gives me the advantage to learn some more about generic programming in C++.

Regards, Matthijs

from sqlpp11.

Hi,

just to be clear: I was talking about the results of a SELECT.

I think I will change the way char_result_row is handled. Thus, if you use it, you are more likely to experience an interface change in the future.

Regards,

Roland

from sqlpp11.

Thanks for the info, at the moment I get a compile error:

In file included from ../sqlpp11/include/sqlpp11/insert.h:36:0,
                 from ../sqlpp11/include/sqlpp11/sqlpp11.h:32,
                 from main.cpp:5:
../sqlpp11/include/sqlpp11/into.h:32:29: fatal error: sqlpp11/no_data.h: No such file or directory
 #include <sqlpp11/no_data.h>

from sqlpp11.

Oops, added the missing file

from sqlpp11.

Sorry to bother you but I still get a compile error while having the following program:

#include <iostream>

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

int main(int argc, char **argv) {

    return 0;
}

Following compile error:

In file included from ../sqlpp11/include/sqlpp11/remove.h:37:0,
                 from ../sqlpp11/include/sqlpp11/sqlpp11.h:33,
                 from main.cpp:5:
../sqlpp11/include/sqlpp11/extra_tables.h:63:62: error: 'using _recursive_traits = struct sqlpp::make_recursive_traits<Columns ...>' conflicts with a previous declaration
    using _recursive_traits = make_recursive_traits<Tables...>;
                                                              ^
../sqlpp11/include/sqlpp11/extra_tables.h:55:11: note: previous declaration 'struct sqlpp::extra_tables_t<Tables>::_recursive_traits'
    struct _recursive_traits
           ^

from sqlpp11.

On 2014-06-22 13:44, matthijs wrote:

Sorry to bother you but I still get a compile error while having the
following program:

#include

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

int main(int argc, char **argv) {

return 0;

}

Fixed that and added a minimalistic test program like your's :-)

Don't worry about bothering me. I am thankful for the feedback and your
patience!

Regards,

Roland

from sqlpp11.

I still can't compile the minimalistic example, did you perhaps forgot to commit something?

Regards, Matthijs

from sqlpp11.

On 2014-06-22 22:23, matthijs wrote:

I still can't compile the minimalistic example, did you perhaps forgot
to commit something?

Regards, Matthijs

Huh, I had tested with clang-3.2 on Ubuntu 12.04 before submitting. I
retested with gcc-4.8 now and got a compile error as well.

Fixed and pushed the code to work with gcc-4.8 now, too. I think that
g++ is correct in two cases and wrong in one (without being a specialist
on the fine print of the standard, though :-) ).

Which compiler and OS are you using, btw?

Regards,

Roland

from sqlpp11.

I am on Debian unstable with compiler gcc version 4.9.

Regards, Matthijs

from sqlpp11.

And I hope the examples from sqlpp11 compile in your settings as well now?

Best,

Roland

from sqlpp11.

Yes, it does!

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

took care of the warnings. This should make things easier for you as
well :-)

Regards,

Roland

from sqlpp11.

Hi Roland,

Thanks, that makes it easier yes!

Regards, Matthijs

from sqlpp11.

Hi Roland,

Did you compile with -Wall ? I still see some warnings:

In file included from ../sqlpp11/include/sqlpp11/select_column_list.h:37:0,
                 from ../sqlpp11/include/sqlpp11/select.h:34,
                 from ../sqlpp11/include/sqlpp11/sqlpp11.h:35,
                 from main.cpp:5:
../sqlpp11/include/sqlpp11/named_interpretable.h: In member function 'std::string sqlpp::named_interpretable_t<Db>::_get_name() const':
../sqlpp11/include/sqlpp11/named_interpretable.h:75:4: warning: no return statement in function returning non-void [-Wreturn-type]
    }
    ^
In file included from ../sqlpp11/include/sqlpp11/sqlpp11.h:36:0,
                 from main.cpp:5:
../sqlpp11/include/sqlpp11/functions.h: In function 'sqlpp::wrap_operand_t<T> sqlpp::value(T)':
../sqlpp11/include/sqlpp11/functions.h:51:47: warning: typedef 'using _provided_tables = struct sqlpp::detail::type_set<>' locally defined but not used [-Wunused-local-typedefs]
    using _provided_tables = detail::type_set<>;
                                               ^
../sqlpp11/include/sqlpp11/functions.h:52:56: warning: typedef 'using _required_tables = struct sqlpp::detail::type_set<>' locally defined but not used [-Wunused-local-typedefs]
    using _required_tables = ::sqlpp::detail::type_set<>;

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

I meant the hand-crafted warnings like

#warning: Check for duplicate table names here

I added -Wall to the CMakeLists now and cleaned up the warnings from the
library, thanks for the hint!

There are still some warnings from unused variables in the test
programs, I will remove those later.

Regards,

Roland

from sqlpp11.

Hi Roland,

Yes! I got the SELECT working with PostgreSQL!

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

congratulations! Very good news!

Cheers,

Roland

On 2014-06-28 20:31, matthijs wrote:

Hi Roland,

Yes! I got the SELECT working with PostgreSQL!

Regards, Matthijs

—
Reply to this email directly or view it on GitHub
#4 (comment).

from sqlpp11.

Hi Matthijs,

As written a few days ago, I changed the usage of the char_result_row. This is now removed from the sqlpp11 library. The translation of char-based results to numbers, bools, etc is now the job of the connector library. The reason is that the char_result_row and the conversions are vendor specific. Thus, they should not be in the core library.

If you are using the char_result_row, please take a look at the mysql connector. It is quite simple to switch to the new API:

https://github.com/rbock/sqlpp11-connector-mysql/blob/develop/include/sqlpp11/mysql/char_result.h

Regards,

Roland

from sqlpp11.

Hi Roland,

I am not using the char_result_row at all, so no need to switch to something else. I will push my code to github as soon as I am back home.

One question, in PostgreSQL you can have named prepared statements. There are at least two possible options for prepared statements in PostgreSQL:

• unnamed prepared statements, those are automatically deallocated when replacing with another prepared statement.
• named prepared statements, those are not automatically deallocated but these are needed if you want to create more then one prepared statement within the same session.

Would it be possible to add an extra argument to the prepare call in connection.h:

template<typename T>
auto prepare(const T &t) -> decltype(t._prepare(*this)) {
    return t._prepare(*this);
}

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

Ok, great. You will still need to rename calls to validate() and
invalidate(), I think. I added a leading underscore to indicate that
these methods should normally not be called by the library user, but for
instance by the connector library.

As for the name for prepared statements: That should be possible, of
course. Can you give me a link to the respective documentation at
postgreSQL as well? I'll look into it beginning of next week.

Regards,

Roland

from sqlpp11.

Hi Roland,

Here you can find the connector: https://github.com/matthijs/sqlpp11-connector-postgresql.git
I still need to fix the prepared statements, statements without parameters will be executed correctly but binding arguments won't work yet.

And here is the documentation to the PostgreSQL library http://www.postgresql.org/docs/9.3/interactive/libpq-exec.html

Search for the two functions:

• PQprepare
• PQexecPrepared

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

Cool! Thanks for the update!

BTW: I released the current status as version 0.16 this morning as I am
through with the big overhaul. Upcoming changes will be comparatively
small, I think.

I'll look into the prepare variants next.

Best regards,

Roland

from sqlpp11.

Hi Matthijs,

After reading the documentation, I suggest you generate a name which is
guaranteed to be unique in the current session instead of letting the
user provide a name. This way, when the library user prepares a
statement, no bad surprises can happen and there is no different
behavior for names with length zero.

Also, when you deallocate the prepared statement in the destructor, you
can be sure not to kill something you did not intend to (in case of
empty names being used).

What do you think?

Regards,

Roland

from sqlpp11.

Hi Roland,

I think you are correct, an instantiation of a prepared statement is enough to distinguish between two prepared statements. I agree with that.

For deallocation of a prepared statement I need the database handle, PostgreSQL doesn't have a specific prepared statement handle. It should be doable.

I will finish the prepared statement implementation this evening.

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

Quite impressive. I'll add a link to your repository soon.

Regards,

Roland

from sqlpp11.

Hi Roland,

I am finishing the implementation of the prepared statements with the PostgreSQL connector, but I now have some troubles with the interpreter interface.

I have implemented it like this (I got that example from the connector_api/ in the sqlpp11 repository):

namespace sqlpp {

    template<typename ValueType, typename NameType>
        struct interpreter_t<postgresql::context_t, parameter_t<ValueType, NameType>> {
            using T = parameter_t<ValueType, NameType>;

            static postgresql::context_t &_(const T &t, postgresql::context_t &context) {
                context << "$" << context.count();
                context.pop_count();
                return context;
            }
        };
}

But when compiling a test program with a prepared statement it will not enter this code (I still see something like: .. WHERE id = ?

I am probably missing a part somewhere so that it is going to use this template, but I am lost here...

I have committed my attempt to make this work.

Regards, Matthijs

from sqlpp11.

Hi Roland,

Ah got it, I changed interpreter_t to serializer_t, probably a misunderstanding, can you explain some more about interpreter_t and serializer_t ?

And I added the following definitions to the connection:

using _serializer_context_t = _context_t;
using _interpreter_context_t = _context_t;

Regards, Matthijs

from sqlpp11.

Hi Matthijs

The idea is to have an interpreter to translate a query expression into
some other expression and a serializer to serialize the query. For
string based backends that is the same. But for example the
sqlpp-connector-stl has a very different interpreter.

Admittedly this is one of the areas that I consider a bit fuzzy myself ;-)

Regards,

Roland

from sqlpp11.

Hi Roland,

Thank you for the explanation, that helped in understanding.

Btw, I have finished the prepared statement implementation, that is now working correctly. I still need to create testcases for the connector to make sure that everything works as expected.

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

Nice to read that! Have you also created a script for parsing DDL dumps?

BTW: I added a link to your repository a few days ago :-)

Best,

Roland

from sqlpp11.

Hi Roland,

I have looked into a few libraries for parsing the DDL dumps, but that's not that easy to do. So I now let my script connect to the database and read all the information from the information_schema database. Much easier to process.

I am almost finished with the script and after I tested it, I will commit of course.

For your information, MySQL has also an information_schema database and you can easily fetch the relevant information from there too.

Thanks for adding the link to my repository.

Regards, Matthijs

from sqlpp11.

Hi Matthijs,

Nice approach for writing code for existing databases :-)

In our team we keep handwritten DDLs and patches in the git
repositories. We use the DDLs to generate code and the patches to
migrate the live databases. We do not use dumps. That is why the current
script is as it is.

It's nice to see your repository develop :-)

Regards,

Roland

from sqlpp11.

Hi Roland,

Ah yes, that's an approach too.

I've added my script to the repository. As this ticket is now resolved, I'll close it.

Thanks

Regards, Matthijs

from sqlpp11.