nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Child Item

Application development

Danger Fourpence

Hey all

I have a very small data entry desktop application that I had created just to speed up some daily tasks. It works very well and other people in the company have started to use it. Up to now it's never been required for anything other than working on a stand alone device.

However, we now need to network this somehow so that one user can modify another user's entries. It is built on SQL Lite meaning that numerous users can't commit to the DB at once. I've been told a web application would be the resolution, but unfortunately we won't always have internet access when using the application.

Can anyone suggest a potential solution? I didn't take scale into a factor when I thought only I would be using it.

Find more posts tagged with

Quick Links

Comments

Jim2007

Danger Fourpence wrote: »

Hey all

I have a very small data entry desktop application that I had created just to speed up some daily tasks. It works very well and other people in the company have started to use it. Up to now it's never been required for anything other than working on a stand alone device.

However, we now need to network this somehow so that one user can modify another user's entries. It is built on SQL Lite meaning that numerous users can't commit to the DB at once. I've been told a web application would be the resolution, but unfortunately we won't always have internet access when using the application.

Can anyone suggest a potential solution? I didn't take scale into a factor when I thought only I would be using it.

Have you got a budget? That would be the first step, how much you are willing to spend on it.

counterpointaud

Danger Fourpence wrote: »

one user can modify another user's entries.

Can you expand on what this means?

You may be able to take the existing application and move it onto a server, but it depends on what's required.

How should users be authenticated?

Are you talking about one user modifying another's entries after the fact, or at the same time (e.g. like two people editing the same spreadsheet at the same time on Google Sheets)?

What is current app written in, and what platform does it run on?

SilverSideUp

"SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it."

Herein lies your problem. This is taken from the SQLite docs. The best solution would be to re-write your app using MYSQL instead of SQLite. Otherwise, it will be a bodge.

counterpointaud

SilverSideUp wrote: »

Herein lies your problem. This is taken from the SQLite docs. The best solution would be to re-write your app using MYSQL instead of SQLite. Otherwise, it will be a bodge.

Yeah but it my be enough for OPs needs to just restrict the app to one logged in user at a time

SilverSideUp

counterpointaud wrote: »

Yeah but it my be enough for OPs needs to just restrict the app to one logged in user at a time

Yes, maybe. But personally I would be pissed if evertime I wanted to access the database I had to wait for someone else to finish.

sf80

Danger Fourpence wrote: »

resolution, but unfortunately we won't always have internet access when using the application.
.

Do they need write access when they are off line? Could you given them an offline viewer and a Web application for editing?

A distributed database which handles merging changes to the same data by different people is probably not a direction you want to take

counterpointaud

Danger Fourpence wrote: »

but unfortunately we won't always have internet access when using the application

This is probably the bigger complicating factor IMO

If you only allow one logged in user at a time (or implement locking at the application layer), but require network access, it's not so hard.

If you allow offline editing which updates when you eventually get online it gets pretty complicated (which update wins? latest timestamp? do we trust time from users machine? etc etc) can of worms

As previous poster suggested, it may be worth migrating to a proper database if you think concurrent multiple users is definitely needed. Otherwise I tend to be a fan of the simplest thing that gets the job done.

dazberry

Back in the 90s I used a toolstack from Borland called Delphi (I'm not recommending this now - but bear with me). As it was a RAD development tool, most things were achieved by dropping components on forms and setting the associated properties and events - and unless you tried very hard - which most people didn't - you ended up with a big ball of mud.

Anyway, one of the Delphi components was a ClientDataSet, which was essentially an in memory dataset, which could load and save data to/from different sources, so for instance you could connect to a database or webservice, fetch some data, disconnect the datasource, work on the data locally (persisting to disk locally), and eventually apply the updates back to the original source using the generated change log. This was known as the briefcase model.

Given what you have, I'd be inclined to try and implement this sort of model, let everyone work locally using the local sql lite database, but fetch/commit to a central database/webservice. The problem is resolving any change conflicts when trying to submit your updates. If the nature of the data is partitioned well between users or is not very volatile (mostly inserts) it should work pretty well.

D.

[Deleted User]

Do you really need to build your own app? Could you do something in Google Forms or Sheets, or even SurveyMonkey or Typeform or whatever? Is the data suitable for cloud storage, or is it very sensitive? Do you need to integrate with other existing systems or data to validate your data entry?

ressem

Danger Fourpence wrote: »

However, we now need to network this somehow so that one user can modify another user's entries. It is built on SQL Lite meaning that numerous users can't commit to the DB at once. I've been told a web application would be the resolution, but unfortunately we won't always have internet access when using the application.

There are design patterns and components that should allow a web application to work in this mode. Some databases make this more possible than others, with rollbacks and events identifying where a conflict is occurring.

The current trend is using IndexedDB, built into your browser.
e.g.
https://codelabs.developers.google.com/codelabs/workbox-indexeddb/index.html?index=..%2F..index#0

There are some things it's not so great at, but most are explained up-front, unlike the expensive commercial tools,
https://developers.google.com/web/fundamentals/instant-and-offline/web-storage/indexeddb-best-practices

You'll have to work through the business effects of how things work. No techie component does all the problem solving and thinking in advance...

Can you consider how your application will deal with simultaneous/ conflicting offline transactions?
Where multiple users change one or more database fields, will some group/ manager/admin have to manually decide which gets priority?
How costly can a conflict become?

Using SQLlite locally might be ok for your app, but you might need fields added so that you keep track of the last update time/centralized transactionID and sync state for each table row.

Try to avoid having both an client app and a web service, for your own sanity/workload.

You will need a more capable central database.

Also if an offline client makes multiple transactions on a record, should all the transactions be synced & recorded in the central db, or just the last one?

Can anyone suggest a potential solution? I didn't take scale into a factor when I thought only I would be using it.

Once you've got this working you'll have knowledge and a toolset to improve all future programs. Trying to do all of this from a blank slate on your own, while easier in theory, often results in the project not getting off the ground.

Danger Fourpence

Wow, so many responses. Thanks!

Jim2007 wrote: »

Have you got a budget?

Little to none. Verging closer to none.

counterpointaud wrote: »

Are you talking about one user modifying another's entries after the fact, or at the same time (e.g. like two people editing the same spreadsheet at the same time on Google Sheets)?

What is current app written in, and what platform does it run on?

I got this wrong, apologies. There are no users currently as such, what I've done is share out my database and the application running it on my network but this leads to a lot of lag obviously as it's a shared folder and because of SQLLite.

SilverSideUp wrote: »

"SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it."

Herein lies your problem. This is taken from the SQLite docs. The best solution would be to re-write your app using MYSQL instead of SQLite. Otherwise, it will be a bodge.

Pardon my ignorance, and I have checked online, but how would MySQL help? I would still be sharing over a network filesystem (not the internet).

sf80 wrote: »

Do they need write access when they are off line? Could you given them an offline viewer and a Web application for editing?

Wouldn't I always need internet for a web application.

counterpointaud wrote: »

This is probably the bigger complicating factor IMO

If you only allow one logged in user at a time (or implement locking at the application layer), but require network access, it's not so hard.

If you allow offline editing which updates when you eventually get online it gets pretty complicated (which update wins? latest timestamp? do we trust time from users machine? etc etc) can of worms

As previous poster suggested, it may be worth migrating to a proper database if you think concurrent multiple users is definitely needed. Otherwise I tend to be a fan of the simplest thing that gets the job done.

I think this might be the case unfortunately, yes. I must investigate the cost of rewriting for mysql

AndrewJRenko wrote: »

Do you really need to build your own app? Could you do something in Google Forms or Sheets, or even SurveyMonkey or Typeform or whatever? Is the data suitable for cloud storage, or is it very sensitive? Do you need to integrate with other existing systems or data to validate your data entry?

No. Sometimes I will be on a dark site.

zbluebirdz

Do you have an intranet? If yes, then you could create the website version of your application.

beauf

You can have a web application without the internet on your machine or intranet you just need a web server locally or on the internal Network.

beauf

Pre web technologies you could write the application so it tracked what was open and don't allow anyone to edit the same data at the same time. Bit Stone age but was simple and worked.

Depends if it's likely to grow to 10 users or 100. If the latter then I wouldn't go down that route.

beauf

Sounds like you need to use a better database though. A web front end is more future proof. Good thing to learn for you too

SilverSideUp

"Pardon my ignorance, and I have checked online, but how would MySQL help? I would still be sharing over a network filesystem (not the internet)."

You wouldn't still be sharing over a network filesystem. MySQL uses a client/server protocol. It will work on your local network and can be installed on any desktop, laptop, raspberry pi, whatever. All other local network computers can then access your SQL database.

sf80

What sort of data is it? Could you live with sharing text files in a distributed VCS like GIT?

[Deleted User]

Danger Fourpence wrote: »

No. Sometimes I will be on a dark site.

Many Google apps work offline. It depends what exactly your users are trying to do, but don't rule this out.

https://support.google.com/drive/answer/2375012?co=GENIE.Platform%3DDesktop&hl=en

Danger Fourpence wrote: »

Little to none. Verging closer to none.

Another good reason for avoiding trying to build an app at all.

DublinWriter

To be honest OP, it sounds like you might be creating a massive rod for your own back.

No budget and no project sponsor/champion?

How important is this data and/or process to the business?

People never appreciate what they get for free.