IT Architecture Team Report:
Web Access to Databases

Aim and Scope

The University offers a wide variety of tools to help faculty, staff and students build data-driven web sites in support of the hundreds of individual campus applications that involve web access to data. This document focuses upon OIT-supported application development frameworks that make possible such individual applications. The frameworks vary widely in the expertise required to get a site up and running, from the point-and-click interfaces of Blackboard and Almagest to the web-based template-driven framework of DBToolBox to the highly flexible but more programming-intensive environments such as CGI and PHP. This document reviews each framework, summarizing its core functionality and the support it provides to its clients.

Constituencies

The following groups take advantage of the University's data-driven web sites and related resources:

·        Administrators, faculty members, and students who are not necessarily programmers themselves, but who need to move beyond static web page development in order to satisfy a variety of needs;

·        OIT staff developing database-drive web applications for faculty and staff customers. 

·        Students, faculty, and staff learning to build web-database applications with PHP/MySQL.

Development Frameworks available at the University

Application development frameworks at Princeton fall into two broad categories.

·        Web-based application frameworks in which users can build applications using web forms with little or no programming experience (e.g., Blackboard and Almagest).

·        Direct application frameworks in which users build applications in a programming language (PHP and Perl, for example).

By far, the most commonly used database back-end at Princeton is Oracle, in versions 8.x, although version 9.x is being taken up as well (e.g. Almagest).  Many DBToolbox applications use MS Access as well. 

Available Systems

Almagest

Almagest is the University’s web-based media management system and display tool for faculty use.  Managed by the Educational Technologies Center, Almagest permits users to upload and catalog their own collections of media, and then to display these media with a built-in slide show program that is designed to work with digital projectors in the classroom.  Media can be of any mime type, including video files and XML documents, although images are most common.  Catalog data can be simple or complex.  Almagest supports the creation of encyclopedic hypertext as background context for media.  To use Almagest, faculty request a project from ETC staff (usually for a course or a research project) and then add media via a forms-based upload tool.  Users then catalog media using web forms and Almagest’s rich metadata system.  Slide shows are created with LectureBuilder, which can be learned in an afternoon.  The search utility supports project-specific customization of various features.  Almagest also provides tools to add users to a project, either as editors or viewers, and to restrict access to a project’s contents by IP address and/or user authentication.

Blackboard

Blackboard is a web-based course management system.  Also managed by the Educational Technologies Center, Blackboard easily permits users to create web sites for courses or projects.   Site managers and end users can upload content to the site, either as HTML, plain text, or binary format (including Microsoft Word and Excel documents).  In addition, Blackboard offers excellent groupware facilities, including support for threaded discussions, email, and chat, as well as a rich set of other tools, including a digital drop-box, an editable calendar, and a grade check feature.  Interested faculty can receive an on-site tutorial from the Blackboard training team. In-house programmers are continually customizing Blackboard in response to faculty needs and requests. Blackboard can also be used for non-course needs through “organization” sites. As of Spring ’05 we are running Backboard Release 6.

DBToolbox

Formerly the University’s web-based database publishing facility, DBToolbox is maintained by OIT's Desktop Application Services Group.  DBToolbox is slated for retirement in Summer, 2007. DBToolBox is hosted on a Microsoft Windows server and runs under Microsoft Internet Information Server (IIS) and Active Server Pages (ASP).  Its capabilities include data reporting, dynamic queries, workflow management, and Web-based data entry.  Reporting transport methods include SMTP (via e-mail) and HTTP.  Output can be targeted via HTTP to Web browsers or client applications such as Microsoft Excel, Word, and Adobe Acrobat. Automatic text-based formatting supports both HTML and XML output.  Supported database back-ends include ODBC compliant databases such as Microsoft Access, Excel, SQL Server, Oracle, MySQL, and structured text files.  These can reside on a networked file server or on a database server.  DBToolBox also provides a built-in SQL query builder interface, and it permits users to define and administer their own groups for controlling access to reports, queries, and web forms.  OIT is currently working on a DBToolbox to PHP conversion utility.

Webscript

Webscript, the Web Scripting Learning Environment, is a new OIT service scheduled for full release during summer ’05. Webscript is a public Linux Apache MySQL PHP (LAMP) server. Users create a MySQL database on the Princeton University Instructional MySql Facility. Files containing HTML and PHP are created under “public_html” on users’ NAS space and are run by calling them from http://webscript.princeton.edu/~netid. OIT’s EIS/CSG group created and maintains Webscript. It is intended for learning to develop PHP/MySQL applications – not for hosting production systems (see Web Hosting Service, below).

Web Hosting Service

For those departments desiring their own virtual LAMP server, OIT offers a $60/month shared Web Hosting Service. This shared server approach substantially reduces the department’s cost for running and administering their own server.

The shared LAMP server service from OIT includes:

• OIT systems administration for operating system software patches and security fixes
• Server networking and TSM nightly backups of server
• An account (netid “service-netid”) with up to 250 MB of disk space (More disk space can be purchased using a departmental project grant)
• A virtual interface with a network identifier of .Princeton.EDU
• An Apache web server running as user “service-netid”
• Enabled connections on ports 80 (and 443 if required) for .Princeton.EDU traffic
• SSL (Secure Sockets Layer) support available (separate fee for SSL web certificate applies)
• Availability of php, perl, python languages
• A mysql database server running as user “service-netid” and accepting connections on a local socket (socket name /tmp/mysql.service-netid.socket)
• A copy of phpMyAdmin installed for managing a mysql database
• ssh (version 2), scp, sftp for remote access from *.Princeton.EDU hosts for web development work

Departments are responsible for web design and development work, either relying on their own internal web programmers or sub-contracting to an outside web development concern. The shared server approach is not for everyone. Depending on the type of application the department needs to host, OIT may recommend the department use a different resource or a dedicated server. Any requirements for special software or other special needs will be reviewed on a case-by-case basis. The Web Hosing Service was built and is managed by OIT EIS/CSG. For more information, send e-mail to Donna Tatro at tatro@princeton.edu.

Instructional Oracle Facility

Desktop Application Services manages the Princeton University Instructional Oracle Facility (a.k.a. “Storacle”). Storacle permits faculty, staff and students to create via the web an Oracle database account on the Instructional Oracle database server for educational and instructional purposes.   In addition to being accessible by standard tools, such as Oracle’s client programming libraries and DBToolBox, Storacle provides two web-based tools for database management—the Secure SQL Editor and the Secure SQL Loader.

CGI

Desktop Application Services also manages the Princeton University Campus-CGI Facility a customized Apache CGI server.  This campus-CGI facility permits students, faculty, and staff to create applications in a variety of programming languages, including C, C++, Java, Perl and Python—although, significantly, not PHP.   Developers connect to databases programmatically to make them available on the web.  To use the facility, the user must know a programming language and be familiar or comfortable with developing in a UNIX environment.  In addition, the user must make use of whatever database libraries his or her language of choice supports.   To enhance its security, Campus-CGI was recently upgraded to support SSL and LDAP authentication.

OIT maintains a set of Apache-CGI servers designed for in-house development of web applications for a variety of clients.  Like Campus-CGI, these programs are written primarily in Perl and use Oracle as the database back-end; Unlike Campus-CGI these servers support SSL and SNK for enhanced security.  Applications include Princeton University Financial Aid Estimator, the Leaves Calendar, My Academic Record, Storacle (see above), the WebSurvey Facility, the WebAnnounce Facility, and the Survey Center Registration Facility.    

In support of a restricted set of programmers, the Educational Technologies Center currently provides access to a server that supports humanities research computing applications.  Unlike Campus CGI, the server supports rapid development of genuine web applications through the use of mod_perl and its associated programming frameworks, such as Embedded Perl.  In addition, the server runs MySQL and contains libraries that allow programs to connect to Oracle databases.  Hosted applications include the Campus Database, the Piero Project, the Princeton Shahnama Project, the Omiti Survey Project, and the Figura tool used by the Charrette Project. 

PHP/MySQL

With the formation of a Humanities Computing Research unit within Academic Services, some of the ETC applications described above have been migrated to a new application server called gravitas.   The new server is a “LAMP” server (e.g. running Linux, Apache, MySQL and PHP) that runs the mod_perl environment in addition to PHP.

The DAS group in Academic Services is currently working with EIS/CSG to build a multiple server PHP/MySQL development and production environment in which to house the applications they develop. There are currently two LAMP servers, aslamp01 and aslamp02, which are used for production and development, respectively. Also there is a PHP/IIS server called Winscript that will be the home of the DBToolbox to PHP converter and will provide PHP-Access database functionality.

Appendix: Selected Framework Feature Comparison

The following table describes the basic architecture of selected frameworks.  The information presented here is based primarily upon publicly available information and from discussions with the lead programmers involved in supporting each framework.

	Almagest	Blackboard	DBToolBox	LAMP	CampusCGI	OIT Internal	ETC
Database	Oracle 9	Oracle 8	Access, Oracle 8	Oracle 8	MySQL	Oracle 8	Oracle 9, MySQL
Web server	Apache	Apache	IIS	Apache	Apache	Apache	Apache
Host OS	Solaris	Solaris	Windows	Linux	Solaris	Solaris	Solaris
Application Server	Tomcat	Tomcat	ASP	CGI	CGI	CGI	Mod_perl
Development Languages	Java, XML, ActionScript	Java, Perl	Visual Basic	PHP, Perl	Perl, Python, C, C++, Java	Perl, Python, C, C++, Java	Perl
Programmers’ Template Framework	Velocity, XSLT	None	None	PEAR	None	None	Embed Perl, PHP template Text::Template
End users’ Template Framework	Web forms	Web forms	Proprietary	None	None	None	None
Presentation Framework	DHTML, Flash	DHTML	DHTML	DHTML	DHTML	DHTML	DHTML
API?	No	Yes	No	No	No	No	No
OKI Compliance?	No	No	No	No	No	No	No
Flash Remoting?	Yes	No	No	No	No	No	No
Native XML Support in db?	Yes	No	Some	No	No	No	Yes
Support for groups and projects?	Yes	Yes	Yes	No	No	No	Yes
Core User Group	Faculty	Faculty	Staff	Faculty, Staff, Students	Faculty, Staff, Students	OIT Staff	Faculty
Core Use Domain	Teaching	Teaching	Administration	Research, Teaching, Administration	Research, Teaching	Research, Teaching, Administration	Research
Core Application Type	Image archive and slideshow	Course management	Administrative data	General	General	General	Text and image archives
Managing Group	ETC	ETC	AS	EIS	EIS	EIS	ETC