ofxSQLite? ofx[Database]?

hi,

does someone knows if there is some sort of ofxDatabase/SQLite addon?
…please reply if you know if there isn’t one, because I need one but don’t want to make the a ofxSQLite again if it’s already there :wink:

roxlu

as far as i know there isn’t but poco has an easy api for accessing sqlite databases very similar to java’s jdbc

hi arturo,

ah nice! thanks for your reply… I’ll have a look at it, maybe I can wrap it up for a simple db addon…

I need it to store user game data (highscores);

roxlu

You might want to check out the MySQL C API:
http://dev.mysql.com/doc/refman/5.0/en/c.html

I believe it does come with its own netcode, so you may want to take that into consideration… but it might make a nice base for an oF addon.

http://oppei.org/22/?p=77

Its Japonese, so i cant quote the author.

jmccd, yeh I found that one as well. It’s a example of using sqlite in a oF project. Though I want to create a addon which is a bit more simpler. I think I’ll wrap up the POCO data API which is indeed very clean (thanks for the tip arturo).

@arturo do you know why the data classes arent packaged in the oF 0.6 lib?

Roxlu

yeah, we decided not to include all poco, just foundation, util and net as the whole library is really big and data and ssl seemed not so useful.

also with the poco api theoretically it should be really easy to change from sqlite to mysql. last time i tried mysql didn’t worked but they were planning to support it and indeed it appeared in the docs.

hi arturo,

ah okay I see. In the past I’ve embedded MySQL into a project which wasn’t really hard to do, though sqlite is even easier. The documentation of poco is clear on that. I will just create a tiny wrapper around it so one can easily add it as an addon.

Though I need the Data part of the POCO lib… I think I’ll put it in the ofxSQLite/lib/poco/…
but I’m not sure as it would be better if it was part of oF.

roxlu

okay I’ve downloaded poco and the data api. I’m trying to compile it using the new 1.3.5 poco lib. I’ve put the newer poco files in my project src directory under “Poco”. Tough I get these errors:

  
  
In file included from ../sqlite_addon/Poco/RefCountedObject.h:44,  
                 from ../sqlite_addon/Poco/ActiveResult.h:46,  
                 from ../sqlite_addon/Poco/ActiveRunnable.h:44,  
                 from ../sqlite_addon/Poco/ActiveStarter.h:45,  
                 from ../sqlite_addon/Poco/ActiveDispatcher.h:46,  
                 from D:\programming\c++\openframeworks-0.06\apps\myapps\sqlite_addon\Poco\ArchiveStrategy.cpp:44:  
../sqlite_addon/Poco/AtomicCounter.h: In member function `int Poco::AtomicCounter::operator++()':  
../sqlite_addon/Poco/AtomicCounter.h:158: error: invalid conversion from `volatile LONG*' to `long int*'  
../sqlite_addon/Poco/AtomicCounter.h:158: error:   initializing argument 1 of `LONG InterlockedIncrement(long int*)'  
../sqlite_addon/Poco/AtomicCounter.h: In member function `int Poco::AtomicCounter::operator++(int)':  
../sqlite_addon/Poco/AtomicCounter.h:165: error: invalid conversion from `volatile LONG*' to `long int*'  
../sqlite_addon/Poco/AtomicCounter.h:165: error:   initializing argument 1 of `LONG InterlockedIncrement(long int*)'  
../sqlite_addon/Poco/AtomicCounter.h: In member function `int Poco::AtomicCounter::operator--()':  
../sqlite_addon/Poco/AtomicCounter.h:172: error: invalid conversion from `volatile LONG*' to `long int*'  
../sqlite_addon/Poco/AtomicCounter.h:172: error:   initializing argument 1 of `LONG InterlockedDecrement(long int*)'  
../sqlite_addon/Poco/AtomicCounter.h: In member function `int Poco::AtomicCounter::operator--(int)':  
../sqlite_addon/Poco/AtomicCounter.h:179: error: invalid conversion from `volatile LONG*' to `long int*'  
../sqlite_addon/Poco/AtomicCounter.h:179: error:   initializing argument 1 of `LONG InterlockedDecrement(long int*)'  
Process terminated with status 1 (0 minutes, 1 seconds)  
  
  

Someone who knows why and how to fix it?

Okay I dropped the Poco::Data as I got some mingw compiler issues. Besides it’s not part of the oF base code so therefore I just deploy my ofxSQLite addon together with the nice amalgamation file of sqlite.

here is a example of the usage so far:

  
  
sqlite = new ofxSQLite("test.db");  
  
	sqlite->simpleQuery(""\  
		"CREATE TABLE IF NOT EXISTS scores (" \  
			" id INTEGER PRIMARY KEY AUTOINCREMENT" \  
			" ,time TEXT" \  
			", score INTEGER" \  
		");"  
	);  
  
	sqlite->simpleQuery(""\  
		"CREATE TABLE IF NOT EXISTS stats ("\  
			"id INTEGER PRIMARY KEY AUTOINCREMENT" \  
			", time TEXT" \  
		");"  
	);  
  
	sqlite->simpleQuery(""\  
		"CREATE TABLE IF NOT EXISTS game_runs( " \  
			" id INTEGER PRIMARY KEY AUTOINCREMENT" \  
			",start_time TEXT" \  
			", end_time TEXT" \  
		");"  
	);  
  
	sqlite->insert("scores")  
		.use("score", 5600)  
		.use(  
			"time"  
			,ofToString(ofGetDay())  
			 +"-" +ofToString(ofGetMonth())  
			 +"-" +ofToString(ofGetYear())  
			 +" " +ofToString(ofGetHours())  
			 +":" +ofToString(ofGetMinutes())  
			 +":" +ofToString(ofGetSeconds())  
	).execute();  
  
  
	ofxSQLiteSelect sel = sqlite->select("id, time").from("scores");  
	sel.execute().begin();  
  
	while(sel.hasNext()) {  
		int id = sel.getInt();  
		std::string name = sel.getString();  
		cout << id << ", " << name << endl;  
		sel.next();  
	}  
  

I need to add some things and when done I’ll put it on google code.

roxlu

Wow, that looks very intuitive and easy to understand, coming from a brief background in database-driven website development. It feels a lot like how (IMHO) SQL should feel in C++.

EDIT: About the above compiler issues, you might need to const_cast<> away the volatility on those volatile ints to make it work properly. Google ‘volatile’ and see why; to sum it up, it’s a specifier that makes a variable update in memory as soon as it is told to, despite optimizations by the compiler pushing it to the end of the function or until a timestep. This is one of the things which made thread locking possible in optimized environments. Also, according to the Standard C++ Library specifications and additions, the const_cast can be used to cast away both const-ness and volatility.

EDIT: EDIT: I guess the book I have is too old… It was proposed for the first C++ Draft Standard, but never realized. The volatility of a variable can’t be cast away by const_cast, at least not in recent versions of gcc and msvcc.

I’ve just created a google code project for the test version of ofxSQLite.

Using svn, you can check it out from this location:

  
  
svn checkout [http://ofxsqlite.googlecode.com/svn/trunk/](http://ofxsqlite.googlecode.com/svn/trunk/) ofxsqlite-read-only  
  

ofxSQLite helps you to embed the sqlite3 database into your projects. It has a OOP interface for Inserting, Updating, Deleting and Selecting data from the database. The addons if sufficient for my current project. If I need to add some features please tell me.

roxlu

Hi roxlu, i’m interested in using your extension. But i’m having a bit of trouble getting your example to run. This has to do with not understanding exactly where the files should go in relation to one another inside an OF project.

Here’s what i’ve tried:

but i get the following error on compile:

Sorry for the novice question, any pointers would be appreciated!

it looks like you’ve included “main.cpp” twice, thus the “duplicate symbols” error. (and it’s visible twice in the src folder).

hope that helps!

  • zach

Thanks zach. There were other duplicates in there too (i was surprised that xcode re-adds the same file as many times as you drag it into the resource sidebar) Removing all the duplicates got the example running.

The addons if sufficient for my current project. If I need to add some features please tell me.

Not sure if its currently possible, but i’d like to do selects which use filters like ‘WHERE x != y’, can this be done already? (my fiddlings didn’t result in getting this working).

Also: i’m having a little difficulty understanding how to access particular columns from a result row, and exactly how to predict what sel.getString and sel.getInt are returning. Here’s a bit of code that shows the point i’m at:

  
ofxSQLiteSelect sel = db->select("id, sex, genome, pointsperfight")  
	.from("faces")  
	.limit(1)  
	.order("random()")  
	.execute().begin();  
		  
	  
	face f;  
        while(sel.hasNext()) {  
        // not sure how to access the desired columns correctly  
        f.id = //?  
        f.sex = //?  
        f.genome = //?  
        f.pointsperfight = //?  
        sel.next();  
	}  

Hi,
I don’t know if anybody experienced some strange issues with ofxSQLite and VisualStudio :frowning:

Anna

This is a fantastic addon, thanks for sharing :slight_smile: