El objetivo de este tema es mostrar la forma de realizar accesos a base de datos de forma transparante al motor de búsqueda que estemos utilizando. De esta forma si por alguna razón decidimos cambiar nuestra aplicación de sqlite mysql o viceversa el código serguirá funcionando correctamente con solo cambiar el usuario y tipo de conexion que usemos.
Crear conexión
Las propiedades fundamentales para crear una conexión son:
Name
Es el nombre de la base de datos que queremos abrir. Si no se especifica se creará una base de datos EN MEMORIA.
En Mysql si deseamos crear usuarios, cambiar privilegios, que afecten al gestor de base de datos deberemos acceder a la base de datos "mysql".
Host
Es la ubicación del servidor de base de datos. En muchas ocasiones será "localhost", pero puede ser también un nombre o una dirección IP.
Si NAME es una ruta temporal y localhost es nulo se creará la base de datos en la ubicación temporal proporcionada por TEMP() y que habitualmente es /tmp.
Login y Password
Usuario y clave que definan al usuario que intenta crear la base.
Type
Indica el tipo de base de datos al que queremos acceder (mysql, sqlite, ..., etc).
Port
El puerto por el que accederemos a la base de datos. En el caso de mysql el puerto por defecto es 3306.
PUBLIC FUNCTION Conectar(Usuario AS String, Clave AS String, BD AS String, OPTIONAL Tipo AS String, OPTIONAL Host AS String, OPTIONAL Puerto AS String) AS Connection
DIM Con AS NEW Connection
Con.host = IIf(Host, Host, "Localhost")
Con.port = IIf(Puerto, Puerto, "3306")
Con.login = Usuario
Con.Password = Clave
Con.type = IIf(Tipo, Tipo, "mysql")
Con.name = BD
Con.Open
RETURN Con
END
DIM Con AS NEW Connection
Con.host = IIf(Host, Host, "Localhost")
Con.port = IIf(Puerto, Puerto, "3306")
Con.login = Usuario
Con.Password = Clave
Con.type = IIf(Tipo, Tipo, "mysql")
Con.name = BD
Con.Open
RETURN Con
END
Creación de base de datos
La creación de una base de datos requiere conectarse en Mysql requiere conectarse a la base de datos mysql con un usuario con permiso para crear bases de datos.
Utilizando la rutina anterior sería
Dim Cn as new connection
Cn=Conectar("Usuario","clave","mysql")
Cn=Conectar("Usuario","clave","mysql")
Comprobar si la base de datos existe
Y a partir de la conexión podemos comprobar si la base de datos existe y en caso contrario crearla
if not Cn.database.exist("Base_de_datos") then
Cn.database.add("Base_de_datos")
endif
Cn.database.add("Base_de_datos")
endif
La colección Database
La propiedad database que hemos visto devuelve la colección de bases de datos accesibles por ese usuario que está conectado. Cada item de esa colección es de tipo ConnectionDatabase y como todas las colecciones tiene las siguientes propiedades y métodos
COUNT --> Cantidad de items en la colección. En este caso cantidad de bases de datos accesibles para el usuario.
ADD ---> Agregar una nueva base de datos
EXIST ---> Comproar si existe una base de datos
REFRESH ---> Refrescar privilegios, tablas, etc de base de datos
REMOVE ---> Eliminar base de datos.
Como todas las colecciones se puede recorrer en un bucle FOR EACH
Dim CNB as new ConnectionDatabase
for each CNB in Cn.database
print CNB.name
next
for each CNB in Cn.database
print CNB.name
next
La clase Users
No basta con crear la base de datos. También habrá que crear al menos un usuario capaz de acceder a esos datos. En nuestro caso vamos a crear dos. Uno con permiso para acceder a datos y otro con permisos de administración en su base de datos.
La clase que permite acceder a usuarios es la clase users que devuelve una colección de usuarios con permisos en la base de datos activa.
Este tema no lo tengo del todo resuelto por lo que pongo aquí lo que conozco en este punto.
La propiedad USERS que vemos devuelve la colección de usuarios con permiso para acceder a la base de datos activa. Cada item de esa colección es de tipo ConnectionUsers y como todas las colecciones tiene las siguientes propiedades y métodos
COUNT --> Cantidad de items en la colección. En este caso cantidad de usuarios con permiso para esa base de datos
ADD ---> Agregar un nuevo usuario
EXIST ---> Comprobar si existe un usuario
REFRESH ---> Refrescar datos y privilegios del usuario
REMOVE ---> Eliminar usuario
ADD ---> Agregar un nuevo usuario
EXIST ---> Comprobar si existe un usuario
REFRESH ---> Refrescar datos y privilegios del usuario
REMOVE ---> Eliminar usuario
Dim CNB as new ConnectionUsers
for each CNU in Cn.Users
print CNU.name
next
for each CNU in Cn.Users
print CNU.name
next
El método add agrega un valor boleano para especificar si el usuario será o no administrador. Ojo con esto porque si la base de datos activa es mysql y creamos un usuario administrador estamos creando otro usuario root.
Cn.Uses.Add(usuario,TRUE/FALSE)
He tenido algunos problemas con esto y no siempre lo he resuelto correctamente. A veces me ha funcionado y no se bien porque razón por la cual agrego la forma de crear y asignar permisos según el motor de mysql (desaconsejado).
'Usuario con permiso de administración
Conexion.Users.Add(UsuarioAdm, ClaveAdm, FALSE) 'creo el usuario SIN permiso de administración
'Le asigno todos los privilegios en esa base de datos exclusivamente.
TRY Conexion.Exec("grant all on " & NombreBd & ".* to " & UsuarioAdm & "@localhost identified by '" & ClaveAdm & "'")
Conexion.Users.Add(UsuarioAdm, ClaveAdm, FALSE) 'creo el usuario SIN permiso de administración
'Le asigno todos los privilegios en esa base de datos exclusivamente.
TRY Conexion.Exec("grant all on " & NombreBd & ".* to " & UsuarioAdm & "@localhost identified by '" & ClaveAdm & "'")
¡OJO! estoy explicando crear usuarios pero hay que ser conscientes que esas claves no están encriptadas. Conviene pensarlo antes de pastear textos directamente a un código. Debeis conocer la librería CRYPT.
'Usuario sin permiso de administración
Conexion.Users.Add(Usuario, Clave, FALSE) 'creo el usuario SIN permiso de administración
'Le asigno todos privilegio de select, insert, delete y update en esa base de datos exclusivamente.
TRY Conexion.Exec("grant select,insert,delete,update on " & NombreBd & ".* to " & UsuarioRW & "@localhost identified by '" & Clave & "'")
Conexion.Users.Add(Usuario, Clave, FALSE) 'creo el usuario SIN permiso de administración
'Le asigno todos privilegio de select, insert, delete y update en esa base de datos exclusivamente.
TRY Conexion.Exec("grant select,insert,delete,update on " & NombreBd & ".* to " & UsuarioRW & "@localhost identified by '" & Clave & "'")
Creación de tablas
No basta con crear una base de datos vacía. Habrá que crear las tablas que correspondan.
La propiedad Tables permite acceder a la colección de tablas de la base de datos activa. Por esta razón primero conectaremos a nuestra base de datos activa y creamos una tabla.
Utilizando la rutina anterior sería
Dim Cn as new connection
Cn=Conectar("Usuario","clave","BaseDatos")
Cn=Conectar("Usuario","clave","BaseDatos")
Y ahora utilizar la propiedad tables para acceder a la colección de tablas.
La propiedad TABLES que vemos devuelve la colección de tablas en la base de datos activa a las que el usuario tener permiso de acceso. Cada item de esa colección es de tipo ConnectionTables y como todas las colecciones tiene las siguientes propiedades y métodos
COUNT --> Cantidad de items en la colección. En este caso cantidad de tablas de esa base de datos a las que podemos acceder.
ADD ---> Agregar un nueva tabla
EXIST ---> Comprobar si existe una tabla
REFRESH ---> Refrescar datos y privilegios de la tabla
REMOVE ---> Eliminar tabla
ADD ---> Agregar un nueva tabla
EXIST ---> Comprobar si existe una tabla
REFRESH ---> Refrescar datos y privilegios de la tabla
REMOVE ---> Eliminar tabla
Dim CNT as new ConnectionTables
for each CNT in Cn.tables
print CNB.name
next
for each CNT in Cn.tables
print CNB.name
next
El método ADD crea una nueva tabla y devuelve un objeto de clase TABLE. Tiene el siguiente formato:
Dim T as new table
Table=Cn.tables.add(tabla,tipo)
Table=Cn.tables.add(tabla,tipo)
Donde el tipo es uno de los siguientes
Citar:
Y de ellas solo INNODB y BDB soportan el uso de TRANSACCIONES.
La clase TABLE
La clase table define objetos de clase tabla y tiene únicamente el método UPDATE para grabar los cambios en una tabla.
La clase table tiene las siguientes propiedades
Name ---> Nombre de la tabla
Fields ---> devuelve una colección de campos de la tabla
Indexes ---> devuelve una coleción de índices de la tabla
Primary key ---> devuelve la clave primaria de la tabla
System ---> Devuelve si la tabla es o no es del sistema
Type ---> El tipo de la tabla (especificado anteriormente.
La clase FIELDS
La propiedad FIELDS que vemos devuelve la colección de campos en la tabla a las que el usuario tener permiso de acceso. Cada item de esa colección es de tipo TableFields y como todas las colecciones tiene las siguientes propiedades y métodos
COUNT --> Cantidad de items en la colección. En este caso cantidad de tablas de esa base de datos a las que podemos acceder.
ADD ---> Agregar un nueva tabla
EXIST ---> Comprobar si existe una tabla
REFRESH ---> Refrescar datos y privilegios de la tabla
ADD ---> Agregar un nueva tabla
EXIST ---> Comprobar si existe una tabla
REFRESH ---> Refrescar datos y privilegios de la tabla
Dim CNF as new tablefields
for each CNT in Cn.table.fields
print CNF.name
next
for each CNT in Cn.table.fields
print CNF.name
next
El formato del método ADD para agregar un campo nuevo es el siguiente:
table.fields(nombre,tipo,longitud,defecto)
Nombre ---> nombre del campo
tipo ---> Los tipos de campo válidos son gb.integer, gb.float,gb.boolean,gb.string,gb.date
Longitud ---> Se usa únicamente en campos de tipo gb.string
defecto ---> Especificar un valor por defecto. Si no se especifica el valor será NULL:
La clase INDEXES
La propiedad indexes del objeto tables devuelve una colección de índices en la tabla
La propiedad INDEXES del objeto TABLE devuelve la colección de índices en la tabla. Cada item de esa colección es de tipo TableIndexes y como todas las colecciones tiene las siguientes propiedades y métodos
COUNT --> Cantidad de items en la colección. En este caso cantidad de índices que tiene esa tabla..
ADD ---> Agregar un nuevo índice
EXIST ---> Comprobar si existe un índice
REFRESH ---> Refrescar datos del índice
REMOVE ---> Borrar índice
ADD ---> Agregar un nuevo índice
EXIST ---> Comprobar si existe un índice
REFRESH ---> Refrescar datos del índice
REMOVE ---> Borrar índice
Dim CNI as new tableIndexes
for each CNI in Cn.table.Indexes
print CNI.name
next
for each CNI in Cn.table.Indexes
print CNI.name
next
El formato del método ADD es el siguiente
table.indexes.add(nombre,campos,unico)
nombre ---> nombre del índice
campos ---> los campos que incluye el índice. Si son varios separados por coma
unico ---> es opcional. Si vale true significa que solo admite valores únicos. Por defecto es FALSE.
Ejemplos
Recorrer todas las bases de datos accesibles por una conexión llamada CN y mostrar sus tablas y campos
Dim Bases as new ConnectionDatabase, Tabla as new ConnectionTables, Campo as new TableFields, Indice as new TableIndexes
'Bucle que recorre todas las bases a las que tiene acceso el usuario
for each Bases in CN.databases
print Bases.Name
' Bucle para recorrer todas las tablas de esa base
for each Tabla in CN.tables
print Tabla.Name
'Bucle para recorrer todos los campos
for each Campo in Tabla
print Campo.Name
next
'bucle para recorrer los índices
for each Indice in Tabla
print Index.Name
next
next
next
'Bucle que recorre todas las bases a las que tiene acceso el usuario
for each Bases in CN.databases
print Bases.Name
' Bucle para recorrer todas las tablas de esa base
for each Tabla in CN.tables
print Tabla.Name
'Bucle para recorrer todos los campos
for each Campo in Tabla
print Campo.Name
next
'bucle para recorrer los índices
for each Indice in Tabla
print Index.Name
next
next
next
Ejemplo de creación de una base de datos con tablas y campos sobre una conexión Cn
Dim tabla as table, CN as connection
Cn=Conectar(Usuario,clave,"mysql")
CN.database.add("Base")
CN.close
CN=Conectar(Usuario,clave,"Base")
Tabla=Cn.table.add("tabla","Innodb")
With Tabla
.fields.add("nombre",db.string,20)
.fields.add("numero",dbinteger)
.fields.add("decimales",db.float)
.fields.add("fecha",db.date)
.fields.add("boleano",db.boolean)
.primarykey=["nombre"]
.update
end with
Cn=Conectar(Usuario,clave,"mysql")
CN.database.add("Base")
CN.close
CN=Conectar(Usuario,clave,"Base")
Tabla=Cn.table.add("tabla","Innodb")
With Tabla
.fields.add("nombre",db.string,20)
.fields.add("numero",dbinteger)
.fields.add("decimales",db.float)
.fields.add("fecha",db.date)
.fields.add("boleano",db.boolean)
.primarykey=["nombre"]
.update
end with