Multiuser app with SQLite, Dancer2 and CLI

[kaldor]

Hello, this is my first question...

I'd like to write a small multi-user text application accessible by different interfaces (web, terminal, ssh and maybe others). The typical scenario is a few hundreds total users, with a dozen users connected at the same time.

I have the following prototype working : SQLite + DBI + Dancer2 web app running as FastCGI (on mac/linux). Now I wonder how to add CLI interfaces without duplicating code and logic... As fas as I understand, the SQLite "file" can be accessed safely by different processes.

Should I just write a basic CLI that accesses the DB directly and use xinetd? Or do I have to write a backend that accesses the DB and executes the logic for UI-only clients?

Suggestions and advice are welcome. Thank you.

[Discipulus]

Hello kaldor and welcome to the monastery and to the wonderful world of perl!

for the little i know SQLite is not well suited to multiple contemporary access. or maybe you will just have read access? See DBD::SqLite issue while accessing DB from multiple processes where sqlite_use_immediate_transaction is also suggested.

Regarding the duplication of code: abstract all parts needed by both interfaces and put them into a testable module.

L*

[Anonymous Monk]

I expect to have much more read accesses than write.

I use AutoCommit => 0, so immediate transactions are used by default. But you made me go through SQLite's documentation (again) : since the DB cannot be read at all while locked for writing, I'll have to deal with SQLITE_BUSY.

In the end, usability will depend mostly on the DB/schema design (fast or slow updates). I'll stick to SQLite for now since I don't have any real-case metrics.

Thanks.

[kaldor]

That was me posting here, by mistake as anonymous.

Again, thank you all for your answers.

[1nickt]

Hi, I usually separate out the Dancer route handling code and the actual do-the-work code into separate modules precisely so I can access the latter outside the framework.

Sometimes there are three layers because the Dancer route-handling code that makes use of the true back end libraries (that you want to consume separately) gets so voluminous that I want to keep it out of the actual route handling classes. In that case I wind up with a somewhat parallel structure for the first two layers, like maybe:

lib/
+++ Routes.pm
+++ Route/
+++++++++ Account.pm # handles routing for /account
+++++++++ Account/
+++++++++++++++++ User.pm
+++ API/
+++++++ Account.pm # does stuff with an account
+++++++ Account/
+++++++++++++++ User.pm
+++ Config.pm
+++ DB.pm # provides access to the database
+++ LowLevelBizStuff.pm

etc., where a cron script or whatever can access the database outside the Dancer framework.

On the other hand, if your database needs are simple, you can use Dancer2::Plugin::Database to greatly simplify your web app code, but in that case you'd have to write other code to get at your data off line. All depends how complex your data is, how complex your app is, and how complex the actions your want to take on the data off line are.

Hope this helps!

[afoken]

As fas as I understand, the SQLite "file" can be accessed safely by different processes.

Maybe it can be accessed by more than one process, but that does not mean it should be. Consider using a "real" database server if you need multi-process access to the database.

I would recommend using PostgreSQL. Here is why: Re^2: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?, Re^2: Perl and Database, Re^2: What is your favourite Linux or cross-platform database?. PostgreSQL runs on Unix (including MacOS X, Linux) and Windows, and is quite easy to set up and run, and it comes with excellent documentation. You may also want to install PgAdmin.

Alexander

[thechartist]

Written for those who aren't sure when SQLite is a good solution for a website DB

SQLite should be OK for a website that doesn't require many writes to the DB.

From the SQLite project website: https://www.sqlite.org/whentouse.html

SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite. (snip next paragraph)

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

[Anonymous Monk]

Weird idea: interface your CLI to Dancer2 using LWP::UserAgent or HTTP::Tiny. Depending on your application, you could have the CLI request the data in text rather than HTTP (requiring a template for each format, I think) or scrape the HTTP (with the usual caveat about the fragility of such things).

[bliako]

Or do completely the opposite: have your Dancer2 app accept only CLI commands as parameter which then delegates to your CLI. Which means: write first your CLI parser and functions, then worry about how to communicate with it. If you write it as a module, you could do something like my $cli = new CLI($params); my $res = $cli->process($commands, $data); either from a Dancer2 app or from a socket listener or from the command line.

[markong]

++ for this approach: I'd add that you could (with a varying level of effort depending on the actual code) expose a REST API (XML/JSON) and having whatever client connecting to the "core" app through HTTP!

In any case I'd recommend to swap SQLite for multi-user RDBMS, or you're destined to hit a bottleneck.