1.4.1. Alt and Function keys

Sometimes the Alt keys or the F1-F10 keys may not function in the terminal where you are running Interactive SQL.

To emulate the Alt key, press Ctrl-A. Then press whatever key was to be pressed with the Alt key. For example, instead of pressing Alt-F, you would press Ctrl-A, then F.

To emulate the function keys, press Ctrl-F, followed by the number of the function key you wanted to press. For example, instead of pressing F9, you would press Ctrl-F, then 9. For F10, use the zero key.

1.5.1. Definition

A relational database-management system (RDBMS) is a system for storing and retrieving data, in which the data is organized in tables. A relational database consists of a collection of tables that store interrelated data.

If that doesn't quite make sense yet, read on.

1.5.2. Example

Suppose you have some software to keep track of sales orders, and each order is stored in the form of a table, called sales_order. It has information about the customer (for example, her name, address and phone number), the date of the order, and information about the sales representative (for example his name, department, and office phone number). Let's put all this into a table, with the data for a few orders:

Everything appears nice and ordered, but there's a fair bit of redundancy. M. Devlin's name appears twice, along with his address and phone number. E. Peros' details appear three times. If you look carefully at the employee side of things, you'll notice that M. Garcia is repeated, as well.

Wouldn't it be nice if you could separate that information and only store it once, rather than several times? In the long term, it would certainly save disk space and allow for greater flexibility. Since redundant data entry is minimized, it would also reduce the chances of erroneous data entering the database, increasing consistency. Well, we can see three different entities involved here: the customer, the order, and the employee. So let's take each of the individuals, put them into categories, and give them identification numbers so they can be referenced.

As you can see, each customer's information is stored only once, and the same goes for each employee. The sales_order table is a lot smaller, too. Each row, representing a sales order, refers to a cust_id and an emp_id.

By looking up the customer corresponding to a cust_id (which is unique), one can find all the needed data on that customer, without having to repeat it in sales_order. In addition, an id column has been added. Its purpose will be explained in the next section.

Why do this, you ask? By eliminating redundancy, this kind of structure reduces the opportunities for inconsistencies to seep in, in addition to lowering storage requirements. If you had to change E. Peros' address in the old sales_order table, you'd have to do it three times, which would take three times as long and give you three times as many chances to make an error. In the newer table, all you'd have to do is change her address once, in the customer table. Also, by carefully separating data, you make access control simpler.

Finally, can you spot another redundancy? The employee table has "Sales" all the way down the dept column. For an organization with multiple departments, you'd want to add a department table and reference it from a dept_id column instead.

1.5.3. Primary and Foreign Keys

As described in the previous section, you can separate a table into interrelated tables. But how do you go about relating tables to each other? In relational databases, primary keys and foreign keys help you link tables together. Primary keys are columns that uniquely identify each row of a table, and foreign keys define the relationship between the rows of two separate tables. Proper use of primary and foreign keys will help you efficiently hold information without excessive redundancy.

Every table should have a primary key to ensure that each row is uniquely identified. This often takes the form of an ID number being assigned to each row, as in the previous section's example. The id column forms the primary key.

As long as you can guarantee the uniqueness of the data in a particular column, though, that column can be a primary key. For example, if you only want one entry per day to be put into a particular table, you could use the date as that table's primary key.

Tables are related to one another by foreign keys. In the sales_order example, the cust_id and sales_rep columns would be called foreign keys to the customer and employee tables, respectively. For terminology's sake, you might want to know that in this case, the sales_order table is called the foreign or referencing table, while the customer and employee tables are called the primary or referenced tables.

4.1.1. Creating a database from the command prompt

The command line utility for creating a database is dbinit.

Syntax:

dbinit [switches] db-file-name

db-file-name is the name you would like to give to your database file, for example, mydb.db. If you issue the command "dbinit -?" you'll be shown the above syntax, along with a list of options you can use.

To create your first Adaptive Server Anywhere database on Linux, enter the following command:

dbinit -t './logs/mydb.log' p 4096 mydb.db

This command creates a database in the current working directory called mydb.db with a page size of 4096 bytes, specified by the -p switch. Assuming the directory exists, it also creates the transaction log mydb.log in the subdirectory "logs," specified by the -t switch. Adaptive Server Anywhere databases carry the extension ".db" .

4.1.2. Creating a database from Sybase Central

To create a database in Sybase Central, open the Adaptive Server Anywhere section of the left pane, and select Utilities. Double-click Create Database in the right pane, and follow the on-screen instructions.

4.2.1. Running the server as a daemon

Sometimes it's necessary for the server to run outside of the current session (that is, regardless of who, if anyone, is logged in). To do so, use the -ud switch at the command line when starting the server to run it as a daemon.

The following command would start up a database server as a daemon, using the database we created before:

dbsrv7 -ud -n MyDatabase mydb.db

NOTE: Using "&" to run the database server in the background does not work.

4.5.1. Connection strings

Connection strings are frequently used when performing actions on a database. They consist of a list of parameter settings, delimited by semicolons and enclosed in double quotes. There should be no extra spaces in a connection string.

Example:

"uid=DBA;pwd=SQL"

The short strings of letters just before each equal sign (in this example, uid, pwd, and dbf) are called keywords, which each correspond to a connection parameter. There are many connection parameters available, and they are listed in the Connecting to a Database chapter of the Adaptive Server Anywhere User's Guide. They are also described in detail in the Connection and Communication Parameters chapter of the Adaptive Server Anywhere Reference.

When Adaptive Server Anywhere utilities are looking for connection parameters, they check the SQLCONNECT environment variable for any parameters that were left out of the connection string. If you're putting connection parameters into the SQLCONNECT environment variable, replace the equal signs with number (#) signs. In bash you would use the following command:

SQLCONNECT='uid#DBA;pwd#SQL'

The single quotes are necessary in the above command because semicolons can be used to separate bash commands. You can also use double quotes.

To make SQLCONNECT available in subsequent shells, you'd need to use "export SQLCONNECT" to export the SQLCONNECT variable to the environment. You may also want to put these commands into your .bash_profile (or .profile, if you're using another shell) if you want the same connection parameters to be available each time you log in.

4.5.2. Connecting from Interactive SQL

To connect to a database from Interactive SQL, go to the Command menu, and choose "Connect...", then fill in the dialog box as appropriate.

4.5.3. Connecting via ODBC

ODBC (which stands for Open Database Connectivity) is an industry-standard interface for connecting client applications to relational and non-relational DBMSes. When you create an ODBC data source, it encapsulates the data and any other information required to get the data, including connection parameters.

dbdsn [ modifier-switches ] 
 { -l 
 | -d dsn 
 | -g dsn 
 | -w dsn [details-switches] 
 | -cl                        }

5.1.1. Full vs. Incremental Backups

A full backup makes copies of the main database file and the transaction log file. While it's the most basic and essential type of backup, it usually isn't practical to regularly perform full backups of large databases. As a result, incremental backups are commonly used.

An incremental backup makes a copy of the transaction log alone. It takes place as part of a cycle that begins with a full backup, which is then followed by a given number of incremental backups. Since only the transaction log is copied, an incremental backup uses less time and resources, making it particularly suited for large databases. Keep in mind, though, that the more time you leave between full backups, the greater the risk of losing data in the event that one of the transaction logs becomes unusable.

5.1.2. Online vs. Offline Backups

An online backup is performed without stopping the database server. It provides a consistent snapshot of the database, even as the database is modified. Online backups are useful for databases with high availability requirements, but they won't complete until all active transactions are complete.

In contrast, offline backups are performed once the database server has been shut down. They're useful for when the database can be taken down on a regular basis. You make offline backups simply by copying the pertinent files to another location using the cp command in a terminal window.

In either case, both full and incremental backups can be performed.

5.1.3. Server-side vs. Client-side Backups

An online backup can be performed from a client using the dbbackup command line utility. This is known as a client-side backup, and it puts a backup of the database on the client machine.

An online backup can also be performed on the server by issuing the BACKUP statement in SQL. Server-side backups are generally faster, owing to the fact that client-side backups usually depend upon transport across networks.

5.1.4. How to make a backup

BACKUP DATABASE DIRECTORY backup-directory
  [ WAIT BEFORE START ]
  [ DBFILE ONLY ]
  [ TRANSACTION LOG ONLY ]
  [ TRANSACTION LOG RENAME [ MATCH ] ]
  [ TRANSACTION LOG TRUNCATE ]

6.1.1. Creating a Table

When you first create a database in Adaptive Server Anywhere, the only tables it contains are the system tables. To create tables to hold your data, use either the CREATE TABLE statement in SQL or the Sybase Central Table Editor. You must have the DBA or RESOURCE authority to create a table, and you must have the DBA authority make another user its owner.

The CREATE TABLE statement has an extremely broad range of options that are documented in the Adaptive Server Anywhere Reference, so only a small subset of options are described here. The basic syntax is as follows:

CREATE TABLE owner.table-name
  (column-name datatype [, column-name datatype]...)

The "owner." portion before tablename is optional, and is used by a user with the DBA authority to make another user the owner of the new table. table-name and column-name, respectively, are the names of the table and its columns. Insert the words PRIMARY KEY after datatype to make it the primary key.

See the SQL Data Types chapter of the Adaptive Server Anywhere Reference for a list of the types available and their characteristics.

To create a table named customer with columns id, name, address, city_state_zip, and phone, with id as the primary key, for example, use the following CREATE TABLE statement:

create table customer 
 (id integer not null primary key, 
  name char ( 35 ), 
  address char ( 35 ), 
  city_state_zip char ( 35 ), 
  phone char ( 12 ) 
 ) 

It's also important to add "not null" in the case of id, since it's the primary key.

To create a table in Sybase Central, connect to your database and open its Tables folder. If you double-click "Add Table," Sybase Central Table Editor will be opened and using the button bar, you can set up the table as you wish. Hover the mouse pointer over each button to find out what it does. Don't forget to make a primary key before you close the Table Editor!

Some table creation options documented in the Adaptive Server Anywhere Reference but not here that you might be interested in include automatic incrementation (often used on the primary key), constraints, and foreign keys.

6.1.2. Making Alterations to Tables

You can make many kinds of changes to a table once it's been created. Some of the things you can do include the following:

• rename a table

• add, remove, or rename columns

• change the datatype, default value, or length of a column

As with creating tables, you can alter them through SQL or Sybase Central. To alter a table in SQL, you use the ALTER TABLE statement. ALTER TABLE has a great variety of options, which are described in detail in the Adaptive Server Anywhere Reference. You'll see a few basic examples here just to get you started.

To rename the customer table to cust:

alter table customer 
  rename cust

To add a company_name column to cust, with a maximum length of 35 characters:

alter table cust
  add (company_name char (35) )

To give company_name a default value of "n/a" :

alter table cust
  alter company_name set default 'n/a'

6.2.1. User IDs

GRANT CONNECT TO userid1 
  IDENTIFIED BY password1 

grant connect to mortimer identified by
monkey 

6.2.2. Permissions

This section explains permissions on tables that can be granted to users. Permissions are granted on a user-by-user basis.

There are a few different table permissions that can be granted to a user, and they are each granted separately.

• SELECT allows the user to read data, and can be restricted to particular columns.

• INSERT allows the user to add data.

• UPDATE allows the user to change data, and can be restricted to particular columns.

• DELETE allows the user to remove data.

• ALTER allows the user to modify the structure of a table.

• REFERENCES allows the user to add indexes, primary keys, and foreign keys.

• ALL includes all the above permissions.

With the exceptions of ALTER and REFERENCES, which apply to tables exclusively, the table permissions apply to both tables and views. The SQL syntax for granting permissions is as follows:

GRANT [ SELECT (column-name, ...) 
      | INSERT 
      | UPDATE (column-name, ...)
      | DELETE 
      | ALTER 
      | REFERENCES 
      | ALL                                          ] 
ON table-name 
TO userid

The user userid is given the specified permission(s) on the table identified by table-name. If the permissions granted include SELECT and/or UPDATE, they are granted only on the columns specified in column-name.

Let's say a list of available banana types is stored in the type and quantity columns of a table named banana_supply. To allow Mortimer to see a list of available banana types along with their quantities, use the following SQL statement:

grant select on banana_supply (type, quantity) to mortimer

When you grant a permission to a user, you have the option of granting him the ability to grant that same permission to others. To grant a user the permission to do so, add WITH GRANT OPTION to the end of your users GRANT statement when you're granting them their permissions.

To allow Mortimer to see a list of banana types available along with the quantities of each, as well as allowing him to grant others the same SELECT permission, use this SQL statement:

grant select on banana_supply (type, quantity)
to mortimer
      with grant option 

6.2.3. Authorities

An authority is a different level of permission. There are two types of authority.

6.2.4. Removing Users and Revoking Permissions

The SQL statement to delete a user ID is REVOKE CONNECT.

Syntax:

REVOKE CONNECT FROM userid [, userid ]

As suggested by the portions in square parentheses, it's possible to remove multiple user IDs in a single statement. For example, to remove the user IDs for Mortimer and Chestington, execute this statement:

revoke connect from mortimer, chestington

To revoke permissions or authorities given to a particular user, you take the original granting statement, replace the GRANT with REVOKE, and replace the TO with FROM. To take away Mortimer's permission to view the banana_supply table, for example, use this REVOKE statement:

revoke select on banana_supply (type, quantity) from mortimer

6.2.5. Changing Passwords

To change the password associated with a particular user ID, use a GRANT CONNECT statement again:

GRANT CONNECT TO userid IDENTIFIED BY newpassword

For example, to change the DBA's password from "SQL" to "d0n13xw9," use this statement:

grant connect to DBA identified by d0n13xw9

6.3.1. Increasing password security

By default, passwords can be any length. For greater security, you can enforce a minimum length on all new passwords, to make them more difficult to guess. You do this by setting the MIN_PASSWORD_LENGTH database option to a greater value. The following statement enforces a minimum password length of 8 characters:

set option public.min_password_length = 8

Check the "Changing Passwords" section of this document to learn how to change a user's password, and don't forget to change the DBA's password!

6.3.2. Views, procedures, and triggers

Views are useful when it is appropriate to give a user access to just one portion of a table. The portion can be defined in terms of rows or in terms of columns. For example, you may wish to prevent a group of users from seeing the quantity column of the banana_supply table, or you may wish to limit a user to see information on a particular type of banana.

While views restrict access based on the data, procedures and triggers restrict access based on the actions a user can take. Procedures and triggers store SQL statements in a database for use by all applications. They execute under the table permissions of the associated table's owner, regardless of the permissions of the user who either executes the procedure or fires the trigger.

Procedures are invoked by a CALL statement, and can take values as well as return them. Unlike procedures, however, triggers are can neither take values nor return them, and are invoked by insertions, updates, or deletions in the table it is associated with. Permissions are not associated with triggers. They execute when the action defined to fire them is performed, regardless of the user.

For strict security, you can prevent all access to the tables, and grant permission to users to execute certain stored procedures that carry out specific tasks. This approach strictly defines the manner in which the database can be modified.

6.3.3. Encrypting client/server communications

Encrypting client/server communications prevents third parties from reading messages being sent between the client and the server. It can be enabled from either the server side or the client side. To enable encryption from the server, use the -e option at server startup. For example, use the following command to start up the database server to accept encrypted connections to mydb.db over TCP/IP:

dbsrv7 -e -x tcpip mydb.db

To enable encryption from a particular client, use the ENC keyword in the connection string. For example, to encrypt a connection over TCP/IP to mydb.db, your connection string would appear as follows:

"uid=mortimer;pwd=monkey;links=tcpip;eng=MyServer;dbf=mydb.db;enc=true"

For more information about client/server communications encryption, look for the -e command-line option under "The database server" in the Adaptive Server Anywhere Reference Manual, and for "Encryption connection parameter" under "Connection parameters" .