Mercurial > hg > ltpda

comparison m-toolbox/classes/+utils/@jmysql/dbquery.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 91f21a0aab35
comparison
equal deleted inserted replaced
-1:000000000000 0:f0afece42f48
1 % DBQUERY query an AO repository database.
2 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
3 %
4 % DESCRIPTION: DBQUERY query an AO repository database.
5 %
6 % CALL: info = dbquery(conn);
7 % info = dbquery(conn, query);
8 % info = dbquery(conn, table, query);
9 %
10 % INPUTS:
11 % conn - a database connection object such as that returned by
12 % utils.jmysql.connect().
13 % query - a valid MySQL query string
14 % table - a table name
15 %
16 % OUTPUTS:
17 % info - the returned 'info' structure contains the fields from
18 % each matching record.
19 %
20 % EXAMPLES:
21 %
22 % >> info = utils.jmysql.dbquery(conn, 'select * from objmeta where id>1000 and id<2000');
23 % >> info = utils.jmysql.dbquery(conn, 'ao', 'id>1000 and id<2000');
24 % >> info = utils.jmysql.dbquery(conn, 'objmeta', 'name like "x12"');
25 % >> info = utils.jmysql.dbquery(conn, 'users', 'username="aouser"');
26 % >> info = utils.jmysql.dbquery(conn, 'collections', 'id=3');
27 % >> info = utils.jmysql.dbquery(conn, 'collections', 'obj_ids="1,2"');
28 % >> info = utils.jmysql.dbquery(conn, 'transactions', 'user_id=3');
29 % >> info = utils.jmysql.dbquery(conn, 'transactions', 'obj_id=56');
30 %
31 % >> tables = utils.jmysql.dbquery(conn)
32 %
33 % The 'tables' cell-array will contain a list of the tables in the database.
34 %
35 % >> info = utils.jmysql.dbquery(conn, query)
36 %
37 % The 'info' cell-array will contain the results from the SQL query.
38 %
39 % VERSION: $Id: dbquery.m,v 1.2 2010/01/19 20:55:30 ingo Exp $
40 %
41 % HISTORY: 10-05-2007 M Hewitson
42 % Creation
43 %
44 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
45
46 function varargout = dbquery(varargin)
47
48 conn = varargin{1};
49 if ~isa(conn, 'mpipeline.repository.RepositoryConnection')
50 error('### First input must be a database connection object.');
51 end
52
53 if nargin == 1
54 % get table list
55 info = getTableList(conn);
56
57 elseif nargin == 2
58 % execute query
59 info = simpleQuery(conn, varargin{2});
60
61 elseif nargin == 3
62 % query a table
63 table = varargin{2};
64 query = varargin{3};
65 info = runQuery(conn, table, query);
66 else
67 error('### Incorrect inputs.');
68 end
69
70 varargout{1} = info;
71 end
72
73 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
74 % Get table list
75 function info = getTableList(conn)
76
77 info = {};
78
79 % open a connection
80 try
81 q = 'show tables';
82 results = conn.query(q);
83 while results.next
84 info = [info {char(results.getString(1))}];
85 end
86 catch
87 error('### Failed to get table list.');
88 end
89 end
90
91 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
92 % Get field list
93 function info = getFieldList(conn, table)
94
95 info = {};
96 try
97 q = ['describe ' table];
98 results = conn.query(q);
99 while results.next
100 info = [info {char(results.getObject(1))}];
101 end
102 catch
103 error('### Failed to get field list.');
104 end
105 end
106
107 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
108 % Get field list
109 function info = simpleQuery(conn, q)
110
111 % open a connection
112 info = {};
113 try
114 results = conn.query(q);
115 mt = results.getMetaData;
116 Ncols = mt.getColumnCount;
117 row = 1;
118 while results.next
119 for kk=1:Ncols
120 info{row,kk} = convertValue(results.getObject(kk));
121 end
122 row = row + 1;
123 end
124
125 catch ME
126 disp(ME.message)
127 error('### Failed to execute query.');
128 end
129 end
130
131 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
132 % Run a query
133 function info = runQuery(conn, table, query)
134
135 % Run query
136 info = {};
137
138 fields = getFieldList(conn, table);
139 f = '';
140 fs = {};
141 for j=1:length(fields)
142 % special cases
143 f = [f fields{j} ',' ];
144 fs = [fs fields(j)];
145 end
146 q = sprintf('select %s from %s where %s', f(1:end-1), table, query);
147 disp(['** QUERY: ' q]);
148 try
149 info = simpleQuery(conn, q);
150 catch
151 error('### Failed to query table.');
152 end
153 end
154
155 function val = convertValue(val)
156
157 switch class(val)
158 case 'java.sql.Timestamp'
159 val = char(val);
160 otherwise
161 end
162 end
163
164