CSE 134A Lecture Notes, October 7, 2002

CSE134A LECTURE NOTES

October 7, 2002

ANNOUNCEMENTS

Today's handout is by Jakob Nielsen on the top ten web page usability mistakes, from 1996.

Mistake zero, not mentioned by Nielsen but the most important and common in amateur pages: unreadable text, e.g. dark text on a dark background, or light on light. The Discus software we're using for 134A has this mistake: the button "Post this message" has black text on a dark blue background under Netscape 4.7 on my Linux box. (It looks fine under Mozilla and IE on Windows so this is another example of how testing necessary on multiple browsers and platforms.)

USER INPUT VALIDATION AND SECURITY

Any verification done on the client with Javascript must be repeated on the server with PHP, because a client-side attacker can modify or bypass the Javascript. For more info on this topic see http://wolves.dreamhost.com/web/xhtml/form09.html.

TABLES AND RECORDS

Tables are divided into rows and columns. A row is also called a record or tuple. A column is also called a field or attribute. A table is also called a relation.

The db server takes care of maintaining the tables on disk, of allowing multiple db clients to read and write to them without data corruption, and of keeping copies cached in memory to increase read/write speed.

All database operations are specified using a language of commands called SQL. For example:

create table temp (
   k        integer unsigned not null,
   s        double not null,
   d        datetime not null,
   quantile double not null )

The syntax of the SQL language is inspired by Cobol, a 1960s business-oriented language. Upper/lower case is not significant.

FIELD TYPES

Unlike in PHP, in MySQL and in all other relational databases, columns have fixed, predeclared types. The most important types are:

INTEGER: signed 32 bits
DOUBLE
CHAR(n): a string of fixed length at most n characters, where n <= 255
VARCHAR(n): a string of variable length at most n characters, where n <= 255
TEXT, BLOB: a string of length at most 2¹⁶-1 bytes
MEDIUMTEXT, MEDIUMBLOB: a string of length at most 2²⁴-1 bytes
LONGTEXT, LONGBLOB: a string of length at most 2³²-1 bytes
DATE: format 'YYYY-MM-DD' with 1000 <= YYYY <= 9999
TIMESTAMP: format 'YYYYMMDDHHMMSS' range 1970 to 2038

Modern database systems can handle very large fields, e.g. photographs. BLOB stands for "binary large object." BLOB values are case-sensitive while TEXT values are not (a leftover from the days before eight-bit ascii character codes).

The strange range for TIMESTAMP comes from a Unix-specific representation.

Unlike in most programming languages, the basic types also have modifiers available. These include:

UNSIGNED
NOT NULL
DEFAULT value

Note that some keywords contain spaces, e.g. NOT NULL.

QUERIES IN SQL

SQL is an acronym for "structured query language." A query allows data to be retrieved based on its content, not its storage location. For example: select author, date from messages where date > '2000-11-11'

The answer from a query is called a "result set." Conceptually, a result set is the same as a new table, except that result sets are not stored persistently in the database.

Result sets can be ordered:

        select author, date
            from messages
            where date > '2000-11-11'
            order by date

Each line that adds detail to what should be selected is called a "clause." The example above has from, where, and order by clauses. By default order is increasing.

UPDATING TABLES

So far we seen how to do queries but not inserts, changes, or deletes.

delete from messages where date < '2000-11-11';
update messages set author = 'John Smith' where author = 'Smith';
insert messages (author, date) values('Brown',curdate());

Syntax note: curdate() is a builtin SQL function, not a PHP function. Semantics note: the where clause is optional in delete and update commands. If you leave it out, every record will be affected. This is almost never what you want.

Usually your PHP script will build up an SQL command by concatenating various substrings, and then send the command to be executed by the MySQL server. For example:

$cmd = "insert messages (author, date) values('Brown'," . today() . ")";
$result = mysql_query($cmd, $link);

In the PHP code above, today() is a PHP function.

What happens when we create a new tuple and only provide values for some of its fields? Then the database server fills in default values for the remaining fields. The most common default value is NULL.

What happens when an insert command fails to specify a value for a field that is supposed to be NOT NULL? I couldn't find the answer in our MySQL book!

UNIQUE IDENTIFIERS

Often it is convenient to have an id number for each record in a table. You can also specify primary key as a modifier in a field declaration. For example:

create table messages (
    ...
    id    integer unsigned not null auto_increment primary key
    ... )

Now every record should be inserted with a NULL value for the field named id. The server will then automatically assign a value to this field that is one larger than the largest value ever assigned previously to a record for this table.

SUMMARIES IN "SELECT" QUERIES

Here is a sequence of select queries illustrating how to generate reports about the information in a database.

select count(*) from messages;
select count(*) from messages where author = 'Smith';
select max(date) from messages where author = 'Smith';
select distinct author from messages order by author;
select count(distinct author) from messages;

Note that distinct is a keyword, written without parentheses, but dayofweek() is a builtin function. Other builtin group-based functions include min, max, sum, and avg.