In the same way that Linux become popular starting to be developed without plans, in the same way Michael "Monty" Widenius start to develop its MySQL. MySQL is a complete and simple to use SQL (Strutured Query Language).

DataBase are similar MySQL, Adabas D, Oracle and others. The mode to handle the records and the tables make the difference.

MySQL is really the most excellent opensource product to handle tables connected to Websites.

We list here some examples:

• NASA switches from Oracle to MySQL
• Handy.de
• and others

This chapter covers MySQL Database. More than a chapter about MySQL commands, its explain how to create, maintain and

MySQL commands are included as well however is not the

Understanding MySQL Components

Under Linux, the MySQL Database is composed by the following RPM packages:

mysql-3.23.41-1 (Mysql Client)
mysqlclient9-3.23.22-6
mysql-server-3.23.41-1 (MySQL Administration or Server )
mysql-devel-3.23.41-1 (MySQL Libraries)

MySQL in the Webserver allows to create a Database to handle the e-commerce.

Under RedHat Linux 7.1, Mandrake 8.X and FTOSX 2002 Web Server, these packages are installed and configured. However you can download and install it running the commands:

rpm -i mysql-3.23.41-1
rpm -i mysql-server-3.23.41-1

Sometimes, you can also install some addition PERL modules to interact with your MySQL data. For example we advice:

rpm -i perl-DBI-1.06-1.i386.rpm
rpm -i perl-DBD-msql-mysql-1.2210-1.i386.rpm

(On RedHat 7.X and FTOSX 2001, these modules are installed in a complete installation).

However, an efficient e-commerce Web server may also be handled using PHP Programming Language.

After the installation there are two Databases present in MySQL by default.

[root@ftosx1 root]# mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+
[root@ftosx1 root]#

These tables represent the "standard" tables.

mysql is the table that regards the databases.

[root@ftosx1 MySQL]# more mysql.db
# MySQL dump 8.13
#
# Host: localhost    Database: mysql
#--------------------------------------------------------
# Server version        3.23.36

#
# Table structure for table 'columns_priv'
#

CREATE TABLE columns_priv (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Table_name char(64) binary NOT NULL default '',
  Column_name char(64) binary NOT NULL default '',
  Timestamp timestamp(14) NOT NULL,
  Column_priv set('Select','Insert','Update','References') NOT NULL default '',
  PRIMARY KEY  (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM COMMENT='Column privileges';

#
# Dumping data for table 'columns_priv'
#
 

#
# Table structure for table 'db'
#

CREATE TABLE db (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (Host,Db,User),
  KEY User (User)
) TYPE=MyISAM COMMENT='Database privileges';
#
# Dumping data for table 'db'
#

INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');
INSERT INTO db VALUES ('%','test\\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');
INSERT INTO db VALUES ('%','90987','90987','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

#
# Table structure for table 'func'
#

CREATE TABLE func (
  name char(64) binary NOT NULL default '',
  ret tinyint(1) NOT NULL default '0',
  dl char(128) NOT NULL default '',
  type enum('function','aggregate') NOT NULL default 'function',
  PRIMARY KEY  (name)
) TYPE=MyISAM COMMENT='User defined functions';

#
# Dumping data for table 'func'
#
 

#
# Table structure for table 'host'
#

CREATE TABLE host (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (Host,Db)
) TYPE=MyISAM COMMENT='Host privileges;  Merged with database privileges';
#
# Dumping data for table 'host'
#
 

#
# Table structure for table 'tables_priv'
#

CREATE TABLE tables_priv (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Table_name char(60) binary NOT NULL default '',
  Grantor char(77) NOT NULL default '',
  Timestamp timestamp(14) NOT NULL,
  Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') NOT NULL default '',  Column_priv set('Select','Insert','Update','References') NOT NULL default '',
  PRIMARY KEY  (Host,Db,User,Table_name),
  KEY Grantor (Grantor)
) TYPE=MyISAM COMMENT='Table privileges';

#
# Dumping data for table 'tables_priv'
#
#
# Table structure for table 'user'
#

CREATE TABLE user (
  Host char(60) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Password char(16) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Reload_priv enum('N','Y') NOT NULL default 'N',
  Shutdown_priv enum('N','Y') NOT NULL default 'N',
  Process_priv enum('N','Y') NOT NULL default 'N',
  File_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';

#
# Dumping data for table 'user'
#

INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('www.futuretg.com','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
INSERT INTO user VALUES ('www.futuretg.com','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
INSERT INTO user VALUES ('213.82.126.2','90987','05jjh6dd1ac2b484e89','N','N','N','N','N','N','N','N','N','N','N','N','N','N');

This database is fundamental to operate with MySQL.

For example may important to update the information about the users, for existent databases, for the Website or other info created.

The previous listing is a DataBase dump of the mysql Database, after some updates.

Adding users to MySQL

Now is fundamental to understand that MySQL works with users and passwords.

So, when internally (inside the company) or from the Web a remote customer try to access the Database must be accessed by some user with its password.

Therefore, there are three elements to fix before to start to use a DB.

• The Database with its tables and data.
• The Database name and
• The users and its password that will access the DB.

When you choose the Database name is important that all the three components are selected with suffcient clear intentions to avoid un-expected intrusions. A simple password, an 'obvious' user and and simple Database name may be overwritten or stolen by un-authorized personal.

We will work here with the following data:

• Database name: futu19
• user: notobvious
• password: ismyproblem

• mysqldump -p -u notobvious -h www.futuretg.com futu19 > 2002-03-23.db
• mysql -p -u notobvious -h www.futuretg.com futu19 < 2002-03-24.db

[root@www root]# mysql -user=root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 3.23.41

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

mysql> GRANT ALL PRIVILEGES ON *.* TO notobvious@localhost
mysql> IDENTIFIED BY 'ismyproblem'  WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO notobvious@"%"
mysql> IDENTIFIED BY 'ismyproblem'  WITH GRANT OPTION;
mysql>  GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
 
 

Creating your MySQL Database
 

To build a MySQL database is necessary to run the following commands:

[root@ftosx1 root]# mysqladmin create futu19
[root@ftosx1 root]# mysqlshow
+-----------+
| Databases |
+-----------+
| futu19    |
| mysql     |
| test      |
+-----------+

This create a Database.
 

Now, you can import your dumped DB.

[root@ftosx1 MMySQL]# mysql -p -u notobvious -h www.futuretg.com futu19  < 2001-10-16.db
Password

[root@ftosx1 MMySQL]# mysql -p -u notobvious -h www.futuretg.com futu19
Password

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.41

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

mysql>

We suppose here that all the commands are runned locally. However is possible to run commands on remote MySQL DB, present on remote Websites. This of course depends test your WebServer security.

mysql -p -u notobvious -h www.futuretg.com futu19 < 2002-03-24.db

Sometimes, may be more simple to destroy a table and rebuilt the entire DB, that modify the loaded rows.
 

Dropping Tables.

In the same mode that we create a table we can remove from the DB. The MySQL command to remove the tables (or destroy it) is drop table tablename.

For example:

    mysql> show tables
    -> ;
+------------------+
| Tables_in_futu19 |
+------------------+
| articles         |
| dollar           |
| items            |
| ordern           |
| orders           |
| regcodes         |
| users            |
+------------------+
7 rows in set (0.00 sec)

mysql> drop table articles
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table dollar;
Query OK, 0 rows affected (0.01 sec)

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

mysql> drop table ordern;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table orders;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql>
 

Of course we can use a single shell script to drop all the tables.

[root@www /root]# more drop_ecommerce
drop table articles;
drop table dollar;
drop table items;
drop table ordern;
drop table orders;
drop table regcodes;
drop table users;
[root@www /root]#

[root@www /root]#mysql futu19 < drop_ecommerce

Drop the tables inside the DB, is a necessary operation to rebuild it.

Only, after the drop (or just after the creation) we can built the DB.

[root@www /root]# mysql -h localhost futu19 < "Sat Dec  2 16:42:15 CET 2000.db"
 

In the same mode we drop tables, we can also drop entire Database.
 

[root@ftosx1 root]# mysqladmin drop futu19
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'futu19' database [y/N] y
Database "futu19" dropped
[root@ftosx1 root]#

Backup and rebuilt your MySQL DB

To dumb and existent MySQL Database you can run the command:

[root@www /root]# mysqldump -p -u notobvious -h www.futuretg.com futu19 > latest_futu19.db

You can update the DB, changes the prices or apply sed/awk/perl operations to create or modify fields

[root@www /root]# vi latest_futu19.db

Drop the DB

[root@www /root]# mysql -p -u notobvious -h www.futuretg.com futu19 < drop_ecommerce

... and ReBuilt

[root@www /root]# mysql -p -u notobvious -h www.futuretg.com futu19 < latest_futu19.db

This simple and innovative mode allow to handle MySQL DB as objects.

DataBase Design

The Design or the simple Database creation may be made in different modes. Or writing an ASCII file and loading it or step-by-step using mysql command from the mysql shell prompt.

Here we will create two simple Databases:

• One to handle RPM Packages and
• One to handle the students inside a Campus (Check the entries at: http://www.LinuxWebCampus.com/TheWebCampus/What_is_lwc_3.html )

Example 1. Write a MySQL Database to handle the RPM Packages.

Suppose that we want to write a DB, to handle RPM Packages.

[root@ftosx1 root]# rpm -qi gsl
Name        : gsl                          Relocations: (not relocateable)
Version     : 0.9.4                             Vendor: Future Technologies Inc
Release     : 0                             Build Date: Wed 17 Oct 2001 04:24:14 PM CEST
Install date: Wed 17 Oct 2001 04:27:23 PM CEST      Build Host: ftosx1.futuretg.com
Group       : System Environment/Libraries   Source RPM: gsl-0.9.4-0.src.rpm
Size        : 20123202                         License: GPL
Packager    : rpmparadaise@futuretg.com
Summary     : The GNU Scientific Library for numerical analysis.
Description :
The GNU Scientific Library (GSL) is a collection of routines for
numerical analysis, written in C.
[root@ftosx1 root]#

The Table that we will create must includes the following data:

• Name
• Description
• Version+Release
• Vendor
• Build Date
• Installation Date
• Group
• Size
• Source
• License
• Packager
• URL
• Description

[root@ftosx1 root]# mysqladmin create myfuturetg

[root@ftosx1 root]# more mydb.db
#
# Table structure for table 'packages'
#

CREATE TABLE packages (
  packname varchar(45) DEFAULT '' NOT NULL,
  version text NOT NULL,
  summary text NOT NULL,
  packgroup text NOT NULL,
  builddate text NOT NULL,
  installdate text NOT NULL,
  mysource text NOT NULL,
  buildhost text NOT NULL,
  vendor text NOT NULL,
  packager text NOT NULL,
  mylicense text NOT NULL,
  size int(10) DEFAULT '' NOT NULL,
  url text NOT NULL,
  description text NOT NULL,
  PRIMARY KEY (packname)
);

#
# Dumping data for table 'packages'
#

INSERT INTO packages VALUES ('rpm','4.0.3-1.04','The RPM package management system',
'System Environment/Base',
'Sun 14 Oct 2001 04:10:55 PM CEST',
'Sun 14 Oct 2001 04:48:12 PM CEST',
'ftosx1.futuretg.com',
'rpm-4.0.3-1.04.src.rpm',
'Future Technologies Inc',
'',
'GPL',
'5277364',
'http://www.redhat.com',
'The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc.'
);
[root@ftosx1 root]#

You can create your table and insert your DB in a very simple mode:

[root@ftosx1 root]# mysql myfuturetg < mydb.db

To check our entry we can run some MySQL commands from the MySQL shell.

[root@ftosx1 root]# mysql myfuturetg
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 3.23.41

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

mysql>

The prompt "mysql>" is called here the MySQL shell.

From this shell we can run any command:

mysql> select * from packages;
+----------+------------+-----------------------------------+-------------------------+----------------------------------+----------------------------------+---------------------+------------------------+-------------------------+----------+-----------+---------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| packname | version    | summary                           | packgroup               | builddate                        | installdate                      | mysource            | buildhost              | vendor                  | packager | mylicense | size    | url                   | description

                                                                                                                 |
+----------+------------+-----------------------------------+-------------------------+----------------------------------+----------------------------------+---------------------+------------------------+-------------------------+----------+-----------+---------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rpm      | 4.0.3-1.04 | The RPM package management system | System Environment/Base | Sun 14 Oct 2001 04:10:55 PM CEST | Sun 14 Oct 2001 04:48:12 PM CEST | ftosx1.futuretg.com | rpm-4.0.3-1.04.src.rpm | Future Technologies Inc |          | GPL       | 5277364 | http://www.redhat.com | The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc. |
+----------+------------+-----------------------------------+-------------------------+----------------------------------+----------------------------------+---------------------+------------------------+-------------------------+----------+-----------+---------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Is fundamental that any MySQL command ends with the semicolon: ';'.

Like,

INSERT INTO packages VALUES ('rpm','4.0.3-1.04','The RPM package management system',
'System Environment/Base',
'Sun 14 Oct 2001 04:10:55 PM CEST',
'Sun 14 Oct 2001 04:48:12 PM CEST',
'ftosx1.futuretg.com',
'rpm-4.0.3-1.04.src.rpm',
'Future Technologies Inc',
'',
'GPL',
'5277364',
'http://www.redhat.com',
'The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc.'
);

Note that also in the file each line ends with the semicolon.

We can also can print a partial selection:
 

mysql> select packname from packages;
+----------+
| packname |
+----------+
| rpm      |
+----------+
1 row in set (0.00 sec)

mysql>
 

Now, we will create a shell script to insert all the RPM packages into the DB. I generally use numbers to create shells, in the sequence of the necessary steps to reach the target.

For example, we can write shell scripts 1, 2 and 3, and then rename it. The numbers "2" and "3" may be used also as output files.

Is fundamental to test each step.

Therefore, we now will create a script that generate the same latest entry (only for the rpm package).

So, we create a simple script to generate automatically a package; the same package that we use before when create the DB for the first time, the rpm package.

[root@ftosx1 root]# more 1
rpm -q rpm --queryformat 'INSERT INTO packages VALUES (~~~%{name}| %{version}-%-{release}| %{summary}|  %{group}| %{buildtime:date}| %{installtime:date}| %{sourcerpm}| %{buildhost} | %{vendor} | %{packager}| %{license}|  %{size}|  %{URL}| %{description}~~~);\n' > Packages.txt
[root@ftosx1 root]#

Any script must be an executable file:

[root@ftosx1 root]# chmod +x 1

Now, run the program:

[root@ftosx1 root]# ./1

Now we check the result:

[root@ftosx1 root]# more Packages.txt
INSERT INTO packages VALUES (~~~rpm| 4.0.3-1.04| The RPM package management system.|  System Environment/Base| Sun 14 Oct 2001 04:10:55 PM CEST| Sun 14 Oct 2001 04:48:12 PM CEST| rpm-4.0.3-1.04.src.rpm| ftosx1.futuretg.com | Future Technologies Inc | (none)| GPL|  5277364|  (none)| The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc.~~~);
[root@ftosx1 root]#

Now, we create a new shell that contains sed commands to generate the RPM Database.

We create some tricks here:

• The separator is "|", because rpm does not accept the '.
• Then we use "~~~" as the first and last separator.
• Before to apply the change to create the valid INSERT MySQL statement we remove any character "  '  ", that may be used inside the description and may cause problems in the MySQL insertion.

[root@ftosx1 root]# more Built_MySQL_RPM_DB
sed "s/'//g" Packages.txt > 2
sed "s/~~~/'/g" 2 > 3
sed "s/|/','/g" 3 > RPM_Data.db
[root@ftosx1 root]#

[root@ftosx1 root]# chmod +x Built_MySQL_RPM_DB

Now, we generate a MySQL row running this script.

[root@ftosx1 root]# ./Built_MySQL_RPM_DB

The result is as follows:

[root@ftosx1 root]# more RPM_Data.db
INSERT INTO packages VALUES ('rpm',' 4.0.3-1.04',' The RPM package management system.','  System Environment/Base',' Sun 14 Oct 2001 04:10:55 PM CEST',' Sun 14 Oct 2001 04:48:12 PM CEST',' rpm-4.0.3-1.04.src.rpm',' ftosx1.futuretg.com ',' Future Technologies Inc ',' (none)',' GPL','  5277364','  (none)',' The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc.');
[root@ftosx1 root]#

Now, we drop the DB and create it running a single command.

[root@ftosx1 root]# more mydrop
drop table packages;
[root@ftosx1 root]#

[root@ftosx1 root]# mysql myfuturetg < mydrop

And the re-insert the new row we create in automatic mode.
 

[root@ftosx1 root]# more mydb2.db
#
# Table structure for table 'packages'
#

CREATE TABLE packages (
  packname varchar(45) DEFAULT '' NOT NULL,
  version text NOT NULL,
  summary text NOT NULL,
  packgroup text NOT NULL,
  builddate text NOT NULL,
  installdate text NOT NULL,
  mysource text NOT NULL,
  buildhost text NOT NULL,
  vendor text NOT NULL,
  packager text NOT NULL,
  mylicense text NOT NULL,
  size int(10) DEFAULT '' NOT NULL,
  url text NOT NULL,
  description text NOT NULL,
  PRIMARY KEY (packname)
);

INSERT INTO packages VALUES ('rpm',' 4.0.3-1.04',' The RPM package management system.','  System Environment/Base',' Sun 14 Oct 2001 04:10:55 PM CEST',' Sun 14 Oct 2001 04:48:12 PM CEST',' rpm-4.0.3-1.04.src.rpm',' ftosx1.futuretg.com ',' Future Technologies Inc ',' (none)',' GPL','  5277364','  (none)',' The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc.');
[root@ftosx1 root]#

... and re-insert the row.

[root@ftosx1 root]# mysql myfuturetg < mydb2.db

The DB built and the row insertion was sucessufull!

We can make some test!

[root@ftosx1 root]# mysql myfuturetg
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 3.23.41

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

mysql> select description from packages;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| description
                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  The RPM Package Manager (RPM) is a powerful command line driven
package management system capable of installing, uninstalling,
verifying, querying, and updating software packages. Each software
package consists of an archive of files along with information about
the package like its version, a description, etc. |
+--------------------------------------------------------------------------------------------------------------------------
| zlib                            |
| zlib-devel                      |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Now, we update the shells to built and insert the entire RPM DB.

[root@ftosx1 root]# more CreatePackages
rpm -qa --queryformat 'INSERT INTO packages VALUES (~~~%{name}| %{version}-%-{release}| %{summary}|  %{group}| %{buildtime:date}| %{installtime:date}| %{sourcerpm}| %{buildhost} | %{vendor} | %{packager}| %{license}|  %{size}|  %{URL}| %{description}~~~);\n' > Packages.txt
[root@ftosx1 root]#

[root@ftosx1 root]# more Built_MySQL_RPM_DB
./CreatePackages
sed "s/'//g" Packages.txt > 2
sed "s/~~~/'/g" 2 > 3
sed "s/|/','/g" 3 > RPM_Data.db
[root@ftosx1 root]#

May be convenient to rebuild the RPM DB.

[root@ftosx1 root]# rpm --rebuilddb

Now, we create all the MySQL RPM DB rows.

[root@ftosx1 root]# ./Built_MySQL_RPM_DB

We need to correct the mydb (removing the previous test row) and include it.
 

[root@ftosx1 root]# cat RPM_Data.db >> mydb.db
 

[root@ftosx1 root]# mysql myfuturetg < myrpmdb.db

Sucess!

Now, we will test how many rpm packages are installed.

mysql> select packname from packages;

...

| zebra                           |
| zip                             |
| zlib                            |
| zlib-devel                      |
| zsh                             |
+---------------------------------+
1128 rows in set (0.06 sec)

mysql>

Now, we can run any SQL command on our DB.

Is also possible to generate from scratch the Database, the table as well as insert all the RPM Packages as row.

[root@ftosx1 root]# more RPM2MySQL
#
# RPM2MySQL:    This program create a MySQL Database inserting all the RPM Packages.
# Author:       Dr. Giovanni A. Orlando
# Date:         Sat, 2001-10-20.
#

# Any RPM row is mapped to a file to create a
rpm -qa --queryformat 'INSERT INTO packages VALUES (~~~%{name}| %{version}-%-{release}| %{summary}|  %{group}| %{buildtime:date}| %{installtime:date}| %{sourcerpm}| %{buildhost} | %{vendor} | %{packager}| %{license}|  %{size}|  %{URL}| %{description}~~~);\n' > Packages.txt

sed "s/'//g" Packages.txt > 2
sed "s/~~~/'/g" 2 > 3
sed "s/|/','/g" 3 > RPM_Data.db

echo "\
#
# Table structure for table 'packages'
#

CREATE TABLE packages (
  packname varchar(45) DEFAULT '' NOT NULL,
  version text NOT NULL,
  summary text NOT NULL,
  packgroup text NOT NULL,
  builddate text NOT NULL,
  installdate text NOT NULL,
  mysource text NOT NULL,
  buildhost text NOT NULL,
  vendor text NOT NULL,
  packager text NOT NULL,
  mylicense text NOT NULL,
  size int(10) DEFAULT '' NOT NULL,
  url text NOT NULL,
  description text NOT NULL,
  PRIMARY KEY (packname)
);" > myrpmdb.db

cat RPM_Data.db >> myrpmdb.db

# Now, we remove the temporary files created in the development
rm RPM_Data.db 2 3

# Now we create the MySQL DB and insert them the table and the rows!

mysqladmin create rpmdb

mysql rpmdb < myrpmdb.db

echo "All your RPM packages now belong to the MySQL DB: rpmdb and the table is packages"
[root@ftosx1 root]#

We will this Database (rpmdb, instead myfuturetg) from now.

[root@ftosx1 root]# mysql rpmdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.41

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

mysql>
 

mysql> show columns from packages;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| packname    | varchar(45) |      | PRI |         |       |
| version     | text        |      |     |         |       |
| summary     | text        |      |     |         |       |
| packgroup   | text        |      |     |         |       |
| builddate   | text        |      |     |         |       |
| installdate | text        |      |     |         |       |
| mysource    | text        |      |     |         |       |
| buildhost   | text        |      |     |         |       |
| vendor      | text        |      |     |         |       |
| packager    | text        |      |     |         |       |
| mylicense   | text        |      |     |         |       |
| size        | int(10)     |      |     | 0       |       |
| url         | text        |      |     |         |       |
| description | text        |      |     |         |       |
+-------------+-------------+------+-----+---------+-------+
14 rows in set (0.00 sec)
 

mysql> select vendor from packages where vendor = ' Future Technologies Inc';
+---------------------------+
| vendor                    |
+---------------------------+
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
|  Future Technologies Inc  |
+---------------------------+
35 rows in set (0.01 sec)

mysql>

Note the "space" in the query.

Now, we will answer some typical questions about RPM packages, using MySQL queries!

For example,

• What is the most big package ?

mysql> select packname, size from packages order by size asc;

...
| kernel-source                   | 99891089 |
+---------------------------------+----------+
1128 rows in set (0.06 sec)
 

The kernel source is the most big package.

• What is the latest package installed ?

 

 
 
 

mysql> select packname, installdate from packages order by installdate asc;
...
| ftliquid                        |  Wed 17 Oct 2001 07:47:06 PM CEST |
+---------------------------------+-----------------------------------+
1128 rows in set (0.09 sec)

ftliquid seems to be the latest package installed in the system. Why this is not a correct answer ?
 

• What are the groups ?

 

 
 
 

mysql> select distinct packgroup from packages order by packgroup asc;
+-------------------------------------+
| packgroup                           |
+-------------------------------------+
|   Amusements/Games                  |
|   Amusements/Graphics               |
|   Applications/Archiving            |
|   Applications/Communications       |
|   Applications/Databases            |
|   Applications/Editors              |
|   Applications/Emulators            |
|   Applications/Engineering          |
|   Applications/File                 |
|   Applications/Internet             |
|   Applications/Multimedia           |
|   Applications/Productivity         |
|   Applications/Publishing           |
|   Applications/System               |
|   Applications/Text                 |
|   Base                              |
|   Development                       |
|   Development/Debuggers             |
|   Development/Languages             |
|   Development/Libraries             |
|   Development/System                |
|   Development/Tools                 |
|   Documentation                     |
|   System Environment/Base           |
|   System Environment/Daemons        |
|   System Environment/Kernel         |
|   System Environment/Libraries      |
|   System Environment/Shells         |
|   User Interface/Desktops           |
|   User Interface/X                  |
|   User Interface/X Hardware Support |
|   X11/Applications/Networking       |
|   XWindow                           |
+-------------------------------------+
33 rows in set (0.10 sec)

mysql>

A similar result is possible running the following command:

mysql> select packgroup from packages group by packgroup;
+-------------------------------------+
| packgroup                           |
+-------------------------------------+
|   Amusements/Games                  |
|   Amusements/Graphics               |
|   Applications/Archiving            |
|   Applications/Communications       |
|   Applications/Databases            |
|   Applications/Editors              |
|   Applications/Emulators            |
|   Applications/Engineering          |
|   Applications/File                 |
|   Applications/Internet             |
|   Applications/Multimedia           |
|   Applications/Productivity         |
|   Applications/Publishing           |
|   Applications/System               |
|   Applications/Text                 |
|   Development/Debuggers             |
|   Development/Languages             |
|   Development/Libraries             |
|   Development/System                |
|   Development/Tools                 |
|   Documentation                     |
|   System Environment/Base           |
|   System Environment/Daemons        |
|   System Environment/Kernel         |
|   System Environment/Libraries      |
|   System Environment/Shells         |
|   User Interface/Desktops           |
|   User Interface/X                  |
|   User Interface/X Hardware Support |
+-------------------------------------+
29 rows in set (0.08 sec)

mysql>

We can also count the packages on each group.

mysql> select packgroup, COUNT(*) from packages group by packgroup order by packname;
+-------------------------------------+----------+
| packgroup                           | COUNT(*) |
+-------------------------------------+----------+
|   System Environment/Daemons        |       83 |
|   Development/Tools                 |       46 |
|   Applications/File                 |       10 |
|   Applications/System               |      104 |
|   Applications/Editors              |       23 |
|   Applications/Internet             |       82 |
|   System Environment/Libraries      |      145 |
|   Applications/Multimedia           |       44 |
|   Applications/Communications       |       19 |
|   Applications/Productivity         |        9 |
|   Amusements/Graphics               |        8 |
|   Development/System                |        5 |
|   System Environment/Kernel         |        6 |
|   Applications/Text                 |       30 |
|   Development/Debuggers             |        9 |
|   System Environment/Base           |       78 |
|   User Interface/Desktops           |       34 |
|   Applications/Engineering          |        5 |
|   Development/Languages             |       70 |
|   Applications/Databases            |       17 |
|   Documentation                     |       15 |
|   Development/Libraries             |      164 |
|   System Environment/Shells         |       10 |
|   Applications/Publishing           |       31 |
|   Applications/Archiving            |       13 |
|   User Interface/X                  |       27 |
|   Applications/Emulators            |        1 |
|   Amusements/Games                  |       12 |
|   User Interface/X Hardware Support |        5 |
+-------------------------------------+----------+
29 rows in set (0.19 sec)

mysql>
 
 

• What package belong to the group Base ?

mysql> select  packname from packages where packgroup = '  Base';
+----------+
| packname |
+----------+
| netcfg   |
+----------+
1 row in set (0.02 sec)

mysql>

• What package belong to the XWindow group?

mysql> select  packname from packages where packgroup = '  XWindow';
+-----------+
| packname  |
+-----------+
| openmotif |
+-----------+
1 row in set (0.01 sec)

mysql>

• What packages have a NULL (none) packager ?

 

 
 
 

mysql> select packname,packager from packages where packager=' (none)';

+---------------------+----------+
| packname            | packager |
+---------------------+----------+
| rpm-python          |  (none)  |
| kdegraphics-devel   |  (none)  |
| kdelibs             |  (none)  |
| qt3-styles          |  (none)  |
| netcfg              |  (none)  |
| kdemultimedia       |  (none)  |
| kdelibs-sound       |  (none)  |
| arts                |  (none)  |
| qt-static           |  (none)  |
| qt-designer         |  (none)  |
| qt-Xt               |  (none)  |
| rpm                 |  (none)  |
| kdegraphics         |  (none)  |
| rpm-perl            |  (none)  |
| kdebase-devel       |  (none)  |
| qt3-devel           |  (none)  |
| kdelibs-devel       |  (none)  |
| qt-devel            |  (none)  |
| anaconda            |  (none)  |
| qt                  |  (none)  |
| kdeadmin            |  (none)  |
| qt3-Xt              |  (none)  |
| qt3-MySQL           |  (none)  |
| ftliquid            |  (none)  |
| popt                |  (none)  |
| anaconda-runtime    |  (none)  |
| rpm-devel           |  (none)  |
| qt3-PostgreSQL      |  (none)  |
| kdemultimedia-devel |  (none)  |
| qt3-ODBC            |  (none)  |
| xsitecopy           |  (none)  |
| rpm-build           |  (none)  |
| kdebase             |  (none)  |
| ftosx-release       |  (none)  |
| qt3                 |  (none)  |
| kdelibs-sound-devel |  (none)  |
| qt3-designer        |  (none)  |
+---------------------+----------+
37 rows in set (0.02 sec)

mysql>
 

• What is the most small package ?

mysql>  select packname, size from packages order by size desc;
| basesystem                      |        0 |
| dev                             |        0 |
| filesystem                      |        0 |
+---------------------------------+----------+

All these sources are included in FTContribs directory.

All the previous MySQL queries haves an equivalent using Linux and rpm commands.

For additional info about the RPM Package you can consult our Chapter 12. Built your RPM package
 

Example 2. Write a MySQL Database to handle a Campus.

Now we will create a Database to handle a Campus, specifically the LinuxWebCampus that represent the

For a Campus we will use the following DB.
 

[root@ftosx1 root]#
[root@ftosx1 root]# more Campusdb.db
#
# Table structure for table 'students'
#

CREATE TABLE students (
  iduser int(11) NOT NULL auto_increment,
  instant int(11) NOT NULL default '0',
  email varchar(60) NOT NULL default '',
  name varchar(50) NOT NULL default '',
  sex tinyint(4) NOT NULL default '',
  age tinyint(4) NOT NULL default '',
  studentdate datetime NOT NULL default '',
  company varchar(50) NOT NULL default '',
  address varchar(100) NOT NULL default '',
  city varchar(30) NOT NULL default '',
  state varchar(15) NOT NULL default '',
  zip varchar(10) NOT NULL default '',
  country varchar(30) NOT NULL default '',
  phone varchar(20) NOT NULL default '',
  url varchar(50) NOT NULL default '',
  s_name varchar(50) NOT NULL default '',
  s_company varchar(50) NOT NULL default '',
  s_address varchar(100) NOT NULL default '',
  s_city varchar(30) NOT NULL default '',
  s_state varchar(15) NOT NULL default '',
  s_zip varchar(10) NOT NULL default '',
  s_country varchar(30) NOT NULL default '',
  pwd varchar(30) NOT NULL default '',
  payment tinyint(4) NOT NULL default '0',
  shipping tinyint(4) NOT NULL default '0',
  fax varchar(20) NOT NULL default '',
  mlist tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (iduser),
  UNIQUE KEY email (email)
) TYPE=MyISAM;

#
# Dumping data for table 'users'
#

INSERT INTO students VALUES (1,961636326,'mary@futuretg.com','Mary Smith','F', '38', '2001-10-21 08:40', 'Future Technologies.','1620 26th Street','North RYDE','NSW','2166','Australia','321-4591-5236','','','','','','','','U.S.A.','uddcVcysOAboo',2,1,'',0);
INSERT INTO students VALUES (2,961636826,'john@pepsicola.com','John Smith','M', '38', '2001-10-21 08:50', 'Pepsi Cola ltd.','7213 Talavera Rd','North RYDE','NSW','2166','Australia','321-4591-5236','','','','','','','','U.S.A.','uddcVcysOAboo',2,1,'',0);
 

[root@ftosx1 root]#

Note here the column type tinyint for a small integer and the datetime type for dates.

[root@ftosx1 root]# mysql myfuturetg < Campusdb.db
[root@ftosx1 root]# mysql myfuturetg
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.41

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

mysql> select name from students;
+------------+
| name       |
+------------+
| Mary Smith |
| John Smith |
+------------+
2 rows in set (0.00 sec)

mysql>
 

Here we includes the datatime type and resume the date and the time.

[root@ftosx1 root]# mysql myfuturetg
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.41

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

mysql> select studentdate from students;
+---------------------+
| studentdate         |
+---------------------+
| 2001-10-21 08:40:00 |
| 2001-10-21 08:50:00 |
+---------------------+
2 rows in set (0.15 sec)

mysql>

At last we also introduce two important MySQL command: update.

Our previous RPM DB was used only for queries to know and check the RPM Packages. Here we will update or modify data, because students may change address or phone number.

Suppose the user mart which email is mary@futuretg.com, wants to changes its phone number. Then we will run the command:

mysql> update students set phone='310-255-8890' where email='mary@futuretg.com';
Query OK, 1 row affected (0.27 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now, we check!

mysql> select iduser,name,phone from students;
+--------+------------+---------------+
| iduser | name       | phone         |
+--------+------------+---------------+
|      1 | Mary Smith | 310-255-8890  |
|      2 | John Smith | 321-4591-5236 |
+--------+------------+---------------+
2 rows in set (0.77 sec)

mysql>

... and that's all about MySQL. The 98% of the MySQL commands are covered and introduce in this chapter. The daily experience and needs get the rest 2%.
 

Conclusions

The MySQL is a clear and effective mode to use a high-performance Database inside your company, for personal or corporate use. However is fundamental to apply and use the normal user/password approach. This in connection with a WebServer open also an opportunity to Hackers that may get un-authorized information.

If your company handle sensitive data like credit cards or other is also convenient to backup these data inside the company don't offering access to the Web.

The MySQL is usefull but need the necessary protection to avoid strange surprises.
 

Exercises

1. Visit mysql.com
2. Browse the MySQL 4.0 Manual

1. What Database are present in a MySQL DataBase, after the installation ?
2. What is the command to list the Databases  ?
3. What is the command to create a DataBase ?
4. Is necessary to have Apache installed to run MySQL ?
5. How the WebMaster access the MySQL data ?
6. What is the command to destroy or remove a MySQL Database ?
7. What is the command to display the fields inside a MySQL Database ?
8. What is the command to destroy a table inside a DB ?
9. Suppose that there are similar repetitive data inside a DB, like: 1,1,1,3,4,1,3,2,4,5,2,1,2,3,4 ... . What is the MySQL command to display the data without doubles?
10. Why is wrong the query about the latest installed package ? (Because its order by week days: Mon, .. Sat, Wed, nor the real date )
11. What is the MySQL Data type for date and time ?

Read the answers to the exercises.

Check the Interactive Exam Cram WebMaster: 

Internet Resources for this Chapter.

• http://www.mysql.com
• MySQL 4.0 Manual