Campos autonuméricos con SQLite3

Introducción

SQLite es un gestor de bases de datos pequeño y simple que se usa ampliamente como apoyo al software (aplicaciones de escritorio, navegadores web, etc.) y en sistemas embebidos como los teléfonos móviles (Android por ejemplo).

A continuación se muestra como crear campos autonuméricos en esta base de datos, es decir, campos cuyo valor es un número serial que es asignado automáticamente por el software.

Procedimiento

Crear la tabla con el campo autonumérico (code en este caso).

CREATE TABLE test (

code     INTEGER        PRIMARY KEY    AUTOINCREMENT,
name     VARCHAR(20)    UNIQUE,
value    FLOAT

);

Agregar datos a la tabla recién creada.

INSERT INTO test VALUES(NULL, 'demo1', 10.10);
INSERT INTO test VALUES(NULL, 'demo2', 20.20);
INSERT INTO test VALUES(NULL, 'demo3', 30.30);

Consultar los datos recién ingresados.

sqlite> select * from test;

    1|demo1|10.1
    2|demo2|20.2
    3|demo3|30.3

Consultar el valor del último campo autonumérico ingresado durante la sesión.

SELECT last_insert_rowid();

    3

Enlaces

Cursos de la Maestría en Software Libre de la UOC

Los cursos listados a continuación hacen parte de la Maestría en Software Libre de la Universidad de Cataluña y que han sido publicados bajo la licencia de Creative Commons a través de su sitio de OpenCourseWare.

50039 – Fundamentos de fotografía e imagen digital
50051 – Sistemas de vídeo y de tratamiento de la imagen
50070 – Laboratorio de PHP y MySQL
75143 – Trabajo final de carrera .NET
75555 – Prácticas de programación
75591 – Estructura de redes de computadores
75611 – Fundamentos físicos de la informática
75614 – Iniciación a las matemáticas para la ingeniería
76514 – Imagen y lenguaje visual
76517 – Arquitectura de la información
76521 – Redes Multimedia
76526 – Composición digital
76529 – Interfaces para sistemas multimedia
76531 – Animación 3D
76532 – Fotografía digital
81504 – Física I
89018 – Trabajo final de carrera de telecomunicación
93133 – Introducción a la creación de páginas web
93134 – Marcadores sociales, una herramienta para organizar la Red
93149 – Fotografia de viajes
B0019 – Estética y narrativa en medios digitales
B0762 – Servicios OGC
B0763 – Programación y Personalización SIG
M1302 – Ingeniería de la usabilidad
M1308 – Tecnología y desarrollo en dispositivos móviles
M2101 – Introducción al software libre
M2102 – Sistema operativo GNU/Linux básico
M2103 – Administración avanzada del sistema operativo GNU/Linux
M2104 – Implantación de sistemas de software libre
M2105 – Redes de computadores
M2107 – Aspectos avanzados de seguridad en redes
M2108 – Desarrollo de aplicaciones web
M2109 – Bases de datos
M2110 – Introducción al desarrollo de software
M2111 – Conceptos avanzados en desarrollo de software libre
M2112 – Ingeniería del software en entornos del software libre
M2113 – Utilidades y herramientas de software libre
M2114 – Aspectos legales y de explotación del software libre
M2114 – Aspectos legales y de explotación del software libre (b)
M2115 – Aspectos económicos y modelos de negocio del software llibre
M2117 – El software libre en el sector público
M2123 – Proyecto web
M2124 – Desarrollo de proyectos de software libre
M2125 – Proyecto de dirección de sistemas de información
M2132 – Proyecto en Administración de Redes y Sistemas Operativos basados en GNU/Linux
M2150 – Administración de sistemas GNU/Linux

Enlaces

Modificar la contraseña del usuario root de MySQL 5

Introducción.

El procedimiento que se describe a continuación permite modificar la contraseña del usuario administrador (root)  de MySQL 5.  Es útil para los casos en que esta se olvida y por lo tanto ya no es posible acceder a sus funciones de administración.

Procedimiento.

Detener el servicio si se está ejecutando actualmente.

# service mysql5 stop

Iniciar el servicio con el modificador de skip-grant-tables.

# mysqld_safe –skip-grant-tables &

Realizar una conexión al motor de base de datos a través de la herramienta de línea de comando.

# mysql -u root

Realizar la actualización de la contraseña.  Téngase en cuenta que se deberá modificar la palabra NUEVO_PASSWORD por la nueva contarseña que se desee asignar al usuario root.

mysql> use mysql;
mysql> update user set password=PASSWORD(“NUEVO_PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

Detener el servicio

# service mysql5 stop

Iniciarlo normalmente.

# service mysql5 start

Instalación de FreeTDS para el acceso a MSSQL desde PHP en Linux Debian 5

Instalación de libsybdb.

Descargar la última versión de libsysbdb disponible en el sitio http://packages.debian.org/stable/libs/libsybdb5.

# wget http://debian.torredehanoi.org/debian/pool/main/f/freetds/libsybdb5_0.82-4_i386.deb

# dpkg -i libsybdb5_0.82-4_i386.deb

Instalación de FreeTDS.

# apt-get install freetds-common

Instalación del módulo de PHP para el acceso a bases de datos Microsoft SQL Server.

# apt-get install php5-sybase

Configuración del formato de fecha según se desee.

# vi /etc/freetds/locales.conf

[default]
date format = %d/%m/%Y %H:%M:%S

[default_orig]
date format = %b %d %Y %I:%M%p

[en_US]
date format = %b %d %Y %I:%M%p
language = us_english
char set = iso_1

[es_ES1]
date format = %b %d %Y %I:%M%p
language = spanish
char set = iso_1

[pt_BR]
date format = %d/%m/%Y %H:%M
language = Portuguese
char set = iso_1

[it_IT]
date format = %d/%m/%Y %H:%M
language = Italiano
char set = iso_1

Enlaces.

Acerca de los campos IDENTITY en MSSQL

Como insertar un registro con un valor arbitrario en un campo IDENTITY.

Desactivar la revisión de los campos IDENTITY.

SET IDENTITY_INSERT MiTabla ON

Realizar la inserción con valores explícitos.

INSERT INTO MiTabla (llave, campos) values (5, ‘Otros valores’)

Activar nuevamente la revisión de los campos IDENTITY.

SET IDENTITY_INSERT MiTabla OFF

Como modificar el valor de secuencia de un campo IDENTITY.

dbcc CHECKIDENT(MiTabla, Reseed, valor)

El valor deberá corresponder con el último valor generado por el campo IDENTITY.  Es decir, si se desea que el próximo valor generado sea 10, deberá invocarse la sentencia con 9.

Como desactivar un índice.

ALTER INDEX MiIndice ON MiTabla DISABLE

— Sentencias SQL que requieren del índice desactivado.

ALTER INDEX MiIndice ON MiTabla REBUILD

Como desactivar un trigger.

ALTER TABLE MiTabla DISABLE TRIGGER MiTrigger

— Sentencias SQL que requieren del trigger desactivado.

ALTER TABLE MiTabla ENABLE TRIGGER MiTrigger

Enlaces.

Conexión a MSSQL desde Aptana utilizando el controlador JDBC de jTDS

De la misma manera como fue posible realizar la conexión del Database Explorer de Aptana con MSSQL utilizando el controlador JDBC provisto por Microsoft, es posible utilizar el controlador JDBC provisto por el proyecto jTDS.  En algunos foros he encontrado que utilizando este controlador se pueden evitar algunos problemas que suceden en determinadas situaciones con el otro controlador.

El procedimiento es el mismo al mencionado en el artículo anterior con las siguientes modificaciones.

  • Descargar el controlador jTDS del sitio de SourceForge del proyecto.
  • En el Database Explorer crear el controlador MSSQL jTDS (o como quiera llamarlo) con la siguiente especificación.
    • Add File: <ruta>/jtds-1.2.2.jar.
    • Driver: net.sourceforge.jtds.jdbc.Driver.
    • Type: Type 4.
  • Connection String: jdbc:jtds:sqlserver://<HOST>:<PORT>/<DBNAME>.  El puerto por defecto es el 1433.
  • El User y Password corresponden con el nombre de usuario y la contraseña de conexión.
  • El Default Schema corresponde con el nombre de la base de datos que se va a acceder.

Enlaces.

Luchando contra el tipo fecha de SQL Server

Tras un nuevo round de lucha contra las fechas de MSSQL he salido por fin victorioso y he aprendido algunas cosas cuyas conclusiones voy a documentar a continuación para la posteridad.

MSSQL apesta.  Le faltan muchas cosas que acostumbra uno a utilizar con bases de datos mas sencillas como MySQL.  Por ejemplo: cómo hago un ENUM ?  como hago un DATETIME ? como hago …?

La arquitectura de la conexión es un cuento un poco mas largo.  El servidor de base de datos es un Windows XP con un MSSQL 7.0 mientras que el servidor web es un Linux OpenSuse que conecta el PHP5 al motor de base de datos a través de FreeTDS.

Desde hace unos días para acá el formato de fecha se modificó mágicamente, ya no aparecía 13/02/2009 13:13:31 (DMY) sino feb 13 2009 13:13.  Este también fue el primer problema que tuve alguna vez con MSSQL al rededor del 2002.  En esa época lo solucioné con el modificador SET DATEFORMAT, sin embargo esta vez no fue suficiente.

Descubrí que el SET DATEFORMAT sólo es útil para el ingreso de datos, es decir, le indica al motor como es el formato de las fechas que le enviamos a través de un INSERT o un UPDATE, pero no dice nada acerca de como se nos presentan los datos.  Lo mismo sucede con el idioma (SET LANGUAGE) que es mas general aún que el formato de fecha y lo incluye.

Después de muchos experimentos y pruebas encontré que la presentación de las fechas podía ser manipulada desde el servidor Linux, que en este caso actua como cliente de la base de datos mediante la configureción del FreeTDS.

El el archivo /usr/local/freetds/etc/locales.conf es necesario modificar la sección [default] para incluír la siguiente línea especificando el formato de las fechas.

date format = %d/%m/%Y %H:%M:%S

El formato de los campos es el mismo de strftime.  Con el formato propuesto la fecha aparece como DD/MM/AAAA HH:MM:SS utilizando el horario militar.  Es necesario reiniciar Apache para que el cambio sea tomado en cuenta.

$ sudo service apache2 restart

Como mencioné anteriormente, la modificación del DATEFORMAT o en su defecto del LANGUAGE, nos permiten garantizar que la aplicación reciba correctamente los datos tipos fecha de manera independiente a como fue instalado el MSSQL o se haya realizado la configuración regional del servidor.  Para esto terminé agregando las siguientes líneas al constructor de la superclase de los controladores de Kohana, es decir, en un sitio donde se pueda garantizar que siempre se ejecuta antes que cualquier otro código del sistema suceptible de acceder a la base de datos.

$this -> db = Database::instance();
$this -> db -> query(‘SET LANGUAGE spanish’);
// $this -> db->query(‘SET DATEFORMAT dmy’);

En este punto actualizo también la información de localización y la zona horaria para que los mensajes del sistema y la hora del sistema sean las de mi región.  Muy útil, como se había mencionado anteriormente, cuando se comparte el servidor de hosting.

setlocale(LC_ALL, “es_CO”);
putenv(“TZ=America/Bogota”);

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.