% EXECUTEQUERY query a LTPDA repository database.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%
% DESCRIPTION: EXECUTEQUERY query a LTPDA repository database.
%
% CALL:        info = executeQuery(pl);
%              info = executeQuery(query);
%              info = executeQuery(table, query);
%              info = executeQuery(query, hostname, database, username);
%              info = executeQuery(table, query, hostname, database, username);
%
% INPUTS:      pl       - a PLIST object.
%              query    - a valid MySQL query string
%              table    - a table name
%              hostname - hostname of the LTPDA database
%              database - LTPDA database
%              username - user name
%
% OUTPUTS:     info - the returned 'info' structure contains the fields
%              from each matching record.
%
% REMARK:      If you don't specify a hostname, database and user name then
%              do this method the following:
%              The repository manager have the following numer of connections:
%                 0:  A GUI will open for creating a new connection.
%                 1:  The method will use this connection.
%                >1:  A GUI will open where you have to select a connection
%
% EXAMPLES:
%
%   >> info  = LTPDARepositoryManager.executeQuery('select * from objmeta where id>1000 and id<2000');
%   >> info  = LTPDARepositoryManager.executeQuery('ao',           'id>1000 and id<2000');
%   >> info  = LTPDARepositoryManager.executeQuery('objmeta',      'name like "x12"');
%   >> info  = LTPDARepositoryManager.executeQuery('users',        'username="aouser"');
%   >> info  = LTPDARepositoryManager.executeQuery('collections',  'id=3');
%   >> info  = LTPDARepositoryManager.executeQuery('collections',  'obj_id="1,2"');
%   >> info  = LTPDARepositoryManager.executeQuery('transactions', 'user_id=3');
%   >> info  = LTPDARepositoryManager.executeQuery('transactions', 'obj_id=56');
%
%   The 'info' cell-array will contain the results from the SQL query.
%
% <a href="matlab:web(LTPDARepositoryManager.getInfo('LTPDARepositoryManager.executeQuery').tohtml, '-helpbrowser')">Parameters Description</a>
%
% VERSION:     $Id: executeQuery.m,v 1.4 2011/04/08 08:56:35 hewitson Exp $
%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

function varargout = executeQuery(varargin)
  
  % Check if this is a call for parameters
  if utils.helper.isinfocall(varargin{:})
    varargout{1} = getInfo(varargin{3});
    return
  end
  
  import utils.const.*
  utils.helper.msg(msg.PROC3, 'running %s/%s', mfilename('class'), mfilename);
  
  % Get the repository manager - there should only be one!
  rm = LTPDARepositoryManager();
  
  % Collect all plists
  [pl, invars, rest] = utils.helper.collect_objects(varargin(:), 'plist');
  
  pl = combine(pl, getDefaultPlist);
  query    = pl.find('query');
  table    = pl.find('table');
  hostname = pl.find('hostname');
  database = pl.find('database');
  username = pl.find('username');
  
  % Check through 'rest'
  if (numel(rest) == 1) && (ischar(rest{1}))
    query = rest{1};
  elseif numel(rest) == 2
    table = rest{1};
    query = rest{2};
  elseif numel(rest) == 4
    query = rest{1};
    hostname = rest{2};
    database = rest{3};
    username = rest{4};
  elseif numel(rest) == 5
    table    = rest{1};
    query    = rest{2};
    hostname = rest{3};
    database = rest{4};
    username = rest{5};
  end
  
  % Get connection
  conn = rm.findConnections(hostname, database, username);
  if numel(conn) == 0
    conn = rm.newConnection(hostname, database, username);
  elseif numel(conn) > 1
    conn = rm.manager.selectConnection([]);
  end
  
  if isempty(conn)
    error('### It is necessary to create or select a connection.');
  end
  
  % open connection
  conn.openConnection();
  if ~conn.isConnected()
    error('### Can not open the connection.');
  end
  
  % make sure 
  try
    mustUnlock = ~conn.isLocked();
    
    % Lock connection
    conn.setLocked(true);
    
    if isempty(table) && ~isempty(query)
      % execute query
      info = simpleQuery(conn, query);
    elseif ~isempty(query) && ~isempty(table)
      % query a table
      info = runQuery(conn, table, query);
    else
      error('### Incorrect inputs. Please specify at least a query and/or a table.');
    end
  catch Exception
    if (mustUnlock)
      conn.setLocked(false);
    end
    error(Exception.message);
  end
  
  if (mustUnlock)
    conn.setLocked(false);
  end
  
  varargout{1} = info;
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%                               Local Functions                               %
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Get table list
function info = getTableList(conn)
  
  info = {};
  
  % open a connection
  try
    q = 'show tables';
    results = conn.query(q);
    while results.next
      info = [info {char(results.getString(1))}];
    end
  catch Exception
    disp(Exception.message);
    error('### Failed to get table list.');
  end
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Get field list
function info = getFieldList(conn, table)
  
  info = {};
  try
    q = ['describe ' table];
    results = conn.query(q);
    while results.next
      info = [info {char(results.getObject(1))}];
    end
  catch Exception
    disp(Exception.message);
    error('### Failed to get field list.');
  end
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Get field list
function info = simpleQuery(conn, q)
  
  % open a connection
  info = {};
  try
    results = conn.query(q);
    mt = results.getMetaData;
    Ncols = mt.getColumnCount;
    row = 1;
    while results.next
      for kk=1:Ncols
        info{row,kk} = convertValue(results.getObject(kk));
      end
      row = row + 1;
    end
    
  catch Exception
    disp(Exception.message);
    error('### Failed to execute query.');
  end
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Run a query
function info = runQuery(conn, table, query)
  
  % Run query
  info = {};
  
  fields = getFieldList(conn, table);
  f = '';
  fs = {};
  for j=1:length(fields)
    % special cases
    f  = [f fields{j} ',' ];
    fs = [fs fields(j)];
  end
  q = sprintf('select %s  from %s where %s', f(1:end-1), table, query);
  disp(['** QUERY: ' q]);
  try
    info = simpleQuery(conn, q);
  catch Exception
    disp(Exception.message);
    error('### Failed to query table.');
  end
end

function val = convertValue(val)
  
  switch class(val)
    case 'java.sql.Timestamp'
      val = char(val);
    otherwise
  end
end


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%
% FUNCTION:    getInfo
%
% DESCRIPTION: Returns the method-info object
%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function ii = getInfo(varargin)
  if nargin == 1 && strcmpi(varargin{1}, 'None')
    sets = {};
    pl   = [];
  else
    sets = {'Default'};
    pl   = getDefaultPlist;
  end
  % Build info object
  ii = minfo(mfilename, 'LTPDARepositoryManager', 'ltpda', utils.const.categories.helper, '$Id: executeQuery.m,v 1.4 2011/04/08 08:56:35 hewitson Exp $', sets, pl);
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%
% FUNCTION:    getDefaultPlist
%
% DESCRIPTION: Returns the default PLIST
%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function pl = getDefaultPlist()
  
  % Initialise plist
  pl = plist();
  
  % query
  p = param({'query', 'A valid MySQL query string.'}, paramValue.EMPTY_STRING);
  pl.append(p);
  
  % table
  p = param({'table', 'A table name.'}, paramValue.EMPTY_STRING);
  pl.append(p);
  
  % hostname
  p = param({'hostname', 'The hostname of the repository to connect to.'}, paramValue.EMPTY_STRING);
  pl.append(p);
  
  % database
  p = param({'database', 'The database on the repository.'}, paramValue.EMPTY_STRING);
  pl.append(p);
  
  % username
  p = param({'username', 'The username to connect with.'}, paramValue.EMPTY_STRING);
  pl.append(p);
  
end