Hello,
I have been strugling with sqlite3 on cocos2d-x. I have seen there is some thread about it but I could not find an answer.
Currently I have populated database file (“save.db3”)
The problem is that I can not open populated database and get data from it. I would like to ask what is the best way of get data from prepopulated database.
(I would like not to parse .json or other and then save that data to database, also would like not to use cloud)
I use SQLite a lot. Show me how you are opening the db
I have added database (save.db3) to the project (draged and droped)
And I am opening database like this :
sqlite3 *pdb=NULL;
std::string path = FileUtils::getInstance()->getWritablePath() + "save.db3";
std::string sql;
int result;
result = sqlite3_open(path.c_str(),&pdb);
if(result!=SQLITE_OK)
log("open database failed, number%d", result);
This opening does not find existing database (Because the database I add is not in writable path) and creates new. What I want is just to use existing database. I have no idea where to put it or if there is other way to approach needed functionality.
You need to create your database at writeable path otherwise we cannot access it due to sandboxing etc
Ok I understand and what is best way to transfer data from existing database?
I have sql statements that I run the first time the game starts (if a database does not already exist) to populate it with defaults.
Use a SQLite client and just export and re-import. The db is just a file after all.
Ok it is getting clearer,
So the only location database could be is writable path, and if I would replace freshly new created database (…/Documents/save.db3) with my current populated database it will be just available on that device/simulator yes?
Yes that is correct.
If my app finds the db. I also check for a key to see if it has already been populated. If it has then start the app. If it hasn’t run sql statements to populate it.
What is the way you are using to populate the database?
Also can you suggest me how I could copy all data from “save.db3” to a database in a writable path?
Let me give you some code. Give me a few hours to get to this.
Yes, sure.
Here is how I implement things, feel free to ask me any questions:
#include "SQLManager.hpp"
#include "sqlite3.h"
#include "cocos2d.h"
SQLManager* SQLManager::pinstance = 0;
SQLManager::SQLManager() {}
SQLManager* SQLManager::Instance()
{
if (pinstance == 0)
{
pinstance = new SQLManager;
pinstance->initInstance();
}
return pinstance;
}
void SQLManager::initInstance()
{
if (connect())
{
if (isDatabasePopulated())
{
// we have a valid database, but is it the latest version?
if (upgradeDatabase())
{
_bDatabaseReady = true;
}
else
{
std::cout << "The database failed to upgrade" << std::endl;
}
}
else
{
if (createDatabaseContents())
{
_bDatabaseReady = true;
}
else
{
std::cout << "We cannot create the database contents" << std::endl;
}
}
}
else
{
std::cout << "We cannot connect to database" << std::endl;
}
}
bool SQLManager::connect()
{
bool _bConnect = false;
_pdb = NULL;
_dbPath = cocos2d::FileUtils::getInstance()->getWritablePath() + _dbFile;
int result = sqlite3_open(_dbPath.c_str(), &_pdb);
if(result == SQLITE_OK)
{
//std::cout << "open database successfull!" << std::endl;
_bConnect = true;
}
return _bConnect;
}
bool SQLManager::isDatabasePopulated()
{
bool _bPopulated = false;
sqlite3_stmt *statement;
std::string _sql = "SELECT count(*) FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' AND name='" +
std::string("Master") +
std::string("' ORDER BY name LIMIT 1;");
//std::cout << "sql: " << _sql << std::endl;
if(sqlite3_prepare_v2(_pdb, _sql.c_str(), -1, &statement, 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);
if(result == SQLITE_ROW)
{
for(int col = 0; col < cols; col++)
{
std::string s = (char*)sqlite3_column_text(statement, col);
//std::cout << "s: " << s << std::endl;
int result = std::stoi(s);
if(result == 1) // table exists, 0 if it doesn't exist
{
//std::cout << "We have AppMaster table" << std::endl;
_bPopulated = true;
}
else
{
std::cout << "We dont have APPMaster table" << std::endl;
}
}
}
else
{
break;
}
}
sqlite3_finalize(statement);
}
return _bPopulated;
}
bool SQLManager::createDatabaseContents()
{
bool _bMainTableCreatedOK = createMainTable();
if (_bMainTableCreatedOK)
{
return true;
}
else
{
return false;
}
}
bool SQLManager::createMainTable()
{
bool _bError = false;
bool _check = false;
int result = 0;
std::string _sql;
const std::vector<std::string> AppDBMasterTableID = {"HighScore", "HighTime",
"CurrentScore", "CurrentTime", "CurrentVersion", "VersionType"};
//std::cout << "Creating DB Master Table" << std::endl;
_sql = "create table " +
std::string("Master") +
std::string(" (_id TEXT PRIMARY KEY, _value INT);");
//std::cout << "sql: " << _sql << std::endl;
result = sqlite3_exec(_pdb, _sql.c_str(), NULL, NULL, NULL);
if(result == SQLITE_OK)
{
char buffer[300];
std::vector<int> AppDBMasterTableValue = {0, 0, 0, 0, _dbVersion};
for (unsigned i = 0; i < AppDBMasterTableID.size(); i++)
{
_sql = "INSERT INTO " +
std::string("Master") +
std::string(" VALUES ('%s', '%i')");
#ifdef _PRO_VERSION_
_dbVersionType = 0;
#endif
#ifdef _FREE_VERSION_
_dbVersionType = 1;
#endif
#ifdef _LITE_VERSION_
_dbVersionType = 2;
#endif
AppDBMasterTableValue.push_back(_dbVersionType);
sprintf(buffer, _sql.c_str(),
AppDBMasterTableID.at(i).c_str(),
AppDBMasterTableValue.at(i));
result = sqlite3_exec(_pdb, buffer, NULL, NULL, NULL);
//if(result == SQLITE_OK)
//{
// std::cout << "Insert to DB Master Table complete" << std::endl;
//}
//else
//{
// std::cout << "Inserts to DB Master Table failed" << std::endl;
//}
}
if (!_bError) // there were no errors
{
_check = true;
}
}
return _check;
}
bool SQLManager::upgradeDatabase()
{
int _version = getKeyByID("CurrentVersion", "_value");
if(_version == 1) // user has 1 -> upgrade to 13
{
if (upgradeTo13())
{
return true;
}
}
else
{
// assume they are up to date
return true;
}
}
bool SQLManager::upgradeTo13()
{
// for v1.3, we need to add the VersionType key, value pair.
int result = 0;
std::string _sql;
char buffer[300];
#ifdef _PRO_VERSION_
_dbVersionType = 0;
#endif
#ifdef _FREE_VERSION_
_dbVersionType = 1;
#endif
#ifdef _LITE_VERSION_
_dbVersionType = 2;
#endif
_sql = "INSERT INTO " +
std::string("Master") +
std::string(" VALUES ('%s', '%i')");
sprintf(buffer, _sql.c_str(),
"VersionType",
_dbVersionType);
result = sqlite3_exec(_pdb, buffer, NULL, NULL, NULL);
updateKey("CurrentVersion", 13);
return true;
}
int SQLManager::executeSelectQueryReturnSingleInt(const std::string& _sql)
{
sqlite3_stmt* statement;
if (sqlite3_prepare_v2(Instance()->getDatabase(), _sql.c_str(), -1, &statement, 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);
if(result == SQLITE_ROW)
{
for(int col = 0; col < cols; col++)
{
std::string s = (char*)sqlite3_column_text(statement, col);
//std::cout << "s: " << s << std::endl;
return std::stoi(s);
}
}
else
{
break;
}
}
}
return -1;
}
int SQLManager::getKeyByID(const std::string& _key, const std::string& _value)
{
std::string _sql = "SELECT " +
std::string(_value.c_str()) +
std::string(" FROM ") +
std::string("Master") +
std::string(" WHERE _id='") +
std::string(_key.c_str()) +
std::string("' LIMIT 1;");
//std::cout << "sql: " << _sql << std::endl;
int result = executeSelectQueryReturnSingleInt(_sql);
if (result == -1)
{
return 0;
}
else
{
return result;
}
}
std::string SQLManager::getSQLToCheckCounts(const std::string& _tableName)
{
return std::string("SELECT Count(*) FROM ") + _tableName + std::string(";");
}
void SQLManager::updateKey(const std::string& _key, const int& _value)
{
// UPDATE Books SET Author='Lev Nikolayevich Tolstoy' WHERE Id=1;
std::string _sql = "UPDATE " +
std::string("Master") +
std::string(" SET _value") +
std::string("=") +
std::to_string(_value) +
std::string(" WHERE _id='") +
std::string(_key.c_str()) +
std::string("';");
//std::cout << "sql: " << _sql << std::endl;
int result = sqlite3_exec(Instance()->getDatabase(), _sql.c_str(), NULL, NULL, NULL);
if (result == SQLITE_OK)
{
//std::cout << (std::string("update to Master: ") + std::string(_key.c_str()) + std::string(" successful")) << std::endl;
}
else
{
std::cout << (std::string("update to Master: ") + std::string(_key.c_str()) + std::string(" Failed")) << std::endl;
}
}
Is it possible to copy .db3 form resource folder on iOS to writable path (Because it is possible to do it on android) ?
I am not sure without trying. Why do you need to do this? Just populate the db you create when the app is launched. I showed you this code above.
Yes thank you for the code, I have looked into and will look deeper into it later today.
I have been offered to copy .db3 file to writable path since it can be done on android, because of that I would like to know is this is possbile with iOS because I get the error on this line: (problem is with string lenght)
std::vector<std::vector<std::string> > rs = query(pdb, sqlstr.c_str());
Here is code used for that.
void GameScene::initializeDataBase() {
sqlite3 *pdb = NULL;
initDB();
std::string path = FileUtils::getInstance()->getWritablePath().append("save.db3");
log("Path: %s", path.c_str());
std::string sql;
int result;
result = sqlite3_open(path.c_str(), &pdb);
if(result != SQLITE_OK) log("open database failed, number: %d", result);
/* Get data from database */
std::string sqlstr = "select * from DIALOGS";
std::vector<std::vector<std::string> > rs = query(pdb, sqlstr.c_str());
if (rs.size() > 0) {
auto ret = rs.at(0).at(0); // get first column of first row
CCLOG(ret.c_str());
}
sqlite3_close(pdb);
}
void GameScene::initDB() {
//check for existence of the copy
std::string wpath = FileUtils::getInstance()->getWritablePath();
wpath.append("save.db3");
FILE* f = fopen(wpath.c_str(), "r");
if (f == nullptr) {
//this is the first run, lets copy the DB
CCLOG("this is the first run, lets copy the DB");
ssize_t size = 0;
const char* data = (char*)FileUtils::getInstance()->getFileData("save.db3", "rb", &size);
//open for write the file this time !
f = fopen(wpath.c_str(), "wb");
//write data read from file f
fwrite(data, size, 1, f);
CC_SAFE_DELETE_ARRAY(data);
}
}
std::vector<std::vector<std::string> > GameScene::query(sqlite3 *db, const char* query)
{
sqlite3_stmt *statement;
std::vector<std::vector<std::string> > results;
if (sqlite3_prepare_v2(db, query, -1, &statement, 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while (true)
{
result = sqlite3_step(statement);
if (result == SQLITE_ROW)
{
std::vector<std::string> values;
for (int col = 0; col < cols; col++)
{
values.push_back((char*)sqlite3_column_text(statement, col));
}
results.push_back(values);
}
else
{
break;
}
}
sqlite3_finalize(statement);
}
std::string error = sqlite3_errmsg(db);
if (error != "not an error") {
CCLOG(error.c_str());
}
return results;
}
Sorry for that many question.
You have to get to your writeable directory a little differently for android vs ios. For iOS it is path = FileUtils::getInstance()->fullPathForFilename("my.db");
Can you explain why here? I don’t ever use fullPathForFilename() ever.
path = FileUtils::getInstance()->getWritablePath();
path.append("my.db");
This doesn’t work on iOS to open an existing, populated database. Maybe it does if you’re creating a database at runtime and populating it with defaults in your code, but I have a database with a table of 200,000+ rows, so I don’t want to do that.
For iOS I can just do
path = FileUtils::getInstance()->fullPathForFilename("my.db");
int_db_open_result = sqlite3_open(path.c_str(), &pdb);
And start running queries on the pre-populated database straight away. For Android I have to getFileData on my database file and write it to the writeable path. There’s no reason to do that for iOS if you don’t have to.
I understand. With that much data I guess populating it the first time in code would take some seconds longer. You can ship a pre-populated version and just update it as needed.