12.2. Crear una base de datos simple con MySQL

Para que el Blog de ejemplo funcione necesitamos tener una base de datos. En este libro usamos el gestor de base de datos MySQL que es muy simple de instalar y administrar.

Para las instrucciones específicas por sistema operativo sobre como instalar el cliente y el servidor MySQL podemos revisar el Apéndice A. Configuring Web Development Environment

Una vez instalado MySQL escribimos el siguiente comando en consola de comandos para iniciar sesión en el cliente de consola de MySQL:

mysql -u root -p

Luego preguntará por la contraseña, escribimos la contraseña del usuario root (la contraseña del usuario root es la que especificamos durante la instalación del servidor MySQL). Si el inicio es exitoso deberíamos ver el siguiente mensaje de bienvenida:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.13-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Ahora podemos escribir los comandos de cliente de MySQL (como show databases, show tables, etc.) o sentencias SQL (como SELECT or INSERT) en la consola de MySQL y ver sus resultados.

Si queremos salir de la consola de MySQL escribimos quit y presionamos Enter.

12.2.1. Creating New Database

Vamos a crear una base de datos y la llamaremos blog. Para hacer esto escribimos la siguiente sentencia SQL y presionamos Enter:

CREATE DATABASE blog;

La salida esperada de este comando es la siguiente:

Query OK, 1 row affected (0.01 sec)

Los comando de MySQL no son sensibles a mayúsculas, así que podríamos escribir create database blog; y obtener el mismo resultado. Se recomienda usar las sentencias SQL en mayúsculas ya que es una convención.

Luego, creamos el nombre de usuario blog y concederle todos los privilegios para acceder y modificar la base de datos blog y todas sus tablas:

GRANT ALL PRIVILEGES ON blog.* TO blog@localhost IDENTIFIED BY '<passwd>';

En el comando de arriba se debe reemplazar el comodín con la nueva contraseña para el usuario blog. La contraseña debería ser diferente a la del usuario root.

Se crea el segundo usuario blog, porque no es recomendable dar a la aplicación web la capacidad de iniciar sesión en la base de datos con el usuario root. El usuario root tiene derechos ilimitados y es una mala práctica de seguridad dar a la aplicación la capacidad de hacer cualquier acción. El usuario blog tendrá permisos para modificar solo la base de datos blog que es suficiente en nuestro caso.

Podemos revisar si la base de datos ha sido creada escribiendo el siguiente comando y presionando Enter:

show databases;

Deberíamos ser capaces de ver un salida como la siguiente (nos interesa la linea blog de la lista de base de datos).

+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog               |
| mysql              |
| performance_schema |
+--------------------+

12.2.2. Crear Tablas

Luego, nosotros crearemos tres tablas que son típicas en un blog simple: la tabla post contendrá las publicaciones, la tabla comment contendrá los comentarios de las publicaciones y finalmente la tabla tag contendrá las etiquetas (una etiqueta es un tipo de palabra clave que describe una publicación del blog).

Además, creamos una cuarta tabla auxiliar llamada post_tag que será usada para crear la relación muchos-a-muchos que existe entre las tablas post y tag.

Nos colocamos sobre la base de datos blog escribiendo el siguiente comando en la consola de MySQL:

use blog;

Para crear la tabla post escribimos la siguiente sentencia SQL:

CREATE TABLE `post` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `status` int(11) NOT NULL,
  `date_created` datetime NOT NULL
);

El cliente de MySQL permite fácilmente usar comandos de múltiples lineas. Solo presionamos Enter cuando queramos mover el cursor a la siguiente línea. El comando es considerado completo cuando el carácter punto y coma (;) es encontrado.

La salida esperada para este comando es la siguiente:

Query OK, 0 rows affected (0.22 sec)

Luego, creamos la tabla comment escribiendo lo siguiente:

CREATE TABLE `comment` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `author` varchar(128) NOT NULL,
  `date_created` datetime NOT NULL
);

Luego, creamos la tabla tag;

CREATE TABLE `tag` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(128)
);

Y finalmente creamos la tabla post_tag:

CREATE TABLE `post_tag` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
);

Vamos a llenar las tablas que hemos creado con datos de ejemplo:

INSERT INTO tag(`name`) VALUES('ZF3');
INSERT INTO tag(`name`) VALUES('book');
INSERT INTO tag(`name`) VALUES('magento');
INSERT INTO tag(`name`) VALUES('bootstrap');

INSERT INTO post(`title`, `content`, `status`, `date_created`) VALUES(
   'A Free Book about Zend Framework',
   'I''m pleased to announce that now you can read my new book "Using Zend Framework 3" absolutely for free! Moreover, the book is an open-source project hosted on GitHub, so you are encouraged to contribute.',
   2, '2016-08-09 18:49');

INSERT INTO post(`title`, `content`, `status`, `date_created`) VALUES(
   'Getting Started with Magento Extension Development - Book Review',
   'Recently, I needed some good resource to start learning Magento e-Commerce system for one of my current web projects. For this project, I was required to write an extension module that would implement a customer-specific payment method.',
   2, '2016-08-10 18:51');

INSERT INTO post(`title`, `content`, `status`, `date_created`) VALUES(
   'Twitter Bootstrap - Making a Professionaly Looking Site',
   'Twitter Bootstrap (shortly, Bootstrap) is a popular CSS framework allowing to make your website professionally looking and visually appealing, even if you don''t have advanced designer skills.',
   2, '2016-08-11 13:01');

INSERT INTO post_tag(`post_id`, `tag_id`) VALUES(1, 1);
INSERT INTO post_tag(`post_id`, `tag_id`) VALUES(1, 2);
INSERT INTO post_tag(`post_id`, `tag_id`) VALUES(2, 2);
INSERT INTO post_tag(`post_id`, `tag_id`) VALUES(2, 3);
INSERT INTO post_tag(`post_id`, `tag_id`) VALUES(3, 4);

INSERT INTO comment(`post_id`, `content`, `author`, `date_created`) VALUES(
    1, 'Excellent post!', 'Oleg Krivtsov', '2016-08-09 19:20');

Si es necesario podemos fácilmente borrar la base de datos y todas las tablas con sus datos escribiendo el siguiente comando en la consola de MySQL:

DROP DATABASE blog;

La figura 12.3 muestra gráficamente cuales entidades tenemos en la base de datos y que relaciones existen entra ellas.

Figure 12.3. Representación gráfica del esquema de base de datos

Como podemos ver en la figura 12.3, la tabla post tiene una relación uno-a-muchos con la tabla comment porque una publicación puede tener muchos comentarios. Esta relación es también llamada uno-a-muchos.

La tabla post tiene una relación mucho-a-muchos con la tabla tag. Una publicación puede tener muchas etiquetas y una etiqueta puede pertenecer a muchas publicaciones. La relación mucho-a-muchos es típicamente implementada por medio de una tabla auxiliar (en nuestro caso la tabla post_tag).

12.2.3. Importar un esquema de base de datos

En la sección anterior hemos mostrado como crear un esquema completo de base de datos que se usa en la aplicación de ejemplo Blog. En la vida real generalmente no escribimos todas las sentencias SQL en la consola de MySQL. En su lugar, podríamos escribir las sentencias CREATE TABLE en un archivo y guardarlo en el disco duro. Luego podríamos importar el archivo y tener el esquema listo.

Para nuestra conveniencia, un esquema listo para usar con el ejemplo del Blog se encuentra en el archivo APP_DIR/data/schema.mysql.sql. Este es una archivo de texto plano que contiene sentencias SQL. Para importar el archivo vamos al directorio APP_DIR/data/ y escribimos el siguiente comando desde la consola de comandos (no en la consola de MySQL):

mysql -u root -p blog < schema.mysql.sql

Cuando pida la contraseña, escribimos la contraseña del usuario root y presionamos Enter:

Una vez que esto esta listo iniciamos sesión en el cliente de MySQL y escribimos los siguientes comandos:

use blog;

show tables;

Deberíamos ver la lista de tablas creadas, algo como lo siguiente:

+----------------+
| Tables_in_blog |
+----------------+
| comment        |
| post           |
| post_tag       |
| tag            |
+----------------+
4 rows in set (0.00 sec)

Además, consideremos usar las migraciones de base de datos para crear el esquema de base de datos. Para información adicional sobre migraciones podemos revisar el capítulo Migración de Base de Datos.