Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

online database for desktop app

by Anonymous Monk
on Nov 04, 2018 at 10:26 UTC ( #1225191=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello

I am in need of some good advice. I have a small desktop program written in Tk. It normally uses a local SQLite database to save/read data. Now I want to give it the possibility to query some data I want to store online on a MySQL database. This would give me more freedom to control/update/etc the data the users can query. I have to admit this is my first attempt to let a desktop application access an online database, I beg for mercy.

The first possibility is to give the application direct access to the database. I have already implemented it and it just works. When a user starts a query, results are presented to the Desktop app. However, I have some obvious concerns about this approach as it exposes the database to the external world through my desktop apps. Even if the database does not contain any sensitive data, I guess this is not good practice. Am I right?

Sorry if my questions are naive.

I read online that I could/should build a web service layer between the desktop app and the database. What is the most basic Perl solution/modules to achieve this? Could you point me to some information keeping in mind I really need a simple solution to implement? It is a small project. Any online database service that you know work well with the Perl proposed solution?

Replies are listed 'Best First'.
Re: online database for desktop app
by LanX (Archbishop) on Nov 04, 2018 at 12:34 UTC
    I'd say a web service in between gives you far better control³. If you expose the DB credentials in your app you'd at least run into the risks of DOS attacks.

    Since most queries just return a nested data structure, using a JSON transport layer in between should be fine.²

    I'd start googling for REST and SQL.°

    I don't know the nature of your centralized data, but propably you'll even only need some static JSON files which are updated regularly from SQL?

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

    update

    °) especially ODATA

    ²) kind of

    • -> post request named query
    • -> + bind variables (placeholders)
    • server: executes prepared statement
    • runs ->fetchall_hashref or ->fetchall_arrayref
    • to_json($ref)
    • <- JSON
    • client: $ref=from_json(JSON)

    ³) and HTTP(S) means far less problems with firewalls.

      Thanks. Interesting thoughts. My data structure is quite simple. Two columns: 1. column to match a query, 2. columns contains results. Just reading. Any data maintenance is done in SQL. Your description of the process seems what I am looking for. Just searching if there are modules I can use in order to not reinvent the wheel (and probably build errors in it).

        Plz have a look at the sketched communication flow in my second footnote (updated).

        A CGI using JSON and DBI to run just 2 or 3 prepared statements returning AoAs should have less overhead than configuring a full REST/ODATA layer.

        Without knowing your app I can't judge how complicated it's supposed to become later, hence justifying the long term investment into abstraction.

        Cheers Rolf
        (addicted to the Perl Programming Language :)
        Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

        update

        NB: if you put these SQL-statements into a separate module you can use and test them directly. The additional CGI/JSON layer would be transparent.

      Implemented 1 to 1. Nice! Thank you!

        Great! YW! :)

        Cheers Rolf
        (addicted to the Perl Programming Language :)
        Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

Re: online database for desktop app
by 1nickt (Abbot) on Nov 05, 2018 at 01:01 UTC

    I guess this is not good practice.

    Hi, you are right about that!

    The reason web services in general are so popular is that HTTP is just a very efficient, time-tested way of handling stateless server-client communication, like SQL queries.

    Adding the capability to your app to make HTTP requests is trivial -- I would use HTTP::Tiny which is in core Perl and has a very user-friendly interface.

    To serve responses I would install a modern Perl web application. There are lots of super-lightweight ones including HTTP::Server::Simple, but I would recommend going with something a little more feature-rich so it can handle your database needs natively, along with logging, authentication, tx encryption (https), etc etc. My choice would be Dancer2, which I like in part because it is very pluggable, meaning you can run it very lightweight with very lightweight code. Not least of the useful features is Dancer2::Plugin::Database. Once you've configured your Dancer app with your database settings, you have access to the DB in your code via the database keyword, and can make simple queries with sugar methods like quick_select.

    Here's an oversimplified and contrived use case. Suppose you are providing the daily closing stock prices for some market and you have the important data in a table called price. The user has to provide the stock symbol and the date for which the price is requested. Your app will make an HTTP request to your server using a URL like https://your-backend.com/GOOG/date/2018-11-05, and get a neat response of {"price":"1057.79"}.

    The entire server code for that is:

    use Dancer2; use Dancer2::Plugin::Database; set serializer => 'JSON'; get '/:stock/date/:date' => sub { database->quick_select('price', { stock => params->{stock}, date => params->{date}, }), }; dance;
    ... with something like this in the Dancer2 config file:
    plugins: Database: driver: 'mysql' database: 'test' host: 'localhost' port: 3306 username: 'your_app_id' password: 's3kr1t' on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" + ] dbi_params: RaiseError: 1 AutoCommit: 1
    It's pretty simple, and lots of help is available if you need it. It's always good practice to lay the foundation so that if you meet with success your path forward is smooth sailing.

    Hope this helps!


    The way forward always starts with a minimal test.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1225191]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (5)
As of 2018-12-16 12:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How many stories does it take before you've heard them all?







    Results (70 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!