Différences entre les versions de « Migration de Gallery2 a Piwigo »

De April MediaWiki
Aller à la navigationAller à la recherche
Ligne 53 : Ligne 53 :
 
** appliquer.
 
** appliquer.
 
** Boire des coups en attendant.
 
** Boire des coups en attendant.
 +
 +
== Application du script ==
 +
 +
* Dumper la base piwigo au cas où :
 +
mysqldump -u root -pxxblingxx piwigodb > dumppiwigodb.sql
 +
* Télécharger le script convertcomments.pl donné dans la section suivante, ou bien https://raw.githubusercontent.com/dschwen/g2piwigo/master/convertcomments.pl s'il est plus récent (projet: https://github.com/dschwen/g2piwigo).
 +
* chmod u+x ../convertcomments.pl
 +
* dans /var/www/piwigo/galleries exécuter :
 +
<syntaxhighlight source="bash">
 +
find | grep -v /thumbnail | grep -v /pwg_high | cut -c3- | ../convertcomments.pl --menalto-dbname=gallery2 --menalto-dbuser=blingblang --menalto-dbpass=blingblang  --piwigo-dbname=piwigodb --piwigo-dbuser=blingblang --piwigo-dbpass=blingblang --piwigo-prefix=piwigo_ --menalto-table-prefix=g2_e --menalto-column-prefix=g_ > log
 +
</syntaxhighlight>
 +
* aller boire un café.
 +
* quand c'est fini, il reste à jardiner un peu : reformater à la main certains textes, etc. La migration de bbcode vers html n'est pas bulletproof. Une façon de détecter les problèmes est de rechercher des crochets dans le log.
  
 
== NEXT: PoluX ==
 
== NEXT: PoluX ==
 
* récupérer les keywords
 
* récupérer les keywords
 +
 +
== Le script ==
 +
 +
<syntaxhighlight lang="perl">
 +
#!/usr/bin/perl
 +
 +
my $usage = "\n
 +
    in the piwigo galleries folder do\n
 +
    find | grep -v /thumbnail | grep -v /pwg_high | cut -c3- | ../convertcomments.pl --menalto-dbname=gl2_database --menalto-dbuser= --menalto-dbpass=gl2_db_password --menalto-table-prefix=gl2_ --menalto-column-prefix=g_ --piwigo-dbname=piwigo_database --piwigo-dbuser=piwigo --piwigo-dbpass=piwigo_db_password\n";
 +
 +
use DBI;
 +
use DBD::mysql;
 +
use Getopt::Long;
 +
 +
my @opt_mandatory = qw/
 +
    menalto-dbname=s
 +
    menalto-dbuser=s
 +
    menalto-dbpass=s
 +
    piwigo-dbname=s
 +
    piwigo-dbuser=s
 +
    piwigo-dbpass=s
 +
/;
 +
 +
my @opt_optional = qw/
 +
    menalto-dbhost=s
 +
    menalto-table-prefix=s
 +
    menalto-column-prefix=s
 +
    piwigo-dbhost=s
 +
    piwigo-prefix=s
 +
/;
 +
 +
my %opt = ();
 +
GetOptions(
 +
    \%opt,
 +
    @opt_mandatory,
 +
    @opt_optional
 +
);
 +
 +
# use Data::Dumper; print Dumper(\%opt); exit();
 +
 +
foreach my $param (@opt_mandatory) {
 +
    $param =~ s/=s$//;
 +
    if (not defined $opt{$param}) {
 +
        print '--'.$param.' is mandatory'."\n";
 +
        print $usage;
 +
        exit();
 +
    }
 +
}
 +
 +
if (not defined $opt{'menalto-dbhost'}) {
 +
    $opt{'menalto-dbhost'} = 'localhost';
 +
}
 +
 +
if (not defined $opt{'menalto-table-prefix'}) {
 +
    $opt{'menalto-table-prefix'} = 'g_';
 +
}
 +
 +
if (not defined $opt{'menalto-column-prefix'}) {
 +
    $opt{'menalto-column-prefix'} = '';
 +
}
 +
 +
if (not defined $opt{'piwigo-dbhost'}) {
 +
    $opt{'piwigo-dbhost'} = 'localhost';
 +
}
 +
 +
if (not defined $opt{'piwigo-prefix'}) {
 +
    $opt{'piwigo-prefix'} = '';
 +
}
 +
 +
$ds1 = "dbi:mysql:".$opt{'menalto-dbname'}.":".$opt{'menalto-dbhost'}.":3306";
 +
$db1 = DBI->connect( $ds1, $opt{'menalto-dbuser'}, $opt{'menalto-dbpass'}, { PrintError => 1})
 +
    or die $DBI::errstr;
 +
 +
$ds2 = "dbi:mysql:".$opt{'piwigo-dbname'}.":".$opt{'piwigo-dbhost'}.":3306";
 +
$db2 = DBI->connect( $ds2, $opt{'piwigo-dbuser'}, $opt{'piwigo-dbpass'}, { PrintError => 1})
 +
    or die $DBI::errstr;
 +
 +
# Gallery2 parent Ids (root is always 7!)
 +
@ids = ( 7,0,0,0,0,0 );
 +
# piwigo uppercats
 +
@uct = ( "NULL",0,0,0,0,0 );
 +
@ranks = ();
 +
 +
while(<STDIN>) {
 +
  s/\n//g;
 +
  $dir = $_;
 +
  @path = split(/\//);
 +
  $level = int(@path);
 +
  next if( $level == 0 );
 +
 +
  $parentId = $ids[$level-1];
 +
 +
  # get id and title/summary/description of tail element in path 
 +
  $query = "
 +
SELECT
 +
    f.".$opt{'menalto-column-prefix'}."id,
 +
    i.".$opt{'menalto-column-prefix'}."title,
 +
    i.".$opt{'menalto-column-prefix'}."summary,
 +
    i.".$opt{'menalto-column-prefix'}."description,
 +
    i.".$opt{'menalto-column-prefix'}."canContainChildren,
 +
    a.".$opt{'menalto-column-prefix'}."orderWeight,
 +
    a.".$opt{'menalto-column-prefix'}."viewCount,
 +
    FROM_UNIXTIME(e.".$opt{'menalto-column-prefix'}."creationTimestamp)
 +
  FROM ".$opt{'menalto-table-prefix'}."Item i
 +
    JOIN ".$opt{'menalto-table-prefix'}."FileSystemEntity f ON i.".$opt{'menalto-column-prefix'}."id = f.".$opt{'menalto-column-prefix'}."id
 +
    JOIN ".$opt{'menalto-table-prefix'}."ChildEntity c ON f.".$opt{'menalto-column-prefix'}."id = c.".$opt{'menalto-column-prefix'}."id
 +
    JOIN ".$opt{'menalto-table-prefix'}."ItemAttributesMap a ON i.".$opt{'menalto-column-prefix'}."id = a.".$opt{'menalto-column-prefix'}."itemId
 +
    JOIN ".$opt{'menalto-table-prefix'}."Entity e ON e.".$opt{'menalto-column-prefix'}."id = i.".$opt{'menalto-column-prefix'}."id
 +
  WHERE c.".$opt{'menalto-column-prefix'}."parentId = ".$db1->quote($parentId)."
 +
    AND f.".$opt{'menalto-column-prefix'}."pathComponent=".$db1->quote($path[$level-1])."
 +
;";
 +
 +
  $sth = $db1->prepare($query);
 +
  $sth->execute;
 +
  @row = $sth->fetchrow();
 +
  $sth->finish;
 +
 +
  #print "$row[4] - $parentId -> $row[0] : $row[1] $row[2] $row[3]\n";
 +
  $title = remove_bbcode($row[1]);
 +
  $summary = remove_bbcode($row[2]);
 +
  $description = remove_bbcode($row[3]);
 +
  $weight = $row[5];
 +
  $views = $row[6];
 +
  $date_available = $row[7];
 +
  $ids[$level] = $row[0];
 +
  $pid{$row[0]}=$dir;
 +
 +
  if( $row[4] == 0 ) {
 +
    # image
 +
    $comment = "";
 +
    if( $summary ne "" && $description ne "" ) {
 +
      $comment = "<b>$summary</b> - $description";
 +
    } else {
 +
      if( $summary ne "" ) {
 +
        $comment = $summary;
 +
      } else {
 +
        $comment = $description;
 +
      }
 +
    }
 +
 +
    $query = "
 +
UPDATE ".$opt{'piwigo-prefix'}."images
 +
  SET name=".$db2->quote($title)."
 +
    , comment=".$db2->quote($comment)."
 +
    , date_available='".$date_available."'
 +
  WHERE path = ".$db2->quote("./galleries/".$dir)." ";
 +
    print "$query\n";
 +
    $sth = $db2->prepare($query);
 +
    $sth->execute;
 +
    $sth->finish;
 +
 +
    # build a map from gallery2 ids to piwigo image ids
 +
    $query = "SELECT id FROM ".$opt{'piwigo-prefix'}."images WHERE path = ".$db2->quote("./galleries/".$dir);
 +
    $sth = $db2->prepare($query);
 +
    $sth->execute;
 +
    ($iid{$row[0]}) = $sth->fetchrow();
 +
    $sth->finish;
 +
 +
  } else {
 +
    # folder
 +
    $comment = "";
 +
    if( $summary ne "" && $description ne "" ) {
 +
      $comment = "$summary <!--complete--> $description";
 +
    } else {
 +
      if( $summary ne "" ) {
 +
        $comment = $summary;
 +
      } else {
 +
        $comment = "<!--complete-->$description";
 +
      }
 +
    }
 +
 +
    # get piwigo category id
 +
    $uc = "= ".$uct[$level-1];
 +
    $uc = "IS NULL" if( $uct[$level-1] eq "NULL" );
 +
    $query = "SELECT id FROM ".$opt{'piwigo-prefix'}."categories WHERE dir = ".$db2->quote($path[$level-1])." AND id_uppercat $uc";
 +
    $sth = $db2->prepare($query);
 +
    $sth->execute;
 +
    @row = $sth->fetchrow();
 +
    $sth->finish;
 +
    $id = $row[0];
 +
    $uct[$level] = $id;
 +
 +
    # build global_rank string
 +
    $grank = "";
 +
    for($i=1;$i<$level;$i++ ) {
 +
      $grank .= $ranks[$i].".";
 +
    }
 +
    $grank .= $weight;
 +
    $ranks[$level]=$weight;
 +
 +
    $query = "UPDATE ".$opt{'piwigo-prefix'}."categories SET name=".$db2->quote($title).", comment=".$db2->quote($comment).", rank=$weight, global_rank=".$db2->quote($grank)." WHERE id = $id";
 +
    print "$query\n";
 +
    $sth = $db2->prepare($query);
 +
    $sth->execute;
 +
    $sth->finish;
 +
 +
    # get highlight picture
 +
    $query = "
 +
SELECT d2.".$opt{'menalto-column-prefix'}."derivativeSourceId
 +
  FROM ".$opt{'menalto-table-prefix'}."ChildEntity c
 +
    JOIN ".$opt{'menalto-table-prefix'}."Derivative d1 ON c.".$opt{'menalto-column-prefix'}."id = d1.".$opt{'menalto-column-prefix'}."id
 +
    JOIN ".$opt{'menalto-table-prefix'}."Derivative d2 ON d1.".$opt{'menalto-column-prefix'}."derivativeSourceId=d2.".$opt{'menalto-column-prefix'}."id
 +
  WHERE c.".$opt{'menalto-column-prefix'}."parentId = ".$ids[$level];
 +
    $sth = $db1->prepare($query);
 +
    $sth->execute;
 +
    ($hid{$id}) = $sth->fetchrow();
 +
    $sth->finish;
 +
}
 +
}
 +
 +
# apply highlites as representative images
 +
while(($key, $value) = each(%hid)) {
 +
  print "$key $value $pid{$value}\n"; 
 +
 +
  # get piwigo picture id
 +
  $query="SELECT id from ".$opt{'piwigo-prefix'}."images WHERE path = ".$db2->quote("./galleries/".$pid{$value});
 +
  print "$query\n";
 +
  $sth = $db2->prepare($query);
 +
  $sth->execute;
 +
  ($id) = $sth->fetchrow();
 +
  $sth->finish;
 +
 +
  $query = "UPDATE ".$opt{'piwigo-prefix'}."categories SET representative_picture_id =".$db2->quote($id)." WHERE id = ".$db2->quote($key);
 +
  print "$query\n";
 +
  $sth = $db2->prepare($query);
 +
  $sth->execute;
 +
  $sth->finish;
 +
}
 +
 +
# copy comments
 +
$query = "
 +
SELECT
 +
    c.".$opt{'menalto-column-prefix'}."parentId,
 +
    t.".$opt{'menalto-column-prefix'}."subject,
 +
    t.".$opt{'menalto-column-prefix'}."comment,
 +
    t.".$opt{'menalto-column-prefix'}."author,
 +
    t.".$opt{'menalto-column-prefix'}."date
 +
  FROM ".$opt{'menalto-table-prefix'}."ChildEntity c
 +
    JOIN ".$opt{'menalto-table-prefix'}."Comment t ON t.".$opt{'menalto-column-prefix'}."id = c.".$opt{'menalto-column-prefix'}."id
 +
  WHERE t.".$opt{'menalto-column-prefix'}."publishStatus=0
 +
";
 +
$sth2 = $db1->prepare($query);
 +
$sth2->execute;
 +
while( ($id,$subject,$comment,$author,$date) = $sth2->fetchrow() ) {
 +
  # FROM_UNIXTIME($date)
 +
  if( $iid{$id} ) {
 +
    if( $subject ne "" ) {
 +
      $comment = "<b>$subject</b> $comment";
 +
    }
 +
    $query = "INSERT INTO ".$opt{'piwigo-prefix'}."comments (image_id,date,author,content,validated) VALUES (".$db2->quote($iid{$id}).",FROM_UNIXTIME($date),".$db2->quote($author).",".$db2->quote($comment).",True)";
 +
    print "$query\n";
 +
    $sth = $db2->prepare($query);
 +
    $sth->execute;
 +
    $sth->finish;
 +
  }
 +
}
 +
$sth->finish;
 +
 +
sub remove_bbcode() {
 +
  my ($title) = @_;
 +
 +
  $title =~ s{\[color=\w+\]}{}g;
 +
  $title =~ s{\[/color\]}{}g;
 +
 +
  $title =~ s{\[b\]}{<b>}g;
 +
  $title =~ s{\[/b\]}{</b>}g;
 +
  $title =~ s{\[i\]}{<i>}g;
 +
  $title =~ s{\[/i\]}{</i>}g;
 +
 +
  $title =~ s{\[url *= *([^\]]+)\]([^\[]+)\[/url\]}{<a href="\1">\2</a>}g;
 +
 +
  return $title;
 +
}
 +
</syntaxhighlight>
  
 
[[Catégorie:Admin sys]]
 
[[Catégorie:Admin sys]]

Version du 10 juillet 2014 à 10:54

Nota concernant piwigo et Debian

http://www.nikrou.net/post/2011/09/09/Faire-un-fork-de-piwigo

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=694820

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=672374

https://packages.debian.org/search?keywords=piwigo

« There's no point in maintaining an uninstallable package. »

Installation de Piwigo

  • Téléchargement depuis http://piwigo.org/download/dlcounter.php?code=2.6.3
  • unzip dans /var/www
  • rendre le truc accessible au travers d'apache :
    • éditer /etc/gallery2/apache.conf
    • (hack) ajouter Alias /piwigo /var/www/piwigo dans le virtualhost
  • créer la database :
 mysql> create database piwigodb;

Migration de Gallery2 vers Piwigo

On suit globalement ça : https://github.com/dschwen/g2piwigo
  • déplacer les photos dans /var/www/piwigo/galleries
  • renommer les photos pour faire disparaître les espaces des noms de fichiers, avec le script suivant :
function  change_spaces () {
  echo $* | sed -e 's/ /_/g'
}

for file in */*" "*
do
  mv "$file" `change_spaces $file`
done
  • synchroniser la base de données avec les fichiers :
    • se logguer sur l'interface d'administration de piwigo et aller dans tools -> synchronize
    • cocher la case "directories + files" dans la section "synchronise file structure with database"
    • conserver la case "perform a simulation only" dans la section "simulation"
    • cliquer sur "submit"
    • lorsqu'il n'y a plus de problème indiqué (0 errors during synchronisation), décocher la case "perform a simulation only" et re-cliquer sur submit.
  • pour générer les thumbnails :
    • aller dans Photos -> Batch manager
    • Sélectionner l'ensemble des photos
    • Dans action, choisir "Generate multiple size images"
    • Sélectionner "thumbnails"
    • appliquer.
    • Boire des coups en attendant.

Application du script

  • Dumper la base piwigo au cas où :
mysqldump -u root -pxxblingxx piwigodb > dumppiwigodb.sql
find | grep -v /thumbnail | grep -v /pwg_high | cut -c3- | ../convertcomments.pl --menalto-dbname=gallery2 --menalto-dbuser=blingblang --menalto-dbpass=blingblang  --piwigo-dbname=piwigodb --piwigo-dbuser=blingblang --piwigo-dbpass=blingblang --piwigo-prefix=piwigo_ --menalto-table-prefix=g2_e --menalto-column-prefix=g_ > log
  • aller boire un café.
  • quand c'est fini, il reste à jardiner un peu : reformater à la main certains textes, etc. La migration de bbcode vers html n'est pas bulletproof. Une façon de détecter les problèmes est de rechercher des crochets dans le log.

NEXT: PoluX

  • récupérer les keywords

Le script

#!/usr/bin/perl

my $usage = "\n
    in the piwigo galleries folder do\n
    find | grep -v /thumbnail | grep -v /pwg_high | cut -c3- | ../convertcomments.pl --menalto-dbname=gl2_database --menalto-dbuser= --menalto-dbpass=gl2_db_password --menalto-table-prefix=gl2_ --menalto-column-prefix=g_ --piwigo-dbname=piwigo_database --piwigo-dbuser=piwigo --piwigo-dbpass=piwigo_db_password\n";

use DBI;
use DBD::mysql;
use Getopt::Long;

my @opt_mandatory = qw/
    menalto-dbname=s
    menalto-dbuser=s
    menalto-dbpass=s
    piwigo-dbname=s
    piwigo-dbuser=s
    piwigo-dbpass=s
/;

my @opt_optional = qw/
    menalto-dbhost=s
    menalto-table-prefix=s
    menalto-column-prefix=s
    piwigo-dbhost=s
    piwigo-prefix=s
/;

my %opt = ();
GetOptions(
    \%opt,
    @opt_mandatory,
    @opt_optional
);

# use Data::Dumper; print Dumper(\%opt); exit();

foreach my $param (@opt_mandatory) {
    $param =~ s/=s$//;
    if (not defined $opt{$param}) {
        print '--'.$param.' is mandatory'."\n";
        print $usage;
        exit();
    }
}

if (not defined $opt{'menalto-dbhost'}) {
    $opt{'menalto-dbhost'} = 'localhost'; 
}

if (not defined $opt{'menalto-table-prefix'}) {
    $opt{'menalto-table-prefix'} = 'g_'; 
}

if (not defined $opt{'menalto-column-prefix'}) {
    $opt{'menalto-column-prefix'} = ''; 
}

if (not defined $opt{'piwigo-dbhost'}) {
    $opt{'piwigo-dbhost'} = 'localhost'; 
}

if (not defined $opt{'piwigo-prefix'}) {
    $opt{'piwigo-prefix'} = '';
}

$ds1 = "dbi:mysql:".$opt{'menalto-dbname'}.":".$opt{'menalto-dbhost'}.":3306";
$db1 = DBI->connect( $ds1, $opt{'menalto-dbuser'}, $opt{'menalto-dbpass'}, { PrintError => 1})
    or die $DBI::errstr;

$ds2 = "dbi:mysql:".$opt{'piwigo-dbname'}.":".$opt{'piwigo-dbhost'}.":3306";
$db2 = DBI->connect( $ds2, $opt{'piwigo-dbuser'}, $opt{'piwigo-dbpass'}, { PrintError => 1})
    or die $DBI::errstr;

# Gallery2 parent Ids (root is always 7!)
@ids = ( 7,0,0,0,0,0 );
# piwigo uppercats
@uct = ( "NULL",0,0,0,0,0 );
@ranks = ();

while(<STDIN>) {
  s/\n//g;
  $dir = $_;
  @path = split(/\//);
  $level = int(@path);
  next if( $level == 0 );

  $parentId = $ids[$level-1];

  # get id and title/summary/description of tail element in path  
  $query = "
SELECT 
    f.".$opt{'menalto-column-prefix'}."id,
    i.".$opt{'menalto-column-prefix'}."title,
    i.".$opt{'menalto-column-prefix'}."summary,
    i.".$opt{'menalto-column-prefix'}."description,
    i.".$opt{'menalto-column-prefix'}."canContainChildren,
    a.".$opt{'menalto-column-prefix'}."orderWeight,
    a.".$opt{'menalto-column-prefix'}."viewCount,
    FROM_UNIXTIME(e.".$opt{'menalto-column-prefix'}."creationTimestamp)
  FROM ".$opt{'menalto-table-prefix'}."Item i
    JOIN ".$opt{'menalto-table-prefix'}."FileSystemEntity f ON i.".$opt{'menalto-column-prefix'}."id = f.".$opt{'menalto-column-prefix'}."id
    JOIN ".$opt{'menalto-table-prefix'}."ChildEntity c ON f.".$opt{'menalto-column-prefix'}."id = c.".$opt{'menalto-column-prefix'}."id
    JOIN ".$opt{'menalto-table-prefix'}."ItemAttributesMap a ON i.".$opt{'menalto-column-prefix'}."id = a.".$opt{'menalto-column-prefix'}."itemId
    JOIN ".$opt{'menalto-table-prefix'}."Entity e ON e.".$opt{'menalto-column-prefix'}."id = i.".$opt{'menalto-column-prefix'}."id
  WHERE c.".$opt{'menalto-column-prefix'}."parentId = ".$db1->quote($parentId)."
    AND f.".$opt{'menalto-column-prefix'}."pathComponent=".$db1->quote($path[$level-1])."
;";

  $sth = $db1->prepare($query);
  $sth->execute;
  @row = $sth->fetchrow();
  $sth->finish;

  #print "$row[4] - $parentId -> $row[0] : $row[1] $row[2] $row[3]\n";
  $title = remove_bbcode($row[1]);
  $summary = remove_bbcode($row[2]);
  $description = remove_bbcode($row[3]);
  $weight = $row[5];
  $views = $row[6];
  $date_available = $row[7];
  $ids[$level] = $row[0];
  $pid{$row[0]}=$dir;

  if( $row[4] == 0 ) {
    # image
    $comment = "";
    if( $summary ne "" && $description ne "" ) {
      $comment = "<b>$summary</b> - $description";
    } else {
      if( $summary ne "" ) {
        $comment = $summary;
      } else {
        $comment = $description;
      }
    }

    $query = "
UPDATE ".$opt{'piwigo-prefix'}."images
  SET name=".$db2->quote($title)."
    , comment=".$db2->quote($comment)."
    , date_available='".$date_available."'
  WHERE path = ".$db2->quote("./galleries/".$dir)." ";
    print "$query\n";
    $sth = $db2->prepare($query);
    $sth->execute;
    $sth->finish;

    # build a map from gallery2 ids to piwigo image ids
    $query = "SELECT id FROM ".$opt{'piwigo-prefix'}."images WHERE path = ".$db2->quote("./galleries/".$dir);
    $sth = $db2->prepare($query);
    $sth->execute;
    ($iid{$row[0]}) = $sth->fetchrow();
    $sth->finish;

  } else {
    # folder
    $comment = "";
    if( $summary ne "" && $description ne "" ) {
      $comment = "$summary <!--complete--> $description";
    } else {
      if( $summary ne "" ) {
        $comment = $summary;
      } else {
        $comment = "<!--complete-->$description";
      }
    }

    # get piwigo category id
    $uc = "= ".$uct[$level-1];
    $uc = "IS NULL" if( $uct[$level-1] eq "NULL" );
    $query = "SELECT id FROM ".$opt{'piwigo-prefix'}."categories WHERE dir = ".$db2->quote($path[$level-1])." AND id_uppercat $uc";
    $sth = $db2->prepare($query);
    $sth->execute;
    @row = $sth->fetchrow();
    $sth->finish;
    $id = $row[0];
    $uct[$level] = $id;

    # build global_rank string
    $grank = "";
    for($i=1;$i<$level;$i++ ) {
      $grank .= $ranks[$i].".";
    }
    $grank .= $weight;
    $ranks[$level]=$weight;

    $query = "UPDATE ".$opt{'piwigo-prefix'}."categories SET name=".$db2->quote($title).", comment=".$db2->quote($comment).", rank=$weight, global_rank=".$db2->quote($grank)." WHERE id = $id";
    print "$query\n";
    $sth = $db2->prepare($query);
    $sth->execute;
    $sth->finish;

    # get highlight picture 
    $query = "
SELECT d2.".$opt{'menalto-column-prefix'}."derivativeSourceId 
  FROM ".$opt{'menalto-table-prefix'}."ChildEntity c
    JOIN ".$opt{'menalto-table-prefix'}."Derivative d1 ON c.".$opt{'menalto-column-prefix'}."id = d1.".$opt{'menalto-column-prefix'}."id
    JOIN ".$opt{'menalto-table-prefix'}."Derivative d2 ON d1.".$opt{'menalto-column-prefix'}."derivativeSourceId=d2.".$opt{'menalto-column-prefix'}."id
  WHERE c.".$opt{'menalto-column-prefix'}."parentId = ".$ids[$level];
    $sth = $db1->prepare($query);
    $sth->execute;
    ($hid{$id}) = $sth->fetchrow();
    $sth->finish;
 }
}

# apply highlites as representative images
while(($key, $value) = each(%hid)) {
  print "$key $value $pid{$value}\n";  

  # get piwigo picture id
  $query="SELECT id from ".$opt{'piwigo-prefix'}."images WHERE path = ".$db2->quote("./galleries/".$pid{$value});
  print "$query\n";
  $sth = $db2->prepare($query);
  $sth->execute;
  ($id) = $sth->fetchrow();
  $sth->finish;

  $query = "UPDATE ".$opt{'piwigo-prefix'}."categories SET representative_picture_id =".$db2->quote($id)." WHERE id = ".$db2->quote($key);
  print "$query\n";
  $sth = $db2->prepare($query);
  $sth->execute;
  $sth->finish;
}

# copy comments
$query = "
SELECT
    c.".$opt{'menalto-column-prefix'}."parentId,
    t.".$opt{'menalto-column-prefix'}."subject,
    t.".$opt{'menalto-column-prefix'}."comment,
    t.".$opt{'menalto-column-prefix'}."author,
    t.".$opt{'menalto-column-prefix'}."date
  FROM ".$opt{'menalto-table-prefix'}."ChildEntity c
    JOIN ".$opt{'menalto-table-prefix'}."Comment t ON t.".$opt{'menalto-column-prefix'}."id = c.".$opt{'menalto-column-prefix'}."id
  WHERE t.".$opt{'menalto-column-prefix'}."publishStatus=0
";
$sth2 = $db1->prepare($query);
$sth2->execute;
while( ($id,$subject,$comment,$author,$date) = $sth2->fetchrow() ) {
  # FROM_UNIXTIME($date)
  if( $iid{$id} ) {
    if( $subject ne "" ) {
      $comment = "<b>$subject</b> $comment";
    }
    $query = "INSERT INTO ".$opt{'piwigo-prefix'}."comments (image_id,date,author,content,validated) VALUES (".$db2->quote($iid{$id}).",FROM_UNIXTIME($date),".$db2->quote($author).",".$db2->quote($comment).",True)";
    print "$query\n";
    $sth = $db2->prepare($query);
    $sth->execute;
    $sth->finish;
  }
}
$sth->finish;

sub remove_bbcode() {
  my ($title) = @_;

  $title =~ s{\[color=\w+\]}{}g;
  $title =~ s{\[/color\]}{}g;

  $title =~ s{\[b\]}{<b>}g;
  $title =~ s{\[/b\]}{</b>}g;
  $title =~ s{\[i\]}{<i>}g;
  $title =~ s{\[/i\]}{</i>}g;

  $title =~ s{\[url *= *([^\]]+)\]([^\[]+)\[/url\]}{<a href="\1">\2</a>}g;

  return $title;
}