Working with databases

Hi everyone,

I need to use a database for a very simple task, few users and very small data.

I saw ofxSQLiteCpp by @bakercp which looks great but afterwards I realised that the only way to use SQLite over network is by updating the database file… is that correct?

ofxSQLite looks a bit out of date… didnt run at first try.
and ofxMySQL looks ok too but i guess will force me to work on remote, with a server set up and all… or set up a server in my own computer…

Could anyone point me out if a remote SQLite is a really bad idea or what?
It’s a long time i don’t do any databases and i guess there must be a very fast and good way to work today.

Any experiences?

Can you give more context on what you are trying to do. If you want multiple instances of your app accessing and updating the same database, you will have to use sql. On the other hand sqlite means each instance of your application gets its own database.

I’ve used sqlite before, but not sql. If you are on a mac, you dont need an addon for sqlite because the library comes with xcode. Just link it through your build phases.

It really depends on what you want to do. You could also consider running a php , python or ruby server somewhere as an API. Then you could serve json data to your apps which is easy to read with c++.

you can work with sqllite but as you say the only way to share it over the network is to share the file and access it through a the network shared file. sqlite supports multiple users simultaneously, the only problem is that it locks the whole database instead of each row or table whenever someone is accessing it. if you don’t have many simultaneous connections you should be fine

mysql is really easy to setup, and if you want to access remotely it’s probably going to be easier than setting up a network share for the file and configure it in every computer from where you want to access since in this case you just need to setup the server to work on the network and everyone else just needs to access to that ip.

as @underdoeg says setting up a php or python web service is also a good solution if you want to avoid interfacing with the DB directly from c++ but depending on what you are doing it might be just easier to setup the DB server and access it from c++.

don’t know much more options, the latest thing in DBs is nosql DBs like mongo DB or coach DB but never used them and not sure how good the interface with c++ might be.

I have used mongodb with c++ and it actually has a very nice and easy to use interface. https://github.com/mongodb/mongo-cxx-driver/wiki/Tutorial#the-c-bson-library

2 Likes

Hey thank you so much for all your replies!
Its really nice to see such feedback in this forum :smile:

Ok, what I’m going to do is something similar to a image tagging app. I will have ROIs in images and different ways to store region information. then each region is linked to a message. Users must be able to search images by region position, which means that at some point i need to check if certain point is within a region, and also if region intersects region, also search through messages and/or regions… then retrieve matching images, messages…

This is quite a prototype, to start, i will have few users (lets say 100) coming from different applications. From your replies It seems that any technology could be used. Then I would like to go for the easiest one.

Is SQLite so much easier than mySQL?

Oh! I just thought on the case where a user could not access the network… that would give me a trouble if using mySQL… so i guess SQLite is much better in this case…

I started to look at mySQL and it seems doable. I already have a DB on a server and connected to it. the addon ofxmySQL helps me very much to plug and play.

Please let me know if anyone can see in advance any issues i could face.

MySQL and SQLite both have a widely the same SQL language so the usage is similar. What is different with mysql though is that you have to connect to a remote server and then you have to handle timeout events, etc…

But I think you won’t be happy managing 100 clients with just one mysqlite database. As Arturo said, mysqlite is only partially multi user capable and accessing an sqlite db over the network is additional work which mysql will handle for you.

What do you mean by “different applications”? All coming from the same OF app on different devices or do you mean different kind of applications?

For a prototype MySQL will probably be fine but if you plan on doing this kind of thing bigger, I’d go with an API. It’s usually a good idea to have an abstraction layer between your application and the storage. That way it is easier to handle changes in the database, catch errors. Also languages like python are easier to code with when it comes to juggling around data IMO.

1 Like

btw, if you want this to work on the internet as opposed to a local network then a webservice in php, python or even OF itself, is the way to go, having a db facing the internet directly is a really bad idea

Thank you very much again for your replies!

@underdoeg yes i meant different OF apps. I didn’t specify because i had no idea this would be relevant to the DB.

Ok, if understand well, the best way to go is to have an API (OF,python, js or other) on the server which does the queries to the database…
I will try to go this way. MySQL, Py->Json ->OF. which looks cool. No idea if i will manage to do that… and if i fail i might go SQLite with hiccups.

Thank you again!
you guys gave me a really nice advise!

1 Like

yes a php or python script working on a web server in the server and doing the queries to the db, then returning json is the most standard. you can probably even look for some framework that will generate a model from parsing the DB automatically and return json through queries. Then in the OF side you can use ofLoadURL to query that script

1 Like

I can recommend Flask & SQLAlchemy if you plan on working with python. But there are tons of tools around…

http://flask.pocoo.org/
http://www.sqlalchemy.org/
http://pythonhosted.org/Flask-SQLAlchemy/

2 Likes