Spring Semester 2004

So far we have investigated CGI and we realized that keeping state is somewhat tricky. We have been exploring strategies of keeping state on the client side. Sending data to the client presents several security problems, mostly because data sent over to the client becomes available and can be changed. So investigating strategies for keeping state on the server side is worthwhile. We will be using mySQL for all our server side state maintaining techniques.

The software mentioned, mySQL, is a relational database management system. (You can read more about it here). Being relational means that data is stored in it in tables. Each table contains a number of columns. Columns have names, data types, and store values (of that particular data type). We won't do much modeling at first. We will start by using one table with two columns. We just want to be clear on how things work. Later we wil revisit this topic and address issues of modeling. For now we work with only one table. Details below.

The software is already installed on burrowww. You only need to set your environment to make it accessible. Should you want to use mySQL on your own computer you'd have to install it. (Same goes for Perl, and Apache. In this class we install Apache, but we do not install Perl which, like mySQL is installed for us already. Many other things are already installed, as you will see).

Make sure that your PATH contains the mySQL location.

burrowww.cs.indiana.edu% echo $PATH
/u/dgerman/bin:/home/user1/mysql/bin:/usr/local/gnu/bin:/usr/bin:/usr/local/bin:/usr/sbin:/usr/ucb:/usr/bin/X11:/usr/openwin/bin:/usr/dt/bin:/opt/SUNWspro/bin:/usr/ccs/bin:/usr/local/gnu/bin

burrowww.cs.indiana.edu% grep -i mysql ~/.cshrc
        /home/user1/mysql/bin \

burrowww.cs.indiana.edu% mysql -ua348 -pa348AG
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78 to server version: 3.23.27-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> exit
Bye

burrowww.cs.indiana.edu% mysql -ua348 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 79 to server version: 3.23.27-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> show databases;
+----------+
| Database |
+----------+
| a348     |
| bassoon  |
| classes  |
| mysql    |
| proj     |
| test     |
+----------+
6 rows in set (0.00 sec)

mysql> create database dgerman;
ERROR 1044: Access denied for user: 'a348@localhost' to database 'dgerman'
mysql> select database();
+------------+
| database() |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

mysql> use a348
Database changed

mysql> select database;
ERROR 1064: You have an error in your SQL syntax near '' at line 1

Likely it won't be the last, so always remain patient, and confident.

mysql> select database();
+------------+
| database() |
+------------+
| a348       |
+------------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.01 sec)

Now let's create a table. Note the naming conventions.

mysql> create table dgerman_student (
    ->   name        varchar(20)      not null, 
    ->   gender      enum ('f', 'm')  not null,
    ->   student_id  int unsigned     not null  auto_increment primary key
    -> );
Query OK, 0 rows affected (0.00 sec)

In my case that means dgerman_. Then I wrote the name of the table (student).

(This is just a convention, but please follow it.)

So we have created a table with three columns:

• name (string of characters of up to 20 chars)
• gender (can only contain 'f' or 'm', this is an enumerated type)
• student ID (unsigned int, also acting as the key)

mysql> show tables like '%dgerman%';
+-----------------+
| Tables_in_a348  |
+-----------------+
| dgerman_student |
+-----------------+
1 row in set (0.00 sec)

You can see that your table has been created and you can ask for info about it, too.

mysql> describe dgerman_student;
+------------+------------------+------+-----+---------+----------------+---------------------------------+
| Field      | Type             | Null | Key | Default | Extra          | Privileges                      |
+------------+------------------+------+-----+---------+----------------+---------------------------------+
| name       | varchar(20)      |      |     |         |                | select,insert,update,references |
| gender     | enum('f','m')    |      |     | f       |                | select,insert,update,references |
| student_id | int(10) unsigned |      | PRI | NULL    | auto_increment | select,insert,update,references |
+------------+------------------+------+-----+---------+----------------+---------------------------------+
3 rows in set (0.00 sec)

When we review it, nothing unusual shows up.

The structure is (emtpy and) ready to go.

mysql> select * from dgerman_student;
Empty set (0.00 sec)

mysql> insert into dgerman_student values 
    ->   ('Abby', 'f', NULL), 
    ->   ('Kyle', 'm', NULL); 
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dgerman_student;
+------+--------+------------+
| name | gender | student_id |
+------+--------+------------+
| Abby | f      |          1 |
| Kyle | m      |          2 |
+------+--------+------------+
2 rows in set (0.01 sec)

How do we delete data?

mysql> delete from dgerman_student;
Query OK, 0 rows affected (0.02 sec)

Only do that if you want to clean everything.

mysql> select * from dgerman_student;
Empty set (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_a348  |
+-----------------+
| dgerman_student |
+-----------------+
1 row in set (0.00 sec)

mysql> drop table dgerman_student;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> exit
Bye
burrowww.cs.indiana.edu% exit
burrowww.cs.indiana.edu% 

Remember that you go in as one and the same user so please

• prefix your tables with your username and
• do not delete someone else's tables.