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

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

I am hoping to replace an Access+Jet (yuk) based application and am dusting off my Perl Skills which have got a bit rusty lately. It will need to run in Windoze. I will replace the database side in MSQL initially (as I may want an embedded app) and will go to MySql or PostGreSql if I need more functionality in the database. I'm an SQL crafter by trade so don't expect too many problems there.

The app will need Data Entry Forms, screen orientated reports, and it would be good to easily produce Pretty Printed Reports (and/or pdf). I've blown my mind searching CPAN and so on for Modules which would avoid me reinventing the wheel. But many seem quite old (abandoned?) or insist on a CGI Route. The reports would benefit from traditional database reporting: Headers, Nested Retrieval, etc. Clearly perl can do all this but it seems silly to code it all from scratch. There doesn't seem to be a reporting framework Module like say powerbuilder would give you.

What do you guys recommend as an Up-To-Date architecture... and which Modules to simplify it. I would prefer to end up with either a standalone app or something running without additional server processes (browser+perl app). If others use it later they are the type to need the minimum to install & configure.

For the Forms: CGI/Perl-Tk/wxPerl or what
For Reports: HTML:Template or what.

Sorry if this a bit of a wideopen first question but as I said I've got a bit out of touch, and I'd like to proceed in a sensible direction.

Update:

Just to emphasise the program functionality I have been using is a bought in application based on Access/Jet which is not a very professional solution. Its also a locked Jet Database so its hard to even code around the problems myself.

I intend implementing a solution with similar but better functionality. I do not intend to use any micro$oft applications to do this.

Thanks for the thoughts so far.

Replies are listed 'Best First'.
Re: Drowning in Modules - Suggest those for DB App
by dragonchild (Archbishop) on Oct 12, 2004 at 00:17 UTC
    If you're just looking to get up and running, you may want to look at the following combination:
    • CGI::Application for the application logic. It also has a ton of plugins to help with basic functions, like building forms, validating parameters, and the like.
    • HTML::Template for display work. If you're doing reports, you might want to look at the sister modules PDF::Template and Excel::Template. I use the combination to create excellent-looking reports.
    • For database access, you will want to look at Class::DBI. If that isn't your cup of tea, the grand-daddy of all database modules in DBI (Class::DBI uses it, for example). You will also need the appropriate DBD, such as DBD::mysql or DBD::Oracle.
    • If you're brave, you could look at Maypole.

    The architecture you will want to look at is MVC, or Model-View-Controller. There are a ton of good descriptions of it on the web.

    Take it easy, take it slow, define everything you're expecting to do, and good luck.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Re: Drowning in Modules - Suggest those for DB App
by NetWallah (Canon) on Oct 12, 2004 at 00:36 UTC
    While I agree with you on replacing the jet DB engine with the more robust/Featured MySQL, I'd like to suggest that the "bathwater" - i.e. ACCESS be retained as a reporting and end-user GUI tool, although the "baby" is being replaced/upgraded.

    Access should still be able to link via ODBC, to the MySQL db, so there is probably no need to re-invent the forms and reports, if they already exist.

    P.S. Hmmm..Interesting .. I just realized how well the "bathwater" analogy fits into your "drowning" scenerio.

        Earth first! (We'll rob the other planets later)

      I can't help but ask why you say that MySQL is more robust/featured than MS Access. Do you have evidence for this?

      C.

        Here is a random opinion.

        In my experience Microsoft Access in multi-user mode has some nasty race conditions. Put it under load and it will randomly lock up and fall over. That would make it significantly less robust than MySQL.

        If you value standards compliance, MySQL wins over Access. (eg Should like use a * or % as the wildcard?) Likewise MySQL does not tie you to a specific OS platform.

        I have no idea which has a better optimizer. I am certainly unimpressed with the Jet engine. But I've never thrown complex queries at MySQL, so I can't say how it compares.

        But on features, I'd suspect that Access wins. If not in features that I care about...

        If 'multi-user' is a 'feature' then I think we have a clear winner. There are packages to patch on to Access which allow 'multi-user' though I don't know if/how well they scale.

        Also direct network access might be construed as a feature if ODBC isn't desired as the interface.

        Also, some may consider portability a feature.

        Authentication and access control are also widely considered to exist in the 'feature' space.

        I am sure there are others in addition to this very brief list.

Re: Drowning in Modules - Suggest those for DB App
by gellyfish (Monsignor) on Oct 12, 2004 at 11:18 UTC

    To be honest if you are going to use MS SQL from the outset then I really wouldn't bother worrying about moving to MySQL or PostgresSQL later if you are planning on staying on a windows platform - it is debatable whether the former has even some of the functionality that MS SQL has and the only major advantage PostgresSQL has (beyond the cost) that I can think of is the ability to write 'stored procedures' in multiple languages.

    /J\

      Yes, MySQL 3.x and 4.x lack support for stored procedures and triggers (which are really just a special case of stored procedures). However, MySQL 5.x (currently under development) will have complete support for stored procedures, triggers, and updateable views. In addition, NDB will be out soon, providing true clustering and failover. Plus, with MySQL 4.1.x, you get the ability to have an embedded server, if that's important to you.

      So, converting to MySQL now will allow a more robust upgrade path than any other database I know of, other than Oracle and, possibly, Sybase.

      Being right, does not endow the right to be rude; politeness costs nothing.
      Being unknowing, is not the same as being stupid.
      Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
      Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Re: Drowning in Modules - Suggest those for DB App
by dga (Hermit) on Oct 12, 2004 at 22:27 UTC

    I don't know if SQLite is available on Windows but it allows SQL queries and can be used as an embedded database. In contrast to mSQL it is still actively supported. (unless that has changed via another package using the same letters or mSQL being developed again since last I looked).

    More generally, since it's SQL if you are careful to use only widely supported SQL syntax, then it should be easy to port the application to use a traditional RDBMS ( Oracle, Sybase, PostgreSQL, MySQL etc )

    There is a DBD::SQLite module on the CPAN.

Re: Drowning in Modules - Suggest those for DB App
by dragonchild (Archbishop) on Oct 12, 2004 at 14:39 UTC
    Another database solution that needs to be suggested is Sybase. A version was just released into the public domain. I think it's the Linux version only, but setting up a Linux server is a fairly trivial task. Plus, I would strongly suggest not using Windows for a business-critical server. Most Windows admins build a reboot into their standard administration protocols. To me, that's a red flag.

    Here's another way to look at it - our Outlook server goes down every two weeks or so. Our Linux servers have never gone down. Ever. And, they've been running for a cumulative total of 5 years.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

      Plus, I would strongly suggest not using Windows for a business-critical server. Most Windows admins build a reboot into their standard administration protocols. To me, that's a red flag.

      This may have been true five years ago (and don't I remember those early versions of NT 4 with horror ...) but I don't believe it is an accurate representation these days. The project I am currently working is implementing an entirely Windows based Billing system for a multinational company with thirteen operating companies in 9 different countries, this kind of thing is quite costly and failure is generally not considered to be particularly career enhancing, so understandably the people making the decisions are extremely cautious when choosing the software and the platform it is to run on. Some very intelligent people have bet their jobs on the reliability of the application. This system achieves in excess of 99.5% availability across eight or more windows servers.

      Windows might not be suitable for every application but to suggest that is not suitable for a "business-critical server" is palpable nonsense.

      /J\

        How about this as a compromise: Maintaining a Windows server to given level of reliability takes more experience than maintaining a Linux server to a similar level.

        Being right, does not endow the right to be rude; politeness costs nothing.
        Being unknowing, is not the same as being stupid.
        Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
        Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

        99.5%? Thats nearly 2 days of downtime a year. I think that most business critical systems would normally be defined as 5 9's (99.999%) which is just over 5 minutes of downtime per year. I have run 'not really critical systems' in the past where the expectation is 4 9's (99.99) which is less (<1 hour/year) (~3157 seconds) and at 3 9's (99.9%) (<9 hours/year), I would have been asked to 'seek opportunities elsewhere'.

        My current server system isn't so critical but it has well past 4 9's and I only admin it very part time. Needless to say it doesn't run a Microsoft OS.

        In fact it has had less than 2 hours of unplanned downtime in 5 years and thus I would consider it 4 9's.

      Minor correction - Sybase hasn't been released into the public domain - rather a specific version of ASE is available for free for both development and production on linux. See http://www.sybase.com/linuxpromo for details.

      Michael

      PS. Yes, I know you know this, dragonchild :-)