SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";



CREATE TABLE `image` (

  `uuid` varchar(36) COLLATE utf8_unicode_ci NOT NULL,

  `revision` int(10) unsigned NOT NULL,

  `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL,

  `path` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `createtime` int(10) unsigned NOT NULL,

  `updatetime` int(10) unsigned NOT NULL,

  `ownerid` int(10) unsigned NOT NULL,

  `operatingsystem` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

  `isvalid` tinyint(1) unsigned NOT NULL,

  `isdeleted` tinyint(1) unsigned NOT NULL,

  `description` text COLLATE utf8_unicode_ci NOT NULL,

  `filesize` bigint(20) unsigned NOT NULL,

  `missingblocks` text COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`uuid`,`revision`),

  KEY `ownerid` (`ownerid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



CREATE TABLE `satellite` (

  `organizationid` varchar(32) NOT NULL,

  `address` varchar(64) NOT NULL,

  `name` varchar(255) NOT NULL,

  `authmethod` varchar(255) NOT NULL,

  `publickey` text,

  PRIMARY KEY (`organizationid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `satellite_suffix` (

  `organizationid` varchar(32) NOT NULL,

  `suffix` varchar(32) NOT NULL,

  PRIMARY KEY (`suffix`),

  KEY `organizationid` (`organizationid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `user` (

  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `login` varchar(48) DEFAULT NULL,

  `password` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,

  `organizationid` varchar(32) NOT NULL,

  `firstname` varchar(32) NOT NULL,

  `lastname` varchar(32) NOT NULL,

  `email` varchar(48) NOT NULL,

  PRIMARY KEY (`userid`),

  UNIQUE KEY `username` (`organizationid`,`login`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;





ALTER TABLE `image`

  ADD CONSTRAINT `image_ibfk_1` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE;



ALTER TABLE `satellite_suffix`

  ADD CONSTRAINT `satellite_suffix_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `satellite` (`organizationid`) ON UPDATE CASCADE;



ALTER TABLE `user`

  ADD CONSTRAINT `user_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `satellite` (`organizationid`) ON UPDATE CASCADE;