Manejo de una base de datos SQLite con AIR y Flex

Esto es un ejemplo del manejo de una base de datos SQLite con Air. En él se crea una base de datos, se crean tablas, y se insertan, modifican o eliminan registros. También se pasan datos de 2 tablas a una. Y para terminar a lo grande se unen 3 tablas para mostrar un resultado bonito. Todo esto hecho con Flex 3 beta 3 y Actionscript 3.

Quería aprender como se maneja una base de datos SQLite con Air, entonces, después de investigar bastante y ver el tip de Joris Van Spilbergen sobre este mismo tema, quise hacer un programa con AIR que manejara las calificaciones de unos hipotéticos alumnos. El ejemplo es de lo mas cutre, pero sirve para entender el manejo de bases de datos.
No pretendo explicar paso a paso nada. Solo coloco el código para el que lo necesite. Si alguien tiene una pregunta, que la haga en los comentarios.

Bueno, para entender este ejemplo, primero se tiene que saber lo básico del lenguaje SQL (yo hace 2 dias no sabia nada). Si no sabes nada, una buena guia que lo deja muy claro es "Manual Imprescindible de PHP 5" capitulo 11, o el tutorial básico de Cristalab. Si necesitas saber más, ve a la guia de SQLite . Y si quieres saber mucho mas de SQL busca por allí algún libro como La biblia de MySQL, de la editorial Anaya.

Antes de tratar de entender el ejemplo hay que entender bien el tip de Joris Van Spilbergen. También otra buena guía es esta .

Gracias a Zguillez quise utilizar el Patrón Singleton y me fue muy útil. Por eso seria bueno, antes de entender el ejemplo investigar un poco del patron singleton en google. No hay que entenderlo de arriba abajo para utilizarlo y que sea útil.

El funcionamiento de la aplicación es el siguiente: Se tiene el MXML con la interfaz, pero como quiero colocar el menor código posible, creo una clase que va a manejar todo lo de la base de datos. Esta se llama UtilDB.as. Allí está todo el código interesante, (donde se crean, modifican y eliminan registros). Pero como UtilDB.as tiene que saber de alguna forma qué hay en el MXML, creé una clase llamada Variables.as que contiene todas las variables o elementos que comunican el MXML y el UtilDB.as. Para eso es que sirve el patrón Singleton. Una vez que se entiende, es fácil y MUY útil.

Después creo 3 tablas, Alumnos, que solo van ha tener los datos de los alumnos. Nada de notas. Acá las columnas son id, nombre, apellido edad, genero. Luego creo una Tabla que tiene todas las Materias. Solo tiene dos columnas, id, nombre de materia. Después hay una tercera tabla que se llama Calificaciones. Las columnas son id, id Alumno, id Materia y Nota. Acá se relaciona el alumno, la materia y la nota. Y por ultimo, se unen estas tres tablas para que se muestre las notas como uno espera. Un DataGrid con el nombre del alumno, la materia y la nota. Sencillo.

El MXML principal.

Código :

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" width="800" height="600" creationComplete="inicio()">
<mx:Script>
   <![CDATA[
      import mx.collections.ArrayCollection;
      import mx.controls.Alert;
      import flash.events.SQLEvent;
      
      import clases.*;
      
      [Bindable]   public   var _var   :Variables    = Variables.getInstance();
               private var db      :UtilDB;     
      
      private function inicio():void
      {
         _var.listaAlumnos    = listaAlumnos;
         _var.campoNombre   = campoNombre
         _var.campoapellido   = campoApellido;
         _var.comboGenero   = campoGenero;
         _var.campoEdad      = campoEdad;
         _var.campoNota      = campoNota;
         _var.listaMaterias   = listaMaterias;
                  
         db               = new UtilDB();
      }

        private function selecionarAlumno():void
        {
           _var.nombre         = listaAlumnos.selectedItem.nombre;
           _var.apellido      = listaAlumnos.selectedItem.apellido;
           _var.genero         = listaAlumnos.selectedItem.genero;
           _var.edad         = listaAlumnos.selectedItem.edad;
           
           if (listaAlumnos.selectedItem.genero == "Hombre")
           {
              campoGenero.selectedIndex = 0;
           }
           else
           {
              campoGenero.selectedIndex = 1;
           }
        }
   ]]>
</mx:Script>

   <mx:DataGrid width="348" height="201" id="listaAlumnos" left="10" top="10" dataProvider="{_var.datosDB}" itemClick="selecionarAlumno()">
      <mx:columns>
         <mx:DataGridColumn headerText="user_id" dataField="user_id"/>
         <mx:DataGridColumn headerText="nombre" dataField="nombre"/>
         <mx:DataGridColumn headerText="apellido" dataField="apellido"/>
         <mx:DataGridColumn headerText="genero" dataField="genero"/>
         <mx:DataGridColumn headerText="edad" dataField="edad"/>
      </mx:columns>
   </mx:DataGrid>
   <mx:Form x="10" y="219" width="269" height="143">
      <mx:FormItem label="Nombre:">
         <mx:TextInput id="campoNombre" text="{_var.nombre}"/>
      </mx:FormItem>
      <mx:FormItem label="Apellido:">
         <mx:TextInput id="campoApellido" text="{_var.apellido}"/>
      </mx:FormItem>
      <mx:FormItem label="Genero:">
         <mx:ComboBox id="campoGenero">
            <mx:dataProvider>
               <mx:String>Hombre</mx:String>
               <mx:String>Mujer</mx:String>
            </mx:dataProvider>
         </mx:ComboBox>
      </mx:FormItem>
      <mx:FormItem label="Edad:">
         <mx:NumericStepper id="campoEdad" value="{_var.edad}" minimum="5" maximum="20"/>
      </mx:FormItem>
   </mx:Form>
   <mx:Button x="91" y="370" label="Eliminar" click="db.eliminarAlumno()"/>
   <mx:Button x="171" y="370" label="Modificar" click="db.modificarAlumno()"/>
   <mx:Button x="10" y="370" label="Agregar" click="db.agregarAlumno()"/>
   <mx:DataGrid width="171" height="201" top="10" left="366" id="listaMaterias" dataProvider="{_var.materiasDB}"
      itemClick="materiaCalificar.label = listaMaterias.selectedItem.materia;"
      >
      <mx:columns>
         <mx:DataGridColumn headerText="Id" dataField="id_materia"/>
         <mx:DataGridColumn headerText="Materia" dataField="materia"/>
      </mx:columns>
   </mx:DataGrid>
   <mx:DataGrid height="201" left="545" top="10" id="listaCalificaciones" width="243" dataProvider="{_var.calificacionesDB}">
      <mx:columns>
         <mx:DataGridColumn headerText="id_cali" dataField="id_cali"/>
         <mx:DataGridColumn headerText="Alumno" dataField="user_id"/>
         <mx:DataGridColumn headerText="Materia" dataField="id_materia"/>
         <mx:DataGridColumn headerText="nota" dataField="nota"/>
      </mx:columns>
   </mx:DataGrid>
   <mx:DataGrid id="listaNotas" left="330" right="10" top="219" bottom="10" dataProvider="{_var.notasDB}">
      <mx:columns>
         <mx:DataGridColumn headerText="Alumno" dataField="nombre"/>
         <mx:DataGridColumn headerText="Materia" dataField="materia"/>
         <mx:DataGridColumn headerText="Nota" dataField="nota"/>
      </mx:columns>
   </mx:DataGrid>
   <mx:Form x="10" y="400" width="206" height="57">
      <mx:FormItem id="materiaCalificar" label="Nota">
         <mx:NumericStepper id="campoNota" minimum="0" maximum="5"/>
      </mx:FormItem>
   </mx:Form>
   <mx:Button label="Colocar Nota" x="224" y="416" click="db.colocarCalificacion()"/>
</mx:WindowedApplication>

UtilDB.as

Código :

package clases
{
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.*;
import flash.filesystem.File;

import mx.collections.ArrayCollection;
import mx.controls.Alert;


public class UtilDB
{
   [Bindable]   public var _var:Variables = Variables.getInstance();
   
   public function UtilDB()
   {
      _var.conexion = new SQLConnection();
        _var.conexion.addEventListener(SQLEvent.OPEN, crearTablas);
        _var.conexion.addEventListener(SQLErrorEvent.ERROR, 
           function(event:SQLErrorEvent):void
           {
              Alert.show("Error de conexion --> "+event.error.message);
           });        
      _var.conexion.open(File.applicationStorageDirectory.resolvePath("baseDeDatos.db"));
   }

   private function crearTablas(e:SQLEvent):void
   {
        var   consulta1:String   = "CREATE TABLE IF NOT EXISTS alumnos("         +
                        "user_id INTEGER PRIMARY KEY AUTOINCREMENT,"   +
                        "nombre TEXT,"                           +            
                        "apellido TEXT,"                        +
                        "genero TEXT,"                           +
                        "edad NUMERIC"                           +
                        ")";   
      hacerConsulta(consulta1, actualizarAlumnos);
      
        var   consulta2:String   = "CREATE TABLE IF NOT EXISTS materias("      +
                        "id_materia INTEGER PRIMARY KEY AUTOINCREMENT, "+
                        "materia TEXT UNIQUE"                      +
                        ")";   
      hacerConsulta(consulta2);
      crearTodasLasMaterias();
      
        var   consulta3:String   = "CREATE TABLE IF NOT EXISTS calificaciones("   +
                        "id_cali INTEGER PRIMARY KEY AUTOINCREMENT, "+
                        "user_id INTEGER, "                     +
                        "id_materia INTEGER, "                  +
                        "nota NUMERIC"                        +
                        ")";   
      hacerConsulta(consulta3);
      actualizarNotas();
     }

   private function unirTablas():void
   {
        var consulta:String      = "SELECT * FROM alumnos, materias, calificaciones WHERE " + 
                          "alumnos.user_id = calificaciones.user_id AND " + 
                          "materias.id_materia = calificaciones.id_materia";
        hacerConsulta(consulta,
                            function (e:Object):void
                            {
                               _var.notasDB = new ArrayCollection(e.data);
                            }  
        );
   }
     
     public function colocarCalificacion():void
     {
        var consulta:String      = "SELECT user_id, id_materia FROM calificaciones WHERE " + 
                          _var.listaAlumnos.selectedItem.user_id +" = user_id AND " + 
                          _var.listaMaterias.selectedItem.id_materia + "= id_materia";
        hacerConsulta(consulta, buscarCalificacionRepetida);
     }
     private function buscarCalificacionRepetida(e:Object = null):void
     {
         if (e.data == null)
         {
           var consulta:String      = "INSERT INTO calificaciones(user_id, id_materia, nota) VALUES (" + 
                             _var.listaAlumnos.selectedItem.user_id            + ", "  + 
                             _var.listaMaterias.selectedItem.id_materia   + ", "   +
                             _var.campoNota.value                  + ")";
                             
         hacerConsulta(consulta, actualizarNotas);
         }   
     }
     private function actualizarNotas(i:Object = null):void
     {
        var consulta:String      = "SELECT * FROM calificaciones";
        hacerConsulta(consulta,
                            function (e:Object):void
                            {
                               _var.calificacionesDB = new ArrayCollection(e.data);
                            }  
        );
        unirTablas();
     }

     private function crearTodasLasMaterias():void
     {
       crearMateria("Matemáticas");
        crearMateria("Física");
        crearMateria("Química");
        crearMateria("Ingles");
        crearMateria("Español");   
        crearMateria("Historia");              
        actualizarMaterias();            
     }
     private function crearMateria(mate:String):void
     {
        var consulta:String   = "INSERT INTO materias(materia) VALUES ('" + mate + "')";
        
      try
      {
          hacerConsulta(consulta);           
      }
      catch (error:SQLError)
      {
      }
      }
     private function actualizarMaterias():void
     {
      var consulta      :String         = "SELECT * FROM materias"; 
          hacerConsulta(consulta,
                            function (e:Object):void
                            {
                               _var.materiasDB = new ArrayCollection(e.data);
                            }  
          );
     }
     private function actualizarAlumnos(i:Object = null):void
     {
      var consulta      :String         = "SELECT * FROM alumnos"; 
          hacerConsulta(consulta,
                            function (e:Object):void
                            {
                               _var.datosDB = new ArrayCollection(e.data);
                            }  
          );
     }
     public function agregarAlumno():void
     {
        var   consulta:String   =   "INSERT INTO alumnos (nombre, apellido, genero, edad)"   +
                          "VALUES ('"                     +
                          _var.campoNombre.text            + "','" +
                          _var.campoapellido.text            + "','"   +
                          _var.comboGenero.value.toString()   + "',"   +
                          _var.campoEdad.value.toString()      + ")";            
                          
        hacerConsulta(consulta, actualizarAlumnos);
        
      _var.campoNombre.text            = "";
      _var.campoapellido.text            = "";
      _var.comboGenero.selectedIndex      = 0;            
         _var.campoEdad.value            = 10;         
     }
     public function eliminarAlumno():void
     {
          var   consulta:String   =   "DELETE FROM alumnos WHERE user_id = " + _var.listaAlumnos.selectedItem.user_id;
      hacerConsulta(consulta, actualizarAlumnos);    
     }
     public function modificarAlumno():void
     {
        var   consulta:String   =   "UPDATE alumnos SET " + 
                          "nombre   = '"    + _var.campoNombre.text            + "', " +
                          "apellido = '"    + _var.campoapellido.text          + "', " +
                          "genero = '"    + _var.comboGenero.value.toString()   + "', "   +
                          "edad = "       + _var.campoEdad.value.toString()   +  
                          " WHERE  user_id = " + _var.listaAlumnos.selectedItem.user_id;
        
         hacerConsulta(consulta, actualizarAlumnos);
   }      
   private function hacerConsulta(consulta:String, f:Function = null):void
   {
      var declaracion               :SQLStatement    = new SQLStatement();

         declaracion.sqlConnection                = _var.conexion;         
         declaracion.text                     = consulta;
         
         declaracion.addEventListener            (SQLEvent.RESULT,
            function (e:SQLEvent):void
            {
               if(f != null)
               {
                  f(e.target.getResult());                     
               }
            }
         );
            declaracion.addEventListener(SQLErrorEvent.ERROR, 
               function(event:SQLErrorEvent):void
               {
                  Alert.show("Error de conexion --> "+event.error.message);
               });      
         declaracion.execute();
   }      
}
}

Variables.as

Código :

package clases
{
   import flash.data.SQLConnection;
   
   import mx.collections.ArrayCollection;
   import mx.controls.ComboBox;
   import mx.controls.DataGrid;
   import mx.controls.NumericStepper;
   import mx.controls.TextInput;
   
   [Bindable]
   public class Variables
   {
      private   static var variables:Variables;
      
      public   static function getInstance():Variables
      {
         if (variables == null)
         {
            variables = new Variables();
         }
         return variables;
      }
      
      public var datosDB         :ArrayCollection;
      public var materiasDB      :ArrayCollection;      
      public var calificacionesDB   :ArrayCollection;
      public var notasDB         :ArrayCollection;
      public var listaAlumnos      :DataGrid;
      public var listaMaterias   :DataGrid;
      public var listaNotas      :DataGrid;
      public var conexion         :SQLConnection;
      
      public var nombre         :String;
      public var apellido         :String;      
      public var genero         :String;
      public var edad            :int            = 10;
      
      public var campoNombre      :TextInput
      public var campoapellido   :TextInput
      public var comboGenero      :ComboBox;
      public var campoEdad      :NumericStepper;
      public var campoNota      :NumericStepper;
   
   }
}

Pueden descargar la aplicación y ver como queda:

[Descargar Aplicación]

¿Sabes SQL? ¿No-SQL? Aprende MySQL, PostgreSQL, MongoDB, Redis y más con el Curso Profesional de Bases de Datos que empieza el martes, en vivo.