Tutorial Postgresql (1): Preparando el entorno (Actualizado)

PostgreSQL- Preparando el entorno

Este un proyecto de aprendizaje con PostgreSQL que busca enseñar mediante el desarrollo de una base de datos completa, esto nos permitirá a los que no han utilizado esta base de datos antes iniciarse en PostgreSQL con la ayuda de las explicaciones y ejemplos. Según avance está guía se explicarán temas avanzados para usuarios con más experiencia.

En este caso vamos a crear una serie de tablas en relación al proyecto que se explica a continuación para poder aprender y hacer pruebas con Postgresql con ejemplos, este tutorial se desarrolla en base al proyecto Learning Project.

En esta primera publicación 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, esto es lo que vamos a ver:

  1. EL PROYECTO: definición y esquema E-R
  2. PREPARACIÓN DEL ENTORNO
  3. CREACIÓN DE LAS TABLAS


 

1. EL PROYECTO: definición y esquema E-R

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.

Learning Project Customerdb Database Esquema Inicial

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 se explicará directamente sobre el código SQL:

Learning Project
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 otros campos básicos propios.
cb_customer
Tabla general de cliente, donde se almacenarán los clientes de las diferentes empresas.
Tendrán definido inicialmente: idioma, país, direcciones, empresas, métodos de pago y moneda, así como otros campos básicos propios.
cb_addresses
Tabla de registro de las direcciones, se asocian aquí las direcciones que tienen un cliente.
Registro para asociar las direcciones al cliente, cada cliente tendrá un número ilimitado de direcciones con cb_address relacionado mediante cb_addresses (es básicamente una tabla relacional)
cb_address
Tabla de dirección donde se guardan los datos de la dirección en sí.
Tabla de dirección que 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_currency
Tabla con las monedas registradas 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_paymentmethod
Métodos de pago definidos para el cliente u otras entidades.


 

2. PREPARACIÓN DEL ENTORNO

 

Instalación de Postgresql e Instalación de 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.

3. 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:

Learning Project Customerdb Database Entity Relation

Esquema E-R de Customerdb propuesto en Learning Project

Creando la primera tabla cb_language

Vamos a definir como clave primaria (primary key) 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, veremos nuevos tipos de datos y nuevas relaciones que podemos establecer para relacionar las tablas entre sí.

 

Espero que te haya sido útilXules

 

¿Qué hacemos ahora?

Continúa con el aprendizaje de PostgreSQL siguiendo con la creación de tablas.

PostgreSQL creación de las tablas

Implementamos el proyecto Learning Project en Postgresql explicando las tablas y su sintaxis paso a paso, hacemos la explicación de las tablas de forma ordenada con lo que ejecutando la sintaxis planteada en este orden podrás crear la base de datos sin problema.
Código Xules

 
 
Iniciate en el desarrollo de aplicaciones empresariales web con el proyecto Learning Project con PostgreSQL:

Desarrollo en 6 pasos de una aplicación con Spring Roo con PostgreSQL

En este tutorial aprenderemos a hacer una aplicación Web desde cero usando Spring Roo con PostgreSQL Código Xules

Spring Roo

A next-generation rapid application development tool for Java developers. With Roo you can easily build full Java applications in minutes.Spring Roo

 

Aprende OpenXava con PostgreSQL

OpenXava es un marco de trabajo AJAX para desarrollo rápido de aplicaciones web empresariales.

Con OpenXava sólo has de escribir las clases del dominio con Java para obtener una aplicación web lista para producción.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *