Learning Project Postgresql Tutorial (1)

Learning Project Postgresql Tutorial (1): Explicación

 

Este un proyecto de aprendizaje que solo busca hacer pruebas y si a alguien le sirve para iniciarse o hacer pruebas bienvenido. En este caso vamos a crear una serie de tablas en relación al proyecto que se pide a continuación para poder aprender y hacer pruebas con Postgresql con ejemplos.

En este primer post se definirá el proyecto y se preparará el entorno en el segundo finalizaremos la creación de tablas, poblaremos de datos la base de datos y haremos algunas consultas. 

Definición

 

El proyecto consiste en la administración de clientes para diferentes empresas, para ello se plantea la necesidad de tener la información de la empresa y de los clientes, cada empresa podrá tener múltiples clientes, para el caso de estudio se plantea que ambas entidades tengan los campos básicos y direcciones que podrán ser una o varias. Así mismo, y para completar el proyecto con el desarrollo que creemos necesario, se plantea que la tabla clientes tenga como tablas asociadas las tablas de: monedas, países e idiomas, que podrían ser tablas de uso general en la aplicación, y una tabla más específica como podría ser formas de pago.

Esquema inicial

 

Este no es un diagrama entidad relación clásico sino una simple estructura de las tablas que se van a implementar y de sus relaciones.

 

Tablas del esquema

 

Para desarrollar este esquema vamos a necesitar las siguientes tablas, a continuación se explica su utilidad y algunas características básicas, el resto irá directamente sobre el código SQL:

TABLA	DESCRIPCIÓN	CARACTERÍSTICAS DE DISEÑO
cb_enterprise	Empresas de la aplicación, cada empresa tendrá sus clientes.	Las empresas tendrán definido inicialmente: idioma, país y moneda, así como los campos básicos para hacer pruebas.
cb_custormer	Tabla general de cliente,  donde se almacenarán los clientes de las diferentes empresas, se entiende cliente como aquel que compra a una empresa.	Los tendrán definido inicialmente: idioma, país, direcciones, empresas, métodos de pago y moneda, así como los campos básicos para hacer pruebas.
cb_addresses	Tabla de registro de las direcciones, se asocian aquí las direcciones que tienen un cliente, cada cliente tendrá un número ilimitado de direcciones con cb_address relacionado mediante cb_addresses (es básicamente una tabla relacional).	Registro para asociar las direcciones al cliente.
cb_address	Tabla de dirección donde se guardan los datos de la dirección en sí.	Tabla dirección tendrá los campos generales de una dirección, así como números de teléfono, transportista y tipos de dirección.
cb_language	Tabla con los idiomas registrados en la aplicación, se podrá relacionar con múltiples tablas.
cb_country	Tabla con los países registrados en la aplicación, se podrá relacionar con múltiples tablas.	Para cada país se especificará la moneda y el idioma utilizado por defecto.
cb_currency	Tabla con las monedas registradas en la aplicación, se podrá relacionar con múltiples tablas.
cb_paymentmethod	Métodos de pago definidos para el cliente u otras entidades.

 

Learning Project Postgresql: Preparación del entorno

 

Instalación de Postgresql y Pgadmin

 

No me voy a extender en este apartado ya que no es el objetivo de este post, si daré los pasos básicos e indicaré  donde puedes encontrar más información.
Empezaremos con la descarga e instalación de Postgresql: Posgresql download aquí encontrarás las explicaciones para la instalación en los sistemas operativos disponibles. En mi caso al utilizar ubuntu este sería el resumen:

Ubuntu incluye PostgreSQL por defecto, así que para instalar PostgreSQL on Ubuntu, usamos el comando apt-get:

apt-get install postgresql-9.4

Este repositorio incluye todo lo que necesitamos para este proyecto, incluido pgadmin:

• postgresql-client-9.4 - librerías y binarios del cliente
• postgresql-9.4 - core database server
• postgresql-contrib-9.4 - módulos adicionales proporcionados.
• libpq-dev - libraries and headers for C language frontend development
• postgresql-server-dev-9.4 - librerías y cabeceras para el desarrollo en C del backend
• pgadmin3 - pgAdmin III utilidad de administración gráfica

 

Puesta en marcha básica del servidor

 
Después de la instalación necesitamos inicializar por primera vez el cluster de nuestra base de datos (se supone que ya estamos como root, para ejecutar estos comandos): 

service postgresql initdb

Ahora ya podemos iniciar el servicio

service postgresql start

Otras opciones del servicio son:  

stop|restart|reload|force-reload|status

Creamos un usuario llamado postgres, en este momento es el único autorizado (ni root puede) para trabajar en el clúster:

sudo -u postgres psql postgres

Configuramos una password para el role de la base de datos "postgres" usando el comando:

\password postgres

Ahora que ya estamos dentro podemos hacer las pruebas para ver que tenemos todo bien configurado, por ejemplo:

SELECT version();

Crear una base de datos de prueba :

createdb pruebadb;

Creación de base de datos. 

Creamos la base de datos para nuestro ejemplo, para ello como se puede ver se ha creado el rol xulescode con todos los privilegios:

CREATE DATABASE customerdb
  WITH OWNER = xulespro
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'es_ES.UTF-8'
       LC_CTYPE = 'es_ES.UTF-8'
       CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE customerdb TO xulescode;
GRANT ALL ON DATABASE customerdb TO public;

El comando para eliminar la base de datos sería.

DROP DATABASE customerdb;

A partir de aquí ya podemos seguir con el pgadmin3, básicamente es conectarse al servidor en este caso como localhost, y una vez dentro crear una base de datos.

Los scripts que relato a continuación los ejecuto directamente desde el pgadmin3.

Learning Project Postgresql: Creación de las tablas

Código sql para el proyecto

Para facilitar que puedas usar este ejemplo explicaré cada tabla en orden de creación con lo que copiando y ejecutando directamente en un script tendrías tu base de datos de ejemplo creada.

Con  pgadmin3 puedes crear las tablas directamente con el entorno gráfico que te proporciona, pero esto lo veremos otro día, ahora nos vamos a centrar en la creación de las tablas directamente en la base de datos con SQL.

Antes de empezar, aquí os presento el esquema real final de la base de datos que vamos a crear:

Creando la primera tabla cb_language

Vamos a definir como clave el campo idlanguage y en este caso no utilizamos un tipo serial (generación automática de un contador único), sino que como clave primaria usamos la codificación del idioma i18n e i10n, las principales: es_ES y en_EN, que serán las que se usarán por defecto.

CREATE TABLE cb_language
(
  idlanguage character varying(6) NOT NULL,
  namelanguage character varying(60) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'N'::bpchar,
  languageiso character(2),
  countrycode character(2),
  isbaselanguage character(1) NOT NULL DEFAULT 'N'::bpchar,
  issystemlanguage character(1) NOT NULL DEFAULT 'N'::bpchar,
  CONSTRAINT pk_cb_language PRIMARY KEY (idlanguage),
  CONSTRAINT u_cb_language_namelanguage UNIQUE (namelanguage),
  CONSTRAINT ch_cb_language_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
  CONSTRAINT ch_cb_language_isbaselang_check CHECK (isbaselanguage = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
  CONSTRAINT ch_cb_language_issysang_check CHECK (issystemlanguage = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
);
ALTER TABLE cb_language  OWNER TO xulespro;
GRANT ALL ON TABLE cb_language TO xulespro;
COMMENT ON TABLE cb_language
  IS 'Como clave primaria usamos la codificación del idioma i18n e i10n, las principales: es_ES y en_EN, que serán las que se usarán por defecto.';
COMMENT ON COLUMN cb_language.idlanguage IS 'Como clave primaria usamos la codificación del idioma i18n e i10n, las principales: es_ES y en_EN, que serán las que se usarán por defecto.';
COMMENT ON COLUMN cb_language.namelanguage IS 'Nombre del idioma en el idioma por defecto del sistema (castellano).';

En la web de Postgresql encontramos la documentación sobre CREATE TABLE SQL, la estructura para crear una tabla es la siguiente:

CREATE TABLE nombre_de_la_tabla(

....

); 

Con esto ya creamos nuestra tabla, ahoramos veamos como definimos la clave primaria, en este caso lo indicamos con la estructura CONSTRAINT nombre_pk PRIMARY KEY (campo_pk), aunque también se puede definir en el propio campo de la siguiente forma: campo_pk  tipo PRIMARY KEY, en nuestro ejemplo quedaría así:

1ª opción: 

   CONSTRAINT pk_cb_language PRIMARY KEY (idlanguage)

2ª opción: 
   idlanguage character varying(6) NOT NULL PRIMARY KEY

En este caso solo estamos usando dos tipos de datos character varying donde podemos definir la longitud del campo, como por ejemplo character varying(6), y character donde también se puede especificar la longitud. Los tipos de datos disponibles en Postgresql los puedes encontrar en Postgresl Chapter 8: Data types


Próximamente el siguiente post con la creación de las otras tablas la población de datos y las consultas básicas sobre nuestra base de datos.