Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Application development

Options
  • 18-05-2019 1:54pm
    #1
    Danger Fourpence
    Registered Users Posts: 190 ✭✭


    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.


Comments

  • Options
    #2
    Jim2007
    Moderators, Business & Finance Moderators Posts: 9,988 Mod ✭✭✭✭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.


  • Options
    #3
    counterpointaud
    Registered Users Posts: 403 ✭✭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?


  • Options
    #4
    SilverSideUp
    Registered Users Posts: 63 ✭✭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.


  • Options
    #5
    counterpointaud
    Registered Users Posts: 403 ✭✭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


  • Options
    #6
    SilverSideUp
    Registered Users Posts: 63 ✭✭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.


  • Advertisement
  • Options
    #7
    sf80
    Registered Users Posts: 252 ✭✭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


  • Options
    #8
    counterpointaud
    Registered Users Posts: 403 ✭✭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.


  • Options
    #9
    dazberry
    Registered Users Posts: 2,145 ✭✭✭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.


  • Options
    #10
    AndrewJRenko
    Registered Users Posts: 28,372 ✭✭✭✭AndrewJRenko


    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?


  • Options
    #11
    ressem
    Registered Users Posts: 2,426 ✭✭✭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.


  • Advertisement
  • Options
    #12
    Danger Fourpence
    Registered Users Posts: 190 ✭✭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.


  • Options
    #13
    zbluebirdz
    Registered Users Posts: 270 ✭✭zbluebirdz


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


  • Options
    #14
    beauf
    Closed Accounts Posts: 22,651 ✭✭✭✭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.


  • Options
    #15
    beauf
    Closed Accounts Posts: 22,651 ✭✭✭✭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.


  • Options
    #16
    beauf
    Closed Accounts Posts: 22,651 ✭✭✭✭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


  • Options
    #17
    SilverSideUp
    Registered Users Posts: 63 ✭✭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.


  • Options
    #18
    sf80
    Registered Users Posts: 252 ✭✭sf80


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


  • Options
    #19
    AndrewJRenko
    Registered Users Posts: 28,372 ✭✭✭✭AndrewJRenko


    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.


  • Options
    #20
    DublinWriter
    Registered Users Posts: 9,555 ✭✭✭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.


Advertisement