GIC Pilot Database

The data is stored in a database using the MySQL database engine. SQL is short for Structured Query Language, and MySQL is an open source implementation of SQL. The data can be accessed in many different ways: using MySQL comands directly; from Matlab; Java; Python; C; Perl; PHP; etc.

The SQL contains a number of statements and functions to manipulate the data in variuos ways. A good book on the subject is the one written by Paul DuBois.

Below follows a very brief decription of how the data in the GIC Pilot database can be accessed from the command prompt and from Matlab.

Accessing the data with mysql

To run mysql commands the client software must be installed. Note that a MySQL server need not to be installed if the database is on an remote machine, as in the examples below. If mysql is not installed (just try to type mysql at the commend prompt) it can be downloaded from the MySQL download page.

To connect to the GIC Pilot database type
mysql -h sunspot.lund.irf.se -u webpilot -pstorms
The parameters are:

• -h host
• -u user
• -p password (with no space)

A prompt like
mysql>
should now appear. MySQL commands can now be typed to access the data. Here follows a few examples:

• List the databases:
  show databases;
• Select a database:
  use gicpilot
• List the tables in the database:
  show tables;
• Show the metadata for a table;
  describe image;
• Select all columns from a table over a limited time period:
  select * from mag16s
  where dt>="1998-09-24 00:00:00"
  and dt<"1998-09-25 00:00:00"
  order by dt;

SQL queries can also be redirected directly into the the mysql command. To simplify matters a file named .my.cnf can be placed in your home directory that contains
[client]
host=sunspot.lund.irf.se
user=webpilot
password=storms
database=gicpilot
The read, write and execute permissions should be changed so that only the user may view the contents of the file. This can be accomplished by
chmod -rwx .my.cnf
chmod u+rw .my.cnf
Text files containing queries can then be redirected into the mysql command and the result can be redirected to another file. Putting the four lines from the select statement into the file mag.sql we may run the command
mysql < mag.sql > mag.txt

Accessing data from Matlab

To access the database in Matlab an interface is necessary. See the installation instructions.

The SQL queries are passed as an argument to the mysql Matlab function. However, first a connection to the database must be established. The following lines illustrates how data can be loaded into Matlab.

mysql('open','sunspot.lund.irf.se','webpilot','storms');
mysql('use gicpilot');
query='select dt,V from magswe64s where dt>="1998-09-24 00:00:00" and dt<"1998-09-25 00:00:00" order by dt';
[dn,v]=mysql(query);
plot(dn,v)
datetick

The function converts the MySQL DATETIME format into the Matlab DATENUMBER format. The number of outputs must equal the number of columns that are read from the database.

References

DuBois, Paul, MySQL - The definitive guide to using, programming, and administering MySQL 4 databases, 2:nd Ed., Sams Publishing, 2003.