Examples of extending CppSqlWrapper

gistfile1.cpp

#include "CppSqlWrapper.h"
#define INFO_TABLE "CoreInfo"
#ifndef SQL_BLOCK
#define SQL_BLOCK(a) #a
#endif

class Database : public SqlDatabase {
public:
	Database();
	virtual ~Database() {}
private:
	static void PrintSqlDebug(void*, const char* sql);
};

Database::Database()
	: SqlDatabase("my-db-file.sqlite", false)
{
	// If you want SQL statements to be traced out, uncomment this:
	//setSqlTraceHandler(&Database::PrintSqlDebug);

	// If this is a new database, set up the INFO_TABLE with the important info about this db:
	if (!tableExists(INFO_TABLE)) {
		sqlExecute("BEGIN TRANSACTION;");
		sqlExecute(SQL_BLOCK(
			CREATE TABLE CoreInfo (
				key TEXT PRIMARY KEY,
				value TEXT
			);
		));
		
		// Insert initial values:
		SqlStatement insert_cmd = sqlCompile("INSERT INTO " INFO_TABLE " VALUES (?, ?);");
		insert_cmd
			.bind("appVersion").bind(APP_VERSION_STRING).execute()
			.bind("sqliteVersion").bind(SQLiteVersion()).execute()
			.bind("schemaHash").bindNull().execute()
			.bind("deviceUUID").bind(g_DeviceId.GetBytes(), UUID::BYTES_REQUIRED).execute();
		sqlExecute("COMMIT TRANSACTION;");
	}

	// Check SQLite version in DB vs. now
	try {
		query_result = sqlQuery("SELECT value FROM " INFO_TABLE " WHERE key='sqliteVersion';");
		const int sqlite_version_in_db = ConvertVersionStringToInt(query_result.currentRow().getStringField(0));
		const int sqlite_version_current = ConvertVersionStringToInt(SQLiteVersion());
		if (sqlite_version_in_db == sqlite_version_current) {
			// All good, so continue.
		} else if (sqlite_version_in_db < sqlite_version_current) {
			// Database was last used with a lower version - should be no problem.
			// Update the SQLite version in the database:
			sqlExec("UPDATE " INFO_TABLE " SET value=%Q WHERE key='sqliteVersion';", SQLiteVersion());
		} else {
			throw std::runtime_error("Database was last updated with a newer version of the SQLite.");
		}
	} catch (SqlDatabaseException const&) {
		s3eDebugErrorShow(S3E_MESSAGE_CONTINUE, "Unable to load SQLite version from the database.");
		throw;
	}

	void Database::PrintSqlDebug(void*, const char* sql) {
		std::string msg = "SQL: ";
		msg.append(sql);
		s3eDebugTraceLine(msg.c_str());
	}
}


//// Usage ////
int main() {
	Database* pDB;
	try {
		pDB = new Database(); // Better if you make it a global singleton though...
	} catch (SqlDatabaseException const& e) {
		DisplayErrorPopup(string("Unable to initialize the app's database: ").append(e.what()));
		throw; // Re-throw the exception, for debugging purposes (allow break, preserve call stack & locals)
	} 
	auto result = pDB->sqlQuery("SELECT value from %s WHERE key='miscUpgradesDone'", INFO_TABLE);
	int miscUpgradesDone = result.hasRow() ? result.currentRow().getIntField(0) : 0;
	if(miscUpgradesDone < 1) {
		// do something.
		miscUpgradesDone = 1;
	}
	GetDB()->sqlExec("INSERT OR REPLACE INTO CoreInfo (key,value) VALUES ('miscUpgradesDone', %d)", miscUpgradesDone);
}