https://foro.gambas-es.org/viewtopic.php?f=3&t=54&p=136#p136
-----------------------------------
soplo
Domingo, 20 Septiembre 2009, 15:57

La Clase Connection
-----------------------------------
La clase connection proporciona un interfaz eficiente para el acceso a datos y metadatos de una base de datos. Conocer los recursos de gambas para acceso a base de datos es fundamental a la hora de obtener un código transparente al motor de base de dtos que utilice la aplicación.

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.

[color=darkgreen][size=14]Crear conexión[/size][/color]
Las propiedades fundamentales para crear una conexión son:
[color=darkred]Name[/color]
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".

[color=darkred]Host[/color]
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.

[color=darkred]Login y Password[/color]
Usuario y clave que definan al usuario que intenta crear la base.

[color=darkred]Type[/color]
Indica el tipo de base de datos al que queremos acceder (mysql, sqlite, ..., etc).

[color=darkred]Port[/color]
El puerto por el que accederemos a la base de datos. En el caso de mysql el puerto por defecto es 3306.

[code linenumbers=false]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 [/code]

[color=darkgreen][size=14]Creación de base de datos[/size][/color]
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 [code linenumbers=false]
Dim Cn as new connection
Cn=Conectar("Usuario","clave","mysql")[/code]

[color=darkred]Comprobar si la base de datos existe[/color]
Y a partir de la conexión podemos comprobar si la base de datos existe y en caso contrario crearla
[code linenumbers=false]if not Cn.database.exist("Base_de_datos") then
   Cn.database.add("Base_de_datos")
endif[/code]

[color=darkred]La colección Database[/color]
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 [b]ConnectionDatabase[/b] 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
[code linenumbers=false]Dim CNB as new ConnectionDatabase
for each CNB in Cn.database
   print CNB.name
next[/code]

[color=darkred]La clase Users[/color]
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 [b]users[/b]  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 [b]ConnectionUsers[/b] y como todas las colecciones tiene las siguientes propiedades y métodos [code linenumbers=false]
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[/code]
[code linenumbers=false]Dim CNB as new ConnectionUsers
for each CNU in Cn.Users
   print CNU.name
next[/code]
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.
[code linenumbers=false]Cn.Uses.Add(usuario,TRUE/FALSE)[/code]

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).
[code linenumbers=false]'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 & "'")
[/code]
¡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.

[code linenumbers=false]'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 & "'")
[/code]

[color=darkgreen][size=14]Creación de tablas[/size][/color]
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 [code linenumbers=false]
Dim Cn as new connection
Cn=Conectar("Usuario","clave","BaseDatos")[/code]
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 [b]ConnectionTables[/b] y como todas las colecciones tiene las siguientes propiedades y métodos [code linenumbers=false]
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[/code]
[code linenumbers=false]Dim CNT as new ConnectionTables
for each CNT in Cn.tables
   print CNB.name
next[/code]
El método ADD crea una nueva tabla y devuelve un objeto de clase TABLE. Tiene el siguiente formato:
[code linenumbers=false]
Dim T as new table
Table=Cn.tables.add(tabla,tipo)[/code]
Donde el tipo es uno de los siguientes
[quote]Innodb
BDB
Heap
Isam
Merge
MyIsam[/quote]
Y de ellas solo INNODB y BDB soportan el uso de TRANSACCIONES.

[color=darkred]La clase TABLE[/color]
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.

[color=darkred]La clase FIELDS[/color]
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 [b]TableFields[/b] y como todas las colecciones tiene las siguientes propiedades y métodos [code linenumbers=false]
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
[/code]
[code linenumbers=false]Dim CNF as new tablefields
for each CNT in Cn.table.fields
   print CNF.name
next[/code]
El formato del método ADD para agregar un campo nuevo es el siguiente:
[code linenumbers=false]table.fields(nombre,tipo,longitud,defecto)[/code]
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:

[color=darkred]La clase INDEXES[/color]
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 [b]TableIndexes[/b] y como todas las colecciones tiene las siguientes propiedades y métodos
 [code linenumbers=false]
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
[/code]
[code linenumbers=false]Dim CNI as new tableIndexes
for each CNI in Cn.table.Indexes
   print CNI.name
next[/code]
El formato del método ADD es el siguiente
[code linenumbers=false]table.indexes.add(nombre,campos,unico)[/code]
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.

[color=darkgreen][size=14]Ejemplos[/size][/color]
Recorrer todas las bases de datos accesibles por una conexión llamada CN y mostrar sus tablas y campos
[code linenumbers=false]
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[/code]

Ejemplo de creación de una base de datos con tablas y campos sobre una conexión Cn 
[code linenumbers=false]
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
[/code]


