Advice sought: How to implement the following scenario?

  1. ‹ Older
  2. 6 days ago

    Thom M

    May 13 Pre-Release Testers Greater Hartford Area, CT

    @Kimball L I use CubeSQL as a central SQLite server. My desktop apps connect to and manipulate data inside the cubesql server. It's not free, but it's very good.

    However, I think the right solution for the requirements you've posted is a simple webapp. Clients interact via their browser, so there is nothing to install. There are no databases to sync because everything lives in the webapp instance. Your scenario is really ideal for a small webapp.

    I have to agree. Otherwise, you really need some sort of central server process like CubeSQL or PostgreSQL. I have an app here that just cannot handle concurrent usage of a sqlite database between processes due to Xojo bugs. You'll be fighting a losing battle with it until you get some kind of server process that is designed for concurrent database access.

    So either web app or database server. Either way, you're not get away from the need for a server.

  3. Karen A

    May 13 Pre-Release Testers
    Edited 6 days ago

    @Thom M I have an app here that just cannot handle concurrent usage of a sqlite database between processes due to Xojo bugs.

    What do you mean by "processes" - helper apps or Xojo threads?

    I thought for a small number of a xojo app using using SQLite in WAL mode would be fine for 10-20 users... That is what what was planning with an Xojo Aloe Server app using threads accessing the SQLite DB...

    So what kind of issues did you encounter?

    - karen

  4. Kimball L

    May 13 Pre-Release Testers, Xojo Pro Meridian, ID, USA

    Xojo processes != xojo threads. Multiple threads accessing the same db *from the same process* should be fine. Multiple processes (especially if you have the database file stored on a network share so that multiple different computers are involved) is fraut with danger - corrupted sqlite database is the most likely outcome.

    Bottom line: If you have multiple computers involved, either build a web app or host a local database server.

  5. 5 days ago

    Karen A

    May 14 Pre-Release Testers

    @Kimball L Xojo processes != xojo threads. Multiple threads accessing the same db *from the same process* should be fine.

    Thom's bug report seems to imply otherwise if you want the DB to yield during potentially long operations in threads:
    Feedback Case #55556
    - karen

  6. Philip C

    May 14 Pre-Release Testers, Xojo Pro Cooroy, QLD, Australia

    You could take a look st the two videos done by Paul which demonstrate how to set up a web app accessing the SQLITE database and another app asking the Web app for information. By using the web app to do the work, it is possible to have many users accessing data from SQLite.

  7. Daniel T

    May 14 Pre-Release Testers, Xojo Pro

    @Markus W Central SQLite database (nobody can administrate PostGres or MySQL)

    Is there anything to administrate once you've set it up? I've got clients where I setup Postgres and a nightly backup service app, and the Postgres server has been running for years without so much as a phone call or email to me regarding that specific component.

    New 1U server spec'd for growth, RAID drives, make sure everything is secure, regular scheduled DB backups, have some type of alert system in case your backups (to another server) fail for any reason...PostgreSQL will just run.

  8. Thom M

    May 14 Pre-Release Testers Greater Hartford Area, CT

    @Karen A What do you mean by "processes" - helper apps or Xojo threads?

    I thought for a small number of a xojo app using using SQLite in WAL mode would be fine for 10-20 users... That is what what was planning with an Xojo Aloe Server app using threads accessing the SQLite DB...

    So what kind of issues did you encounter?

    - karen

    Both Xojo threads and helper apps actually. I use CriticalSection to control transactions with a single database connection so Xojo threads don’t get their transactions confused. This works great most of the time.

    But if I give each thread it’s own connection to the database with wal active, then database locks produce errors. This is to be expected though and once again, CriticalSection would be the answer to ensure that one thread doesn’t try to write while another thread wants to. In this case, one connection per thread or a single connection doesn’t really make a difference.

    But here’s where it gets interesting. Using a helper app, a Mutex is needed to control write permission between the helper(s) and the master. But in some Xojo versions, locking Mutex on the main thread makes the app lock permanently.

    So it sounds like using a single process with CriticalSection sounds good, right? Well not exactly. SQLite doesn’t yield to threads by default. Using ThreadYieldInterval with a value that is too low to complete the desired work also causes the app to lock permanently. Since the interval must be greater than your longest action - and who really knows what that would be - it also defeats the purpose of the property entirely since it won’t yield any faster than not yielding anyway.

    In my experience, trying to “cheat” concurrency with xojo and sqlite will have problems in one way or another. Just use an external engine and make your life simpler. The effort required to get that external engine setup and running will be less than you’ll spend fighting with these behaviors.

  9. Karen A

    May 15 Pre-Release Testers

    @Thom M But if I give each thread it’s own connection to the database with wal active, then database locks produce errors. This is to be expected though and once again, CriticalSection would be the answer to ensure that one thread doesn’t try to write while another thread wants to. In this case, one connection per thread or a single connection doesn’t really make a difference.

    But here’s where it gets interesting. Using a helper app, a Mutex is needed to control write permission between the helper(s) and the master. But in some Xojo versions, locking Mutex on the main thread makes the app lock permanently.

    Can't you avoid all of that by just checking the Sqlite error code on the connection for a write lock after an attempted write, and if it was locked, after a short interval retry the write, be it from be it from threads or helper apps ?

    - Karen

  10. Thom M

    May 15 Pre-Release Testers Greater Hartford Area, CT

    Things get really complicated when dealing with prepared statements and trying to reexecute with the same values, without allowing the code to continue.

  11. Karen A

    May 15 Pre-Release Testers

    BTW for an in-house app (LAN only) is there a reason not to have client connect directly to say a postgreSQL DB on a machine that has a LAN only IP address?

    Isn't using middleware for DB client access is mainly a security thing? If so, then if the DB can't be accessed from the outside there would be any issues with that?

    - Karen

  12. Thom M

    May 15 Pre-Release Testers Greater Hartford Area, CT

    @Karen A BTW for an in-house app (LAN only) is there a reason not to have client connect directly to say a postgreSQL DB on a machine that has a LAN only IP address?

    Isn't using middleware for DB client access is mainly a security thing? If so, then if the DB can't be accessed from the outside there would be any issues with that?

    - Karen

    For a lan-only connection, that should be safe.

    Middleware provides both security and abstraction. The abstraction layer can change logic in a lot of ways to maintain support for legacy versions and fix bugs without releasing a whole update. The cost is development time, performance, and an added source of potential bugs.

    Somewhere in between would be using a direct connection and storing lots of logic in the pg database.

  13. 4 days ago

    James D

    May 15 Pre-Release Testers Europe (Switzerland)

    @Karen A BTW for an in-house app (LAN only) is there a reason not to have client connect directly to say a postgreSQL DB on a machine that has a LAN only IP address?

    Isn't using middleware for DB client access is mainly a security thing? If so, then if the DB can't be accessed from the outside there would be any issues with that?

    - Karen

    No it is not just a security thing, although it is certainly a big part of it.

    - It’s an administration thing. You will have to manage all users at the database level

    - It’s a data integrity thing. Once users have access rights on a database level they can connect with any tool they like and make changes. So you are going to need a very robust database.

  14. James D

    May 15 Pre-Release Testers Europe (Switzerland)

    @Markus W nobody can administrate PostGres or MySQL

    I think you’ll find a lot of people would disagree on that one...

    You could use a container and ship a preconfigured instance.

  15. Karen A

    May 15 Pre-Release Testers

    @James D - It’s a data integrity thing. Once users have access rights on a database level they can connect with any tool they like and make changes.

    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.

    - Karen

  16. 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 C 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.

  17. Karen A

    May 15 Pre-Release Testers

    @Kristin G 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 C notes above, Paul posted some good videos on this.

    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.

    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...

    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.

    You could even look at creating the server as a console app that runs in the background.

    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.

    - Karen

  18. 2 days ago

    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.

  19. scott b

    May 17 Pre-Release Testers, Xojo Pro local coffee shop

    @Kristin G 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.

    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.

    @Kristin G 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.

    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.

    --sb

  20. Karen A

    May 17 Pre-Release Testers

    @Kristin G 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.

    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.

    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.

    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)

    - karen

  21. Daniel T

    May 17 Pre-Release Testers, Xojo Pro

    @Kristin G 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.

    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.

or Sign Up to reply!