Generando diagramas entidad-relación con SchemaSpy

Introducción.

SchemaSpy screenshot - All relationships
SchemaSpy screenshot – All relationships

Dando vueltas el mes pasado por los lados de SourceForge encontré esta aplicación y no había tenido tiempo de probarla hasta el día de hoy.  Para mi satisfacción resultó ser muy interesante.  Es una aplicación desarrollada en Java que genera el diagrama Entidad-Relación de una base de datos y otras cositas, como sus desarrolladores la llaman, un navegador gráfico de esquemas de metadatos de una base de datos.  Puede acceder a cualquier motor de base de datos desde que se cuente con su correspondiente driver JDBC.

Requerimientos.

Para utilizar la aplicación se requiere lo siguiente.

  1. Contar con Java5 o superior.
  2. Descargar la última distribución de SchemSpy.
  3. Obtener el driver correspondiente a su base de datos.

Para realizar la conexión con una base de datos Microsoft SQL Server recomiendo utilizar el driver de jTDS ya que el proporcionado por Microsoft me generó problemas del siguiente estilo.

Driver version is x.x.xxx.xxx
com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
        at com.microsoft.sqlserver.jdbc.TypeInfo.init(Unknown Source)
        at com.microsoft.sqlserver.jdbc.StreamColumns.processBytes(Unknown Source)
        at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteQuery(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
        at one_con.main(one_con.java:26)

Instalación.

La instalación de la aplicación no excede el descomprimir los paquetes descargados, sin embargo su ubicación es importante.  Para esto utilicé la siguiente estructura de directorios.

  1. ~/Applications/SchemaSpy – directorio de la aplicación.
  2. ~/Applications/SchemaSpy/drivers – controladores de acceso a la base de datos.

En la carpeta [1] se ubica el JAR de la aplicación: schemaSpy_4.1.1.jar.

En la carpeta [2] se ubican los JAR de los drivers JDBC: mysql-connector-java-5.1.7-bin.jar para MySQL y jtds-1.2.2.jar para MSSQL.

Ejecución.

Para esto acostumbro a crear scripts del shell que ubico en ~/Applications, sin embargo lo importante es su contenido que puede invocarse directamente desde la línea de comando.

Para MySQL.

$ java -jar SchemaSpy/schemaSpy_4.1.1.jar -dp ~/Applications/SchemaSpy/drivers/mysql-connector-java-5.1.7-bin.jar -t mysql -host HOST -db DBNAME -u DBUSER -p DBPASS -o OUTDIR

Para MSSQL.

$ java -jar SchemaSpy/schemaSpy_4.1.1.jar -dp ~/Applications/SchemaSpy/drivers/jtds-1.2.2.jar -t mssql-jtds -host HOST -port PORT -db DBNAME -s DBSCHEMA -u DBUSER -p DBPASS -o OUTDIR

Por supuesto se deben reemplazar apropiadamente las siguientes variables.

  • HOST – servidor de base de datos.
  • PORT (requerido por MSSQL) – puerto de conexión al motor de base de datos.  El puerto por defecto de MSSQL es el 1433.
  • DBNAME – nombre de la base de datos.
  • DBSCHEMA (sólo con MSSQL) – esquema de la base de datos.  Utilizo dbo.
  • DBUSER – usuario de la base de datos.
  • DBPASS – contraseña del usuario $DBUSER.
  • OUTDIR – ubicación donde la aplicación generará los archivos resultantes.

Los comandos utilizan la sintaxis del shell de Linux, para utilizarlos desde Windows adapte las rutas según su conveniencia, recuerde modificar los slashes (/) por backslashes ().

Nota acerca de la generación de gráficos.

Para que se generen correctamente los gráficos de los diagramas Entidad-Relación es necesario que se cuente previamente con la librería Graphviz.  Para instalar esta librería desde Ubuntu Linux sólo es necesario ejecutar el siguiente comando. $ sudo apt-get install graphviz

Conclusiones.

Muy intersante esta aplicación, me gustó bastante.  Genera una buena documentación de la base de datos en formato HTML con varias secciones que se pueden navegar entre ellas.  Incluye varias perspectivas de la misma información que facilita su documentación.  Incluso siendo una presentación local, incluye JQuery para mejorar la usabilidad de los reportes.

Enlaces.

Conexión a MSSQL desde Aptana

Aptana's Database Manager
Aptana’s Database Manager

Probando una nueva alternativa para tener un cliente a MSSQL desde Linux he decidido probar el cliente de bases de datos JDBC de Aptana Studio con buenos resultados.

Para su configuración se deben seguir estos pasos.

  1. Tener el driver JDBC de MSSQL en algún lugar del disco duro.
  2. Abrir la perspectiva de Database Explorer.  Para hacer esto seleccione los menúes Window > Open Perspective > Other … > Database Explorer.
  3. Haga click derecho sobre la rama Databases ubicada al lado izquierdo y seleccione la opción Add.
  4. Elija el nombre de la conexión y presione el botón Next.
  5. En los detalles de conexión presione el botón Add.
    1. Especifique un nombre para el controlador.
    2. Presione el botón Add File y seleccione el archivo del driver JDBC de MSSQL establecido anteriormente.
    3. En la información de conexión seleccione el driver encontrado.
    4. Presione el botón OK.
  6. Adecúe la cadena de conexión según sus necesidades: jdbc:sqlserver://<HOST>:1433;DatabaseName=<DBNAME>
  7. Especifique el nombre de usuario de conexión a la base de datos (User).
  8. Especifique la contraseña del usuario de conexión a la base de datos (Password).
  9. Presione el botón Test Connection para verificar la información de conexión.  Realice los ajustes necesarios en caso de presentarse problemas.
  10. Presione el botón Finish para terminar la configuración.

Probando Firebird Embedded con C#

Desde hace mucho tiempo tengo planeado desarrollar una aplicación cuya versión 0.1 desarrollé hace ya varios años en C++ y Fox Toolkit, perdida ya en el museo del olvido.

La aplicación es muy sencilla.  Inicialmente son dos módulos pero la idea es agregarle después algunos otros.  El problema es que cada vez que me siento a pensar en ella termino haciendo un diseño complejo y lleno de cositas que me pondrían a estudiar.  Esto no sería malo sino fuera porque de esta manera se va a terminar cuando el tiempo tienda a infinito.

Por esto he cambiado mi plan.  Voy a hacer una aplicación pequeña y la voy a ir hacer creciendo, aunque esto signifique que alguna versión deba volverla a hacer desde scratch (cero).

Con respecto al lenguaje de programación había elegido Java para retomar mi estudio pero en medio de la moda local y temporal de .NET he decidido empezar a implementar en C#.

El primer reto a resolver es la escogencia de un motor de base de datos ya que la aplicación va a ser pequeña, me interesa que se pueda ejecutar en equipos no necesariamente robustos, no quiero despilfarrar recursos y quiero que la instalación y copia de seguridad sean fáciles: sólo copiar archivos.  Por esto he pensado en utilizar una base de datos basada en archivos o embedded.

Para Java hay varias de donde escoger, pero para .NET la oferta, al parecer, no es tan amplia.  Encontré SQLite (ya lo he utilizado varias veces pero he tenido problemas con bloqueos) VistaDB (comercial, no me sirve) y a SharpHSQL hermanita de HSQL (Java) para .NET, sin embargo no estoy seguro de cuan activo está ese proyecto.  Las mejores opciones por ahora parecen ser FireBird Embedded y SQL Server Compact, ambas prometen muchas cosas, incluyendo un bajo footprint ~ 2MB.

Para la primera prueba elegí a FireBird ya que esa fue la primera base de datos que utilicé en un proyecto después de graduado como ingeniero y me unen a ella emotivos recuerdos 🙂

Instalación.

Para desarrollar una aplicación con FireBird inmerso se requieren dos recursos.

El FirebirdClient se instala como cualquier aplicación Windows sin ninguna opción específica.  Me imagino que habrá que instalarlo también en los clientes cuando se haga el despliegue (por confirmar).

La distribución se debe descomprimir y mover los siguientes archivo al mismo directorio donde se encontrará el archivo ejecutable de la aplicación: <ruta>binDebug (desarrollo) o <ruta>binRelease (producción).

  • fbembed.dll
  • icudt30.dll
  • icuin30.dll
  • icuuc30.dll
  • firebird.conf
  • firebird.msg

Para la implementación del proyecto estoy utilizando Visual C# Express 2008 y en él es necesario incluír la referencia a FirebirdSql.Data.FirebirdClient instalada con el Data Provider.

Creación de la cadena de conexión.

Es fácil construír la ConnectionString con la ayuda del FbConnectionStringBuilder de la siguiente manera.

            FbConnectionStringBuilder csb = new FbConnectionStringBuilder();

            csb.ServerType = FbServerType.Embedded;
            csb.UserID     = "SYSDBA";
            csb.Password   = "masterkey";
            csb.Dialect    = 3;
            csb.Database   = @"datadatabase.fdb";
            csb.Charset    = "UTF8";

Sólo los parámetros ServerType y Database son obligatorios, los demás son opcionales y puede encontrarse mayor información sobre ellos en ConectionStringParameters, también se puede encontrar mayor información acerca de los Charset disponibles en Firebird Character Sets and Collations.

La ubicación de la base de datos (parámetro Database) es relativo a la ubicación del archivo fembed.dll, es decir, al ejecutable de la aplicación.

Creación de la base de datos.

Desde la misma aplicación es posible crear la base de datos (manera programática) a la cual hace referencia la cadena de conexión.

           FbConnection.CreateDatabase(csb.ToString());

Debe tenerse en cuenta que la ruta de directorios bajo la cual se ubicará la base de datos ya debe existir previa la creación del archivo de datos, de lo contrario la creación fallará.

Conexión a la base de datos.

          con = new FbConnection(csb.ToString());
          con.Open();

Verificación del estado de la conexión.

            if (con.State == System.Data.ConnectionState.Open)
                Console.WriteLine("Established");
            else
                Console.WriteLine("NOT established");

Otros posibles estados definidos por ConnectionState son Broken, Closed, Connecting, Executing y Fetching.

Verificación de la existencia de una tabla.

            FbCommand cmd = new FbCommand("SELECT COUNT(RDB$RELATION_NAME)" +
                                          "FROM RDB$RELATIONS WHERE (RDB$RELATION_NAME = 'users')" +
                                          "AND RDB$VIEW_SOURCE IS NULL", con);

            int tableCount = (int)cmd.ExecuteScalar();

            if (tableCount == 0)
                Console.WriteLine("No");
            else
                Console.WriteLine("Yes");

Verifica la existencia o no de la tabla users en la base de datos conectada.

Ejecución de un INSERT.

            String sql = "INSERT INTO "users" VALUES ('" + user[0] + "', '" + user[1] + "')";
            cmd = new FbCommand(sql, con);
            cmd.ExecuteNonQuery();

Ejecución de una consulta de datos.

            String select = "SELECT username, name FROM "users"";
            cmd = new FbCommand(select, con);
            FbDataReader reader = cmd.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    String username = reader.GetString(0).Trim();
                    String name = reader.GetString(1).Trim();

                    Console.WriteLine("t{'" + username + "', '" + name + "'}");
                }
            }
            finally
            {
                reader.Close();
            }

Finalización de la conexión.

            con.Close();

Conclusiones.

Esta primera aproximación fue sencilla, práctica y funcional.  Se encuentra buena documentación del motor de base de datos y movimiento en los foros en su respecto.  A pesar de que, al menos por estas latitudes, no es muy utilizada Firebird parece ser que en otras partes si lo es.

Los archivos requeridos (dlls) ocupan 2.87MB mientras que todos los archivos, incluyendo los opcionales, ocupan 4.94MB.

Voy a revisar también cuan viable es utilizar el SQL Server Compact, sin embargo en los últimos días que hemos tenido un poco de contacto con SQL Server han aparecido con él algunos detalles desagradables que desde mi punto de vista de conocer poco acerca de este motor, me desaniman de utilizarlo.  Ya veremos.

Enlace: Aplicación de prueba – Firebird Embedded Demo 0.1.

Cliente para MSSQL desde Linux

Uno de los proyectos que estoy desarrollando requiere de la conexión a una base de datos Microsoft SQL Server 7 cuya instancia de desarrollo se encuentra instalada en un equipo Windows XP.  Solucionado anteriormente la conexión desde el servidor web con OpenSuse, ahora el problema es como administrar la base de datos sin utilizar la consola que obviamente se ejecuta en Windows unicamente.

Para esto encontré la aplicación SQuirrel SQL que se encuentra desarrollada en Java y soporta por supuesto, conexiones con cualquier base de datos que tenga soporte por un controlador JDBC.

Después de instalada en mi escritorio, descargué al controlador JDBC para MSSQL y lo copié bajo el directorio /lib de la aplicación.  Para mi caso: /home/jimezam/SQuirreL SQL Client/lib.  Los controladores JDBC para MSSQL pueden obtenerse desde la siguiente ubicación.

http://msdn.microsoft.com/en-us/data/aa937724.aspx

Al ejecutar la aplicación aparece activo el Driver Microsoft MSSQL Server JDBC Driver con un chulito azul.  Con esto, se crea la conexión a la base de datos: Aliases > New Alias.

Name Nombre de la conexión o alias a la base de datos.
Driver Microsoft MSSQL Server JDBC Driver
URL jdbc:sqlserver://HOST;databaseName=NAME;
User Name Nombre del usuario de la conexión
Password Contraseña de conexión
Auto logon [seleccionado]
Connect at Startup [seleccionado]

Otra aplicación Java/JDBC que instalé fue ThinkUI SQL Client (free for personal) la cual me había funcionado bien hasta que empecé a notarle pequeños detalles que desenamoran como el hecho de no mostrarme todos los registros retornados por una consulta sino los primeros x.  Espero tener una mejor experiencia con este nuevo cliente.

Oracle's Server Technologies Curriculum

Dando una vuelta por el blog de Informatics, art, graphics, desing, drawing … blog me encontré un enlace muy interesante de Oracle, el Server Technologies Curriculum desde donde se puede obtener tutoriales en línea de algunos de los productos de Oracle.

Otros sitios interesantes relacionados con la educación son Oracle University y Oracle by Example que complementan a los ya conocidos Documentation, Getting Started y Downloads.

Oracle es una de las muchas cosas que voy a aprender cuando tenga un tiempo libre, empezando por el Express Edition.

Autenticación local y conexiones de red de PostgreSQL 8.2 en FreeBSD 7

Autenticación local.

Por defecto PostgreSQL permite que las conexiones al motor de bases de datos que se realizan de manera local se hagan sin autenticación, lo cual es poco recomendable. Este comportamiento puede modificarse siguiendo los pasos dispuestos a continuación.

# vi /usr/local/pgsql/data/pg_hba.conf

Modificamos el método pasándolo de trust a md5 para los orígenes de conexión que nos interese asegurar. El siguiente es el nuevo contenido del archivo tomando en cuenta las modificaciones realizadas para activar el soporte de SSL.

local      all    all                      md5
hostssl    all    all    127.0.0.1/32      md5
hostssl    all    all    ::1/128           md5

Habilitar el acceso a las bases de datos a través de TCP/IP.

Nuevamente, por defecto PostgreSQL no permite la realización de conexiones cuya petición provenga de un equipo externo, es decir, diferente de localhost. Para habilitar esto se deberán seguir los pasos a continuación.

Es posible autorizar las conexiones desde orígenes externos nombrados, es decir, especificar con precisión desde cuales direcciones IP se habilitarán las conexiones remotas enunciándolas separadas por espacio en la variable listen_address (reemplazando el asterisco = todos).

# vi /usr/local/pgsql/data/postgres.conf
listen_addresses='*'

Debe tenerse en cuenta que además de permitir las conexiones desde una dirección IP específica (o varias), es necesario especificar el tipo de autenticación para ese equipo/red como se realizó en la sección anterior para localhost. Para permitir conexiones entrantes con soporte para SSL (hostssl), a cualquier base de datos (primer all), de cualquier usuario (segundo all) desde cualquier equipo de la red 192.168.1.x (192.168.0.0/24) utilizando contraseñas encriptadas con MD5 se deberá realizar el siguiente procedimiento.

# vi /usr/local/pgsql/data/postgres.conf
hostssl all all 192.168.1.0/24 md5

Para ajustar una mayor granularidad en la seguridad de conexión se deberán personalizar estos valores a sus requerimientos individuales.

Es necesario reiniciar el servicio para que se tomen en cuenta las modificaciones a la configuración recién realizadas.

Enlaces:

Soporte SSL para las conexiones de PostgreSQL 8.2 en FreeBSD 7

PostgreSQL trae inmerso por defecto el soporte nativo para encriptar el transporte de la información utilizando SSL. A continuación se describe el procedimiento necesario para activarlo en un servidor FreeBSD.

Activa el uso de SSL para las conexiones del motor de bases de datos.

# vi /usr/local/pgsql/data/postgresql.conf
ssl = on

Detiene el servicio del motor de bases de datos.

# /usr/local/etc/rc.d/postgresql stop

Crea el certificado SSL.

# cd /usr/local/pgsql/data/

En este paso se deben tener en cuenta dos aspectos. El primero de ellos es la clave PEM que se utilizará mas adelante y el segundo es que el Common Name deberá corresponder con nombre FQDN del servidor.

# openssl req -new -text -out server.req
Enter PEM pass phrase: MiClavePEM
Verifying - Enter PEM pass phrase: MiClavePEM
Country Name (2 letter code) [AU]: CO
State or Province Name (full name) [Some-State]: Caldas
Locality Name (eg, city) []: Manizales
Organization Name (eg, company) [Internet Widgits Pty Ltd]:  Personal Development
Organizational Unit Name (eg, section) []: Educacion
Common Name (eg, YOUR name) []: webserver.mydomain.com
Email Address []:jimezam@gmail.com
A challenge password []: Certificado&2008k
An optional company name []: Smiley World
# openssl rsa -in privkey.pem -out server.key
Enter pass phrase for privkey.pem: MiClavePEM
# rm privkey.pem

Se crean la llave privada y el certificado del servidor.

# openssl req -x509 -in server.req -text -key server.key -out server.crt
# chmod og-rwx server.key
# chown pgsql:pgsql server.*

Se reinicia el servidor para comprobar los cambios realizados.

# reboot

Por defecto las conexiones locales (con 127.0.0.1) se realizan sin encriptación. Siguiendo los pasos dispuestos a continuación es posible modificar este comportamiento y obligar a que este tráfico se encripte.

# vi /usr/local/pgsql/data/pg_hba.conf
local      all    all                      trust
hostssl    all    all    127.0.0.1/32      trust
hostssl    all    all    ::1/128           trust

Enlaces: