http://www.perlmonks.org?node_id=1068059

neilwatson has asked for the wisdom of the Perl Monks concerning the following question:

Brother and sisters,

I've been absent from Perl for a while. I'm about to start writing a small CLI program to query a simple (so far) database and generate reports. Years ago I would go to DBI. Is DBI still a good tool? Are there better ways?

And what about formatting output (CLI)? Is there something better than format.

Neil Watson
watson-wilson.ca

Replies are listed 'Best First'.
Re: Returning to Perl, seeking advice on modern DB and format tools
by mbethke (Hermit) on Dec 22, 2013 at 01:36 UTC
    Yes, pretty much everything that people actually use builds on DBI. However, there are a bunch of newer modules that can make your life easier with layers on top of DBI, such as DBIx::Simple (convenience methods for queries, simpler and saner return values, statement caching and such) or DBIx::Class (complete object-relational mapper using abstracted SQL and a whole ecosystem of extensions). As for databases, it all depends on what you want to do. SQLite has become quite popular for smallish things that don't need much in terms of remote access and concurrency. Its databases live in one easily distributed file and basically create themselves. Otherwise, yes, Postgres.
Re: Returning to Perl, seeking advice on modern DB and format tools
by erix (Prior) on Dec 21, 2013 at 22:30 UTC

    Yes, DBI is still very much the way to get data to and from databases.

    As to database system: PostgreSQL recommended. Perl/DBI and postgres are a golden combination.

Re: Returning to Perl, seeking advice on modern DB and format tools
by moritz (Cardinal) on Dec 22, 2013 at 07:47 UTC
Re: Returning to Perl, seeking advice on modern DB and format tools
by karlgoethebier (Abbot) on Dec 22, 2013 at 09:26 UTC

    I'm using DBI with MySQl, Oracle, Sybase and MSSQL since many years.

    Nothing to complain about.

    IMHO it's worth to take a look at DBIx::Simple as well as at SQL::Abstract.

    Combining DBI with PostgreSQL seems to be great - and you can write your Stored Procedures in Perl!

    But i just started learning about this.

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

Re: Returning to Perl, seeking advice on modern DB and format tools
by tangent (Parson) on Dec 22, 2013 at 15:30 UTC
    For a different approach you may want to have a look at BerkeleyDB. It enables you to tie data structures to a file - there are no database servers to connect to, no SQL to write. There are options for hash, array, queue, heap and Btree, or you can choose your own serialization format for both keys and values, allowing you to store arbitrary data structures. You can do useful things like keep hash keys in sorted order, or have duplicate hash keys sorted by value.

    For output, have a look at Text::Autoformat and Text::Template

Re: Returning to Perl, seeking advice on modern DB and format tools
by zentara (Archbishop) on Dec 21, 2013 at 22:06 UTC
    Hi, I don't use dbs much, but I thought that you might find it interesting that Slackware is ditching MySQL in favor of MariaDB.

    From the Slackware site:

    From today's Slackware -current ChangeLog: The big news here is the removal of MySQL in favor of MariaDB. This shouldn't really be a surprise on any level. The poll on LQ showed a large majority of our users were in favor of the change. It's my belief that the MariaDB Foundation will do a better job with the code, be more responsive to security concerns, and be more willing to work with the open source community. And while I don't think there is currently any issue with MySQL's licensing of the community edition for commercial uses, several threads on LQ showed that there is confusion about this, whereas with MariaDB the freedom to use the software is quite clear. Thanks are due to Heinz Wiesinger for his work on transitioning the build script, testing, and getting us all behind this move. He's been working with MariaDB (and their developers) for several years now. Vincent Batts also had a hand in the early discussions here -- he met Daniel Bartholomew of MariaDB on a train last year and got a copy of the source to play with to pass the time on the journey (ah, the miracle of thumbdrives :), and was impressed with not only MariaDB itself, but also with the welcome that Slackware was getting. We expect they'll be responsive to any concerns we have. In the vast majority of situations, MariaDB is entirely compatible with existing MySQL databases and will drop right in with no changes required. There's an article available outlining the areas in which MariaDB differs from MySQL that I'd recommend reading: https://kb.askmonty.org/v/mariadb-versus-mysql-compatibility/

    You can see how to use it with perl, at connecting to Maria DB with Perl


    I'm not really a human, but I play one on earth.
    Old Perl Programmer Haiku ................... flash japh

      Although your reply is not really a recommendation (fortunately), I cannot resist to place this comparison here. FWIW.

      Look at the "Specifications" (=features) for each of the systems.

      That lack of features is probably accurate. MariaDB is a young (2007 ?), under-duresse derivative of an already problematically weak system (MySQL), and consequently derives its following mainly from the ranks of the mysql-encumbered (such as, apparently, SlackWare).

      Greenfield projects can/should do better.

      update: fixed link.

        The Greenfield projects link doesn't go anywhere useful except a wikipedia search page.

        Although your reply is not really a recommendation (fortunately), I cannot resist to place this comparison here. FWIW.
        Sorry, but that's a crock of shit. MariaDB can only not run unprivileged? PostgreSQL is not open source? MariaDB supports ACID but no referential integrity? If one system supports one datatype that is equivalent to a dozen types of the other's, that counts as negative? I suppose this is auto-generated by a necessarily shoddy parser from marketing blurb and nobody with an ounce of a clue has even glanced at it.

      FWIW, this isn't a Slackware-specific move, but is part of a general trend away from inclusion of Oracle-supported MySQL in many Linux distros. Arch Linux has already done this, and last I heard the move was afoot in Debian and Ubuntu as well.

      To answer the OP, yes DBI is a great interface for either Postgres or MySQL or MariaDB.

Re: Returning to Perl, seeking advice on modern DB and format tools
by ysth (Canon) on Dec 22, 2013 at 11:28 UTC
    If this small program is the only interface to the database, I would consider SQLite rather than Postgres or similar.
    --
    A math joke: r = | |csc(θ)|+|sec(θ)| |-| |csc(θ)|-|sec(θ)| |
Re: Returning to Perl, seeking advice on modern DB and format tools
by stefbv (Curate) on Dec 22, 2013 at 11:46 UTC
Re: Returning to Perl, seeking advice on modern DB and format tools
by karlgoethebier (Abbot) on Dec 23, 2013 at 09:25 UTC

    BTW, some thoughts about mySQL.

    There are some really annoying details:

    If you use InnoDB as engine (you should), mySQL generates a ibdata file.

    If you delete records/tables, this file does not shrink.

    It's getting bigger and bigger and the only way to manage this is to dump the database, drop and recreate it and then loading the dump again.

    Ok, one can have a ibdata file for each table. This makes live a little bit easier.

    The dump is plain SQL - a text file with tons of INSERT statements.

    Loading this dump can last very, very long.

    Same thing with backup/recovery.

    And you must explicitly backup your grants and stored procedures.

    And as far as i know, the only way to force reindexing is the procedure described above.

    Many caveats.

    And it is a mess to figure out if this all did really work as expected.

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

Re: Returning to Perl, seeking advice on modern DB and format tools
by Generoso (Prior) on Dec 23, 2013 at 06:58 UTC
Re: Returning to Perl, seeking advice on modern DB and format tools
by vincent_veyron (Sexton) on Dec 23, 2013 at 17:14 UTC

    "And what about formatting output (CLI)? Is there something better than format."

    I suppose you write to a file? you could have your script write the data in html, so that it can be read in a navigator



    libremen.com : legal case, contract and insurance claim management software
Re: Returning to Perl, seeking advice on modern DB and format tools
by sam_bakki (Pilgrim) on Dec 25, 2013 at 15:24 UTC

    Hi Neil Watson

    I have just designed & implemented a secure web service framework based on perl + mod_perl + https + SOAP::Lite + DBI + SQLite.

    DBI is great. Production ready. If your DB needs is minimal go for SQLite. Lite weight, server less, super fast DB.

    Just in case, if you wanna move to GUI from CLI, Have a look at GTK2, With Glade user interface designer tool you can easily create GUIs.

    Thanks & Regards,
    Bakkiaraj M
    My Perl Gtk2 technology demo project - http://code.google.com/p/saaral-soft-search-spider/ , contributions are welcome.

      Glade doesn't support Perl. There are some flakey Perl libraries you have to use to produce Perl code, and you have to read the API and write your own code since they dont come as a runable script. Its easier to write the gtk perl code by hand from the XML files Glade produces than figure out how to convert them.

        Hi

        Glade do support perl. See GTK2::Builder module. Its so easy to create and align GUI widgets in Glade (it produce XML file) and write call back in Perl. But, its all personal preference, Should I write Gui code or not.

        For code example, Have a look at this: http://code.google.com/p/saaral-soft-search-spider/source/browse/trunk/src/Libs/GtkSpiderGui.pm

        Thanks & Regards,
        Bakkiaraj M
        My Perl Gtk2 technology demo project - http://code.google.com/p/saaral-soft-search-spider/ , contributions are welcome.