Word game read,verify words

Hi

I’m currently developing a word game. But i’m stuck on storing words. 50k+ words
I’m thinking of .txt file, is it possible?

I have read this thread [ Solved ] Dictionary for Word Based Game... but I didn’t get what they were talking about.

@slackmoehrle Hi slackmoe it’s me again.

I followed this guide here A simple Tutorial How to use SQLite in cocos2d-x ……(Tutorial by YUYE)

I successfully connected to the database
But im stuck on reading/retrieving data

result = sqlite3_get_table(db,"select * from words",&re,&r,&c,NULL);

CCLog("row is %d,column is %d",r,c);
CCLog("Result get_table %d",result);

CClog:

  1. row is 0, column is 0
  2. Result get_table 1

It is actually unsuccessful, I tried connecting to another db name even though it is not there the response is still successful

Perhaps the table is empty? How did you populate the table with words?

@mannewalis I used sqlite browser. I created the database there and imported a textfile of 50k+ words, and it was successfully populated. My question is, where should I put my .db3? In Resources?

yeah, and if on iOS, don’t forget to add it to the xcode project so it makes it into the bundle.

I see, thanks @mannewalis. But I still can’t get it working. Here’s my code:

sqlite3 *pdb=NULL;  
std::string path=CCFileUtils::sharedFileUtils()->getWritablePath()+"words.db3";  

std::string sql;  
int result;  
result=sqlite3_open(path.c_str(),&pdb);  
if(result!=SQLITE_OK)  
    CCLog("open database failed,  number%d",result); 

char **re;  

int r,c;  

sqlite3_get_table(pdb,"select * from wordwizard",&re,&r,&c,NULL);  

CCLog("row is %d,column is %d",r,c);

dbname = words
table = wordwizard

problem is: row is 0 column is 0
Did I miss something?

Im developing for android and I didn’t get this part here from the tutorial, I didn’t add this lines to my code yet.

std::string dbPath = CCFileUtils::sharedFileUtils()->fullPathForFilename("words.db3");

#if CC_TARGET_PLATFORM == CC_PLATFORM_ANDROID
        dbPath  = CCFileUtils::sharedFileUtils()->getWritablePath();
        dbPath  += "/words.db3";

        FILE* file = fopen(dbPath.c_str(), "r");
        if (file == nullptr)
        {
            unsigned long size;
            const char* data = (char*) CCFileUtils::sharedFileUtils()->getFileData("dict.db", "rb", &size);
            file = fopen(dbPath.c_str(), "wb");
            fwrite(data, size, 1, file);
            CC_SAFE_DELETE_ARRAY(data);
        }
        fclose(file);
#endif

where should I put these lines?

That looks like it just saves out the database the first time if it doesn’t exist.

Perhaps you need a ; after the query?

Have you tried using sqlite on the command line and running the query at the sqlite prompt and see what it says?

i.e. sqlite words.db3

sqlite> select * from wordwizard;

@mannewalis seems to be like that

Ill try to add ;

And yes I tried that too, it showed the 50k+ words.

@mannewalis I added ; already still row is 0 and column is 0

do you know where to put these lines?

 std::string dbPath = CCFileUtils::sharedFileUtils()->fullPathForFilename("words.db3");

#if CC_TARGET_PLATFORM == CC_PLATFORM_ANDROID
        dbPath  = CCFileUtils::sharedFileUtils()->getWritablePath();
        dbPath  += "/words.db3";

        FILE* file = fopen(dbPath.c_str(), "r");
        if (file == nullptr)
        {
            unsigned long size;
            const char* data = (char*) CCFileUtils::sharedFileUtils()->getFileData("dict.db", "rb", &size);
            file = fopen(dbPath.c_str(), "wb");
            fwrite(data, size, 1, file);
            CC_SAFE_DELETE_ARRAY(data);
        }
        fclose(file);
#endif

I’m not sure where you would put that code.

Looking at the docs, it looks like sqlite3_get_table is deprecated, so perhaps it doesn’t work properly anymore? Maybe try to use sqlite3_exec instead, it’s a little more complicated, but perhaps you’ll have more luck with that.

@mannewalis I tried inserting the lines for android, but app crashes. I tried inserting/deleting a record using sqlite3_record but errors. Anyway thanks for trying to help

If you can zip up a test case, can try to help you figure it out.

i tried SQL. it was too slow. went with pure byte array with another c array of pointers to words within. not got the timings to hand but think 10x improvement.

https://play.google.com/store/apps/details?id=uk.co.best_bit.pluckywords&hl=en

or, without cocos2dx,

https://play.google.com/store/apps/details?id=uk.co.best_bit.anagrammania

thank you :slight_smile:

1 Like

how did you populate the array?

I started with SQL but for a word list, at least without definitions it was a big waste.

//This is orders of magnitude faster than the std::vector/set
//Load time down from 14seconds to 0.3-0.6.

So I was only talking about load time. But it is obviously a huge impact. Depends on if you can do a load of other stuff during that time. The data structure I use is:

struct nativeDict {
    unsigned char *byteBuf;
    char **wordPtr;
    unsigned int wordCnt;

The speed boost comes from using strtok and mutable byteBuf to load words. Otherwise you’re doing an alloc for each word. Load the file into an array using C++ or C or SDL or cocos2dx functions the just hit it up with the libc strtok rock.

2 Likes

Thanks Jon, ill try to apply your ways too. Noted