#include <sys/types.h> 
#include <sys/stat.h> 
#include <sys/param.h>
#include <sys/mount.h>
#include <Carbon/Carbon.h>
#include <unistd.h>
#include <math.h>
#include <openssl/md5.h>
#include "database.h"

void HandleDBerror(dbi_conn Conn, void *user_argument)
{
	const char *msg;
	
	dbi_conn_error(Conn, &msg);
	if(msg)
		ServerLoger->MakeEntry("Database- " + string((char *)user_argument) + string(msg));
}

void DumpDBDriverList(int cs, char *buf, unsigned long size)
{
	dbi_driver dvr;
	const char *name, *version;
	int tx_length;

	dvr = NULL;
	while(dvr = dbi_driver_list(dvr)){
		name = dbi_driver_get_name(dvr);
		version = dbi_driver_get_version(dvr);
		tx_length = snprintf(buf, size, "%s\t%s\n", name, version);
        my_send(cs, buf, tx_length, false);
	}
}

unsigned char MakeLogEntry(ProgramLogStruct* rec)
{
	dbi_driver driver;
    dbi_conn conn;
    dbi_result result;
	char *Name, *Artist, *Album, *Comment, *Source;
	const char *prefix;
	
	if((driver = dbi_driver_open(GetMetaData(0, "db_type").c_str())) == NULL)
		return false;
	if((conn = dbi_conn_open(driver)) == NULL)
		return false;
		
	dbi_conn_set_option(conn, "host", GetMetaData(0, "db_server").c_str());
    dbi_conn_set_option(conn, "username", GetMetaData(0, "db_user").c_str());
    dbi_conn_set_option(conn, "password", GetMetaData(0, "db_pw").c_str());
    dbi_conn_set_option(conn, "dbname", GetMetaData(0, "db_name").c_str());
	// for sqlite only
	dbi_conn_set_option(conn, "sqlite-dbdir", GetMetaData(0, "db_dir").c_str());


    if(dbi_conn_connect(conn))
		return false;
		
	prefix = GetMetaData(0, "db_prefix").c_str();
	
	dbi_conn_error_handler(conn, HandleDBerror, (void*)("MakeLogEntry "));	
	
	// allocate, copy and encode the strings in the db's format
	Name = (char*)malloc(rec->name.length() + 1);
	strcpy(Name, rec->name.c_str());
	dbi_driver_quote_string(driver, &Name);
	
	Artist = (char*)malloc(rec->artist.length() + 1);
	strcpy(Artist, rec->artist.c_str());
	dbi_driver_quote_string(driver, &Artist);
	
	Album = (char*)malloc(rec->album.length() + 1);
	strcpy(Album, rec->album.c_str());
	dbi_driver_quote_string(driver, &Album);
	
	Source = (char*)malloc(rec->source.length() + 1);
	strcpy(Source, rec->source.c_str());
	dbi_driver_quote_string(driver, &Source);
	
	Comment = (char*)malloc(rec->comment.length() + 1);
	strcpy(Comment, rec->comment.c_str());
	dbi_driver_quote_string(driver, &Comment);

	// perform the sql insert function
    result = dbi_conn_queryf(conn, "INSERT INTO %slogs (Item, Location, Time, Name, Artist, Album, Added, ArtistID, AlbumID, "
				"Comment, Source) VALUES (%lu, %lu, %ld, %s, %s, %s, %u, %lu, %lu, %s, %s)", 
				prefix, rec->ID, rec->location, rec->when, Name, Artist, Album, rec->added, 
				rec->artistID, rec->albumID, Comment, Source);
    
	// clean up
    dbi_result_free(result);
	dbi_conn_error_handler(conn, NULL, NULL);	
    dbi_conn_close(conn);
			
	// free char strings
	if(Name) free(Name);
	if(Artist) free(Artist);
	if(Album) free(Album);
	if(Comment) free(Comment);
	if(Source) free(Source);
	
	return true;
}

void GetItemMetaData(unsigned long UID, CFURLRef url)
{
	dbi_driver driver;
    dbi_conn conn;
    dbi_result result;
	const char *Str;
	const char *prefix;
	char Type[8];
	char buf[32];
	float Float;
	unsigned char isAbsolutePath;
	CFStringRef input_str, output_str;
	char input[256];
	unsigned long recID;
	
	if((driver = dbi_driver_open(GetMetaData(0, "db_type").c_str())) == NULL)
		return;
	if((conn = dbi_conn_open(driver)) == NULL)
		return;
		
	dbi_conn_set_option(conn, "host", GetMetaData(0, "db_server").c_str());
    dbi_conn_set_option(conn, "username", GetMetaData(0, "db_user").c_str());
    dbi_conn_set_option(conn, "password", GetMetaData(0, "db_pw").c_str());
    dbi_conn_set_option(conn, "dbname", GetMetaData(0, "db_name").c_str());
	// for sqlite only
	dbi_conn_set_option(conn, "sqlite-dbdir", GetMetaData(0, "db_dir").c_str());


    if(dbi_conn_connect(conn))
		return;
		
	prefix = GetMetaData(0, "db_prefix").c_str();

	dbi_conn_error_handler(conn, HandleDBerror, (void *)("GetItemMetaData "));	

	// get db record id from URL
	input_str = CFURLCopyStrictPath(url, &isAbsolutePath);
	if(input_str == NULL)
		return;
	CFStringGetCString(input_str, input, sizeof(input), CFStringGetSystemEncoding());
	CFRelease(input_str);
	if(input)
		SetMetaData(UID, "ID", string(input));
	recID = atol(input);
	
	// perform the sql query function
    result = dbi_conn_queryf(conn, "SELECT * FROM %stoc WHERE ID = %lu", prefix, recID);
	// get first record (should be the only record)
	if(!dbi_result_has_next_row(result)){
		dbi_result_free(result);
		return;
	}
	if(dbi_result_next_row(result)){ 
        Str = (const char*)dbi_result_get_binary(result, "Type");
		// make lowercase
		for(unsigned char idx = 0; idx <= strlen(Str); idx = idx + 1)
			Type[idx] = tolower(Str[idx]);
		
        Str = (const char*)dbi_result_get_binary(result, "Name");
        SetMetaData(UID, "Name", string(Str));
		
        Str = (const char*)dbi_result_get_binary(result, "Tag");
        if(Str)	
			SetMetaData(UID, "Tag", string(Str));
		
		Str = (const char*)dbi_result_get_binary(result, "Script");
		if(Str){
			// URL type escape (%nn) the string, except for " " chars.
			input_str = CFStringCreateWithCString(NULL, Str, kCFStringEncodingUTF8);
			output_str = CFURLCreateStringByAddingPercentEscapes(NULL, input_str, CFSTR(" "), NULL, kCFStringEncodingUTF8);
			Str = CFStringGetCStringPtr(output_str, kCFStringEncodingUTF8);
			if(Str) SetMetaData(UID, "Script", string(Str));
			CFRelease(output_str);
			CFRelease(input_str);
		}
				
		Float = dbi_result_get_float(result, "Duration");
		snprintf(buf, sizeof buf, "%.2f", Float);
        SetMetaData(UID, "Duration", string(buf));
 
		Float = dbi_result_get_longlong(result, "Added");
		snprintf(buf, sizeof buf, "%.0f", Float);
        SetMetaData(UID, "Added", string(buf));
	}else{
		dbi_result_free(result);
		return;
	}
	dbi_result_free(result);

	SetMetaData(UID, "Type", string(Type));
	
    if(!strcmp(Type,"file"))
		// gett additional meta data related to file references stored in the db
		GetdbFileMetaData(UID, recID, driver, conn);
    if(!strcmp(Type,"task"))
		// gett additional meta data related to task references stored in the db
		GetdbTaskMetaData(UID, recID, driver, conn);

	// clean up
	dbi_conn_error_handler(conn, NULL, NULL);	
    dbi_conn_close(conn);
}

void GetdbFileMetaData(unsigned long UID, unsigned long recID, dbi_driver driver, dbi_conn conn)
{
    dbi_result result;
	const char *Str;
	const char *vName;
	const char *prefix;
	char *mount;
	char *newURL;
	char fVol[4096];
	char buf[32];
	char path[4096];
	unsigned long inode, ulong, artID, albID;
	double Float;
	unsigned char isDir;
	unsigned char missing;
	unsigned char giveup;
	unsigned char changed;
	unsigned char md5Ptr[16];
	string MD5_str;
	CFURLRef url;
	CFStringRef URLcfstr;
	FIDParam pb;
	FSCatalogInfo catalogInfo;
	FSRefParam fs;
	Str255 tempStr;
	FSRef ref;		
	
	dbi_conn_error_handler(conn, HandleDBerror, (void *)("GetdbFileMetaData "));	

	prefix = GetMetaData(0, "db_prefix").c_str();
			
	// perform the sql query function
    result = dbi_conn_queryf(conn, "SELECT * FROM %sfile WHERE ID = %lu", prefix, recID);
																	
	// get first record (should be the only record)
	if(!dbi_result_has_next_row(result)){
		dbi_result_free(result);
		return;
	}
	if(dbi_result_next_row(result)) { 
		Str = (const char*)dbi_result_get_binary(result, "URL");
        if(Str) 
			SetMetaData(UID, "URL", string(Str));
       
		Str = dbi_result_get_string(result, "MD5");
		if(Str) 
			SetMetaData(UID, "MD5", string(Str));

		inode = dbi_result_get_ulong(result, "FileID");
		snprintf(buf, sizeof buf, "%ld", inode);
        SetMetaData(UID, "FileID", string(buf));
		
		artID = dbi_result_get_ulong(result, "Artist");
		snprintf(buf, sizeof buf, "%ld", artID);
        SetMetaData(UID, "ArtistID", string(buf));

		albID = dbi_result_get_ulong(result, "Album");
		snprintf(buf, sizeof buf, "%ld", albID);
        SetMetaData(UID, "AlbumID", string(buf));

		Float = dbi_result_get_float(result, "Volume");
		snprintf(buf, sizeof buf, "%0.2f", Float);
        SetMetaData(UID, "Volume", string(buf));

		Float = dbi_result_get_float(result, "SegIn");
		snprintf(buf, sizeof buf, "%0.1f", Float);
        SetMetaData(UID, "SegIn", string(buf));

		Float = dbi_result_get_float(result, "SegOut");
		snprintf(buf, sizeof buf, "%0.1f", Float);
        SetMetaData(UID, "SegOut", string(buf));

		Float = dbi_result_get_float(result, "FadeOut");
		snprintf(buf, sizeof buf, "%0.1f", Float);
        SetMetaData(UID, "FadeOut", string(buf));

		Float = dbi_result_get_float(result, "Memory");
		snprintf(buf, sizeof buf, "%0.1f", Float);
        SetMetaData(UID, "Memory", string(buf));
		
		Str = (const char*)dbi_result_get_binary(result, "OutCue");
		if(Str) 
			SetMetaData(UID, "OutCue", string(Str));

		ulong = dbi_result_get_ulong(result, "Track");
		snprintf(buf, sizeof buf, "%ld", ulong);
        SetMetaData(UID, "Track", string(buf));

		vName = (const char*)dbi_result_get_binary(result, "Mount");
        if(vName) 
			SetMetaData(UID, "Mount", string(vName));
		 		
		missing = dbi_result_get_uchar(result, "Missing");
		snprintf(buf, sizeof buf, "%d", missing);
        SetMetaData(UID, "Missing", string(buf));
		dbi_result_free(result);

		// get Artist Name
		result = dbi_conn_queryf(conn, "SELECT Name FROM %sartist WHERE ID = %lu", prefix, artID);
		// get first record (should be the only record)
		if(dbi_result_has_next_row(result)){
			if(dbi_result_next_row(result)) { 
				Str = (const char*)dbi_result_get_binary(result, "Name");
				SetMetaData(UID, "Artist", string(Str));
			}
		}
		dbi_result_free(result);		

		// get Album Name
		result = dbi_conn_queryf(conn, "SELECT Name FROM %salbum WHERE ID = %lu", prefix, albID);
		// get first record (should be the only record)
		if(dbi_result_has_next_row(result)){
			if(dbi_result_next_row(result)) { 
				Str = (const char*)dbi_result_get_binary(result, "Name");
				SetMetaData(UID, "Album", string(Str));
			}
		}
		dbi_result_free(result);
	}else{
		dbi_result_free(result);
		return;
	}
	
	// check file realted properties in db against the file itself... find file if needed.
	// use file as final authority for properties
	giveup = false;
	changed = false;
	do{
		if(!missing){
			// not marked as missing
			if(!URLfromString(&url, GetMetaData(UID, "URL").c_str())){
				SetMetaData(UID, "Missing", "1");
				missing = 1; // it is missing!
			}else{
				if(!CFURLGetFileSystemRepresentation(url, true, (unsigned char*)path, sizeof(path))){
					SetMetaData(UID, "Missing", "1");
					missing = 1; // it is missing!				
				}else{
					if(!GetFileVolume(path, fVol)){
						SetMetaData(UID, "Missing", "1");
						missing = 1; // it is missing!
					}
					else if(!GetFileInode(path, &inode)){
						SetMetaData(UID, "Missing", "1");
						missing = 1; // it is missing!
					}
					else if(!GetFileMD5(path, md5Ptr)){
						SetMetaData(UID, "Missing", "1");
						missing = 1; // it is missing!
					}
					else{
						if(atoi(GetMetaData(UID, "Missing").c_str()))
							changed = true;
						SetMetaData(UID, "Missing", "0");
						
						snprintf(path, sizeof(path), "%lu", inode);
						if(GetMetaData(UID, "FileID") != string(path))
							changed = true;
						SetMetaData(UID, "FileID", string(path));

						if(GetMetaData(UID, "Mount") != string(fVol))
							changed = true;								
						SetMetaData(UID, "Mount", string(fVol));
						
						MD5_str = "";
						for(int i=0; i<16; i++){
							snprintf(buf, sizeof(buf), "%02x", md5Ptr[i]);
							MD5_str = MD5_str + string(buf);
						}
						if(GetMetaData(UID, "MD5") != MD5_str)
							changed = true;
						SetMetaData(UID, "MD5", MD5_str);
						giveup = true;
					}
				}
				CFRelease(url);	
			}
		}
		if(missing){
			// Marked as missing... try to find it

			// first, get the mountpoint volume ref number
			if(!FSPathMakeRef((const UInt8*)GetMetaData(UID, "Mount").c_str(), &ref, &isDir)){
				if(!FSGetCatalogInfo(&ref, kFSCatInfoVolume, &catalogInfo, NULL, NULL, NULL)){
					// next try to resolve the file ID reference 
					tempStr[0] = 0;
					pb.ioNamePtr = tempStr;
					pb.ioVRefNum = catalogInfo.volume;
					pb.ioFileID = atol(GetMetaData(UID, "FileID").c_str());			
					if(!PBResolveFileIDRefSync((HParmBlkPtr)&pb)){
						//Got it! Make an FSRef to the file 
						fs.ioVRefNum = catalogInfo.volume;
						fs.ioDirID = pb.ioSrcDirID;
						fs.ioNamePtr = (StringPtr)tempStr;
						fs.newRef = &ref;	
						if(!PBMakeFSRefSync(&fs)){
							//then we make a full path and URL from the FSRef
							if(!FSRefMakePath(&ref, (UInt8*)path, sizeof(path))){
								if(GetFileMD5(path, md5Ptr)){
									MD5_str = "";
									for(int i=0; i<16; i++){
										snprintf(buf, sizeof(buf), "%02x", md5Ptr[i]);
										MD5_str = MD5_str + string(buf);
									}
									if(GetMetaData(UID, "MD5") == MD5_str){
										// the MD5 hashcode is the same... it must be the same file!
										// update the metadata URL... everything else will follow as we go through the do loop again
										url = CFURLCreateFromFileSystemRepresentation(NULL, (UInt8*)path, strlen(path), false);
										URLcfstr = CFURLGetString(url);
										if(URLcfstr){
											CFStringGetCString(URLcfstr, path, sizeof(path), CFStringGetSystemEncoding());
											SetMetaData(UID, "URL", string(path));
										}
										CFRelease(url);
										missing = false;	
									}
								}
							}
						}		
					}
				}
			}
			if(missing){
				// can't find it
				ServerLoger->MakeEntry("Database- item "+GetMetaData(UID, "Name")+" ID#"+GetMetaData(UID, "ID")+": File is missing.");
				giveup = true;
			}
		}
	}while(!giveup);
	
	if(atoi(GetMetaData(0, "db_mark_missing").c_str())){
		// if we are to manage missing files in the database
		if(changed){
			// status has changed... update database
			if(missing){
				// mark record as missing
				result = dbi_conn_queryf(conn, "UPDATE %sfile SET Missing = 1 WHERE ID = %lu", prefix, recID);
				dbi_result_free(result);
			}else{
				// unmark record as missing and update file location, etc.
				mount = (char*)malloc(GetMetaData(UID, "Mount").length() + 1);
				strcpy(mount, GetMetaData(UID, "Mount").c_str());
				dbi_driver_quote_string(driver, &mount);
				newURL = (char*)malloc(GetMetaData(UID, "URL").length() + 1);
				strcpy(newURL, GetMetaData(UID, "URL").c_str());
				dbi_driver_quote_string(driver, &newURL);
				// do the update
				result = dbi_conn_queryf(
					conn, 
					"UPDATE %sfile SET Missing = 0, MD5 = '%s', FileID = %lu, Mount = %s, URL = %s WHERE ID = %lu", 
					prefix, MD5_str.c_str(), inode, mount, newURL, recID
				);
				
				dbi_result_free(result);
				free(mount);
				free(newURL);
			}
		}
	}
}

void GetdbTaskMetaData(unsigned long UID, unsigned long recID, dbi_driver driver, dbi_conn conn)
{
    dbi_result result;
	const char *Key, *Val;
	const char *prefix;
	unsigned int i, count;
	
	dbi_conn_error_handler(conn, HandleDBerror, (void *)("GetdbTaskMetaData "));	
		
	prefix = GetMetaData(0, "db_prefix").c_str();
			
	// perform the sql query function
    result = dbi_conn_queryf(conn, "SELECT * FROM %stask WHERE ID = %lu", prefix, recID);
																	
	// get first record (should be the only record)
	count = dbi_result_get_numrows(result);
	for(i = 0; i < count; i++){
		if(dbi_result_next_row(result)){ 
			Key = (const char*)dbi_result_get_binary(result, "ParamA");
			Val = (const char*)dbi_result_get_binary(result, "ParamB");
			if(Key && Val)
				SetMetaData(UID, string(Key), string(Val));
		}
	}
	dbi_result_free(result);
}

unsigned char GetFileMD5(char *file, unsigned char *md5_str)
{
    FILE *fp;
    unsigned char block[4096];
	int bSize, i;
     
	// get md5 hash of the second block of the file
    if((fp = fopen(file, "r")) == NULL)
        return false;
	if(fseek(fp, 4096, SEEK_SET) == 0){
		// file is longer than one block... calculate MD5 of the second block
		bSize = fread(block, sizeof(block), 1, fp);
		MD5(block, bSize, md5_str);
	}else{
		// file id less than one block in length, fill the MD5 array with zeros
		for(i=0; i<16; i++) md5_str[i] = 0;
	}
	fclose(fp);
	return true;
}

unsigned char GetFileVolume(char *file, char *mount)
{
	struct statfs mount_info;
     	
	// get the name of the volume that conatains the file
	if(statfs(file, &mount_info))
		return false;
	// copy the path to the volume's mount point
	strcpy(mount, mount_info.f_mntonname);
	return true;	
}

unsigned char GetFileInode(char *file, unsigned long *iNode)
{
	struct stat fstat_buf;
     	
	// get iNode id of the file
	if(stat(file, &fstat_buf))
		return false;
	*iNode = fstat_buf.st_ino;
	return true;	
}

unsigned char dbTaskRunner(unsigned long UID, unsigned char load)
{
	string subType;
	string key;
	string value;
	string supress;
	string prefix;
	string qStr;
	CFStringRef input_str, output_str;
	char *theCommand; 
	const char *cStr;
	TaskItem *task;
	
	// check to make sure it isn't already running
	tMutex->readLock(true);
	for(unsigned int i=0; i<TaskList.size(); i++){
		if(TaskList.at(i)->UID== UID)
			// it's already running
			return false;
	}
	tMutex->readUnlock();
	// if we made it this far, then it's not running
	
	prefix = GetMetaData(0, "db_prefix");
	subType = GetMetaData(UID, "Subtype");
	if(load == false){
		// playlist add 
		if(subType == "Pick"){
			// pick subtypes are run at the time they are added to the playlist
			value = GetMetaData(UID, "Query");
			if(value.length() > 0){
				// database custom query pick
				task = new TaskItem("DB Query Pick", dbPick, NULL, UID, 300L, true); // time out in 5 minutes
				return true;
			}
			value = GetMetaData(UID, "Category");
			if(value.length() > 0){
				// category pick
				supress = GetMetaData(UID, "Supress");
				if(supress == "Artist")
					qStr = "SELECT "+prefix+"file.ID as ID, MAX(ar_logs.Time) AS Time FROM "+prefix+"file, "
					+prefix+"category_item LEFT JOIN "+prefix+"logs ON ("+prefix+"file.Artist = "
					+prefix+"logs.ArtistID AND "+prefix+"logs.Location = "+GetMetaData(0, "db_loc")+") WHERE NOT ("
					+prefix+"file.Missing <=> 1) AND "+prefix+"file.ID = "+prefix+"category_item.Item AND "
					+prefix+"category_item.Category = "+value+" GROUP BY "+prefix+"file.ID ORDER BY Time LIMIT 250";
				else if(supress == "Album")
					qStr = "SELECT "+prefix+"file.ID as ID, MAX("+prefix+"logs.Time) AS Time FROM "+prefix+"file, "
					+prefix+"category_item LEFT JOIN "+prefix+"logs ON ("+prefix+"file.Album = "
					+prefix+"logs.AlbumID AND "+prefix+"logs.Location = "+GetMetaData(0, "db_loc")+") WHERE NOT ("
					+prefix+"file.Missing <=> 1) AND "+prefix+"file.ID = "+prefix+"category_item.Item AND "
					+prefix+"category_item.Category = "+value+" GROUP BY "+prefix+"file.ID ORDER BY Time LIMIT 250";
				else if(supress == "Name")
					qStr = "SELECT "+prefix+"toc.ID as ID, MAX("+prefix+"logs.Time) AS Time FROM "+prefix+"file, "
					+prefix+"category_item LEFT JOIN "+prefix+"logs ON ("+prefix+"toc.Name = "+prefix+"logs.Name AND "
					+prefix+"logs.Location = "+GetMetaData(0, "db_loc")+") LEFT JOIN "+prefix+"file ON ("
					+prefix+"toc.ID = "+prefix+"file.ID) WHERE NOT ("+prefix+"file.Missing <=> 1) AND "
					+prefix+"toc.ID = "+prefix+"category_item.Item AND "+prefix+"category_item.Category = "
					+value+" GROUP BY "+prefix+"toc.ID ORDER BY Time LIMIT 250";
				else // order by item ID last played
					qStr = "SELECT "+prefix+"toc.ID as ID, MAX("+prefix+"logs.Time) AS Time FROM "+prefix+"file, "
					+prefix+"category_item LEFT JOIN "+prefix+"logs ON ("+prefix+"toc.ID = "+prefix+"logs.Item AND "
					+prefix+"logs.Location = "+GetMetaData(0, "db_loc")+") LEFT JOIN "+prefix+"file ON ("
					+prefix+"toc.ID = "+prefix+"file.ID) WHERE NOT ("+prefix+"file.Missing <=> 1) AND "
					+prefix+"toc.ID = "+prefix+"category_item.Item AND "+prefix+"category_item.Category = "
					+value+" GROUP BY ar_toc.ID ORDER BY Time LIMIT 250";
					
				// URL type escape (%nn) the query string, except for " " chars.
				input_str = CFStringCreateWithCString(NULL, qStr.c_str(), kCFStringEncodingUTF8);
				output_str = CFURLCreateStringByAddingPercentEscapes(NULL, input_str, CFSTR(" "), NULL, kCFStringEncodingUTF8);
				cStr = CFStringGetCStringPtr(output_str, kCFStringEncodingUTF8);
				if(cStr){
					SetMetaData(UID, "Query", string(cStr));
					task = new TaskItem("Category Pick", dbPick, NULL, UID, 300L, true); // time out in 5 minutes
					return true;
				}else{
					CFRelease(output_str);
					CFRelease(input_str);
					return false;
				}
			}
			value = GetMetaData(UID, "Folder");
			if(value.length() > 0){
				// folder pick
				
				return true;
			}
		}
	}else{
		// player load attempt
		// all of these are run only when a player load is attempted
		if(subType == "Command"){
			if(GetMetaData(UID, "Command").c_str()){
				theCommand = (char *)malloc(GetMetaData(UID, "Command").size() + 1);
				if(theCommand){
					strcpy(theCommand, GetMetaData(UID, "Command").c_str());
					task = new TaskItem(GetMetaData(UID, "Command").c_str(), ExecuteCommand, (void *)theCommand, UID, 0L, true); // no time out (not cancelable)
					return true;
				}
			}
			return false;
		}
		if(subType == "Open"){
			// open (via finder) an application or file
			Execute("open '"+GetMetaData(UID, "Path")+"'", UID);
			return true;
		}
		if(subType == "Execute"){
			// execute a unix shell script, command, program, etc
			Execute(GetMetaData(UID, "Command"), UID);
			return true;
		}
	}
	return false;
}

unsigned char db_initialize(void)
{

	return true;
}

float GaussianNumber(void)
{
	float x, y, r;

	// normal (gaussian) distribution random pick
	do{
		x = drand48();
		//x = (rnd * 2.0) -1.0
		y = drand48();
        //  y = (rnd * 2.0) -1.0
		r = powf(x, 2) + powf(y, 2);
	}while((r < 1.0) && (r != 0.0));
	r = sqrtf(r);
	y = sqrtf(-2.0 * logf(r)/r);
	return fabsf(y * x);
}

float RandomNumber(void)
{
	return drand48();
}

void dbPick(TaskItem *parent)
{
	dbi_driver driver;
    dbi_result result;
	struct{
		dbi_result lastResult;
		dbi_conn conn;
	} locals;
	const char *Str;
	const char *prefix;
	CFStringRef input_str, output_str;
	char buf[32];
	string mode;
	string qStr;
	string single;
	unsigned int field;
	unsigned int pos;
	unsigned long Item;
	unsigned long long count;
	unsigned long long row;
	
	qStr = GetMetaData(parent->UID, "Query");
	if(qStr.length() < 1)
		return;
	
	// URL type un-escape (%nn) the query string.
	input_str = CFStringCreateWithCString(NULL, qStr.c_str(), kCFStringEncodingUTF8);
	output_str = CFURLCreateStringByReplacingPercentEscapesUsingEncoding(NULL, input_str, CFSTR(""), kCFStringEncodingUTF8);
	Str = CFStringGetCStringPtr(output_str, kCFStringEncodingUTF8);
	if(Str){
		qStr = string(Str);
		CFRelease(output_str);
		CFRelease(input_str);
	}else{
		CFRelease(output_str);
		CFRelease(input_str);
		return;
	}

	if((driver = dbi_driver_open(GetMetaData(0, "db_type").c_str())) == NULL)
		return;
	if((locals.conn = dbi_conn_open(driver)) == NULL)
		return;
		
	// set up database connection
	dbi_conn_set_option(locals.conn, "host", GetMetaData(0, "db_server").c_str());
    dbi_conn_set_option(locals.conn, "username", GetMetaData(0, "db_user").c_str());
    dbi_conn_set_option(locals.conn, "password", GetMetaData(0, "db_pw").c_str());
    dbi_conn_set_option(locals.conn, "dbname", GetMetaData(0, "db_name").c_str());
	// for sqlite only
	dbi_conn_set_option(locals.conn, "sqlite-dbdir", GetMetaData(0, "db_dir").c_str());
	prefix = GetMetaData(0, "db_prefix").c_str();

    if(dbi_conn_connect(locals.conn))
		return;
		
	dbi_conn_error_handler(locals.conn, HandleDBerror, (void *)("db query Pick "));	
	pthread_cleanup_push((void (*)(void *))dbPickCleanUp, (void *)&locals);

	field = 1;
	locals.lastResult = NULL;
	// parse query string for multiple queries, separated with ';' char
	single = NthField(qStr, ";", field);
	while(single.length() > 0){
		// perform the sql query function
		pthread_testcancel();
		// replace any special function macros
		dbMacroReplace(&single);
		result = dbi_conn_query(locals.conn, single.c_str());
		count = dbi_result_get_numrows(result);
		if(count > 0){
			if(locals.lastResult){
				dbi_result_free(locals.lastResult);
			}
			locals.lastResult = result;
		}else{
			dbi_result_free(result);
		}
		field++;
		single = NthField(qStr, ";", field);
	}
	
	if(locals.lastResult){
		count = dbi_result_get_numrows(locals.lastResult);
		mode = GetMetaData(parent->UID, "Mode");
		if(mode == "random"){
			row = (unsigned long long)(count * RandomNumber());
			if(dbi_result_seek_row(locals.lastResult, row)){ 
				Item = dbi_result_get_ulong(locals.lastResult, "ID");
				snprintf(buf, sizeof(buf), "%lu", Item);
				lMutex->readLock(true);
				pos = parent->UID;
                if(getListPos(&pos)){
                    AddItem(pos, "item:///"+string(buf));
                }
				lMutex->readUnlock();
			}
		}
		else if(mode == "weighted"){
			row = (unsigned long long)((count / 2) * GaussianNumber());
			if(dbi_result_seek_row(locals.lastResult, row)){ 
				Item = dbi_result_get_ulong(locals.lastResult, "ID");
				snprintf(buf, sizeof(buf), "%lu", Item);
				lMutex->readLock(true);
				pos = parent->UID;
                if(getListPos(&pos)){
                    AddItem(pos, "item:///"+string(buf));
                }
				lMutex->readUnlock();
			}
		}
		else if(mode == "first"){
			if(dbi_result_has_next_row(locals.lastResult)){
				if(dbi_result_next_row(locals.lastResult)) { 
					Item = dbi_result_get_ulong(locals.lastResult, "ID");
					snprintf(buf, sizeof(buf), "%lu", Item);
					lMutex->readLock(true);
					pos = parent->UID;
					if(getListPos(&pos)){
						AddItem(pos, "item:///"+string(buf));
					}
					lMutex->readUnlock();
				}
			}
		}
		else if(mode == "all"){
			while(dbi_result_has_next_row(locals.lastResult)){
				pthread_testcancel();
				if(dbi_result_next_row(locals.lastResult)) { 
					Item = dbi_result_get_ulong(locals.lastResult, "ID");
					snprintf(buf, sizeof(buf), "%lu", Item);
					lMutex->readLock(true);
					pos = parent->UID;
					if(getListPos(&pos)){
						AddItem(pos, "item:///"+string(buf));
					}
					lMutex->readUnlock();
				}
			}
		}
	}				
	// all done... clean up!
	pthread_cleanup_pop(1);
}

void dbPickCleanUp(void *pass)
{
	struct locals{
		dbi_result lastResult;
		dbi_conn conn;
	} *ptr;

	ptr = (struct locals *)pass;
	if(ptr->lastResult)
		dbi_result_free(ptr->lastResult);
	dbi_conn_close(ptr->conn);
}

void dbMacroReplace(string *query)
{
	ReplaceAll(query, "ThisLocationID()", GetMetaData(0, "db_loc"));
}