E131 Website Application Framework with Sybase, Apache & Perl

1. E131 Website Application Framework with Sybase, Apache & Perl • Michael Peppler Senior Consultant Data Migrations, Inc. mpeppler@peppler.org

2. Application Framework for Apache/Perl Introduction The application framework presented here will be illustrated by a small web-based bug tracking system that I wrote for this presentation, and that is loosely based on Bugzilla. The source code to the entire system is available at http://www.peppler.org/downloads. This system is not really production quality, but should serve as an illustration for the ideas presented here.

3. Application Framework for Apache/Perl Goals The target is to create a system where adding new functionality is easy, where the interdependency of the components is low, and where changing business requirements can be satisfied with a minimum of disruption.

4. Application Framework for Apache/Perl Perl as Large Project Language Perl is a flexible language with a powerful modular structure and very strong text handling ability. • Literally thousands of existing modules • Comprehensive Perl Archive Network (CPAN). • Strong Regular Expressions • Makes string manipulation very easy • Very flexible data handling • Dynamic memory management.

5. Application Framework for Apache/Perl Perl's OO Model Perl's Object model is flexible, allowing the programer to easily create a functional system. • Use a class paradigm where it makes sense. • A class is just a perl package, where the object has been blessed. • Inheritence is of course supported. • Use packages to create separate namespaces for non-class subroutines and data.

6. Application Framework for Apache/Perl Apache Web Server A modular Open Source web server - with a proven track record. • Integrates very well with perl via the mod_perl module. • mod_perl provides a persistant perl interpreter. • Use with normal CGI scripts, or with ad-hoc handlers. • Installing and configuring mod_perl is beyond the scope of this presentation, but plenty of information is available on the modperl mailing list.

7. Application Framework for Apache/Perl Linux Linux gives you the power of Unix on cheap Intel hardware. For web servers this is just about perfect. Note that the code/system presented here will work on most Unix platforms, and may work on Windows to a certain extent.

8. Application Framework for Apache/Perl General Applicability While some of the techniques that we'll describe today are specific to Web/CGI applications, a lot of it is applicable to generic application development with perl.

9. Application Framework for Apache/Perl Basic Components A web site (or just about any application) can be reduced to the following: • Presentation layer • HTML for a web application. • Application layer • Database layer • Stored procedures, INSERT/UPDATE/DELETE

10. Application Framework for Apache/Perl Development Process Web sites have a tendency to start small and grow by adding new functionality. This tends to generate a system with little structure, and a lot of inter-dependencies. • Repeated code • SQL requests in many places

11. Application Framework for Apache/Perl Some Principles • Loose vs. Tight Coupling • Well defined database requests • Centralized Error handling • Stored procedures

12. Application Framework for Apache/Perl Loose Coupling By enforcing separation between different functions/layers we achieve a flexible system. • Loose vs. Tight Coupling • Well defined database requests • Ability to update parts of the system without having to validate the entire system each time

13. Application Framework for Apache/Perl HTML Templates Use a template system that separates code from HTML. • HTML can be updated by people who don't know perl. • Less likely to introduce bugs when UI definitions change.

14. Application Framework for Apache/Perl Well Defined Database Requests Do not code ad-hoc queries as/when necessary. Instead, create application calls that encapsulate the necessary database requests. • Localize all database access to one (or a few) files. • Simplified application development • All actions required for an operation are encapsulated in the request.

15. Application Framework for Apache/Perl Centralized Error Handling Database requests can fail for various reasons. Other things can go wrong as well. Using a centralized error handler means: • Consistency • Code simplification

16. Application Framework for Apache/Perl Stored Procedures By forcing all access to the database through stored procedures you achieve: • Data integrity • Access control • Performance • Procedures are tunable independently from the application level code.

17. Application Framework for Apache/Perl Stored Procedures (2) Additional advantages of using stored procedures: • Application developer (perl programer) may not know SQL well. • The DBA can quickly review all the SQL that the server runs.

18. Application Framework for Apache/Perl Benefits • Simplified application-level coding. • Improved development speed. • Flexibility when requirements change.

19. Application Framework for Apache/Perl The following basic components are needed in the framework: • HTML Template module • Configuration module • Database request module • Application module(s)

20. Application Framework for Apache/Perl HTML Template handling • Several templating systems exist on CPAN (HTML::Template, Mason, etc) • Some systems allow embedding perl code in the HTML. • Creates a very powerful system. • Drawback – application code and presentation layer get intermixed.

21. Application Framework for Apache/Perl HTML Template handling (2) • The system I favor completely separates the HTML from the code. • All the data needed for the page is marshalled and stored in the Template object. • Handles lists of data (i.e. dynamically generated tables) • Call parseTemplate() to generate the actual HTML from the template.

22. Application Framework for Apache/Perl HTML Template handling (3) For extra flexibility you can define a simple macro system that you use in "source" templates. These macros can then be converted to their actual values using a simple "compiler" (a small perl script to do the macro substitution). This is a good way of maintaining different versions of the same site (for example different UI look & feel for different OEMs).

23. Application Framework for Apache/Perl Configuration handling You obviously don't want to put things like server names, file locations, etc. directly in the code. There are several configuration packages available on CPAN - I use a simple key/value pair handler module that automatically reloads the configuration file if it is modified. • my $fileDir = My::Config->get('serviceDir');

24. Application Framework for Apache/Perl Table-Driven Database Calls A single perl module is used for all database calls. • Developers don't need to know Open Client API. • Stored procedure parameter sending and result retrieval is centralized. • Error handling is consistent across all calls.

25. Application Framework for Apache/Perl Table-Driven Database Calls (2) Each database request (stored procedure) can be described in a perl data structure. • Input parameters and their data types. • Result set description. • Non-fatal error conditions.

26. Application Framework for Apache/Perl Table-Driven Database Calls (3) Each database request is mapped to a perl subroutine. For example, adding a new user to the small bug database system example would look like this: my $req = new My::Request; $req->addUser(email => $email, password => $password); where $email will be assigned to @email and $password to @password before calling the stored procedure.

27. Application Framework for Apache/Perl Table-Driven Database Calls (4) • The My::Request object handles the entire database request. • Any data that is returned can be retrieved with accessor methods. • Result sets are fully described in the configuration for the database call, and data for each result set is fetchable by name. • An error handler is automatically called if the stored procedure fails (negative status result) or if the procedure returns a non-0 return status.

28. Application Framework for Apache/Perl Table-Driven Database Calls (5) The perl subroutine has a configuration hash that defines all the inputs and outputs for the request. The configuration hash consists of several sections: • name – the name of the stored procedure. • param – a list of parameter names, with type. • output – a list of named result sets. • server – logical server name. • database – logical database name.

29. Application Framework for Apache/Perl Database Calls Configuration - Parameters Stored procedure parameters are described by name: param => { user_id => { type => CS_INT_TYPE, opt => 0 }, description => { type => CS_CHAR_TYPE, opt => 0 }, } The opt attribute, if set, tells the request module that NULL or missing values are OK for this parameter.

30. Application Framework for Apache/Perl Database Calls Configuration - Output Stored procedure result sets are positional – but are given names for easier use: output => [ { name => 'addBug', cols => [ qw(bug_id email owner_id) ]} ] In this example the procedure returns a single result set with three columns.

31. Application Framework for Apache/Perl Database Requests – Error Handling An error handler is automatically called if the stored procedure returns a non-0 status. By convention a negative status, or a status > 100 is considered a fatal error. Fatal errors generate a special web page, and an email with full details of the requests is sent to an error alias.

32. Application Framework for Apache/Perl Perl Method Auto-Loading Table-driven database calls can be combined with perl's auto-loading features to make adding new functionality very simple. Auto-loading is a technique where perl loads subroutines at run-time as they are called. To do this you need to pre-declare the auto-loadable subroutines, and you define an AUTOLOAD block that executes whenever a subroutine is called that has been pre-declared but is not currently loaded.

33. Application Framework for Apache/Perl Auto-Loading The AUTOLOAD block needs to be coded to load the appropriate file that has the code for the subroutine, and eval it so that it gets added to the running perl symbol tree.

34. Application Framework for Apache/Perl Connection Pooling When using a web farm you can reduce the load on the database server by using connection pooling. Generally this is done by writing an ad-hoc Open Server - but the same functionality can be achieved by letting Apache and mod_perl handle this via the Apache::Sybase::ConPool module.

35. Application Framework for Apache/Perl Apache::Sybase::ConPool This module takes advantage of Open Client's ability to share database connections across child processes. The ConPool module will open a predefined number of connections to your database server(s), and uses System V semaphores to coordinate access to the connections between each Apache child process.

36. Application Framework for Apache/Perl Examples Now it's time to look at some actual code that implements these principles. The code is taken from a simple bug tracking database system loosely modelled on Bugzilla. The code is available for download from http://www.peppler.org/downloads.