Newbe… which DB?

[D4vidrim]

Hi!

I'm starting to write a small app which should allow a few users to connect to a DB file to retrieve and save data.

There might me up to 10 users connected at the same time. Those users have different versions of Windows (XP, Vista, Win7 on both 32 and 64 bit).

I've always used Access or Oracle DB via odbc, but we've just found out that some users have problems with odbc and, since we cannot change any settings on theirs PCs to fix those problems, I'm looking for alternatives!

My question is: is it wise/possible to create a SQLite DB file on the lan on a shared folder and allow all the users to connect directly to it?
I'm a complete beginner on SQLite (I'm reading some info on their website), so I'm not yet sure about its limits.

This DB will be very small: 5-6 tables, with a maximum of a few thousands records and no more than 10 users connected at the same time (usually less than 3 people at the same time). It's not required for the DB to be be password locked, but I might need to add this feature later on if asked to.

Any suggestions will be much appreciated!
Thank you very much.

btw… I hope my english is not so bad!

[Simon]

Hi D4vidrim,
Don't worry your English is very good.
Because of the multiple user need sqlite is not what you want, mysql is:
https://www.digitalocean.com/community/ ... nt-systems
One thing I don't know about is whether it can be run on a local system. hmmm...
http://dev.mysql.com/doc/refman/5.1/en/ ... ation.html
Says yes.

Simon

[D4vidrim]

We cannot use a server for the DB, that is why I was trying with SQLite.
I'll try to have a look at postgresql and mysql to see if I come up with something useful.

Thanks

[FourthWorld]

We cannot use a server for the DB, that is why I was trying with SQLite.

Whenever you're sharing data, the machine the data resides on is effectively a server.

By adding server software like Apache to the mix you get a well-tested standards-based implementation.

While publicly-accessible Web servers often need to be powerful systems to handle a large number of requests, intranet servers can be very, very modest in both CPU speed and RAM. Intel NUCs and most cheap nettops can do very well as intranet servers.

One of the advantages of deploying systems on dedicated servers, even small and cheap ones, is that you then have a place to add other services whenever you need them.

For example, the ultra-cheap nettop I use in my office started out as a Web development server, but I've added other things to it over time so that it now provides access to LiveCode-based applications and I recently added OwnCloud to it as well. Three sets of services available to every machine in my office, on hardware that cost <$200. And with the low power consumption of most nettops it's a pretty green system as well, costing only pennies a week to have it always available.

There's a modest learning curve to setting up a Linux box with Apache, but with all the resources on the Web it's very modest. And once you have it, you have the foundation for your current project, and a system on which you can add anything else you might need in the future.

[D4vidrim]

I work for a big company who has its servers, many databases, everything managed by our IT Offices.
I use LiveCode to develop and test small project and to understand what we actually want to ask to our IT and to show them our needs.

This project is about saving basic information about invoices we receive from our suppliers, to make PDF files to allow their payments and to check this process.
I'm looking for the fastest-easiest way to make it possible, because it will need to work for a few month, maybe 1-2 years, while we wait for the real IT guys to make something more "established", I'm not sure it is the right word to use, but I hope you get the idea.

This way to go gives us more flexibility, allows us to be very productive and, at the same time, it gives to the IT more time to develop, because in the meantime we can use our own small apps.

[D4vidrim]

Correct me if I'm wrong, by I find Livecode a great developer tool for this kind of needs!
Even though I'm a beginner, in a few months I've done 3-4 apps quite useful for our office and it's just because Livecode is so easy to develop with.

Back to my first question! If I understand, SQLite allows more users to connect at the same time, but it doesn't allow multiple user to modify records. If this is right, I might stick with this db, cause I need more users to read data, but only one of them will be allowed (through the LC app) to insert and update record.

[Simon]

Hi D4vidrim,
I just ran two instances of the SQLite Example from the lessons and revDatabaseConnect worked simultaneously.
From that I'd say you were OK.

The new word around the forums seems to say always use revDatabaseDisconnect when done with your operation, that would definitely help you in using SQLite and will only slow down the operation a bit.

Simon

[SparkOut]

I haven't tested with the latest SQLite version in LC but you should be able to use SQLite with multiple users for edit. The tricky thing is to ensure that records are locked and unlocked so that concurrent edits do not clash.
A better approach in any dB scenario is to have a dB app running to which the users send their queries and have that single instance actually read and write the SQLite file.

[BvG]

It is possible to have several users write, as long as it's not too many, and especially if you refrain from using update. However SQLite warns against using it on a shared drive. Because I was scared of that, I made a socket server & client in LC, which then writes into a SQLite file locally. Because LC is single threaded, concurrent writes are then also automatically avoided. Over all this is very robust, and has been used to insert ca. 250 small entries within 30 minutes, by 4 typists.

Now of course I could have as well used a mysql or postgresql server directly, but where's the fun in that

[SparkOut]

That's just what I meant, only you said it better.

[AxWald]

Hi,

I work for a big company who has its servers, many databases, everything managed by our IT Offices.

Just an idea: Isn't it possible to persuade the IT guys to create a small database for 'in house development' only? I bet they have some database engine(s) running already (on the big servers), and adding a simple (empty) database + access rules for you on a spare system shouldn't be much work ...

That's what I'd try first.

Another idea would be to rent a tiny web server somewhere. Such have usually a MySQL running and often come for very small money. Whereas you may have problems accessing it from the outside, and accessing a web db from the outside isn't this good an idea anyways, in case the data are sensible in any way. And it can be slow/ unreliable ...

Another thing: If you get a hand on "Microsoft Visual Studio 2010 Express", it comes with a MS-SQL Server 2008 Express - no idea how much use this has, have just downloaded and not evaluated yet. (You'd need a machine running all the time for it)

Third, both MySQL and PostgreSQL are running nicely on Windows machines. PostgreSQL uses about 2MB RAM on my Win7-64 machine when idle ... (You'd need a machine running all the time for it)

At last, SQLite is an awesome tool. I'm using it as a 'cache database' for my LC stuff, and it's lightening fast, and uses very few RAM. I have concurrent read access all the time, and this works fine. For concurrent write access I'd think of ways of handle it, but might prefer to have the 'writes' to other databases that are able to handle it flawlessly, out of the box.

Hope I could help, have fun!

[AxWald]

Hi,

For concurrent write access I'd think of ways of handle it [...]

I've read a bit in the SQLite documentation today. Seems it's a bit slow in doing transactions (it does all action queries with transactions as it seems) when you fire up a series of small "INSERT INTO"s or such stuff (because it starts a separate transaction for each). It's advised that you create a full set of queries for the transaction, then enclose them with "Begin - Commit transaction".
Link: > http://www.sqlite.org/faq.html#q19

This might be a way to overcome the concurrent write access problem. Construct a set of queries that selects the data, updates it, then selects again to check if the results are good. Wrap it in a "Begin - Commit transaction" statement. Since transactions are made one after the other (as I understand it), this might work.
Link: > http://www.sqlite.org/lang_transaction.html

Disclaimer: I have not tested this. It's plain speculation. Don't try this at home, or you might fertilize your wife, cause nausea & diarrhea in your cats, dogs, hamsters and parrots, and infect your children with Scabies. Even your car might get rusty! You have been warned!

Since I'm not afraid of anything (but any computer language starting with "C") I'll try it, and report here, should I survive :)

Have fun!

[skiplondon]

You can always create he ODBC connection from within LC itself. I have had to do that many times when deploying an app across the network. That might open your possibilities up a little bit.

[StackLord]

This DB will be very small: 5-6 tables, with a maximum of a few thousands records and no more than 10 users connected at the same time (usually less than 3 people at the same time). It's not required for the DB to be be password locked, but I might need to add this feature later on if asked to.

Not sure if you ever came to a good solution however this is a great use case for LiveCloud, if you're interested in a non-sql solution, have a look.

LiveCloud.io

[D4vidrim]

This DB will be very small: 5-6 tables, with a maximum of a few thousands records and no more than 10 users connected at the same time (usually less than 3 people at the same time). It's not required for the DB to be be password locked, but I might need to add this feature later on if asked to.

Not sure if you ever came to a good solution however this is a great use case for LiveCloud, if you're interested in a non-sql solution, have a look.

LiveCloud.io

Hi,
we are currently using SQLite for our "read and write" needs to small tables. It's working well for what I've heard from my colleagues!
The app I made is pretty easy and it can connect to different databases (Oracle, SQLite, Access via odbc), depending on the task the user needs to accomplish. Unfortunately when I try to connect directly to Oracle (no odbc needed) via OS X it doesn't work at all, while the same code works perfectly fine on Windows (I have the LC commercial edition).

I will certainly have a look to LiveCloud!