src/database-postgresql.cpp

   1 /*
   2 Copyright (C) 2016 Loic Blot <loic.blot@unix-experience.fr>
   3
   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.
   8
   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.
  13
  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.
  17 */
  18
  19 #include "config.h"
  20
  21 #if USE_POSTGRESQL
  22
  23 #include "database-postgresql.h"
  24
  25 #ifdef _WIN32
  26         #ifndef WIN32_LEAN_AND_MEAN
  27                 #define WIN32_LEAN_AND_MEAN
  28         #endif
  29         // Without this some of the network functions are not found on mingw
  30         #ifndef _WIN32_WINNT
  31                 #define _WIN32_WINNT 0x0501
  32         #endif
  33         #include <windows.h>
  34         #include <winsock2.h>
  35 #else
  36 #include <netinet/in.h>
  37 #endif
  38
  39 #include "log.h"
  40 #include "exceptions.h"
  41 #include "settings.h"
  42
  43 Database_PostgreSQL::Database_PostgreSQL(const Settings &conf) :
  44         m_connect_string(""),
  45         m_conn(NULL),
  46         m_pgversion(0)
  47 {
  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"
  52                         "Notes:\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!");
  59         }
  60
  61         connectToDatabase();
  62 }
  63
  64 Database_PostgreSQL::~Database_PostgreSQL()
  65 {
  66         PQfinish(m_conn);
  67 }
  68
  69 void Database_PostgreSQL::connectToDatabase()
  70 {
  71         m_conn = PQconnectdb(m_connect_string.c_str());
  72
  73         if (PQstatus(m_conn) != CONNECTION_OK) {
  74                 throw DatabaseException(std::string(
  75                         "PostgreSQL database error: ") +
  76                         PQerrorMessage(m_conn));
  77         }
  78
  79         m_pgversion = PQserverVersion(m_conn);
  80
  81         /*
  82         * We are using UPSERT feature from PostgreSQL 9.5
  83         * to have the better performance where possible.
  84         */
  85         if (m_pgversion < 90500) {
  86                 warningstream << "Your PostgreSQL server lacks UPSERT "
  87                         << "support. Use version 9.5 or better if possible."
  88                         << std::endl;
  89         }
  90
  91         infostream << "PostgreSQL Database: Version " << m_pgversion
  92                         << " Connection made." << std::endl;
  93
  94         createDatabase();
  95         initStatements();
  96 }
  97
  98 void Database_PostgreSQL::verifyDatabase()
  99 {
 100         if (PQstatus(m_conn) == CONNECTION_OK)
 101                 return;
 102
 103         PQreset(m_conn);
 104         ping();
 105 }
 106
 107 void Database_PostgreSQL::ping()
 108 {
 109         if (PQping(m_connect_string.c_str()) != PQPING_OK) {
 110                 throw DatabaseException(std::string(
 111                         "PostgreSQL database error: ") +
 112                         PQerrorMessage(m_conn));
 113         }
 114 }
 115
 116 bool Database_PostgreSQL::initialized() const
 117 {
 118         return (PQstatus(m_conn) == CONNECTION_OK);
 119 }
 120
 121 void Database_PostgreSQL::initStatements()
 122 {
 123         prepareStatement("read_block",
 124                         "SELECT data FROM blocks "
 125                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 126                         "posZ = $3::int4");
 127
 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 "
 134                         "posZ = $3::int4)");
 135
 136                 prepareStatement("write_block_update",
 137                         "UPDATE blocks SET data = $4::bytea "
 138                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 139                         "posZ = $3::int4");
 140         } else {
 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");
 146         }
 147
 148         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 149                         "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 150
 151         prepareStatement("list_all_loadable_blocks",
 152                         "SELECT posX, posY, posZ FROM blocks");
 153 }
 154
 155 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 156 {
 157         ExecStatusType statusType = PQresultStatus(result);
 158
 159         switch (statusType) {
 160         case PGRES_COMMAND_OK:
 161         case PGRES_TUPLES_OK:
 162                 break;
 163         case PGRES_FATAL_ERROR:
 164         default:
 165                 throw DatabaseException(
 166                         std::string("PostgreSQL database error: ") +
 167                         PQresultErrorMessage(result));
 168         }
 169
 170         if (clear)
 171                 PQclear(result);
 172
 173         return result;
 174 }
 175
 176 void Database_PostgreSQL::createDatabase()
 177 {
 178         PGresult *result = checkResults(PQexec(m_conn,
 179                 "SELECT relname FROM pg_class WHERE relname='blocks';"),
 180                 false);
 181
 182         // If table doesn't exist, create it
 183         if (!PQntuples(result)) {
 184                 static const char* dbcreate_sql = "CREATE TABLE blocks ("
 185                         "posX INT NOT NULL,"
 186                         "posY INT NOT NULL,"
 187                         "posZ INT NOT NULL,"
 188                         "data BYTEA,"
 189                         "PRIMARY KEY (posX,posY,posZ)"
 190                 ");";
 191                 checkResults(PQexec(m_conn, dbcreate_sql));
 192         }
 193
 194         PQclear(result);
 195
 196         infostream << "PostgreSQL: Game Database was inited." << std::endl;
 197 }
 198
 199
 200 void Database_PostgreSQL::beginSave()
 201 {
 202         verifyDatabase();
 203         checkResults(PQexec(m_conn, "BEGIN;"));
 204 }
 205
 206 void Database_PostgreSQL::endSave()
 207 {
 208         checkResults(PQexec(m_conn, "COMMIT;"));
 209 }
 210
 211 bool Database_PostgreSQL::saveBlock(const v3s16 &pos,
 212                 const std::string &data)
 213 {
 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()
 218                                 << ")" << std::endl;
 219                 return false;
 220         }
 221
 222         verifyDatabase();
 223
 224         s32 x, y, z;
 225         x = htonl(pos.X);
 226         y = htonl(pos.Y);
 227         z = htonl(pos.Z);
 228
 229         const void *args[] = { &x, &y, &z, data.c_str() };
 230         const int argLen[] = {
 231                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 232         };
 233         const int argFmt[] = { 1, 1, 1, 1 };
 234
 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);
 238         } else {
 239                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 240         }
 241         return true;
 242 }
 243
 244 void Database_PostgreSQL::loadBlock(const v3s16 &pos,
 245                 std::string *block)
 246 {
 247         verifyDatabase();
 248
 249         s32 x, y, z;
 250         x = htonl(pos.X);
 251         y = htonl(pos.Y);
 252         z = htonl(pos.Z);
 253
 254         const void *args[] = { &x, &y, &z };
 255         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 256         const int argFmt[] = { 1, 1, 1 };
 257
 258         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 259                         argLen, argFmt, false);
 260
 261         *block = "";
 262
 263         if (PQntuples(results)) {
 264                 *block = std::string(PQgetvalue(results, 0, 0),
 265                                 PQgetlength(results, 0, 0));
 266         }
 267
 268         PQclear(results);
 269 }
 270
 271 bool Database_PostgreSQL::deleteBlock(const v3s16 &pos)
 272 {
 273         verifyDatabase();
 274
 275         s32 x, y, z;
 276         x = htonl(pos.X);
 277         y = htonl(pos.Y);
 278         z = htonl(pos.Z);
 279
 280         const void *args[] = { &x, &y, &z };
 281         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 282         const int argFmt[] = { 1, 1, 1 };
 283
 284         execPrepared("read_block", ARRLEN(args), args, argLen, argFmt);
 285
 286         return true;
 287 }
 288
 289 void Database_PostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 290 {
 291         verifyDatabase();
 292
 293         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 294                         NULL, NULL, NULL, false, false);
 295
 296         int numrows = PQntuples(results);
 297
 298         for (int row = 0; row < numrows; ++row) {
 299                 dst.push_back(pg_to_v3s16(results, 0, 0));
 300         }
 301
 302         PQclear(results);
 303 }
 304
 305 #endif // USE_POSTGRESQL