[commit-womb] gnumaint gnufsd-psql

[Kaloian Doganov]

CVSROOT:        /sources/womb
Module name:    gnumaint
Changes by:     Kaloian Doganov <kaloian>       09/04/18 12:26:36

Modified files:
        .              : gnufsd-psql 

Log message:
        Export category information for every package.

CVSWeb URLs:
http://cvs.savannah.gnu.org/viewcvs/gnumaint/gnufsd-psql?cvsroot=womb&r1=1.2&r2=1.3

Patches:
Index: gnufsd-psql
===================================================================
RCS file: /sources/womb/gnumaint/gnufsd-psql,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -b -r1.2 -r1.3
--- gnufsd-psql 26 Feb 2009 12:40:12 -0000      1.2
+++ gnufsd-psql 18 Apr 2009 12:26:35 -0000      1.3
@@ -1,5 +1,5 @@
 #!/usr/bin/env perl
-# $Id: gnufsd-psql,v 1.2 2009/02/26 12:40:12 kaloian Exp $
+# $Id: gnufsd-psql,v 1.3 2009/04/18 12:26:35 kaloian Exp $
 # Extract information about GNU packages from the Free Software
 # Directory's PostgreSQL database.
 #
@@ -28,7 +28,8 @@
 
 # FIXME: What about handling UTF-8 from PostgreSQL?
 
-my ($dbh, $sth_homepage, $sth_download_url, $sth_activity_status);
+my ($dbh, $sth_homepage, $sth_download_url, $sth_activity_status,
+    $sth_categories, $sth_category);
 
 exit (&main ());
 
@@ -56,6 +57,14 @@
   $sth_activity_status = $dbh->prepare (
     "SELECT name, date FROM directory_version
      WHERE project_id = ? ORDER BY date DESC;");
+
+  $sth_categories = $dbh->prepare (
+    "SELECT category_id FROM directory_project_categories
+     WHERE project_id = ?;");
+
+  $sth_category = $dbh->prepare (
+    "SELECT name, slug, parent_category_id  FROM directory_category
+     WHERE id = ?;");
 }
 
 # Disconnect from the database.
@@ -225,6 +234,38 @@
   return $result;
 }
 
+# Formats a category_id suitable for printing.
+sub format_category($)
+{
+  my ($id) = @_;
+  my @chain;
+  do {
+    $sth_category->bind_param (1, $id);
+    my $cat = row ($sth_category);
+    my $name = $cat->{"name"};
+    my $slug = $cat->{"slug"};
+    push (@chain, $name);
+    $id = $cat->{"parent_category_id"};
+  } until ($id == 1);
+  return "/" . join ("/", reverse (@chain));
+}
+
+# Fetch categories for a project.
+sub categories(\%)
+{
+  my ($project) = @_;
+  my $id = $project->{"id"};
+  $sth_categories->bind_param (1, $id);
+  my $categories = all ($sth_categories);
+
+  my @result;
+  foreach my $category (@{$categories}) {
+    my $category_id = $category->{"category_id"};
+    push (@result, format_category ($category_id));
+  }
+  return sort(@result);
+}
+
 # Dump a project to STDOUT.
 sub project(\%)
 {
@@ -245,6 +286,9 @@
 
   my $activity_status = activity_status (%$project);
   print "activity-status: $activity_status\n";
+
+  print "category: $_\n" foreach (categories (%$project));
+
   print "\n";
 }