field comments.date becomes a datetime MySQL field type (instead of int)

git-svn-id: http://piwigo.org/svn/trunk@420 68402e56-0260-453c-a942-63ccdbb3a9ee

5 files changed, 173 insertions, 157 deletions

diff --git a/comments.php b/comments.php
index 1ae42749f..f829e44d7 100644
--- a/comments.php
+++ b/comments.php
@@ -91,13 +91,16 @@ $template->assign_vars(array(
 );
 
 foreach ( $conf['last_days'] as $option ) {
-  $url = $PHP_SELF.'?last_days='.($option - 1);
+  $url = $_SERVER['PHP_SELF'].'?last_days='.($option - 1);
   if (defined('IN_ADMIN')) $url.= '&page=comments';
-  $template->assign_block_vars('last_day_option', array (
-    'OPTION'=>$option,
-	'T_STYLE'=>(( $option == MAX_DAYS + 1 )?'text-decoration:underline;':''),
-	'U_OPTION'=>add_session_id( $url )
-	));
+  $template->assign_block_vars(
+    'last_day_option',
+    array(
+      'OPTION'=>$option,
+      'T_STYLE'=>(( $option == MAX_DAYS + 1 )?'text-decoration:underline;':''),
+      'U_OPTION'=>add_session_id( $url )
+      )
+    );
 }
 
 // 1. retrieving picture ids which have comments recently added
@@ -109,7 +112,7 @@ $query.= '(ic.category_id) as category_id';
 $query.= ' FROM '.COMMENTS_TABLE.' AS c';
 $query.= ', '.IMAGE_CATEGORY_TABLE.' AS ic';
 $query.= ' WHERE c.image_id = ic.image_id';
-$query.= ' AND date > '.$maxtime;
+$query.= ' AND date > FROM_UNIXTIME('.$maxtime.')';
 if ( $user['status'] != 'admin' )
 {
   $query.= " AND validated = 'true'";
@@ -173,7 +176,7 @@ while ( $row = mysql_fetch_array( $result ) )
     // for each picture, retrieving all comments
     $query = 'SELECT * FROM '.COMMENTS_TABLE;
     $query.= ' WHERE image_id = '.$row['image_id'];
-    $query.= ' AND date > '.$maxtime;
+    $query.= ' AND date > FROM_UNIXTIME('.$maxtime.')';
 	if ( $user['status'] != 'admin' )
     {
       $query.= " AND validated = 'true'";
@@ -201,18 +204,20 @@ while ( $row = mysql_fetch_array( $result ) )
       $pattern = '/\/([^\s]*)\//';
       $replacement = '<span style="font-style:italic;">\1</span>';
       $content = preg_replace( $pattern, $replacement, $content );
-      $template->assign_block_vars('picture.comment',array(
-	    'COMMENT_AUTHOR'=>$author,
-		'COMMENT_DATE'=>format_date( $subrow['date'], 'unix', true ),
-		'COMMENT'=>$content,
-		));
-		if ( $user['status'] == 'admin' )
-		{
-		  $template->assign_block_vars('picture.comment.validation', array(
-		    'ID'=> $subrow['id'],
-			'CHECKED'=>($subrow['validated']=='false')?'checked="checked"': ''
-			));
-		}
+      $template->assign_block_vars(
+        'picture.comment',array(
+          'COMMENT_AUTHOR'=>$author,
+          'COMMENT_DATE'=>format_date( $subrow['date'],'mysql_datetime',true ),
+          'COMMENT'=>$content,
+          ));
+      if ( $user['status'] == 'admin' )
+      {
+        $template->assign_block_vars(
+          'picture.comment.validation', array(
+            'ID'=> $subrow['id'],
+            'CHECKED'=>($subrow['validated']=='false')?'checked="checked"': ''
+            ));
+      }
     }
   }
 //----------------------------------------------------------- html code display
diff --git a/include/functions.inc.php b/include/functions.inc.php
index 4337cf81a..01f13f78d 100644
--- a/include/functions.inc.php
+++ b/include/functions.inc.php
@@ -374,6 +374,12 @@ function format_date( $date, $type = 'us', $show_time = false )
   case 'unix' :
     $unixdate = $date;
     break;
+  case 'mysql_datetime' :
+    preg_match( '/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/',
+                $date, $matches );
+    $unixdate = mktime($matches[4],$matches[5],$matches[6],
+                       $matches[2],$matches[3],$matches[1]);
+    break;
   }
   $formated_date = $lang['day'][date( "w", $unixdate )];
   $formated_date.= date( " j ", $unixdate );
diff --git a/install/dbscheme.txt b/install/dbscheme.txt
index 3ee5b8ea6..06c757949 100644
--- a/install/dbscheme.txt
+++ b/install/dbscheme.txt
@@ -1,133 +1,137 @@
-table      categories
-table      comments
-table      config
-table      favorites
-table      group_access
-table      groups
-table      history
-table      image_category
-table      images
-table      sessions
-table      sites
-table      user_access
-table      user_category
-table      user_group
-table      users
-table      waiting
-column     id                             categories           smallint(5) unsigned
-column     date_last                      categories           date
-column     nb_images                      categories           mediumint(8) unsigned
-column     name                           categories           varchar(255)
-column     id_uppercat                    categories           smallint(5) unsigned
-column     comment                        categories           text
-column     dir                            categories           varchar(255)
-column     rank                           categories           tinyint(3) unsigned
-column     status                         categories           enum('public','private')
-column     site_id                        categories           tinyint(4) unsigned
-column     visible                        categories           enum('true','false')
-column     uploadable                     categories           enum('true','false')
-column     representative_picture_id      categories           mediumint(8) unsigned
-column     uppercats                      categories           varchar(255)
-column     id                             comments             int(11) unsigned
-column     image_id                       comments             mediumint(8) unsigned
-column     date                           comments             int(11) unsigned
-column     author                         comments             varchar(255)
-column     content                        comments             longtext
-column     validated                      comments             enum('true','false')
-column     param                          config               varchar(40)
-column     value                          config               varchar(255)
-column     comment                        config               varchar(255)
-column     user_id                        favorites            smallint(5) unsigned
-column     image_id                       favorites            mediumint(8) unsigned
-column     group_id                       group_access         smallint(5) unsigned
-column     cat_id                         group_access         smallint(5) unsigned
-column     id                             groups               smallint(5) unsigned
-column     name                           groups               varchar(255)
-column     date                           history              int(11)
-column     login                          history              varchar(15)
-column     IP                             history              varchar(50)
-column     category                       history              varchar(150)
-column     file                           history              varchar(50)
-column     picture                        history              varchar(150)
-column     image_id                       image_category       mediumint(8) unsigned
-column     category_id                    image_category       smallint(5) unsigned
-column     id                             images               mediumint(8) unsigned
-column     file                           images               varchar(255)
-column     date_available                 images               date
-column     date_creation                  images               date
-column     tn_ext                         images               char(3)
-column     name                           images               varchar(255)
-column     comment                        images               text
-column     author                         images               varchar(255)
-column     hit                            images               int(10) unsigned
-column     filesize                       images               mediumint(9) unsigned
-column     width                          images               smallint(9) unsigned
-column     height                         images               smallint(9) unsigned
-column     keywords                       images               varchar(255)
-column     storage_category_id            images               smallint(5) unsigned
-column     id                             sessions             varchar(255) binary
-column     user_id                        sessions             smallint(5) unsigned
-column     expiration                     sessions             int(10) unsigned
-column     ip                             sessions             varchar(255)
-column     id                             sites                tinyint(4)
-column     galleries_url                  sites                varchar(255)
-column     user_id                        user_access          smallint(5) unsigned
-column     cat_id                         user_access          smallint(5) unsigned
-column     user_id                        user_category        smallint(5) unsigned
-column     category_id                    user_category        smallint(5) unsigned
-column     date_last                      user_category        date
-column     nb_sub_categories              user_category        smallint(5) unsigned
-column     user_id                        user_group           smallint(5) unsigned
-column     group_id                       user_group           smallint(5) unsigned
-column     id                             users                smallint(5) unsigned
-column     username                       users                varchar(20) binary
-column     password                       users                varchar(255)
-column     mail_address                   users                varchar(255)
-column     nb_image_line                  users                tinyint(1) unsigned
-column     nb_line_page                   users                tinyint(3) unsigned
-column     status                         users                enum('admin','guest')
-column     language                       users                varchar(50)
-column     maxwidth                       users                smallint(6)
-column     maxheight                      users                smallint(6)
-column     expand                         users                enum('true','false')
-column     show_nb_comments               users                enum('true','false')
-column     short_period                   users                tinyint(3) unsigned
-column     long_period                    users                tinyint(3) unsigned
-column     template                       users                varchar(255)
-column     forbidden_categories           users                text
-column     id                             waiting              int(10) unsigned
-column     storage_category_id            waiting              smallint(5) unsigned
-column     file                           waiting              varchar(255)
-column     username                       waiting              varchar(255)
-column     mail_address                   waiting              varchar(255)
-column     date                           waiting              int(10) unsigned
-column     tn_ext                         waiting              char(3)
-column     validated                      waiting              enum('true','false')
-column     infos                          waiting              text
-pk         id                             categories     
-pk         id                             comments       
-pk         param                          config         
-pk         user_id                        favorites      
-pk         image_id                       favorites      
-pk         group_id                       group_access   
-pk         cat_id                         group_access   
-pk         id                             groups         
-pk         image_id                       image_category 
-pk         category_id                    image_category 
-pk         id                             images         
-pk         id                             sessions       
-pk         id                             sites          
-pk         user_id                        user_access    
-pk         cat_id                         user_access    
-pk         user_id                        user_category  
-pk         category_id                    user_category  
-pk         group_id                       user_group     
-pk         user_id                        user_group     
-pk         id                             users          
-pk         id                             waiting        
-index      categories_i2                  categories           id_uppercat
-index      image_category_i1              image_category       image_id
-index      image_category_i2              image_category       category_id
-index      storage_category_id            images               storage_category_id
-index      sites_ui1                      sites                galleries_url
-index      users_ui1                      users                username
+
+table:categories
+table:comments
+table:config
+table:favorites
+table:group_access
+table:groups
+table:history
+table:image_category
+table:images
+table:sessions
+table:sites
+table:user_access
+table:user_category
+table:user_group
+table:users
+table:waiting
+
+column:id                        table:categories     type:smallint                 nullable:Y length:5   signed:N
+column:date_last                 table:categories     type:date                     nullable:N
+column:nb_images                 table:categories     type:mediumint                nullable:Y length:8   signed:N
+column:name                      table:categories     type:varchar                  nullable:Y length:255 binary:N
+column:id_uppercat               table:categories     type:smallint                 nullable:N length:5   signed:N
+column:comment                   table:categories     type:text                     nullable:N
+column:dir                       table:categories     type:varchar                  nullable:N length:255 binary:N
+column:rank                      table:categories     type:tinyint                  nullable:N length:3   signed:N
+column:status                    table:categories     type:enum('public','private') nullable:Y
+column:site_id                   table:categories     type:tinyint                  nullable:Y length:4   signed:N
+column:visible                   table:categories     type:enum('true','false')     nullable:Y
+column:uploadable                table:categories     type:enum('true','false')     nullable:Y
+column:representative_picture_id table:categories     type:mediumint                nullable:N length:8   signed:N
+column:uppercats                 table:categories     type:varchar                  nullable:Y length:255 binary:N
+column:id                        table:comments       type:int                      nullable:Y length:11  signed:N
+column:image_id                  table:comments       type:mediumint                nullable:Y length:8   signed:N
+column:date                      table:comments       type:datetime                 nullable:Y
+column:author                    table:comments       type:varchar                  nullable:N length:255 binary:N
+column:content                   table:comments       type:longtext                 nullable:N
+column:validated                 table:comments       type:enum('true','false')     nullable:Y
+column:param                     table:config         type:varchar                  nullable:Y length:40  binary:N
+column:value                     table:config         type:varchar                  nullable:N length:255 binary:N
+column:comment                   table:config         type:varchar                  nullable:N length:255 binary:N
+column:user_id                   table:favorites      type:smallint                 nullable:Y length:5   signed:N
+column:image_id                  table:favorites      type:mediumint                nullable:Y length:8   signed:N
+column:group_id                  table:group_access   type:smallint                 nullable:Y length:5   signed:N
+column:cat_id                    table:group_access   type:smallint                 nullable:Y length:5   signed:N
+column:id                        table:groups         type:smallint                 nullable:Y length:5   signed:N
+column:name                      table:groups         type:varchar                  nullable:Y length:255 binary:N
+column:date                      table:history        type:int                      nullable:Y length:11  signed:Y
+column:login                     table:history        type:varchar                  nullable:N length:15  binary:N
+column:IP                        table:history        type:varchar                  nullable:Y length:50  binary:N
+column:category                  table:history        type:varchar                  nullable:N length:150 binary:N
+column:file                      table:history        type:varchar                  nullable:N length:50  binary:N
+column:picture                   table:history        type:varchar                  nullable:N length:150 binary:N
+column:image_id                  table:image_category type:mediumint                nullable:Y length:8   signed:N
+column:category_id               table:image_category type:smallint                 nullable:Y length:5   signed:N
+column:id                        table:images         type:mediumint                nullable:Y length:8   signed:N
+column:file                      table:images         type:varchar                  nullable:Y length:255 binary:N
+column:date_available            table:images         type:date                     nullable:Y
+column:date_creation             table:images         type:date                     nullable:N
+column:tn_ext                    table:images         type:char                     nullable:Y length:3   binary:N
+column:name                      table:images         type:varchar                  nullable:N length:255 binary:N
+column:comment                   table:images         type:text                     nullable:N
+column:author                    table:images         type:varchar                  nullable:N length:255 binary:N
+column:hit                       table:images         type:int                      nullable:Y length:10  signed:N
+column:filesize                  table:images         type:mediumint                nullable:N length:9   signed:N
+column:width                     table:images         type:smallint                 nullable:N length:9   signed:N
+column:height                    table:images         type:smallint                 nullable:N length:9   signed:N
+column:keywords                  table:images         type:varchar                  nullable:N length:255 binary:N
+column:storage_category_id       table:images         type:smallint                 nullable:N length:5   signed:N
+column:id                        table:sessions       type:varchar                  nullable:Y length:255 binary:Y
+column:user_id                   table:sessions       type:smallint                 nullable:Y length:5   signed:N
+column:expiration                table:sessions       type:int                      nullable:Y length:10  signed:N
+column:ip                        table:sessions       type:varchar                  nullable:Y length:255 binary:N
+column:id                        table:sites          type:tinyint                  nullable:Y length:4   signed:Y
+column:galleries_url             table:sites          type:varchar                  nullable:Y length:255 binary:N
+column:user_id                   table:user_access    type:smallint                 nullable:Y length:5   signed:N
+column:cat_id                    table:user_access    type:smallint                 nullable:Y length:5   signed:N
+column:user_id                   table:user_category  type:smallint                 nullable:Y length:5   signed:N
+column:category_id               table:user_category  type:smallint                 nullable:Y length:5   signed:N
+column:date_last                 table:user_category  type:date                     nullable:N
+column:nb_sub_categories         table:user_category  type:smallint                 nullable:Y length:5   signed:N
+column:user_id                   table:user_group     type:smallint                 nullable:Y length:5   signed:N
+column:group_id                  table:user_group     type:smallint                 nullable:Y length:5   signed:N
+column:id                        table:users          type:smallint                 nullable:Y length:5   signed:N
+column:username                  table:users          type:varchar                  nullable:Y length:20  binary:Y
+column:password                  table:users          type:varchar                  nullable:Y length:255 binary:N
+column:mail_address              table:users          type:varchar                  nullable:N length:255 binary:N
+column:nb_image_line             table:users          type:tinyint                  nullable:Y length:1   signed:N
+column:nb_line_page              table:users          type:tinyint                  nullable:Y length:3   signed:N
+column:status                    table:users          type:enum('admin','guest')    nullable:Y
+column:language                  table:users          type:varchar                  nullable:Y length:50  binary:N
+column:maxwidth                  table:users          type:smallint                 nullable:N length:6   signed:Y
+column:maxheight                 table:users          type:smallint                 nullable:N length:6   signed:Y
+column:expand                    table:users          type:enum('true','false')     nullable:Y
+column:show_nb_comments          table:users          type:enum('true','false')     nullable:Y
+column:short_period              table:users          type:tinyint                  nullable:Y length:3   signed:N
+column:long_period               table:users          type:tinyint                  nullable:Y length:3   signed:N
+column:template                  table:users          type:varchar                  nullable:Y length:255 binary:N
+column:forbidden_categories      table:users          type:text                     nullable:N
+column:id                        table:waiting        type:int                      nullable:Y length:10  signed:N
+column:storage_category_id       table:waiting        type:smallint                 nullable:Y length:5   signed:N
+column:file                      table:waiting        type:varchar                  nullable:Y length:255 binary:N
+column:username                  table:waiting        type:varchar                  nullable:Y length:255 binary:N
+column:mail_address              table:waiting        type:varchar                  nullable:Y length:255 binary:N
+column:date                      table:waiting        type:int                      nullable:Y length:10  signed:N
+column:tn_ext                    table:waiting        type:char                     nullable:N length:3   binary:N
+column:validated                 table:waiting        type:enum('true','false')     nullable:Y
+column:infos                     table:waiting        type:text                     nullable:N
+
+PK:categories_pk     table:categories     column:id
+PK:comments_pk       table:comments       column:id
+PK:config_pk         table:config         column:param
+PK:favorites_pk      table:favorites      column:user_id
+PK:favorites_pk      table:favorites      column:image_id
+PK:group_access_pk   table:group_access   column:group_id
+PK:group_access_pk   table:group_access   column:cat_id
+PK:groups_pk         table:groups         column:id
+PK:image_category_pk table:image_category column:image_id
+PK:image_category_pk table:image_category column:category_id
+PK:images_pk         table:images         column:id
+PK:sessions_pk       table:sessions       column:id
+PK:sites_pk          table:sites          column:id
+PK:user_access_pk    table:user_access    column:user_id
+PK:user_access_pk    table:user_access    column:cat_id
+PK:user_category_pk  table:user_category  column:user_id
+PK:user_category_pk  table:user_category  column:category_id
+PK:user_group_pk     table:user_group     column:group_id
+PK:user_group_pk     table:user_group     column:user_id
+PK:users_pk          table:users          column:id
+PK:waiting_pk        table:waiting        column:id
+
+index:categories_i2       table:categories     column:id_uppercat
+index:image_category_i1   table:image_category column:image_id
+index:image_category_i2   table:image_category column:category_id
+index:storage_category_id table:images         column:storage_category_id
+index:sites_ui1           table:sites          column:galleries_url
+index:users_ui1           table:users          column:username
diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql
index ba3102af0..a6d28e7be 100644
--- a/install/phpwebgallery_structure.sql
+++ b/install/phpwebgallery_structure.sql
@@ -36,7 +36,7 @@ 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 int(11) 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',
diff --git a/picture.php b/picture.php
index 07b72167e..6f1c7c149 100644
--- a/picture.php
+++ b/picture.php
@@ -24,6 +24,7 @@
 // | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
 // | USA.                                                                  |
 // +-----------------------------------------------------------------------+
+
 //----------------------------------------------------------- include
 define('PHPWG_ROOT_PATH','./');
 include_once( PHPWG_ROOT_PATH.'include/common.inc.php' );    
@@ -224,7 +225,7 @@ if ( isset( $_POST['content'] ) && !empty($_POST['content']) )
     // anti-flood system
     $reference_date = time() - $conf['anti-flood_time'];
     $query = 'SELECT id FROM '.COMMENTS_TABLE;
-    $query.= ' WHERE date > '.$reference_date;
+    $query.= ' WHERE date > FROM_UNIXTIME('.$reference_date.')';
     $query.= " AND author = '".$author."'";
     $query.= ';';
     if ( mysql_num_rows( mysql_query( $query ) ) == 0
@@ -233,7 +234,7 @@ if ( isset( $_POST['content'] ) && !empty($_POST['content']) )
       $query = 'INSERT INTO '.COMMENTS_TABLE;
       $query.= ' (author,date,image_id,content,validated) VALUES (';
       $query.= "'".$author."'";
-      $query.= ','.time().','.$_GET['image_id'];
+      $query.= ',NOW(),'.$_GET['image_id'];
       $query.= ",'".htmlspecialchars( $_POST['content'], ENT_QUOTES)."'";
       if ( !$conf['comments_validation'] or $user['status'] == 'admin' )
       {        
@@ -577,7 +578,7 @@ if ( $conf['show_comments'] )
 	
     $template->assign_block_vars('comments.comment', array(
     'COMMENT_AUTHOR'=>empty($row['author'])?$lang['guest']:$row['author'],
-    'COMMENT_DATE'=>format_date( $row['date'], 'unix', true ),
+    'COMMENT_DATE'=>format_date( $row['date'], 'mysql_datetime', true ),
 	'COMMENT'=>$content
 	));