I have discrete screens in my game, like in the original Zelda for NES.

I’ve decided to go with MySQL to store the world data, such as screens, portals (staircases, etc), and sprites.

Anyway, there is a need to store connectivity data relating the adjacent screens to each other, in terms of which screen is north of what screen, and so on and so forth.

Should I put the connectivity data (4 pointers, one for each direction on the compass) in the screens table? Or should I normalize the hell out of it and make a separate table that encodes these connections (e.g. screen_connection table)?

Normalizing allows you to change the design easier to potentially point more than the 4 directions (maybe up, down, portals, etc). Could make for some interesting design possibilities depending on your project goals, but it complicates the queries.

If you know for certain you'll never want to change from storing the information for the 4 possible directions, then all 4 in the screens table would be fine. Simple to query, and you could add more columns easily. Not that it would be super difficult to port over to a normalized version, but it's yet another thing you'd have to do if you made that decision.

Have you considered sqlite instead of full MySQL?

I didn't know about SQLite until you brought it up.

Basically, I need to store and retrieve very large BLOBs, consisting of PNG files. Surely SQLite does this too.

Thank you.

Holy cow, SQLite is awesome. I got the BLOB insertion working, and it’s not very complicated. This is exactly what I wanted! Thank you so much khawk!

I was having difficulty finding a distilled example of how to select a blob, so I thought that I would give ChatGPT another try. I chose to ask: ‘SQLite select blob C++’ and it returned a distilled example.

#include <sqlite3.h>
#include <iostream>
#include <vector>

int main() {
    sqlite3 *db;
    sqlite3_stmt *stmt;
    const char *sql = "SELECT your_blob_column FROM your_table WHERE your_condition_column = ?";
    int rc = sqlite3_open("your_database.db", &db);

    if (rc) {
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        return rc;
    }

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (rc != SQLITE_OK) {
        std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_close(db);
        return rc;
    }

    int param_value = 123; // Example value
    sqlite3_bind_int(stmt, 1, param_value);

    rc = sqlite3_step(stmt);

    if (rc == SQLITE_ROW) {
        const void *blob = sqlite3_column_blob(stmt, 0);
        int blob_size = sqlite3_column_bytes(stmt, 0);

        std::vector<unsigned char> blobData((unsigned char*)blob, (unsigned char*)blob + blob_size);

        // Do something with blobData...
    } else if (rc == SQLITE_DONE) {
        std::cout << "No rows found." << std::endl;
    } else {
        std::cerr << "Failed to step statement: " << sqlite3_errmsg(db) << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return 0;
}

The whole code is:

// https://stackoverflow.com/questions/18092240/sqlite-blob-insertion-c
int InsertFile(const string& db_name, const string &file_name)
{
	ifstream file(file_name.c_str(), ios::in | ios::binary);
	if (!file) {
		cerr << "An error occurred opening the file\n";
		return 12345;
	}
	file.seekg(0, ifstream::end);
	streampos size = file.tellg();
	file.seekg(0);

	char* buffer = new char[size];
	file.read(buffer, size);

	sqlite3* db = NULL;
	int rc = sqlite3_open_v2(db_name.c_str(), &db, SQLITE_OPEN_READWRITE, NULL);
	if (rc != SQLITE_OK) {
		cerr << "db open failed: " << sqlite3_errmsg(db) << endl;
	}
	else {
		sqlite3_stmt* stmt = NULL;
		rc = sqlite3_prepare_v2(db,
			"INSERT INTO DEMO_TABLE(DEMO_FILE)"
			" VALUES(?)",
			-1, &stmt, NULL);
		if (rc != SQLITE_OK) {
			cerr << "prepare failed: " << sqlite3_errmsg(db) << endl;
		}
		else {
			// SQLITE_STATIC because the statement is finalized
			// before the buffer is freed:
			rc = sqlite3_bind_blob(stmt, 1, buffer, size, SQLITE_STATIC);
			if (rc != SQLITE_OK) {
				cerr << "bind failed: " << sqlite3_errmsg(db) << endl;
			}
			else {
				rc = sqlite3_step(stmt);
				if (rc != SQLITE_DONE)
					cerr << "execution failed: " << sqlite3_errmsg(db) << endl;
			}
		}
		sqlite3_finalize(stmt);
	}
	sqlite3_close(db);

	delete[] buffer;
}


int retrieve_file(const string& db_name, const string& file_name)
{
	sqlite3* db;
	sqlite3_stmt* stmt;
	const char* sql = "SELECT demo_file FROM demo_table WHERE ID = ?";
	int rc = sqlite3_open(db_name.c_str(), &db);

	if (rc) 
	{
		std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
		return rc;
	}

	rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

	if (rc != SQLITE_OK) {
		std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
		sqlite3_close(db);
		return rc;
	}

	int param_value = 1; // Example value
	sqlite3_bind_int(stmt, 1, param_value);

	rc = sqlite3_step(stmt);

	if (rc == SQLITE_ROW) {
		const void* blob = sqlite3_column_blob(stmt, 0);
		int blob_size = sqlite3_column_bytes(stmt, 0);

		std::vector<unsigned char> blobData((unsigned char*)blob, (unsigned char*)blob + blob_size);

		ofstream f(file_name.c_str(), ios_base::binary);
		f.write(reinterpret_cast<char*>(&blobData[0]), blobData.size() * sizeof(unsigned char));
		f.close();
	}
	else if (rc == SQLITE_DONE) {
		std::cout << "No rows found." << std::endl;
	}
	else {
		std::cerr << "Failed to step statement: " << sqlite3_errmsg(db) << std::endl;
	}

	sqlite3_finalize(stmt);
	sqlite3_close(db);

	return 0;
}



int main(int argc, char** argv)
{
	//string filename = "";

	//if(openFileDialog(filename))
	//	cout << filename << endl;	

	//InsertFile("test.db", "C:/temp/1.png");
	int x = retrieve_file("test.db", "C:/temp/1_out.png");

	cout << x << endl;

	return 0;
}

Out of curiosity, why do you want to store the blobs in the database? Vs. store the paths/filenames to the PNG's in the filesystem. Just curious the goal.

I guess maybe using the database might be cool, I wouldn't do it myself, but I guess u get all the features, but it would slow u down I think. Doesn't a game need max perf?

MagnusWootton said:
I guess maybe using the database might be cool, I wouldn't do it myself, but I guess u get all the features, but it would slow u down I think. Doesn't a game need max perf?

Games use relational databases for all kinds of things. Yes, performance absolutely matters but mostly in the right places at the right times.

What you're looking up, when, and where, make a huge difference. A flat data table loaded in memory can be best measured in microseconds and potentially nanoseconds, a simple local relational database is best measured in milliseconds for something simple, a remote query is best measured in fractional seconds. Each is suitable or unsuitable in different situations.

Data like quest details or a lot of bulk information during long loads is just fine in databases. Lots of persistent information also works well in databases. Do the bulk processing and pull it into faster query results that are then used moment-to-moment. For writing, similarly do the slow work of pushing out database stuff when it makes sense versus the moment-to-moment updates kept in faster memory. Games with more complex shared worlds have tiers around what gets persisted and when. Some are worth the time for atomic consistency reasons like auction trades, most are transitory data that never hits a database like your moment-to-moment ammunition count.

khawk said:

Out of curiosity, why do you want to store the blobs in the database? Vs. store the paths/filenames to the PNG's in the filesystem. Just curious the goal.

Data integrity, thanks to foreign key constraints. I’m only using SQLite for the development. I will eventually have an Export button, which spits out a directory full of files, to be used in production.