Comments (41)
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.
Related Issues (20)
- mingw64, vcpkg, CMake, Project installation cannot find mariadb ? HOT 5
- sqlite connector: execute() silently ignores statements after the first one HOT 7
- how to use binary equal in where HOT 2
- Is there any opentracing support in sqlpp,like jaeger?Will it be supported in the future? HOT 1
- how to fix update exception? HOT 23
- How to retrieve the row count of a query result? HOT 6
- mysql does not seem to support the TIME type HOT 4
- Multiple table query error HOT 3
- Mysql prepared_statement.h missing _bind_time_of_day_parameter function HOT 3
- How do I get record with count of references from one to many? HOT 4
- Handling of `::sqlpp::tag::enforce_null_result_treatment` does not seem to be implemented, NULL documentation is erroneous HOT 11
- scripts/sqlite2cpp.py requires insert of nullable column HOT 6
- [Feedback Requested] Results returning optional, string_view, and span HOT 22
- [requesting assistance] Can the same parameter be used in multiple places in the same prepared statement? HOT 13
- [Need assistance] Common Table Expressions with update HOT 2
- Can't find how to use count(1) HOT 3
- head file include question HOT 3
- how to predict const struct sqlpp::result_row_t is or not NULL? HOT 3
- Supporting the BETWEEN operator? HOT 2
- SQLite: Is order by rowid possible? HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlpp11.