source: trunk/de.ugoe.cs.swe.memos/schema/memos-mysql.sql @ 9

-- phpMyAdmin SQL Dump
-- version 3.2.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 19. Oktober 2009 um 10:38
-- Server Version: 5.0.51
-- PHP-Version: 5.2.6-1+lenny3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `xprakt2`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `Categories`
--

CREATE TABLE IF NOT EXISTS `Categories` (
  `ID` int(10) unsigned NOT NULL auto_increment COMMENT 'unique primary key for Categories',
  `Parent` int(10) unsigned default NULL COMMENT 'optional reference to parent category',
  `Name` varchar(255) NOT NULL COMMENT 'non unique categorie description',
  PRIMARY KEY  (`ID`),
  KEY `FK_Parent` (`Parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Stores memo Categories' AUTO_INCREMENT=776 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `Files`
--

CREATE TABLE IF NOT EXISTS `Files` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(700) NOT NULL,
  `Memo` int(10) unsigned NOT NULL,
  `Content` longblob NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `FK_Memo` (`Memo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=96 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `Memos`
--

CREATE TABLE IF NOT EXISTS `Memos` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Title` varchar(255) default NULL,
  `Content` text,
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Category` int(10) unsigned default NULL,
  `LockedBy` varchar(255) default NULL,
  `Author` varchar(255) NOT NULL,
  `Draft` tinyint(1) NOT NULL,
  `LockedAt` timestamp NULL default NULL,
  PRIMARY KEY  (`ID`),
  KEY `FK_Categories` (`Category`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Stores individual Memos' AUTO_INCREMENT=642 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `Tagging`
--

CREATE TABLE IF NOT EXISTS `Tagging` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Tag` varchar(255) NOT NULL,
  `Memo` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `Tag` (`Tag`,`Memo`),
  KEY `FK_Memo` (`Memo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Stores every tag for all Memos' AUTO_INCREMENT=490 ;

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `Categories`
--
ALTER TABLE `Categories`
  ADD CONSTRAINT `FK_Parent` FOREIGN KEY (`Parent`) REFERENCES `Categories` (`ID`);

--
-- Constraints der Tabelle `Memos`
--
ALTER TABLE `Memos`
  ADD CONSTRAINT `FK_Categories` FOREIGN KEY (`Category`) REFERENCES `Categories` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints der Tabelle `Tagging`
--
ALTER TABLE `Tagging`
  ADD CONSTRAINT `FK_Memo` FOREIGN KEY (`Memo`) REFERENCES `Memos` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION;