Mercurial > hg > ltpda

comparison m-toolbox/classes/@LTPDARepositoryManager/executeQuery.m @ 0:f0afece42f48

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Import.
author Daniele Nicolodi <nicolodi@science.unitn.it>
date Wed, 23 Nov 2011 19:22:13 +0100
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f0afece42f48
1 % EXECUTEQUERY query a LTPDA repository database.
2 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
3 %
4 % DESCRIPTION: EXECUTEQUERY query a LTPDA repository database.
5 %
6 % CALL: info = executeQuery(pl);
7 % info = executeQuery(query);
8 % info = executeQuery(table, query);
9 % info = executeQuery(query, hostname, database, username);
10 % info = executeQuery(table, query, hostname, database, username);
11 %
12 % INPUTS: pl - a PLIST object.
13 % query - a valid MySQL query string
14 % table - a table name
15 % hostname - hostname of the LTPDA database
16 % database - LTPDA database
17 % username - user name
18 %
19 % OUTPUTS: info - the returned 'info' structure contains the fields
20 % from each matching record.
21 %
22 % REMARK: If you don't specify a hostname, database and user name then
23 % do this method the following:
24 % The repository manager have the following numer of connections:
25 % 0: A GUI will open for creating a new connection.
26 % 1: The method will use this connection.
27 % >1: A GUI will open where you have to select a connection
28 %
29 % EXAMPLES:
30 %
31 % >> info = LTPDARepositoryManager.executeQuery('select * from objmeta where id>1000 and id<2000');
32 % >> info = LTPDARepositoryManager.executeQuery('ao', 'id>1000 and id<2000');
33 % >> info = LTPDARepositoryManager.executeQuery('objmeta', 'name like "x12"');
34 % >> info = LTPDARepositoryManager.executeQuery('users', 'username="aouser"');
35 % >> info = LTPDARepositoryManager.executeQuery('collections', 'id=3');
36 % >> info = LTPDARepositoryManager.executeQuery('collections', 'obj_id="1,2"');
37 % >> info = LTPDARepositoryManager.executeQuery('transactions', 'user_id=3');
38 % >> info = LTPDARepositoryManager.executeQuery('transactions', 'obj_id=56');
39 %
40 % The 'info' cell-array will contain the results from the SQL query.
41 %
42 % <a href="matlab:web(LTPDARepositoryManager.getInfo('LTPDARepositoryManager.executeQuery').tohtml, '-helpbrowser')">Parameters Description</a>
43 %
44 % VERSION: $Id: executeQuery.m,v 1.4 2011/04/08 08:56:35 hewitson Exp $
45 %
46 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
47
48 function varargout = executeQuery(varargin)
49
50 % Check if this is a call for parameters
51 if utils.helper.isinfocall(varargin{:})
52 varargout{1} = getInfo(varargin{3});
53 return
54 end
55
56 import utils.const.*
57 utils.helper.msg(msg.PROC3, 'running %s/%s', mfilename('class'), mfilename);
58
59 % Get the repository manager - there should only be one!
60 rm = LTPDARepositoryManager();
61
62 % Collect all plists
63 [pl, invars, rest] = utils.helper.collect_objects(varargin(:), 'plist');
64
65 pl = combine(pl, getDefaultPlist);
66 query = pl.find('query');
67 table = pl.find('table');
68 hostname = pl.find('hostname');
69 database = pl.find('database');
70 username = pl.find('username');
71
72 % Check through 'rest'
73 if (numel(rest) == 1) && (ischar(rest{1}))
74 query = rest{1};
75 elseif numel(rest) == 2
76 table = rest{1};
77 query = rest{2};
78 elseif numel(rest) == 4
79 query = rest{1};
80 hostname = rest{2};
81 database = rest{3};
82 username = rest{4};
83 elseif numel(rest) == 5
84 table = rest{1};
85 query = rest{2};
86 hostname = rest{3};
87 database = rest{4};
88 username = rest{5};
89 end
90
91 % Get connection
92 conn = rm.findConnections(hostname, database, username);
93 if numel(conn) == 0
94 conn = rm.newConnection(hostname, database, username);
95 elseif numel(conn) > 1
96 conn = rm.manager.selectConnection([]);
97 end
98
99 if isempty(conn)
100 error('### It is necessary to create or select a connection.');
101 end
102
103 % open connection
104 conn.openConnection();
105 if ~conn.isConnected()
106 error('### Can not open the connection.');
107 end
108
109 % make sure
110 try
111 mustUnlock = ~conn.isLocked();
112
113 % Lock connection
114 conn.setLocked(true);
115
116 if isempty(table) && ~isempty(query)
117 % execute query
118 info = simpleQuery(conn, query);
119 elseif ~isempty(query) && ~isempty(table)
120 % query a table
121 info = runQuery(conn, table, query);
122 else
123 error('### Incorrect inputs. Please specify at least a query and/or a table.');
124 end
125 catch Exception
126 if (mustUnlock)
127 conn.setLocked(false);
128 end
129 error(Exception.message);
130 end
131
132 if (mustUnlock)
133 conn.setLocked(false);
134 end
135
136 varargout{1} = info;
137 end
138
139 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
140 % Local Functions %
141 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
142
143 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
144 % Get table list
145 function info = getTableList(conn)
146
147 info = {};
148
149 % open a connection
150 try
151 q = 'show tables';
152 results = conn.query(q);
153 while results.next
154 info = [info {char(results.getString(1))}];
155 end
156 catch Exception
157 disp(Exception.message);
158 error('### Failed to get table list.');
159 end
160 end
161
162 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
163 % Get field list
164 function info = getFieldList(conn, table)
165
166 info = {};
167 try
168 q = ['describe ' table];
169 results = conn.query(q);
170 while results.next
171 info = [info {char(results.getObject(1))}];
172 end
173 catch Exception
174 disp(Exception.message);
175 error('### Failed to get field list.');
176 end
177 end
178
179 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
180 % Get field list
181 function info = simpleQuery(conn, q)
182
183 % open a connection
184 info = {};
185 try
186 results = conn.query(q);
187 mt = results.getMetaData;
188 Ncols = mt.getColumnCount;
189 row = 1;
190 while results.next
191 for kk=1:Ncols
192 info{row,kk} = convertValue(results.getObject(kk));
193 end
194 row = row + 1;
195 end
196
197 catch Exception
198 disp(Exception.message);
199 error('### Failed to execute query.');
200 end
201 end
202
203 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
204 % Run a query
205 function info = runQuery(conn, table, query)
206
207 % Run query
208 info = {};
209
210 fields = getFieldList(conn, table);
211 f = '';
212 fs = {};
213 for j=1:length(fields)
214 % special cases
215 f = [f fields{j} ',' ];
216 fs = [fs fields(j)];
217 end
218 q = sprintf('select %s from %s where %s', f(1:end-1), table, query);
219 disp(['** QUERY: ' q]);
220 try
221 info = simpleQuery(conn, q);
222 catch Exception
223 disp(Exception.message);
224 error('### Failed to query table.');
225 end
226 end
227
228 function val = convertValue(val)
229
230 switch class(val)
231 case 'java.sql.Timestamp'
232 val = char(val);
233 otherwise
234 end
235 end
236
237
238 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
239 %
240 % FUNCTION: getInfo
241 %
242 % DESCRIPTION: Returns the method-info object
243 %
244 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
245 function ii = getInfo(varargin)
246 if nargin == 1 && strcmpi(varargin{1}, 'None')
247 sets = {};
248 pl = [];
249 else
250 sets = {'Default'};
251 pl = getDefaultPlist;
252 end
253 % Build info object
254 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);
255 end
256
257 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
258 %
259 % FUNCTION: getDefaultPlist
260 %
261 % DESCRIPTION: Returns the default PLIST
262 %
263 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
264 function pl = getDefaultPlist()
265
266 % Initialise plist
267 pl = plist();
268
269 % query
270 p = param({'query', 'A valid MySQL query string.'}, paramValue.EMPTY_STRING);
271 pl.append(p);
272
273 % table
274 p = param({'table', 'A table name.'}, paramValue.EMPTY_STRING);
275 pl.append(p);
276
277 % hostname
278 p = param({'hostname', 'The hostname of the repository to connect to.'}, paramValue.EMPTY_STRING);
279 pl.append(p);
280
281 % database
282 p = param({'database', 'The database on the repository.'}, paramValue.EMPTY_STRING);
283 pl.append(p);
284
285 % username
286 p = param({'username', 'The username to connect with.'}, paramValue.EMPTY_STRING);
287 pl.append(p);
288
289 end