diff options
Diffstat (limited to 'install')
| -rw-r--r-- | install/dbscheme.txt | 17 | ||||
| -rw-r--r-- | install/phpwebgallery_structure.sql | 382 |
2 files changed, 208 insertions, 191 deletions
diff --git a/install/dbscheme.txt b/install/dbscheme.txt index d53e742c4..002059c4d 100644 --- a/install/dbscheme.txt +++ b/install/dbscheme.txt @@ -1,4 +1,5 @@ +table:caddie table:categories table:comments table:config @@ -17,8 +18,10 @@ table:user_group table:users table:waiting +column:user_id table:caddie type:smallint nullable:N length:5 signed:Y +column:element_id table:caddie type:mediumint nullable:N length:8 signed:Y column:id table:categories type:smallint nullable:N length:5 signed:N -column:date_last table:categories type:date nullable:Y +column:date_last table:categories type:datetime nullable:Y column:nb_images table:categories type:mediumint nullable:N length:8 signed:N column:name table:categories type:varchar nullable:N length:255 binary:N column:id_uppercat table:categories type:smallint nullable:Y length:5 signed:N @@ -39,6 +42,7 @@ column:date table:comments type:datetime column:author table:comments type:varchar nullable:Y length:255 binary:N column:content table:comments type:longtext nullable:Y column:validated table:comments type:enum('true','false') nullable:N +column:validation_date table:comments type:datetime nullable:Y column:param table:config type:varchar nullable:N length:40 binary:N column:value table:config type:varchar nullable:Y length:255 binary:N column:comment table:config type:varchar nullable:Y length:255 binary:N @@ -58,7 +62,7 @@ column:image_id table:image_category type:mediumint column:category_id table:image_category type:smallint nullable:N length:5 signed:N column:id table:images type:mediumint nullable:N length:8 signed:N column:file table:images type:varchar nullable:N length:255 binary:N -column:date_available table:images type:date nullable:N +column:date_available table:images type:datetime nullable:N column:date_creation table:images type:date nullable:Y column:tn_ext table:images type:varchar nullable:Y length:4 binary:N column:name table:images type:varchar nullable:Y length:255 binary:N @@ -91,7 +95,7 @@ column:user_id table:user_group type:smallint column:group_id table:user_group type:smallint nullable:N length:5 signed:N column:id table:users type:smallint nullable:N length:5 signed:N column:username table:users type:varchar nullable:N length:20 binary:Y -column:password table:users type:varchar nullable:N length:255 binary:N +column:password table:users type:varchar nullable:Y length:32 binary:N column:mail_address table:users type:varchar nullable:Y length:255 binary:N column:nb_image_line table:users type:tinyint nullable:N length:1 signed:N column:nb_line_page table:users type:tinyint nullable:N length:3 signed:N @@ -103,6 +107,9 @@ column:expand table:users type:enum('true','false') column:show_nb_comments table:users type:enum('true','false') nullable:N column:recent_period table:users type:tinyint nullable:N length:3 signed:N column:template table:users type:varchar nullable:N length:255 binary:N +column:last_feed_check table:users type:datetime nullable:Y +column:feed_id table:users type:varchar nullable:Y length:50 binary:Y +column:registration_date table:users type:datetime nullable:N column:id table:waiting type:int nullable:N length:10 signed:N column:storage_category_id table:waiting type:smallint nullable:N length:5 signed:N column:file table:waiting type:varchar nullable:N length:255 binary:N @@ -113,6 +120,8 @@ column:tn_ext table:waiting type:char column:validated table:waiting type:enum('true','false') nullable:N column:infos table:waiting type:text nullable:Y +PK:caddie_pk table:caddie column:user_id +PK:caddie_pk table:caddie column:element_id PK:categories_pk table:categories column:id PK:comments_pk table:comments column:id PK:config_pk table:config column:param @@ -137,6 +146,8 @@ PK:users_pk table:users column:id PK:waiting_pk table:waiting column:id index:categories_i2 table:categories column:id_uppercat +index:comments_i2 table:comments column:validation_date +index:comments_i1 table:comments column:image_id index:history_i1 table:history column:date index:image_category_i1 table:image_category column:image_id index:image_category_i2 table:image_category column:category_id diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql index 2d92e25e6..9ca7af0a7 100644 --- a/install/phpwebgallery_structure.sql +++ b/install/phpwebgallery_structure.sql @@ -1,275 +1,281 @@ --- MySQL dump 8.21 +-- MySQL dump 9.11 -- -- Host: localhost Database: pwg-bsf ---------------------------------------------------------- --- Server version 3.23.49-log +-- ------------------------------------------------------ +-- Server version 4.0.24_Debian-10-log -- --- Table structure for table 'phpwebgallery_caddie' +-- Table structure for table `phpwebgallery_caddie` -- -DROP TABLE IF EXISTS phpwebgallery_caddie; -CREATE TABLE phpwebgallery_caddie ( - user_id smallint(5) NOT NULL default '0', - element_id mediumint(8) NOT NULL default '0', - PRIMARY KEY (user_id,element_id) +DROP TABLE IF EXISTS `phpwebgallery_caddie`; +CREATE TABLE `phpwebgallery_caddie` ( + `user_id` smallint(5) NOT NULL default '0', + `element_id` mediumint(8) NOT NULL default '0', + PRIMARY KEY (`user_id`,`element_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_categories' --- - -DROP TABLE IF EXISTS phpwebgallery_categories; -CREATE TABLE phpwebgallery_categories ( - id smallint(5) unsigned NOT NULL auto_increment, - date_last date default NULL, - nb_images mediumint(8) unsigned NOT NULL default '0', - name varchar(255) NOT NULL default '', - id_uppercat smallint(5) unsigned default NULL, - comment text, - dir varchar(255) default NULL, - rank tinyint(3) unsigned default NULL, - status enum('public','private') NOT NULL default 'public', - site_id tinyint(4) unsigned default '1', - visible enum('true','false') NOT NULL default 'true', - uploadable enum('true','false') NOT NULL default 'false', - representative_picture_id mediumint(8) unsigned default NULL, - uppercats varchar(255) NOT NULL default '', - commentable enum('true','false') NOT NULL default 'true', - global_rank varchar(255) default NULL, - PRIMARY KEY (id), - KEY categories_i2 (id_uppercat) +-- Table structure for table `phpwebgallery_categories` +-- + +DROP TABLE IF EXISTS `phpwebgallery_categories`; +CREATE TABLE `phpwebgallery_categories` ( + `id` smallint(5) unsigned NOT NULL auto_increment, + `date_last` datetime default NULL, + `nb_images` mediumint(8) unsigned NOT NULL default '0', + `name` varchar(255) NOT NULL default '', + `id_uppercat` smallint(5) unsigned default NULL, + `comment` text, + `dir` varchar(255) default NULL, + `rank` tinyint(3) unsigned default NULL, + `status` enum('public','private') NOT NULL default 'public', + `site_id` tinyint(4) unsigned default '1', + `visible` enum('true','false') NOT NULL default 'true', + `uploadable` enum('true','false') NOT NULL default 'false', + `representative_picture_id` mediumint(8) unsigned default NULL, + `uppercats` varchar(255) NOT NULL default '', + `commentable` enum('true','false') NOT NULL default 'true', + `global_rank` varchar(255) default NULL, + PRIMARY KEY (`id`), + KEY `categories_i2` (`id_uppercat`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_comments' +-- Table structure for table `phpwebgallery_comments` -- -DROP TABLE IF EXISTS phpwebgallery_comments; -CREATE TABLE phpwebgallery_comments ( - id int(11) unsigned NOT NULL auto_increment, - image_id mediumint(8) unsigned NOT NULL default '0', - date datetime NOT NULL default '0000-00-00 00:00:00', - author varchar(255) default NULL, - content longtext, - validated enum('true','false') NOT NULL default 'false', - PRIMARY KEY (id) +DROP TABLE IF EXISTS `phpwebgallery_comments`; +CREATE TABLE `phpwebgallery_comments` ( + `id` int(11) unsigned NOT NULL auto_increment, + `image_id` mediumint(8) unsigned NOT NULL default '0', + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `author` varchar(255) default NULL, + `content` longtext, + `validated` enum('true','false') NOT NULL default 'false', + `validation_date` datetime default NULL, + PRIMARY KEY (`id`), + KEY `comments_i2` (`validation_date`), + KEY `comments_i1` (`image_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_config' +-- Table structure for table `phpwebgallery_config` -- -DROP TABLE IF EXISTS phpwebgallery_config; -CREATE TABLE phpwebgallery_config ( - param varchar(40) NOT NULL default '', - value varchar(255) default NULL, - comment varchar(255) default NULL, - PRIMARY KEY (param) +DROP TABLE IF EXISTS `phpwebgallery_config`; +CREATE TABLE `phpwebgallery_config` ( + `param` varchar(40) NOT NULL default '', + `value` varchar(255) default NULL, + `comment` varchar(255) default NULL, + PRIMARY KEY (`param`) ) TYPE=MyISAM COMMENT='configuration table'; -- --- Table structure for table 'phpwebgallery_favorites' +-- Table structure for table `phpwebgallery_favorites` -- -DROP TABLE IF EXISTS phpwebgallery_favorites; -CREATE TABLE phpwebgallery_favorites ( - user_id smallint(5) unsigned NOT NULL default '0', - image_id mediumint(8) unsigned NOT NULL default '0', - PRIMARY KEY (user_id,image_id) +DROP TABLE IF EXISTS `phpwebgallery_favorites`; +CREATE TABLE `phpwebgallery_favorites` ( + `user_id` smallint(5) unsigned NOT NULL default '0', + `image_id` mediumint(8) unsigned NOT NULL default '0', + PRIMARY KEY (`user_id`,`image_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_group_access' +-- Table structure for table `phpwebgallery_group_access` -- -DROP TABLE IF EXISTS phpwebgallery_group_access; -CREATE TABLE phpwebgallery_group_access ( - group_id smallint(5) unsigned NOT NULL default '0', - cat_id smallint(5) unsigned NOT NULL default '0', - PRIMARY KEY (group_id,cat_id) +DROP TABLE IF EXISTS `phpwebgallery_group_access`; +CREATE TABLE `phpwebgallery_group_access` ( + `group_id` smallint(5) unsigned NOT NULL default '0', + `cat_id` smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (`group_id`,`cat_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_groups' +-- Table structure for table `phpwebgallery_groups` -- -DROP TABLE IF EXISTS phpwebgallery_groups; -CREATE TABLE phpwebgallery_groups ( - id smallint(5) unsigned NOT NULL auto_increment, - name varchar(255) NOT NULL default '', - PRIMARY KEY (id) +DROP TABLE IF EXISTS `phpwebgallery_groups`; +CREATE TABLE `phpwebgallery_groups` ( + `id` smallint(5) unsigned NOT NULL auto_increment, + `name` varchar(255) NOT NULL default '', + PRIMARY KEY (`id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_history' +-- Table structure for table `phpwebgallery_history` -- -DROP TABLE IF EXISTS phpwebgallery_history; -CREATE TABLE phpwebgallery_history ( - date datetime NOT NULL default '0000-00-00 00:00:00', - login varchar(15) default NULL, - IP varchar(50) NOT NULL default '', - category varchar(150) default NULL, - file varchar(50) default NULL, - picture varchar(150) default NULL, - KEY history_i1 (date) +DROP TABLE IF EXISTS `phpwebgallery_history`; +CREATE TABLE `phpwebgallery_history` ( + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `login` varchar(15) default NULL, + `IP` varchar(50) NOT NULL default '', + `category` varchar(150) default NULL, + `file` varchar(50) default NULL, + `picture` varchar(150) default NULL, + KEY `history_i1` (`date`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_image_category' +-- Table structure for table `phpwebgallery_image_category` -- -DROP TABLE IF EXISTS phpwebgallery_image_category; -CREATE TABLE phpwebgallery_image_category ( - image_id mediumint(8) unsigned NOT NULL default '0', - category_id smallint(5) unsigned NOT NULL default '0', - PRIMARY KEY (image_id,category_id), - KEY image_category_i1 (image_id), - KEY image_category_i2 (category_id) +DROP TABLE IF EXISTS `phpwebgallery_image_category`; +CREATE TABLE `phpwebgallery_image_category` ( + `image_id` mediumint(8) unsigned NOT NULL default '0', + `category_id` smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (`image_id`,`category_id`), + KEY `image_category_i1` (`image_id`), + KEY `image_category_i2` (`category_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_images' --- - -DROP TABLE IF EXISTS phpwebgallery_images; -CREATE TABLE phpwebgallery_images ( - id mediumint(8) unsigned NOT NULL auto_increment, - file varchar(255) NOT NULL default '', - date_available date NOT NULL default '0000-00-00', - date_creation date default NULL, - tn_ext varchar(4) default '', - name varchar(255) default NULL, - comment text, - author varchar(255) default NULL, - hit int(10) unsigned NOT NULL default '0', - filesize mediumint(9) unsigned default NULL, - width smallint(9) unsigned default NULL, - height smallint(9) unsigned default NULL, - keywords varchar(255) default NULL, - storage_category_id smallint(5) unsigned default NULL, - representative_ext varchar(4) default NULL, - date_metadata_update date default NULL, - average_rate float(5,2) unsigned default NULL, - path varchar(255) NOT NULL default '', - PRIMARY KEY (id), - KEY images_i2 (date_available), - KEY images_i1 (storage_category_id), - KEY images_i3 (average_rate), - KEY images_i4 (hit), - KEY images_i5 (date_creation) +-- Table structure for table `phpwebgallery_images` +-- + +DROP TABLE IF EXISTS `phpwebgallery_images`; +CREATE TABLE `phpwebgallery_images` ( + `id` mediumint(8) unsigned NOT NULL auto_increment, + `file` varchar(255) NOT NULL default '', + `date_available` datetime NOT NULL default '0000-00-00 00:00:00', + `date_creation` date default NULL, + `tn_ext` varchar(4) default '', + `name` varchar(255) default NULL, + `comment` text, + `author` varchar(255) default NULL, + `hit` int(10) unsigned NOT NULL default '0', + `filesize` mediumint(9) unsigned default NULL, + `width` smallint(9) unsigned default NULL, + `height` smallint(9) unsigned default NULL, + `keywords` varchar(255) default NULL, + `storage_category_id` smallint(5) unsigned default NULL, + `representative_ext` varchar(4) default NULL, + `date_metadata_update` date default NULL, + `average_rate` float(5,2) unsigned default NULL, + `path` varchar(255) NOT NULL default '', + PRIMARY KEY (`id`), + KEY `images_i2` (`date_available`), + KEY `images_i1` (`storage_category_id`), + KEY `images_i3` (`average_rate`), + KEY `images_i4` (`hit`), + KEY `images_i5` (`date_creation`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_rate' +-- Table structure for table `phpwebgallery_rate` -- -DROP TABLE IF EXISTS phpwebgallery_rate; -CREATE TABLE phpwebgallery_rate ( - user_id smallint(5) unsigned NOT NULL default '0', - element_id mediumint(8) unsigned NOT NULL default '0', - rate tinyint(2) unsigned NOT NULL default '0', - PRIMARY KEY (user_id,element_id) +DROP TABLE IF EXISTS `phpwebgallery_rate`; +CREATE TABLE `phpwebgallery_rate` ( + `user_id` smallint(5) unsigned NOT NULL default '0', + `element_id` mediumint(8) unsigned NOT NULL default '0', + `rate` tinyint(2) unsigned NOT NULL default '0', + PRIMARY KEY (`user_id`,`element_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_sessions' +-- Table structure for table `phpwebgallery_sessions` -- -DROP TABLE IF EXISTS phpwebgallery_sessions; -CREATE TABLE phpwebgallery_sessions ( - id varchar(255) binary NOT NULL default '', - user_id smallint(5) unsigned NOT NULL default '0', - expiration datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (id) +DROP TABLE IF EXISTS `phpwebgallery_sessions`; +CREATE TABLE `phpwebgallery_sessions` ( + `id` varchar(255) binary NOT NULL default '', + `user_id` smallint(5) unsigned NOT NULL default '0', + `expiration` datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_sites' +-- Table structure for table `phpwebgallery_sites` -- -DROP TABLE IF EXISTS phpwebgallery_sites; -CREATE TABLE phpwebgallery_sites ( - id tinyint(4) NOT NULL auto_increment, - galleries_url varchar(255) NOT NULL default '', - PRIMARY KEY (id), - UNIQUE KEY sites_ui1 (galleries_url) +DROP TABLE IF EXISTS `phpwebgallery_sites`; +CREATE TABLE `phpwebgallery_sites` ( + `id` tinyint(4) NOT NULL auto_increment, + `galleries_url` varchar(255) NOT NULL default '', + PRIMARY KEY (`id`), + UNIQUE KEY `sites_ui1` (`galleries_url`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_user_access' +-- Table structure for table `phpwebgallery_user_access` -- -DROP TABLE IF EXISTS phpwebgallery_user_access; -CREATE TABLE phpwebgallery_user_access ( - user_id smallint(5) unsigned NOT NULL default '0', - cat_id smallint(5) unsigned NOT NULL default '0', - PRIMARY KEY (user_id,cat_id) +DROP TABLE IF EXISTS `phpwebgallery_user_access`; +CREATE TABLE `phpwebgallery_user_access` ( + `user_id` smallint(5) unsigned NOT NULL default '0', + `cat_id` smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (`user_id`,`cat_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_user_forbidden' +-- Table structure for table `phpwebgallery_user_forbidden` -- -DROP TABLE IF EXISTS phpwebgallery_user_forbidden; -CREATE TABLE phpwebgallery_user_forbidden ( - user_id smallint(5) unsigned NOT NULL default '0', - need_update enum('true','false') NOT NULL default 'true', - forbidden_categories text, - PRIMARY KEY (user_id) +DROP TABLE IF EXISTS `phpwebgallery_user_forbidden`; +CREATE TABLE `phpwebgallery_user_forbidden` ( + `user_id` smallint(5) unsigned NOT NULL default '0', + `need_update` enum('true','false') NOT NULL default 'true', + `forbidden_categories` text, + PRIMARY KEY (`user_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_user_group' +-- Table structure for table `phpwebgallery_user_group` -- -DROP TABLE IF EXISTS phpwebgallery_user_group; -CREATE TABLE phpwebgallery_user_group ( - user_id smallint(5) unsigned NOT NULL default '0', - group_id smallint(5) unsigned NOT NULL default '0', - PRIMARY KEY (group_id,user_id) +DROP TABLE IF EXISTS `phpwebgallery_user_group`; +CREATE TABLE `phpwebgallery_user_group` ( + `user_id` smallint(5) unsigned NOT NULL default '0', + `group_id` smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (`group_id`,`user_id`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_users' --- - -DROP TABLE IF EXISTS phpwebgallery_users; -CREATE TABLE phpwebgallery_users ( - id smallint(5) unsigned NOT NULL auto_increment, - username varchar(20) binary NOT NULL default '', - password varchar(255) NOT NULL default '', - mail_address varchar(255) default NULL, - nb_image_line tinyint(1) unsigned NOT NULL default '5', - nb_line_page tinyint(3) unsigned NOT NULL default '3', - status enum('admin','guest') NOT NULL default 'guest', - language varchar(50) NOT NULL default 'english', - maxwidth smallint(6) default NULL, - maxheight smallint(6) default NULL, - expand enum('true','false') NOT NULL default 'false', - show_nb_comments enum('true','false') NOT NULL default 'false', - recent_period tinyint(3) unsigned NOT NULL default '7', - template varchar(255) NOT NULL default 'default', - PRIMARY KEY (id), - UNIQUE KEY users_ui1 (username) +-- Table structure for table `phpwebgallery_users` +-- + +DROP TABLE IF EXISTS `phpwebgallery_users`; +CREATE TABLE `phpwebgallery_users` ( + `id` smallint(5) unsigned NOT NULL auto_increment, + `username` varchar(20) binary NOT NULL default '', + `password` varchar(32) default NULL, + `mail_address` varchar(255) default NULL, + `nb_image_line` tinyint(1) unsigned NOT NULL default '5', + `nb_line_page` tinyint(3) unsigned NOT NULL default '3', + `status` enum('admin','guest') NOT NULL default 'guest', + `language` varchar(50) NOT NULL default 'english', + `maxwidth` smallint(6) default NULL, + `maxheight` smallint(6) default NULL, + `expand` enum('true','false') NOT NULL default 'false', + `show_nb_comments` enum('true','false') NOT NULL default 'false', + `recent_period` tinyint(3) unsigned NOT NULL default '7', + `template` varchar(255) NOT NULL default 'default', + `last_feed_check` datetime default NULL, + `feed_id` varchar(50) binary default NULL, + `registration_date` datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`id`), + UNIQUE KEY `users_ui1` (`username`) ) TYPE=MyISAM; -- --- Table structure for table 'phpwebgallery_waiting' +-- Table structure for table `phpwebgallery_waiting` -- -DROP TABLE IF EXISTS phpwebgallery_waiting; -CREATE TABLE phpwebgallery_waiting ( - id int(10) unsigned NOT NULL auto_increment, - storage_category_id smallint(5) unsigned NOT NULL default '0', - file varchar(255) NOT NULL default '', - username varchar(255) NOT NULL default '', - mail_address varchar(255) NOT NULL default '', - date int(10) unsigned NOT NULL default '0', - tn_ext char(3) default NULL, - validated enum('true','false') NOT NULL default 'false', - infos text, - PRIMARY KEY (id) +DROP TABLE IF EXISTS `phpwebgallery_waiting`; +CREATE TABLE `phpwebgallery_waiting` ( + `id` int(10) unsigned NOT NULL auto_increment, + `storage_category_id` smallint(5) unsigned NOT NULL default '0', + `file` varchar(255) NOT NULL default '', + `username` varchar(255) NOT NULL default '', + `mail_address` varchar(255) NOT NULL default '', + `date` int(10) unsigned NOT NULL default '0', + `tn_ext` char(3) default NULL, + `validated` enum('true','false') NOT NULL default 'false', + `infos` text, + PRIMARY KEY (`id`) ) TYPE=MyISAM; |