This bonus chapter is provided with Mastering Delphi 6. It is a basic introduction to SQL to accompany Chapter 14, “Client/Server Programming.” RDBMS packages are generally based so closely on SQL (Structured Query Language, commonly pronounced “sequel”) that they are often called SQL servers. The SQL standard is
defined by an ANSI/ISO committee, although many servers use custom extensions to the last official standard (called SQL-92 or SQL2). Recently many servers have started adding object extensions, which should be part of the future SQL3 standard.
Contrary to what its name seems to imply, SQL is used not only to query a database and manipulate its data, but also to define it. SQL actually consists of two areas: a Data Definition Language (DDL), including the commands for creating databases and tables; and a Data Manipulation Language (DML), including the query commands. The next two sections will explore these two distinct areas.
NOTE All of the SQL snippets presented in this chapter have been tested with InterBase 5 and 6.
SQL: The Data Definition Language
The DDL commands are generally used only when designing and maintaining a database;
they are not used directly by a client application. The starting point is the create database command, which has a very simple syntax: create database “mddb.gdb”;
This command creates a new database (in practice, a new InterBase GDB file) in the current directory or in the indicated path. In the above statement, notice the final semicolon, used as a command terminator by the InterBase console. The opposite operation is drop database, and you can also modify some of the creation parameters with alter database.
NOTE In general, client programs should not operate on metadata, an operation that in most organizations would compete with the database administrator’s responsibilities. I’ve added these calls to a simple Delphi program (called DdlSample) only to let you create new tables, indexes, and triggers in a sample database. You can use that example while reading the following sections. As an alternative, you can type the commands in the Windows Interactive SQL application.
Data Types
After creating the database, you can start adding tables to it with the create table command. In creating a table, you have to specify the data type of each field. SQL includes several data types, although it is less rich than Paradox and other local databases. Table 11.1 lists SQL standard data types and some other types available on most servers.
NOTE The TStringField class in Delphi can distinguish between char and varchar types, indicating the actual type in a property and fixing some the problems of using a char that’s not padded with trailing spaces in the where clause of an update statement.
Programmers who are used to Paradox and other local engines will probably notice the absence of a logical or Boolean type, of date and time fields (the date type in InterBase holds both date and time), and of an AutoInc type, which offers a common way to set up a unique ID in a table. The absence of a logical type can create a few problems when upsizing an existing application. As an alternative, you can use a smallint field with 0 and 1 values for True and False, or you can use a domain, as explained in the next section. An AutoInc type is present in some servers, such as Microsoft SQL Server, but not in InterBase. This type can be replaced by the use of a generator, as discussed in the book.
SQL: The Data Definition Language
Domains
Domains can be used to define a sort of custom data type on a server. A domain is based on an existing data type, possibly limited to a subset (as in a Pascal subrange type). A domain is a useful part of a database definition, as you can avoid repeating the same range check on several fields, and you can make the definition more readable at the same time.
As a simple example, if you have multiple tables with an address field, you can define a type for this field and then use this type wherever an address field is used:create domain AddressType as char(30);
The syntax of this statement also allows you to specify a default value and some constraints,with the same notation used when creating a table (as we’ll see in the next section). This is the complete definition of a Boolean domain: create domain boolean as smallint default 0 check (value between 0 and 1); Using and updating a domain (with the alter domain call) makes it particularly easy to update the default and checks of all the fields based on that domain at once. This is much easier than calling alter table for each of the tables involved.
Creating Tables
In the create table command, after the name of the new table, you indicate the definition of a number of columns (or fields) and some table constraints. Every column has a data type and some further parameters:
• not null indicates that a value for the field must always be present (this parameter is mandatory for primary keys or fields with unique values, as described below).
• default indicates the default value for the field, which can be any of the following: a given constant value, null, or user (the name of the user who has inserted the record).
• One or more constraints may be included, optionally with a name indicated by the constraint keyword. Possible constraints are primary key, unique (which indicates that every record must have a different value for this field), references (to refer to a field of another table), and check (to indicate a specific validity check).
Here is an example of the code you can use to create a table with simple customer information:
create table customer (cust_no integer not null primary key,firstname varchar(30) not null,lastname varchar(30) not null,address varchar(30),phone_number varchar(20));
In this example, we’ve used not null for the primary key and for the first and last name
fields, which cannot be left empty. The table constraints can include a primary key using
multiple fields, as in:
create table customers (cust_no integer not null,firstname varchar(30) not null,…primary key (cust_no, name));
NOTE The most important constraint is references, which allows you to define a foreign key for a field. A foreign key indicates that the value of the field refers to a key in another table (a master table). This relation makes the existence of the field in the master table mandatory. In other words, you cannot insert a record referring to a nonexistent master field; nor can you destroy this master field while other tables are referencing it.
Once you’ve created a table, you can remove it with the drop table command, an operation that might fail if the table has some constrained relations with other tables. Finally, you can use alter table to modify the table definition, removing or adding one or more fields and constraints. However, you cannot modify the size of a field (for example, a varchar field) and still keep the current contents of the table. You should move the contents of the resized field into temporary storage, drop the field, add a new one with the same name and a different size, and finally move back the data.
Indexes
The most important thing to keep in mind about indexes is that they are not relevant for the definition of the database and do not relate to the mathematical relational model. An index should be considered simply a suggestion to the DBMS on how to speed up data access.
In fact, you can always run a query indicating the sort order, which will be available independently from the indexes (although the RDBMS can generate a temporary index). Of course, defining and maintaining too many indexes might require a lot of time; if you don’t know exactly how the server will be affected, simply let the RDBMS create the indexes it needs. The creation of an index is based on the create index command:
create index cust_name on customers (name);
You can later remove the index by calling drop index. InterBase also allows you to use the alter index command to disable an index temporarily (with the inactive parameter) and reenable it (with the active parameter).
Views
Besides creating tables, the database allows you to define views of a table. A view is defined using a select statement and allows you to create persistent virtual tables mapped to the physical ones. From Delphi, views look exactly the same as tables.
Views are a handy way to access the result of a join many times, but they also allow you to limit the data that specific users are allowed to see (restricting access to sensitive data). When the select statement that defines a view is simple, the view can also be updated, actually updating the physical tables behind it; otherwise, if the select statement is complex, the view will be read-only.
SQL: The Data Manipulation Language
The SQL commands within the Data Manipulation Language are commonly used by programmers,so I’ll describe them in more detail. There are four main commands: select, insert, update, and delete. All these commands can be activated using a Query component,but only select returns a result set. For the others, you should open the query using the ExecSQL method instead of Open (or the Active property).
Select
The select statement is the most common and well-known SQL com