# HG changeset patch # User Daniele Nicolodi # Date 1323098406 -3600 # Node ID 91f21a0aab355a52065e8896df8fdf6dc59e419c # Parent ce3fbb7ebe71e839c5dbdb08efdb2cf6e1fd42b1 Update utils.jquery * * * Update utils.jmysql.getsinfo diff -r ce3fbb7ebe71 -r 91f21a0aab35 m-toolbox/classes/+utils/@jmysql/connect.m --- a/m-toolbox/classes/+utils/@jmysql/connect.m Mon Dec 05 16:20:06 2011 +0100 +++ b/m-toolbox/classes/+utils/@jmysql/connect.m Mon Dec 05 16:20:06 2011 +0100 @@ -11,55 +11,15 @@ % in the LTPDA user preferences. % % CALL: conn = connect(hostname) -% conn = connect(hostname, dbname) -% conn = connect(hostname, dbname, dbuser, dbpass) +% conn = connect(hostname, database) +% conn = connect(hostname, database, username, password) % % VERSION: $Id: connect.m,v 1.13 2011/04/01 08:36:49 hewitson Exp $ % %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% -function varargout = connect(varargin) - - dbuser = ''; - dbpass = ''; - dbhost = ''; - dbname = ''; +function conn = connect(varargin) + + conn = LTPDADatabaseConnectionManager().connect(varargin{:}); - if nargin == 1 - dbhost = varargin{1}; - elseif nargin == 2 - dbhost = varargin{1}; - dbname = varargin{2}; - elseif nargin == 3 - dbhost = varargin{1}; - dbname = varargin{2}; - elseif nargin == 4 - dbhost = varargin{1}; - dbname = varargin{2}; - dbuser = varargin{3}; - dbpass = varargin{4}; - elseif nargin == 5 - dbhost = varargin{1}; - dbname = varargin{2}; - dbuser = varargin{3}; - dbpass = varargin{4}; - end - - rm = LTPDARepositoryManager(); - conn = rm.findConnections(dbhost, dbname, dbuser, dbpass); - - if isempty(conn) - conn = rm.newConnection(dbhost, dbname, dbuser, dbpass); - end - - if ~isempty(dbpass) && isempty(char(conn.getPassword)) - conn.setPassword(dbpass) - end - - % If we have more than one matching connection, let the user choose. - if numel(conn) > 1 - conn = rm.manager.selectConnection([]); - end - - varargout{1} = conn(1); end diff -r ce3fbb7ebe71 -r 91f21a0aab35 m-toolbox/classes/+utils/@jmysql/dbquery.m --- a/m-toolbox/classes/+utils/@jmysql/dbquery.m Mon Dec 05 16:20:06 2011 +0100 +++ b/m-toolbox/classes/+utils/@jmysql/dbquery.m Mon Dec 05 16:20:06 2011 +0100 @@ -1,27 +1,25 @@ -% DBQUERY query an AO repository database. -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% -% -% DESCRIPTION: DBQUERY query an AO repository database. +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 such as that returned by -% utils.jmysql.connect(). -% query - a valid MySQL query string -% table - a table name +% +% conn - a database connection object implementing java.sql.Connection +% query - a valid MySQL query string +% table - a table name % % OUTPUTS: -% info - the returned 'info' structure contains the fields from -% each matching record. +% +% 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, '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"'); @@ -30,111 +28,65 @@ % % >> tables = utils.jmysql.dbquery(conn) % -% The 'tables' cell-array will contain a list of the tables in the database. +% 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. +% 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.'); + if ~isa(conn, 'java.sql.Connection') + error('### first argument should be a java.sql.Connection object'); end - if nargin == 1 - % get table list - info = getTableList(conn); - - elseif nargin == 2 - % execute query - info = simpleQuery(conn, varargin{2}); + 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 - elseif nargin == 3 - % query a table - table = varargin{2}; - query = varargin{3}; - info = runQuery(conn, table, query); - else - error('### Incorrect inputs.'); + 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 -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% -% Get table list -function info = getTableList(conn) +function info = getFieldList(conn, table) info = {}; - - % open a connection try - q = 'show tables'; - results = conn.query(q); - while results.next - info = [info {char(results.getString(1))}]; + rows = utils.mysql.execute(conn, sprintf('DESCRIBE `%s`', table)); + for kk = 1:size(rows, 1) + info = [info rows(kk,1)]; end - catch - error('### Failed to get table list.'); + catch ex + error('### failed to get field list. %s', ex.message); 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 +function info = runQuery(conn, table, query) 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 = {}; @@ -143,22 +95,10 @@ 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]); + q = sprintf('SELECT %s FROM %s WHERE %s', f(1:end-1), table, query); try - info = simpleQuery(conn, q); - catch - error('### Failed to query table.'); + info = utils.mysql.execute(conn, q); + catch ex + error('### failed to query table. %s', ex.message); end end - -function val = convertValue(val) - - switch class(val) - case 'java.sql.Timestamp' - val = char(val); - otherwise - end -end - - diff -r ce3fbb7ebe71 -r 91f21a0aab35 m-toolbox/classes/+utils/@jmysql/getsinfo.m --- a/m-toolbox/classes/+utils/@jmysql/getsinfo.m Mon Dec 05 16:20:06 2011 +0100 +++ b/m-toolbox/classes/+utils/@jmysql/getsinfo.m Mon Dec 05 16:20:06 2011 +0100 @@ -1,79 +1,56 @@ -% GETSINFO This function returns an sinfo object containing many useful information about an object retrieved from the repository -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% +function sinfo = getsinfo(conn, varargin) +% GETSINFO Retrieved objects submission info from the repository. +% +% CALL: % -% usage: sinfo = getsinfo(id, conn) +% sinfo = getsinfo(conn, id, id) % -% inputs: id = obj_id from objmeta table -% conn = utils.mysql.connect(server, dbname); +% INPUTS: +% +% id - object ID +% conn - repository connection implementing java.sql.Connection % -% outputs: sinfo cell array object containing info about object having the -% given obj_id on the repository. -% sinfo contains the following fields: -% - name -% - experiment_title -% - experiment_desc -% - analysis_desc -% - quantity -% - additional_authors -% - additional_comments -% - keywords and reference_ids +% OUTPUTS: +% +% sinfo - array of sinfo structures containing fields % -% A Monsky 05-02-2009 -% -% version: $Id: getsinfo.m,v 1.2 2011/03/29 13:40:16 hewitson Exp $ +% - name +% - experiment_title +% - experiment_desc +% - analysis_desc +% - quantity +% - additional_authors +% - additional_comments +% - keywords +% - reference_ids % -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% -function [varargout] = getsinfo(varargin) - - import utils.const.* - utils.helper.msg(msg.PROC3, 'running %s/%s', mfilename('class'), mfilename); - - % Collect input variable names - in_names = cell(size(varargin)); - for ii = 1:nargin,in_names{ii} = inputname(ii);end - - % Collect all ids and plists and connections - pl = utils.helper.collect_objects(varargin(:), 'plist'); - id = utils.helper.collect_objects(varargin(:), 'double'); - conn = utils.helper.collect_objects(varargin(:), 'mpipeline.repository.RepositoryConnection'); - - if isempty(conn) - error('### This method needs a java connection (mpipeline.repository.RepositoryConnection).'); - end - - if isempty(id) - error('### This method needs at least one object id.'); +% VERSION: $Id: getsinfo.m,v 1.2 2011/03/29 13:40:16 hewitson Exp $ + + if ~isa(conn, 'java.sql.Connection') + error('### first argument should be a java.sql.Connection object'); end - - if nargout == 0 - error('### lscov can not be used as a modifier method. Please give at least one output'); - end - - % combine plists - pl = combine(pl, plist()); - - % Algorithm - % Get complete experiment information for each input id + sinfo = []; - for ii=1:length(id) - qall = ['select name,experiment_title,experiment_desc,analysis_desc,quantity, '... - 'additional_authors,additional_comments,keywords,reference_ids FROM objmeta ' ... - sprintf('where objmeta.obj_id=%d',id(ii))]; - - infoall = utils.jmysql.dbquery(conn, qall); + for kk = 1:length(varargin) + + q = ['SELECT name, experiment_title, experiment_desc, analysis_desc, ' ... + 'quantity, additional_authors, additional_comments, keywords, ' ... + 'reference_ids FROM objmeta WHERE obj_id = ?']; + + info = utils.mysql.execute(conn, q, varargin{kk}); + s.conn = conn; - s.name = infoall{1}; - s.experiment_title = infoall{2}; - s.experiment_description = infoall{3}; - s.analysis_description = infoall{4}; - s.quantity = infoall{5}; - s.additional_authors = infoall{6}; - s.additional_comments = infoall{7}; - s.keywords = infoall{8}; - s.reference_ids = infoall{9}; + s.name = info{1}; + s.experiment_title = info{2}; + s.experiment_description = info{3}; + s.analysis_description = info{4}; + s.quantity = info{5}; + s.additional_authors = info{6}; + s.additional_comments = info{7}; + s.keywords = info{8}; + s.reference_ids = info{9}; + sinfo = [sinfo s]; end - - % Set output - varargout{1} = sinfo; + end diff -r ce3fbb7ebe71 -r 91f21a0aab35 m-toolbox/classes/+utils/@jmysql/query.m --- a/m-toolbox/classes/+utils/@jmysql/query.m Mon Dec 05 16:20:06 2011 +0100 +++ b/m-toolbox/classes/+utils/@jmysql/query.m Mon Dec 05 16:20:06 2011 +0100 @@ -1,70 +1,44 @@ -% QUERY query an LTPDA repository. -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% +function varargout = query(conn, query, varargin) +% QUERY Query an LTPDA repository. % -% DESCRIPTION: QUERY query an LTPDA repository. +% DEPRECATED! Use utils.mysql.execute instead! % -% CALL: results = query(conn, q) -% [results, col_names] = query(conn, q) +% CALL: [results] = query(conn, q, varargin) +% [results, cnames] = query(conn, q, varargin) % % INPUTS: -% conn - an mpipeline.repository.RepositoryConnection object, -% as is returned by utils.jmysql.connect -% q - a valid MySQL query string +% +% conn - database connection object implementing java.sql.Connection +% query - a valid SQL query +% varargin - optional query parameters % % OUTPUTS: -% results - a cell-array of the results -% col_names - a cell-array of the column names in the query -% -% VERSION: $Id: query.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $ % -% HISTORY: 24-05-2007 M Hewitson -% Creation +% results - a cell-array of the results +% cnames - a cell-array of the column names in the query % -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% - +% VERSION: $Id: query.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $ +% -% (*) the expiry time for MySQL logins is a property of the -% LTPDA Toolbox and can be set in the LTPDA Preferences. - - -function varargout = query(varargin) + warning('!!! deprecated. use utils.mysql.execute instead'); - prefs = getappdata(0, 'LTPDApreferences'); - - if ~isa(varargin{1}, 'mpipeline.repository.RepositoryConnection') - error('### The first argument should be a RepositoryConnection'); + if ~isa(conn, 'java.sql.Connection') + error('### first argument should be a java.sql.Connection object'); end - if ~ischar(varargin{2}) - error('### The second argument should be a query string.'); + stmt = conn.prepareStatement(query); + for kk = 1:numel(varargin) + stmt.setObject(kk, varargin{kk}); end - % Inputs - conn = varargin{1}; - q = varargin{2}; - - % Outputs - results = {}; - colnames = {}; + % execute query + resultSet = stmt.executeQuery(); - % Check connection - if ~conn.isConnected - conn.openConnection - end - - if ~conn.isConnected - conn.display; - error('### Failed to open connection'); - return - end - - resultSet = conn.query(q); - - % Set outputs + % set outputs if nargout > 0 varargout{1} = resultSet; if nargout == 2 - % Get column names from the meta data + % get column names from the meta data rsm = resultSet.getMetaData; Nc = rsm.getColumnCount; colnames = cell(1,Nc); diff -r ce3fbb7ebe71 -r 91f21a0aab35 m-toolbox/classes/+utils/@jmysql/resultsToCell.m --- a/m-toolbox/classes/+utils/@jmysql/resultsToCell.m Mon Dec 05 16:20:06 2011 +0100 +++ b/m-toolbox/classes/+utils/@jmysql/resultsToCell.m Mon Dec 05 16:20:06 2011 +0100 @@ -1,58 +1,67 @@ -% RESULTSTOCELL converts a java sql ResultSet to a cell-array -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% +function varargout = resultsToCell(resultSet) +% RESULTSTOCELL Converts a java.sql.ResultSet to a cell array % -% DESCRIPTION: RESULTSTOCELL converts a java sql ResultSet to a cell-array. +% DEPRECATED! Use utils.mysql.execute instead! % -% CALL: [results, colnames] = utils.jmysql.resultsToCell(resultSet); +% CALL: +% +% [results, colnames] = utils.jmysql.resultsToCell(rs); % % INPUTS: -% resultSet - a result set like that returned from -% utils.jmysql.query +% +% rs - a java.sql.ResultSet like that returned from utils.jmysql.query % % OUTPUTS: -% results - a cell array of results -% col_names - a cell array of column names % +% results - a cell array of results +% colnames - a cell array of column names % -% VERSION: $Id: resultsToCell.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $ +% VERSION: $Id: resultsToCell.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $ % -% HISTORY: 24-05-2007 M Hewitson -% Creation -% -%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% + + warning('!!! deprecated. use utils.mysql.execute instead'); + + if ~isa(resultSet, 'java.sql.ResultSet') + error('### first argument should be a java.sql.ResultSet'); + end + % get results into a cell array + md = rs.getMetaData(); + nc = md.getColumnCount(); + row = 1; + while rs.next() + for kk = 1:nc + % convert to matlab objects + rows{row, kk} = java2matlab(rs.getObject(kk)); + end + row = row + 1; + end + + % get column names + names = cell(1, nc); + for kk = 1:nc + names{kk} = char(md.getColumnName(kk)); + end + + % assign output + switch nargout + case 0 + varargout{1} = rows; + case 1 + varargout{1} = rows; + case 2 + varargout{1} = names; + varargout{2} = rows; + otherwise + error('### too many output arguments'); + end +end -function varargout = resultsToCell(varargin) - - prefs = getappdata(0, 'LTPDApreferences'); - - if ~isa(varargin{1}, 'java.sql.ResultSet') - error('### The first argument should be a ResultSet'); +function val = java2matlab(val) + % matlab converts all base types. just add a conversion for datetime columns + switch class(val) + case 'java.sql.Timestamp' + val = time(plist('time', char(val), 'timezone', 'UTC')); end - - % Inputs - resultSet = varargin{1}; - - os = mpipeline.repository.RepositoryConnection.resultSetToObjectArray(resultSet); - disp(sprintf('*** converted result set: [%dx%d]', numel(os), numel(os(1)))); - - % Set outputs - if nargout > 0 - varargout{1} = cell(os); - if nargout == 2 - % Get column names from the meta data - rsm = resultSet.getMetaData; - Nc = rsm.getColumnCount; - colnames = cell(1,Nc); - for kk=1:Nc - colnames{kk} = char(rsm.getColumnName(kk)); - end - - varargout{2} = colnames; - end - end - - end -