--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/m-toolbox/classes/@LTPDARepositoryManager/executeQuery.m	Wed Nov 23 19:22:13 2011 +0100
@@ -0,0 +1,289 @@
+% 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