#! /usr/bin/python

# Copyright 1999-2009 University of Chicago
# 
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
# http://www.apache.org/licenses/LICENSE-2.0
# 
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
"""
The globus-usage-uploader command is a utility which parses usage
packets in directories of the form created by globus-usage-collector
and uploads them to a postgresql database.
"""

import os
import sys
import getopt
import time

import globus.usage.usageconfig as usageconfig

def usage(exe):
    """
    Print usage string and exit

    """
    print """
Usage: %s [options]
Options:
  -h                        Print this help message and exit
  -t COMPONENT              Aggregate data for toolkit component (gridftp or gram)
  -d YYYY-MM-DD             Date of hourly data to aggregate 
  -m                        Aggregate the month containing -d DATE
  -c CONFIG-FILE            Read configuration from CONFIG-FILE""" \
    % os.path.basename(exe)
    sys.exit(0)

def main(argv):
    """
    globus-usage-aggregator main program

    """
    opts, args = getopt.getopt(argv[1:], "hc:d:t:m")
    config_file = None
    agg_date = None
    component = None
    month = False
    for option_name in opts:
        if option_name[0] == "-h":
            usage(argv[0])
        elif option_name[0] == "-c":
            config_file = option_name[1]
        elif option_name[0] == "-d":
            agg_date = option_name[1]
        elif option_name[0] == '-t':
            component = option_name[1]
        elif option_name[0] == '-m':
            month = True
    if args != []:
        print "Unexpected command-line: ", args
        return 1

    config = usageconfig.UsageConfig(path = config_file)
    con = config.connect()
    cursor = con.cursor()

    if agg_date is None:
        t = time.gmtime(time.time() - 24*60*60)
        agg_date = "%04d-%02d-%02d" % (t.tm_year, t.tm_mon, t.tm_mday)

    if component == 'gram':
        # Aggregate GRAM5 into daily aggregation
        cursor.execute('START TRANSACTION ISOLATION LEVEL SERIALIZABLE')
        if not month:
            cursor.execute("""
                INSERT INTO gram5_aggregations_daily (aggregation_time, job_manager_id, failure_code, job_count)
                SELECT DATE_TRUNC('day', aggregation_time), gram5_job_managers.id, failure_code, SUM(job_count)
                from gram5_aggregations_hourly
                inner join gram5_job_manager_instances
                    on gram5_aggregations_hourly.job_manager_instance_id = gram5_job_manager_instances.id
                inner join gram5_job_managers
                    on gram5_job_manager_instances.job_manager_id = gram5_job_managers.id
                where date(aggregation_time) = '%s'
                group by date_trunc('day', aggregation_time), gram5_job_managers.id, failure_code""" %(agg_date) )
            cursor.execute("""
                DELETE FROM gram5_aggregations_hourly
                WHERE date(aggregation_time) = '%s'
                """ %(agg_date) )
        else:
            cursor.execute("""
                INSERT INTO gram5_aggregations_monthly (aggregation_time, job_manager_id, failure_code, job_count)
                SELECT DATE_TRUNC('month', aggregation_time), gram5_job_managers.id, failure_code, SUM(job_count)
                from gram5_aggregations_daily
                inner join gram5_job_manager_instances
                    on gram5_aggregations_hourly.job_manager_instance_id = gram5_job_manager_instances.id
                inner join gram5_job_managers
                    on gram5_job_manager_instances.job_manager_id = gram5_job_managers.id
                where date(aggregation_time) = '%s'
                group by date_trunc('month', aggregation_time), gram5_job_managers.id, failure_code""" %(agg_date) )
        con.commit()
    elif component == 'gridftp':
        cursor.execute('START TRANSACTION ISOLATION LEVEL SERIALIZABLE')
        if not month:
            # Aggregate GridFTP into daily aggregation
            cursor.execute("""
                INSERT INTO gftp_server_histogram_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    log10_transfer_size_bytes,
                    log10_transfer_rate_bps,
                    transfer_count, byte_count)
                SELECT DATE_TRUNC('day', aggregation_time), 'daily',
                    server_id, log10_transfer_size_bytes, log10_transfer_rate_bps,
                    SUM(transfer_count), SUM(byte_count)
                FROM gftp_server_histogram_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                GROUP by DATE_TRUNC('day', aggregation_time),
                        server_id,
                        log10_transfer_size_bytes,
                        log10_transfer_rate_bps""" % ( agg_date))
            cursor.execute("""
                INSERT INTO gftp_server_xfer_type_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    trans_type,
                    transfer_count,
                    byte_count)
                SELECT DATE_TRUNC('day', aggregation_time),
                    'daily',
                    server_id,
                    trans_type,
                    SUM(transfer_count),
                    SUM(byte_count)
                FROM gftp_server_xfer_type_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                GROUP BY
                    DATE_TRUNC('day', aggregation_time),
                    server_id,
                    trans_type""" % (agg_date))
            cursor.execute("""
                INSERT INTO gftp_server_stream_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    num_streams,
                    transfer_count,
                    byte_count)
                SELECT DATE_TRUNC('day', aggregation_time),
                    'daily',
                    server_id,
                    num_streams,
                    SUM(transfer_count),
                    SUM(byte_count)
                FROM gftp_server_stream_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                GROUP BY
                    DATE_TRUNC('day', aggregation_time),
                    server_id, 
                    num_streams""" % (agg_date))

            cursor.execute("""
                INSERT INTO gftp_client_server_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    client_id,
                    transfer_count,
                    byte_count)
                SELECT DATE_TRUNC('day', aggregation_time),
                    'daily',
                    server_id,
                    client_id,
                    SUM(transfer_count),
                    SUM(byte_count)
                FROM gftp_client_server_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                GROUP BY
                    DATE_TRUNC('day', aggregation_time),
                    server_id, 
                    client_id""" % (agg_date))

            cursor.execute("""
                DELETE 
                FROM gftp_server_histogram_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
               """ % (agg_date))

            cursor.execute("""
                DELETE
                FROM gftp_server_xfer_type_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                """ % (agg_date))

            cursor.execute("""
                DELETE
                FROM gftp_server_stream_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                    """ % (agg_date))
            
            cursor.execute("""
                DELETE
                FROM gftp_client_server_aggregations
                WHERE aggregation_time_range = 'hourly'
                    AND DATE_TRUNC('day', aggregation_time) = '%s'
                    """ % (agg_date))
        else:
            # Aggregate GridFTP into monthly aggregation
            cursor.execute("""
                INSERT INTO gftp_server_histogram_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    log10_transfer_size_bytes,
                    log10_transfer_rate_bps,
                    transfer_count, byte_count)
                SELECT DATE_TRUNC('month', aggregation_time), 'monthly',
                    server_id, log10_transfer_size_bytes, log10_transfer_rate_bps,
                    SUM(transfer_count), SUM(byte_count)
                FROM gftp_server_histogram_aggregations
                WHERE aggregation_time_range = 'daily'
                    AND DATE_TRUNC('month', aggregation_time) = date_trunc('month', '%s'::date)
                GROUP by DATE_TRUNC('month', aggregation_time),
                        server_id,
                        log10_transfer_size_bytes,
                        log10_transfer_rate_bps""" % ( agg_date))
            cursor.execute("""
                INSERT INTO gftp_server_xfer_type_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    trans_type,
                    transfer_count,
                    byte_count)
                SELECT DATE_TRUNC('month', aggregation_time),
                    'monthly',
                    server_id,
                    trans_type,
                    SUM(transfer_count),
                    SUM(byte_count)
                FROM gftp_server_xfer_type_aggregations
                WHERE aggregation_time_range = 'daily'
                    AND DATE_TRUNC('month', aggregation_time) = date_trunc('month', '%s'::date)
                GROUP BY
                    DATE_TRUNC('month', aggregation_time),
                    server_id,
                    trans_type""" % (agg_date))
            cursor.execute("""
                INSERT INTO gftp_server_stream_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    num_streams,
                    transfer_count,
                    byte_count)
                SELECT DATE_TRUNC('month', aggregation_time),
                    'monthly',
                    server_id,
                    num_streams,
                    SUM(transfer_count),
                    SUM(byte_count)
                FROM gftp_server_stream_aggregations
                WHERE aggregation_time_range = 'daily'
                    AND DATE_TRUNC('day', aggregation_time) = DATE_TRUNC('month', '%s'::date)
                GROUP BY
                    DATE_TRUNC('month', aggregation_time),
                    server_id, 
                    num_streams""" % (agg_date))

            cursor.execute("""
                INSERT INTO gftp_client_server_aggregations(
                    aggregation_time,
                    aggregation_time_range,
                    server_id,
                    client_id,
                    transfer_count,
                    byte_count)
                SELECT DATE_TRUNC('month', aggregation_time),
                    'monthly',
                    server_id,
                    client_id,
                    SUM(transfer_count),
                    SUM(byte_count)
                FROM gftp_client_server_aggregations
                WHERE aggregation_time_range = 'daily'
                    AND DATE_TRUNC('month', aggregation_time) = DATE_TRUNC('month', '%s'::date)
                GROUP BY
                    DATE_TRUNC('month', aggregation_time),
                    server_id, 
                    client_id""" % (agg_date))
        con.commit()
    else:
        print "Unknown component %s" %( str(component))
    return 0


if __name__ == "__main__":
    retval = main(sys.argv)
    sys.exit(retval)
# vim: ts=4: sw=4: filetype=python :
