Mercurial > hg > ltpda

comparison m-toolbox/classes/+utils/@mysql/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
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.mysql.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 = dbquery(conn, 'select * from objmeta where id>1000 and id<2000');
23 % >> info = dbquery(conn, 'ao', 'id>1000 and id<2000');
24 % >> info = dbquery(conn, 'objmeta', 'name like "x12"');
25 % >> info = dbquery(conn, 'users', 'username="aouser"');
26 % >> info = dbquery(conn, 'collections', 'id=3');
27 % >> info = dbquery(conn, 'collections', 'obj_ids="1,2"');
28 % >> info = dbquery(conn, 'transactions', 'user_id=3');
29 % >> info = dbquery(conn, 'transactions', 'obj_id=56');
30 %
31 % >> info = dbquery(conn)
32 %
33 % The 'info' structure will contain a list of the tables in the database.
34 %
35 % >> info = dbquery(conn, query)
36 %
37 % The 'info' structure will contain a list of records resulting from the SQL query.
38 %
39 % VERSION: $Id: dbquery.m,v 1.2 2010/01/22 12:46:08 ingo Exp $
40 %
41 % HISTORY: 10-05-2007 M Hewitson
42 % Creation
43 %
44 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
45
46 function varargout = dbquery(varargin)
47
48 error('### Obsolete as of LTPDA version 2.2. Replaced my the utils class jmysql (utils.jmysql.%s)', mfilename());
49 conn = varargin{1};
50 if ~isa(conn, 'database')
51 error('### First input must be a database connection object.');
52 end
53
54 if nargin == 1
55 % get table list
56 info = getTableList(conn);
57
58 elseif nargin == 2
59 % execute query
60 info = simpleQuery(conn, varargin{2});
61
62 elseif nargin == 3
63 % query a table
64 table = varargin{2};
65 query = varargin{3};
66 info = runQuery(conn, table, query);
67 else
68 error('### Incorrect inputs.');
69 end
70
71 varargout{1} = info;
72 end
73
74 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
75 % Get table list
76 function info = getTableList(conn)
77
78 % open a connection
79 try
80 curs = exec(conn, 'show tables');
81 curs = fetch(curs);
82 info = curs.Data;
83 close(curs);
84 catch
85 error('### Failed to get table list. Server returned: %s', curs.Message);
86 end
87 end
88
89 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
90 % Get field list
91 function info = getFieldList(conn, table)
92
93 try
94 curs = exec(conn, sprintf('describe %s', table));
95 curs = fetch(curs);
96 info = curs.Data;
97 close(curs);
98 catch
99 error('### Failed to get field list. Server returned: %s', curs.Message);
100 end
101 end
102
103 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
104 % Get field list
105 function info = simpleQuery(conn, q)
106
107 % open a connection
108
109 try
110 curs = exec(conn, sprintf('%s', q));
111 curs = fetch(curs);
112 info = curs.Data;
113 close(curs);
114 catch
115 error('### Failed to execute query. Server returned: %s', curs.Message);
116 end
117 end
118
119 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
120 % Run a query
121 function info = runQuery(conn, table, query)
122
123 % Run query
124 info = [];
125
126 fieldlist = getFieldList(conn, table);
127 fields = fieldlist(:,1);
128 f = '';
129 fs = {};
130 for j=1:length(fields)
131 % special cases
132 f = [f fields{j} ',' ];
133 fs = [fs fields(j)];
134 end
135 q = sprintf('select %s from %s where %s', f(1:end-1), table, query);
136 disp(['** QUERY: ' q]);
137 try
138 curs = exec(conn, q);
139 curs = fetch(curs);
140 info = curs.Data;
141 close(curs);
142 catch
143 error('### Failed to query table. Server returned: %s', curs.Message);
144 end
145 end
146
147