Index: /trunk/DataStoreServer/scripts/Makefile.am
===================================================================
--- /trunk/DataStoreServer/scripts/Makefile.am	(revision 16761)
+++ /trunk/DataStoreServer/scripts/Makefile.am	(revision 16762)
@@ -3,5 +3,8 @@
 
 install_files = \
-	dsreg
+	dsreg \
+        dsrootindex \
+        dsprodindex \
+        dsfsindex
 
 install_SCRIPTS = $(install_files)
Index: /trunk/DataStoreServer/scripts/dsfsindex
===================================================================
--- /trunk/DataStoreServer/scripts/dsfsindex	(revision 16762)
+++ /trunk/DataStoreServer/scripts/dsfsindex	(revision 16762)
@@ -0,0 +1,130 @@
+#!/usr/bin/env perl
+
+# list the filesets in a data store product
+# The only ipp specific code is the function that gets the database handle and the
+# exit status codes
+
+use strict;
+use warnings;
+
+use DBI;
+
+use PS::IPP::Config qw($PS_EXIT_SUCCESS
+		       $PS_EXIT_UNKNOWN_ERROR
+		       $PS_EXIT_SYS_ERROR
+		       $PS_EXIT_CONFIG_ERROR
+		       $PS_EXIT_PROG_ERROR
+		       $PS_EXIT_DATA_ERROR
+		       $PS_EXIT_TIMEOUT_ERROR
+		       metadataLookupStr
+		       metadataLookupBool
+		       caturi
+		       );
+
+my $fileset = pop;
+die("must specify fileset to list") unless defined $fileset;
+
+my $product = pop;
+die("must specify product to list") unless defined $product;
+
+my $dbh = getDBHandle();
+
+# Look up the product id
+my $prod_stmt = $dbh->prepare("SELECT prod_id FROM dsProduct WHERE prod_name = '$product'");
+$prod_stmt->execute();
+my $prod = $prod_stmt->fetchrow_hashref();
+if (! defined $prod) {
+    print STDERR "failed to find $product in product list\n";
+    exit ($PS_EXIT_DATA_ERROR);
+}
+
+my $prod_id = $prod->{prod_id};
+
+my $stmt = $dbh->prepare("SELECT fileset_id FROM dsFileset WHERE fileset_name = \'$fileset\'");
+$stmt->execute();
+my $fs = $stmt->fetchrow_hashref();
+if (!defined $fs) {
+    print STDERR "failed to find $fileset in $product\n";
+    exit ($PS_EXIT_DATA_ERROR);
+}
+
+my $fs_id = $fs->{fileset_id};
+
+
+# get the headers for all of the type specific columns
+# a fileset may contain files with multiple types. We print out a new
+# header each time the type in the list changes.
+# save the header
+
+$stmt = $dbh->prepare("SELECT * FROM dsFileType");
+$stmt->execute();
+my %fileTypes;
+while( my @row = $stmt->fetchrow_array()) {
+    my $type = $row[0];
+    $fileTypes{$type} = \@row;
+}
+
+my $header = "# fileID          |bytes   |md5sum                          |type        |";
+
+$stmt = $dbh->prepare("SELECT * from dsFile WHERE fileset_id = $fs_id");
+$stmt->execute();
+
+my $print_header=1;
+my $last_type = "";
+while( my $row = $stmt->fetchrow_hashref()) {
+    my $type = $row->{type};
+
+    # if the type changes print out a new header
+    if ($type ne $last_type) {
+        $print_header = 1;
+        $last_type = $type;
+    }
+    if ($print_header) {
+        # add the type specific columns
+        my $typeheader = "";
+        my $fileTypeDef = $fileTypes{$type};
+        for (my $i= 1; $i <=4; $i++) {
+            if (!defined $fileTypeDef->[$i]) {
+                last;
+            }
+            $typeheader .= sprintf "%-8s|", $fileTypeDef->[$i];
+        }
+        print "$header$typeheader\n";
+        $print_header = 0;
+    }
+
+    my $line = sprintf "%-18s|%-8s|%-32s|%-12s|", 
+        $row->{file_name}, $row->{bytes}, $row->{md5sum}, $row->{type};
+
+    # now add any type specific columns that are defined (8 character minimum width is arbitrary)
+    if (defined $row->{type_col_0}) {
+        $line .= sprintf "%-8s|", $row->{type_col_0};
+        if (defined $row->{type_col_1}) {
+            $line .= sprintf "%-8s|", $row->{type_col_1};
+            if (defined $row->{type_col_2}) {
+                $line .= sprintf "%-8s|", $row->{type_col_2};
+                if (defined $row->{type_col_3}) {
+                    $line .= sprintf "%-8s|", $row->{type_col_3};
+                }
+            }
+        }
+    }
+
+    print "$line\n";
+}
+
+sub getDBHandle {
+    my $ipprc =  PS::IPP::Config->new(); # IPP Configuration
+    my $siteConfig = $ipprc->{_siteConfig};
+
+    my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
+    my $dbname   = metadataLookupStr($siteConfig, 'DBNAME');
+    my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
+    my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
+    exit ($PS_EXIT_CONFIG_ERROR) unless defined $dbserver and $dbname and $dbuser and $dbpass;
+
+    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
+
+    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
+    return $dbh;
+}
Index: /trunk/DataStoreServer/scripts/dsprodindex
===================================================================
--- /trunk/DataStoreServer/scripts/dsprodindex	(revision 16762)
+++ /trunk/DataStoreServer/scripts/dsprodindex	(revision 16762)
@@ -0,0 +1,163 @@
+#!/usr/bin/env perl
+
+# list the filesets in a data store product
+
+use strict;
+use warnings;
+
+use DBI;
+use PS::IPP::Config qw($PS_EXIT_SUCCESS
+		       $PS_EXIT_UNKNOWN_ERROR
+		       $PS_EXIT_SYS_ERROR
+		       $PS_EXIT_CONFIG_ERROR
+		       $PS_EXIT_PROG_ERROR
+		       $PS_EXIT_DATA_ERROR
+		       $PS_EXIT_TIMEOUT_ERROR
+		       metadataLookupStr
+		       metadataLookupBool
+		       caturi
+		       );
+
+my $product = shift;
+die("must specify product to list") unless defined $product;
+
+# optional fileset name. Ignore filesets up to and including $after_fileset
+my $after_fileset = shift;
+
+my $dbh = getDBHandle();
+
+my $prod_stmt = $dbh->prepare("SELECT * FROM dsProduct WHERE prod_name = '$product'");
+$prod_stmt->execute();
+my $prod = $prod_stmt->fetchrow_hashref();
+if (! defined $prod) {
+    print STDERR "failed to find $product in product list\n";
+    exit ($PS_EXIT_DATA_ERROR);
+}
+
+# build the header line
+my $header = "# filesetID|time registered     |type     |";
+
+# now add the product specific columns that are defined
+# not particularly elegant. I guess I should have fetched the row as an array
+# note: we don't do any formatting for these columns. The width of the string
+# in the database should be padded if a nice width is desired
+my $last_prod_col = -1;
+if (defined $prod->{prod_col_0}) {
+    $last_prod_col = 0;
+    $header .= "$prod->{prod_col_0}|";
+    if (defined $prod->{prod_col_1}) {
+        $last_prod_col = 1;
+        $header .= "$prod->{prod_col_1}|";
+        if (defined $prod->{prod_col_2}) {
+            $last_prod_col = 2;
+            $header .= "$prod->{prod_col_2}|";
+            if (defined $prod->{prod_col_3}) {
+                $last_prod_col = 3;
+                $header .= "$prod->{prod_col_3}|";
+                if (defined $prod->{prod_col_4}) {
+                    $last_prod_col = 4;
+                    $header .= "$prod->{prod_col_4}|";
+                    if (defined $prod->{prod_col_5}) {
+                        $last_prod_col = 5;
+                        $header .= "$prod->{prod_col_5}|";
+                        if (defined $prod->{prod_col_6}) {
+                            $last_prod_col = 6;
+                            $header .= "$prod->{prod_col_6}|";
+                            if (defined $prod->{prod_col_7}) {
+                                $last_prod_col = 7;
+                                $header .= "$prod->{prod_col_7}|";
+                            }
+                        }
+                    }
+                }
+            }
+        }
+    }
+}
+    
+
+my $prod_id = $prod->{prod_id};
+
+my $stmt;
+my $after_clause = "";
+if ($after_fileset) {
+    # client wants only filesets registered after $after_fileset
+    # Since fileset_id is auto increment the desired filesets will have a larger fileset_id than
+    # the target. Look up it's fileset_id
+
+    $stmt = $dbh->prepare("SELECT fileset_id FROM dsFileset " .
+                            "WHERE prod_id = $prod_id AND fileset_name = \'$after_fileset\'");
+    $stmt->execute();
+    my $fs = $stmt->fetchrow_hashref();
+    if ($fs) {
+        $after_clause = " AND fileset_id > $fs->{fileset_id}";
+    } else {
+        # $after_fileset not found
+        #
+        # XXX: the spec doesn't say what should happen in this case.
+        # Here we follow the conductor implementation and return the whole list.
+        # This may not be the right thing to do.
+    }
+}
+        
+$stmt = $dbh->prepare("SELECT * FROM dsFileset WHERE prod_id = $prod_id  $after_clause");
+$stmt->execute();
+
+# XXX: dsproductls expects a header even if there are no matching filesets
+print "$header\n";
+
+while( my $row = $stmt->fetchrow_hashref()) {
+
+    my $daytime = $row->{reg_time};
+    (my $date, my $time) = split " ", $daytime;
+
+    my $reg_time = $date . "T" . $time . "Z";
+
+    my $line = sprintf "%-11s|%-20s|%-9s|", $row->{fileset_name}, $reg_time, $row->{type};
+
+    # now add the product specific columns that are defined
+    # again using an array would have made this look nicer
+    if ($last_prod_col >= 0) {
+        $line .= "$row->{prod_col_0}|";
+        if ($last_prod_col >= 1) {
+            $line .= "$row->{prod_col_1}|";
+            if ($last_prod_col >= 2) {
+                $line .= "$row->{prod_col_2}|";
+                if ($last_prod_col >= 3) {
+                    $line .= "$row->{prod_col_3}|";
+                    if ($last_prod_col >= 4) {
+                        $line .= "$row->{prod_col_4}|";
+                        if ($last_prod_col >= 5) {
+                            $line .= "$row->{prod_col_5}|";
+                            if ($last_prod_col >= 6) {
+                                $line .= "$row->{prod_col_6}|";
+                                if ($last_prod_col >= 7) {
+                                    $line .= "$row->{prod_col_7}|";
+                                }
+                            }
+                        }
+                    }
+                }
+            }
+        }
+    }
+
+    print "$line\n";
+}
+
+# TODO: move this to a module
+sub getDBHandle {
+    my $ipprc =  PS::IPP::Config->new(); # IPP Configuration
+    my $siteConfig = $ipprc->{_siteConfig};
+
+    my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
+    my $dbname   = metadataLookupStr($siteConfig, 'DBNAME');
+    my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
+    my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
+    exit ($PS_EXIT_CONFIG_ERROR) unless defined $dbserver and $dbname and $dbuser and $dbpass;
+
+    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
+
+    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
+    return $dbh;
+}
Index: /trunk/DataStoreServer/scripts/dsrootindex
===================================================================
--- /trunk/DataStoreServer/scripts/dsrootindex	(revision 16762)
+++ /trunk/DataStoreServer/scripts/dsrootindex	(revision 16762)
@@ -0,0 +1,64 @@
+#!/usr/bin/env perl
+#
+# dsrootindex:  Extract the root data store index from the database.
+# The output is in DataStore index.txt format
+#
+
+use strict;
+use warnings;
+
+use DBI;
+
+use PS::IPP::Config qw($PS_EXIT_SUCCESS
+		       $PS_EXIT_UNKNOWN_ERROR
+		       $PS_EXIT_SYS_ERROR
+		       $PS_EXIT_CONFIG_ERROR
+		       $PS_EXIT_PROG_ERROR
+		       $PS_EXIT_DATA_ERROR
+		       $PS_EXIT_TIMEOUT_ERROR
+		       metadataLookupStr
+		       metadataLookupBool
+		       caturi
+		       );
+
+my $dbh = getDBHandle();
+
+my $stmt = $dbh->prepare("SELECT * FROM dsProduct");
+$stmt->execute();
+
+print "# productID  |Most recent |Time registered     |type     |Description                     |\n";
+
+while( my $row = $stmt->fetchrow_hashref()) {
+    my $prod_id = $row->{prod_id};
+
+    my ($date, $time) = split / /, $row->{last_update};
+    my $datetime = $date . "T" . $time . "Z";
+
+    my $last_fs =  $row->{last_fs};
+    if (!$last_fs) {
+        # dsrootls is fussy when this field is blank
+        $last_fs = "none";
+    }
+
+    my $line = sprintf "%-13s|%-12s|%-20s|%-9s|%-32s|\n",  $row->{prod_name}, $last_fs, $datetime,
+        $row->{type}, $row->{description};
+
+    print $line;
+}
+
+# todo move to a module
+sub getDBHandle {
+    my $ipprc =  PS::IPP::Config->new(); # IPP Configuration
+    my $siteConfig = $ipprc->{_siteConfig};
+
+    my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
+    my $dbname   = metadataLookupStr($siteConfig, 'DBNAME');
+    my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
+    my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
+    exit ($PS_EXIT_CONFIG_ERROR) unless defined $dbserver and $dbname and $dbuser and $dbpass;
+
+    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
+
+    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
+    return $dbh;
+}
Index: /trunk/DataStoreServer/scripts/tabledefs.sql
===================================================================
--- /trunk/DataStoreServer/scripts/tabledefs.sql	(revision 16762)
+++ /trunk/DataStoreServer/scripts/tabledefs.sql	(revision 16762)
@@ -0,0 +1,112 @@
+DROP TABLE IF EXISTS dsFileType;
+DROP TABLE IF EXISTS dsFile;
+DROP TABLE IF EXISTS dsFileset;
+DROP TABLE IF EXISTS dsProduct;
+
+#
+# TODO: arguably we should put prod_col_n in another table, but since the
+# number of products will be small, it is simpler to just keep them here
+#
+
+CREATE TABLE dsProduct (
+    prod_id     BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+    prod_name   VARCHAR(64) NOT NULL UNIQUE,
+    last_update datetime,
+    last_fs     VARCHAR(64),
+    type        VARCHAR(64),
+    description VARCHAR(255),
+    path_name   VARCHAR(255),
+    prod_col_0  VARCHAR(64),        # names of product specific columns
+    prod_col_1  VARCHAR(64),        # shows up in the header list
+    prod_col_2  VARCHAR(64),
+    prod_col_3  VARCHAR(64),
+    prod_col_4  VARCHAR(64),
+    prod_col_5  VARCHAR(64),
+    prod_col_6  VARCHAR(64),
+    prod_col_7  VARCHAR(64)
+) Engine=InnoDB DEFAULT CHARSET=latin1;
+   
+
+CREATE TABLE dsFileset (
+    prod_id     BIGINT(20),
+    fileset_id  BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+    fileset_name VARCHAR(64) NOT NULL UNIQUE,
+    reg_time    datetime NOT NULL,
+    type        VARCHAR(64),
+    prod_col_0  VARCHAR(64),        # values for product specific columns
+    prod_col_1  VARCHAR(64),
+    prod_col_2  VARCHAR(64),
+    prod_col_3  VARCHAR(64),
+    prod_col_4  VARCHAR(64),
+    prod_col_5  VARCHAR(64),
+    prod_col_6  VARCHAR(64),
+    prod_col_7  VARCHAR(64),
+    FOREIGN KEY  (prod_id) REFERENCES dsProduct(prod_id)
+) Engine=InnoDB DEFAULT CHARSET=latin1;
+
+
+CREATE TABLE dsFile (
+    fileset_id  BIGINT(20),
+    file_id     BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+    file_name   VARCHAR(255) NOT NULL,
+    bytes       BIGINT(20),
+    md5sum      VARCHAR(255),
+    type        VARCHAR(64),
+    type_col_0  varchar(64),        # value for type specific columns
+    type_col_1  varchar(64),
+    type_col_2  varchar(64),
+    type_col_3  varchar(64),
+    type_col_4  varchar(64),
+    type_col_5  varchar(64),
+    type_col_6  varchar(64),
+    type_col_7  varchar(64),
+    FOREIGN KEY (fileset_id) REFERENCES dsFileset(fileset_id)
+) Engine=InnoDB DEFAULT CHARSET=latin1;
+
+#
+# names of the type specific columns for a data store file type
+# 
+# if not null, the cols will show up in the indexes
+#
+
+CREATE TABLE dsFileType (
+    type        VARCHAR(64),
+    type_col_0  varchar(64),
+    type_col_1  varchar(64),
+    type_col_2  varchar(64),
+    type_col_3  varchar(64)
+) Engine=InnoDB DEFAULT CHARSET=latin1;
+
+# type specific columns for type chip
+INSERT INTO dsFileType (type, type_col_0) VALUES('chip', 'chipname');
+INSERT INTO dsFileType (type) VALUES('psrequest');
+INSERT INTO dsFileType (type) VALUES('psresults');
+INSERT INTO dsFileType (type) VALUES('pstamp');
+
+INSERT INTO dsProduct (prod_id, prod_name, last_update, last_fs, type, description, path_name)
+        VALUES (0, 'pstamprequest', UTC_TIMESTAMP(), NULL, 'psrequest', 'Postage Stamp Request',
+        '/var/www/html/ds/dsroot');
+
+INSERT INTO dsProduct (prod_id, prod_name, last_update, last_fs, type, description, path_name)
+        VALUES (0, 'sample', UTC_TIMESTAMP(), NULL, 'psresult', 'Postage Stamp Request', '/var/www/html/ds/dsroot');
+
+#
+# the width of the product specific column header strings should match the usual width of the columns
+# to make the fileset lists look neat (note 'telescope pointing') 
+#
+
+INSERT INTO dsProduct VALUES (0, 'gpc1', UTC_TIMESTAMP(), NULL, 'image',
+        'Gigapixel Camera 1', '/var/www/html/ds/dsroot', 'telescope pointing        ', 'etime', 'f',
+        'airm', 'comments', NULL, NULL, NULL);
+
+INSERT INTO dsFileset VALUES(1, 0, 'fileset1', UTC_TIMESTAMP(), 'PSTAMP');
+INSERT INTO dsFileset VALUES(1, 0, 'fileset2', UTC_TIMESTAMP(), 'PSTAMP');
+INSERT INTO dsFileset VALUES(1, 0, 'fileset13', UTC_TIMESTAMP(), 'PSTAMP');
+
+INSERT INTO dsFile VALUES(LAST_INSERT_ID(), 0, 'fileset13.fits', 8640, 'b8217dcb393de3af02d915d77025021d',
+    'psrequest');
+
+    
+
+
+