Database Systems - Software PageMySQL Galax Note: there is also a homework info page available. MySQLOfficial Web SiteDownload Documentation Installing:The MySQL website provides good documentation on how to install MySQL on a personal machine for which you have administrative control. If you plan to install MySQL on your personal computer you can probably just stick to the instructions provided at the official MySQL website. The instructions bellow provide an example how to run MySQL in user mode on the department machines. All instructions were tested on slinky.cs.nyu.edu.The first step is to download the binary tar distribution for our platform. The simple command below can be used to fetch the tar file to the present directory:
wget -d "http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-standard-5.0.18-solaris9-sparc-64bit.tar.gz/from/http://mirror.trouble-free.net/mysql_mirror/"
Then unzip and untar the distribution: gunzip mysql-standard-5.0.18-solaris9-sparc-64bit.tar.gz tar xvf mysql-standard-5.0.18-solaris9-sparc-64bit.tarThis will create a directory named: mysql-standard-5.0.18-solaris9-sparc-64bit . Change into the
created directory: cd mysql-standard-5.0.18-solaris9-sparc-64bit (you may wish for easier reference to rename this directory). We now need to create the basic databases and tables for MySQL to manage users and privileges. To create the tables run the following command: ./scripts/mysql_install_dbTo verify that the tables were created list the directory: ls data/mysql and you should see a list of
files, which were just created.The script: ./bin/mysqld_safe is used to run MySQL server. We need to specify an alternative
socket file to use (as the default one might be in use).
To specify an alternative socket pass on the command line the options: "--socket=$HOME/mysql.sock --skip-networking"
(this option will also be used for starting the mysql client). Make sure to run the command from the extracted
root directory (mysql-standard-5.0.18-solaris9-sparc-64bit). The full command is:./bin/mysqld_safe --socket=$HOME/mysql.sock --skip-networking &After running this command the server should be up and running. Check the process list for mysqld
to verify that the process started properly.To start the mysql client run the command: ./bin/mysql --socket=$HOME/mysql.sock -u rootThe " -u root" specifies the to use the "root" user for authenticating to the db. The "root" user is created
by default by the mysql_install_db. If you wish you can create other users (and set passwords)
but for the class needs this should be sufficient. Once connected you will receive a "mysql>" prompt, providing
you an interface to execute commands.You can use the following scripts to run the server and client without specifying the socket option (the client still requires the " -u root" option): start_server.sh start_client.sh To stop the server run the command: ./bin/mysqladmin --socket=$HOME/mysql.sock -u root shutdownNote: make sure to shutdown the server before logging out of the machine. Creating a test table:To create a test table make sure the server is up and connect with the mysql client:./bin/mysql --socket=$HOME/mysql.sock -u root test
The above command connects with user "root" to the "test" database. The test database is created by the default installation. The database is empty. If you don't specify a database name on the command line you can later on run the following command at the mysql prompt to connect to the test database: mysql> use testTo see the tables in the database run: mysql> show tables;To create a table we use the sql command CREATE TABLE. Documentation for this command can be
found here. We will create a simple
table: names(first, last). The simplest form of this command is:mysql>CREATE TABLE names (first varchar(50), last varchar(50));Note: sql commands are case insensitive, but table names are case sensitive. To insert a value into the table we can use the INSERT command in the following form:mysql>INSERT INTO names VALUES("Joe", "Smith");
We can now do a SELECT and see what's in the table:mysql>SELECT * FROM names;This should output something of the form: +-------+-------+ | first | last | +-------+-------+ | Joe | Smith | +-------+-------+ 1 row in set (0.00 sec) GalaxOfficial Web SiteGalax is a XQuery processor developed by Mary Fernandez and Jerome Simeon, who happen to sit on the W3C XQuery committee. (This means that they are designers of the language.) It is one of the most complete XQuery implementations around. Yet it is simple to use. The instructions below will demonstrate how to install and run Galax on the department machines. The first step is to download the binary tar distribution from: http://www.galaxquery.org/distrib.html. For the department machines download the Solaris binary distribution. Then unzip and untar the distribution: gunzip Galax-0.5.0-Solaris.tar.gz tar xvf Galax-0.5.0-Solaris.tar This will create a directory Galax-0.5.0-Solaris. Now add the Galax bin directory to your path. If you are using bash do: export PATH=$PATH:~/Galax-0.5.Solaris/binOr with csh (I think this is the default on the department machines) do: set path = ($path ~/Galax-0.5.0-Solaris/bin)Try creating a simple file containing the following:
<two>{1+1}</two>
You can create this file using the command:
echo "<two>{1+1}</two>" > test.xq
Try running galax-run on this file:galax-run test.xqYou should get the following output: <two>2</two>For homework 2 you will be using the file: hispo.xml which is part of the Galax examples available at: ~/Galax-0.5.0-Solaris/examples/docs/hispo.xmlCopy this file to your working directory. An example: Suppose you want to select the names of the customers in the purchase order. You could use a file with the following xquery to do so:
<answer>
{
for $i in doc("hispo.xml")//name
return <name> {$i/text()} </name>
}
</answer>
Running the command: galax-run query.xq (assuming the file is named query.xq) would yield:
<answer><name>Alice Smith</name><name>Robert Smith</name></answer> Thanks to Alberto Lerner for helping out with this section. |