[commit-womb] gnumaint/fsd gnufsd-psql

[Kaloian Doganov]

CVSROOT:        /sources/womb
Module name:    gnumaint
Changes by:     Kaloian Doganov <kaloian>       09/04/20 14:37:47

Modified files:
        fsd            : gnufsd-psql 

Log message:
        Added dumping of versions, along with licenses and interfaces.

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

Patches:
Index: gnufsd-psql
===================================================================
RCS file: /sources/womb/gnumaint/fsd/gnufsd-psql,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -b -r1.1 -r1.2
--- gnufsd-psql 20 Apr 2009 08:12:08 -0000      1.1
+++ gnufsd-psql 20 Apr 2009 14:37:47 -0000      1.2
@@ -1,5 +1,5 @@
 #!/usr/bin/env perl
-# $Id: gnufsd-psql,v 1.1 2009/04/20 08:12:08 kaloian Exp $
+# $Id: gnufsd-psql,v 1.2 2009/04/20 14:37:47 kaloian Exp $
 # Extract information about GNU packages from the Free Software
 # Directory's PostgreSQL database.
 #
@@ -30,7 +30,8 @@
 # FIXME: What about handling UTF-8 from PostgreSQL?
 
 my ($dbh, $sth_homepage, $sth_download_url, $sth_activity_status,
-    $sth_categories, $sth_category);
+    $sth_categories, $sth_category, $sth_versions, $sth_licenses,
+    $sth_interfaces);
 
 my %user_level_enum = (
   0 => "unknown",
@@ -38,6 +39,15 @@
   2 => "intermediate",
   3 => "advanced");
 
+my %version_status_enum = (
+  2 => "alpha",
+  3 => "beta",
+  4 => "developmental",
+  5 => "testing",
+  6 => "unstable",
+  7 => "stable",
+  8 => "mature");
+
 exit (&main ());
 
 # Connect to the database and prepare all reusable statements in
@@ -72,6 +82,27 @@
   $sth_category = $dbh->prepare (
     "SELECT name, slug, parent_category_id  FROM directory_category
      WHERE id = ?;");
+
+  $sth_versions = $dbh->prepare (
+    "SELECT v.id, v.name, v.date, v.status, v.notes,
+       u.first_name || ' ' || u.last_name || ' <' || u.email || '>'
+         AS license_verified_by,
+       v.license_verified_on, v.source_code
+     FROM directory_version AS v, auth_user AS u
+     WHERE v.license_verified_by_id = u.id AND v.project_id = ?
+     ORDER BY v.date DESC;");
+
+  $sth_licenses = $dbh->prepare (
+    "SELECT l.slug
+     FROM directory_license AS l, directory_version_licenses AS vl
+     WHERE l.id = vl.license_id AND vl.version_id = ?
+     ORDER BY slug;");
+
+  $sth_interfaces = $dbh->prepare (
+    "SELECT i.name
+     FROM directory_interface AS i, directory_version_interfaces AS vi
+     WHERE i.id = vi.interface_id AND vi.version_id = ?
+     ORDER BY name;");
 }
 
 # Disconnect from the database.
@@ -123,6 +154,18 @@
   return trim_whitespace ($dbh->selectrow_hashref ($sql));
 }
 
+# Fetch all values in one COLUMN resulted from a SELECT statement.
+sub all_col($$)
+{
+  my ($sql, $col) = @_;
+  my @result;
+  my $rows = all ($sql);
+  for my $row (@$rows) {
+    push (@result, $row->{$col});
+  }
+  return @result;
+}
+
 # Fetch one COLUMN from the first row resulted from a SELECT
 # statement.
 sub col($$)
@@ -302,6 +345,59 @@
   return " " . $out;
 }
 
+# Fetch licenses for a project version.
+sub licenses(\%)
+{
+  my ($version) = @_;
+  my $id = $version->{"id"};
+  $sth_licenses->bind_param (1, $id);
+  return all_col ($sth_licenses, "slug");
+}
+
+# Fetch interfaces for a project version.
+sub interfaces(\%)
+{
+  my ($version) = @_;
+  my $id = $version->{"id"};
+  $sth_interfaces->bind_param (1, $id);
+  return all_col ($sth_interfaces, "name");
+}
+
+# Fetch versions for a project.
+sub versions(\%)
+{
+  my ($project) = @_;
+  my $id = $project->{"id"};
+  $sth_versions->bind_param (1, $id);
+  return all ($sth_versions);
+}
+
+# Dump a version.
+sub version(\%)
+{
+  my ($version) = @_;
+  my $name = $version->{"name"};
+  my $date = iso_date_to_str ($version->{"date"});
+  my $status = $version_status_enum{$version->{"status"}};
+  my $notes = $version->{"notes"};
+  my $license_verified_by = $version->{"license_verified_by"};
+  my $license_verified_on = iso_date_to_str
+      ($version->{"license_verified_on"});
+  my $source_code = $version->{"source_code"};
+  my $licenses = join (", ", licenses (%$version));
+  my $interfaces = join (", ", interfaces (%$version));
+  print "version:  $name\n";
+  print "  date: $date\n";
+  print "  status: $status\n";
+  print "  notes: $notes\n" if defined $notes and ! ($notes eq '');
+  print "  license-verified-by: $license_verified_by\n";
+  print "  license-verified-on: $license_verified_on\n";
+  print "  source-code: $source_code\n"
+      if defined $source_code and ! ($source_code eq '');
+  print "  licenses: $licenses\n" if ! ($licenses eq '');
+  print "  interfaces: $interfaces\n" if ! ($interfaces eq '');
+}
+
 # Dump a project to STDOUT.
 sub project(\%)
 {
@@ -342,6 +438,11 @@
   print "description: $project->{short_description}\n";
   print "$full_descr\n";
 
+  my $versions = versions (%$project);
+  for my $version (@{$versions}) {
+    version (%$version);
+  }
+
   print "\n";
 }