The client Xojo app would have access … but that does not mean end users have to have direct access…
If the app is doing the login I can use an encoding of the username for the login iD which they won’t know… Could do the same type of thing with the passwords as well… So while the they could get on through the app, even if they knew the address of the server they would not be able to access it independent of the app.
The client app could directly control what they could do… which would be only what the app would be designed to do regardless of architecture.
I’ve been doing a lot of thinking recently along the same lines as this question. Client/Server… multi-user environments… the need for a synchronized local copy (maybe?)… managing lists of data… there are a lot of answers to this question but within your given constraints, here is what I would do…
I would create a Web Service that manages the SQLite database and provides a REST interface for the clients… and then a simple client app that uses the server’s API to manage the data. As @Philip Cumpston notes above, Paul posted some good videos on this.
The perceived need for a local copy of data might be due to the fear that a direct SQL connection might prove unstable due to poor wifi network connections and causes crashes… but the use of a REST interface helps and if you have an unreliable network then maybe that should be the focus. Removing the need to synchronize local/remote data will improve your chances of success greatly. Making the client a simple REST client (with no SQLite database of it’s own) is probably the best way to go. My head hurts just thinking about how you would maintain sequential primary keys.
Still, let’s say your users are mountain rangers who regularly venture into places data networks just can’t reach… I would create the client app as a stand-alone app with it’s own SQLite database that does everything your users need… just without sharing of data. Then, I would write a thread in the client app that synchronizes the local database with the server in the background… kind of like IMAP email clients… but still using REST and JSON. You could even look at creating the server as a console app that runs in the background.
While not the original poster, I have ben considering doing exactly teh same things him using Sqlite… until Thom’s posts about issues using SQLite that way and other agreeing with him.
For me, although this is for an in-house app, the issue is that all the clients would be connecting via Wifi and that can be problematic in our building in some places … but they need to always be able to run it…
In this case the downloaded DB would be read only on the client (data required to run does not change often) … while not ideal, if there is no connection at the time, the data can be uploaded later… and it only gets put in the a db after uploading. so no issues with primary keys.
[quote]
You could even look at creating the server as a console app that runs in the background.[/quote]
If I go that route instead of connecting directly to the DB, I would be using an Aloe Express (open source Xojo) server… I may still do that with SQLite as although I could have 10-20 users , with how this app will be used it. I think it would be rare for more than 3 or 4 to hit the server at the same time…
Using “Begin Immediate” for transactions (which guaranties no other writes until the transaction is done and will return busy on that statement) and checking for the Busy error code when starting a transaction may be workable.
I should be honest and say that I haven’t personally done what I’ve suggested above. After doing my own research (still ongoing), I had come to this conclusion and I decided to share it.
That said, I don’t have the same level of experience that Thom seems to have and if a Xojo Web Service isn’t ‘battle-ready’ then that’s a bigger issue and part of the response to the OP should be that a xojo-only solution may simply not be possible.
While I had considered exposing the SQL database (MySQL or PostgreSQL) to the private network, that seems to limit the future scope of the project in such a way that public access would not be allowed. (Imagine the client suddenly wants to publish select data on the company website!) By using a REST API, instead of direct SQL, you put yourself in a better position for exposing the DB to the public internet. Arguments for data abstraction also exist. Not to mention the potential for multi-tenancy.
I had even considered using Yii2 to provide a basic administrative web interface and a built-in RESTful server as a front end to an SQL server. If you can figure out Xojo, you can figure out Yii2. Lots of great tutorials out there.
At this point, I’m inclined to believe that the best solution for a multi-user business application is a REST/JSON server (Xojo or otherwise) and a Xojo client app whenever a rich, platform-specific UI is desired and direct hardware access may be required.
Oh, and if your wifi network is spotty, caching a local copy of the database is not the solution (IMHO)… a better wifi network is.
using a REST API as the “middle man” between the app and the database is a very good solution. plus it allows you to make changes to either the app or the database without having to change EVERYTHING at once.
this is soooo true. if your network is spotty (think it is unreliable) then you need to upgrade the network. doesnt matter if the network is physical cables or wifi (or something else).
my only suggestion is that you dont make your solution relying on the internet itself. the internet breaks at random intervals and you dont want to be down because “someone in a backhoe some where” took down the link between you and the online resource you are depending on. majority of my clients have a requirement that nothing in their solution is dependent of the internet being functional. just some friendly advice.
I had been planing something similar to Markus (the OP)
That you posted made sense to me and what I planned to do using an AloeExpress (Xojo) server, until Thom’s warnings… Now I’m not sure now which is best.
[quote]
Oh, and if your wifi network is spotty, caching a local copy of the database is not the solution (IMHO)… a better wifi network is.[/quote]
If only that were an option in my situation!!!
BTW I am starting to question if JSON is the best solution to return data to the client if the both the client and the server are written in Xojo. In that case does not have to work within the confines of what browsers and other toys of servers can do.
What I was thinking of was writing record set data to a memory block and sending the binary data to the client…
That could be done 2 ways…
Write a server side class that takes a recordset and writes the records to a memoryblock with header of filenames and datatypes
On the client side a write class that takes the binary string and shoves it into an internal memoryblock. This could allow iteration through the records just like a RecordSet using either and index or fieldnames to access the field data again like a recordset. Under the hood it could be accessing the data under the hood directly from the memoryblock
The other approach would be the same basic idea but writing code to write classes that does that for specific queries (Where field names and datatypes are known upfront) for both the client and server. One would feed the Code writing app a recordset and it would spit out the classes to be imported into client and server apps.
That has the advantage of not needing to send filename and data types as the receiving class would know them. The Client class would have computed properties (or getter methods) so one could use autocomplete in the client code for the fields.
Automating the class production (once written and debugged) could be a real client coding productivity booster I think!
In fact I may do both because I can see situations where one or the other would be best…
It seems to me this type of approach (Recordset data in a binary format Xojo can directly write and read) would be faster and less memory and CPU intensive on the server, as well as resulting is smaller (so less network traffic) payload to send to the client, than converting to (and from) JSON (or XML).
Am I wrong? Is there a strong reason not to do things that way if one uses a Xojo server? If one is using a non Xojo code server that it likely would not be that efficient)
You can always add a REST layer for public consumption even if you have native connections for LAN clients. I’ve done that before to save time and money (REST in the middle is another layer to code/test) and to maximize LAN client performance. The public web client had comparatively few functions and I only developed out the REST API to the point needed to support those functions.
I’m not sure the binary data of a recordset would be faster. I guess you’d have to look at what that includes. There may be a lot of overhead that is not needed. JSON, is pretty lean although, the conversion/mapping of RS to JSON before transmission would take up a bit more time.
Still, I like to use standards whenever possible. Xojo is great, and all, but… let’s say that the users create way more data than originally thought and Xojo chokes on it. (I’m not saying that it would, but…) If you used JSON, you could switch your server to something completely different… like Yii2 with a RESTful server as a middleman to a more robust database server like PostgreSQL… all running on a LAMP instance on AWS… and you wouldn’t have to modify your client app much at all.
In fact, imagine having to switch from a local server to a cloud server. Such a change would be easy if standards are used even if the server technology itself was completely different.
The other reason I like to use standards is help the next developer that comes after me. Let’s say they hire an Android programmer to make a fancy new app (because the new CEO loves Android and thinks that it is the future). The new programmer doesn’t need to know anything about Xojo or even have the original source code to your app or your server. All they need is some API documentation and they can use whatever tech they want/need.
I would be curious as to the performance gain or loss by encoding to a memory block. In C I can just about guarantee a significant performance gain over JSON construction/parsing. But in Xojo you could get bit by an unexpected overhead somewhere.
Of course there’s also the question of whether or not the gain would be worth it.
You could still do that, though it might require more code than a JSON solution would with some of those options.
That’s still true for the most part. Though they would probably curse at you under their breath for having to write the code to extract the data rather than just use some built in framework function to map JSON-to-whatever.
As I said in the beginning, I would be very curious about the performance gain. But I also wonder if it would be worth it for most applications. Note that while I offered a counterpoint to your points for sticking with JSON, each one has the caveat “…but it would be more work.” I’m all for a little extra work to improve performance, but the gain has to be worth it.
Theoretically it could be… can’t know the actual until it’s done… and if it is done well enough generally enough, once it is done, for future projects there would be no extra work…
I wonder how DBs send their cursor (recordset) data back to their clients…(that is what middleman app has to do too) For example for Postgres is it by something like JSON or binary?
Wouldn’t it be nice just to to send a Record set back to the client and be received as a recordset and used like on there? Then basically the same code could be used for a local DB or one serviced by Xojo built middleware. (would be nice if Xojo supported that!)
Along with making processing on the server faster and less memory intensive, that is basically one of my objectives (Using a client class that has the same API as a recordset and maybe making recordsets and that class interchangeable using a Class interface)
This is the first time I am diving into Client Server stuff so maybe I am just trying to reinvent the wheel!
[quote=437585:@Karen Atkocius]Theoretically it could be… can’t know the actual until it’s done… and if it is done well enough generally enough, once it is done, for future projects there would be no extra work…
I wonder how DBs send their cursor (recordset) data back to their clients…(that is what middleman app has to do too) For example for Postgres is it by something like JSON or binary?
[/quote]
The protocols that I have looked at used a binary protocol
[quote=437585:@Karen Atkocius]Wouldn’t it be nice just to to send a Record set back to the client and be received as a recordset and used like on there? Then basically the same code could be used for a local DB or one serviced by Xojo built middleware. (would be nice if Xojo supported that!)
[/quote]
see RecordSetInterface & DatabaseFieldInterface