miércoles, 15 de octubre de 2008

precaucion

ufff, hace tanto no escribo aqui, luego lo hare, ahora lo uso para tomar una precaucion.

debo hacer una prueba laboral, y en dicha prueba me piden que adjunt el dump o el ER de una base de datos que haya desarrollado, la unica que puedo enviar (por cuestiones de derecho y confidencialidad) es uno de un sistema que estoy desarrollando, pero para prevenir que mi DB luego aparezca en otro sistema y digan que yo lo copie, lo coloco en alguna web para poder decir que primero fue mio
gracias


# This is a fix for InnoDB in MySQL >= 4.1.x
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- modulos
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `modulos`;


CREATE TABLE `modulos`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(255) NOT NULL,
`privado` INTEGER NOT NULL,
`publico` INTEGER NOT NULL,
`archivozip` VARCHAR(255),
`procesado` INTEGER default 0 NOT NULL,
PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- acciones
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `acciones`;


CREATE TABLE `acciones`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(255) NOT NULL,
`privado` INTEGER NOT NULL,
`publico` INTEGER NOT NULL,
`popularidad` INTEGER default 0 NOT NULL,
`modulo` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `acciones_FI_1` (`modulo`),
CONSTRAINT `acciones_FK_1`
FOREIGN KEY (`modulo`)
REFERENCES `modulos` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- servicios
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `servicios`;


CREATE TABLE `servicios`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(255) NOT NULL,
`accion` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `servicios_FI_1` (`accion`),
CONSTRAINT `servicios_FK_1`
FOREIGN KEY (`accion`)
REFERENCES `acciones` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- grupo_servicio
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `grupo_servicio`;


CREATE TABLE `grupo_servicio`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`servicio` INTEGER NOT NULL,
`grupo` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `grupo_servicio_FI_1` (`servicio`),
CONSTRAINT `grupo_servicio_FK_1`
FOREIGN KEY (`servicio`)
REFERENCES `servicios` (`id`)
ON DELETE CASCADE,
INDEX `grupo_servicio_FI_2` (`grupo`),
CONSTRAINT `grupo_servicio_FK_2`
FOREIGN KEY (`grupo`)
REFERENCES `sf_guard_group` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- servicio_servicio
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `servicio_servicio`;


CREATE TABLE `servicio_servicio`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`padre` INTEGER NOT NULL,
`hijo` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `servicio_servicio_FI_1` (`padre`),
CONSTRAINT `servicio_servicio_FK_1`
FOREIGN KEY (`padre`)
REFERENCES `servicios` (`id`)
ON DELETE CASCADE,
INDEX `servicio_servicio_FI_2` (`hijo`),
CONSTRAINT `servicio_servicio_FK_2`
FOREIGN KEY (`hijo`)
REFERENCES `servicios` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- servicio_usuario
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `servicio_usuario`;


CREATE TABLE `servicio_usuario`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`servicio` INTEGER NOT NULL,
`usuario` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `servicio_usuario_FI_1` (`servicio`),
CONSTRAINT `servicio_usuario_FK_1`
FOREIGN KEY (`servicio`)
REFERENCES `servicios` (`id`)
ON DELETE CASCADE,
INDEX `servicio_usuario_FI_2` (`usuario`),
CONSTRAINT `servicio_usuario_FK_2`
FOREIGN KEY (`usuario`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- accion_grupo
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `accion_grupo`;


CREATE TABLE `accion_grupo`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`accion` INTEGER NOT NULL,
`grupo` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `accion_grupo_FI_1` (`accion`),
CONSTRAINT `accion_grupo_FK_1`
FOREIGN KEY (`accion`)
REFERENCES `acciones` (`id`)
ON DELETE CASCADE,
INDEX `accion_grupo_FI_2` (`grupo`),
CONSTRAINT `accion_grupo_FK_2`
FOREIGN KEY (`grupo`)
REFERENCES `sf_guard_group` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- accion_usuario
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `accion_usuario`;


CREATE TABLE `accion_usuario`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`accion` INTEGER NOT NULL,
`usuario` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `accion_usuario_FI_1` (`accion`),
CONSTRAINT `accion_usuario_FK_1`
FOREIGN KEY (`accion`)
REFERENCES `acciones` (`id`)
ON DELETE CASCADE,
INDEX `accion_usuario_FI_2` (`usuario`),
CONSTRAINT `accion_usuario_FK_2`
FOREIGN KEY (`usuario`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- usuarios
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `usuarios`;


CREATE TABLE `usuarios`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`nombres` VARCHAR(255) NOT NULL,
`apellidos` VARCHAR(255) NOT NULL,
`correo` VARCHAR(255) NOT NULL,
`nacimiento` DATETIME NOT NULL,
`pais` VARCHAR(3) NOT NULL,
`estado` VARCHAR(255) NOT NULL,
`ciudad` VARCHAR(255) NOT NULL,
`firma` TEXT,
`actividades` INTEGER default 0 NOT NULL,
`sexo` VARCHAR(1) NOT NULL,
`user` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `usuarios_FI_1` (`user`),
CONSTRAINT `usuarios_FK_1`
FOREIGN KEY (`user`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- access_log
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `access_log`;


CREATE TABLE `access_log`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`fecha` DATE NOT NULL,
`fechahora` DATETIME NOT NULL,
`modulo` VARCHAR(255) NOT NULL,
`accion` VARCHAR(255) NOT NULL,
`request` TEXT NOT NULL,
`response_code` TEXT NOT NULL,
`user` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `access_log_FI_1` (`user`),
CONSTRAINT `access_log_FK_1`
FOREIGN KEY (`user`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- message_log
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `message_log`;


CREATE TABLE `message_log`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`fecha` DATE NOT NULL,
`fechahora` DATETIME NOT NULL,
`message` TEXT NOT NULL,
`mostrado` INTEGER default 0 NOT NULL,
`user` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `message_log_FI_1` (`user`),
CONSTRAINT `message_log_FK_1`
FOREIGN KEY (`user`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_simple_forum_category
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_simple_forum_category`;


CREATE TABLE `sf_simple_forum_category`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`stripped_name` VARCHAR(255),
`description` TEXT,
`rank` INTEGER,
`created_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_simple_forum_forum
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_simple_forum_forum`;


CREATE TABLE `sf_simple_forum_forum`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`description` TEXT,
`rank` INTEGER,
`category_id` INTEGER,
`created_at` DATETIME,
`updated_at` DATETIME,
`stripped_name` VARCHAR(255),
`latest_post_id` INTEGER,
`nb_posts` BIGINT,
`nb_topics` BIGINT,
PRIMARY KEY (`id`),
UNIQUE KEY `sf_simple_forum_forum_stripped_name_unique` (`stripped_name`),
INDEX `sf_simple_forum_forum_FI_1` (`category_id`),
CONSTRAINT `sf_simple_forum_forum_FK_1`
FOREIGN KEY (`category_id`)
REFERENCES `sf_simple_forum_category` (`id`)
ON DELETE CASCADE,
INDEX `sf_simple_forum_forum_FI_2` (`latest_post_id`),
CONSTRAINT `sf_simple_forum_forum_FK_2`
FOREIGN KEY (`latest_post_id`)
REFERENCES `sf_simple_forum_post` (`id`)
ON DELETE SET NULL
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_simple_forum_topic
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_simple_forum_topic`;


CREATE TABLE `sf_simple_forum_topic`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255),
`is_sticked` INTEGER default 0,
`is_locked` INTEGER default 0,
`forum_id` INTEGER,
`created_at` DATETIME,
`updated_at` DATETIME,
`latest_post_id` INTEGER,
`user_id` INTEGER,
`stripped_title` VARCHAR(255),
`nb_posts` BIGINT default 0,
`nb_views` BIGINT default 0,
PRIMARY KEY (`id`),
INDEX `sf_simple_forum_topic_FI_1` (`forum_id`),
CONSTRAINT `sf_simple_forum_topic_FK_1`
FOREIGN KEY (`forum_id`)
REFERENCES `sf_simple_forum_forum` (`id`)
ON DELETE CASCADE,
INDEX `sf_simple_forum_topic_FI_2` (`latest_post_id`),
CONSTRAINT `sf_simple_forum_topic_FK_2`
FOREIGN KEY (`latest_post_id`)
REFERENCES `sf_simple_forum_post` (`id`)
ON DELETE SET NULL,
INDEX `sf_simple_forum_topic_FI_3` (`user_id`),
CONSTRAINT `sf_simple_forum_topic_FK_3`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE SET NULL
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_simple_forum_post
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_simple_forum_post`;


CREATE TABLE `sf_simple_forum_post`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255),
`content` TEXT,
`topic_id` INTEGER,
`user_id` INTEGER,
`created_at` DATETIME,
`forum_id` INTEGER,
`author_name` VARCHAR(255),
PRIMARY KEY (`id`),
INDEX `sf_simple_forum_post_FI_1` (`topic_id`),
CONSTRAINT `sf_simple_forum_post_FK_1`
FOREIGN KEY (`topic_id`)
REFERENCES `sf_simple_forum_topic` (`id`)
ON DELETE CASCADE,
INDEX `sf_simple_forum_post_FI_2` (`user_id`),
CONSTRAINT `sf_simple_forum_post_FK_2`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE,
INDEX `sf_simple_forum_post_FI_3` (`forum_id`),
CONSTRAINT `sf_simple_forum_post_FK_3`
FOREIGN KEY (`forum_id`)
REFERENCES `sf_simple_forum_forum` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_simple_forum_topic_view
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_simple_forum_topic_view`;


CREATE TABLE `sf_simple_forum_topic_view`
(
`user_id` INTEGER NOT NULL,
`topic_id` INTEGER NOT NULL,
`created_at` DATETIME,
PRIMARY KEY (`user_id`,`topic_id`),
CONSTRAINT `sf_simple_forum_topic_view_FK_1`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE,
INDEX `sf_simple_forum_topic_view_FI_2` (`topic_id`),
CONSTRAINT `sf_simple_forum_topic_view_FK_2`
FOREIGN KEY (`topic_id`)
REFERENCES `sf_simple_forum_topic` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_group
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_group`;


CREATE TABLE `sf_guard_group`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`description` TEXT,
PRIMARY KEY (`id`),
UNIQUE KEY `sf_guard_group_name_unique` (`name`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_permission
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_permission`;


CREATE TABLE `sf_guard_permission`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`description` TEXT,
PRIMARY KEY (`id`),
UNIQUE KEY `sf_guard_permission_name_unique` (`name`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_group_permission
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_group_permission`;


CREATE TABLE `sf_guard_group_permission`
(
`group_id` INTEGER NOT NULL,
`permission_id` INTEGER NOT NULL,
PRIMARY KEY (`group_id`,`permission_id`),
CONSTRAINT `sf_guard_group_permission_FK_1`
FOREIGN KEY (`group_id`)
REFERENCES `sf_guard_group` (`id`)
ON DELETE CASCADE,
INDEX `sf_guard_group_permission_FI_2` (`permission_id`),
CONSTRAINT `sf_guard_group_permission_FK_2`
FOREIGN KEY (`permission_id`)
REFERENCES `sf_guard_permission` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_user`;


CREATE TABLE `sf_guard_user`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`username` VARCHAR(128) NOT NULL,
`algorithm` VARCHAR(128) default 'sha1' NOT NULL,
`salt` VARCHAR(128) NOT NULL,
`password` VARCHAR(128) NOT NULL,
`created_at` DATETIME,
`last_login` DATETIME,
`is_active` INTEGER default 1 NOT NULL,
`is_super_admin` INTEGER default 0 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sf_guard_user_username_unique` (`username`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_user_permission
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_user_permission`;


CREATE TABLE `sf_guard_user_permission`
(
`user_id` INTEGER NOT NULL,
`permission_id` INTEGER NOT NULL,
PRIMARY KEY (`user_id`,`permission_id`),
CONSTRAINT `sf_guard_user_permission_FK_1`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE,
INDEX `sf_guard_user_permission_FI_2` (`permission_id`),
CONSTRAINT `sf_guard_user_permission_FK_2`
FOREIGN KEY (`permission_id`)
REFERENCES `sf_guard_permission` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_user_group
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_user_group`;


CREATE TABLE `sf_guard_user_group`
(
`user_id` INTEGER NOT NULL,
`group_id` INTEGER NOT NULL,
PRIMARY KEY (`user_id`,`group_id`),
CONSTRAINT `sf_guard_user_group_FK_1`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE,
INDEX `sf_guard_user_group_FI_2` (`group_id`),
CONSTRAINT `sf_guard_user_group_FK_2`
FOREIGN KEY (`group_id`)
REFERENCES `sf_guard_group` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_remember_key
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_remember_key`;


CREATE TABLE `sf_guard_remember_key`
(
`user_id` INTEGER NOT NULL,
`remember_key` VARCHAR(32),
`ip_address` VARCHAR(50) NOT NULL,
`created_at` DATETIME,
PRIMARY KEY (`user_id`,`ip_address`),
CONSTRAINT `sf_guard_remember_key_FK_1`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`)
ON DELETE CASCADE
)Type=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;