Mercurial > hg > ltpda
view m-toolbox/classes/@LTPDARepositoryManager/executeQuery.m @ 25:79dc7091dbbc database-connection-manager
Update tests
author | Daniele Nicolodi <nicolodi@science.unitn.it> |
---|---|
date | Mon, 05 Dec 2011 16:20:06 +0100 |
parents | f0afece42f48 |
children |
line wrap: on
line source
% 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