Skip to content

Commit af502f3

Browse files
zeunersfan5
zeuner
authored and
sfan5
committedJan 8, 2017
PostgreSQL database support
·
2025040820170606
1 parent d490cf0 commit af502f3

File tree

6 files changed

+243
-0
lines changed

6 files changed

+243
-0
lines changed
 

‎CMakeLists.txt

+38
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,39 @@ if(NOT SQLITE3_LIBRARY OR NOT SQLITE3_INCLUDE_DIR)
7878
message(FATAL_ERROR "sqlite3 not found!")
7979
endif(NOT SQLITE3_LIBRARY OR NOT SQLITE3_INCLUDE_DIR)
8080

81+
# Libraries: postgresql
82+
83+
option(ENABLE_POSTGRESQL "Enable PostgreSQL backend" TRUE)
84+
set(USE_POSTGRESQL 0)
85+
86+
if(ENABLE_POSTGRESQL)
87+
find_program(POSTGRESQL_CONFIG_EXECUTABLE pg_config DOC "pg_config")
88+
find_library(POSTGRESQL_LIBRARY pq)
89+
if(POSTGRESQL_CONFIG_EXECUTABLE)
90+
execute_process(COMMAND ${POSTGRESQL_CONFIG_EXECUTABLE} --includedir-server
91+
OUTPUT_VARIABLE POSTGRESQL_SERVER_INCLUDE_DIRS
92+
OUTPUT_STRIP_TRAILING_WHITESPACE)
93+
execute_process(COMMAND ${POSTGRESQL_CONFIG_EXECUTABLE}
94+
OUTPUT_VARIABLE POSTGRESQL_CLIENT_INCLUDE_DIRS
95+
OUTPUT_STRIP_TRAILING_WHITESPACE)
96+
# This variable is case sensitive for the cmake PostgreSQL module
97+
set(PostgreSQL_ADDITIONAL_SEARCH_PATHS ${POSTGRESQL_SERVER_INCLUDE_DIRS} ${POSTGRESQL_CLIENT_INCLUDE_DIRS})
98+
endif()
99+
100+
find_package("PostgreSQL")
101+
102+
if(POSTGRESQL_FOUND)
103+
set(USE_POSTGRESQL 1)
104+
message(STATUS "PostgreSQL backend enabled")
105+
# This variable is case sensitive, don't try to change it to POSTGRESQL_INCLUDE_DIR
106+
message(STATUS "PostgreSQL includes: ${PostgreSQL_INCLUDE_DIR}")
107+
include_directories(${PostgreSQL_INCLUDE_DIR})
108+
set(POSTGRESQL_LIBRARY ${PostgreSQL_LIBRARIES})
109+
else()
110+
message(STATUS "PostgreSQL not found!")
111+
endif()
112+
endif(ENABLE_POSTGRESQL)
113+
81114
# Libraries: leveldb
82115

83116
set(USE_LEVELDB 0)
@@ -148,6 +181,10 @@ set(mapper_SRCS
148181
db-sqlite3.cpp
149182
)
150183

184+
if(USE_POSTGRESQL)
185+
set(mapper_SRCS ${mapper_SRCS} db-postgresql.cpp)
186+
endif(USE_POSTGRESQL)
187+
151188
if(USE_LEVELDB)
152189
set(mapper_SRCS ${mapper_SRCS} db-leveldb.cpp)
153190
endif(USE_LEVELDB)
@@ -163,6 +200,7 @@ add_executable(minetestmapper
163200
target_link_libraries(
164201
minetestmapper
165202
${SQLITE3_LIBRARY}
203+
${POSTGRESQL_LIBRARY}
166204
${LEVELDB_LIBRARY}
167205
${REDIS_LIBRARY}
168206
${LIBGD_LIBRARY}

‎TileGenerator.cpp

+7
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,9 @@
1313
#include "ZlibDecompressor.h"
1414
#include "util.h"
1515
#include "db-sqlite3.h"
16+
#if USE_POSTGRESQL
17+
#include "db-postgresql.h"
18+
#endif
1619
#if USE_LEVELDB
1720
#include "db-leveldb.h"
1821
#endif
@@ -283,6 +286,10 @@ void TileGenerator::openDb(const std::string &input)
283286

284287
if(backend == "sqlite3")
285288
m_db = new DBSQLite3(input);
289+
#if USE_POSTGRESQL
290+
else if(backend == "postgresql")
291+
m_db = new DBPostgreSQL(input);
292+
#endif
286293
#if USE_LEVELDB
287294
else if(backend == "leveldb")
288295
m_db = new DBLevelDB(input);

‎cmake_config.h.in

+1
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33
#ifndef CMAKE_CONFIG_H
44
#define CMAKE_CONFIG_H
55

6+
#define USE_POSTGRESQL @USE_POSTGRESQL@
67
#define USE_LEVELDB @USE_LEVELDB@
78
#define USE_REDIS @USE_REDIS@
89

‎config.h

+1
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@
99
#ifdef USE_CMAKE_CONFIG_H
1010
#include "cmake_config.h"
1111
#else
12+
#define USE_POSTGRESQL 0
1213
#define USE_LEVELDB 0
1314
#define USE_REDIS 0
1415

‎db-postgresql.cpp

+167
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,167 @@
1+
#include <stdexcept>
2+
#include <iostream>
3+
#include <fstream>
4+
#include <arpa/inet.h>
5+
#include "db-postgresql.h"
6+
#include "util.h"
7+
#include "types.h"
8+
9+
#define ARRLEN(x) (sizeof(x) / sizeof((x)[0]))
10+
11+
DBPostgreSQL::DBPostgreSQL(const std::string &mapdir)
12+
{
13+
std::ifstream ifs((mapdir + "/world.mt").c_str());
14+
if(!ifs.good())
15+
throw std::runtime_error("Failed to read world.mt");
16+
std::string const connect_string = get_setting("pgsql_connection", ifs);
17+
ifs.close();
18+
db = PQconnectdb(connect_string.c_str());
19+
20+
if (PQstatus(db) != CONNECTION_OK) {
21+
throw std::runtime_error(std::string(
22+
"PostgreSQL database error: ") +
23+
PQerrorMessage(db)
24+
);
25+
}
26+
27+
prepareStatement(
28+
"get_block_pos",
29+
"SELECT posX, posY, posZ FROM blocks"
30+
);
31+
prepareStatement(
32+
"get_blocks_z",
33+
"SELECT posX, posY, data FROM blocks WHERE posZ = $1::int4"
34+
);
35+
36+
checkResults(PQexec(db, "START TRANSACTION;"));
37+
checkResults(PQexec(db, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;"));
38+
}
39+
40+
41+
DBPostgreSQL::~DBPostgreSQL()
42+
{
43+
try {
44+
checkResults(PQexec(db, "COMMIT;"));
45+
} catch (std::exception& caught) {
46+
std::cerr << "could not finalize: " << caught.what() << std::endl;
47+
}
48+
PQfinish(db);
49+
}
50+
51+
std::vector<BlockPos> DBPostgreSQL::getBlockPos()
52+
{
53+
std::vector<BlockPos> positions;
54+
55+
PGresult *results = execPrepared(
56+
"get_block_pos", 0,
57+
NULL, NULL, NULL, false, false
58+
);
59+
60+
int numrows = PQntuples(results);
61+
62+
for (int row = 0; row < numrows; ++row)
63+
positions.push_back(pg_to_blockpos(results, row, 0));
64+
65+
PQclear(results);
66+
67+
return positions;
68+
}
69+
70+
71+
void DBPostgreSQL::getBlocksOnZ(std::map<int16_t, BlockList> &blocks, int16_t zPos)
72+
{
73+
int32_t const z = htonl(zPos);
74+
75+
const void *args[] = { &z };
76+
const int argLen[] = { sizeof(z) };
77+
const int argFmt[] = { 1 };
78+
79+
PGresult *results = execPrepared(
80+
"get_blocks_z", ARRLEN(args), args,
81+
argLen, argFmt, false
82+
);
83+
84+
int numrows = PQntuples(results);
85+
86+
for (int row = 0; row < numrows; ++row) {
87+
BlockPos position;
88+
position.x = pg_binary_to_int(results, row, 0);
89+
position.y = pg_binary_to_int(results, row, 1);
90+
position.z = zPos;
91+
Block const b(
92+
position,
93+
ustring(
94+
reinterpret_cast<unsigned char*>(
95+
PQgetvalue(results, row, 2)
96+
),
97+
PQgetlength(results, row, 2)
98+
)
99+
);
100+
blocks[position.x].push_back(b);
101+
}
102+
103+
PQclear(results);
104+
}
105+
106+
PGresult *DBPostgreSQL::checkResults(PGresult *res, bool clear)
107+
{
108+
ExecStatusType statusType = PQresultStatus(res);
109+
110+
switch (statusType) {
111+
case PGRES_COMMAND_OK:
112+
case PGRES_TUPLES_OK:
113+
break;
114+
case PGRES_FATAL_ERROR:
115+
throw std::runtime_error(
116+
std::string("PostgreSQL database error: ") +
117+
PQresultErrorMessage(res)
118+
);
119+
default:
120+
throw std::runtime_error(
121+
"Unhandled PostgreSQL result code"
122+
);
123+
}
124+
125+
if (clear)
126+
PQclear(res);
127+
128+
return res;
129+
}
130+
131+
void DBPostgreSQL::prepareStatement(const std::string &name, const std::string &sql)
132+
{
133+
checkResults(PQprepare(db, name.c_str(), sql.c_str(), 0, NULL));
134+
}
135+
136+
PGresult *DBPostgreSQL::execPrepared(
137+
const char *stmtName, const int paramsNumber,
138+
const void **params,
139+
const int *paramsLengths, const int *paramsFormats,
140+
bool clear, bool nobinary
141+
)
142+
{
143+
return checkResults(PQexecPrepared(db, stmtName, paramsNumber,
144+
(const char* const*) params, paramsLengths, paramsFormats,
145+
nobinary ? 1 : 0), clear
146+
);
147+
}
148+
149+
int DBPostgreSQL::pg_to_int(PGresult *res, int row, int col)
150+
{
151+
return atoi(PQgetvalue(res, row, col));
152+
}
153+
154+
int DBPostgreSQL::pg_binary_to_int(PGresult *res, int row, int col)
155+
{
156+
int32_t* raw = reinterpret_cast<int32_t*>(PQgetvalue(res, row, col));
157+
return ntohl(*raw);
158+
}
159+
160+
BlockPos DBPostgreSQL::pg_to_blockpos(PGresult *res, int row, int col)
161+
{
162+
BlockPos result;
163+
result.x = pg_to_int(res, row, col);
164+
result.y = pg_to_int(res, row, col + 1);
165+
result.z = pg_to_int(res, row, col + 2);
166+
return result;
167+
}

‎db-postgresql.h

+29
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
#ifndef _DB_POSTGRESQL_H
2+
#define _DB_POSTGRESQL_H
3+
4+
#include "db.h"
5+
#include <libpq-fe.h>
6+
7+
class DBPostgreSQL : public DB {
8+
public:
9+
DBPostgreSQL(const std::string &mapdir);
10+
virtual std::vector<BlockPos> getBlockPos();
11+
virtual void getBlocksOnZ(std::map<int16_t, BlockList> &blocks, int16_t zPos);
12+
virtual ~DBPostgreSQL();
13+
protected:
14+
PGresult *checkResults(PGresult *res, bool clear = true);
15+
void prepareStatement(const std::string &name, const std::string &sql);
16+
PGresult *execPrepared(
17+
const char *stmtName, const int paramsNumber,
18+
const void **params,
19+
const int *paramsLengths = NULL, const int *paramsFormats = NULL,
20+
bool clear = true, bool nobinary = true
21+
);
22+
int pg_to_int(PGresult *res, int row, int col);
23+
int pg_binary_to_int(PGresult *res, int row, int col);
24+
BlockPos pg_to_blockpos(PGresult *res, int row, int col);
25+
private:
26+
PGconn *db;
27+
};
28+
29+
#endif // _DB_POSTGRESQL_H

0 commit comments

Comments
 (0)
Please sign in to comment.