function varargout = dbquery(conn, varargin)
% 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 implementing java.sql.Connection
%   query  - a valid MySQL query string
%   table  - a table name
%
% OUTPUTS:
%
%     info - structure containing 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 $
%

  if ~isa(conn, 'java.sql.Connection')
    error('### first argument should be a java.sql.Connection object');
  end

  switch nargin
    case 1
      % get table list
      try
        info = utils.mysql.execute(conn, 'SHOW TABLES');
      catch ex
        error('### failed to get table list. %s', ex.message);
      end

    case 2
      % execute query
      try
        info = utils.mysql.execute(conn, varargin{1});
      catch ex
        error('### failed to execute query. %s', ex.message);
      end

    case 3
      % query a table
      table = varargin{1};
      query = varargin{2};
      info = runQuery(conn, table, query);

    otherwise
      error('### incorrect inputs');
  end

  varargout{1} = info;
end


function info = getFieldList(conn, table)
  info = {};
  try
    rows = utils.mysql.execute(conn, sprintf('DESCRIBE `%s`', table));
    for kk = 1:size(rows, 1)
      info = [info rows(kk,1)];
    end
  catch ex
    error('### failed to get field list. %s', ex.message);
  end
end


function info = runQuery(conn, table, 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);
  try
    info = utils.mysql.execute(conn, q);
  catch ex
    error('### failed to query table. %s', ex.message);
  end
end