Introduction To Sybase, Part 3
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.
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.
Jay Sissom (email@example.com) 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.
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
|Non-Linux FOSS: Seashore||May 10, 2013|
|Trying to Tame the Tablet||May 08, 2013|
- Using Salt Stack and Vagrant for Drupal Development
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- New Products
- Validate an E-Mail Address with PHP, the Right Way
- Drupal Is a Framework: Why Everyone Needs to Understand This
- A Topic for Discussion - Open Source Feature-Richness?
- Home, My Backup Data Center
- New Products
- New Products
- RSS Feeds
- This is the easiest tutorial
5 hours 50 min ago
- Ahh, the Koolaid.
11 hours 28 min ago
- git-annex assistant
17 hours 28 min ago
- direct cable connection
17 hours 51 min ago
- Agreed on AirDroid. With my
18 hours 1 min ago
- I just learned this
18 hours 5 min ago
18 hours 35 min ago
- not living upto the mobile revolution
21 hours 26 min ago
- Deceptive Advertising and
22 hours 2 min ago
- Let\'s declare that you have
22 hours 3 min ago
Enter to Win an Adafruit Prototyping Pi Plate Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Prototyping Pi Plate Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- Next winner announced on 5-21-13!
Free Webinar: Linux Backup and Recovery
Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.
In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.