Mostrando entradas con la etiqueta sql. Mostrar todas las entradas
Mostrando entradas con la etiqueta sql. Mostrar todas las entradas

lunes, 18 de febrero de 2013

Bases de datos: PostgreSQL

PostgreSQL PostgreSQL, o simplemente Postgres, es una base de datos relacional y orientada a objetos de código abierto y es una alternativa a MySQL en el desarrollo de proyectos web. Según sus desarrolladores es el sistema de gestión de bases de datos de código abierto más potente del mercado y en sus últimas versiones no tiene nada que envidiarle a otras bases de datos comerciales como Oracle o DB2.

PostgreSQL, que tiene versiones tanto para Windows como para Linux y Unix, utiliza un modelo cliente/servidor y usa multiprocesos para garantizar la estabilidad del sistema, de forma que un fallo en uno de los procesos no afecte al resto y el sistema continúe funcionando. Sus puntos fuertes son: estabilidad, potencia, robustez, facilidad de administración e implementación de estándares.


Caracterísicas:

Podríamos definir PostgreSQL como una base de datos Objeto-Relacional, ya que incorpora características tanto de las bases de datos orientadas a objetos como de las relacionales. Sus características más importantes son:

  • Integridad transaccional.
  • Integridad referencial (claves foráneas).
  • Extensibilidad de tipos de datos. Soporta de forma nativa gran cantidad de tipos de datos y permite definir nuevos.
  • Control de acceso multi-versión (MVCC), que permite que un proceso acceda a una tabla mientras otro la sobreescribe, sin necesidad de bloqueos.
  • Soporte completo al lenguaje SQL.
  • Restricciones (constraints).
  • Disparadores (triggers).
  • Reglas (rules).
  • Funciones/procedimientos almacenados (stored procedures).
  • Lenguaje de programacion PL/pgSQL (similar al PL/SQL de Oracle).
  • Numerosas APIs para programar en C/C++, Java, .Net, Python, Ruby, etc.
  • Soporte a juegos de caracteres internacionales.
  • Licencia BSD (código abierto).

Historia:

PostgreSQL tiene detrás una larga historia. El proyecto nació en 1986 en la Universidad de Berkeley como sucesor de uno anterior llamado Ingres, el cual fue a su vez uno de los primeros intentos de crear una base de datos relacional. Su nombre original fue Postgres (Post-Ingres) y fue pionera en muchos de los conceptos de bases de datos relacionales orientadas a objetos. El proyecto duró hasta 1994 y finalizó con el lanzamiento de la versión 4. Después de eso continuó siendo desarrollado como código abierto, ya que contaba con licencia BSD. En 1995 se le añadió soporte para el lenguaje SQL, naciendo Postgres95. Posteriormente en 1996 se le cambió el nombre al que tiene en la actualidad: PostreSQL. Con dicho cambio de nombre pretendía diferenciarse del proyecto original. Actualmente el proyecto va por la versión 9 y está dirigido por la comunidad PGDG (PostgreSQL Global Development Group). Como los demás proyectos de código libre se trata de una comunidad de desarrolladores que trabajan de forma desinteresada, altruista y libre, en ocasiones apoyados por organizaciones comerciales. Aquí podéis leer más información sobre la historia de PostgreSQL.


PostgreSQL frente a MySQL:

Tanto PostgreSQL como MySQL son gestores de bases de datos de código abierto y ambos comparten muchas características. Los expertos consideran que PostgreSQL es superior a MySQL porque incorpora de forma nativa gestión de transacciones, soporte a la integridad referencial y subconsultas. Además ofrece mejor tiempo de respuesta en bases de datos de gran tamaño. Sin embargo PostgreSQL carece de un conjunto de herramientas que permitan una fácil gestión de los usuarios y de las bases de datos que contenga el sistema. Por su parte los puntos fuertes de MySQL son su bajo consumo, siendo apto para ser ejecutados en máquinas de bajos recursos, su velocidad a la hora de realizar las operaciones (siempre que el número de registros no sea muy elevado) y la gran cantidad de herramientas de las que dispone. Además las últimas versiones de MySQL van incorporando algunas de las características de las que carecía como la integridad referencial.

MySQL es, con diferencia, el gestor de bases de datos más usado en el diseño de páginas web, disponiendo de numerosa documentación y multitud de herramientas de ayuda al programador. Debería ser por lo tanto la primera elección a la hora de escoger un gestor de bases de datos para un proyecto web. Sin embargo PostgreSQL es superior en muchos aspectos y debería ser tenido en cuenta en proyectos que requieran alta concurrencia o elevado volumen de datos.


Enlaces:

martes, 15 de enero de 2013

[Truco] Importar un archivo CSV en una tabla MySQL

Hoy necesitaba importar un volcado inmenso de datos en una tabla MySQL. El archivo origen estaba en formato CSV para Excel, que es algo diferente del CSV estándar. Nunca había realizado una carga de datos similar, pero una búsqueda en Google me ha revelado el comando LOAD DATA INFILE. Sólo he encontrado referencias al mismo en inglés, así que paso a contar como funciona.

El comando MySQL LOAD DATA INFILE permite leer registros desde un fichero de texto, pudiendo determinar las características de los datos a procesar como el carácter de separación (coma, punto y coma, etc.), el carácter de fin de línea (CR, CRLF, etc.) y algunas cosas más. El formato de este comando se puede encontrar en el manual de MySQL.


Ejemplo #1:

LOAD DATA
LOCAL INFILE 'C:\\location.csv'
INTO TABLE location
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

En este ejemplo se procesará el archivo "C:\location.csv", que se encuentra en el ordenador cliente (LOCAL), grabando los datos en la tabla "location", indicando que los campos están separados por punto y coma (CSV para Excel) y las líneas acaban con "CRLF" (Windows). Además se salta la línea de cabecera (IGNORE).


Ejemplo #2:

LOAD DATA
INFILE 'location.csv'
INTO TABLE location
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

En este segundo ejemplo se procesará el archivo "location.csv", que se encuentra en el servidor, grabando los datos en la tabla "location", indicando que los campos están separados por coma y delimitados por comillas (CSV estándar) y las líneas acaban con "LF" (Linux).

jueves, 20 de diciembre de 2012

Tabla de países 2.0

Hace tiempo necesitamos una tabla de países para un proyecto web. Aprovechamos en ese momento para ofrecer de forma libre y gratuita un archivo SQL que generaba una tabla de países para una base de datos MySQL. Dicha tabla tenía el código del país en formato ISO 3166-1 alfa-2 y el nombre oficial del país en Español.

Actualmente hemos necesitado ampliar esa tabla de países con los códigos ISO 3166-1 alfa-3 y el nombre internacional (en Inglés) y hemos aprovechado para añadir el código ISO 3166-1 numérico y el código FIPS por si en un futuro los necesitamos en otro proyecto. Todos esos códigos definen de forma única un país o territorio:

  • Códigos ISO 3166-1: Se trata de distintos códigos de países otras dependencias administrativas definidos como parte de la norma ISO 3166:
    • alfa-2: Código de 2 letras.
    • alfa-3: Código de 3 letras.
    • numérico: Código numérico de 3 dígitos.
  • Código FIPS: FIPS son las siglas de (Federal Information Processing Standard) y son un conjunto de estándares definidos por el gobierno de los Estados Unidos para la utilización por parte de todas las agencias del gobierno no militares y por los contratistas del gobierno. Los códigos de países FIPS son una versión modificada del código ISO 3166-1 alfa-2.

Ejemplo:

alfa-2alfa-3num-3FIPSNombre [ES]Nombre [INT]
ESESP724SPEspañaSpain
PTPRT620POPortugalPortugal
FRFRA250FRFrancia France
GBGBR826UKReino UnidoUnited Kingdom
USUSA840USEstados UnidosUnited States

Tabla de países:

CREATE TABLE IF NOT EXISTS `countries` (
  `iso_a2` varchar(2) NOT NULL,
  `iso_a3` varchar(3) DEFAULT NULL,
  `iso_n3` varchar(3) DEFAULT NULL,
  `fips` varchar(10) DEFAULT NULL,
  `name_es` varchar(64) DEFAULT NULL,
  `name_int` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`iso_a2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Como de costumbre ponemos a disposición de quien lo necesite, de forma libre y gratuita, un archivo que genera la versión 2.0 de la tabla de países en formato MySQL. Se permite explícitamente usar y alterar los datos contenidos en ese archivo para cualesquier fin, aunque no garantizamos que los datos sean correctos o estén totalmente actualizados.

Descargar paises-v2.0.zip (6,31 KB).


Fuentes:

lunes, 16 de abril de 2012

Tabla de países y actualización de códigos postales

En la entrada anterior habíamos ofrecido forma libre y gratuita un archivo que generaba una base de datos de provincias, municipios y códigos postales (MySQL). Ese archivo tenía algunos pequeños errores, por lo que ahora ofrecemos la versión 1.1 del mismo. Se permite explícitamente usar y alterar los datos contenidos en ese archivo para cualesquier fin, aunque no garantizamos que los datos sean correctos o estén totalmente actualizados.

Descargar codigos-postales-v1.1.zip (393 KB).

Además hemos necesitado para otro cliente una lista actualizada de países que se seleccionarán de una lista desplegable en un formulario HTML. En otro proyecto anterior ya habíamos necesitado esa lista de países, así que en este caso ha sido fácil. Aprovechamos para poner a disposición de quien lo necesite, nuevamente de forma libre y gratuita, un archivo que genera una tabla de países en formato MySQL. Se permite explícitamente usar y alterar los datos contenidos en ese archivo para cualesquier fin, aunque no garantizamos que los datos sean correctos o estén totalmente actualizados.

Descargar paises-v1.0.zip (2,67 KB).

El formato de las tablas es el siguiente:

CREATE TABLE `provincias`
( `cod_prov` VARCHAR(2) NOT NULL
, `provincia` VARCHAR(48) NOT NULL
, PRIMARY KEY ( `cod_prov`, `provincia` )
);

CREATE TABLE `poblaciones`
( `cod_postal` VARCHAR(5) NOT NULL
, `poblacion` VARCHAR(64) NOT NULL
, `cod_prov` VARCHAR(2) NOT NULL
, PRIMARY KEY ( `cod_postal`, `poblacion`, `cod_prov` )
);

CREATE TABLE `paises`
( `cod_pais` VARCHAR(2) NOT NULL
, `pais` VARCHAR(64) NOT NULL
, PRIMARY KEY ( `cod_pais` )
);

lunes, 12 de marzo de 2012

Provincias, municipios y códigos postales

Quería empezar este blog oficial de iFraktal Internet Marketing, S.L. con un caso práctico.

Estábamos diseñando la página web para un cliente consistente en una mini-tienda on-line, desde la cual se aceptan pedidos de un producto y pensamos que sería buena idea añadir una validación de códigos postales, municipios y provincias. Al tratarse de un nuevo negocio no había datos previos que importar, con lo partíamos de una base de datos en blanco y de esta forma podíamos evitar que la misma se llenase de información duplicada e inútil que nos hemos encontrado otras veces con nombres de municipio mal escritos. Un caso típico sería encontrarse municipios como "HOSPITALET", "L'HOSPITALET", "HOSPITALET DE LLOBREGAT", "L'HOSPITALET DE LLOBREGAT",... cuando la denominación oficial según el INE es "HOSPITALET DE LLOBREGAT, L'".

Así que nos pusimos a buscar en Internet una lista actualizada de poblaciones y códigos postales españoles. Debería ser fácil, ¿no? PUES NO.

Lo primero que encontramos fue una relación de provincias con sus códigos y la lista oficial de municipios actualizada a 1 de enero de 2012, ambos proporcionados por el INE (Instituto Nacional de Estadística). Hasta aquí fue fácil, pero había un problema: la lista de municipios incluía el código de la provincia, el código de municipio y hasta un dígito de control, pero no incluía el código postal del mismo.

Así que nos pusimos a buscar una relación de códigos postales y municipios. Siguiendo con el INE hay una sección de descargas del callejero, que prometía. Sin embargo una vez descargada la información consistía de 4 archivos de texto en formato ASCII con campos de ancho fijo conteniendo vías, pseudovías, tramos de vías y unidades poblacionales. Existe un archivo en formato Word que explica como se relaciona cada archivo. Tras revisar la documentación y echar un vistazo a los archivos decidimos seguir buscando. Procesar esos archivos iba a requerir demasiado esfuerzo.

Seguimos buscando y lo siguiente que encontramos es que no somos los primeros que nos ha ocurrido esto. En el blog de Carme Pla la autora explica que se encontró con la misma problemática que nosotros. Allí nos enteramos que Correos proporciona unas bases de datos de códigos postales a un precio totalmente desorbitado. El mismo blog nos dirige a una página llamada "Códigos Postales de España: Base de datos", que contiene datos de provincias, municipios poblaciones y calles con sus correspondientes códigos postales en hasta 3 formatos diferentes (CSV, XML y MySQL). Descargamos y probamos.

Los fichero tienen algún fallo y probablemente no estén complemente actualizados pero algo es algo. Tras cargar el archivo CSV en una tabla MySQL y procesar el contenido, hemos conseguido una tabla conteniendo la lista de municipios poblaciones y códigos postales, que era lo que queríamos. La información de las calles se ha eliminado pues en nuestro caso no tenía utilidad.

Y para que este trabajo pueda servir a otros, hemos decidido ofrecer de forma libre y gratuita un archivo de texto conteniendo las sentencias MySQL para crear la base de datos de provincias, municipios poblaciones y códigos postales. Se permite explícitamente usar y alterar los datos contenidos en ese archivo para cualesquier fin. No garantizamos que los datos sean correctos o estén actualizados.

Descargar codigos-postales-v1.0.zip (391 KB).


ACTUALIZACIÓN:

Se ha actualizado el archivo de códigos postales a la versión 1.1 debido a algunos pequeños fallos: Descargar codigos-postales-v1.1.zip.

También puede interesarte: