# HG changeset patch # User David Demelier # Date 1506078770 -7200 # Node ID 24ec19139f08da425921348ca4b3035beeef40a5 # Parent bc3257f515cc83c76d08dbfdcba001e0fb6dc780 Server: implement SQLite support, closes #687 diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/CMakeLists.txt --- a/libdb-sqlite/CMakeLists.txt Fri Sep 22 13:10:40 2017 +0200 +++ b/libdb-sqlite/CMakeLists.txt Fri Sep 22 13:12:50 2017 +0200 @@ -16,4 +16,28 @@ # OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. # +project(libmlk-db-sqlite) + add_subdirectory(sqlite) + +set( + HEADERS + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/common.hpp + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/sqlite_account.hpp + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/sqlite_database.hpp +) + +set( + SOURCES + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/common.cpp + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/sqlite_account.cpp + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/sqlite_character.cpp + ${libmlk-db-sqlite_SOURCE_DIR}/malikania/server/db/sqlite_database.cpp +) + +malikania_define_library( + TARGET libmlk-db-sqlite + SOURCES ${HEADERS} ${SOURCES} + LIBRARIES libmlk-server sqlite + PUBLIC_INCLUDES ${libmlk-db-sqlite_SOURCE_DIR} +) diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/common.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/common.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,104 @@ +/* + * common.cpp -- common routines for SQLite + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +stmt_ptr query(sqlite3* instance, const std::string& sql, const std::vector& params) +{ + sqlite3_stmt* stmt; + + if (sqlite3_prepare_v2(instance, sql.c_str(), sql.length(), &stmt, nullptr) != SQLITE_OK) + throw std::runtime_error(sqlite3_errmsg(instance)); + + stmt_ptr ptr(stmt, &sqlite3_finalize); + + int index = 1; + for (const auto& p : params) { + switch (p.which()) { + case 0: + sqlite3_bind_text(ptr.get(), index, boost::get(p).c_str(), -1, nullptr); + break; + case 1: + sqlite3_bind_int(ptr.get(), index, boost::get(p)); + break; + case 2: + sqlite3_bind_int(ptr.get(), index, boost::get(p)); + break; + case 3: + sqlite3_bind_int(ptr.get(), index, boost::get(p)); + break; + case 4: + sqlite3_bind_int(ptr.get(), index, boost::get(p)); + break; + case 5: + sqlite3_bind_int(ptr.get(), index, boost::get(p)); + break; + case 6: + sqlite3_bind_int(ptr.get(), index, boost::get(p)); + break; + case 7: + sqlite3_bind_int64(ptr.get(), index, boost::get(p)); + break; + case 8: + sqlite3_bind_int64(ptr.get(), index, boost::get(p)); + break; + default: + break; + } + index++; + } + + return ptr; +} + +void exec(sqlite3* instance, const std::string& sql, const std::vector& params) +{ + auto ptr = query(instance, sql, params); + auto status = sqlite3_step(ptr.get()); + + switch (status) { + case SQLITE_DONE: + case SQLITE_ROW: + case SQLITE_OK: + return; + default: + throw std::runtime_error(sqlite3_errmsg(instance)); + } +} + +std::int64_t to_int(const stmt_ptr& stmt, int column) +{ + return sqlite3_column_int64(stmt.get(), column); +} + +std::string to_string(const stmt_ptr& stmt, int column) +{ + auto ptr = reinterpret_cast(sqlite3_column_text(stmt.get(), column)); + + return ptr ? ptr : ""; +} + +} // !server + +} // !mlk diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/common.hpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/common.hpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,102 @@ +/* + * common.hpp -- common routines for SQLite + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#ifndef MALIKANIA_SQLITE_COMMON_HPP +#define MALIKANIA_SQLITE_COMMON_HPP + +#include +#include + +#include "sqlite3.h" + +namespace mlk { + +namespace server { + +#if 0 +/** + * Wrap sqlite3 instance. + */ +using sqlite_ptr = std::shared_ptr; +#endif + +/** + * Wrap sqlite3_stmt pointer. + * + * Uses sqlite3_finalize function. + */ +using stmt_ptr = std::unique_ptr; + +/** + * Generic parameter for exec. + */ +using parameter = boost::variant< + std::string, + std::int8_t, + std::uint8_t, + std::int16_t, + std::uint16_t, + std::int32_t, + std::uint32_t, + std::int64_t, + std::uint64_t +>; + +/** + * Execute a SQL command that should produce a result. + * + * \param instance the sqlite3 instance + * \param sql the SQL command + * \param params the optional parameters + * \return the sqlite3_stmt result + * \throw std::runtime_error on error + */ +stmt_ptr query(sqlite3* instance, + const std::string& sql, + const std::vector& params = {}); + +/** + * Wrapper to execute some SQLite command with no result. + */ +void exec(sqlite3* instance, + const std::string& sql, + const std::vector& params = {}); + +/** + * Get the specified column as 64 bits integer. + * + * \param stmt the statement + * \param column the column index + * \return the int + */ +std::int64_t to_int(const stmt_ptr& stmt, int column); + +/** + * Get the specified column as string. + * + * \param stmt the statement + * \param column the column index + * \return the string + */ +std::string to_string(const stmt_ptr& stmt, int column); + +} // !server + +} // !mlk + +#endif // !MALIKANIA_SQLITE_COMMON_HPP diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/db.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/db.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,95 @@ +/* + * db.cpp -- general dynlib database functions + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +namespace sqlite { + +sqlite3* handle{nullptr}; + +} // !sqlite + +} // !server + +} // !mlk + +extern "C" { + +#if 0 + +BOOST_SYMBOL_EXPORT +void mlk_db_close() +{ + sqlite3_close(mlk::server::handle); +} + +BOOST_SYMBOL_EXPORT +void mlk_db_init() +{ + mlk::server::exec( + "create table account(" + " id INTEGER PRIMARY KEY AUTOINCREMENT," + " login TEXT," + " email TEXT," + " firstname TEXT," + " lastname TEXT" + ")" + ); + mlk::server::exec( + "create table character(" + " id INTEGER PRIMARY KEY AUTOINCREMENT," + " name TEXT," + " account_id INTEGER," + " FOREIGN KEY(account_id) REFERENCES account(id) ON DELETE CASCADE" + ")" + ); + mlk::server::exec( + "create table spell(" + " id INTEGER PRIMARY KEY AUTOINCREMENT," + " character_id INTEGER," + " level INTEGER," + " FOREIGN KEY(character_id) REFERENCES character(id) ON DELETE CASCADE" + ")" + ); + mlk::server::exec("PRAGMA foreign_keys = ON"); +} + +BOOST_SYMBOL_EXPORT +void mlk_db_open(const std::unordered_map& parameters) +{ + auto it = parameters.find("path"); + + if (it == parameters.end()) + throw std::runtime_error("missing 'path' parameter"); + + std::remove(it->second.c_str()); + + if (sqlite3_open(it->second.c_str(), &mlk::server::handle) != SQLITE_OK) + throw std::runtime_error(sqlite3_errmsg(mlk::server::handle)); +} + +#endif + +} // !C diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_account.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_account.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,164 @@ +/* + * sqlite_account.cpp -- database account object (SQLite implementation) + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include "sqlite_database.hpp" + +namespace mlk { + +namespace server { + +using sqlite_dao = sqlite_account::sqlite_dao; + +/* + * sqlite_account::do_save + * ------------------------------------------------------------------ + */ +void sqlite_account::do_save() +{ + const std::string sql( + "INSERT INTO account(login, password, email, firstname, lastname)" + "VALUES (?, ?, ?, ?, ?)" + ); + + exec(db_.instance(), sql, {login_, password_, email_, firstname_, lastname_}); + id_ = sqlite3_last_insert_rowid(db_.instance()); +} + +/* + * sqlite_account::do_remove + * ------------------------------------------------------------------ + */ +void sqlite_account::do_remove() +{ + const std::string sql( + "DELETE" + " FROM account" + " WHERE id = ?" + ); + + exec(db_.instance(), sql, {id_}); +} + +/* + * sqlite_account::do_set_password + * ------------------------------------------------------------------ + */ +void sqlite_account::do_set_password(const std::string& password) +{ + const std::string sql( + "UPDATE account" + " SET password = ?" + " WHERE id = ?" + ); + + exec(db_.instance(), sql, {password, id_}); +} + +/* + * sqlite_account::do_set_email + * ------------------------------------------------------------------ + */ +void sqlite_account::do_set_email(const std::string& email) +{ + const std::string sql( + "UPDATE account" + " SET email = ?" + " WHERE id = ?" + ); + + exec(db_.instance(), sql, {email, id_}); +} + +/* + * sqlite_account::do_set_firstname + * ------------------------------------------------------------------ + */ +void sqlite_account::do_set_firstname(const std::string& name) +{ + const std::string sql( + "UPDATE account" + " SET firstname = ?" + " WHERE id = ?" + ); + + exec(db_.instance(), sql, {name, id_}); +} + +/* + * sqlite_account::do_set_lastname + * ------------------------------------------------------------------ + */ +void sqlite_account::do_set_lastname(const std::string& name) +{ + const std::string sql( + "UPDATE account" + " SET lastname = ?" + " WHERE id = ?" + ); + + exec(db_.instance(), sql, {name, id_}); +} + +/* + * sqlite_account::sqlite_dao::get + * ------------------------------------------------------------------ + */ +std::unique_ptr sqlite_dao::get(const stmt_ptr& stmt) +{ + auto step = sqlite3_step(stmt.get()); + + switch (step) { + case SQLITE_DONE: + // No row, not found so just return an empty account. + return nullptr; + case SQLITE_ERROR: + case SQLITE_MISUSE: + throw std::runtime_error(sqlite3_errmsg(db_.instance())); + default: + break; + } + + auto ac = std::make_unique(to_string(stmt, 1), to_string(stmt, 2), db_); + + ac->id_ = to_int(stmt, 0); + ac->email_ = to_string(stmt, 3); + ac->firstname_ = to_string(stmt, 4); + ac->lastname_ = to_string(stmt, 5); + ac->characters_ = db_.sqlite_character_dao().characters_for_account(ac->id_); + + return ac; +} + +/* + * sqlite_account::sqlite_dao::find_by_login + * ------------------------------------------------------------------ + */ +std::unique_ptr sqlite_dao::find_by_login(const std::string& login) +{ + const std::string sql( + "SELECT *" + " FROM account" + " WHERE login = ?" + ); + + return get(query(db_.instance(), sql, {login})); +} + +} // !server + +} // !db diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_account.hpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_account.hpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,125 @@ +/* + * sqlite_account.hpp -- database account object (SQLite implementation) + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#ifndef MALIKANIA_DB_SQLITE_ACCOUNT_HPP +#define MALIKANIA_DB_SQLITE_ACCOUNT_HPP + +/** + * \file sqlite_account.hpp + * \brief Database account object (SQLite implementation). + */ + +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +class sqlite_database; + +/** + * \brief Database account object (SQLite implementation). + */ +class sqlite_account : public account { +public: + class sqlite_dao; + +private: + sqlite_database& db_; + +protected: + /** + * \copydoc account::do_save + */ + void do_save() override; + + /** + * \copydoc account::do_remove + */ + void do_remove() override; + + /** + * \copydoc account::do_set_password + */ + void do_set_password(const std::string& password) override; + + /** + * \copydoc account::do_set_email + */ + void do_set_email(const std::string& email) override; + + /** + * \copydoc account::do_set_firstname + */ + void do_set_firstname(const std::string& name) override; + + /** + * \copydoc account::do_set_lastname + */ + void do_set_lastname(const std::string& name) override; + +public: + /** + * Create the sqlite account. + * + * \pre instance != nullptr + * \param login the login name + * \param password the password + * \param db the sqlite_database owner + */ + inline sqlite_account(std::string login, std::string password, sqlite_database& db) noexcept + : account(std::move(login), std::move(password)) + , db_(db) + { + } +}; + +/** + * \brief DAO for accounts (SQLite implementation). + */ +class sqlite_account::sqlite_dao : public account::dao { +private: + sqlite_database& db_; + + std::unique_ptr get(const stmt_ptr& stmt); + +protected: + /** + * \copydoc dao::find_by_login + */ + std::unique_ptr find_by_login(const std::string& login) override; + +public: + /** + * Construct the DAO. + * + * \param db the sqlite_database owner + */ + inline sqlite_dao(sqlite_database& db) noexcept + : db_(db) + { + } +}; + +} // !server + +} // !mlk + +#endif // !MALIKANIA_DB_SQLITE_ACCOUNT_HPP diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_account_dao.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_account_dao.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,63 @@ +/* + * sqlite_account_dao.cpp -- account_dao for SQLite + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include +#include +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +class sqlite_account_dao : public account_dao { +private: + std::shared_ptr get(sqlite::stmt_ptr); + +public: + using account_dao::account_dao; + + std::shared_ptr authenticate(const std::string&, + const std::string&) override; + + std::shared_ptr find(const std::string&) override; + + std::int64_t save(std::shared_ptr ac) override; + + void set_email(std::shared_ptr ac, const std::string&) override; + + void set_firstname(std::shared_ptr, const std::string&) override; + + void set_lastname(std::shared_ptr, const std::string&) override; +}; + + +} // !server + +} // !mlk + +extern "C" { + +BOOST_SYMBOL_EXPORT +std::unique_ptr mlk_account_dao(std::shared_ptr db) +{ + return std::make_unique(std::move(db)); +} + +} // !C diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_character.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_character.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,95 @@ +/* + * sqlite_character.cpp -- database character object (SQLite implementation) + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include "sqlite_database.hpp" + +namespace mlk { + +namespace server { + +using sqlite_dao = sqlite_character::sqlite_dao; + +void sqlite_character::do_set_level(std::uint16_t level) +{ + const std::string sql( + "UPDATE character" + " SET level = ?" + " WHERE id = ?" + ); + + exec(db_.instance(), sql, {level, id_}); +} + +void sqlite_character::do_save(std::int64_t account_id) +{ + const std::string sql( + "INSERT INTO character(" + " account_id," + " nickname," + " classname," + " level" + ") VALUES (?, ?, ?, ?)" + ); + + exec(db_.instance(), sql, {account_id, nickname_, classname_, level_}); +} + +void sqlite_character::do_remove() +{ + const std::string sql( + "DELETE FROM character WHERE id = ?" + ); + + exec(db_.instance(), sql, {id_}); +} + +std::unique_ptr sqlite_dao::get(stmt_ptr& stmt) +{ + auto ch = std::make_unique(to_string(stmt, 2), to_string(stmt, 3), db_); + + ch->account_id_ = to_int(stmt, 1); + ch->level_ = to_int(stmt, 4); + ch->id_ = to_int(stmt, 0); + + return ch; +} + +character_list sqlite_dao::get_all(stmt_ptr stmt) +{ + character_list list; + + while (sqlite3_step(stmt.get()) == SQLITE_ROW) + list.push_back(get(stmt)); + + return list; +} + +character_list sqlite_dao::characters_for_account(std::int64_t account_id) +{ + const std::string sql( + "SELECT *" + " FROM character" + " WHERE account_id = ?" + ); + + return get_all(query(db_.instance(), sql, {account_id})); +} + +} // !server + +} // !mlk diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_character.hpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_character.hpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,111 @@ +/* + * sqlite_character.hpp -- database character object (SQLite implementation) + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#ifndef MALIKANIA_DB_SQLITE_CHARACTER_HPP +#define MALIKANIA_DB_SQLITE_CHARACTER_HPP + +/** + * \file sqlite_character.hpp + * \brief Database character object (SQLite implementation). + */ + +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +class sqlite_database; + +/** + * \brief Database character object (SQLite implementation). + */ +class sqlite_character : public character { +private: + sqlite_database& db_; + +public: + class sqlite_dao; + +protected: + /** + * \copydoc character::do_set_level + */ + void do_set_level(std::uint16_t level) override; + + /** + * \copydoc character::do_save + */ + void do_save(std::int64_t account_id) override; + + /** + * \copydoc character::do_remove + */ + void do_remove() override; + +public: + /** + * Construct the sqlite character. + * + * \pre instance != nullptr + * \param nickname the nickname + * \param classname the classname + * \param db the sqlite_database owner + */ + inline sqlite_character(std::string nickname, std::string classname, sqlite_database& db) noexcept + : character(std::move(nickname), std::move(classname)) + , db_(db) + { + } +}; + +/** + * \brief DAO for characters (SQLite implementation). + */ +class sqlite_character::sqlite_dao { +private: + sqlite_database& db_; + + std::unique_ptr get(stmt_ptr& stmt); + + character_list get_all(stmt_ptr stmt); + +public: + /** + * Construct the DAO. + * + * \param db the sqlite_database owner + */ + inline sqlite_dao(sqlite_database& db) noexcept + : db_(db) + { + } + + /** + * Get the set of characters for the given account. + */ + character_list characters_for_account(std::int64_t account_id); +}; + +} // !server + +} // !mlk + +#endif // !MALIKANIA_DB_SQLITE_CHARACTER_HPP diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_character_dao.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_character_dao.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,85 @@ +/* + * sqlite_character_dao.cpp -- character_dao for SQLite + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include +#include +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +class sqlite_character_dao : public character_dao { +public: + using character_dao::character_dao; + + std::int64_t save(std::shared_ptr, std::shared_ptr ac) override; + + void remove(std::shared_ptr) override; + + void set_name(std::shared_ptr, const std::string&) override; +}; + +std::int64_t sqlite_character_dao::save(std::shared_ptr ch, std::shared_ptr ac) +{ + const std::string sql( + "INSERT INTO character(account_id, name) VALUES (?, ?)" + ); + + mlk::server::sqlite::exec(sql, {ac->id(), ch->name()}); + + return sqlite3_last_insert_rowid(mlk::server::sqlite::handle); +} + +void sqlite_character_dao::remove(std::shared_ptr ch) +{ + const std::string sql( + "DELETE" + " FROM character" + " WHERE id = ?" + ); + + mlk::server::sqlite::exec(sql, {ch->id()}); +} + +void sqlite_character_dao::set_name(std::shared_ptr ch, const std::string& name) +{ + const std::string sql( + "UPDATE character" + " SET name = ?" + " WHERE id = ?" + ); + + mlk::server::sqlite::exec(sql, {name, ch->id()}); +} + +} // !server + +} // !mlk + +extern "C" { + +BOOST_SYMBOL_EXPORT +std::unique_ptr mlk_character_dao(std::shared_ptr db) +{ + return std::make_unique(std::move(db)); +} + +} // !C diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_database.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_database.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,86 @@ +#include "sqlite_database.hpp" +#include "sqlite_account.hpp" +#include "sqlite_character.hpp" + +namespace mlk { + +namespace server { + +namespace { + +const std::string init_account( + "CREATE TABLE account(" + " id INTEGER PRIMARY KEY AUTOINCREMENT," + " login TEXT NOT NULL," + " password TEXT NOT NULL," + " email TEXT NOT NULL," + " firstname TEXT," + " lastname TEXT" + ")" +); + +const std::string init_character( + "CREATE TABLE character(" + " id INTEGER PRIMARY KEY AUTOINCREMENT," + " account_id INTEGER," + " nickname TEXT," + " classname TEXT," + " level INTEGER," + " FOREIGN KEY(account_id) REFERENCES account(id) ON DELETE CASCADE" + ")" +); + +const std::string init_spell( + "CREATE TABLE spell(" + " id INTEGER PRIMARY KEY AUTOINCREMENT," + " character_id INTEGER," + " level INTEGER," + " FOREIGN KEY(character_id) REFERENCES character(id) ON DELETE CASCADE" + ")" +); + +} // !namespace + +sqlite_database::sqlite3_ptr sqlite_database::open(const std::string& path) const +{ + sqlite3* instance; + auto flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE; + + if (sqlite3_open_v2(path.c_str(), &instance, flags, nullptr) != SQLITE_OK) + throw std::runtime_error(sqlite3_errmsg(instance)); + + return {instance, &sqlite3_close}; +} + +sqlite_database::sqlite_database(const std::string& path) + : instance_(open(path)) +{ +} + +void sqlite_database::init() +{ + // TODO: verify if initialized and appropriate version. + + exec(instance_.get(), init_account); + exec(instance_.get(), init_character); + exec(instance_.get(), init_spell); +} + +std::unique_ptr sqlite_database::account_draft(std::string login, std::string password) +{ + return std::make_unique(std::move(login), std::move(password), *this); +} + +account::dao& sqlite_database::account_dao() +{ + return account_dao_; +} + +std::unique_ptr sqlite_database::character_draft(std::string nickname, std::string classname) +{ + return std::make_unique(std::move(nickname), std::move(classname), *this); +} + +} // !server + +} // !mlk diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_database.hpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_database.hpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,79 @@ +#ifndef MALIKANIA_DB_SQLITE_DATABASE_HPP +#define MALIKANIA_DB_SQLITE_DATABASE_HPP + +#include + +#include + +#include "sqlite_account.hpp" +#include "sqlite_character.hpp" + +namespace mlk { + +namespace server { + +class sqlite_database : public database { +public: + using sqlite3_ptr = std::unique_ptr; + +private: + sqlite3_ptr instance_{nullptr, nullptr}; + sqlite_account::sqlite_dao account_dao_{*this}; + sqlite_character::sqlite_dao character_dao_{*this}; + + sqlite3_ptr open(const std::string&) const; + +public: + /** + * Create a sqlite_database at the given path. + * + * \throw exceptions if file does not exist + */ + sqlite_database(const std::string& path); + + /** + * Return the sqlite3 instance. + * + * \return the instance + */ + inline sqlite3* instance() noexcept + { + return instance_.get(); + } + + /** + * Get the real underlying sqlite_character::sqlite_dao. + * + * \return the character dao + */ + inline sqlite_character::sqlite_dao& sqlite_character_dao() noexcept + { + return character_dao_; + } + + /** + * \copydoc database::init + */ + void init() override; + + /** + * \copydoc database::account_draft + */ + std::unique_ptr account_draft(std::string login, std::string password) override; + + /** + * \copydoc database::account_dao + */ + account::dao& account_dao() override; + + /** + * \copydoc database::character_draft + */ + std::unique_ptr character_draft(std::string nickname, std::string classname) override; +}; + +} // !server + +} // !mlk + +#endif // !MALIKANIA_DB_SQLITE_DATABASE_HPP diff -r bc3257f515cc -r 24ec19139f08 libdb-sqlite/malikania/server/db/sqlite_spell_dao.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libdb-sqlite/malikania/server/db/sqlite_spell_dao.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -0,0 +1,85 @@ +/* + * sqlite_spell_dao.cpp -- spell_dao for SQLite + * + * Copyright (c) 2013-2017 David Demelier + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +#include +#include +#include + +#include "common.hpp" + +namespace mlk { + +namespace server { + +class sqlite_spell_dao : public spell_dao { +public: + using spell_dao::spell_dao; + std::int64_t save(std::shared_ptr, std::shared_ptr parent) override; + void set_level(std::shared_ptr, std::uint8_t level) override; + void remove(std::shared_ptr) override; +}; + +std::int64_t sqlite_spell_dao::save(std::shared_ptr s, std::shared_ptr parent) +{ + const std::string sql( + "INSERT INTO spell(account_id, level) VALUES(?, ?)" + ); + + mlk::server::sqlite::exec(sql, { + parent->id(), + static_cast(s->level()) + }); + + return sqlite3_last_insert_rowid(mlk::server::sqlite::handle); +} + +void sqlite_spell_dao::set_level(std::shared_ptr s, std::uint8_t level) +{ + const std::string sql( + "UPDATE spell" + " SET level = ?" + " WHERE id = ?" + ); + + mlk::server::sqlite::exec(sql, {static_cast(level), s->id()}); +} + +void sqlite_spell_dao::remove(std::shared_ptr s) +{ + const std::string sql( + "DELETE" + " FROM spell" + " WHERE id = ?" + ); + + mlk::server::sqlite::exec(sql, {s->id()}); +} + +} // !server + +} // !mlk + +extern "C" { + +BOOST_SYMBOL_EXPORT +std::unique_ptr mlk_spell_dao(std::shared_ptr db) +{ + return std::make_unique(std::move(db)); +} + +} // !C diff -r bc3257f515cc -r 24ec19139f08 libserver-test/CMakeLists.txt --- a/libserver-test/CMakeLists.txt Fri Sep 22 13:10:40 2017 +0200 +++ b/libserver-test/CMakeLists.txt Fri Sep 22 13:12:50 2017 +0200 @@ -44,6 +44,7 @@ TARGET libmlk-server-test SOURCES ${HEADERS} ${SOURCES} LIBRARIES + libmlk-db-sqlite libmlk-server PUBLIC_INCLUDES ${libmlk-server-test_SOURCE_DIR} diff -r bc3257f515cc -r 24ec19139f08 libserver-test/malikania/server/db/database_fixture.cpp --- a/libserver-test/malikania/server/db/database_fixture.cpp Fri Sep 22 13:10:40 2017 +0200 +++ b/libserver-test/malikania/server/db/database_fixture.cpp Fri Sep 22 13:12:50 2017 +0200 @@ -24,6 +24,15 @@ namespace server { +sqlite_fixture::sqlite_fixture() +{ + const std::string path(CMAKE_CURRENT_BINARY_DIR "/test.db"); + + std::remove(path.c_str()); + + db_ = std::make_unique(path); + db_->init(); +} } // !server diff -r bc3257f515cc -r 24ec19139f08 libserver-test/malikania/server/db/database_fixture.hpp --- a/libserver-test/malikania/server/db/database_fixture.hpp Fri Sep 22 13:10:40 2017 +0200 +++ b/libserver-test/malikania/server/db/database_fixture.hpp Fri Sep 22 13:12:50 2017 +0200 @@ -24,12 +24,40 @@ #include #include +#include namespace mlk { namespace server { /** + * \brief Create a sqlite database fixture. + * + * This class create a test.db file in CMAKE_CURRENT_BINARY_DIR directory, it + * is deleted in the constructor and initialized afterwards. + */ +class sqlite_fixture { +private: + std::unique_ptr db_; + +public: + /** + * Constructor. + * + * Remove and create the test.db file, then initialize it. + */ + sqlite_fixture(); + + /** + * Access the database object. + */ + inline database& db() noexcept + { + return *db_; + } +}; + +/** * \brief Create a test database fixture. */ class test_fixture { @@ -50,6 +78,7 @@ * \brief List to use with BOOST_AUTO_TEST_CASE_TEMPLATE */ using database_types = boost::mpl::list< + sqlite_fixture, test_fixture >;