Dev:Database

Contents 1 table _album 2 table _album_directory 3 table _album_discussion 4 table _album_group 5 table _album_notification 6 table _album_photo 7 table _enemy 8 table _friend 9 table _group 10 table _group_category 11 table _group_discussion 12 table _group_member 13 table _group_notification 14 table _hash 15 table _image 16 table _mail 17 table _password 18 table _photo 19 table _photo_comment 20 table _photo_notification 21 table _photo_preview 22 table _photo_tag 23 table _ugly 24 table _unverified_mail 25 table _user 26 table _user_settings

table _album

CREATE TABLE `_album` (
  `aid` int(10) unsigned NOT NULL auto_increment,
  `directory` int(10) unsigned NOT NULL,
  `owner` int(10) unsigned NOT NULL,
  `icon` int(10) unsigned default NULL,
  `state` enum('waiting-approval','approved','banned') NOT NULL default 'waiting-approval',
  `reason` text,
  `date_started` datetime default NULL,
  `date_approved` datetime default NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `skin` varchar(255) default NULL,
  `free_visit` tinyint(1) NOT NULL,
  `free_photo_contribution` tinyint(1) NOT NULL,
  `photos_need_confirmation` tinyint(1) NOT NULL,
  `photos_need_reconfirmation` tinyint(1) NOT NULL,
  `public_discussion` enum('no','read-only','moderated','full') NOT NULL,
  PRIMARY KEY  (`aid`),
  KEY `owner` (`owner`),
  KEY `icon` (`icon`),
  KEY `directory` (`directory`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _album_directory

CREATE TABLE `_album_directory` (
  `adid` int(10) unsigned NOT NULL auto_increment,
  `parent` int(10) unsigned default NULL,
  `name` varchar(255) NOT NULL,
  `icon` int(10) unsigned default NULL,
  `description` text,
  `state` enum('suggested','approved','hidden') NOT NULL default 'suggested',
  `no_albums` tinyint(1) NOT NULL,
  `no_directories` tinyint(1) NOT NULL,
  `free_albums` tinyint(1) NOT NULL,
  `wanted_count` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`adid`),
  UNIQUE KEY `parent` (`parent`,`name`),
  KEY `icon` (`icon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _album_discussion

CREATE TABLE `_album_discussion` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `thread` int(10) unsigned default NULL,
  `parent` int(10) unsigned default NULL,
  `album` int(10) unsigned NOT NULL,
  `state` enum('waiting-approval','approved','deleted') NOT NULL default 'waiting-approval',
  `date` datetime NOT NULL,
  `user` int(10) unsigned default NULL,
  `name` varchar(255) default NULL,
  `mail` varchar(255) default NULL,
  `text` text,
  `ip` int(11) default NULL,
  `host` varchar(255) default NULL,
  PRIMARY KEY  (`cid`),
  KEY `thread` (`thread`),
  KEY `parent` (`parent`),
  KEY `album` (`album`,`state`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _album_group

CREATE TABLE `_album_group` (
  `album` int(10) unsigned NOT NULL,
  `group` int(10) unsigned NOT NULL,
  `rights` set('add-photo','confirm-photo','suspend-photo','delete-photo','read-discussion','write-discussion','confirm-discussion','delete-discussion','manage-groups','modify-attributes') default NULL,
  PRIMARY KEY  (`group`,`album`),
  KEY `album` (`album`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _album_notification

CREATE TABLE `_album_notification` (
  `user` int(10) unsigned NOT NULL,
  `album` int(10) unsigned NOT NULL,
  `type` set('favourite','confirm-photo','confirm-discussion','new-photo','new-discussion') default NULL,
  `last_access_photo` datetime default NULL,
  `last_access_discussion` datetime default NULL,
  PRIMARY KEY  (`user`,`album`),
  KEY `album` (`album`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _album_photo

CREATE TABLE `_album_photo` (
  `album` int(10) unsigned NOT NULL,
  `photo` int(10) unsigned NOT NULL,
  `state` enum('waiting-approval','approved','banned') NOT NULL default 'waiting-approval',
  `reason` text,
  `added` datetime default NULL,
  PRIMARY KEY  (`photo`,`album`),
  KEY `album` (`album`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _enemy

CREATE TABLE `_enemy` (
  `user` int(10) unsigned NOT NULL,
  `enemy` int(10) unsigned NOT NULL,
  `ignore` set('mails','comments','group-discussion','album-discussion','photos') default NULL,
  `notes` text,
  PRIMARY KEY  (`user`,`enemy`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _friend

CREATE TABLE `_friend` (
  `user` int(10) unsigned NOT NULL,
  `friend` int(10) unsigned NOT NULL,
  `rename` varchar(255) default NULL,
  `tag` varchar(255) default NULL,
  `notes` text,
  PRIMARY KEY  (`user`,`friend`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _group

CREATE TABLE `_group` (
  `gid` int(10) unsigned NOT NULL auto_increment,
  `category` int(10) unsigned NOT NULL,
  `owner` int(10) unsigned NOT NULL,
  `icon` int(10) unsigned default NULL,
  `state` enum('waiting-approval','approved','banned') NOT NULL default 'waiting-approval',
  `reason` text,
  `date_started` datetime default NULL,
  `date_approved` datetime default NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `free_join` tinyint(1) NOT NULL,
  `hide_list_of_members` enum('no','visitor-only','non-member','yes') NOT NULL,
  `public_discussion` enum('no','read-only','moderated','full') NOT NULL,
  `private_discussion` enum('no','moderators-only','read-only','moderated','full') NOT NULL,
  PRIMARY KEY  (`gid`),
  UNIQUE KEY `name` (`name`),
  KEY `category` (`category`),
  KEY `owner` (`owner`),
  KEY `icon` (`icon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _group_category

CREATE TABLE `_group_category` (
  `gcid` int(10) unsigned NOT NULL auto_increment,
  `parent` int(10) unsigned default NULL,
  `name` varchar(255) NOT NULL,
  `icon` int(10) unsigned default NULL,
  `description` text,
  `state` enum('suggested','approved','hidden') NOT NULL default 'suggested',
  `no_groups` tinyint(1) NOT NULL,
  `no_categories` tinyint(1) NOT NULL,
  `free_groups` tinyint(1) NOT NULL,
  `wanted_count` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`gcid`),
  UNIQUE KEY `parent` (`parent`,`name`),
  KEY `icon` (`icon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _group_discussion

CREATE TABLE `_group_discussion` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `thread` int(10) unsigned default NULL,
  `parent` int(10) unsigned default NULL,
  `group` int(10) unsigned NOT NULL,
  `state` enum('waiting-approval','approved','deleted') NOT NULL default 'waiting-approval',
  `date` datetime NOT NULL,
  `user` int(10) unsigned default NULL,
  `name` varchar(255) default NULL,
  `mail` varchar(255) default NULL,
  `text` text,
  `ip` int(11) default NULL,
  `host` varchar(255) default NULL,
  PRIMARY KEY  (`cid`),
  KEY `thread` (`thread`),
  KEY `parent` (`parent`),
  KEY `group` (`group`,`state`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _group_member

CREATE TABLE `_group_member` (
  `group` int(10) unsigned NOT NULL,
  `user` int(10) unsigned NOT NULL,
  `state` enum('waiting-approval','invited','member','moderator','banned') NOT NULL default 'waiting-approval',
  `reason` text,
  `joined` datetime default NULL,
  `last_discussion_access` datetime default NULL,
  PRIMARY KEY  (`group`,`user`),
  KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _group_notification

CREATE TABLE `_group_notification` (
  `user` int(10) unsigned NOT NULL,
  `group` int(10) unsigned NOT NULL,
  `type` set('confirm-member','confirm-discussion','new-discussion') default NULL,
  `last_access_discussion` datetime default NULL,
  PRIMARY KEY  (`user`,`group`),
  KEY `group` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _hash

CREATE TABLE `_hash` (
  `hash` varchar(255) NOT NULL,
  `str` varchar(255) default NULL,
  PRIMARY KEY  (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _image

CREATE TABLE `_image` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `size` int(10) unsigned NOT NULL,
  `width` int(10) unsigned NOT NULL,
  `height` int(10) unsigned NOT NULL,
  `type` enum('gif','jpeg','png') NOT NULL,
  `md5` char(32) NOT NULL,
  `md5ex` int(10) unsigned NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY  (`iid`),
  UNIQUE KEY `md5` (`md5`,`md5ex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _mail

CREATE TABLE `_mail` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user` int(10) unsigned NOT NULL,
  `sent` datetime default NULL,
  `type` enum('user-mail','special-notice','notif-photo-comment','notif-photo-version','notif-group-member','notif-group-discussion','notif-album-photo','notif-album-discussion') NOT NULL,
  `date` datetime NOT NULL,
  `from` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `subject` varchar(255) default NULL,
  `text` text,
  `ip` int(11) default NULL,
  `host` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _password

CREATE TABLE `_password` (
  `user` int(10) unsigned NOT NULL,
  `passwd` char(32) NOT NULL,
  `once` tinyint(1) NOT NULL,
  KEY `user` (`user`,`passwd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _photo

CREATE TABLE `_photo` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `hash` char(32) default NULL,
  `owner` int(10) unsigned default NULL,
  `image` int(10) unsigned NOT NULL,
  `private` tinyint(1) NOT NULL,
  `display_count` int(10) unsigned NOT NULL,
  `score_numerator` int(10) unsigned NOT NULL,
  `score_denominator` int(10) unsigned NOT NULL,
  `size` int(10) unsigned NOT NULL,
  `width` int(10) unsigned NOT NULL,
  `height` int(10) unsigned NOT NULL,
  `added` datetime NOT NULL,
  `name` varchar(255) default NULL,
  `description` text,
  `location` varchar(255) default NULL,
  `author` varchar(255) default NULL,
  `created` varchar(255) default NULL,
  `exif_info` text,
  `watermark_disabled` tinyint(1) NOT NULL,
  `last_comments_access` datetime default NULL,
  PRIMARY KEY  (`pid`),
  KEY `owner` (`owner`),
  KEY `image` (`image`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _photo_comment

CREATE TABLE `_photo_comment` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `thread` int(10) unsigned default NULL,
  `parent` int(10) unsigned default NULL,
  `photo` int(10) unsigned NOT NULL,
  `state` enum('waiting-approval','approved','deleted') NOT NULL default 'waiting-approval',
  `date` datetime NOT NULL,
  `user` int(10) unsigned default NULL,
  `name` varchar(255) default NULL,
  `mail` varchar(255) default NULL,
  `text` text,
  `ip` int(11) default NULL,
  `host` varchar(255) default NULL,
  PRIMARY KEY  (`cid`),
  KEY `thread` (`thread`),
  KEY `parent` (`parent`),
  KEY `photo` (`photo`,`state`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _photo_notification

CREATE TABLE `_photo_notification` (
  `user` int(10) unsigned NOT NULL,
  `photo` int(10) unsigned NOT NULL,
  `type` set('new-version','new-comment') default NULL,
  `last_access_photo` datetime default NULL,
  `last_access_comments` datetime default NULL,
  PRIMARY KEY  (`user`,`photo`),
  KEY `photo` (`photo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _photo_preview

CREATE TABLE `_photo_preview` (
  `photo` int(10) unsigned NOT NULL,
  `watermark` tinyint(1) NOT NULL,
  `resolution` int(10) unsigned NOT NULL,
  `type` enum('gif','jpeg','png') NOT NULL,
  `filename` varchar(255) default NULL,
  `last_access` datetime default NULL,
  `data` blob,
  PRIMARY KEY  (`photo`,`watermark`,`resolution`),
  UNIQUE KEY `filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _photo_tag

CREATE TABLE `_photo_tag` (
  `photo` int(10) unsigned NOT NULL,
  `tag` varchar(255) NOT NULL,
  PRIMARY KEY  (`photo`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _ugly

CREATE TABLE `_ugly` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user` int(10) unsigned default NULL,
  `photo` int(10) unsigned default NULL,
  `group` int(10) unsigned default NULL,
  `album` int(10) unsigned default NULL,
  `reporter` int(10) unsigned default NULL,
  `added` datetime default NULL,
  `notes` text,
  `ip` int(11) default NULL,
  `host` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `user` (`user`),
  KEY `photo` (`photo`),
  KEY `group` (`group`),
  KEY `album` (`album`),
  KEY `reporter` (`reporter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _unverified_mail

CREATE TABLE `_unverified_mail` (
  `hash` char(32) NOT NULL,
  `user` int(10) unsigned NOT NULL,
  `email` varchar(255) default NULL,
  `added` datetime default NULL,
  PRIMARY KEY  (`hash`),
  KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _user

CREATE TABLE `_user` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `login` varchar(255) NOT NULL,
  `state` enum('waiting-mail','waiting-approval','approved','banned') NOT NULL default 'waiting-mail',
  `reason` text,
  `session` varchar(255) default NULL,
  `date_reg` datetime default NULL,
  `date_approved` datetime default NULL,
  `last_state` datetime default NULL,
  `last_login` datetime default NULL,
  `last_access` datetime default NULL,
  `current_ip` varchar(255) default NULL,
  `previous_ip` varchar(255) default NULL,
  `photo` int(10) unsigned default NULL,
  `display_name` varchar(255) default NULL,
  `email_reg` varchar(255) default NULL,
  `email_contact` varchar(255) default NULL,
  `web` varchar(255) default NULL,
  `icq` int(10) unsigned default NULL,
  `birth` date default NULL,
  `sex` enum('male','female') default NULL,
  `address` text,
  `country` char(2) default NULL,
  `description` text,
  `telephone` varchar(255) default NULL,
  `show_profile_info` set('email-contact','web','icq','birth','sex','address','country','description','telephone') default NULL,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `login` (`login`),
  UNIQUE KEY `session` (`session`),
  KEY `photo` (`photo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table _user_settings

CREATE TABLE `_user_settings` (
  `user` int(10) unsigned NOT NULL,
  `language` char(3) default NULL,
  `skin` varchar(255) default NULL,
  `max_resolution` int(10) unsigned default NULL,
  `signature_name` varchar(255) default NULL,
  `signature_email` varchar(255) default NULL,
  `signature_add_id` tinyint(1) NOT NULL,
  `user_list_style` varchar(255) default NULL,
  `photo_list_style` varchar(255) default NULL,
  `comments_list_style` enum('tree-up','tree-down','date-up','date-down') default NULL,
  `directory_list_style` varchar(255) default NULL,
  `album_list_style` varchar(255) default NULL,
  `album_photo_list_style` varchar(255) default NULL,
  `category_list_style` varchar(255) default NULL,
  `group_list_style` varchar(255) default NULL,
  `watermark_position` varchar(255) default NULL,
  `watermark_photo` int(10) unsigned default NULL,
  `common_notifications` set('photo-comment','group-member','group-new-discussion','group-confirm-discussion','album-new-photo','album-confirm-photo','album-new-discussion','album-confirm-discussion') default NULL,
  `notifications_frequency` enum('single','hourly','daily','weekly','monthly') default NULL,
  `user_mails_frequency` enum('single','hourly','daily','weekly','monthly') default NULL,
  `hidden_objects` set('qfriends','qphotos','qgroups','qalbums') default NULL,
  `want_confirm_comments` tinyint(1) NOT NULL,
  `actual_tags` varchar(255) default NULL,
  `skip_captcha` tinyint(1) NOT NULL,
  `override_space_quota` int(11) NOT NULL,
  `full_read_access` tinyint(1) NOT NULL,
  `may_manage_users` tinyint(1) NOT NULL,
  `may_manage_rights` tinyint(1) NOT NULL,
  `may_manage_photos` tinyint(1) NOT NULL,
  `may_manage_shared_storage` tinyint(1) NOT NULL,
  `may_manage_categories` tinyint(1) NOT NULL,
  `may_manage_groups` tinyint(1) NOT NULL,
  `may_manage_directories` tinyint(1) NOT NULL,
  `may_manage_albums` tinyint(1) NOT NULL,
  `may_manage_system` tinyint(1) NOT NULL,
  PRIMARY KEY  (`user`),
  KEY `watermark_photo` (`watermark_photo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8