/*
Copyright (c) 2000                      RIPE NCC


All Rights Reserved

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose and without fee is hereby granted,
provided that the above copyright notice appear in all copies and that
both that copyright notice and this permission notice appear in
supporting documentation, and that the name of the author not be
used in advertising or publicity pertaining to distribution of the
software without specific, written prior permission.

THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING
ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS; IN NO EVENT SHALL
AUTHOR BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN
AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

-------------------------------------------------------------------------------
Module Header
Filename          : querydb.c
Purpose           : Query The Test Traffic DB for routeids
Author            : Manuel Valente <manuel@ripe.net>
Date              : 20000315
Description       : 
Language Version  : gcc version 2.95.2
OSs Tested        : Solaris 2.6
Command Line      : 
Input Files       : 
Output Files      :
External Programs : mysqld
Problems          : Should make sure that it scales up to 100 boxes
To Do             :
Comments          :
-------------------------------------------------------------------------------
*/

static char const rcsid[] = "$Id: querydb.c,v 1.2 2002/08/19 01:07:00 wilhelm Exp $";

#include "querydb.h"

/* Data is a hash
Keys are the machines indexes ( 1- 47)
Values are linked list of pointers which contain buffer data from the DB */

/*
-------------------------------------------------------------------------------
Purpose           : Remove a record from the Data Hash
Params            : key, value, NULL
Returns           : 1
Comments          : Frees the memory associated with the record
*/
gboolean remove_item (gpointer key,gpointer value,gpointer user_data)
{
	struct vectorstruct* record;
	struct vectorstruct* index;
	
	/* Free the memory for all the records of this hash key */
	index = (struct vectorstruct*)value;
	while (index != NULL)
	{
		record = index;
		index = index->next;
		free(record);
	};
	return (1);
};
/*
-------------------------------------------------------------------------------
Purpose           : Reset the Data hash
Params            : Data Hash
Returns           : NULL
Comments          : Calls remove_item on each record
*/

void reset_data(GHashTable* Data)
{
	g_hash_table_foreach_remove (Data,remove_item,NULL);
	g_hash_table_destroy(Data);
};
/*
-------------------------------------------------------------------------------
Purpose           : Print the contents of the Data Hash
Params            : key, value, NULL
Returns           : NULL
Comments          : Only useful for debugging purposes
*/

void print_data (gpointer key,gpointer value,gpointer user_data)
{
	struct vectorstruct* index;
	
	printf ("[%u] => ",*(int*)key);
	
	index = value;
	
	while (index != NULL)
	{
		printf ("%u ",index->tstart);
		index = index->next;
	};
	printf ("\n\n");
	
};

/*
-------------------------------------------------------------------------------
Purpose           : Build the Data Hash
Params            : MySQL handle, srcid, start timestamp
Returns           : Pointer to GHashTable(success) or NULL (failure)
Comments          : reset_data() must be called to free the allocated memory
*/

GHashTable* build_data (MYSQL *mysql, unsigned int src, time_t tstart)
{
	char query [1024];
	MYSQL_RES *result;
	MYSQL_ROW row;
		
	char* p;
	unsigned int dst,tend;
	
	unsigned int* dstp;
	
	GHashTable* Data;
	
	struct vectorstruct* record;
	struct vectorstruct* index;
		
	/* Initialize Data */
        Data = g_hash_table_new (g_int_hash,g_int_equal);
	
	/* End of the day is start + 86400 s */
	tend = tstart + 86400;	
		
	/* Execute SQL query */
	sprintf (query,"SELECT Routes.len, Records.dst, Records.routeid, Records.tstart, Records.tend FROM Routes, Records WHERE Routes.id=Records.routeid AND Records.src=%u AND Records.tend>=%d AND Records.tstart<=%d ORDER BY Records.dst,Records.tstart",
	src,(unsigned int)(tstart-TIME_LIMIT),tend+TIME_LIMIT);
		
	if (mysql_query(mysql,query))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		return (NULL);
	};   

	if (!(result = mysql_use_result(mysql)))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		mysql_free_result(result);
		return (NULL);
	};
		
	/* For each row returned from the DB */
	while ((row = mysql_fetch_row(result)))
	{
		dst = atoi(row[1]);

		/* Create a record with the contents of the row */
		record = malloc(sizeof(struct vectorstruct));
		record->nhops   = atoi(row[0]);
		record->routeid = atoi(row[2]);
		record->tstart  = atoi(row[3]);
		record->tend    = atoi(row[4]);
		record->next    = NULL;
	
		p = g_hash_table_lookup (Data,&dst);
		
		/* If there is no key in the Data hash for this index */
		if (p == NULL)
		{
			/* Allocate memory to hold key */
			dstp = malloc(sizeof(int));
			*dstp = dst;
			
			g_hash_table_insert(Data,dstp,record);
						
			continue;
		};
		
		index = (struct vectorstruct*)p;
		
		/* Find last record for this key */
		while (index->next != NULL)
		{
			index = index->next;
		};
		
		/* Append new record to the end of the list for this key */
		index->next = record;
	};
	
	mysql_free_result(result);
	
	//g_hash_table_foreach (Data,print_data,NULL);
	
	//exit(0);
		
	return (Data);	
};

/*
-------------------------------------------------------------------------------
Purpose           : Match an SNDP vector with a routeID
Params            : Data Hash, targetID, timestamp, routeID, Nhops
Returns           : 1(success) or 0 (problem)
Comments          : build_data has to be called before this function
*/
int match_vector(GHashTable* Data, unsigned int targetID, time_t timestamp, unsigned int *routeID, unsigned int *Nhops)
{
	struct vectorstruct* record;
	struct vectorstruct* index;
	
	char* p;
	
	*routeID = -1;
	*Nhops   = -1;
	
	/* Find key for the destination box */
	p = g_hash_table_lookup(Data,&targetID);
	
        if (p == NULL)
        {
#ifdef DEBUG
		fprintf (stderr,"Couldn't find any data for box %u\n",targetID);
#endif
                return (0);
        };	
	
	index = (struct vectorstruct*)p;
	record = NULL;
	
	/* Search the value list for a suitable record */
	while (index != NULL)
	{
		if (timestamp < index->tstart)
		{
			/* We passed the valid records - exit now */
			break;
		}
		else
		{
			if (timestamp <= index->tend)
			{
				/* tstart < timestamp < tend 
				Real value - store it and exit */
				record = index;
				break;
			}
			else
			{
				if (timestamp <= index->tend + TIME_LIMIT)
				{
					/* tend < timestamp < tend + TIME_LIMIT
					Potential value - keep it unless
					we find a better one later */
					record = index;
				};
			};
		};
		
		index = index->next;
	};
	
	if (record == NULL) { return (0); };
	
	*routeID = record->routeid;
	*Nhops = record->nhops;

	return (1);
};


/*
-------------------------------------------------------------------------------
Purpose           : Lookup a route
Params            : mysql handler, sourceID, targetID, timestamp, routeID, Nhops
Returns           : 1(success) or 0 (problem)
Comments          :
*/

int match_vector_sql(MYSQL *mysql, unsigned int sourceID, unsigned int targetID, time_t timestamp, unsigned int *routeID, unsigned int *Nhops)
{
	char query [1024];
	MYSQL_RES *result;
	MYSQL_ROW row;
	
	*routeID = -1;
	*Nhops   = -1;

	sprintf (query,"SELECT Routes.len, Records.routeid, Records.tstart, Records.tend FROM Routes, Records WHERE Records.src=%u AND Records.dst=%u AND Records.tstart>=%u AND Records.tend<=%u AND Records.routeid=Routes.id ORDER BY Records.tstart",
	sourceID,targetID,(unsigned int)(timestamp-TIME_LIMIT),(unsigned int)(timestamp));
	
	//printf ("%s\n",query);

	if (mysql_query(mysql,query))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		return (0);
	};   

	if (!(result = mysql_use_result(mysql)))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		mysql_free_result(result);	
		return (0);
	};

	while ((row = mysql_fetch_row(result)))
	{
		*routeID = atoi(row[1]);
		*Nhops   = atoi(row[0]);

		if ((atoi(row[2]) <= timestamp) && (atoi(row[3]) >= timestamp))
		{	
			break;
		};
		
		
	};

	mysql_free_result(result);
	
	return (*routeID!=-1);
};


/*
-------------------------------------------------------------------------------
Purpose           : Return the corresponding route from a given Routeid
Params            : MySQL handle, routeid, route, route length
Returns           : 0 (ok) or 1(error)
Comments          : 
*/
int route_from_routeid (MYSQL *mysql, unsigned int routeid, char* route, unsigned int* len)
{
	char query [1024];
	MYSQL_RES *result;
	MYSQL_ROW row;
	
	sprintf(query,"SELECT route,len from Routes where id=%u",routeid);
	
	if (mysql_query(mysql,query))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		return (0);
	};   

	if (!(result = mysql_use_result(mysql)))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		mysql_free_result(result);	
		return (0);
	};

	if ((row = mysql_fetch_row(result)))
	{		
		strcpy(route,route_int_to_ip(row[0]));
		*len = atoi(row[1]);
		
		mysql_free_result(result);		
		return (1);
	};
	
	mysql_free_result(result);		
	return (0);
};


/*
-------------------------------------------------------------------------------
Purpose           : Return the corresponding routeid from a Route
Params            : MySQL handle, route, routeid, route length
Returns           : 0 (ok) or 1(error)
Comments          : 
*/
int routeid_from_route (MYSQL *mysql, char* route, unsigned int* routeid, unsigned int* len)
{
	char query [1024];
	MYSQL_RES *result;
	MYSQL_ROW row;
	
	strcpy(route,route_ip_to_int(route));
	
	sprintf(query,"SELECT id,len from Routes where route=\"%s\"",route);
		
	if (mysql_query(mysql,query))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		return (0);
	};   

	if (!(result = mysql_use_result(mysql)))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		mysql_free_result(result);	
		return (0);
	};

	if ((row = mysql_fetch_row(result)))
	{
		*routeid = atoi(row[0]);		
		*len = atoi(row[1]);
				
		mysql_free_result(result);	
		return (1);
	}

	mysql_free_result(result);	
	return (0);
};

/*
-------------------------------------------------------------------------------
Purpose           : Return all routes in a time interval
Params            : MySQL handle, source, destination, t_start, t_end,
                    Pointer to rec struct
Returns           : 1(ok) or 0(error)
Comments          : 
*/
int get_routes_by_time (MYSQL *mysql, unsigned int sourceID, unsigned int targetID, time_t t_start, time_t t_end, struct datastruct **d)
{
	char query [1024];
	MYSQL_RES *result;
	MYSQL_ROW row;
	
	struct datastruct* current;
	struct datastruct* p;

	current = NULL; p = NULL;

	/* Not Found -> query the DB*/
	sprintf (query,"SELECT Routes.len, Records.routeid, Records.tstart, Records.tend FROM Routes, Records WHERE Records.src=%u AND Records.dst=%u AND ((INTERVAL(Records.tstart, %u, %u)=1) OR (INTERVAL(%u, Records.start, Records.tend+%u)=1)) AND Records.routeid=Routes.id ORDER BY Records.tstart",
	sourceID,targetID,(unsigned int)(t_start),(unsigned int)(t_end),(unsigned int)(t_start),TIME_LIMIT);
	
	//printf ("%s\n",query);

	if (mysql_query(mysql,query))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		return (0);
	};   

	if (!(result = mysql_use_result(mysql)))
	{
		fprintf(stderr, "Error: %s\n", mysql_error(mysql));
		mysql_free_result(result);
		return (0);
	};

	/* Set return params - add new element to Buffers */
	while ((row = mysql_fetch_row(result)))
	{
		p = current;

		current = malloc(sizeof(struct datastruct));
		current->src     = sourceID;
		current->dst     = targetID;
		current->next    = NULL;
		current->tstart  = atoi(row[2]);
		current->tend    = atoi(row[3]);
		current->routeid = atoi(row[1]);
		current->nhops   = atoi(row[0]);
		
		if (*d == NULL) { *d = current; }
		else {p->next = current;};
	};
	
	mysql_free_result(result);

	return (1);
};

/*
-------------------------------------------------------------------------------
Purpose           : Print routes in a formatted way
Params            : Data struct
Returns           : NULL
Comments          : 
*/
void display_routes (struct datastruct *d)
{
	struct datastruct* current;
	
	current = d;
	
	while (current != NULL)
	{
		printf ("%d %d %d %d %d %d\n",current->src,current->dst,current->tstart,current->tend,current->routeid,current->nhops);
		current = current->next;
	};
};