root/trunk/data/sql/snippets-schema.sql

# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- sn_user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_user`;


CREATE TABLE `sn_user`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `login` VARCHAR(10),
    `password` VARCHAR(10),
    `email` VARCHAR(50),
    `first_name` VARCHAR(50),
    `last_name` VARCHAR(50),
    `is_admin` INTEGER default 0,
    `created_at` DATETIME,
    PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_snippet
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_snippet`;


CREATE TABLE `sn_snippet`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER,
    `title` TEXT,
    `body` TEXT,
    `html_body` TEXT,
    `created_at` DATETIME,
    `average_vote` DOUBLE,
    `nb_comments` INTEGER,
    `all_tags` VARCHAR(255),
    PRIMARY KEY (`id`),
    INDEX `sn_snippet_FI_1` (`user_id`),
    CONSTRAINT `sn_snippet_FK_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `sn_user` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_tag
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_tag`;


CREATE TABLE `sn_tag`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER,
    `snippet_id` INTEGER,
    `name` VARCHAR(50),
    `created_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `sn_tag_FI_1` (`user_id`),
    CONSTRAINT `sn_tag_FK_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `sn_user` (`id`),
    INDEX `sn_tag_FI_2` (`snippet_id`),
    CONSTRAINT `sn_tag_FK_2`
        FOREIGN KEY (`snippet_id`)
        REFERENCES `sn_snippet` (`id`)
        ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_comment
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_comment`;


CREATE TABLE `sn_comment`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER,
    `snippet_id` INTEGER,
    `body` TEXT,
    `html_body` TEXT,
    `created_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `sn_comment_FI_1` (`user_id`),
    CONSTRAINT `sn_comment_FK_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `sn_user` (`id`),
    INDEX `sn_comment_FI_2` (`snippet_id`),
    CONSTRAINT `sn_comment_FK_2`
        FOREIGN KEY (`snippet_id`)
        REFERENCES `sn_snippet` (`id`)
        ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_vote
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_vote`;


CREATE TABLE `sn_vote`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER,
    `snippet_id` INTEGER,
    `vote` INTEGER,
    `created_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `sn_vote_FI_1` (`user_id`),
    CONSTRAINT `sn_vote_FK_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `sn_user` (`id`),
    INDEX `sn_vote_FI_2` (`snippet_id`),
    CONSTRAINT `sn_vote_FK_2`
        FOREIGN KEY (`snippet_id`)
        REFERENCES `sn_snippet` (`id`)
        ON DELETE CASCADE
)Type=InnoDB;

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