% DBQUERY query an AO repository database.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%
% DESCRIPTION: DBQUERY query an AO repository database.
%
% CALL:        info = dbquery(conn);
%              info = dbquery(conn, query);
%              info = dbquery(conn, table, query);
%
% INPUTS:
%           conn   - a database connection object such as that returned by
%                    utils.jmysql.connect().
%           query  - a valid MySQL query string
%           table  - a table name
%
% OUTPUTS:
%           info - the returned 'info' structure contains the fields from
%                  each matching record.
%
% EXAMPLES:
%
%   >> info  = utils.jmysql.dbquery(conn, 'select * from objmeta where id>1000 and id<2000');
%   >> info  = utils.jmysql.dbquery(conn, 'ao',           'id>1000 and id<2000');
%   >> info  = utils.jmysql.dbquery(conn, 'objmeta',      'name like "x12"');
%   >> info  = utils.jmysql.dbquery(conn, 'users',        'username="aouser"');
%   >> info  = utils.jmysql.dbquery(conn, 'collections',  'id=3');
%   >> info  = utils.jmysql.dbquery(conn, 'collections',  'obj_ids="1,2"');
%   >> info  = utils.jmysql.dbquery(conn, 'transactions', 'user_id=3');
%   >> info  = utils.jmysql.dbquery(conn, 'transactions', 'obj_id=56');
%
%   >> tables  = utils.jmysql.dbquery(conn)
%
%   The 'tables' cell-array will contain a list of the tables in the database.
%
%   >> info  = utils.jmysql.dbquery(conn, query)
%
%   The 'info' cell-array will contain the results from the SQL query.
%
% VERSION:     $Id: dbquery.m,v 1.2 2010/01/19 20:55:30 ingo Exp $
%
% HISTORY:     10-05-2007 M Hewitson
%                 Creation
%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

function varargout = dbquery(varargin)

  conn = varargin{1};
  if ~isa(conn, 'mpipeline.repository.RepositoryConnection')
    error('### First input must be a database connection object.');
  end

  if nargin == 1
    % get table list
    info = getTableList(conn);

  elseif nargin == 2
    % execute query
    info = simpleQuery(conn, varargin{2});

  elseif nargin == 3
    % query a table
    table    = varargin{2};
    query    = varargin{3};
    info = runQuery(conn, table, query);
  else
    error('### Incorrect inputs.');
  end

  varargout{1} = info;
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 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
    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
    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 ME
    disp(ME.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
    error('### Failed to query table.');
  end
end

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