3

I am developing an application that can be simplified as follows:

The application is basically a mailing list.

Users browse to http://mysite/subscribe. It's on the intranet, and the site is using Windows Auth, so I can retrieve their email adress from Active Directory. This email adress is stored in an SQLLite DB, on the webserver.

Every hour, on the same server, a scheduled task runs a .exe console app. It reads the email adresses from the DB, and sends a report by mail.

My question is:

Where should I save the DB file, in a way that is easily configured in both apps?

In the wwwroot/mysite folder? In the console App folder? In some other folder dedicated for this I don't know about?

Maxime
  • 300
  • 2
  • 6
  • 1
    Accessing a database from *several* processes is a good use case for server RDBMS like [PostGreSQL](http://postgresql.org/). [Sqlite](http://sqlite.org/) tend to favor (or prefer) single process applications. – Basile Starynkevitch Jan 09 '18 at 14:11
  • 2
    @BasileStarynkevitch: for **several** processes this is true. But using PostGreSQL just because there is one second process which accesses the db once an hour sounds a lot like premature optimization. – Doc Brown Jan 09 '18 at 17:20

2 Answers2

2

You mentioned the external application and the website exist on the same server, so in some respects, it really doesn't matter where it is stored. The only thing that matters is what permissions the applications have to the file.

In most cases, the website is going to be the application with the most constraints for permissions. It depends on how/if your app server sandboxes your access to the file system. You'll want to make sure your web application has read/write access, and you want to limit your external app to have read-only access. This minimizes the risk of database corruption due to multiple processes accessing the file. Make sure your web application is opening the database in a way that does not exclusively lock the file.

So to summarize:

  • File location only matters if your app is sandboxed in any way
  • Make the location configurable in your console app
  • Web app needs to open the database with nolock=1 (i.e. can be opened by multiple processes)
  • Console app needs to open the database in read-only mode

Any steps you can take to minimize the time that your console app has a handle to the database file will help. You can open a memory database and import the data from the file into it, or you can make a copy of the file on disk and open a reference to that.

Berin Loritsch
  • 45,784
  • 7
  • 87
  • 160
  • This is IMHO a good answer, but I can imagine the console app needs to write some information back into the db which emails it has send sucessfully (or which mailings have failed). And I am pretty sure this is possible with sqlite. – Doc Brown Jan 09 '18 at 20:12
  • .. of course, not with concurrent writes, but that may not be a problem for a small mailing list with <100 mails per day. – Doc Brown Jan 09 '18 at 20:18
  • I've used SQLite with multiple read processes and only one write process quite some years ago. At that time, even having one extra process writing to the file was a recipe for trouble. If you really do need multiple processes writing to the file, you will need to do some serious lock management--or implement a web API that the console app uses. Unless of course something has fundamentally changed since then. – Berin Loritsch Jan 09 '18 at 20:35
  • Thanks for that information. I did not try this out so far, but having a short look into the Sqlite manual part about [file locking and concurrency](https://www.sqlite.org/lockingv3.html), it seems Sqlite V3 provides the necessary tools for this. Maybe you were using an older Sqlite version in the past? – Doc Brown Jan 09 '18 at 21:07
  • @DocBrown, definitely. It's been at least 7 years since then. I would still try testing it out before allowing writes from multiple databases as a proof of concept--but I'm not working on anything at the moment that requires Sqlite so I can only speak from past experience. – Berin Loritsch Jan 11 '18 at 13:21
  • @DocBrown, a little more info: at that time the locking was good for multiple threads, but not multiple processes. That's the part that concerns me for multiple writes. – Berin Loritsch Jan 11 '18 at 13:24
-3

Users browse to mysite/subscribe. It's on the intranet ... This email adress is stored in an SQLLite DB.

Your Users do not require any access to the database.
Indeed, putting a copy of this Personal Data on the user's local machine might well get you into hot water with your local Data Protection legislature.

Your application is "on the Intranet" - that's a web application running on a web server. That will require access to the database.

Your hourly "report" program may or may not require access to the database itself; you might prefer to make the data available through a Web [REST] API that the program reads from instead. YMMV.

Phill W.
  • 11,891
  • 4
  • 21
  • 36
  • 2
    I have edited the question for clarity. Obviously, everything is on the server. The adresses are written by the webserver when a client subscribes, and read by the report program (although, as you pointed out, it may be avoided). There is only one place this is stored, and that's on the webserver – Maxime Jan 09 '18 at 11:59