195 changes: 157 additions & 38 deletions db-sqlite3.cpp
View file
Original file line number Diff line number Diff line change
Expand Up @@ -2,10 +2,15 @@
#include <stdexcept>
#include <unistd.h> // for usleep


DBSQLite3::DBSQLite3(const std::string &mapdir) :
m_getBlockPosStatement(NULL),
m_getBlocksOnZStatement(NULL),
m_getBlocksOnPosStatement(NULL)
cacheWorldRow(false),
m_blocksReadCount(0),
m_blocksCachedCount(0),
m_blocksUnCachedCount(0),
m_blockPosListStatement(NULL),
m_blocksOnZStatement(NULL),
m_blockOnPosStatement(NULL)
{

std::string db_name = mapdir + "map.sqlite";
Expand All @@ -15,96 +20,210 @@ DBSQLite3::DBSQLite3(const std::string &mapdir) :
}

DBSQLite3::~DBSQLite3() {
if (m_getBlockPosStatement) sqlite3_finalize(m_getBlockPosStatement);
if (m_getBlocksOnZStatement) sqlite3_finalize(m_getBlocksOnZStatement);
if (m_getBlocksOnPosStatement) sqlite3_finalize(m_getBlocksOnPosStatement);
if (m_blockPosListStatement) sqlite3_finalize(m_blockPosListStatement);
if (m_blocksOnZStatement) sqlite3_finalize(m_blocksOnZStatement);
if (m_blockOnPosStatement) sqlite3_finalize(m_blockOnPosStatement);
sqlite3_close(m_db);
}

int DBSQLite3::getBlocksReadCount(void)
{
return m_blocksReadCount;
}

int DBSQLite3::getBlocksCachedCount(void)
{
return m_blocksCachedCount;
}

int DBSQLite3::getBlocksUnCachedCount(void)
{
return m_blocksUnCachedCount;
}

std::vector<int64_t> DBSQLite3::getBlockPos() {
std::vector<int64_t> vec;
std::string sql = "SELECT pos FROM blocks";
if (m_getBlockPosStatement || sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_getBlockPosStatement, 0) == SQLITE_OK) {
if (m_blockPosListStatement || sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_blockPosListStatement, 0) == SQLITE_OK) {
int result = 0;
while (true) {
result = sqlite3_step(m_getBlockPosStatement);
result = sqlite3_step(m_blockPosListStatement);
if(result == SQLITE_ROW) {
sqlite3_int64 blocknum = sqlite3_column_int64(m_getBlockPosStatement, 0);
sqlite3_int64 blocknum = sqlite3_column_int64(m_blockPosListStatement, 0);
vec.push_back(blocknum);
} else if (result == SQLITE_BUSY) // Wait some time and try again
usleep(10000);
else
break;
}
} else {
sqlite3_reset(m_blockPosListStatement);
throw std::runtime_error("Failed to get list of MapBlocks");
}
sqlite3_reset(m_blockPosListStatement);
return vec;
}

DBBlockList DBSQLite3::getBlocksOnZ(int zPos)
void DBSQLite3::prepareBlocksOnZStatement(void)
{
//std::string sql = "SELECT pos, data FROM blocks WHERE (pos >= ? AND pos <= ?)";
std::string sql = "SELECT pos, data FROM blocks WHERE (pos BETWEEN ? AND ?)";
if (!m_blocksOnZStatement && sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_blocksOnZStatement, 0) != SQLITE_OK) {
throw std::runtime_error("Failed to prepare statement (blocksOnZStatement)");
}
}

void DBSQLite3::prepareBlockOnPosStatement(void)
{
std::string sql = "SELECT pos, data FROM blocks WHERE (pos >= ? AND pos <= ?)";
if (!m_getBlocksOnZStatement && sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_getBlocksOnZStatement, 0) != SQLITE_OK) {
throw std::runtime_error("Failed to prepare statement");
std::string sql = "SELECT pos, data FROM blocks WHERE pos == ?";
if (!m_blockOnPosStatement && sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_blockOnPosStatement, 0) != SQLITE_OK) {
throw std::runtime_error("Failed to prepare SQL statement (blockOnPosStatement)");
}
}

// Apparently, this attempt at being smart, is actually quite inefficient for sqlite.
//
// For larger subsections of the map, it performs much worse than caching an entire
// world row (i.e. z coordinate). In cases where it *is* more efficient, no caching is
// much more efficient still.
//
// It seems that any computation on pos severely affects the performance (?)...
//
// For the moment, this function is not used.
void DBSQLite3::prepareBlocksYRangeStatement(void)
{
// This one seems to perform best:
std::string sql = "SELECT pos, data FROM blocks WHERE (pos BETWEEN ?1 AND ?2) AND (pos-?3)&4095 == 0 AND (pos-?3 BETWEEN ?4 AND ?5)";
// These perform worse:
//std::string sql = "SELECT pos, data FROM blocks WHERE (pos BETWEEN ?1 AND ?2) AND (pos-?3 BETWEEN ?4 AND ?5) AND (pos-?3)&4095 == 0";
//std::string sql = "SELECT pos, data FROM blocks WHERE (pos BETWEEN ?1 AND ?2) AND (pos-?3 BETWEEN ?4 AND ?5)";
//std::string sql = "SELECT pos, data FROM (select pos, data FROM blocks WHERE (pos BETWEEN ?1 AND ?2) ) WHERE (pos-?3 BETWEEN ?4 AND ?5) AND (pos-?3)&4095 == 0";
//std::string sql = "SELECT pos, data FROM (select pos, (pos-?3) AS pos3, data FROM blocks WHERE (pos BETWEEN ?1 AND ?2) ) WHERE (pos3 BETWEEN ?4 AND ?5) AND (pos3)&4095 == 0";
//std::string sql = "SELECT pos, data FROM (select pos, (pos-?3) AS pos3, data FROM blocks WHERE (pos BETWEEN ?1 AND ?2) ) WHERE (pos3 BETWEEN ?4 AND ?5)";
if (!m_blocksYRangeStatement && sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_blocksYRangeStatement, 0) != SQLITE_OK) {
throw std::runtime_error("Failed to prepare SQL statement (blocksYRangeStatement)");
}
}

void DBSQLite3::cacheBlocksOnZRaw(int zPos)
{
prepareBlocksOnZStatement();

DBBlockList blocks;

sqlite3_int64 psMin;
sqlite3_int64 psMax;

psMin = (static_cast<sqlite3_int64>(zPos) * 16777216l) - 0x800000;
psMax = (static_cast<sqlite3_int64>(zPos) * 16777216l) + 0x7fffff;
sqlite3_bind_int64(m_getBlocksOnZStatement, 1, psMin);
sqlite3_bind_int64(m_getBlocksOnZStatement, 2, psMax);

sqlite3_bind_int64(m_blocksOnZStatement, 1, psMin);
sqlite3_bind_int64(m_blocksOnZStatement, 2, psMax);

cacheBlocks(m_blocksOnZStatement);
}

DBBlock DBSQLite3::getBlockOnPosRaw(sqlite3_int64 psPos)
{
prepareBlockOnPosStatement();

DBBlock block(0,(const unsigned char *)"");
int result = 0;

sqlite3_bind_int64(m_blockOnPosStatement, 1, psPos);

while (true) {
result = sqlite3_step(m_getBlocksOnZStatement);
result = sqlite3_step(m_blockOnPosStatement);
if(result == SQLITE_ROW) {
sqlite3_int64 blocknum = sqlite3_column_int64(m_getBlocksOnZStatement, 0);
const unsigned char *data = reinterpret_cast<const unsigned char *>(sqlite3_column_blob(m_getBlocksOnZStatement, 1));
int size = sqlite3_column_bytes(m_getBlocksOnZStatement, 1);
blocks.push_back(DBBlock(blocknum, std::basic_string<unsigned char>(data, size)));
sqlite3_int64 blocknum = sqlite3_column_int64(m_blockOnPosStatement, 0);
const unsigned char *data = reinterpret_cast<const unsigned char *>(sqlite3_column_blob(m_blockOnPosStatement, 1));
int size = sqlite3_column_bytes(m_blockOnPosStatement, 1);
block = DBBlock(blocknum, std::basic_string<unsigned char>(data, size));
m_blocksUnCachedCount++;
//std::cerr << "Read block " << blocknum << " from database" << std::endl;
break;
} else if (result == SQLITE_BUSY) { // Wait some time and try again
usleep(10000);
} else {
break;
}
}
sqlite3_reset(m_getBlocksOnZStatement);
sqlite3_reset(m_blockOnPosStatement);

return blocks;
return block;
}

DBBlock DBSQLite3::getBlockOnPos(int64_t iPos)
void DBSQLite3::cacheBlocksYRangeRaw(int x, int y, int z)
{
std::string sql = "SELECT pos, data FROM blocks WHERE pos == ?";
if (!m_getBlocksOnPosStatement && sqlite3_prepare_v2(m_db, sql.c_str(), sql.length(), &m_getBlocksOnPosStatement, 0) != SQLITE_OK) {
throw std::runtime_error("Failed to prepare statement");
}
DBBlock block(0,(const unsigned char *)"");
prepareBlocksYRangeStatement();

sqlite3_int64 psZPosFrom = (static_cast<sqlite3_int64>(z) << 24) - 0x800000;
sqlite3_int64 psZPosTo = (static_cast<sqlite3_int64>(z) << 24) + 0x7fffff;
sqlite3_int64 psPosZero = static_cast<sqlite3_int64>(x);
psPosZero += static_cast<sqlite3_int64>(z) << 24;
sqlite3_int64 psYPosFrom = 0;
sqlite3_int64 psYPosTo = static_cast<sqlite3_int64>(y) << 12;

sqlite3_bind_int64(m_blocksYRangeStatement, 1, psZPosFrom);
sqlite3_bind_int64(m_blocksYRangeStatement, 2, psZPosTo);
sqlite3_bind_int64(m_blocksYRangeStatement, 3, psPosZero);
sqlite3_bind_int64(m_blocksYRangeStatement, 4, psYPosFrom);
sqlite3_bind_int64(m_blocksYRangeStatement, 5, psYPosTo);

sqlite3_int64 psPos = static_cast<sqlite3_int64>(iPos);
sqlite3_bind_int64(m_getBlocksOnPosStatement, 1, psPos);
cacheBlocks(m_blocksYRangeStatement);
}

void DBSQLite3::cacheBlocks(sqlite3_stmt *SQLstatement)
{
int result = 0;
while (true) {
result = sqlite3_step(m_getBlocksOnPosStatement);
result = sqlite3_step(SQLstatement);
if(result == SQLITE_ROW) {
sqlite3_int64 blocknum = sqlite3_column_int64(m_getBlocksOnPosStatement, 0);
const unsigned char *data = reinterpret_cast<const unsigned char *>(sqlite3_column_blob(m_getBlocksOnPosStatement, 1));
int size = sqlite3_column_bytes(m_getBlocksOnPosStatement, 1);
block = DBBlock(blocknum, std::basic_string<unsigned char>(data, size));
break;
sqlite3_int64 blocknum = sqlite3_column_int64(SQLstatement, 0);
const unsigned char *data = reinterpret_cast<const unsigned char *>(sqlite3_column_blob(SQLstatement, 1));
int size = sqlite3_column_bytes(SQLstatement, 1);
m_blockCache[blocknum] = DBBlock(blocknum, std::basic_string<unsigned char>(data, size));
m_blocksCachedCount++;
//std::cerr << "Cache block " << blocknum << " from database" << std::endl;
} else if (result == SQLITE_BUSY) { // Wait some time and try again
usleep(10000);
} else {
break;
}
}
sqlite3_reset(m_getBlocksOnPosStatement);
sqlite3_reset(SQLstatement);
}

return block;
DBBlock DBSQLite3::getBlockOnPos(int x, int y, int z)
{
sqlite3_int64 psPos;
psPos = static_cast<sqlite3_int64>(x);
psPos += static_cast<sqlite3_int64>(y) << 12;
psPos += static_cast<sqlite3_int64>(z) << 24;
//std::cerr << "Block " << x << "," << y << "," << z << " -> " << psPos << std::endl;

m_blocksReadCount++;

BlockCache::const_iterator DBBlockSearch;
DBBlockSearch = m_blockCache.find(psPos);
if (DBBlockSearch == m_blockCache.end()) {
if (cacheWorldRow) {
m_blockCache.clear();
cacheBlocksOnZRaw(z);
DBBlockSearch = m_blockCache.find(psPos);
if (DBBlockSearch != m_blockCache.end()) {
return DBBlockSearch->second;
}
else {
return DBBlock(0, (const unsigned char *)"");
}
}
else {
return getBlockOnPosRaw(psPos);
}
}
else {
return DBBlockSearch->second;
}
}

38 changes: 33 additions & 5 deletions db-sqlite3.h
View file
Original file line number Diff line number Diff line change
Expand Up @@ -3,20 +3,48 @@

#include "db.h"
#include <sqlite3.h>
#if _cplusplus == 201103L
#include <unordered_map>
#else
#include <map>
#endif
#include <string>
#include <sstream>

class DBSQLite3 : public DB {
#if _cplusplus == 201103L
typedef std::unordered_map<uint64_t, DBBlock> BlockCache;
#else
typedef std::map<uint64_t, DBBlock> BlockCache;
#endif
public:
bool cacheWorldRow;
DBSQLite3(const std::string &mapdir);
virtual int getBlocksUnCachedCount(void);
virtual int getBlocksCachedCount(void);
virtual int getBlocksReadCount(void);
virtual std::vector<int64_t> getBlockPos();
virtual DBBlockList getBlocksOnZ(int zPos);
virtual DBBlock getBlockOnPos(int64_t iPos);
virtual DBBlock getBlockOnPos(int x, int y, int z);
~DBSQLite3();
private:
int m_blocksReadCount;
int m_blocksCachedCount;
int m_blocksUnCachedCount;
sqlite3 *m_db;
sqlite3_stmt *m_getBlockPosStatement;
sqlite3_stmt *m_getBlocksOnZStatement;
sqlite3_stmt *m_getBlocksOnPosStatement;
sqlite3_stmt *m_blockPosListStatement;
sqlite3_stmt *m_blocksOnZStatement;
sqlite3_stmt *m_blockOnPosStatement;
sqlite3_stmt *m_blocksYRangeStatement;
std::ostringstream m_getBlockSetStatementBlocks;
BlockCache m_blockCache;

void prepareBlocksOnZStatement(void);
void prepareBlockOnPosStatement(void);
void prepareBlocksYRangeStatement(void);
void cacheBlocksYRangeRaw(int x, int y, int z);
void cacheBlocksOnZRaw(int zPos);
DBBlock getBlockOnPosRaw(sqlite3_int64 psPos);
void cacheBlocks(sqlite3_stmt *SQLstatement);
};

#endif // _DB_SQLITE3_H
6 changes: 4 additions & 2 deletions db.h
View file
Original file line number Diff line number Diff line change
Expand Up @@ -14,8 +14,10 @@ typedef std::list<DBBlock> DBBlockList;
class DB {
public:
virtual std::vector<int64_t> getBlockPos()=0;
virtual DBBlockList getBlocksOnZ(int zPos)=0;
virtual DBBlock getBlockOnPos(int64_t iPos)=0;
virtual int getBlocksUnCachedCount(void)=0;
virtual int getBlocksCachedCount(void)=0;
virtual int getBlocksReadCount(void)=0;
virtual DBBlock getBlockOnPos(int x, int y, int z)=0;
};

#endif // _DB_H
7 changes: 7 additions & 0 deletions mapper.cpp
View file
Original file line number Diff line number Diff line change
Expand Up @@ -18,6 +18,8 @@

using namespace std;

#define OPT_SQLITE_CACHEWORLDROW 0x81

void usage()
{
const char *usage_text = "minetestmapper [options]\n"
Expand All @@ -36,6 +38,7 @@ void usage()
" --backend <sqlite3/leveldb>\n"
" --geometry x:y+w+h\n"
" --forcegeometry\n"
" --sqlite-cacheworldrow\n"
" --verbose\n"
"Color format: '#000000'\n";
std::cout << usage_text;
Expand All @@ -61,6 +64,7 @@ int main(int argc, char *argv[])
{"min-y", required_argument, 0, 'a'},
{"max-y", required_argument, 0, 'c'},
{"backend", required_argument, 0, 'd'},
{"sqlite-cacheworldrow", no_argument, 0, OPT_SQLITE_CACHEWORLDROW},
{"verbose", no_argument, 0, 'v'},
};

Expand Down Expand Up @@ -119,6 +123,9 @@ int main(int argc, char *argv[])
case 'H':
generator.setShading(false);
break;
case OPT_SQLITE_CACHEWORLDROW:
generator.setSqliteCacheWorldRow(true);
break;
case 'a': {
istringstream iss;
iss.str(optarg);
Expand Down