1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.bnf.context; |
7 | |
8 | import java.sql.Connection; |
9 | import java.sql.DatabaseMetaData; |
10 | import java.sql.PreparedStatement; |
11 | import java.sql.ResultSet; |
12 | import java.sql.SQLException; |
13 | import java.util.ArrayList; |
14 | |
15 | import org.h2.command.Parser; |
16 | import org.h2.util.New; |
17 | import org.h2.util.StringUtils; |
18 | |
19 | /** |
20 | * Keeps meta data information about a database. |
21 | * This class is used by the H2 Console. |
22 | */ |
23 | public class DbContents { |
24 | |
25 | private DbSchema[] schemas; |
26 | private DbSchema defaultSchema; |
27 | private boolean isOracle; |
28 | private boolean isH2; |
29 | private boolean isPostgreSQL; |
30 | private boolean isDerby; |
31 | private boolean isSQLite; |
32 | private boolean isH2ModeMySQL; |
33 | private boolean isMySQL; |
34 | private boolean isFirebird; |
35 | private boolean isMSSQLServer; |
36 | |
37 | /** |
38 | * @return The default schema. |
39 | */ |
40 | public DbSchema getDefaultSchema() { |
41 | return defaultSchema; |
42 | } |
43 | |
44 | /** |
45 | * @return True if this is an Apache Derby database. |
46 | */ |
47 | public boolean isDerby() { |
48 | return isDerby; |
49 | } |
50 | |
51 | /** |
52 | * @return True if this is a Firebird database. |
53 | */ |
54 | public boolean isFirebird() { |
55 | return isFirebird; |
56 | } |
57 | |
58 | /** |
59 | * @return True if this is a H2 database. |
60 | */ |
61 | public boolean isH2() { |
62 | return isH2; |
63 | } |
64 | |
65 | /** |
66 | * @return True if this is a H2 database in MySQL mode. |
67 | */ |
68 | public boolean isH2ModeMySQL() { |
69 | return isH2ModeMySQL; |
70 | } |
71 | |
72 | /** |
73 | * @return True if this is a MS SQL Server database. |
74 | */ |
75 | public boolean isMSSQLServer() { |
76 | return isMSSQLServer; |
77 | } |
78 | |
79 | /** |
80 | * @return True if this is a MySQL database. |
81 | */ |
82 | public boolean isMySQL() { |
83 | return isMySQL; |
84 | } |
85 | |
86 | /** |
87 | * @return True if this is an Oracle database. |
88 | */ |
89 | public boolean isOracle() { |
90 | return isOracle; |
91 | } |
92 | |
93 | /** |
94 | * @return True if this is a PostgreSQL database. |
95 | */ |
96 | public boolean isPostgreSQL() { |
97 | return isPostgreSQL; |
98 | } |
99 | |
100 | /** |
101 | * @return True if this is an SQLite database. |
102 | */ |
103 | public boolean isSQLite() { |
104 | return isSQLite; |
105 | } |
106 | |
107 | /** |
108 | * @return The list of schemas. |
109 | */ |
110 | public DbSchema[] getSchemas() { |
111 | return schemas; |
112 | } |
113 | |
114 | /** |
115 | * Read the contents of this database from the database meta data. |
116 | * |
117 | * @param url the database URL |
118 | * @param conn the connection |
119 | */ |
120 | public synchronized void readContents(String url, Connection conn) |
121 | throws SQLException { |
122 | isH2 = url.startsWith("jdbc:h2:"); |
123 | if (isH2) { |
124 | PreparedStatement prep = conn.prepareStatement( |
125 | "SELECT UPPER(VALUE) FROM INFORMATION_SCHEMA.SETTINGS " + |
126 | "WHERE NAME=?"); |
127 | prep.setString(1, "MODE"); |
128 | ResultSet rs = prep.executeQuery(); |
129 | rs.next(); |
130 | if ("MYSQL".equals(rs.getString(1))) { |
131 | isH2ModeMySQL = true; |
132 | } |
133 | rs.close(); |
134 | prep.close(); |
135 | } |
136 | isSQLite = url.startsWith("jdbc:sqlite:"); |
137 | isOracle = url.startsWith("jdbc:oracle:"); |
138 | // the Vertica engine is based on PostgreSQL |
139 | isPostgreSQL = url.startsWith("jdbc:postgresql:") || url.startsWith("jdbc:vertica:"); |
140 | // isHSQLDB = url.startsWith("jdbc:hsqldb:"); |
141 | isMySQL = url.startsWith("jdbc:mysql:"); |
142 | isDerby = url.startsWith("jdbc:derby:"); |
143 | isFirebird = url.startsWith("jdbc:firebirdsql:"); |
144 | isMSSQLServer = url.startsWith("jdbc:sqlserver:"); |
145 | DatabaseMetaData meta = conn.getMetaData(); |
146 | String defaultSchemaName = getDefaultSchemaName(meta); |
147 | String[] schemaNames = getSchemaNames(meta); |
148 | schemas = new DbSchema[schemaNames.length]; |
149 | for (int i = 0; i < schemaNames.length; i++) { |
150 | String schemaName = schemaNames[i]; |
151 | boolean isDefault = defaultSchemaName == null || |
152 | defaultSchemaName.equals(schemaName); |
153 | DbSchema schema = new DbSchema(this, schemaName, isDefault); |
154 | if (isDefault) { |
155 | defaultSchema = schema; |
156 | } |
157 | schemas[i] = schema; |
158 | String[] tableTypes = { "TABLE", "SYSTEM TABLE", "VIEW", |
159 | "SYSTEM VIEW", "TABLE LINK", "SYNONYM", "EXTERNAL" }; |
160 | schema.readTables(meta, tableTypes); |
161 | if (!isPostgreSQL) { |
162 | schema.readProcedures(meta); |
163 | } |
164 | } |
165 | if (defaultSchema == null) { |
166 | String best = null; |
167 | for (DbSchema schema : schemas) { |
168 | if ("dbo".equals(schema.name)) { |
169 | // MS SQL Server |
170 | defaultSchema = schema; |
171 | break; |
172 | } |
173 | if (defaultSchema == null || |
174 | best == null || |
175 | schema.name.length() < best.length()) { |
176 | best = schema.name; |
177 | defaultSchema = schema; |
178 | } |
179 | } |
180 | } |
181 | } |
182 | |
183 | private String[] getSchemaNames(DatabaseMetaData meta) throws SQLException { |
184 | if (isMySQL || isSQLite) { |
185 | return new String[] { "" }; |
186 | } else if (isFirebird) { |
187 | return new String[] { null }; |
188 | } |
189 | ResultSet rs = meta.getSchemas(); |
190 | ArrayList<String> schemaList = New.arrayList(); |
191 | while (rs.next()) { |
192 | String schema = rs.getString("TABLE_SCHEM"); |
193 | String[] ignoreNames = null; |
194 | if (isOracle) { |
195 | ignoreNames = new String[] { "CTXSYS", "DIP", "DBSNMP", |
196 | "DMSYS", "EXFSYS", "FLOWS_020100", "FLOWS_FILES", |
197 | "MDDATA", "MDSYS", "MGMT_VIEW", "OLAPSYS", "ORDSYS", |
198 | "ORDPLUGINS", "OUTLN", "SI_INFORMTN_SCHEMA", "SYS", |
199 | "SYSMAN", "SYSTEM", "TSMSYS", "WMSYS", "XDB" }; |
200 | } else if (isMSSQLServer) { |
201 | ignoreNames = new String[] { "sys", "db_accessadmin", |
202 | "db_backupoperator", "db_datareader", "db_datawriter", |
203 | "db_ddladmin", "db_denydatareader", |
204 | "db_denydatawriter", "db_owner", "db_securityadmin" }; |
205 | } |
206 | if (ignoreNames != null) { |
207 | for (String ignore : ignoreNames) { |
208 | if (ignore.equals(schema)) { |
209 | schema = null; |
210 | break; |
211 | } |
212 | } |
213 | } |
214 | if (schema == null) { |
215 | continue; |
216 | } |
217 | schemaList.add(schema); |
218 | } |
219 | rs.close(); |
220 | String[] list = new String[schemaList.size()]; |
221 | schemaList.toArray(list); |
222 | return list; |
223 | } |
224 | |
225 | private String getDefaultSchemaName(DatabaseMetaData meta) { |
226 | String defaultSchemaName = ""; |
227 | try { |
228 | if (isOracle) { |
229 | return meta.getUserName(); |
230 | } else if (isPostgreSQL) { |
231 | return "public"; |
232 | } else if (isMySQL) { |
233 | return ""; |
234 | } else if (isDerby) { |
235 | return StringUtils.toUpperEnglish(meta.getUserName()); |
236 | } else if (isFirebird) { |
237 | return null; |
238 | } |
239 | ResultSet rs = meta.getSchemas(); |
240 | int index = rs.findColumn("IS_DEFAULT"); |
241 | while (rs.next()) { |
242 | if (rs.getBoolean(index)) { |
243 | defaultSchemaName = rs.getString("TABLE_SCHEM"); |
244 | } |
245 | } |
246 | } catch (SQLException e) { |
247 | // IS_DEFAULT not found |
248 | } |
249 | return defaultSchemaName; |
250 | } |
251 | |
252 | /** |
253 | * Add double quotes around an identifier if required. |
254 | * For the H2 database, all identifiers are quoted. |
255 | * |
256 | * @param identifier the identifier |
257 | * @return the quoted identifier |
258 | */ |
259 | public String quoteIdentifier(String identifier) { |
260 | if (identifier == null) { |
261 | return null; |
262 | } |
263 | if (isH2 && !isH2ModeMySQL) { |
264 | return Parser.quoteIdentifier(identifier); |
265 | } |
266 | return StringUtils.toUpperEnglish(identifier); |
267 | } |
268 | |
269 | } |