Last active
July 20, 2018 23:42
-
-
Save falgon/2ade37d9ceb914f2c0dbd16c6271d98a to your computer and use it in GitHub Desktop.
Connecting mysql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| version: '3.2' | |
| services: | |
| mysql: | |
| container_name: docker-mysql | |
| image: mysql:5.7 | |
| restart: always | |
| volumes: | |
| - ./conf:/etc/mysql/conf.d | |
| - ./logs:/var/log/mysql | |
| - ./init:/docker-entrypoint-initdb.d | |
| environment: | |
| MYSQL_ROOT_PASSWORD: rootpasswd | |
| MYSQL_DATABASE: sample | |
| MYSQL_USER: test | |
| MYSQL_PASSWORD: passwd | |
| TZ: Asia/Tokyo | |
| ports: | |
| - 3306:3306 | |
| pma: | |
| container_name: docker-pma | |
| image: phpmyadmin/phpmyadmin:latest | |
| restart: always | |
| environment: | |
| PMA_USER: root | |
| PMA_PASSWORD: rootpasswd | |
| ports: | |
| - 8080:80 | |
| links: | |
| - mysql:db |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #include <array> | |
| #include <iostream> | |
| #include <srook/scope/unique_resource.hpp> | |
| #include "sql_executor.hpp" | |
| int main() | |
| { | |
| playing::cppconn::sql_executor executor("tcp://127.0.0.1:3306", "test", "passwd", "test_db"); | |
| { | |
| auto commit = srook::scope::make_unique_resource(&executor, [](playing::cppconn::sql_executor* r) { | |
| r->do_mutation("COMMIT"); | |
| }); | |
| const std::array<std::string, 5> init {{ | |
| "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\"", | |
| "SET AUTOCOMMIT = 0", | |
| "START TRANSACTION", | |
| "SET time_zone = \"+00:00\"", | |
| "CREATE TABLE IF NOT EXISTS customers (" | |
| " customer_no varchar(16) NOT NULL COMMENT '顧客番号'," | |
| " name varchar(64) NOT NULL COMMENT '氏名'," | |
| " zipcode char(8) NOT NULL COMMENT '郵便番号'," | |
| " address varchar(64) NOT NULL COMMENT '住所'," | |
| " tel_no varchar(16) NOT NULL COMMENT '電話番号'" | |
| ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顧客マスタ'", | |
| }}; | |
| std::string insert = "INSERT IGNORE INTO customers VALUES "; | |
| // These are dummy data that generated by faker (https://github.com/joke2k/faker). | |
| const std::array<std::string, 6> set_table {{ | |
| insert + "('C001', '青山 花子', '958-3626', '大分県横浜市瀬谷区台場12丁目23番18号 勝どきコーポ435', '090-3537-6380')", | |
| insert + "('C002', '廣川 翔太', '297-3630', '群馬県西多摩郡奥多摩町平須賀14丁目5番2号 コーポ台東850', '88-1940-6921')", | |
| insert + "('C003', '田辺 浩', '596-4390', '大分県いすみ市虎ノ門虎ノ門ヒルズ森タワー31丁目11番1号', '080-4923-6200')", | |
| insert + "('C004', '井上 知実', '903-5859', '岩手県北区箪笥町7丁目6番2号 高田馬場クレスト753', '090-1267-5646')", | |
| insert + "('C005', '浜田 明美', '644-0375', '北海道武蔵野市蟇沼41丁目6番4号 パレス南赤田445', '090-3897-3724')", | |
| "ALTER TABLE customers ADD PRIMARY KEY (customer_no)" | |
| }}; | |
| executor.do_mutation(init); | |
| executor.do_mutation(set_table); | |
| } | |
| executor.do_select("SELECT * FROM customers", [](playing::cppconn::sql_executor::result_ptr_type ptr) { | |
| do { | |
| std::cout << | |
| "|顧客番号| " << ptr->getString("customer_no") << | |
| "\t|氏名| " << ptr->getString("name") << | |
| "\t|郵便番号| " << ptr->getString("zipcode") << | |
| "\t|住所| " << ptr->getString("address") << | |
| "\t|電話番号| " << ptr->getString("tel_no") << std::endl; | |
| } while (ptr->next()); | |
| }); | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Dependencies | |
| # - Connector/C++: https://dev.mysql.com/downloads/connector/cpp/8.0.html | |
| # - Boost C++ Libraries 1.67.0: https://sourceforge.net/projects/boost/files/boost/1.67.0/ | |
| # - Srook C++ Libraries: https://github.com/falgon/SrookCppLibraries | |
| CXX:=clang++ -stdlib=libc++ | |
| CXXFLAGS:=-std=c++11 | |
| CPPFLAGS:=-Wall -Wextra -pedantic -I/usr/local/include/ -I./ `pkg-config mysqlclient --cflags --libs` | |
| LDFLAGS:=-L/usr/local/opt/mysql-client/lib | |
| LDLIBS:=-lmysqlcppconn | |
| DFLAGS:=-DBUILD_STATIC=false\ | |
| -DCMAKE_BUILD_TYPE=Debug\ | |
| -DWITH_BOOST=/usr/local/Cellar/boost/1.67.0_1/include/\ | |
| -DMYSQL_CONFIG_EXECUTABLE=/usr/local/opt/mysql-client/bin/mysql_config | |
| SRC:=$(wildcard *.cpp) | |
| EXE:=$(SRC:.cpp=) | |
| all:$(EXE) | |
| $(EXE):$(SRC) | |
| $(CXX) $< $(LDFLAGS) $(LDLIBS) $(CPPFLAGS) $(CXXFLAGS) $(DFLAGS) -o $@ | |
| run:$(SRC) | |
| ./$(EXE) | |
| clean: | |
| @$(RM) $(EXE) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #ifndef INCLUDED_PLAYING_CPPCONN_SQL_EXECUTOR_HPP | |
| #define INCLUDED_PLAYING_CPPCONN_SQL_EXECUTOR_HPP | |
| #include <srook/config/feature.hpp> | |
| #include <srook/config/attribute.hpp> | |
| #include <srook/type_traits/is_range.hpp> | |
| #include <srook/type_traits/decay.hpp> | |
| #include <srook/type_traits/conjunction.hpp> | |
| #include <srook/type_traits/is_constructible.hpp> | |
| #include <srook/type_traits/iterator/is_forwarditerator.hpp> | |
| #include <srook/type_traits/is_invocable.hpp> | |
| #include <srook/string/string_view.hpp> | |
| #include <srook/functional/invoke.hpp> | |
| #include <srook/iterator/range_iterators/range_iterator.hpp> | |
| #include <srook/iterator/range_access/begin_end.hpp> | |
| #include <srook/utility/forward.hpp> | |
| #include <mysql_driver.h> | |
| #include <mysql_connection.h> | |
| #include <mysql_error.h> | |
| #include <cppconn/Statement.h> | |
| #include <cppconn/ResultSet.h> | |
| #include <iostream> | |
| #include <memory> | |
| SROOK_NESTED_NAMESPACE(playing, cppconn) { | |
| class sql_executor { | |
| public: | |
| typedef std::shared_ptr<sql::ResultSet> result_ptr_type; | |
| SROOK_FORCE_INLINE | |
| sql_executor( | |
| const char* host, | |
| const char* user, | |
| const char* passwd, | |
| const char* dbname, | |
| std::ostream& err = std::cerr, | |
| const char* charcode = "utf8", | |
| const char* collate = "utf8_general_ci") SROOK_NOEXCEPT_TRUE | |
| : dbname_(dbname), connection_{}, statement_{}, is_valid_(false), cerr_(err) | |
| { | |
| try { | |
| sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance(); | |
| connection_.reset(driver->connect(host, user, passwd)); | |
| statement_.reset(connection_->createStatement()); | |
| statement_->execute(std::string("CREATE DATABASE IF NOT EXISTS ") + dbname + | |
| " DEFAULT CHARACTER SET " + charcode + " COLLATE " + collate); | |
| statement_->execute(std::string("USE ") + dbname); | |
| is_valid_ = true; | |
| } catch (const sql::SQLException& e) { | |
| out_exception(e); | |
| } catch (const std::exception& e) { | |
| out_exception(e); | |
| } | |
| } | |
| SROOK_FORCE_INLINE bool do_mutation(const char* s) | |
| { | |
| return execute(s); | |
| } | |
| template <class SQLStatement, | |
| SROOK_REQUIRES(srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<SQLStatement>::type>::value)> | |
| SROOK_FORCE_INLINE bool do_mutation(SQLStatement&& s) | |
| { | |
| return execute(s.data()); | |
| } | |
| template <class Range, | |
| SROOK_REQUIRES( | |
| srook::conjunction< | |
| srook::is_range<SROOK_DEDUCED_TYPENAME srook::decay<Range>::type>, | |
| srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<Range>::type::value_type> | |
| >::value | |
| )> | |
| SROOK_FORCE_INLINE bool do_mutation(Range&& range) | |
| { | |
| for (auto&& val : srook::forward<Range>(range)) { | |
| if (!do_mutation(val)) break; | |
| } | |
| return is_valid(); | |
| } | |
| template <class ForwardIterator, | |
| SROOK_REQUIRES(srook::is_forwarditerator<SROOK_DEDUCED_TYPENAME srook::decay<ForwardIterator>::type>::value)> | |
| SROOK_FORCE_INLINE bool do_mutation(ForwardIterator&& first, ForwardIterator&& last) | |
| { | |
| return do_mutation(srook::range::iterator::make_range_iterator(srook::forward<ForwardIterator>(first), srook::forward<ForwardIterator>(last))); | |
| } | |
| template <class F, | |
| SROOK_REQUIRES(srook::is_invocable<SROOK_DEDUCED_TYPENAME srook::decay<F>::type, result_ptr_type>::value)> | |
| SROOK_FORCE_INLINE bool do_select(const char* s, F&& fn) | |
| { | |
| return select(s, srook::forward<F>(fn)); | |
| } | |
| template <class SQLStatement, class F, | |
| SROOK_REQUIRES( | |
| srook::conjunction< | |
| srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<SQLStatement>::type>, | |
| srook::is_invocable<SROOK_DEDUCED_TYPENAME srook::decay<F>::type, result_ptr_type> | |
| >::value | |
| )> | |
| SROOK_FORCE_INLINE bool do_select(SQLStatement&& s, F&& fn) | |
| { | |
| return select(s.data(), srook::forward<F>(fn)); | |
| } | |
| template <class Range, class F, | |
| SROOK_REQUIRES( | |
| srook::conjunction< | |
| srook::is_invocable<SROOK_DEDUCED_TYPENAME srook::decay<F>::type, result_ptr_type>, | |
| srook::is_range<SROOK_DEDUCED_TYPENAME srook::decay<Range>::type>, | |
| srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<Range>::type::value_type> | |
| >::value | |
| )> | |
| SROOK_FORCE_INLINE bool do_select(Range&& range, F&& fn) | |
| { | |
| for (auto&& val : srook::forward<Range>(range)) { | |
| if (!do_select(val, fn)) break; | |
| } | |
| return is_valid(); | |
| } | |
| SROOK_CONSTEXPR bool is_valid() const SROOK_NOEXCEPT_TRUE | |
| { | |
| return is_valid_; | |
| } | |
| private: | |
| SROOK_FORCE_INLINE bool execute(const char* s) | |
| { | |
| if (is_valid()) { | |
| try { | |
| statement_->execute(s); | |
| } catch (const sql::SQLException& e) { | |
| out_exception(e); | |
| } catch (const std::exception& e) { | |
| out_exception(e); | |
| } | |
| } | |
| return is_valid(); | |
| } | |
| template <class F> | |
| SROOK_FORCE_INLINE bool select(const char* s, F&& fn) | |
| { | |
| if (is_valid()) { | |
| try { | |
| for (result_ptr_type res(statement_->executeQuery(s)); res->next();) { | |
| srook::invoke(srook::forward<F>(fn), res); | |
| } | |
| } catch (const sql::SQLException& e) { | |
| out_exception(e); | |
| } catch (const std::exception& e) { | |
| out_exception(e); | |
| } | |
| } | |
| return is_valid(); | |
| } | |
| SROOK_FORCE_INLINE void out_exception(const sql::SQLException& e) | |
| { | |
| out_exception(static_cast<std::exception>(e)); | |
| cerr_ << "My SQL error code: " << e.getErrorCode() << '\n' << | |
| "SQLState: " << e.getSQLState() << '\n'; | |
| } | |
| SROOK_FORCE_INLINE void out_exception(const std::exception& e) | |
| { | |
| is_valid_ = false; | |
| cerr_ << e.what() << '\n'; | |
| } | |
| private: | |
| std::string dbname_; | |
| std::unique_ptr<sql::Connection> connection_; | |
| std::unique_ptr<sql::Statement> statement_; | |
| bool is_valid_; | |
| std::ostream& cerr_; | |
| }; | |
| } SROOK_NESTED_NAMESPACE_END(cppconn, playing) | |
| #endif |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment