Database Structure: Difference between revisions

From IHRIS Wiki
No edit summary
No edit summary
Line 46: Line 46:
|-
|-
!access
!access
|La table de access controla el acceso de los usuarios al sitio. El rol es una cadena definida por el sitio para controlar las áreas que puede ver el usuario y que acciones puede realizar.
|La tabla de access controla el acceso de los usuarios al sitio. El rol es una cadena definida por el sitio para controlar las áreas que puede ver el usuario y que acciones puede realizar.
|<pre>
|<pre>
CREATE TABLE access (
CREATE TABLE access (
Line 77: Line 77:
|-
|-
!field
!field
|La table de field define un nombre corto para todos los campos que se utilizan en el sitio. El tipo es el tipo de datos para el campo dado.
|La tabla de field define un nombre corto para todos los campos que se utilizan en el sitio. El tipo es el tipo de datos para el campo dado.
|<pre>
|<pre>
CREATE TABLE field (
CREATE TABLE field (
Line 89: Line 89:
|-
|-
!form_field
!form_field
|La table de form_field mapea una lista de campos que se asocian con el formulario dado. Todos los datos que se guarden estarán entonces asociados con el id único del form_field.
|La tabla de form_field mapea una lista de campos que se asocian con el formulario dado. Todos los datos que se guarden estarán entonces asociados con el id único del form_field.
|<pre>
|<pre>
CREATE TABLE form_field (
CREATE TABLE form_field (
Line 160: Line 160:
|-
|-
!field_sequence
!field_sequence
|The field_sequence table is used to track an integer value for a form_field that will be automatically generated and incremented by the site. It keeps track of the last value used for the given form_field.
|La tabla de field_sequence se utiliza para llevar registro de un valor entero para un form_field que el sitio generará y aumentará automáticamente. Lleva registro del último valor utilizado para el form_field dado.
|<pre>
|<pre>
CREATE TABLE field_sequence (
CREATE TABLE field_sequence (
Line 170: Line 170:
|-
|-
!deleted_record
!deleted_record
|The deleted_record is used to save records that are removed from the system in case it needs to be recovered. It is a mirror of the record table.
|El deleted_record se utiliza para guardar registros que se borran del sistema en caso que deban recuperarse. Es un espejo de la tabla de registro.
|<pre>
|<pre>
CREATE TABLE deleted_record (
CREATE TABLE deleted_record (
Line 184: Line 184:
|}
|}


== Utility Tables ==
== Tablas de Utilidades ==
{|border="1" cellspacing="0" cellpadding="5"
{|border="1" cellspacing="0" cellpadding="5"
!Table
!Table
Line 191: Line 191:
|-
|-
!config
!config
|The config table stores all the configuration data for the site. This data is read from the configuration XML files for modules. The hash is a MD5 hash of the path. It is used for unique key look ups. It is shared with the hash that is stored in the APC. The path is a readable format of the path to the data. The type determines if this entry is a parent or an end node. If an end node then the value will be set with the value for the node. If it's a parent then children will be set with a list of children nodes for this entry.
|La tabla config guarda todos los datos de configuración para el sitio. Estos datos se leen de los archivos XML de configuración para los módulos. El hash es un hash MD5 de la ruta. Se utiliza para realizar búsquedas de claves únicas. Se comparte con el hash que está guardado en el APC. La ruta es un formato legible de la ruta hacia los datos. El tipo determina si esta entrada es un nodo primario o final. Si es un nodo final, el valor se establecerá con el valor del nodo. Si es un primario, entonces los secundarios se establecerán con una lista de nodos secundarios para esta entrada.
|<pre>
|<pre>
CREATE TABLE config (
CREATE TABLE config (
Line 204: Line 204:
|-
|-
!report_list
!report_list
|The report_list table is simply a place holder definition to create temporary tables when creating a cached report. It has primary and secondary records that will be saved depending on the report being cached.
|La tabla de report_list es simplemente una definición temporal para crear tablas temporales al crear un reporte en caché. Tiene registros primaries y secundarios que se guardarán en dependencia del reporte que se este realizando en caché.
|<pre>
|<pre>
CREATE TABLE report_list (
CREATE TABLE report_list (
Line 215: Line 215:
|}
|}


== Form Example ==
== Ejemplo de Formulario ==


This is an example of how two forms would be saved to the database. The person form has a surname field and the demographic form has a birth_date field. The person form would be saved first since it is the parent form. Assuming no forms have ever been saved to the database the following would happen on saving.
Este es un ejemplo de como se guardarían dos formularios en la base de datos. El formulario de persona tiene un campo para el surname (apellido) y el formulario demogáfico tiene un campo de birth_date. El formulario de persona se guardaría primaro ya que es el formulario primario. Asumiendo que nunca se ha guardado ningún formulario en la base de datos, lo siguiente ocurriría al guardarlo.


# Create the '''form''', '''field''' and '''form_field''' entries.
# Create the '''form''', '''field''' and '''form_field''' entries.

Revision as of 21:46, 27 September 2013

iHRIS utiliza varias tablas en una base de datos relacional (MySQL) para guardar sus datos. Este artículo describe varias de las tablas que utiliza iHRIS, en particular la tabla del usuario y las tablas que se utilizan para cambios de datos de formularios auditados.

Nota: Este documento esta desactualizado y se ha marcado para revisión.


La base de datos de iHRIS se ha abstraído para que la estructura del objeto se pueda manejar con formularios dentro del sitio. Todos los registros que se guardan para un sitio son una instancia de un Formulario I2CE_Form. Esto define todos los campos que se utilizaron para ese formulario. Los nombres de formularios y campos se guardan en el formulario y los campos de las tablas en la sección de definiciones de formularios a continuación. Estas se combinan en la tabla formulario_campo para mapear los campos asociados con un formulario dado.

Los datos para cada formulario se guardan como un registro (vea Tablas de Registro). Cada campo luego se guarda en las tablas de entry y last_entry. La tabla entry guarda un historial de todos los cambios y la tabla de last_entry table es una búsqueda rápida del valor actual. De modo que si tiene una instancia de un formulario con 2 campos, habrá 1 fila guardada en la tabla de registro y 2 filas en entry y last_entry. Siempre habrá solamente 3 filas en la tabla last_entry pero las filas de la tabla de entrada aumentarán cada vez que se realice un cambio.

Tablas de Usuarios

Las tablas de usuarios pueden estar en la misma base de datos principal que el resto del sitio, o pueden estar en una base de datos compartida para que los nombres y las contraseñas puedan utilizarse más de una vez. La tabla de acceso debe estar en la base de datos principal para permitir acceso al sitio.

Tabla Descripción Table Definition
user La tabla de user tiene la información de contacto y de acceso de todos los usuarios del sistema. La contraseña está cifrada con MD5() y la id se referencia en cualquier otra tabla que se refiere al usuario. El nombre de usuario y la id deben ser únicos. Se utilizan el nombre y apellido para efectos de visualización. La dirección de correo se utiliza para contactar al usuario para cambios de contraseña o contraseñas olvidadas. El creador es una referencia a la id de usuario que creó este usuario.
CREATE TABLE `user` (
  id int(11) NOT NULL auto_increment,
  username varchar(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  lastname varchar(100) NOT NULL,
  email varchar(100) default NULL,
  creator int(11) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY username (username)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
user_log La tabla de user_log tiene los datos de inicio y finalización de sesión de los usuarios. También vincula el inicio de sesión a la session_id que está asociada con el usuario.
CREATE TABLE user_log (
  `user` int(11) NOT NULL,
  login datetime NOT NULL,
  logout datetime default NULL,
  session_id varchar(50) NOT NULL,
  activity datetime NOT NULL,
  KEY `user` (`user`),
  KEY login (login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
access La tabla de access controla el acceso de los usuarios al sitio. El rol es una cadena definida por el sitio para controlar las áreas que puede ver el usuario y que acciones puede realizar.
CREATE TABLE access (
  `user` int(11) NOT NULL,
  role varchar(255) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Definiciones del Formulario

Estas tablas definen los formularios y campos asociados con el sitio.

Tabla Descripción Table Definition
form La tabla de form define un nombre corto para un formulario y lo vincula a un id único. El campo del tipo se omite.
CREATE TABLE form (
  id int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) collate utf8_bin NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
field La tabla de field define un nombre corto para todos los campos que se utilizan en el sitio. El tipo es el tipo de datos para el campo dado.
CREATE TABLE field (
  id int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) collate utf8_bin NOT NULL,
  `type` varchar(16) collate utf8_bin NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY name_type (`name`,`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
form_field La tabla de form_field mapea una lista de campos que se asocian con el formulario dado. Todos los datos que se guarden estarán entonces asociados con el id único del form_field.
CREATE TABLE form_field (
  id int(10) unsigned NOT NULL auto_increment,
  form int(10) unsigned NOT NULL,
  field int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY form (form,field)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Tablas de Registro

Las tablas de registro guardan información específica que se ha guardado para cada formulario asociado con el sitio. .

Table Description Table Definition
record La tabla record es la tabla principal asociada con cada instancia de un formulario. Hay una id única para su fácil referencia. El campo last_modified se actualiza cada vez que se le hace un cambio al registro dado. El formulario es la id del formulario del que este registro es una instancia. Si el registro tiene un registro primario, entonces el campo primario se llenará con esa id de registro.
CREATE TABLE record (
  id int(10) unsigned NOT NULL auto_increment,
  last_modified datetime NOT NULL,
  form int(10) unsigned NOT NULL,
  parent int(10) unsigned default NULL,
  PRIMARY KEY  (id),
  KEY parent (parent)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
entry

last_entry

Las tablas de entry and last_entry son muy similares. La tabla de entrada lleva un registro de todos los cambios realizados al valor de un form_field dado para un registro. La last_entry mantiene la última entrada para su fácil acceso. El registro es la id del registro con el que se asocia este valor para el form_field dado. La fecha es la fecha en que se guardó este valor. Who es la user id de la persona que realizó esta modificación. El change_type se establece dependiendo de si esta es una entrada inicial, una corrección o una actualización regular de este valor. También se puede establecer para que se verifique si los datos se han revisado. Uno de los campos de valores se llenará en base al tipo de form_field.
CREATE TABLE entry (
  record int(10) unsigned NOT NULL,
  form_field int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  who int(10) unsigned NOT NULL,
  change_type tinyint(3) unsigned NOT NULL,
  string_value varchar(255) collate utf8_bin default NULL,
  integer_value int(11) default NULL,
  text_value text collate utf8_bin,
  date_value datetime default NULL,
  blob_value longblob,
  PRIMARY KEY  (record,form_field,`date`),
  KEY `date` (`date`),
  KEY form_field (form_field),
  KEY record (record)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE last_entry (
  record int(10) unsigned NOT NULL,
  form_field int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  who int(10) unsigned NOT NULL,
  change_type tinyint(3) unsigned NOT NULL,
  string_value varchar(255) collate utf8_bin default NULL,
  integer_value int(11) default NULL,
  text_value text collate utf8_bin,
  date_value datetime default NULL,
  blob_value longblob,
  PRIMARY KEY  (record,form_field),
  KEY form_field (form_field),
  KEY record (record)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
field_sequence La tabla de field_sequence se utiliza para llevar registro de un valor entero para un form_field que el sitio generará y aumentará automáticamente. Lleva registro del último valor utilizado para el form_field dado.
CREATE TABLE field_sequence (
  form_field int(11) NOT NULL,
  sequence int(11) unsigned NOT NULL,
  PRIMARY KEY  (form_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
deleted_record El deleted_record se utiliza para guardar registros que se borran del sistema en caso que deban recuperarse. Es un espejo de la tabla de registro.
CREATE TABLE deleted_record (
  id int(10) unsigned NOT NULL auto_increment,
  last_modified datetime NOT NULL,
  form int(10) unsigned NOT NULL,
  parent int(10) unsigned default NULL,
  PRIMARY KEY  (id),
  KEY parent (parent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Tablas de Utilidades

Table Description Table Definition
config La tabla config guarda todos los datos de configuración para el sitio. Estos datos se leen de los archivos XML de configuración para los módulos. El hash es un hash MD5 de la ruta. Se utiliza para realizar búsquedas de claves únicas. Se comparte con el hash que está guardado en el APC. La ruta es un formato legible de la ruta hacia los datos. El tipo determina si esta entrada es un nodo primario o final. Si es un nodo final, el valor se establecerá con el valor del nodo. Si es un primario, entonces los secundarios se establecerán con una lista de nodos secundarios para esta entrada.
CREATE TABLE config (
  `hash` char(32) character set latin1 NOT NULL,
  path varchar(10000) character set latin1 NOT NULL,
  `type` tinyint(4) NOT NULL,
  `value` varchar(2000) character set latin1 default NULL,
  children varchar(10000) character set latin1 default NULL,
  PRIMARY KEY  (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
report_list La tabla de report_list es simplemente una definición temporal para crear tablas temporales al crear un reporte en caché. Tiene registros primaries y secundarios que se guardarán en dependencia del reporte que se este realizando en caché.
CREATE TABLE report_list (
  `primary` int(11) NOT NULL,
  secondary int(11) NOT NULL,
  PRIMARY KEY  (`primary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Ejemplo de Formulario

Este es un ejemplo de como se guardarían dos formularios en la base de datos. El formulario de persona tiene un campo para el surname (apellido) y el formulario demogáfico tiene un campo de birth_date. El formulario de persona se guardaría primaro ya que es el formulario primario. Asumiendo que nunca se ha guardado ningún formulario en la base de datos, lo siguiente ocurriría al guardarlo.

  1. Create the form, field and form_field entries.
    1. An entry is added to the form table with the name being "person." This will automatically assign a form id of 1 since it's the first one.
    2. An entry is added to the field table with the name being "surname." This will automatically assign a field id of 1.
    3. An entry is added to the form_field table with the form being 1 (for person) and the field being 1 (for surname). This will automatically assign a form_field id of 1.
    4. An entry is added to the form table with the name being "demographic." This will automatically assign a form id of 2 since it's the first one.
    5. An entry is added to the field table with the name being "birth_date." This will automatically assign a field id of 2.
    6. An entry is added to the form_field table with the form being 2 (for demographic) and the field being 2 (for birth_date). This will automatically assign a form_field id of 2.
  2. Create the person record.
    1. A new record will be added to the record table. The record id will be generated automatically (1) and the form will be set to 1. There is no parent and the last_modified time will be set to the current time.
    2. An entry will be added to the entry and last_entry tables. The record will be set to 1 and the form_field will be set to 1 (the form_field id created above for person-surname). The date will be the current time and who will be set to the user id making the change. The string_value field will be set to the value for the surname.
  3. Create the demographic record.
    1. A new record will be added to the record table. The record id will be generated automatically (2) and the form will be set to 2. The parent will be set to 1 since this is a child form for the person record that was just created. The last_modified time will be set to the current time.
    2. An entry will be added to the entry and last_entry tables. The record will be set to 2 and the form_field will be set to 2 (the form_field id created above for demographic-birth_date). The date will be the current time and who will be set to the user id making the change. The date_value field will be set to the value for the birth_date.