2 Copyright (C) 2016 Loic Blot <loic.blot@unix-experience.fr>
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU Lesser General Public License as published by
6 the Free Software Foundation; either version 2.1 of the License, or
7 (at your option) any later version.
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU Lesser General Public License for more details.
14 You should have received a copy of the GNU Lesser General Public License along
15 with this program; if not, write to the Free Software Foundation, Inc.,
16 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 #include "database-postgresql.h"
26 #ifndef WIN32_LEAN_AND_MEAN
27 #define WIN32_LEAN_AND_MEAN
29 // Without this some of the network functions are not found on mingw
31 #define _WIN32_WINNT 0x0501
36 #include <netinet/in.h>
40 #include "exceptions.h"
43 Database_PostgreSQL::Database_PostgreSQL(const Settings &conf) :
48 if (!conf.getNoEx("pgsql_connection", m_connect_string)) {
49 throw SettingNotFoundException(
50 "Set pgsql_connection string in world.mt to "
51 "use the postgresql backend\n"
53 "pgsql_connection has the following form: \n"
54 "\tpgsql_connection = host=127.0.0.1 port=5432 user=mt_user "
55 "password=mt_password dbname=minetest_world\n"
56 "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
57 "DELETE rights on the database.\n"
58 "Don't create mt_user as a SUPERUSER!");
64 Database_PostgreSQL::~Database_PostgreSQL()
69 void Database_PostgreSQL::connectToDatabase()
71 m_conn = PQconnectdb(m_connect_string.c_str());
73 if (PQstatus(m_conn) != CONNECTION_OK) {
74 throw DatabaseException(std::string(
75 "PostgreSQL database error: ") +
76 PQerrorMessage(m_conn));
79 m_pgversion = PQserverVersion(m_conn);
82 * We are using UPSERT feature from PostgreSQL 9.5
83 * to have the better performance where possible.
85 if (m_pgversion < 90500) {
86 warningstream << "Your PostgreSQL server lacks UPSERT "
87 << "support. Use version 9.5 or better if possible."
91 infostream << "PostgreSQL Database: Version " << m_pgversion
92 << " Connection made." << std::endl;
98 void Database_PostgreSQL::verifyDatabase()
100 if (PQstatus(m_conn) == CONNECTION_OK)
107 void Database_PostgreSQL::ping()
109 if (PQping(m_connect_string.c_str()) != PQPING_OK) {
110 throw DatabaseException(std::string(
111 "PostgreSQL database error: ") +
112 PQerrorMessage(m_conn));
116 bool Database_PostgreSQL::initialized() const
118 return (PQstatus(m_conn) == CONNECTION_OK);
121 void Database_PostgreSQL::initStatements()
123 prepareStatement("read_block",
124 "SELECT data FROM blocks "
125 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
128 if (m_pgversion < 90500) {
129 prepareStatement("write_block_insert",
130 "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
131 "$1::int4, $2::int4, $3::int4, $4::bytea "
132 "WHERE NOT EXISTS (SELECT true FROM blocks "
133 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
136 prepareStatement("write_block_update",
137 "UPDATE blocks SET data = $4::bytea "
138 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
141 prepareStatement("write_block",
142 "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
143 "($1::int4, $2::int4, $3::int4, $4::bytea) "
144 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
145 "UPDATE SET data = $4::bytea");
148 prepareStatement("delete_block", "DELETE FROM blocks WHERE "
149 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
151 prepareStatement("list_all_loadable_blocks",
152 "SELECT posX, posY, posZ FROM blocks");
155 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
157 ExecStatusType statusType = PQresultStatus(result);
159 switch (statusType) {
160 case PGRES_COMMAND_OK:
161 case PGRES_TUPLES_OK:
163 case PGRES_FATAL_ERROR:
165 throw DatabaseException(
166 std::string("PostgreSQL database error: ") +
167 PQresultErrorMessage(result));
176 void Database_PostgreSQL::createDatabase()
178 PGresult *result = checkResults(PQexec(m_conn,
179 "SELECT relname FROM pg_class WHERE relname='blocks';"),
182 // If table doesn't exist, create it
183 if (!PQntuples(result)) {
184 static const char* dbcreate_sql = "CREATE TABLE blocks ("
189 "PRIMARY KEY (posX,posY,posZ)"
191 checkResults(PQexec(m_conn, dbcreate_sql));
196 infostream << "PostgreSQL: Game Database was inited." << std::endl;
200 void Database_PostgreSQL::beginSave()
203 checkResults(PQexec(m_conn, "BEGIN;"));
206 void Database_PostgreSQL::endSave()
208 checkResults(PQexec(m_conn, "COMMIT;"));
211 bool Database_PostgreSQL::saveBlock(const v3s16 &pos,
212 const std::string &data)
214 // Verify if we don't overflow the platform integer with the mapblock size
215 if (data.size() > INT_MAX) {
216 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
217 << "data.size() over 0xFFFF (== " << data.size()
229 const void *args[] = { &x, &y, &z, data.c_str() };
230 const int argLen[] = {
231 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
233 const int argFmt[] = { 1, 1, 1, 1 };
235 if (m_pgversion < 90500) {
236 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
237 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
239 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
244 void Database_PostgreSQL::loadBlock(const v3s16 &pos,
254 const void *args[] = { &x, &y, &z };
255 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
256 const int argFmt[] = { 1, 1, 1 };
258 PGresult *results = execPrepared("read_block", ARRLEN(args), args,
259 argLen, argFmt, false);
263 if (PQntuples(results)) {
264 *block = std::string(PQgetvalue(results, 0, 0),
265 PQgetlength(results, 0, 0));
271 bool Database_PostgreSQL::deleteBlock(const v3s16 &pos)
280 const void *args[] = { &x, &y, &z };
281 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
282 const int argFmt[] = { 1, 1, 1 };
284 execPrepared("read_block", ARRLEN(args), args, argLen, argFmt);
289 void Database_PostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
293 PGresult *results = execPrepared("list_all_loadable_blocks", 0,
294 NULL, NULL, NULL, false, false);
296 int numrows = PQntuples(results);
298 for (int row = 0; row < numrows; ++row) {
299 dst.push_back(pg_to_v3s16(results, 0, 0));
305 #endif // USE_POSTGRESQL