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

De April MediaWiki
Aller à la navigationAller à la recherche
(Ajout de remarques sur un fichier embarqué dans l'archive)
 
(7 versions intermédiaires par un autre utilisateur non affichées)
Ligne 2 : Ligne 2 :
  
 
http://www.nikrou.net/post/2011/09/09/Faire-un-fork-de-piwigo
 
http://www.nikrou.net/post/2011/09/09/Faire-un-fork-de-piwigo
 +
 +
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=662130
  
 
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=694820
 
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=694820
Ligne 10 : Ligne 12 :
  
 
« There's no point in maintaining an uninstallable package. »
 
« There's no point in maintaining an uninstallable package. »
 +
 +
Voir la tâche sur Redmine : https://agir.april.org/issues/1529
 +
 +
Nota: aucune trace dans la version actuelle de jsmin.php ni aucune occurrence de "Evil" dans les sources.
 +
Piwigo utilise désormais jQuery.
 +
 +
En revanche, Piwigo utilise un uploader flash : uploadify … dont la
 +
licence semble être MIT :
 +
http://www.uploadify.com/wp-content/uploads/files/uploadify.zip
 +
Lui même est basé sur https://code.google.com/p/swfupload/
 +
 +
Autant l'archive de swfupload contient le code source, mais ce n'est pas
 +
le cas pour uploadify.
  
 
== Installation de Piwigo ==
 
== Installation de Piwigo ==
Ligne 54 : Ligne 69 :
 
** Boire des coups en attendant.
 
** Boire des coups en attendant.
  
== NEXT: PoluX ==
+
== Application du script ==
* récupérer les data via le script perl
+
 
* récupérer les keywords
+
* Dumper la base piwigo au cas où :
* récupérer les commentaires d'album et en faire qq chose
+
mysqldump -u root -pxxblingxx piwigodb > dumppiwigodb.sql
* vérifier dans le thème les mentions de licence.
+
* 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 lang="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.
 +
 
 +
== Installation du thème SimpleNG ==
 +
 
 +
* Aller dans configuration -> theme -> ...
 +
 
 +
== Mise en route du site ==
 +
 
 +
Créer un fichier /etc/apache2/sites-available/piwigo ; le lier depuis /etc/apache2/sites-enabled/piwigo ; et reloader apache.
 +
 
 +
== Autre ==
 +
 
 +
Nous avons installé le plugin Community pour permettre l'upload de photo par les utilisateurs.
 +
 
 +
Suite à ça, nous avons ajouté dans ./include/config_default.inc.php pour afficher la liste des fichiers uploadables :
 +
<syntaxhighlight lang="php">
 +
// Added due to community extension
 +
$conf['upload_form_all_types'] = true;
 +
</syntaxhighlight>
 +
 
 +
De fait, il était déjà possible d'uploader du zip. Mais il n'y avait aucune info donné à l'utilisateur. Bug ?
 +
 
 +
== 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]]

Dernière version du 12 juillet 2014 à 14:37

Nota concernant piwigo et Debian[modifier]

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

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

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. »

Voir la tâche sur Redmine : https://agir.april.org/issues/1529

Nota: aucune trace dans la version actuelle de jsmin.php ni aucune occurrence de "Evil" dans les sources. Piwigo utilise désormais jQuery.

En revanche, Piwigo utilise un uploader flash : uploadify … dont la licence semble être MIT : http://www.uploadify.com/wp-content/uploads/files/uploadify.zip Lui même est basé sur https://code.google.com/p/swfupload/

Autant l'archive de swfupload contient le code source, mais ce n'est pas le cas pour uploadify.

Installation de Piwigo[modifier]

  • 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[modifier]

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[modifier]

  • 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.

Installation du thème SimpleNG[modifier]

  • Aller dans configuration -> theme -> ...

Mise en route du site[modifier]

Créer un fichier /etc/apache2/sites-available/piwigo ; le lier depuis /etc/apache2/sites-enabled/piwigo ; et reloader apache.

Autre[modifier]

Nous avons installé le plugin Community pour permettre l'upload de photo par les utilisateurs.

Suite à ça, nous avons ajouté dans ./include/config_default.inc.php pour afficher la liste des fichiers uploadables :

// Added due to community extension
$conf['upload_form_all_types'] = true;

De fait, il était déjà possible d'uploader du zip. Mais il n'y avait aucune info donné à l'utilisateur. Bug ?

Le script[modifier]

#!/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;
}