Introduction To Sybase, Part 3

Writing a Sybase web application.
The Application Database

In our application, we use two databases. The first is the pubs2 example database. We use the titles table as the list of books. This table is read-only. We just view it; the application does not make changes to it. The second database is called book_d and it is created on two devices. The log segment is on device02 and has 10MB allocated to it. The data segment is on device01 and has 20MB allocated to it. There are five tables in this database.

  • inventory_t: this table is a one-to-one relationship to the titles table in the pubs2 database. There is one row in this table for each row in the titles table. This table contains the number of books in stock and the number on order.

  • orders_t: this table is a list of customer orders.

  • order_nbr_t: this table has one row in it. It is used to guarantee that the order number is always unique.

  • types_t: this table is a list of the types of books. It is used to populate the drop-down box on the search screen.

  • user_t: this table is a list of customers.

To provide access to the data, we use stored procedures in all cases except one. Stored procedures are SQL code stored in the database. This allows us to encapsulate procedures in the database so it doesn't have to be replicated in all your applications. It also provides a performance benefit. When the stored procedure is loaded, the SQL is precompiled. When the application runs, the server doesn't have to precompile the SQL, so the application should run faster. We used SQL in the search1 procedure, because it would change dramatically depending on the parameters given.

In the scripts, we have also created a user to access the database. Each time the CGI script runs, it logs in as this user. The end users of your application do not need to know this, and in fact, shouldn't know it. Even though this user has the minimum rights necessary to run the application, you should protect this user name and password.

This is a very small application. A few more things must be done if you put this application into production.

  • Make it look nicer. This application would have looked fine in the early 90's, but in today's world, it would need to look much better before people would use it.

  • Prepare and schedule automatic backups. Use cron to back up your data at regular intervals.

  • Prepare and schedule database consistency checks. These should be run regularly, again using cron to schedule these checks.

  • Create and tune table indexes. These tables have no defined indexes. An index is extra data about the table that will allow the database server to access the data faster.

Explaining how the database server uses indexes is beyond the scope of this article—I have a complete book on the subject. Before you put an application into production, you should load example data in all the tables and test queries to be run in your application. Based on the queries, you can make good guesses as to which indexes will be needed. There is a command in SQL that can help tune indexes. It is set showplan on. Entire book chapters are devoted to explaining this command and its output.

Summary

As you can see, using a Sybase database as the basis for your client-server applications is not a trivial task. The Sybase database server is an industrial-strength database, capable of handling hundreds of users and many gigabytes of data. Many databases are available for Linux. Of the free ones, I believe the Sybase database server is the most powerful. If you don't need a powerful database, there may be better choices. If, however, you need a high-power database to manage a large amount of data or many users, a Sybase database server would be a solid foundation.

Resources

Jay Sissom (jsissom@indiana.edu) is responsible for the web-based front end to the financial decision support data at Indiana University. He has installed and supported Sybase databases on many operating systems and has written database clients for the web. When not programming, he enjoys amateur radio and playing his bass guitar and keyboards.

______________________

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState