Mercurial > hg > ltpda

annotate m-toolbox/classes/+utils/@jmysql/dbquery.m @ 24:056f8e1e995e database-connection-manager

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Properly record history in fromRepository constructors
author Daniele Nicolodi <nicolodi@science.unitn.it>
date Mon, 05 Dec 2011 16:20:06 +0100
parents 91f21a0aab35
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
1 function varargout = dbquery(conn, varargin)
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
2 % DBQUERY Query an AO repository database
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
3 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
4 % CALL: info = dbquery(conn);
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
5 % info = dbquery(conn, query);
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
6 % info = dbquery(conn, table, query);
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
7 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
8 % INPUTS:
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
9 %
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
10 % conn - a database connection object implementing java.sql.Connection
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
11 % query - a valid MySQL query string
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
12 % table - a table name
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
13 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
14 % OUTPUTS:
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
15 %
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
16 % info - structure containing fields from each matching record
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
17 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
18 % EXAMPLES:
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
19 %
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
20 % >> info = utils.jmysql.dbquery(conn, 'SELECT * FROM objmeta WHERE id>1000 AND id<2000');
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
21 % >> info = utils.jmysql.dbquery(conn, 'ao', 'id>1000 AND id<2000');
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
22 % >> info = utils.jmysql.dbquery(conn, 'objmeta', 'name LIKE "x12"');
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
23 % >> info = utils.jmysql.dbquery(conn, 'users', 'username="aouser"');
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
24 % >> info = utils.jmysql.dbquery(conn, 'collections', 'id=3');
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
25 % >> info = utils.jmysql.dbquery(conn, 'collections', 'obj_ids="1,2"');
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
26 % >> info = utils.jmysql.dbquery(conn, 'transactions', 'user_id=3');
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
27 % >> info = utils.jmysql.dbquery(conn, 'transactions', 'obj_id=56');
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
28 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
29 % >> tables = utils.jmysql.dbquery(conn)
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
30 %
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
31 % The 'tables' cell-array will contain a list of the tables in the database
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
32 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
33 % >> info = utils.jmysql.dbquery(conn, query)
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
34 %
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
35 % The 'info' cell-array will contain the results from the SQL query
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
36 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
37 % VERSION: $Id: dbquery.m,v 1.2 2010/01/19 20:55:30 ingo Exp $
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
38 %
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
39
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
40 if ~isa(conn, 'java.sql.Connection')
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
41 error('### first argument should be a java.sql.Connection object');
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
42 end
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
43
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
44 switch nargin
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
45 case 1
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
46 % get table list
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
47 try
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
48 info = utils.mysql.execute(conn, 'SHOW TABLES');
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
49 catch ex
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
50 error('### failed to get table list. %s', ex.message);
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
51 end
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
52
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
53 case 2
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
54 % execute query
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
55 try
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
56 info = utils.mysql.execute(conn, varargin{1});
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
57 catch ex
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
58 error('### failed to execute query. %s', ex.message);
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
59 end
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
60
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
61 case 3
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
62 % query a table
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
63 table = varargin{1};
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
64 query = varargin{2};
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
65 info = runQuery(conn, table, query);
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
66
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
67 otherwise
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
68 error('### incorrect inputs');
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
69 end
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
70
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
71 varargout{1} = info;
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
72 end
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
73
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
74
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
75 function info = getFieldList(conn, table)
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
76 info = {};
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
77 try
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
78 rows = utils.mysql.execute(conn, sprintf('DESCRIBE `%s`', table));
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
79 for kk = 1:size(rows, 1)
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
80 info = [info rows(kk,1)];
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
81 end
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
82 catch ex
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
83 error('### failed to get field list. %s', ex.message);
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
84 end
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
85 end
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
86
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
87
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
88 function info = runQuery(conn, table, query)
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
89 info = {};
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
90 fields = getFieldList(conn, table);
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
91 f = '';
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
92 fs = {};
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
93 for j=1:length(fields)
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
94 % special cases
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
95 f = [f fields{j} ',' ];
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
96 fs = [fs fields(j)];
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
97 end
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
98 q = sprintf('SELECT %s FROM %s WHERE %s', f(1:end-1), table, query);
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
99 try
16
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
100 info = utils.mysql.execute(conn, q);
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
101 catch ex
91f21a0aab35 Update utils.jquery
Daniele Nicolodi <nicolodi@science.unitn.it>
parents: 0
diff changeset
102 error('### failed to query table. %s', ex.message);
0
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
103 end
f0afece42f48 Import.
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
104 end