Recommendations for Database

So, I am just starting to play around with databases and I need some inspiration from you guys for which way to go. The amount of possible database solutions is simply overwhelming.

My first “test” project looks like this:
A Mac on my network should use a small Xojo app to monitor the room temperature. This information should be written to a database. Other clients on the network should be able to access this database too and get the temperature readings - maybe plot a nice chart…

From my current research - I have watched several videos on youtube from Mr. Levabre - this seems to be a rather easy project. But here are my concerns:

If I use MySQL I will need to purchase the DBAccess license for Xojo, OR I need to install the Xojo IDE on all clients. This is too expensive for a simple home project. :frowning:
If I use the built in SQLite I can’t get the data from other clients on the network without additional programming work. Of cause I could write a “DB Server” which uses SQLite and “DB Clients” but that’s way bejond the scope of my project. :frowning:
Since it is a “home” project which I will never deploy, I don’t want to use a commercial solution.
I’d like to use Xojo as front-end because I could even build a Web-App and check the temp on my iPhone.
As the project evolves, I may eventually add other machines that measure the temperature and send it to the Database.

Any help is appreciated.

Try CubeSQL. Up to 3 users it’s free.

Have a look at Brad Hutchinson’s StudioStable database. It is based on SQlite so you don’t need the database license, has a free server, and can handle multiple users

http://www.studiostable.com/database

If the data are important for you, remember to add Import and Export features in your application.

Also, at debug time, you will have data to import / export if the data base file crashed.

[quote=72862:@Sebastian Sparrer]So, I am just starting to play around with databases and I need some inspiration from you guys for which way to go. The amount of possible database solutions is simply overwhelming.

My first “test” project looks like this:
A Mac on my network should use a small Xojo app to monitor the room temperature. This information should be written to a database. Other clients on the network should be able to access this database too and get the temperature readings - maybe plot a nice chart…

From my current research - I have watched several videos on youtube from Mr. Levabre - this seems to be a rather easy project. But here are my concerns:

If I use MySQL I will need to purchase the DBAccess license for Xojo, OR I need to install the Xojo IDE on all clients. This is too expensive for a simple home project. :frowning:
If I use the built in SQLite I can’t get the data from other clients on the network without additional programming work. Of cause I could write a “DB Server” which uses SQLite and “DB Clients” but that’s way bejond the scope of my project. :frowning:
Since it is a “home” project which I will never deploy, I don’t want to use a commercial solution.
I’d like to use Xojo as front-end because I could even build a Web-App and check the temp on my iPhone.
As the project evolves, I may eventually add other machines that measure the temperature and send it to the Database.

Any help is appreciated.[/quote]

You CAN use sqlite with several readers & write as long as you turn on the multiuser flag
What you WONT have is a way for a remote machine to connect to the database on another machine
But if all the apps writing to & read from the database are on one machine you can have several quite safely

We use SQLite in a multi-user environment without issues, so what Norman said works. Our database file is on a shared network drive while the application is on the user’s local PC. A text file on each computer includes a path to database so it can be moved as necessary.

If you need something that is accessible from outside your network you might need to move to a cloud option such as Windows Azure SQL which works quite well.

This is definitely NOT a good plan. If you had any idea how much money I’ve made over my career repeatedly fixing this problem… Network file systems just are not designed for this. If multiple computers need to share one database, you need a “server” to broker the requests and a more robust protocol to connect clients with server.

[quote=72910:@Joseph Evert]We use SQLite in a multi-user environment without issues, so what Norman said works. Our database file is on a shared network drive while the application is on the user’s local PC. A text file on each computer includes a path to database so it can be moved as necessary.
[/quote]
This is one of the VERY few places where sharing a Sqlite DB might _not_work very well

[quote=72874:@Markus Winter]Have a look at Brad Hutchinson’s StudioStable database. It is based on SQlite so you don’t need the database license, has a free server, and can handle multiple users

http://www.studiostable.com/database[/quote]
Sorry - Hutchings. Wrote from Memory.

Check out PostgreSQL. It’s free, cross platform, fully supported by XOJO and very fast. If your on a Mac you can use the Heroku Postgres.app 1-click installer. It does take a bit of work to configure access, but this can be scripted and once you get the hang of it you can do pretty much anything you want.

The issue is, he doesn’t want to pay for a license for something with such a limited use, and doesn’t want to have to run the app through the IDE.

WHY?

I can only speak from experience in what I have done. Our database file resides on our server and the application resides locally on the computer. We do have some individuals running the application on the server on a remote application broker as well.

We actually have buildings connected from 1/8 mile away wirelessly with TrendNet using a POE AccessPoint. No issues.

We typically have 4 or 5 users at a time, maybe two or three inputting the balance reading the database. Database has about 100k records.

Only have had a few instances which were related to network connectivity issues.

How else would you use SQLite in a multiuser environment if the database is not accessible to multiple users?

[quote=72955:@Joseph Evert]Only have had a few instances which were related to network connectivity issues.
[/quote]

Timed unluckily, this can hose your SQLite database. See discussion at the bottom of this page:

https://www.sqlite.org/whentouse.html

SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption.

If you need a free, super easy to deploy server that might meet your needs, check out Studio Stable Database.

http://www.studiostable.com/database

Because network file systems have issues - see http://sqlite.org/faq.html#q5
Its not a Xojo issue or really even a sqlite issue - its that network file systems may not do the right thing

It’s an issue of multiple users vs. multiple clients. You really don’t want multiple clients connecting to SQLite as the file could get corrupted. Instead, you can have a single client handle the multiple users. This is essentially what you get with a web app, Studio Stable Database and CubeSQL.

Allowing multiple clients to connect to and modify a SQLite database on a network file share is an invitation for disaster.

And I love living on the edge of disaster baby!!!

From the docs

FAT File system Win95/98/ME, really? File locking doesn’t work on Windows Server 2008R2? It says file locking is broken on “many” NFS implementations - which? This should be readily defined somewhere. Then it goes on to say:

I really think it comes down to is whether the locking mechanism on the Network File System you are using is broken or not. IF the file locking mechanism works the database will return an error if two applications are trying to write to the DB. It’s up to the application on how to deal with, bail out, retry, etc. I built a logging system in our app to log these and we see it occasionally but the application deals with it like it should - it retries a few times.

Again, I’ve tested and tested trying to do concurrent writes and cause a problem - I can’t.

For the original posts SQLite is perfect solution for him. He’s logging temperature, what could he be taking a few samples a second? Then he has a few people reviewing the data?

[quote=73020:@Joseph Evert]And I love living on the edge of disaster baby!!!
[/quote]
Apparently since I didn’t say what you quoted me as saying :stuck_out_tongue:
Paul did

That’d be a fair assessment - although many do not - there are some that do and so it would work
No idea which Dr Hipp et al have actually tested to see if they do work

[quote=73020:@Joseph Evert]I really think it comes down to is whether the locking mechanism on the Network File System you are using is broken or not. IF the file locking mechanism works the database will return an error if two applications are trying to write to the DB. It’s up to the application on how to deal with, bail out, retry, etc. I built a logging system in our app to log these and we see it occasionally but the application deals with it like it should - it retries a few times.
[/quote]

You’re not going to get an error when the NFS allows two clients to write simultaneously to the same portion of a file. Downstream, you’ll get a garbage read, indicating a corrupt file. That’s your potential problem.

Fine if you want to do it on your own project, but the caveat list is too long for this kind of recommendation to go unchallenged. On the list of rookie mistakes programmers can make, this one is in a class by itself.

[quote=73026:@Brad Hutchings]You’re not going to get an error when the NFS allows two clients to write simultaneously to the same portion of a file. Downstream, you’ll get a garbage read, indicating a corrupt file. That’s your potential problem.

Fine if you want to do it on your own project, but the caveat list is too long for this kind of recommendation to go unchallenged. On the list of rookie mistakes programmers can make, this one is in a class by itself.[/quote]

What can I say, I’m in Rookie class by myself Brad! You have such a way about being insulting and arrogant - you’re response speaks volumes on your character. Had you stopped at your first sentence I might have engaged further. I was certainly open to a intelligent conversation on the matter and perhaps teach you a thing and you might teach me a thing or two and people who search on the subject would get the benefit. I have done some extensive testing on the subject.

BTW: There is absolutely no way I would use a product being offered by you. The caveat list is too long for this kind of recommendation to go unchallenged! It’s not a product issue but a character issue of the developer! I find it interesting that you are asking people why they are adverse to using third party products/Plug In’s. Hmmm… I wonder.

Peace out.