Creating Database‐backed Library Web Pages Using Open Source Tools

Eric Jukes (Asset and Information Systems Manager, Learning Resources, College of North East London (CONEL), UK)

Program: electronic library and information systems

ISSN: 0033-0337

Article publication date: 25 July 2008




Jukes, E. (2008), "Creating Database‐backed Library Web Pages Using Open Source Tools", Program: electronic library and information systems, Vol. 42 No. 3, pp. 323-326.



Emerald Group Publishing Limited

Copyright © 2008, Emerald Group Publishing Limited

What are database‐backed library web pages and what do databases have to do with publishing web pages anyway? Anyone who has produced anything beyond the simplest of HTML web pages is likely to have used DreamWeaver, FrontPage or a similar package which, in effect, produce lines of HTML. In fact, all database‐backed web pages are essentially web‐based reports. Web pages search a database, and embed the results within HTML tags, and then output the results to a web server. Using databases can make web site maintenance and data publishing much easier.

This book is different from many covering database‐backed web sites as it is written by a librarian for librarians, and particularly those who would like to try their hand at developing database‐backed web pages but are not sure where to start. The author, Stephen R. Westman, is the Digital Information Services librarian at the University of North Carolina at Charlotte.

An aim of the book is to encourage the use of open source tools, which, according to the author, will enable the reader to build a complete, very powerful and dynamic database‐web presence for the cost of a basic PC, and, in the process, “strike a blow for truth, justice, and the library way!” Indeed, Eric Lease Morgan, in the Foreword waxes lyrical about open source tools “a topic near and dear to my heart” saying that “open source software is just as much about freedom as it is about computer technology and, in fact, it uses computer technology to express freedom”. Morgan goes on to say that “these principles are not too far from the principles of modern librarianship, therefore, the use of open source software is a natural fit for librarians”.

The book comprises ten chapters, including the introduction. Chapter 2 – “Database basics” – examines how databases are used to store and retrieve data, structured query language (SQL), focussing on relational database management systems (RDBMS). The chapter very usefully describes structured text files, comma‐separated values and database structure including object‐oriented databases, XML databases and (familiar to librarians) MARC systems. Relations are covered in some detail – one‐to‐one, one‐to‐many, many‐to‐many, and a number of pages are devoted to SQL, multiple tables, and two and three‐table joins. The chapter concludes with various choices of open source systems for RDBMS, each system having varying degrees of open source‐ness each with its own pros and cons. The main candidates are MySQL, PostgreSQL, Firebird, mSQL, Ingres and Genezzo.

Chapter 3 – “Setup and administration” looks at moving data already stored in another system into MySQL, and guides the reader through selecting appropriate fields, creating data structures, taking downloaded data from a legacy system and importing it into newly created MySQL tables, and finally establishing user accounts that can access that data via a web‐based output page. In fact, this entire chapter is very useful for anyone who needs to migrate a legacy system. The chapter includes a useful section on how to define the data, e.g. type – char, varchar, integer, Boolean etc. size, search, and description. The reader is also taken through the difference confusing concept of NULL versus NOT NULL.

“Introductory programming” – Chapter 4 – is demonstrated using a cooking recipe. It may well be that there are those who are familiar with the creation of shrimp étoufée. Unfortunately I was not, so I was unable to relate to, and therefore “analogise” with such terms as “roux”. When analogies are used to describe programming they can be an excellent teaching tool, but the analogy must be with something well‐known to the reader or the purpose of the analogy is completely lost or confused. Similarly, if a book is intended for audiences in countries other than the country of publication, care should be taken not to use particularly “in‐jokes” for example “I am a Yankee, but earned a Master's degree from LSU, where I became a Cajun‐wannabe”. On much the same point, the name of the Foreword writer, Eric Lease Morgan, was not familiar to me, the author did not give Mr Morgan's status at all, and his efforts were not even mentioned in the Acknowledgements section of the book. A Google search reveals that Mr Morgan is Head of the Digital Access and Information Architecture Department at the University Libraries of Notre Dame, Indiana, and clearly has some eminence in the library field in the United States ‐it is important that the information should be communicated to the wider audience.

Chapter 5 covers “Creating reports”. This is a particularly important chapter, and the author deals with the subject in detail. The reports are created in PHP, beginning with a simple example of searching with a telephone number database created in an earlier chapter. The chapter builds upon this example to look at ways to expand and build more sophisticated searching techniques with multiple ANDs.

Having covered the basics in designing and implementing databases, writing reports and simple search applications, Chapter 6 turns to working through an actual project – defining the project, defining the data model, building the model, and designing the application. The author has developed a number of useful forms for the project design phase, these are all available as downloadable PDF versions as part of the companion materials available on the, now customary, web site which complements the book. The author's companion web site for the book can be found on the web site of the publishers, the American Library Association. It contains links for downloading function libraries, scripts, and other files used in the examples, a bibliography, and other resources, and includes an errata page.

Chapter 7 looks at the position once the specifications have been developed and agreed upon. If it is right in the planning phase, then the programming part should be easy. Before the programming begins, there is a need to implement the data model in the database and to create a configuration file that will contain information and parameters to be used throughout the application. Each of these is considered in turn in this chapter – “Programming the application” – which looks at implementing the data model, setting up foreign key support and authority table maintenance.

Clearly, there are a number of ways in which an application can be compromised at the security level, and Chapter 8 covers “Security‐related techniques”, both internal and external, access control with authentication and authorisation, and session support. Session support, which was added to PHP as of version 4.0, is a mechanism whereby PHP can remember individual users. Controlling access to an application is another consideration as well as dealing with what happens within an application once users are interacting with records in the database. Encryption is another important area covered within this section.

In Chapter 9 – “Creating public interfaces” – the author recognises that even a representative sampling of possible approaches to creating public interfaces is beyond the scope of the book, so, instead, he examines a few useful ideas and techniques, including implementing two applications covered in an earlier part of the book – dynamically generated subject pages and a search interface. Public searching is an important facility for end users and the chapter looks at implementing a search facility and covers important issues including stop words, values from foreign tables, providing links from authority entries and full key‐word searching. The final part of the chapter includes the all‐important testing, letting the users try it out, and finally moving into the production phase. The chapter ends with a section on maintaining and upgrading the application. It would have been helpful too if at least one screen illustration of a completed public interface was included

The final chapter – Chapter 10 – “Development procedures” – is quite brief and looks at practices designed to standardise the approach, make code readable and, more importantly, supportable, and ensure proper functioning with a minimum of effort. The chapter covers four areas: establishing a development process, implementing programming standards, establishing quality assurance procedures, and using good debugging techniques.

There are two appendices. The first appendix is the shrimp étoufée example on which I have already commented, but, helpfully, the appendix does include the program structure with diagram as well as “code” for creating the étoufée. The second appendix – “Programming Standards” – emphasises the principle of consistency in naming, which should make code self‐documenting. Databases, tables, fields, functions, files and variables are all included. As well as easy‐to‐read code, a programmer ought to include sufficient commenting, and this subject ends the appendix.

The book includes a detailed glossary, covering from *nix (the shorthand way of saying the various flavours of UNIX) to web‐aware (a database has an API or other techniques that allow web‐based programs to be written to dynamically access data contained in the database).

This is a book with an all‐over pleasant layout and which is of right length and balance. The type font is just about the right size, although the code listings are bordering on being too small. More youthful readers than myself will not have a problem! Clear diagrams are used to illustrate various points, e.g. database structure, and there are neither too many nor too few.

Whilst we are always told that we cannot judge a book by its cover, this book does have a very pleasingly designed cover, of a light green, black, and white, over a faint binary design. Very nice! I certainly am happy to recommend Creating Database‐backed Library Web Pages Using Open Source Tools, but be under no misapprehension, this is not a book for a complete beginner. The author says he does not “assume anything beyond a basic level of familiarity with HTML and the ability to create HTML forms, but Foreword writer, Eric Lease Morgan, does say “The process is not easy … ” I agree with him!

Despite my criticisms, which some may consider carping, this is an excellent guide to programming within the library environment, including, as it does, plenty of useful, practical material. But it is only for the dedicated and determined, and not for a casual reader. It is also a book which ought to appeal to a wider readership, despite its library orientation. For example, information professionals, students and others, will find the library subject‐oriented approach of more interest than books which use employees and sales figures as examples.

Related articles