MySql_Tables.sql

/**
 * @filesource MySql_Tables.sql
 *
 * @category freeSN
 * @copyright Copyright by mensch.coop e.G. 2009
 * @mailto	dev [at] mensch.coop
 * @version 0.4.200901
 * @link http://mensch.coop
 */

-- DROP DATABASE test;
-- CREATE DATABASE test;
-- use test

--
-- http://dev.mysql.com/doc/refman/5.1/de/storage-engines.html
--
--	Die Speicher-Engines InnoDB und BDB stellen transaktionssichere Tabellen zur Verfügung.
--

--	--------------------------------------------------------
--	Tabellen
select 'losp_BE_profile';
CREATE TABLE losp_BE_profile (
	MTextID int(11) UNSIGNED NOT NULL,
	NickName varchar(20) NOT NULL,
	EMail varchar(256) NOT NULL,
	Fingerprint varchar(42) NOT NULL,
	Jabber varchar(256) NOT NULL,
	Url varchar(2048),
	OnlineState int(1) NOT NULL,
	OnlineSince int(11),
	Avatar varchar(2048),
	Signature text(2048),
	PRIMARY KEY  (MTextID),
	UNIQUE (NickName)
) ENGINE=InnoDB;


-- --------------------------------
select 'losp_be_account';
CREATE TABLE losp_be_account (
	ID int(11) UNSIGNED NOT NULL auto_increment,
	Password varchar(50) NOT NULL,
	ProfileID int(11) UNSIGNED,
	FirstName varchar(50) NOT NULL,
	SurName varchar(100) NOT NULL,
	Street varchar(100) NOT NULL,
	Streetnumber int(4) NOT NULL,
	City varchar(50) NOT NULL,
	ZipCode int(5) NOT NULL,
	EMail varchar(256) NOT NULL,
	CDate timestamp NOT NULL default CURRENT_TIMESTAMP,
  	PRIMARY KEY (ID)
) ENGINE=InnoDB;


-- ------------------------
select 'losp_BasisElementDesign';
CREATE TABLE losp_BasisElementDesign (
	ID int(11) UNSIGNED NOT NULL ,
	BEClassName varchar(64) NOT NULL,
	Author_ProfileID int(11) UNSIGNED,
	CDate timestamp NOT NULL default CURRENT_TIMESTAMP,
	CSS text NOT NULL,
	Miscellaneous text NULL default NULL,
	DefaultBC varchar(64) NULL default NULL,
	PRIMARY KEY (ID)
) ENGINE=InnoDB;

--
select 'losp_BasisElementDesign_values';
CREATE TABLE losp_BasisElementDesign_values (
	ID int(11) UNSIGNED NOT NULL ,
	VKey varchar(128) NOT NULL,
	VValue text NOT NULL,
	PRIMARY KEY (ID, VKey)
) ENGINE=InnoDB;

--
select 'losp_BasisElement_categorynamelist';
CREATE TABLE losp_BasisElement_categorynamelist (
	BECategoryID int(11) UNSIGNED NOT NULL,
	BECategoryName varchar(64),
	BECategoryGroupID int(11) UNSIGNED,
	PRIMARY KEY (BECategoryID)
) ENGINE=InnoDB;

--
select 'losp_BasisElement_categorylist';
CREATE TABLE losp_BasisElement_categorylist (
	BECategoryListID int(11)  UNSIGNED NOT NULL,
	BECategoryID int(11) UNSIGNED NOT NULL,
	PRIMARY KEY (BECategoryListID,BECategoryID),
	FOREIGN KEY (BECategoryID)
	REFERENCES losp_BasisElement_categorynamelist(BECategoryID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
--
) ENGINE=InnoDB;

--
select 'losp_BasisElement_tagnamelist';
CREATE TABLE losp_BasisElement_tagnamelist (
	TagID int(11) UNSIGNED NOT NULL,
	TagName varchar(64),
	PRIMARY KEY (TagID)
) ENGINE=InnoDB;

--
select 'losp_BasisElement_taglist';
CREATE TABLE losp_BasisElement_taglist (
	TagListID int(11) UNSIGNED NOT NULL,
	TagID int(11) UNSIGNED NOT NULL,
	PRIMARY KEY (TagListID,TagID)
--
	,
	FOREIGN KEY (TagID)
		REFERENCES losp_BasisElement_tagnamelist(TagID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
--
) ENGINE=InnoDB;

select 'losp_class';
CREATE TABLE losp_class (
	ClassID int(11) UNSIGNED NOT NULL,
	ClassName  varchar(512),
	PRIMARY KEY (ClassID)
) ENGINE=InnoDB;

--
select 'losp_BasisElement';
CREATE TABLE losp_BasisElement (
	ID int(11) UNSIGNED NOT NULL,
	Author_ProfileID int(11) UNSIGNED,
	Subject varchar(512),
	Text text NOT NULL,
	CreateDate timestamp NOT NULL default CURRENT_TIMESTAMP,
	TagListID int(11) UNSIGNED default null,
	BECategoryListID int(11) UNSIGNED default null,
	BECategoryChoiceBEID int(11) UNSIGNED default null,
	BasisElementDesignID int(11) UNSIGNED default null,
	AuthReadListID int(11) UNSIGNED default 0,
	AuthWriteListID int(11) UNSIGNED default 0,
	PCAuthReadListID int(11) UNSIGNED default 0,
	PCAuthWriteListID int(11) UNSIGNED default 0,
	ClassID int(11) UNSIGNED,
	LanguageCode varchar(3),
	PRIMARY KEY (ID),
	INDEX (Author_ProfileID, AuthReadListID,AuthWriteListID,BasisElementDesignID, ClassID),
	FOREIGN KEY (Author_ProfileID)
		REFERENCES losp_BE_profile(MTextID)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY (BECategoryListID)
		REFERENCES losp_BasisElement_categorylist(BECategoryListID)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY (BECategoryChoiceBEID)
		REFERENCES losp_BasisElement(ID)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY (TagListID)
		REFERENCES losp_BasisElement_taglist(TagListID)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY (BasisElementDesignID)
		REFERENCES losp_BasisElementDesign(ID)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY (ClassID)
		REFERENCES losp_class(ClassID)
		ON DELETE SET NULL
		ON UPDATE CASCADE
--
) ENGINE=InnoDB;

--
select 'losp_BE_newsfeed';
CREATE TABLE losp_BE_newsfeed (
	MTextID int(11) UNSIGNED NOT NULL,
	Url varchar(2048),
	PRIMARY KEY (MTextID)
--
	,
	FOREIGN KEY (MTextID)
	REFERENCES losp_BasisElement(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
--
) ENGINE=InnoDB;

--
select 'losp_BasisClipboard_state';
CREATE TABLE losp_BasisClipboard_state (
	StateID int(4) UNSIGNED,
	State varchar(128),
	PRIMARY KEY (StateID)
) ENGINE=InnoDB;

--
select 'losp_BasisClipboard_objvar';
CREATE TABLE losp_BasisClipboard_objvar (
	PartOfObjVarID int(4) UNSIGNED,
	PartOfObjVar varchar(128),
	PRIMARY KEY (PartOfObjVarID)
) ENGINE=InnoDB;

--
select 'losp_BasisClipboard_category';
CREATE TABLE losp_BasisClipboard_category (
	CategoryID int(11) UNSIGNED,
	CategoryName varchar(128),
	PRIMARY KEY (CategoryID)
) ENGINE=InnoDB;

--
select 'losp_BasisClipboard';
CREATE TABLE losp_BasisClipboard (
	PartOfID int(11) UNSIGNED NOT NULL,
	PartOfObjVarID int(4) UNSIGNED,
	AttachID int(11) UNSIGNED NOT NULL,
	AttachStateID int(4) UNSIGNED,
	LinkerProfileID int(11) UNSIGNED NOT NULL,
	CategoryID int(11) UNSIGNED,
	CDate timestamp NOT NULL default CURRENT_TIMESTAMP,
	INDEX (PartOfID, PartOfObjVarID,AttachID, AttachStateID,LinkerProfileID,CategoryID),
	PRIMARY KEY (PartOfID, AttachID, AttachStateID, PartOfObjVarID, CategoryID)
) ENGINE=InnoDB;

--
select 'losp_BasisElement_writeauthorisation';
CREATE TABLE losp_BasisElement_writeauthorisation (
	ListID int(11) UNSIGNED NOT NULL ,
	ProfileID int(11) UNSIGNED NOT NULL,
	PRIMARY KEY (ListID,ProfileID),
	INDEX (ProfileID),
	FOREIGN KEY (ProfileID)
		REFERENCES losp_BE_profile(MTextID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
) ENGINE=InnoDB;

--
select 'losp_BasisElement_readauthorisation';
CREATE TABLE losp_BasisElement_readauthorisation (
	ListID int(11) UNSIGNED NOT NULL,
	ProfileID int(11) UNSIGNED NOT NULL,
	PRIMARY KEY (ListID,ProfileID),
	INDEX (ProfileID),
	FOREIGN KEY (ProfileID)
		REFERENCES losp_BE_profile(MTextID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
) ENGINE=InnoDB;

--
select 'create losp_BE_date';
CREATE TABLE losp_BE_date (
	MTextID int(11) UNSIGNED NOT NULL,
	Start int(11),
	End int(11),
	Cycle int(3) NULL default NULL,
	PRIMARY KEY (MTextID),
	FOREIGN KEY (MTextID)
		REFERENCES losp_BasisElement(ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
) ENGINE=InnoDB;

--
select 'create losp_BE_topic';
CREATE TABLE losp_BE_topic (
	MTextID int(11) UNSIGNED NOT NULL,
	Name varchar(20),
	PRIMARY KEY  (MTextID)
) ENGINE=InnoDB;
alter table losp_BE_topic add unique (Name);
--
select 'create losp_BE_treepart';
CREATE TABLE losp_BE_treepart (
	MTextID int(11) UNSIGNED NOT NULL,
	Name varchar(20),
	PRIMARY KEY  (MTextID),
	FOREIGN KEY (MTextID)
	REFERENCES losp_BasisElement(ID)
	ON DELETE CASCADE
--
) ENGINE=InnoDB;

--
select 'losp_BE_file ';
CREATE TABLE losp_BE_file (
	MTextID int(11) UNSIGNED NOT NULL,
	Link varchar(512),
	Mime varchar(20),
	PRIMARY KEY (MTextID)
--
	,
	FOREIGN KEY (MTextID)
	REFERENCES losp_BasisElement(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
--
) ENGINE=InnoDB;

select 'losp_BE_url ';
CREATE TABLE losp_BE_url (
	MTextID int(11) UNSIGNED NOT NULL,
	Url varchar(2048),
	PRIMARY KEY (MTextID)
--
	,
	FOREIGN KEY (MTextID)
	REFERENCES losp_BasisElement(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
--
) ENGINE=InnoDB;



select 'losp_BEBasisClipboardsRightManagement ';
CREATE  TABLE IF NOT EXISTS losp_BEBasisClipboardsRightManagement (
	PartOfID INT(11) UNSIGNED NOT NULL ,
	PartOfObjVarID INT(4) UNSIGNED NOT NULL ,
	CategoryID INT(11) UNSIGNED NOT NULL ,
	WriteAuthorisaton CHAR(1),
	ReadAuthorisation CHAR(1),
	PRIMARY KEY (`PartOfID`, `PartOfObjVarID`, `CategoryID`),
	INDEX `ID` (`PartOfID` ASC) ,
	INDEX `PartOfObjVarID` (`PartOfObjVarID` ASC) ,
	INDEX `CategroyID` (`CategoryID` ASC) ,
	CONSTRAINT `ID`
	FOREIGN KEY (`PartOfID` )
		REFERENCES `losp_BasisElement` (`ID` )
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	CONSTRAINT `PartOfObjVarID`
	FOREIGN KEY (`PartOfObjVarID` )
		REFERENCES `losp_BasisClipboard_objvar` (`PartOfObjVarID` )
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	CONSTRAINT `CategroyID`
	FOREIGN KEY (`CategoryID` )
		REFERENCES `losp_BasisClipboard_category` (`CategoryID` )
		ON DELETE CASCADE
		ON UPDATE CASCADE
--
) ENGINE = InnoDB;


select 'losp_BE_threadmessage';
CREATE TABLE losp_BE_threadmessage (
	MTextID int(11) UNSIGNED NOT NULL,
	LastInsertDate int(11) UNSIGNED,
	ThreadBEQuantity int(11) UNSIGNED,
	PRIMARY KEY (MTextID),
	FOREIGN KEY (MTextID)
	REFERENCES losp_BasisElement(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
--
) ENGINE=InnoDB;


\. RightManagement/MySql_TableProfileCollectionAuthorisation.sql