Possible to have local application with db on web server?

[Rog]

I have written a club management program for a small amateur sports club, that uses a SQLLite db.

Currently it is used by one person, so it sits on a single PC.

Now the club wants to have three different people use it, and I was wondering if it is possible to give the other 2 people copies of the application, and have the db (which doesn't have to be SQLLite) reside on the clubs website server? (Don't want to have to rewrite the whole thing using PHP or similar, as there are some complex SQL relationships which show up best on a local application).

If so can someone give me an idea of how the connections should work?

I have never used the web side of Delphi/Lazarus before, and am finding it hard to find a suitable tutorial.

Thanks

[Pascal]

Just use MySQL on a server. All clients can access this database over network.

[rvk]

Just use MySQL on a server. All clients can access this database over network.

Yes, So I can access that db-server too, nice

Rog is probably talking about accessing it over the internet. Normally MySQL-server are only accessible to the local server (i.e. the website on the same server). Opening up the MySQL server to the internet brings security concerns with it (which need to be addressed before doing so).

It's best to create a small PHP-page which can serve up the needed information (in JSon for example) and to which you can post information which will be stored in the server (with security-features of course). Search the internet for multi-tier solutions.

Some reading material:
http://www.tonymarston.net/php-mysql/3-tier-architecture.html

As an alternative, you could limit the MySQL exposure on the internet to certain IP-numbers but in that case you would need to have fixed IP-addresses for your clients.

[Rog]

Yes that's want I want to do.

Had a quick look at the reference you gave and, must admit, it went right over my head.

Looks like it is not as simple as I hoped it might be.

[rvk]

You could begin "simple" with creating a PHP-page yourself which provides the info in json for your clients.

Otherwise you could open up the port and make sure only certain IPs can access it.

Does your website provider allow opening up MySQL to the internet? Or do you have your own web-server?

(if the provider doesn't allow opening up MySQL, you're only option is the multi-tier one)

[DonAlfredo]

If you want a Pascal solution, you might try the mORMot.
Works with SQLite3. And automagical webserver (JSON) for Pascal clients and Java.

https://synopse.info/fossil/wiki?name=SQLite3+Framework

https://github.com/synopse/mORMot/tree/master/SQLite3/Samples

Disclaimer: I am a very frequent and happy user of the mORMot.

[john horst]

You could use samba, dropbox or similar to sync the DB. Use lock file, deal with conflicts.

[Leledumbo]

Solution 1) Just open the db server port and change the host accordingly. For SQLite, this means opening access to the file remotely, mount using whatever mechanism your OS has. Caveat: opening big security hole.
Solution 2) Wrap the data access in web services (protect with at least basic authentication, but use oauth if possible), might be difficult if you never write web apps in Pascal, but worth maintenance once you made it. Data format is of course your choice.

[john horst]

I wouldn't use OAuth, it's overkill and commonly implemented wrong both in pkg form and as user of said pkg. Opening a port to any DB for remote use, also a bad idea.

Use a tool that was designed to DO what you are asking. Dropbox. ~sqlite.lock, problem solved, you won't get fired for doing stupid things.

If it must be on server, use SSH and port forward. This solution also won't get you fired for doing stupid things.

Even a database, like CouchDB, that is REST, recommends the SSH solution.